Feeds:
Posts
Comments

Archive for August, 2013

Sometimes, date formatting becomes much critical when you know that the format is not supported by the application but to gain the client’s satisfaction, you need to format as per the requirement. I recently came across a date format where I had to add st, nd, rd & th to the dates. Fortunately my client is using SQL Server 2012. So I thought of developing the solution using new functions introduced in SQL Server 2012 to reduce the code and improve the performance.
Let me create a sample to demonstrate the solution.

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample table
CREATE TABLE [dbo].[tbl_sample](
[ID] [int] NULL,
[Date] [date] NULL
)
GO
--Insert records in the table
INSERT INTO dbo.[tbl_sample]
SELECT 1 AS [ID] ,N'2013-08-01' AS [Date] UNION ALL
SELECT 2 AS [ID] ,N'2013-08-02' AS [Date] UNION ALL
SELECT 3 AS [ID] ,N'2013-08-03' AS [Date] UNION ALL
SELECT 4 AS [ID] ,N'2013-08-04' AS [Date] UNION ALL
SELECT 5 AS [ID] ,N'2013-08-05' AS [Date] UNION ALL
SELECT 6 AS [ID] ,N'2013-08-06' AS [Date] UNION ALL
SELECT 7 AS [ID] ,N'2013-08-07' AS [Date] UNION ALL
SELECT 8 AS [ID] ,N'2013-08-08' AS [Date] UNION ALL
SELECT 9 AS [ID] ,N'2013-08-09' AS [Date] UNION ALL
SELECT 10 AS [ID] ,N'2013-08-10' AS [Date] UNION ALL
SELECT 11 AS [ID] ,N'2013-08-11' AS [Date] UNION ALL
SELECT 12 AS [ID] ,N'2013-08-12' AS [Date] UNION ALL
SELECT 13 AS [ID] ,N'2013-08-13' AS [Date] UNION ALL
SELECT 14 AS [ID] ,N'2013-08-14' AS [Date] UNION ALL
SELECT 15 AS [ID] ,N'2013-08-15' AS [Date] UNION ALL
SELECT 16 AS [ID] ,N'2013-08-16' AS [Date] UNION ALL
SELECT 17 AS [ID] ,N'2013-08-17' AS [Date] UNION ALL
SELECT 18 AS [ID] ,N'2013-08-18' AS [Date] UNION ALL
SELECT 19 AS [ID] ,N'2013-08-19' AS [Date] UNION ALL
SELECT 20 AS [ID] ,N'2013-08-20' AS [Date] UNION ALL
SELECT 21 AS [ID] ,N'2013-08-21' AS [Date] UNION ALL
SELECT 22 AS [ID] ,N'2013-08-22' AS [Date] UNION ALL
SELECT 23 AS [ID] ,N'2013-08-23' AS [Date] UNION ALL
SELECT 24 AS [ID] ,N'2013-08-24' AS [Date] UNION ALL
SELECT 25 AS [ID] ,N'2013-08-25' AS [Date] UNION ALL
SELECT 26 AS [ID] ,N'2013-08-26' AS [Date] UNION ALL
SELECT 27 AS [ID] ,N'2013-08-27' AS [Date] UNION ALL
SELECT 28 AS [ID] ,N'2013-08-28' AS [Date] UNION ALL
SELECT 29 AS [ID] ,N'2013-08-29' AS [Date] UNION ALL
SELECT 30 AS [ID] ,N'2013-08-30' AS [Date] UNION ALL
SELECT 31 AS [ID] ,N'2013-08-31' AS [Date]

Solution :
In this solution, I used IIF & Format function introduced in SQL Server 2012.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE [tempdb]
GO
SELECT [ID],[Date],FORMAT([Date],'d'
+IIF(DAY([Date]) IN (1,21,31),'''st'''
,IIF(DAY([Date]) IN (2,22),'''nd'''
,IIF(DAY([Date]) IN (3,23),'''rd''','''th''')))
+' MMMM yyyy') As [Formatted Date]
FROM tbl_sample
--OUTPUT

date format to st_rd_th.1.1

Let me know if you come across such issues and the proposed solution.

Read Full Post »

