T-SQL: Return nth delimited element from a string

CREATE FUNCTION [dbo].[fn_String_SplitByDelimiter]
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber int
)
RETURNS VARCHAR(4000)
AS
BEGIN

   DECLARE @result varchar(4000)    
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,
                          Item VARCHAR(4000)
                         )  

   DECLARE @ll INT , @ld INT;  
   set @ll =  LEN(@List) + 1
   set @ld = LEN(@Delimiter);

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   SELECT @result=Item
   FROM @Items
   WHERE position=@ElementNumber

   RETURN @result;
END

Example

SELECT [dbo].fn_String_SplitByDelimiter (
   '1ST\2ND\3RD\4TH'
  ,'\'
  ,3)

Will output
3RD

taken from the below url, just updated it to work on SQL2005
http://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string

T-SQL: Which table taking most space

This query will list all tables of the selected database, sorted by the table which is taking up most data space in MB from the database.  I used it on a database which had a huge MDF file and found out that the Audit table had grown too much.

SELECT 
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
TotalSpaceMB desc
 --OBJECT_NAME(i.object_id) 

Source: http://stackoverflow.com/questions/3927231/how-can-you-tell-what-tables-are-taking-up-the-most-space-in-a-sql-server-2005-d

Run Visual Studio as an Administrator in Windows 8

In Windows 8, you have to right-click devenv.exe and select "Troubleshoot compatibility".

1.    select "Troubleshoot program"

2.    check "The program requires additional permissions"

3.    click "Next", click "Test the program..."

4.    wait for the program to launch

5.    click "Next"

6.    select "Yes, save these settings for this program"

7.    click "Close"

http://stackoverflow.com/questions/12257110/can-you-force-visual-studio-to-always-run-as-an-administrator-in-windows-8

If you are getting the error 'Unable to debug the server' when trying to debug a solution, try running visual studio as administrator.  The above forces Visual Studio to always run as administrator so you won't get this error again

T-SQL: List all table foreign keys

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Find number of active SQL connections and kill them

How to find number of active SQL connections

select 
    db_name(dbid) as [Database Name], 
    count(dbid) as [No Of Connections],
    loginame as [Login Name]
from
    sys.sysprocesses
where 
    dbid > 0
group by 
    dbid, loginame

How to kill active SQL connections to a single database

set nocount on
declare @databasename varchar(100)
declare @query varchar(max)
set @query = ''

set @databasename = 'My database'
if db_id(@databasename) < 4
begin
         print 'system database connection cannot be killeed'
return
end

select @query=coalesce(@query,',' )+'kill '+convert(varchar, spid)+ '; '
from master..sysprocesses where dbid=db_id(@databasename)

if len(@query) > 0
begin
print @query
         exec(@query)
end

Link to article
http://www.codeproject.com/Articles/389725/How-to-find-the-active-SQL-connections-and-kill-th

T-SQL: Get current date and time

Functions available to get the current date and time

Function	Result	Return type
GETDATE()	2013-02-26 20:54:24.057	DATETIME
CURRENT_TIMESTAMP	2013-02-26 20:54:24.057	DATETIME
GETUTCDATE()	2013-02-26 20:09:17.467	DATETIME
SYSDATETIME()	2013-02-26 21:09:17.4680514	DATETIME2
SYSUTCDATETIME()	2013-02-26 20:09:17.4680514	DATETIME2
SYSDATETIMEOFFSET()	2013-02-26 21:09:17.4680514 +01:00	DATETIMEOFFSET

UTC means Coordinated Universal Time and is widely used as time standard in web applications. The result above were taken on a PC with the time zone (UTC+01:00). Note that the UTC functions return the time one hour before the rest.

CURRENT_TIMESTAMP is the recommended one as it is the ANSI equivalent of GETDATE()

Don't forget that if you only need the current DATE or TIME, you can cast the return of the above functions to a DATE or TIME

Example:

SELECT CAST(GETDATE() as DATE)

which will return

2013-02-26

For all DateTime functions visit the following link

ASP.NET: How to check ViewState size

Just enter the following code in the console of firebug or chrome developer tools and press Enter. Or call the code upon a button click

alert("View state is " + document.forms[0]["__VIEWSTATE"].value.length+ " bytes");