SQL Server: Execute SQL Script Using SQLCMD Command Line

SQLCMD command line Utility is an alternative way to execute SQL scripts using the command line. This allows us to execute SQL scripts without touching SQL Server Management Studio (SSMS) GUI.

The command line has multiple advantages in certain circumstances. Frequently, this process will save your time and mouse clicks. Plus, it is frequently used to run the same files on multiple sql servers with ease.

To follow along with my example, copy the following code and save it as CreateDBCompany.sql file under the C:\Sqlscripts\ folder.

USE Master
GO

IF EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = 'DBCompany' )
DROP DATABASE DBCompany
GO

CREATE DATABASE DBCompany
GO

USE DBCompany
GO

IF EXISTS ( SELECT [name] FROM sys.tables where [name]='Employee' )
DROP TABLE Employee
GO

CREATE TABLE Employee
(
EmpId int,
Fname varchar(40),
Lname varchar(40),
Hiredate datetime,
Salary int
)
GO

INSERT INTO EMPLOYEE VALUES (101,'Vishwanath','Dalvi','10/16/2011',23025);

Using the sqlcmd command

Now that our example is set, we will execute the CreateDBCompany.sql script file using SQLCMD command line.

1. Click Run and type the letters CMD. Hit OK, and a command prompt window will appear.

2. Now, locate the directory C:\sqlscripts\ using the following commands:

• Change the directory to C drive using cd c:\.
• Go to the folder by typing cd followed by a space, and then sqlscripts folder name such as cd sqlscripts.
• See the sqlscripts directory contents using dir. You can see our CreateDBCompany.sql file.

3. Here, we are using SQLCMD to connect to your local sql server database. Through this same process you may connect to any remote sql server on your network.

SQLCMD -S DALVI2\SQLSERVER2012 -E -i CreateDBCompany.sql

The -S switch is for the Server name and is the same one we use to connect using SSMS GUI. Connect to server option. In my case, it is DALVI2\SQLSERVER2012.
The -E switch is used for trusted connection.
The -i switch is for .sql script file name.

All switches are case sensitive. For more help on SQLCMD Utility switches, use the sqlcmd /? command.

You should now be able to go to SSMS and query the DBCompany database. You will see the employee table with the one record that we had previously created.

SQL Server: Check if Table or Database Already Exists

Errors warning that “there is already an object” and that the “database already exists” can easily be avoided by first determining if your table and database have already been created.

Does these types of error annoys you when you’re trying to create tables and database in SQL Server?

You will never get these messages if you first check that the database and table exists through the IF EXISTS command. Here are two examples searching for a specific table or database.

Does the table exist in the database?

IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'Employee' )
DROP TABLE Employee
GO

CREATE TABLE Employee
(
   EmpId INT,
   FName VARCHAR(30),
   LName VARCHAR(30),
   Salary INT
)
GO

In this example sys.tables is a system created table which includes the list of all the created tables within your database. First, we filter with the name column and specify the employee table name. If this table already exists, we drop it and create a new employee table.

If the table does not exist, the employee table statement.

Does the database exist?

We will walk through in a pattern similiar to our previous example. Instead of checking for a table, we will check if the db exists instead.

IF EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = 'DbArticles' )
DROP DATABASE DbArticles
GO

CREATE DATABASE DbArticles
GO

The sys.databases is a system created table which includes the list of databases within the SQL server. We filter based on the name column and the dArticles database name. If the database already exists, we then drop the database and create a new database under the same name. If the database does not exist, it will create dArticles database.

SQL Server: Copy One Table to Another Using Stored Procedure

When modifying an important table in the database, user frequently backup the table by making a copy of the original table with a different name. Using a stored procedure makes this process simple and convenient to reuse.

Data can be priceless. One of the ways to best protect is to duplicate the table. Let us walk through an example.

The syntax for a basic table copy command would be the following:

Select * 
Into   original_tablename_backup
From   original_tablename;

In our example, we will have a table named emp in our database. We wish to make a copy of the originalemp table. This query will create an emp_backup table. It will raise an error if the emp_backup table already exists.

Select * 
into   emp_backup
from   emp;

As we need to perform these type of queries many times, we can use a stored procedure to make a copy of table. A stored procedure is nothing more than saved SQL that can be called repeatedly to perform similar functions.

