No One is Perfect

Rarely used SQL Server Functions

2007-09-21T14:22:36+02:00

BINARY_CHECKSUM
SIGN
COLUMNPROPERTY
DATALENGTH
ASCII, UNICODE
NULLIF
PARSENAME
STUFF
REVERSE
GETUTCDATE

BINARY_CHECKSUM
BINARY_CHECKSUM is handy if you want to check for data differences between 2 rows of data

In order to see what rows are in table 1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use BINARY_CHECKSUM. You have to run this example o SQL Server 2000 to see it work, you can ofcourse use any tables just modify the queries
Let’s get started…

--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors

--update 5 records by appending X to the au_fname
SET ROWCOUNT 5

UPDATE tempdb..authors2
SET au_fname =au_fname +'X'

--Set rowcount back to 0
SET ROWCOUNT 0

--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'

--* The BIG SELECT QUERY --*

--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2

--Clean up
DROP TABLE tempdb..authors2
GO

SIGN
Sometimes you are asked by the front-end/middle-tier developers to return a rowcount as well with the result set. However the developers want you to return 1 if there are rows and 0 if there are none. How do you do such a thing?
Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function

For CASE we will do this

RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0

Using the SIGN function is even easier, all you have to do is this

RETURN SIGN(@@ROWCOUNT)

That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are
So let's see this in action

USE pubs
GO

--Case Proc
CREATE PROCEDURE TestReturnValues
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id

RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
GO

--Sign Proc
CREATE PROCEDURE TestReturnValues2
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id

RETURN SIGN(@@ROWCOUNT)
GO

--Case Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues
SELECT @Rowcount
GO

--Case Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues 'ABC'
SELECT @Rowcount
GO

--Sign Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2
SELECT @Rowcount
GO

--Sign Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2 'ABC'
SELECT @Rowcount
GO

--Help the environment by recycling ;-)
DROP PROCEDURE TestReturnValues2,TestReturnValues
GO

COLUMNPROPERTY
COLUMNPROPERTY is handy if you need to find scale, precision, if it is an identity column and more. I have listed all of them below

CREATE TABLE blah (ID DECIMAL(5,2) not null DEFAULT 99)
INSERT blah DEFAULT VALUES

SELECT * FROM blah
SELECT COLUMNPROPERTY( OBJECT_ID('blah'),'ID','AllowsNull') AS AllowsNull,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsComputed') AS IsComputed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsCursorType') AS IsCursorType,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsDeterministic') AS IsDeterministic,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsFulltextIndexed') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdentity') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdNotForRepl') AS IsIdNotForRepl,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIndexable') AS IsIndexable,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsOutParam') AS IsOutParam,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsPrecise') AS IsPrecise,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsRowGuidCol') AS IsRowGuidCol,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Precision') AS 'Precision',
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Scale') AS Scale,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','UsesAnsiTrim') AS UsesAnsiTrim
FROM Blah

So what does all that stuff mean?

AllowsNull
Allows null values. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsComputed
The column is a computed column. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsCursorType
The procedure parameter is of type CURSOR. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsDeterministic
The column is deterministic. This property applies only to computed columns and view columns. 1 = TRUE
0 = FALSE
NULL = Invalid input. Not a computed column or view column.

IsFulltextIndexed
The column has been registered for full-text indexing. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsIdentity
The column uses the IDENTITY property. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsIdNotForRepl
The column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR REPLICATION is specified, the IDENTITY_INSERT setting is not checked. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsIndexable
The column can be indexed. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsOutParam
The procedure parameter is an output parameter. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsPrecise
The column is precise. This property applies only to deterministic columns. 1 = TRUE
0 = FALSE
NULL = Invalid input. Not a deterministic column

IsRowGuidCol
The column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property. 1 = TRUE
0 = FALSE
NULL = Invalid input

Precision
Precision for the data type of the column or parameter. The precision of the specified column data type
NULL = Invalid input

