This is to get a list of Running Transactions.

SELECT sqltext.TEXT,

 req.session_id,

 req.status,

 req.command,

 req.cpu_time,

 req.total_elapsed_time

 FROM sys.dm_exec_requests req

 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

 where DB_NAME(req.database_id) = ‘master’

Difference beween Rank(), Dense_Rank(), Row number()

DECLARE @OrderRanking AS TABLE

(

OrderID INT IDENTITY(1,1) NOT NULL,

CustomerID INT,

OrderTotal decimal(15,2)

)

INSERT into @OrderRanking (CustomerID, OrderTotal)

SELECT 1, 1000

UNION ALL

SELECT 1, 500

UNION ALL

SELECT 1, 650

UNION ALL

SELECT 1, 650

UNION ALL

SELECT 1, 3000

UNION ALL

SELECT 2, 1000

UNION ALL

SELECT 2, 2000

UNION ALL

SELECT 2, 500

UNION ALL

SELECT 2, 500

UNION ALL

SELECT 3, 500

UNION ALL

SELECT 3, 20

SELECT * FROM @OrderRanking

SELECT *,

ROW_NUMBER() OVER (ORDER BY OrderTotal DESC) AS RN,

ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RNP,

RANK() OVER (ORDER BY OrderTotal DESC) AS R,

RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RP,

DENSE_RANK() OVER (ORDER BY OrderTotal DESC) AS DR,

DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS DRP

FROM @OrderRanking

Introduced in SQL Server 2008 : DateTime and DateTime2

DateTime and DateTime2

The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes.
Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to
microseconds and avoids the 3/1000 second rounding issue.
The precision with a datetime2 is upto 100 nanoseconds.

DECLARE @dt AS DATETIME
SET @dt = GETDATE()
SELECT @dt

DECLARE @dt2 AS DATETIME2
SET @dt2 = GETDATE()
SELECT @dt2

As you can see, when using the datetime datatype is rounded to increments of .000, .003, or .007 seconds.
However the datetime2 has a larger date range, a larger default fractional precision, and optional
user-specified precision. The precision scale is 0 to 7 digits, with an accuracy of 100 nanoseconds.
The default precision is 7 digits.

Moreover datetime2 supports a date range of 0001-01-01 through 9999-12-31 while the datetime type
only supports a date range of January 1, 1753, through December 31, 9999. The timerange as mentioned
earlier in case of datetime is 00:00:00 through 23:59:59.997
whereas in datetime2 is 00:00:00 through 23:59:59.9999999.

Stuff function in T SQL

The Stuff Function in SQL Server is used to deletes a sequence of characters from a source string and then inserts a string into another string.

Character_Expression: String on which you want to insert New_String using the SQL Server stuff function.

Starting_Position: From which index position, you want to start inserting the New_String characters.

Length: How many characters you want to delete from the Character_Expression. SQL Stuff Function will start at Starting_Position and delete the specified number of characters from the Character_Expression

New_String: New string you want to stuff inside the Character_Expression at Starting_Position

Syntax: SELECT STUFF (Character_Expression, Starting_Position, Length, New_String)

FROM [Source]

DECLARE @Character_Expression varchar(50)

SET @Character_Expression = 'Learn Server' 

-- Starting Position = 6 and End Position = 0

SELECT STUFF (@Character_Expression, 6, 0, ' SQL') AS 'SQL STUFF' 

-- Starting Position = 7 and End Position = 6

SELECT STUFF (@Character_Expression, 7, 6, 'SQL') AS 'SQL STUFF' 

-- Starting Position = 1 and End Position = 5

SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQL STUFF' 

-- Starting Position = 20 and End Position = 5

SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQL STUFF'

One more use of STUFF to get comma separated values in a a rows as per given below example.

DECLARE @TblCity TABLE(CountryCode INT, City VARCHAR(50))

INSERT @TblCity(CountryCode, City)

VALUES

(1, 'Johannesburg'), 

(1, 'Cape Town'), --South Africa

(2, 'New York'), 

(2, 'Washington'), --USA

(3, 'Paris'),

(3, 'Nice'), --France

(4, 'Rome'), 

(4, 'Bologna'), --Itlay

(5, 'Athens'), 

(5, 'Volos') --Greece

---// Add the Following SELECT FOR XML PATH Query to concatenate the Cities belonging to the same country code onto one line:

--Concat

SELECT DISTINCT CountryCode,

STUFF((SELECT ',' + City

FROM @TblCity t1

WHERE t1.CountryCode = t2.CountryCode

FOR XML PATH('')), 1, 1,'') 

FROM @TblCity t2

Below is also an example to make comma separated values in a column.

But this STRING_AGG() function can be used in and above versions of SQL Server 2017

STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions.

SELECT CountryCode, City = STRING_AGG(City, ', ') WITHIN GROUP (ORDER BY CountryCode)

FROM @TblCity

