MS SQL Server

ByHariharan Rajendran

Database Table and Primary Key – Part 2

In my previous article, I have explained about the table creation and primary keys.

In real time scenarios, a primary key field should generate values automatically whenever a new row added to the table.

If the primary key field is an integer then set up an identity

  1. Identity needs to be defined in (X, Y) format
  2. Define the value for X which is starting value and Y is an increment value.

CREATE TABLE Users

(Id int primary key identity (1,1),

Name varchar(50))

This table will generate a value for Id starting from 1 and increment by 1. The column values will be 1, 2, 3, .etc.

Primary key on unique identifier field,

CREATE TABLE Users

(Id uniqueidentifier default NEWID() primary key,

Name varchar(50))

This will generate random new GUID value for each row.

Choosing a right primary key column is very challenging.

We can create a primary key on any of the columns but we need to understand the business requirement.

Consider, if you are creating a primary key on Integer field. It has both advantages and disadvantages. Most of the time people will consider its advantages and start using integer as primary key column for all the tables.

Points to remember,

  1. Performance wise, this is the best option
  2. Applying and rebuilding the index is very easy
  3. If we consider a big environment where data storage is important. Then int will hold values up to Inserting records more than this limit will break the table.

Primary Key on unique identifier field,

Points to remember,

  1. It is very good idea to use unique identifier as a primary key column when data is less in the table
  2. It will occupy more disk space
  3. If data is huge then indexing and rebuilding are very hectic.

Primary key on varchar field, consider an emailid field

Point to remember,

  1. Sometimes we might think that our table should maintain a unique email address so considering this we may create aprimary key on email field. This is not advisable.
  2. Email address is a field which will change over the period of time.
  3. If considering this as a primary key field and involved on many to many relationships then if any changes to the email address, it will affect the whole database.
ByHariharan Rajendran

Database Table and Primary Key – Part 1

Creating a table in MS SQL Server is very easy, this can be achieved by using T-SQL scripts or GUI. This article will use T-SQL Scripts to a create a table.

Basic Pre-Requisites to create a table,

  1. Understand the purpose of the table
  2. Identify the columns and data types. Refer this link, to know about data types.

Table is the storage unit in a database which stores the data in rows and columns as like excel sheet. The output of the table result is similar to the excel sheet data.

If you are decided to store some data in a database then you must use table.

The basic table structure will look like below.

CREATE TABLE Users

(Id int)

We can add more columns in the structure, each column should be define with the right data type. Here Id is the column name and int is the data type which is nothing but an integer.

Let us discuss, what is a primary key and how to create it.

Primary key is a constraint which we need to define in any one of the column in a table. It will not allow null values in that defined column and entry to the column should be unique  i.e. it won’t allow duplicate values.

Choose an ID or Unique Identifier (GUID) column to create a primary key, this is the best way to define a table structure.

Create a table with primary Key on integer column,

CREATE TABLE Users

(Id int primary key,

Name varchar(50))

In the above script, ID column is considered as a primary key. You can check this by clicking the columns folder under dbo.Users table.

Create a table with primary key on unique identifier column,

CREATE TABLE Users

( Id uniqueidentifier primary key,

Name varchar(50))

ByDr. SubraMANI Paramasivam

SQL Server 2016 CTP 3 in Azure VM

Though SQL Server 2016 CTP 3.2 was released in mid Dec 2015, you can download the latest version from this link or you can also choose CTP3 to try by creating Azure Virtual machine with this link.
After clicking on this link, you need to login to Microsoft Azure portal with your subscription and then you can see below image, which talks about the legal terms, documentation, etc.,
SQL2016CTP3_AzureVM_01
Once you have validated, you can click on the “Create” button, which will then take you to below screenshot which has simple 5 steps to create the VM with SQL Server 2016 CTP3.2.
SQL2016CTP3_AzureVM_02
Then, I have provided below details in step 1 and clicked okay.
Server name: DAGEOP2016CTP32
user name: MSSQLCTP32
password: Password1!
Resource Group: MSSQLGroup
Then, in step 2, I have moved on to creating the D1 standard size that looks like below screenshot. You can choose the right size according to your requirements. If you don’t see all sizes, then you need to click on Show all instead of Recommended options.
SQL2016CTP3_AzureVM_03
Then in Step 3, it gives all the settings option for Storage account, virtual network, subnet, public IP address, Network security group. You can continue to choose the defaults or choose from already existing accounts if you have any.
SQL2016CTP3_AzureVM_04
In step 4, you will have further options to configure SQL Server settings like SQL authentications, port, connectivity, etc.,
SQL2016CTP3_AzureVM_05
In step 5, summary you can see all the configured features that will enable you to review at once and then click on OK button.
SQL2016CTP3_AzureVM_06
This will directly take you to the dashboard and you can see below screenshot which deploys the SQL Server 2016 CTP3 Evaluation. This will take minutes to hours to get it deployed and make the VM available for you to use.
SQL2016CTP3_AzureVM_07
ByHariharan Rajendran

