SQL 2005: (Table) JOIN (Table Valued Function)

In SQL 2005, I had to write a Query in which a TVF(Table Valued Function) should be joined with a Table. The TVF had a Parameter which should be joined to the Table's Column
TVF - MyFunction(@ID)
Table - MyTable (with a Column ID)
Here the MyTable.ID should be joined with the MyFunction(@ID) to produce the results. The normal JOIN will not work in this case. This should be done by using CROSS APPLY feature:

SELECT * FROM MyTable T CROSS APPLY MyFunction(T.ID)
CROSS APPLY = Table INNER JOIN TVF

In case of LEFT JOINing use 'OUTER APPLY' like this:
SSELECT * FROM MyTable T OUTER APPLY MyFunction(T.ID)
OUTER APPLY = Table LEFT OUTER JOIN TVF

Note: No need to USE 'ON' clause

SQL Tip: Query to get Physical file size of a DB

SQL Query to get the Physical file size of a given Database:

select Name, size, maxsize, growth from sys.sysfiles

SQL Tip: Query to get Table Statistics

SQL Query to get the Table(s) Statistics like RowCount, CreatedDate, Page Size etc., on a given Database:

SELECT s.Name SchemaName
, o.Name TableName
, coalesce(i.Name, 'HEAP') IndexName
, p.used_page_count * 8 UsedPageCountInKB
, p.reserved_page_count * 8 ReservedPageCountInKB
, p.row_count RowsCount
, t.create_date CreatedDate
, t.modify_date ModifiedDate
, t.max_column_id_used Columns
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o
ON o.object_id = p.object_id
INNER JOIN sys.schemas as s
ON s.schema_id = o.schema_id
INNER JOIN
sys.tables t ON t.object_id = o.object_id
LEFT OUTER JOIN sys.indexes as i
on i.object_id = p.object_id and i.index_id = p.index_id
WHERE o.type_desc = 'USER_TABLE'
and o.is_ms_shipped = 0
ORDER BY
p.row_count DESC

SQL Tip: SQL Statement to find Business Dates

SQL Statement to find Business Dates, posted by Pinal Dave here. I have added my own logic of finding the 'First Day of Previous Month'.

DECLARE @mydate DATETIME
SELECT @mydate = getdate()
SELECT CONVERT(VARCHAR(25), DATEADD(DD, -DAY(@mydate-1), DATEADD(MM, -1, @mydate)), 101) ,
'First Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25), DATEADD(DD, -7, @mydate), 101) AS Date_Value, 'Start Date of Previous Week' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25), DATEADD(DD, -1, @mydate), 101) AS Date_Value, 'End Date of Previous Week' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'

SQL Server 2008 - Roadshow Presentations

SQL Server 2008 - Roadshow Presentations covered in New York. This covers the new features introduced in SQL 2008.

SQL Tip: SQL to Get dates between given range

SQL statement to get the dates in between the given date range. for (e.g) to get the Dates between 1/1/2007 to Today:

DECLARE @MinDate DATETIME
DECLARE @MaxDate DATETIME

SET @MinDate = '2007-01-01'
SET @MaxDate = getdate()
;
With Dates(Date)
AS
(
Select @MinDate Date
UNION ALL
SELECT (Date+1) Date
FROM Dates
WHERE
Date < @MaxDate ) SELECT Date, Datename(Month, Date) Month, Year(Date) Year FROM Dates OPTION(MAXRECURSION 0)

MS SQL 2005 Best Practices Analyzer(BPA)

The tool Microsoft SQL 2005 Best Practices Analyzer(BPA), is used to scan and analyze the given SQL 2005 Service and give a complete report about the Issues/Suggestions based upon the Best Practices. The SQL 2005 Services including Database Services, Analysis Services and Integration Services can be analyzed using this tool. This is really a useful tool for SQL 2005 DBAs and Database Developers.

This tool can be downloaded here

MS SQL - Query to Get Columns Definition

To get the Column Definitions of a Given table in MS SQL, use this query:

SELECT so.name ,
sc.name ,
st.name ,
sc.length ,
CASE
WHEN sc.status = 0x80
THEN 'Y'
ELSE 'N'
END AS IsIdent ,
ColOrder
FROM sysobjects so
INNER JOIN syscolumns sc
ON so.id= sc.id
INNER JOIN systypes st
ON sc.xtype = st.xusertype
WHERE so.Name = 'YourTableName'
ORDER BY ColOrder

I used this for generating a Bulk Insert statement for a XML Rowset in C#.

SQL Stored Procedure - Split

In SQL 2005, there is no built in function that Splits the given string based upon the given delimiter character (i.e) like Split function VB6. So I came up with the following SQL Function.

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

CREATE FUNCTION [dbo].[Split](@input nvarchar(4000), @delimiter char(1) )
RETURNS @results TABLE(SplittedString NVARCHAR(4000) )
AS



BEGIN
DECLARE @tempInput NVARCHAR(4000)
DECLARE @position INT
DECLARE @slice NVARCHAR(4000)


IF @input IS NULL RETURN

SELECT @tempInput = @input
SELECT @position = 1

WHILE @position != 0
BEGIN
SELECT @position = CHARINDEX(@delimiter, @tempInput)
IF @position > 0
SELECT @slice = LEFT(@tempInput, @position - 1)
ELSE
SELECT @slice = @tempInput

INSERT INTO @results(SplittedString) VALUES (@slice)

SELECT @tempInput = RIGHT(@tempInput, LEN(@tempInput) - @position)

IF LEN(@tempInput) = 0 BREAK
END

RETURN

END

SSDS - SQL Server Data Services

SQL Server Data Services (SSDS) are highly scalable, on-demand data storage and query processing utility services. Built on robust SQL Server database and Windows Server technologies, these services provide high availability, security and support standards-based web interfaces for easy programming and quick provisioning.

Know More

SQL Stored Procedure - GetQueryStringValue

Recently, I had a requirement to create a SQL function to get the Query String value from a given Query String for a particular Query String Key. (e.g) Input QueryString = 'search=google&client=safari&data=xyz'

Input QS Key = 'client'

Output = 'safari'

So I came up with the following SQL function.

CREATE FUNCTION [dbo].[GetQueryStringValue]
(
@inputQS VARCHAR(400),
@QSKey VARCHAR(50)
)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @positionStart INT
DECLARE @positionEnd INT


SET @positionStart = PATINDEX('%' + @qsKey + '%', @inputQS) 
IF @positionStart > 0
BEGIN
SET @positionStart = @positionStart + len(@qsKey) + 1 -- Get QSKey pos

SET @positionEnd = CHARINDEX('&', @inputQS,  @positionStart) -- Get the pos of next '&'
RETURN SUBSTRING(@inputQS, @positionStart, @positionEnd - @positionStart)
END
ELSE
RETURN ''
END


--------------------------
Usage : select dbo. GetQueryStringValue('search=google&client=safari&data=xyz', 'client')
output : safari