Scale
Scale for the data type of the column or parameter. The scale
NULL = Invalid input

UsesAnsiTrim
ANSI padding setting was ON when the table was initially created. 1= TRUE
0= FALSE
NULL = Invalid input

DATALENGTH
Okay so you know the LEN function but do you know the DATALENGTH function? There are two major difference between LEN and DATALENGTH.
The first one deals with trailing spaces, execute the following code and you will see that LEN returns 3 while DATALENGTH returns 4

DECLARE @V VARCHAR(50)
SELECT @V ='ABC '
SELECT LEN(@V),DATALENGTH(@V),@V

The second difference deals with unicode character data, as you know unicode uses 2 bytes to store 1 character
Run the following example and you will see that LEN returns 3 while DATALENGTH returns 6
DECLARE @V NVARCHAR(50)
SELECT @V ='ABC'
SELECT LEN(@V),DATALENGTH(@V),@V

If you do DATALENGTH(CONVERT(VARCHAR,@V)) you will get the same as LEN because LEN does a RTRIM and converts to VARCHAR before returning

ASCII, CHAR,UNICODE
ASCII will give you the ascii code for a character so for A you will get 65
CHAR does the reverse of ascii CHAR(65) returns A
UNICODE will give you the unicode value for a character
NCHAR will give you the character for a unicode or ascii value
let's see how this works

SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),
UNICODE(N'Λ'),NCHAR(923),NCHAR(UNICODE(N'Λ'))

NULLIF
NULLIF Returns a null value if the two specified expressions are equivalent.

Syntax
NULLIF ( expression , expression )

DECLARE @v VARCHAR(20)
SELECT @v = ' '

SELECT NULLIF(@v,' ')

You can combine NULLIF with COALESCE if you want to test for NULLS and Blanks for example

DECLARE @v VARCHAR(20)
SELECT @v = ' '

SELECT COALESCE(NULLIF(@v,' '),'N/A')

Here is another NULLIF example:
CREATE TABLE Blah (SomeCol VARCHAR(33))

INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')

--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah

--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah

Output for both queries
-----------------------
N/A
N/A
N/A
A
B B

PARSENAME
PARSENAME retrieves parts of string delimited by dots. It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName but you can use it to split IP addresses, names etc

DECLARE @ParseString VARCHAR(100)
SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'

SELECT PARSENAME(@ParseString,4),
PARSENAME(@ParseString,3),
PARSENAME(@ParseString,2),
PARSENAME(@ParseString,1)

CREATE TABLE #Test (
SomeField VARCHAR(49))

INSERT INTO #Test
VALUES ('aaa-bbbbb')

INSERT INTO #Test
VALUES ('ppppp-bbbbb')

INSERT INTO #Test
VALUES ('zzzz-xxxxx')

--using PARSENAME
SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
FROM #Test

Another example:

CREATE TABLE BadData (FullName varchar(20) NOT NULL);
INSERT INTO BadData (FullName)
SELECT 'Clinton, Bill' UNION ALL
SELECT 'Johnson, Lyndon, B.' UNION ALL
SELECT 'Bush, George, H.W.';

Split the names into 3 columns

Your output should be this:
LastName FirstName MiddleInitial
Clinton Bill
Johnson Lyndon B.
Bush George H.W.

SELECT FullName,PARSENAME(FullName2,NameLen+1) AS LastName,
PARSENAME(FullName2,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(FullName2,NameLen-1),'~','.'),'') AS MiddleInitial
FROM(
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName2,FullName
FROM BadData) x

STUFF
STUFF is another function that is hardly used, it is useful if you want to replace or add characters inside data
Take a look at the code below. the first STUFF will replace X with 98765, the second STUFF will place 98765 before the X and the third stuff will replace X- with 98765
DECLARE @v VARCHAR(11)
SELECT @v ='-X-'

