How to Save Query Results to Text or Excel File From SSMS - SQL Server Tutorial

In this video you will learn how to save query results in TEXT, Report or Excel format. It explains different options to save query results such as in Text file, in grid and in reporting (.rpt) format. It also shows how to save query results with header (column names) in SQL Server Management studio.

How to Save Query Results to Text or Excel File From SSMS

How to Find out SQL Server Configuration Changes in last Hour - SQL Server DBA Tutorial

This video explain how to find out SQL Server Configuration Changes in last hour, how to find out who and what changed in SQL Server Configurations. It shows multiple ways of looking at the SQL Server configuration changes history, what is default trace in SQL Server, how to enable and disable default trace in SQL Server, best practices to define SQL Server Configuration changes history retention period, location of default trace in SQL Server, how to read default trace event selection using T-SQL Script, how to open default trace in SQL Server Profiler and resolution to the error " Failed to open the trace, Access denied"

Script for the video: How to Find out SQL Server Configuration Changes

SELECT * 
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL12.SQLTEST\MSSQL\Log\log_56.trc', default);
GO

exec sp_executesql @stmt=N'begin try
declare @enable int;
select @enable = convert(int,value_in_use) from sys.configurations where name = ''default trace enabled''
if @enable = 1 --default trace is enabled
begin
        declare @d1 datetime;
        declare @diff int;  
        declare @curr_tracefilename varchar(500); 
        declare @base_tracefilename varchar(500); 
        declare @indx int ;
        declare @temp_trace table (
                textdata nvarchar(MAX) collate database_default 
        ,       login_name sysname collate database_default
        ,       start_time datetime
        ,       event_class int
        );
        
        select @curr_tracefilename = path from sys.traces where is_default = 1 ; 
        
        set @curr_tracefilename = reverse(@curr_tracefilename)
        select @indx  = PATINDEX(''%\%'', @curr_tracefilename) 
        set @curr_tracefilename = reverse(@curr_tracefilename)
        set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';
        
        insert into @temp_trace
        select TextData
        ,       LoginName
        ,       StartTime
        ,       EventClass 
        from ::fn_trace_gettable( @base_tracefilename, default ) 
        where ((EventClass = 22 and Error = 15457) or (EventClass = 116 and TextData like ''%TRACEO%(%''))
        
        select @d1 = min(start_time) from @temp_trace
        
        set @diff= datediff(hh,@d1,getdate())
        set @diff=@diff/24; 

        select (row_number() over (order by start_time desc))%2 as l1
        ,       @diff as difference
        ,       @d1 as date
        ,       case event_class 
                        when 116 then ''Trace Flag '' + substring(textdata,patindex(''%(%'',textdata),len(textdata) - patindex(''%(%'',textdata) + 1) 
                        when 22 then substring(textdata,58,patindex(''%changed from%'',textdata)-60) 
                end as config_option
        ,       start_time
        ,       login_name
        ,       case event_class 
                        when 116 then ''--''
                        when 22 then substring(substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))
                                                                ,patindex(''%changed from%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata)))+13
                                                                ,patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - patindex(''%from%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - 6) 
                end as old_value
        ,       case event_class 
                        when 116 then substring(textdata,patindex(''%TRACE%'',textdata)+5,patindex(''%(%'',textdata) - patindex(''%TRACE%'',textdata)-5)
                        when 22 then substring(substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))
                                                                ,patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata)))+3
                                                                , patindex(''%. Run%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - 3) 
                end as new_value
        from @temp_trace 
        order by start_time desc
end else 
begin 
        select top 0  1  as l1, 1 as difference,1 as date , 1 as config_option,1 as start_time , 1 as login_name, 1 as old_value, 1 as new_value
end
end try 
begin catch
select -100  as l1
,       ERROR_NUMBER() as difference
,       ERROR_SEVERITY() as date 
,       ERROR_STATE() as config_option
,       1 as start_time 
,       ERROR_MESSAGE() as login_name
,       1 as old_value, 1 as new_value
end catch',@params=N''

How to Find out SQL Server Configuration Changes in last Hour

How to Enable or Disable SQL Server Agent Job and How to Start Job at Later days - SQL Server DBA Tutorial

