What are the Different Types of Triggers?

There are two types of Triggers.

1) DML Trigger

... There are two types of DML Triggers

1.Instead of Trigger
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.

2. After Trigger
After triggers execute following the triggering action, such as an insert, update, or delete.

2) DDL Trigger

This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.

(Read more here http://ow.ly/gbwHq )

What is a Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be considered to be similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

(Read more here http://ow.ly/gbwDn )

What is a Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.
http://ow.ly/gbwsv

When is the use of UPDATE_STATISTICS command?

This command is basically used when a large amount of data is processed. If a large amount of deletions, modifications or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

(Read more here http://ow.ly/g464c )

What is Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.
http://ow.ly/fXrpS

What is BCP? When is it Used?

BCP or BulkCopy is a tool used to copy huge amounts of data from tables and views. BCP does not copy the complete structures from source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

What is the Difference between a Local and a Global Temporary Table?

A local temporary table exists only for the duration of a connection, or if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database accessible across the connections. Once the connection where original global table is declared dropped this becomes unavailable.

SQL SERVER – Fix: Error : 402 The data types ntext and varchar are incompatible in the equal to operator

December 4, 2012 by pinaldave

Some errors are very simple to understand but the solution of the same is not easy to figure out. Here is one of the similar errors where it clearly suggests where the problem is but does not tell what is the solution. Additionally, there are multiple solutions so developers often get confused with which one is correct and which one is not correct.

Let us first recreate scenario and understand where the problem is. Let us run following

USE Tempdb
GOCREATE TABLE TestTable (ID INT, MyText NTEXT)GOSELECT ID, MyTextFROM TestTableWHERE MyText = 'AnyText'GODROP TABLE TestTable
GO

When you run above script it will give you following error.

Msg 402, Level 16, State 1, Line 1
The data types ntext and varchar are incompatible in the equal to operator.

One of the questions I often receive is that voucher is for sure compatible to equal to operator, then why does this error show up. Well, the answer is much simpler I think we have not understood the error message properly. Please see the image below. The next and varchar are not compatible when compared with each other using equal sign.

Now let us change the data type on the right side of the string to nvarchar from varchar. To do that we will put N’ before the string.

USE Tempdb
GOCREATE TABLE TestTable (ID INT, MyText NTEXT)GOSELECT ID, MyTextFROM TestTableWHERE MyText = N'AnyText'GODROP TABLE TestTable
GO

When you run above script it will give following error.

Msg 402, Level 16, State 1, Line 1
The data types ntext and nvarchar are incompatible in the equal to operator.

You can see that error message also suggests that now we are comparing next to nvarchar. Now as we have understood the error properly, let us see various solutions to the above problem.

Solution 1: Convert the data types to match with each other using CONVERT function.

Change the datatype of the MyText to nvarchar.

SELECT ID, MyTextFROM TestTableWHERE CONVERT(NVARCHAR(MAX), MyText) = N'AnyText'GO

Solution 2: Convert the data type of columns from NTEXT to NVARCHAR(MAX) (TEXT to VARCHAR(MAX)

ALTER TABLE TestTableALTER COLUMN MyText NVARCHAR(MAX)GO

Now you can run the original query again and it will work fine.

Solution 3: Using LIKE command instead of Equal to command.

SELECT ID, MyTextFROM TestTableWHERE MyText LIKE 'AnyText'GO

Well, any of the three of the solutions will work. Here is my suggestion if you can change the column data type from ntext or text to nvarchar or varchar, you should follow that path as text and ntext datatypes are marked as deprecated. All developers any way to change the deprecated data types in future, it will be a good idea to change them right early.

If due to any reason you can not convert the original column use Solution 1 for temporary fix. Solution 3 is the not the best solution and use it as a last option. Did I miss any other method? If yes, please let me know and I will add the solution to original blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2005 – Retrieve Any User Defined Object Details Using sys.objects Database

March 16, 2008 by pinaldave

sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database.

Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of foreign key, name of the table it FK belongs and the schema owner name of table.
USE AdventureWorks;GOSELECT name AS ObjectName,OBJECT_NAME(schema_id) SchemaName,OBJECT_NAME(parent_object_id) ParentObjectName, name, *FROM sys.objectsWHERE type = 'F'GO
You can use any of the following in your WHERE clause and retrieve necessary information.

Object type:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL – sys.objects

SQL SERVER – Monitoring SQL Server Database Transaction Log Space Growth – DBCC SQLPERF(logspace) – Puzzle for You

July 3, 2012 by pinaldave

First of all – if you are going to say this is very old subject, I agree this is very (very) old subject. I believe in earlier time we used to have this only option to monitor Log Space. As new version of SQL Server released we all equipped with DMV, Performance Counters, Extended Events and much more new enhancements. However, during all this year, I have always used DBCC SQLPERF(logspace) to get the details of the logs. It may be because when I started my career I remember this command and it did what I wanted all the time.

Recently I have received interesting question and I thought, I should request your help. However, before I request your help, let us see traditional usage of DBCC SQLPERF(logspace).

Every time I have to get the details of the log I ran following script. Additionally, I liked to store the details of the when the log file snapshot was taken as well so I can go back and know the status log file growth. This gives me a fair estimation when the log file was growing.

CREATE TABLE dbo.logSpaceUsage(id INT IDENTITY (1,1),logDate DATETIME DEFAULT GETDATE(),databaseName SYSNAME,logSize DECIMAL(18,5),logSpaceUsed DECIMAL(18,5),[status] INT)GOINSERT INTO dbo.logSpaceUsage(databaseName, logSize, logSpaceUsed, [status])EXEC ('DBCC SQLPERF(logspace)')GOSELECT *FROM dbo.logSpaceUsage
GO

I used to record the details of log file growth every hour of the day and then we used to plot charts using reporting services (and excel in much earlier times). Well, if you look at the script above it is very simple script. Now here is the puzzle for you.

Puzzle 1: Write a script based on a table which gives you the time period when there was highest growth based on the data stored in the table.

Puzzle 2: Write a script based on a table which gives you the amount of the log file growth from the beginning of the table to the latest recording of the data.

You may have to run above script at some interval to get the various data samples of the log file to answer above puzzles. To make things simple, I am giving you sample script with expected answers listed below for both of the puzzle.

Here is the sample query for puzzle:

-- This is sample query for puzzleCREATE TABLE dbo.logSpaceUsage(id INT IDENTITY (1,1),logDate DATETIME DEFAULT GETDATE(),databaseName SYSNAME,logSize DECIMAL(18,5),logSpaceUsed DECIMAL(18,5),[status] INT)GOINSERT INTO dbo.logSpaceUsage(databaseName, logDate, logSize, logSpaceUsed, [status])SELECT 'SampleDB1', '2012-07-01 7:00:00.000', 5, 10, 0UNION ALLSELECT 'SampleDB1', '2012-07-01 9:00:00.000', 16, 10, 0UNION ALLSELECT 'SampleDB1', '2012-07-01 11:00:00.000', 9, 10, 0UNION ALLSELECT 'SampleDB1', '2012-07-01 14:00:00.000', 18, 10, 0UNION ALLSELECT 'SampleDB3', '2012-06-01 7:00:00.000', 5, 10, 0UNION ALLSELECT 'SampleDB3', '2012-06-04 7:00:00.000', 15, 10, 0UNION ALLSELECT 'SampleDB3', '2012-06-09 7:00:00.000', 25, 10, 0
GO

Expected Result of Puzzle 1

You will notice that there are two entries for database SampleDB3 as there were two instances of the log file grows with the same value.

Expected Result of Puzzle 2

Well, please a comment with valid answer and I will post valid answers with due credit next week. Not to mention that winners will get a surprise gift from me.

Reference: Pinal Dave (http://blog.sqlauthority.com)

What is the Difference between a Function and a Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

What is OLAP?

OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

SQL SERVER – Denali – 14 New Functions – A Quick Guide

September 21, 2011 by pinaldave

Last two weeks I have written various blog post on new functions introduced in SQL Server Denali. So many comments and request I have received from various readers that they would like to see everything together. I have put up a quick guide here where I am writing all the 14 new functions linking them to my blog post as well Book On-Line for a quick reference.

SQLAuthority.com	Book On-Line
Conversion functions
PARSE	PARSE
TRY_CONVERT	TRY_CONVERT
TRY_PARSE	TRY_PARSE
Date and time functions
DATEFROMPARTS	DATEFROMPARTS
DATETIME2FROMPARTS	DATETIME2FROMPARTS
DATETIMEFROMPARTS	DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS	DATETIMEOFFSETFROMPARTS
EOMONTH	EOMONTH
SMALLDATETIMEFROMPARTS	SMALLDATETIMEFROMPARTS
TIMEFROMPARTS	TIMEFROMPARTS
Logical functions
CHOOSE	CHOOSE
IIF	IIF
String functions
CONCAT	CONCAT
FORMAT	FORMAT

I have personally bookmarked this post for my future reference.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – String Function – CONCAT() – A Quick Introduction

September 16, 2011 by pinaldave

In SQL Server Denali, there are two new string functions being introduced, namely:

CONCAT()
FORMAT()

Today we will quickly take a look at the CONCAT() function. CONCAT takes a minimum of two arguments to concatenate them, resulting to a single string.

Now let us look at these examples showing how CONCAT() works:

Example 1: CONCAT Function Usage

SELECT CONCAT(1, 2, 3, 4) AS SingleStringSELECT CONCAT('One',1, 1.1, GETDATE()) AS SingleStringSELECT CONCAT('One',2,NULL) AS SingleStringSELECT CONCAT('','','','') AS SingleStringSELECT CONCAT(NULL, NULL) AS SingleString

Now let us observe a few things based on the result above. Earlier when we had to concat strings, we used ‘+’ sign and always CAST/CONVERT any variable to string. It used to give us an error. However, when you look at this new one function, it automatically and implicitly CAST/CONVERT any datatype to integer and then CONCATs them together in a single string. NULL values are automatically converted to empty strings. If you notice that even the datetime fields are automatically converted to the string without any extra operations. Additionally, the return value from the CONCAT string could be of datatype VARCHAR(MAX).

Example 2: Usage of CONCAT with Table

USE [AdventureWorks2008R2]
GOSELECT CONCAT([AddressID],' ',[AddressLine1],' ',[AddressLine2],' ',[City],' ',[StateProvinceID],' ',[PostalCode]) AS AddressFROM [Person].[Address]
GO

In the following example, we see the result of the query listed above:

You can see how neatly and easily the strings are concatenated using this new function. It takes out lots of unnecessary code and makes it much simpler to execute.

Now let us look under the hood in the execution plan. In the execution plan we can see that CONCAT function is a scalar operation. When we look at the scalar operation using Properties, it shows that the CONVERT_IMPLICIT function is automatically called to convert non-nvarchar datatypes columns to NVARCHAR.

Overall, when I have to concat multiple values and data in the future, I am going to use the CONCATE() function.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix Error: Microsoft OLE DB Provider for SQL Server error ’80040e07′ or Microsoft SQL Native Client error ’80040e07′

December 3, 2012 by pinaldave

I quite often receive questions where users are looking for solution to following error:

Microsoft OLE DB Provider for SQL Server error ’80040e07′
Syntax error converting datetime from character string.

OR

Microsoft SQL Native Client error ’80040e07′
Syntax error converting datetime from character string.

If you have ever faced above error – I have a very simple solution for you.

The solution is being very check date which is inserted in the datetime column. This error often comes up when application or user is attempting to enter an incorrect date into the datetime field. Here is one of the examples – one of the reader was using classing ASP Application with OLE DB provider for SQL Server. When he tried to insert following script he faced above mentioned error.

INSERT INTO TestTable (ID, MyDate)
VALUES (1, '01-Septeber-2013')

The reason for the error was simple as he had misspelled September word. Upon correction of the word, he was able to successfully insert the value and error was not there. Incorrect values or the typo’s are not the only reason for this error. There can be issues with cast or convert as well. If you try to attempt following code using SQL Native Client or in your application you will also get similar errors.

SELECT CONVERT (datetime, '01-Septeber-2013', 112)

The reason here is very simple, any conversion attempt or any other kind of operation on incorrect date/time string can lead to the above error. If you not using embeded dynamic code in your application language but using attempting similar operation on incorrect datetime string you will get following error.

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Remember: Check your values of the string when you are attempting to convert them to string – either there can be incorrect values or they may be incorrectly formatted.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Quick Trick about SQL Server 2012 CONCAT function

December 20, 2011 by pinaldave

Just a day before I was presenting at Virtual Tech Days and I wanted to demonstrate the current time to audience using SQL Server Management Studio, I ended up a quick error. If any of you ever tried to concat multiple values of different datatype this should not be surprise to you.

SELECT 'Current Time ' + GETDATE()

I quickly modified script to following workaround and my script worked right away.

SELECT 'Current Time ' + CAST(GETDATE() AS VARCHAR(20))

Current Time Dec 20 2011  7:00PM

However, I instantly realized that I can use SQL Server 2012 function which can right fit for this situation.

SELECT CONCAT('Current Time ', GETDATE())

I have previously written in detail article about SQL Server 2012 CONCAT function over here additionally, I have written quick guide on 14 new functions of SQL Server Denali.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

What is Standby Servers?

Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. This mechanism usually involves the process of moving backup from the primary server to the secondary server using T-SQL scripts. Often, database wizards are used to set up this process.

What is Fact table?

Fact table contains measurements of business processes also fact table contains the foreign keys for the dimension tables. For example, if your business process is “paper production” then “average production of paper by one machine” or “weekly production of paper” would be considered as measurement of business process.

What are the different index configurations a table can have?

A table can have one of the following index configurations:

No indexes
...

A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes