About Author

Ritesh Shah is an author of many technical article on Microsoft Technology, especially on SQL-Server, .NET technology like C# and ASP.Net etc. He is having 10+ year of experience in IT industry and working as Consultant Project Leader and Database Administrator.

Well, We have just seen Paging Stored Procedure with the help of Windowing Function Row_Number () in my previous article. This is now time to look at one of the very handy yet useful function NTILE. This is really very useful function if you want to groups up your result sets in Microsoft SQL Server 2005/2008. If you want to get top 10% students based on the percentage they get, this becomes really very easy with NTILE function.

Actually I have used this function so many times but when I have used this function recently in one of my recent project, It pops up in my mind why not write something about this function in blog?

Actually I had result sets which I wanted to show in three bunch, this could be done via Row_number function as I wrote Paging stored procedure recently but since I have fixed requirement that no matters how many rows in total result sets, I wanted to display it in three bunch, I felt NTILE function most prominent candidate for this. Let us look at one small demonstration about this.

Well, We have just seen Paging Stored Procedure with the help of Windowing Function Row_Number () in my previous article. This is now time to look at one of the very handy yet useful function NTILE. This is really very useful function if you want to groups up your result sets in Microsoft SQL Server 2005/2008. If you want to get top 10% students based on the percentage they get, this becomes really very easy with NTILE function.

Actually I have used this function so many times but when I have used this function recently in one of my recent project, It pops up in my mind why not write something about this function in blog?

Actually I had result sets which I wanted to show in three bunch, this could be done via Row_number function as I wrote Paging stored procedure recently but since I have fixed requirement that no matters how many rows in total result sets, I wanted to display it in three bunch, I felt NTILE function most prominent candidate for this. Let us look at one small demonstration about this.

Paging is something every website developer need. To make paging happens, you need to work a lot if you create your logic in any front end but it became very easy if you will use windows partition functions of SQL Server. My previous article was completely dedicated to this topic and in this article, I am going to show you how you can create paging with the help of SQL Server’s Row_Number() function, if you don’t have identity field.

Generally people used to get full record sets in dataset (.NET) and used to show only those records which needs to display, I don’t encourage this practice, fetch only those records from database which needs to display, by this way, you can reduce network traffic and can get good performance.

Anyway, let me show you the magical, handy stored procedure.

CreateTable UsersTable

(

UserID Varchar(25),

FirstName Varchar(25),

LastName Varchar(25),

JoiningDate datetime

)

GO

Insertinto UsersTable

select‘Ritesh’,‘Ritesh’,‘Shah’,GETDATE()-50 unionall

select‘Rajan’,‘Rajan’,‘Shah’,GETDATE()-4 unionall

select‘Bihag’,‘Bihag’,‘Thaker’,GETDATE()-10 unionall

select‘Alka’,‘Alka’,‘Shah’,GETDATE()-30 unionall

select‘Rushik’,‘Rushik’,‘Shah’,GETDATE()-25 unionall

select‘Krunal’,‘Krunal’,‘Shah’,GETDATE()-20 unionall

select‘Bhaumik’,‘Bhaumik’,‘Shah’,GETDATE()-10

GO

select*from UsersTable

go

–creating stored procedure for searching user with paging.

CREATEPROC SearchUser

@PageNum INT,–pass page number you want to see

@UserPerPage INT,–pass number of users, you want to see on one page.

@Date Datetime–you can pass as many varialbe as you want to put in WHERE clause.

Paging is something every website developer need. To make paging happens, you need to work a lot if you create your logic in any front end but it became very easy if you will use windows partition functions of SQL Server. My previous article was completely dedicated to this topic and in this article, I am going to show you how you can create paging with the help of SQL Server’s Row_Number() function, if you don’t have identity field.

Generally people used to get full record sets in dataset (.NET) and used to show only those records which needs to display, I don’t encourage this practice, fetch only those records from database which needs to display, by this way, you can reduce network traffic and can get good performance.

Anyway, let me show you the magical, handy stored procedure.

CreateTable UsersTable

(

UserID Varchar(25),

FirstName Varchar(25),

LastName Varchar(25),

JoiningDate datetime

)

GO

Insertinto UsersTable

select‘Ritesh’,‘Ritesh’,‘Shah’,GETDATE()-50 unionall

select‘Rajan’,‘Rajan’,‘Shah’,GETDATE()-4 unionall

select‘Bihag’,‘Bihag’,‘Thaker’,GETDATE()-10 unionall

select‘Alka’,‘Alka’,‘Shah’,GETDATE()-30 unionall

select‘Rushik’,‘Rushik’,‘Shah’,GETDATE()-25 unionall