SELECT STUFF(@v, 2, 1, '98765'),
STUFF(@v, 2, 0, '98765'),
STUFF(@v, 2, 2, '98765')

The STUFF function is very handy if you need to insert dashes in a social security. You can accomplish that by using the function STUFF twice instead of using substring,left and right

DECLARE @v VARCHAR(11)
SELECT @v ='123456789'

SELECT @v,STUFF(STUFF(@v,4,0,'-'),7,0,'-')

REVERSE
REVERSE just reverses the value, for example the code below returns CBA

SELECT REVERSE('ABC')

Reverse is handy if you need to split values, take a look at this example

CREATE TABLE #TestCityStateZip (csz CHAR(49))
INSERT INTO #TestCityStateZip VALUES ('city ,st 12223')
INSERT INTO #TestCityStateZip VALUES ('New York City,NY 10028')
INSERT INTO #TestCityStateZip VALUES ('Princeton , NJ 08536')
INSERT INTO #TestCityStateZip VALUES ('Princeton,NJ 08536 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City , NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City ,NY 10013 ')

SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS State,
RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
FROM #TestCityStateZip

GETUTCDATE
SELECT GETUTCDATE()

Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.

Comments

Formating Number in VB.NET

2007-08-31T05:37:43+02:00

Display Formats
Information stored in variables as various data types may not be in a format for visual presentation on a Web page. Visual Basic provides several built-in functions for presentation of this data.
Formatting Currency
The FormatCurrency() function formats numeric data for presentation as dollars and cents. Its general format is shown below.
FormatCurrency(value [, trailing digits] [, leading digit] [, parentheses] [, group digits])
value is any expression that produces a number;

trailing digits is an integer giving the number of digits following the decimal point; the default is rounding to 2 digits;

leading digit is True or False to indication whether a leading 0 is to appear before the decimal point for fractional values;

parentheses is True or False to indicate whether negative numbers should be displayed inside parentheses;

group digits is True or False to indicate whether numbers should be grouped between commas.
Format Output
FormatCurrency(12345.6789) $12,345.68
FormatCurrency(12345.6789, 4) $12,345.6789
FormatCurrency(12345.6789,,,,False) $12345.68
FormatCurrency(-12345.6789) ($12,345.68)
FormatCurrency(-12345.6789,,,False) -$12,345.68
FormatCurrency(.6789) $0.68
FormatCurrency(.6789,,False) $.68
FormatCurrency(-.6789,,False,False) -$.68
Formatting Numbers
The FormatNumber() function returns a value formatted as a number. Its general format is shown below.
FormatNumber(value [, trailing digits] [, leading digit] [, parentheses] [, group digits])
value is any expression that produces a number;

trailing digits is an integer giving the number of digits following the decimal point; the default is rounding to 2 digits;

leading digit is True or False to indication whether a leading 0 is to appear before the decimal point for fractional values;

parentheses is True or False to indicate whether negative numbers should be displayed inside parentheses;

group digits is True or False to indicate whether numbers should be grouped between commas.
Format Output
FormatNumber(12345.6789) 12,345.68
FormatNumber(12345.6789,5) 12,345.67890
FormatNumber(12345.6789,,,,False) 12345.68
FormatNumber(-12345.6789) -12,345.68
FormatNumber(-12345.6789,,,True) (12,345.68)
FormatNumber(.6789) 0.68
FormatNumber(.6789,,False) .68
FormatNumber(-.6789,4) -0.6789
Formatting Percentages
The FormatPercent() function returns a value formatted as a percentage, that is, multiplied by 100 with a trailing % character. Its general format is shown below.
FormatPercent(value [, trailing digits] [, leading digit] [, parentheses] [, group digits])
value is any expression that produces a number;

trailing digits is an integer giving the number of digits following the decimal point; the default is rounding to 2 digits;

leading digit is True or False to indication whether a leading 0 is to appear before the decimal point for fractional values;

parentheses is True or False to indicate whether negative numbers should be displayed inside parentheses;

