Category: SCRIPT GALLERY

ROW_NUMBER() window function in SQL Server

This blog post explains few methods to generate a running number for a SELECT query. Different SQL versions have different ways to achieve this. Let us quickly see ROW_NUMBER() window function with this blog post.

ROW_NUMBER()OVER(PARTITION BY column_list ORDER BY column_list ASC/DESC)

This returns the sequential number for rows. A Quite simple way in SQL Server so far, note that there are different ways we can generate this numbers on group or set depending on sorted manner.Let us see some of those forms in this post. As first step, let us create a SampleData table populated with few records as below.

Create Table SampleData
(
	CourseId	Int,
	CourseName	Varchar(100),
	Institute	Varchar(100),
	Fees		Int
)

Insert into SampleData
	Values  (1,'SQL Server', 'Aptech', 1000),
			(1,'SQL Server', 'WowTech', 2000),
			(2,'.NET', 'NetTechs', 6000),
			(2,'.NET', 'Aptech', 8000),
			(2,'.NET', 'SimpleLearn', 7500),
			(3,'Python', 'Aptech', 1000),
			(3,'Python', 'SimpleLearn', 1500),
			(3,'Python', 'PyLearn', 1000),
			(3,'Python', 'NetTechs', 1000),
			(3,'Python', 'WowTech', 1000)

Select * from SampleData

--Drop Table SampleData

Simple Form of generating a running number

We can create a running number column with row_number window function as below:

Select Row_Number()Over(order by (Select NULL) ASC) Rn,* From SampleData
Select Row_Number()Over(order by (Select NULL) DESC) Rn,* From SampleData

In the above two example, we can see both ASC and DESC returns the same set of ordering and running number because the order by always on NULL value which would have no effect for ASC and DESC.

Form of generating a running number based on a set of data

Yes, this is based on set or group data. For an example, if we need to generate a running number for a grouped records and so on, you can introduce the Partition by clause to the above query as below.

Select Row_Number()Over( partition by CourseId order by (Select NULL) ASC) Rn,* From SampleData

Form of generating a running number based on a set of data with a defined order of data

In the above example, we have seen the number based on data, now, we are going to quickly see the numbering is based on group of data as well as ordered in a defined way. In our example, we wanted to see the data group for CourseID should be sorted by its Fees in ascending order.

Select Row_Number()Over( partition by CourseId order by Fees ASC) Rn,* From SampleData

Hope this is helpful to understand better of ROW_NUMBER window function in SQL Server.

Hope you enjoyed this post, share your feedback in comment section. Recommending to go through “Microsoft SQL Server – Beginners Guide” series for more information.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

How to find last restart time of SQL Server

Why do we need this information?

This is an important piece of information for a SQL Server engineer. I carefully tend to use the word “engineer” here because this information helps all kinds of people who works with SQL Server. As an Administrator would like to understand when did it happen to understand the underlining issue with any server level issues/fail over details etc. As a performance Engineer, this information helps when the cache gets cleared and for many other reasons. So its very important and useful information as long as you are working in SQL Server.

Now, there are two different ways your SQL Service could get restart – (1) By restarting the SQL Service and (2) By restarting the Windows Server. Let us look at different ways to find this information.

What are the different ways to get last restart of SQL Service?

Method 1: Using sys.dm_os_sys_info Its the most simple, easiest and my favorite way to get this information.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Method 2: Using tempdb database creation date
SELECT create_date FROM sys.databases WHERE name = 'tempdb'

Method 3: Using sp_readerrorlog
sp_readerrorlog 0,1,'Copyright (c)'

What are the different ways to get last restart of Windows Server?

Method 1: Using Task Manager

Up time represents how long the server is being up and running since the last restart. So to calculate when did the reboot happened, need to substract the up time with current date and time.

Method 2: Using systeminfo command
systeminfo | find /i "Boot Time"

Hope this helps and if you have any other ways, please share in the comment section.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

xp_servicecontrol in SQL Server

Problem Statement:

While I was automating a new server preparation step, one of my requirement is to make sure “MSDTC” service is up and running on the server. To enable this check, we can use an extended stored procedure – xp_servicecontrol. There are two parameters to this procedure as below.

It is important to understand that this extended procedure is an undocumented one which means, it cannot be used for a development purpose, this can be anytime removed from the product by Microsoft. However, this can be used as it warranted.

xp_servicecontrol @Action = N’Action’ @ServiceName = N’Service Name’

Action Description
start To start the service if it is not running. If the service is already in running state, an error is raised.
stop To stop the service if it is running. If the service is not running, an error is raised.
pause To pause a running service. An error is raised if the service is not running. Remember not all services can be paused.
continue To continue running a paused service. An error is raised if the serviced is not in paused state.
querystate To return the current state of the service.

