Feeds:
Posts
Comments

Archive for November, 2013

In my earlier articles I wrote about many new enhancements in SQL Server 2012. Today, I will discuss the new enhancement in the permission area that includes Availability group, Schema, Search property list & Server categories.

The simplest way to query permissions is using sys.fn_builtin_permissions.
Given below is the script.

SELECT * FROM sys.fn_builtin_permissions('');

Above script will give you the complete list of permissions in SQL Server, but I need only the new permissions shipped in SQL Server 2012, so I compared the SQL Server 2008 R2 permissions with SQL Server 2012 and got the given below new permissions shipped with SQL Server 2012.

S. No

Class Description

Permission Name

Covering Permission Name

Parent Class Description

Parent Covering Permission Name

1

AVAILABILITY GROUP

ALTER

CONTROL

SERVER

ALTER ANY AVAILABILITY GROUP

2

AVAILABILITY GROUP

CONTROL

 

SERVER

CONTROL SERVER

3

AVAILABILITY GROUP

TAKE OWNERSHIP

CONTROL

SERVER

CONTROL SERVER

4

AVAILABILITY GROUP

VIEW DEFINITION

CONTROL

SERVER

VIEW ANY DEFINITION

5

SCHEMA

CREATE SEQUENCE

ALTER

DATABASE

CONTROL

6

SEARCH PROPERTY LIST

ALTER

CONTROL

DATABASE

ALTER ANY FULLTEXT CATALOG

7

SEARCH PROPERTY LIST

CONTROL

 

DATABASE

CONTROL

8

SEARCH PROPERTY LIST

REFERENCES

CONTROL

DATABASE

REFERENCES

9

SEARCH PROPERTY LIST

TAKE OWNERSHIP

CONTROL

DATABASE

CONTROL

10

SEARCH PROPERTY LIST

VIEW DEFINITION

CONTROL

DATABASE

VIEW DEFINITION

11

SERVER

ALTER ANY AVAILABILITY GROUP

CONTROL SERVER

 

 

12

SERVER

ALTER ANY EVENT SESSION

CONTROL SERVER

 

 

13

SERVER

ALTER ANY SERVER ROLE

CONTROL SERVER

 

 

14

SERVER

CREATE AVAILABILITY GROUP

ALTER ANY AVAILABILITY GROUP

 

 

15

SERVER

CREATE SERVER ROLE

ALTER ANY SERVER ROLE

 

 

16

SERVER ROLE

ALTER

CONTROL

SERVER

ALTER ANY SERVER ROLE

17

SERVER ROLE

CONTROL

 

SERVER

CONTROL SERVER

18

SERVER ROLE

TAKE OWNERSHIP

CONTROL

SERVER

CONTROL SERVER

19

SERVER ROLE

VIEW DEFINITION

CONTROL

SERVER

VIEW ANY DEFINITION

I will discuss the above permissions in my future articles in detail.

Advertisements

Read Full Post »

The sp_configure value ‘contained database authentication’ must be set to 1 in order to %S_MSG a contained database. You may need to use RECONFIGURE to set the value_in_use is one of the new error messages come in SQL Server 2012. This error message is related to Contained database, a new database feature shipped with SQL Server 2012.

Let’s discuss this in detail:
Message Number: 12824

Severity : 16

Error Message: The sp_configure value ‘contained database authentication’ must be set to 1 in order to %S_MSG a contained database. You may need to use RECONFIGURE to set the value_in_use.

Error Generation:

Basically, I re-installed my test database server, and I had plenty of database to attach it back to the server. So I was attaching one by one and this process was performing well. Suddenly, in one database it gave me this error. Given below are the error details.

errormessage12824.1.1

Ooopps…… I am unable to attach this database.

Resolution:
Basically, the database I was trying to attach is a contained database and please note that whenever you need to create or attach any contained database, you must enable the contained database authentication in the database server. But how ?
Given below is the script to enable it.

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
--OUTPUT

Configuration option ‘contained database authentication’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once you receive the above output saying  ‘contained database authentication’ has been changed from 0 to 1, you can attach or create any contained database in your database server.

Reference : MSDN

Read Full Post »

Today, I received a query from one of my blog readers asking how to get the current value from a sequence ? He also mentioned to me that he usually uses given below (Database console command) script to get the current value from identity column.

 DBCC CHECKIDENT ('Table Name', NORESEED)

So, do we have any DBCC command like this to get the current value from sequence object? Basically we do not have any DBCC command to get the current value from sequence same like identity but we do have work around to find it. Given below are the two methods to get the current value from any sequence object.

METHOD 1 :
In this method, you need to open SQL Server Management Studio and select the particular database and further select the sequence object in which current value is required. Then right click on it and browse its property to view the current value as shown in the image below.

sequence current value.1.1

sequence current value.1.2

METHOD 2 :
In this method, you need to open a NEW query window in SSMS and write the given below script to get the current value of sequence using sys.sequences  (A new system view shipped in SQL Server 2012).

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
SELECT current_value FROM sys.sequences
WHERE [name]='Seq_sample'
--OUTPUT

sequence current value.1.3

Read Full Post »

How to remove extra spaces from string value is a common issue and we usually come across this issue while massaging the data. I used this solution as a part of a solution in one of my earlier solutions.

