Wednesday, July 18, 2012

Meaning of auto generated STATISTIC name

Use sys.stats catalog to see all the STATISTICS available for the database. To see the statistics in AdventureWorks2012 database, use the following T-SQL statement.

USE AdventureWorks2012
GO
SELECT * FROM sys.stats

You would see the output something like below; (only the values of name column appeared)

_WA_Sys_00000009_00000005
_WA_Sys_00000005_00000005
_WA_Sys_00000003_00000005
_WA_Sys_00000004_00000005

How do you understand above names.

_WA - Washington, the state of the US where SQL Server development team is located.
All automatically generated statistics have the name starting with _WA_Sys. The first number is the column id of the column which these statistics are based on. The next number is the hexadecimal number of the object id of the table.
Source: Inside the SQL Server Query Optimaztion by Benjamin Nevarez

Tuesday, July 10, 2012

Replication framework between relational database and document oriented (NOSQL) database

I’m working on a research project of building a replication framework from relational database to document oriented (non-relational/nosql) database system. This is to fulfill the research requirement of the MSc degree. The POC would be developed to demonstrate the solution by using MS SQL Server (relational) and mongoDB (document oriented).  I would like to hear any comments/thoughts about this from the community.


I started a discussion on this in LinkedIn mongoDB user group and received couple of valuable comments. Many thanks for those who given the comments. 


You can see them here.

Thursday, July 5, 2012

sp_attach_db / sp_detach_db are deprecated

These two system stored procedures are used frequently by DBAs in general administrative work. However these are marked as deprecated so they will be removed from future versions of SQL Server. I checked in MSDN and from SQL Server 2005 these are marked as deprecated. Interestingly they still available in SQL Server 2012 release as well. However it recommends not use them for any development work and use the new method using CREATE DATABASE statement. 

sp_attach_dbsp_detach_db


Attaching a database using CREATE DATABASE;


CREATE DATABASE Archive
      ON (FILENAME = 'D:\SalesData\archdat1.mdf')
      FOR ATTACH ;
GO

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...