05 January 2009 12:45

Compatibility Level of a database

When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility Level 90 which is required to make use of the newly available functions

There are many methods to know the Compatibility Level of a database using queries

--Method 1
EXEC sp_dbcmptlevel your_database_name

--Method 2
EXEC sp_helpdb your_database_name

--Method 3
SELECT name,cmptlevel
FROM master..sysdatabases
WHERE name='your_database_name'

--Method 4
SELECT name,compatibility_level
FROM sys.databases
WHERE name='your_database_name'

Also read http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/12/common-migration-issues-version-2000-to-2005.aspx
Posted by Madhivanan with no comments
Filed under: , ,
31 December 2008 09:47

Happy New Year

set nocount on

select space(7-len(replicate(char(42),no)))+ replicate(char(42),no*2-1) from
(
    select top 5 row_number() over (order by name) as no from sysobjects
) as t
union all
select space(5)+replicate(char(124),3)
union all
select cast(0x4861707079204E65772059656172 as varchar(100))

Posted by Madhivanan with no comments
Filed under: , ,
24 December 2008 10:24

NULL on joined columns

 

You know that NULL values on joined columns are omitted from comparision when tables are joined

Consider this example

Declare @t1 table(col1 int, col2 varchar(10))

insert into @t1
select 1, 'test1' union all
select 2, 'test2' union all
select NULL,'test3' union all
select 5,' test4'

Declare @t2 table(col1 int, col2 varchar(10))

insert into @t2
select 1, 'testing1' union all
select 2, 'testing2' union all
select NULL, 'testing3' union all
select 3, 'testing4'

select t1.col1,t2.col2 from @t1 as t1 inner join @t2 as t2 on t1.col1=t2.col1

The result is

col1       col2
----------- ----------
1           testing1
2           testing2

What if you want to compare NULL values too and get the value testing3?

The query is

select t1.col1,t2.col2 from @t1 as t1 inner join @t2 as t2 on t1.col1=t2.col1 or (t1.col1 is null and t2.col1 is null)

and the result is

col1      col2
----------- ----------
1           testing1
2           testing2
NULL      testing3

Posted by Madhivanan with no comments
Filed under: , ,
11 December 2008 13:28

Script of DDL triggers

 

How do I view the script of the DDL triggers?

The answer is not

sp_helptext 'DDL trigger'

Because DDL triggers are defined at database/server level and not at any table level
So they are not stored in a system objects like sys.objects and the usage of sp_helptext,object_name, etc wont work

Using this

select * from sys.objects where type='tr'

You can see all triggers except DDL triggers

To know the DDL trigger, you need to use one of the following

select * from sys.triggers
where parent_class=0

select m.* from sys.sql_modules as m left join sys.objects as o
on m.object_id=o.object_id
where o.object_id is null

To script the DDL trigger, you need to use one of the following

select object_definition(object_id) from sys.triggers
where parent_class=0

select m.definition from sys.sql_modules as m left join sys.objects as o
on m.object_id=o.object_id
where o.object_id is null

Posted by Madhivanan with no comments
Filed under: , , ,
29 November 2008 09:28

Row_number() Function with no specific order

Row_number() function is used to generate row number to rows based on the column which is ordered

What if you want to generate row number without ordering any column

Here is the method (Consider Suppliers table from northwind database)

1 Assign dummy column with literal 0 and order it by that column

select row_number() over(order by order_col),companyname,address from
(
        select companyname,address,0 as order_col from northwind..Suppliers
) as t

2 Assign dummy column with literal 0 directly on the Order by clause

select row_number() over(order by (select 0)),companyname,address from northwind..Suppliers

Also refer Multipurpose Row_Number() Function

Posted by Madhivanan with no comments
Filed under: , ,
15 November 2008 09:13

Time Calculation on Numbers

In Forums sometimes I read questions on how to find out the difference between two times which are stored as characters

Here are some possible answers

Subtraction

declare @t1 varchar(5), @t2 varchar(5), @sum float

select @t1='12.56', @t2='7.58'

set @sum=cast(@t1 as float)-cast(@t2 as float)

--Method 1 : Convert to Datetime
select convert(varchar(5),dateadd(day,0,replace(@t1,'.',':'))- dateadd(day,0,replace(@t2,'.',':')),108)

--Method 2 : Number caculation1
select case when right(@sum,2)>=60 then @sum-0.4 else @sum end

--Method 3 : String caculation
select cast(left(@sum,charindex('.',@sum)-1)+case when right(@sum,charindex('.',reverse(@sum))-1)>60 then right(@sum,charindex('.',reverse(@sum))-1)-40 else right(@sum,charindex('.',reverse(@sum))-1) end/100.0 as decimal(4,2))

