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

Database Modelling – Many to Many Relationship

Let’s start with an example to understand what is many to many relationship and how this can be represented in database.

 

If you have shopping experience with any online shopping websites like amazon.com, you could have noticed that the way they organized their products to display.

The online store may sell same type of products of different manufacturer and different type of products from same manufacturer. Example Mobile can be manufactured by Nokia, Apple and Samsung. Similarly Apple manufactures Mobile and Computer. I have represented the same scenario in the below diagram.

 

 

 

 

 

 

 

 

 

 

 

I hope, by now we know what is many to many relationships. Having this knowledge let’s design database table for this use case.

 

Department
department_id		department_name
1		Mobile Phone
2		Computer

 

Manufacturer
manufacturer _id	manufacturer _name
1	Apple
2	Dell
3	Nokia

 

The “Department” table can be used to store all the department of the online store and “Manufacturer” table can be used to store all the manufacturer information. So far everything looks good, but the store wants to display the menu in a way where user can browser product by manufacturers. In our example, computer by manufacturers.

 

The below given menu is what the online store need.

 

Browse By Department			Browse By Department
Mobile Phone ›	Apple		Mobile Phone
Computer	Samsung		Computer ›	Apple
				Dell
Browse By Manufacturer			Browse By Manufacturer
Apple	Mobile Phone		Apple
Dell	Computer		Dell	Computer
Samsung			Samsung

 

To achieve this, we create a new table to hold the department and manufacturer relation, this table is called junction table.   

 

Department_Manufacturer
department_id	manufacturer_id
1	1
1	3
2	1
2	2

 

Using this table, we will be able to answer the below questions

1. Which departments belong to manufacturer “Apple”
2. List of manufacturers who belong to department “Mobile”

Read More

Data compression in SQL Server 2008

Data compression – SQL Server 2008

I found the Data compression feature in SQL Server 2008 is very handy. This definitely helps in optimizing query performance in terms of I/O cost and additionally it helps in saving storage space.

There are two types of compression in SQL Server 2008

Row Level Compression

When we enable the Row level compression, the data stored in the fixed length data type will be stored in variable length format.

Before I show you the example, it is worth explaining the difference between CHAR and VARCHAR data type

“CHAR is a fixed-length data type; the storage size of the char value is equal to the maximum size for this column. Whereas VARCHAR is a variable-length data type, the storage size of the VARCHAR value is the actual length of the data entered, not the maximum size for this column.”

Okay, now let’s look at the example,

Let’s assume, we have a table with CHAR (15) column. It is not always true that you will store the data in this column which has 15 characters in length; it may vary, but still depends on your application. For example if you are storing “SQL Server 2008” and “SQL” in the CHAR(15) column each value will take 15 characters space to store the data irrespective of the actual number of characters. When the same value stored in variable length column then the storage space will depend on the length of the character stored.

You may ask yourself, why I need a feature “Row level Compression” if I change the CHAR data type to VARCHAR of my table? Yes, absolutely no sense of having such a feature but it is applicable for any fixed length data type and more importantly another advantage of this feature is, it doesn’t take space to store zero and NULL values.

Page Level Compression

Page level compression is very useful functionally which I was expecting for a long time. I was working in analytical processing application, which uses SQL Server 2000,and of course SQL Server 2000 doesn’t have such compression feature. However I have managed to reduce or compress the data size by doing some workaround. I can explain in detail in another post.

Let’s see how we can get benefit by using the Page level compression; Page level compression is nothing but removing the duplicate values within the page (i.e.) irrespective of rows and columns.

For example, when you insert value “SQL Server 2000”, “SQL Server 2008” into a table the SQL Server storage engine will store “SQL Server 200” only once in a page and it will refer this value for all other occurrences.

It is advisable to estimate the benefit before you implement the compression, you can make use of the system stored procedure sp_estimate_data_compression_savings or data compression wizard to estimate how much space you will save by implementing.

Read More

Be aware when you use date to filter the records

 When you execute the below query, as you expect, the result will be “September 26^th 2049”

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/49'
SELECT @DATE

Now, Lets see what happen when we pass 50 instead of 49

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/50'
SELECT @DATE

You will get the result as “September 26^th 1950”; this is because by default, SQL Server interprets two-digit years based on a cutoff year of 2049.

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/2050'
SELECT @DATE

Now, as you expect you will get the result as “September 26^th 2050”; so it is always good to specify the four-digit year.

Notes from BOL

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.”

Read More

How to insert values into an Identity column in SQL Server

Identity is a property that can be set on a column. When an identity is enabled the column will be populated automatically by system and it will not allow user to enter value explicitly however there is an option in SQL server SET IDENTITY_INSERT.

Let’s create table to explain in details.

CREATE TABLE dbo.ErrorLog (LogId INT IDENTITY(1,1), LogMsg VARCHAR(MAX))
INSERT INTO ErrorLog (LogMsg) VALUES('a') GO 10 SELECT * FROM dbo.ErrorLog

The test table is created and populated with some sample records. Let’s insert a record into the table and specify value for the LogId column explicitly.

INSERT INTO dbo.ErrorLog(LogId,LogMsg) VALUES(0,'b')

As expected we get the error message Cannot insert explicit value for identity column in table 'ErrorLog' when IDENTITY_INSERT is set to OFF. There are situations where we need to enter value into the Identity enabled column. To do that we must turn on the IDENTITY_INSERT property as in below example. The identity_insert property must be turned off after the insert otherwise it always expect you to specify value for the identity enabled column with in the session.

SET IDENTITY_INSERT dbo.ErrorLog ON
INSERT INTO dbo.ErrorLog(LogId,LogMsg) VALUES(20,'b')
SET IDENTITY_INSERT dbo.ErrorLog OFF

Read More

Concatenate column values from multiple rows into a single column in SQL Server

I have created a simple table to demonstrate how to concatenate the values from multiple rows in to one column.

CREATE TABLE #tblProduct (product_Id INT IDENTITY(1,1), product_name VARCHAR(10))

Insert some sample records in to this temporary table

INSERT INTO #tblProduct (product_name) VALUES('AAA')
INSERT INTO #tblProduct (product_name) VALUES('BBB')
INSERT INTO #tblProduct (product_name) VALUES('CCC')
INSERT INTO #tblProduct (product_name) VALUES('DDD')
INSERT INTO #tblProduct (product_name) VALUES('EEE')
INSERT INTO #tblProduct (product_name) VALUES('FFF')
INSERT INTO #tblProduct (product_name) VALUES('GGG')


Once you have populated the table with data, Run this query to get the row values in to one column.

DECLARE @productarr VARCHAR(8000) SET @productarr = '' SELECT @productarr = @productarr + '"' + CAST(product_Id AS VARCHAR) + '",' FROM #TBLPRODUCT ORDER BY product_Id
SELECT LEFT(@productarr,LEN(@productarr)-1)
DROP TABLE #tblProduct

That’s all you have done with the job

Read More

How to find which query is currently running in SQL Server 2000.

The below given function will return the currently running query for the process id.

STEP1: Create the below function in your server database.

CREATE FUNCTION SHOW_MY_PROCESS (@SPID INT)
RETURNS VARCHAR(8000)AS
BEGIN
DECLARE @sql_handle BINARY(20), @handle_found BIT
DECLARE @stmt_start INT, @stmt_end INT
DECLARE @line NVARCHAR(4000), @wait_str VARCHAR(8)
DECLARE @sql_process AS VARCHAR(8000)
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.SYSPROCESSES
WHERE spid = @spid AND ecid = 0
SELECT @sql_process = SUBSTRING(TEXT, COALESCE(NULLIF(@stmt_start, 0), 1), CASE @stmt_end WHEN -1 THEN DATALENGTH(TEXT) ELSE (@stmt_end - @stmt_start)END)
FROM ::fn_get_sql(@sql_handle)
RETURN @sql_process
END

STEP2: Run the below query

SELECT dbo.SHOW_MY_PROCESS(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE SPID > 50

Read More