This is a guest post by Priyanka Pathak who is my friend and Sr. Business Executive. Summary: Are you having trouble getting exclusive access to a SQL database that you need to restore? Read this article to find more about this issue and how you can resolve it. It also describes methods you can use to restore the database if the issue persists. One of the responsibilities that a SQL database administrator needs to carry out is to restore the test or development database from backup whenever it’s needed. However, before restoring a database, a DBA must also ensure getting exclusive access to SQ ..read more

In SQL Server most of the system functions require parenthesis at the end. The examples can be SELECT GETDATE(), NEWID(), RAND(), ERROR_MESSAGE() Some functions do not need the parenthesis. The examples can be SELECT CURRENT_TIMESTAMP, CURRENT_USER The question is Why do some system functions require parenthesis and some do not require? Post your answer in the comment section ..read more

Well. I often see people using the word “Statement” when they refer CASE like CASE Statement. In SQL Server, It evaluates the conditions and return one of the results based on whether the condition is true. A simple example is SELECT CASE WHEN 1=1 then 'Yes' ELSE 'No' END as Case_example In SQL Server, CASE can be considered as a Keyword that works on an Expression or a statement which returns true/false value. The result of CASE is always a single value. Note that IF statement in SQL Server is a Keyword that return either single value or multiple values IF which returns a single value IF 1 ..read more

One of my blog readers asked me “How to create random data with different data types? I need to create this data set to test queries on large set of data” Well. There can be several methods to create sample data. The simple method is to make use of System functions CHECKSUM and NEWID as shown below Create a temporary table with different data types Create table #random_data ( first_name varchar(100), unique_id uniqueidentifier, mobile_number bigint, dob date, inserted_dte datetime, user_code int, is_active ..read more

When you write queries that involve datetime columns, it is often needed to remove time part from datetime value to compare only date part. An example would be, you have table called sales_details that has sales_date as one of the columns and you want to find out all sales that were made yesterday. In this case you need to include all times of yesterday ie remove time part and include only date part. The simple code that finds yesterday’s sales is WHERE datediff(day,sales_date,getdate())=1 But if the column sales_date is indexed, SQL Server will not make use of it because of the usage of fun ..read more

Alveena Joyce, my colleague, SQL Blogger and SQL Cartoonist, is celebrating her birthday today. I am wishing her using a SQL code which will print Customized Greeting message The following code is not formatted and may not be understandable but will print Greeting message In SQL Server Management Studio, Set the result mode to Text (Press CTRL+T) and then execute the following code set nocount on select space(17-len(replicate( ​ char(37),no)))+ replicate(char(case when no=1 then 134 else 37 end), no*2-1) from (select ​​ row_number() over (order by (select 1)) as no from (select 0 as no​​ unio ..read more

DateSerial function accepts three paramter values year,month and day and return a valid date value with time set to midnight. SQL Server does not support this function until version 2012 is released. There can be many ways to simulate this functionality. Here are 10 different ways Consider the following set of data create table #t(year_col int,month_col int,day_col int) GO insert into #t (year_col,month_col,day_col) select 2001,6,2 Character Handling Method 1 : Convert the numbers to varchars and into datetime set dateformat dmy select convert(datetime,cast(day_col as varchar ..read more

This code is highly not formatted but will produce a nice result. Set Result mode into Text (Ctrl+T) and then Execute this code set nocount on select case no when 1 then col when 2 then stuff(stuff(stuff(stuff( col,1,cast(4e1/5 as int),replicate(' ',cast(4e1/5 as int))),19, 3,replicate(' ',3)),cast(sqrt(12+11e2)/3 as int)*3,5,replicate(' ', 5)),47,16,replicate(' ',3))when 3 then stuff(stuff(stuff(stuff(col,1,cast( 4e1/5 as int),replicate(' ',cast(4e1/5 as int))),19,3,replicate (' ',3)),cast(sqrt(12+11e2)/3 as int)*3,4,replicate(' ',4)),48, 15,replicate(' ',2))when 4 then stuff(stuff(stuff(s ..read more

There is an interesting question that I have read recently “Given a number N, write a code to print all positive numbers less than N in which all adjacent digits differ by 1” Here is my attempt to solve this using SQL Server T-SQL --Provide input value declare @number int set @number=105 --Create a temporary table to store numbers create table #temp (number int) insert into #temp(number) select top 100000 row_number () over (order by (select null)) as sno from sys.sysobjects as t1 cross join sys.sysobjects as t2 --Create another temporary table to derive the differe ..read more

HAVING Clause is used to filter the grouped set of result. It is always used along with GROUP BY Clause. Mostly people use it to find count, minimum or maximum value, sum etc. It can also be used effectively apply many business logic. Let use consider the following tables create table #products ( product_id char(6) primary key, product_name varchar(75) ) insert into #products (product_id,product_name) select 'PR0001','Television' union all select 'PR0002','Mobile' union all select 'PR0003','Air conditioner' create table #customers ( cust_id char(6) primary key ..read more