Tag Archives: MS SQL Server 2005

Windowing Partition Functions (Rank(), Row_Number() and Dense_Rank())

Microsoft SQL Server 2005 comes up with fabulous Partitioning functions which work well in SQL Server 2008 too. Just because of ignorance or unawareness of these functions, programmer used to iterate BIG BIG loops in front-end. Without much a boring lecture, let me start over functions now.
Row_Number():  Basically Row_Number() function is used to give number to each row in result set. It plays an important and a crucial role in many difficult situations. One can use it for paging purpose too (I probably will post one SP in future article which you can directly use in your front-end for paging purpose).
–create temp table for demo
IF OBJECT_ID(‘tempdb..#Employee’) is not null DROP TABLE #EMPLOYEE
Create Table #Employee
(
      FirstName varchar(20),
      LastName Varchar(20),
      DepartMent varchar(20)
)

–insert few records
Insert Into #Employee
SELECT ‘Ritesh’,‘Shah’, ‘MIS’ UNION ALL
SELECT ‘Rajan’,‘Shah’,‘ACCT’ UNION ALL
SELECT ‘Rajan’,‘Mehta’,‘ACCT’ UNION ALL
SELECT ‘Alka’,‘Shah’,‘MIS’

–simple Row_Number with Order By First Name, Last Name
–this will not make any partition and simply give row number to every row
SELECT ROW_NUMBER() over(order by FirstName,LastName) as Num,FirstName,LastName,DepartMent
FROM #Employee

–this will make a partition on First Name
–so, every first instance of FirstName will have row number 1
–you can find duplicate records with this way too. :)
SELECT ROW_NUMBER() over(Partition by FirstName order by FirstName,LastName) as Num,FirstName,LastName,DepartMent
FROM #Employee
Rank() and Dense_Rank(): These two functions mainly used to use for giving Rank to each row.  You may use it for finding Toppers based on examination results records set or maybe use it for finding few top vendors based on track records of sales you have etc. There is only one small but technically big difference between Rank() and Dense_Rank() functions which I am going to show you in practical script below which will be easy to evaluate as I am going to show you all possible partitioning function in one T-SQL.
–create temp table for demo
IF OBJECT_ID(‘tempdb..#SampleOrder’) is not null DROP TABLE #SampleOrder
Create Table #SampleOrder
(
      OrderID Int Identity(1,1),
      ClientID int,
      TotalSample int,
      SampleDate datetime
)
–insert few records
Insert Into #SampleOrder
SELECT 1,2,GETDATE()-5 UNION ALL
SELECT 2,5,GETDATE()-8 UNION ALL
SELECT 1,22,GETDATE()-3 UNION ALL
SELECT 3,2,GETDATE()-1 UNION ALL
SELECT 1,2,GETDATE()-5
SELECT *,
            ROW_NUMBER() over(order by TotalSample) as RowNum,
            ROW_NUMBER() over(Partition By ClientID order by TotalSample) as RowNumP,
            Rank() over(order by TotalSample) as Ran,
            Rank() over(Partition By ClientID order by TotalSample) as RanP,
            Dense_Rank() over(order by TotalSample) as DRan,
            Dense_Rank() over(Partition By ClientID order by TotalSample) as DRanP
FROM #SampleOrder

If you will observe output of above T-SQL, you will get to know the difference between Rank and Dense_Rank. There is only difference, if you will get same instance based on partition, rank will give same number all, suppose we get three same instance (same clientID three time with same value) rank will give it, suppose 1 for all three and when next instance come, rank will give it 4 rather than 2. In Dense_Rank, you will get 2, it won’t break the chain.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
 http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Linked Server User in SQL Server 2008/2005 – MUST Read this

Microsoft has recently found memory leak issues in Linked Server query in SQL Server 2008 and SQL Server 2005. If you are using Linked server frequently than please do read this article to know what kind of problem could happens.
SQL Server 2008
  • ANY remote stored procedure execution will leak memory for each execution on the local server (the server where you initiated the remote procedure execution). The leak is not large for each execution (around 40 bytes) but over time this can add up. This specific problem involves the RETURN status of a stored procedure so there is no way to avoid it (even if you don’t use RETURN in your proc a return status is sent back to the client) if you execute remote stored procedures.
  • If you use sql_variant SQL Server data types you could also face a different memory leak (again on the local server only). These leaks can occur under the following conditions: 
    • If you use a sql_variant type for an OUTPUT parameter of a remote stored procedure you will leak around 40 bytes just for using a sql_variant OUTPUT parameter (the leak is per parameter). If the value of the parameter is of type char, varchar, nchar, nvarchar,binary, or varbinary, you will also leak the size of the value itself for each parameter. So a large character string over time could result in a considerable memory leak.
    • if you run a remote query that returns a result with a sql_variant column AND the value of the column is a char, varchar, nchar, nvarchar, binary, or varbinary value, you will leak the value of that variant for each row returned to the local server. This one has a potential to cause a fairly significant leak depending on how big the values are and how many rows are returned.
SQL Server 2005
  • You are only affected by the sql_variant problems listed above.
Please click here to read complete article on official Microsoft website.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Search latest modified SP and View with its text in SQL Server 2008/2005

Today I have seen interesting question in one forum that how can I find modified Stored Procedure and Views after certain date with its text. Question is really interesting but the answer is really very small T-SQL.
If you use Sys.Objects, you can get list of all available objects in your database. Sys.Object has much important information about that object along with “Modify_Date” but this system view doesn’t contain text of those objects.
Well, in this scenario Object_Defination method comes to rescue us. If you pass Object’s ID in Object_Defination method, it will return Text for that object. So now let me show you short T-SQL for this task.
select name,OBJECT_DEFINITION(object_id),modify_date from sys.objects
where [type] in (‘V’,‘p’)  and convert(varchar,modify_date,112)>‘20090601’



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find size of each table in database of SQL Server 2005/2008

Well, recently I have observed that data file of my database started growing like anything and I wonder which table(s) is causing this. Generally by looking at datafile you can’t assume that which table(s) is growing high so you need to check size of table and index. I decided to first look at the size of table, how can do that? Well there is one stored procedure in SQL Server which is really very handy in this scenario.
use adventureworks
go

–look at overall scenario about total size of table
–and index and database size etc.
EXEC sp_spaceused

–now let us look at the size of perticular table
EXEC sp_spaceused ‘Production.ProductProductPhoto’
Last T-SQL statement would give you total number of rows in table, reserved size, data size, index size and unused space. WOW, really quick and handy SP, isn’t it????
But this SP will give you information about just one table and what, if you want to look at the details about all tables in your database.
There are two ways to go for in this scenario.
1.)    Use following T-SQL which uses SP_MSForEachTable (undocumented SP), If you want to know more about this SP, click here to look at my past article.
exec sp_MSforeachtable @command1=‘print ”?” exec sp_spaceused ”?”’

