Posts Tagged ‘SQL Server 2014’

Good news – Microsoft makes SQL Server “Developer Edition” free for developers !

April 1, 2016 3 comments

Microsoft on 31st March 2016 announced the free availability of Developer Edition of SQL Server, currently 2014. The Developer Edition is meant for development and testing only, and not for production environments or for use with production data.

SQL Server 2014 Dev Ed free

With SQL Server 2014 Developer edition developers can build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.

So, with this edition you are getting the Database Engine as well as DW/BI capabilities ( i.e. SSIS /AS /RS) for free 🙂


[Download the SQL Server 2014 Developer Edition free here]

This is a very good news for Developers, as till now the SQL Server Express edition used to be the free database for entry-level development, which has lot of limitations like:

1. Only Database Engine, no DW/BI suit (absence of SSIS/RS/AS).

2. Max size of a DataBase is set to 10GB (but you can create multiple databases)

3. No SQL Agent

4. Single CPU utilization

5. Max 1 GB RAM allocation

6. Max 16 number of instances per server

Hence this is a very good deal that you can now get full featured suit of SQL Server with Database Engine as well as with all DW/BI capabilities for free to play, develop and learn with.


Exam 70-461, SQL 2014 update – Querying Microsoft SQL Server 2014

December 1, 2015 7 comments

In one of my previous post I talked about [Exam 70-461 for SQL Server 2012], and I really got good and big response, with ~250 comments. As SQL Server 2014 got released most of the folks asked me about the certification update.

So here in this post I’ll talk about the same exam 70-461 which is now updated for SQL Server 2014, and Microsoft has not changed the exam code for this version of SQL Server.

This updated certification exam is still based on SQL Server 2012 with new features of SQL Server 2014. Previously with SQL 2012 the exam was divided into 4 modules, please check my previous post, link above. Here with SQL 2014 update the exam is reshaped into 20 modules listed below:

1. Introduction to Microsoft SQL Server 2014
– SQL Server architecture | MSDN
– Editions | MSDN
– SQL Server Management Studio | MSDN

2. Introduction to T-SQL Querying
– T-SQL intro | MSDN
– Set based queries
– Predicate Logic | MSDN
– SELECT statement Logical Ordering | MSDN

3. Writing SELECT Queries
– Simple SELECT queries | MSDN
– Find & eliminate duplicates, and DISTINCT keyword
– Working with Columns
– CASE expressions | MSDN

4. Querying Multiple Tables

5. Sorting and Filtering Data
– Sorting data with ORDER BY clause | MSDN
– Filtering data with WHERE clause MSDN
– Filtering data with TOP keyword | MSDN
– Using TOP-OFFSET clause | MSDN | TechNet
– Working with NULL/unknown values

6. Working with SQL Server 2014 Data Types
– New SQL 2014 datatypes (Character, Date, Time) | MSDN
– Write Queries using different Datatypes | MSDN

7. Using DML to Modify Data
– Inserting Data | MSDN
– Modifying Data | MSDN
– Deleting Data | MSDN

8. Using Built-In Functions
– SQL 2014 new functions (Conversion, Logical)

9. Grouping and Aggregating Data
– Using Aggregate functions | MSDN
– Using GROUP BY clause | MSDN
– Using HAVING clause | MSDN

10. Using Sub-Queries
– Self-Contained Subqueries | TechNet
– Correlated Subqueries | TechNet
– Using EXISTS() predicate with Subqueries | TechNet

11. Using Table Expressions
– Using Views | MSDN
– Using Inline Table Values functions | MSDN | TechNet
– Using Derived Tables
– Using CTEs (Common Table Expressions) | MSDN | TechNet

12. Using Set Operators
– The UNION, UNION ALL Operator | MSDN
– Using APPLY (CROSS & OUTER) | TechNet

13. Using Window Ranking, Offset, and Aggregate Functions
– Window functions with OVER() clause | MSDN
– More Window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) | MSDN

14. Pivoting and Grouping Sets
– Writing queries with PIVOT and UNPIVOT | MSDN | TechNet
– Using Grouping Sets | MSDN | TechNet

15. Querying data with Stored Procedures
– Creating Stored Procedures (SP) | MSDN
– Using Parameters with SPs | MSDN
– Working with Dynamic SQL | MSDN
   – sp_ExecuteSQL | MSDN

16. Programming with T-SQL
– Working with Variables, Batches | MSDN | TechNet
– Using IF conditions | MSDN
– WHILE loops | MSDN

17. Implementing Error Handling
– Using TRY/CATCH block | MSDN
– Working with Error Handling | MSDN
– Returning error information
– Raising user-defined errors and passing system errors | MSDN

18. Implementing Transactions
– Using Transactions | MSDN
– Use of “SET XACT_ABORT” with Transactions | MSDN | MSDN-2
– Effects of Isolation Levels on Transactions | MSDN

19. Improving Query Performance
– Factors affecting Query Performance | MSDN
– Checking Execution plan
– Optimize SQL queries, Query tuning | MSDN
– Optimize SQL Indexes, Index tuning | MSDN

20. Querying SQL Server Metadata
– Querying System Catalog Views | MSDN
– Querying System Catalog Functions | MSDN
– Using System Stored Procedures | MSDN
– Using Dynamic Management Objects (DMVs, DMFs) | MSDN

–> Check SQL Server 2014 videos here:

I’ll be updating the above post going forward and as I post something related to SQL Server 2012 & 2014.

Please provide your comments if you want me to talk about any of the above items, thanks !

