Find outdated statistics in SQL Server

SQL server uses the STATISTICS to find the appropriate Query plan.

if you are experiencing performance issues for your queries than it may be due to outdated statistics. An outdated statistic can make the sql server choose a wrong plan, use the following query to find outdated stats:

SELECT OBJECT_NAME(ID) AS [Table], SI.Name, STATS_DATE(ID, IndID) AS [Date Last Update], RowModCtr [Rows Modified Since Last Update]

FROM SYS.Tables AS ST INNER JOIN SYS.SYSIndexes AS SI ON SI.[ID] = ST.[object_ID]

INNER JOIN SYS.Schemas AS SS ON ST.[schema_ID] = SS.[Schema_ID]

WHERE STATS_DATE(ID, IndID) <= DATEADD(DAY,-1,GETDATE()) AND RowModCtr > 10

ORDER BY [RowModCtr] DESC

Once you identified the outdated stats, you can use the "Update Statistics" to do the update.

Query to find out parameters for Stored Procedure

Use below query to find out parameters for Stored Procedure

SELECT Schema_Name(Schema_ID) AS Schema_Name,
 O.Type_Desc,
 O.Name AS Object_Name, 
 P.Parameter_ID,
 P.Name AS Parameter_Name,
 Type_Name(P.User_Type_ID) AS Parameter_Type,
 P.MAX_Length,
 P.Precision,
 P.Scale,
 P.IS_Output
FROM SYS.Parameters AS P INNER JOIN SYS.Objects AS O 
ON O.Object_ID = P.Object_ID 
WHERE O.Object_ID = Object_ID('<Stored Procedure>') 
ORDER BY Schema_Name, P.Parameter_ID;

Replace <Stored Procedure> with Original Stored Procedure name.

Selecting a random number for each row

Sometime you may want to return a random number for each row. You can not do this directly selecting a RAND() function in query:

SELECT Rand() AS Random_Number, Name, Code from MyTable.

The above query will return the same random number for each row.

But you want to select different random number for each row. Follow below steps to do so:

create view rand_view
as
select rand( ) as random_value
go

create function New_rand()
returns float
as
begin
declare @f float
set @f = (select random_value from rand_view)
return @f
end

SELECT dbo.New_rand() wrapped_rand, Name, Code FROM MyTable

Number of words in a string

In SQL Server there is not direct function to calculate the number of words in a string. 
But we can calculate the number of words in following way:

Option 1 :
CREATE FUNCTION dbo.UDFWordCount(@Text VARCHAR(8000))
RETURNS int
as
/*
SELECT dbo.UDFWordCount ('hello   world')
*/
BEGIN
     
    DECLARE @iCtr int, @iCtx INT, @Words int
    SELECT @iCtr = 1, @Words = 0 

    WHILE @iCtr <= DATALENGTH(@Text)
    BEGIN
        SELECT @iCtx = CHARINDEX(' ', @Text, @iCtr)
        if @iCtx = 0
        BEGIN
            SELECT @iCtx = DATALENGTH(@Text) + 1
        END
        IF SUBSTRING(@Text, @iCtr, @iCtx - @iCtr) <> ' '
              SELECT @Words = @Words + 1 
        SELECT @iCtr = @iCtx + 1
    END
    RETURN(@Words)
END
GO

SELECT dbo.UDFWordCount ('Microsoft SQL Server2008')
SELECT dbo.UDFWordCount ('Microsoft  SQL  Server  2008')

Option 2 :
DECLARE @strTemp VARCHAR(4000)
SELECT @strTemp = 'SQL Server 2005'
SELECT LEN(@strTemp) - LEN(REPLACE(@strTemp, ' ', '')) + 1

But in this option if word are separated by a single space than it will give you the correct result, If there is more than single space between words than it will give you  wrong result.

Query to find the Index with size

Use below query to find the Index with size in a table:

SELECT
OBJECT_Name(I.OBJECT_ID) AS TableName,
I.index_ID AS IndexID,
I.Name AS IndexName,
8 * SUM(A.Used_Pages)/1024 AS 'IndexSize in KB'
FROM sys.Indexes AS I
JOIN sys.Partitions AS p ON p.OBJECT_ID = I.OBJECT_ID AND p.index_ID = I.index_ID
JOIN sys.Allocation_Units AS A ON A.Container_ID = P.Partition_ID
WHERE I.Object_ID = Object_ID ('<Table_Name>')
GROUP BY I.OBJECT_ID,I.index_ID,I.Name

Replace <Table_Name> with Table Name