Synonyms in SQL Server

SQL Server 2005 introduces the concept of a synonym. A synonyms is a single-part name which can replace multi part name in SQL Statement. Use of synonyms cuts down typing long multi part server name and can replace it with one synonyms. It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).

A synonym is an identifier that can be used to refer an object from the data base that is having a very long name. Thus instead of using the long name every time we can have the alias or synonym. It gives us and extra layer of security as we have only the Abstract of the object in the form of name which helps us if the underlying object is changed or modified.

We can refer the synonym to perform operations on the base object. We can also use a synonym to refer objects on a linked server, thereby masking the server name.

CREATE SYNONYM statement:

CREATE SYNONYM [schema_name.]synonym_name FOR object_name

To create a synonym,

USE UrDBName;
GO
CREATE SYNONYM MySyn

FOR UrDBName.ObjectNsame

GO

Use Synonyms :

USE test;
GO
SELECT * FROM MySyn;
GO

To drop a synonym, all you need to do is execute the DROP SYNONYM command followed by the synonym name or right-click the synonym and select delete from the context menu.

DROP SYNONYM [schema].SynonymName

Synonyms can be created on only following objects.

• Assembly (CLR) Stored Procedure
• Assembly (CLR) Table-valued Function
• Assembly (CLR) Scalar Function
• Assembly (CLR) Aggregate Functions
• Replication-filter-procedure
• Extended Stored Procedure
• SQL Scalar Function
• SQL Table-valued Function
• SQL Inline-table-valued Function
• SQL Stored Procedure
• View
• Table (User-defined)

Additionally SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.

Now when writing the stored procedure instead of having to write out that entire alias every time you accessed the table you can just use the alias Clients. Furthermore, if you ever change the location or the name of the production database location all you need to do is modify one synonym instead of having to modify all of the stored procedures which reference the old server.

Benefits

• SYNONYMs provide a layer of abstraction over the referenced object
• Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
• Provides flexibility for changing the location of objects without changing existing code.
• SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
• SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations

• SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
• Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
• Obviously consumes possible object names, as you can not create a table with the same name of a synonym
• The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
• SYNONYM can not be referenced in a DDL statement

Written by An Indian Software Engineer A Bitter Reality

When I was going through my Facebook updates I came across this story, thought of sharing with you all.

As the dream of most parents I had acquired a degree in Software Engineering and joined a company based in USA, the land of braves and opportunity. When I arrived in the USA, it was as if a dream had come true.

Here at last I was in the place where I want to be. I decided I would be staying in this country for about Five years in which time I would have earned enough money to settle down in India. My father was a government employee and after his retirement, the only asset he could acquire was a decent one bedroom flat.

I wanted to do some thing more than him. I started feeling homesick and lonely as the time passed. I used to call home and speak to my parents every week using cheap international phone cards. Two years passed, two years of Burgers at McDonald’s and pizzas and discos and 2 years watching the foreign exchange rate getting happy whenever the Rupee value went down.

Finally I decided to get married. Told my parents that I have only 10 days of holidays and everything must be done within  these 10 days. I got my ticket booked in the cheapest flight. Was jubilant and was actually enjoying hopping for gifts for all my friends back home. If I miss anyone then there will be talks. After reaching home I spent home one week going through all the photographs of girls and as the time was getting shorter I was forced to select one candidate.

In-laws told me, to my surprise, that I would have to get married in 2-3 days, as I will not get anymore holidays. After the marriage, it was time to return to USA, after giving some money to my parents and telling the neighbors to look after them, we returned to USA.

My wife enjoyed this country for about two months and then she started feeling lonely. The frequency of calling India increased to twice in a week sometimes 3 times a week. Our savings started diminishing.

After two more years we started to have kids. Two lovely kids, a boy and a girl, were gifted to us by the almighty. Every time I spoke to my parents, they asked me to come to India so that they can see their grand-children.

Every year I decide to go to India… But part work part monetary conditions prevented it. Years went by and visiting India was a distant dream. Then suddenly one day I got a message that my parents were seriously sick. I tried but I couldn’t get any holidays and thus could not go to India … The next message I got was my parents had passed away and as there was no one to do the last rights the society members had done whatever they could. I was depressed. My parents had passed away without seeing their grand children.

After couple more years passed away, much to my children’s dislike and my wife’s joy we returned to India to settle down. I started to look for a suitable property, but to my dismay my savings were short and the property prices had gone up during all these years. I had to return to the USA…

My wife refused to come back with me and my children refused to stay in India… My 2 children and I returned to USA after promising my wife I would be back for good after two years. Time passed by, my daughter decided to get married to an American and my son was happy living in USA… I decided that had enough and wound-up every thing and returned to India… I had just enough money to buy a decent 02 bedroom flat in a well-developed locality.

Now I am 60 years old and the only time I go out of the flat is for the routine visit to the nearby temple. My faithful wife has also left me and gone to the holy abode. Sometimes I wondered was it worth all this?  My father, even after staying in India, Had a house to his name and I too have the same nothing more. I lost my parents and children for just ONE EXTRA BEDROOM.

Looking out from the window I see a lot of children dancing. This damned cable TV has spoiled our new generation and these children are losing their values and culture because of it. I get occasional cards from my children asking I am alright. Well at least they remember me. Now perhaps after I die it will be the neighbours’ again who will be performing my last rights, God Bless them.

But the question still remains ‘was all this worth it?’

I am still searching for an answer……………..!!!

START THINKING

IS IT JUST FOR ONE EXTRA BEDROOM???

