Grouping Datetime Column by Date part only.

SELECT DATEADD(dd,(DATEDIFF(dd,0,CRETS)),0), COUNT(*)
FROM Table1
GROUP BY DATEADD(dd,(DATEDIFF(dd,0,CRETS)),0)

SELECT CAST(DATEDIFF(dd,0,CRETS) AS Datetime), COUNT(*)
FROM Table1
GROUP BY CAST(DATEDIFF(dd,0,CRETS) AS Datetime)


--Don't use this:
SELECT CONVERT(Datetime, CONVERT(NCHAR(10), CRETS, 121)), COUNT(*)
FROM Table1
GROUP BY CONVERT(Datetime, CONVERT(nchar(10), CRETS, 121))

Taken from: http://www.sqldisco.com/?p=6

Import from Excel Sheet into Sql Table with filtering

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Data.xls',

'SELECT * FROM [Sheet1$] where [col_name] <> '' ')

SQL FUNCTION ExtractNumbers

SQL FUNCTION ExtractNumbers

Create Function ExtractNumbers

(

@str_passed varchar(max)

)

Returns varchar(50)

AS

BEGIN

    declare @val varchar(50), @i int

    select @val = '', @i = 1

    while (@i <= len(@ str_passed))

        select @val= @val+ (CASE when substring(@ str_passed,@i,1) like '[0-9]' 

                                  then substring(@ str_passed,@i,1) else '' END),

              SET @i = @i + 1

    Return @val

END

Usage:-

SELECT SrNo,  dbo. ExtractNumbers(AlphaNumCol) as NumColData from TableName

Drop commas from '45,22,135'

or Drop dash & braces from '(91)123-456-789'

or Drop alphabets from 'C99B33A1234'

Scalar User Defined Functions and Computed Columns

Scalar User Defined Functions and Computed Columns

Came across an interesting SQL problem where a customer has a table with a primary key and X numerical fields. For each record the maximum value in these X fields was needed.

The issue was how to accomplish this efficiently? Or more accurately the least inefficient way of doing it is.
One option suggested was to have to UNION the table with itself X times and then do a MAX aggregation since there was a lot of data.

The more interesting option, which it turns out not many people are aware of, is to use a Scalar User Defined Function (UDF) to calculate the maximum value.

Here's an example of this in action:

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)

Basically the scalar UDF is called for each row returned from a SELECT. The column itself is not actually stored in the SQL Server table. Any INSERT and UPDATE on the table do not calculate the value for the computed column. Now obviously this is an overhead that may not be acceptable if the table is very static and read a lot. Here, it may be more efficient to perform the calculation once on INSERT or UPDATE of the table with INSTEAD OF triggers or another similar method.

Convert an Integer/Number into words

