Get all the queries that executed in the last 10 seconds...

There are sometimes when you wanted to capture the most recent queries that has hit your database but you don't want to run a heavy duty tool like a profiler or whatever.

DMVs can help you here.

As long as the query is in the cache, you can recover the SQL text for that query.

This query will help you for that

select st.text, qs.last_execution_time
 from msdb.sys.dm_exec_query_Stats qs cross apply msdb.sys.dm_exec_sql_text (qs.sql_handle) st
where qs.last_execution_time > dateadd(ss,-10,getdate())
order by qs.last_execution_time

Note:
dateadd(ss,-10,getdate())  -- This means , All of the queries executed in the last 10 seconds. If you want to get everything in the last one minute, change the condition to this dateadd(mi,-1,getdate())

Query to get the size of All databases in an instance

The following query can list the databases (over 1 GB) in an instance along with their size.

select DatabaseName,
       DBSize
From ( Select db_name(mf.database_id) As DatabaseName
      , CONVERT(varchar(30),convert(int, sum(mf.size) * 8 / 1000)/1000) + ' GB' As DBSize
      , cast(getdate() As date) As SizeDate
   From sys.master_files                mf
Where mf.state = 0
Group By
mf.database_id) a
Where DBSize <> '0 GB'

What an opportunity...!!

Thanks Paul for everything you do to the SQL Server community...!!!

Wow.. Here comes an opportunity to be mentored by THE master of SQL Server...!! I sure am not going to miss this one and will try my best to make use of this great opportunity..!!

About me and How got into IT:
I am from a remote village in India where television and electricity was luxury (in 1996)...!! I studied Mechanical Engineering and was not sure what to do after my graduation as I did not have any job offer in hand when I graduated (in 2000). A software consulting company in India gave me a job offer as a trainee in 2001. I accepted it as it was the best opportunity that life gave me until then, even though I did not have any basic idea about computers or software.

Started working as a tester in mainframes where my responsibility was only to execute the commands given in the documentation and record the results. There were some people in the company who had some computer knowledge. They got opportunity to code in Java or .Net. I used to envy them and felt bad that I am not skilled enough to be considered for those kind of projects. But, I always believed that with proper guidance I can achieve greater heights and be a king of what I am doing. I did not know what I should do to learn more and equip myself to be competent. One good thing that happened to me was that I got an opportunity to come to the US (2003) and work in the client site.

How I got into SQL Server
A few years of doing mainframes testing, I got another opportunity through a friend of mine as he wanted someone to replace his role in his company in (2008) as he was moving on to the next role.  Fortunately it was a Sybase DBA position.

I approached their management and told them I do not have any Sybase or DBA skills. but if they give me an opportunity to learn, I would show them my potential in 2 months. As a trade off I was offered less than what I was making in the previous company. I accepted it as I felt at last I found some light and I can be a DBA soon. They recognized my hardwork after few months by giving me a hike and trusted me to handle their production databases.

Two years later (in 2010), the company was making transition from Sybase to SQL Server and I was the one to implement the migration.  So finally I arrived at the technology that I would learn to make my living...!!!

In SQL Server so far
After working in SQL Server for few months, I felt this product is very user friendly and I should learn more about it. When I searched online on how to improve my SQL Server skills, the first thing that I found was to join the local SQL Server User group and start talking to people about what you do and vice versa, I joined the SQL Server user group and started attending SQL Saturdays and so on... Started writing Blogs (in 2012) as a repository of what I learn..!!

What I have done so far to improve my SQL Server knowledge
1. Attend SQL Server user group meetings.
2. Attend SQL Saturdays
3. Attended Dev connections (in 2012 March)
4. Follow SQL Server experts like you, Kimberly, Brent etc.
5. Follow discussions in SQLServerCentral.com

My Pain points / Obstacles in my learning

1. I am overwhelmed by the volume and variety of things available to learn in front of me.  I am not sure what to take and what to omit.

2. How to retain my knowledge. What I learned today is not available to recollect after a month or so.

3. What are my next steps in my career? Where do I go next..?


My request to  Paul

Before everything,  Thanks so much for your time to read my blog until now...!

Thanks again for your great service to the SQL Server community...!!!

I look up to you as a great person who has mastered the technology that helps me bring food to my family. I would be honored if I get an opportunity to hold your hand and walk in the SQL Server land for few steps.

