What is deadlock?
A deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does.
Refer: http://en.wikipedia.org/wiki/Deadlock

Scenario:
Data Preparation Script: (Script-a)

--Create Sample Database
CREATE DATABASE SampleDeadLockDatabase
GO

USE SampleDeadLockDatabase
GO

--Create Sample Tables
CREATE TABLE SampleTable1(ID INT IDENTITY(1,1),VALUE UNIQUEIDENTIFIER)
GO

CREATE TABLE SampleTable2(ID INT IDENTITY(1,1),VALUE UNIQUEIDENTIFIER)
GO

--Populate Sample tables
INSERT INTO SampleTable1(VALUE)
SELECT NEWID()
GO 10

INSERT INTO SampleTable2(VALUE)
SELECT NEWID()
GO 10

--Retreive data for verification
SELECT * FROM SampleTable1
SELECT * FROM SampleTable2

Transaction1 Script: (Script-b)

Select @@SPID -- Note down this value here (68)
------------------Transaction1-------------------
--Step1 Begins--
USE SampleDeadLockDatabase
GO
BEGIN TRAN

UPDATE SampleTable1
SET VALUE = '1DF1EBE1-322E-4698-A5BD-2E2C6D85AEB6' 
WHERE ID=1
--Step1 Ends--

---RUN Step2 in Transaction2

--Step3 Begins--
SELECT * FROM SampleTable2 WHERE ID=4

ROLLBACK TRAN
--Step3 Ends--
------------------Transaction1-------------------

Transaction2 Script: (Script-c)

Select @@SPID -- Note down this value here -- 65
------------------Transaction2-------------------
--Step2 Begins--
USE SampleDeadLockDatabase
GO
BEGIN TRAN

UPDATE SampleTable2
SET VALUE = '23154CCE-0919-4D3A-92EB-E6B4062F8715' 
WHERE ID=3
--Step2 Ends--

--Step4 Begins--
SELECT * FROM SampleTable1 WHERE ID=5

ROLLBACK TRAN
--Step4 Ends--
------------------Transaction2-------------------

Data Preparation:
In SQL Server Management Studio, Click on new query
Paste the Data Preparation Script, and execute it.

Dead Lock Scenario Creation:
Paste the two code snippets transaction1 and transaction 2 in separate query windows.
In each of them highlight the code (SELECT @@SPID) and run it to note down the SPID
Now run the Step1 in transaction1
This step updates SampleTable1 with a new value for id 1
Now run the Step2 in transaction2
This step updates SampleTable2 with a new value for id 3
Now run the Step3 in transaction1
This step selects the values of SampleTable2 with id 4
You will notice that this step3 is still executing. Why? We will analyze this later in the post.
Now run the Step4 in transaction2
This step selects the values of SampleTable1 with id 5
You will notice that this step executes for few seconds and completes. Also one of the session (Transaction1 or Transaction2 will be deadlocked.) with the errormessage

Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Analysis:
Why Step3 was waiting (Blocked?)
So now let us replicate the scenario again, by running the above steps1 and 2.
In the new query window (again a new Query window ) Run the below script to understand what locks are placed on the tables pages and rows.

Script-d

--Update this id with the @@spid of transaction1
SELECT  resource_type,
		resource_description,
		resource_associated_entity_id,
		request_mode,
		request_type,
        request_status FROM sys.dm_tran_locks
where request_session_id = 68

--Update this id with the @@spid of transaction2
SELECT resource_type,
		resource_description,
		resource_associated_entity_id,
		request_mode,
		request_type,
        request_status FROM sys.dm_tran_locks
where request_session_id = 65

--Fetch the partition_id of the table
select p.partition_id,t.name,t.object_id From sys.partitions p join sys.tables t
on t.object_id = p.object_id
where t.name in ('SampleTable1','SampleTable2')

Update the values above with the respective @@spud’s that we noted down earlier. (SPID’s of the respective queries)
Output:

