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.

Advertisements

April 5, 2009

How to create Custom Database Role and Manage its permissions

Filed under: How To,Query,SQL Server 2005,SQL Server 2008,TSQL — namwar @ 8:50 PM
Tags: , , ,

Custom database roles is the best way to manage object permission for the given database in structured way. Following script will demonstrate the steps necessary to create a new role and manage object permissions through it

--- Step 1 -------- Create Role ------------
USE [MyTestDB]
GO
CREATE ROLE [TestRole1] AUTHORIZATION [dbo]
Go

--- Step 2 -------- Assign Permissions to role  ------------
USE [MyTestDB]
GO
GRANT ALTER,EXECUTE, SELECT,INSERT,UPDATE,DELETE
ON SCHEMA ::dbo
TO TestRole1
Go

USE [MyTestDB]
GO
GRANT BACKUP DATABASE,BACKUP LOG To [TestRole1]
Go

--- Step 3 ---- Add user to our role --------------
USE [MyTestDB]
GO
EXEC sp_addrolemember N'TestRole1', N'SPUser7'
GO

-- ******  Create user *********************

--- Step 1 ---- Create Windows Login For SQL Server ------------
USE [master]
GO
CREATE LOGIN [TestDomain\SPUser7] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTestDB]
GO

--- Step 2 ---- Create Database User for our database --------------
USE [MyTestDB]
GO
CREATE USER [SPUser7] FOR LOGIN [TestDomain\SPUser7]
GO

January 2, 2009

Two quickest ways to find file information of a given database

If you want to check the name of the files and their respective sizes for the given database then following are two quick tsql scripts:

1. Use sp_helpfile

This stored procedure returns the file information for the selected database e.g.

USE AdventureWorks;

GO

EXEC sp_helpfile;

GO

It will return the resultset similar to following

sp_helpfile

By default, this view returns a column called size but this is actually the size in 8KB pages therefore, to get the exact size in KB, as returned by sp_helpfile, multiply it by 8 as mentioned in above query.

September 14, 2008

Quick technique to retrieve alphanumeric values by using regular expression in TSQL

Filed under: Query,tips,TSQL — namwar @ 12:33 AM
Tags: , ,

If you have a column in a database which contains numeric, alphanumeric and only aplha values and you just want to find all those rows which have alphanumeric values i.e which contains atleast one numeric alongwith anynumber of alphabets then you can use the power of Like operator and a little regular expression technique. Following is a query for Name column in Production.Product table of AdventureWorks database. This query retrieves all rows which contains alphanumeric values:

Use AdventureWorks

Go

Select * from Production.Product Where Name like ‘%[0-9]%’

Go

August 26, 2008

Deleting ununsed primary keys

Filed under: Query,tips,TSQL — namwar @ 10:35 PM
Tags: , ,

Sometimes we need to make sure that all keys in the primary key table has atleast one corresponding row in child table or in other words every key has been used atleast once as a foreign key. This is a typical scenario we face during data import where data integrity can not be guarnteed 100% due to various reason. Following script demonstrate a simple technique to delete all those rows in the master table which are not used in child table.

Use tempdb;

/* Create sample tables and insert sample data */

Create table tblMaster

(

masterId int,

masterName varchar(50)

)

Go

Create table tblChild

(

childId int NOT NULL IDENTITY (1, 1),

masterId int,

childName varchar(50)

)

Go

Insert into tblMaster (masterId,masterName) values (1,‘Master 1’);

Insert into tblMaster (masterId,masterName) values (2,‘Master 2’);

Insert into tblMaster (masterId,masterName) values (3,‘Master 3’);

Insert into tblMaster (masterId,masterName) values (4,‘Master 4’);

Go

Insert into tblChild (masterId,childName) values (1,‘Child 1’);

Insert into tblChild (masterId,childName) values (1,‘Child 2’);

Insert into tblChild (masterId,childName) values (1,‘Child 3’);

Insert into tblChild (masterId,childName) values (2,‘Child 4’);

Go

/* Master table before deletion */

Select * from tblMaster

Go

/* Delete unused rows */

Delete tblMaster

where

not exists

(

Select masterId

from tblChild

where

tblChild.masterId=tblMaster.masterId

)

Go

/* Master table after deletion */

Select * from tblMaster

Go

/* Clean up */

drop table tblMaster;

Go

drop table tblChild;

Go

August 21, 2007

Caching and Recompilation in SQL Server 2005

Filed under: Information,New Features,Performance,Query,SQL Server 2005 — namwar @ 8:11 PM