Thanks again...!!

Getting the Size of the Full Backups in an Instance

If you are planning for a restore of all the database in an instance to another instance, you might be interested in knowing the size of all the backups that you might have to restore.

The following query will help you get the size of all the full backups (if available) in the instance.

select Database_Name, 

       backup_start_date BackupStartDate , 

       convert(numeric(10,2),(compressed_backup_size/1000000)) 'BackupSizeIn MB',

       bmf.physical_device_name

from   msdb..backupset bs inner join 

       msdb..backupmediafamily bmf on bs.media_set_id = bmf.media_set_id

where  type = 'D'

and    backup_start_date = (select  max(backup_start_date) 

                            from    msdb..backupset bs1 

                            where   bs1.type = 'D' 

                            and     bs1.database_name = bs.database_name)

order by convert(numeric(10,2),(compressed_backup_size/1000000)) desc

Estimated Completion Time of SQL Queries (BACKUP/RESTORE)

When you restore or backup a huge database, you might be anxious to know the estimated completion time of that command.

There is a "STATS" parameter that you can use with the command, but it is not dynamic.

The following query can help you in such scenario.

select dr.session_id,      

       dr.command,

       percent_complete,

       dr.start_time,

       dateadd(mi,estimated_completion_time/60000,getdate()) EstimatedCompletionTime,

       ds.text

from  sys.dm_exec_requests dr cross apply sys.dm_exec_sql_text(dr.sql_handle) as ds

where estimated_completion_time > 0

Note that, only following SQL Commands have the percentage value populated. 

·         ALTER INDEX REORGANIZE

·         SHRINK

·         BACKUP DATABASE

·         DBCC COMMANDS

·         RECOVERY

·         RESTORE DATABASE,

·         ROLLBACK

·         TDE ENCRYPTION

Report of All Databases, File Size and Space available.

If you want to get a list of all the databases and their file sizes and available free space, you can use the following script. 

You can also put a wrapper and make it a stored procedure. 

List of all backups from the last full backup

What if one of your database is corrupted and you need to restore all the backups one by one from your last full.

It will be a time consuming and confusing task to get all the backups in order to be restored so as to not break the log chain.

The following script will help you in getting the list of all backups since the last full backup for all the databases in your instance.

It assumes there was atleast one full backup in the last 30 days...if not, you have a bigger problem.

/*

  Author: Siva Ramasamy

 

  Date  : 09/18/2014

  Description: This procedure will return the list of backup files in order from the last fullbackup for the DBs for all the databases.

  Assumption:   This procedure assumes that there was atleast one full backup in the last 30 days. If not :-) you have a bigger problem.

*/

begin

declare @db_name varchar(100)

declare @backup_start_date datetime

declare @most_recent_diffbackup_date datetime

declare @sqlcmd varchar(1000)

--drop table #temp

select bps.database_name     DatabaseName,

    'Full'                BackupType,

    CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

    bps.backup_start_date BackupStartDate,

    bps.backup_finish_date BackupFinishDate,

    CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

       bmf.physical_device_name BackupFileName,

       ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date Desc) BackupOrder

into   #temp

from msdb..backupset bps inner join

  msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

where bps.type = 'D' --  use the Full Backups as the starting point

and   bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

and bps.backup_start_date > DATEADD (DD,-30,getdate()) -- assuming there was atleast 1 good full backup in the last 30 days

delete from #temp where BackupType = 'Full' and  BackupOrder > 1

--select * from #temp

-- Add and Update the Flag for Verification

Alter table #temp add Processed char(1)

Update #temp set Processed = 'N'

