How to find when was SQL Server restarted?

There are few ways to find out when the SQL Server was restarted, here I am going to use DMV (sys.dm_os_sys_info) to find the SQL Server restarted time.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Read More

How to get error description in SSIS dataflow?

In SSIS, there is no direct method to get the error description when error occurred during data Load and transformation.

We can only redirect the failed records along with Error Code to log file (or any destination component). Since the Error Code is not very useful, we may need to store the Error Description along with the failed row. Even though there is no direct mechanism there is a workaround to achieve. The workaround is to add Script Component.

Let’s explore with an example.

Below is the sample package, designed to load Sales Order details from flat file to Sales database. Note the Script Component “SC-GetErrorDesc” placed in between Sales Database and Log Error tasks.

The next screen shows the configuration of Script Component to get the Error Description.

1. Choose the ErrorCode from the available Input columns.

2. Add new output column (ErrDesc) in the Inputs and Outputs section

3. Edit the script

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
Add your code here
*/
Row.ErrDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
}

4. Finally the result from Script component is stored in the flat file. When you map the column you will notice a new columns named ErrDesc in the Available Input Column side, this new column is created and populated within the script component task.

Read More

NULL value for empty field when importing flat file in SSIS

When importing data from text file you may need to apply NULL to the field where no values present (blank). Not doing so the package may fail.

Consider a scenario; you received a text file contains sales order details, it need to be loaded in to database for further reporting or analysis.

The format of CSV file:

---------------------------------------------------------------
OrderId, OrderDate, CustomerId, Qty, UnitPrice
---------------------------------------------------------------

The table definition is:

CREATE TABLE SalesOrderDetails
(
OrderId INT NOT NULL,
OrderDate DATE NOT NULL,
CustomerId INT NOT NULL,
Qty INT NULL,
UnitPrice DECIMAL(10,4) NULL
)

The below image shows how the data in text file, if you look at the Qty field of 3rd row, there is no value present. The corresponding column in the destination table is defined as INT. So, you can’t insert non integer value into Qty column. If you do so, you may end up with the following error message.

Error code: 0x80004005.
Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".

So the blank field needs to be converted to NULL before inserting into table. You can do this by enabling “retain null values from the source as null values in the data flow” setting available in the Flat File Source Editor.

Read More

Usage of @@Error within transaction

@@ERROR returns error number of the last statement executed. When the last executed statement completed successfully, this returns 0. In case of any error, the error number will be return.

Example:

Declare @n int, @d int
Set @n=10
Set @d=0
Select @n/@d
PRINT @@ERROR


I am going to show you a common mistake most of the developer does when using @@ERROR and PRINT, In fact I did so, that’s why I am able write this blog.

Let’s take a look at an example


Create table tblPrintErr(rowId tinyint)
Begin Tran
Insert into tblPrintErr(rowId) values (1)
PRINT @@ERROR
Insert into tblPrintErr(rowId) values (300) // This statement will generate an error as 300 is not a tinyint
PRINT @@ERROR
IF @@ERROR = 0
BEGIN
    COMMIT TRAN
END
ELSE
BEGIN
    ROLLBACK TRAN
END


I am forcefully creating an error in order to roll back the transaction, but what really happened was the transaction got committed.
Let’s see what caused for the transaction to commit instead of roll back.

PRINT @@ERROR statement next to the insert query is what caused the transaction to commit. When IF @@ERROR = 0 statement executed, the last executed statement was PRINT @@ERROR, since this statement executed without error, @@ERROR holds value of 0 and transaction went into commit.

You need to be careful, when you use @@ERROR. In order to avoid the above discussed behaviour. Use local variable to hold the value of @@ERROR.

Example:


Declare @Errno int
Insert into tblPrintErr(rowId) values (300)
Set @Errno = @@ERROR
.
.
.
.
IF @Errno =0
BEGIN
    … … … … 
END
ELSE
BEGIN
    … … … … 
END


CodeProject

Read More

SELECT * Vs. SELECT COLUMNS – SQL Server Optimization Techniques

“Don’t use * in SELECT query, instead use only required column”.

This is one of the tips to optimize SELECT query. However, does this really give better performance in SQL Server query processing?