Let me create a sample to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP TABLE tbl_sample
--GO
--Create table
CREATE TABLE tbl_sample
(
 [ID] INT,
 [NAME] VARCHAR(100)
)
GO
--Insert few records in the table
--Note : Names have been taken from adventureworks2012 database.
INSERT INTO tbl_sample VALUES (1,'Terri    Lee          Duffy')
INSERT INTO tbl_sample VALUES (2,'Roberto     Tamburello')
INSERT INTO tbl_sample VALUES (3,'Rob   Walters')
INSERT INTO tbl_sample VALUES (4,'Gail   A        Erickson')
INSERT INTO tbl_sample VALUES (5,'Gigi      N      Matthew')
GO
--Browse table
SELECT
 [ID]
,[NAME] AS [String with extra spaces]
FROM tbl_sample
--OUTPUT

remove extra spaces.1.2

SOLUTION 1 : Using REPLACE(string) Function
In this solution, we need to use the built-in function REPLACE to remove extra spaces from string value.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
 [ID]
,[NAME] AS [String with extra spaces]
,REPLACE(REPLACE(REPLACE([NAME]
,CHAR(32),'()'),')(',''),'()',CHAR(32))
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

remove extra spaces.1.1

SOLUTION 2 : Using User Defined Function
In this solution, we need to create a User Defined Function to remove extra spaces from string using XML.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
(
   @String VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Xml XML
DECLARE @Removed_Extra_Space VARCHAR(MAX)
DECLARE @delimiter VARCHAR(5)

SET @delimiter=' '
SET @Xml = CAST(('<a>'+REPLACE(@String,@delimiter,'</a><a>')+'</a>')
AS XML)

;WITH CTE AS (SELECT
 A.value('.', 'VARCHAR(MAX)') AS [Column]
 FROM @Xml.nodes('A') AS FN(A))

 SELECT @Removed_Extra_Space=REPLACE(
 Stuff((
 SELECT ';' + A.[Column]
 FROM CTE A
 WHERE ISNULL(A.[Column],'') <>''
 FOR XML PATH('')),1,1,''),';',' ')

RETURN (@Removed_Extra_Space)
END
GO

SELECT
 [ID]
,[NAME] AS [String with extra spaces]
,dbo.[UDF_Remove_Extra_Space_From_String] ([Name])
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

remove extra spaces.1.1

Read Full Post »

Today, I was developing a customer analysis report, basically trying to find out customer’s trend of purchasing. To get the trend I need to get his next row (purchasing) result set in the current row for comparison purposes.  Fortunately, we do have a solution for this problem using self join. But I will share another efficient solution, using LEAD (an analytic function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

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

INSERT INTO tbl_sample VALUES (1,'LEVEL 1')
INSERT INTO tbl_sample VALUES (2,'LEVEL 2')
INSERT INTO tbl_sample VALUES (3,'LEVEL 3')
INSERT INTO tbl_sample VALUES (4,'LEVEL 4')
INSERT INTO tbl_sample VALUES (5,'LEVEL 5')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

get the previous row result.1.1

Let me show you both old and new approaches.

Old Approaches :
Given below is the old approach that we generally develop using self join. This approach you can use in any version of SQL Server.

USE tempdb
GO
SELECT
 A.ID
,A.Levels As [Current Level]
,B.Levels AS [Next Level]
FROM tbl_sample A
LEFT JOIN tbl_sample B ON A.ID+1=B.ID
ORDER BY A.ID
GO
--OUTPUT

get value from next row

New Approaches :
In this approach, you do not need to do self join and make it complicated. You just need to use LEAD function and it will calculate the next result row for you automatically. This approach can be used in SQL Server 2012 and above.

USE tempdb
GO
SELECT
 A.ID
,A.Levels As [Current Level]
,LEAD(A.levels,1,0) OVER (ORDER BY A.ID) AS [Next Level]
FROM tbl_sample A
GO
--OUTPUT

get value from next row

Conclusion:
In the above approaches, you can see that the result set are same but the new approaches reduce the complexity and increase the performance.

Read Full Post »

A few days ago, I was working on list of customers and all the customers’ names were in upper case. Coincidentally, I found one customer having upper and lower case in his name. So, I thought of checking the entire customer list if anyone was having name in upper and lower case.
Note : The database is not case sensitive.

Let me create a sample to demonstrate the solution.

USE tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [Name] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'RARESQL.COM')
INSERT INTO tbl_sample VALUES (2,'RaReSql.com')
INSERT INTO tbl_sample VALUES (3,'Raresql')
INSERT INTO tbl_sample VALUES (4,'raresql.com')
GO
SELECT * FROM tbl_sample
--OUTPUT

lowercase.1.1

SOLUTION 1 : Using UPPER (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample WHERE
UPPER([NAME]) COLLATE Latin1_General_CS_AS !=[NAME]
--OUTPUT

lowercase.1.2

SOLUTION 2 : Using PATINDEX (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample
WHERE PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
,[NAME] COLLATE Latin1_General_CS_AS)>0
GO
--OUTPUT

lowercase.1.2

SOLUTION 3 : Using ASCII(String) Function
In this solution, it will not only give you the rows having lower case letter but it will also give you what lower characters are there in those rows. Given below is the script.

USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Extract_small_letters_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Extract_small_letters_From_String]
(
      @String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')
+ SUBSTRING(@String,Nums.n,1)
FROM N4 Nums
WHERE Nums.n<=LEN(@String)
AND ASCII(SUBSTRING(@String,Nums.n,1)) BETWEEN 97 AND 122

RETURN @RETURN_STRING
END
GO

SELECT *,dbo.[UDF_Extract_small_letters_From_String]([NAME])
As [Lower cases letters]
FROM tbl_sample
--OUTPUT

lowercase.1.3

Read Full Post »

« Newer Posts