While (select count(1) from #temp where backuptype = 'Full' and  Processed = 'N') > 0

begin

  select top (1) @db_name = databasename, @backup_start_date = BackupStartDate from #temp where processed = 'N'

  --print @db_name

 --print @backup_start_date

 insert into #temp (DatabaseName, BackupType, Size, BackupStartDate, BackupFinishDate, DurationOfBackup, BackupFileName,BackupOrder,Processed)

 select  bps.database_name     DatabaseName,

       'Diff'                BackupType,

      CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

      bps.backup_start_date BackupStartDate,

      bps.backup_finish_date BackupFinishDate,

      CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

      bmf.physical_device_name BackupFileName,

   ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date Desc) BackupOrder,

   'P'

    from    msdb..backupset bps inner join

         msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

    where   bps.type = 'I' --  Pull all the differential backups after the full backup

    and     bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

    and     bps.backup_start_date > @backup_start_date

 and     bps.database_name  = @db_name

  delete from #temp where BackupType = 'Diff' and  BackupOrder > 1 and DatabaseName = @db_name

    --get all the log backups after most recent full or differential backup

    insert into #temp (DatabaseName, BackupType, Size, BackupStartDate, BackupFinishDate, DurationOfBackup, BackupFileName, BackupOrder,Processed)

 select  bps.database_name     DatabaseName,

       'Log'                BackupType,

   CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

   bps.backup_start_date BackupStartDate,

   bps.backup_finish_date BackupFinishDate,

      CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

      bmf.physical_device_name BackupFileName,

   ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date) BackupOrder,

   'P'

 from    msdb..backupset bps inner join

   msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

 where   bps.type = 'L' --  Pull all the Log Backups after the most recent Differential Backup

 and     bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

 and     bps.backup_start_date > (select max(BackupStartDate) from #temp where DatabaseName = @db_name)

 and     bps.database_name  = @db_name

 order by backup_start_date

  Update #temp set Processed = 'P' where databasename = @db_name

end

select * from #temp order by databasename, backupstartdate

end

Trancactional Replication - Setup Issues and Solution.

Recently I encountered an issue with setting up Transactional Replication. There are many blog posts about setting up Transactional replication and providing step by step explanations. I followed the exact steps mentioned in them but have been unsuccessful in setting up transactional replication successfully.

After spending some time on it, I figured out that it is due to windows login being used to connect to the publisher and subscriber. The Windows login has sysadmin privilege on the instance and I was able to connect to the instance using that login successfully. But, for some reason, It did not like it to be tied up with replication. So, I changed it to SQL account and it started working..!!

I tried to re-produce this issue by completely deleting the replication and tried again windows credentials..this time it was successful.

But, if you have been having issues with transactional replication/windows login, try changing it to SQL Login.

SQL Server Deadlocks

You are an accidental/junior DBA. One of your developer says he's getting a lot of deadlock messages in the application log suddenly.  He is asking for the DBA's (Your) help.  What are you going to do.  This blog post will help you to handle this issue.

What is deadlock
Deadlock is a situation when two transactions have mutually locked out each other. 

For example, Lets take two transactions
 "TRAN-1",  it has two insert statements,  First insert on Table-A and Second Insert on Table-B
 "TRAN-2" ,  This also has two insert statements, First insert in on Table-B,  Second Insert is on Table-A.

When both of these Transactions execute at the same time,
Tran-1 will finish the insert on Table-A and wait for the lock to be released (acquired by Tran-2) on Table-B. The lock on Table-A will still be active because it is inside a transaction.
Tran-2 would have completed the insert on Table-B but would not have released the lock since the transaction is not commited yet, but will be waiting for the lock to be released for Table-A.

At this point,  The deadlock situation has encountered. 

SQL Server is smart enough to diagnose the deadlock situation and identify one of the processes as victim and kill it so that the other process can continue to execute.

How to analyze deadlock
Deadlock related information will be logged to the errorlog only if the related trace flags are turned on. Otherwise, you won't find any useful info from the errorlog.

The traceflags are 1204 and 1222(After SQL Server 2005).

How to identify if the trace flags are already turned on

DBCC TRACESTATUS command will return the trace flags that are currently turned on.

How to turn on the trace flag.

The following commands will turn the trace flags on.

DBCC TRACEON (1222, -1)
DBCC TRACEON (1204, -1)

What to look for in the error logs

Here is a sample error message from the log

2014-04-11 15:24:22.22 spid4s      Requested by:
2014-04-11 15:24:22.22 spid4s        ResType:LockOwner Stype:'OR'Xdes:0x00000004E9FD8BC0 Mode: U SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000004E9BBC608) Value:0xeb284540 Cost:(0/144)
2014-04-11 15:24:22.22 spid4s     
2014-04-11 15:24:22.22 spid4s      Victim Resource Owner:
2014-04-11 15:24:22.22 spid4s       ResType:LockOwner Stype:'OR'Xdes:0x00000004F08D6D28 Mode: U SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000004E97B8608) Value:0xf5b126c0 Cost:(0/144)
2014-04-11 15:24:22.22 spid18s     deadlock-list
2014-04-11 15:24:22.22 spid18s      deadlock victim=process4f9025498
2014-04-11 15:24:22.22 spid18s       process-list
2014-04-11 15:24:22.22 spid18s        process id=process4f9025498 taskpriority=0 logused=144 waitresource=RID: 8:1:301:0 waittime=5851 ownerId=32694 transactionname=user_transaction lasttranstarted=2014-04-11T15:24:16.370 XDES=0x4f08d6d28 lockMode=U schedulerid=3 kpid=3476 status=suspended spid=58 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-04-11T15:24:16.370 lastbatchcompleted=2014-04-11T15:24:07.050 lastattention=1900-01-01T00:00:00.050 clientapp=Microsoft SQL Server Management Studio - Query hostname=SIVA-PC hostpid=4024 loginname=Siva-PC\Siva isolationlevel=read committed (2) xactid=32694 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2014-04-11 15:24:22.22 spid18s         executionStack
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=4 stmtstart=16 sqlhandle=0x020000006306cb0282580a95a5146f4b2ce8d05ad05f852e0000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE [dbo].[DeadLockTest] set [col1] = @1    
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=4 stmtstart=106 sqlhandle=0x020000000f8d9d36c56b96bdcb04670ca5b75bc17de9868c0000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest SET col1 = 1    
2014-04-11 15:24:22.22 spid18s         inputbuf
2014-04-11 15:24:22.22 spid18s     BEGIN TRAN
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest SET col1 = 1   
2014-04-11 15:24:22.22 spid18s        process id=process4f04fb0c8 taskpriority=0 logused=144 waitresource=RID: 8:1:303:0 waittime=650 ownerId=32286 transactionname=user_transaction lasttranstarted=2014-04-11T15:22:53.877 XDES=0x4e9fd8bc0 lockMode=U schedulerid=2 kpid=4148 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=5 lastbatchstarted=2014-04-11T15:24:21.570 lastbatchcompleted=2014-04-11T15:24:12.090 lastattention=2014-04-11T15:23:49.210 clientapp=Microsoft SQL Server Management Studio - Query hostname=SIVA-PC hostpid=4024 loginname=Siva-PC\Siva isolationlevel=read committed (2) xactid=32286 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2014-04-11 15:24:22.22 spid18s         executionStack
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=1 stmtstart=16 sqlhandle=0x02000000ca356b202147fbe58aa2b109b537cf37cb3083430000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE [dbo].[DeadLockTest2] set [col1] = @1    
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=1 sqlhandle=0x0200000087d432229b1acc5bc82908fa19f6f23bbc0e4e820000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1    
2014-04-11 15:24:22.22 spid18s         inputbuf
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1   
2014-04-11 15:24:22.22 spid18s       resource-list
2014-04-11 15:24:22.22 spid18s        ridlock fileid=1 pageid=301 dbid=8 objectname=ReplB.dbo.DeadLockTest id=lock4edbe2480 mode=X associatedObjectId=72057594039828480
2014-04-11 15:24:22.22 spid18s         owner-list
2014-04-11 15:24:22.22 spid18s          owner id=process4f04fb0c8 mode=X
2014-04-11 15:24:22.22 spid18s         waiter-list
2014-04-11 15:24:22.22 spid18s          waiter id=process4f9025498 mode=U requestType=wait
2014-04-11 15:24:22.22 spid18s        ridlock fileid=1 pageid=303 dbid=8 objectname=ReplB.dbo.DeadLockTest2 id=lock4f5abe780 mode=X associatedObjectId=72057594039894016
2014-04-11 15:24:22.22 spid18s         owner-list
2014-04-11 15:24:22.22 spid18s          owner id=process4f9025498 mode=X
2014-04-11 15:24:22.22 spid18s         waiter-list
2014-04-11 15:24:22.22 spid18s          waiter id=process4f04fb0c8 mode=U requestType=wait


