RSS

Tag Archives: General

General categories information, SQL Server, Microsoft, Business Intelligence

Three Things I’ve Learned From Warren Buffett – By Bill Gates

Hi Friends,
Just wanted to share with you quickly… a good article by Bill Gates.

http://www.linkedin.com/today/post/article/20130612065727-251749025-three-things-i-ve-learned-from-warren-buffett?trk=eml-mktg-celeb-bg-title

Thanks, Khilit

 
Leave a comment

Posted by on June 14, 2013 in General

 

Tags:

Retrieving id of inserted record: What to use @@IDENTITY or SCOPE_IDENTITY() or OUTPUT

Hi guys,
This seems very basic information but need to know properly.

Issue:
I need to find out or have the id of the records which I inserted in table.

Analysis:
This requirement seems very simple but there could be various cases where you would not get the correct id just by using @@IDENTITY function.

Think of the case where you are inserting a record in table A and there is a trigger which fires as soon as there is a insert in table A which insert a record in table B. so in such case @@IDENTITY wont give you the correct id/pointer of inserted record.

So to deal with this issue you can use SCOPE_IDENTITY(). You will always get the last value you have inserted in table. However, it is noticed that sometimes in some scenarios SCOPE_IDENTITY() does not promise the correctness of results. May be due to parallelism or multiple inserts or bulk inserts in table(s). E.g. when you do … Insert Into Select <columns> from Table

Well, Microsoft recommends that “You do not use either of these functions in your queries when parallel plans are involved as they are not always reliable. Instead use the OUTPUT clause of INSERT statement to retrieve the identity value“.

Check out MS articles:
http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779
http://msdn.microsoft.com/en-us/library/ms177564.aspx

I hope this will help you somewhere.
Thanks, Khilit

 

Tags: , , , , , ,

The Story of Precision, Scale, and Length in SQL Server

Hi guys,

Did you ever faced issues with BIG mathematical figures – related to either overflow or any  other arithmetic errors. This may be applicable to SQL arithmetic operations or arithmetic  operations you do with SQL table in SSIS etc.

Well, check 1 more time, if you have any issues in your database design related to column  data type which stores the BIG numbers.

I ran into this issue couple of times, and reason behind this issue was same all the time. So  decided to do a quick post. And that reason is “Improper or insufficient precision, scale and length defined for storing Decimals and Numeric values in table“.

I wanted to describe this with an example but there is already a very nice explanation by MSDN  on this topic. Check out: http://msdn.microsoft.com/en-us/library/ms190476.aspx

You will notice in this article that, how any arithmetic operation will affect size and space  allocation of the resultant value during mathematical operation. You should use following shown table for defining and designing tables in such a way that can  handle BIG mathematical figures during operations.

From MSDN, for your quick view:

Operation

Result precision

Result scale *

e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 – e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 – s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Important Note: Don’t forget to check for your SQL Server Version.

I hope you will find this informative.
Thanks, Khilit

 

Tags: , , , , ,

Adding a new column to an existing table??? Check the rules first

Hi All,
So, you are standing at the gates where you need to add a new COLUMN to an existing table.

Well, there are 2 cases:

  • Table is empty
  • Table contains data

If “Table is empty” then you are still in design-phase of that table. Add new column whichever the way you know :)
E.g. Using T-SQL or through design mode of table etc.

Read the rest of this entry »

 

Tags: , , , ,

Search Country & Location Information based on IP Address

Hi All,

Once I ran into the situation where I have to find out the CityCountry name & location (Longitude, Longitude) based on the IP address. This was not difficult but rather not straight-forward as IP addresses are stored into database table as strings which is very obvious because there are no standard data type which supports IP address as value.

Anyways, so in my database table there are 2 important columns regarding IP address details i.e. Start_IP_Address and End_IP_Address. And mind-well, a range of IP addresses belongs to a country. Currently my database table looks like following: (Below screenshot shows only 5 records)

Read the rest of this entry »

 
2 Comments

Posted by on February 22, 2012 in General, SQL Server, Tutorial

 

Tags: ,

“Specify Values for Template Parameters” in SQL Server

Hi All,

SQL Server Management Studio provides a dialog box named “Specify Values for Template Parameters” for filling values for the template you have used. This is very handy if you always like interface based interactions with components.

E.g. When you choose any template from Template Explorer, you will see template with lots of place-holders where you can specify your values. In following example I am going to show you the template of basic stored procedure.

Read the rest of this entry »

 
 

Tags: ,

FileTables in SQL Server 2012

Hi All,

FileTables feature introduced in SQL Server 2012. This feature is built on the top of FileStream feature of SQL Server 2012. I published the introduction of FileStream in my previous post.

FileTables allows us to store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

A FileTable is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes.

Read the rest of this entry »

 
 

Tags: , ,

Analyzing DEFAULT schema & assigned Role for a Database

Hi All,

Following SQL query will list available roles in database and the default schema they are assigned to.
You can also check when it’s last modified.

–Query Start
Use <Your Database Name>
Go

Read the rest of this entry »

 
1 Comment

Posted by on February 14, 2012 in General, SQL Server, Tutorial

 

Tags: , ,

Welcome Microsoft Help Viewer 1.1

Hi All,
With the installation of SQL Server 2012 RC0, you will also find integrated “Help Viewer 1.1“. This is pretty much like MSDN help.

You can launch the Help viewer by following method.

Read the rest of this entry »

 

Tags: , ,

Auto Generate (Some particular)Change Scripts in SSMS

Hi All,
How are you doing?

We all know that Change is a rule of life …. And pretty much that applied to SQL Server as well :)

Yes, my point is to find out the changes we have done in current SQL Server so we can deliver into the next environment i.e. UAT/Test or Production etc. People follow lots of ways to find/generate change scripts for SQL objects for their creation and modifications.

But I would like to draw your attention to a facility/functionality which is provided by MS SQL Server Management Studio by default. But by default this is disabled and thanks to GOD for that.

Read the rest of this entry »

 

Tags: ,