Selecting only relevant columns doesn’t improve the performance of query processing; however there is an overall performance improvement to the system. For example, when you select only relevant column(s) from a table, the amount of data transferred from database server to frontend server will be less and data get transferred quickly.

In some cases, there will be improvement in query processing too. For example, you are extracting records from a column which is the key column of non-clustered index; in such case, records will be retrieved from non-clustered index page, thus less number of physical database page reads issued. The less physical page read is always good sign for better performance.

Is there any difference in physical page reads when we use SELECT * and SELECT COLUMNS?

“SQL Server by default copy the complete table to buffer pool irrespective of columns selection” – So no matter whether you use * or few columns.

I will elaborate this with an example. Let’s create a table and insert some records.

create table tblBuffer
(
r1 int identity(1,1),
c1 varchar(900) default replicate('a',900),
c2 varchar(900) default replicate('b',900),
c3 varchar(900) default replicate('c',900),
c4 varchar(900) default replicate('d',900),
c5 varchar(900) default replicate('e',900),
c6 varchar(900) default replicate('f',900),
c7 varchar(900) default replicate('g',900),
c8 varchar(900) default replicate('h',900)
) declare @count int
set @count = 1
while @count <= 1000
begin
    insert into tblBuffer default values
    set @count = @count +1
end

Once the table is ready, verify space used by the table. Executing “sp_spaceused tblBuffer” I got below result. Data size is 8000 KB approximately 1000 pages (1 page = 8KB).

Name	Rows	Reserved	Data	Index_size	Unused
tblBuffer	1000	8008 KB	8000 KB	8 KB	0 KB

In order to capture stats of page read operation, I prefer to use the performance monitor (perfmon) with following counters.

SQL Server: Buffer Manager
Database pages - Number of pages in the buffer pool with database content.
Free Pages - Total number of pages on all free lists.
Total Pages - Number of pages in the buffer pool (includes database, free, and stolen).

By now, I have everything setup and ready to do some experiment.

To verify the page read operation, we need to run the SELECT statements, but before that it is good to clean buffer. DBCC DROPCLEANBUFFERS command can be used to clean buffers from the pool.

Let’s execute DBCC DROPCLEANBUFFERS and capture the performance counters value.

(This screen scrap taken from perfmon after execution of DBCC DROPCLEANBUFFERS command)

Now, execute the query “select c1 from tblBuffer” and capture the performance counters values.

(This screen scrap taken from perfmon after execution of select c1 from tblBuffer)

I have taken the performance counter values into below table for clarity.

	After Buffer Cleaned	After query exaction : select c1 from tblBuffer	Difference
Database Pages	700	1719	1019
Free Pages	2866	1839	1024

After execution of query “select c1 from tblBuffer”, the free page has reduced to 1839 from 2866 similarly number of database pages got increased from 700 to 1719. And the difference shown in the above is closely matching with the number of pages of the table.

It clearly shows that, though I select single column, the complete table has been brought into buffer pool.

Next, I will show you an example for table with non-clustered index briefly.

Let’s create a non clustered index on column “c2”

create nonclustered index idx_tblBuffer_c2 on tblBuffer(c2)

The number of pages copied to buffer pool will be approximately 110 when you run this query “select c2 from tblBuffer”... It is because, this time data are copied from non-clustered index “idx_tblBuffer_c2”.

You can verify it in perfmon as we did earlier or use the “set statistics io on” settings to verify the same.

Summary

1. SQL Sever by default copies all the columns to buffer pool, irrespective of column used in the SELECT query. 2. Though you don’t find much performance impact in the SQL Server query processing, it is good to use only the columns you needed as this will increase the overall system performance.

Read More

JOIN – Inner Join and Outer Join

JOIN is one of the fundamental units of relational database system. Generally JOIN is used to combine related data from many tables to produce result.

There are different types of JOINs available,

1. Inner Join
2. Outer Join
3. Cross Join
4. Full Join

In this article, I have used few examples to demonstrate inner and outer joins. before dive into the examples, lets see the definition of inner and outer joins.

Inner Join, The default join in SQL Server is inner join. Inner join returns all matching records from the given datasets. The match is identified using predicates.

Outer join can be classified into Left outer join and Right outer join.

Left outer join returns all records from left table, at same time, it brings all matching rows from right table for the given predicate and NULL appears in right side when no matching row exists.

Left outer join can also be defined as “result set of inner join + missed records from left table with NULL in the right hand side”

Right outer join returns all records from right table, at the same time, it brings all matching rows from left table for the given predicate, and NULL appears in left side when no matching row exists.

Right outer join can also defined as “result set of inner join + missed records from right table with NULL in the left hand side”

Below example used to illustrate the “join”

Let’s assume we have two datasets, one contains the student’s details of class “A” and other contains exam results details. This has been illustrated using Venn diagram, however Venn diagram shouldn’t be used to represent table since a table can have many group of related data. Just to give a clarity and visual representation, I have used Venn diagram.

There are six students in class “A” which represented in the blue circle. But only two students from class “A” passed in exam. The green circle contains students who passed the exam, in this case, two students from Class “A” and one student from other class.

From here, let’s go by some questions.

Q. Find out the students from class “A” who passed in exam?

In order to answer this question, the best suitable join is inner join. Because, we need to extract only the matching records from both datasets. As shown in the figure, each row in the CLASS table is matched with all rows of the EXAM table using student_id predicate. When the match is found the record will be copied down.

The corresponding transact sql code is :

“Select * from
Class a join Exam b
On a.student_id = b.student_id”

Q. Find out the students from class “A” who failed in exam?

This can be answered using the “Left Outer Join”. Though there are different ways to get the result, I will be using left outer join since this article about join.

When joining CLASS and EXAM table using left outer join, we get all the records from CLASS table and matching records from EXAM table and NULL for the unmatched rows.

In order to answer the question, We need to extract all records where student_id is null in right side table. Referring to the below picture, you will get better understating of exactly how left outer join works.

The corresponding transact sql code is :

“Select * from Class a left outer join Exam b
On a.student_id = b.student_id
where b.student_id is null”

Q. Find out the students who passed the exam but not studied in class “A” ?

This can be answered using the “Right Outer Join”.

When joining EXAM and CLASS table using right outer join, we get all the records from EXAM table and matching records from CLASS “A” table and NULL for the unmatched rows.

In order to answer the question, We need to extract all records where student_id is null in left side table

The corresponding transact sql code is :

“Select * from
Class a right outer join Exam b
On a.student_id = b.student_id
where a.student_id is null”

I hope, you had a good time in reading and with this I am ending this article. The next article will be about Internals of Join in SQL Server.

Read More

Table variable and Table type in transaction

The myth is to roll back the work done inside the transaction, in case of any error during the transaction. However this is not the case with the SQL Server table variable, table type and variables. That is, the changes made on these types are not rolled back even when there is an error.

Let me explore this with an example.

In order to distinguish variable from table, I have created one temporary table and local variable. Inside the transaction, value 1 is assigned to the variable then 256. Assigning of value 256 will cause error as the variable data type is tinyint. The same applied for the temporary table. Due to the error, the transaction enter into the roll back section and the entire transaction rolled back successfully. However by running the select statement “select @num as num” value 1 is return whilst “select * from #tbl” return nothing as expected. This clearly shows that there is no effect for local variable even though the transaction rolled back.

use tempdb
GO
create table #tbl (c1 tinyint)
declare @num tinyint
begin tran
set @num = 1
set @num = 256
insert into #tbl (c1) values(1)
insert into #tbl (c1) values(256)
if @@ERROR =0
commit tran
else
rollback tran
select @num as num
select * from #tbl

The same is applicable for table variable and table type. Sample using table variable:

declare @tbl as table (c1 int)
create table #tbl (c1 int)
begin tran
insert into @tbl (c1) values (1)
insert into @tbl (c1) values (1/0)
insert into #tbl (c1) values(1)
insert into #tbl (c1) values(1/0)
if @@ERROR =0
commit tran
else
rollback tran
select * from @tbl
select * from #tbl

Sample using table type:

create type mytab as table(c1 tinyint)
GO
declare @mytab as mytab
begin tran
insert into @mytab(c1) values (1)
insert into @mytab(c1) values (256)
if @@ERROR = 0
commit tran
else
rollback tran
select * from @mytab

Read More