When you execute this you can find that each session has created exclusive lock (X) on the row level and intent exclusive (IX) locks on the page and table level.
Let’s find what exactly those rows are.
Copy the resource_description values for resource_type RID to the below script and run it.

Script-e:

select * From SampleTable1 (nolock) where %%lockres%% = '1:154:0'
select * From SampleTable2 (nolock) where %%lockres%% = '1:156:2'

This would output the rows that we updated in respective transactions.

Now run the step3 in transaction1, and run the script-d pasted above.
Output:

You will notice that transaction1 now has the new lock for RID 1:156:2 which is in waiting state.
It’s waiting for an exclusive lock to be released on a row on SampleTable2
In order to find out the row for which transaction 1 is waiting copy the resource_description values to the below script and run it.
Script-f:

select * From SampleTable2 (nolock) where %%lockres%% = '1:156:2'

So transaction1 is waiting for exclusive lock on row ID 3 to be released.
But in no way we are referring to rowid3 of transaction1., we were actually referring to row ID 4.

Why is SQL Server waiting for this row?

In order to understand this stop the step3 in transaction1 (it’s still getting executed) , select step3 code and click on Display estimated execution plan button ( or Query – Display estimated execution plan button) (or Ctrl + L )
You will find a table scans in the execution plan.

So this table scan is forcing SQL Server to actually scan all the rows in the table(for ID=4) , as the ID=3 is blocked by transaction 2 it is waiting for transaction 2 to actually complete in order to take shared lock on the row and compare its value with 4.

Similarly when step4 is executed it is blocked by transaction 1, as step 4 has to do a table scan on the table SampleTable1 for ID = 5, but the transaction 1 has exclusively locked the row ID = 1.
So,
Transaction 1 is blocked by Transaction 2
AND
Transaction 2 is blocked by Transaction 1
This causes deadlock situation.
As both transactions would stay for infinite time blocking each other.
So, one of the transaction that is easier to rollback is terminated by SQL Server giving deadlock error: 1205

Resolutions:
1. Change to Snapshot Isolation.

ALTER DATABASE SampleDeadLockDatabase
SET READ_COMMITTED_SNAPSHOT ON

Run the above query after disconnecting all the queries connected to SampleDeadLockDatabase
Now if you run the above Dead Lock Scenario steps you will notice that the deadlock is not occurring now.

2.Create index on the table so that there will be alternates for Table Scan.
Before testing the below approach remove snapshot isolation

ALTER DATABASE SampleDeadLockDatabase
SET READ_COMMITTED_SNAPSHOT OFF

Script to Create clustered indexes on tables.

ALTER TABLE SampleTable1
ADD CONSTRAINT SampleTable1_PK PRIMARY KEY (ID)
GO

ALTER TABLE SampleTable2
ADD CONSTRAINT SampleTable2_PK PRIMARY KEY (ID)

Now if you run the above Dead Lock Scenario steps you will notice that the deadlock is not occurring now. But you can reproduce dead lock if you force SQL Server to do an index /table scan instead of index seek. Using WITH (INDEX (0)) hint in Step 3 and Step 4.

--Step3 Begins--
SELECT * FROM SampleTable2 WITH (INDEX(0)) WHERE ID=4

ROLLBACK TRAN
--Step3 Ends—

--Step4 Begins--
SELECT * FROM SampleTable1 WITH (INDEX(0)) WHERE ID=3

ROLLBACK TRAN
--Step4 Ends--

SQL Server has System base tables which are hidden cannot be directly queried.

For example:

SELECT * FROM sys.sysrscols

Output:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.sysrscols’.

In order to view data from the hidden System base tables you have to connect to SQL Server using Dedicated Administrator Connection.

In order to connect using Dedicated Administrator Connection, Add ADMIN: before the name of the server\instance in SQL Server Management studio.

Now you can query all the System tables.

