Feeds:
Posts
Comments

Archive for the ‘SQL SERVER 2014’ Category

SQL Server 2014 is shipped with lots of exciting features and enhancements, which I usually share with you in my blog from time to time. Today, I will discuss a new enhancement that will minimize your code. This enhancement is actually in the CREATE TABLE (Transact-SQL). Now you can actually create NONCLUSTERED index within the create statement and you do not need to alter table to add NONCLUSTERED index anymore. However, if you want to follow the old method, you can continue doing it. The old method has NOT been discontinued.

Given below are both methods, demonstrating the enhancement.

NEW Method : (Create NONCLUSTERED index within the create table statement)
In this method, we will create NONCLUSTERED index within create table statement. This script is compatible with SQL SERVER 2014 and above.

USE AdventureWorks2014
GO
--DROP TABLE Employee
--GO
CREATE TABLE Employee
(
[Emp_ID] int NOT NULL,
[LastName] varchar(255) NOT NULL,
[FirstName] varchar(255),
[Address] varchar(255),
[City] varchar(255),
[PostalCode] nvarchar(15),
CONSTRAINT pk_Emp_ID PRIMARY KEY ([Emp_ID]),
INDEX IX_Employee_PostalCode NONCLUSTERED (PostalCode) 
);

GO
--OUTPUT

How to create a nonclustered index within the create table.1.1

OLD Method : (Create NONCLUSTERED index after creation of the table)
In this method, we will create NONCLUSTERED index AFTER table creation. This script is compatible with SQL SERVER 2005 and above.

USE AdventureWorks2012
GO
--DROP TABLE Employee
--GO
CREATE TABLE Employee
(
[Emp_ID] int NOT NULL,
[LastName] varchar(255) NOT NULL,
[FirstName] varchar(255),
[Address] varchar(255),
[City] varchar(255),
[PostalCode] nvarchar(15),
CONSTRAINT pk_Emp_ID PRIMARY KEY ([Emp_ID])
)
GO

CREATE NONCLUSTERED INDEX IX_Employee_PostalCode
ON dbo.Employee (PostalCode)
GO

How to create a nonclustered index within the create table.1.1

Conclusion
As you can see above, both methods will give you the same output, however, new method will reduce the line of code. Let me know your feedback about new enhancement.

Read Full Post »

HASH index (NONCLUSTERED) is a new type of index introduced in SQL Server 2014. It is only supported by memory optimized table. Recently, I was working on a memory optimized table and I came across in a situation where I had to find all the hash indexes available in the database. As usual I got it from a system view (sys.indexes). It is nothing new but the story did not end here because I found a shorter way to get the list of non-clustered hash indexes.
Given below is script that can give you the list of all hash indexes available in any particular database.

USE hkNorthwind
GO
SELECT
  object_schema_name([object_id]) As [Schema Name]
, object_name([object_id]) As [Table Name]
, [name] As [Index Name]
, [type_desc] As [Index Description]
FROM
sys.hash_indexes
--OUTPUT

list of hash indexes.1.1

Read Full Post »

Recently, I was working on SQL Server 2014 CTP 1 and doing my usual research, and came across the new system objects shipped with CTP 1.

Given below is the list of new system objects :

S.No

 Object Name

Object Type

1

 sp_set_cardinality_estimation_model_110

EXTENDED STORED PROCEDURE

2

 plan_persist_query_text

INTERNAL TABLE

3

 plan_persist_query

INTERNAL TABLE

4

 plan_persist_plan

INTERNAL TABLE

5

 plan_persist_runtime_stats

INTERNAL TABLE

6

 plan_persist_runtime_stats_interval

INTERNAL TABLE

7

 plan_persist_context_settings

INTERNAL TABLE

8

 fn_dump_dblog_xtp

INLINE TABLE VALUED FUNCTION

9

 fn_dblog_xtp

INLINE TABLE VALUED FUNCTION

10

 fn_hadr_is_primary_replica

SCALAR FUNCTION

11

 sp_MSalreadyhavegeneration

STORED PROCEDURE

12

 sp_xtp_merge_checkpoint_files

STORED PROCEDURE

13

 sp_db_enable_clustered_columnstores

STORED PROCEDURE

14

 sp_cci_tuple_mover

STORED PROCEDURE

15

 syscsrowgroups

SYSTEM TABLE

16

 dm_resource_governor_resource_pool_volumes

VIEW

17

 dm_xtp_transaction_stats

VIEW

18

 dm_xe_database_session_event_actions

VIEW

19

 dm_xtp_gc_stats

VIEW

20

 dm_db_xtp_index_stats

VIEW