The above given information will be helpful in identifying the transactions involved, the logins involved, time of the deadlock etc.

You can start analysing the issue after gathering these informations.

How to turn off trace flags

Once you have obtained the necessary deadlock information from the log file, you can turn off the trace flags.

Command to turn off the traceflags

DBCC TRACEOFF (1204, -1)
DBCC TRACEOFF (1222, -1)

There is no need to restart the instance for turning these trace flags on/off.

Hope this helps.

Changing Schema/Role Ownership

Though you can change ownership using SSMS, it is always good to know the command to perform the operation.


ALTER AUTHORIZATION ON SCHEMA/ROLE::[name] TO [new owner]

This will also be helpful when you need to change ownership on many objects at a time.

Query to get a combined count of parents (with no children) and children

One of my good friends approached me this morning with this scenario.

1. Parent Id and ChildId are stored in the same table.
2. The relationship goes only one level deep (i.e. a child record is not a parent record for any other child record)

Requirement:
He wanted a combined count of
1. All Parent Ids that do not have any child records
+
2. Count of all Child records.

Here is the script I wrote and gave him.

Hope it is useful to you as well.. :-)



-- CREATE A TABLE FOR OUR SCENARIO
CREATE TABLE T1 (ID INT IDENTITY PRIMARY KEY, PARENT INT REFERENCES T1(ID))
GO 

