Sparse Columns in SQL Server

Sparse Columns in SQL Server

Sparse column is introduced in SQL Server 2008, which is designed to store null values. If you store any null value in sparse column it doesn’t occupy any space on the database. If you store any non null value in sparse column it takes 4 bytes extra space. For example if you store bigint in database usually it requires 8 bytes if you store this value in sparse column it will occupy 12 bytes.

CREATE TABLE Students_Sparse (Id int IDENTITY(1,1), NAME VARCHAR(30), Address1 VARCHAR(30), Address2 VARCHAR(30), ADDRESS3 VARCHAR(30) SPARSE)

CREATE TABLE Students (Id int IDENTITY(1,1), NAME VARCHAR(30), Address1 VARCHAR(30), Address2 VARCHAR(30), ADDRESS3 VARCHAR(30))

INSERT INTO students_sparse VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

INSERT INTO students VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

-- If you insert non null values in to sparse column the size of table will be huge then an ordinary column table

sp_spaceused Students_Sparse

go

sp_spaceused Students

TRUNCATE TABLE Students_Sparse

TRUNCATE TABLE Students

Below example shows if you leave the sparse column by entering null values the column doesn’t occupy any space and there will be a huge change in space

INSERT INTO students_sparse VALUES ('anand','Hyderabad','AP',null)

go 1000

INSERT INTO students VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

-- If you insert non null values in to sparse column the size of table will be huge then an ordinary column table

sp_spaceused Students_Sparse

go

sp_spaceused Students

Filtered Index in SQL Server

Filtered Index is a new feature in SQL Server 2008, it is an optimized non-clustered index created on a subset of data. The definition of the index will have where clause in it. It provides huge performance improvement when we query a subset of data from a large table. These filtered indexes are relatively small when comparing to normal Indexes and queries will be less expensive in terms of I/O.

Note: We can’t create a filter index on complex WHERE clause queries and it doesn’t allow LIKE in where clause, we can use simple operators. Filtered Indexes can be rebuild online.

CREATE INDEX idx_hostName ON Total_Hosts(ServerName) WHERE Active = 1

SELECT si.index_id, si.name, si.type_desc, si.filter_definition FROM sys.indexes si, sys.tables st

WHERE si.object_id = st.object_id AND st.name= ‘Total_Hosts’

SQL Server Agent with Express Edition

If you install SQL Server Express Edition, it will install only Database Engine Services.

In configuration manager it shows SQL Server and SQL Server Agent services, but SQL Server agent in disable state. Why it installed SQL Server Agent is because if you perform upgrade from Express Edition to any other edition it enables the service after validating the installation and also no need to replace all files at the time of upgrade, because of that it installs SQL Server Agent service along with Express Edition.

Express Edition comes in different versions.

Express Edition with SSMS

Database Engine, Import and Export Data (No SSMS)

Express Edition with Advanced Features

Database Engine, SSMS, Reporting Services Configuration Manager, BIDS, Import and Export Data.

SQL Server MetaData Functions

fn_helpcollations() -- Lists all the collations supported by SQL Server
select * from sys.fn_helpcollations()

fn_servershareddrives() -- Lists all shared drives used by cluster
select * from fn_servershareddrives()

fn_virtualfilestats(DatabaseId, FileId) -- Displays I/O statistics for data and log files.
select * from fn_virtualfilestats(1,1)
select * from fn_virtualfilestats(DB_ID('kalyandb'),1)
select * from fn_virtualfilestats(DB_ID('kalyandb'),2)
select * from fn_virtualfilestats(Null,Null)

 fn_virtualserverfilemodes() -- Displays clustered instance nodes

Recommended SQL Server files to be in anti-virus exclusions

Below SQL Server related files can be included in exclusion list of Anti-Virus, because the files may locked while scanning or sometimes anti-virus may prevent accessing them or we may face performance degradation while accessing them.

.Mdf -- SQL Server Primary Data File

.Ldf  -- SQL Server Transaction Log File

.Trn  -- Transaction Log Backup

.Bak -- Full Backup

.Diff  -- Differential Backup

.Ndf  -- SQL Server Secondar Data File

.Sql   -- SQL Script Files



Fix : Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]

When I am trying to open SQL Server 2012 configuration manager i got the above error, To fix this

