tag:blogger.com,1999:blog-88570192410815366392024-03-05T06:38:27.807-08:00Sam's SQL Server blogTips, Design principles, Best practices...Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.comBlogger55125tag:blogger.com,1999:blog-8857019241081536639.post-51870417680929490712012-04-22T07:08:00.000-07:002012-04-22T07:08:07.567-07:00How to create SEQUENCE in SQL Server 2012<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
<span style="font-family: Calibri;"><span style="font-family: Times New Roman;">
</span></span><span style="font-family: inherit;">Sequence ID generation is one of the common problems faced by SQL Server development community especially with high concurrent user scenarios. Though
SQL Server supports different work arrounds currently, each one has its own pros
and cons.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">
Microsoft has finally put an end to the long wait and
introduced the SEQUENCE object in its latest version SQL Server 2012 (Similar to the Oracle Sequences).</span></div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<span style="font-family: inherit;">SEQUENCE is a user defined object type which generates a
sequence of numbers based on the increment definition.I have given the syntax and example below</span></div>
<span style="font-family: Calibri;">
</span><h4 class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
</h4>
<h4 class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
SEQUENCE Syntax</h4>
<span style="font-family: Calibri;">
<div class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
<span style="font-family: Times New Roman;"><strong>
</strong></span><span lang="EN" style="color: #333333; font-family: "Verdana","sans-serif"; line-height: 115%; mso-ansi-language: EN;"><span style="font-size: x-small;">CREATE
SEQUENCE [SequenceName]<br />
START WITH [StarNumber]<br />
INCREMENT BY [IncrementNumber];</span></span></div>
</span><h4 class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
SEQUENCE Example</h4>
<span style="font-family: Calibri;">
<div class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
<span style="font-family: Times New Roman;"><strong>
</strong></span><span lang="EN" style="color: #333333; font-family: "Verdana","sans-serif"; line-height: 115%; mso-ansi-language: EN;"><span style="font-size: x-small;">CREATE
SEQUENCE EmployeeID<br />
START WITH 1<br />
INCREMENT BY 1;<o:p></o:p></span></span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><strong>
</strong></span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-size: x-small;"><strong>
</strong><span lang="EN" style="color: #333333; line-height: 115%; mso-ansi-language: EN;">INSERT INTO EmployeeTable ( ID ,Name, Age
,Sex)</span></span></span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
<span lang="EN" style="color: #333333; line-height: 115%; mso-ansi-language: EN;"></span><span lang="EN" style="color: #333333; line-height: 115%; mso-ansi-language: EN;"><span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-size: x-small;">SELECT NEXT VALUE FOR EmployeeID ,’sam’ ,’30’,’Male’</span></span></span></div>
</span><div class="MsoNormal" style="margin: 0in 0in 10pt; text-align: left;">
For generating Sequence on older version of SQL Server , refer to my earlier article <a href="http://www.samsudeenb.blogspot.in/2009/06/how-to-generate-sequence-number-in-sql.html">http://www.samsudeenb.blogspot.in/2009/06/how-to-generate-sequence-number-in-sql.html</a></div>
</div>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com5tag:blogger.com,1999:blog-8857019241081536639.post-78038922419254979632012-01-08T08:11:00.000-08:002012-01-08T08:11:39.825-08:00Plan Cache and Parameter sniffing in SQL Server 2008<div dir="ltr" style="text-align: left;" trbidi="on"> <span style="font-family: Calibri;">I have recently encountered<span style="mso-spacerun: yes;"> </span>performance problem with one of my production servers due to parameter sniffing in SQL Server 2008. It actually took us quite a bit time to find out the problem and I thought it would be really useful to share it. So what is parameter sniffing?</span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">“Parameter sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed”. <span style="mso-spacerun: yes;"> </span>In Other words the plan is never regenerated / optimized after the first execution which forces the optimizer to use the same plan irrespective of the parameters.</span></div><span style="font-family: Calibri;"><span style="mso-spacerun: yes;"> </span>Though there are many ways to identify the parameter sniffing the simplest behavior is there is a considerable difference in the query execution times when executed using SQL Query Analyzer and actual stored procedure call from the application.</span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">I have given some of the commonly used solutions to solve the “Parameter sniffing”</span></div><b style="mso-bidi-font-weight: normal;"><span style="font-family: Calibri;">Trace Flag<o:p></o:p></span></b><br />
<br />
<span style="font-family: Calibri;">The simplest of all the solutions is to switch of the Parameter Sniffing using the trace flag 4136. The DBCC syntax is given below</span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">DBCC TRACEON (4136)</span></div><b style="mso-bidi-font-weight: normal;"><span style="font-family: Calibri;">Local Variable Substitution <o:p></o:p></span></b><br />
<br />
<span style="font-family: Calibri;">We can force the SQL optimizer to temporarily suspend the Parameter Sniffing by using local variables substitution for the parameters and use only the local variables in the query as shown below</span><br />
<br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">CREATE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">PROCEDURE</span> pGetPatientName<o:p></o:p></span><br />
<span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">(<o:p></o:p></span><br />
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>@P_PatientID <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>50<span style="color: grey;">),<o:p></o:p></span></span><br />
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>@P_Name <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>50<span style="color: grey;">)<o:p></o:p></span></span><br />
<span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">)<o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">AS<o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">BEGIN<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">DECLARE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> @V_PatientID <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>50<span style="color: grey;">)<o:p></o:p></span></span></div><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">DECLARE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> @V_Name <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>50<span style="color: grey;">)<o:p></o:p></span></span><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SET</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> @V_Name <span style="color: grey;">=</span> @P_Name<o:p></o:p></span></div><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SET</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> @V_PatientID <span style="color: grey;">=</span> @P_PatientID<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <o:p></o:p></span></div><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">ForeName<span style="color: grey;">,<o:p></o:p></span></span><br />
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SurName <o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">FROM<o:p></o:p></span><br />
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">Patient <o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> PatientID <span style="color: grey;">=</span> @V_PatientID<span style="color: red;"><o:p></o:p></span></span><br />
<span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">AND</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> ForeName <span style="color: grey;">LIKE </span>@V_Name<span style="color: grey;">+</span><span style="color: red;">'%'</span><o:p></o:p></span><br />
<br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">END</span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;"><b style="mso-bidi-font-weight: normal;"><span style="font-family: Calibri;">Recompile & Query Hints</span></b></div><div class="MsoNormal" style="margin: 0in 0in 10pt;"><span style="font-family: Calibri;">You can also make the optimizer to force for the plan compilation using the options such as</span></div> <span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="mso-list: Ignore;"><span style="font-family: Calibri;">1.</span><span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Execute the query “With Recompile” option</span><br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;"><span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="mso-list: Ignore;"><span style="font-family: Calibri;">2.</span><span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Alter the Procedure / Drop & Re Create Indexes</span></div><div class="MsoListParagraphCxSpLast" style="margin: 0in 0in 10pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;"><span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="mso-list: Ignore;"><span style="font-family: Calibri;">3.</span><span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Specifying Query Hints</span></div></div>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com12tag:blogger.com,1999:blog-8857019241081536639.post-7374673524713068512011-11-20T06:16:00.000-08:002011-11-20T06:16:24.737-08:00How to retrieve TOP Distinct values in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-family: "Times New Roman","serif"; font-size: 12pt; mso-fareast-font-family: "Times New Roman";">I have recently got a requirement from one of our customers to show recently used distinct items for a particular report. While writing the query, I found out it is not straight forward as there is a little cache “recent items” in the requirement. <o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;"><span style="font-family: "Times New Roman","serif"; font-size: 12pt; mso-fareast-font-family: "Times New Roman";">This can be achieved by using a simple trick as shown in the below example. To illustrated the scenario, I have create a sample table with data<o:p></o:p></span></div><br />
<strong>Sample Data</strong><br />
<br />
CREATE TABLE SaleItem ( ItemName NVARCHAR(30),SaleDate DATETIME)<br />
<br />
insert into SaleItem SELECT 'Book',GETDATE()<br />
insert into SaleItem SELECT 'Book',GETDATE()<br />
insert into SaleItem SELECT 'Book',GETDATE()<br />
insert into SaleItem SELECT 'Pencil',GETDATE()<br />
insert into SaleItem SELECT 'Pencil',GETDATE()<br />
insert into SaleItem SELECT 'Pencil',GETDATE()<br />
insert into SaleItem SELECT 'Pen',GETDATE()<br />
insert into SaleItem SELECT 'Pen',GETDATE()<br />
insert into SaleItem SELECT 'Pen',GETDATE()<br />
<br />
<strong>SQL Query</strong><br />
<br />
WITH ItemList AS<br />
(<br />
SELECT<br />
IL.ItemName,<br />
IL.SaleDate,<br />
ROW_NUMBER() OVER (ORDER BY IL.SaleDate DESC) as 'RankID'<br />
FROM <br />
SaleItem IL<br />
)<br />
SELECT DISTINCT TOP 30 <br />
IL.ItemName,<br />
IL.SaleDate <br />
FROM ItemList IL<br />
WHERE NOT EXISTS (Select 1 FROM ItemList IL2 <br />
WHERE ( IL2.ItemName = IL.ItemName )<br />
AND IL.RankID < IL2.RankID)<br />
ORDER BY SaleDate DESC<br />
<br />
The trick is assigning RankID to each row and eliminate the duplicate row by comparing the RankID while selecting the records.</div>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com4tag:blogger.com,1999:blog-8857019241081536639.post-71798862693867270572010-12-19T02:02:00.000-08:002010-12-19T02:02:55.257-08:00How to Create Linked Server between MYSQL and SQL ServerSteps to configure Linked Server <br />
<br />
<strong>Step 1:</strong><br />
<ul><li>Install the My SQL OBDC connector on the SQL Server machine (This will come default if you have already installed My SQL in the SQL Server machine. The setup file for the drive is attached with in the mail.</li>
<li>Create an ODBC System DSN with the name “MYSQL” using the driver installed on the previous step as shown in the below image <div></div></li>
</ul><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbtF6f3yny4kCH4PrmaVTu2P32SmZe6MYlqfTsECtFTO0FxpP1X81ToySX_ZSLSiBYtNcTmdT9k1OqioEteS-RvmypOpZrBGZdtAJKYX5mVPk0aLLNV2k0Rjuurm8lDuWpOv9U552hIfB9/s1600/CREATE_ODBC.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbtF6f3yny4kCH4PrmaVTu2P32SmZe6MYlqfTsECtFTO0FxpP1X81ToySX_ZSLSiBYtNcTmdT9k1OqioEteS-RvmypOpZrBGZdtAJKYX5mVPk0aLLNV2k0Rjuurm8lDuWpOv9U552hIfB9/s320/CREATE_ODBC.bmp" width="320" /></a> <br />
<br />
<div></div> <strong>Step 2: </strong><br />
<div></div><div>Create a linked server between the SQL Server and the My SQL Server using the steps given below. </div><ul><li>Connect to the SQL Server instance and expand to the section Linked Servers </li>
<li>Right click on the Linked Server and select the option “New Linked Server”</li>
</ul><br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgr5Wq449TVtd0l9QGfHvi8KakIiOpUcT1IlGxgFTC6rFMjmQIMBXqbnfD_PC-vQ6QGQp0B12yF5OwYWQoKy3Knpju8B1QJMEiwFQTExswvEWt7Q0zhaGT1wgWkXMvPJ38aaM5kAb2-RSja/s1600/LINKED_SERVER.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgr5Wq449TVtd0l9QGfHvi8KakIiOpUcT1IlGxgFTC6rFMjmQIMBXqbnfD_PC-vQ6QGQp0B12yF5OwYWQoKy3Knpju8B1QJMEiwFQTExswvEWt7Q0zhaGT1wgWkXMvPJ38aaM5kAb2-RSja/s1600/LINKED_SERVER.bmp" /></a></div><ul><li><div class="separator" style="clear: both; text-align: left;">Specify the connection details to the ODBC driver as shown below</div></li>
</ul><br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDkz6vWpq3zyu8R42jm1GzIG5vEUvyDKIM6tCCZ3HCWc_SIEbpZd-RfjKTt4WLp6THZb65TG5EV_4aEwuyLOfcG9VraYXRGVO0UwTHeB6fC1XF5_1Ld0AJnPlQp_gXKODm-aeqlR9ClwBb/s1600/CREATE_LINKEDSERVER.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="287" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDkz6vWpq3zyu8R42jm1GzIG5vEUvyDKIM6tCCZ3HCWc_SIEbpZd-RfjKTt4WLp6THZb65TG5EV_4aEwuyLOfcG9VraYXRGVO0UwTHeB6fC1XF5_1Ld0AJnPlQp_gXKODm-aeqlR9ClwBb/s320/CREATE_LINKEDSERVER.bmp" width="320" /></a> <br />
<br />
<div></div>Details<br />
<div></div>Linked Server : MYSQL<br />
Provider : Microsoft OLE DB provider for ODBC drivers<br />
Product Name : MYSQL<br />
Data Source : MYSQL<br />
Provider String : DRIVER={MySQL ODBC 5.1 Driver};SERVER=SAMSUDEEN;PORT=3306;DATABASE=salem_dbo;USER=root;PASSWORD=password<br />
Catalog : salem_dbo<br />
<ul><li>Go to the Security Tab and give tab and give the MY SQL username & password under the option “Be made using this security context”</li>
<li>Go to the Server options and set value true for RPC & RPC Out properties.</li>
</ul><strong>Step 3:</strong><br />
<ul><li>Click OK to create the linked server. You can test the connection using the test connection option as shown below</li>
<div class="separator" style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD92aOc4EjXMmp-pAHSWr-WpZRRawHsV4eEzVNlwOqSYunH090jQpOtGEKugKNTIxGevfGcoaGRiX3G49fJfr0o_PJ50tX7tTL8yJg9DKGPLj7M28ffDI_uPfvJMgnCK4K3nwN8_NyxZGC/s1600/TEST_LINKED_SERVER.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD92aOc4EjXMmp-pAHSWr-WpZRRawHsV4eEzVNlwOqSYunH090jQpOtGEKugKNTIxGevfGcoaGRiX3G49fJfr0o_PJ50tX7tTL8yJg9DKGPLj7M28ffDI_uPfvJMgnCK4K3nwN8_NyxZGC/s1600/TEST_LINKED_SERVER.bmp" /></a></div></ul><br />
<div></div><br />
<div></div><br />
<div></div>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com9tag:blogger.com,1999:blog-8857019241081536639.post-88656967225605882952010-03-23T04:30:00.000-07:002010-03-23T04:32:49.176-07:00How to run a SQL batch multiple times<span style="font-size:85%;">Today I came across one surprising use of GO statement in SQL Server which is solely used as batch terminator in SQL Server utilities such as SQL Server Management Studio etc. Though “GO” is not a Transact –SQL statement it can be used in multiple ways .One such example is to run a batch of statements as illustrated below </span><br /><span style="font-size:85%;"><br />CREATE TABLE TestData ( Name VARCHAR(100), Age INT)<br />GO<br />Insert into TestData<br />SELECT 'John' ,28<br />GO 100 </span><br /><br /><span style="font-size:85%;">In the above example I have inserted 100 rows of test data by just specifying 100 next to the GO statement. All these days I have used the traditional while loop statement for doing this, now just specify the number next to GO statment the job is done. This feature is no where documented and supported only in SQL Server 2005 and greater versions </span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com6tag:blogger.com,1999:blog-8857019241081536639.post-58564827911628318782010-02-22T08:36:00.000-08:002010-02-23T01:14:57.477-08:00How to recover a Database in suspect mode<p><span style="font-size:85%;">I was struck with a database which is on suspect mode and need to recover it without any recent backups. I searched across the net and found these steps which are quite useful </span></p><span style="font-size:85%;"><p><strong>Step 1:<br /></strong>Clear the suspect mode of the database using sp_resetstatus <em><span style="font-size:78%;">DatabaseName</span></em>. This will clear the suspect flag and make the database available online </p><p><strong>Step 2:</strong><br />Change the database status to Emergency using the following command. Emergency mode allows you to access the databases as normal but with no consistency guarantee. This option also allows us to export the table data so that we can minimize the damage.<br />ALTER DATABASE <em><span style="font-size:78%;">DatabaseName</span></em> <database>SET EMERGENCY; </p><p><strong>Step 3:</strong><br />Restrict database to single user by changing the access mode as mentioned below<br />ALTER DATABASE <em><span style="font-size:78%;">DatabaseName</span></em> <database>SET SINGLE_USER; </p><p><strong>Step 4:</strong><br />Run the CHECKDB command with “REPAIR_ALLOW_DATA_LOSS” option. This option should be tried as last option as it always behaves the way it is named. We are not sure of what data it removes.<br />DBCC CHECKDB (<em><span style="font-size:78%;">DatabaseName<database></span></em>, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS; </p><p>There are some best (simple) practices which prevents us from such failures. Below are some of them </span></p><ul><li><span style="font-size:85%;">Backup your data frequently ( daily or once in two days) </span></li><li><span style="font-size:85%;">Have multiple backups. Move the backups to external drives or tapes frequently </span></li><li><span style="font-size:85%;">Validate that your backups are good by doing trial restores to alternate server </span></li><li><span style="font-size:85%;">Run CHECKDB regularly</span></li></ul>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com9tag:blogger.com,1999:blog-8857019241081536639.post-8598115509496808872009-11-18T07:33:00.000-08:002009-11-24T01:46:28.636-08:00Filter Index in SQL Server 2008<span style="font-size:85%;">SQL Server 2008 introduces a new type of index called “Filtered Index” which is basically a covered indexed designed to retrieve a smaller set of qualified data from a table. This can be a very hand feature particularly in working with larger data tables.<br />A Filter index allows us to apply filter criteria on the index definition so that a particular sub set of rows in a table alone can be indexed. Filter indexes can be only created as non clustered index </span><span style="font-size:85%;"><br /></span><span style="font-size:85%;"><br />Syntax<br />CREATE INDEX Index Name ON Table Name (Columns..) Filter Criteria </span><br /><span style="font-size:85%;"><br />Example<br />CREATE INDEX IX_RegistrationDate ON Employee (RegistrationDate) WHERE RegistrationDate IS NOT NULL</span><br /><span style="font-size:85%;"></span><span style="font-size:85%;"><br /><strong>Advantages of Filtered Index</strong><br /><br /></span><span style="font-size:85%;"><ul><li><strong>Improved Performance</strong>: The performance of the query is improved especially with larger tables as it has to scan through as lesser number of records<br /></li><li><strong>Lesser Maintenance Cost</strong>: Since the size of the index is smaller compared to full table index the index maitntenance cost will be much lesser. Also index maintenance jobs like update statics could be faster. </li><br /><li><strong>Lesser Storage</strong>: The amount of space required for index storage will also be very less since the size of the index is smaller compared to the full table index </li></ul><p><strong>Analysis</strong></p><p>I have created a table patient with 1 lakh records of different Organisations and populated 70% data with OwnerOrganisation value 10 and selected the record with OwnerOrganisation value ="6" </p><p>Normal Index</p><p><span style="font-size:78%;"><span style="font-family:courier new;font-size:100%;"><strong>CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID)</strong></span> </span></span></p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0DktNeKFQIb5UtNiMkFuZsxCFkffsWohH-b8hvRKc3OVj4SXkqhVe8gSNlXpOHW8e4znDC5iZczSNFIjlC7oZjPTzhUdnhJJVc8UJ8AFSkNIW7WHG-zc9wGKuF5SGWFhwd0g9R8wIw-_a/s1600/NormalIndex.bmp"><img style="WIDTH: 320px; HEIGHT: 164px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5405474961755379394" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0DktNeKFQIb5UtNiMkFuZsxCFkffsWohH-b8hvRKc3OVj4SXkqhVe8gSNlXpOHW8e4znDC5iZczSNFIjlC7oZjPTzhUdnhJJVc8UJ8AFSkNIW7WHG-zc9wGKuF5SGWFhwd0g9R8wIw-_a/s320/NormalIndex.bmp" /></a><br /><br /><span style="font-size:85%;">Filterer Index to exclude records of Organisation =10</span><br /><span style="font-size:85%;"><br /><span style="font-family:courier new;">CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID) </span><span style="font-family:courier new;">WHERE OwnerOrganisationUID <>10</span> <span style="font-size:78%;"><br /></span></span><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDWRKiMCjQeP5tzUloavGRYnXlbUOP5vClr1YcIgvgkyLyVq9HjFvlpB9jlCxKoYd3vdyRvvTEA3SHnPicdbAFQtEORMGHNlm7NylU7Lmzpmea2k_gyMwswT5FG-dAAxxZaHhmV0fb8mV8/s1600/ImprovedIndex.bmp"><img style="WIDTH: 320px; HEIGHT: 165px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5405475351118675010" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDWRKiMCjQeP5tzUloavGRYnXlbUOP5vClr1YcIgvgkyLyVq9HjFvlpB9jlCxKoYd3vdyRvvTEA3SHnPicdbAFQtEORMGHNlm7NylU7Lmzpmea2k_gyMwswT5FG-dAAxxZaHhmV0fb8mV8/s320/ImprovedIndex.bmp" /></a><br /><br /><br /><span style="font-size:85%;"><strong>Conclusion</strong></span><br /><br /><span style="font-size:85%;">Index creation is always case to case basis as the need to create a filtered index should be carefully analysed based on the WHERE clause and the data distribution in the table. It is recommended to create filtered indexes if the data retrieved to be a smaller subset. Scenarios like columns with NULL data as major set and NOT NULL values of defined subsets could be a suitable candidate</span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com9tag:blogger.com,1999:blog-8857019241081536639.post-27018797002476157902009-11-04T22:44:00.000-08:002009-11-04T23:13:48.581-08:00How to select records with in Date Range<span style="font-size:85%;">Selecting records within a given date range is one of the common requirement these days, but many people find it difficult. The most common mistake people make is always try to do the comparison as the same as the way we do for numbers as shown below<br /><br /><strong>Wrong comparison</strong><br />SELECT *<br />FROM Table<br />WHERE StartDate >= @P_StartDate<br />AND EndDate =< @P_EndDate <br /><br />This wills not retrieve the qualified records as the comparison will not be against the range instead it will be against two dates and it will ignore any records fall within the range. The trick is to change the parameter to check the date in the reverse order as shown below. <br /><br /><strong>Modified Query </strong><br />SELECT *<br />FROM Table <br />WHERE EndDate > = @P_StartDate<br />AND StartDate =< @P_EndDate </span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com4tag:blogger.com,1999:blog-8857019241081536639.post-11792814744015377712009-07-27T04:59:00.000-07:002009-07-27T05:13:47.863-07:00Parameterized sorting in SQL Server<span style="font-size:85%;">Applications that allow users to sort data by different columns of the table might need to go for dynamic stored procedures or will end up in adding multiple procedures based on the number of combinations. In SQL Server we can achieve this easily through parameterized sorting.<br />As shown in the below example we can have a parameter which says the column on which the sort criteria can be applied and using the case statement we achieve the results without going for a dynamic /multiple stored procedures. </span><br /><br /><span style="font-size:78%;"><strong>DECLARE @SortOrder INT<br />SET @SortOrder =1<br />SELECT ForeName,<br />SurName,<br />PASID<br />FROM PAtient<br />WHERE Forename LIKE 'A%'<br />ORDER BY CASE WHEN @SortOrder = 1 THEN ForeName<br />WHEN @SortOrder = 2 THEN SurName<br />ELSE PASID<br />END </strong></span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com4tag:blogger.com,1999:blog-8857019241081536639.post-86401715188460669722009-07-09T07:15:00.001-07:002009-07-09T07:33:27.194-07:00Generate Index scripts using included columns<span style="font-size:85%;">This article is a continuation of the previous article </span><a href="http://samsudeenb.blogspot.com/2007/11/scripts-to-drop-and-recreate-indexes-in.html"><span style="font-size:85%;">http://samsudeenb.blogspot.com/2007/11/scripts-to-drop-and-recreate-indexes-in.html</span></a><span ><span style="font-size:85%;"> with small enhancement to generate the index scripts with included columns. The below function “fGetIncludedIndex is used to get the included columns for a given index.<br /></span><br /></span><span ><span style="font-size:78%;">CREATE FUNCTION fGetIncludedIndex (<br />@objname VARCHAR(50),<br />@indid INT<br />)<br />RETURNS NVARCHAR(200)<br />AS<br />BEGIN<br />DECLARE @V_IncludedCol NVARCHAR(200)<br />select @V_IncludedCol = COALESCE(@V_IncludedCol + ',', '') +<br />( select name from sys.syscolumns where id =a.Object_id and colid =a.column_id)<br />from sys.index_columns a<br />where object_name(a.object_id) =@objname<br />and a.index_id =@indid<br />and a.Is_included_column =1<br />order by a.index_column_id<br />return @V_IncludedCol<br />END </span><br /><span style="font-size:78%;"></span></span><span style="font-size:78%;"><br /></span><span ><span style="font-size:85%;">After creating the function in the DB make a small change to the existing query( highlighted in bold) to generate the index scripts with included columns </span><br /></span><span style="font-size:85%;"><br /></span><span style="font-size:78%;">SELECT 'CREATE ' +<br />CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +<br />'NONCLUSTERED ' + ' INDEX '+<br />NAME + ' ' +' ON ' +<br />OBJECT_NAME (OBJECT_ID) +<br />'('+<br />DBO.fGetIndexCols (object_NAME(OBJECT_ID), index_id) + ')'<br /><strong>+Case when dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id)+')' end +</strong><br />' ON ['+<br />( SELECT GROUPNAME<br />FROM SYSFILEGROUPS<br />WHERE GROUPID = DATA_SPACE_ID<br />) + ']' IndexScript<br />FROM SYS.INDEXES<br />WHERE NAME IS NOT NULL<br />AND Is_Primary_Key =0<br />AND type_desc ='NONCLUSTERED'<br />AND OBJECT_ID > 97 </span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com17tag:blogger.com,1999:blog-8857019241081536639.post-34538249190557230632009-07-07T07:16:00.000-07:002009-07-07T07:23:41.292-07:00How to take week or month wise report in SQL Server<span style="font-size:85%;">I was breaking my head whole day for writing a stored procedure which produces week wise report for plotting a revenue trend. I came across this solution which is fairly a simple one. Just thought of sharing this<br /></span><br /><span style="font-size:85%;">The below code is written using the SQL Server built in functions DATEDIFF & DATEADD to produce the report</span><br /><span style="font-size:85%;"></span><br /><span style="font-size:85%;"><strong>Select DATEADD(WK, datediff(WK, 0, CollectionDate),0) as week,<br />SUM(Amount)<br />from Revenue<br />group by dateadd(WK, datediff(WK 0, CollectionDate),0)<br />order by 1 asc<br /></strong><br />The logic here is to find out the corresponding week from Default date (01-01-1900) and adding the default date again to the result will get the corresponding week in date format. The same can be used for generating month wise report as well by just replacing "wk" with "MM"<br /><br /><strong>Select DATEADD(MM, datediff(MM, 0, CollectionDate),0) as week,<br />SUM(Amount)<br />from Revenue<br />group by dateadd(MM, datediff(MM 0, CollectionDate),0)<br />order by 1 asc</strong> </span><br /><span style="font-size:85%;"><br /><strong>Note: </strong>adding “0” will be automatically converted to default date “01-01-1900” </span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com4tag:blogger.com,1999:blog-8857019241081536639.post-18931847662670373702009-06-27T04:36:00.000-07:002009-06-27T04:44:42.203-07:00How to generate Sequence number in SQL Server<span style="font-size:85%;">Sequence number generation is one of the common requirements in all the OLTP applications.SQL Server supports many ways to generate Sequence numbers. The below example explains how to generate multiple sequences dynamically using the SQL Server </span><br /><span style="font-size:85%;"><br /></span><strong>Schema Design</strong><br /><br /><p><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 320px; DISPLAY: block; HEIGHT: 148px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5351970366881631074" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheUzBTjNjxIWdY8-JN_A3MTkpNgEgOY3sO2HLuqx9nYJ_QgLzzq1ASrVd-R73CumAoXTXHsObghtfH6RKc98Pm4Cf5ctE0nENkdf3W3702qVKrbcMMnxxcfBMkZkDGhVXvRtWpCWRBARHz/s320/Schema.bmp" /><span style="font-size:85%;">This table will hold the configuration parameters for each of the Sequence Types (eg: PurchaseOrder, GRN etc).The column “SequenceName” column will have the unique code for each Sequence type and the “TableName” column is used to map the name of the “IDGenerator” table. (I.e. multiple IDGenerator tables can be created with the same structure and mapped accordingly)</span><br /><strong></strong></p><p><strong>ID Generation:</strong><br /><span style="font-size:85%;">The below stored procedure pGetSEQID is used to generate the new sequence number .It accepts the table name as input and return the new sequence number. Since the IDGenerator table can be different for each ID type the stored procedure is written as dynamic.</span> </p><p><br /><span style="font-size:78%;">CREATE PROCEDURE pGetSEQID (<br />@P_SEQTableName VARCHAR(30)<br />)<br />AS<br />BEGIN<br />DECLARE @V_SEQValue BIGINT<br />DECLARE @V_SQLString NVARCHAR(200)<br />DECLARE @V_ParmDefinition NVARCHAR(200)<br />DECLARE @V_SEQTableName VARCHAR(30)<br />SET @V_ParmDefinition = N'@V_SEQValue BIGINT OUTPUT'<br />SET @V_SQLString = N'INSERT INTO ' +<br />@P_SEQTableName +<br />N'(Status) values (''Y'') SELECT @V_SEQValue = SCOPE_IDENTITY()'<br />exec sp_executesql @V_SQLString,@V_ParmDefinition ,@V_SEQValue =@V_SEQValue OUTPUT<br />SELECT @V_SEQValue NewSequenceValue<br />END</span><br /><br /><span style="font-size:85%;"><strong>EXEC pGetSEQID ‘PurchaseOrder’</strong> will generate the Sequence Number for ID type purchase order<br /></p></span><p><span style="font-size:85%;">We can make use of Prefix and Suffix columns in the IDParamter table to generate the Sequence number with the required format<br />Eg: “BL0001” , BL100/0908</span> </p>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com6tag:blogger.com,1999:blog-8857019241081536639.post-73464431591368251102009-06-16T06:27:00.000-07:002009-06-16T06:32:12.525-07:00How to Select Data in Random Order in SQL Server<span style="font-size:85%;">In number of scenarios we might want to do data sampling or select the data in a Random Order. SQL Server supports various options for data sampling. I have given some of the examples here<br /><br /><strong><span style="font-size:78%;">Using NEWID() :</span></strong><br /><span style="font-size:78%;">SELECT TOP 10 ForeName<br />FROM Patient<br />ORDER BY NEWID()</span><br /><br /><span style="font-size:78%;"><strong>Using PERCENT</strong><br />SELECT TOP 10 ForeName<br />FROM (SELECT TOP 30 PERCENT ForeName<br />FROM Patient<br />ORDER BY ForeName ASC) AS Pat<br />ORDER BY 1 DESC</span><br /></span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com5tag:blogger.com,1999:blog-8857019241081536639.post-91789703930717908902009-05-08T07:13:00.000-07:002009-05-08T07:25:11.416-07:00How to find the Database Restore Details in SQL Server 2008<span style="font-size:85%;">It is often useful to know the details from where the database is restored.The restore history of the database is stored in the msdb table "restorehistory".I recently came across this query which gives the complete information of restore history including the Restored Login , From & To Location of the Files<br /><br />SELECT<br />rsh.destination_database_name AS [Database],<br />rsh.user_name AS [Restored By],<br />CASE WHEN rsh.restore_type = 'D' THEN 'Database'<br />WHEN rsh.restore_type = 'F' THEN 'File'<br />WHEN rsh.restore_type = 'G' THEN 'Filegroup'<br />WHEN rsh.restore_type = 'I' THEN 'Differential'<br />WHEN rsh.restore_type = 'L' THEN 'Log'<br />WHEN rsh.restore_type = 'V' THEN 'Verifyonly'<br />WHEN rsh.restore_type = 'R' THEN 'Revert'<br />ELSE rsh.restore_type<br />END AS [Restore Type],<br />rsh.restore_date AS [Restore Started],<br />bmf.physical_device_name AS [Restored From],<br />rf.destination_phys_name AS [Restored To]<br />FROM msdb.dbo.restorehistory rsh<br />INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id<br />INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id<br />INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id<br /><br /><br />You can apply filter criteria such as the restore date , Database by addding a where clause to the existing query as shown below<br /><br />WHERE<br />rsh.restore_date >= DATEADD(dd, "No of Past Days" , GETDATE())<br />AND destination_database_name = ISNULL( "DB Name", destination_database_name)<br />ORDER BY rsh.restore_history_id DESC<br /><br /><br /></span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com37tag:blogger.com,1999:blog-8857019241081536639.post-89934293866856562912009-05-05T06:14:00.000-07:002009-05-05T06:28:20.643-07:00SQL Server 2008 - Performance white paper<span style="font-size:85%;">Microsoft has release its latest white paper on SQL Server 2008 performance trouble shooting.This article provides steps to diagonse the common performance problems faced by Microsoft CSS team.The following areas are covered in depth</span><br /><ul><li><span style="font-size:85%;">CPU Bottlenecks</span></li><li><span style="font-size:85%;">Memory Bottlenecks</span></li><li><span style="font-size:85%;">IO Bottlenecks</span></li><li><span style="font-size:85%;">Temp DB</span></li><li><span style="font-size:85%;">Slow Running Queries</span></li><li><span style="font-size:85%;">Extended Events</span></li><li><span style="font-size:85%;">Data Collector & MDV</span></li></ul><span style="font-size:85%;">The new features of SQL Server 2008 such as Extended Events & Data Collector are covered in detail .It is worth to have a look at this long running ( 102 pages) document. It is avilable for download at</span><br /><span style="color:#009900;"><span style="font-size:85%;color:#000000;"><a href="http://msdn.microsoft.com/en-us/library/dd672789.aspx">http://msdn.microsoft.com/en-us/library/dd672789.aspx</a></span></span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com2tag:blogger.com,1999:blog-8857019241081536639.post-55673462209450402682009-04-13T01:11:00.000-07:002009-04-13T01:15:36.775-07:00SQL Server 2008 SP1- Released<span style="font-size:85%;">Microsoft has released the latest service pack for SQL Server 2008 with the following key improvements</span><br /><ul><li><span style="font-size:85%;">SlipStream - The SQL Server 2008 and Service Pack 1 installation can be integrated and installed in a single step. </span></li><li><span style="font-size:85%;">Service Pack Uninstall – We can uninstall the service pack alone ( no need to un install the entire service) </span></li><li><span style="font-size:85%;"> Report Builder 2.0 Click Once capability </span></li></ul><p><span style="font-size:85%;">It is available for download at</span></p><p><span style="font-size:85%;">http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19 </span></p>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com3tag:blogger.com,1999:blog-8857019241081536639.post-28937122641803744922009-02-11T22:13:00.000-08:002009-02-11T22:19:11.853-08:00SQL Server 2008 DMV's Relationship mapping<span style="font-size:85%;">Microsoft has introduced the concept of DMV’s (Dynamic Management Views) in SQL Server 2005 and concept is extended to SQL Server 2008 also with additional DMV's for mirroring , memory management etc.</span><br /><span style="font-size:85%;"><br />Microsoft has recently released the latest “System Views Map” for SQL Server 2008 which shows the key system views and the relationships between them. You can download the latest System Views Map at<br /><br /></span><a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en"><span style="font-size:85%;">http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en</span></a><br /></span><br /><span style="font-size:85%;">The updated “System views Map” for SQ Server 2005 also can be downloaded at</span><br /><span style="font-size:85%;"><br /></span><a href="http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en"><span style="font-size:85%;">http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en</span></a>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com5tag:blogger.com,1999:blog-8857019241081536639.post-84684797024979129482009-02-11T06:33:00.000-08:002009-02-11T06:45:55.382-08:00SQL Server Hot Fixes at one place<span style="font-size:85%;">Microsoft is now days releasing lot of hot fixes for its products’ recently found one TechNet blog which is specific to hot fixes. This is one place where you can find the cumulative update of each hot fixes / updates released for SQL Server, also it has the release note information linked to the knowledge base articles.</span><br /><span style="font-size:85%;"></span><br /><span style="font-size:85%;">This blog also contains hot fix information for other Microsoft products such as Windows, Visual Studio, and IE etc. The original blog can be find at</span><br /><br /><span style="font-size:85%;"><a href="http://blogs.technet.com/hot/archive/tags/SQL+Server/default.aspx">http://blogs.technet.com/hot/archive/tags/SQL+Server/default.aspx</a><a href="http://blogs.technet.com/hot/archive/tags/SQL+Server/default.aspx"></a></span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com2tag:blogger.com,1999:blog-8857019241081536639.post-83155319461128444982009-02-05T05:28:00.000-08:002009-02-05T05:31:12.559-08:00Reasons for slower Delete in SQL Server<p><span style="font-size:85%;">Delete statement plays a major role in many of the database maintenance activities. Extreme care should be taken before executing the DELETE statements<br />Before executing the DELETE statement it is better to check the “Estimated execution plan”, so that we can create proper indexes to speed up the execution .I have given some of the possible reasons for slower delete.</span></p><p><span style="font-size:85%;"><br />1. Locking / Blocking - If it is a production database process is having Lock / Block on the table</span></p><p><span style="font-size:85%;">2. Fragmentation - The Index pages are fragmented due to excessive delete on the table (Defragment the Indexes and try again)</span></p><p><span style="font-size:85%;">3. The table you are trying to delete is referred by many tables as foreign key and those columns are not indexed.</span></p><p><span style="font-size:85%;">4. There might be hanging transactions on the table - Try to truncate the Log and do</span></p><p><span style="font-size:85%;">5. You can also change the Recovery Mode to simple and try - but not advised in case of production DB<br /></span></p><p><span style="font-size:85%;">Also it is recommended to execute the DELETE statements in smaller batches so that the Log space & Roll back of the records can be easier as shown below</span></p><p><br /><span style="font-size:85%;"><em>// assume you wanted to delete 100000 records, we can split them into 10 batches as<br />DECLARE @V_Count INT =0<br />WHILE @V_Count < 100000<br />BEGIN<br />DELETE TOP(10000)<br />FROM <strong>Table</strong><br />WHERE <strong>COLUMN</strong> = <strong>Condition</strong><br />SET @V_Count =@V_Count+10000<br />END</em></span></p>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com3tag:blogger.com,1999:blog-8857019241081536639.post-28175640685848055882009-02-04T04:40:00.000-08:002009-02-04T04:42:36.736-08:00Sinlge user mode in SQL ServerI was trying to change the isolation level of my database using the ALTER DATABASE statement and the query was running for hours to execute it, because there were ongoing transactions in the db which is preventing the isolation change. I have solved this problem by taking the DB into single user mode before running the alter statement using below script<br /><br /><span style="font-size:85%;">if(charindex('Microsoft SQL Server 2005',@@version) > 0)<br />begin<br />declare @sql varchar(8000)<br />select @sql = '<br />ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;<br />ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;<br />ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'<br />Exec(@sql)<br />end</span><br /><br />Any changes to Database level properties in SQL Server can be done easily through the use of single user mode, which permits only one connection to be made to the database at any time.Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com4tag:blogger.com,1999:blog-8857019241081536639.post-34140869023705437632009-02-03T06:20:00.000-08:002009-02-03T06:29:52.094-08:00How to combine Multiple Rows into Single Column in SQL ServerI have come across a link where you can combine multiple rows into single column using the COALESCE function.It is very simple as shown below<br /><br /><span style="font-size:85%;">CREATE TABLE WeekDays<br />([Name] varchar(40))<br />INSERT INTO WeekDays VALUES('Mon');<br />INSERT INTO WeekDays VALUES('Tue');<br />INSERT INTO WeekDays VALUES('Wed');<br /><br />DECLARE @str VARCHAR(2000)<br />select @str = COALESCE(@str + ',', '') + [Name]<br />from WeekDays<br />SELECT @str</span><br /><span style="font-size:85%;"></span><br /><em>Output :Mon,Tue,Wed</em><br /><em></em><br />You can see the original article on<br /><a href="http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html">http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html</a>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com16tag:blogger.com,1999:blog-8857019241081536639.post-87923048196094349462009-02-02T06:44:00.000-08:002009-02-02T06:58:06.704-08:00How to import Database Schema to XML<span style="font-size:85%;">I often get questions on how to import database schema into XML file for doing activities like Data Comaprison etc. I have written a small script to generate the database schema including the following</span><br /><ul><li><span style="font-size:85%;">Column Details ( Name, data type ,length etc)</span></li><li><span style="font-size:85%;">Primary Key</span></li><li><span style="font-size:85%;">Foreign Key</span></li><li><span style="font-size:85%;">Indexes</span></li></ul><p><span style="font-size:85%;">Sample Script</span></p><p><span style="font-size:85%;">SELECT a.name TableName,<br />( SELECT<br />c.name ColumnName,type_name(c.xusertype) DataType,<br />CASE WHEN type_name(c.xusertype)='NUMERIC' THEN CAST(c.prec AS SMALLINT)<br />WHEN type_name(c.xusertype)='UNIQUEIDENTIFIER' THEN NULL<br />WHEN type_name(c.xusertype)='BIGINT' THEN<br />CASE WHEN colstat =1 THEN CAST(IDENT_SEED(a.name) AS SMALLINT)<br />END<br />ELSE CAST(c.prec AS SMALLINT)<br />END DataLength,<br />CASE WHEN type_name(c.xusertype)='NUMERIC' THEN c.scale<br />WHEN type_name(c.xusertype)='BIGINT' THEN<br />CASE WHEN colstat =1 THEN CAST( IDENT_INCR(a.name) AS INT)<br />END<br />ELSE NULL<br />END Scale,<br />CAST(c.isnullable AS BIT) As IsNullable,NULL AS DataDefault,NULL AS DefConstraintName,<br />CASE WHEN colstat=1 THEN CAST(1 AS BIT)<br />ELSE CAST(0 AS BIT)<br />END AS IsIdentColumn<br />FROM SYSColumns c<br />WHERE c.id = OBJECT_ID(a.name)<br />and a.id = c.id<br />AND C.CDEFAULT =0<br />FOR XML AUTO, TYPE<br />) columns,<br />(select 'UID' ColumnName,f.name PrimaryKeyName, f.type_desc PrimaryKeyType<br />from sys.indexes f<br />where f.object_id = a.id<br />AND f.NAME IS NOT NULL<br />AND f.Is_Primary_Key =1<br />AND OBJECT_ID > 97<br />FOR XML AUTO, TYPE<br />)PrimaryKey,<br />(Select<br />object_name(rkeyid) Parent_Table,object_name(fkeyid) Child_Table, object_name(constid) FKey_Name, c1.name FKey_Col,c2.name Ref_KeyCol<br />From<br />sys.sysforeignkeys s<br />Inner join sys.syscolumns c1<br />on ( s.fkeyid = c1.id And s.fkey = c1.colid )<br />Inner join syscolumns c2<br />on ( s.rkeyid = c2.id And s.rkey = c2.colid )<br />where s.fkeyid = a.id<br />FOR XML RAW,TYPE<br />) ForeignKey,<br />(select f.name IndexName ,DBO.fGetIndexCols (object_NAME(f.object_id), f.index_id ) IndexColumn,<br />f.type_desc IndexType<br />from sys.indexes f<br />where f.object_id = a.id<br />AND f.NAME IS NOT NULL<br />AND f.Is_Primary_Key =0<br />AND OBJECT_ID > 97<br />FOR XML AUTO, TYPE<br />) Indexes<br />from sysobjects a<br />where a.xtype ='u'<br />FOR XML PATH('Table'), ROOT('TableDetails')</span></p>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com2tag:blogger.com,1999:blog-8857019241081536639.post-35314022833326500382009-01-30T22:12:00.000-08:002009-01-30T22:15:18.533-08:00SQL Server 2008 – Inline variable initialization<span style="font-size:85%;">Microsoft has extended the support of inline variable initialization feature from the programming languages in to T-SQL in SQL Server 2008.</span><br /><span style="font-size:85%;"><br />When we want to declare and initialize a value to variable in T- SQL, we need to do it is two steps (declaration & initialization) as shown in the example<br /><br />DECLARE @V_Value DATETIME<br />SET @V_Value =GETDATE()<br /><br />In SQL Server 2008 this can be simplified by combining both the lines into a single steps as we do in programming languages<br /><br />DECLARE @V_Value DATETIME = GETDATE()<br /><br />Even though it is a very small feature, it helps the developers who have the technical background for programmig languages like JAVA, C#.NET , VB.NET etc</span><br /></span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com3tag:blogger.com,1999:blog-8857019241081536639.post-87235605372152612082009-01-25T07:26:00.000-08:002009-01-25T07:45:28.874-08:00Turn Off - Prevent saving changes in SQL Server 2008<div>I was working with SQL Server 2008 for quite some time and noticed a strange behavior (which is not in previous versions of SQL Server) when I try to save a table in Management Studio that requires table to be dropped and recreated (e.g. Try to ADD a new NOT NULL column to the table which is having data) .I get the following warning message</div><br /><div><br /><span style="font-family:arial;"><em>Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.</em></span></div><br /><div></div><br /><div>I though it is bug with SQL Server, but later cam to know it is the expected behavior and can be turned of by unchecking "Prevent saving changes that require table re-creation" in the Designer properties. Please refer to the below screen shot</div><br /><div></div><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimuhEPlkkh5MRha-i-fx4hiyST52C1_XDiECbQNyBOKYgryoVFxZTtpb1MB4keZC62kLqzsDiK5IrcE47_spADhdK6TgG47k32KGYIv00P9I-BsC2djCunSOH24pVVfkK2B0IufJ3QmX81/s1600-h/SQL.png"><img id="BLOGGER_PHOTO_ID_5295256321631123218" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 320px; CURSOR: hand; HEIGHT: 182px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimuhEPlkkh5MRha-i-fx4hiyST52C1_XDiECbQNyBOKYgryoVFxZTtpb1MB4keZC62kLqzsDiK5IrcE47_spADhdK6TgG47k32KGYIv00P9I-BsC2djCunSOH24pVVfkK2B0IufJ3QmX81/s320/SQL.png" border="0" /></a>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com5tag:blogger.com,1999:blog-8857019241081536639.post-84613593183324966832008-12-14T04:00:00.000-08:002008-12-14T04:05:39.242-08:00How to upgrade database from SQL Server 2005 to SQL Server 2008<span style="font-size:85%;">The following steps should be carried out upgrade a database from SQL Server 2005 to SQL Server 2008<br /></span><br />Step 1:<br /><span style="font-size:85%;">Detach the database files from SQL Server 2005 and attach to SQL Server 2008 Serve</span><span style="font-size:85%;">r</span><br /><br />Step 2:<br /><span style="font-size:85%;">Execute the below command to update the index and table Statistics in the newly attached database<br />SP_UPDATESTATS<br />It is recommended to execute update statistics separately for each table with full scan as given below<br /> UPDATE STATISTICS <schema>.<tablename> WITH FULLSCAN, NORECOMPUTE</span><br /><span style="font-size:85%;"><br /></span>Step 3:<br /><span style="font-size:85%;">Execute the below command to rest all the counters DBCC UPDATEUSAGE('<actual>')<br /></span><br /></span>Step 4:<br /><span style="font-size:85%;">Change the Database Compatibility Level as specified<br /> Go to Database Properties à Options à Compatibility Level to à SQL Server 2008(100)</span>Samsudeen Bhttp://www.blogger.com/profile/04746198882784164274noreply@blogger.com2