Feeds:
Posts
Comments

SQL Server 2012 – Audit Enhancements – Part I

June 18, 2013 by Muhammad Imran

SQL Server 2012 came up with lots of new exciting features along with great enhancements as well. One of the enhancements available in SQL SERVER 2012 is its audit features.

Given below are the details of the enhancements:

  • Database level auditing is available in Developer, Enterprise & Evaluation editions but its support is available in all editions of SQL Server. So, now you can take the benefit of database audit in every edition.
  • Another major issue is resolved using this new enhancement. Lets say if you setup a target directory over the network and due to any reason (e.g network failure) the target is not available, you usually lose the data in the earlier version. But in SQL Server 2012, it will automatically recover once the connection is restored.
  • Now, you can restrict the number of files to be created for audit. This will stop creating unnecessary files in the audit folder. But there’s one more option to make it unlimited. (Note: be careful when you use this option, it requires proper maintenance).
  • This feature is basically helpful for the database developers because now it gives the additional information of audit log and you can even trace whether the SQL Query is passed through application directly or via stored procedures.
  • Now you can also write audited events in the audit log using a new stored procedure (sp_audit_write) introduced in SQL Server 2012.
  •  Using user-defined audit event, now applications can write custom audit information in the log as well.
  • To keep a track of user defined events, SQL Server has provided the new catalog views (sys.server_audits, sys.server_file_audits) .
  • It is also very easy to filter the audit events before it is written into the audit files. So you can customize the type of events you would like to capture. Otherwise you need to capture unnecessary events.
  • As we discussed contained database features in my earlier article, now you can monitor contained database users & its activity as well.
  • And the last but not the least, the dialogue boxes are now available in SQL Server management studio. So not only from script but also you can get much more control than earlier version through SSMS as well.

Posted in Security Enhancements, SQL Server 2012 | Tagged , , , , | Leave a Comment »

SQL SERVER – Convert a datetime to a short date format

June 16, 2013 by Muhammad Imran

Datetime conversion from one format to another format is sometimes very critical. You should be very careful while doing it, since even a single digit can mess up everything. Recently, I was working on a Report and I needed to convert datetime to a short date format in SQL Server. Given below are three different methods we can use to achieve it.

Method 1 :
In this method, we will convert the datetime value to short date format, using CONVERT function. Here goes the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
CONVERT(VARCHAR(11), @datetime, 100) AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Method 2 :
In this method, we will convert the datetime to short date format using CAST function. Script given below.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
CAST(@datetime AS VARCHAR(11)) AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Method 3 :
In this method, we will convert the datetime to short date format using FORMAT function. Script as follows:

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
FORMAT(@datetime, 'MMM dd yyyy') AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Conclusion :
As you can see, we used three different methods (CONVERT, CAST & FORMAT functions)  to convert datetime to short date format and all of them gave the same result set. Nonetheless, I usually use Method 1 (CONVERT function) because it is compatible with different versions of SQL SERVER and also the performance is better than other two.

What do you prefer? Do write to me.

Posted in SQL Server Solutions | Tagged , , , , , | 1 Comment »

SQL SERVER 2012 – Fix – Error :11525 – The metadata could not be determined because statement uses a temp table.

June 15, 2013 by Muhammad Imran

Temporary table is one of the frequently used objects in SQL Server and whenever we need to place data temporarily somewhere, we prefer to use the temporary table. Recently, I was working on temporary table  in SQL Server 2012 and wanted to view the metadata of the temporary table using sp_describe_first_result_set (a new system stored procedure shipped with SQL Server 2012) . Ooopps….. there’s an error, let me explain the error in detail. This error message is available in SQL Server 2012 and above.

Message Number: 11525

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ uses a temp table..

Error Generation:
Let me create a temporary table to demonstrate this error.

USE tempdb
GO
CREATE TABLE #temp_Sample
(
 [ID] INT,
 [NAME] VARCHAR(32)
)
GO
SELECT * FROM #temp_Sample
GO
--OUTPUT

erromessage11525.1.1

You can see that I have created a temporary table and browsed it as well. Now I would like to view the metadata of the above temporary table. Given below is the script to view the metadata using sp_describe_first_result_set

USE tempdb
GO
Sp_describe_first_result_set @tsql =N'Select * from #temp_Sample'
GO
--OUTPUT

Msg 11525, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘Select * from #temp_Sample’ uses a temp table.

Ooopps…… I am unable to view it.

Resolution:
The reason behind this error is, you cannot generate the metadata of temporary table. So what to do ?Very simple guys, just convert temporary table to global temporary table and view the metadata.
Given below is the correct script.

-- Convert the temporary table to global temporary table.
SELECT * into ##temp_Sample FROM #temp_Sample

-- View the metadeta
Sp_describe_first_result_set @tsql =N'Select * from ##temp_Sample'
GO
--OUTPUT

errormessage11525.1.2

Conclusion :
Remember, whenever you use temporary table and would like to view the metadata of that query using sp_describe_first_result_set,  always convert temporary table to global temporary table.