group digits is True or False to indicate whether numbers should be grouped between commas.
Format Output
FormatPercent(.6789) 67.89%
FormatPercent(.6789,4) 67.8900%
FormatPercent(-.6789) -67.89%
FormatPercent(-.6789,,,True) (67.89%)
Formatting Dates and Times
The FormatDateTime() function returns a string expression representing a date/time value. Its general format is
FormatDateTime(value [, DateFormat.format])
where value is a date or time value and format is one of the following values: GeneralDate, LongDate, ShortDate, LongTime, or ShortTime.
Format Output
FormatDateTime(Now) 8/22/2007 4:50:38 AM
FormatDateTime(Today) 8/22/2007
FormatDateTime(TimeOfDay) 4:50:38 AM
FormatDateTime(Now,DateFormat.LongDate) Wednesday, August 22, 2007
FormatDateTime(Today,DateFormat.LongDate) Wednesday, August 22, 2007
FormatDateTime(Now,DateFormat.ShortDate) 8/22/2007
FormatDateTime(Today,DateFormat.ShortDate) 8/22/2007
FormatDateTime(Now,DateFormat.LongTime) 4:50:38 AM
FormatDateTime(TimeOfDay,DateFormat.LongTime) 4:50:38 AM
FormatDateTime(Now,DateFormat.ShortTime) 04:50
FormatDateTime(TimeOfDay,DateFormat.ShortTime) 04:50
The Format() Function
The Format() function is a general-purpose formatting function that returns a string value formatted according to a format string. The format strings duplicate numeric and date/time formats produced by the specialized formats described above. The general format for applying the Format() function is shown below.
Format(value, "format string")
Formatting Numbers
A format string for numeric values can use one of the predefined string values shown in the following table.
String Description
General Number|G|g Displays number with no thousand separator.
Currency|C|c Displays number with thousand separator, if appropriate; display two digits to the right of the decimal separator.
Fixed|F|f Displays at least one digit to the left and two digits to the right of the decimal separator.
Standard|N|n Displays number with thousand separator, at least one digit to the left and two digits to the right of the decimal separator.
Percent Displays number multiplied by 100 with a percent sign (%) appended immediately to the right; always displays two digits to the right of the decimal separator.
P|p Displays number with thousandths separator multiplied by 100 with a percent sign (%) appended to the right and separated by a single space; always displays two digits to the right of the decimal separator.
Examples of applying a format string to numeric values are shown in the following table.
Format Output
Format(12345.6789,"g") 12345.6789
Format(12345.6789,"c") $12,345.68
Format(12345.6789,"f") 12345.68
Format(12345.6789,"n") 12,345.68
Format(-12345.6789,"g") -12345.6789
Format(-12345.6789,"c") ($12,345.68)
Format(-12345.6789,"f") -12345.68
Format(-12345.6789,"n") -12,345.68
Format(.6789,"Percent") 67.89%
Format(.6789,"p") 67.89 %
Format(-.6789,"Percent") -67.89%
Format(-.6789,"p") -67.89 %
Formatting Dates and Times
A format string for date/time values can use one of the predefined string values shown in the following table.
String Description
Long Date|D Displays a date in long date format.
Short Date|d Displays a date in short date format.
Long Time|T Displays a date in long date format.
Short Time|t Displays a date in short date format.
F Displays the long date and long time.
f Displays the long date and short time.
g Displays the short date and short time.
M|m Displays the month and the day of a date.
Y|y Formats the date as the year and month.
Examples of applying a format string to date/time values are shown in the following table.
Format Output
Format(Now,"D") Wednesday, August 22, 2007
Format(Now,"d") 8/22/2007
Format(Now,"T") 4:50:38 AM
Format(Now,"t") 4:50 AM
Format(Now,"F") Wednesday, August 22, 2007 4:50:38 AM
Format(Now,"f") Wednesday, August 22, 2007 4:50 AM
Format(Now,"g") 8/22/2007 4:50 AM
Format(Now,"m") August 22
Format(Now,"y") August, 2007
User-defined Numeric Formats
Formats can be defined for displaying numeric values by composing a string to describe the format. This user-defined string is applied through the Format() function. The characters shown in the following table are used to compose the format string.
Character Description
0 Digit placeholder. Displays a digit or a zero. If the value has a digit in the position, then it displays; otherwise, a zero is displayed.
# Digit placeholder. Displays a digit or a space. If the value has a digit in the position, then it displays; otherwise, a space is displayed.
. Decimal placeholder; determines how many digits are displayed to the left and right of the decimal separator.
, Thousand separator; separates thousands from hundreds within a number that has four or more places to the left of the decimal separator. Only a single "," is required in the format, between the first set of digit placeholders.
% Percent placeholder. Multiplies the expression by 100. The percent character (%) is inserted in the position where it appears in the format string.
- + $ ( ) Literal characters; displayed exactly as typed in the format string.
Examples of applying a user-defined format string to numeric values are shown in the following table.
Format Output
Format(012345.6789,"0.00") 12345.68
Format(012345.6789,"0,0.000") 12,345.679
Format(012345.6789,"00000,0.000000") 012,345.678900
Format(012345.6789,"#.") 12345.68 Format(012345.6789,"#,#.") 12,345.68
Format(012345.6789,"$ #,#.") $ 12,345.68 Format(-012345.6789,"#,#.") -12,345.6789 Format(-012345.6789,"$#,#.") -$12,345.68
Format(.6789,"#,#.##") .68
Format(.6789,"0,0.000") 00.679
Format(-.6789," 0.0000") - 0.6789
Format(.6789,"0.00%") 67.89%
User-defined Date/Time Formats
Formats can be defined for displaying date and time values by composing a string to describe the format. This user-defined string is applied through the Format() function. The characters shown in the following table are used to compose the date/time format string.
Character Description
: Time separator.
/ - Date separators.
% Precedes a single-character format string.
d Displays the day as a number without a leading zero.
dd Displays the day as a number with a leading zero.
ddd Displays the day name as an abbreviation.
dddd Displays the day as a full name.
M Displays the month as a number without a leading zero.
MM Displays the month as a number with a leading zero.
MMM Displays the month name as an abbreviation.
MMMM Displays the month as a full name.
yy Displays the year in two-digit format.
yyyy Displays the year in four-digit format.
h Displays the hour as a number without leading zeros using the 12-hour clock.
hh Displays the hour as a number with leading zeros using the 12-hour clock.
H Displays the hour as a number without leading zeros using the 24-hour clock.
HH Displays the hour as a number with leading zeros using the 24-hour clock.
m Displays the minute as a number without leading zeros.
mm Displays the minute as a number with leading zeros.
s Displays the seconds as a number without leading zeros.
ss Displays the seconds as a number with leading zeros.
f... Displays fractions of seconds using up to 7 characters to display fractional digits.
tt Uses the 12-hour clock and displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M.
Additional characters and punctuation marks can be used within the format string. Characters that match any of the formatting characters must be preceded by "\".
Examples of applying a user-defined format string to date/time values are shown in the following table.
Format Output
Format(Now,"M/d/yy") 8/22/07
Format(Now,"M-d-yyyy") 8-22-2007
Format(Now,"d-MMMM-yy") 22-August-07
Format(Now,"d MMMM, yyyy") 22 August, 2007
Format(Now,"MMMM d, yyyy") August 22, 2007
Format(Now,"MMMM, yyyy") August, 2007
Format(Now,"%d") 22
Format(Now,"h:m tt") 4:50 AM
Format(Now,"h:m:ss tt") 4:50:38 AM
Format(Now,"H:m") 4:50
Format(Now,"M/d/yy - h:mtt") 8/22/07 - 4:50AM
Format(Now,"H:m:ss.fffffff") 4:50:38.1853725
Format(Now,"To\da\y i\s MMMM d, yyyy.") Today is August 22, 2007.