------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM sys.sysrscols
GO
SELECT * FROM sys.sysrowsets
GO
SELECT * FROM sys.sysallocunits
GO
SELECT * FROM sys.sysfiles1
GO
SELECT * FROM sys.syspriorities
GO
SELECT * FROM sys.sysdbfrag
GO
SELECT * FROM sys.sysfgfrag
GO
SELECT * FROM sys.syspru
GO
SELECT * FROM sys.sysbrickfiles
GO
SELECT * FROM sys.sysphfg
GO
SELECT * FROM sys.sysprufiles
GO
SELECT * FROM sys.sysftinds
GO
SELECT * FROM sys.sysowners
GO
SELECT * FROM sys.sysdbreg
GO
SELECT * FROM sys.sysprivs
GO
SELECT * FROM sys.sysschobjs
GO
SELECT * FROM sys.syslogshippers
GO
SELECT * FROM sys.syscolpars
GO
SELECT * FROM sys.sysxlgns
GO
SELECT * FROM sys.sysxsrvs
GO
SELECT * FROM sys.sysnsobjs
GO
SELECT * FROM sys.sysusermsgs
GO
SELECT * FROM sys.syscerts
GO
SELECT * FROM sys.sysrmtlgns
GO
SELECT * FROM sys.syslnklgns
GO
SELECT * FROM sys.sysxprops
GO
SELECT * FROM sys.sysscalartypes
GO
SELECT * FROM sys.systypedsubobjs
GO
SELECT * FROM sys.sysidxstats
GO
SELECT * FROM sys.sysiscols
GO
SELECT * FROM sys.sysendpts
GO
SELECT * FROM sys.syswebmethods
GO
SELECT * FROM sys.sysbinobjs
GO
SELECT * FROM sys.sysaudacts
GO
SELECT * FROM sys.sysobjvalues
GO
SELECT * FROM sys.sysclsobjs
GO
SELECT * FROM sys.sysrowsetrefs
GO
SELECT * FROM sys.sysremsvcbinds
GO
SELECT * FROM sys.sysxmitqueue
GO
SELECT * FROM sys.sysrts
GO
SELECT * FROM sys.sysconvgroup
GO
SELECT * FROM sys.sysdesend
GO
SELECT * FROM sys.sysdercv
GO
SELECT * FROM sys.syssingleobjrefs
GO
SELECT * FROM sys.sysmultiobjrefs
GO
SELECT * FROM sys.sysguidrefs
GO
SELECT * FROM sys.syschildinsts
GO
SELECT * FROM sys.syscompfragments
GO
SELECT * FROM sys.sysftstops
GO
SELECT * FROM sys.sysqnames
GO
SELECT * FROM sys.sysxmlcomponent
GO
SELECT * FROM sys.sysxmlfacet
GO
SELECT * FROM sys.sysxmlplacement
GO
SELECT * FROM sys.sysobjkeycrypts
GO
SELECT * FROM sys.sysasymkeys
GO
SELECT * FROM sys.syssqlguides
GO
SELECT * FROM sys.sysbinsubobjs
GO
SELECT * FROM sys.syssoftobjrefs
GO

Following was an intresting observation when creating table variables.
1. Table variables are created in tempdb.
2. Table variables are created in tempdb even before the declare table is called.

Steps to find that the Table variables are created in tempdb:
1. Restart your local SQL Server instance. (So that the tempdb gets cleared)
2. Connect to the SQL Server in SQL Server management studio and check if there are any records in sys.objects in tempdb.

SELECT * FROM tempdb.sys.tables

You will find there are 0 records(ideally)
3. In a new query window run the below script.

USE MASTER
GO

DECLARE @TableVariable TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @TableVariable
select 1,'A'

WAITFOR DELAY '00:02:00'

4. While the above code is getting executed run the below script in other query window.

SELECT * FROM tempdb.sys.tables
--Take objectid from the above resultset
SELECT * FROM tempdb.sys.columns where OBJECT_ID = 117575457

Result: It can be found that the tempdb.sys.tables and tempdb.sys.columns has records for the table variable. This proves that the table variable is created in tempdb.

