Category Archives: SQL Advanced

SQL Triggers – An Introduction

SQL Triggers – An Introduction Contents Introduction Magic Tables Difference between Stored Procedure and Trigger DML Triggers After Triggers Syntax of the After trigger Example of After Trigger for Insert Example of After Trigger for Delete Example of After Trigger … Continue reading

Posted in Most Imp SQL Concepts, SQL Advanced, SQL Server, SQL Triggers | Tagged , , , , | 33 Comments

SQL Script to find all the triggers defined on a database or on a single table

SQL Script to find triggers in SQL Database Sometime we need to find all the triggers defined on the database. So in this case, we can use the below SQL Query:-SELECT tbl.name as [Table Name] , trig.name as [Trigger Name] … Continue reading

Posted in SQL Advanced, SQL Tips, SQL Triggers | Tagged , | 30 Comments

Interview question – Is Clustered index on column with duplicate values possible?

Through this article, we are going to discuss three important interview questions of SQL which are given below:-  1) Can we create clustered index on a column containing duplicate values?  2) Can we create a Primary Key on a table on which a … Continue reading

Posted in Interview Questions, SQL Advanced, SQL Indexes | Tagged , , , | Leave a comment

Importance of Best Practices in database programming

Best Practices in database programming For any programming language, just writing the code is not well enough. It should be written using the best practices. This article Best Practices in database programming  I will try to explain the disadvantages of … Continue reading

Posted in SQL Advanced, SQL Best Practices, SQL Server | Tagged | 32 Comments

SQL Script to find the missing indexes

Script to find the missing indexes Performance tuning in SQL is important exercise and index creation is an important part of it. Sometimes base on the frequent SQL queries, we need to create some indexes or missing indexes which are enhance the … Continue reading

Posted in DBA, SQL Advanced, SQL Indexes, SQL Server | Tagged , | 29 Comments

SQL Script to find the list of all the jobs failed yesterday

One of the important task of any DBA is to find out all the jobs which are failed yesterday. Below SQL Script can be used to find out all the jobs which are failed yesterday. SELECT DISTINCT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) … Continue reading

Posted in DBA, SQL Advanced, SQL Server | Tagged , | Leave a comment

SQL Script to search stored procedures containing a given text

Sometimes we need to find out how many stored procedures contains a given text. Below SQL query can be used to find out the list of all the stored procedures which contains a  particular given text as input. SELECT OBJECT_NAME(object_id), … Continue reading

Posted in DBA, SQL Advanced, SQL Server | Tagged , | 32 Comments

SQL Script to find the tables created from a given date

Below is the SQL query which can be used to find out the tables which are created or modified on or from a given date. SELECT         [name] as Tablename        ,create_date       … Continue reading

Posted in DBA, SQL Advanced, SQL Server | Tagged , | 29 Comments

SQL Script to find the databases size

Sometimes we need to find out the size of the database on a server. Below SQL Scripts can be used to find out the size of all the databases created on the server.  ;WITH DBSize (SqlServerInstanceName, DatabaseName, DatabaseSize, DBLogSize, TotalDBSize) … Continue reading

Posted in DBA, SQL Advanced, SQL Server | Tagged , | Leave a comment

T-SQL script to find the growth size of database files

Below query can be used to see the growth size of database files. DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE @bfn VARCHAR(1000); DECLARE @efn VARCHAR(10); — Get the name of the current default trace SELECT @filename = … Continue reading

Posted in DBA, SQL Advanced, SQL Server | Tagged , | 32 Comments