Wednesday 29 August 2012

How to use Row_number() to insert consecutive numbers (Identity Values) on a Non-Identity column


Scenario:-

We have database on a vendor supported application in which a Table TestIdt has a integer column col1 which is not specified as identity as below,
 
--Create Table
CREATE TABLE [dbo].[TestIdt]
(
[col1] int NOT NULL,
[col2] [varchar](60) NULL,
[col3] [varchar](60) NULL,
[col4] [varchar](50) NULL,
CONSTRAINT [PK_TestIdt] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFFSTATISTICS_NORECOMPUTE = OFFIGNORE_DUP_KEY = OFFALLOW_ROW_LOCKS =ONALLOW_PAGE_LOCKS = ONFILLFACTOR = 80) ON [PRIMARY]
ON [PRIMARY]
 
--Insert Values to the Table
INSERT INTO TestIdt VALUES (1,'Britan','London','Football')
INSERT INTO TestIdt VALUES (2,'Wales','Cardiff','Football')
INSERT INTO TestIdt VALUES (3,'Scotland','Edinburgh','Football')
INSERT INTO TestIdt VALUES (4,'France','Paris','Football')
INSERT INTO TestIdt VALUES (5,'Ireland','Dublin','Football')
 
Now we need to insert values of another table(subsetTbl as below) to TestIdt with col1 on TestIdt need to be increaed by 1 with each row in the subset, In short Col1 should be populated like an identity column.
 
CREATE TABLE [dbo].[subsetTbl]
(
[subcol2] [varchar](60) NULL,
[subcol3] [varchar](60) NULL,
[subcol4] [varchar](50) NULL,
ON [PRIMARY]
 
--Insert Values to the Table
INSERT INTO subsetTbl VALUES ('Germany','Berlin','Tennis')
INSERT INTO subsetTbl VALUES ('Swiss','Bern','Tennis')
INSERT INTO subsetTbl VALUES ('Italy','Rome','Tennis')
INSERT INTO subsetTbl VALUES ('Belgium','Brussels','Tennis')
INSERT INTO subsetTbl VALUES ('Spain','Madrid','Tennis')
 
Let us see how we can Accomplish this task, One way of doing it is using SQL cursors which looks fine till we have small tables but with large tables and inserts cursor has its own disadvantages of using more resources and
more locking on the table. With the introduction of OVER clause on SQL 2005 we can accomplish this task pretty easily using rownumber function as below,
 
DECLARE @cnt int
SELECT @cnt = MAX(col1) from TestIdt
    INSERT INTO TestIdt
    select @cnt + (row_number() OVER (ORDER BY subcol2 ASC)) As Col1,subcol2,subcol3,subcol4 from subsetTbl
 
col1col2col3col4
1BritanLondonFootball
2WalesCardiffFootball
3ScotlandEdinburghFootball
4FranceParisFootball
5IrelandDublinFootball
6BelgiumBrusselsTennis
7GermanyBerlinTennis
8ItalyRomeTennis
9SpainMadridTennis
10SwissBernTennis
 
Now that we have inserted subsetTbl values to TestIdt with Col1 values incremented by 1, We can specify the start value as we wish, Let say I want to start with 100 for next insert We can do it as follows,
 
DECLARE @cnt int
SET @cnt = 99
    INSERT INTO TestIdt
    select @cnt + (row_number() OVER (ORDER BY subcol2 ASC)) As Col1,subcol2,subcol3,subcol4 from subsetTbl
 
col1col2col3col4
1BritanLondonFootball
2WalesCardiffFootball
3ScotlandEdinburghFootball
4FranceParisFootball
5IrelandDublinFootball
6BelgiumBrusselsTennis
7GermanyBerlinTennis
8ItalyRomeTennis
9SpainMadridTennis
10SwissBernTennis
100BelgiumBrusselsTennis
101GermanyBerlinTennis
102ItalyRomeTennis
103SpainMadridTennis
104SwissBernTennis
 
We have eventually created a Identity Insert for a Non-Identity Column.

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Tuesday 28 August 2012

Changing Database context inside store procedure

When I was developing a code today  I landed in a situation to create a store proc on database DB1 which inturn needs to create user and schema on database DB2  and also grant roles on DB2, with the below code

Since we know USE DB statement is not allowed in a store procedure, I decided to use as below and tried to execute the query,
 
set @sql = 'USE DB2;CREATE USER ['+@sName + '] FOR LOGIN ['+ @sName +']'
exec(@sql)
--give the user DB_reader/db_dbwriter rights
set @sql = 'USE DB2;sp_addrolemember ''db_datareader'', '+ @sName +''
exec(@sql)
set @sql = 'USE DB2;sp_addrolemember ''db_datawriter'', '+ @sName +''
exec(@sql)
-- create the schema
set @sql = 'USE DB2;CREATE SCHEMA '+ @sName +' AUTHORIZATION '+ @sName
exec(@sql)
-- set the user schema to be the new schema
set @sql = 'USE DB2;ALTER USER ['+ @sName +' ] WITH DEFAULT_SCHEMA=[' + @sName + ']'
exec(@sql)
 
which resulted with the following error,
 
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_addrolemember'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_addrolemember'.
Msg 111, Level 15, State 1, Line 1
'CREATE SCHEMA' must be the first statement in a query batch.
 
So I was not able to use USE DB2 in EXEC statement too, In SQL 2012 I can use
 
USE DB2;ALTER ROLE db_datareader ADD MEMBER test
USE DB2;ALTER ROLE db_datawriter ADD MEMBER test
 
which will resolve the sp_addrolemember syntax error, but CREATE SCHEMA error will not resolve as the statement will need to be the first statement in the query batch. Finally after considering various options this is how I overcame the issue,
 
1. create a Store proc usp_createuser on DB2 with no DB context with the above code.
2. Added the below code to the parent store procedure usp_security
   Exec DB2.dbo.usp_createuser @parameter1,@parameter2
 
which resolved my issue, I am fine with this resolution as of now but we have system with 200 Databases and I don't want to create 200 SP to overcome this problem, I shall blog if I find a better answer to this.

Saturday 25 August 2012

SQL Server Memory Allocation and Monitoring - Part 2


In the part 1 we saw how SQL server memory was allocated at SQL server start up, in this article we will see how to find out what is the memory assigned to the different parts of memory discussed in earlier article.

Most of the scripts used in this article are taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and  Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx). I have merely used these scripts to explain on SQL Server Memory.

1. To find total size of the physical memory on the Server:-  

select total_physical_memory_kb/1024/1024 AS [total_physical_memory_GB] from sys.dm_os_sys_memory

total_physical_memory_GB
------------------------
63
(1 row(s) affected)

2. To Find Total Memory allocated to SQL Server :-

select "Max_Memory" =
CASE
WHEN a.value = 2147483647 THEN (select total_physical_memory_kb/1024/1024 AS [total_physical_memory_GB] fromsys.dm_os_sys_memory)
ELSE a.value
END
from sys.configurations a where name = 'max server memory (MB)'

Max_Memory
------------------
47344
(1 row(s) affected)

3. To Find the total memory allocated to Buffer Pool:-

a. 64- bit SQL:-
        
select SUM(awe_allocated_kb)/1024/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'

--------------------
46
(1 row(s) affected)

b. 32 Bit Awe Enable:- To find if Awe is enable run select value from sys.configurations where name = 'awe enabled', The Value 1 indicates AWE is enabled.

select SUM(awe_allocated_kb)/1024/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'

--------------------
21
(1 row(s) affected)

c. 32 bit AWE not allocated:-

I could not find exact query but the below query will give you the almost the the correct value, 

select SUM(virtual_memory_reserved_kb)/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'

4. To Find Total Memory assigned to the VAS(User Kernel mode):-

The below Query is taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and  Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx) and Christian Bolton's Blog http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx
WITH VASummary(Size,Reserved,Free) AS
(SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024/1024 AS [Total avail mem, MB] ,CAST(MAX(Size) AS BIGINT)/1024/1024 AS [Max free size, MB]
FROM VASummary
WHERE Free <> 0

Result:-
Total avail mem, MB Max free size, MB
-------------------- --------------------
288                         84
(1 row(s) affected)

We have seen how to find the total memory allocated to the in each section, now we can discuss how to find the amount of memory used in each parts of SQL memory.

Before SQL 2005 we used DBCC MEMORYSTATUS output to find the memory components used space, post SQL 2005 we can get all the info from OS DMV sys.dm_os_memory_clerks.

As we discussed in PART 1 MultiPage allocation happens in the VAS Area and Single page allocation comes from Buffer Pool, we can calculate the space used in Bpool and outside Bpool with the below query,

The below Query is taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and  Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx)

-- amount of memory consumed by components outside the bBuffer pool
-- note that we exclude single_pages_kb as they come from BPool
select
    sum(multi_pages_kb
        + virtual_memory_committed_kb
        + shared_memory_committed_kb) as [Overall used w/o BPool, Kb]
from
    sys.dm_os_memory_clerks
where
    type <> 'MEMORYCLERK_SQLBUFFERPOOL'