21

 dm_xe_database_session_object_columns

VIEW

22

 dm_os_buffer_pool_extension_configuration

VIEW

23

 dm_db_xtp_object_stats

VIEW

24

 dm_db_xtp_table_memory_stats

VIEW

25

 dm_xtp_consumer_memory_usage

VIEW

26

 dm_io_cluster_shared_volumes

VIEW

27

 dm_db_xtp_memory_consumers

VIEW

28

 dm_db_xtp_checkpoint

VIEW

29

 dm_xtp_threads

VIEW

30

 dm_db_merge_requests

VIEW

31

 dm_xtp_transaction_recent_rows

VIEW

32

 dm_db_xtp_checkpoint_files

VIEW

33

 dm_xtp_system_memory_consumers

VIEW

34

 dm_xe_database_session_events

VIEW

35

 dm_xtp_memory_stats

VIEW

36

 dm_db_xtp_hash_index_stats

VIEW

37

 dm_xe_database_sessions

VIEW

38

 dm_xe_database_session_targets

VIEW

39

 dm_db_xtp_gc_cycle_stats

VIEW

40

 dm_xtp_gc_queue_stats

VIEW

41

 dm_db_xtp_transactions

VIEW

42

 column_store_row_groups

VIEW

43

 hash_indexes

VIEW

44

 event_sessions

VIEW

45

 event_session_targets

VIEW

46

 event_session_fields

VIEW

47

 event_session_events

VIEW

48

 event_session_actions

VIEW

Read Full Post »

Columnstore index is one of the nice features introduced in SQL Server 2012. It improves the performance more times than the normal query but on the other hand it has some limitations as well, and one of the limitation is that you cannot create a CLUSTERED Columnstore index.

Let me demonstrate it in SQL Server 2012.

USE tempdb
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT,
 [Product Name] NVARCHAR(100),
 [Model] INT
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX
[IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] ([Product ID])
GO
--OUTPUT

Msg 35338, Level 15, State 1, Line 3
Clustered columnstore index is not supported.

Ooopps…… I am unable to create it in SQL Server 2012.

The above limitation has been eliminated in SQL Server 2014 and you can add the CLUSTERED COLUMNSTORE INDEX in the table.

Let me demonstrate the same example in SQL Server 2014.


USE tempdb
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT,
 [Product Name] NVARCHAR(100),
 [Model] INT
)
GO

CREATE CLUSTERED COLUMNSTORE INDEX
[IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master]
GO
--OUTPUT

Command(s) completed successfully.

Lets check whether the index has been created successfully or not.

columnstoreindexcusteredindex1.1

The result is evident above, in SQL 2014.

Let me know if you explore the other nice enhancements of SQL Server 2014.

Read Full Post »

In my earlier article, I had discussed about new features of SQL Server 2014. Those features are very exciting and I was waiting for the CTP. Now, CTP 1 is available for download.

Given below are the few instructions that you should follow to install CTP 1.

  • The machine should not have any earlier versions of SQL Server.
  • There is no upgrade / side by side installation available in this CTP.
  • This CTP is only available for X64 architecture.

System requirement for SQL Server 2014 is available here.

Let me know if you face any issues with the installation.

Read Full Post »

SQL Server 2014 was announced a couple of weeks ago during TechEd North America Conference 2013. So, I started research about its features and I found few features very exciting to me. Given below are the details of the few features available in SQL Server 2014.

  • In-Memory OLTP : It improves the performance of database applications about 50x as compared to the normal database application. It can achieve significant performance. 
  • Enhanced In-Memory ColumnStore : Now, you can update the columnstore index (woww) , also you can apply a new option called COLUMNSTORE_ARCHIVE for higher compression and storage space savings of as much as 90 per cent in columnstore index.
  • Buffer pool extension to SDD (solid-state drives)SQL Server improved the query performance by allowing the use of non-volatile devices (e.g solid-state drives (SSDs)) to reduce usage of SQL Server memory with no risk of data loss.
  • Enhanced Query Processing : SQL queries became much faster due to the enhancement of query processing and one of the reasons why querying process became faster is, it separates querying into two steps, decision-making and execution. 
  • Enhanced Always On : Now it can support up to 8 secondaries.
  • Backup to Azure : SQL Server can take backup and restore directly to the Windows Azure Blob service.

  • Resource Governor Enhancements

    Resource Governor can provide better control and performance for different types of workloads in SQL Server applications.

  • SQL Server Audit Enhancements : Auditing became more easier because it is enabled by default and it will record each and every database activities, including database reads, with minimal impact on performance.

Let me know if I missed out any important features.

For further details, you can download given below sources:

Read Full Post »