Archive for the ‘SQLServer’ Category

NHibernate 3.0+ rounding a decimal data to 5 decimal places

March 14, 2014

Note: Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

Problem: One of our team member was trying to to insert data into a SQL Server database from an application that is using NHibernate ver. 3.1. The format for a decimal datatype value is being forced to decimal(16,5) always, regardless of the SCALE defined for the column (ex: DECIMAL_VALUE1 decimal(16,6) in the SQL Server table. This results in decmial values being truncated before being written to the SQL Server database.

Here is the sample sql profiler trace:

exec sp_executesql N’INSERT INTO TEST_TABLE( DECIMAL_VALUE1, DECIMAL_VALUE2, LONG_VALUE1) VALUES (@p0, @p1, @p2); select SCOPE_IDENTITY()’ , N’@p0 decimal(16,5),@p1 decimal(16,5),@p2 bigint’, @p0 = 9.94434, @p1 = 9.94434, @p2 = 5583

Hence, the gotcha is that NHibernate by default sets the SCALE to “5”, if we haven’t told any SCALE in entity mapping class for the particular property.

The reason behind: [NH-1594] – When setting property in hbm type=”Decimal(precision, scale)” – “DECIMAL(19,5)” is always generated Ref: https://github.com/leemhenson/nhibernate/blob/master/nhibernate/releasenotes.txt

Solution: Enforcing scale to “6” in the fluent mapping or .hbm like below, it works great.

Map(x => x.DECIMAL_VALUE1).Scale(6);

<property name=”DECIMAL_VALUE1″ scale=”6″ />

Happy coding!

RowID or RowNumber for each rows in SQLServer!

April 15, 2012

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!

SQL Server CTE ( Common Table Expressions )

September 5, 2011

Here are 2 fantastic links….awesome! Hope, you would love reading it! ūüôā

http://msdn.microsoft.com/en-us/magazine/cc163346.aspx By John Papa

http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/ by Robert Sheldon

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

September 4, 2011

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)

February 14, 2011

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

January 8, 2011

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 statementLINQ 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 ūüôā

SQLView

Hope, you would love this ūüôā

SQL Enlight – Analyze and validate T-SQL

December 4, 2010

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