Comments

Know your identity

2007-06-29T12:10:54+02:00

Know your Identity

Often we create tables that have identity columns. An identity column is a special column in SQL Server that can automatically take on values when an insert operation is done on the table. What if you want to find out whether a table contains an identity column?? SQL Server stores this information in the syscolumns table, but you can access this information in many ways. In this article, we will see the various ways to access information about whether a table has an identity column.

Before we start off with the methods, let's first create a table that has an identity column, like this:

CREATE TABLE samp_test
(
colA INT IDENTITY,
colB VARCHAR(10)
)

Method A

In the first method, we will fetch this information from the syscolumns table. This table contains a column called status that contains various values. If the value in this column contains hexadecimal 80, then it means that the corresponding column contains an identity column. Here is how we can check this:

IF (EXISTS (SELECT name FROM syscolumns WHERE
OBJECT_NAME(id) = 'samp_test' AND status = 0x80))
PRINT 'Table has an identity column'
ELSE
PRINT 'Table does not have an identity column'

Method B

In the second method, we will see whether a table has an identity column using the COLUMNPROPERTY function. This function takes an argument IsIdentity that indicates whether the column is an identity column. Here is how we can do the check using this function:

IF (EXISTS (SELECT myTemp.IsIdentity FROM
(SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = 'samp_test') AS myTemp
WHERE myTemp.IsIdentity = 1))
PRINT 'Table has an identity column'
ELSE
PRINT 'Table does not have an identity column'

