Archive

Archive for September, 2015

SQL Basics – Data Types in SQL Server (Video)

September 20, 2015 Leave a comment

 


 

–> Here is the notepad file I used in the above video for your reference:
 

– Numerics:

bit – 1, 0, NULL

tinyInt – 1 byte 0 to 255
smallInt – 2 bytes -32,767 to +32,767
int – 4 bytes -2,147,483,647 to +2,147,483,647
bigint – 8 bytes -9,223,372,036,854,775,808 to +9,223,372,036,854,775,808

decimal(p,s)/numeric(p,s) -10^38 +1 to +10^38 +1

smallMoney -214,748.3648 to +214,748.3648
money -922,337,203,685,477.5808 to +922,337,203,685,477.5808
 

– Flaoting point

float & real
 

– Date & Time (Temporal)

date – YYYY-MM-DD
time – hh:mm:ss[.nnnnnnn]
smallDatetime – YYYY-MM-DD hh:mm:ss
datetime – YYYY-MM-DD hh:mm:ss[.nnn]
datetime2 – YYYY-MM-DD hh:mm:ss[.nnnnnnn]
 

– Character Strings

char – 1 to 8000 chars, 8000 btyes
nchar – 1 to 4000 chars, 8000 btyes
varchar – 1 to 8000 chars, 8000 btyes
nvarchar – 1 to 4000 chars, 8000 btyes
[n]varchar(max) – 2GB

here prefix “n” is unicode to store international languages and take double space.
 

– binary Strings

binary – 1 to 8000 bytes
varbinary – 1 to 8000 bytes
varbinary(max) – 2GB
image – 2GB
 

– Other Datatypes

cursor
timestamp
xml
uniqueIdentifier – GUID
Spatial Types (geography, geometry)
sql_variant

… etc


DB Basics – As Primary Key can identify a row uniquely, what are the scenarios you need a Super Key?

September 17, 2015 Leave a comment

 
In one of my [previous post] I discussed about various types of Keys in DBMS, like Candidate, Primary, Composite & Super Keys.

I got few questions like:
1. If there is already a Primary Key to identify a row uniquely, then in which scenario a Super Key can use used?
2. How to create a Super Key like a Primary Key in SQL Server or Oracle?
 

–> First of all what is a Primary Key (PK): It is the Candidate key attribute/column that is most suited to maintain uniqueness in a table at the tuple/row level.

–> And a Super Key is a super-set of Candidate key. If you add any other attribute/column to a Candidate Key then it become a Super Key. Or a minimal Super Key is called a Candidate Key.
 

–> Now consider the same example that I discussed in my [previous post]:

CREATE TABLE Employee (
    EmployeeID, 
    EmployeeName, 
    DOB, -- Date of birth
    DOJ, -- Date of joining
    SSN, -- Social Security Number
    DeptID, -- Department ID
    MgrID -- Manager ID
)

Now in this table there are 2 Candidate Keys i.e. EmployeeID & SSN.
– EmployeeID: should be unique with auto-increment column.
– SSN: should also be unique for every employee.

So, here you cannot make SSN as a Primary Key, because its a PII and secure data.
Hence, creating EmployeeID as Primary Key makes sense, this way SSN will be left as an Alternate Key.
 

–> Now, coming to the Super Key concept: As per the above definition, a minimal Super Key will be called as a Candidate or Primary Key. Thus every Primary Key and Alternate Key is also a Super Key. And if you combine any other column with the EmployeeID (PK) column, the combination will also be called as a Super Key, like:
– EmployeeID
– EmployeeID + DOB
– EmployeeID + DOJ
– EmployeeID + DeptID + MgrID
– EmployeeID + DOB + DOJ + DeptID + MgrID
– EmployeeID + [other column combinations]

For all the above and other possible combinations the row will still be identified uniquely, thus all these combinations will act like a Super Key.

And, if you add all columns with the EmployeeID, it is called a Trivial Super Key, because it uses all columns of Employee table to identify a row.
 

And in SQL Server or Oracle there is no syntax as such to create a Super Key, this is just a concept in DBMS.

Hope this helps !!!


Categories: DB Concepts Tags: ,

Stretch your on-premise Database/Table to Azure SQL Database with StretchDB – SQL Server 2016

September 15, 2015 4 comments

Stretch Database lets you archive your historical data transparently and securely in the Azure SQL Database (i.e. Microsoft PaaS Cloud).

The idea is to have an On-Prem database configured to allow extension of one or more of its tables to a SQL Azure Database.

This extension to Azure can archive transparently “cold” data without change access to information requirements when the table is queried, all this while enjoying the very attractive storage cost proposed by Microsoft Azure.

SQL Server 2016 - StretchDB.JPG
 

–> We will see here how we can configure a Database for Stretch and enable it in a table in simple steps.

Step #1. Configure SQL Server instance to enable “Remote Data Archive”

USE [master]
GO

EXEC sp_configure 'remote data archive', '1';
RECONFIGURE;
GO

 

Step #2. Enable Database for Stretch to Azure

2.a. Enable a Database for Stretch from SSMS:

SQL Server 2016 - StretchDB 01

2.b. Sign-in to Microsoft Azure with your subscription ID:

SQL Server 2016 - StretchDB 02

2.c. Provide your preference for Azure Datacenter and login credentials:

SQL Server 2016 - StretchDB 03

2.d. Final page showing successful completion of enabling StretchDB:

SQL Server 2016 - StretchDB 04

2.e. Now go to the Azure portal and check the new SQL DB created by the Enable process:

SQL Server 2016 - StretchDB 05

2.f. Check the SQL Instance settings and DB Server name to connect it from SSMS:

SQL Server 2016 - StretchDB 06

Now we have a new Azure SQL Database created that is paired up with our On-prem SQL Server instance Database. We can not enable individual tables that we want to stretch to Azure.
 

Step #3. Now Create a table and insert some sample data in batches

USE [StretchDB]
GO

CREATE TABLE dbo.EmployeeStretch (
	EmployeeID	INT IDENTITY(1,1),
	EmployeeName VARCHAR(1000)
)

-- Batch 1
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT 'Manoj P'
UNION ALL
SELECT 'Saurabh S'
UNION ALL
SELECT 'Keshav K'
UNION ALL
SELECT 'Vivek S'
UNION ALL
SELECT 'Ganesh S'

select * from dbo.EmployeeStretch

 

Step #4. Enable Table for Stretch

With SSMS only you can enable a table for Stretch with 1-2 simple clicks, check the image below:

SQL Server 2016 - StretchDB 07

So, as soon as you enable this table for stretch the process creates an mutually equivalent table in Azure SQL DB with similar name which can be seen immediately via SSMS, check below:

SQL Server 2016 - StretchDB 08

On Querying this table the Stretch process adds an extra column with name “batchid–[object_id]” suffixed with the table ID belonging to On-prem SQL DB, check below:

SQL Server 2016 - StretchDB 09
 

Step #5. Load some more data in batches

-- Batch 2
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT 'Kaushik S'
UNION ALL
SELECT 'Deepak B'

select * from dbo.EmployeeStretch

-- Batch 3
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT 'Nitya S'
UNION ALL
SELECT 'Hema S'

select * from dbo.EmployeeStretch

-- Batch 4
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT TOP 100 CONCAT([FirstName], ' ', [LastName]) FROM [AdventureWorks2014].[Person].[Person]

select * from dbo.EmployeeStretch

As we’ve done INSERT in separate 4 Batches, thus you can see Batch IDs assigned to records from 1-4 under the batchid column of the Stretch table on Azure SQL DB, below:
SQL Server 2016 - StretchDB 10
 

–> Check the stats, i.e. space used by the table in on-prem and Azure DB:

sp_spaceused @objname = 'dbo.EmployeeStretch'
--Output: 
--name	               rows  reserved  data   index_size  unused
--dbo.EmployeeStretch  109   48 KB     16 KB  32 KB	  0 KB

sp_spaceused @objname = 'dbo.EmployeeStretch', @mode = N'LOCAL_ONLY'
--Output: 
--name	               rows  reserved  data   index_size  unused
--dbo.EmployeeStretch  0     16 KB     8 KB   8 KB        0 KB

sp_spaceused @objname = 'dbo.EmployeeStretch', @mode = N'REMOTE_ONLY'
--Output: 
--name	               rows  reserved  data   index_size  unused
--dbo.EmployeeStretch  109   32 KB     8 KB   24 KB       0 KB

