Archive for the ‘SQL Function’ Category

SQL Server – SERVERPROPERTY Function

SERVERPROPERTY Function

How does one get list all Server Properties? Well, we can use a very helpful system function SERVERPROPERTY. It will return information about the server instance for a specified property.

Syntax:

SERVERPROPERTY (propertyname)

I have kept the following script in my archives, you may do the same.

— Returns all the property information about the server instance

 

SELECT ‘BuildClrVersion’ AS ‘Server Property’, SERVERPROPERTY(‘BuildClrVersion’) AS ‘Value’

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’)

UNION ALL

SELECT ‘FilestreamShareName’, SERVERPROPERTY(‘FilestreamShareName’)

UNION ALL

SELECT ‘FilestreamConfiguredLevel’, SERVERPROPERTY(‘FilestreamConfiguredLevel’)

UNION ALL

SELECT ‘FilestreamEffectiveLevel’, SERVERPROPERTY(‘FilestreamEffectiveLevel’)

If you want to check what all these different Server Property means, please also see http://msdn.microsoft.com/en-us/library/ms174396.aspx

Good Luck!

SQL Server – Set Functions – COUNT, MAX, MIN, SUM and AVG

Set Functions – COUNT, MAX, MIN, SUM and AVG

Many times the information we require from table doesn’t relate to individual rows but to set of rows. In such case we can use Set functions provided by the SQL Server. These function are COUNT, MAX, MIN, SUM and AVG. Set functions operate on groups of rows.

1. COUNT

The COUNT function returns the rows count in the table.  When used with WHERE clause, only number of rows which satisfy the condition will be returned. The DISTINCT can be used to get the count of rows with unique values.

Example

USE AdventureWorks

— Calculates the row count of the table

SELECT COUNT(*) FROM Purchasing.PurchaseOrderDetail

— Calculates the row count which satisfy the condition

SELECT COUNT(*) FROM Purchasing.PurchaseOrderDetail WHERE PurchaseOrderID=18

— Calculates the number of non-null values for that column

SELECT COUNT(ProductID) FROM Purchasing.PurchaseOrderDetail

— Calculates the number of distinct non-null values for that column

SELECT COUNT(DISTINCT(ProductID)) FROM Purchasing.PurchaseOrderDetail

2. MAX

The MAX function returns the maximum value in the specified column. The MAX function can also be used as the part of sub-query.

Example

— Finds the maximum value for the column

SELECT MAX(OrderQty) FROM Purchasing.PurchaseOrderDetail

— It can be used as part of subquery

— Displays all the records having maximum OrderQty

SELECT * FROM Purchasing.PurchaseOrderDetail WHERE OrderQty =

(SELECT MAX(OrderQty) FROM Purchasing.PurchaseOrderDetail)

3. MIN

The MIN function returns the minimum value in the specified column. The MIN function can also be used as the part of sub-query.

Example

— Finds the minimum value for the column

SELECT MIN(OrderQty) FROM Purchasing.PurchaseOrderDetail

— It can be used as part of subquery

— Displays all the records having minimum OrderQty

SELECT * FROM Purchasing.PurchaseOrderDetail WHERE OrderQty =

(SELECT MIN(OrderQty) FROM Purchasing.PurchaseOrderDetail)

4. SUM

The SUM function adds up the values in a specified column. The column must be of one of numeric data types. The DISTINCT can be used to get the sum of only unique values.

Example

— Finds the total sum for the column

SELECT SUM(OrderQty) FROM Purchasing.PurchaseOrderDetail

— Finds the sum of distinct values for the column

SELECT SUM(DISTINCT(OrderQty)) FROM Purchasing.PurchaseOrderDetail

5. AVG

The AVG function returns the average of all the values in the specified column. As with SUM function, the column must be of one of numeric data types. The DISTINCT can be used to get the average of only unique values.

Example

— Finds the average for the column

SELECT AVG(OrderQty) FROM Purchasing.PurchaseOrderDetail

— Finds the average of distinct values for the column

SELECT AVG(DISTINCT(OrderQty)) FROM Purchasing.PurchaseOrderDetail

Good Luck!

SQL Server – Function to split comma delimited String or List

Function to split a string which is delimited by comma or other delimiter.

We often come across a problem when the all values needed are supplied as a list in a single string, generally separated by comma, hyphen, blank space or other delimiter. What is required is to split the sting and extract each value from it for us to be of any use in SQL statements.

Eg.:  ‘C104, C584, C875, C946’

Following is the function dbo.Split1, which will take two inputs – string to be split and the delimiter used in that string. It will return the all the components of the string in form of table.

ALTER FUNCTION [dbo].[Split1]

(

@List NVARCHAR(1000),   — String of values

@SplitOn NVARCHAR(5)    — delimiter value

)

RETURNS @RtnValue TABLE

(

Id INT IDENTITY(1,1),

Component NVARCHAR(50)

)

AS

BEGIN

WHILE (CHARINDEX(@SplitOn,@List)>0)

BEGIN

INSERT INTO @RtnValue (Component)

SELECT Component = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))

SELECT @List = SUBSTRING(@List, CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@list))

END

INSERT INTO @RtnValue (Component)

SELECT Component = LTRIM(RTRIM(@List))

RETURN

END

GO

You can directly use the above function in a simple SELECT statement as follows:

SELECT CONVERT(VARCHAR,Component) AS ‘String Components’ FROM dbo.Split1(‘one,2,teen’,‘,’)

You can also pass a string variable to the function.

SELECT CONVERT(VARCHAR,Component) AS ‘String Components’ FROM dbo.Split1(@myList,‘,’)

It is most useful as a part of WHERE IN clause, which can be part a complex SQL queries.

SELECT * FROM Customer WHERE City IN (SELECT CONVERT(VARCHAR,Component) FROM dbo.Split1(@CityList,‘,’)

Good Luck