SET NOCOUNT ON
=============================================
Description: Converts an integer into words,
viz., --
master.dbo.fnSpellInteger(10129) ==> "Ten Thousand One Hundred Twenty-Nine"
=============================================
USE MASTER
IF OBJECT_ID('dbo.fnSpellInteger') IS NOT NULL DROP FUNCTION fnSpellIntegerGO
CREATE FUNCTION dbo.fnSpellInteger ( @number int )RETURNS VARCHAR(100)ASBEGIN -- For debugging outside of the UDF: DECLARE @debug bit SET @debug = 0
DECLARE @result VARCHAR(100), @word VARCHAR(100), @group VARCHAR(100) DECLARE @i int, @j int, @m int, @digit VARCHAR(2), @cn VARCHAR(20)
IF @number = 0 RETURN 'Zero'
SELECT @result = '', @word = '', @group = '' SET @cn = @number SET @cn = REPLACE(@cn,',','') SET @m = LEN(@cn) % 3 IF @m > 0 SET @cn = REPLICATE('0',3-@m) + @cn -- Left pad with zeroes to a multiple of 3
SET @i = 1 SET @j = LEN(@cn)-@i+1 SET @m = @i % 3 WHILE @i <= LEN(@cn) BEGIN -- @i is 1 origin index into numeric string while @m = @i modulo 3 -- If the middle digit of each group of 3 is a '1' then this is a 'Ten' or a '...teen' IF @m = 2 AND SUBSTRING(@cn,@i,1) = '1' BEGIN SET @digit = SUBSTRING(@cn,@i,2) -- Skip rightmost digit of 3 if processing teens SET @i = @i + 1 END ELSE SET @digit = SUBSTRING(@cn,@i,1)
SET @word = CASE WHEN @m = 0 THEN -- Rightmost digit of group of 3 CASE @digit WHEN '0' THEN '' WHEN '1' THEN 'One' WHEN '2' THEN 'Two' WHEN '3' THEN 'Three' WHEN '4' THEN 'Four' WHEN '5' THEN 'Five' WHEN '6' THEN 'Six' WHEN '7' THEN 'Seven' WHEN '8' THEN 'Eight' WHEN '9' THEN 'Nine' END + CASE WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 2 THEN ' Thousand' WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 3 THEN ' Million' WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 4 THEN ' Billion' ELSE '' END WHEN LEN(@digit) = 2 THEN -- Special case when middle digit is a '1' CASE @digit WHEN '10' THEN 'Ten' WHEN '11' THEN 'Eleven' WHEN '12' THEN 'Twelve' WHEN '13' THEN 'Thirteen' WHEN '14' THEN 'Fourteen' WHEN '15' THEN 'Fifteen' WHEN '16' THEN 'Sixteen' WHEN '17' THEN 'Seventeen' WHEN '18' THEN 'Eighteen' WHEN '19' THEN 'Nineteen' END + CASE WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 2 THEN ' Thousand' WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 3 THEN ' Million' WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 4 THEN ' Billion' ELSE '' END WHEN @m = 2 THEN -- Middle digit of group of 3 CASE @digit WHEN '2' THEN 'Twenty' WHEN '3' THEN 'Thirty' WHEN '4' THEN 'Forty' WHEN '5' THEN 'Fifty' WHEN '6' THEN 'Sixty' WHEN '7' THEN 'Seventy' WHEN '8' THEN 'Eighty' WHEN '9' THEN 'Ninety' ELSE '' END WHEN @m = 1 THEN -- Leftmost digit of group of 3 CASE @digit WHEN '0' THEN '' WHEN '1' THEN 'One' WHEN '2' THEN 'Two' WHEN '3' THEN 'Three' WHEN '4' THEN 'Four' WHEN '5' THEN 'Five' WHEN '6' THEN 'Six' WHEN '7' THEN 'Seven' WHEN '8' THEN 'Eight' WHEN '9' THEN 'Nine' END + CASE WHEN @digit <> '0' THEN ' Hundred' ELSE '' END END
SET @group = @group + RTRIM(@word) -- Group value
IF @word <> '' BEGIN DECLARE @prefix VARCHAR(20) IF CHARINDEX(' ',@word) > 0 SET @prefix = LEFT(@word,CHARINDEX(' ',@word)) ELSE SET @prefix = @word IF RIGHT(@result,2) = 'ty' AND @prefix IN ('One','Two','Three','Four','Five','Six','Seven','Eight','Nine') SET @result = @result + '-' + LTRIM(@word) ELSE SET @result = @result + ' ' + LTRIM(@word) END -- The following needs to be outside of a UDF to work: --IF @debug = 1 SELECT @cn as 'Number', @i as '@i', @j as '@j', @m as '@m', @digit as '@digit', CAST(replace(@group,' ','`') AS CHAR(30)) as '@group', @word as '@word', @result as '@result' SET @i = @i + 1 SET @j = LEN(@cn)-@i+1 SET @m = @i % 3 IF @m = 1 SET @group = '' -- Clear group value when starting a new one
END
IF @result = '' SET @result = '0' RETURN LTRIM(@Result)
ENDGO
SET NOCOUNT ON

