Tuesday, January 15, 2008

Finding gaps in an identity column (or any integer based column for that matter) using MS SQL

Few days ago one of my friend ask me to do a query to find gaps in an identity column in MS SQL. This is not that tough i thought and started to work on it. But i was given a boundary, no Stored Procedure can be introduced. Need to write it in query and if possible then in a single query! I found some solution but those were for MS SQL 2005 and got a comment "With Sql Server 2000, it would be extremely difficult and would require some iterative techniques."

But now i have a solution for that and want to share with you. Here it is with complete query for Table Creation, Data insertion and Selecting query.

Table query :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[StudentInfo]GOCREATE TABLE [dbo].[StudentInfo] ( [SlNo] [int] NULL , [Name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Roll] [int] NULL ) ON [PRIMARY]
GO

Inserting Data:

Insert Into StudentInfo Values(1,'Zakirul', 1)
Insert Into StudentInfo Values(2,'Mamun', 2)
Insert Into StudentInfo Values(3,'Robin', 3)
Insert Into StudentInfo Values(5,'Fakhrul', 4)
Insert Into StudentInfo Values(7,'Shamol', 5)

Selection Query:

SELECT DISTINCT (a.SlNo + 1) gapId Into #tmp FROM StudentInfo a
WHERE NOT EXISTS
(SELECT * FROM StudentInfo b WHERE b.SlNo = a.SlNo + 1)
AND a.SlNo < (SELECT MAX(SlNo) FROM StudentInfo)

UNION ALL

SELECT a.SlNo + 1 FROM StudentInfo a
WHERE NOT EXISTS
(SELECT * FROM StudentInfo b WHERE b.SlNo = a.SlNo + 1)
AND a.SlNo > (SELECT MAX(SlNo) FROM StudentInfo)



SELECT gapId AS BitPosition FROM #tmp ORDER BY gapId

It is working nicely for my domain, hope work with yours. Feel free to ask me any question regarding this.

Thanks,
-Zaq

Wednesday, January 9, 2008

An introduction about Lambda Expressions

Ok start with a short introduction. With lambda expressions, we can define function objects for use at any time. C# has always supported this capability via delegates, whereby you create a function object and wire it up to the backing code at the time of creation. Lambda expressions join these two actions—creation and connection—into one expressive statement in the code. A functional is a function that takes functions in its parameter list and operates on those functions, possibly even returning another function as the result. For example, a functional could accept two functions, where one performs one mathematical operation and the other performs a different mathematical operation, and return a third function that is a composite function built from the two. Lambda expressions provide a more natural way to create and invoke functionals. In simple syntactic terms, lambda expressions are a syntax whereby you can declare anonymous functions (delegates) in a more fluid and expressive way. Lambda expressions really take two forms. The form that most directly replaces anonymous methods in syntax includes a statement block within braces. I like to refer to these as lambda statements. These lambda statements are a direct replacement for anonymous methods. Lambda expressions, on the other hand, provide an even more abbreviated way to declare an anonymous method and do not require code within braces nor a return statement. Both types of lambda expressions can be converted to delegates.

Zaq

Monday, January 7, 2008

How to run a script file from MS SQL Query Analyzer

No direct command can be use to read a script file and execute it. But the isql.exe and osql.exe come in handy when you have to execute a script file from within T-SQL. Just call any of these exes using xp_cmdshell and pass the script file name as parameter to it. See SQL Server Books Online for more information about the input parameters of these exes. Here are some quick examples:

EXEC master..xp_cmdshell 'osql /d DatabaseName /U UserName /P Password /i C:\YourFileNameWithExt -n'

See xp_cmdshell in SQL Server Books Online if you are having permissions problems in getting this technique to work

Hope this will work.


Zaq