Posted in Errors, SQL Server 2012 | Tagged , , , , , | 1 Comment »

SQL SERVER 2014 Announcement & its features

June 14, 2013 by Muhammad Imran

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:

Posted in SQL SERVER 2014 | Tagged , , , | 2 Comments »

SQL SERVER – How to insert a string value with an apostrophe (single quote) in a column

June 13, 2013 by Muhammad Imran

How to insert a string value with an apostrophe (single quote) in a column is a general problem? Mostly, it happens when you insert any name with apostrophe. One of my colleagues faced this issue this morning. He had to upload a list of customers and some of the customers had apostrophe in the name and he was getting error during insertion. After a while, he emailed me stating he fixed it. So  I inquired him how he did it and I received a very interesting reply, he said he copied the name then opened the table and pasted it ;) . Opsssssssss. Never try this method.

Lets now resolve it step by step.

Step 1 :
Create a sample table.

USE tempdb
GO
CREATE TABLE tbl_sample
(
  [ID] INT,
  [Name] VARCHAR(50)
)
GO

Step 2 :
Insert the name with apostrophe. This step is just to demonstrate the error.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1,'Irwin D'Mello')
GO
--OUTPUT

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘Mello’.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ‘)
‘.
Ooopps…… I am unable to insert it.

Step 3 :
Just replace the single apostrophe with double apostrophe and insert the record again.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1,'Irwin D''Mello')
GO
--OUTPUT

(1 row(s) affected)

Step 4 :
Lets check if the data is inserted or not.

USE tempdb
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

singlequotes1.1

You can now see the name in the right format.

Conclusion :
Remember, whenever you come across such cases, just replace apostrophe (single quote) with double apostrophe (double quotes) and it works fine.

Posted in SQL Server Solutions | Tagged , , , | 1 Comment »

SQL SERVER 2012 – Scalability and Performance Enhancements – FILESTREAM Filegroups Can Contain Multiple Files

June 12, 2013 by Muhammad Imran

Scalability and performance is one of the core areas of SQL Server that we should take care of. Today, I was searching for something on MSDN and came across an exciting page (Scalability and Performance Enhancements). In this page, I found few Scalability and Performance Enhancements shipped with SQL Server 2012. The exciting topic was flashing & encouraging me to test was FILESTREAM Filegroups Can Contain Multiple Files
The purpose of this enhancement is to increase the performance of your database because now you can place multiple files in multiple drives under one file group.

Let me create a sample to explain how you can contain multiple files in a single file group.

CREATE DATABASE [Personal_DB]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'Personal_DB',
FILENAME = N'C:\Data\Personal_DB.mdf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB
),
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT
(
NAME = N'Music',
FILENAME = N'C:\Data\Music',
MAXSIZE = UNLIMITED
),
(
NAME = N'Videos',
FILENAME = N'C:\Data\Videos',
MAXSIZE = 100MB
)
LOG ON
(
NAME = N'Personal_DB_log',
FILENAME = N'C:\Data\Personal_DB_log.ldf',
SIZE = 100MB,
MAXSIZE = 1GB,
FILEGROWTH = 1MB
)
GO

Do share with me if you implemented this enhancement in the real world.

Posted in SQL Server 2012, Scalability and Performance Enhancements | Tagged , , , , | Leave a Comment »

SQL SERVER – How to extract numeric and special characters separately

June 11, 2013 by Muhammad Imran

I came across with a new issue related to the legacy system. The situation is that, I have a varchar column in a table having numeric data with the currency and I need to segregate the numeric data and currency symbol. I spent sometime to figure it out and finally did it.

Before proceeding with the solution, let me create a sample to demonstrate the issue.

USE tempdb
GO
CREATE TABLE tbl_sample
(
  [ID] INT,
  [Curreny] VARCHAR(10)
)
GO
INSERT INTO tbl_sample VALUES (1,'£ 12.95')
INSERT INTO tbl_sample VALUES (2,'$ 20.38')
INSERT INTO tbl_sample VALUES (3,'Rs176.34')
INSERT INTO tbl_sample VALUES (4,'€58')
INSERT INTO tbl_sample VALUES (5,'¥ 33.3')
INSERT INTO tbl_sample VALUES (6,'97800')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

extractsymbols1.1

Now, lets segregate the numbers and symbols. Given below is the solution.

USE tempdb
GO
SELECT
  [ID]
, [Curreny]
, LEFT([Curreny],PATINDEX('%[0-9]%',[Curreny])-1)
  AS [Currency Symbol]
, SUBSTRING([Curreny],PATINDEX('%[0-9]%',[Curreny]),LEN([Curreny]))
  AS [Numbers]
FROM tbl_sample
--OUTPUT

extractsymbols1.2

Posted in SQL Server Solutions | Tagged , , , | 1 Comment »

Older Posts »

Follow

Get every new post delivered to your Inbox.

Join 110 other followers