PolyBase Services in SQL Server 2016 CTP

Microsoft SQL Server 2016 has one of the exciting feature called PolyBase. It is acts as a bridge between relational database and Hadoop.

We can access all structured, unstructured and semi-structured data using known language of SQL.

Polybase allows user to use T-SQL Statements in Microsoft SQL Server Management studio to access data stored in Hadoop or Microsoft Azure blob storage.

Below services should up and running to use this feature.

  1. SQL Server PolyBase Data Movement
  2. SQL Server PolyBase Engine

To check the installed services in system,

  1. Press Ctrl + R (Run command)
  2. Type services.msc and enter

polybase

ByHariharan Rajendran

Secrets behind Query processing Speed in Columnstore Index

Go through my previous article here, about Data storage format in columnstore Index.

There are lots of improvements in Query processing speed when we use columnstore  Index.

  1. The data are organized in a column. As a result, a much higher level compression is possible, compare than row store.
  2. It uses Vertipaq technology for data compression. The Compression rate can be achieved up to 15 to 1. Click here, to know about Vertipaq technology.
  3. Compressed data require only less IO because the data transferred between disk to memory are reduced.
  4. Reducing IO leads to improvement in query processing and response times.
  5. Amount of data transfer to memory is going to be less which leads less space is required in memory to hold the data which processed by the query.
  6. SQL Server fetches only the data which are required for the query. For example, data is maintained in a column from C1 to C10, if the required data residing only on C6 & C7 then it will fetch only those columns.
  7. It uses the advanced technology for processing the queries. The data in the columns are processed in batches using high-efficient vector technology. This processing mode is called “Batch-Mode Processing”.
ByHariharan Rajendran

Temporal Table in SQL Server 2016

Temporal is a database feature that was introduced in ANSI SQL 2011 and this is now supported in SQL Server 2016.

It is a new type of user defined table and designed to keep track of all historical changes on the table and make easy to do point in time analysis.

Main reasons to use temporal table,

  1. In real time, data is not going to be static. To track the changes (Auditing) of the data over a point of time.
  2. Recovering from accidental data changes.

Prerequisites to create temporal table,

  1. The table should have a primary key column
  2. Two columns should have datatime2 data type to track the data validity (from & to).

Syntax to create Temporal Table

CREATE TABLE <Table Name>

(

<column 1> int NOT NULL PRIMARY KEY CLUSTERED

,<column 2> <datatype>

,.

,.

, <column n>

, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START

, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = <schema>.<tablename> –Optional));

We can give any name instead of ValidFrom and ValidTo, both should be bind in PERIOD.

ByHariharan Rajendran

Vertipaq

Vertipaq is an engine which was found in Power Pivot for Analysis server in SQL Server 2008 R2. Basically, it is used for data compression. Data retrieval and calculations are happening at much faster rate as it holds the compressed data in memory.  It is also called xVelocity and it has been added to SQL Server 2012. As a result, it is delivering very huge performance improvements for data warehouse and business intelligence solutions.

Columnstore index using this compression algorithm technology.

This algorithm using following compression techniques.

  1. Value Encoding
  2. Dictionary Encoding
  3. Run Length Encoding (RLE)

Check this link, for detailed information about VertiPaq Compression techniques.

ByHariharan Rajendran

Data Storage Format in Columnstore Index

Go through my previous article purpose of columnstore index.

Normally SQL Server stores the data in pages in a row based manner for tables (heaps) and B-tree indexes. This is the traditional approach and technically this is called “Row store”.

Column store is something like turning traditional storage model into 90 degrees. In this approach, all the values in single column will be in compressed form. Column store index stores each column in a separate set of disk pages which is different from the traditional storage format.

Let’s see an example,

ID Name Department
1 xyz Production
2 Robert Information Technology
3 Mary Sales
4 John Account

 

Row store
1 xyz Production
2 Robert Information Technology
3 Mary Sales
4 John Account

 

Column Store
1 2 3 4
Xyz Robert Mary John
Production Information Technology Sales Account

Column store index is more compressed than the row store. This can understand by seeing above examples. In row store format we have different data types involved in a single row (ID is Integer, Name is string, Department is string). Row store method trying to compress the different data type fields, but in column store, all the values in a same data type so it is easy to compress the data.

Data Compression percentage of column store is greater than the row store.