Feeds:
Posts
Comments

Archive for June, 2013

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.

Read Full Post »

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.

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 »

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.

Read Full Post »

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.

Read Full Post »

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

Read Full Post »

Sequence is one of the nice features shipped with SQL Server 2012 and it enhances a lot of identity column features and provides hands on a lot of things that identity does not support. But on the other hand it has its own limitations as well. Today, I was working on one of the user defined functions in my application using SEQUENCE and I came across an error.

Let me explain this error in detail :

Message Number: 11724

Severity : 15

Error Message: An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.

Error Generation:

Let me create a User defined function and a Sequence to demonstrate this error.

USE tempdb
GO

--Create a sequence
CREATE SEQUENCE [dbo].[Seq_Sequence]
AS [int]
START WITH 1
GO

--Create a user defined function
CREATE FUNCTION [dbo].[fn_Sample]
(
    @SequenceID INT
)
RETURNS TABLE AS RETURN
(
    SELECT 'RCP' + TRY_Convert(varchar(10),@SequenceID) AS [ID]
   , GETDATE() AS [CREATION DATETIME]
)
GO

The purpose to create the Sequence object and the user defined function is basically I want to get the new ID from sequence and pass it to the user defined function to FORMAT and return it with creation date time.
It seems very simple, lets do it.

USE tempdb
GO
SELECT * FROM [dbo].[fn_Sample]
(
   NEXT VALUE FOR [Seq_Sequence]
)
--OUTPUT

Msg 11724, Level 15, State 1, Line 1
An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function

Ooopps…… I am unable to execute it.

Resolution:

The resolution is very simple, NEVER pass the NEXT VALUE of sequence directly to table value user defined function. Just introduce a variable, store the NEXT VALUE in it and pass the variable into the user defined function.
Lets do it.

USE tempdb
GO
DECLARE @ID AS BIGINT = NEXT VALUE FOR [Seq_Sequence]
SELECT * FROM [dbo].[fn_Sample](@ID)
--OUTPUT

errormsg11724.1

Conclusion :

Remember, whenever you use SEQUENCE object with any other object always be careful about the limitations of sequence object.

Read Full Post »

Debugger plays an important role in any development platform. As much stronger the debugger is, as less consumption of debugging time. SQL Server 2012 came with the strong debugging techniques that will reduce your time and efforts as well. But how ?

Before proceeding with the explanation of debugger, let me create an example to demonstrate debugger features.

Example :

USE AdventureWorks2012
GO
DECLARE @BusinessEntityID INT,
@FirstName VARCHAR(50),
@SalesQuota NUMERIC(18,2)

DECLARE Database_Cursor CURSOR FOR

Select top 4 BusinessEntityID,FirstName,SalesQuota
from [Sales].[vSalesPerson]

OPEN Database_Cursor

FETCH NEXT FROM Database_Cursor
INTO @BusinessEntityID, @FirstName, @SalesQuota

WHILE @@FETCH_STATUS = 0
BEGIN
      PRINT CONCAT(@BusinessEntityID,' '
     , @FirstName,' ' ,@SalesQuota)

      FETCH NEXT FROM Database_Cursor
      INTO @BusinessEntityID, @FirstName, @SalesQuota
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
GO

sqldebugger1.1

How to start debugger :
Before proceeding with the features, let me tell you how to run the debugger.
Go to Debug menu and select Start Debugging or Press (Alt +F5) as shown in the picture.

sqldebugger1.9

Let me explain the feature of debugger in detail.

Feature 1 : (Cannot put the breakpoint in any invalid location)
This is one of the nicest features in debugger that will stop you putting unnecessary breakpoints on INVALID location.
Given below is the screen image :

sqldebugger1.2

Feature 2 : (Define Condition)
In the earlier version of SQL Server, we could place the breakpoint but we could not put any condition inside the breakpoint.
In SQL Server 2012, you can easily put the condition inside the breakpoint, so even if there is a breakpoint and if it does not meet the criteria, it will not stop the debugger.

Let me show you step by step, how to put the breakpoint condition.

Step 1 :
First of all, place your cursor, where you need to place the breakpoint in the query editor window and PRESS F9.

Step 2 :
Right click on the breakpoint and select Condition as shown in the picture below.
sqldebugger1.4

Step 3 :
Once you get in the condition window just define the condition and PRESS OK.
Now the debugger will not stop at each and every row. It will only stop once debugger meets the criteria (@BusinessEntityID=275).
sqldebugger1.3

Feature 3 : (Delete/Disable Breakpoint)
Sometime, we partially finish the debugging, so we usually delete the breakpoint from line of code we already debugged. In my recommendation, until unless you finish the debugging do not delete the breakpoint, just disable it. Both disable and delete breakpoint options are available on right click of the breakpoint, as shown in the picture below.

sqldebugger1.5

Note : You can also disable / delete all breakpoints altogether from DEBUG menu as shown in the picture below.

sqldebugger1.6

Feature 4 : (Hit Count)
Sometimes we place the condition in the breakpoint (as explained in feature 2) but we do not want to stop whenever the condition meets. Instead we want to stop debugger if breakpoint criteria meets with certain number of times. (This feature is very handy whenever you are dealing with large data.)

Let me show you step by step, how to put the hit count condition.

Step 1 :
First of all, put a breakpoint.

Step 2 :
Right click on breakpoint and select Hit Count as shown in the picture below.

sqldebugger1.7

Step 3 :
Once you get inside the Hit Count window, by default it is break always but you can change it to any condition as shown in the picture below.

sqldebugger1.8

Read Full Post »

Few days ago, I was working on a report and I came across an issue wherein I had a varchar [ID] column with alphanumeric data in it but I had to display only number from this column (no strings). I provided this solution long back and you can use it here as well. But given below is the better solution to accommodate this issue.

Let me create an example to demonstrate it.

USE tempdb
GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [String] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'RCP0001')
INSERT INTO tbl_sample VALUES (2,'TEMP0231')
INSERT INTO tbl_sample VALUES (3,'PO0999')
INSERT INTO tbl_sample VALUES (4,'SO0341')
INSERT INTO tbl_sample VALUES (5,'SUP12')
INSERT INTO tbl_sample VALUES (6,'CUST76')
INSERT INTO tbl_sample VALUES (7,'241')
INSERT INTO tbl_sample VALUES (8,'0000')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

extractnumbers1.1

Solution :
You need to use PATINDEX to get the first non numeric character location and then you can use SUBSTRING to get the all numeric values.
Given below is the script.

USE tempdb
GO
SELECT
  [ID]
, [String]
, SUBSTRING([String],PatIndex('%[0-9]%',[String])
 ,DATALENGTH([String])) AS [Numbers]
FROM tbl_sample
GO
--OUTPUT

extractnumbers1.2

Read Full Post »

« Newer Posts - Older Posts »