Code:

declare @ServicesStatus table
 ( 
 Status varchar(50)
 )
    
 INSERT @ServicesStatus 
 EXEC xp_servicecontrol N'QUERYSTATE',N'msdtc'
    
 Declare @v varchar(max)
 select @v=status from @ServicesStatus
 select @v

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

ISUNIQUEIDENTIFIER function in SQL Server

Today, I had a requirement to check a value can be converted as uniqueidentifier datatype or not, so quickly come up with the below function. Hope this would help you if you come across similar requirement in future.

Code:

CREATE FUNCTION DBO.ISUNIQUEIDENTIFIER
(
@InputVal nvarchar(50)
)  
RETURNS SmallInt AS  
BEGIN

RETURN 
	CASE WHEN SERVERPROPERTY('PRODUCTMAJORVERSION') >=11 THEN
		CASE WHEN TRY_CONVERT(UNIQUEIDENTIFIER,@InputVal) IS NOT NULL THEN 1 ELSE 0 END
	ELSE (
		CASE WHEN LEN(@InputVal) = 36 AND @InputVal LIKE
		   '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' THEN 1 ELSE 0 END ) 
	END
END

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Calendar Table in SQL Server

What is Calendar Table in SQL Server ?

Calendar Table is a custom/user table in SQL Server created for ease of users coding. At times, it is observed many programmers are struggling to write date/calendar queries. Also, it is observed online processing of these functionalities has its own performance impact. As mentioned, Calendar table would come in time to save programmers as those are pre populated and quite easy to use as well.

Calendar Table Code

Drop Table if exists Calendar;

Create Table Calendar 
(
	Date Date Primary Key, DayNo TinyInt, MonthNo TinyInt, YearNo SmallInt
	, WeekNo TinyInt, ISOWeekNo TinyInt,WeekDayNo TinyInt
	, DayName Varchar(10), MonthName Varchar(10), QuaterNo TinyInt
	, DayOfYearNo SmallInt, IsWeekEnd TinyInt
	, IsHoliday TinyInt, HolidayDescription nVarchar(500)
)

;with cteSeed  as ( Select 0 Seed union all Select Seed +1 From cteSeed where Seed <=100000)
,     cteDateDimenion as ( Select dateadd(Day,Seed,cast('1990-01-01' as date) ) Date from cteSeed)
,     cteCalendar as (
		Select 
		         Date,Day(Date)DayNo,Month(Date) MonthNo, Year(Date) YearNo 
			,DAtePArt(Week,Date) WeeKNo, DATEPART(ISO_WEEK,Date) ISOWeekNo,DATEPART(WEEKDAY,Date) WeekDayNo
			,DAteName(WEEKDAY,Date) DayName, DAteName(Month,Date) MonthName
			,DATEPART(Quarter,date) QuaterNo, DATEPART(DAYOFYEAR, date) DayOfYearNo
			,Case when DAteName(WEEKDAY,Date) in ('Saturday','Sunday') Then 1 Else 0 End 'IsWeekEnd'
			,Case When Day(Date)=1 and Month(Date)=1 Then '|New Year Day|' 
					When Day(Date)=25 and Month(Date)=12 Then '|Christmas Day|' 
					Else '' End  
			+ Case when DAteName(WEEKDAY,Date) in ('Saturday','Sunday') Then '|' 
			+  DAteName(WEEKDAY,Date) + '|' Else '' End as HoliDayDescription
		From cteDateDimenion )
Insert into Calendar (Date,DayNo,MonthNo,YearNo,WeekNo,ISOWeekNo,WeekDAyNo
,DayName,MonthName,QuaterNo,DayOfYearNo,IsWeekEnd,IsHoliday,HolidayDescription)
Select Date,DayNo,MonthNo,YearNo,WeekNo,ISOWeekNo,WeekDAyNo
,DayName,MonthName,QuaterNo,DayOfYearNo,IsWeekEnd,NULL,HolidayDescription From cteCalendar
OPTION (MAXRECURSION 0);

Update Calendar Set IsHoliday = Case when Len(HoliDayDescription) > 0 Then 1 Else 0 End 

Select * From Calendar

Assumptions:

1. The week day starts from Sunday.

2. Holiday list is specific to country, so the code above is as an example, feel free to add your specific holidays.

Use Cases:

How do you calculate business/working days between two dates?

Select count(1) as 'No. of Business Days' From Calendar 
where Date between '2020-01-01' and '2020-12-31' and IsHoliday != 1

How do I get Day name of first day of given month and year?

Select Date,DayName FRom Calendar where YearNo=2020 and MonthNo =11 and DayNo=1

I am sure, there many other use cases it would be helpful in real time, keep explore and feel free to make modification as required.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!