I was browsing through the newsgroups and found a very interesting white paper link about SQL Server 2005 Plan caching and batch recompilations. This white paper is a must read for anyone who really wants to master the query tuning and optimization strategies.
You can read this white paper here

August 15, 2007

Difference between INNER JOIN and INTERSECT

Suppose you have two tables and you want the distinct rows which are common in both the tables what will you do to get them? Most of us will answer that they will use INNER JOIN to get the common rows of two tables, right? Wrong!!!!!!
This is a big misconception that INNER JOIN will always return all the common rows between two tables. In reality, INNER JOIN treats two NULLS as two different values rather than a same value so if you are joining a row based on a NULLable column there is a chance that if both tables have NULLs in that column then INNER JOIN will ignore those rows because

Two NULLs are not same in TSQL

To correctly retrieve all common rows between two tables, SQL Server 2005 has introduced the INTERSECT keyword. INTERSECT treats two NULLs as a same value and it returns all rows which are common in both the tables.
Run the following example and you will see the result yourself:

–Create two table variables for testing
Declare @m_table1 table (id int, firstName varchar(50))
Declare @m_table2 table (id int, firstName varchar(50))

–Fill the tables with NULLs
Insert into @m_table1 values (1,NULL)
Insert into @m_table2 values (1,NULL)

—Getting all common rows by Inner Join
Select t1.*
from @m_table1 t1
inner join @m_table2 t2
On
t1.id=t2.id and t1.firstName=t2.firstName

—Getting all common rows by INTERSECT
Select * from @m_table1
INTERSECT
Select
* from @m_table2

August 12, 2007

Finding client application name by TSQL

Filed under: Information,Query,tips,TSQL — namwar @ 2:10 PM

A friend of mine asked me is there any way to find the name of the application or client which is connected to the SQL Server by using just TSQL? I gave him the answer and thought that it will be useful to other readers of my blog. It is very simple to find the client application’s name. Following is the sample code:

Select APP_NAME() as currentApplication

August 10, 2007

Optimized solution of Paging by using Count(*) OVER() functionality

Paging through a dataset is always a requirement of any data oriented application. There are several solutions to implement paging. Some of them are client based and some are server based.
Every paging solution needs:

  1. Records of only selected page
  2. Total no. of records to calculate total of pages available

Whatever solution you implement, you will end up doing at least two queries, first to find total no. of records and secondly to find the records of your selected page.

In oneof my previous posts, I showed you the way of implementing paging by using Row_Number() function of SQL Server 2005. Today, I am showing you an excellent trick to get total of records and list of selected records in just one query. Following is the sample code to implement this logic. This code demonstrates the way you can implement paging very effectively without hitting the server twice. Note down the use of Count(*) OVER() functionality.

Use AdventureWorks
Go

–Change these two variable to parameters of your
–actual stored procedure

Declare @PageNumber int
Declare @PageSize int

–Assume we need page 6 i.e. records from 51-60
Select @PageNumber=6
Select @PageSize=10

–Select only those records which
–fit into a single page

Select Top(@PageSize) * from
(
Select
RowID=ROW_NUMBER() OVER (ORDER BY Name),
ProductID,Name,ProductNumber,ListPrice,
TotalRows=Count(*) OVER() –Count all records
from Production.Product

) A
Where A.RowId > ((@PageNumber1)*@PageSize)

August 9, 2007

Calculate Product of the numeric values by TSQL

Filed under: Information,Performance,Query,tips,TSQL — namwar @ 9:55 PM

TSQL Provides you mathematical functions like SUM(), AVG() etc. but it does not provide you a way to calculate Product of a list of values. Most of us know from our high school mathematics that Multiplication can be converted into a series of addition. If you use remember basic logarithms then you can easily calculate the product of the values. To refresh your concepts of logarithms please visit wikipedia here
Now, following is a sample code to demonstrate how you can calculate the product of the values by TSQL

Please be aware that since logarithms involves decimal numbers therefore, the result will not be 100% accurate but it will serve the purpose in most of the cases.

–Declare a sample table
Declare @m_TestTable table (sampleValue int)

–Fill the sample table with 10 values
Declare @m_value int
Select @m_value=1
While (@m_value < 10)
Begin
Insert into @m_TestTable values(@m_value)
Select @m_value=@m_value+1

End

–Calculate the product
Select Power(10,Sum(Log10(sampleValue))) from @m_TestTable

Next Page »

Blog at WordPress.com.