Feeds:
Posts
Comments

Archive for August, 2013

Memory optimized table is a new concept to maintain records in table with high performance. I already discussed this new type of table earlier in my article. So, I kept on researching for this new feature and came across an issue. The issue is, I once created a memory optimized table and inserted few records in it and did other research work on the test database and once I was done, I just shut down my machine. Next morning when I tuned on my machine, I found no data in the memory optimized table. First I thought I deleted the data by mistake and forgot it. So I repeated the process but the following morning same thing happened to me again. I could not comprehend what on earth was going on !!  I was puzzled and started doing my research and finally resolved it.

Let me explain it step by step. (Never apply these steps on production database)

Step 1 :
Create a memory optimized table in a memory optimized table enabled database.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--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, DURABILITY = SCHEMA_ONLY)
GO

Step 2 :
Insert records in the memory optimized table.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
INSERT INTO tbl_Product_Master VALUES (1, 'SQL Server 2012',GETDATE())
INSERT INTO tbl_Product_Master VALUES (2, 'SQL Server 2014',GETDATE())
GO

Step 3 :
Browse the memory optimized table.

--This script is compatible with SQL Server 2005 and above.
USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO

Memory Optimized table myth.1

Step 4 :
Now either restart the database server or shutdown your test machine and turn it on.
Never do this exercise on production server without taking proper precautions.
Browse the table again.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO

Memory Optimized table myth.2

Step 5 :
Opsssss, now you can see the data gone and the reason behind this is, just a parameter that we passed at the time of memory optimized table creation and this is DURABILITY because if you make DURABILITY = SCHEMA_ONLY the schema will be durable but not the data. Once you restart the database server, you will lose your data. Remember, whenever you create a memory optimize table, to  keep the durable data (permanent data). Always use WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA).

Note : If you create the memory optimized table without passing DURABILITY parameter, by default it will be DURABILITY = SCHEMA_AND_DATA.

Lets recreate the memory optimize table. Given below is the script. Do the same process again without step 1 and your data will remain with you.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--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, DURABILITY = SCHEMA_AND_DATA)
GO

Please let me know if you come across this issue and its resolution.

Reference : MSDN

Read Full Post »

Stored procedure is one of the frequently used objects in the SQL Server. The error message we are about to discuss in this article is linked with stored procedure and its parameters. Let me explain the error message and its solution.

Message Number: 119

Severity : 15

Error Message: Must pass parameter number %d and subsequent parameters as ‘@name = value’. After the form ‘@name = value’ has been used, all subsequent parameters must be passed in the form ‘@name = value’.

Error Generation:
Let me execute a stored procedure and pass its parameter values.

USE AdventureWorks2012
GO
sp_describe_first_result_set @tsql =N'
SELECT object_id,name,type_desc
FROM sys.tables
WHERE object_id=@objectid'
,N'@objectid int'
--OUTPUT

Msg 119, Level 15, State 1, Line 1
Must pass parameter number 2 and subsequent parameters as ‘@name = value’. After the form ‘@name = value’ has been used, all subsequent parameters must be passed in the form ‘@name = value’.

Ooopps…… I am unable to execute a simple stored procedure.

Resolution:
The reason behind this error is, we passed first parameter with the parameter name and its value (@tsql) but we did not pass second parameter with parameter name & its value. Instead, we just passed the second parameter value in the stored procedure. So what is the solution ?
Given below are two methods.

Method 1 :
In this method, you must pass all parameter name(s) along with the value(s). I generally recommend this method because it helps you debug any error (misplacement of parameters & its values) in the parameters quickly.
Given below is the script.

USE AdventureWorks2012
GO
sp_describe_first_result_set @tsql =N'
SELECT object_id,name,type_desc
FROM sys.tables
WHERE object_id=@objectid'
, @params =N'@objectid int'
--OUTPUT

errormessage119.1.1

Method 2 :
In this method you should not pass any parameter name along with the value. Just pass the parameter values separated by comma. I do not recommend this method because it puts you in trouble if you have a lot of parameters in a stored procedure and you need to debug any error in the parameters.
Given below is the script.

USE AdventureWorks2012
GO
sp_describe_first_result_set N'
SELECT object_id,name,type_desc
FROM sys.tables
WHERE object_id=@objectid'
, N'@objectid int'
--OUTPUT

errormessage119.1.1

Conclusion :
Remember, whenever you pass parameter(s) to any stored procedure, make sure that you pass the parameter name along with the values.

Read Full Post »

A couple of month ago, I migrated one of my client’s data from SQL Server earlier versions to SQL Server 2012. The most important part was, I changed all the identity columns of the tables from identity to sequence in the entire database, here is the solution. But another important aspect that I did not discuss is that, whenever you migrate or create a new sequence column make sure that it is not be changeable like identity columns. In the identity column it is implemented by default but in sequence column, you must implement it. But how ?

First of all let me update an identity column of a table to show how identity columns are prevented from being changed (by default). Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
UPDATE HumanResources.Department
SET [DepartmentID]=17
WHERE [DepartmentID]=16
--OUTPUT

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column ‘DepartmentID’.

Solution :

By default, sequence columns are not prevented from being changed, you need to manually control this aspect. Let me show you the solution step by step.

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample_Seq
--GO
CREATE TABLE tbl_sample_Seq
(
[ID] int,
[Name] varchar(50)
)
GO

Step 2 :
Once you created the table, create a sequence object to insert [ID]s in the table.

USE tempdb
GO
--DROP SEQUENCE [dbo].[Seq_Student]
--GO
CREATE SEQUENCE [dbo].[Seq_Student]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
GO

Step 3 :
Once you created the sequence object, insert few records using sequence object in the table. Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Imran')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Bob')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Alexander')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Hosanna')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'William')
GO

Step 4 :
Let me browse the table and show the records that have been inserted successfully.

USE tempdb
GO
SELECT * FROM tbl_sample_Seq
GO
--OUTPUT

prevent sequence1.1

Step 5 :
Lets update the sequence column. Do not DO this step in your production database. This step is just to demonstrate the issue.

USE tempdb
GO
UPDATE tbl_sample_Seq SET [ID]=33 WHERE [ID]=3
GO
--OUTPUT

(1 row(s) affected)

Step 6 :
Ooops….in the above step the [ID] column of the table got modified. Let me create an update trigger on the [ID] column of the table, so that we can prevent sequence values from being changed. This is the most important step while implementing sequence object in a table. Do not skip it.

USE tempdb
GO
CREATE TRIGGER trg_update
ON tbl_sample_Seq
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @OldID int
DECLARE @NewID int

SELECT @OldID =[ID] FROM deleted
SELECT @NewID =[ID] FROM inserted

--Print @OldID
--Print @NewID

IF @OldID <> @NewID
BEGIN
RAISERROR('Failed', 16, 1);
ROLLBACK TRANSACTION
END
END
GO

Step 7 :
Once the update trigger has been created, lets update the sequence column ([ID]) again.

USE tempdb
GO
UPDATE tbl_sample_Seq SET [ID]=33 WHERE [ID]=3
GO
--OUTPUT

Msg 50000, Level 16, State 1, Procedure trg_update, Line 19
Failed
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Conclusion :
Whenever you implement sequence object in any table, make sure you create the trigger to prevent sequence values from being changed.

Let me know if you came across this issue and its solutions.

Read Full Post »

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 »

« Newer Posts - Older Posts »