Note on precision & scale in SQL Server

Generally for storing a variable like 123.45 we can use a decimal or numeric data type. The usual notation for these data types are decimal [ (p[ ,s] )] & numeric[ (p[ ,s] )], p stands for precision & s stands for scale.

Precision is number digits in variable

Scale is of digits to the right of the decimal point

For ex: 12.345 p = 5 & s = 3

The sample query explains the behavior of the decimal, float & int

declare @t table(

     c1 int,

     c2 float,

     c3 decimal(18,2)

)

insert into @t (c1,c2,c3) select 12.345, 12.345, 12.345

insert into @t (c1,c2,c3) select 10/3, 10/3, 10/3

insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, 10/3.0

insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, round(10/3.0, 2)

select * from @t

c1        c2                    c3

12        12.345                12.35

Note: The value in C3 is truncated to two decimal places, C1 completely ignored the fraction part.

3           3            3.00

Note: The fraction part is completely ignored because the denominator is an integer

3           3.333333           3.33

Note: The fraction part is retained correctly

3           3.333333           3.33

Note: The fraction part can also be achieved by rounding the value using round function

TSQL Rambling

Wanted to share my thoughts on three interesting things stumbled across in TSQL

1. SQL Unary Operator

Lets create a simple table as follows

declare @TestPlus table

(

Type varchar(2),

TypeName varchar(10),

code int

)

insert into @TestPlus values ('P', 'Procedure', 1)

insert into @TestPlus values ('T', 'Table', 2)

insert into @TestPlus values ('V', 'View', 3)

insert into @TestPlus values ('F', 'Function', 4)

What is the output for the following select query?

select Type from @TestPlus where Code in ( +1, +++3, +4)

select +++Type from @TestPlus where Type in (+'P', +++'T')

When I saw this select query for a second I thought its trick question on how to use ++ operator, however it turns out that the result has nothing to do with the “+++” operator. Quickly browsed through MSDN and found that it has no effect on the evaluated expression

“Although a unary plus can appear before any numeric expression, it performs no operation on the value returned from the expression.”

http://technet.microsoft.com/en-us/library/ms174362.aspx

SQL Row Constructor

I was surprised to see a query like this

;with cte as

(

select dddid, payorid

from (values  ('100100', 1), ('100101', 2), ('100102',3 ) ) f(ClientID, SeqNo)

)

select * from cte where SeqNo = 3

turns out that this type of query is called row constructor a new feature introduced in SQL 2008.

3.  SQL Strict Names

Check out the following queries, spot the odd man out

select top 10  * from [dbo].[TestUpSurdTable]

select top 10  * from [dbo].[TestUpSurdTable]_Final

I was expecting two different result sets however the same result set was repeating twice, turns out that the “[ ]” ignored what ever is after the underscore

TSQL Errors

Error 1: Cannot define PRIMARY KEY constraint on nullable column in table 

Cause: set ansi null default on, when you create a new col

Solution: explicitly state not null 


Error 2: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object 

Cause: Duplicate value for the desired columns

Solution: Run the following query to check the dup key, assuming unique index is on col1

select Col1 , count(*)
from Customers 
group by col1
having count(*) > 1

How to use table name as a variable in stored procedure

Sometimes it would be nice to pass the table name as a variable in a select query, its possible by using dynamic query as shown below

create procedure
declare @tablename varchar(30)


create procedure dbo.Test
@tablename varchar(30),
@val int
as

DECLARE @cmd AS NVARCHAR(max)