1. search for the file "sqlmgmproviderxpsp2up.mof"  (usually it is located under shared folder under version folder).
2. After locating the file, open command prompt and run the below command
mofcomp C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof
Output
Microsoft (R) MOF Compiler Version 6.1.7600.16385
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmp
roviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!
3. After it is stored in the repository, close the SSMS if it already open
4. Now open SQL Configuration manager, it will work.

SQL Server Details

Below query displays the sql server name and product details along with CPU and RAM details
SELECT SERVERPROPERTY('ServerName') AS [SQLServer],

SERVERPROPERTY('ProductVersion') AS [VersionBuild],SERVERPROPERTY('ProductLevel') AS [Product],SERVERPROPERTY ('Edition') AS [Edition],SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly],SERVERPROPERTY('IsClustered') AS [IsClustered],[cpu_count] AS [CPUs],round(cast([physical_memory_in_bytes]/1048576 as real)/1024,2) AS [RAM (GB)]FROM [sys].[dm_os_sys_info]

Retrieving Resultset Using XML

We can generate XML documents by using SQL Server FOR XML. There are 4 methods we can include with FOR XML are AUTO, EXPLICIT, PATH, RAW.

FOR XML AUTO - Displays nested XML tree with each column representing as a single element.

Sample Data

Examples

SELECT * FROM TestIdentity WHERE id = 1 FOR XML AUTO <TestIdentity Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" />

SELECT * FROM TestIdentity AS Students WHERE id = 1 FOR XML AUTO  <Students Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" />
SELECT
* FROM TestIdentity AS Students WHERE id in (1,2) FOR XML AUTO,ELEMENTS
<Students><
Id>1</Id><
NAME>Kalyan</NAME><
Phone>9090909092</Phone><
City>Hyderabad</City></
Students><
Students><
Id>2</Id><
NAME>Kishore</NAME><
Phone>8080808082</Phone><
City>Secunderabad</City></
Students>
SELECT * FROM TestIdentity WHERE id IN(1,2) FOR XML AUTO, ROOT('students')
<students><
Students Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" /><
Students Id="2" NAME="Kishore" Phone="8080808082" City="Secunderabad" /></
students>
SELECT CASE WHEN LEN(NAMES)>0 THEN LEFT(NAMES, LEN(NAMES)-1) ELSE '' END AS NAMES_CSV
FROM (SELECT T.NAME + ',' FROM TestIdentity T FOR XML PATH('')) TMP_NAMES (NAMES)
Kalyan,Kishore,Krishna,Raju,Ramesh,Shiva,Mallik,Naveen,Madhu,Srinu,Harish,Ramkumar

Using the below query we can create as csv

SELECT City, STUFF((SELECT ',' + T.NAME FROM TestIdentity T WHERE T.City = Ti.City FOR XML PATH('')),1,1,'')
FROM TestIdentity Ti GROUP BY City

Identity Columns

Using this property we can create an identity column in a table, An Identity column is a auto incrementing column. A table can have only one Identity column. SQL Server will automatically take care of identity columns. By default the column value will increment by 1, if require we can modify the increment value.

Identity (seed, increment) -- Here seed is the starting value and increment is the growth value.

If neither is specified then it treat as identity (1,1).

Example

CREATE TABLE TestIdentity (Id INT IDENTITY(1,1), NAME VARCHAR(20))

  

If you start inserting rows into the above table then it starts from 1 and increment by 1 from next row onwards.

INSERT INTO TestIdentity VALUES ('Kalyan')INSERT INTO TestIdentity VALUES ('Kishore')
By using DBCC CHECKIDENT command we can verify the current identity value of a table.

DBCC CHECKIDENT(TestIdentity) 
 

The disadvantage of identity property is, if you remove any row from a table then that gap will remains same.

If you want to insert value into identity column manually use the below command Identity_Insert on tablename, It will allow us to insert value into identity field  

SET IDENTITY_INSERT testIdentity ONINSERT
INTO TestIdentity (ID, NAME) VALUES (7, 'Mallik')SET IDENTITY_INSERT testIdentity OFF 
How to find last inserted identity value, SQL Server provides three methods to retrieve the value.
SELECT @@identity -- Returns last identity value irrespective of any table in the current session.SELECT SCOPE_IDENTITY() -- Returns last identity value irrespective of any table in the current scope.SELECT IDENT_CURRENT('TestIdentity') -- Returns the last identity value of the specific table

Monitor Database Activity