Steps to find that the Table variables are created in tempdb even before the declare table is called:
1. Restart your local SQL Server instance. (So that the tempdb gets cleared)
2. Connect to the SQL Server in SQL Server management studio and check if there are any records in sys.objects in tempdb.

SELECT * FROM tempdb.sys.tables

You will find there are 0 records(ideally)
3.In a new query window run the below script.

SELECT * FROM tempdb.sys.tables

DECLARE @TableVariable TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @TableVariable
select 1,'A'

Result: It can be found that there is a record in the tempdb.sys.tables even before the DECLARE Statement is executed. This proves that the table variables are created in tempdb even before the declare table is called.

Wishing all the blog readers advanced happy new year.

SQL Server Management Studio (SSMS) Has a available databases dropdown ( to select a database to execute the query.).

This dropdown behaves weird when you change the connection properties.

Observation 1:
Run the below query in the query window

SET PARSEONLY ON

Now when you try to expand the dropdown, the drop down does not expand.

Run the below query to turn off PARSEONLY

SET PARSEONLY OFF

Observation 2:

Run the below query in the query window

SET ROWCOUNT 2

Now if you try to click on the dropdown, you will find only 2 available databases.

Run the below query to reset ROWCOUNT to 0

SET ROWCOUNT 0

Why is the dropdown behaving this way?
SSMS is using the same connection used by the query to fetch the Available databases list.
SSMS runs the below command to fetch the results for the Availabale Databases DropDown, using the same connection used by the Query.

SELECT dtb.name AS [Name], dtb.state AS [State] FROM master.sys.databases dtb

Also, You would also get wrong results count into the @@ROWCOUNT, when you run the query and accidentally touch the availabale databases dropdown.

Observation 3:
Run the below query

SELECT * FROM SYS.OBJECTS
SELECT @@ROWCOUNT as ROWSCOUNT

Make a note of the ROWSCOUNT Result.

Now run the query

SELECT * FROM SYS.OBJECTS

Click on the available databases dropdown and run the below query.

SELECT @@ROWCOUNT as ROWSCOUNT

You can identify the difference in the values of @@ROWCOUNT.

The first result would be the number of objects in the database.
The second result would be the number of databases in the Server.

Consider a scenario where a table has multiple rows, and each of these rows belong to a group and the data from all the rows should be concatenated based on the group. This post explains different ways to accomplish this.

Sample data creation:

CREATE TABLE GroupMembers(GroupName VARCHAR(100),MemberName VARCHAR(100))
GO
 
INSERT INTO GroupMembers
SELECT 'Group1','A'
union
SELECT 'Group1','B'
union
SELECT 'Group1','C'
union
SELECT 'Group1','D'
union
SELECT 'Group1','E'
union
SELECT 'Group1','F'
union
SELECT 'Group1','G'
union
SELECT 'Group1','H'
union
SELECT 'Group2','A1'
union
SELECT 'Group2','B1'
union
SELECT 'Group2','C1'
union
SELECT 'Group2','D1'
union
SELECT 'Group2','E1'
union
SELECT 'Group2','F1'
union
SELECT 'Group2','G1'
union
SELECT 'Group2','H1'
GO
SELECT GroupName,MemberName FROM GroupMembers
GO

The above created table has data that belongs to two groups Group1 and Group2.

Below TSQL Queries concatenate this data into single column/row for each group.

--Sol1:
--Using XML
SELECT GroupName,
(SELECT MemberName 
FROM GroupMembers WHERE GroupName=A.GroupName 
FOR XML PATH('')) 
FROM GroupMembers A
GROUP BY GroupName
GO
 
--Sol2:
--With out seperation between member names
--Using XML, Without XML Attributes
SELECT GroupName,
(SELECT MemberName AS 1 
FROM GroupMembers WHERE GroupName=A.GroupName 
FOR XML PATH('')) 
FROM GroupMembers A
GROUP BY GroupName
 