CREATE proc BACKUP_TB 
@tbname AS varchar(MAX) 
AS 
BEGIN
SET nocount ON; 
DECLARE @query AS varchar(MAX)
SET @query = 'select * into '+@tbname+'_backup from '+@tbname+''; 
EXEC (@query)
SET nocount OFF; 
END

This stored procedure takes the original table name as a parameter and it will create original table name underscore backup table as a copy of original table.

Let’s see how to execute this ‘BACKUP_TB’ stored procedure with table name as parameter.

Suppose we have the dept table in our database, and we wish to make copy of dept table as namedept_backup.

EXEC BACKUP_TB DEPT;

We are executing our procedure ‘BACKUP_TB’ and passing the table name a parameter. Our stored procedure will create dept_backup table as a copy of original dept table.

If we have a different schema name in our database and we are making a copy of the table under AbcLtd schema, then we need to use brackets to parse the ‘.’ symbol as a parameter with our backup_tb procedure as the following:

EXEC BACKUP_TB [AbcLtd.salary];

SQL Server: How to Left Pad a Number with Zeros

The replicate T-SQL function in Microsoft’s SQL Server makes it simple to add leading zeros to a number value.

Let’s create a table ‘emp’ with empid and salary columns.

create table emp
(
empid int,
salary int
);

Insert some sample data into table ‘emp’.

insert into emp values (1,300);
insert into emp values (2,30);
insert into emp values (3,500000);
insert into emp values (4,1000);

select * from emp;

empid       salary
----------- -----------
1           300
2           30
3           500000
4           1000

(4 row(s) affected)

Now, let us left pad the salary column with 0s.

If we don’t know how many 0s we need to pad to the left so that all the values would be equal length, we can find the max length of salary column using following query:

SELECT Max(mylength)
FROM   (SELECT Len(salary) AS mylength
        FROM   emp) x;

-----------
6

The query returns a value of 6 which is the max length in salary column due to empid 3 with 500000 salary.

Now to add the leading zeros to the salary column, we can use the replicate inbuilt string function in T-SQL.

Syntax:

replicate(string expression, integer expression);

SQL Server 2008: The below query will left pad salary column with 0s.

select empid,          
       replicate('0', 6 - len(salary)) + cast (salary as varchar) as salary
from   emp;  

empid       salary
----------- -----------
1           000300
2           000030
3           500000
4           001000

(4 row(s) affected)

In the SQL Server 2008 version query, the first parameter is our ‘0’ padding string. In the second parameter we are subtracting the length of salary column from 6 which is our max length. Thus, our expression knows how many 0s are needed to left pad and concatenate the salary column.

With SQL Server 2012 you can achieve the same output with a simple one line code using FORMAT function.

SQL Server 2012: Left pad salary column with 0s

SELECT FORMAT (salary, '000000') FROM emp;

empid       salary
----------- -----------
1           000300
2           000030
3           500000
4           001000

(4 row(s) affected)

SQL Server: How to Swap Values between Two Columns

When working with databases, moving and swapping values between columns is a common task.

Frequently, when building and administrating my database, errors in configuration and coding can be fixed by simply swapping values between two different columns.

Let’s start by creating table ’employees’.

if exists ( SELECT name from sys.tables where name ='employees')
drop table employees
GO
CREATE table employees
(
empid int,
fname varchar(20),
lname varchar(20),
deptno int
);

Let’s insert some sample data in the table.

INSERT into employees VALUES (100,'smith','jon',10);
INSERT into employees VALUES (101,'novak','tim',10);
INSERT into employees VALUES (102,'benk','mark',10);
INSERT into employees VALUES (103,'jobs','steve',10);
INSERT into employees VALUES (104,'alex','gravell',20);

Suppose that accidentally we have inserted firstname as lastname and vice versa for department 10. To correct this, we need to swap the firstname and lastname values for that department.

In employees table, firstname =’smith’ and lastname=’jon’, we need to swap these values so the updated table will have firstname=’jon’ and lastname=’smith’ and likewise for all the employees in department 10.

The below query will update the employees table by swapping the values for fname and lname.

SELECT * from employees;
Go

DECLARE @temp as varchar(20)
update employees
set    @temp = fname,
       fname = lname,
       lname = @temp
WHERE  deptno = 10;
GO

SELECT * from employees;

Swap values between two columns

The logic for the update query is same as we do in other programming languages. We store the values that are going to be overwritten in a temporary variable and then write back the temporary variable to the correct position.

