In my code, I am creating a temp table using select * into #tempTable.  Temp table contains the huge number of records and I need to retrieve data from it many times in my code so I created a clustered index on it. But somehow I am not getting data in correct order as per created index.  I know I can do it simply by putting the order by clause, but my question is why I am not getting data in the correct order even if I am forcing the optimizer to use the particular index?  Below is my sample code:  ----------- Sample code ----------------------------  ;with CTE1 as (     select col1         ,col2         ,-- - -- - - from table1      union      select col1         ,col2         ,-- - -- - - from table2         -- few more tables in union --     )     , CTE2 as (     select *         ,RowNumber = RowNumber()              over (order by case                      when @sortOrder = 0                     then case                              when @sortColumn = 'Date'                             then [tdate]......some more dynamic order by conditions..........                          end                     end asc             )     from CTE1     inner join     from TABLE3 on -- some conditions     ) select * from CTE2 into #TempTable;   Then add the clustered index:  create clustered index IX_TempTable_RowNumber on #TempTable (RowNumber asc);  select * from #TempTable with (index (IX_TempTable_RowNumber));  ----------------- End of sample code ---------------------   Even I checked the table scheme and index details using  exec tempdb.dbo.sp_help N'#TempTable';  select * from tempdb.sys.indexes where name = N'IX_TempTable_RowNumber'   And it seems correct to me. What am I missing here?
124048
In order to better understand the SQL Server query processor, I've been thinking about how the ORDER BY clause works, and how SQL Server provides results.  It appears SQL Server will provide results in exactly the same order for any given set of data that remains the same and never, ever changes.  Once you introduce any kind of uncertainty such as developers changing something, the order of results can no longer be expected to be the same.  Simply seeing the results in the same order, each time you press F5 does not guarantee the results you expect.  Try this:  USE TempDB; DROP TABLE TestOrderBy; DROP TABLE TestOrderBy2; CREATE TABLE TestOrderBy (     ID INT NOT NULL CONSTRAINT PK_TestOrderBy PRIMARY KEY CLUSTERED IDENTITY(1,1)     , SomeData varchar(255) ); INSERT INTO TestOrderBy (SomeData) VALUES ('E'); INSERT INTO TestOrderBy (SomeData) VALUES ('D'); INSERT INTO TestOrderBy (SomeData) VALUES ('C'); INSERT INTO TestOrderBy (SomeData) VALUES ('B'); INSERT INTO TestOrderBy (SomeData) VALUES ('A');  CREATE TABLE TestOrderBy2 (     ID INT NOT NULL CONSTRAINT PK_TestOrderBy2 PRIMARY KEY CLUSTERED IDENTITY(1,1)     , SomeData varchar(255) ); INSERT INTO TestOrderBy2 (SomeData) VALUES ('E'); INSERT INTO TestOrderBy2 (SomeData) VALUES ('D'); INSERT INTO TestOrderBy2 (SomeData) VALUES ('C'); INSERT INTO TestOrderBy2 (SomeData) VALUES ('B'); INSERT INTO TestOrderBy2 (SomeData) VALUES ('A');  SELECT SomeData  FROM TestOrderBy UNION ALL SELECT SomeData FROM TestOrderBy2;  CREATE INDEX IX_TestOrderBy_SomeData ON TestOrderBy (SomeData); CREATE INDEX IX_TestOrderBy2_SomeData ON TestOrderBy2 (SomeData);  SELECT SomeData  FROM TestOrderBy UNION ALL SELECT SomeData FROM TestOrderBy2;   The results:    As you see, adding a simple index on the fields selected in the query alters the order of the results.  From here on, I'm adding ORDER BY unless I truly don't care.
42549
All,  So I've got all my select queries in LINQ-to-SQL converted to using CompiledQueries to speed things up.  Works great so far for select statements, but I haven't been able to figure out how to pre-compile insert, update, or delete statements.  Granted, when you're inserting, deleting or updating in LINQ-to-SQL, you have to use the object model.  But obviously somewhere along the way it generates a query, which would be nice to pre-compile and store in a static member.  Is this possible?  What is LINQ performance like for updates, deletes and inserts when its not pre-compiled?  I could see it being a lot faster than the selects, because what they do underneath is a lot simpler and less "dynamic"...
51458
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?  If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?  (I'm guessing the answers are yes and yes :).
32585
For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan.  I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause, and then at the end just shrink that result set down to the n number of rows that was requested.  The graphical execution plan seems to indicate this is the case -- TOP is the "last" step.  But it appears there is more going on.  My question is, how (and why) does a TOP n clause impact the execution plan of a query?  Here is a simplified version of what is going on in my case:  The query is matching rows from two tables, A and B.    Without the TOP clause, the optimizer estimates there will be 19k rows from table A and 46k rows from table B.  The actual number of rows returned is 16k for A and 13k for B.  A hash match is used to join these two results sets for a total of 69 rows (then a sort is applied).  This query happens very quickly.  When I add TOP 1001 the optimizer does not use a hash match; instead it first sorts the results from table A (same estimate/actual of 19k/16k) and does a nested loop against table B.  The estimated number of rows for table B is now 1, and the strange thing is that the TOP n directly affects the estimated number of executions (index seek) against B -- it appears to always be 2n+1, or in my case 2003.  This estimate changes accordingly if I change TOP n.  Of course, since this is a nested join the actual number of executions is 16k (the number of rows from table A) and this slows down the query.  The actual scenario is a bit more complex but this captures the basic idea/behavior.  Both tables are searched using index seeks.  This is SQL Server 2008 R2 Enterprise edition.
81744
Is there any way in SQL Server to get the results starting at a given offset? For example, in another type of SQL database, it's possible to do:  SELECT * FROM MyTable OFFSET 50 LIMIT 25   to get results 51-75. This construct does not appear to exist in SQL Server.    How can I accomplish this without loading all the rows I don't care about?  Thanks!
110869
A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of relationships, so a relational database seems to make sense.  The common solution that I have seen is to add an integer column order:  CREATE TABLE AS your_table (id, title, sort_order) AS VALUES   (0, 'Lorem ipsum',   3),   (1, 'Dolor sit',     2),   (2, 'Amet, consect', 0),   (3, 'Elit fusce',    1);   Then, we can sort the rows by order to get them in the proper order.  However, this seems clumsy:   If I want to move record 0 to the start, I have to reorder every record If I want to insert a new record in the middle, I have to reorder every record after it If I want to remove a record, I have to reorder every record after it   It's easy to imagine a situations like:   Two records have the same order There are gaps in the order between records   These could happen fairly easily for a number of reasons.  This is the approach that applications like Joomla take:    You could argue that the interface here is bad, and that instead of humans directly editing numbers, they should use arrows or drag-and-drop—and you'd probably be right. But behind the scenes, the same thing is happening.  Some people have proposed using a decimal to store order, so that you can use "2.5" to insert a record in between the records at order 2 and 3. And while that helps a little, it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)  Is there a better way to store order in a table?
14862
I am trying to query two tables and get results like the following:  Section    Names shoes      AccountName1, AccountName2, AccountName3 books      AccountName1   The tables are:  CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));  INSERT dbo.TableA(ID, Section, AccountId) VALUES (1 ,'shoes','A1'), (2 ,'shoes','A2'), (3 ,'shoes','A3'), (4 ,'books','A1');  CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));  INSERT dbo.TableB(AccountId, Name) VALUES ('A1','AccountName1'), ('A2','AccountName2'), ('A3','AccountNAme3');   I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing.  I have tried the below query and get the error message:     Column 'a.AccountId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.   I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.  Here is the query I am currently trying to get working correctly.  SELECT section, names= STUFF((     SELECT ', ' + Name FROM TableB as b  WHERE AccountId = b.AccountId FOR XML PATH('')), 1, 1, '') FROM TableA AS a GROUP BY a.section
2711
I have this code:  DECLARE @MyTable AS TABLE (    [Month] INT,    Salary INT );  INSERT INTO @MyTable VALUES (1,2000), (1,3100);  SELECT [Month], Salary FROM @MyTable;   Output:    I want to concat the Salary (grouping by month) so that it will be NVARCHAR like this: '2000,3100'  How would I do this efficiently?
78310
We are debating whether to use the SORT_IN_TEMPDB option for our DW tables. My understanding is that there are more writes when using this option, although they are more sequential. We have a SAN (which has has been notoriously slow at times), so in our case we want to limit the number of writes as much as possible. I believe tempdb is on a separate LUN (set of disks).  We have plenty of disk space in our data file and on our tempdb file. In this case, would we benefit from using SORT_IN_TEMPDB?   One thing that struck me was this comment on this      When rebuilding an index you would need twice the space of the index +   20% for the sorting. So in general to rebuild every index in your db   you only need 120% of your biggest index in your DB. If you use   SORT_IN_TEMPDB, you only win 20%, you still need an aditional 100% in   your data file. Further more, using sort in tempdb increases your IO   load drastically, since instead of Writing the index one time to the   datafile, you now write it one time to the tempdb and then write it to   the data file. So that is not always ideal.   We definitely don't want to increase our IO load with our slow/possibly misconfigured SAN.  What would be the best way to test this? By simply rebuilding the table with and without the option and log the times?  Edit: We have 8 tempdb files, each 15GB. We do have TF 1117/1118 flags set and IFI is enabled. We currently do a mixture of rebuilding with the sort_in_tempdb option and without it.   Thanks!  SQL Server 2012 Enterprise
58140
I have a common scenario in app design, where I need to store an ordered list, and make the ordering easily accessible and adjustable via an application UI.    The ordering operations are simple and operate on only one item at a time - move to top, move to bottom, move up one position, or down one position.  These positioning operations may be interspersed in with typical add / edit / delete list operations.  For discussion, this is a single-user app and db store.  Because the objects can be highly complex, I'd like to avoid caching the entire list client-side, and tracking newly created objects in memory until a big save operation occurs.    I am using MS-SQL almost exclusively on these projects, and I'm wondering if there are db features, SQL features, or design patterns I should be using to better support the management of ordered lists.    My current approach is to use a DECIMAL column to store the list positions, which become fractional during the item move process.  Then I sort the list and re-generate integer positions to normalize list.  Following this operation, it's ready for another item move.   In an example implementation, I create a column named Seq for position tracking.  Once decimal place is enough for the .5 storage.   [Seq] [decimal](10, 1) NOT NULL   When an item is re-positioned in the app UI,    I UPDATE that record with new Seq value I retrieve the full table, sorted ascending by Seq and regenerate all Seq values in the table from a 1-based integer series   The new Seq value is easily calculated-    Move to top = 0 Move to bottom = MAX(Seq) + 1 Move up = subtract 1.5 from the current Seq value Move down = add 1.5 to the current Seq value   As lists get longer, updates get slower, since I'm doing N UPDATE statements, where N is the number of rows in the table, so it seems only suitable for small tables.    Is there some kind of in-built capability for managing an ordered list in SQL or MS-SQL? Is there a means to update multiple rows in one call?    A far more performant approach would be to allow more decimal places, and then position at the median of the two adjacent items, with no re-sequencing on the list.     Move to top = Min(Seq) - 1 Move to bottom = MAX(Seq) + 1 Move up = median of the 2 previous item Seq values Move down = median of the 2 following item Seq values   This approach only requires one SELECT (containing 2 rows max) and one UPDATE.    However with enough re-positions I imagine the field would run out of decimal places. For example, if I start with items at positions 10 and 11, and keep moving items down the list from position 9, they'd be given sequences of 10.5, then 10.25, then 10.125.  After 10 moves, the positions get lengthy... 10.0009765625.    UPDATE #1 - IMPROVED RE-SEQUENCING OF THE LIST  Regarding re-sequencing, I'm more caught up on row_number(), OVER, and WITH capabilities in MS-SQL.  It's nice to find that there's a much smoother way to re-sequence the list in a single UPDATE operation;  with OrderedItems as  (     select         ItemID,         Seq,         row_number() over (order by Seq asc) NewSeq     from          Items ) update OrderedItems set Seq = NewSeq
33374
I've got a report being built from a dataset. The dataset uses the Sort property to order the data. I know that I can create a sort expression like this:  "field desc, field2 asc"  But what I need now is a way to do a custom sort. In SQL, I can perform a custom sort by doing something like this:  order by      case when field = 'Some Value' then 0 end     case when field = 'Another Value' then 1 end   To basically re-define my sort (i.e, Some Value comes before Another Value).  Is it possible to do something similar as a sort expression against a DataView?
34791
This is the announcement post of the Stack Exchange Data Explorer. Its answers were meant to iron out the initial glitches 6 to 8 years ago.  If you have found a bug or need support  today with any of SEDEs features, please ask a new question and tag it with   What does it do? Allows you to run arbitrary SQL queries on the data dumps, and share those queries with your friends. Technical details It is hosted on the Stack Exchange infrastructure , the database is hosted on the same SQL Server version all other sites are hosted on. You can use most of the TSQL commands, even create temporary objects (but not User Defined Functions). The controls used are jQuery based, the grid is  . All query results are cached, so sharing a link to a query will not result in the server going in to a tailspin. The query edit control is a slightly modified  control, I also am considering bespin cause the inter iframe communication is nasty complicated. It gives up nice syntax highlighting for queries. Parameters There is support for parameterized queries, to get it to work name your params like ##this##. For example have a . Query name and description A query can have a name and description, to name it lead with a comment, to describe it continue commenting, for example: -- This is my query name  -- This is my description -- I can span multiple lines  SELECT 1  Anonymous users can initially name a query, but can not change the names of any existing queries. Tutorial There's a  which helps you getting started in writing your own SQL queries. Logging in You can use your openid provider to log in, at the moment once logged in we will track the list of queries you execute (giving you basic history support) Also if there are any params named UserId they will be populated automatically if your EmailHash matches the one on the site being queried. Magic columns If you alias an id column with as [Post Link] it will automatically create a link in the result set to the parent site. Similarly if you alias an id column with as [User Link] it will display a link to the user page. select top 10 * from Posts will show you how tags are done. (Magic columns for images is planned) example (most controversial posts:  ) So what are you waiting for, check it out at  Any feedback would be greatly appreciated. I hope to have the login section working fairly soon and allow voting on the best queries and commenting. Featured Queries At the moment, admins have permission to feature interesting queries on this page , I am looking for better ways to manage the huge query list that is building up.
64730
--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i&lt;100 BEGIN INSERT INTO fltrind(a) values(@i) SET @i=@i+1 END INSERT INTO fltrind(a,b) values(9000,0900)  insert into fltrind(a,b) select top 100000 f1.a,f1.b from fltrind f1 , fltrind f2  create nonclustered index nf on fltrind(b) INCLUDE(a) create nonclustered index f on fltrind(b) INCLUDE(a) where b is not null  UPDATE STATISTICS fltrind WITH FULLSCAN  select a from fltrind where b is not null  DROP INDEX fltrind.nf  UPDATE STATISTICS fltrind WITH FULLSCAN  select a from fltrind where b is not null   Looking at the query plan, the filtered index is not used as long as the non-filtered index is present.  Any idea why, and how can I get it to use the filtered index?  Dropping the non filtered index nf makes the optimizer use the filtered index f.  In fact, increasing the rowcount so that >10% of the rows qualify results in a table scan when the non filtered index is dropped.
11195
I found an example in the  for Dynamic LINQ that allows you to use a sql-like string (e.g. OrderBy("Name, Age DESC")) for ordering. Unfortunately, the method included only works on IQueryable&lt;T&gt;. Is there any way to get this functionality on IEnumerable&lt;T&gt;?
70647
Is a   select *  from myView   faster than the query itself to create the view (in order to have the same resultSet):  select * from ([query to create same resultSet as myView])   ?  It's not totally clear to me if the view uses some sort of caching making it faster compared to a simple query.
10339
In SQL Server 2005, we can create temp tables one of two ways:  declare @tmp table (Col1 int, Col2 int);   or  create table #tmp (Col1 int, Col2 int);   What are the differences between these two?  I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory.    In which scenarios does one out-perform the other?
42372
I'd like to select the most recent row in a MS SQL Server 2008 R2 database based on a couple of factors.  I've seen a lot of people want to do the same thing online, and a lot of different proposed solutions, but most seem overcomplicated, and I can't get anything to work properly or do what I want.  I have data in a table where a particular thing (Room) will have a name, and a most recent status (AnalogValue; it correlates to online status) with a corresponding date/time (LogTimeStanp).  I'm most interested in the current online status for each room, so I just need the most recent row for each room.   There can be a number of rooms, for an example 1 - 5,000 (there's no imposed upper limit). There is a lot of data that pulls back when I look at the historic online status. There is other data in this table that I'm not interested in for this query. I have to join tables to get a readable room name, otherwise I just have a GUID. I'm not sure if I need to use MAX(), SELECT DISTINCT, Group by, or something else.  I have not been able to get these to work to my satisfaction.   This code does not work, but should give you an idea of what I want to do:  SELECT r.RoomName, a.AttributeID, a.AnalogValue, max(a.LogTimeStamp)   FROM CRV_AttributeLog a join CRV_Rooms r on a.RoomID=r.RoomID   where a.AttributeID like 'online_status'   My intent is to get the AnalogValue with the max (most recent) LogTimeStamp per individual room.  I have gotten a query to run with group by, but it doesn't seem to be grouping it (and certainly not by room name), so I guess I am doing it wrong.  Update 1, 2, 3, 4  Added pics for context.  Also, this query will form the basis of a report, so it has to be entirely referential, that's why I just want "the latest" online status.  Further complicating things, "the latest" may not have happened today, this week, or this month.  But it's in there, somewhere, sometime.  Each row in CRV_AttributeLog will have its own AttributeID and AnalogValue, and the row is made unique by the time stamp (LogTimeStamp).  Otherwise, it's entirely possible for AttributeID and AnalogValue to be matching across rows (and that's OK).  This table is logging information about when a few different somethings change: online status, if there is an error, etc.  ONLINE_STATUS can change from 0-2 (Offline, partially online, online) and will change between them periodically.  The other AttributeIDs' AnalogValues may also change back and forth depending on other conditions.      Desired Output  Room Name          AttributeID     AnalogValue     LogTimeStamp Conference Room    ONLINE_STATUS   0               2016-06-11 21:21:25:123 Gymnasium          ONLINE_STATUS   1               2016-07-21 20:23:45:456 Boardroom          ONLINE_STATUS   2               2017-02-17 05:15:37:951 Great Hall         ONLINE_STATUS   0               2016-10-23 07:28:54:753 ...