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

SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

March 11, 2009 by pinaldave

More than a year ago I had written article SQL SERVER – Union vs. Union All – Which is better for performance? I have got many request to update this article. It is not fair to update already written article so I am rewriting it again with additional information.

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Run following script in SQL Server Management Studio to see the result between UNION ALL and UNION. Download complete script from here.

/* Declare First Table */DECLARE @Table1 TABLE (ColDetail VARCHAR(10))INSERT INTO @Table1SELECT 'First'UNION ALLSELECT 'Second'UNION ALLSELECT 'Third'UNION ALLSELECT 'Fourth'UNION ALLSELECT 'Fifth'/* Declare Second Table */DECLARE @Table2 TABLE (ColDetail VARCHAR(10))INSERT INTO @Table2SELECT 'First'UNION ALLSELECT 'Third'UNION ALLSELECT 'Fifth'/* Check the data using SELECT */SELECT *FROM @Table1SELECT *FROM @Table2/* UNION ALL */SELECT *FROM @Table1UNION ALLSELECT *FROM @Table2/* UNION */SELECT *FROM @Table1UNION
SELECT *FROM @Table2GO

In our example we have two tables: @Table1 and @Table2.

Now let us run UNION ALL and UNION together and see the resultset as well as Execution Plan compared to complete set of query. You can always turn on actual execution plan using CTRL+M.

We can see from the resultset of UNION ALL that it returns everything from both the table but from UNION it is very clear that only DISTINCT rows from both the table is only retrieved.

Additionally, when comparing the execution plan of UNION ALL and UNION it is also quite clear that UNION ALL is way less expensive than UNION as it does not have DISTINCT SORT operation.

Let me know what do you think about this article. If you have any suggestion for improvement please let me know and I will update articles according to that.

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

SQL SERVER – 2012 – All Download Links in Single Page – SQL Server 2012

March 11, 2012 by pinaldave

SQL Server 2012 RTM is just announced and recently I wrote about all the SQL Server 2012 Certification on single page. As a feedback, I received suggestions to have a single page where everything about SQL Server 2012 is listed. I will keep this page updated as new updates are announced.

Microsoft SQL Server 2012 EvaluationMicrosoft SQL Server 2012 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization.

Microsoft SQL Server 2012 ExpressMicrosoft SQL Server 2012 Express is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications.

Microsoft SQL Server 2012 Feature PackThe Microsoft SQL Server 2012 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft SQL Server 2012.

Microsoft SQL Server 2012 Report BuilderReport Builder provides a productive report-authoring environment for IT professionals and power users. It supports the full capabilities of SQL Server 2012 Reporting Services.

Microsoft SQL Server 2012 Master Data Services Add-in For Microsoft ExcelThe Master Data Services Add-in for Excel gives multiple users the ability to update master data in a familiar tool without compromising the data’s integrity in Master Data Services.

Microsoft SQL Server 2012 Performance Dashboard ReportsThe SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio.

Microsoft SQL Server 2012 PowerPivot for Microsoft Excel® 2010Microsoft PowerPivot for Microsoft Excel 2010 provides ground-breaking technology; fast manipulation of large data sets, streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft SharePoint.

Microsoft SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Technologies 2010The SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint 2010 technologies allows you to integrate your reporting environment with the collaborative SharePoint 2010 experience.

Microsoft SQL Server 2012 Semantic Language StatisticsThe Semantic Language Statistics Database is a required component for the Statistical Semantic Search feature in Microsoft SQL Server 2012 Semantic Language Statistics.

Microsoft ®SQL Server 2012 FileStream Driver – Windows Logo CertificationCatalog file for Microsoft SQL Server 2012 FileStream Driver that is certified for WindowsServer 2008 R2. It meets Microsoft standards for compatibility and recommended practices with the Windows Server 2008 R2 operating systems.

Microsoft SQL Server StreamInsight 2.0Microsoft StreamInsight is Microsoft’s Complex Event Processing technology to help businesses create event-driven applications and derive better insights by correlating event streams from multiple sources with near-zero latency.

Microsoft JDBC Driver 4.0 for SQL ServerDownload the Microsoft JDBC Driver 4.0 for SQL Server, a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5 and 6.

Data Quality Services Performance Best Practices GuideThis guide focuses on a set of best practices for optimizing performance of Data Quality Services (DQS).

Microsoft Drivers 3.0 for SQL Server for PHPThe Microsoft Drivers 3.0 for SQL Server for PHP provide connectivity to Microsoft SQLServer from PHP applications.