--POPULATE THE TABLE FOR OUR SCENARIO
-- EXECUTE NEXT 6 INSERT STATEMENTS..THESE 6 STATEMENTS SHOULD CREATE 
-- ABOUT 25 RECORDS OUT OF WHICH 20 RECORDS WILL QUALIFY FOR OUR SCENARIO    
INSERT T1 (PARENT) VALUES (NULL)
GO 10
INSERT INTO T1 (PARENT) VALUES (1)
GO
INSERT INTO T1 (PARENT) VALUES (2)
GO 2
INSERT INTO T1 (PARENT) VALUES (3)
GO 3
INSERT INTO T1 (PARENT) VALUES (4)
GO 4
INSERT INTO T1 (PARENT) VALUES (5)
GO 5

-- DO A SELECT TO MAKE SURE IT LOOKS GOOD.
SELECT * FROM T1
GO

--EXECUTE THE NEXT QUERY THAT GIVES THE RESULTS.
WITH COUNT1(ID) AS 
( SELECT COUNT(DISTINCT a.ID) ID
  FROM T1 a
  WHERE PARENT IS NULL
  AND   NOT EXISTS (SELECT 1 FROM T1 b WHERE a.ID = b.PARENT) 
  UNION
  SELECT COUNT(DISTINCT t2.ID) ID
  FROM T1 t2
  WHERE PARENT IS NOT NULL
 )  
SELECT SUM(ID)
FROM   COUNT1
GO

Basic Database Maintenance Activites

I have been thinking about the core database server maintenance activities that are necessary for a DBA. Here are the basic database maintenance activities that I perform in my servers.

1. Setup Backup Jobs
One of my friends told me once that a DBA needs to have a good backup in place or a good resume in hand..!! Backups are the most important responsibility in a DBA's day to day activities. I usually setup a full backup (once a week or daily depending on the size of the database) and differential backups (only if the full backups are taken once a week) and transaction log backups every hour. I usually retain the backups for 2 weeks (There is also a system level backup happening in my company that is written to tape). I also try to store backups in a separate external hardware from the one where the data files are living..(Though it is not possible always..:-( ). It is also very important to have email notification setup to inform the DBA incase of any failures.

2. Setup Alerts
I usually setup alerts for any SQL Server event with severity 19 or more. This can be easily done using SQL Server Management Studio. I will write a separate post about setting up alerts. The most important thing is to setup email notification when the event happens.

3. Database Integrity Check
A corruption free database means peaceful life for a DBA. I usually check my database consistency once a week. This can also be done using a maintenance plan in SQL Server Management Studio.

4. Index Rebuild / Reorg
A fragmented Index can cause severe performance issues and could be a DBA's nightmare when he is on a hot seat to solve a performance issue. It is always good to be proactive and check the fragmentation level of all the indexes and perform rebuild or reorg as necessary. The thumb rule is to leave the index as is if the fragmentation is less than 10% ReOrg if it is less than 30% and Rebuild if it is more than 30%. Do not use the maintenance task for Index rebuild or Update Statistics if your database is huge. This will cause the job to rebuild all the Indexes in the database which could possibly run for days and would never end. Instead, it can be easily done using a script from the Following Link from books online. This script would only rebuild or reorg an Index if it is necessary.

