Today, I was trying optimize the query. For SQL Server temporary table variables, we cannot reseed for Identity ID. So I followed below approach: This process is used while you are trying to use the same temp table variable in while loop. Delete all the records from the table. Then while Inserting you can use Row_Number() to freshly start with new IDs from 1,2,3, etc. DECLARE @index int SET @index =0 DECLARE @TAB TABLE (ID INT ,VALUE VARCHAR(100)) INSERT INTO @Tab SELECT TOP 2 ROW_NUMBER() OVER (ORDER BY name) AS Row ,EmployeeName FROM dbo.Employee llb --ORDER BY llb.LoBName SELECT * FROM @Tab DELETE FROM @tab -- deleted two reords INSERT INTO @Tab SELECT TOP 4 ROW_NUMBER() OVER (ORDER BY name DESC ) AS ID ,EmployeeName FROM dbo.Employee llb --ORDER BY llb.LoBName desc SELECT * FROM @Tab -- now we got new 4 records starting from ID : 1,2,3,4
Friday, November 7, 2014
Monday, November 3, 2014
Hey Guys, For SQL Server 2008, sometimes we might not get auto-complete feature. In scripts, keywords to be in UPPER case. While formatting, it would be difficult. So we have a FREE tool from APEX, it is an add-in for both SSMS and VS-IDE. http://www.apexsql.com/sql_tools_complete_features.aspx For Intelligent SQL auto-complete add-in Code smarter, faster, and easier. Try it, its worth installing the tool. Enjoy guys!!