select‘Krunal’,‘Krunal’,‘Shah’,GETDATE()-20 unionall

select‘Bhaumik’,‘Bhaumik’,‘Shah’,GETDATE()-10

GO

select*from UsersTable

go

–creating stored procedure for searching user with paging.

CREATEPROC SearchUser

@PageNum INT,–pass page number you want to see

@UserPerPage INT,–pass number of users, you want to see on one page.

@Date Datetime–you can pass as many varialbe as you want to put in WHERE clause.

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

IFOBJECT_ID(‘tempdb..#Employee’)isnotnullDROPTABLE #EMPLOYEE

CreateTable #Employee

(

FirstName varchar(20),

LastName Varchar(20),

DepartMent varchar(20)

)

–insert few records

InsertInto #Employee

SELECT‘Ritesh’,‘Shah’,‘MIS’UNIONALL

SELECT‘Rajan’,‘Shah’,‘ACCT’UNIONALL

SELECT‘Rajan’,‘Mehta’,‘ACCT’UNIONALL

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

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

IFOBJECT_ID(‘tempdb..#SampleOrder’)isnotnullDROPTABLE #SampleOrder

CreateTable #SampleOrder

(

OrderID IntIdentity(1,1),

ClientID int,

TotalSample int,

SampleDate datetime

)

–insert few records

InsertInto #SampleOrder

SELECT 1,2,GETDATE()-5 UNIONALL

SELECT 2,5,GETDATE()-8 UNIONALL

SELECT 1,22,GETDATE()-3 UNIONALL

SELECT 3,2,GETDATE()-1 UNIONALL

SELECT 1,2,GETDATE()-5

SELECT*,

ROW_NUMBER()over(orderby TotalSample)as RowNum,

ROW_NUMBER()over(PartitionBy ClientID orderby TotalSample)as RowNumP,

Rank()over(orderby TotalSample)as Ran,

Rank()over(PartitionBy ClientID orderby TotalSample)as RanP,

Dense_Rank()over(orderby TotalSample)as DRan,

Dense_Rank()over(PartitionBy ClientID orderby 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.

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

IFOBJECT_ID(‘tempdb..#Employee’)isnotnullDROPTABLE #EMPLOYEE

CreateTable #Employee

(

FirstName varchar(20),

LastName Varchar(20),

DepartMent varchar(20)

)

–insert few records

InsertInto #Employee

SELECT‘Ritesh’,‘Shah’,‘MIS’UNIONALL

SELECT‘Rajan’,‘Shah’,‘ACCT’UNIONALL

SELECT‘Rajan’,‘Mehta’,‘ACCT’UNIONALL

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

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

IFOBJECT_ID(‘tempdb..#SampleOrder’)isnotnullDROPTABLE #SampleOrder

CreateTable #SampleOrder

(

OrderID IntIdentity(1,1),

ClientID int,

TotalSample int,

SampleDate datetime

)

–insert few records

InsertInto #SampleOrder

SELECT 1,2,GETDATE()-5 UNIONALL

SELECT 2,5,GETDATE()-8 UNIONALL

SELECT 1,22,GETDATE()-3 UNIONALL

SELECT 3,2,GETDATE()-1 UNIONALL

SELECT 1,2,GETDATE()-5

SELECT*,

ROW_NUMBER()over(orderby TotalSample)as RowNum,

ROW_NUMBER()over(PartitionBy ClientID orderby TotalSample)as RowNumP,

Rank()over(orderby TotalSample)as Ran,

Rank()over(PartitionBy ClientID orderby TotalSample)as RanP,

Dense_Rank()over(orderby TotalSample)as DRan,

Dense_Rank()over(PartitionBy ClientID orderby 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.

Installing MySQL Server in CentOS 7 As a part of some latest R&D, I wanted to replicate some data from SQL Server to MySQL. Since there is no native way to do this, I am preparing my own framework which suits my requirement. To work on the project, I have purchased, recently, cloud space with […] The post Installing MySQL Server in CentOS 7 appeared firs […]

I have started working with Microsoft SQL Server 7.0 in late 1999 and even today I become very excited with the new version of Microsoft SQL Server. SQL Server 2016 has been in market for quite some time but now finally Microsoft has released service pack 1 for SQL Server 2016 to download. This […] The post SQL Server 2016 Service Pack 1 appeared first on […]

SQL Server 2014 CTP 1 available to download SQL Server 2014 is recently announced in TechED Europe. I am really very excited to download and knew more about it. You can download SQL Server 2014 CTP 1 from here. Each new version of software needs compatible hardware and software. Those are mandatory before you try […] The post SQL Server 2014 CTP 1 available […]

I have very often seen that people are so used to with traditional methods and hence never think in terms of set based programming, they used to write code with evergreen traditional method which people used to use in sequential language like C or many be ASP etc. Let us see this practically with an […] The post Dynamic filter in WHERE Clause with CASE_WHEN_ […]

SQL Server Login permission to read queue message count of Service Broker Few weeks back, I have written one article “Find Service Broker Queue Count in SQL Server”. One of the blog reader has created the UDF I have given in that article and tried to fetch value of that UDF from his web application […] The post SQL Server Login permission to read queue messa […]

Count active Thread for Service Broker Queue in SQL Server Since we have big service broker setup, it is very important to know for us about thread running for target queue. Target queue is the queue which suppose to do the work of actual business logic and we used to give proper naming convention to […] The post Count active Thread for Service Broker Queue […]

Error Fix – Replication subscriber does not exist on the server anymore Recently one of my client has faced weird situation related to SQL Server Replication. Their main database goes down so they started running mirroring database as a primary database, removed subscriber and distributor. Once they have setup primary server back properly, they have […] The […]

Bug Fix – Show Result Pane not working in SQL Server 2012 with CTRL + R Once you are addicted with some keyboard short keys then you hate to use mouse for those things for sure. SQL Developer/DBA are, mostly, addicted with “CTRL + R” short key which is being used to show/hide result panel […] The post Bug Fix – Show Result Pane not working in SQL Server 2012 […]

Find disabled job in SQL Server I, generally, don’t tend to keep disabled job in my server. I would always like to remove disabled job after generating the script and keep the backup of script and this is the reason whenever someone intentionally or unintentionally disabled the job, my monitoring application shows the server name […] The post Find disabled j […]

Copy and archive SQL Server database backup Though you are working as SQL Server DBA, you have to think, at least sometime, beyond relational. We can schedule different types of backup, ie: full, differential, transaction etc., from within SQL Server but what if you want to set archiving policy also? There are so many different […] The post Copy and archive […]

Error Fix Database Mail XPs error in SQL Server Today I have received one email from one of the blog reader since he was facing an issue in his .NET application which is related to SMTP email in SQL Server. Here is the error he was facing: SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of […] The post Error Fix Database Mail XPs error in SQL Ser […]

Deal with message queue conversation with state CD in Service Broker Each side of a Service Broker conversation is represented by a conversation endpoint and sys.conversation_endpoints catalog view contains a row per conversation endpoint in the database. Generally when service broker accept record in message queue, it used to process message and remove it a […]

Keep watch on Service Broker Queue Delay in SQL Server We have very large setup of Service Broker enabled in our live databases. Out of which, some of the nightly business processes create a huge target queue which we suppose to finish during the night time itself. If we can’t cleanup all these queue in […] The post Keep watch on Service Broker Queue Delay i […]

Milestone of 500+ article This post is 504th post in my blog. I have started sharing my knowledge in form of blog article three years back. Initially I, just, wanted to keep my script collection online so that I can find it easily as and when I need it but later on my blog has […] The post Milestone of 500+ article appeared first on Ritesh Shah (Extreme-Advi […]

Update XML attribute value based on condition with XQUERY Recently I had one article which shows how to update XML attribute value which doesn’t had any checking/condition while update. One of the reader asked me how can he update value based on condition. Here is the solution for it. INSERT INTO Orders (ClientID,TestDetails) SELECT ‘CHEM02′,'

Query to Find missing job in SQL Server While managing multiple instance of SQL Server, it is little bit difficult to keep watch on every jobs running in every SQL Server Instances we are managing. I have created one monitoring application which suppose to be watched by at least one DBA 24*7. I have one […] The post Query to Find missing job in SQL Server ap […]

Update XML attribute value with XQUERY in SQL Server Recently I have written article on Inserting attribute in existing XML Elements and Delete XML element. Today I am going to show how to update existing attribute value in XML elements. INSERT INTO Orders (ClientID,TestDetails) SELECT ‘CHEM02′,'’ UNION ALL SELECT ‘ACCU01′,'

XQuery in SQL Server to insert XML attribute in existing XML element Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to insert […] The post XQuery in SQL Server to insert XML attribute in existing […]

XQuery in SQL Server to delete XML element Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to delete element from XML data […] The post XQuery in SQL Server to delete XML element appeared first on […]

WHERE clause on XML column in SQL Server table Xquery is one of the wonderful feature I appreciate most in SQL Server. Due to lack of knowledge, people doesn’t tend to use it. I always prefer XML in case of I have to pass multiple values in one parameter of stored procedure. It is even […] The post WHERE clause on XML column in SQL Server table appeared firs […]