RowID or RowNumber for each rows in SQLServer!

Yesterday when I was interviewing candidates for one of our requirement, I asked a simple select query to write that “how to get, only the last two rows of an Employee table?”

The answer were ( only from few 😦 ) : SELECT TOP 2 * FROM Employee ORDER BY EmployeeId DESC (Provided EmployeeId INT, PrimaryKey)

Then, just i altered the question with “we don’t have an “INT” datatype for EmployeeId column, but UniqueIdentifier (GUID) as well, so now how would you get the last 2 rows?

Some of them were tried to use ROW_NUMBER(), but as that requires OVER(ORDER BY <Column> DESC), and the primary key is with GUID, so they got blocked!

But here is one solution I had, and with that I was asking them: 🙂

SELECT TOP 2 *, %%physloc%% AS PhysLoc FROM Employee ORDER BY PhysLoc DESC

Ref:

http://www.codeproject.com/Articles/159785/Physical-location-of-a-row-in-SQL-Server

 

DENALI (SQLServer 2012):

The special thing is that, we have got the cool and long waiting stuff  “SEQUENCE” in DENALI as it is in ORACLE. That helps great for the above question, with the elegant solution.

Here you go…

CREATE SEQUENCE RowId
    AS int
    START WITH 1
    INCREMENT BY 1 ;

SELECT TOP 2 *, NEXT VALUE FOR RowId AS NewId FROM Employee ORDER BY NewId DESC

Ref:

http://www.sqlservercentral.com/articles/sequence/88422/

http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx

 

Hope this helps!

Run / Executing SQL files as a batch using SQLCMD and XP_CMDSHELL

I had to run a set of SQL files into one of my DB. As i haven’t had a tool (like multiscript), i was checking the easy way of reading the files, reading from my local folder and executing them all in one go.

Here you go…. 🙂

SET NOCOUNT ON

SET XACT_ABORT ON
BEGIN TRAN

DECLARE @DBServerName   VARCHAR(100) = ‘(local)’   — SERVER NAME
DECLARE @DBName VARCHAR(100) = ‘MyDB’  — DB Name
DECLARE @FolderPath   VARCHAR(200) = ‘D:\MyFloder\DBScripts\’  — FOLDER PATH
DECLARE @DirCommand VARCHAR(100) = ‘dir ‘ + @FolderPath + ‘*.sql /b’

DECLARE @FileList TABLE (Files NVARCHAR(MAX))
INSERT INTO @FileList EXEC xp_cmdshell @DirCommand
–SELECT * FROM @FileList

PRINT ‘Execution starts…!’
WHILE (SELECT COUNT(Files) FROM @FileList) > 0
BEGIN
DECLARE @FileName NVARCHAR(MAX) = (SELECT TOP(1) Files FROM @FileList)
DECLARE @command  VARCHAR(500)  = ‘sqlcmd -S ‘ + @DBServerName + ‘ -d  ‘ + @DBName + ‘ -i “‘ + @FolderPath + @Filename +'”‘
–PRINT @command
EXEC xp_cmdshell  @command

PRINT ‘Executed: ‘ + @FileName
DELETE FROM @FileList WHERE Files = @FileName
END
PRINT ‘Execution ends…!’
COMMIT TRAN

Courtesy : Stack overflow.  (Above is the bit tweaked version)

NULL check results differently in LINQ To SQL (L2S) and LINQ To EF (L2E)

Here are some interesting gotchas i would like to share that are all confirmed with @jimwooley and @julielerman

As i learnt that L2S & L2E uses different parser to generate the TSQL, thought of checking them mainly with “null” in SELECT & WHERE clause respectively. Surprisingly, i got great differences and listed them here.

LINQ To SQL

I have used Table “Orders” from DB NorthWind

Example: var result = from ord in Orders select ord.ShipRegion;

In SELECT statement

LINQ	SQL Equivalent
select ord.ShipRegion ?? “Lara”	SELECT COALESCE([t0].[ShipRegion],@p0) AS [value]
select ord.ShipName == null ? “Lara” : ord.ShipName	SELECT (CASEWHEN [t0].[ShipName] IS NULL THEN CONVERT(NVarChar(40),@p0) ELSE [t0].[ShipName] END) AS [value]
select Object.Equals(ord.ShipRegion, “Lara”)	SELECT (CASEWHEN ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0) THEN 1 WHEN NOT (([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)) THEN 0 ELSE NULL END) AS [value]
select Object.Equals(ord.ShipRegion, null)	SELECT (CASEWHEN [t0].[ShipRegion] IS NULL THEN 1 ELSE 0 END) AS [value]

