Just like Varchar for Char, why don’t we have Varnumeric for Numeric…

When tables grow to millions of rows reducing the amount of storage a few bytes per row can have significant effect. The decimal and numeric data types are typically stored on disk as fixed-length data. Mostly these values are defined with high precision, but precision is not required in most of these cases. In such cases the storage space consumed is unnecessary. The situation becomes even worse when NULL or zero values are also present in numeric or decimal type column. Such issues are required to be addressed for optimization of storage space, but what options are available?

SQL Server 2005 has introduced the VARDECIMAL storage format. This new storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at the table level.

When a table is not using the VARDECIMAL format every numeric or decimal value uses a fixed size (between 5 and 17 bytes) even when the data is 0. With VARDECIMAL, storage becomes dynamic and optimized for each value.

The VARDECIMAL is implemented at table level, so we can have table first with all column defined and inserted data, there after we can find how much space it takes without actually going for VARDECIMAL and how much we can save if we go for VARDECIMAL.

Let create the scenario to test the VARDECIMAL, we are creating table with decimal and numeric datatype fields

create table testVarDecimal

(col1 decimal(18,0),

col2 numeric(18,0))

Command(s) completed successfully.

Now lets try to insert around 4000 record in by the below script.

declare @cnt int

set @cnt=1

while (@cnt < 4000)

begin

      if (@cnt < 2000)

            insert into testVarDecimal values(@cnt,0)

      else

            insert into testVarDecimal values(@cnt,(@cnt + 100))

      set @cnt +=1

end

Now since we have records in place, lets analyze how much space we can reduce if we implement VARDECIMAL to this table.

exec sp_estimated_rowsize_reduction_for_vardecimal 'testVarDecimal'

avg_rowlen_fixed_format   avg_rowlen_vardecimal_format  row_count

-----------------------   ----------------------------  ----------

25.00                                   16.27           3999

We can see that there may be difference of about (25.00 – 16.27) i.e.9 bytes in each row. So it seems suitable to apply vardecimal storage for this table.

To implement VARDECIMAL, we first need to enable it for the database, which can done like the way below

execute sp_db_vardecimal_storage_format 'testDemo', 'on'

Command(s) completed successfully.

Here my database is testDemo, you can put your own database name, where your table object reside.

Now let’s implement it on table by following script

exec sys.sp_tableoption 'testVarDecimal', 'VarDecimal storage format',1

So it is all over. If you want to check which all database has VarDecimal Enabled just fire the below command

exec sp_db_vardecimal_storage_format

To check which table has VARDECIMAL enabled execute the following

select name, OBJECT_ID, type_desc from sys.objects 

WHERE OBJECTPROPERTY(OBJECT_ID, N'TableHasVarDecimalStorageFormat') = 1

Code not always straightforward, but rewards are worth it 

Spread storage of data and indexes to different disks to improve I/O performance

When we are creating any database, the default FILEGROUP which gets created is PRIMARY file group, so by default Primary filegroup is the default filegroup.

Any database objects user create will stores in PRIMARY file group by default, but one can change the default filegroup. Even at the time of object creation user can specify where the object will get stored.

For our example let’s create a database “DemoFileGroup”

use master

go

create database DemoFileGroup

go

Command(s) completed successfully.

Now we are creating two filegroup and attaching it to the newly created database “DemoFileGroup” as fg1 and fg2.

alter database DemoFileGroup add FILEGROUP fg1

alter database DemoFileGroup add FILEGROUP fg2

go

Command(s) completed successfully.

Now let’s query sysfilegroups to find out, various filesgroups gets created in the database.

use DemoFileGroup

go

select groupName as FileGroupName from sysfilegroups

FileGroupName

-----------------------------

PRIMARY

fg1

fg2

(3 row(s) affected)

At this point we have three file groups primary, FG1 and FG2 in our new database. Any new table or index will be created in the primary filegroup which is the default unless we specify the filegroup to use.

If we want to create a data object in fg1 or fg2 then the only way is to specify the filegroup when creating the storage object as we are doing in the below example.

Here we are creating table table1 but we are not specifying on which filegroup it should get created, so by default it will get created in the PRIMARY file group.

create table table1

(id int identity(1,1))

Go

Lets query sp_help with table1 to find out the where the table object “table1” gets its storage.

sp_help table1

go

Data_located_on_filegroup

---------------------------

PRIMARY

So by output we can see the table gets created in the default file group ie, PRIMARY filegroup.

However at the time of table creation we can specify the filegroup name as we are doing in the below example.

create table table2

(id int identity(1,1),

fname varchar(20))

on fg1

go

Command(s) completed successfully.

Now let’s query sp_help with table1 to find out the where the table object “table2” gets its storage.

sp_help table2

go

Data_located_on_filegroup

---------------------------

fg1

Here we can see the table gets created in the file group fg1 which we specified at the time of table creation.

Now that we created the table on the new filegroup we can try to insert data.

insert into table2(fname) values('Prachi')

Msg 622, Level 16, State 3, Line 1

The filegroup "fg1" has no files assigned to it. Tables, indexes,

text columns, ntext columns, and image columns cannot be populated

on this filegroup until a file is added.

Now since we have no file associated with File Group fg1 we got the above error. Even if we try change the default filegroup, we will be getting the same error.

alter database DemoFileGroup modify FILEGROUP fg1 default

go

Msg 5050, Level 16, State 1, Line 1

Cannot change the properties of empty filegroup 'fg1'.

The filegroup must contain at least one file.

At this moment, we are done with FileGroup creation, its now time to create file and attached it to the newly created filegroup. Lets see the below script to create and attach file to the newly created filegroup “fg1”.

  

-- Add data file to FG1

alter database DemoFileGroup

add file

(NAME = DemoFileGroup_fg1,FILENAME = 'D:\Sqldata\DemoFileGroup_fg1.ndf')

to FILEGROUP fg1

go

Command(s) completed successfully.

Now we can set the default FILEGROUP to fg1, since now it has file associated with it.

alter database DemoFileGroup modify FILEGROUP fg1 default

go

The filegroup property 'DEFAULT' has been set.

We can check this by going to the database properties

Now if you try to insert data to table 2 which is associated by FILEGROUP fg2, it will not throw error.

insert into table2(fname) values('Prachi')

(1 row(s) affected)

Here we can can conclude, we are now known with the filegroup and file creation. In coming post, I will explain various benefits and uses of filegroup with Horizontal Partition.

Dare to Challenge code, challenge youself 

Post Reference: Vikram Aristocratic Elfin Share

GO, you can also specify your own word as a batch terminator

GO is a batch terminator, you can however change it to whatever you want.

Also read previous articles on GO

GO and the Stored Procedure

GO, is it a SQL command, a jump to pointer or what exactly?

GO and the Scope of variable.

Go to Tool-> Option -> Query Execution

And then change the Batch Separator to word of your choice, here in this example, I changed it to BATCH.

Now if I try to write code with batch separators GO, SSMS will not recognize.

alter table tab2

add  d_manager varchar(10)

GO

select d_id,d_name,d_manager from tab2

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near 'GO'.

Now if we replace GO with BATCH, it should work properly. Lets see

alter table tab2

add  d_manager varchar(10)

BATCH

select d_id,d_name,d_manager from tab2 

So this is how you can specify your own word as a batch terminator

Your code, sound the horn 

Post Reference: Vikram Aristocratic Elfin Share

GO and batch execution multiple times.

Go is a useful command especially when you want to execute a batch multiple times. For time being we can take an example of inserting multiple dummy records in a table, with just ONE insert statement. Also read previous articles on GO

GO and the Stored Procedure

GO, is it a SQL command, a jump to pointer or what exactly?

GO and the Scope of variable.

Let’s create a temporary table for this purpose.

use testDemo

go --First Batch

create table #tempTable

(tempID uniqueidentifier,

tempMonth int,

tempDateTime datetime )

GO -- Second Batch

Command(s) completed successfully.

These batches will just execute these separate batches for once as default value for GO is one. Lets insert 100 records in our temporary table with just one insert statement

insert into #tempTable (tempID, tempMonth, tempDateTime)

select NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()

GO 10

Batch execution completed 10 times.

“Go 100” will execute this insertion batch for ten times, and exultantly 10 records will be inserted. That’s what I call it “Power of GO”. Check this.

select * from #tempTable

tempID                               tempMonth   tempDateTime

------------------------------------ ----------- -----------------------

C2055F4A-0FF9-4A2C-BB9F-9CC665EEC009 3           2013-03-25 14:30:37.830

4322FE78-4494-45B3-8923-5D9CD5F030CA 3           2013-03-25 14:30:37.933

86D89223-530B-423E-AAB1-8E65601D821B 8           2013-03-25 14:30:37.933

7E1A8812-9A8E-412D-8EE4-4FBE02FB4CBB 8           2013-03-25 14:30:37.937

917C89A6-1DFB-4375-A6E3-5C15F556AFCB 6           2013-03-25 14:30:37.940

7DEBDD5D-E2B9-45BB-9D65-D911DA32F431 7           2013-03-25 14:30:37.940

CC124777-7AE4-47A8-B7CA-0EE566D01465 5           2013-03-25 14:30:37.940

6A0E6F7C-47A6-4F2A-A8C0-58E4A0B498E2 4           2013-03-25 14:30:37.947

CB22E285-689A-466A-A7EB-E0734ED1435B 3           2013-03-25 14:30:37.950

7102D516-03AC-4EE9-93C8-820477E645D6 5           2013-03-25 14:30:37.953

(10 row(s) affected)

Now drop the temporary table

drop table #tempTable

Conclusion: GO can be used for multiple times batch execution.

Honk if you hate coding

Post Reference: Vikram Aristocratic Elfin Share

GO and the Stored Procedure

In this article, we will see how GO can have an effect on writing stored procedure, before that go through my previous post 

GO, is it a SQL command, a jump to pointer or what exactly?

GO and the Scope of variable.

We will directly jump into the example to see the effect of go in stored procedure, Now we are creating the stored procedure MyManager, and if you notice after end block of stored procedure, there is a select statement and a GO statement proceed with it.

Create procedure MyManager

as

begin

     select 'My Manager is the best one.'

end

select 'Oh come on! This is absolutly lie'

go

Command(s) completed successfully.

Now if we try to execute the procedure..

exec MyManager

---------------------------

My Manager is the best one.

(1 row(s) affected)

---------------------------------

Oh come on! This is absolutly lie

(1 row(s) affected)

Say, if you show this stored procedure’s output to your Manager, you better have a new job lined up.

What happened? You intended to show him only the first line, but the output of the second select also is displayed.

Why? Because the whole code is scripted into the syscomments table until a GO command is found.

Now lets drop the procedure and recreate the same

drop proc MyManager

Command(s) completed successfully.

Procedure lets recreate the same procedure, where select statement appears after the GO statement.

Create procedure MyManager

as

begin

     select 'My Manager is the best one.'

end

go

select 'Oh come on! This is absolutly lie'

Now if we execute the procedure, we gets our desired result.

exec MyManager

---------------------------

My Manager is the best one.

(1 row(s) affected)

Conclusion: If the stored procedure isn’t immediately followed by a GO command, an executable statement that appears after the stored procedure will be executed together with the stored procedure, which as we have seen can have unintended consequences.

I just like to code and be happy.

Post Reference: Vikram Aristocratic Elfin Share