--Method 4 : Number calculation2
select cast(cast(@sum as int)+case when cast(@sum*100 as int)%100>=60 then cast(@sum*100 as int)%100-40 else cast(@sum*100 as int)%100 end/100.0 as decimal(4,2))

Addition

--Method 1: Date calculation
select convert(varchar(5),dateadd(day,0,replace(@t1,'.',':'))+ dateadd(day,0,replace(@t2,'.',':')),108)

--Method 2: Number calculation
select @sum=cast(@t1 as decimal(4,2))+@t2

select case when right(@t1,2)*1+right(@t2,2)>=60 then @sum+0.4 else @sum end

Posted by Madhivanan with no comments
Filed under: , ,
01 November 2008 09:08

SQL_VARIANT_PROPERTY function

If you want to know the datatype or length of a column, you can query on the system table syscolumns or
system view INFORMATION_SCHEMA.COLUMNS

But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function

Example

SELECT
    TOP 1
        SQL_VARIANT_PROPERTY('column_name','basetype')
FROM
    table_name

which gives you the datatype of the column_name

This is an example from Northwind..Orders table

SELECT 
    TOP 1
        SQL_VARIANT_PROPERTY('orderid','basetype') as basetype,
        SQL_VARIANT_PROPERTY('orderid','maxlength') as maxlength,
        SQL_VARIANT_PROPERTY('orderid','precision') as precision,
        SQL_VARIANT_PROPERTY('orderid','scale') as scale,
        SQL_VARIANT_PROPERTY('orderid','collation') as collation
FROM
        Northwind..Orders

Posted by Madhivanan with no comments
Filed under: , ,
10 October 2008 15:55

Export to EXCEL with column names

In the post Import/Export to Excel, I showed how to export data to EXCEL
The problem that most users find it is it wont include column names in the file and file should exists already with headings

This procedure would solve that problem

 

create procedure proc_generate_excel_with_columns
(
        @db_name varchar(100),
        @table_name varchar(100), 
        @file_name varchar(100)
)

as

--Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select 
        @columns=coalesce(@columns+',','')+column_name+' as '+column_name
from 
        information_schema.columns
where 
        table_name=@table_name