If we want to monitor when the last action performed on the database? Or to find database last accessed time? The below query is used to monitor those kind of activities on database.

Note - Create a job which runs every 1 hour with the below query and insert data into a temporary table, later you can do a group by based on database name and host name to identify who used the database recently.

Query

SELECT DISTINCT db_name(sp.dbid) as sysdbname,
sp.loginame, sp.hostname, sp.login_time, sp.last_batch, sp.status, sp.cmd
FROM master..sysprocesses sp

Merge Example in SQL Server

It is very useful to perform inserts, updates or deletes by comparing two different tables easily.

Scenario: For example we have different tables with similar schema in environment, one is in source and one is in target.  Periodically we need to perform target refresh only source table, in that case Merge can be used to match the rows and can perform whatever operations we want.


USE kalyandb

Source Table
CREATE TABLE HOSTS(HOST_ID INT IDENTITY, HOST_NAME VARCHAR(30), IP_Address VARCHAR(30), Location VARCHAR(30), OS_Version VARCHAR(25))

Target Table
CREATE TABLE HOSTS_dup (HOST_ID INT IDENTITY, HOST_NAME VARCHAR(30), IP_Address VARCHAR(30), Location VARCHAR(30), OS_Version VARCHAR(25))

INSERT INTO HOSTS VALUES ('HOST1', '10.22.44.11', 'HYDERABAD','WINDOWS 2003')
INSERT INTO HOSTS VALUES ('HOST2', '10.22.44.12', 'COCHIN','WINDOWS XP')
INSERT INTO HOSTS VALUES ('HOST3', '10.22.44.13', 'PUNE','WINDOWS NT')

INSERT INTO HOSTS_dup VALUES ('HOST1', '10.22.44.01', 'HYDERABAD','WINDOWS 2003')
INSERT INTO HOSTS_dup VALUES ('HOST4', '10.22.44.04', 'HYDERABAD','WINDOWS 2003')

Select * from HOSTS

 HOST_ID HOST_NAME IP_Address Location OS_Version
----------- ------------------------------ ------------------------
1 HOST1 10.22.44.11 HYDERABAD WINDOWS 2003
2 HOST2 10.22.44.12 COCHIN WINDOWS XP
3 HOST3 10.22.44.13 PUNE WINDOWS NT

Select * from HOSTS_dup

 HOST_ID HOST_NAME IP_Address Location OS_Version
----------- ------------------------------ ------------------------------
1 HOST1 10.22.44.01 HYDERABAD WINDOWS 2003
2 HOST4 10.22.44.04 HYDERABAD WINDOWS 2003

 In the above two tables we have two different set of records, Host4 records is not there in source  table   
 but exists in target table, we dont care about target records, so we need to align records as per source table

Merge hosts_dup as s
using (select host_id, host_name, ip_address, location, os_version from hosts) as p
on s.host_id = p.host_id
When MATCHED then
update set s.host_name = p.host_name, s.ip_address = p.ip_address, s.location = p.location, s.os_version = p.os_version
When NOT MATCHED then
insert values (p.host_name, p.ip_address, p.location, p.os_version)
When NOT MATCHED BY SOURCE then
delete;
Select * from HOSTS_dup

HOST_ID HOST_NAME IP_Address Location OS_Version
----------- ------------------------------ ------------------------------
1 HOST1 10.22.44.11 HYDERABAD WINDOWS 2003
2 HOST2 10.22.44.12 COCHIN WINDOWS XP
3 HOST3 10.22.44.13 PUNE WINDOWS NT

 We can write multiple When MATCHED conditions and we can specify and operator also along with When MATCHED to achieve desired output

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

I got the issue when i hit edit button in the job step.

The issue may cause due to some issue with installation. But it is clearly stating interface is not registered.

Search for dts.dll file in DTS folder of Microsoft SQL Server

c:\program files\microsoft sql server\90\dts\binn\dts.dll

c:\> regsvr32 c:\program files\microsoft sql server\90\dts\binn\dts.dll

By registering the dll will fix the issue

Get SQL Query from SPID

SELECT sp.spid, sp.blocked, sp.loginame, sp.last_batch, sp.status, sp.hostname, sp.program_name,
(SELECT text FROM sys.dm_exec_sql_text(sp.sql_handle)) AS 'Query'
FROM SysProcesses sp LEft OUTER JOIN sysdatabases sd
on sp.spid = sd.dbid

