Skip to main content

Posts

SQL Query Time Execute Check

SELECT    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,      qs.total_elapsed_time / 1000000.0 AS total_seconds,      qs.execution_count,  SUBSTRING (qt.text,qs.statement_start_offset/2,            (CASEWHEN qs.statement_end_offset = -1   THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2   ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,      o.nameAS object_name,
Recent posts

How to check SQL Database missing Index

SELECTdm_mid.database_id AS DatabaseID,  dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,  dm_migs.last_user_seek AS Last_User_Seek,  OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],  'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')   + CASE

Remove HTML all css Microsoft word (MS WORD) format

public static string StripHtml(string source)
    {
        source = Regex.Replace(source, "(<style.+?</style>)|(<script.+?</script>)", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        source = Regex.Replace(source, "(<img.+?>)", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        source = Regex.Replace(source, "(<o:.+?</o:.+?>)", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        source = Regex.Replace(source, "<!--.+?-->", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        source = Regex.Replace(source, "class=.+?>", ">", RegexOptions.IgnoreCase | RegexOptions.Singleline);

        return source = Regex.Replace(source.Replace(System.Environment.NewLine, "<br/>"), "<[^(a|img|b|i|u|ul|ol|li)][^>]*>", " ");

    }

SQL query maximum time execution check

SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

SQL Query Indian all bank and loop example

CREATE TABLE dbo.M_Bank
(
ID           INT IDENTITY NOT NULL,
MB_NAME      NVARCHAR (50),
MB_SNAME     NVARCHAR (20),
MB_ADDUSER   INT,
MB_ADDDate   DATETIME CONSTRAINT DF_M_Bank_MB_ADDDate DEFAULT (getdate()),
MB_MODUSER   INT,
MB_MODDate   DATETIME,
MB_ISActive  BIT CONSTRAINT DF_M_Bank_ISActive DEFAULT ((1)),
MB_ISDeleted BIT CONSTRAINT DF_M_Bank_ISDeleted DEFAULT ((0)),
MB_IP        VARCHAR (30),
CONSTRAINT PK_M_Bank PRIMARY KEY (ID)
)
GO

--Create Function
CREATE FUNCTION [dbo].[nop_splitstring_to_table]
(
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(
    data NVARCHAR(MAX)
)
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data)
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END
 …

Resignation Letter Format

[Director Name] Director [Company Address]
Dear Sir


Due to some unavoidable circumstances on personal reasons, I hereby tender my resignation to the post of [Post] with effect from [effect date]. I shall be serving a Notice Period of [notice days] days. My last working day would be [Last working date]. After serving for almost 1 year, I have gained immensely from the knowledge and experience. I must admit that I take lot of good things from here, which should take me too much higher levels. 

It was fantastic working with [company Name]  and I take this opportunity to thank my colleagues. The associations I've made during my employment here will truly be memorable for years to come.

I request the authorities kindly to accept my resignation with effect from [Last working date] Thank you very much for the opportunity to work here.

Sincerely,



Ashish Kumar Srivastava Sr. Software Engineer

Get size of all tables in database

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
 (SUM(a.total_pages) - SUM(a.used_pages)) * 8   AS UnusedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB
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
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name