Getting started with SQL Server 2014 | Download and Install Free (Express) or Full version

May 19, 2015 9 comments

How to download SQL Server?

What are the available options and versions?

Isn’t there any Free version to play with, learn and practice SQL?

I get emails regarding these question many a times from my readers, sometime from my friends and old colleagues too. So I thought to collate all this information into this single post so that people won’t have to search here and there on internet and finally end up nowhere or to any junk sites.

Currently the latest version of SQL Server from Microsoft is SQL Server 2014 released last year on 1st April 2014, and its first Service Pack (SP1) was released just few days back (15th May 2015). You have following options to choose from:

1. SQL Server Full version contains the Database Engine with whole BI suit and is a paid version and you have to buy license key from your MSDN Subscriptions. It is also available for free download for evaluation of 180 days, but post that you have to buy a license key.

2. SQL Server Express is a Free version that can be used by anyone, like students, small setups and companies, etc. It is a lite version which contains the Database Engine with some limitations like: max size of a DataBase is set to 10GB (but you can create multiple databases), no SQL Agent, single CPU utilization & max 1 GB RAM allocation. I think this is a good package and configuration you are getting it for free. And a best option for students and for beginners in SQL if they want to learn and practice SQL Querying without investing anything.

–> Check this video on how to download SQL Server with above two options:

You can also directly download the Express bits from here:

– SQL Server 2014 Express and Tools (DB Engine with SSMS): x64 | x86

– SQL Server Management Studio (SSMS only) 2014: x64 | x86

– SQL Server 2014 Express and Tools with Advanced Services: x64 | x86

… with the Advanced Services option you get extra tools like Fulltext Search and Reporting Services.

After downloading the bits from Microsoft site you are now ready to install SQL Server.

–> Check this video on how to install SQL Server (its very easy, but just in case):

–> After install completes you can download the sample database AdventureWorks2014 for SQL Server 2014 from CodePlex and restore the Backup (.BAK) file to SQL Server, click on the image below:


AdventureWorks 2014 Sample Database Released! – for SQL Server 2014 (Hekaton)

August 5, 2014 1 comment

Sample DataBases AdventureWorks 2014 for SQL Server 2014 has been released and is ready for download, [link].

The sample includes various flavors of samples that you can use with SQL Server 2014, and are:
1. OLTP Database
2. DW Database
3. Tabular Model Database
4. MultiDimensional Model Database

So download these now and start practicing and working on 2014!!!


–> Check the same demo on YouTube:

Maintaining Uniqueness with Clustered ColumnStore Index | SQL Server 2014

July 24, 2014 5 comments

Column Store indexes were introduced in SQL Server 2012 with a flavor of Non-Clustered index i.e. “Non-Clustered ColumnStore” index. However there is a big limitation that the underlying table becomes read-only as soon as you create one.

In SQL Server 2014 this behavior is unchanged and addition to this you can also create ColumnStore index as a Clustered index. And the good thing is that the table having “Clustered ColumnStore” index can also be updated. However there is one more big limitation here that there is no Clustered Key with this type if index, thus risking the Uniqueness in the table.

–> Here we will see this limitation and a workaround which can be used in some scenarios:

USE tempdb

-- Create a simple table with 3 columns having 1st column to contain Unique values:
	PKCol int NOT NULL,
	Foo int,
	Bar int

-- Now create a "Clustered ColumnStore" index on this table:

Notice: While creating this index there is no provision to provided the “Clustering Key”, as this index includes all of the columns in the table, and stores the entire table by compressing the data and store by column.

On checking the metadata (by ALT+F1) of the table, you will see NULL under the index_keys column:


– Now let’s check this feature of absence of Uniquenes. We will enter 2 records with same value:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33

SELECT * FROM dbo.TableWithCCI

You will see 2 records with same duplicate value.

– Now, let’s create another Unique index to enforce this constraint:


We get an error that you cannot create more indexes if you have a Clustered ColumnStore index:

Msg 35303, Level 16, State 1, Line 25
CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.


–> Workaround: As a workaround we can create an Indexed/Materialized View on top this table, with Clustering Key as the PK (1st column of the table/view):

CREATE VIEW dbo.vwTableWithCCI 
	SELECT PKCol, Foo, Bar
	FROM dbo.TableWithCCI

-- Delete duplicate records entered previously:

-- Create a Unique Clustered Index on top of the View to Materialize it:

– Now let’s try to enter duplicate records again and see if these can be entered or not:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33

– As expected we get an error after we inserted 1st records and tried to insert the 2nd duplicate record:

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 48
Cannot insert duplicate key row in object ‘dbo.vwTableWithCCI’ with unique index ‘IDX_vwTableWithCCI’. The duplicate key value is (1).
The statement has been terminated.

–> Not sure why Microsoft has put this limitation of not maintaining the Uniqueness with these indexes. While using this workaround you need to consider this approach if possible. Like in some scenarios where the table is very big and there are frequent updates (INSERT/UPDATE/DELETES) this approach of maintaining another Indexed-View would be expensive. So this approach should be evaluated before implementing.

-- Final Cleanup:
DROP VIEW dbo.vwTableWithCCI


I look forward in new versions of SQL Server to address this limitation.
You can also refer to MSDN BOL [here] for checking all limitations with ColumnStore Indexes.

[Update as of May-2015] with SQL Server 2016 you can make unique Clustered ColumnStore Index indirectly by creating Primary/Unique Key Constraint on a heap with a Non-Clustered Index, [check here].

Update: Know on ColumnStore Indexes as of SQL Server 2016:


Check & Like my FB Page