This method is slightly convoluted since the COLUMNPROPERTY function takes the name of the column as the parameter. Thus, if we want to make a generic sort of a call, we need to iterate through each column of a table using the syscolumns table and then make a call to the COLUMNPROPERTY function. Finally we then run an EXISTS check over this derived table to check for identity.

Method C

In the final method, we will use the OBJECTPROPERTY function to check whether a table has an identity column. Here is how we can do the check using this function:

IF (OBJECTPROPERTY(OBJECT_ID('samp_test'), 'TableHasIdentity') = 1)
PRINT 'Table has an identity column'
ELSE
PRINT 'Table does not have an identity column'

This is the easiest of the lot. The OBJECTPROPERTY function takes as parameter the name of the object to check (a table in this case) and then the name of the property. We use the OBJECT_ID function to get the ID of the object. We then use the TableHasIdentity property to check if the table has an identity column. One difference between this method and the others is that this method does not give the name of the column which is the identity column, which is possible using the other two methods.

Comments

SQL Date Time

2007-06-29T12:07:50+02:00

DECLARE @Date datetime
SET @Date = '2001/08/31'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'

==============================================================================================================

--To get the first day of month and day name by any date
Declare @pInputDate Datetime
set @pInputDate=getdate()- 40
set @pInputDate= CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
--Will give First date of the month

--Will give day of the date
select @pInputDate AS First_Day_of_mth, datename(dw,@pInputDate) AS Day_Name
==============================================================================================================
Hi,

here is the Table:

Id Saledate Sales
1 2 Jan 2006 100
1 13 Jan 2006 200
1 14 Feb 2006 300
2 3 Feb 2006 150
2 4 Feb 2006 200
3 5 Jan 2006 300
3 13 Feb 2006 400

I need the following output?

Id Jan Feb Mar Apr
1 300 300 0 0
2 0 350 0 0
3 300 400 0 0

--Here is the result:

SELECT ID, SUM(CASE WHEN MONTH(saledate) = 1 THEN sales ELSE 0 END) AS 'Jan',
SUM(CASE WHEN MONTH(saledate) = 2 THEN ISNULL(sales,0) ELSE 0 END) AS 'Feb',
SUM(CASE WHEN MONTH(saledate) = 3 THEN ISNULL(sales,0) ELSE 0 END) AS 'Mar',
SUM(CASE WHEN MONTH(saledate) = 4 THEN ISNULL(sales,0) ELSE 0 END) AS 'Apr'
FROM sale
GROUP BY ID
--Here it is shown only for 4 months. You can extend this query for complete year.