--Sol3:
--With comma seperation between member names
--Using XML, Without XML Attributes
SELECT GroupName,
(SELECT MemberName+ ',' AS 1 
FROM GroupMembers WHERE GroupName=A.GroupName 
FOR XML PATH('')) 
FROM GroupMembers A
GROUP BY GroupName
 
--Sol4:
--With comma seperation between member names(no ending comma)
--Using XML, Without XML Attributes
SELECT G.GroupName,Left(G.Members,Len(G.Members)-1)
FROM (
SELECT GroupName,
(SELECT MemberName+ ',' AS 1 
FROM GroupMembers WHERE GroupName=A.GroupName 
FOR XML PATH('')) AS Members
FROM GroupMembers A
GROUP BY GroupName
) G

This post deals with one of the SQL Server Misconception – VARCHAR(MAX) Cannot Store More Than 8000 Characters.

Lets analyse this first
Take the below query.

DECLARE @String VARCHAR(MAX)
DECLARE @i INT
SELECT @i = 10000,@String=''

WHILE @i>0
BEGIN
SELECT @String = @String + 'A'
SET @i = @i - 1
END

PRINT @String

When you run this query you will get the following output.
Output:

Find the length of the output:
When you move the cursor to the end of the output in the Messages window you can notice that the column number (as shown in above image) to be 8001(So length is 8000 characters).

This leads to the misconception
- VARCHAR(MAX) Cannot Store More Than 8000 Characters

Actuality:
- Print statement has a limit of 8000 characters(non-unicode) hence the output is truncated.
- The varchar(max) statement can store up to 2gb of data(2^31-1 bytes).

Take the below SQL Code

DECLARE @String nVARCHAR(MAX)
DECLARE @i INT
SELECT @i = 10000,@String=''

WHILE @i>0
BEGIN
SELECT @String = @String + 'A'
SET @i = @i - 1
END

SELECT LEN(@String) as Length

Output:
Length
——————–
10000

(1 row(s) affected)

LEN statement returns 10000 and it proves that varchar(max) can store more than 8000 characters.

Unable to Restore SQL Server 2000 Backup on SQL Server 2012?

If you are trying to restore SQL Server 2000 backup on SQL Server 2008 you would be facing error like:

Msg 3169, Level 16, State 1, Line 1

The database was backed up on a server running version 8.00.2055. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Restoring SQL Server 2000 backup to SQL Server 2012 is not supported.

Why?
According to MSDN In SQL Server 2012, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version.

How to restore then?
Fix/resolution:
Use SQL Server 2008 R2 as an intermediate source.

Steps:
1. First restore the SQL Server 2000 backup to SQL Server 2008 r2.
2. Then backup the database from SQL Server 2008 r2 and restore it on SQL Server 2012.

If you do not have SQL Server 2008 r2, You can download a trial version of SQL Server 2008 r2 from the below links.
http://msdn.microsoft.com/en-us/evalcenter/ff459612
http://www.microsoft.com/sqlserver/en/us/editions/previous-versions.aspx

Alternatively you can use SQL Server 2005 (or) SQL Server 2008 also as an intermediate source.

Following the above steps should help solve the problem.

Also verify the database backup on each target server before restoring: Verify SQL Server Database Backup

If you are sharing single database server with multiple users you might many times end up your process being killed and an error message shown below comes up.

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

You might be interested in policing as to who killed your process?
Was it done intentionally or some server side error occurred?

In this post let us analyze a technique to sport the user who killed your process:

Using SQL Server error Log:
SQL Server logs errors to the sql server error log, It can be found that it also logs the killing of processes in its error log.

So first query the latest error log for the word kill.

EXEC sys.xp_readerrorlog 0,1,'kill'

If you cannot find any results then continue with the next error log

EXEC sys.xp_readerrorlog 1,1,'kill'

You can find the logdate,the killed SPID ,Host name and host processid that killed the process in the result

Now find out the record for your processid in the result.

