Search Results

Search found 28900 results on 1156 pages for 'sql 2005'.

Page 369/1156 | < Previous Page | 365 366 367 368 369 370 371 372 373 374 375 376  | Next Page >

  • Clustered index - multi-part vs single-part index and effects of inserts/deletes

    - by Anssssss
    This question is about what happens with the reorganizing of data in a clustered index when an insert is done. I assume that it should be more expensive to do inserts on a table which has a clustered index than one that does not because reorganizing the data in a clustered index involves changing the physical layout of the data on the disk. I'm not sure how to phrase my question except through an example I came across at work. Assume there is a table (Junk) and there are two queries that are done on the table, the first query searches by Name and the second query searches by Name and Something. As I'm working on the database I discovered that the table has been created with two indexes, one to support each query, like so: --drop table Junk1 CREATE TABLE Junk1 ( Name char(5), Something char(5), WhoCares int ) CREATE CLUSTERED INDEX IX_Name ON Junk1 ( Name ) CREATE NONCLUSTERED INDEX IX_Name_Something ON Junk1 ( Name, Something ) Now when I looked at the two indexes, it seems that IX_Name is redundant since IX_Name_Something can be used by any query that desires to search by Name. So I would eliminate IX_Name and make IX_Name_Something the clustered index instead: --drop table Junk2 CREATE TABLE Junk2 ( Name char(5), Something char(5), WhoCares int ) CREATE CLUSTERED INDEX IX_Name_Something ON Junk2 ( Name, Something ) Someone suggested that the first indexing scheme should be kept since it would result in more efficient inserts/deletes (assume that there is no need to worry about updates for Name and Something). Would that make sense? I think the second indexing method would be better since it means one less index needs to be maintained. I would appreciate any insight into this specific example or directing me to more info on maintenance of clustered indexes.

    Read the article

  • Linq to SQL gives NotSupportedException when using local variables

    - by zwanz0r
    It appears to me that it matters whether you use a variable to temporary store an IQueryable or not. See the simplified example below: This works: List<string> jobNames = new List<string> { "ICT" }; var ictPeops = from p in dataContext.Persons where ( from j in dataContext.Jobs where jobNames.Contains(j.Name) select j.ID).Contains(p.JobID) select p; But when I use a variable to temporary store the subquery I get an exception: List<string> jobNames = new List<string> { "ICT" }; var jobs = from j in dataContext.Jobs where jobNames.Contains(j.Name) select j.ID; var ictPeops = from p in dataContext.Persons where jobs.Contains(p.JobID) select p; "System.NotSupportedException: Queries with local collections are not supported" I don't see what the problem is. Isn't this logic that is supposed to work in LINQ?

    Read the article

  • Multilingual best practices on SQL Server, EF and MVC combinations

    - by dengereli
    ASP.NET MVC, resource management is look like enough for application multlingual multiculture support. But I am wondering practices about data. User stories; User set culture as en-US and see all product items in English. User set culture as fr-FR and see all product items in French. User set culture as ru-RU and see all product items in Russian. User doesn't have right change culture settings and application never reach multilingual resources, it will use default language and culture.

    Read the article

  • MS SQL: Primary file group is full

    - by aximili
    I have a very large table in my database and I am starting to get this error Could not allocate a new page for database 'mydatabase' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. How do you fix this error? I don't understand the suggestions there.

    Read the article

  • Good way to format decimal in SQL Server

    - by Brad
    We store a decimal(9,8) in our database. It can have any number of places after the decimal point (well, no more than 8). I am frustrated because I want to display it as human-readable text as part of a larger string created on the server. I want as many decimals to the right of the decimal point as are non-zero, for example: 0.05 0.12345 3.14159265 Are all good If I do CAST(d AS varchar(50)) I get formatting like: 0.05000000 0.12345000 3.14159265 I get similar output if I cast/convert to a float or other type before casting to a varchar. I know how to do a fixed number of decimal places, such as: 0.050 0.123 3.142 But that is not what I want. Yes, I know I can do this through complicated string manipulation (REPLACE, etc), there should be a good way to do it.

    Read the article

  • Why hasn't MSSQL made a WHERE clause mandatory by default?

    - by Josh Einstein
    It seems like a no brainer to me. I've heard countless stories about people forgetting the WHERE clause in an UPDATE or DELETE and trashing an entire table. I know that careless people shouldn't be issuing queries directly and all that... and that there are legitimate cases where you want to affect all rows, but wouldn't it make sense to have an option on by default that requires such queries to be written like: UPDATE MyTable SET MyColumn = 0 WHERE * Or without changing the language, UPDATE MyTable SET MyColumn = 0 WHERE 1 = 1 -- tacky, I know

    Read the article

  • TSQL: Variable scope and EXEC()

    - by Joel
    declare @test varchar(20) set @test = 'VALUE' exec(' select '+@test+' ') This returns: Invalid column name 'VALUE'. Is there an alternate method to display the variable value on the select statement?

    Read the article

  • sql query by passing te values in one table

    - by subash
    can any one help me in generating query for the below scenario? i have twop tables TableA and TableB TableA has teh follwing columns EMPLOYEEID, SKILLSETCODE,CERTID, LASTNAME, FIRSTNAME, MIDDLEINITIAL TableB has two columns EMPLOYEEID and key_user i want to SELECT EMPLOYEEID, SKILLSETCODE,CERTID, LASTNAME, FIRSTNAME, MIDDLEINITIAL FROM TableA WHERE EMPLOYEEID = (select employeeid from TableB where key_user='249')

    Read the article

  • SQL Server 2008 - Difference between time(0)

    - by lugeno
    I've a table with working_hours time(0), lunch_hours time(0) What I have to do is the following: If lunch_hours is greater that one hour, I have to calculate the offset Example: lounch_hour = 01:30:00 = offset = 00:30:00 Once done I've to subtract the offset from the working_hours value Example: offset = 00:30:00, working_hours = 07:30:00 = working_hours = 07:00:00 The result must be in time(0) format (hh:mm:ss) I've tried several solutions but still not working. Used DATEDIFF probably didn't used in correct way. Thanks for any help Bye!

    Read the article

  • Count problem in SQL when I want results from diffrent tabels

    - by Nicklas
    ALTER PROCEDURE GetProducts @CategoryID INT AS SELECT COUNT(tblReview.GroupID) AS ReviewCount, COUNT(tblComment.GroupID) AS CommentCount, Product.GroupID, MAX(Product.ProductID) AS ProductID, AVG(Product.Price) AS Price, MAX (Product.Year) AS Year, MAX (Product.Name) AS Name, AVG(tblReview.Grade) AS Grade FROM tblReview, tblComment, Product WHERE (Product.CategoryID = @CategoryID) GROUP BY Product.GroupID HAVING COUNT(distinct Product.GroupID) = 1 This is what the tabels look like: **Product** |**tblReview** | **tblComment** ProductID | ReviewID | CommentID Name | Description | Description Year | GroupID | GroupID Price | Grade | GroupID GroupID is name_year of a Product, ex Nike_2010. One product can have diffrent sizes for exampel: ProductID | Name | Year | Price | Size | GroupID 1 | Nike | 2010 | 50 | 8 | Nike_2010 2 | Nike | 2010 | 50 | 9 | Nike_2010 3 | Nike | 2010 | 50 | 10 | Nike_2010 4 | Adidas| 2009 | 45 | 8 | Adidas_2009 5 | Adidas| 2009 | 45 | 9 | Adidas_2009 6 | Adidas| 2009 | 45 | 10 | Adidas_2009 I dont get the right count in my tblReview and tblComment. If I add a review to Nike size 8 and I add one review to Nike size 10 I want 2 count results when I list the products with diffrent GroupID. Now I get the same count on Reviews and Comment and both are wrong. I use a datalist to show all the products with diffrent/unique GroupID, I want it to be like this: ______________ | | | Name: Nike | | Year: 2010 | | (All Sizes) | | x Reviews | | x Comments | | x AVG Grade | |______________| All Reviewcounts, Commentcounts and the Average of all products with the same GroupID, the Average works great.

    Read the article

  • VB working with SQL DB - end of row count, keeps looping

    - by Tramd
    I'm adding to a combo box an ID and a name that i'm pulling from a database. My problem is that for some reason my loop doesnt end once it reaches the end of the records in the database table. Here's my code: For intcount = 0 To dtOrders.Rows.Count - 1 cmbSearch.Items.Add(dtOrders.Rows(intcount)("EmployeeID").ToString & " " & dtOrders.Rows(intcount)("EmployeeLastName").ToString & ", " & dtOrders.Rows(intcount)("EmployeeFirstName").ToString) Next Shouldnt the .rows.count - 1 stop it once it reaches the last record? It loops 4 times through.

    Read the article

  • LINQ to SQL: On load processing of lazy loaded associations

    - by Matt Holmes
    If I have an object that lazy loads an association with very large objects, is there a way I can do processing at the time the lazy load occurs? I thought I could use AssociateWith or LoadWith from DataLoadOptions, but there are very, very specific restrictions on what you can do in those. Basically I need to be notified when an EntitySet< decides it's time to load the associated object, so I can catch that event and do some processing on the loaded object. I don't want to simply walk through the EntitySet when I load the parent object, because that will force all the lazy loaded items to load (defeating the purpose of lazy loading entirely).

    Read the article

  • SQL Access INSERT INTO Autonumber Field

    - by KrazyKash
    I'm trying to make a visual basic application which is connected to a Microsoft Access Database using OLEDB. Inside my database I have a user table with the following layout ID - Autonumber Username - Text Password - Text Email - Text To insert data into the table I use the following query INSERT INTO Users (Username, Password, Email) VALUES ('004606', 'Password', '[email protected]') However I seem to get an error with this statement and according to VB it's a syntax error. But then I tried to use the following query INSERT INTO Users (Username) Values ('004606') This query seemed to work absolutely fine... So the problem is I can insert into just one field but not all 3 (excluding the ID field because it's an autonumber). Any help would be appreciated, Thanks

    Read the article

  • Need help with SQL Query

    - by StackOverflowNewbie
    Say I have 2 tables: Person - Id - Name PersonAttribute - Id - PersonId - Name - Value Further, let's say that each person had 2 attributes (say, gender and age). A sample record would be like this: Person->Id = 1 Person->Name = 'John Doe' PersonAttribute->Id = 1 PersonAttribute->PersonId = 1 PersonAttribute->Name = 'Gender' PersonAttribute->Value = 'Male' PersonAttribute->Id = 2 PersonAttribute->PersonId = 1 PersonAttribute->Name = 'Age' PersonAttribute->Value = '30' Question: how do I query this such that I get a result like this: 'John Doe', 'Male', '30'

    Read the article

  • User preferences using SQL and JavaScript

    - by Shyam
    Hi, I am using Server Side JavaScript - yes, I am actually using Server Side JavaScript. To complexify things even more, I use Oracle as a backend database (10g). With some crazy XSLT and mutant-like HTML generation, I can build really fancy web forms - yes, I am aware of Rails and other likewise frameworks and I choose the path of horror instead. I have no JQuery or other fancy framework at my disposal, just plain ol' JavaScript that should be supported by the underlying engine called Mozilla Rhino. Yes, it is insane and I love it. So, I have a bunch of tables at my disposal and some of them are filled with associative keys that link to values. As I am a people pleaser, I want to add some nifty user-preference driven solutions. My users have all an unique user_id and this user_id is available during the entire session. My initial idea is to have a user preference table, where I have "three" columns: user_id, feature and pref_string. Using a delimiter, such as : or - (haven't thought about a suitable one yet), I could like store a bunch of preferences as a list and store its elements inside an array using the .split-method (similar like the PHP-explode function). The feature column could be like the table name or some identifier for the "feature" i want to link preferences too. I hate hardcoding objects, especially as I want to be able to back these up and reuse this functionality application-wide. Of course I would love better ideas, just keep in mind I cannot just add a library that easily. These preferences could be like "joined" to the table, so I can query it and use its values. I hope it doesn't sounds too complex, because well.. its basically something really simple I need. Thanks!

    Read the article

  • Query with many CASE statements - optimization

    - by Nemanja Vujacic
    Hi guys, I have one very dirty query that per sure can be optimized because there are so many CASE statements in it! SELECT (CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id WHEN 2 THEN fw.fw_id WHEN 3 THEN s.sw_Id WHEN 4 THEN id.ia_id END) as Deal_Id, max(CASE pa.KplusTable_Id WHEN 1 THEN sp.Trans_Id WHEN 2 THEN fw.Trans_Id WHEN 3 THEN s.Trans_Id WHEN 4 THEN id.Trans_Id END) as TransId_CurrentMax INTO #MaxRazlicitOdNull FROM #PotencijalniAktuelni pa LEFT JOIN kplus_sp sp (nolock) on sp.sp_id=pa.Deal_Id AND pa.KplusTable_Id=1 LEFT JOIN kplus_fw fw (nolock) on fw.fw_id=pa.Deal_Id AND pa.KplusTable_Id=2 LEFT JOIN dev_sw s (nolock) on s.sw_Id=pa.Deal_Id AND pa.KplusTable_Id=3 LEFT JOIN kplus_ia id (nolock) on id.ia_id=pa.Deal_Id AND pa.KplusTable_Id=4 WHERE isnull(CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA WHEN 2 THEN fw.BROJ_TIKETA WHEN 3 THEN s.tiket WHEN 4 THEN id.BROJ_TIKETA END, '')<>'' GROUP BY CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id WHEN 2 THEN fw.fw_id WHEN 3 THEN s.sw_Id WHEN 4 THEN id.ia_id END Because I have same condition couple times, do you have idea how to optimize query, make it simpler and better. All suggestions are welcome! TnX in advance! Nemanja

    Read the article

  • Replication - User defined table type not propogating to subscriber

    - by Aamod Thakur
    I created a User defined table type named tvp_Shipment with two columns (id and name) . generated a snapshot and the User defined table type was properly propogated to all the subscribers. I was using this tvp in a stored procedure and everything worked fine. Then I wanted to add one more column created_date to this table valued parameter.I dropped the stored procedure (from replication too) and also i dropped and recreated the User defined table type with 3 columns and then recreated the stored procedure and enabled it for publication When i generate a new snapshot, the changes in user defined table type are not propogated to the subscriber. The newly added column was not added to the subscription. the Error messages: The schema script 'usp_InsertAirSa95c0e23_218.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 Invalid column name 'created_date'. (Source: MSSQLServer, Error number: 207) Get help: http://help/207

    Read the article

  • LINQ to SQL:DataContext.SubmitChanges not updating immediately

    - by aximili
    I have a funny problem. Doing DataContext.SubmitChanges() updates Count() in one way but not in the other, see my comment in the code below.(DC is the DataContext) var compliances = c.DataCompliances.Where(x => x.ComplianceCriteria.FKElement == e.Id); if (compliances.Count() == 0) // Insert if not exists { DC.DataCompliances.InsertOnSubmit(new DataCompliance { FKCompany = c.Id, FKComplianceCriteria = criteria.Id }); DC.SubmitChanges(); compliances = c.DataCompliances.Where(x => x.ComplianceCriteria.FKElement == e.Id); // At this point DC.DataCompliances.Count() has increased, // but compliances.Count() is still 0 // When I refresh the page however, it will be 1 } Why does that happen? I need to update compliances after inserting one. Does anyone have a solution?

    Read the article

  • Performance optimization for SQL Server: decrease stored procedures execution time or unload the ser

    - by tim
    We have a web service which provides search over hotels. There is a problem with performance: a single request to the service takes around 5000 ms. Almost all of the time is spent in database by executing storing procedures. During the request our server (mssql2008) consumes ~90% of the processor time. When 2 requests are made in parallel the average time grows and is around 7000 ms. When number of request is increasing, the average time of response is increasing as well. We have 20-30 requests per minute. Which kind of optimization is the best in this case having in mind that the goal is to provide stable response time for the service: 1) Try to decrease the stored procedures execution time 2) Try to find the way how to unload the server It is interesting to hear from people who deal with booking sites.

    Read the article

  • Return if remote stored procedure fails

    - by njk
    I am in the process of creating a stored procedure. This stored procedure runs local as well as external stored procedures. For simplicity, I'll call the local server [LOCAL] and the remote server [REMOTE]. USE [LOCAL] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[monthlyRollUp] AS SET NOCOUNT, XACT_ABORT ON BEGIN TRY EXEC [REOMTE].[DB].[table].[sp] --This transaction should only begin if the remote procedure does not fail BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp1] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp2] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp3] COMMIT BEGIN TRAN EXEC [LOCAL].[DB].[table].[sp4] COMMIT END TRY BEGIN CATCH -- Insert error into log table INSERT INTO [dbo].[log_table] (stamp, errorNumber, errorSeverity, errorState, errorProcedure, errorLine, errorMessage) SELECT GETDATE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() END CATCH GO When using a transaction on the remote procedure, it throws this error: OLE DB provider ... returned message "The partner transaction manager has disabled its support for remote/network transactions.". I get that I'm unable to run a transaction locally for a remote procedure. How can I ensure that the this procedure will exit and rollback if any part of the procedure fails?

    Read the article

  • SQL Server schema-owner permissions

    - by Andrew Bullock
    if i do: CREATE SCHEMA [test] AUTHORIZATION [testuser] testuser doesn't seem to have any permissions on the schema, is this correct? I thought as the principal that owns the schema, you had full control over it? What permission do i need to grant testuser so that it has full control over the test schema only? Edit: by "full control" i mean the ability to CRUD tables, views, sprocs etc Thanks

    Read the article

< Previous Page | 365 366 367 368 369 370 371 372 373 374 375 376  | Next Page >