Feed on
Posts
Comments

Archive for the ‘SQL Function’ Category

SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice - Part 2

Posted in Author Pinal, Best Practices, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 18, 2008 | 1 Comment »

A year ago I wrote post about SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice where I have discussed two different methods of getting datepart from datetime.
Method 1:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
Method 2:
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
I have summarized my post suggesting that either method works fine and I prefer to [...]

Read Full Post »

SQL SERVER - Get Numeric Value From Alpha Numeric String - UDF for Get Numeric Numbers Only

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 14, 2008 | 4 Comments »

SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote few days ago, which will return only Numeric values from AlphaNumeric values.
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, ” )
SET [...]

Read Full Post »

SQL SERVER - 2008 - Enhenced TRIM() Function - Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on October 10, 2008 | 5 Comments »

After reading my article SQL SERVER - 2008 - TRIM() Function - User Defined Function, I have received email and comments where user are asking if it is possible to remove trailing spaces, leading spaces, white space, tabs, carriage returns, line feeds etc.
I found following script posted by Russ and Erik. It is modified a [...]

Read Full Post »

SQL SERVER - 2008 - TRIM() Function - User Defined Function

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 9, 2008 | 4 Comments »

I just received following question in email by James Louren.
“How come SQL Server 2000, 2005 does not have function TRIM()? Is there any way to get similar results.
What about SQL Server 2008?”
James has asked very interesting question. I have previously wrote about SQL SERVER - TRIM() Function - UDF TRIM(). Today my answer is no [...]

Read Full Post »

SQL SERVER - Get Answer in Float When Dividing of Two Integer

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on September 26, 2008 | 6 Comments »

Many times we have requirements of some calculations amongst different fields in Tables. One of the software developers here was trying to calculate some fields having integer values and divide it which gave incorrect results in integer where accurate results including decimals was expected.
Something as follows,
Example,
USE [AdventureWorks]
GO
CREATE TABLE [dbo].ConvertExample(
[ID]          [int] NULL,
[Field1]    [int] NULL,
[Field2]    [int] NULL,
[Field3]    [...]

Read Full Post »

SQL SERVER - Guidelines and Coding Standards Complete List Download

Posted in Author Pinal, Best Practices, DBA, Data Warehousing, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL Documentation, SQL Download, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQLAuthority, T SQL, Technology on September 25, 2008 | 3 Comments »

SQL SERVER - Guidelines and Coding Standards complete List Download
Coding standards and guidelines are very important for any developer on the path of successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQL XML, SQLAuthority, T SQL, Technology on September 20, 2008 | 12 Comments »

Download SQL Server 2008 Interview Questions and Answers Complete List
Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 8

Posted in Author Pinal, Data Warehousing, Database, Main, SQL, SQL Authority, SQL Data Storage, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQL Utility, SQLAuthority, T SQL, Technology on September 19, 2008 | 1 Comment »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
What is Data Compression?
In SQL SERVE 2008 Data Compression comes in two flavors:

Row Compression
Page Compression

Row Compression
Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 6

Posted in Author Pinal, Database, Main, SQL, SQL Authority, SQL Data Storage, SQL DateTime, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, SQLAuthority, T SQL, Technology on September 17, 2008 | No Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
3) Questions of SQL SERVER 2008
What are the basic functions for master, msdb, model, tempdb and resource databases?
The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 4

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, SQLAuthority, T SQL, Technology on September 15, 2008 | 4 Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
What is SQL Server Agent?
SQL Server agent plays an important role in the day-to-day tasks of a [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 2

Posted in Author Pinal, Data Warehousing, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLAuthority, T SQL, Technology on September 13, 2008 | 9 Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
What is Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In [...]

Read Full Post »

SQL SERVER - Few Useful DateTime Functions to Find Specific Dates

Posted in Author Pinal, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 29, 2008 | 10 Comments »

Recently I have recieved email from Vivek Jamwal, which contains many useful SQL Server Date functions.
—-Today
SELECT GETDATE() ‘Today’
—-Yesterday
SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’
—-First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’
—-Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’
—-First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’
—-Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’
—-First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’
—-Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’
—-First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’
—-Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) ‘Last Day of Last Month’
—-First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’
—-Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) ‘Last Day of Current Year’
—-First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’
—-Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) ‘Last Day of Last Year’
ResultSet:
Today
———————–
2008-08-29 21:54:58.967
Yesterday
———————–
2008-08-28 21:54:58.967
First Day of Current Week
————————-
2008-08-25 00:00:00.000
Last Day [...]

