FORMAT ( Transact-SQL)

 

Brief Description:      Returns a value formatted with the specified format and optional culture

Syntax:                       FORMAT ( value, format [, culture ] )

Availability:               All Versions of SQL Server 2012

Details:           Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general type conversions, continue to use CAST or CONVERT.

In the case of errors other than a culture that is not valid, such as a format that is not valid, FORMAT returns a null.

FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Acceptable data types for the value argument are listed in the following table, together with their .NET Framework mapping information:

Category	Type	.NET type
Numeric	bigint	Int64
Numeric	int	Int32
Numeric	smallint	Int16
Numeric	tinyint	Byte
Numeric	decimal	SqlDecimal
Numeric	numeric	SqlDecimal
Numeric	float	Double
Numeric	real	Single
Numeric	smallmoney	Decimal
Numeric	money	Decimal
Date and Time	date	DateTime
Date and Time	time	TimeSpan
Date and Time	datetime	DateTime
Date and Time	smalldatetime	DateTime
Date and Time	datetime2	DateTime
Date and Time	datetimeoffset	DateTimeOffset

 

Examples:

1.   

 

declare @d datetime = getdate();

select format( @d, 'dd/MM/yyyy', 'en-US' ) as fomatteddate

 

output

---------------

fomatteddate

01/04/2012

 

  2.

declare @a int = 1234

select format(@a, '0000000000') AS foramt_a

go

 

output

-----------

foramt_a

0000001234

 

 

Pros:  

 

·         Formatting Numeric values with prefix zeros

·         Formatting Dates on different cultures

CHOOSE ( Transact-SQL)

 

Brief Description:      Returns the item at the specified index from a list of values

Syntax:                       CHOOSE ( index, val_1, val_2 [, val_n ] )

Availability:               All Versions of SQL Server 2012

Details:                       CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned

 

Examples:

1.        

declare @a int = 1

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

 

output

------------

selectedmonth

Jan

 

set @a = 6

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

output

------------

selectedmonth

Jun

------------------------------------------------------------------------------------

2.

 

Following settings will all return null as output for out of bound index values

 

declare @a int

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

declare @a int = 0

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

declare @a int = 13

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

declare @a int = -1

select choose( @a,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as selectedmonth

go

 

 

 

output

------------

selectedmonth

NULL

 

 

 

 

Pros:  

 

·         Short hand expression for array of values

·         Conversion from Access to SQL Server is straight forward 

 

Cons:  

 

·         Index value NULL need to be handled

·         Out of bound values return NULL  

IIF ( Transact–SQL)

Brief Description:      Returns one of two values, depending on whether the Boolean expression evaluates to true or false

Syntax:                       IIF ( boolean_expression, true_value, false_value )

Availability:               All Versions of SQL Server 2012

Details:                       IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type

Examples:

1.

declare @a int = 1, @b int = 2, @c int = 3, @d int = 4, @e int = 5, @f int = 6, @g int = 7

select iif ( (iif (@b > @a,@c,@d) ) < @e, @f, @g) as iifresult

go

 

iifresult

6

----------------------------------------------------------------------------------------

2. 

-- Assigning constant NULLs to true_vale , false_value  will result in error

declare @a int = 1, @b int = 2

select iif ( @a < @b , null,null)  as iifresult

go

 

Msg 8133, Level 16, State 1, Line 3

At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.

-----------------------------------------------------------------------------------

 

3.

 

-- Assigning variables with NULLs to true_vale , false_value, output is NULL 

declare @a int = 1, @b int = 2, @c int = null, @d int = null

select @c = null, @d = null

 

select iif ( @a < @b , @c,@d ) as iifresult

go

 

iifresult

NULL

 

-----------------------------------------------------------------------------------

 

 

Pros:  

 

·         Shorthand for CASE when two conditions are involved

 

Cons:  

 

·         Assigning  NULL constant to true  or false value result in error

·         Assigning NULL values through variables to true or false value results in NULL output

sp_server_diagnostics

 

/*

Captures diagnostic data and health information about SQL Server to detect potential failures.

The procedure runs in repeat mode and sends results periodically. It can be invoked from either a regular or a DAC connection.

 

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

 

descriptions of the five components:

 

system: Collects data from a system perspective on spinlocks, severe processing conditions,

non-yielding tasks, page faults, and CPU usage. This information is produces an overall health state recommendation.

 

resource: Collects data from a resource perspective on physical and virtual memory, buffer pools, pages,

cache and other memory objects. This information produces an overall health state recommendation.

 

query_processing: Collects data from a query-processing perspective on the worker threads, tasks, wait types,

CPU intensive sessions, and blocking tasks. This information produces an overall health state recommendation.

 

io_subsystem: Collects data on IO. In addition to diagnostic data, this component produces a

clean healthy or warning health state only for an IO subsystem.

 

events: Collects data and surfaces through the stored procedure on the errors and events of

interest recorded by the server, including details about ring buffer exceptions,

ring buffer events about memory broker, out of memory, scheduler monitor, buffer pool,

spinlocks, security, and connectivity . Events will always show 0 as the state.

 

From a failure perspective, the system, resource, and query_processing components will be leveraged for failure detection while the io_subsystem and events components will be leveraged for diagnostic purposes only.

 

 

The following table maps the components to their associated health states.

 

Components	Clean (1)	Warning (2)	Error (3)	Unknowns (0)
system	x	x	x
resource	x	x	x
query_processing	x	x	x
io_subsystem	x	x
events				x

The (x) in each row represents valid health states for the component. For example, io_subsystem will either show as clean or warning. It will not show the error states.

 

 

*/

 

 

exec sp_server_diagnostics