LIFE IS BEYOND THIS… DON’T JUST LEAVE YOUR LIFE… START LIVING IT… LIVE IT AS YOU WANT IT TO BE……………….

 

Windows 8 Platform and Tools

As a developer of Microsoft technologies I always think about what programming model and tools are provided by the platform. On Microsoft new operation system Windows 8 developer write two type of applications first one is Metro Style Apps and second one is Desktop Apps.

Metro Style Apps
Metro style apps are full screen apps tailored to your users' needs, tailored to the device they run on, tailored for touch interaction, and tailored to the Windows user interface.

Desktop Apps
Normal Windows applications that we are using in Windows 7 and Windows Vista like msn messenger,

Following diagram show the programming model and Tools for Windows 8

 

SQL Server

Tips to improve SQL Server database design and performance

Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into the best performance of an application.

During database designing and data manipulation we should consider the following key points:

1.   Choose Appropriate Data Type

Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

2.   Avoid nchar and nvarchar

Does practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc. 

3.   Avoid NULL in fixed-length field

Does practice to avoid the insertion of NULL values in the fixed-length (char) field. Since, NULL takes the same space as desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL. 

4.   Avoid * in SELECT statement

Does practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.

1.   -- Avoid

2.  SELECT * FROM tblName

3.  --Best practice

4.  SELECT col1,col2,col3 FROM tblName 

5.   Use EXISTS instead of IN

Does practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.

1.   -- Avoid

2.  SELECT Name,Price FROM tblProduct

3.  where ProductID IN (Select distinct ProductID from tblOrder)

4.  --Best practice

5.  SELECT Name,Price FROM tblProduct

6.  where ProductID EXISTS (Select distinct ProductID from tblOrder)

6.   Avoid Having Clause

Does practice to avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don't use HAVING clause for any other purpose.

7.   Create Clustered and Non-Clustered Indexes

Does practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table. 

8.   Keep clustered index small

Does practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly. 

 

9.   Avoid Cursors

Does practice to avoid cursor since cursor are very slow in performance. Always try to use SQL Server cursor alternative.

10.                     Use Table variable inplace of Temp table

Does practice to use Table varible in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking task.

11.                     Use UNION ALL inplace of UNION

Does practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

12.                     Use Schema name before SQL objects name

Does practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.

1.   --Here dbo is schema name

2.  SELECT col1,col2 from dbo.tblName

3.  -- Avoid

4.  SELECT col1,col2 from tblName 

13.                     Keep Transaction small

Does practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks. 

14.                     SET NOCOUNT ON

Does practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:

1.   CREATE PROCEDURE dbo.MyTestProc

2.  AS

3.  SET NOCOUNT ON

4.  BEGIN

5.  .

6.  .

7.  END 

15.                     Use TRY-Catch

Does practice to use TRY-CATCH for handling errors in T-SQL statements. Sometimes an error in a running transaction may cause deadlock if you have no handle error by using TRY-CATCH.

16.                     Use Stored Procedure for frequently used data and more complex queries

Does practice to create stored procedure for quaery that is required to access data frequently. We also created stored procedure for resolving more complex task. 

17.                     Avoid prefix "sp_" with user defined stored procedure name

Does practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Hence SQL server first search the user defined procedure in the master database and after that in the current session database. This is time consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure. 

Different Types of SQL Server Stored Procedures

A stored procedure is a precompiled set of one or more SQL statements that is stored on Sql Server. Benifit of Stored Procedures is that they are executed on the server side and perform a set of actions, before returning the results to the client side. This allows a set of actions to be executed with minimum time and also reduce the network traffic. Hence stored procedure improve performance to execute sql statements.

Stored procedure can accepts input and output parameters. Stored procedure can returns multiple values using output parameters. Using stored procedure, we can Select,Insert,Update,Delete data in database.

Types of Stored Procedure

1.   System Defined Stored Procedure

These stored procedure are already defined in Sql Server. These are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database. These procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures. Here is a list of some useful system defined procedure.

System Defined Stored Pocedure

System Procedure

Description

sp_rename

It is used to rename an database object like stored procedure,views,table etc.

sp_changeowner

It is used to change the owner of an database object.

sp_help

It provides details on any database object.

sp_helpdb

It provide the details of the databases defined in the Sql Server.

sp_helptext

It provides the text of a stored procedure reside in Sql Server

sp_depends

It provide the details of all database objects that depends on the specific database object.

2.   Extended Procedure

Extended procedures provide an interface to external programs for various maintenance activities. These extended procedures starts with the xp_ prefix and stored in Master database. Basically these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.

Example Below statements are used to log an event in the NT event log of the server without raising any error on the client application.

1.   declare @logmsg varchar(100)

2.  set @logmsg = suser_sname() + ': Tried to access the dotnet system.'

3.  exec xp_logevent 50005, @logmsg

4.  print @logmsg 

Example The below procedure will display details about the BUILTIN\Administrators Windows group.

5.   EXEC xp_logininfo 'BUILTIN\Administrators'

3.   User Defined Stored Procedure

These procedures are created by user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.

4.   CLR Stored Procedure

CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#. I will discuss CLR stored procedure later.

Note

1.      We can nest stored procedures and managed code references in Sql Server up to 32 levels only. This is also applicable for function, trigger and view.

2.      The current nesting level of a stored procedures execution is stored in the @@NESTLEVEL function.

3.      In Sql Server stored procedure nesting limit is up to 32 levels, but there is no limit on the number of stored procedures that can be invoked with in a stored procedure