In WHERE condition

LINQ	SQL Equivalent
where Object.Equals(ord.ShipRegion, null)		WHERE [t0].[ShipRegion] IS NULL
where Object.Equals(ord.ShipRegion, “Lara”)		WHERE ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)
where String.Equals(or.ShipRegion, “Lara”)		WHERE ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)
where ord.ShipRegion == (ord.ShipRegion ?? “Lara”)		WHERE [t0].[ShipRegion] = (COALESCE([t0].[ShipRegion],@p0))
where ord.ShipRegion == (ord.ShipRegion == null ? “Lara” : ord.ShipRegion)		WHERE [t0].[ShipRegion] = ((CASE WHEN [t0].[ShipRegion] IS NULL THEN CONVERT(NVarChar(15),@p0) ELSE [t0].[ShipRegion] END))

Note: DECLARE @p0 NVarChar(1000) = ‘Lara’ Gotchas:

• ?? => COALESCE()
• ? => CASE WHEN….END

LINQ To Entity Framework

Used a custom table: Vendor

In SELECT statement

· select (re.VendorName ?? null); · select (re.VendorName == null ?  "N/A" : re.VendorName);	SELECT [Extent1].[VendorName] AS [VendorName]
select (re.VendorName.Length > 0 ?  re.VendorName : "N/A");	SELECT CASE WHEN ((LEN([Extent1].[VendorName])) > 0) THEN [Extent1].[VendorName] ELSE N’N/A’ END AS [C1]

In WHERE condition

DateTime? dt = null;  where DateTime.Equals(res.UpdatedDateTime, dt)	If dt is “null” WHERE [Extent1].[UpdatedDateTime] IS NULL If dt is “not null”, the following exception for all primitive types: Unable to cast the type ‘System.Nullable`1’ to type ‘System.Object’. LINQ to Entities only supports casting Entity Data Model primitive types.
String defaultVal = null; ……. where Object.Equals(re.VendorName, defaultVal)	WHERE [Extent1].[VendorName] = @p__linq__0
· from re in obj.Vendors where Object.Equals(re.VendorName, null)       select re.VendorId; · from re in obj.Vendors where (re.VendorName.Equals(null))        select re. VendorId;	If we keep “null” directly, then the following weird TSQL for both the LINQ: SELECT CAST(NULL AS int) AS [C1] FROM  ( SELECT 1 AS X ) AS [SingleRowTable1] WHERE 1 = 0
where re.VendorName.Equals(null) || re.VendorId > 0	Consider only VendorId and ignore the other 😦 WHERE [Extent1].[VendorId] > 0
where (!re.VendorName.Equals(null))	Ignores WHERE keyword in TSQL generation 😦
· where (re.UpdatedDateTime ==  (re.UpdatedDateTime ?? dt)) · where (re.UpdatedDateTime ==  (re.UpdatedDateTime == null ?  dt : re.UpdatedDateTime))	WHERE [Extent1].[UpdatedDateTime] = (CASE WHEN ([Extent1].[UpdatedDateTime] IS NULL) THEN @p__linq__0 ELSE [Extent1].[UpdatedDateTime] END)

Gotchas:

• For both ?? and ? => CASE WHEN….END in “where” clause only, but ignored in “select” clause
• Weird TSQL equivalent with .Equals(null)

If anybody has any special thoughts, please comment 🙂

VS.NET Entity Visualizer to display SQL source code

Entity Framework Debugger Visualizer to display SQL source code generated by Entity Framework.

Courtesy : Codeplex.com

1. Download the DLL from http://entityvisualizer.codeplex.com/releases/view/58464
2. Place the DLL to either of the following locations:
   1. InstallPath\Microsoft Visual Studio 10.0\Common7\Packages\Debugger\Visualizers
   2. My Documents\Visual Studio 2010\Visualizers
3. In Win 7/Vista systems, Unblock the dll EntityFrameworkVisualizer.dll, if it is locked
   1. Right click -> Properties -> Unblock
4. Restart the Visual Studio debugging session and place the break-point in one of the LINQ statement
   
5. Pass the LINQ statement. And at mouse over, you would see the magnifying glass icon (lens)
6. Click the icon, you would see as below 🙂

Hope, you would love this 🙂

SQL Enlight – Analyze and validate T-SQL

SQL Enlight is a tool for SQL Server that can automate and facilitate the T-SQL code and database schema analysis. The tool works in a way similar to the .NET focused tools like FxCop and StyleCop. It provides design-time code, database schema and query plan analysis

Just check out the below Url

http://www.sqlservercurry.com/2010/10/analyze-and-validate-t-sql-code-using.html