Description: Converts a string numeric expression into words,
viz., --
master.dbo.fnSpellNumber('15.99') ==> "Fifteen and Ninety-Nine Hundredths"-- Notes: Uses fnSpellInteger to convert an integer into words-- Example: fnSpellNumber can be used to generate pseudo-random test character data/* set nocount on declare @rand int, @i int set @rand = rand(131)*100 set @i = 0
while @i < 5 begin set @i = @i + 1 select 'insert(id,number,words) values(' + cast(@i as varchar(5)) + ',' + cast(@rand as varchar(5)) + ',''' + master.dbo.fnspellnumber(@rand) + ''')' set @rand = rand()*100 end*/-- =============================================
USE MASTER
IF OBJECT_ID('master.dbo.fnSpellNumber') IS NOT NULL DROP FUNCTION fnSpellNumberGO
CREATE FUNCTION dbo.fnSpellNumber ( @number varchar(20) )RETURNS VARCHAR(200)AS--For debugging: declare @number varchar(20) set @number = '192.1'BEGIN -- This is for use outside of a function: DECLARE @debug bit SET @debug = 0
DECLARE @result varchar(200), @word varchar(100) DECLARE @i int, @intpart varchar(20), @decpart varchar(20) SET @word = LTRIM(RTRIM(@number)) -- Check for a bad number, e.g., one with embedded spaces IF ISNUMERIC(@word) = 0 RETURN '<<>>'
SET @i = CHARINDEX('.', @word) -- Remove trailing zeroes for any decimal portion IF @i > 0 -- Number contains a decimal point BEGIN WHILE RIGHT(@word,1) = '0' SET @word = LEFT(@word,LEN(@word)-1) IF @word = '' SET @word = '0' END -- Insert a decimal point at the end if none was specified IF @i = 0 -- No decimal point BEGIN SET @word = @number + '.' SET @i = CHARINDEX('.', @word) END
SET @intpart = LEFT(@word,@i-1) -- Extract the integer part of the number if any IF LEN(@intpart) > 0 SET @result = master.dbo.fnSpellInteger(CAST(@intpart AS int)) ELSE SET @result = ''
-- Extract the decimal portion of the number SET @decpart = RIGHT(@word,LEN(@word)-@i) -- @i is position of decimal point
IF LEN(@decpart) > 0 BEGIN IF @result = 'Zero' SET @result = '' ELSE IF @result <> '' SET @result = @result + ' and '
SET @result = @result + master.dbo.fnSpellInteger(@decpart) + CASE LEN(@decpart) WHEN 0 THEN '' WHEN 1 THEN ' Tenths' WHEN 2 THEN ' Hundredths' WHEN 3 THEN ' One-Thousandths' WHEN 4 THEN ' Ten-Thousandths' WHEN 5 THEN ' One-Hundred-Thousandths' WHEN 6 THEN ' One-Millionths' WHEN 7 THEN ' Ten-Millionths' WHEN 8 THEN ' One-Hundred-Millionths' WHEN 9 THEN ' One-Billionths' END -- Check for a valid plural IF @decpart = 1 SET @result = LEFT(@result, LEN(@result)-1) -- Remove last "s" for just 1 END -- This is for use outside of a function: if @debug = 1 select @word as '@word', @i as '@i', @intpart as '@intpart', @decpart as '@decpart', @result as '@result' RETURN @result
ENDGO

How to increase the size of a database (Enterprise Manager)

How to increase the size of a database (Enterprise Manager)

To increase the size of a database

Expand a server group, and then expand a server.

Expand Databases, right-click the database to increase, and then click Properties.

To increase the data space, click the General tab. To increase the transaction log space, click the Transaction Log tab.

To add a new file, click the next empty row and, in the File name column, enter the file name that will contain the additional space.

The file location is generated automatically and given the .ndf suffix for a database file, or an .ldf suffix for a transaction log file.

To change the default values provided in the File name, Location, Space allocated (MB), and Filegroup (not applicable for the transaction log) columns, click the cell to change and enter the new value.

For existing files, only the Space allocated (MB) value can be changed; the new value must be larger than the existing value.

To specify how the file should grow, select from these options:

To allow the currently selected file to grow as more data space is needed, select Automatically grow file.

To specify that the file should grow by fixed increments, select In megabytes and specify a value.

To specify that the file should grow by a percentage of the current file size, select By percent and specify a value.

To specify the file size limit, select from these options:

To allow the file to grow as much as necessary, select Unrestricted filegrowth.

To specify the maximum size to which the file should be allowed to grow, select Restrict filegrowth (MB) and specify a value.