-- It gives maximum sales in each month
select id as ID ,
MAX(case when month(saledate) =1 then isnull(sales,0) else 0 end) as 'Jan',
MAX(case when month(saledate) = 2 then isnull(sales,0) else 0 end) as 'Feb',
Max(case when month(saledate) = 3 then isnull(sales,0) else 0 end) as 'Mar',
Max(case when month(saledate) = 4 then isnull(sales,0) else 0 end) as 'Apr',
Max(case when month(saledate) = 5 then isnull(sales,0) else 0 end) as 'May',
Max(case when month(saledate) = 6 then isnull(sales,0) else 0 end) as 'June',
Max(case when month(saledate) = 7 then isnull(sales,0) else 0 end) as 'July',
Max(case when month(saledate) = 8 then isnull(sales,0) else 0 end) as 'Aug',
Max(case when month(saledate) = 9 then isnull(sales,0) else 0 end) as 'Sept',
Max(case when month(saledate) = 10 then isnull(sales,0) else 0 end) as 'Oct',
Max(case when month(saledate) = 11 then isnull(sales,0) else 0 end) as 'Nov',
Max(case when month(saledate) = 12 then isnull(sales,0) else 0 end) as 'Dec'
from sale
group by month(saledate),id

SELECT
DAY('5/5/2007'),
MONTH('5/1/2007'),
YEAR('5/1/2007'),
DATEPART(DAY, '1/5/2007'),
DATEPART(MONTH, '5/1/2007'),
DATEPART(YEAR, '1/1/2007')

Day MOnth year datepart_Day datepart_MOnth datepart_year
======-- ======-- ======-- ======--- ======----- ======----
5 5 2007 5 5 2007

SELECT
GETDATE() AS local_date,
GETUTCDATE() AS UTC_date

local_date UTC_date
============----- ============-----
2007-05-08 19:12:50.687 2007-05-08 13:42:50.687

SELECT DATEADD(MONTH, 6, GETDATE())AS '6_months_from_now'

6_months_from_now
============-----
2007-11-08 19:12:50.687
==============================================================================================================
declare @datevar datetime
select @datevar = getdate()

/*Example for getdate() : getting current datetime*/
select getdate() [Current Datetime]

/*Example for dateadd : getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]

/*Example for datediff : getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]

/*Example for datename : getting month name*/
select datename(mm, @datevar) [Month Name]

/*Example for datepart : getting week from date*/
select datepart(wk, @datevar ) [Week No]

/*Example for day : getting day part of date*/
select day (@datevar) [Day]

/*Example for month : getting month part of date*/
select month(@datevar) [Month]

/*Example for year : getting year part of date*/
select year(@datevar) [Year]

/* Getting the Day Name like monday tuesday... */
SELECT DATENAME(dw, GETDATE())
-- or
/* 0-monday,1-tuesday,2-wednesday ....7-monday,8-tuesday... */
SELECT DATENAME(dw, 0)

==============================================================================================================

Comments

Links

2007-05-04T12:05:09+02:00