Product Documentation for Microsoft SQL Server 2012 for firewall and proxy restricted environmentsThe Microsoft SQL Server 2012 setup installs only the Help Viewer…install any documentation. All of the SQL Server documentation is available online.

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

What are slowly changing dimensions (SCD)?

SCD is abbreviation of Slowly changing dimensions. SCD applies to cases where the attribute for a record varies over time.
There are three different types of SCD.
1) SCD1 : The new record replaces the original record. Only one record exist in database – current data.
...

2) SCD2 : A new record is added into the customer dimension table. Two records exist in database – current data and previous history data.
3) SCD3 : The original data is modified to include new data. One record exist in database – new information are attached with old information in same row.

(Read more here http://bit.ly/sql-interview)

L SERVER – Sends backups to a Network Folder, FTP Server, Dropbox, Google Drive or Amazon S3

November 26, 2012 by pinaldave

Let me tell you about one of the most useful SQL tools that every DBA should use – it is SQLBackupAndFTP. I have been using this tool since 2009 – and it is the first program I install on a SQL server. Download a free version, 1 minute configuration and your daily backups are safe in the cloud.
In summary, SQLBackupAndFTP

• Creates SQL Server database and file backups on schedule
• Compresses and encrypts the backups
• Sends backups to a network folder, FTP Server, Dropbox, Google Drive or Amazon S3
• Sends email notifications of job’s success or failure

SQLBackupAndFTP comes in Free and Paid versions (starting from $29) – see version comparison. Free version is fully functional for unlimited ad hoc backups or for scheduled backups of up to two databases – it will be sufficient for many small customers.

What has impressed me from the beginning – is that I understood how it works and was able to configure the job from a single form (see Image 1 – Main form above)

1. Connect to you SQL server and select databases to be backed up
2. Click “Add backup destination” to configure where backups should go to (network, FTP Server, Dropbox, Google Drive or Amazon S3)
3. Enter your email to receive email confirmations
4. Set the time to start daily full backups (or go to Settings if you need Differential or  Transaction Log backups on a flexible schedule)
5. Press “Run Now” button to test

You can get to this form if you click “Settings” buttons in the “Schedule section”. Select what types of backups and how often you want to run them and you will see the scheduled backups in the “Estimated backup plan” list

A detailed tutorial is available on the developer’s website.

Along with SQLBackupAndFTP setup gives you the option to install “One-Click SQL Restore” (you can install it stand-alone too) – a basic tool for restoring just Full backups.

However basic, you can drag-and-drop on it the zip file created by SQLBackupAndFTP, it unzips the BAK file if necessary, connects to the SQL server on the start, selects the right database, it is smart enough to restart the server to drop open connections if necessary – very handy for developers who need to restore databases often.

You may ask why is this tool is better than maintenance tasks available in SQL Server? While maintenance tasks are easy to set up, SQLBackupAndFTP is still way easier and integrates solution for compression, encryption, FTP, cloud storage and email which make it superior to maintenance tasks in every aspect.

On a flip side SQLBackupAndFTP is not the fanciest tool to manage backups or check their health. It only works reliably on local SQL Server instances. In other words it has to be installed on the SQL server itself. For remote servers it uses scripting which is less reliable. This limitations is actually inherent in SQL server itself as BACKUP DATABASE command  creates backup not on the client, but on the server itself.

This tool is compatible with almost all the known SQL Server versions. It works with SQL Server 2008 (all versions) and many of the previous versions. It is especially useful for SQL Server Express 2005 and SQL Server Express 2008, as they lack built in tools for backup.

I strongly recommend this tool to all the DBAs. They must absolutely try it as it is free and does exactly what it promises. You can download your free copy of the tool from here.

Please share your experience about using this tool. I am eager to receive your feedback regarding this article.

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

SQL SERVER – Changing Default Installation Path for SQL Server

November 11, 2012 by pinaldave

Earlier I wrote a blog post about SQL SERVER – Move Database Files MDF and LDF to Another Location and in the blog post we discussed how we can change the location of the MDF and LDF files after database is already created. I had mentioned that we will discuss how to change the default location of the database. This way we do not have to change the location of the database after it is created at different locations.

The ideal scenario would be to specify this default location of the database files when SQL Server Installation was performed. If you have already installed SQL Server there is an easy way to solve this problem. This will not impact any database created before the change, it will only affect the default location of the database created after the change.

To change the default location of the SQL Server Installation follow the steps mentioned below:

Go to Right Click on Servers >> Click on Properties >> Go to the Database Settings screen

You can change the default location of the database files. All the future database created after the setting is changed will go to this new location.

You can also do the same with T-SQL and here is the T-SQL code to do the same.

USE [master]
GOEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'F:\DATA'GOEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'F:\DATA'GO

What are the best practices do you follow with regards to default file location for your database? I am interested to know them.

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

What is SQL Profiler ?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

(Read more here http://bit.ly/sql-interview)

QL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012

November 25, 2012 by pinaldave

Yesterday we had very first SQL Bangalore User Group meeting and I was asked following question right after the session.
“How do we know if today is a weekend or weekday using SQL Server Functions?”
Well, I assume most of us are using SQL Server 2012 so I will suggest following solution. I am using SQL Server 2012′s CHOOSE function. It is
SELECT GETDATE() Today,DATENAME(dw, GETDATE()) DayofWeek,CHOOSE(DATEPART(dw, GETDATE()), 'WEEKEND','Weekday','Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
GO

You can use the choose function on table as well. Here is the quick example of the same.
USE AdventureWorks2012
GOSELECT A.ModifiedDate,DATENAME(dw, A.ModifiedDate) DayofWeek,CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday','Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
FROM [Person].[Address] A
GO

If you are using an earlier version of the SQL Server you can use a CASE statement instead of CHOOSE function.
Please read my earlier article which discusses CHOOSE function and CASE statements. Logical Function – CHOOSE() – A Quick Introduction
Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – Logical Function – CHOOSE() – A Quick Introduction

September 11, 2011 by pinaldave

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

IIF()
CHOOSE()

Today we will quickly take a look at the CHOOSE() function. This function is very simple and it returns specified index from a list of values. If Index is numeric, it is converted to integer. On the other hand, if index is greater than the element in the list, it returns NULL.

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

Example 1: CHOOSE Usage

SELECT CHOOSE ( 0, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Null;SELECT CHOOSE ( 1, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_First;SELECT CHOOSE ( 2, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Second;SELECT CHOOSE ( 3, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Third;SELECT CHOOSE ( 4, 'TRUE', 'FALSE', 'Unknown' ) AS Result_NULL;

You can see that when index is Zero or greater than the elements in the list,  it returns the value as NULL and it does not return error.

Example 2: Usage of CHOOSE when Index is Not Integer

SELECT CHOOSE ( 1.1, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_First;SELECT CHOOSE ( 2.9, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Second;

You can see that Float value is automatically converted to Integer value and appropriate list value is selected.

Example 3: Usage of CHOOSE with Table

CHOOSE() function can be very useful when it is used in the table as well. In the following example, I am trying to figure out if the day is weekend or weekday using CHOOSE function. There are other ways to figure that out as well, but here in this example I am using that to demonstrate the usage of the CHOOSE function.

USE AdventureWorks2008R2
GOSELECT A.ModifiedDate,DATEPART(dw, A.ModifiedDate) DayofWeek,DATENAME(dw, A.ModifiedDate) DayofWeek,CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday','Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDayFROM [Person].[Address] AINNER JOIN [Person].[StateProvince] SPON SP.StateProvinceID = A.StateProvinceID
GO

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

Example 4: Usage of CHOOSE with Table and CASE statement and performance comparison

Let us re-write the above query with CASE statement first.

USE AdventureWorks2008R2
GOSELECT A.ModifiedDate,DATEPART(dw, A.ModifiedDate) DayofWeek,DATENAME(dw, A.ModifiedDate) DayofWeek,CASE DATEPART(dw, A.ModifiedDate)WHEN 1 THEN 'WEEKEND'WHEN 2 THEN 'Weekday'WHEN 3 THEN 'Weekday'WHEN 4 THEN 'Weekday'WHEN 5 THEN 'Weekday'WHEN 6 THEN 'Weekday'WHEN 7 THEN 'WEEKEND'END  WorkDayFROM [Person].[Address] AINNER JOIN [Person].[StateProvince] SPON SP.StateProvinceID = A.StateProvinceID
GO

The following image demonstrates that the results from both the queries are the same.

Now let us compare the execution plans of both the query – the CHOOSE function query and CASE Statement.

When the execution plans are compared, it is very clear that the cost of both queries is the same. The execution plans also look similar. Let us now examine the properties of the Compute Scalar function for both execution plans.

When the properties of the Compute Scalar function are examined, it is clear that both of them are used at the end CASE statement. Just like IIF function, the CHOOSE function is also the shorthand of the CASE statement.

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

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

(Read more here http://bit.lysql-interview/)

What is 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; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as 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://bit.ly/sql-interview)