Note  The maximum database size is determined by the amount of disk space available and the licensing limits determined by the version of SQL Server you are using.

How to Get Available space on Database

How to Get Available space on Database using a Dynamic Query:-

EXEC(N'USE ' + @dbname + N' select  [FILE_SIZE_MB] =

           convert(decimal(12,2),round(size/128.000,2)),

     [SPACE_USED_MB] =

           convert(decimal(12,2),round(fileproperty(name,''SpaceUsed'')/

128.000,2)),

     [FREE_SPACE_MB] =

           convert(decimal(12,2),round((size-

fileproperty(name,''SpaceUsed''))/128.000,2)),

     [DEVICE_NAME] = left(NAME,15)

from  dbo.sysfiles;')

Create A Comma Delimited List From a Column

Create A Comma Delimited List From a Column

Create Sample Data for Both Solutions

-- Suppress data loading messages SET NOCOUNT ON   -- Create Sample Data  CREATE TABLE TeamInfo    (  MemberID    int IDENTITY,       TeamID      int,        FirstName   varchar(50)    )   -- Load Sample Data INSERT INTO TeamInfo VALUES ( 1, 'Jim' ) INSERT INTO TeamInfo VALUES ( 1, 'Mary' ) INSERT INTO TeamInfo VALUES ( 1, 'Bob' ) INSERT INTO TeamInfo VALUES ( 2, 'Sue' ) INSERT INTO TeamInfo VALUES ( 2, 'Ralph' ) INSERT INTO TeamInfo VALUES ( 2, 'Ellen' ) INSERT INTO TeamInfo VALUES ( 3, 'Bill' ) INSERT INTO TeamInfo VALUES ( 3, 'Linda' )

create function -- SQL 2000, Retrieve desired data

-- With SQL 2000, we will create a User Defined Function to do the concatenation. -- While this solution can also be used with SQL Server 2005/SQL Server 2008,  -- the previous suggestion is more efficient.   CREATE FUNCTION dbo.fnMakeTeamList    (  @TeamID int  )    RETURNS varchar(1000) AS    BEGIN       DECLARE @TempTeam table          (  Firstname varchar(20)  )       DECLARE @TeamList varchar(1000)       SET @TeamList = ''       INSERT INTO @TempTeam          SELECT FirstName          FROM TeamInfo          WHERE TeamID = @TeamID       IF @@ROWCOUNT > 0          UPDATE @TempTeam             SET @TeamList = ( @TeamList + FirstName + ', ' )       RETURN substring( @TeamList, 1, ( len( @TeamList ) - 1 ))    END   -- Usage SELECT     TeamID,     MemberList = dbo.fnMakeTeamList( TeamId )  FROM TeamInfo  GROUP BY TeamID   -- Results TeamID     MemberList 1 Jim, Mary, Bob 2 Sue, Ralph, Ellen 3 Bill, LindaS

QL Server 2005 / SQL Server 2008 Solution

--Retrieve desired data SELECT    t1.TeamID,    MemberList = substring((SELECT ( ', ' + FirstName )                            FROM TeamInfo t2                            WHERE t1.TeamID = t2.TeamID                            ORDER BY                                TeamID,                               FirstName                            FOR XML PATH( '' )                           ), 3, 1000 )FROM TeamInfo t1 GROUP BY TeamID   -- Results TeamID     MemberList 1 Bob, Jim, Mary 2 Ellen, Ralph, Sue 3 Bill, Linda

ISNULL() and COALESCE()

ISNULL() and COALESCE()

In dealing with NULL values in SQL Server, the developer is often faced with the requirement to evaluate the content of a field, and when said field in null, return another value (or field). There are two functions in SQL Server which support this requirement: ISNULL() and COALESCE.()

ISNULL() accepts two parameters. The first is evaluated, and if the value is null, the second value is returned (regardless of whether or not it is null). The following queries will return the second parameter in both cases:

SELECT ISNULL(NULL, 1) --Returns 1 SELECT ISNULL(NULL, NULL) --Returns NULL 

COALESCE()

COALESCE() is a TSQL function which, like ISNULL, is built into SQL Server. Unlike ISNULL, COALESCE is also a part of the ANSI-92 SQL Standard. Coalesce returns the first non-null expression in a list of expressions. The list can contain two or more items, and each item can be of a different data type. The following are valid examples of COALESCE:

SELECT COALESCE(NULL, 1) --Returns 1   SELECT COALESCE(NULL, 3, NULL, 1) --Returns 3 

ISNULL vs. COALESCE

Whenever multiple methods exist for addressing a single problem, the inevitable question is: which method is better? There are a few differences between the two functions which make COALESCE come out on top more often than not:
- COALESCE is ANSI-92 compliant. In the event that you need to port your code to another RDBMS, COALESCE will not require rework.
- COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:
SELECT ISNULL(ISNULL(Col1, Col2), Col3)
- ISNULL constrains the result of a comparison of parameterrs to the datatype of the first value. For example, the following query will produce some often undesirable results using ISNULL, however it will behave as expected with COALESCE:

DECLARE @Field1 char(3), @Field2 char(50) SET @Field2 = 'Some Long String'   SELECT ISNULL(@Field1, @Field2) --Returns 'Som' SELECT COALESCE(@Field1, @Field2) --Returns 'Some Long String' 

Note: In other situations, COALESCE will produce unexpected results. COALESCE by nature promotes it's arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren't compatable, will of course throw an error). When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime. For example:
SELECT COALESCE(5, GETDATE())
Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime).

