Wednesday, April 24, 2013

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

Saturday, November 24, 2012

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
-------------------------

Tuesday, November 20, 2012

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

Monday, November 19, 2012

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)

Monday, October 29, 2012

How to create a comma delimited list using SELECT clause from table column?



How to create a comma delimited list using SELECT clause from table column?

USE
 tempdb;
GO

/*Create sample table and data*/
CREATE TABLE sampleTable (id INT, VALUE VARCHAR(150))
GO

INSERT dbo.sampleTable
        ( id, VALUE )
VALUES  ( 10, 'Value 1' ),( 11, 'Value 2' ),( 12, 'Value 3' ),( 13, 'Value 4' ),( 14, 'Value 5' )
GO

DECLARE @CSV_str NVARCHAR(500);
/*Collecting all values in Value column to @CSV_str variable*/
SELECT @CSV_str = ISNULL(@CSV_str + ',','') + VALUE FROM dbo.sampleTable

PRINT @CSV_str


/*drop sample table*/

DROP TABLE dbo.sampleTable

Sunday, October 28, 2012

Connecting to SQL Server over the Internet


         Once I had to analyze a SQL Server in a remote site every day, to do the analyzing I had to get a RDC to the machine or go to that site. This was so irritating for me because working with a RDC is like watching a slow-motion movie, and I don’t like to go outside in daytime ;-).
So I Google to find a way to connect directly to the all SQL server instances in all sites over the internet. There was some Q&A, blogs and some topics in MSDN but I couldn’t find an every steps to configure the SQL server, Firewall, Router in one place. So I decide to write this article for help newbies to the SQL Server.
 

How to: configure SQL Server to Connect Remotely.

    1. Go to SQL Server Configuration Manager.
    2. Select SQL Server Network Configuration.
    3. Select Protocols for your Server Instance.
    4. Double Click on protocol TCP/IP and go to IP Addresses Tab
If you have a default Instance you will see TCP Port set to 1433 in IPAll (Default instances of the Database Engine use TCP port 1433 by default.), if there are no port have set to TCP port, set it to 1433.

 

For the named instance SQL server dynamically assign an unused TCP port number the first time the instance is started. If that port is used by another application SQL server will assign an unused TCP port. So we can’t use a dynamic port to connect remotely to the SQL Server. Assign a new TCP port number to TCP port.
5.  Go to Protocol tab and Set Enabled to Yes. Click Ok
6.  Go to SQL Server Services Restart the SQL server.
7.  Start the SQL Server Browser Service (if u need to remote connections to the Dedicated Administrator Connection this service should be enabled.)
 
Now the SQL Server Configuration part is done.

How to: Configure a Windows Firewall for Database Engine Access

 
                To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server to allow access. To do so we need to create a firewall rule to open a port to connect with the SQL server

 

To create a new firewall rule using the New Rule wizard

  • On the Start menu, click Run, type WF.msc, and then click OK.
  • In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule.

image002

  • Select Port Click Next

image005

  • Enter the SQL Server Listening port(s) (this port is that we configured in SQL Server Configuration Manager, TCP/IP port) and click next.

image007

  • Select allow the connection, Click next.

image009

  • Select Appropriate profiles click next.

image011

  • Give a Name click finish

How to: configure a Router.

      Open the Port Forwarding or Applications and Gaming tab. Each router will be slightly different, but look for these two options. If you don't see them, try Advanced Settings and look for a Port Forwarding subsection. You will see something like below.

image012
 
Application: Is the name (Give “SQL” or any name u like)
External Port: You will connect to the SQL server using this port
Internal Port: this should be the port that we configured in SQL Server Configuration Manager, TCP/IP port
Protocol: Select TCP
IP Address: IP address on the computer that is running SQL Server
Enabled: you know what this is right? ;-)
If you need more knowledge about port forwarding read this article.

Find the public IP

Open IE and go to Google type “My IP” and search, it will show your public IP. When we are connecting through the internet we will use this IP as our SQL server Name
 

How to: connect to a SQL server instance

 
               Now we have configured the SQL server and punch a hole on the firewall to connect to the SQL Server from outside and also we have configured the router to forward the request to the SQL server.
 
Open SQL Server Management Studio.
 
image013
 
Type the public IP and the external port number (that you have set in router) in the Server name and give login details
You are done.
 
Click Connect…..