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.
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 🙂
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.
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:
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
8. Using Built-In Functions
– SQL 2014 new functions (Conversion, Logical)
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 !
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:
… 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:
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:
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 GO -- Create a simple table with 3 columns having 1st column to contain Unique values: CREATE TABLE dbo.TableWithCCI ( PKCol int NOT NULL, Foo int, Bar int ) GO -- Now create a "Clustered ColumnStore" index on this table: CREATE CLUSTERED COLUMNSTORE INDEX CCI_TableWithCCI ON dbo.TableWithCCI GO
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 GO SELECT * FROM dbo.TableWithCCI GO
You will see 2 records with same duplicate value.
– Now, let’s create another Unique index to enforce this constraint:
CREATE UNIQUE INDEX UX_TableWithCCI ON dbo.TableWithCCI(PKCol) GO
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 WITH SCHEMABINDING AS SELECT PKCol, Foo, Bar FROM dbo.TableWithCCI GO -- Delete duplicate records entered previously: DELETE FROM dbo.TableWithCCI GO -- Create a Unique Clustered Index on top of the View to Materialize it: CREATE UNIQUE CLUSTERED INDEX IDX_vwTableWithCCI ON dbo.vwTableWithCCI(PKCol) GO
– 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 GO
– 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 GO DROP TABLE dbo.TableWithCCI GO
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: