Equivalent of Oracle PL/SQL TRIM function in SQL Server (T-SQL)

In PL/SQL, when the TRIM function is called with just the data parameter it will strip the leading and trailing spaces.   You can accomplish the same functionality in t-sql by using two t-sql functions:  LTRIM (strips leading spaces) and RTRIM (strips trailing spaces).  An example of accomplishing this would be the following:

One thing to note is that TRIM with the LEADING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the LTRIM function in t-sql;  TRIM with the TRAILING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the RTRIM function in t-sql.

You can also create a user defined function for TRIM in your database.  The following listing below will do that for you.

After running the above script you could test it by running the following:

References

 PL/SQL TRIM

TSQL LTRIM

TSQL RTRIM

Insert data into new (or existing table) from another (T-SQL, PL/SQL, SQL Server, Oracle)

Often times we wish to copy data from one table into another.  The methods of doing this where the new table does not exist will be slightly different from the case in which it does exist.  Also I will show display the method of doing this in T-SQL and PL/SQL (just because I get confused all the time when I switch databases Smile ).

SQL copy tables to new table

When performing this command, a table will created using the same data types as the columns which as selected in the select statement.  This is usually very useful when making a copy of the existing table before making any alterations to the data.

T-SQL:

PL/SQL:

 

SQL copy tables to existing table

These following when executed will insert data into already existing table.  It will match fields based on name if you choose to list the fields in the select clause.  This normally useful for when you need to reinsert data (don’t ask why… Smile)

T-SQL:

PL/SQL:

Run Transact-SQL Script Files Using sqlcmd–SQL Server T-SQL

Problem:

I recently received a script which creates a database and loads data.  I was unfortunately too large for SQL Server Management Studio to process.

Solution:

You can use the sqlcmd utility to run the script file. The example below shows the execution of a script file called script.sql which is located on the C drive and a connection is made to the SQL Server using integrated security.

To run the script file

  1. Open a windows command prompt window.
  2. In the command prompt window, type: sqlcmd –S SQLServerName-i C:\DatabaseLoad.sql
  3. Press ENTER.

How to delete duplicate records from your SQL Server tables – T-SQL

Below you will find two methods that can be used to remove duplicate records from you Microsoft SQL Server tables.

Method 1:

This method will only work in SQL Server 2005 and later; if you need to remove duplicates from an earlier version please upgrade you SQL Server version.  Just kidding!  Use the technique in method 2.  Actually you really should upgrade your version of SQL Server…

This method uses the Common Table Expression functionality and row_number function which was introduced in Microsoft SQL Server 2005.   The key strategy to this method is to generate a number next to the  records which denotes the row number.

So in essence we want to transform this:

Duplicated Records

Duplicated Records

To this:

DuplicatedResultWithNumber

Duplicated Results with Numbers

And remove all instances of those rows which the row number column do not correspond to 1.

Below is a script which can be used to demonstrate this.   Also you can use this link in SQL Fiddle.

Method 2:

This method should work on all versions for SQL Server.  It basically works by copying the unique rows out of the original table into a temporary table, and then deleting all records from the original table;  the unique rows from the temporary table are then copied into the original table.

Link to SQL Fiddle is here.

— Create Table

How to create a blocking transaction in SQL Server – T-SQL (exclusive lock)

Excuse the odd topic.   I often like to experiment with SQL Server and create odd situations which we normally should be avoiding.  One of those things is creating SQL Statement which get blocked and creating a timeout.  One of the simplest ways to run a query against a table which is locked in another in another session.  To do this do the following:

1) Create a lock on a table.  

The example below demonstrates a lock on the Person table in the AdventureWorks2012 sample table.    The key points which make this work is to create a transaction, and then issuing a query using the TABLOCKX and HOLDLOCK table lock hints.  This will force an exclusive lock on the table.  To keep the lock for significant amount of time we specify that the transaction stays alive for a minute by introducing the WAITFOR DELAY ’00:01:00′ command, which will prevent any further execution of statements until a minute has passed.