Performance

For most purposes, ISNULL and COALESCE perform in an almost identical fashion. It is generally accepted that ISNULL is slightly quicker than COALESCE, but not sufficiently to outweigh it's inherent limitations. As with any performance related issue, if performance is a significant concern, write it both ways, and test!

SQL2008: Lock a Stored Procedure for Single Use Only

SQL2008: Lock a Stored Procedure for Single Use Only

Create a Custom Error Message, Designating an User assigned Error Number, starting at 50000, or higher, and a user designated Error Message.

USE Northwind GO   EXECUTE sp_addmessage     @msgnum   = 51001,     @severity = 16,     @msgtext  = N'Resource NOT Available',     @lang     = 'us_english',    @replace  = REPLACE

Create a Stored Procedure that Can be 'locked'

CREATE PROCEDURE dbo.Employees_U_LastName    (  @EmployeeID   int,       @LastName     varchar(20)    ) AS    BEGIN         SET TRANSACTION ISOLATION LEVEL REPEATABLE READ       BEGIN TRANSACTION         DECLARE @LockResult int         EXECUTE @LockResult = sp_getapplock           @Resource    = 'RepeatableRead_TRANSACTION',           @LockMode    = 'Exclusive',          @LockTimeout = 0              IF @LockResult <> 0             BEGIN                ROLLBACK TRANSACTION                RAISERROR ( 51001, 16, 1 )                RETURN             END            -- All code between the use of sp_getapplock above,          -- and sp_releaseapplock below will be restricted to           -- only one user at a time.              -- Ten Second delay for Demonstration Purposes          WAITFOR DELAY '00:00:10'          -- Remove these three lines for 'Normal' use            UPDATE Employees             SET LastName = @LastName             WHERE EmployeeID = @EmployeeID            EXECUTE sp_releaseapplock              @Resource = 'RepeatableRead_TRANSACTION'         COMMIT TRANSACTION      END  Demonstration

Open two separate Query Windows, and place the following code in one of the windows.
DO NOT Execute the code until both Query Windows are in place and connected to the database.

Query Window 1

   EXECUTE dbo.Employees_U_LastName       @EmployeeID = 1,       @LastName   = 'Davolio-Jones'         SELECT           EmployeeID,          LastName,          FirstName       FROM Employees       WHERE EmployeeID = 1 

Place the following code in Query window 2

Query Window 2

   EXECUTE dbo.Employees_U_LastName       @EmployeeID = 1,       @LastName   = 'Davolio'         SELECT           EmployeeID,          LastName,          FirstName       FROM Employees       WHERE EmployeeID = 1 

Execute the code in Query window 1, and then immediately execute the code in Query window 2.

Results in Query Window 2

Msg 51001, Level 16, State 1, Procedure Employees_U_LastName, Line 21 Resource NOT Available 

As you can see, it becomes possible to control code to single use only. 