2.)    Another way is really easy to go for, it’s a readymade graphical report provided by SQL Server itself. Just open up your SSMS, right click on database, Select Reports, click on Standard Reports and click on “Disk Usage by Top Tables”. There are so many other useful report too, which you can study and can use whenever you need it.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Encryption – Decryption in SQL Server 2008 – Part 1

Though Encryption and Decryption process is resource intensive, it is become necessary in some cases. If you look back in SQL Server 2000 and 7.0 days, you didn’t have any in-built mechanism and had to depend on 3rd party tools. But from SQL Server 2005, Microsoft started providing in-built support for encryption and decryption. Let us look in details about what is it? How does it work?

Since this is pretty big topic, it is not desirable to have it in one article so I will upload it in part. Before we move further in topic, let us find out Architecture (Hierarchy) of Encryption and Decryption mechanism in SQL Server 2008. Following image will give you an idea about that.

SQL Server 2008 encryption model inherits Windows Crypto API to encrypt and decrypt data in your database and supports layered approach. At the second level of encryption, there will be SMK (Service Master Key) . You can find one SMK (Service Master Key) per instance. It used to get generated by its own when it needs to encrypt any other key.  Well as I just told you that each server instance can have only one SMK but every database in your instance can have separate DMK (Database Master Key) which is encrypted by SMK.

At the bottom level of Encryption, you can find Certificates, Asymmetric key and Symmetric key. Detailed article with example about each of these are going to come soon. Keep Reading!!!!

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Dynamic alter table script to make all column NULL in sql server 2005/2008

I have read question in few different forums many time that “How to make all column null in one table?” 
Answer is very small and handy dynamic script which uses “Information_Schema.columns” view to get column name and generate Alter Table script. Have a look:
select ‘alter table ‘ + ic.TABLE_SCHEMA + ‘.’ + ic.TABLE_NAME + ‘ alter column ‘ + ic.COLUMN_NAME +   + ic.DATA_TYPE 
+ case when ic.DATA_TYPE=‘varchar’ then + ‘(‘ + cast(ic.CHARACTER_MAXIMUM_LENGTH AS varchar(3)) + ‘)’ else ‘ ‘ end + ‘ ‘ + ‘null’
 from INFORMATION_SCHEMA.COLUMNS as ic
 left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as icu on ic.TABLE_NAME=icu.TABLE_NAME and ic.COLUMN_NAME=icu.COLUMN_NAME
 where ic.TABLE_NAME=‘YourTableName’ and COLUMNPROPERTY(object_id(ic.table_name),ic.column_name,‘IsIdentity’)=0
 and icu.TABLE_NAME is null

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OUTPUT Clause in SQL Server 2008/2005