GROUP BY CountryCode

As compare to FOR XML PATH query, STRING_AGG() function is much faster.. detail comparison given in below link.

https://www.mssqltips.com/sqlservertip/5542/using-for-xml-path-and-stringagg-to-denormalize-sql-server-data/

TSQL query to split full name into firstname and lastname

This script only works with combination of firstname+' '+lastname.
--firstname
SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) as firstname
--lastname
SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, LEN(fullname)) as lastname

SQL Server 2008 New DATETIME DataTypes

The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are

• DATE
• TIME
• DATETIME2
• DATETIMEOFFSE

DATE Data Type

Property	Value
Syntax	date
Usage	DECLARE @MyDate date CREATE TABLE Table1 ( Column1 date )
Default string literal format	YYYY-MM-DD
Range	0001-01-01 through 9999-12-31 January 1, 1 A.D. through December 31, 9999 A.D.
Element ranges	YYYY is four digits from 0001 to 9999 that represent a year. MM is two digits from 01 to 12 that represent a month in the specified year. DD is two digits from 01 to 31, depending on the month, that represent a day of the specified month.
Character length	10 positions
Precision, scale	10, 0
Storage size	3 bytes, fixed
Storage structure	1, 3-byte integer stores date.
Accuracy	One day
Default value	1900-01-01 This value is used for the appended date part for implicit conversion from time to datetime2 or datetimeoffset.
Calendar	Gregorian
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

TIME Datatype

Property	Value
Syntax	time [ (fractional second precision) ]
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
fractional seconds precision	Specifies the number of digits for the fractional part of the seconds. This can be an integer from 0 to 7. The default fractional precision is 7 (100ns).
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
Default string literal format	hh:mm:ss[.nnnnnnn]
Range	00:00:00.0000000 through 23:59:59.9999999
Element ranges	hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
Character length	8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn)
Precision, scale (user specifies scale only)	Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision time(16,7)57 time(0)(8,0)30-2 time(1)(10,1)30-2 time(2)(11,2)30-2 time(3)(12,3)43-4 time(4)(13,4)43-4 time(5)(14,5)55-7 time(6)(15,6)55-7 time(7)(16,7)55-7
Storage size	5 bytes, fixed, is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds
Default value	00:00:00 This value is used for the appended time part for implicit conversion from date to datetime2 or datetimeoffset.
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No

DATETIME2 Data Type

Property	Value
Syntax	datetimeoffset [ (fractional seconds precision) ]
Usage	DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats (used for down-level client)	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] For more information, see the "Backward Compatibility for Down-level Clients" section of Using Date and Time Data.
Date range	0001-01-01 through 9999-12-31 January 1,1 A.D. through December 31, 9999 A.D.
Time range	00:00:00 through 23:59:59.9999999
Time zone offset range	-14:00 through +14:00
Element ranges	YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59.
Character length	26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
Precision, scale	Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision datetimeoffset(34,7)107 datetimeoffset(0)(26,0)80-2 datetimeoffset(1)(28,1)80-2 datetimeoffset(2)(29,2)80-2 datetimeoffset(3)(30,3)93-4 datetimeoffset(4)(31,4)93-4 datetimeoffset(5)(32,5)105-7 datetimeoffset(6)(33,6)105-7 datetimeoffset(7)(34,7)105-7
Storage size	10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds
Default value	1900-01-01 00:00:00 00:00
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	Yes
Daylight saving aware	No

DATETIMEOFFSET Datatype

Property	Value
Syntax	datetimeoffset [ (fractional seconds precision) ]
Usage	DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
Date range	0001-01-01 through 9999-12-31 January 1,1 A.D. through December 31, 9999 A.D.
Time range	00:00:00 through 23:59:59.9999999
Time zone offset range	-14:00 through +14:00
Element ranges	YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59.
Character length	26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
Precision, scale	Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision datetimeoffset(34,7)107 datetimeoffset(0)(26,0)80-2 datetimeoffset(1)(28,1)80-2 datetimeoffset(2)(29,2)80-2 datetimeoffset(3)(30,3)93-4 datetimeoffset(4)(31,4)93-4 datetimeoffset(5)(32,5)105-7 datetimeoffset(6)(33,6)105-7 datetimeoffset(7)(34,7)105-7
Storage size	10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds
Default value	1900-01-01 00:00:00 00:00
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	Yes
Daylight saving aware	No

Difference between SMALLDATETIME and DATETIME

The differences are catagorize in three things.

1. Range of Dates

A DateTime can range from January 1, 1753 to December 31, 9999.
A SmallDateTime can range from January 1, 1900 to June 6, 2079.

2. Accuracy

DateTime is accurate to three-hundredths of a second.
SmallDateTime is accurate to one minute.

3. Size

DateTime takes up 8 bytes of storage space.
SmallDateTime takes up 4 bytes of storage space.