Systems Engineering and RDBMS

Posts Tagged ‘SQL Server 2012’

Online Index Operations involving certain large data types are allowed in SQL Server 2012

Posted by decipherinfosys on April 3, 2012

Prior to SQL Server 2012, when you did online index operations (available in Enterprise Edition), if the index contained certain large data types (specifically varchar(max), nvarchar(max), varbinary(max)), then those online operations were not allowed on that index.  That restriction does not exist anymore in SQL Server 2012 so there is no need to take such indexes out of your online operations.

Example:  Let’s create a sample table which contains two columns – an ID column of type INT and the second column of data type NVARCHAR(MAX) and then let’s try to create an index on it with ONLINE option set to ON.

CREATE TABLE tblTestOnlineIdx
(
ID INT IDENTITY NOT NULL
, COL1 NVARCHAR(MAX) NOT NULL
, COL2 XML NOT NULL
)
GO
CREATE INDEX tblTestOnlineIdx_IND_1 on tblTestOnlineIdx (ID) INCLUDE (COL1) WITH (ONLINE = ON)
GO

We will see that we will get an error in SQL Server 2008 telling us that:

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index ‘tblTestOnlineIdx_IND_1’ because the index contains column ‘COL1’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

And this same code will run fine on SQL Server 2012 because this limitation has been removed.  However, even though the BOL link above states that the same is true for indexes containing a XML data type column, that is not the case.  ONLINE=ON is not a valid option for the XML indexes and it will still give an error.  That is reflected in the BOL link here which covers the creation of XML indexes.

Posted in SQL Server | Tagged: , | Leave a Comment »

Editions in SQL Server 2012 and Licensing

Posted by decipherinfosys on March 27, 2012

SQL Server 2012 is available in these editions:

  • Enterprise Edition
  • Standard Edition
  • Business Intelligence Edition
  • Developer Edition
  • Web Edition, and
  • Express Edition

Out of these, the Enterprise Edition is of course the one that has all the high end features for meeting the needs of a Tier-1 large scale deployment.  You would recall that previously there was a DataCenter Edition available – that is no longer available now and all of it’s capabilities are now included in the Enterprise Edition itself.

The Business Intelligence Edition is a new Edition that is offered now.   This is available to offer clients the full suite of powerful BI features in SQL Server 2012 like Power View, PowerPivot, enhanced reporting and analytics.  Any company specifically looking at SQL Server from the perspective of BI and not interested in the full OLTP features that are found in the Enterprise Edition would opt for this one.

The Standard Edition is aimed towards customers who do not need the high end features that are available in the Enterprise or the Business Intelligence Editions.

Out of the remaining three specialized Editions, the Developer Edition (like in previous versions) has all the features of the Enterprise Edition but is meant only to be used in a Dev/QA environment and does allow for a direct upgrade to the Enterprise Edition.  The Web Edition is essentially intended for the hosting service providers.  It is scaled down version in terms of support for processors (max: 4) and the feature sets but is good enough to be used if you are looking for an edition to host web-sites and small scale web applications.  It supports up-to 64GB of memory.  The Express Edition is a free edition and has restrictions on the DB size (max: 10GB), 1 processor and 1GB of memory.

Here are some links comparing the different feature sets across the different editions available in SQL Server 2012:

SQL Server 2012 Editions: http://www.microsoft.com/sqlserver/en/us/sql-2012-editions.aspx

Article on MSDN with the Feature Comparison between the different Editions: http://msdn.microsoft.com/en-us/library/cc645993

And here is the information from MSFT in regards to licensing: http://www.microsoft.com/sqlserver/en/us/get-sql-server/licensing.aspx

Posted in SQL Server | Tagged: , , | Leave a Comment »

Virtual Labs for SQL Server 2012

Posted by decipherinfosys on March 23, 2012

MSFT has released a bunch of virtual labs to easily come up to speed with the new features of SQL Server 2012.  So, if you are restrained due to hardware/software and are finding it difficult to convince your boss to provide environments to play around with the new feature sets, here is a great place to get your hands dirty and start learning more about SQL Server 2012 – all these virtual labs run off the MSFT servers in the cloud.

http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx

Posted in SQL Server | Tagged: , | Leave a Comment »

Sample Databases for SQL Server 2012

Posted by decipherinfosys on March 22, 2012

In case you are looking for the AdventureWorks Sample Databases for SQL Server 2012 version, you can download them from codeplex – here is the link for all the downloads (Data files, Script files etc.):

http://msftdbprodsamples.codeplex.com/releases/view/55330

Once you download the “AdventureWorks2012_Data” data file, you can simply attach it using T-SQL or through the GUI.  If you use T-SQL, you can do simply:

USE MASTER
GO
CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG

It will automatically create the log file in the same folder.  And if you want to do it from the GUI, you can right click on databases in Management Studio and Select “Attach” which will bring up the GUI in which you can select the MDF file location.  You will notice that under the details section, it will complain about not finding the Log file.  If you proceed as is, you will get an error since it is expecting the log file to be present.  Simply highlight the log entry line in the details section and click on Remove and then proceed and it will create the log file for you and attach the database:

SQL Server 2012 - Attach Database

Posted in SQL Server | Tagged: , , | Leave a Comment »