select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> '+@file_name+''''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

For more informations and to know how to solve the errors, refer this thread Export to Excel

Posted by Madhivanan with no comments
Filed under: , , ,
12 September 2008 15:39

Return TOP N rows

The TOP Clause returns top rows from the table based on the number or percentage value
What if you want to have TOP N rows for each group?
The following explains it

(
The purpose is to return top 3 orders for each customer based on the
most recent orderdate from the table Orders in Northwind database
)

1 Use IN

select
        o.*
from 
        northwind..orders as owhere orderdate in 
        (select top 3 orderdate from northwind..orders 
        where customerid=o.customerid order by orderdate desc
        )order by customerid, orderdate desc

2 Dynamically generate serial number for each customer

select 
        *
from 
        northwind..orders as o
where 
        (select count(*) from northwind..orders where customerid=o.customerid 
        and orderdate>=o.orderdate)<=3
        order by customerid,orderdate desc

3 Use Row_number() function

select * from
(
        select *, row_number() over(partition by customerid order by customerid,orderdate desc) as sno 
        from northwind..orders
) as t
where sno<=3

4 Use UDF and Cross Apply Operator

create function dbo.top_orders
(
@customerid nchar(10),
@limit int
)
returns table
as
return
(
        select top (@limit) orderdate from northwind..orders 
        where customerid=@customerid order by orderdate desc
)
GO

select distinct o.* from northwind..orders as o
cross apply dbo.top_orders(o.customerid,3) as t
where o.orderdate=t.orderdate
order by customerid,orderdate desc

Posted by Madhivanan with no comments
11 September 2008 14:06

Splitting delimited data to columns - Set based approach


In this post splitting-csv-to-columns I showed how to split delimited data into multiple columns

Simon in his post Set-based-splitting-of-delimited-strings-to-columns showed how to use that in a set based approach which would work on a set of data.In his approach he has limited it to maximum of four columns
I have modified his approach to work for any number of columns

CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns')

DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT
        @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM
        master..spt_values where type='p' and
        number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT
    @select='
        select p.*
        from (
        select 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, data, n as start, charindex('','',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        id, '','' + data +'','' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '','') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

DROP TABLE #test

Also Refer
csv to multiple columns
Dynamic pivot in sql server 2005

Posted by Madhivanan with no comments
Filed under: , , , ,
09 September 2008 14:19

Should alias names be preceded by AS? - Part 2

In the Part 1, I expressed my opinion on having the alias names preceded by the keyword AS

Adding to the examples given at the link, consider this example too

SELECT
        10number,
        10.number,
        10 number,
        10 [number],
        10[number],
        10 as number

It gives six columns with the result 10 and alias name number. Note that the first two expressions seem to be completely misleading

Am I right in insisting that SQL Server should force the usage of AS before the alias name?

Posted by Madhivanan with no comments
Filed under: , ,
05 September 2008 10:01

Fun with GO

In this blog post, I explained different uses of GO command

Here is a Fun when you use GO as object name

CREATE PROCEDURE GO
(
    @I INT
)
AS
    SELECT @I AS number

Well. The procedure is created and let us try to execute

GO 2

What we see is

Command(s) completed successfully

Becuase it becomes a syntax to execute a batch for two times

EXECUTE GO 2

Result is 2

Now see what the results are for the following

Execution 1
[GO] 2

Execution 2
[GO] 2
GO 2

Execution 3
[GO] 2
GO
EXECUTE GO 2
GO

Execution 4
[GO] 2
GO 2
GO 2

Execution 5
[GO] 2
GO
GO

Execution 6
EXECUTE GO 2
EXECUTE GO 2
GO 2

Execution 7
[GO] 2
GO
GO 2
GO 2
EXECUTE GO 2
GO
GO 2
GO 2
GO 2

Execution 8
GO;

Execution 9
GO;2

Execution 10
GO 2;

 
Posted by Madhivanan with 1 comment(s)
Filed under: , ,
02 September 2008 10:21

Empty string and Default values

Often newbies use empty string and consider it as NULL. But when an empty string is passed, it may not be stored as empty string or NULL. It actually depends on the datatype of the column

Consider this example

DECLARE @t TINYINT,@s SMALLINT,@i INT,@big BIGINT,@f FLOAT,@bit BIT, @sm SMALLMONEY,@m MONEY,@d DATETIME
 
SELECT
        @t='',

        @i='',
        @big='',

        @f='',
        @bit='',

        @sm='',
        @m='',
        @d=''

SELECT
        @t as tinyint_col,
        @i as int_col,

        @big as bigint_col,
        @f as float_col,

        @bit as bit_col,
        @sm as smallmoney_col,

        @m as money_col,
        @d as datetime_col

 

The result is Zero for all the variables except @d which has the date value of 1900-01-01 00:00:00.000 





So beware of this and always use NULL if you want NULL values stored in the column 
and dont use empty string
Posted by Madhivanan with no comments
Filed under: , ,
27 August 2008 13:31

Dynamic PIVOT in SQL Server 2005

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results
Consider this example

select * from
(
    select Year(OrderDate) as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e
    INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID)
) as t
pivot
(
    Count(OrderDate) for pivot_col in ([1996],[1997])
) as p

which shows total orders of each employees for years 1996 and 1997

What if we want to have this for all the years available in the table
You need to use dynamic sql


This procedure is used to generate Dynamic Pivot results
The approach is very similar to my Dynamic Crosstab with multiple PIVOT Columns blog post which can be used in SQL Server 2000

create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as

declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
 

create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
    select * from 
    (
        '+@select+'
    ) as t
    pivot 
    (
        '+@Summaries+' for pivot_col in ('+@pivot+')
    ) as p
'

exec(@sql)


 Purpose : Find total sales made by each employee for each year(from Employees and Orders table from Northwind databases)

Usage :

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'

Purpose : Find total sales made by each company for each product(from products, order details and suppliers table from Northwind database)

Usage :

EXEC dynamic_pivot
'SELECT s.companyname,coalesce(od.unitprice*od.quantity ,0) as total_cost FROM northwind..products as p
inner join northwind..[order details] as od on p.productid=od.productid
inner join northwind..suppliers as s on p.supplierid=s.supplierid',
'productname',
'sum(total_cost)'
Posted by Madhivanan with 2 comment(s)
Filed under: , , ,
12 August 2008 09:41

Common migration issues (version 2000 to 2005)

Many users complain that some new features are not working in SQL Server 2005 when the server is migrated from SQL Server 2000

These are the common tasks that should be done to avoid some migration issues

1 Change the compatibility level to 90
 
  EXEC sp_dbcmptlevel 'your_db_name',90

  This will solve the errors that occur during the execution of the code that contains newly introduced functions 
  like row_number(), rank(),etc

2 Enable Ad Hoc Distributed Queries

  EXEC SP_CONFIGURE 'show advanced options', 1
  RECONFIGURE
  go
  EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
&nb