Read Full Post »

SQL SERVER - UDF - Function to Convert Text String to Title Case - Proper Case - Part 2

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 22, 2008 | 1 Comment »

I had previously written SQL SERVER - UDF - Function to Convert Text String to Title Case - Proper Case and I had really enjoyed writing it. Above script converts first letter of each word from sentence to upper case.
For example
this function will convert this string to title case!
will be converted to
This Function Will Convert This String To Title Case!
However if you [...]

Read Full Post »

SQL SERVER - Get Date Time in Any Format - UDF - User Defined Functions

Posted in Author Pinal, Database, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on August 14, 2008 | 3 Comments »

One of the reader Nanda of SQLAuthority.com has posted very detailed script of converting any date time in desired format. I suggest every reader of this blog to save this script in your permanent code bookmark and use it when you need it.
Refer the function and get familiar yourself with different format this function support. [...]

Read Full Post »

SQL SERVER - Introduction to SERVERPROPERTY and example

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on June 16, 2008 | 4 Comments »

SERVERPROPERTY is very interesting system function. It returns many of the system values. I use it very frequently to get different server values like Server Collation, Server Name etc.
Run following script to see all the properties of server.
SELECT ’BuildClrVersion’ ColumnName, SERVERPROPERTY(’BuildClrVersion’) ColumnValue
UNION ALL
SELECT ’Collation’, SERVERPROPERTY(’Collation’)
UNION ALL
SELECT ’CollationID’, SERVERPROPERTY(’CollationID’)
UNION ALL
SELECT ’ComparisonStyle’, SERVERPROPERTY(’ComparisonStyle’)
UNION ALL
SELECT ’ComputerNamePhysicalNetBIOS’, SERVERPROPERTY(’ComputerNamePhysicalNetBIOS’) 
UNION ALL
SELECT ’Edition’, SERVERPROPERTY(’Edition’)
UNION ALL
SELECT ’EditionID’, SERVERPROPERTY(’EditionID’)
UNION ALL
SELECT ’EngineEdition’, SERVERPROPERTY(’EngineEdition’)
UNION ALL
SELECT ’InstanceName’, SERVERPROPERTY(’InstanceName’)
UNION ALL
SELECT ’IsClustered’, SERVERPROPERTY(’IsClustered’)
UNION ALL
SELECT ’IsFullTextInstalled’, SERVERPROPERTY(’IsFullTextInstalled’)
UNION ALL
SELECT ’IsIntegratedSecurityOnly’, SERVERPROPERTY(’IsIntegratedSecurityOnly’)
UNION ALL
SELECT ’IsSingleUser’, SERVERPROPERTY(’IsSingleUser’)
UNION ALL
SELECT ’LCID’, SERVERPROPERTY(’LCID’)
UNION ALL
SELECT ’LicenseType’, SERVERPROPERTY(’LicenseType’)
UNION ALL
SELECT ’MachineName’, SERVERPROPERTY(’MachineName’)
UNION ALL
SELECT ’NumLicenses’, SERVERPROPERTY(’NumLicenses’)
UNION ALL
SELECT ’ProcessID’, SERVERPROPERTY(’ProcessID’)
UNION ALL
SELECT ’ProductVersion’, SERVERPROPERTY(’ProductVersion’)
UNION ALL
SELECT ’ProductLevel’, SERVERPROPERTY(’ProductLevel’)
UNION ALL
SELECT ’ResourceLastUpdateDateTime’, SERVERPROPERTY(’ResourceLastUpdateDateTime’)
UNION ALL
SELECT ’ResourceVersion’, SERVERPROPERTY(’ResourceVersion’)
UNION ALL
SELECT ’ServerName’, SERVERPROPERTY(’ServerName’)
UNION ALL
SELECT ’SqlCharSet’, SERVERPROPERTY(’SqlCharSet’)
UNION ALL
SELECT ’SqlCharSetName’, SERVERPROPERTY(’SqlCharSetName’)
UNION ALL
SELECT ’SqlSortOrder’, SERVERPROPERTY(’SqlSortOrder’)
UNION ALL
SELECT ’SqlSortOrderName’, SERVERPROPERTY(’SqlSortOrderName’)