2)  Now open up another session in SQL Server (new query from Microsoft SQL Server Management Studio), and execute the following query:

You will notice that after this query is excuted, the query seems to run forever.  It will actually never  execute until the query in step 1 is finished.

3) Check for blocked transaction.  You can find this by opening up an new query in Microsoft SQL Server Management Studio and executing the following query:

I will explain in more detail how this blocking query works in a later post.

If you have any other tips on how to create a blocked transaction please share them in the comments below.

How to start and stop Microsoft SQL Server from the command line

Start a command prompt using the “Run as administrator” option.  If you do not use this option you may be a message that ‘Access is denied.’

To start the default instance of SQL Server type the following in command line:

To stop the default instance of SQL Server type the following in the command line:

Equivalent of DUAL table in Microsoft SQL Server

I spent quite a few few years developing PL/SQL on Oracle databases and had grown accustomed to its syntax.  One of the things which I didn’t enjoy was that the ‘FROM’ clause in a SELECT statement was required (it is not in TSQL).  This causes a  bit of a problem when all you want to do is do retrieve 1 row with a result.  The workaround for this problem was to introduce a dummy table which just contains one row which you could use in your queries.  This table was called DUAL and has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.

To demonstrate the use of DUAL, the following displays the use of a SELECT statment that calculates 1+1 in TSQL and PL/SQL:

TSQL:

PL/SQL:

The name DUAL seems a bit odd for a table which just has one record in it.  The reason for this is that it originally had two records and was intended to be used to return two records when being used.   There is a bit of history which explains this on wikipedia article.

What if you wanted to port existing code from PL/SQL to TSQL and your code already utilizes DUAL?  I would personally rewrite it as you generally have to write rewrite code anyways but the other option is to actually recreate the DUAL table in your database.   The following code will assist creating this:

If your a TSQL programmer working on a PLSQL you will need to remember to utilize the DUAL table,

T-SQL – Search Stored Procedure Code for Text

Often I have to search for stored procedures in SQL Server to find pieces of text.   The motivation for this is to find procedures which use a table or to look for code which the developer has done silly things like hardcoded directories (Boris….).  Luckly you can query sys.procedures to generally find the information that you need.  Below is an example of query that searches for stored procedures for the word ‘c:\*’.

REFERENCES:

Technet – sys.procedures

How to determine the version and edition of SQL Server

Connect to the instance of SQL Server, and then run the following query (this will work for all versions of SQL Server):

The result form the query will look like the following.

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

For version of SQL Server 2005 and newer you also can use the SERVERPROPERTY function to retrieve additional information.  Executing the following will provide you with the SQL Server product version (i.e. 10.0.2531.0, product level (RTM) and edition information (i.e. Standard, Enterprise):

You can lookup the product version using the following table:

Release Product Version
SQL Server 2012 Service Pack 1 11.00.3000.00
SQL Server 2012 RTM 11.00.2100.60
SQL Server 2008 R2 Service Pack 1 10.50.2500.0
SQL Server 2008 R2 RTM 10.50.1600.1
SQL Server 2008 Service Pack 3 10.00.5500.00
SQL Server 2008 Service Pack 2 10.00.4000.00
SQL Server 2008 Service Pack 1 10.00.2531.00
SQL Server 2008 RTM 10.00.1600.22
SQL Server 2005 Service Pack 4 9.00.5000.00
SQL Server 2005 Service Pack 3 9.00.4035
SQL Server 2005 Service Pack 2 9.00.3042
SQL Server 2005 Service Pack 1 9.00.2047
SQL Server 2005 RTM 9.00.1399
SQL Server 2000 Service Pack 4 8.00.2039
SQL Server 2000 Service Pack 3 8.00.760
SQL Server 2000 Service Pack 2 8.00.534
SQL Server 2000 Service Pack 1 8.00.384
SQL Server 2000 RTM 8.00.194

 

Where to find information about the latest SQL Server builds use the following link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;957826

%d bloggers like this: