Archive for the ‘T-SQL’ Category
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 [...]
February 6, 2013
· 
Khilitchandra Prajapati · 
No Comments
Tags: Business Intelligence, General, IDENTITY, Interview Tips, OUTPUT, SCOPE_IDENTITY(), SQL Server · Posted in: Business Intelligence, Free, General, Interview QA, Quick Fix, Quick Tips, Script, SQL Server, SQL Server 2012, SQLServerPedia, T-SQL, Training, Tutorial
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 [...]
January 28, 2013
· Khilitchandra Prajapati · No Comments
Tags: Arithmetic Overflow, Business Intelligence, Error, General, SQL Server, SSIS · Posted in: Business Intelligence, Free, General, Interview QA, Quick Fix, Quick Tips, Script, SQL Server, SQL Server 2012, SQLServerPedia, SSAS, SSIS, SSRS, T-SQL, Tutorial
Tracking Down Ghost Characters: WHERE Clause Does Not Work Properly In T-SQL Query
Hi guys, I would like to share an experience with you when I was working on 1 SSIS related issue. Scenario was: I have to exclude some records from Table A by matching some keys of Table B. Â Table A Year Month CustomerCode <Extra Columns> 2012 JAN CX1000 Â 2012 JAN CX2000 Â 2012 JAN [...]
January 25, 2013
· Khilitchandra Prajapati · No Comments
Tags: Business Intelligence, Carriage Return, Query, Sanity Check, Special Characters, SQL Server, SQL Table Value with Carriage Return, SSIS, T-SQL, Where Clause · Posted in: Business Intelligence, Free, General, Interview QA, Quick Fix, Quick Tips, Script, SQL Server, SQL Server 2012, SSIS, T-SQL, Tutorial
View is not showing newly added column in table
Hi all, This is for the people who still use “Select * From Table” in their SQL Views. I don’t know what is the reason that you are using this way to create your views, but you could be the victim of this scenario (see… what title says). Let me show you how you can [...]
June 27, 2012
· Khilitchandra Prajapati · One Comment
Tags: INFORMATION_SCHEMA, INFORMATION_SCHEMA.COLUMNS, SQL Server, Stored Procedure, Tip, View · Posted in: General, Interview QA, Quick Fix, Quick Tips, Script, SQL Server, SQL Server 2012, T-SQL, Tutorial
What happens to IDENTITY Column Value When SQL Transaction Fails??
Hi All, I would like to share a simple interview question. This is very simple yet many developers overlooks or not taking care of it. Do you know what happens to identity value if transaction fails? Do you think that identity value of table is unchanged after failure? Before looking at the solution below, try [...]
March 15, 2012
· Khilitchandra Prajapati · No Comments
Tags: IDENTITY, Interview Question, SQL Server, Transaction Fails · Posted in: Interview QA, Quick Tips, SQL Server, T-SQL
Best Practices : Writing Stored Procedures
Hi All, I would like to share a handy list which I found today from my old collection during files clean-up. These are few quick tips that every developer should bear in mind while writing T-SQL codes particularly Stored Procedures. It’s always hard to find the best version of such list but we can definitely [...]
March 14, 2012
· Khilitchandra Prajapati · 2 Comments
Tags: Best Practices, Quick Tips, SQL Server, Stored Procedures · Posted in: SQL Server, Suggestion, T-SQL
“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 [...]
February 21, 2012
· Khilitchandra Prajapati · No Comments
Tags: General, SQL Server · Posted in: General, Quick Tips, SQL Server, T-SQL
When my SQL table was updated???
Here is very tiny SQL query which will help you find out when the tables are modified in the SQL server database. First you try the * (get all the columns) and then choose the selected ones. – List all the tables updated in SQL Server (in desc order) Use your Database Name Select * [...]
January 27, 2012
· Khilitchandra Prajapati · One Comment
Tags: sql database, sql query · Posted in: General, Interview QA, Quick Tips, SQL Server, Suggestion, T-SQL