Today I was working on one Audit Trail project and thought to test OUTPUT clause. As soon as I wrote OUTPUT clause, it pops up in my mind that I have not written anything about OUTPUT clause in my blog so far. So, I felt, this is the time to write something about this.
You people might be aware with pseudo table (INSERTED and DELETED) in trigger, this is something similar concept, the difference is, you can get pseudo table inside trigger and you can use OUTPUT clause outside trigger too.
Let us see its usefulness by one small example.
–create table for demo and insert few records
create table testOutPut(tid int identity(1,1), name varchar(50))
insert into testOutPut (name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Pinal’ union all
select ‘Bihag’ union all
select ‘John’ union all
select ‘Bhaumik’ union all
select ‘Avi’ union all
select ‘James’
go
–check whether all records came with proper tid
select * from testOutPut
go
–create temp duplicate table which will store data which are deleted
create table #deleted (id int, name varchar(50))
–deleting records from testOutPut table and inserting those deleted
–records into temp table with help of OUTPUT clause and DELETED pseudo table
delete testOutPut
output DELETED.* into #deleted
where tid < 3
–checking temp table
select * from #deleted
–this will show deleted data on screen
–but won’t store anywhere like we did in previous snippet
–and stored data in #deleted (temp table)
delete testOutPut
output DELETED.*
where tid >3
select * from testOutPut
go
select * from #deleted
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Insert and Update image field in SQL Server 2008/2005

Few months back I have written one article which was showing how to store image and other files into SQL Server column by converting it to byte object and receive it back. That was with the help of C# but now this time I am going to show, how you can insert and update image or varbinary field from within SQL Server itself.
Before we move further, If you wish to look at my previous article with C# script, please have a look at:
Ok, now let us move ahead with our script in SQL Server itself.
–create table for demonstration
create table emps
(
      name varchar(50),
      dept varchar(10),
      empImg image
)
GO


–insert statement with single_blob to upload image to SQL Server
INSERT INTO emps ([Name],dept,empImg)
SELECT ‘Ritesh’,‘MIS’,
(select * FROM OPENROWSET(BULK ‘C:\Ritesh.JPG’, SINGLE_BLOB) AS img)
GO


–check the inserted data
select * from emps
GO

–update your table, along with image also.
update emps
set empImg=(select * FROM OPENROWSET(BULK ‘C:\Ritesh1.JPG’, SINGLE_BLOB) AS img), dept=‘IT’
where name=‘Ritesh’
GO

–check the data whether it has been updated
select * from emps
go

 
Happy SQLing!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Update UnTyped XML data column in SQL Server 2008/2005

Now a day, XML is booming, it is welcomed from every platform so why Microsoft and especially SQL Server sit back? After SQL Server 2005, Microsoft have provided so many functionality for make developer’s life easy who are using XML. Today, I would like you to show how you can update un-typed XML data from within well known UPDATE T-SQL statement.
–create table for testing purpose with XML column
–in SQL Server 2005 or in 2008
create table xmlTest
(
ColumnXML xml
)
GO
–insert data in XML column
insert into xmlTest
select

   
  A1000  
  Ritesh Shah  
  HP Notebook  
GO
–check the output
select * from xmlTest
GO
–update value (un-typed) in product tag
DECLARE @Val varchar(50) 
SELECT @Val = ‘Dell Inspiron’ 
 
UPDATE xmlTest 
SET ColumnXML.modify(‘replace value of (/Orders/Product/text())[1] with sql:variable(“@Val”)’) 
GO
–confirm changes
select * from xmlTest



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Return comma separated value with For XML Path in SQL Server 2008/2005

We many time needs to return value of one column as a comma separated value, we can use COALESCE or ISNULL for this task but it is a lengthy process. I also have written one article for same task with COALESCE function, have a look:
Today I am going to show you one of the easy methods to do same task. Let us create one dummy table and move ahead.
use Adventureworks
CREATE TABLE [dbo].[emps](
[Name] [varchar](50),
[Dept] [varchar](10),
[Company] [varchar](15)
) ON [PRIMARY]
GO
–insert records
INSERT INTO emps
SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL
SELECT ‘Rajan’,‘MIS’,‘mar’
Now this is a time to show you how to get all names with separated by comma.
select left(t.name,len(t.name)-1) as ‘allName’ from
(
select name + ‘,’  from emps for xml path()
) as t(name)

Isn’t is easy to use method rather than ISNULL and COALESCE? Yes, it is!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah