How to get Identity Columns details

Many times developers need to know identity columns related information from tables.
After SQL Server 2005 , its easy for us to get this info with much rich informations.

sys.identity_columns is a table which have all this information we need.

CREATE TABLE testtable (
id INT IDENTITY ( 1 , 1 ))

INSERT INTO testtable
DEFAULT VALUES

INSERT INTO testtable
DEFAULT VALUES

INSERT INTO testtable
DEFAULT VALUES

When we look at last value it will show last added value for identity column.
This table will provide basic info like table name, column name, data type,seed , increment value etc.

SELECT Object_name(object_id) table_name,

object_id,

name,

column_id,

seed_value,

increment_value,

last_value,

system_type_id,

(SELECT name

FROM sys.types

WHERE system_type_id = i.system_type_id) AS system_type_name,

user_type_id,

(SELECT name

FROM sys.types

WHERE user_type_id = i.user_type_id) AS user_type_name,

max_length,

PRECISION,

scale,

collation_name,

is_nullable,

is_ansi_padded,

is_rowguidcol,

is_identity,

is_filestream,

is_replicated,

is_non_sql_subscribed,

is_merge_published,

is_dts_replicated,

is_xml_document,

xml_collection_id,

default_object_id,

rule_object_id,

is_not_for_replication,

is_computed,

is_sparse,

is_column_set

FROM sys.identity_columns i

WHERE Objectproperty(object_id,‘isusertable’) = 1

How to get rowcount/size for eachtable.

Many times we want to get number of rows and spaceused by each table.
We can use sp_spaceused for this.
But when we want this info for all tables than we need some simple way.

Here is a solution for this.

create table #tempsize
(table_name varchar(256),
rows int,
reserved varchar(256),
data varchar(256),
index_size varchar(256),
unused varchar(256)
)

insert into #tempsize
exec sp_msforeachtable ‘exec sp_spaceused ”?”’

select * from #tempsize
order by rows desc

Since sp_spaceused will give us approximate data. If we need exact number of row count we can try this way

create table #temprowcountforeachtable
(table_name varchar(256),
row_count int)

insert into #temprowcountforeachtable
exec sp_msforeachtable ‘select convert (varchar(256),”?”) as table_name, count(*) from ?’

select * from #temprowcountforeachtable order by row_count desc

This will give us exact row count.

Cheers.

SQL 2008 SP1 released

MS has released SP1 for SQL Server 2008.

To download click here

Also MS has released cumulative update of SP1.

To download click here

Filtered indexes Part2 – SQL Server 2008

To visit part 1 click here

Some benefit of filtered indexes
1. It has less performance hit. Since only data qualifying in the where condition are used , less data inserted/updated/deleted in index pages.

2.It uses less space.

When to use filtered indexes
When rows qualifying in the where clause is less than 50% of over all rowcount we should use filetered indexes.
Sparse columns are best example to be used for filtered indexes, because in general columns where more than 50% of value is null are used as sparse column.

Lets take one example

create table customer_master
(customer_id int,
customer_name varchar(100),
country varchar(100) sparse null)

insert into customer_master values(1,’abc’,’usa’)
insert into customer_master values (2,’def’,’usa’)
insert into customer_master values (3,’ghi’,’india’)
insert into customer_master values (4,’jkl’,null)
insert into customer_master values (5,’mno’,null)
insert into customer_master values (6,’pqr’,null)

create index idx_customer_master_country
on customer_master (country,customer_id)
where country is not null

select customer_id from customer_master where country = ‘usa’
select customer_id from customer_master where country = ‘india’

Here if we look at execution it uses index.

Filtered index part -1 , SQL 2008

Along with sparse columns SQL Server 2008 comes with two more advanced features

1.Filtered indexes
2.Filtered statistics

What is filtered indexes?
Filtered indexes can be explained as index with where clause in create index statement.
Suppose we have customer data and we are interested in customer whoses country is U.S.A.
We are not interested in other customer from other countries.
Here we can create filtered index for country U.S.A only.

Lets create a table customer_master

create table customer_master
(customer_id int,
customer_name varchar(100),
country varchar(100))

insert into customer_master values(1,’abc’,’usa’)
insert into customer_master values (2,’def’,’usa’)
insert into customer_master values (3,’ghi’,’india’)
insert into customer_master values (4,’jkl’,’india’)
insert into customer_master values (5,’mno’,’usa’)
insert into customer_master values (6,’pqr’,’china’)

create index idx_customer_master_country_usa
on customer_master (country,customer_id)
where country = ‘usa’

select customer_id from customer_master where country = ‘india’

Execution plan Query1

select customer_id from customer_master where country = ‘usa’

Execution plan Query2

Here we can see that in second query it uses index because country = ‘usa’

To visit part 2 click here