SQL Tips by Namwar Rizvi

August 30, 2009

How to Query Active Directory (AD) from SQL Server

There can be a scenario where you will want to query Active Directory, directly from your stored procedure. This can be achieved by creating a linked server to your target Active Directory and then querying it through OPENQUERY functionality. Following is an example to achieve this:

Step 1: Add Linked Server for Active Directory

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Step 2: Query the Active Directory

SELECT * FROM OpenQuery
(
ADSI,   --Name of the linked server for Active directory, created in step 1
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn   --Fields you want to retreive
FROM ''LDAP://DC=TestDomain''    --Name of your Active Directory Domain
where objectClass = ''User'''         --Type of objects you want to query e.g. User, Person etc...
)

NOTE: The order in which columns will return will be exactly reversed of what you mentioned in your query. For example, in above query we have mentioned 
title, displayName…….,facsimileTelephoneNumber, sn but the resultset will return as
sn,facsimileTelephoneNumber……,displayName,title.
There is no reason of this behaviour and it is just how Active Directory OLEDB Provider works.

June 20, 2009

What is not Data Mining? – A Myth Buster

Filed under: Uncategorized — namwar @ 11:38 PM
Tags: , , ,

While watching a web cast by John Weston, I noticed a very important thing which is the clarification of what is not Data Mining? It is very common that people sometime get confused with what actually is Data Mining and start referring different terms and techniques used in normal data processing as Data Mining activities.

Following is a list which describes different data processing techniques and why they can not be reffered as Data Mining?

1. Ad Hoc Query

Ad Hoc queries just examines the current data set and gives you result based on that. This means you can check what is the maximum price of a product but you can not predict what will be the maximum price of that product in near future? A Data Mining Algorithm can do it.

2. Event Notification:

You can set different alerts based on some threshold values which will inform you as soon as that threshold will reach by actual transactional data but again you can not predict when that threshold will reach? A Data Mining Algorithm can do it.

3. Multidimensional Analysis:

You can find the value of an item based on different dimensions like Time, Area, Color but you can not predict what will be the value of the item when its color will be Blue and Area will be UK and Time will be First Quarter of the year? A Data Mining Algorithm can do it.

4. Statistics:

Item Statistics can tell you the history of price changes, moving averages, maximum values, minimum values etc. but it can not tell you how price will change if you start selling another product in the same season. A Data Mining Algorithm can do it.

So in simple words…Data Mining is not history…It is Future!

January 20, 2009

TSQL Function to encode HTML Text

Filed under: Uncategorized — namwar @ 9:36 PM
Tags: , , , ,

While browsing through the Sharepoint content database, I found a very useful TSQL utility function which can be used in any application. This function encodes the given html so that it can be safely used in XML tags and other html rendering requirements.

Please note,  this function is originally provided in Sharepoint content database and I have just copied it because of its usefulness. PLEASE DO NOT CALL it directly from Sharepoint database as it is not recommended and supported by Microsoft. Just create this function in your own database and modify it as per your requirement.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
 go

CREATE FUNCTION [dbo].[fn_HtmlEncode](
     @Value nvarchar(1023),
     @PreserveNewLine bit)
 RETURNS nvarchar(4000)
AS
BEGIN
     DECLARE @Result nvarchar(4000)
     SELECT @Result = @Value
     IF @Result IS NOT NULL AND LEN(@Result) > 0
     BEGIN
         SELECT @Result = REPLACE(@ResultN'&'N'&')
         SELECT @Result = REPLACE(@ResultN'<'N'&lt;')
         SELECT @Result = REPLACE(@ResultN'>'N'&gt;')
         SELECT @Result = REPLACE(@ResultN''''N''')
         SELECT @Result = REPLACE(@ResultN'"'N'&quot;')
         IF @PreserveNewLine = 1
             SELECT @Result = REPLACE(@ResultCHAR(10), CHAR(10) + N'<br>')
     END
     RETURN @Result
END

August 8, 2007

No more Notification Service in SQL Server 2008 !

Filed under: Uncategorized — namwar @ 8:28 PM

At last Microsoft has realised that Notification Service (NS) component of SQL Server is not justifying its position. Microsoft has decided that NS will not be available in SQL Server 2008.
Reporting Services will keep having its subscription based module but no more Notification Service as a separate component.
I think this is a good decision because Notification Service is not generating that much interest and people are not yet convinced to use it in real applications.
Check Readme file of SQL Server 2008 July CTP for further details about this.

August 2, 2007

I am back!

Filed under: Uncategorized — namwar @ 9:05 PM

As I promised that I will be back again in August 2007, so here I am :):) I will start posting SQL Server blogs again from tomorrow as promised. Please keep visiting my blog and keep posting your valuable comments as and when you feel necessary.

June 30, 2007

Will be back in August !

Filed under: Uncategorized — namwar @ 11:28 PM

As you already know that taking off from your busy life is very essential so I am taking off :):) Going on Holidays!!
Will be back after 1 month so please visit my blog again for more new stuff in August!

Thanks for taking time to read my blog.

Namwar Rizvi

June 23, 2007

Finding all Managers of the given employee thourgh Recursive CTE

Filed under: Uncategorized — namwar @ 10:36 PM

Common Table Expressions or CTE are not just for writing more manageable queries. They are lot more than this, recursion is one of the features you can now use by CTE based queries. Remember your school days! Recursion is the solution of many complex or lengthy algorithms like factorial, finding employee hierarchy, finding parent nodes, finding child nodes etc.

SQL Server 2005 CTE based recursive queries are native to the TSQL engine and are very efficient also. Recursive CTE query is composed of two parts:

1. Anchor query, this query starts the recursion or you can say provides seed row for the recursion
2. Recursive query, this query returns the result based on the join condition with the anchor query.

Non-Recursive condition:
As you may already know that recursion always need a non-recursive condition or exit condition otherwise it will go in infinite loop. In Recursive CTE, Non-recursive condition is implicit; it will automatically stop when last run will not return any row.

You can better understand this by running the following query which can be used to return all managers of the given employee.

Use AdventureWorks
Go

With OrgChart as
(
–Acnhor Query
Select EmployeeId,LoginId,ManagerId from HumanResources.Employee Where EmployeeId=4
Union all
–Recursive Query
Select E.EmployeeId,E.LoginId,E.ManagerId from HumanResources.Employee E
Inner Join OrgChart M –Joining with anchor member
On E.EmployeeId=M.ManagerId

)

–Returning all managers of EmployeeId 4
Select * from OrgChart

For detailed study of Recursive CTE please refer to the following resources:

Recursive CTE on MSDN
Excellent article for Recursive CTE on SQL Server Central

June 9, 2007

SQL Server and HyperThreading

Filed under: Uncategorized — namwar @ 5:57 PM
Several people have asked me about the performance impact of Hyper Threading in SQL Server. Always remember, Hyper threaded processor simulates a second processor as a virtual processor so it is not actually a dual processor system but since windows reports it as a second processor therefore, SQL Server query optimizer generates query plans for parallel processing which most of the time degrades your query because of the absence of actual second processor. Now you have two two choices for your queries running on hyper threaded server:

1. Use MAXDOP 1 query hint in the query to inform SQL Server that you do not need parallel processor based query plan.
Example:
Use Adventureworks
Go
Select * From HumanResources.Department OPTION (MAXDOP 1)
OR
2. Set max degree of parallelism server option to 1
Example:
Use master
Go
sp_configure ‘max degree of parallelism’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

I found following discussion quite informative about SQL Server and HyperThreading in Microsoft SQL Server news group:

“The HT technology is not a CPU. The system optimize the CPU access by
providing a better usage of the entire CPU, when you use integer
calculation, only half of the CPU is used, so the floating calculation
engine is free its where HT takes few advantages.
So you have some performance improvement when you run integer related
application and floating calculations (like SQL Server (integer) and 3DSMax
(floating))
So finally, HT will not help your server, sometimes this can degrade the performance because SQL Server think there is 2 CPU and optimize the queries to spread the calculation for 2 CPUs, but in reality you have only 1 CPU so there is some concurrency problem. its recommended to NOT enable the HT technology for SQL Server.”

May 23, 2007

Difference between EXCEPT and NOT IN Operator

Filed under: Uncategorized — namwar @ 7:53 PM

SQL Server 2005 has introduced a new operator called “EXCEPT”. It is a good shortcut for Distinct Not In queries. EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table. On the other hand “NOT IN” will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result. Following code demonstrates the behavior of these two operators:

–Disbale intermediate informative messages
Set Nocount on
go

—Create two sample tables
Declare @m_TestTable table ( id int, name varchar(50) )
Declare @m_TestTable2 table ( id int, name varchar(50) )

–Fill first table with sample data
Insert into @m_TestTable values(1,‘BBC’)
Insert into @m_TestTable values(2,‘CNN’)
Insert into @m_TestTable values(2,‘CNN’)
Insert into @m_TestTable values(3,‘FoxNews’)
Insert into @m_TestTable values(3,‘FoxNews’)

–Fill second table with sample data
Insert into @m_TestTable2 values(1,‘BBC’)

–Run query with EXCEPT operator, Only distinct rows will return
Select id,name from @m_TestTable
except
Select
id,name from @m_TestTable2

–Run query with NOT IN operator, duplicate rows will exist in the result
Select distinct id,name from @m_TestTable
Where id not in
( Select id from @m_TestTable2 )

May 10, 2007

Use OUTPUT to log what is happening to data during INSERT,UPDATE and DELETE

Filed under: Uncategorized — namwar @ 8:20 PM

Triggers are good…triggers are Bad, you can have your own opinion about their usage but one thing is the fact that RDBMS is not complete without them. They are one of the most common strategies to implement history logs to history tables to record old data values before modification or deletion.

There are some scenarios where you cannot use triggers simply because of DBA policies or other strategic or architectural constraints. For example, if you just want to record only those record values which are getting modified by an specific stored procedure, or you want to record values from table variable update statements.

For all these scenarios, here is the solution. Use new OUTPUT construct in SQL Server 2005 TSQL.

Following is the working example to show how you can use OUTPUT in update statement:

Set Nocount On
Declare
@m_tempTable table (itemId int, itemName varchar(50))
Declare @m_tempLogTable table (OlditemId int, NewItemId int,OlditemName varchar(50),NewitemName varchar(50))

—Adding a row into table
Insert into @m_tempTable values (1,‘BeforeUpdateItem’)

— First Update Statement —-

Update @m_tempTable
Set ItemName=‘ItemAfterFirstUpdate’
OutPut Deleted.ItemId,Inserted.ItemId,Deleted.itemName,Inserted.itemName
Into @m_tempLogTable

— Second Update Statement —-
Update @m_tempTable
Set ItemName=‘ItemAfterSecondUpdate’
OutPut Deleted.ItemId,Inserted.ItemId,Deleted.itemName,Inserted.itemName
Into @m_tempLogTable

–Display the data of log table recording after two updates
Select * from @m_tempLogTable

Next Page »

Create a free website or blog at WordPress.com.