Generate Incremented Linear Number Sequence

There are lots of methods of creating numbers in SQL, We could use a recursive Common Table Expression or we could use a cross join of sys.columns (or any other system table or view) with itself for lots of numbers, or simply generate a numbers table.
my favorite method is Itzik Ben-Gan's auxiliary table of numbers. In case you cannot, or do not want to generate a permanent table, you can produce a virtual one on the fly using this method very efficiently.
DECLARE @max_rows AS BIGINT;
SET @max_rows = 50;-- put here any number you want

WITH
  lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
  lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b), -- 4
  lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b), -- 16
  lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b), -- 256
  lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b), -- 65,536
  lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b), -- 4,294,967,296

  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv5)

SELECT TOP (@max_rows) n FROM Nums ORDER BY n;
when you wrap this in to a Table Valued Function you can call it whenever you want to generate sequential numbers.
-- =============================================
-- Author:       
-- Create date:
-- Description:   
-- =============================================
CREATE FUNCTION GenerateSqnNumbers
(   
    @max_rows int
)
RETURNS TABLE
AS
RETURN
(

    WITH
      lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
      lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b),
      lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b),
      lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b),
      lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b),
      lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b),

      Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv5)

    SELECT TOP (@max_rows) n FROM Nums ORDER BY n

)
GO

Other Methods

Using another recursive Common Table Expression.
WITH cte AS
(
    SELECT 1 AS num
    UNION ALL
    SELECT (num + 1) AS num FROM cte
    WHERE num < @SomeMaximum
)
SELECT * FROM cte OPTION (MAXRECURSION 0);
Note that without the MAXRECURSION option CTE recursion depth in MS SQL is limited to 100. (value of 0 disables the recursion limit)
Using While loop
DECLARE @tmp_numbTable AS TABLE (Number BIGINT);
DECLARE @MaxSqnNumber BIGINT = 1000;
DECLARE @MinSqnNumber BIGINT = 0;

WHILE @MaxSqnNumber >@MinSqnNumber
BEGIN
   
    SET @MinSqnNumber = @MinSqnNumber + 1
    INSERT @tmp_numbTable ( [Number] ) VALUES(@MinSqnNumber)
    PRINT @MinSqnNumber
END


SELECT * FROM @tmp_numbTable AS tnt

Get Server name, Database name, Schema name or Object name

There is a simple T-SQL function that Returns the specified part of an object name. using this function we can retrieve object name, owner name, database name, and server name from a fully qualified object name. This function doesn't require the object name that is provided is in the database, its only returns the specified part of the specified object name.

PARSENAME ( 'object_name' , object_piece )

object_piece

Object_piece is the part that you want to retrieve.
1 = Object name 
2 = Schema name
3 = Database name
4 = Server name

what this function actually doing is, it getting the object name and split the object name by period (‘.’) and returning the specified position in Object_piece.

SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',1) AS ObjectName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',2) AS SchemaName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',3) AS DatabaseName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',4) AS ServerName

if you smart enough you can use this for many things, such as separating extension from the file name or parse an IP address. Just remember you are working backwards.

How to add line break to a string

when we creating dynamic sql it is easy to read or troubleshoot the SQL query if we format it. I’m always adding line breaks to my dynamic queries using following code.
Here I’m just declaring a variable and setting the CR and line breaks to it. you can use one of these CHAR(13) for CR and CHAR(10) is for DOS/Windows style CRLF Line breaks.

DECLARE @line_break NVARCHAR(2);
SET @line_break = CHAR(13) + CHAR(10);

PRINT '-------------------------'+@line_break+'Line 1' + @line_break + 'Line 2' + @line_break +'-------------------------'

result…
-------------------------
Line 1
Line 2
-------------------------

How to monitor page split in SQL Server

I had a question when creating or rebuilding a index, the fillfactor value that I set to it is the perfect value for my requirement. I have found a query that use a undocumented function and read the transaction log and identify the indexes that are splitting.



SELECT AllocUnitName,COUNT([AllocUnitName]) [Splits]
FROM ::FN_DBLOG(NULL,NULL)
WHERE Operation=N'LOP_DELETE_SPLIT' AND PARSENAME(AllocUnitName,3)<>'sys'
GROUP BY AllocUnitName




there is another way to find the page split with Extended events. for more details go to Michael Zilberstein's Blog

Disable constraints on a table

Sometimes it's useful to disable one or more constraints on a table, do something significant, and then re-enable the constraints after you're done. This is most often done to improve performance during a bulk load operation, Some type of Database cleanup process.

How to: Enable/Disable single constraint

According to SQL Server Books Online, we can disable constraints using the ALTER TABLE statement. for more details go to ALTER TABLE (Transact-SQL)

for an example:

/***** disable the FK_TABLE2_TABLE1 constraint on TABLE2 *****/

ALTER TABLE dbo.TABLE2 NOCHECK CONSTRAINT FK_TABLE2_TABLE1

/***** enable the FK_TABLE2_TABLE1 constraint on TABLE2 *****/

ALTER TABLE dbo.TABLE2 CHECK CONSTRAINT FK_TABLE2_TABLE1

How to: Enable/Disable all constraint

The following example shows how to disable all constraints on a table.
here i'm creating T-SQL statement for all tables using INFORMATION_SCHEMA View and execute them

--Disable
DECLARE @SQL VARCHAR(MAX) 
SELECT @SQL = ISNULL(@SQL,'') + 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL;' 
FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' 
EXEC(@SQL) 
go 

--Enable 
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ISNULL(@SQL,'') + 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL;' 
FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' 
EXEC(@SQL)

Convert all tables in a database to a specific schema

Create schema

CREATE SCHEMA sampleSchema
GO


Create a T-SQL statement to transfer all tables to created schema

DECLARE @sql NVARCHAR(MAX) ;
DECLARE @LINEBREAK AS VARCHAR(2)
SET @LINEBREAK = CHAR(13) + CHAR(10)
        
SELECT  @sql = COALESCE(@sql+ '; ' + @LINEBREAK , '') + 'ALTER SCHEMA [sampleSchema] TRANSFER '
        + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME])
    
FROM    INFORMATION_SCHEMA.TABLES

PRINT @sql
EXECUTE sp_executesql @sql