Blog Archives

How to show more than 8000 chars in SSMS output

T SQL Date format convert function

If you ever tried to display more than 8000 characters column such as varchar(MAX) column value in SSMS output in text mode then you will quickly realize that you hit the wall. SSMS truncates anything more than 8000 chars (approx.

Tagged with: ,
Posted in SQL Server, T-SQL Tips, Tips

DISTINCT vs GROUP BY Performance in SQL Query

T SQL Date format convert function

If you ever wonder which one is really faster in Distinct vs Group By Performance Test then I will show some facts in this post so you can write your query accordingly.   To test distinct vs group by performance in SQL Query

Tagged with: , ,
Posted in SQL Server, T-SQL Tips

How to overwrite SQL Server Backup rather than Append Mediaset

T SQL Date format convert function

In this post I will show you how to overwrite sql server backup file rather than append (default action is append to backup media set) If you try below t-sql command to take full sql server database backup then you

Tagged with: , ,
Posted in DBA, SQL Server, T-SQL Tips

SQL Server parent child query example using recursive CTE (Common Table Expression)

How to get parent child relationship using recursive CTE query

Many times we have to write recursive parent child query (Multiple levels of Parent-Child Links)  in SQL Server. There are multiple approaches to achieve this Using Hierarchy ID builtin CLR datatype in SQL Server 2008 or higher (not ion sql server

Tagged with: ,
Posted in SQL Server, T-SQL Tips

SQL Date format using T-SQL CONVERT function (More than 40 formats)

T SQL Date format convert function

 How many time you had to t sql date format date specific way such as yyyymmdd ??   Many people don’t know hidden feature of builtin T-SQL CONVERT function . This function can not only convert data types but also change format of  dates/numbers.

Tagged with: , ,
Posted in SQL Server, T-SQL Tips

How to Create Static / Temp Table on PDW (Parallel Data Warehouse)

Sql ServerParallel Data Warehouse (PDW)

Microsoft PDW (Parallel Data Warehouse) is a special version of SQL Server which is specifically design to scale for massive amount of data (upto several petabytes). PDW can not be downloaded like regular SQL server because it comes as pre-packaged

Tagged with: , , ,
Posted in Parallel Data Warehouse (PDW)

How to login to sql server after all admin accounts removed and you are locked

T SQL Date format convert function

Recently I had an interesting situation. Consider below situation You want to connect to sql server as sysadmin but you are not added to sql server logins using windows authentication BUILTIN\Administrators group has been removed from SQL Server You have

Tagged with: ,
Posted in DBA, SQL Server

How to Script user permissions for SQL Server 2000/2005/2008 for all or single user

T SQL Date format convert function

Recently I had to work on database migration project from SQL 2000 to SQL 2008 and one of the challenges you face is scripting correct permissions to new database. There is no way to script user permissions in right way

Tagged with: ,
Posted in SQL Server, T-SQL Tips

Measure the I/O load on database files and file group using fn_virtualfilestats

T SQL Date format convert function

Here is the script which can give you SQL Server file IO Statistics for each file and file group. Using this data you can find out sql server read/write performance of your disks. DECLARE @TotalIO BIGINT, @TotalBytes BIGINT, @TotalStall BIGINT

Tagged with: ,
Posted in SQL Server

What’s New in SQL Server 2014

T SQL Date format convert function

Here is great presentation/demo about new features in SQL Server 2014. [su_youtube url=”https://www.youtube.com/watch?v=YF9Kidp7qmI” width=”400″ height=”340″]    

Tagged with: ,
Posted in SQL Server