The above stats clearly shows that the space used by Stretch enabled table in On-premise and On Azure SQL DB. As the data is moved behind the scenes from On-Prem to Azure SQL DB the space used by the table in On-prem is negligible i.e. some minimal default space.

–> Let’s load some more data as a 5th batch

-- Batch 5
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT TOP 1000 CONCAT([FirstName], ' ', [LastName]) FROM [AdventureWorks2014].[Person].[Person]

select * from dbo.EmployeeStretch

–> Now again check the space used:

sp_spaceused @objname = 'dbo.EmployeeStretch', @mode = N'REMOTE_ONLY'
--Output: 
--name	               rows  reserved  data   index_size  unused
--dbo.EmployeeStretch  1509  288 KB    80 KB  80 KB       128 KB

This again shows that whatever data you load on an On-Prem Stretch-enabled table it moves all data to Azure SQL DB without taking any space in On-prem DB, but on Azure SQL DB.
 

–> Some DMVs that you can use to check the stats of the data migration and Database’s & Table’s Stretch status:

select * from sys.dm_db_rda_migration_status

select * from sys.remote_data_archive_databases

select object_name (885578193), * from sys.remote_data_archive_tables

 

–> Let’s try to DELETE the Stretch Enabled table:

DELETE FROM dbo.EmployeeStretch WHERE EmployeeID >= 10

It throws following error:

Msg 14826, Level 14, State 1, Procedure trigger_RemoteDataArchive_885578193, Line 94
Update and delete of rows eligible for migration in table ‘EmployeeStretch’ is not allowed because of the use of REMOTE_DATA_ARCHIVE.
Msg 3609, Level 16, State 1, Line 79
The transaction ended in the trigger. The batch has been aborted.

 

Please Note: As of CTE 2.3 release you can’t run UPDATE or DELETE operations on a Stretch-enabled table.
 

You can check the same demo to configure StretchDB here:


 

Check & Like my FB Page.


SQL Server Geeks Annual Conference sessions – SSGAS 2015

September 4, 2015 Leave a comment

This year I got an opportunity to attend the SSGAS 2015 i.e. SQL Server Geeks – Annual Summit, the first time ever, large scale professional SQL Conference in India, Bangalore (KA), India, on Aug 27-29, 2015.
 

11952925_10153595838942152_239524521059569587_o
 

This was a 3 day conference organized by the SQLServerGeeks team, headed by Amit Bansal, covering sessions on Database Development, Administration, BI & Analytics, Big Data and Cloud & Azure Platform, delivered by expert speakers from Microsoft, MVP & MCM Community and Industry leaders.
 

11882831_10153595836172152_4025091312935596970_o
 

I would like to share the sessions/topics I attended, their details and links to external references with my readers.

Following are the sessions I attended in the 3 day conference:
 

SSGAS 2015 | Day – 1

Topic Speaker Session Details & external links
SQL Server 2016, new innovations Sanjay Mishra (MSFT) Introduced various new features in SQL 2016, link.
Azure SQL Database, now & future Lara Rubbelke (MSFT) Detailed deep dive on Azure SQL database, link.
Tabular vs Multidimensional in SSAS Alberto Ferrari Details and differences between the two, link.
Business Prediction using Microsoft Azure ML Deepthi Anantharam (MSFT) Demoed a scenario on predicting income by reading different metrics, link.
Column Store Index: SQL Server 2014 & beyond Sunil Agarwal (MSFT) Detailed deep dive on ColumnStore tech, link.
Advanced SQL 2014 Reporting Tech Dr Subramani Paramasivam Detailed deep dive on Reporting Services (SSRS), and demoed how to configure Reports, connections and properties.

 

SSGAS 2015 | Day – 2

Topic Speaker Session Details & external links
In Memory OLTP: SQL Server 2014 and beyond Sunil Agarwal (MSFT) Detailed deep dive on In-Memory tech, link.
SQL Server Performance Tuning Amit Bansal/ Manohar Punna (SSGAS) Demoed performance tuning by using Query Store tool, a new feature of SQL 2016, link.
Analytics with Column Store Index Praveen Srivastava Discussed and demoed ColumnStore indexes, link.
Troubleshooting SQL Server Tips & Tech Amit Khandelwal/ Sumit Sarabhai (MSFT) Customer centric perf issues, creating dump by using SSDIAG & PSDIAG tools, and their dump analysis by SQL Nexus.
Query Processing in In-Memory OLTP Benjamen Nevarez (MVP) Discussed about In-Memory features in SQL 2014/2016, link.
Big Data for the SQL Ninja Scott Klein (MSFT) Demo on spinning up a new Hadoop cluster and Blob Storage on Azure.

 

