Sparse Column – Part 3 Column Sets

Column Sets

SQL 2008 provides column_set for all sparse columns which can be used as a single set for all sparse columns. It can be used to display values or to update/insert values also.

Let us see how.

create table test
( id int ,
person varchar(10),
salary int sparse,
department varchar(100) sparse,
details xml column_set for all_sparse_columns )

• There can be only one column set per table.

• You can not add column set to an existing table

insert into test(id,person,salary,department) values (1, ‘person1′,’1000′,’dep1’)
insert into test(id,person,salary,department) values (2, ‘person2’,null,null)

select * from test
ID Person Details
================================================================1 person1 <salary>1000</salary><department>dep1</department>
2 person2 NULL
================================================================

Here you can see all sparse columns are shown as single xml string under details column

Now if you want to see every sparse column in the result set you should provide all column name in select query

select id,person,salary,department from test

ID Person Salary Department
===============================
1 person1 1000 dep1
2 person2 NULL NULL
================================

• Simlar if we pass values in details column it will be also inserted into sparse columns

insert into test (id, person, details)values (3,’person3′,'<salary>3000</salary><department>dep3</department>’)

select id ,person,salary, department from test where id = 3

ID Person Salary Department
===============================
3 person1 3000 dep3
================================

• Similarly if we update column set it will update sparse columns

update test set details =‘<salary>4000</salary><department>dep3</department>’
where id = 3

select id ,person,salary, department from test where id = 3

ID Person Salary Department
===============================
3 person1 4000 dep3
================================

Here it has update salary from 3000 to 4000.

• However we need to provide all sparse column values in the update statement

• You can not update both column set and sparse column at same time

Sparse Columns Part 2

In last article we look into what is sparse columns and how to use it. You can visit Part 1 from here
Sparse Column Part 1

Rules for creating sparse columns.

• Sparse Column must be nullable.
• you cant assign default values to sparse columns
• You cant make sparse column as identity or rowguidcol.
• computed columns can not be sparse
• You can not create clustered index/Unique Index/Primary Index on a sparse column.
• You can have 30000 columns as sparse column in a table.
• You can create 1000 index in table with sparse columns.
• A table with sparse columns can not be compressed
• Tables Types cannot use sparse columns
• Sparse columns can not be used with merge replications

Sparse column Part 1

Sparse ColumnsMSSQL 2008 has come with sparse columns,which is a solution for space taken by null columns. It is used when column has majority of null values. It actually takes no value for storing null values but it takes some more space for non null values.

So How much space is taken by sparse columns?

• It takes no space for null values.
• For non null values
  For fixed length and precision dependent it takes 4 extra bytes
  For variable length it takes 2 extra bytes

Lets try to under stand how it works
Table with non sparse columncreate table test
( id int ,
person varchar(10),
salary int )
Suppse we have 10,000 rows in a table , out of which only for 1000 person salary data is available.
Now if salary is not sparse then it will take about 10,000 * 4 = 40,000 byte of space.
Table with sparse columncreate table test
( id int ,
person varchar(10),
salary int sparse )
Now since its sparse non null int will take 4 bytes more .
so 1000 non null salary values will take 1000 * (4 + 4) = 8000 bytes.
So we can see here that it saves 32,000 bytes.
For a column with atleast 60 % null values we can think to make it a sparse column.

How to alter column to make it sparse columnAlter table test
alter column salary add sparse
How to make a sparse column to non sparse columnalter table test
alter column salary drop sparse

Job Monitoring Part -3

In my past article we looked on how to monitor jobs using TSQL using procedure sp_get_composite_job_info
Now we will look some more parameters for it.

Anyone who is reading this article first visit part 1 and part 2 from here
Job Monitoring Part 1
Job Monitoring Part 2
Syntax
EXEc sp_get_composite_job_info
@job_id UNIQUEIDENTIFIER = NULL,
@job_type VARCHAR(12) = NULL, — LOCAL or MULTI-SERVER
@owner_login_name sysname = NULL,
@subsystem NVARCHAR(40) = NULL,
@category_id INT = NULL,
@enabled TINYINT = NULL,
@execution_status INT = NULL,
@date_comparator CHAR(1) = NULL, — >, < or =””>
@date_created DATETIME = NULL,
@date_last_modified DATETIME = NULL,
@description NVARCHAR(512) = NULL, — We do a LIKE on this so it can include wildcards
@schedule_id INT = NULL — if supplied only return the jobs that use this schedule

Job based on categoryselect * from msdb..syscategories order by category_idHere you will get info for all category and their name.
Result
category_id category_class category_type name
0 1 1 [Uncategorized (Local)]
1 1 1 Jobs from MSX
2 1 2 [Uncategorized (Multi-Server)]
3 1 1 Database Maintenance
4 1 1 Web Assistant
5 1 1 Full-Text
6 1 1 Log Shipping
7 1 1 Database Engine Tuning Advisor
10 1 1 REPL-Distribution
11 1 1 REPL-Distribution Cleanup
12 1 1 REPL-History Cleanup
13 1 1 REPL-LogReader
14 1 1 REPL-Merge
15 1 1 REPL-Snapshot
16 1 1 REPL-Checkup
17 1 1 REPL-Subscription Cleanup
18 1 1 REPL-Alert Response
19 1 1 REPL-QueueReader
20 2 3 Replication
98 2 3 [Uncategorized]
99 3 3 [Uncategorized]
100 1 1 Report Server
Now if we want to get all jobs related to Database Maintenance we have to use category_id 3
Exec sp_get_composite_job_info @category_id =3
Jobs based on schedule.If we want to find jobs based on certain schedule id
select * from sysschedules
This will give us schedule_id and its related details.
Now we have to pass related schedule_id in the procedure.
Exec sp_get_composite_job_info @schedule_id = 43

Job Monitoring Part – 2

In my past article we looked on how to monitor jobs using TSQL using procedure sp_get_composite_job_info
Now we will look some more parameters for it.

Anyone who is reading this article visit other parts from here

Job Monitoring Part 1
Job Monitoring Part3

Syntax

EXEc sp_get_composite_job_info

@job_id UNIQUEIDENTIFIER = NULL,

@job_type VARCHAR(12) = NULL, — LOCAL or MULTI-SERVER

@owner_login_name sysname = NULL,

@subsystem NVARCHAR(40) = NULL,

@category_id INT = NULL,

@enabled TINYINT = NULL,

@execution_status INT = NULL,

@date_comparator CHAR(1) = NULL, — >, < or =” @date_created” datetime =” NULL,” datetime =” NULL,” int =” NULL”>


To get local or Multiserver job
Exec sp_get_composite_job_info @job_type = ‘LOCAL’

Exec sp_get_composite_job_info @job_type = ‘MULTI-SERVER’

To check all jobs created after a date

here we will use @date_comparator ‘>’

Exec sp_get_composite_job_info @date_comparator = ‘>’,
@date_created = ‘2009-01-01’

jobs created before a dateHere we will use @date_comparator ‘<‘ Exec sp_get_composite_job_info @date_comparator = ‘<‘, @date_created = ‘2009-01-01’ We can use also = and <> also @date_comparator

To check all jobs last modified after a date

here we will use @date_comparator ‘>’

Exec sp_get_composite_job_info @date_comparator = ‘>’,
@date_last_modified = ‘2009-01-01’

jobs last modified before a date
Here we will use @date_comparator ‘<‘ Exec sp_get_composite_job_info @date_comparator = ‘<‘, @date_last_modified = ‘2009-01-01’ We can use also = and <> also @date_comparator