Calling MS SQL Server user defined functions with default parameters

User Defined Function Call:

SELECT *
FROM udf_TestDef(1, DEFAULT)

Yes!! You have to insert the term default literary!

User Defined Function Sample:

CREATE FUNCTION [dbo].[udf_TestDef]
(
@Param INTEGER
@IsBigContainerPhoto BIT = 0
)
RETURNS TABLE
AS
RETURN
(
.
.
.

Generating a sequence of numbers using SQL Select statement

SELECT (Tens.N + Units.N) NN
FROM
(
SELECT 0 N
UNION SELECT 1 N
UNION SELECT 2 N
UNION SELECT 3 N
UNION SELECT 4 N
UNION SELECT 5 N
UNION SELECT 6 N
UNION SELECT 7 N
UNION SELECT 8 N
UNION SELECT 9 N
)Units
CROSS JOIN
(
SELECT 0 N
UNION SELECT 10 N
--UNION SELECT 20 N
--UNION SELECT 30 N
--UNION SELECT 40 N
--UNION SELECT 50 N
--UNION SELECT 60 N
--UNION SELECT 70 N
--UNION SELECT 80 N
--UNION SELECT 90 N
)Tens

-----
NN
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Thanks for:
http://sqlmag.com/t-sql/generating-sequences-t-sql

SQL: Get aggregates for multiple values from the same table

Table Doc

ID Version

--  --

1  1

1  2

1  3*

2  1

2  2*

3  1

3  2*

4  1

4  2

4  3

4  4*

5  1*

To get maximum versions for each document

SELECT D.ID, D.Version

FROM Doc D 

INNER JOIN (SELECT ID, MAX(Version) FROM Doc GROUP BY ID) DM ON D.ID = DM.ID

Thanks Dinos for the inspiration!

To maintain MS SQL column identity on delete

- Disable the identity for the specific column
- Delete
- Enable the identity for that same column

Thanks Dinos for the tip!

Print Date and Time Using TSQL

PRINT LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 121), '-','_'),':','_'),' ','_'), 16)

16 characters on the LEFT only to omit extra time details.

TSQL Insert multiple values using a single insert statement

INSERT INTO TargetTable (Column1, Column2)
SELECT 'Value1', 'Value1'
UNION
SELECT 'Value2', 'Value2'
UNION
SELECT Column1, Column2
FROM SourceTable

Identifying Active directory group members using TSQL on MS SQL Server

exec master.dbo.xp_logininfo 'DomainName\GroupName','members'

Prerequisites: Requires membership in the sysadmin role for the database server.
Requires EXECUTE permission for this stored procedure within the master database.

P.S.
By experiments, I  noticed that I could enumerate only members for groups to which I belong.

TSQL update sample using Join (Update multiple rows)

Update PA
SET PA.ActionID= A.ID
FROM ActionDates PA
LEFT JOIN Action A ON PA.Action = A.Name
AND PA.PhaseID=A.PhaseID

TSQL: Create Date

CAST(
CAST(2012 AS varchar) + '-' +
CAST(9 AS varchar) + '-' +
CAST(17 AS varchar) AS DATETIME)

TSQL Not Equal

!=

TSQL Unique Constraint

Samples:

ALTER TABLE [PermissionsRequest] ADD CONSTRAINT UNIQUE_EMail UNIQUE (EMail)

ALTER TABLE [Schedule] ADD CONSTRAINT UNIQUE_Registration UNIQUE ( RoundNo, ClassNo)

Calculate Age using TSQL

This is a modified version from the original post by Michael Valentine Jones Yak DBA Kernel 


CREATE function [dbo].[fn_Age](@START_DATE datetime, @END_DATE datetime)
returns  varchar(9) 
as
-- Original version @http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
-- Calculates age in years, months and days from @START_DATE through @END_DATE and
-- Returns the age in format YYY MM DD.

-- Years is the number of full years between @START_DATE and @END_DATE.
-- Months is the number of full months since the last full year anniversary.
-- Days is the number of days since the last full month anniversary.
BEGIN
--([dbo].[fn_Age](isnull([DOB],getdate()),getdate()))

declare @AGE varchar(9)

declare @AGE_YEARS int
declare @AGE_MONTHS int
declare @AGE_DAYS int

-- Return null if @START_DATE > @END_DATE
IF @START_DATE > @END_DATE BEGIN RETURN @AGE END

SELECT @AGE_YEARS = AGE_YEARS,
@AGE_MONTHS = AGE_MONTHS,
@AGE_DAYS = datediff(dd, dateadd(mm, AGE_MONTHS, dateadd(yy, AGE_YEARS, START_DATE)),END_DATE)
FROM (SELECT AGE_MONTHS = CASE WHEN AnniversaryThisMonth <= END_DATE
THEN datediff(mm,dateadd(yy,AGE_YEARS,START_DATE),END_DATE)
ELSE datediff(mm,dateadd(yy,AGE_YEARS,START_DATE),END_DATE)-1
END, *
FROM (SELECT AGE_YEARS = CASE WHEN AnniversaryThisYear <= END_DATE
THEN datediff(yy,START_DATE,END_DATE)
ELSE datediff(yy,START_DATE,END_DATE)-1
END, *
FROM (SELECT AnniversaryThisYear = dateadd(yy,datediff(yy,START_DATE,END_DATE),START_DATE),
AnniversaryThisMonth = dateadd(mm,datediff(mm,START_DATE,END_DATE),START_DATE), *
FROM (Select START_DATE = dateadd(dd,datediff(dd, 0, @START_DATE),0), END_DATE = dateadd(dd, datediff(dd, 0, @END_DATE),0)) Temp4
) Temp3
) Temp2
) Temp1

SELECT @AGE = right('000'+convert(varchar(4),@AGE_YEARS),3) + ' ' +
right('00'+convert(varchar(4),@AGE_MONTHS),2) + ' ' +
right('00'+convert(varchar(4),@AGE_DAYS),2)

RETURN @AGE
END