SQL Server and MS BI With a Geek Touch

A TRY ….CATCH construct includes a TRY block followed by a CATCH block. The TRY block contains a set of transact SQL statements. If an error occurs in any statements of the TRY block, the control is passed to the CATCH block, which contains another set of SQL statements.

The CATCH block contains SQL statements that executes only during errors in TRY block. The general syntax of the TRY…CATCH block is as shown below

BEGIN TRY

                <SQL Statements…..>

                <SQL Statements…..>

END TRY

BEGIN CATCH

                <SQL Statements…..>

                <SQL Statements…..>

END CATCH

  Read more…

Many have asked me this in the past. so what exactly is the difference between different type of joins? lets consider case by case, this will give you a better understanding. let us consider two tables for this.

1. Inner Join ( also commonly called join) Using INNER JOIN tells database to return only the columns that match the condition provided in the join clause. now if we want to select the employees and their respective team names, all we need to do is to join these two tables.INNER JOIN is often (but not always) created between the primary key column in one table and the foreign key column of another .

Read more…

In Sql Server 2005 or earlier , to insert each record into a table , you had to isuue one insert statement. One of the new programmability enhancements in the Database Engine introduced in SQL Server 2008 for Transact-SQL is the row constructor which consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma.  With the row constructor, you can now insert multiple values using a single INSERT statement.  

Example:

INSERT INTO [dbo].[TestTable] VALUES
('1', 'Simon'),  
('2', 'Mark'),  
('3', 'Peter')

One of the issue with this approach is that the number of records in a single insert is limited to 1000 records. You might get an error if you try to insert more than 1000 records.

Very often, it is important to check the last identity value inserted into a table. This can be easily done using the system function “@@IDENTITY” . This System function returns the last inserted identity value. 

The function returns an integer value or a NULL. After an INSERT statement the @@IDENTITY function will contain the latest value inserted into the identity field. It will contain NULL, If the insert/ select statement does not affect any records. one Key feature that needs to be noted is that the @@IDENTITY value does not revert to a previous value even if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back. However the Triggers fired doesn’t have an impact on the function. Failed statements and transactions can change the  identity for a table and create gaps in the identity column values.

to check these , Execute the function with out performing an insert in a new session.

SELECT  @@IDENTITY
Result will be "NULL"

 Now insert some value into a table containing an identity field. If you do not have one, the below script might be of use. 

CREATE TABLE [dbo].[TestTable](
[Ident] [int] IDENTITY(100,1) NOT NULL,
[ColUmn_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Insert the data by executing the statements below

INSERT INTO TESTTABLE ( COLUMN_1) VALUES ('HELLO');
SELECT  @@IDENTITY AS CURRENT_IDENTITY_VALUE
     , IDENT AS INSERTED_IDENTITY_VALUE
FROM
 TESTTABLE ;

Result:

Current_identity_value    Inserted_Identity_Value
100                       100

The Scope of the function is restricted to the current session. To get the identity value on a different server , we need to use functions / procedures.

It is often a requirement to fetch the latest/ current identity value used in a specific table . There are two types of identity values that you can fetch.

• Depending on the scope of execution and irrespective of the table.
• The value used in a table and irrespective of scope.

Let me show this to you, with an example. Create a table using the query

CREATE TABLE TestTable (
 TestID INT NOT NULL IDENTITY(1,1) ,
 TestValue NVARCHAR(50) NOT NULL )

Depending on the scope of execution and irrespective of the table

Insert a record into the table

INSERT TestTable( TestValue)
VALUES ( 'record 22')

SELECT SCOPE_IDENTITY() as RecentInserted

The function “SCOPE_IDENTITY()” prints the identity value used in the recent insert. This is irrespective of the table used. If you continue inserting record in any other table with Identity columns, you will notice the value returned by the function changing. This function does not accept any parameters.

The identity value used in a table

Use the function “IDENT_CURRENT() ” to get the current identity value used for a table . A sample query is as given below.

SELECT IDENT_CURRENT('TestTable') IdentityInTable

Notice that the table name has to be passed as a parameter for this function.

Also Check how to  Insert values into IDENTITY column of SQL server table

Usually problem occurs when we are trying to build a query dynamically and if the length exceeds 4000 characters ( a variable of type nvarchar) or 8000 ( in case of varchar). The query stored in the variable gets truncated when it reaches the limit. One of the easiest way to overcome this is to split the query across few manageable number of variables. You can then execute the query/es by concatenating the variables. How ever do not use “SP_EXECUTESQL”  , as this will throw an error when you try to execute. SP_EXECUTESQL can execute a string, a variable but not when you concatenate variables.

that is Exec sp_executesql @var1+@var2 will throw an error.

You can use the EXEC( ) to execute these type of queries. All you need to do is EXEC( @var1 +@var2+@var3)

example:

Declare @var1 nvarchar(max), @var2 nvarchar(max),@var3 nvarchar(max);

Set @var1 = ‘Select  ‘;

Set @var2 = ‘ * From ‘;

Set @var3= ‘ mytable’;

exec (@var1+@var2+@var3);

Above query will return the records in the table.

This was a simple experiment that I performed to find, how much difference it makes by replacing some of the characters in a field with others. The basic requirement was to have a varchar / nvarchar field with huge data . I created a sample table with a field of datatype  nvarchar(max). The table used no disk space

name                  rows    reserved    data    index_size    unused
TESTTABLE    0              0 KB          0 KB     0 KB               0 KB

Then inserted data into this field. Also didn’t forget to add lot of  “space” in the data. I inserted around 17 records in the table and then ran the stored proc to find the space used by the table.

the result was

name                 rows    reserved     data            index_size    unused
TESTTABLE     17         984 KB       968 KB 8 KB                  8 KB

I replaced the 3 consecutive spaces with a “horizontal tab” by using the query

UPDATE TESTTABLE
SET CHARS = REPLACE(TESTFIELD,’   ‘,CHAR(9))

then i checked the space used by the table

name                 rows          reserved    data           index_size    unused
TESTTABLE      17             968 KB        696 KB 8 KB                 264 KB

you might have already noticed that, there is a huge differnce in the data size. The size was reduced to 696KB from 968KB. This was for a small table with only 17 records, imagine for a table with millions of records ,  it can make a huge difference. Theoratically speaking 3 charchters were replace with a single character with out affecting the way the data is displayed in my UI . This saved me 4 bytes for each replace ( 1 characters = 2 byte for nvarchar, therefore 3-1 = 2 characters removed for each replace ). This does not mean that you can replace all characters with someting else.

One of my record had  23345 characters, the replace reduced it to  14761 characters

The unused space can be regained by using some techniques like shrinking the database.

Now the question in your mind might be, does the same work for char / nchar datatypes.  I leave that to you to find out. May be,  you already have the answer .

Please use the content at your own risk. In no event shall the
authors be liable for any claim, damages/issues that may be caused
by using the content.