Print Dates without Day or Time in SQL Server

While designing one of the queries in SQL Server 2005, I came across a requirement to print dates without the day (i.e. only the month and year had to be printed) or print only the time and so on. I had seen a couple of questions on the forums about the same in the past, so thought of sharing these queries in case you needed them.

The original format in which the data was stored was 2010-06-30 14:15:15.390. Just declare it in your sql statement as follows:

DECLARE @CurrentDate DateTime
SET @CurrentDate = '2010-06-30 14:15:15.390'

Here’s how we can achieve the desired output with the @CurrentDate:

Print Date in MM/YYYY format

SELECT DATENAME(m, @CurrentDate) 
+ ' ' + CONVERT(varchar(4), DATEPART(year, @CurrentDate))
as 'MM/YYYY'

OUTPUT



Print Date in MM-DD-YY format

SELECT CONVERT(varchar(10), @CurrentDate, 110)
as 'MM-DD-YY'

OUTPUT


Print only the Time without the Date

SELECT CONVERT(varchar(8), CURRENT_TIMESTAMP, 112)

OUTPUT

Temp Table VS Table Variable in SQL Server

Here are some differences between Temp Table and Table Variable in SQL Server

Temp Table	Table Variable
Temp table is valid for a session. For eg: when you run the following code create table #temp(i int) insert into #temp select 345 Go create table #temp(i int) insert into #temp select 345 Go you will get an error	Table variable has a statement-level scope. ie as soon as you execute the statement the scope is lost For eg: when you run the following code declare @t table(i int) insert into @t select 45 GO declare @t table(i int) insert into @t select 45 GO you will not get an error
It is possible to alter the temp table to add columns, idexes,etc	It is not possible to alter a table variable
It is possible to truncate a temp table	It is not possible to truncate a table variable
SELECT INTO method can be used for temp table SELECT * INTO #temp from your_table	SELECT INTO method cannot be used for table variable. You get error for the following SELECT * INTO @t from your_table
Temp table can be useful when you have a large amount of data	For small set of data, table variables can be useful

TSQL Challenge - Find the total number of 'Full Attendees' in each 24 HOP Session

My colleague and SQL Server MVP Jacob Sebastian runs a blog called Beyond Relational which primarily helps people to enhance their T-SQL query writing skills.

Jacob also holds a T-SQL Challenge every now and then on his blog. These TSQL Challenges pose exciting real world problems that we as programmers face in our projects and they require a decent amount of T-SQL Knowledge to solve. Each T-SQL challenge comes with Sample Scripts and Expected Results. You just have to write the best possible TSQL query to solve the challenge.

So if you are a T-SQL programmer who loves to take on these challenges or learn how to best solve these challenge, then here’s a new T-SQL challenge Jacob has come up with

Problem: Find the total number of 'Full Attendees' in each 24 HOP Session

Description: The recent 24-hours-of-pass or more widely known as #24HOP on twitter and other social media was one of the most exciting SQL Server events that happened recently. For this challenge, we created some (fake) attendance data for this event. Your task is to count the number of attendees who watched the complete presentation of each speaker. Click here.

Good luck!

Find the Most Time Consuming Code in your SQL Server Database

This post will demonstrate how to find T-SQL code (SQL Server 2005/2008) that takes the most time to execute. Note that a time consuming code may not necessarily be inefficient; it also depends on the volume of data being processed.

--Top 10 codes that takes maximum time
select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
cross apply
(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))

AS query_text
order by total_elapsed_time desc

--Top 10 codes that takes maximum physical_reads
select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
cross apply
(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))
AS query_text
order by total_physical_reads desc

The sys.dm_exec_query_stats is a Dynamic Management view that gives the statistical information's about cached data. The sys.dm_exec_sql_text is the another view that gives actual text of the sql_handle which is in binary format.

The first query sorts data based on descending order of total_elapsed_time and second query by total_physical_reads.

Similarly also read:

Identify Memory and Performance Issues in T-SQL Queries and Fix them 

and

Monitor Running Processes in SQL Server 2005/2008

NVARCHAR(MAX) VS NTEXT in SQL Server

Here are some differences between nvarchar(max)/nvarchar and ntext in SQL Server