What is Data Warehouse

There are various definitions of Data Warehouse. The most popular definition from Bill Inmon is A data warehouse is a “subject-oriented, integrated, timevariant and nonvolatile” collection of data in support of management’s decision-making process.

Subject-Oriented – This can be used to analyze a particular area.
Integrated – This can be used to integrated data from multiple sources.
Time-Variant – Keeps Historical data in data warehouse.
Non-Volatile – Once data keeps in data warehouse, it will not change. Historical data will never be modified.

A Data Warehouse is a copy of transactional data used to perform analysis and reports.
ERP – It will run the business
BI – Used for reports, data mining and analysis based on historical information.
Data warehousing is a process of managing data from various sources to analyze and generate reports based
on business requirements.
What is MSBI – Microsoft 2005 has built in components to implement data warehouse. The pack contains
SSIS, SSRS and SSAS.

Data Warehouse Terminology
OLTP – Online Transaction Processing
OLAP – Online Analytical Processing
Facts – Transaction Tables
Slowly Changing Dimensions
Dimensions – De normalized master tables
Attributes – Columns of Dimensions
Cube – Multi Dimensional storage of data
MOLAP – Multidimensional OLAP
ROLAP – Relational OLAP
HOLAP – Hybrid OLAP
Data Mart – Collection of data for a particular subject

Change Data Capture

The feature is introduced in SQL Server 2008 to track Insert, Update & Deletes made on Tables. Prior to this version to track any information we need to write an additional programming using triggers (After Insert, After Delete etc,) to track the information, using this version we need not put any development effort to achieve this. It tracks only user created tables. If CDC is enabled then it starting reading transaction log for tracking.


To Start with this
1. Needs to enable CDC on the database
2. Need to specify each table name to be captured (for each captured table a mirror table will be created with same structure to store the modified values) We can call them as audit tables

Example
USE master
SELECT [name], database_id, is_cdc_enabled FROM sys.databases

Note - If cdc_enabled parameter returns 1 then it is enabled.

--- -To enable CDC ----
Use Kalyandb
Exec sys.sp_cdc_enable_db
SELECT [name], database_id, is_cdc_enabled FROM sys.databases
The sp_cdc_enable_db stored procedure creates few system tables and creates a schema with name “cdc” in the current database. These will be maintained by system to track the changes.

----Below are system tables----
cdc.captured_columns – Holds the list of captured columns information.
cdc.change_tables – Holds the list of tables which are enabled for capture.
cdc.ddl_history – Holds the history of all DDL changes.
cdc.index_columns – Holds indexes which are related to change table.
cdc.lsn_time_mapping – It is used to map the LSN Number

USE Kalyandb
SELECT [name], is_tracked_by_cdc FROM sys.tables
sp_cdc_enable_table --This stored procedure helps to enable the table to track the DML Operations.

USE Kalyandb
GO
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
@source_name = N'items', @role_name = NULL

Note – Once you invoke the above command then it creates two jobs in the Agent with names dbname_capture, dbname_cleanup and creates a table dbo_items_CT in system tables.

Job 'cdc.kalyandb_capture' started successfully.
Job 'cdc.kalyandb_cleanup' started successfully.

After enabling CDC on a table then from that moment onwards it starts capturing the information into tracking tables. For the above example it creates a table in the system tables section inside the database with table name like this cdc.dbo_items_CT.

The mirror table has 5 additional columns along with original columns which helps to track the changed data. Below is the list.
__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask

Among the above five columns we need to concentrate on two columns __$operation, __$update_mask. The operation column contains the DML Operation such as Insert, Update and delete. The column value indicated with the below number value.

Delete Statement = 1
Insert Statement = 2
Value before Update Statement = 3
Value after Update Statement = 4

Below command is to view on which tables CDC is enabled
USE kalyandb
EXEC sys.sp_cdc_help_change_data_capture

To disable CDC
For enabling CDC first we enabled at database level and then table level, for disable also we need to follow the same procedure.

USE kalyandb
GO
EXECUTE sys.sp_cdc_disable_table @source_schema = 'dbo',
@source_name = 'Items', @capture_instance = 'dbo_items'

Use kalyandb
Go
Execute sys.sp_cdc_disable_db
There is an auto cleanup process that occurs every three days (which can be configured)
Sys.sp_cdc_cleanup_change_table