ResultSet:
ColumnName [...]

Read Full Post »

SQL SERVER - Create a Comma Delimited List Using SELECT Clause From Table Column

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on June 4, 2008 | 7 Comments »

I received following question in email :
How to create a comma delimited list using SELECT clause from table column?
Answer is to run following script.
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name
FROM Production.Product
SELECT @listStr
GO

I have previously written similar [...]

Read Full Post »

SQL SERVER - Example of DISTINCT in Aggregate Functions

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on June 3, 2008 | 1 Comment »

Just a day ago, I was was asked this question in one of the teaching session to my team members. One of the member asked me if I can use DISTINCT in Aggregate Function and does it make any difference.
Of course! It does make difference. DISTINCT can be used to return unique rows from a [...]

Read Full Post »

SQL SERVER - UNPIVOT Table Example

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology, tagged , on May 29, 2008 | 1 Comment »

My previous article SQL SERVER - PIVOT Table Example encouraged few of my readers to ask me question about UNPIVOT table. UNPIVOT table is reverse of PIVOT Table.
USE AdventureWorks
GO
CREATE TABLE #Pvt ([CA] INT NOT NULL,
                [AZ] INT NOT NULL,
                [TX] INT NOT NULL);
INSERT INTO #Pvt ([CA], [AZ], [TX])
    SELECT [CA], [AZ], [TX]
    FROM
    (
    SELECT sp.StateProvinceCode
        FROM Person.Address a
                INNER JOIN Person.StateProvince sp
                ON a.StateProvinceID = sp.StateProvinceID
    ) p
            PIVOT
    (
                COUNT (StateProvinceCode)
                FOR StateProvinceCode
                IN ([CA], [AZ], [TX])
    ) AS pvt;
SELECT StateProvinceCode, Customer_Count
    FROM
    (
    SELECT [CA], [AZ], [TX]
        FROM #Pvt
    ) t
            UNPIVOT
    (
                Customer_Count
                FOR StateProvinceCode
                IN ([CA], [AZ], [TX])
    ) AS unpvt;
DROP TABLE #Pvt;
GO

Reference : Pinal Dave (http://www.SQLAuthority.com)

Read Full Post »

SQL SERVER - SQL SERVER - UDF - Get the Day of the Week Function - Part 3

Posted in Author Pinal, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 27, 2008 | 1 Comment »

Datetime functions and stored procedures always interests me. Nanda Kumar has suggested modification to previous written article about SQL SERVER - SQL SERVER - UDF - Get the Day of the Week Function - Part 2. He has improved on UDF.
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
            AS
    BEGIN
    DECLARE @rtDayofWeek VARCHAR(10)
    DECLARE @weekDay INT
        —-Here I have subtracted 7 For keeping Sunday as the First day like wise for Monday we need to subtract 2 and so on
        SET @weekDay=((DATEPART(dw,@dtDate)+@@DATEFIRST-7)%7)
    SELECT @rtDayofWeek = CASE @weekDay
                    WHEN 1 THEN ’Sunday’
                    WHEN 2 THEN ’Monday’
                    WHEN 3 THEN ’Tuesday’
                    WHEN 4 THEN ’Wednesday’
                    WHEN 5 THEN ’Thursday’
                    WHEN 6 THEN ’Friday’
                    WHEN 0 THEN ’Saturday’
        END
    RETURN (@rtDayofWeek)
    END
GO
SELECT dbo.udf_dayofweek(GETDATE())

Reference : Pinal Dave (http://www.SQLAuthority.com), Nanda Kumar

Read Full Post »

SQL SERVER - 2005 - Twelve Tips For Optimizing Sql Server 2005 Query Performance

Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, T SQL, Technology on May 21, 2008 | 12 Comments »

I recently came across very nice article about optimization tips for SQL Server 2005. Here is the list of those 12 tips.
Twelve Tips For Optimizing Sql Server 2005 Query Performance
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old [...]

Read Full Post »

Older Posts »