SSGAS 2015 | Day – 3

Topic Speaker Session Details & external links
Dive into the Query Optimizer Benjamen Nevarez (MVP) Discussed QO internal and Trace flags, link.
SQL Server under attack via SQL injection Anderas Wolter (MCM) Preventing SQL Injection, link.
Operational Analytics in SQL Server Sunil Agarwal (MSFT) Real time analytics with ColumnStore, link.
Understanding Parameter Sniffing Benjamen Nevarez (MVP) Checking and resolving incorrect plans created by SQL DB engine, link.
SQL Server 2016 Stretch Database Stuart Padley (MSFT) Stretch Database demo to stretch on-prem SQL table to Azure DB, link.
Dealing with Bad Roommates – Resource Governor Joey D’Antoni (MVP) Discussed about how to manage SQL workload and system resource consumption, link.
Evangelizing SQL Server Dev paradise Mahendraprasad Dubey/ Narendra Angane (MSFT) Customer centric perf issues due to Blocks, Waits and Latches, resolving them by refactoring their SQL Code.
Social Media Sentiment Analysis using Hadoop on Windows Debcharan Skarkar (MSFT) Intro, demo.
Bursting Through the power of Datazen Slevakumar Rajakumar (MSFT) Datazen Intro, Server & Client details, vs Power BI & SSRS

 

Some social-networking, met with some tech-gurus:

11950421_10153595838007152_7307917696804527762_o

With Scott Klien, [twitter], the Microsoft Channel9 host.
 

11895306_10153595837672152_7826040929249792430_o

With Benjamin Nevarez, [twitter], [blog], the SQL Server Performance guru and author of Microsoft SQL Server 2014 Query Tuning & Optimization book.

And Alberto Ferrari, [twitter], [blog], founder at SQLBI and author of Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model book.
 

11113257_10153595837972152_3300664183761273059_o

The wall!
 

11887758_10153595841867152_4004313675303231303_o

at NIMHANS Convention Center, Bangalore (KA), India


SQL Server 2016 CTP 2.3 update available | download now

September 4, 2015 Leave a comment

Microsoft announced the next update of the recently released Community Technology Preview (CTP) 2.2 version of SQL Server 2016, the CTP 2.3, link.
 

[Register and Download the CTP 2.3 Evaluation version (180 days) here]
 

Direct download link for ISO: SQLServer2016CTP2.3-x64-ENU.iso ~ 2.6 GB
 

Check version and SQL build: select @@version
Microsoft SQL Server 2016 (CTP2.3) – 13.0.500.53 (X64) Aug 24 2015 03:51:16 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 10240: )
 
–> Enhancements and Issues fixed in this release:

1. Row Level Security support for In-memory OLTP tables, with Natively Compiled UDF support, demo video.

2. In-memory DW (Data Warehouse) ColumnStore performance optimizations. NCCI can now be created on tables with triggers, enabled with CDC/Change Tracking, demo video.

3. Performance improvements to SSAS, including DAX query performance, DirectQuery enhancements, support for variables in DAX.

4. Enhancements to SSRS, including an updated Report Builder with a modern theme and report rendering for modern browsers built on HTML5 standards.

5. Enhancements to SSIS, by releasing oData v4 protocol support, SSIS Error Column support, and advanced logging levels.

6. Improvements to MDS, Many to many derived hierarchy, Excel Add-in Business rule management, and Merge conflicts.

7. Core engine scalability improvement, dynamically partitioning thread safe memory objects by NUMA or CPU, which enables higher scalability of high concurrency workloads running on NUMA hardware.

8. Improvements to the Query Execution with improved diagnostics for memory grant usage.

9. DBCC CHECKDB includes Performance improvement with Persisted computed columns & filtered indexes validation, and Validating a table with thousands of partitions.
 

For all other new features released in SQL Server 2016, please check my blog posts here.
 

So, download the Preview today and start playing with the new features and plan your DB migration/upgrade.
 

Check the [SQL Server blog] for all these updates in detail.
 


 

Check & Like my FB Page.