Clean up the test environment

DROP PROCEDURE dbo.Employees_U_LastName 

Concurrency and Performance Considerations

Applications should capture the error, and handle appropriately, perhaps attempting to EXECUTE the query after a brief delay.

Finding the Last Occurrence of a Pattern in a String

When dealing with string data, one of the most useful Transact-SQL functions you can have in your toolbox is PATINDEX. Aside from it’s many creative uses (and there are quite a few), it allows you to search for an expression (including wildcard operators) within any valid SQL Server character type. It returns the position of the first occurrence of the pattern within the string, and returns zero (0) if the pattern is not found. As always, an example is worth 1000 bon mots:

SELECT PATINDEX('%c%', 'abcdefgabcdefga')

The above snippet returns 3, because the first instance of “c” within “abcdefgabcdefga” is at the third character.

On occasion, you may be required to find the last occurrence of the pattern rather than the first. This usually happens when you’re dealing with data from external systems, or when you’ve inherited custody of an inappropriately denormalized system (one where more than one “logical” data item has been stored in a single, concatenated field). I have the same requirement for my “mad science” series, so I developed a function to take care of it. Here it is:

CREATE FUNCTION dbo.REVERSEPATINDEX(@StringToFind    nvarchar(max),

                                  @StringToSearch nvarchar(max))

RETURNS bigint

AS

BEGIN

  --LastIndex will store the "previous" index of the pattern

  --while we search for the "next" index inside our string.

  DECLARE @LastIndex bigint, @NextIndex bigint


  --We will chop off the "searched" portion of our string

  --After each search iteration

  DECLARE @CurrentStringToSearch nvarchar(max)


  --Assign the value of the input parameter to @CurrentStringToSearch

  SET @CurrentStringToSearch = @StringToSearch


  --Grab the first occurrence, if it exists, using PATINDEX

  SET @NextIndex = PATINDEX(@StringToFind, @CurrentStringToSearch)

    
  --If we've found the pattern within the string, continue and find

  --subsequent occurrences until there are no more to find.

  WHILE @NextIndex > 0

  BEGIN

      --Add @NextIndex to @LastIndex, effectively storing within

      --@LastIndex the index of the occurence that we have just found,

      --based upon the original (non-truncated) string.

      SET @LastIndex = COALESCE(@LastIndex,0) + @NextIndex

    
      --Truncate @CurrentStringToSearch, removing the portion of the

      --string that we have already searched.

      SET @CurrentStringToSearch
          = RIGHT(@CurrentStringToSearch, LEN(@CurrentStringToSearch) - @NextIndex)

    
      --Find the NEXT occurence of the pattern in the remaining portion of

      --the string to search.

      SET @NextIndex = PATINDEX(@StringToFind, @CurrentStringToSearch)

  END


  --We will COALESCE @LastIndex and @NextIndex; @LastIndex will be NULL

  --if the pattern does not exist in the string (as the loop above will

  --never have been executed).

  RETURN  COALESCE(@LastIndex, @NextIndex)

END

GO

And to test:

SELECT dbo.REVERSEPATINDEX('%c%', 'abcdefgabcdefga')

Returns 10 – the correct answer, given that the last occurrence of “c” in “abcdefgabcdefga” is at the tenth character.

How do you find out all the IDENTITY columns of all the tables in a given database?

How do you find out all the IDENTITY columns of all the tables in a 

given database?

SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS [Table Name], COLUMN_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

Differences Between SQL Server 2000 and 2005

