Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

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)

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

Wednesday, October 3, 2012

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

Saturday, April 28, 2012

Logical Query Processing Phases in Brief


         Observing true experts in different fields, you find a common practice that they all share—mastering the basics. One way or another, all professions deal with problem solving. All solutions to problems, complex as they may be, involve applying a mix of fundamental techniques. If you want to master a profession, you need to build your knowledge upon strong foundations. Put a lot of effort into perfecting your techniques, master the basics and you’ll be able to solve any problem.

          So I can’t think of a better way to start writing my bogs without the fundamentals of logical query processing in SQL Server, Not just because it covers the essentials of query processing but also because SQL programming is conceptually very different than any other sort of programming.

          SQL programming has many unique aspects, such as thinking in sets, the logical processing order of query elements, and three-valued logic. Trying to program in SQL without this knowledge is a straight path to lengthy, poor-performing code that is difficult to maintain.

Logical Query Processing

            Following list contains a general form of a query, along with step numbers assigned according to the order in which the different clauses are logically processed.

(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;


Flow diagram logical query processing

(1)  FROM : The FROM phase identifies the query’s source tables and processes table operators. Each table operator applies a series of sub phases. For example, the phases involved in a join are (1-J1) Cartesian product, (1-J2) ON Filter, (1-J3) Add Outer Rows. The FROM phase generates virtual table VT1.

(1-J1) Cartesian Product : This phase performs a Cartesian product (cross join) between the two tables involved in the table operator, generating VT1-J1.

(1-J2) ON Filter : This phase filters the rows from VT1-J1 based on the predicate that appears in the ON clause (<on_predicate>). Only rows for which the predicate evaluates to TRUE are inserted into VT1-J2.

(1-J3) Add Outer Rows : If OUTER JOIN is specified (as opposed to CROSS JOIN or INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT1-J2 as outer rows, generating VT1-J3.

 (2) WHERE : This phase filters the rows from VT1 based on the predicate that appears in the WHERE clause (<where_predicate>). Only rows for which the predicate evaluates to TRUE are inserted into VT2.

 (3) GROUP BY: This phase arranges the rows from VT2 in groups based on the column list specified in the GROUP BY clause, generating VT3. Ultimately, there will be one result row per group.

 (4) HAVING : This phase filters the groups from VT3 based on the predicate that appears in the HAVING clause (<having_predicate>). Only groups for which the predicate evaluates to TRUE are inserted into VT4.

(5) SELECT : This phase processes the elements in the SELECT clause, generating VT5.

(5-1) Evaluate Expressions : This phase evaluates the expressions in the SELECT list, generating VT5-1.

(5-2) DISTINCT : This phase removes duplicate rows from VT5-1, generating VT5-2.

(5-3) TOP : This phase filters the specified top number or percentage of rows from VT5-2 based on the logical ordering defined by the ORDER BY clause, generating the table VT5-3.

(6) ORDER BY : This phase sorts the rows from VT5-3 according to the column list specified in the ORDER BY clause, generating the cursor VC6.