Friday, November 7, 2014

TSQL - Auto ID for Temporary table variable


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


Monday, November 3, 2014

SQL - Intelligent SQL auto-complete add-in

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!!