Tuesday, February 26, 2013

SQL Server-RowVersion

In SQL Server one of the recent additon has been change data capture a very useful and a powerful functionality to capture data changes in sql server. One of the other sdditions has been a column datatype called rowversion. The column type rowversion usually is used for situations where one needs to capture deltas with respect to the data. When i use the term delta what i mean is that let us take a scenario: Intially say we load 100 records into a table and then in the next day load around 10 records change, we could use the  rowversion column to identify the rows that changed. What happens is that when a record is inserted or updated the rowversion value changes. Rowversion column type would eventually replace the timestamp column type in sql server. In case data is pulled from a source which is sql server based, the rowversion column type could be added to the source tables in oder to track which records changed. Here is a sample code which can be used: I used MSDN as a reference to get the sample code:

CREATE TABLE MyVer (myKey int PRIMARY KEY

,myValue int, RV rowversion);
GO

INSERT INTO MyVer (myKey, myValue) VALUES (1, 0);
GO
INSERT INTO MyVer (myKey, myValue) VALUES (2, 0);
GO
Here in the sample code above one can see that the column RV is defined as a rowversion data type.  As per MSDN:
Rowversion Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

Once the table is created and data has been inserted, we can run the following statement and look at the values of the RV column:

myKey myValue RV


1          0           0x0000000000000A36

2          0            0x0000000000000A37


Now when the value of the column myValue is update in the table, the value of RV will change. In order to determine the latest value of rowversion in a database , one can use @@DBTS.


One can use the following code, to implement Optimistic concurrency control.

DECLARE @t TABLE (myKey int);


UPDATE MyTest
SET myValue = 2
OUTPUT inserted.myKey INTO @t(myKey)
WHERE myKey = 1
AND RV = myValue;

IF (SELECT COUNT(*) FROM @t) = 0
BEGIN
RAISERROR ('error changing row with myKey = %d'
,16 -- Severity.
,1 -- State
,1) -- myKey that was changed

END

In Summary the rowversion datatype can be used to track changes in the data in sql server.






Monday, February 25, 2013

SQL Server- Soft Skills...

The reason i choose this blog title was because of the numerous opinions raised by lot of folks in the industry stating that in order for one to be competent one needs to able to clearly articulate his ideas and communicate effectively. This blog post is going to highlight the importance of communication and presentation and use one of the blog posts written by a SQL Server Adam Machanic to substantiate this line of thought. The article written by Adam is excellent and very detailed. Please feel free to check out the link below to find out his thoughts on capturing attention. Here is the link below:
http://sqlblog.com/blogs/adam_machanic/archive/2013/02/22/capturing-attention-writing-great-session-descriptions.aspx?CommentPosted=true#commentmessage
Hope you find it useful just as I did by reading through the article. I will return to writing more detailed technical articles in my next post.

Thursday, February 21, 2013

SQL Server Statistics...

In the world of managing data and querying  data in sql server databases one of the areas that require good understanding is the use and meaning of SQL Server Statistics. One of the key areas that is often discussed and required by many businesses is area of performance. There is a very good article on SQL Server statistics by Grant Fritchey called SQL Server Statistics Questions We Were Too Shy to Ask. Here is the link enclosed: https://www.simple-talk.com/sql/performance/sql-server-statistics-questions-we-were-too-shy-to-ask/. He lists very valid questions about statistics and provided detailed explanations.


Monday, February 4, 2013

Tabular Model SSAS 2012...

With SQL Server 2012 released and all the new features being evaluated and implemented one of the areas where there has been a lot of debate is between the SSAS model and the tabular model in the BI domain. The question being raised is which is better, also would tabular model replace the dimension model and many questions related to features and capabilities. I came across a very informative and interesting blog article by Marco Russo, a SQL Server BI expert and a excellent blog writer. In his article titled "Why to use #Tabular in Analysis Services 2012 #ssas " he writes about why tabular model should be given a serious look , to quote him regarding tabular model  "You have to learn it deeper than a casual user, but you will found an unexplored territory of new data modeling and reporting opportunities". Here is the link to the blog article:
http://sqlblog.com/blogs/marco_russo/archive/2012/03/01/why-to-use-tabular-in-analysis-services-2012-ssas.aspx