SQL Server: Maintaining Integrity during Transactions

Data integrity is the most important aspect of anything and everything we do in database.

We developers often tend to ignore couple of things, or take things for granted. One of them is we forgot the Microsoft left it to the developer to decide whether to abort a transaction automatically when there is an error.

For detailed information, go through this link.

Reference : DP (http://dptechnicalblog.blogspot.com/ )

SQL Server: Difference between GetDate() and GetUTCDate()

The difference between GETDATE() and GETUTCDATE() is time zone number of the SQL Server machine.

E.g.

DECLARE @local_time DATETIME;
DECLARE @gmt_time DATETIME;
SET @local_time = GETDATE();
SET @gmt_time = GETUTCDATE();
SELECT 'Server local time: '
   + CONVERT(VARCHAR(40),@local_time);
SELECT 'Server GMT time: '
   + CONVERT(VARCHAR(40),@gmt_time);
SELECT 'Server time zone: '
   + CONVERT(VARCHAR(40),
      DATEDIFF(hour,@gmt_time,@local_time));
GO

Output:
Server local time: Apr 26 2011 09:47PM
Server GMT time: Apr 26 2011  04:17PM
Server time zone: 5

GETDATE = Returns the day of the month for the specified date according to local time.

GETUTCDATE() = Returns the day (date) of the month in the specified date according to universal time.

 Reference: DP(http://dptechnicalblog.blogspot.com )

SQL Server 2008 : Types of T-SQL Functions

There are rich set of different types built-in T-SQL functions available in SQL Server 2008 which can be broadly categorized as below.

Aggregate functions : Aggregate functions are applied to a group of data values from a column and will always return single value.

• AVG
• COUNT
• COUNT_BIG
• MIN
• MAX
• SUM

Scalare functions: Scalare functions are used in the construction of the scalar expressions and they can be operated on one or more columns. These functions can be further categorized as below.

• Numeric functions
• Date functions
• String functions
• System functions
• Metadata functions

 For complete information, go through the link here.

Reference: DP(http://dptechnicalblog.blogspot.com/ )

AdventureWork Data Dictionary

Adventure Works is a sample database which is useful for learning the SQL Server concepts, quick tests and demonstrating our thoughts by using scripts. This database need to be downloaded and seperately installed.

While the AdventureWorks family of databases is easily available from CodePlex, there is no well-known place where we can go to for lookup to the description of the schema and the tables.

A data dictionary is a centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format. Data dictionaries are commonly used to circulate copies of the database schema to vendors and technology partners, and once a product is released, this may be made available to the end customers depending upon the need (eg. to allow for customization or study).

You can get the data dictionary for AdventureWorks sample databases at: http://technet.microsoft.com/en-us/library/ms124438(SQL.100).aspx

Reference : DP (http://dptechnicalblog.blogspot.com/ )

3 Different Methods to Create Primary Keys

Primary key is unique identifier for each record in the database table and creates clustered index on the column on which its defined.

--Cleanup
IF EXISTS ( SELECT OBJECT_ID('constraintTable'))
DROP TABLE constraintTable
GO

CREATE TABLE constraintTable
(ID INT CONSTRAINT ct_ID PRIMARY KEY, Col2 VARCHAR(20))
GO

--Cleanup
IF EXISTS ( SELECT OBJECT_ID('constraintTable'))
DROP TABLE constraintTable
GO

--Primary Key upon table creating Method2
CREATE TABLE constraintTable
(ID INT, Col2 VARCHAR(20) CONSTRAINT ct_ID PRIMARY KEY)
GO

--Cleanup
IF EXISTS(SELECT OBJECT_ID('constraintTable'))
DROP  TABLE constraintTable
GO

--Primary Key upon table creating Method3
CREATE TABLE constraintTable
(ID INT, Col2 VARCHAR(20))
GO

--Primay key doesn't allow NULL values
ALTER TABLE constraintTable
ALTER COLUMN ID INT NOT NULL
GO

ALTER TABLE constraintTable
ADD CONSTRAINT Ct_ID PRIMARY KEY (ID)
GO

Reference: DP (http://dptechnicalblog.blogspot.com/ )

SSIS - Types of Transformations

SSIS transformation tasks can use memory or buffer in different ways. The way transformations use memory can impact the performance of your SSIS package dramatically. Transformations buffer usage can be categorised as below.

• Non blocking transformations
• Partially blocking transformations
• Full Blocking transformations

In general if you can avoid using fully blocking and partial blocking transformations, your SSIS package will perform better. The transfromations can be classified as below

Non blocking transformations

• Audit
• Cache Transform
• Character Map
• Conditional Split
• Copy Column
• Data Conversion
• Devired Column
• Export Column
• Import Column
• Lookup
• Multicast
• OLE DB Command
• Percentage Sampling
• Script Component
• Slowly Changing Dimesion

Partial blocking transformations

•  Data Mining
• Merge
• Merge Join
• Pivot
• Unpivot
• Term Lookup

Fully Blocking Transformations

•  Aggregate
• Fuzzy grouping
• Fuzzy lookup
• Row Sampling
• Sort
• Term Extraction

If you clearly observe Sort is a fully blocking transformation, so its better to sort your data using the SQL command in OLE DB Source instead of using Sort transformation. Merge tranform requires Sort but not Union All, so use Union All wherever possible.

Hope this post helps in analysing the performance bottle necks in your SSIS package and also while developing new SSIS package for performance tuning.

Reference: DP (http://dptechnicalblog.blogspot.com/ )