Feature	SQL Server 2000	SQL Server 2005
Server Programming Extensions	Limited to extended stored procedures, which are difficult to write and can impact the server stability.	The incorporation of the CLR into the relational engine allows managed code written in .NET languages to run. Different levels of security can protect the server from poorly written code.
T-SQL Error Handling	Limited to checking @@error, no much flexibility.	Addition of TRY/CATCH allows more mature error handling. More error_xx functions can gather additional information about errors.
T-SQL Language	SQL Language enhanced from previous versions providing strong data manipulation capabilities.	All the power of SQL Server 2000 with the addition of CTEs for complex, recursive problems, enhanced TOP capabilities, PIVOT/APPLY/Ranking functions, and ROW_NUMBER
Auditing	Limited support using triggers to audit changes.	Robust event handling with EVENT NOTIFICATIONS, the OUTPUT clauses, and DDL triggers.
Large Data Types	Limited to 8k for normal data without moving to TEXT datatypes. TEXT is hard to work with in programming environments.	Includes the new varchar(max) types that can store up to 2GB of data in a single column/row.
XML	Limited to transforming relational data into XML with SELECT statements, and some simple query work with transformed documents.	Native XML datatype, support for schemas and full XPATH/XQUERY querying of data.
ADO.NET	v1.1 of ADO.NET included enhancements for client development.	v2 has more features, including automatic failover for database mirroring, support for multiple active result sets (MARS), tracing of calls, statistics, new isolation levels and more.
Messaging	No messaging built into SQL Server.	Includes Service Broker, a full-featured asynchronous messaging system that has evolved from Microsoft Message Queue (MSMQ), which is integrated into Windows.
Reporting Services	An extremely powerful reporting environment, but a 1.0 product.	Numerous enhancements, run-time sorting, direct printing, viewer controls and an enhanced developer experience.
ETL	DTS is a very easy to use and intuitive tool. Limited capabilities for sources and transformations. Some constructs, such as loops, were very difficult to implement.	Integration Services is a true programming environment allowing almost any source of data to be used and many more types of transformations to occur. Very complex environment that is difficult for non-DBAs to use. Requires programming skills.
Full-Text Search	Workable solution, but limited in its capabilities. Cumbersome to work with in many situations.	More open architecture, allowing integration and plug-ins of third party extensions. Much more flexible in search capabilities.

Sql Split function

CREATE FUNCTION Split

(

     @StringToSplit varchar(2048),

     @Separator varchar(128)

)

RETURNS TABLE AS RETURN

WITH CTE AS

(

     SELECT 0 StartIndex, 1 EndIndex

     UNION ALL

     SELECT EndIndex, CHARINDEX(@Separator, @StringToSplit, EndIndex) + LEN(@Separator)

     FROM CTE

     WHERE EndIndex > StartIndex

)

SELECT

     SUBSTRING(@StringToSplit

                        , StartIndex

                        , CASE WHEN EndIndex > LEN(@Separator)

                                  THEN EndIndex-StartIndex-LEN(@Separator)

                                  ELSE LEN(@StringToSplit) - StartIndex + 1

                         END) String

     ,StartIndex StartIndex     

FROM CTE

WHERE StartIndex > 0

Import Data from Excel File to sql datatable

Import Data from Excel File to sql datatableTo Export Data Form an Excel file, there is one drawback lying behind them. The Sheet1 from the query actually is the name of the spreadsheet in your excel file. What if there is no Sheet1 inside the excel file? What if you don’t even know the sheet name? 

Yes. I found this error when I tried to upload an excel file with different sheet name. And here is the solution for that.

To get the sheet name in your excel file, firstly, Microsoft DAO 3.5 Library is needed. Go to Project -> Reference -> Add Reference, select Microsoft DOA 3.5 Library from the list and add to your project. Here is the example code of how to get the first sheet in the excel file.

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strSheet As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strSheet = dao_DB.TableDefs(0).Name
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""

da = New OleDbDataAdapter("SELECT * FROM [" & _
strSheet & "]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

Import Data from Excel File to sql datatable

Import Data from Excel File to sql datatable

As I has stated in my previous post regarding to Export Data Form an Excel file, there is one drawback lying behind them. The Sheet1 from the query actually is the name of the spreadsheet in your excel file. What if there is no Sheet1 inside the excel file? What if you don’t even know the sheet name? 

Yes. I found this error when I tried to upload an excel file with different sheet name. And here is the solution for that.

To get the sheet name in your excel file, firstly, Microsoft DAO 3.5 Library is needed. Go to Project -> Reference -> Add Reference, select Microsoft DOA 3.5 Library from the list and add to your project. Here is the example code of how to get the first sheet in the excel file.

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strSheet As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strSheet = dao_DB.TableDefs(0).Name
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""

da = New OleDbDataAdapter("SELECT * FROM [" & _
strSheet & "]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)