In this video you will learn multiple ways of how to enable and disable SQL Server Agent Job, how to schedule jobs that would run in later hours using SQL Server Management Studio as well as T-SQL script. It also explains how to enable or disable Job Schedule as well as how to update SQL Server Agent Job Schedule.

Scripts to Enable or Disable SQL Server Agent Jobs or Schedule to Start Later on Schedule

--disable Job
USE msdb ;
GO

EXEC dbo.sp_update_job
    @job_name = N'BackupAlldb',
   @enabled = 0 ;
GO

--enable job
EXEC dbo.sp_update_job
    @job_name = N'BackupAlldb',
   @enabled = 1 ;

   --Enable Job Schedule
   EXEC dbo.sp_update_schedule
    @name = 'WeeklyRun',
    @enabled = 0

    -- Disable Job Schedule
      EXEC dbo.sp_update_schedule
    @name = 'WeeklyRun',
    @enabled = 1

    --Start Schedule at Later date (04/04/2015)
    USE [msdb]
GO
EXEC msdb.dbo.sp_update_schedule @name=WeeklyRun, 
        @active_start_date=20150404
GO

How to Enable or Disable SQL Server Agent Job in SQL Server

How to Avoid Using C drive for Database Create in SQL Server - SQL Server DBA Tutorial

In this video you will learn how to avoid using C drive as SQL Server Database data or/and log files. It also explains what are the drawbacks of using C:\ drive as SQL Server database data drive or log drive. You will learn how to look at SQL Server database settings and how to change default database data and log file location. Video explains step by step process of changing SQL Server Database settings along with pre-requisites of these changes to be in effect.

Script used in the video To avoid C drive for database create in SQL Server

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'L:\Data'
GO

Avoid Using C drive for Database Create in SQL Server - DBA Tutorial

How to Temporarily Disable Maintenance Plan in SQL Server - SQL Server DBA Tutorial

In this video you will learn multiple ways of how to disable maintenance plan temporarily using SQL Server Management studio. It also explains how to enable or disable tasks in the Maintenance plans.

How to Temporarily Disable Maintenance Plan in SQL Server

Start SQL Server Profiler Trace Automatically After SQL Server Service Restart - SQL Server DBA Tutorial

In this video you will learn step by step process of starting SQL Server Profiler automatically after SQL Server services restart. It shows how to create a sample trace using SQL Server Profiler (GUI), how to export Trace settings to a file, how to provide trace location, how to create store procedure from the trace definition, how to execute store procedure to verify if trace is running on the SQL Server, how to Mark Store procedure to run at start up in SQL Server, how to enable store procedure startup scan in SQL Server Configuration and finally how to check if the settings are working after SQL Server restarts.

Script used in the video To Automatically Restart SQL Profiler after SQL Server Service Restart

--see if trace is running
select * from sys.traces
--Mark Trace Store Proc startup option True
EXEC sp_procoption 'StoreProcName', 'startup', 'true';
--Verify start option value is 1
USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO





Create proc SPTroubelTrace
/****************************************************/
/* Created by: SQL Server 2014 Profiler          */
/* Date: 03/28/2015  03:44:23 PM         */
/****************************************************/
AS

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
declare @TrcFileName nvarchar(300) 
 
set @TrcFileName = N'C:\STARTTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 
exec @rc = sp_trace_create @TraceID output, 0, @TrcFileName, @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 2, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 3, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 26, @on
exec sp_trace_setevent @TraceID, 11, 34, @on
exec sp_trace_setevent @TraceID, 11, 35, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 26, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 44, 1, @on
exec sp_trace_setevent @TraceID, 44, 3, @on
exec sp_trace_setevent @TraceID, 44, 12, @on
exec sp_trace_setevent @TraceID, 44, 14, @on
exec sp_trace_setevent @TraceID, 44, 22, @on
exec sp_trace_setevent @TraceID, 44, 26, @on
exec sp_trace_setevent @TraceID, 44, 34, @on
exec sp_trace_setevent @TraceID, 44, 35, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 35, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