ASP.NET Resources
www.asp.net
msdn-asp.net/
wikipedia
www.w3schools.com
forums.asp.net
ajax.asp.net
www.codeproject.com/aspnet/
www.411asp.net
aspnet.4guysfromrolla.com
www.123aspx.com
www.gotdotnet.com
www.hotscripts.com
www.developerfusion.co.uk
www.aspnetpro.com
www.asp-net.com
www.aspnetfaq.com
www.aspspider.net
www.aspdeveloper.net
www.aspcode.net
www.programmersheaven.com
www.15seconds.com
www.learnasp.com
aspnetpodcast.com
aspnetresources.com
www.devasp.net
www.codeplex.com
www.codeguru.com
C# Resources
Msdn-C#
Msdn
www.gotdotnet.com
www.c-sharpcorner.com
www.csharphelp.com
en.wikipedia.org/wiki/C_Sharp
www.csharpfriends.com/
www.csharp-station.com
www.codeproject.com
www.mastercsharp.com
www.csharp-source.net
www.codehound.com
www.developerfusion.co.uk
http://csharpcomputing.com
www.developersdex.com
www.planet-source.code.com
www.csharp-home.com
SQL Server Resource
msdn.microsoft.com/sql/express/
SQL Server 2005
www.sql-server-perforamance.com
www.sqlskills.com
www.technetevents.com
www.sqlspider.net
www.aspspider.net
www.sqlservercentral.com
www.sswugorg.com
vyaskn.tripod.com
www.databasejournal.com
www.microsoft.com/sql
www.dataschenk.com/
www.mcdbadirectory.com/
www.programmingmsaccess.com
www.umachandar.com
www.asksql.com
www.dbarecovery.com
www.karaszi.com
www.sqlteam.com
www15.brinkster.com/shakhov/
www.1sqlstreet.com
cindygross.tripod.com/
www.programmersheaven.com/
www.dbbest.com/
sql.manivesa.com
www.mugh.net/sql
www.microsoft.com/data
www.easy-sql-server.com/
www.sqlmag.com/
www.msde.biz/
www.mssqlcity.com/
www.sqlserverportal.com/
www.sqlswiss.com
www.expertexchange.com

Comments

Dynamic Management Views [DMV] – A SQL Server 2005 Feature

2007-04-10T07:58:55+02:00

The DMV’s; newly introduced in SQL Server 2005 gives database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. DMV’s are designed to be used instead of system tables and various other functions provided in SQL Server 2000.

Two types of dynamic management views:-

Server-scoped DMV: Stored in Master Database
Database-scoped DMV: Specific to each database

Permission to Execute DMV [Security]
To query a Server scoped DMV the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV the user must have SELECT privilege on VIEW DATABASE STATE.

GRANT VIEW SERVER STATE to
GRANT VIEW DATABASE STATE to

If you want to deny a user permission to query certain DMV’s you can use the DENY command to restrict access to specific DMV.

Comments

DBCC DBTABLE

2007-04-09T07:47:22+02:00

This command prints out the contents of the DBTABLE structure.

Syntax:

DBCC DBTABLE ({dbid|dbname})

where
dbid|dbname - database name or database ID

This is the example:

DBCC TRACEON (3604)
DBCC DBTABLE (master)

The DBTABLE structure has an output parameter called dbt_open.
This parameter keeps track of how many users are in the database.

Comments

DBCC DBINFO

2007-04-09T07:46:20+02:00

Print DBINFO structure for specified database.

Syntax:

DBCC DBINFO [( dbname )]

where
dbname - is the database name.

This is the example:

DBCC TRACEON (3604)
DBCC DBINFO (master)

Comments

DBCC BYTES

2007-04-09T07:45:38+02:00

This command can be used to dump out bytes from a specific address.

Syntax:

dbcc bytes ( startaddress, length )

where
startaddress - starting address to dump
length - number of bytes to dump

This is the example:

DBCC TRACEON (3604)
dbcc bytes (10000000, 100)

Comments

DBCC BUFFER

2007-04-09T07:44:24+02:00

This command can be used to print buffer headers and pages from the
buffer cache.

Syntax:

dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])

where
dbid|dbname - database id|database name.
objid|objname - object id|object name
nbufs - number of buffers to examine
printopt - print option
0 - print out only the buffer header and page header
(default)
1 - print out each row separately and the offset table
2 - print out each row as a whole and the offset table

This is the example:

DBCC TRACEON (3604)
dbcc buffer(master,'sysobjects')

Comments