I am sure there are other important maintenance activities as well. But, according to me, these are the core maintenance activities that any DBA should perform in his/her servers.

Hope this helps..!!

Range Non Existence(^) Searches Using PatIndex

We all know that PatIndex is one of the cool features of T-SQL. It helps to locate the position of the pattern in a given string.

We can also use PatIndex to do range searches. For Example, "[A-Z]" would search for any alphabetical character. "[0-9]" would search for any numeric character.  With the use of these range searches, it gets much easier to find out data issues.

Let us execute the following queries to generate some data for our learning.

  CREATE TABLE #t1 (PATCHECK VARCHAR(20))

  INSERT INTO #T1 (PATCHECK) VALUES ('ABCDEFGHIJKLMN')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA PAT INDEX CHECK')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA 123')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA 123 !@#')

  INSERT INTO #T1 (PATCHECK) VALUES ('123456789')

  INSERT INTO #T1 (PATCHECK) VALUES ('!@#$%^&')

if you select everything from this table, this is how it will look like.

Query  - 0
  SELECT * FROM #T1
    ABCDEFGHIJKLMN
    SIVA PAT INDEX CHECK
    SIVA 123
    SIVA 123 !@#
    123456789
    !@#$%^&


Now, Let us execute a simple PatIndex Search. 

Query-1

  SELECT *
  FROM #T1 
  WHERE PATINDEX('%SIVA%',PATCHECK) > 0 

This query would return any row that has the string "SIVA" in it. The results will look like below.     
   SIVA PAT INDEX CHECK
   SIVA 123
   SIVA 123 !@#

Range Search
Here is the example for a range search.
The below mentioned query means that any row that does not have an alphabetical character. 

Query - 2

  SELECT *
  FROM #T1 
  WHERE PATINDEX('%[A-Z]%',PATCHECK)  = 0

results would look like

  123456789

    !@#$%^&

The below mentioned query means that any row that contains (notice the difference in the operator. Previous one was =0, this one below is > 0) alphabetical character. 

Query - 3

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[A-Z]%',PATCHECK)  > 0

results would look like 

   ABCDEFGHIJKLMN

   SIVA PAT INDEX CHECK

   SIVA 123

   SIVA 123 !@#

Range Search with Not Operator

"Not Operator" is added in front of the range set like this [^A-Z] . This means that any non alphabetical character. 

Query - 4 

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[^A-Z]%',PATCHECK)  > 0

results would look like 

SIVA PAT INDEX CHECK

SIVA 123

SIVA 123 !@#

123456789

!@#$%^&

Note that even though query 2 and Query 4 looks similar, they are not producing the same results. 

Multiple Range Searches 

It is also possible to combine multiple range searches in a single query. The below mentioned query would return any row that does have only alphabets or numeric. 

Query - 5

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[^0-9][^A-Z]%',PATCHECK)  = 0

results would look like. 

ABCDEFGHIJKLMN

123456789

Hope this helps..!!

   

Status of All Constraints in a Database.

One of my developers today reached out to me about knowing the status of the constraints and triggers in a database. He wanted to know whether a constraint was in enabled or disabled state.

The reason behind this question was that he was planning a huge data load in that database and was planning to disable all the constraints and triggers before the load and put the constraints back in the same state after the data load. He wanted to know if some constraints were disabled on purpose and did not want to enable them by mistake after the data load.

Here are some facts about the status change of constrains

• Default, Unique and Primary Key constraints cannot be disabled. 
• Check or Foreign Key constraints can be disabled and enabled.

So, If a default, Unique or Primary Key constraint exists on the database, it can only be in the active state.

The following two queries can give the list of Check and Foreign Key constraints with their status.

SELECT name AS check_constraint_name,

       OBJECT_NAME(parent_object_id) Parent_Object,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.check_constraints

SELECT name AS ForeignKeyName,

       OBJECT_NAME(parent_object_id) AS TableName,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.foreign_keys

I gave him one more query that would give the list of triggers in the database with their status.

SELECT name as trigger_name,

       OBJECT_NAME(parent_id) parent_object,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.triggers

WHERE  OBJECT_NAME(parent_id) IS NOT NULL

 

Hope this helps.

Thanks and Regards,

Siva.