/****************************************************/
/* Created by: SQL Server 2014 Profiler          */
/* Date: 03/28/2015  04:54:42 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 2, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 3, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 26, @on
exec sp_trace_setevent @TraceID, 11, 34, @on
exec sp_trace_setevent @TraceID, 11, 35, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 26, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 44, 1, @on
exec sp_trace_setevent @TraceID, 44, 3, @on
exec sp_trace_setevent @TraceID, 44, 12, @on
exec sp_trace_setevent @TraceID, 44, 14, @on
exec sp_trace_setevent @TraceID, 44, 22, @on
exec sp_trace_setevent @TraceID, 44, 26, @on
exec sp_trace_setevent @TraceID, 44, 34, @on
exec sp_trace_setevent @TraceID, 44, 35, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 35, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Start SQL Server Profiler Trace Automatically After SQL Server Service Restart

How to Run SQL Server Agent jobs in Batch File - SQL Server DBA Tutorial

In this video you will learn how to run SQL Server Agent Job in a Batch file, it explains step by step process of creating a batch file using SQLCMD, connecting with target SQL Server, database and running SQL Server Store procedure from a batch file. It also shows step by step process of creating and scheduling batch job in windows scheduler instead of using SQL Server Agent Scheduler.

Script used in video to Run SQL Server Agent Job from Batch File

sqlcmd -S TBSSQL\SQLPROD -E -d MSDB -Q "sp_start_job 'Test'"

How to Execute SQL Server Agent Job/s From Batch File - SQL Server DBA Tutorial

How to Create a SQL Server Agent Job that Calls Another Job - SQL Server DBA Tutorial

In this video you will learn step by step process of creating SQL Server agent job that calls another job on same SQL Server instance or remote SQL Server using T-SQL Script. It also explains what is required to connect to remote SQL Server in this scenario (Creating Linked Server) or using power-shell script to connect to remote SQL Server. It also provides a resolution to an error " The server doesn't allow RPC connection or RPC is disabled on the server"

Script to call SQL Server Agent Job inside another SQL Server Agent Job from Same Server or other instance.

--Call another job on same SQL Server
USE msdb 
GO
EXEC dbo.sp_start_job N'BackupAlldb' 
GO

--Call another job on different SQL Server 
-- Requires Linked server 
EXEC [TBSSQL\SQLPROD].[msdb].dbo.sp_start_job N'BackupAlldb'

How to Call SQL Server Agent Job/s from Another SQL Server Agent Job

How to Configure Maximum Number of Concurrent Connections in SQL Server - SQL Server DBA Tutorial

In this video you will learn how to configure Maximum number of concurrent connections in SQL Server using SQL Server Management studio as well as using T-SQL Script. Video also explains step by step process of going through advance options of SQL Server, what is default number of concurrent connection SQL Server allows and pre-requisites of concurrent connection settings to take effect in SQL Server.

Scripts used in the video to Configure Maximum Number of Concurrent Connections in SQL Server

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'user connections', N'200'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

Configure Maximum Number of Concurrent Connections in SQL Server

How to Find Object ( Table.View,Stored Procedure,Function etc.) in All the Database in SQL Server - TSQL Tutorial / SQL Server Tutorial

On daily basis we come across different questions and scenarios as Database developers and DBAs. Sometime users just stop by and they ask us questions and tell us that they have a table in one of the database and if we can take a look for them. When we try to find out the table, we don't see the table.The table might be in different Database and user is not providing correct information.

The below script can be used to find out any object on Entire SQL Server Instance. you can use this script

To find a Table in all the Databases on SQL Server Instance
To find a Stored Procedure in All the Databases on SQL Server Instance
To find a view on entire SQL Server Instance
To find a function in any database on SQL Server Instance.

In short any object can be found by using below script. You only have to provide the name of object and it will return you information with Database Name, Schema Name, Object Name, Type and Type Description. The below code is also one of the example where we need to change the database name in our query and we can use Dynamic SQL to perform that.

--Provide The Object Name such as TableName,Stored Procedure, View,
--Function etc that you find on Entire SQL SERVER INSTANCE
DECLARE @ObjectName VARCHAR(100)
SET @ObjectName='Customer' -- Change the Customer to your ObjectName

------------------------------------------------------------------------------------
    IF OBJECT_ID('tempdb..##Object') IS NOT NULL
        DROP TABLE ##Object

    CREATE TABLE ##Object (
        DatabaseName VARCHAR(500)
        ,ObjectName VARCHAR(500)
        ,SchemaName VARCHAR(100),
        ObjectType VARCHAR(10),
        ObjectDesc VARCHAR(100)
        )

--Use Cursor to Loop through Databases to Find Object in SQL Server Instance Databases
    DECLARE @DatabaseName AS VARCHAR(500)

    DECLARE DBCursor CURSOR
    FOR
    SELECT NAME
    FROM sys.databases
    
    OPEN DBCursor

    FETCH NEXT
    FROM DBCursor
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        
        EXEC (
                N'USE ' + @DBName + N'; EXEC(''
Insert into ##Object
Select DB_Name() AS DatabaseName,
name as ObjectName,
schema_name(schema_id) AS SchemaName,
Type,
type_desc From sys.objects
where is_ms_shipped=0
'');'
                );

        FETCH NEXT
        FROM DBCursor
        INTO @DatabaseName
    END

    CLOSE DBCursor

    DEALLOCATE DBCursor

    --Return the Object Name with DatabaseName,SchemaName,Type and Type Descripton. 
   
    SELECT *
    FROM ##Object
    where ObjectName=@ObjectName

Find object ( Table,Stored Procedure, Function,View etc. in SQL Server)

Video Tutorial : How to Search Object Name on SQL Server Instance 

How to Add a Column with Values to Existing SQL Server Table - TSQL Tutorial / SQL Server Tutorial

We often have to add a new column to existing Table/s. Let's consider we have dbo.Customer table and we are US based company and we never did business outside USA. so we never need Country Column to our existing Table for Address as we only operator in USA. Now company has decided to expand the business and might have the customers from other countries. What we want to do , we want to add column to existing table with default value. All the existing values should be also updated to default value when we add this new column. Moving forward if no Country Name provided, It should take USA otherwise value provided.

--Create Sample Table without CountryName Column
Create table dbo.Customer (
CustomerId Int Identity(1,1),
CustomerName VARCHAR(100),
StreetAddress VARCHAR(100),
City VARCHAR(100),
State CHAR(2))

--Insert couple of Records in Sample Table
Insert into dbo.Customer
Select 'Aamir Shahzad','Test Street Address','Charlotte','NC'
Union
Select 'M Raza','Test Street Address','Charlotte','NC'

Add the new column Without Providing NOT NULL Constraint 

Alter Table dbo.Customer
ADD CountryName VARCHAR(50)
Default 'USA'

Select * From dbo.Customer

Fig 1: Add New Column to Existing SQL Server Table without Providing Not Null Constraint

In above query we did not mention the Not Null constraint for our CountryName column. SQL Server took that by default as Null. Also it did not update the newly added column with default values.

Add New Column With NOT NULL AND Default Constraint

 If we will provide Not Null constraint for the CountryColumn and also default value. The newly added column will be populated with default values as shown below.

Alter Table dbo.Customer
ADD CountryName VARCHAR(50) NOT NULL
Default 'USA'

Select * from  dbo.Customer

Fig 2: Add Not Null Constraint with Default Value To populate existing Column in Table

Now consider the last scenario, In which we want to keep our Column to accept Null values but populate existing records with default and add default constraint.

Add Column To Existing Table With Default Values and Column should accept Null as well. Also populate the Newly Added Column with Default Values at time of Adding Column

Alter Table dbo.Customer
ADD CountryName VARCHAR(50) 
Default 'USA'
WITH Values

Select * from  dbo.Customer

Fig 3: Add New column with Null constraint and default value and populate with Default values

As you can see in last sql script we used "With values" clause. If you use that, the existing records will be populated with default values. Also as we did not provide NOT Null Constraint, our table will accept Null values in case we want to save Null value in column.  If no value will be provided, default value will be inserted to the table.

--Check if Default is working fine
Insert into dbo.Customer(CustomerName,StreetAddress,City,State)
Select 'John Smith','Test Street Address','Charlotte','NC'

--Insert Null CountryName
Insert into dbo.Customer(CustomerName,StreetAddress,City,State,CountryName)
Select 'July River','Test Street Address','Jersey City','NJ',Null

Fig 4: Insert Records SQL Server Table after Adding New Column 

SQL Server Video Tutorial: Watch the Video for Above Scenarios How to Add Column To SQL Table and populate already existing rows with default value.

How to Read Uncommitted Rows from SQL Server Table by using WITH (NOLOCK) Hint - TSQL Tutorial

By default isolation level of SQL Server Database Engine is READ COMMITTED. That mean if we try to select the rows, it will only return us only committed rows.

To Check the Default Isolation level of SQL Sever we can use

DBCC useroptions 

Fig 1: DBCC useroptions output to check Isolation level of SQL Server

Let's consider couple of scenarios where we want to read uncommited data

1. We are deleting alot of records from Table and want to take a look on progress
2. We are inserting a lot of records by using SSIS Package, even the records are not committed, we want to see the progress of load.

OR

Sometime we are preparing our Scripts for deployments and we run the statement and then want to see if script worked as expected before committing the transaction.

WITH (NOLOCK) is hint that we can use to read dirty or uncommitted records.

Let's create a table in one of our Database by using below DDL Script and Insert couple of Records

USE TestDB
go
Create Table dbo.TblCustomer(
Id int,
NAME VARCHAR(100),
Region VARCHAR(100)
)
go
insert into dbo.TblCustomer values (1,'Aamir',NULL)
go
Insert into dbo.TblCustomer values (2,'Raza',Null)

Now let's Begin our Transaction in a connection and update the Region Column with value='NA' as shown below.

BEGIN TRAN
Update dbo.TblCustomer
set Region='NA'

Fig 2: Update the Records by using Begin Transaction in SQL Server

As we can see in Fig 2. Two rows are effected.  As we did not Commit the Transaction. The records are still not committed to the table. Let's open a new query windows (Connection/Session) and try to Select the Rows from dbo.tblCustomer table.

Fig 3: Trying to read data from Table on which we have uncommitted transaction

As we can see that in Fig 3, It is trying to read the database but not returning anything. The reason it is not returning any results because we have Read Commit Isolation level on SQL Server Database Engine and records are not committed those we start updating in Fig 2. The query can only return committed records. 

To read the uncommitted records we can use WITH (NOLOCK) hint and see the dirty or uncommitted rows.

Fig 4: Read uncommitted records by using WITH (NOLOCK) Hint in SQL Server

As we can see that we even did not commit the Transaction that we opened in Fig 2 but by using WITH ( NOLOCK) Hint on Table, we are table to read the dirty or uncommitted records. Now at this point if we feel that our script worked as expected, we can go ahead and commit Transaction by using COMMIT TRAN or we can rollback by using ROLLBACK TRAN.

SQL Server Video Tutorial: How to Read uncommitted Rows/ Dirty Records

How to Import Image File from File System to SQL Server Table - TSQL Tutorial

To save Image file in a SQL Server Table first of all we need to have a column that can handle Image files. VARBINARY is data type that we can use to store Images or any other type of files. Let's create the Table with Varbinary Datatype and then import the image by using below script.

CREATE TABLE dbo.TblPicture_Src (
 ID INT Identity(1, 1)
 ,NAME VARCHAR(100)
 ,Picture VARBINARY(MAX)
 )
GO

--Import Image/File from File System To SQL Server Table
INSERT INTO dbo.TblPicture_Src (
 NAME
 ,Picture
 )
SELECT 'Capture1.PNG'
 ,BulkColumn
FROM Openrowset(BULK 'C:\4_Mypicture.PNG', Single_Blob) AS Picture

SELECT *
FROM dbo.TblPicture_Src

Fig 1: How to Import Files to SQL Server Table by using TSQL in SQL Server

As you can see that we used OpenRowset to import the file to SQL Server table. To make this script work, Change the file path according to your input file.


We can also use SQL Server Integration Services (SSIS) Package to load the image or any other files from file system to SQL Server Table by using Import Column Transformation.

How to Import Files to SQL Server Table by SSIS  Package

How to Concatenate String and Integer Values in SQL Server ( + Sign vs CONCAT ) - TSQL Tutorial

Working with Databases is fun. On daily basis we save data into tables in different columns and often we need to write queries which involve concatenation of multiple columns of different datatypes to produce our final output. In below example we have saved some data into #Customer Table which has Street Address column as string and zip as INT. we want to produce Full Address by concatenating these columns. To concatenate we can use + sign but this works only with String values. So if we have any Integer value/s we have to convert them to String first. We can use Cast or Convert function to convert Integer value to string. Also if there is any Null value among which values we are concatenating , our output will be Null by using + sign. To take care of that part we can use either ISNULL() or Coalesce Function to replace NULL with blank value in SQL Server.

In SQL Server 2012 and Later versions, CONCAT function was introduced. You can concatenate multiple values by using this function. Fun part of this function is you don't have to worry about Null outcome as it will Ignore Null values and also you don't have to worry about converting INT into strings, It will take care of that automatically. So if you are using new versions such as 2012 or later, start taking advantage of this function.

Create table #TempCustomer (
Id int, Name VARCHAR(100),
 StreetAddress VARCHAR(100), 
 Zip Int, 
 City VARCHAR(100))
go
Insert into #TempCustomer 
Select 1,'Aamir','House# 000 Street ABC',28000,'Charlotte'
Union 
Select 2,'Raza','House# 001 Street PJK',45000,'New York'
Union 
Select 2,'Raza','House# 011 Street ATC',7500,'Harisburg'
Union 
Select 2,'Raza','House# 222 Street ZZZ',2500,NULL
Union 
Select 2,'Raza','House# 222 Street ZZZ',Null,'High Points'

--By using Plus ( Sign)
Select StreetAddress+' '+Cast(Zip AS VARCHAR(10))+' '+ City 
AS FullAddress from #TempCustomer

--Have to Take care of Null Values by ourselves by using ISNULL 
Select ISNULL(StreetAddress,'')+' '+ ISNULL(Cast(Zip AS VARCHAR(10)),'')+' '+ 

ISNULL(City,'') 
AS FullAddress from #TempCustomer

--Have to Take care of Null Values by ourselves by using Coalesce 
Select Coalesce(StreetAddress,'')+' '+ Coalesce(Cast(Zip AS VARCHAR(10)),'')+' '+ 

Coalesce(City,'') 
AS FullAddress from #TempCustomer


--USE CONCAT Function in SQL 2012 and Later Version, NULL values will be 
--ignored and we don't have to convert Zip( INT To VARCHAR), It will take care of conversion

Select Concat(StreetAddress,' ',Zip,' ',City) AS FullAddress from #TempCustomer

drop table #TempCustomer

Output from above Queries for String  and Integer concatenation in SQL Server



TSQL Video Tutorial : How to use + Sign or CONCAT Function in SQL Server for Concatenation

How to Determine if value is Numeric by using ISNumeric and Try_Convert Function in SQL Server - TSQL Tutorial

ISNUMERIC( ) function is provided to us in SQL Server to check if the expression is valid numeric type or not. As per Microsoft it should work with Integers and Decimal data types and if data is valid integer or decimal, ISNumeric() should return us 1 else 0. But ISNUMERIC() does not work as expected with some of values specially when we have "-" or "d" in value and have two numbers after "d" such as 123d22, it still return us 1. Also if we have data in money format $XXXXX e.g $2000, It returns us 1.


In SQL Server 2012. Microsoft introduced new function call Try_Convert( ). You can use try_convert function to convert to required data type and if it is not able to convert then it will return Null as output. As you will see below, I did some experiment and found out that Try_Convert will produce 0 for "-" when we try to convert to Int, That should not be happening as "-" is symbol not Integer. But when I try to convert "-" to decimal, Try_Convert produced Null output.

Take a look in below results and keep in mind the outputs when you have to evaluate expression to Numeric or find out if expression is Numeric or Not.

USE TestDB
GO
CREATE TABLE #Customer (ColName VARCHAR(100))
GO
--Insert some Sample Data
INSERT INTO #Customer 
Select '1-2'   Union
Select '54f54' Union
Select '123'   Union 
Select '123.09' Union
Select '-' Union
Select '24d09' Union
Select '$200' Union
Select 'TestData' Union
Select 'Street 123' Union
Select '123$' Union
Select '$234$' Union
Select '1.2' Union
Select '1' Union
Select '234-4' Union 
Select '_'
    
--Use ISNUMERIC AND TRY_Convert Functions to Check if Given Records Are Numeric OR NOT
SELECT ColName
    ,ISNUMERIC(ColName) AS ISNUMERIC_Function_Output
    ,try_convert(DECIMAL(18, 3), ColName) AS DecimalConversion
    ,TRY_CONVERT(INT, ColName) AS INTConversion
    ,CASE 
        WHEN ColName='-' THEN 'Not Numeric'
        WHEN try_convert(DECIMAL(18, 3), ColName) IS NOT NULL
            THEN 'NUMERIC'
        WHEN try_convert(INT, ColName) IS NOT NULL
            THEN 'NUMERIC'
        ELSE 'Not Numeric'
        END AS IsNumericOrNot
FROM #Customer

GO
Drop table #customer 

Fig 1: Using ISNUMERIC()  or Try_Convert Function to evaluate if Expression is Numeric

SQL Server Video Tutorial: Learn Details of ISNUMERIC AND Try_Convert Function

How to Change Database Name inside Stored Procedure in SQL Server - TSQL Tutorial/ SQL Server Tutorial

We often come across different scenarios where we need to switch the database name inside stored procedure. Let's take a look for few of scenarios below

1. We have never introduced Audit Columns to our tables on SQL Server Instance such as ModifiedBy, ModifiedOn. Now we want to add these two columns to all the tables on SQL Server Instance.
2. We want to get the record count for all the tables in SQL Server Instance. That includes any Database on our Current SQL Server Instance.
3. We have enabled CDC on multiple Databases on our SQL Server Instance, we want to get the list of tables from multiple Databases on which CDC is enabled.

In all above scenarios we have to switch Database to get the required results. In our below example we are creating a stored procedure that should return us all the user tables on SQL Server Instance from User databases with record count. To get the record count we have to run our query on each Database. We will be using Dynamic SQL to change the database scope for our query so we can get results from that Database.

You can use Dynamic SQL to change the Database Name and run query against that database as shown in below example.

USE Test
GO

CREATE PROCEDURE dbo.GetTableRecordCountForSQLInstance
AS
BEGIN
    --Drop Temp table if exists to save record count for all the tables on SQL Server Instance
    IF OBJECT_ID('tempdb..##RecordCount') IS NOT NULL
        DROP TABLE ##RecordCount

    CREATE TABLE ##RecordCount (
        DatabaseName VARCHAR(500)
        ,TableName VARCHAR(500)
        ,RecordCount INT
        )

    --Use Cursor to Loop through Databases
    DECLARE @DatabaseName AS VARCHAR(500)

    DECLARE CDCCursor CURSOR
    FOR
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

    OPEN CDCCursor

    FETCH NEXT
    FROM CDCCursor
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To change the Database Name 
        EXEC (
                N'USE ' + @DBName + N'; EXEC(''
Insert into ##RecordCount
Select DB_Name() AS DatabaseName,t.name,s.rows from sysindexes s
inner join sys.tables t
on s.id=t.object_id
and t.is_ms_shipped=0
'');'
                );

        FETCH NEXT
        FROM CDCCursor
        INTO @DatabaseName
    END

    CLOSE CDCCursor

    DEALLOCATE CDCCursor

    --Return DatabaseName,TableName and Record Count from Temp Table
    SELECT *
    FROM ##RecordCount
END

You can create this stored procedure in any of the Database you want. Once you run the Stored Procedure, It is going to get the user databases list and then use Cursor to loop through. Inside the Cursor we are using Dynamic SQL to change the scope of Query to Database by using USE statement.

SQL Server Video Tutorial: Detailed Video how to Change DB Name inside Stored Procedure

Detailed Overview of Model Database in SQL Server - SQL Server DBA Tutorial

In this video you will learn about Model DataBase. Model Database is system Database in SQL Server that is used as template when we create New Database in SQL Server.

In this video you will learn following Items

1. How to Create Objects such as Tables, Views, Functions those we want to Create By default in any newly Create Database by using Model Database
2. What are the restrictions on Model Database
3. What Settings/Configurations are used in TempDB from Model Database
4. Does Create Database Statement take all the settings/Configurations of Model Database
5. Does GUI ( Graphical User Interface) copy the setting from Model for New Database Create Process

What is Model Database in SQL Server - SQL Server Tutorial