SQL Server: How to Check if a File Exists in a Directory

Frequently, when working with SQL, we need to know if a file exists in a local directory or not. This can be done using SQL Server’s built-in procedure known as master.dbo.xp_fileexist. This user-defined function (UDF) checks whether or not a file exists in a specified directory.

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

In the function above, we are passing parameter @path, and the built-in procedure master.dbo.xp_fileexist will check whether that file really exists on the specified path. The function will return 1 if it exists. If the file does not exist, the function will return 0.

Let us see how this function performs with an example.

select dbo.fc_FileExists('C:\mywork\tech-recipes.rar');
-----
1


select dbo.fc_FileExists('C:\mywork\barfi.mp3');
-----
0

If you’ve a table with a column listing all the file paths you can use this function on table too.

Create table filelist
(
  fileno int,
  filename varchar(max)
);

Let us insert sample data.

Insert into filelist values (1, 'C:\mywork\tech-recipes.rar');
Insert into filelist VALUES (2, 'C:\mywork\barfi.mp3');

Here we can use the dbo.fc_FileExists(filename) function to check whether or not the file exists.

Select fileno,
       filename, 
       dbo.fc_FileExists(filename) as IsFileExists
From   filelist;

fileno      filename                              IsFileExists
----------- ------------------------------------  ------------
1           C:\mywork\tech-recipes.rar                  1    
2           C:\mywork\barfi.mp3                         0

IsFileExists is a temporary column. If you want to make it a permanent column, you will need to use update query.

SQL Server: Generate Insert Statement Script

When running reports, for example, the same insert statements need to be created over and over again. SQL Server includes a great feature to generate these insert statements automatically.

For this example, I be working with the emp table with 9 rows in my database. Now, many months later, I need to generate an insert statement for this table.

1.Open SQL Server Management Studio and go to Object Explorer.

2.Right click on your database. Go to Tasks then Generate Scripts.

3.You will find a window titled “Generate and Publish Scripts” and an introduction is displayed. Just click on Next to proceed.

4.In the Choose Objects window select the radio button Select specific database objectsand expand the Tables tree structure. Select the table name for which you are going
to generate the insert statements. Then click on Next. In my example, I am selecting the emp table below.

5.  Now in Set Scripting Options window, you need specify how you are going to save your script. Let save to a file by selecting Save script to a specific location and then Save to file with Single file option. You will need to make sure you note the path. Then select theAdvanced button.

6.Now in “Advanced Scripting Options” window in General go to Types of data to script and select Data only. Click on OK and then click on Next.

7.Now you will find the Summary window which shows the information about the selected database, tables, and target file path. Click on Next.

8.Now in Save or Publish Scripts window will show the results of success.

9.In the target file you will find the insert statements for the table.

SQL Server 2008/2008 R2: Move tempdb to Improve Performance

By default, tempdb is placed on the same drive that SQL Server is installed on. This can impair performance as tempdb is frequently used to store temporary tables and objects. If the hard drive is being accessed for other functions, it can result in sluggish performance by the database as well as any software that is using the database. For optimum performance, tempdb should be on a SATA drive instead of an IDE drive and should not be on the same drive as the SQL Server software or the operating system (boot drive).

1. Open SQL Server Management Studio.

2. Connect to the desired server.

3. Click the New Query button.

4. Copy and paste the following into the query pane:
USE master; GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '[new location]\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '[new location]\templog.ldf');
GO

5. Change [new location] in the pasted code (for both the tempdb.mdf and templog.ldf files) to the path of the new location.

6. Click Execute.

7. Go to the Control Panel and then Administrative Tools. Select Services.

8. Stop and restart SQL Server (MSSQLSERVER).

9. Go back to SQL Server Management Studio and open a new query pane.

10. Execute the following to verify that tempdb was moved to the desired location:
SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

11. You should see the path to the new location in the physical_name column.

12. If the move was successful, go to the old location and delete the tempdb.mdf and tempdb.ldf files as they are no longer needed.

SQL Server 2012: Find Expiry Date of SQL server

If you have installed SQL server 2012 Enterprise Evaluation Edition, this trial software will expire after 180 days without any pre alert notification. This SQL query will show the current version and edition of SQL Server you are using.

SQL server 2012 has been introduced in three main licensed editions: Enterprise, Business intelligence and Standard. Microsoft has also released fully featured 180 days free trial SQL server 2012 Enterprise Evaluation Edition.

