Feeds:
Posts
Comments

Archive for July, 2013

SQL SERVER – Date formatting in excel

Posted in Functions, SQL SERVER, tagged , , , on July 9, 2013| 1 Comment »

Whenever we copy data from SQL Server to excel, we usually face some formatting issues in excel. Specially, if you copy date field from SQL Server to excel. Given below is the script and screen image.

USE AdventureWorks2012
GO
SELECT * FROM [HumanResources].[Department]
GO
--Copy the result set of the above query and paste into excel.

formattingdateinexcel1.1

Let me explain the solution step by step.

Step 1 :
Execute the query and paste the data from result set to excel.

USE AdventureWorks2012
GO
SELECT * FROM [HumanResources].[Department]
GO
--Copy the result set of the above query and paste into excel.

formattingdateinexcel1.1

Step 2 :
Once you paste the data in excel, just select the date column field, right click on it and select format cells.. from the pop up menu as shown in the picture.

formattingdateinexcel1.3

Step 3 :
Once you are in the format cells.. dialogue box, you can select the category as a date and whatever type you want you can select. Given below is the screen image.

formattingdateinexcel1.4

Step 4 :
Press OK once you are done and your date column will be formatted as shown in the picture below.

formattingdateinexcel1.5

Note : I used SQL Server 2005 & Excel 2007 to demonstrate this problem and its solution.

Let me know if you know a better solution.

Read Full Post »

SQL SERVER HEKATON – WAIT_AT_LOW_PRIORITY

Posted in Functions, SQL SERVER, tagged , , , , on July 7, 2013|

In the earlier versions of SQL Server, whenever you need to rebuild an index (pass DDL statement for an online index), you sometimes end up with deadlock situation. Given below is the script that we use to rebuild an online index in the earlier version of SQL Server.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);

In SQL Server Hekaton, a solution to this problem has been introduced namely WAIT_AT_LOW_PRIORITY . It allows DBA to control the locking mechanism that is required to rebuild an online index and causing deadlock situations.
Given below is the script :

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
DATA_COMPRESSION = ROW
)
;

In the above script, you can see two arguments MAX_DURATION and ABORT_AFTER_WAIT. Given below are the details.

MAX_DURATION :
It describes the waiting time in minutes, but you can just pass the integer number and MINUTES can be removed from the syntax.

ABORT_AFTER_WAIT:
ABORT_AFTER_WAIT came up with three nice options. It basically provides you the hands on the different locking mechanism. Given below are the details :

NONE : It implements no locking on the online index rebuild operation,it performs operation like a normal scenario.
Given below is the script if you would like to implement NONE in ABORT_AFTER_WAIT.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE ) ),
DATA_COMPRESSION = ROW
)
;

SELF: It aborts the online index rebuild operation by using normal priority. It gives priority to the user operation instead of rebuilding index.
Given below is the script if you intend to implement SELF.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = SELF) ),
DATA_COMPRESSION = ROW
)
;

BLOCKERS: It kills all the user transactions that usually block the online index rebuilding, so that you can rebuild index easily. This is not recommended if you are using at the peak hours.
Given below is the script if you would like to implement BLOCKERS.

USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS) ),
DATA_COMPRESSION = ROW
)
;

Let me know if you implement it in CTP 1 and find any issues.

Read Full Post »

SQL SERVER HEKATON – Northwind sample database for CTP 1

Posted in SQL SERVER, Tips, tagged , , , on July 6, 2013| 4 Comments »

Whenever new technology steps in, we usually run for its sample database to test it features, functionality & enhancement, etc. When I started testing SQL Server Hekaton, I was looking for some sample databases. Finally, I grabbed it from msdn but there, it is script only. So you need to copy and execute it but if you need a downloadable version, you can download it from here (Northwind database).

Note : Make sure you have data folder in c: drive (C:\data)

Read Full Post »

SQL SERVER 2014 – New system objects

Posted in SQL SERVER, Tips, tagged , , , , on July 5, 2013| Leave a Comment »

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 »

SQL SERVER 2014 – Columnstore Index Enhancement – Part 1

Posted in Performance, SQL SERVER, tagged , , , on July 3, 2013| 2 Comments »

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 »

SQL SERVER 2012 – Fix – Error – 11708 – An invalid value was specified for argument ‘%.*ls’ for the given data type.

Posted in Errors, SQL SERVER, tagged , , , , , on July 1, 2013| Leave a Comment »

An invalid value was specified for argument ‘%.*ls’ for the given data type  is one of the new error messages in SQL Server 2012. This error message is related to Sequence object, a new database feature shipped with SQL Server 2012.

Lets discuss this in detail:
Message Number: 11708

Severity : 16

Error Message: An invalid value was specified for argument ‘%.*ls’ for the given data type.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 256
NO CACHE
GO

Msg 11708, Level 16, State 1, Line 1
An invalid value was specified for argument ‘MAXVALUE’ for the given data type.

Ooopps…… I am unable to execute it.

Resolution:
The issue is quite simple. I created the sequence object using tinyint data type and range of tinyint data type is from 0 to 255 as per MSDN. But if you observe carefully, in my above example, the maximum value of sequence object is 256 and that is more than the tinyint data type maximum range (255).

Lets rewrite the script and correct the maximum value range. It must be less than or equal to the defined data type range.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 255
NO CACHE
GO

Conclusion :
Remember, whenever you create the sequence object always keep the minimum and maximum value within the range of the data type.

Let me know if you have a better solution.

Read Full Post »

« Newer Posts