To find the user who killed your process run the below queries replacing the host process id with the value that you found out in the above step.

select login_name,* from sys.dm_exec_sessions where host_process_id = '[host process ID]'

The login_name in the result gives you the login name of the user who killed your process.

Hope this post helps..
Applicable to sql server 2005,sql server 2008,sql server 2008 r2,sql server 2012
Also read: Read SQL Server Error Log Using TSQL Query

Do Comment if you wanted to discuss further or have any issues.

In order to group by month you can use the SQL Server in built funtion Month().

GROUP BY MONTH([datetimecolumn])

TO group data by year you can use the in built funtion YEAR().

GROUP BY YEAR([datetimecolumn])

In order to group by month and year you can use bith YEAR() and MONTH().

GROUP BY YEAR([datetimecolumn])

Example code:

CREATE TABLE #Sales
(
    Name VARCHAR (100),
    SalesDateTime DATETIME
)

GO
INSERT INTO #Sales
SELECT 'Product1',
       '2010-04-01 00:00:00.000'
UNION ALL
SELECT 'Product2',
       '2010-04-02 00:00:00.000'
UNION ALL
SELECT 'Product3',
       '2011-04-02 00:00:00.000'
UNION ALL
SELECT 'Product4',
       '2011-04-03 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2011-04-03 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000'

GO
--GROUP BY Month
SELECT   count(*) AS SalesCount, MONTH(SalesDateTime) Month
FROM #Sales
GROUP BY MONTH(SalesDateTime)

--GROUP BY YEAR
SELECT   count(*) AS SalesCount, YEAR(SalesDateTime) YEAR
FROM #Sales
GROUP BY YEAR(SalesDateTime)

--GROUP BY Month and Year
SELECT   count(*) AS SalesCount, YEAR(SalesDateTime) YEAR,MONTH(SalesDateTime) Month
FROM #Sales
GROUP BY YEAR(SalesDateTime),MONTH(SalesDateTime)

GO
DROP TABLE #Sales

sql server 2005,2008,2008 r2,2012

How to Group by Day in sql server?

Grouping By Day Of month:
In order to group the data by day you can use sql server in built DAY() funtion.

Grouping By Day Of week:
For this you can use DATEPART(weekday,[date column])

Grouping By week:
For this you can use DATEPART(week,[date column])

Grouping By DAY of Year:
For this you can use DATEPART(dayofyear,[date column])

Let us suppose the column which contains date/datetime values is solddatetime.

To group by day of month you can use:

GROUP BY DAY(SalesDateTime)

To group by Day of week you can use:

GROUP BY DATEPART(weekday,SalesDateTime)

To group by week you can use:

GROUP BY DATEPART(week,SalesDateTime)

To group by Day of year you can use:

GROUP BY DATEPART(dayofyear,SalesDateTime)

Complete example:

CREATE TABLE #Sales
(
    Name VARCHAR (100),
    SalesDateTime DATETIME
)

GO
INSERT INTO #Sales
SELECT 'Product1',
       '2012-04-01 00:00:00.000'
UNION ALL
SELECT 'Product2',
       '2012-04-02 00:00:00.000'
UNION ALL
SELECT 'Product3',
       '2012-04-02 00:00:00.000'
UNION ALL
SELECT 'Product4',
       '2012-04-03 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-04-03 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000'

GO
--GROUP BY DAY of Month
SELECT   count(*) AS SalesCount, DAY(SalesDateTime) Day
FROM #Sales
GROUP BY DAY(SalesDateTime)

--GROUP BY DAY of Week
SELECT   count(*) AS SalesCount, DATEPART(weekday,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(weekday,SalesDateTime)

--GROUP BY Week
SELECT   count(*) AS SalesCount, DATEPART(week,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(week,SalesDateTime)

--GROUP BY DAY of Year
SELECT   count(*) AS SalesCount, DATEPART(dayofyear,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(dayofyear,SalesDateTime)

GO
DROP TABLE #Sales