June 2013 | SansSQL

Wednesday, June 26, 2013

Delete Vs Truncate

Delete Truncate
Delete is DML Command Truncate is a DDL Command.
Delete Operation will not Reset Identity property of the column Truncate Operation will Reset the Identity Property to the seed value that is defined for the column
Filters can be specified for Delete Operation (Where Condition) Filters cannot be specified for Truncate Operation
Delete can be rolled back Truncate can be rolled back if used within a Transaction
Delete statement uses row lock and each row is locked for deletion Truncate statement uses Table or page Lock
Delete Operation is slower when compared to truncate because it logs an entry in transaction log for each deletion Truncate Operation is faster because it is minimally Logged in transaction Log 
Delete statements can activate triggers when defined Triggers cannot be defined for Truncate Operation

Tuesday, June 11, 2013

Create and schedule SQL Server jobs with SSIS Packages

In my previous post, we have seen how to use SQL Server Import and export wizard to import or export data and save the package.
When you save the package that is generated using this wizard, it is actually an SSIS package.
You can also use Visual Studio to create complex SSIS packages which performs the data extraction, transformation, maintenance of database, cleanup, etc...

Monday, June 10, 2013

Import and Export Data in SQL Server

Importing or exporting data is a very frequently performed task to copy the data between different databases. When I say different databases, it can be the databases which is present in the same server or on different servers.
There are many ways to import or export data, like using Queries, in-built tools, 3rd party tools, etc...
One of the easy way using the inbuilt tools is by using the "SQL Server Import and Export Wizard"
Using SQL Server Import and Export Wizard, it is easy to select the type of source and destination and other options like Database, table, etc...

Friday, June 7, 2013

Renaming Filegroup in SQL Server

Renaming of a filegroup is SQL server can be done using T-SQL scripts as demonstrated below.
SELECT name AS BeforeRename FROM sys.filegroups

ALTER DATABASE SansSQL 
MODIFY FILEGROUP Secondary NAME=SansSQLFG

SELECT name AS AfterRename FROM sys.filegroups

Monday, June 3, 2013

Client Side and Server Side Tracing in SQL Server

What comes to your mind first when someone says or talks about the performance tuning?
Obviously, it will be Profiler and Tracing.
Profiler is a tool which is used to trace and store the events that are happening on a particular database or on a server overall. The result of this tool can be stored either as a file or in a table.
When you open the profiler and run the trace against a database it is referred to Client side tracing, this is because the profiler being a client tool and even when the profiler is run within the server, it is also referred as Client Side Tracing.