tag:blogger.com,1999:blog-59784450552906653092024-03-05T23:19:45.998-08:00Database and BI Concepts BlogDurga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.comBlogger54125tag:blogger.com,1999:blog-5978445055290665309.post-2282592341654242172012-02-13T04:01:00.000-08:002012-02-13T04:01:10.095-08:00SQL Server : SQL Job Status<div dir="ltr" style="text-align: left;" trbidi="on"><br />
-- SQL Server check job status with all job related information<br />
USE msdb<br />
EXEC dbo.sp_help_job @job_name = N'TestSQLJob' ,<br />
@job_aspect = N'ALL' ;<br />
GO<br />
<br />
-- Check run status of a job - check if job currently running<br />
-- SQL Server Agent - Running = 1 means currently executing<br />
DECLARE @job_id uniqueidentifier<br />
SELECT @job_id=job_id FROM msdb.dbo.sysjobs WHERE name=N'TestSQLJob'<br />
<br />
EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id<br />
GO<br />
<br />
-- SQL Server start job programmatically<br />
EXEC msdb.dbo.sp_start_job N'TestSQLJob' ;<br />
GO<br />
</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-67958392023283274812011-10-08T19:49:00.000-07:002011-10-08T19:49:00.419-07:00SQL Server 2008 - Wide Tables<div dir="ltr" style="text-align: left;" trbidi="on">Recently I navigated through the <a href="http://msdn.microsoft.com/en-us/library/ms143432.aspx">Maximum Capacity Specifications for SQL Server 2008</a><br />
<br />
I found something interesting which says that, <br />
<br />
Max columns per 'non wide' table : 1024<br />
Max columns per 'wide' table : 30,000<br />
<br />
Where non wide tables are regular database tables and wide table is a table which uses <a href="http://msdn.microsoft.com/en-us/library/cc280521.aspx">column sets</a> and <a href="http://msdn.microsoft.com/en-us/library/cc280604.aspx">sparse columns</a>. It still follows the same width restrictions per row i.e. 8019 bytes. So you would typically use it only when most of your columns data in a particular row is NULL.<br />
<br />
Refer to below link for more information.<br />
<br />
<a href="http://msdn.microsoft.com/en-us/library/ms186986.aspx">Special Table Types</a><br />
<br />
Reference: DP ( <a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )<br />
<br />
<br />
</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com1tag:blogger.com,1999:blog-5978445055290665309.post-4106164691220175192011-10-08T07:42:00.000-07:002011-10-08T07:42:57.795-07:00T-SQL Exception Handling<div dir="ltr" style="text-align: left;" trbidi="on">Prior toSQL Server 2005 i.e. in SQL Server 2000 / 7.0, exception handling is a very premitive task with handful of limitations.<br />
SQL Server 2005 architecture is now started using Visual Studio platform and based on Microsoft .NET framework.<br />
One of the features support is that now SQL Server can leverage on TRY..CATCH functionality provided by the .NET framework.<br />
<br />
Following are the inbuilt methods supported by this TRY..CATCH functionality and should be used within CATCH block.<br />
<br />
ERROR_NUMBER()<br />
,ERROR_SEVERITY()<br />
,ERROR_STATE()<br />
,ERROR_PROCEDURE()<br />
,ERROR_LINE()<br />
,ERROR_MESSAGE() <br />
<br />
<br />
For complete information refer to this good<a href="http://beyondrelational.com/blogs/nakul/archive/2011/02/24/exception-handling-in-t-sql-try-catch-underappreciated-features-of-microsoft-sql-server.aspx"> article</a> written by Nakul Vachhrajani.<br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-88576094628022348232011-10-06T19:32:00.000-07:002011-10-06T19:32:00.227-07:00SQL Server 2008 - Table Valued Parameters<div dir="ltr" style="text-align: left;" trbidi="on">SQL Server 2008 introduced a nice feature called Table Valued Parameters created using user defined table type.<br />
<br />
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. There are tons of benefits, and here is the list provided by BOL:<br />
<ul><li>Do not acquire locks for the initial population of data from a client </li>
<li>Provide a simple programming model </li>
<li>Enable you to include complex business logic in a single routine </li>
<li>Reduce round trips to the server </li>
<li>Can have a table structure of different cardinality </li>
<li>Table valued parameters are strongly typed </li>
<li>Enable the client to specify sort order and unique ke</li>
</ul><br />
For more information refer to this <a href="http://beyondrelational.com/blogs/nakul/archive/2011/02/21/table-valued-parameters-to-a-table-valued-function-underappreciated-features-of-microsoft-sql-server.aspx">link</a>.<br />
<br />
Reference: DP ( <a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-45786482974878533102011-10-06T19:20:00.000-07:002011-10-06T19:20:00.404-07:00SQL Server 2008 R2 : T-SQL Data Type Precedence<div dir="ltr" style="text-align: left;" trbidi="on">When an operator combines two different data types, the data type precedence rules says that the data type with lowest precedence converts to the data type with highest precedence. If the conversion is not supported implicit conversion, an error is returned. If both the operands are of same data type, then the result is of same data type.<br />
<br />
SQL Server uses the following order of data type precedence.<br />
<br />
1. User defined data types (highest)<br />
2. sql_variant<br />
3. xml<br />
4. datatimeoffset<br />
5. datetime2<br />
6. datetime<br />
7. smalldatetime<br />
8. date<br />
9. time<br />
10. float<br />
11. real<br />
12. decimal<br />
14. money<br />
15. small money<br />
16. bigint<br />
17. int<br />
18. smallint<br />
19. tinyint<br />
20. bit<br />
21. ntext<br />
22. text<br />
23. image<br />
24. timestamp<br />
25. uniqueidentifier<br />
26. nvarchar (including nvarchar(max))<br />
27. nchar<br />
28. varchar (including varchar(max))<br />
29. char<br />
30. varbinary (including varbinary(max))<br />
31. binary (lowest)<br />
<br />
For complete details refer to the msdn link below:<br />
<span style="font-family: "Calibri", "sans-serif"; font-size: 11pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US;"><a href="http://msdn.microsoft.com/en-us/library/ms190309.aspx">http://msdn.microsoft.com/en-us/library/ms190309.aspx</a> </span><br />
<br />
<span style="font-family: "Calibri", "sans-serif"; font-size: 11pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US;">Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</span></div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-71175341486513597382011-06-23T10:28:00.000-07:002011-06-23T10:28:52.101-07:00Some Good SQL-Related YouTube Videos<div dir="ltr" style="text-align: left;" trbidi="on">You may find below link useful to learn about SQL Server Best Practises.<br />
<br />
<a href="http://www.helpwithsql.com/2011/06/some-good-sql-related-youtube-videos/">Some Good SQL-Related YouTube Videos</a><br />
<br />
Reference: <a href="http://www.helpwithsql.com/">http://www.helpwithsql.com/</a></div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-52749860513768608702011-06-22T10:17:00.000-07:002011-06-22T10:17:15.436-07:00SQL SERVER - T-SQL New Line Character<div dir="ltr" style="text-align: left;" trbidi="on">To print information like multi line address, we have to use new line character. Different operating systems have different ways to understand this new line character. Mac supports only '\r' where as Linux supports '\n' and the most popular OS Windows needs both of these characters to understand the new line which is '\r\n'.<br />
<br />
Few synonyms for line feed (LF) and carriage return(CR) are listed as below<br />
<br />
<div style="text-align: justify;">Line Feed – LF – \n – 0x0a – 10 (decimal)</div><div style="text-align: justify;">Carriage Return – CR – \r – 0x0D – 13 (decimal)</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">E.g.</div><div style="text-align: justify;"><strong><u>Without new line character</u></strong></div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">DECLARE @NewLineCharacter CHAR(2) = CHAR(13) + CHAR(10)</div><div style="text-align: justify;">PRINT 'Address1 Address2'</div><div style="text-align: justify;">GO</div><div style="text-align: justify;"><strong><u>Result:</u></strong> </div><div style="text-align: justify;">Address1 Address2</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><strong><u>With new line character</u></strong></div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">DECLARE @NewLineCharacter CHAR(2) = CHAR(13) + CHAR(10)</div><div style="text-align: justify;">PRINT 'Address1 ' + NewLineCharacter +'Address2'</div><div style="text-align: justify;">GO</div><div style="text-align: justify;"><strong><u>Result:</u></strong></div><div style="text-align: justify;">Address1</div><div style="text-align: justify;">Address2</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;">Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div><div style="text-align: justify;"><br />
</div><div style="text-align: justify;"><br />
</div><br />
</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-3127395645759004322011-06-13T09:41:00.000-07:002011-06-13T09:41:22.659-07:00Microsoft Web Camps - Hyderabad<div dir="ltr" style="text-align: left;" trbidi="on">Hi All,<br />
<br />
Microsoft Web Camps - Hyderabad will happen on <u><strong>16th June, 2011</strong></u>.<br />
<br />
Please find the details as below.<br />
<br />
<u><strong>Venue:</strong></u><br />
Microsoft R&D India Pvt. Ltd. MPR 1-5, Building 3<br />
Gachibowli Hyderabad Andhra Pradesh 500032<br />
India <br />
<br />
<strong><u>Audience(s):</u></strong> Architect and Pro Dev/Programmer. <br />
<br />
To register, click the <a href="https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032486029&culture=en-IN">link</a> here:<br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-40857168752558624722011-06-10T23:11:00.000-07:002011-06-10T23:11:26.216-07:00Microsoft Tech.Ed on the Road 2011.<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-family: "Calibri", "sans-serif";"><u><strong>' Tech.Ed on The Road'</strong></u> had been bringing the best of Tech.ED sessions, that happened in Bangalore during March 2011, from past one month in Pune, Trivandrum, Hyderabad, Chandigarh and Delhi. Microsoft's efforts to reach out to thousands of developers and infrastructure professionals have received great appreciation. With great pleasure, Microsoft wish to announce that the rich Tech.Ed content will now be available in the following cities on the dates specified.</span><br />
<br />
<span style="font-family: "Calibri", "sans-serif";"> Ahmedabad - 11-June-2001 </span><br />
<span style="font-family: "Calibri", "sans-serif";"> Chennai - 18-June-2011 </span><br />
<span style="font-family: "Calibri", "sans-serif";"> Mumbai - 25-June-2011 </span><br />
<span style="font-family: "Calibri", "sans-serif";"> Kolkata - 2-July-2011 </span><br />
<span style="font-family: "Calibri", "sans-serif";"> Bangalore - 9-July-2011 </span><br />
<span style="font-family: "Calibri", "sans-serif";"></span><br />
<span style="font-family: "Calibri", "sans-serif";">Register at <a href="http://www.communitytechdays.com/">http://www.communitytechdays.com/</a></span> <br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-46160002596108639892011-06-07T10:11:00.000-07:002011-06-07T10:11:29.155-07:00SQL Server: Using JOIN Clause(s) with UPDATE and DELETE<div dir="ltr" style="text-align: left;" trbidi="on">SQL Server JOINs are very useful in data processing.<br />
They are commonly used in SELECT queries but can be used with UPDATE and DELETE statements.<br />
<br />
<u><strong>E.g.</strong></u><br />
<br />
IF OBJECT_ID('TableA') IS NOT NULL THEN<br />
BEGIN<br />
DROP TABLE TableA<br />
END<br />
GO<br />
<br />
IF OBJECT_ID('TableB') IS NOT NULL THEN<br />
BEGIN<br />
DROP TABLE TableB<br />
END<br />
GO<br />
<br />
CREATE TABLE TableA(<br />
ID INT,<br />
Name VARCHAR(100)<br />
)<br />
GO<br />
<br />
CREATE TABLE TableB(<br />
ID INT,<br />
Name VARCHAR(100)<br />
)<br />
GO<br />
<br />
Lets assume that these tables are populated with some records.<br />
<br />
UPDATE ta<br />
SET ta.Name = 'Join clause with Update statement'<br />
FROM TableA ta<br />
INNER JOIN TableB tb ON ta.ID = tb.ID<br />
GO<br />
<br />
DELETE ta<br />
FROM TableA ta<br />
INNER JOIN TableB tb ON ta.ID = tb.ID<br />
GO<br />
<br />
Similar to INNER JOIN, other types of JOIN clauses like LEFT JOIN and RIGHT JOIN can also be used with UPDATE and DELETE statements.<br />
<br />
Please review this article and provide your comments.<br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-44658718189975682702011-06-06T09:43:00.000-07:002011-06-06T09:43:16.610-07:00SQL Server: Checking Existence and to Drop Temp Tables<div dir="ltr" style="text-align: left;" trbidi="on">Temporary tables are useful for storing the data temporarily during the data processing. There are two types of temp tables available in SQL Server namely local temporary tables and global temporary tables. <br />
The name of local temp tables prefix with '#' and global temp tables prefix with that of '##'.<br />
<br />
To check existence of a local temparary table, drop and recreate the same use below approach.<br />
<br />
IF OBJECT_ID('tempdb.dbo.#localTempTable') IS NOT NULL THEN<br />
BEGIN <br />
DROP TABLE #localTempTable<br />
END IF<br />
CREATE TABLE #localTempTable(ID INT NOT NULL)<br />
GO<br />
<br />
IF OBJECT_ID('tempdb.dbo.##globalTempTable') IS NOT NULL THEN<br />
BEGIN <br />
DROP TABLE ##globalTempTable<br />
END IF<br />
CREATE TABLE ##globalTempTable(ID INT NOT NULL)<br />
GO<br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )<br />
</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-940279184688856492011-06-03T09:45:00.000-07:002011-06-03T10:01:40.416-07:00SQL Server : To Retrive Columns of an Object<div dir="ltr" style="text-align: left;" trbidi="on">To find the all the columns of a db objects like table and view we can use below approach using SQL Sever meta data objects like sys.objects and sys.columns.<br />
<br />
<span style="color: blue;">USE</span> AdventureWorks2008<br />
<span style="color: blue;">GO</span><br />
<span style="color: blue;"><span style="color: blue;">SELECT DISTINCT<span style="color: black;"> c</span><span style="color: grey;"><span style="color: grey;">.</span></span><span style="color: black;">name [Column Name]</span><br />
<span style="color: blue;"><span style="color: blue;">FROM <span style="color: green;">sys</span><span style="color: grey;"><span style="color: grey;">.</span></span><span style="color: green;"><span style="color: green;">columns</span></span> c <br />
<span style="color: grey;"><span style="color: grey;">INNER</span></span> <span style="color: grey;"><span style="color: grey;">JOIN</span></span> <span style="color: green;"><span style="color: green;">sys</span></span><span style="color: grey;"><span style="color: grey;">.</span></span><span style="color: green;"><span style="color: green;">objects</span></span> o <span style="color: blue;"><span style="color: blue;">on</span></span> c<span style="color: grey;"><span style="color: grey;">.</span></span><span style="color: magenta;"><span style="color: magenta;">object_id</span></span><span style="color: grey;"><span style="color: grey;">=</span></span>o<span style="color: grey;"><span style="color: grey;">.</span></span><span style="color: magenta;"><span style="color: magenta;">object_id</span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">WHERE <span style="color: magenta;">object_id</span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: red;"><span style="color: red;">'Production.Product'</span></span><span style="color: grey;"><span style="color: grey;">)=</span></span>o<span style="color: grey;"><span style="color: grey;">.</span></span><span style="color: magenta;"><span style="color: magenta;">object_id</span></span> <br />
<span style="color: blue;"><span style="color: blue;">ORDER BY [Column Name] </span></span></span></span></span></span></span></span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: black;">Where the <u>Production.Product</u> is a db table and can be substituted by any other table or view.</span></span></span></span></span></span></span></span></span><br />
<br />
<span style="color: black;">Partial result:<br />
-------------------------------------------------</span><br />
<span style="color: black;"><u>Column Name</u><br />
Class<br />
Color<br />
DaysToManufacture<br />
DiscontinuedDate<br />
FinishedGoodsFlag</span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: black;">Reference : DP</span> (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</span></span></span></span></span></span></span></span><br />
<br />
<span style="color: black;"></span></div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-37138148429451812882011-05-28T00:56:00.000-07:002011-05-28T00:56:56.972-07:00SQL Server : Blackbox XML Method<div dir="ltr" style="text-align: left;" trbidi="on">Often its needed for reporting purpose to summarize the normalized data into groups or list of values. This is also known as row concatenation. This can be best done using some reporting and client side tools which support this functionality directly. Using SQL Server also we can do this in different ways.<br />
<br />
<div style="margin: 0px;"><span style="color: blue;">IF OBJECT_ID<span style="color: black;">(</span>'<span style="color: black;">Products </span>'<span style="color: black;">)</span> <span style="color: #444444;">NOT NULL</span></span></div><div style="margin: 0px;"><span style="color: blue;">DROP TABLE <span style="color: black;">Products</span></span></div><div style="margin: 0px;"><span style="color: blue;"><span style="color: black;">GO</span></span></div><span style="color: blue;"><div style="margin: 0px;"><span style="color: blue;">IF OBJECT_ID<span style="color: black;">(</span><span style="color: black;">'Departments'</span><span style="color: black;">)</span> <span style="color: #444444;">NOT NULL</span></span></div><div style="margin: 0px;"><span style="color: blue;">DROP TABLE <span style="color: black;">Departments</span></span></div><div style="margin: 0px;"><span style="color: blue;"><span style="color: black;">GO</span></span></div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: blue;">CREATE TABLE </span><span style="color: black;">Products</span> <span style="color: black;">(</span></div><div style="margin: 0px;"> <span style="color: black;">PROD_ID</span> <span style="color: blue;">INT PRIMARY KEY</span>,</div><div style="margin: 0px;"> <span style="color: black;">PRODUCT_DESC</span> <span style="color: blue;">VARCHAR</span>(50)<span style="color: black;">)</span>;</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: blue;">CREATE TABLE </span><span style="color: black;">Departments</span> (</div><div style="margin: 0px;"> <span style="color: black;">DEPT_ID</span> <span style="color: blue;">INT PRIMARY KEY</span>,</div><div style="margin: 0px;"> <span style="color: black;">DEPT_TITLE</span> <span style="color: blue;">VARCHAR</span>(50));</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: blue;">CREATE TABLE </span><span style="color: black;">DepartmentProducts</span> <span style="color: black;">(</span></div><div style="margin: 0px;"> <span style="color: black;">DEPT_ID</span> <span style="color: blue;">INT </span></div><div style="margin: 0px;"> <span style="color: blue;">REFERENCES </span><span style="color: black;">Departments (DEPT_ID),</span></div><div style="margin: 0px;"> <span style="color: black;">PROD_ID</span> <span style="color: blue;">INT </span></div><div style="margin: 0px;"> <span style="color: blue;">REFERENCES </span><span style="color: black;">Products (PROD_ID),</span></div><div style="margin: 0px;"> <span style="color: blue;">PRIMARY KEY </span><span style="color: black;">(DEPT_ID,PROD_ID));</span></div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Products</span> <span style="color: blue;">VALUES </span><span style="color: black;">(1,</span> <span style="color: #a31515;">'Book'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Products</span> <span style="color: blue;">VALUES </span><span style="color: black;">(2,</span> <span style="color: #a31515;">'Magazine'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Products</span> <span style="color: blue;">VALUES </span><span style="color: black;">(3,</span> <span style="color: #a31515;">'DVD'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Products</span> <span style="color: blue;">VALUES </span><span style="color: black;">(4,</span> <span style="color: #a31515;">'Video'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Products</span> <span style="color: blue;">VALUES </span><span style="color: black;">(5,</span> <span style="color: #a31515;">'CD'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Products</span> <span style="color: blue;">VALUES </span><span style="color: black;">(6,</span> <span style="color: #a31515;">'Map'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Departments</span> <span style="color: blue;">VALUES </span><span style="color: black;">(1,</span> <span style="color: #a31515;">'Reference'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Departments</span> <span style="color: blue;">VALUES </span><span style="color: black;">(2,</span> <span style="color: #a31515;">'Periodicals'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">Departments</span> <span style="color: blue;">VALUES </span><span style="color: black;">(3,</span> <span style="color: #a31515;">'Entertainment'</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">VALUES </span><span style="color: black;">(1, 1)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">VALUES </span><span style="color: black;">(1, 6)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">VALUES </span><span style="color: black;">(2, 2)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">VALUES </span><span style="color: black;">(3, 3)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">VALUES </span><span style="color: black;">(3, 4)</span>;</div><div style="margin: 0px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">VALUES </span><span style="color: black;">(3, 5)</span>;</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><strong><u><span style="color: black;">The Blackbox XML methods:</span></u></strong></div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: #6aa84f;">--Using CTE</span> </div><div style="margin: 0px;"><span style="color: blue;">WITH</span> <span style="color: black;">cteProdDesc(DEPT_ID,PROD_DESC)</span></div><div style="margin: 0px;"><span style="color: blue;">AS</span></div><div style="margin: 0px;">(</div><div style="margin: 0px;"> <span style="color: blue;">SELECT</span> <span style="color: black;">DEPT_ID,PRODUCT_DESC</span></div><div style="margin: 0px;"> <span style="color: blue;">FROM </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">AS </span><span style="color: black;">DP</span></div><div style="margin: 0px;"> <span style="color: #666666;">INNER</span> <span style="color: grey;">JOIN </span><span style="color: black;">Products</span> <span style="color: blue;">AS </span><span style="color: black;">P</span> <span style="color: grey;">ON </span><span style="color: black;">P.PROD_ID= DP.PROD_ID</span></div><div style="margin: 0px;"> <span style="color: blue;">WHERE </span><span style="color: black;">DP.DEPT_ID = D.DEPT_ID</span></div><div style="margin: 0px;">)</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: green;"><span style="color: blue;">SELECT</span> <span style="color: black;">D.DEPT_ID,</span></span></div><div style="margin: 0px;"><span style="color: black;"> D.DEPT_TITLE,</span><span style="color: magenta;">STUFF</span>((<span style="color: blue;">SELECT</span> <span style="color: red;">','</span> + <span style="color: black;"><span style="background-color: #eeeeee;">cpd</span>.PRODUCT_DESC</span></div><div style="margin: 0px;"><span style="color: green;"><span style="color: blue;">FROM</span> <span style="color: black;">Departments D</span></span></div><div style="margin: 0px;"><span style="color: green;"><span style="color: black;"><span style="color: #666666;">INNER JOIN</span> <span style="color: black;">cteProdDesc cpd ON cpd.DEPT_ID = D.DEPT_ID</span> </span></span></div><div style="margin: 0px;"><span style="color: green;"><span style="color: black;"><span style="color: blue;">ORDER BY</span> <span style="color: black;">PRODUCT_DESC</span> <span style="color: blue;">FOR XMLPATH</span>('')),1,1,'') <span style="color: blue;">AS</span> </span><span style="color: black;">PRODUCT_LIST</span></span></div><div style="margin: 0px;"><span style="color: green;"><span style="color: black;"><span style="color: green;"><span style="color: blue;">FROM</span> <span style="color: black;">Departments D</span></span></span></span></div><div style="margin: 0px;"><span style="color: green;"><span style="color: black;"></span></span><span style="color: blue;">GO</span> </div><div style="margin: 0px;"><span style="color: green;">-- Using correlated subquery </span></div><div style="margin: 0px;"><span style="color: blue;">SELECT </span><span style="color: black;">D.DEPT_ID,</span></div><div style="margin: 0px;"><span style="color: black;"> D.DEPT_TITLE,</span></div><div style="margin: 0px;"> <span style="color: magenta;">STUFF</span>((<span style="color: blue;">SELECT </span><span style="color: #a31515;">',' </span>+ <span style="color: black;">PRODUCT_DESC</span></div><div style="margin: 0px;"> <span style="color: blue;">FROM </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">AS </span><span style="color: black;">DP</span></div><div style="margin: 0px;"> <span style="color: grey;">INNER </span><span style="color: grey;">JOIN </span><span style="color: black;">Products</span> <span style="color: blue;">AS </span><span style="color: black;">P</span> <span style="color: grey;">ON </span><span style="color: black;">P.PROD_ID= DP.PROD_ID</span></div><div style="margin: 0px;"> <span style="color: blue;">WHERE </span><span style="color: black;">DP.DEPT_ID = D.DEPT_ID</span></div><div style="margin: 0px;"> <span style="color: blue;">ORDER BY </span><span style="color: black;">PRODUCT_DESC</span></div><div style="margin: 0px;"> <span style="color: blue;">FOR XML PATH</span>(<span style="color: #a31515;">''</span>)), 1, 1, <span style="color: #a31515;">''</span>) <span style="color: blue;">AS </span><span style="color: black;">PRODUCT_LIST</span></div><div style="margin: 0px;"><span style="color: blue;">FROM </span><span style="color: black;">Departments</span> <span style="color: blue;">AS </span><span style="color: black;">D</span>;</div><div style="margin: 0px;"><span style="color: blue;">GO</span> </div><div style="margin: 0px;"><span style="color: green;">-- Using CROSS APPLY</span></div><div style="margin: 0px;"><span style="color: blue;">SELECT </span><span style="color: black;">D.DEPT_ID,</span></div><div style="margin: 0px;"><span style="color: black;"> D.DEPT_TITLE,</span></div><div style="margin: 0px;"> <span style="color: magenta;">STUFF</span>(<span style="color: black;">P.PROD_LIST, 1, 1,</span> <span style="color: #a31515;">''</span>) <span style="color: blue;">AS </span><span style="color: black;">PRODUCT_LIST</span></div><div style="margin: 0px;"><span style="color: blue;">FROM </span>Departments <span style="color: blue;">AS </span>D</div><div style="margin: 0px;"><span style="color: grey;">CROSS APPLY</span> (<span style="color: blue;">SELECT </span><span style="color: #a31515;">',' </span>+ <span style="color: black;">PRODUCT_DESC</span></div><div style="margin: 0px;"> <span style="color: blue;">FROM </span><span style="color: black;">DepartmentProducts</span> <span style="color: blue;">AS </span><span style="color: black;">DP</span></div><div style="margin: 0px;"> <span style="color: grey;">INNER </span><span style="color: grey;">JOIN </span><span style="color: black;">Products</span> <span style="color: blue;">AS </span>P <span style="color: grey;">ON </span><span style="color: black;">P.PROD_ID= DP.PROD_ID</span></div><div style="margin: 0px;"> <span style="color: blue;">WHERE </span><span style="color: black;">DP.DEPT_ID = D.DEPT_ID</span></div><div style="margin: 0px;"> <span style="color: blue;">ORDER BY </span><span style="color: black;">PRODUCT_DESC</span></div><div style="margin: 0px;"> <span style="color: blue;">FOR XML PATH</span>(<span style="color: #a31515;">''</span>)) <span style="color: blue;">AS </span><span style="color: black;">P</span> <span style="color: black;">(</span><span style="color: black;">PROD_LIST</span><span style="color: black;">)</span>;</div><div style="margin: 0px;"><span style="color: blue;">GO</span></div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: green;">-- Results </span> </div><div style="margin: 0px;"><span style="color: black;">DEPT_ID DEPT_TITLE PRODUCT_LIST</span></div><div style="margin: 0px;"><span><span style="color: black;">-------------- ---------------- ------------</span></span></div><div style="margin: 0px;"><span style="color: black;">1 Reference Book,Map</span></div><div style="margin: 0px;"><span style="color: black;">2 Periodicals Magazine</span></div><div style="margin: 0px;"><span style="color: black;">3 Entertainment CD,DVD,Video</span></div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><span style="color: black;">This method is often called blackbox XML method and here is the explanation for this effect using FOR XML clause. Normally the PATH clause use with input string creates the wrapper element in result set. However the PATH clause used with empty string gives result without creating wrapper element. And since the content is retrieved as text, the concatenation effect is achieved.</span></div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;">Resources:</div><div style="margin: 0px;"><a href="http://www.projectdmx.com/tsql/rowconcatenate.aspx">http://www.projectdmx.com/tsql/rowconcatenate.aspx</a></div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;">Reference: DP(<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><br />
</div><div style="margin: 0px;"><br />
</div></span></div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-37551134143870241242011-05-19T10:15:00.000-07:002011-05-19T10:15:05.349-07:00SQL Server : SQL code Formatter Tools<div dir="ltr" style="text-align: left;" trbidi="on">Often while debugging the SQL code written another developer which is not formatted, formatting will help in analysing and debugging the code effectively. Also writting the SQL code with proper formatting improves the readability and is a best practise.<br />
<br />
I found couple of online code formatting tools which formats SQL code very good and can be handy. <br />
<br />
Tool 1: <a href="http://www.dpriver.com/pp/sqlformat.htm">Instant SQL Formatter</a><br />
Tool 2: <a href="http://www.sqlinform.com/">SQLinForm</a><br />
<br />
You may try the above tools next time when you need to format your SQL code.<br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com1tag:blogger.com,1999:blog-5978445055290665309.post-16850307944402114952011-04-30T10:32:00.000-07:002011-04-30T10:32:23.736-07:00SQL Server: Maintaining Integrity during Transactions<div dir="ltr" style="text-align: left;" trbidi="on">Data integrity is the most important aspect of anything and everything we do in database.<br />
<br />
We developers often tend to ignore couple of things, or take things for granted. One of them is we forgot the Microsoft left it to the developer to decide whether to abort a transaction automatically when there is an error.<br />
<br />
For detailed information, go through this <a href="http://beyondrelational.com/blogs/nakul/archive/2011/04/07/t-sql-worst-practices-a-compilation-from-jacob-s-session-at-tech-ed-2011-part-03-practices-that-contribute-to-execution-errors.aspx">link</a>.<br />
<br />
Reference : DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-80980972684071272662011-04-27T07:15:00.000-07:002011-04-27T07:15:01.307-07:00SQL Server: Difference between GetDate() and GetUTCDate()<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-family: Arial, Helvetica, sans-serif;">The difference between GETDATE() and GETUTCDATE() is time zone number of the SQL Server machine.</span><br />
<br />
<span style="font-family: Arial;">E.g.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">DECLARE @local_time DATETIME;<br />
DECLARE @gmt_time DATETIME;<br />
SET @local_time = GETDATE();<br />
SET @gmt_time = GETUTCDATE();<br />
SELECT 'Server local time: '<br />
+ CONVERT(VARCHAR(40),@local_time);<br />
SELECT 'Server GMT time: '<br />
+ CONVERT(VARCHAR(40),@gmt_time);<br />
SELECT 'Server time zone: '<br />
+ CONVERT(VARCHAR(40),<br />
DATEDIFF(hour,@gmt_time,@local_time));<br />
GO</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong><u>Output:</u></strong></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Server local time: Apr 26 2011 09:47PM</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Server GMT time: Apr 26 2011 04:17PM</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Server time zone: 5</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">GETDATE = Returns the day of the month for the specified date according to local time.<br />
<br />
GETUTCDATE() = Returns the day (date) of the month in the specified date according to universal time.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><span style="font-family: Arial, Helvetica, sans-serif;">Reference: DP(</span><a href="http://dptechnicalblog.blogspot.com/"><span style="font-family: Arial, Helvetica, sans-serif;">http://dptechnicalblog.blogspot.com</span></a><span style="font-family: Arial, Helvetica, sans-serif;"> )</span></div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-84747349750896641872011-04-25T09:51:00.000-07:002011-04-25T09:51:12.784-07:00SQL Server 2008 : Types of T-SQL Functions<div dir="ltr" style="text-align: left;" trbidi="on">There are rich set of different types built-in T-SQL functions available in SQL Server 2008 which can be broadly categorized as below.<br />
<div style="text-align: left;"><strong><u>Aggregate functions :</u></strong> Aggregate functions are applied to a group of data values from a column and will always return single value.</div><ul style="text-align: left;"><li><div style="text-align: left;">AVG</div></li>
<li><div style="text-align: left;">COUNT</div></li>
<li><div style="text-align: left;">COUNT_BIG</div></li>
<li><div style="text-align: left;">MIN</div></li>
<li><div style="text-align: left;">MAX</div></li>
<li><div style="text-align: left;">SUM</div></li>
</ul><div style="text-align: left;"><strong><u>Scalare functions:</u></strong> Scalare functions are used in the construction of the scalar expressions and they can be operated on one or more columns. These functions can be further categorized as below.</div><ul><li><div style="text-align: left;">Numeric functions</div></li>
<li><div style="text-align: left;">Date functions</div></li>
<li><div style="text-align: left;">String functions</div></li>
<li><div style="text-align: left;">System functions</div></li>
<li><div style="text-align: left;">Metadata functions</div></li>
</ul><div style="text-align: left;"> For complete information, go through the <a href="http://searchsqlserver.techtarget.com/feature/SQL-Server-2008-function-types-in-T-SQL">link</a> here.</div><div style="text-align: left;"><br />
</div><div style="text-align: left;">Reference: DP(<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div><br />
</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-50414723670149046962011-04-24T07:29:00.000-07:002011-04-24T07:29:21.744-07:00AdventureWork Data Dictionary<div dir="ltr" style="text-align: left;" trbidi="on">Adventure Works is a sample database which is useful for learning the SQL Server concepts, quick tests and demonstrating our thoughts by using scripts. This database need to be downloaded and seperately installed.<br />
<br />
While the AdventureWorks family of databases is easily available from CodePlex, there is no well-known place where we can go to for lookup to the description of the schema and the tables.<br />
<br />
A data dictionary is a centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format. Data dictionaries are commonly used to circulate copies of the database schema to vendors and technology partners, and once a product is released, this may be made available to the end customers depending upon the need (eg. to allow for customization or study).<br />
<br />
You can get the data dictionary for AdventureWorks sample databases at: <a href="http://technet.microsoft.com/en-us/library/ms124438(SQL.100).aspx" title="http://technet.microsoft.com/en-us/library/ms124438(SQL.100).aspx">http://technet.microsoft.com/en-us/library/ms124438(SQL.100).aspx</a><br />
<br />
Reference : DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-2784720937173989552011-04-23T06:37:00.000-07:002011-04-23T06:37:45.200-07:003 Different Methods to Create Primary Keys<div dir="ltr" style="text-align: left;" trbidi="on">Primary key is unique identifier for each record in the database table and creates clustered index on the column on which its defined.<br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: green;"><span style="color: green;">--Cleanup</span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">IF</span></span> <span style="color: grey;"><span style="color: grey;">EXISTS</span></span><span style="color: blue;"><span style="color: blue;"> </span></span><span style="color: grey;"><span style="color: grey;">(</span></span> <span style="color: blue;"><span style="color: blue;">SELECT</span></span> <span style="color: magenta;"><span style="color: magenta;">OBJECT_ID</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: red;"><span style="color: red;">'constraintTable'</span></span><span style="color: grey;"><span style="color: grey;">))</span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">DROP TABLE constraintTable <br />
<span style="color: blue;"><span style="color: blue;">GO</span></span></span></span></span></span><br />
<br />
<span style="color: blue;"><span style="color: blue;">CREATE TABLE constraintTable <br />
<span style="color: grey;"><span style="color: grey;">(</span></span>ID <span style="color: blue;"><span style="color: blue;">INT</span></span> <span style="color: blue;"><span style="color: blue;">CONSTRAINT</span></span> ct_ID <span style="color: blue;"><span style="color: blue;">PRIMARY</span></span> <span style="color: blue;"><span style="color: blue;">KEY</span></span><span style="color: grey;"><span style="color: grey;">,</span></span> Col2 <span style="color: blue;"><span style="color: blue;">VARCHAR</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>20<span style="color: grey;"><span style="color: grey;">))</span></span></span></span><br />
<span style="color: blue;">GO</span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: green;"><span style="color: green;">--Cleanup</span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">IF</span></span> <span style="color: grey;"><span style="color: grey;">EXISTS</span></span><span style="color: blue;"><span style="color: blue;"> </span></span><span style="color: grey;"><span style="color: grey;">(</span></span> <span style="color: blue;"><span style="color: blue;">SELECT</span></span> <span style="color: magenta;"><span style="color: magenta;">OBJECT_ID</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: red;"><span style="color: red;">'constraintTable'</span></span><span style="color: grey;"><span style="color: grey;">))</span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">DROP TABLE constraintTable <br />
<span style="color: blue;"><span style="color: blue;">GO</span></span></span></span></span></span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: green;"><span style="color: green;">--Primary Key upon table creating Method2</span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">CREATE TABLE constraintTable <br />
<span style="color: grey;"><span style="color: grey;">(</span></span>ID <span style="color: blue;"><span style="color: blue;">INT</span></span><span style="color: grey;"><span style="color: grey;">,</span></span> Col2 <span style="color: blue;"><span style="color: blue;">VARCHAR</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>20<span style="color: grey;"><span style="color: grey;">)</span></span> <span style="color: blue;"><span style="color: blue;">CONSTRAINT</span></span> ct_ID <span style="color: blue;"><span style="color: blue;">PRIMARY</span></span> <span style="color: blue;"><span style="color: blue;">KEY</span></span><span style="color: grey;"><span style="color: grey;">)</span></span></span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">GO</span></span></span></span></span></span></span></span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: green;"><span style="color: green;">--Cleanup</span></span></span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">IF</span></span> <span style="color: grey;"><span style="color: grey;">EXISTS(</span></span><span style="color: blue;"><span style="color: blue;">SELECT</span></span> <span style="color: magenta;"><span style="color: magenta;">OBJECT_ID</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: red;"><span style="color: red;">'constraintTable'</span></span><span style="color: grey;"><span style="color: grey;">))</span></span></span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">DROP TABLE constraintTable <br />
<span style="color: blue;"><span style="color: blue;">GO</span></span></span></span></span></span></span></span></span></span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: green;"><span style="color: green;">--Primary Key upon table creating Method3</span></span></span></span></span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">CREATE TABLE constraintTable <br />
<span style="color: grey;"><span style="color: grey;">(</span></span>ID <span style="color: blue;"><span style="color: blue;">INT</span></span><span style="color: grey;"><span style="color: grey;">,</span></span> Col2 <span style="color: blue;"><span style="color: blue;">VARCHAR</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>20<span style="color: grey;"><span style="color: grey;">))</span></span></span></span></span></span></span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">GO</span></span></span></span></span></span></span></span></span></span></span></span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: green;"><span style="color: green;">--Primay key doesn't allow NULL values</span></span></span></span></span></span></span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">ALTER TABLE constraintTable <br />
<span style="color: blue;"><span style="color: blue;">ALTER</span></span> <span style="color: blue;"><span style="color: blue;">COLUMN</span></span> ID <span style="color: blue;"><span style="color: blue;">INT</span></span> <span style="color: grey;"><span style="color: grey;">NOT</span></span> <span style="color: grey;"><span style="color: grey;">NULL</span></span></span></span></span></span></span></span></span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;">GO</span></span></span></span></span></span></span></span></span></span></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><br />
ALTER TABLE constraintTable <br />
<span style="color: blue;"><span style="color: blue;">ADD</span></span> <span style="color: blue;"><span style="color: blue;">CONSTRAINT</span></span> Ct_ID <span style="color: blue;"><span style="color: blue;">PRIMARY</span></span> <span style="color: blue;"><span style="color: blue;">KEY </span></span><span style="color: grey;"><span style="color: grey;">(</span></span>ID<span style="color: grey;"><span style="color: grey;">)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: blue; font-size: small;">GO</span></span></span></span> </span></span></span></span></span></span></span></span></span></span></span></span><br />
<br />
<span style="color: black;">Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</span></div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-24264536254987779642011-04-23T04:38:00.000-07:002011-04-23T04:38:48.911-07:00SSIS - Types of Transformations<div dir="ltr" style="text-align: left;" trbidi="on">SSIS transformation tasks can use memory or buffer in different ways. The way transformations use memory can impact the performance of your SSIS package dramatically. Transformations buffer usage can be categorised as below.<br />
<ul style="text-align: left;"><li>Non blocking transformations</li>
<li>Partially blocking transformations</li>
<li>Full Blocking transformations</li>
</ul>In general if you can avoid using fully blocking and partial blocking transformations, your SSIS package will perform better. The transfromations can be classified as below<br />
<br />
Non blocking transformations<br />
<ul style="text-align: left;"><li>Audit</li>
<li>Cache Transform</li>
<li>Character Map</li>
<li>Conditional Split</li>
<li>Copy Column</li>
<li>Data Conversion</li>
<li>Devired Column</li>
<li>Export Column</li>
<li>Import Column</li>
<li>Lookup</li>
<li>Multicast</li>
<li>OLE DB Command</li>
<li>Percentage Sampling</li>
<li>Script Component</li>
<li>Slowly Changing Dimesion</li>
</ul><br />
Partial blocking transformations<br />
<ul style="text-align: left;"><li> Data Mining</li>
<li>Merge</li>
<li>Merge Join</li>
<li>Pivot </li>
<li>Unpivot</li>
<li>Term Lookup</li>
</ul><br />
Fully Blocking Transformations<br />
<ul style="text-align: left;"><li> Aggregate</li>
<li>Fuzzy grouping</li>
<li>Fuzzy lookup</li>
<li>Row Sampling</li>
<li>Sort</li>
<li>Term Extraction</li>
</ul><br />
If you clearly observe Sort is a fully blocking transformation, so its better to sort your data using the SQL command in OLE DB Source instead of using Sort transformation. Merge tranform requires Sort but not Union All, so use Union All wherever possible.<br />
<br />
Hope this post helps in analysing the performance bottle necks in your SSIS package and also while developing new SSIS package for performance tuning.<br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )<br />
<br />
<br />
<br />
</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-33391640747527793892011-03-26T14:30:00.000-07:002011-03-26T14:30:33.977-07:00SSIS Data Flow Scripting<div dir="ltr" style="text-align: left;" trbidi="on">I am very much excited to post my first BI article on my blog.<br />
<br />
Microsoft SSIS is a powerful ETL technology to work on both OLTP and OLAP processes with its rich set of API components like control flow components, data flow components like various types of sources, destinations and transformation.<br />
<br />
Using Data flow scripting transformation, we can use .NET code to customize the functionality of SSIS.<br />
To know more about this transformation one should have knowledge of both SSIS and .NET.<br />
<br />
For more information, go through this <a href="http://channel9.msdn.com/Blogs/adebruyn/SQL-Server-Day-SSIS-Data-Flow-Scripting">video</a> posted on Channel9 web site. For starters, <a href="http://channel9.msdn.com/browse">Channel9</a> is a microsoft maintained web site with lots of videos, blogs, shows and series etc.<br />
<br />
I hope this information is useful to the relevant audience.<br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-84093214665372031362011-03-21T11:31:00.000-07:002011-03-21T11:33:03.446-07:00SQL Server: Difference between Primary and Foreign Keys<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<span style="font-family: "Courier New", Courier, monospace;"><strong><u>Primary Keys:</u></strong><br />
Uniquely identify a row in a table with one or more columns<br />
Column values cannot be null<br />
There can only be one primary key on a table<br />
Candidate for either a clustered or non-clustered index to improve performance <br />
Additional information - Finding primary keys and missing primary keys in SQL Server</span><br />
<span style="font-family: "Courier New", Courier, monospace;"><strong><u>Foreign Keys:</u></strong> <br />
They physically define the relationship between tables by preventing data integrity issues in your database (e.g. The database prevents line items from being created without an existing order header).<br />
They logically document the <span style="font-family: "Courier New", Courier, monospace;">relationships</span> between tables by showing how all data relates to each other. To someone new to your organization, this allows him/her to get a good understanding of how the business works (e.g. Every order taken must have a valid customer assigned).<br />
Foreign Keys are native to SQL Server and are designed to prevent data integrity issues. Business logic developers should not be in the business of verifying table relationships. <br />
If defined and indexed correctly, they can be leveraged by the SQL Server query engine to generate extremely efficient query plans. </span><br />
<span style="font-family: "Courier New", Courier, monospace;">Reference: DP(</span><a href="http://dptechnicalblog.blogspot.com/"><span style="font-family: "Courier New", Courier, monospace;">http://dptechnicalblog.blogspot.com</span></a><span style="font-family: "Courier New", Courier, monospace;"> )</span></div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-91650182442545819392011-03-14T16:16:00.000-07:002011-03-14T16:16:57.721-07:00Extracting Only Numbers from a String<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-family: "Courier New", Courier, monospace;">While working on projects, sometimes we may need to exatract only numbers from a string.</span><br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">The regular approach is to run a while loop on given string to check each and every character and extract it to get the result.</span><br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">Here is alternative approach I read in another article,</span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="font-family: "Courier New", Courier, monospace;">Declare</span></span></span><span style="font-family: "Courier New", Courier, monospace;"> @str <span style="color: blue;"><span style="color: blue;">varchar</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>100<span style="color: grey;"><span style="color: grey;">),</span></span>@result_num <span style="color: blue;"><span style="color: blue;">varchar</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>100<span style="color: grey;"><span style="color: grey;">)</span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="font-family: "Courier New", Courier, monospace;">set</span></span></span><span style="font-family: "Courier New", Courier, monospace;"> @str<span style="color: grey;"><span style="color: grey;">=</span></span><span style="color: red;"><span style="color: red;">'zr3jgc124785ce64'</span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="font-family: "Courier New", Courier, monospace;">set</span></span></span><span style="font-family: "Courier New", Courier, monospace;"> @result_num<span style="color: grey;"><span style="color: grey;">=</span></span><span style="color: red;"><span style="color: red;">''</span></span></span><br />
<span style="color: blue;"><span style="color: blue; font-family: "Courier New", Courier, monospace;">select </span></span><span style="font-family: "Courier New", Courier, monospace;">@result_num </span><span style="font-family: "Courier New", Courier, monospace;"><span style="color: grey;"><span style="color: grey;">= </span></span>@result_num<span style="color: grey;"><span style="color: grey;">+</span></span></span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="color: blue;"><span style="color: blue;">case</span></span> <span style="color: blue;"><span style="color: blue;">when</span></span> number <span style="color: grey;"><span style="color: grey;">like</span></span> <span style="color: red;"><span style="color: red;">'[0-9]'</span></span> <span style="color: blue;"><span style="color: blue;">then</span></span> number <span style="color: blue;"><span style="color: blue;">else</span></span> <span style="color: red;"><span style="color: red;">''</span></span> <span style="color: blue;"><span style="color: blue;">end</span></span> <span style="color: blue;"><span style="color: blue;">from</span></span> </span><br />
<span style="color: blue;"><span style="color: blue;"></span></span><span style="color: grey;"><span style="color: grey;"><span style="font-family: "Courier New", Courier, monospace;">(</span></span></span><span style="font-family: "Courier New", Courier, monospace;"><span style="color: blue;"><span style="color: blue;">select</span></span> <span style="color: magenta;"><span style="color: magenta;">substring</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>@str<span style="color: grey;"><span style="color: grey;">,</span></span>number<span style="color: grey;"><span style="color: grey;">,</span></span>1<span style="color: grey;"><span style="color: grey;">)</span></span> <span style="color: blue;"><span style="color: blue;">as</span></span> number <span style="color: blue;"><span style="color: blue;">from</span></span> </span><br />
<span style="color: blue;"><span style="color: blue;"></span></span><span style="color: grey;"><span style="color: grey;"><span style="font-family: "Courier New", Courier, monospace;">(</span></span></span><span style="font-family: "Courier New", Courier, monospace;"><span style="color: blue;"><span style="color: blue;">select</span></span> number <span style="color: blue;"><span style="color: blue;">from</span></span> <span style="color: blue;"><span style="color: blue;">master</span></span><span style="color: grey;"><span style="color: grey;">..</span></span></span><span style="font-family: "Courier New", Courier, monospace;">spt_values </span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="color: blue;"><span style="color: blue;">where</span></span> <span style="color: blue;"><span style="color: blue;">type</span></span><span style="color: grey;"><span style="color: grey;">=</span></span><span style="color: red;"><span style="color: red;">'p'</span></span> <span style="color: grey;"><span style="color: grey;">and</span></span> number <span style="color: grey;"><span style="color: grey;">between</span></span> 1 <span style="color: grey;"><span style="color: grey;">and</span></span> <span style="color: magenta;"><span style="color: magenta;">len</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>@str<span style="color: grey;"><span style="color: grey;">)</span></span></span><span style="font-family: "Courier New", Courier, monospace;"><span style="color: grey;"><span style="color: grey;">)</span></span> <span style="color: blue;"><span style="color: blue;">as</span></span></span><span style="font-family: "Courier New", Courier, monospace;"> t</span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="color: grey;"><span style="color: grey;">)</span></span> <span style="color: blue;"><span style="color: blue;">as</span></span> t </span><br />
<span style="color: blue;"><span style="color: blue;"><span style="font-family: "Courier New", Courier, monospace;">select</span></span></span><span style="font-family: "Courier New", Courier, monospace;"> @result_num <span style="color: blue;"><span style="color: blue;">as</span></span> [only numbers] </span><br />
<span style="color: blue;"><span style="color: blue; font-family: "Courier New", Courier, monospace;">go</span></span><br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">Result:</span><br />
<span style="font-family: "Courier New", Courier, monospace;">312478564</span><br />
<br />
Reference : DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com1tag:blogger.com,1999:blog-5978445055290665309.post-45603984092511458172011-03-08T22:30:00.000-08:002011-03-08T22:31:34.658-08:00SQL SERVER: User Defined Data Types<div dir="ltr" style="text-align: left;" trbidi="on">To create user defined data types, use either of the 2 methods.<br />
<br />
1. Using T-SQL syntax<br />
<br />
CREATE TYPE [Schema].[Datatype Name] FROM Datatype [NULL / NOT NULL]<br />
<br />
E.g. <br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="font-size: small;">USE <span style="color: black;">[AdventureWorks2008]</span></span><br />
<span style="color: blue;"><span style="color: blue;">GO</span></span></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: green;"><span style="color: green;">/****** Object: UserDefinedDataType [dbo].[Test] ******/</span></span></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue;"><span style="color: blue;">CREATE</span></span> <span style="color: blue;"><span style="color: blue;">TYPE </span></span> [dbo]<span style="color: grey;"><span style="color: grey;">.</span></span>[Test] <span style="color: blue;"><span style="color: blue;">FROM</span></span> [int] </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: grey;"><span style="color: grey;">NOT</span></span> <span style="color: grey;"><span style="color: grey;">NULL</span></span></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: small;">GO<br />
<br />
<span style="color: black; font-family: inherit;">2. Using SSMS also we can acheive this.</span><br />
<br />
<span style="color: black;">In database expand the Programmability section, the Type section and the User Defined Data Type section</span><br />
<span style="color: black;">Right click and select New User-defined Data Type and a pop up appears as below</span><br />
<span style="color: black;">Fill appropriate data for Schema, Name and Data type fields and save it.</span><br />
</span></span></span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGGTsZeA2wzV5ZRBZf2S0cY92w6Ez2A_gYFxXHOd0GjWH6LGAYmDjiUCCT0FkqXQoajoK-XltrUgddkhA7T87ZDFcakE4cGKvYJVw5v9OpDhxJSSJR_jjXfTRWSxqQPoYLd1CAdtQ0zEJ5/s1600/SS2008UserDefinedDatatype.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="286" q6="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGGTsZeA2wzV5ZRBZf2S0cY92w6Ez2A_gYFxXHOd0GjWH6LGAYmDjiUCCT0FkqXQoajoK-XltrUgddkhA7T87ZDFcakE4cGKvYJVw5v9OpDhxJSSJR_jjXfTRWSxqQPoYLd1CAdtQ0zEJ5/s320/SS2008UserDefinedDatatype.png" width="320" /></a></div><br />
<span style="color: black;">Reference : DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</span></div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0tag:blogger.com,1999:blog-5978445055290665309.post-25296355595607622922011-03-08T06:20:00.000-08:002011-03-08T06:20:07.511-08:00SQL Server : Challenges of Working with Nothing<div dir="ltr" style="text-align: left;" trbidi="on">If your data contains NULL values there will be deviations in result set, so the developer should be very careful while dealing with NULL values data.<br />
<br />
Usually when comparing a column's data to a value or comparing column's data it results either in TRUE or FALSE, but comparision with NULL returns UNKNOWN which in somemeans same as FALSE.<br />
We don't find the difference with '=' operator but with remaining operators like '<>' , the results will vary significantly as shown below.<br />
<br />
<span style="color: blue;"><span style="color: blue;">USE</span></span> AdventureWorks2008<br />
<span style="color: blue;"><span style="color: blue;">GO</span></span><br />
<span style="color: green;"><span style="color: green;">--Returns 504 rows</span></span><br />
<span style="color: blue;"><span style="color: blue;">SELECT</span></span> ProductID<span style="color: grey;"><span style="color: grey;">,</span></span>Name<br />
<span style="color: blue;"><span style="color: blue;">FROM</span></span> Production<span style="color: grey;"><span style="color: grey;">.</span></span>Product<br />
<span style="color: blue;"><span style="color: blue;">GO</span></span><br />
<span style="color: green;"><span style="color: green;">--Returns 248 rows</span></span><br />
<span style="color: blue;"><span style="color: blue;">SELECT</span></span> ProductID<span style="color: grey;"><span style="color: grey;">,</span></span>Name<br />
<span style="color: blue;"><span style="color: blue;">FROM</span></span> Production<span style="color: grey;"><span style="color: grey;">.</span></span>Product<br />
<span style="color: blue;"><span style="color: blue;">WHERE</span></span> Color <span style="color: grey;"><span style="color: grey;">IS</span></span> <span style="color: grey;"><span style="color: grey;">NULL</span></span><br />
<span style="color: blue;"><span style="color: blue;">GO</span></span><br />
<span style="color: green;"><span style="color: green;">--Expected 478 rows but returns 230 rows</span></span><br />
<span style="color: blue;"><span style="color: blue;">SELECT</span></span> ProductID<span style="color: grey;"><span style="color: grey;">,</span></span>Name<span style="color: grey;"><span style="color: grey;">,</span></span>Style<span style="color: grey;"><span style="color: grey;">,</span></span>Size<span style="color: grey;"><span style="color: grey;">,</span></span>Color<br />
<span style="color: blue;"><span style="color: blue;">FROM</span></span> Production<span style="color: grey;"><span style="color: grey;">.</span></span>Product<br />
<span style="color: blue;"><span style="color: blue;">WHERE</span></span> Color <span style="color: grey;"><span style="color: grey;"><></span></span> <span style="color: red;"><span style="color: red;">'Blue'</span></span><br />
<span style="color: blue;"><span style="color: blue;">GO</span></span><br />
<span style="color: green;"><span style="color: green;">--Returns 478 rows</span></span><br />
<span style="color: blue;"><span style="color: blue;">SELECT</span></span> ProductID<span style="color: grey;"><span style="color: grey;">,</span></span>Name<span style="color: grey;"><span style="color: grey;">,</span></span>Style<span style="color: grey;"><span style="color: grey;">,</span></span>Size<span style="color: grey;"><span style="color: grey;">,</span></span>Color<br />
<span style="color: blue;"><span style="color: blue;">FROM</span></span> Production<span style="color: grey;"><span style="color: grey;">.</span></span>Product<br />
<span style="color: blue;"><span style="color: blue;">WHERE</span></span> Color <span style="color: grey;"><span style="color: grey;"><></span></span> <span style="color: red;"><span style="color: red;">'Blue'</span></span> <span style="color: grey;"><span style="color: grey;">OR</span></span> Color <span style="color: grey;"><span style="color: grey;">IS</span></span> <span style="color: grey;"><span style="color: grey;">NULL</span></span><br />
<span style="color: blue;"><span style="color: blue;">GO</span></span><br />
<br />
We can observe the significance in result set due to NULL in last 2 query results.<br />
<br />
Reference: DP (<a href="http://dptechnicalblog.blogspot.com/">http://dptechnicalblog.blogspot.com/</a> )</div>Durga Prasadhttp://www.blogger.com/profile/10795027256719348207noreply@blogger.com0