SET @cmd = N'SELECT * FROM ' + @table_name +
' WHERE Column1 = ''' + @val +  ''''


EXEC sp_executesql @cmd


exec Test

How to series TSQL 2

6. How to find second highest

SELECT COL 
FROM TABLE 
ORDER BY COL DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;


7. How to use multiple cte

;with cte1
as
(select col1, null as col2 from Tab),

cte2
as 
(
select col2, null as col1 from Tab
)

select * from cte1
union
select * from cte2

8 How to rename column
EXEC sp_rename 'TABLENAME.OLD_COLUMNNAME', 'NEW_COLUMNAME', 'COLUMN';
.

How to series TSQL 1

1. How to change col datatype

-- TransDate was in datetime change to date

alter table Transactions 
alter column TransDate date


2.  How to create primary key on a col

alter table TransactionsArchive 
add constraint  PK_TransactionsArchive_KeyID primary key clustered (key_ID);
GO

3.  How to re-sequence an identity column


alter table List_NotesType
drop constraint PK_List_CommentsType

alter table List_NotesType
drop column NoteType

alter table List_NotesType
add NoteType tinyint identity(1,1)

alter table List_NotesType
add constraint PK_List_NoteTypeID primary key clustered (NoteType);

4. How to change a not null col to null

alter table [Notes] 
alter column Comments varchar(500) NULL

5. How to update existing NULL cols with a value

update Notes 
set Comments = t.Description
from Notes n join List_NotesType t on n.Type = t.NoteType
where Noteid in (select Noteid from Notes where Comments is null)

How to use cross apply instead of cursors in SQL Server

I recently ran into a stored procedure with cursor logic, I wanted to see what would be the performance gain if cursor logic is rewritten with set theory operations.

Here is a simplified description of the stored procedure. There is a table with five columns, one column is an identity and a primary, (let's call it the ID), three columns (x, y, z) are of type integers and the fifth column (a) is a computed column. The computation is quite complex so it cannot be declared as a computed column expression. For each row, the values of the three columns (x, y, z) are passed in as a parameters to a custom function (where the logic is encapsulated) which spits out a calculated value. Finally, for every row the column "a" is updated with the calculated value from the custom function. There are around 7000 rows in this table. 

I put this blogpost on code project as well. Check out the following link
http://www.codeproject.com/Tips/654894/How-to-use-cross-apply-instead-of-cursors-in-SQL-S

Let's create a table called test1 with five columns. For this example lets stick with a simple logic, the fifth column is a sum of cols x,y,z.   

Step 1: Create the test table  
create table test1 
(
    id int not null identity(1,1), 
    x int,
    y int,
    z int,
    a int null
)

Step 2: Insert dummy data    
insert into test1 values (5,5,5, NULL)
go 10
-- (I inserted 9132 rows, took five minutes for the code to execute) 

Step 3: Using cursors to update the column "a"  

declare @x1 int
declare @x2 int
declare @x3 int
declare @x4 int
declare @x5 int
declare c1 cursor local for  
select id, x, y, z, a from test1
open c1
    while (0=0)
        begin
        fetch next from c1 into 
        @x1, @x2,@x3,@x4,@x5
        
        if (@@FETCH_STATUS = -1)
            break
        
        -- your logic
        set @x5 = @x4 + @x2 + @x3
        
    
        update test1
        set test1.a = @x5
        from test1
        where id = @x1
        end
close c1
deallocate c1

-- Exexcution time 01:09

Step 4: Reset column "a" 

update test1 set a = null 

Step 5: Create a table valued function (tvf) shown below

create function dbo.fnsomelogic (@x int ,@y int, @z int)
returns @val table
(
    q int
)
as 
begin 
    declare @q int 
    set @q = @x + @y +@z    
    insert into @val (q) values (@q)
    return
end

-- tvf can be invoked as shown below
-- select * from dbo.fnsomelogic(1,2,3)

Step 6: use cross apply and the tvf  to update column a

update test1
set test1.a = c.q
from test1 b cross apply dbo.fnsomelogic(b.x,b.y,b.z) c
--Execution Time: (9132 row(s) affected) in less than a second.   

If you observe the messages, the cursor which is a row based operator, displays (1 row(s) affected) for every row it updated, unlike the cross apply which displays (9132 row(s) affected). Although the problem is screaming out use cursors, however with little observation a cross apply along with a table valued function can boost the performance significantly. Relational/Set theory concepts are deeply embedded within SQL Server.    

Stored Procs 101

Stored procedures are programmability features of a database engine.

Advantages of stored procs (sp)

1. Reduce server/client network traffic
2. Stronger security 
3. Modularization & Code re-usability
4. Easier maintainance
5. Improved performance 

-- 1.  basic sp

create procedure TestProc1
as 
begin --optional
select top 5 * from sales.customers 
end -- optional 

-- 2. execute a sp

exec TestProc1 -- or execute TestProc1

-- 3. alter a sp 

alter procedure TestProc1
as
begin 
select top 10 * from sales.customers
end

-- 4. drop a sp

drop procedure TestProc1

-- 5. Rename/ "sp_" are system defined stored procs

exec sp_rename 'TestProc1', 'TestProc2'

-- 6. View the definition of sp

-- approach 1

exec sp_helptext 'TestProc1'

-- approach 2

select object_definition (object_id('TestProc'))

-- approach 3

select definition 
from sys.sql_modules
where object_id = (object_id('TestProc'))

-- 7 sp with one parameter

create procedure TestProc
( -- optional 
@custid int 
) -- optional
as
select * from sales.customers where custid = @custid

-- execute sp with parameters 

exec testproc 3

--or 

exec testProc @custid = 3

-- 8  sp with more than one parameter
create procedure TestProc
@custid int,
@contacttitle varchar(30) 
as
select * from sales.customers where custid = @custid

and contacttitle = @contacttitle

-- 9 proc which returns data, use output keyword

create procedure TestProc
@custid int,
@contacttitle varchar(30),
@companyname varchar(40) output
as
select * from sales.customers where custid = @custid

and contacttitle = @contacttitle

declare @c varchar(30)

exec testproc 2, 'Owner', @companyname = @c output

select @c -- print @c

If you don't use output keyword then if the sp will return a table if called from .net code. 

If we use the output keyword then a single value is returned which can be captured as a .net data type

Error codes of sp with output keyword

0 : Successful execution
1 : Required param is not specified
2 : Specified param value is not valid
3 : Error has occurred in getting the value
4 : Null value found for variable 


-- 10 recompiling sp
There are three ways to recompile a sp

exec testproc with recompile

exec sp_recompile 'testproc'

SQL SERVER Collation Conflict

Recently, DBA attached a copy of  our a SQL 2000 mdf file to our new SQL 2012 server.   I had a join query on two different databases within the same server, I ran into this error. 

Cannot resolve the collation conflict between "SQL_Latin1_General_CP437_CI_AS" 
and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Turns out to be SQL 2000 database and SQL 2012 had two different collations and the cross database queries should be written as shown below

select <cols>
from <table1> t1 join <table2> t2 on 
t1.col1 = t2.c1 COLLATE Database_Default
where t1.c1 = 'Blah'

The sys.databases would have the collation name of the database 

Insert into a table from stored proc

I never realized until today that the results from a stored proc can be inserted into a table. For example, I am selecting employeeID and ManagerID from Adventureworks

select EmployeeID, ManagerID
from HumanResources.Employee

Then create a stored procedure

create procedure dbo.EmpTest
as
select EmployeeID, ManagerID
from HumanResources.Employee

The results of the stored proc exec EmpTest can be used to insert into a table as follows.

declare @test table
(
    EmployeeID int,
    ManagerID int
)

insert into @test
exec EmpTest

select * from @test

Get rows between any two arbitrary numbers

A week ago, I was asked to write a query to return rows between 30 and 40 of a particular ID column of a particular table and the result set should be sorted. There are 4 different ways of achieving this:
For this example, I took the products table from Northwind database.

Method 1: Using the between keyword

select productid, productname from Products where ProductID between 30 and 40

Method 2: Using the except keyword (set theory)

select productid, productname from Products where ProductID < 41 except
select productid, productname from Products where ProductID < 30 order

Method 3: Using ranking function and a subquery

select temp1.rownum, temp1.ProductName from (select ROW_NUMBER()
over(order by productId desc) as rownum, ProductName
      from Products) as temp1 where temp1.rownum between 30 and 40

Method 4: Using CTE and ranking function

with temp as
(
      select ROW_NUMBER() over(order by productId desc) as rownum, ProductName
      from Products
)
select * from temp where rownum between 30 and 40

To do: 
Analysis the performance on a large dataset and compare efficiencies

Create Database Adventurework2012

1. Get the Adventureworks 2012 OLTP mdf file from
http://msftdbprodsamples.codeplex.com/downloads/get/165399

2. Copy the mdf file to
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA'

3.  Run the following script to create the database

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG

Since there is no log file, the database engine will create one for us and the engine will display the following message.

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect.


New log file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf' was created.
Converting database 'AdventureWorks2012' from version 705 to the current version 706.
Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706.

Refresh the Databases folder to see Adventureworks2012