Nvarchar(max)/Nvarchar()	Ntext
Nvarchar(max) is supported only from version 2005 onwards	Ntext is available in prior versions too
You can specify the length in advance for nvarchar datatype i.e. nvarchar(100), nvarchar(4000)	You can't specify the length for Ntext datatype
All types of string functions such as left, right, len, etc. can be used for Nvarchar data type	Only limited functions like substring, datalength are used for Ntext datatype
Nvarchar datatype can be used in local variables	Ntext can't be used in local variables
Nvarchar datatype is available from version 2005 onwards and will be supported for further releases	Ntext will be removed from future release of SQL Server and will be substituted by nvarchar(max) datatype

In one of the upcoming posts, I will tell you how to Convert an Ntext column to Nvarchar(max) and some points to consider while doing so.

Calculate Age from Date Of Birth using SQL Server

Here’s a query to calculate the Age in Years, Months, Days, Hours and Minutes

declare @birth_day datetime
set @birth_day ='19660527 12:34:22'
select
 years,
 months,
 case
 when day(@birth_day)>day(getdate())
 then day(getdate())+
 datediff(day,@birth_day,dateadd(month,datediff(month,0,@birth_day)+1,0))-1
 else day(getdate())-day(@birth_day)
 end as days,
 datepart(hour,convert(varchar(10),dateadd(minute,minutes,0),108)) as hours,
 datepart(minute,convert(varchar(10),dateadd(minute,minutes,0),108)) as minutes
from
(
select
 years,
 datediff(month,dateadd(year,years,@birth_day),getdate())+
 case
 when day(getdate())>=day(@birth_day)
 then 0
 else -1 
 end as months,
 datediff(minute,convert(varchar(8),@birth_day,108),
 convert(varchar(8),getdate(),108)) as minutes
from
(
select
 datediff(year,@birth_day,getdate()) +
 case
 when month(getdate())>=month(@birth_day)
 then 0
 else -1
 end as years
) as t
) as t

In the query shown above, the inner query finds the year difference between current date and birth date. The case expression checks whether current month is greater than the month of birth date. If it is greater, a full year is completed, else the full year is not completed and one year is reduced from the result.

A similar check is done for month too. The outer parts calculate the hours and minutes.

OUTPUT

Fix Row Page and Counts after a Database Upgrade - SQL Server

If your databases were originally created in SQL 2000 and you plan to upgrade to SQL Server 2005/SQL Server 2008, then you should run DBCC UPDATEUSAGE

The DBCC UPDATEUSAGE reports and corrects inaccurate row and page count for tables and indexes and as a result, when you run the sp_spaceused system stored procedure, the space usage reported may be incorrect. Make sure you run DBCC CHECKDB to see if there are any problems reports (Reference)

It is advisable to run the DBCC UPDATEUSAGE command when the load on the server is not too much, although I believe the impact of running it on a ‘normal’ day traffic should not be much. As given in the BOL, here are some best practices to follow:

• Always run DBCC UPDATEUSAGE after upgrading a database from SQL Server 2000. The page and row counts are corrected and are maintained thereafter.
• Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.
• Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.

Make a SQL Server Database Read Only

Usually a database is made read-only when you have to migrate a database to another server for running reports. Before the migration, here’s how to make a SQL Server Database ReadOnly so that data cannot be modified during the process

-- read only
ALTER DATABASE MYDB
SET READ_ONLY
GO

Once in a Read-Only mode, you cannot add new records, update or delete records.

To set the database back to read-write mode use this query:

ALTER DATABASE MYDB
SET READ_WRITE
GO

Now records can be added, deleted or modified.

GROUPBY, HAVING and ORDER BY Usage in SQL Server

I have often seen T-SQL beginners having confusion over the sequence and usage of GroupBy, Having and Order By clause. Here’s a simple example:

I am using the AdventureWorks database.

USE AdventureWorks
GO
SELECT CustomerID, SUM(TaxAmt) AS CustomerTax
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
HAVING SUM(TaxAmt) > 2000
ORDER BY CustomerID

As you can see, we are using HAVING to filter rows based on an aggregate expression. Note that you can include nonaggregate columns in the HAVING clause, however the condition is that these columns must appear in the GROUP BY clause.

OUTPUT

Monitor Activities on your SQL Server 2005/2008

Have you heard about or used SQL Server MVP Adam Machanics Who is Active? This is a must have script for DBA’s and if you have been using sp_who or sp_who2, then you will love it.