-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
    sum(multi_pages_kb
        + virtual_memory_committed_kb
        + shared_memory_committed_kb
        + awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
    sys.dm_os_memory_clerks
where
    type = 'MEMORYCLERK_SQLBUFFERPOOL'

We can also use Ring buffers to find memory used, we can discuss this in a separate article as i am planning write a blog on RING BUFFERS and extended events.

Wednesday 22 August 2012

INSERT INTO + CTE SELECT


Today I was doing a development where I wanted to insert a output of a CTE to a temp variable,
normal INSERT T-SQL syntax wont work as CTE needs a semi-colon before the start and will give the below error,
 
Msg 336, Level 15, State 1, Line 11
Incorrect syntax near 'grp_opbx'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.
 
Here is the right syntax,
 
DECLARE @tmptbl TABLE (tmp_col1 int,tmp_col2 int);
WITH Testcte( col1, col2) AS
(
SELECT a.samplecol,b.samplecol2 from testdb1.dbo.testfunction(arg1) AS i
JOIN
testdb2.dbo.tbl123 AS b ON a.check1 = b.check2
)
INSERT INTO @tmptbl
SELECT col1, col2 from Testcte
--Check the result
SELECT * from @tmptbl

Monday 20 August 2012

Monitoring using EVENTDATA() Function


One of most under utilized function by DBA for monitoring SQL server is EVENTDATA() function, With Evendata function you can monitor many database and instance level changes at a granular level.

The only drawback is Eventdata functions can be used only within a DDL trigger or Logon trigger.
Let us see some examples how we can use Evendata() function,

I am going to use DDL_SERVER_LEVEL_EVENTS and DDL_DATABASE_LEVEL_EVENTS event group in a trigger to capture some simple DDL events at server level and database level,

1. DDL_Server_LEVEL_Events:-
CREATE TRIGGER Eventtest
ON ALL SERVER
FOR DDL_SERVER_LEVEL_EVENTS
AS
DECLARE @result XML
SET @result = EVENTDATA()
SELECT
GETDATE() As Time,
@result.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(20)') As EventType,
@result.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(20)') As SPID,
@result.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(20)') As LoginName,
@result.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(300)') As TSQL,
@result.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(20)') As ServerName,
@result.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(20)') As DatabaseName
GO
CREATE DATABASE TEST
GO
ALTER DATABASE TEST SET RECOVERY SIMPLE
GO
CREATE LOGIN [Test] WITH PASSWORD='test',CHECK_POLICY=OFF
GO
DROP LOGIN [Test]
GO
DROP DATABASE TEST
GO
DROP TRIGGER Eventtest
ON ALL SERVER;
GO

Result:-



Time
EventType
SPID
LoginName
TSQL
ServerName
DatabaseName
2012-08-20 17:45:54.927
CREATE_DATABASE
135
Test\Vinoth
CREATE DATABASE TEST   
T01\TEST
TEST
2012-08-20 17:45:54.997
ALTER_DATABASE
135
Test\Vinoth
ALTER DATABASE TEST SET RECOVERY SIMPLE  
T01\TEST
TEST
2012-08-20 17:45:55.040
CREATE_LOGIN
135
Test\Vinoth
CREATE LOGIN [Test] WITH PASSWORD='******',CHECK_POLICY=OFF  
T01\TEST
NULL
2012-08-20 17:45:55.097
DROP_LOGIN
135
Test\Vinoth
DROP LOGIN [Test]  
T01\TEST
NULL
2012-08-20 17:45:55.203
DROP_DATABASE
135
Test\Vinoth
DROP DATABASE TEST  
T01\TEST
TEST



2. DDL_DATABASE_LEVEL_EVENTS:-
CREATE TRIGGER Eventtest
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @result XML
SET @result = EVENTDATA()
SELECT
GETDATE() As Time,
@result.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(20)') As EventType,
@result.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(20)') As SPID,
@result.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(20)') As LoginName,
@result.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(300)') As TSQL,
@result.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(20)') As ServerName,
@result.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(20)') As DatabaseName
GO
CREATE TABLE test1 (col1 int)
GO
ALTER TABLE test1 ADD col2 int
GO
CREATE LOGIN [Test] WITH PASSWORD='test',CHECK_POLICY=OFF
GO
CREATE USER test for login Test
GO
GRANT SELECT ON test1 TO test
GO
DROP DATABASE TEST
GO
DROP TRIGGER Eventtest
ON DATABASE;
GO

Result:-
You can see all server events are ignored and only database events are captured.

Time
EventType
SPID
LoginName
TSQL
ServerName
DatabaseName
2012-08-20 17:59:09.087
CREATE_TABLE
135
Test\Vinoth
CREATE TABLE test1 (col1 int)  
T01\TEST
TEST
2012-08-20 17:59:09.157
ALTER_TABLE
135
Test\Vinoth
ALTER TABLE test1 ADD col2 int  
T01\TEST
TEST
2012-08-20 17:59:09.197
CREATE_USER
135
Test\Vinoth
CREATE USER test for login Test  
T01\TEST
TEST
2012-08-20 17:59:09.230
GRANT_DATABASE
135
Test\Vinoth
GRANT SELECT ON test1 TO test  
T01\TEST
TEST


I have just given a sample of what can be done, the total events which can be captured using even data can be found in http://msdn.microsoft.com/en-us/library/bb510452.aspx ,You can also find all the events supported by event data C:\Program Files\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.