Considering all the various different versions and the potential of expiration of trial additions, detecting the expiry date is a neat weapon to have.

The succeeding query will show the version and edition of SQL server you are presently using.

SELECT 'SQL_Server_Edition' AS 'Verion', SERVERPROPERTY('Edition') AS Type_Of_Edition
UNION
SELECT 'Version', @@VERSION

The following query will list the installed date, expiry date, number of days used, and number of days left to use SQL server 2012 evaluation edition.

SELECT  create_date AS 'SQL Server Installed Date',
     Expiry_date AS 'SQL Server Expiry Date',
    DATEDIFF(dd,create_date, GETDATE()) 'No_of_Days_Used',
    ABS(DATEDIFF(dd,expiry_date,GETDATE())) 'No_Of_Days_Left'
FROM
(SELECT sp.create_date,
     DATEADD(dd, 180, sp.create_date) AS Expiry_date
 FROM   sys.server_principals sp
 WHERE  sp.name = 'NT AUTHORITY\SYSTEM') as exp_date_tbs

Results are shows in yyyy-mm-dd format.

The above query uses the system internal table sys.server_principals to determine the sp.create_date (sql server installed date) based on the where condition which is filtering on name ‘NT AUTHORITY\SYSTEM’ service which gets installed while installing SQL server 2012 for the first time.

After using SQL server 2012 enterprise evaluation edition for 180 days you may receive ‘SQL server 2012 evaluation period has expired’ error. To fix this issue you need to obtain a valid license product key which you can buy from Microsoft official site.

SQL Server 2012: Logical Functions (IIF(), CHOOSE())

SQL Server 2012 has introduced the new logical function IIF(). The behavior of function IIF() is quite similar to CASE and IF statements in SQL Server. Using IIF(), you can use fewer lines of code, and your code will be more readable.

Let’s explore the IIF() function with some examples.

IIF() – Function returns one of the two values, depending upon whether the specified boolean expression evaluates to true or false.

Syntax : IIF ( boolean_expression, true_value, false_value )

• The first argument (boolean_expression) accepts the boolean expression to be evaluated.
• Function returns second argument (true_value), if boolean expression evaluates to true.
• Function will return third argument (false_value) means boolean expression has failed.

Let’s see some quick SQL queries with function IIF()

Example 1.1 : Compare two numbers with IIF()

SELECT IIF(1 > 0,'True','False') AS 'Output'
GO

Output
------
True

Example 1.2: Compare two dates with IIF()

SELECT IIF('10/15/2012' > '01/01/2012','Yes','No') AS 'Output'
GO

Output
------
Yes

Example 1.3: Compare two integer variables with IIF()

DECLARE @num1 AS INT = 150
DECLARE @num2 AS INT = 100

SELECT IIF( @num1 < @num2, 'True', 'False') AS 'Output'
GO

Output
------
False

Example 1.4: Compare two strings with IIF()

Most of you are wondering why we should use IIF() over CASE and IF statements.

IIF() looks more readable, is simpler to craft, and has fewer line of codes compared using CASE and IF statements.

Let us contrast the various methods of comparing two strings by using IF, CASE, and IIF() Function.

DECLARE @str as varchar(20) = 'tech-recipes'
if(@str = 'tech-recipes') 
select 'Yes' AS 'OUTPUT'
ELSE
select 'No' AS 'OUTPUT'
GO

OUTPUT
------
Yes

DECLARE @str AS varchar(20) = 'tech-recipes'
select CASE when @str='tech-recipes' 
THEN 'Yes'
ELSE 'No'
END AS 'Output'
GO

OUTPUT
------
Yes

DECLARE @str as varchar(20) = 'tech-recipes'
select IIF(@str = 'tech-recipes', 'yes', 'no') as OUTPUT
GO

OUTPUT
------
Yes

The above three queries using IF, CASE, and IIF() Function do the same thing and return the same result. However, using IIF() is easier and more readable.

SQL Server 2012: How to Use CHOOSE() Logical Function

SQL Server 2012 introduced the powerful new CHOOSE() function. This tech-recipe will explore its use and common mistakes through some simple examples.

The CHOOSE() function returns the item at a specified index. The behavior is the same as the list of items in array and uses array index to get the item at specified index.

Syntax

Syntax: CHOOSE ( index, val_1, val_2 [, val_n ] )

