First hands on using SQL Server 2014
SQL Server 2014
The new version of SQL server 2014 comes with various capabilities for OLTP and DW system along with enhancements previous version features.
Performance improvements
- New In-Memory (codenamed as Hekaton) OLTP built in to core SQL Server database allows to improve performance of database applications.
- Enhanced In-Memory ColumnStore for Data Warehousing – now updatable with even faster query speeds and with greater data compression for more real-time analytics support.
- New buffer pool extension support to non-volatile memory such as solid state drives (SSDs) – Increase performance by extending SQL Server in-memory buffer pool to SSDs for faster paging.
- New Enhanced Query Processing – speeds all SQL Server queries regardless of workload.
Enhanced Availability, Security and Scalability
- Enhanced AlwaysOn – Built upon the significant capabilities introduced with SQL Server 2012, delivers mission critical availability with up to 8 readable secondaries and no downtime during online indexing operations.
- Online Indexing at partition level – this is a good thing as now index rebuilding can be done at a partition level.
- Statistics at the partition level – this will be a huge improvement in performance.
- Increased scale – Continue to benefit from scale for up to 640 logical processors and 4TB of memory in a physical environment and up to 64 virtual processors and 1TB of memory per VM.
- Network Virtualization – Abstracts networking layer so that it you can easily migrate SQL Server from one datacenter to another.
- Storage Virtualization with Storage Spaces – Create pools of storage and storage tiers allowing your hot data to access the premium storage and cold data to access standard storage improving resilience, performance and
predictability. - Enhanced Resource Governance – With Resource Governor, SQL Server today helps you with scalability and predictable performance, and in SQL Server 2014, new capabilities allow you to manage IO, in addition to compute and memory to
provide more predictable performance.
- Enhanced Separation of Duties – Achieve greater compliance with new capabilities for creating role and sub-roles. For example, a database administrator can now manage the data without seeing sensitive data or personally identifiable information.
Platform for Hybrid Cloud
SQL Server 2014 creates a strong platform for hybrid cloud where cloud scale can be leveraged to extend the scalability and availability of on-premises database applications as well as reduce costs.
Simplified Cloud Backup and Disaster Recovery
- Backup to Azure Storage – reduce costs and achieve greater data protection by backing up your on-premises database to Azure Storage at an instance level. Optimize backup policy with intelligence built in to SQL Server that monitors and tracks backup usage patterns to provide optimal cloud backup. Backups can be automatic or manual, and in case of an on-premises failure, a backup can be restored to a Windows Azure Virtual Machine.
- Datafile on azure storage - Databases can be easily detached and migrated or moved to different virtual machines (that is, portability). Standby SQL Server virtual machines can be easily used for fast disaster recovery.
Tables
CREATE TABLE [dbo].[orders_mem](
[O_OrderKey] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000),
[O_CustKey] [int] NULL,
[O_OrderStatus] [varchar](64) NULL,
[O_TotalPrice] [decimal](13, 2) NULL,
[O_OrderDate] [datetime] NOT NULL INDEX IX_OrderDate NONCLUSTERED,
[O_OrderPriority] [varchar](15) NULL,
[O_Clerk] [varchar](64) NULL,
[O_ShipPriority] [int] NULL,
[O_Comment] [varchar](80) NULL
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
- There are two types of disabilities (SCHEMA_AND_DATA and SCHEMA_ONLY) supported in Heckaton. The first one keeps schema and data even the sql server restarts, where as in second case the schema only is retained when sql server restarts.
- The data are stored as file stream. The object can be found in file stream.
- Here primary key cannot be created on Clustered index as the data is residing in memory where data is stored in hash table with linked list connecting to rows.
- Every memory-optimized table must have at least one index, because it is the indexes that combine all the rows into a single table. We cannot create index which accepts NULL values.
- If we want to create index on varchar column in the table, the column should have windows collation of _BIN2 type (ex COLLATE Latin1_General_100_BIN2).
- Once the table is created in memory, we cannot alter it. We can drop and recreate with new structure.
- The hash indexes works better for equal search and range index works better for queries searched in range value.
- The indexes are not stored and they are recreated when the sql server starts. We cannot create column store index on Heckaton tables.
- The sys.tables need to be quried to find Heckaton objects by using option is_memory_optimized = 1
- These don’t support (n)varchar (max), userdefined datatypes etc.
- We cannot MERGE the target Heckaton tables. The Heckaton tables cannot be populated or joined with cross database tables. Below query will not work.
INSERT INTO dbo.Table1
SELECT * FROM
DB2.dbo.Table2
We have to store the table to a temp table before loading to Heckaton table.
Stored Procedures
CREATE PROCEDURE <Proc name>
<Param list>
With
native_compilation
,schemabinding
,execute as owner
AS
BEGIN atomic with(transaction isolation level = snapshot, language = 'english')
SELECT <Col name>
FROM <table name >
WHERE <Condition>
END
- The natively complied stored procedures. The stored procedure is compiled to machine code and stored as dll.
- The native compilation hints that it should be compiled to native code.
- Schema binding option restricts the drop of the Heckaton objects.
- Execute as owner to be mentioned as native compiled object cannot resolve the user and permissions.
- Language is provided to provide the format of the timestamp used internally to manage transaction.
- The objects must be two part name i.e. schema name and table name.
- The dmv dm_os_loaded_modules can be queried to find the memory optimized objects loaded. When the server restarts, while querying this dmv, we can get the list of tables but procedures will not be listed until they are recompiled (executed). This behavior is to speed up the database recovery.
- Below features are not supported as of now.
- Distinct
- SET option like NOCOUNT
- Creation of temp table (# or table variables as they are stored in tempdb)
- Common Table Expression
- Ranking functions
- Subqueries, Exists
- Combining result sets by UNION (ALL)
- The GROUP BY should have the aggregated function used as part of SELECT statement.
SELECT name
FROM dbo.Table1
GROUP BY name
The above will work but not inside the proc. We need to add aggregate function in SELECT statement in order to work inside the proc.
SELECT name, COUNT(id)
FROM dbo.Table1
GROUP BY name
References
https://msdn.microsoft.com/en-us/library/bb500435(v=sql.120).aspx
https://msdn.microsoft.com/en-us/library/dn606154(v=sql.120).aspx
https://www.brentozar.com/archive/2013/06/new-dmvs-in-sql-server-2014-ctp1/