In short, this stored procedure helps you monitor a bunch of stuff on your SQL Server quickly and efficiently. You can monitor locks, tasks, resources, sleeping SPID’s and much more. You can read more over here as well as the change log.

The best part is unlike other scripts, this script is updated regularly by Adam, making it production box stuff. I am excited about sharing this script with my DBA friends and I hope you make the most of it too.
Download Link (v 9.89)

SQL Server 2008 Service Pack 2 CTP is now Available

A Community Technology Preview (CTP) of SQL Server 2008 Service Pack 2 (SP2) is now available.

“SQL Server 2008 SP2 CTP contains the cumulative updates up to SQL Server 2008 SP1 cumulative update package 8, and fixes to issues that have been reported through our customer feedback platforms. These include supportability enhancements and issues that have been reported through Windows Error Reporting”

Here are some new changes in SQL Server 2008 SP2 CTP

- SQL Server Utility - models SQL Server-related entities in a unified view. Read more at Managing the SQL Server Utility

- Data-tier Applications - DAC provides a single unit for authoring, deploying, and managing the data-tier objects instead of having to manage them separately. See Understanding Data-tier Applications

Reporting Services in SharePoint Integrated Mode (Feature Pack) - The SQL Server 2008 Service Pack 2 (SP2) Reporting Services Add-in for Microsoft SharePoint Products 2007 allows you to connect to SQL Server 2008 R2 Report Servers

Download the Community Technology Preview (CTP) of SQL Server 2008 Service Pack 2 (SP2) to learn more.

Check If Stored Procedure Exists, Else Drop It and Recreate – SQL Server

We tend to forget the most basic syntax of all and one of them is to check if a stored procedure exists and then drop it. Here’s the syntax for your reference:

IF EXISTS
(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[YourStoredProcName]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS
-- Logic Comes Here
GO

Update: A BETTER solution suggested by Madhivanan

IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS-- Logic Comes Here
GO

The syntax shown above will drop a stored procedure if it exists and recreate it.

Find Maximum Value in each Row – SQL Server

Here’s a simple and efficient way to find the maximum value in each row using SQL Server UNPIVOT

DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)

-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2

-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(
  SELECT id, col FROM @t
  UNPIVOT
  (col FOR ListofColumns IN (col1,col2,col3))
   AS unpivott) AS p
GROUP BY id 

OUTPUT

If you new to UNPIVOT, read Using PIVOT and UNPIVOT

SQL Azure – Free Technical Documents

Microsoft recently released a set of document that provides guidelines on how to sign up for SQL Azure, how to get started creating SQL Azure servers and databases, how to develop and deploy solutions with Azure, Security Guidelines, Query Troubleshooting, Performance and Scalability, SLA’s, Pricing and so on. Here are the download links for your reference:

Getting Started with SQL Azure

Accounts and Billing in SQL Azure

Developing and Deploying with SQL Azure

Security Guidelines for SQL Azure

Scaling out with SQL Azure

SQL Azure vs. SQL Server

SQL Azure SLA document

Troubleshooting and Optimizing Queries with SQL Azure

Windows Azure Platform Training Kit

Count Occurrences of Characters in a String – SQL Server

While implementing a feature of a GUI Editor, we came across a requirement to count the occurrences of characters in a string, stored in our SQL Server Database.

I was suggested a simple and efficient script that I am sharing with all of you for your use.

DECLARE @longWord varchar(28)='AntiDisEstablishmentArianism'

SELECT CountCharacters =
DATALENGTH(@longWord)
- DATALENGTH (REPLACE(REPLACE(LOWER(@longWord),'a',''),'i',''))

OUTPUT

As simple as it gets without using the SUBSTRING, PATINDEX or GROUP BY functions!

Understanding SQL Server DateTime Column – 4 Part Series

One of the most frequently asked questions by SQL Server Developers is about the SQL Server DateTime column. Out of these, questions like Retrieving Date in a particular format, Saving Dates, Formatting Dates, Date Ranges etc. are most common.

SQL Server MVP Madhivanan recently wrote a four part series covering most of these questions. They are just awesome and full of SQL Server DateTime goodness. I highly recommend SQL Server Developers to read this series to understand DateTime Operations. Here is the list:

Understanding Datetime column - Part I

Understanding Datetime column - Part II

Understanding Datetime column - Part III

Understanding Datetime column - Part IV