• Index argument accepts integer expression, and it has to start with 1-based index.
• Val_1 to val_n list of items.

Examples

Example 1.1 – CHOOSE() with index

SELECT CHOOSE(0 ,'tech', 'recipes', 'cookbook' ) AS 'index_as_0'
GO

index_as_0
----------
NULL

This shows NULL because CHOOSE() accepts index arguments and must start with one or a value greater than one.

Example 1.2 – CHOOSE() with valid index value

SELECT CHOOSE( 1 ,'tech' ,'recipes' ,'cookbook' ) AS 'index_as_1'
GO

index_as_1
----------
tech

This returns the ‘tech’ item as result from the value list because we have passed one as index argument.

Example 1.3 – CHOOSE() with index greater than number of items in the list

SELECT CHOOSE(4,'tech', 'recipes', 'cookbook') AS 'index_as_4'
GO

index_as_4
----------
NULL

This returns NULL because we are passing index argument as four, and we do not have any item at the fourth position.

Example 1.4 – CHOOSE() with decimal index

SELECT CHOOSE(2.9,  'tech',  'recipes',  'cookbook') AS 'decimal_index'
GO

decimal_index
-------------
recipes

This does not return any error. It shows ‘recipes’ in result. When you are passing the first argument as 2.9, it converts a decimal value into an integer, treats the decimal value 2.9 as 2, and shows the ‘recipes’ item as the result.

Example 1.5 CHOOSE() with negative index

SELECT CHOOSE(-2, 'tech', 'recipes', 'cookbook') AS 'Negative_index'
GO

Negative_index
--------------
NULL

This results in NULL because you are passing the first argument as a negative value which violates the syntax. Always make sure you always pass the positive integer value.

Example 1.6 CHOOSE() with variable

DECLARE @index AS INT = 3
SELECT CHOOSE(@index, 'tech', 'recipes', 'cookbook') AS 'Index_as_Variable'
GO

Index_as_Variable
-----------------
cookbook

We have declared an int variable @index with value three and have passed the variable as index value. It is showing ‘cookbook’ because it is the third index item in the list.

Choose in SQL Server 2012

Overview

SQL Server 2012 has introduced 2 new logical functions. IIF & Choose.
Let's go over CHOOSE function. It returns a value at specific index from a list. 

Scenarios & Example

•  Basic Use

SELECT CHOOSE(3, 'SQL 2005', 'SQL 2008', 'SQL 2012')

(No column name)

SQL 2012

So we can see that, it gives 3rd value from the set of value we have provided. 
Let's test if we give some unusual index values

SELECT 'CASE 1.1' [Case], CHOOSE(1.1, 'SQL 2005', 'SQL 2008', 'SQL 2012')  [Value]

UNION ALL

SELECT 'CASE 1.9' , CHOOSE(1.9, 'SQL 2005', 'SQL 2008', 'SQL 2012')

UNION ALL

SELECT 'CASE -3' , CHOOSE(-3, 'SQL 2005', 'SQL 2008', 'SQL 2012')

UNION ALL

SELECT 'CASE 5' , CHOOSE(5, 'SQL 2005', 'SQL 2008', 'SQL 2012')

Case	Value
CASE 1.1	SQL 2005
CASE 1.9	SQL 2005
CASE -3	NULL
CASE 5	NULL

•  Advance Use

Let's say, we have a date stored in a table, we want to know whether it is weekday/weekend, then this is how we can write using CHOOSE.

SELECT EmployeeName,EmployeeDOB, DATENAME(DW,EmployeeDOB),DATEPART(DW,EmployeeDOB),

CHOOSE(DATEPART(DW,EmployeeDOB), 'WEEKEND', 'Weekday', 'Weekday', 'Weekday','Weekday', 'Weekday', 'WEEKEND') FROM Employee

EmployeeName	EmployeeDOB	(No column name)	(No column name)	(No column name)
John	4/4/1989	Tuesday	3	Weekday
Rob	5/28/1990	Monday	2	Weekday
Bob	3/16/1991	Saturday	7	WEEKEND
Ryan	12/5/1983	Monday	2	Weekday
Lisa	9/14/1979	Friday	6	Weekday

We can also write the same thing using CASE statement but the only thing CHOOSE will be easier to write than CASE.
w.r.t Performance, there is no difference either you use CASE or CHOOSE, both are same. CHOOSE internally uses CASE only.