‘Update statement’ is one of the frequently used Data Manipulation Language (DML) in SQL Server scripting.  We usually use this statement to rectify the data. Recently, I was working on memory optimized table and I tried to update a primary key column and I came across an error.
Given below is the script that we usually use to update any disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
--Create table
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Product Name] NVARCHAR(100),
 [Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO
--Insert record into the table
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (1, 'SQL Server 2012',getdate())
GO
--Update table
Update tbl_Product_Master SET [Product ID]=11 WHERE [Product ID]=1
GO

Msg 10770, Level 16, State 10, Line 40
The operation ‘primary key update’ is not supported with memory optimized tables.

Oooops.. I am unable to update a primary key in memory optimized table. So how to update the primary key of a memory optimized table?

Let me explain it step by step.

Step 1 :
In this step, you need to delete the record which you need to update .

USE hkNorthwind
GO
DELETE FROM tbl_Product_Master WHERE [Product ID]=1
GO

Step 2 :
In this step, you need to insert the record with the updated data.

USE hkNorthwind
GO
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (11, 'SQL Server 2012',getdate())
GO

Step 3 :
Browse the table and check whether the data has been updated or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

update_PK_in_memory_optimized_Table1.1

Read Full Post »

In SQL Server, we usually come across a situation where we need to format values as a percentage. Usually, developers format the values as a percentage manually. But in this article, I will share a shortcut to format values as a percentage.
Before proceeding with the solution, I would like to create a sample table and data to demonstrate the formatting.

USE tempdb
GO
--DROP TABLE [dbo].[Student]
--GO
CREATE TABLE [dbo].[Student]
(
[Student ID] int,
[Student Name] [varchar](50) NOT NULL,
[Percentage] numeric(18,2)
)
GO
Insert into dbo.[Student] values (1,'Bob', 0.10)
Insert into dbo.[Student] values (2,'Alexander',0.50)
Insert into dbo.[Student] values (3,'Hosanna',0.85)
Insert into dbo.[Student] values (4,'William',0.11)
Insert into dbo.[Student] values (5,'Hulda',1.20)
Insert into dbo.[Student] values (6,'Jacoba',1.15)
GO

Solution 1 :
In this solution, we need to manually calculate the percentage and then format it. Given below is the script.

--This script is compatible with SQL Server 2000 and above.
USE tempdb
GO
SELECT [Student ID]
,[Student Name]
,[Percentage]
,CONVERT(VARCHAR(50),[Percentage]*100)+' %' AS [%] FROM dbo.Student
GO

Formatvalues1.1

Solution 2 :
In this solution, we will use a new function shipped with SQL Server 2012 namely Format. You just need to pass the values in this function and it will format values as a percentage (without any hurdles of calculations).
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT [Student ID]
,[Student Name]
,[Percentage]
,FORMAT([Percentage],'p') AS [%] FROM dbo.Student
GO

Formatvalues1.1

Read Full Post »

The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid is one of the new error messages shipped with SQL Server 2012. This error message is related to sp_describe_undeclared_parameters, a new system stored procedure shipped with SQL Server 2012 as well.

Lets discuss this in detail:
Message Number: 11507

Severity : 16

Error Message: The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid.

Error Generation:

Given below is the script that I tried to execute but it gave me this error.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql = N'
Select * from [Production].[ProductModel]
Where ProductModelID=@ProductModelID
And Instructions =@Instruction'
GO
--OUTPUT

Msg 11507, Level 16, State 1, Line 1
The parameter type for ‘@Instruction’ cannot be deduced because no type would make the query valid.

Ooopps…… I am unable to execute it.

erromessage11507.1.1

Resolution:

To resolve this issue you need to go to details of sp_describe_undeclared_parameters because sp_describe_undeclared_parameters does not support few data types and if those data types comes as undeclared parameters, it generates the error message.
Given below are the details of unsupported data types:

  • image
  • text
  • ntext
  • xml

Lets rewrite the script and remove the xml parameter from the script.
Given below is the script
:

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql = N'
Select * from [Production].[ProductModel]
Where ProductModelID=@ProductModelID '
--OUTPUT

Conclusion :
Remember, whenever you use sp_describe_undeclared_parameters, make sure you are using only supportive data type columns.

Let me know if you faced this issue and solved it in a different way.

Read Full Post »

‘How to implement default constraints in the memory optimized table’ seems very simple to me and I tried to implement in the same way, as we usually do it in the disk based table. But the output was not as per the expectation. Let me show you the output.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Product Name] NVARCHAR(100),
 [Creation Datetime] datetime default Getdate()
) WITH (MEMORY_OPTIMIZED = ON)
GO

Msg 10770, Level 16, State 13, Line 3
The feature ‘default constraints’ is not supported with memory optimized tables.

Oooops.. I am unable to create a default constraint in memory optimized table.

So, can we create a default constraint column in memory optimized table ? The answer is No & Yes.

Why No: Because you cannot create a default constraint column in memory optimized table.
Why Yes:Because you can implement the default constraint functionality in memory optimized table using stored procedure.

Let me create the table & stored procedure to implement default constraints in the memory optimized table.

Step 1 :
First of all, you need to create a memory optimized table without any default constraint.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Product Name] NVARCHAR(100),
 [Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO

Step 2 :
Once you have created the memory optimized table, create a stored procedure to insert records in it.

USE hkNorthwind
GO
CREATE PROCEDURE usp_Insert_Product_Master
@ProductID int,
@ProductName nvarchar(100)
AS
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (@ProductID,@ProductName,getdate())

Step 3 :
Insert the records using stored procedure in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
EXEC usp_Insert_Product_Master 1, 'SQL Server 2012'
GO
EXEC usp_Insert_Product_Master 2, 'SQL Server 2014'

Step 4 :
Just browse the table and check whether the default constraint is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

implement_deafult_constraints.1.1

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

« Newer Posts