SQL code Formatter Tools with SQL SERVER

Often while debugging the SQL code written another developer which is not formatted, formatting will help in analyzing and debugging the code effectively. Also writing the SQL code with proper formatting improves the readability and is a best practice.

Found couple of online code formatting tools which formats SQL code very good and can be handy. 

Tool 1: Instant SQL Formatter
Tool 2: SQLinForm

You may try the above tools next time when you need to format your SQL code.

Table Valued Parameters in Sql server 2008

SQL Server 2008 introduced a nice feature called Table Valued Parameters created using user defined table type.

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. There are tons of benefits, and here is the list provided by BOL:

• Do not acquire locks for the initial population of data from a client
• Provide a simple programming model
• Enable you to include complex business logic in a single routine
• Reduce round trips to the server
• Can have a table structure of different cardinality
• Table valued parameters are strongly typed
• Enable the client to specify sort order and unique keys

Reference URL:http://beyondrelational.com/blogs/nakul/archive/2011/02/21/table-valued-parameters-to-a-table-valued-function-underappreciated-features-of-microsoft-sql-server.aspx

T-SQL Exception Handling

Prior toSQL Server 2005 i.e. in SQL Server 2000 / 7.0, exception handling is a very primitive task with handful of limitations.

SQL Server 2005 architecture is now started using Visual Studio platform and based on Microsoft  .NET framework.

One of the features support is that now SQL Server can leverage on TRY..CATCH functionality provided by the .NET framework.

Following are the inbuilt methods supported by this TRY..CATCH functionality and should be used within CATCH block.

ERROR_NUMBER()

,ERROR_SEVERITY()

,ERROR_STATE()

,ERROR_PROCEDURE()

,ERROR_LINE()

,ERROR_MESSAGE()

difference between len and datalength

DATALENGTH() returns length of string in bytes, including trailing spaces. LEN() returns length of  string in characters excluding trailing spaces.

 

One more difference  varchar vs nvarchar with sql server 

What is heap?

A table without clustered index is called heap.

HCL Interview faced questions

1.create table emp(empid int,empname varchar(20));
create table dept(empid int,deptid int,deptname varchar(20));

insert into emp
select 1,'ravi'
union all
select 2,'chandra'
union all
select 3,'dama';

insert into dept
select 1,1,'IT'
union all
select 2,2,'Sales'
union all
select null,2,'Sales';

select * from emp where empid  not in (select empid from dept)



Question: What is the output?

I told wrong answer....

Original answer: empid   empname  (no rows are retrieving)

2.Write a query Highest 9th salary employee  from employee table (with out use top and max keywords)

3. Difference between len and datalength? and give me syntax?


4. What page and Extent in sql server 2008?

5 Write triggers 

   We have 2 tables
table 1: customerBalance (CustomerId intger,CurrentBalance numeric(22,2))
table 2. CustomerAuditBalance(AuditId intger identity(1,1),CustomerId integer,Old_Balance numeric(22,2),New_Balance(22,2),Createddate datetime default getdate())

Write triggers
Q1)Dont allow negative balance of customer withdraw time?
Q2) All success withdraw transactions need to insert in audit table?






 

Compare Stored Procdure and Views?

Stored Procedure,  View both can return the data from tables.
Stored Procedure can return scalar values as output parameters , views can not.
Stored Procedure can be parametrized whereas views can not be. (there is workaround for
parametrized views , but with certain limitations.)

Both hide the database structure from the user and provides the security.

Views can be indexed, Stored Procedures can not be.

Stored Procedures are better than views in performance, particularly on large tables.

Temporary tables vs table variables in SQL Server

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables. Each of the four table options has its own purpose and use, and each has its benefits and issues:

•Normal tables are exactly that, physical tables defined in your database.

•Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.

•Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.

•Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Get top 5 records in sql server

The TOP clause is used to specify the number of records to return.

The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Note: Not all database systems support the TOP clause.

SQL Server Syntax

SELECT TOP number|percent column_name(s)

FROM table_name

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax

SELECT column_name(s)

FROM table_name

LIMIT number

Example

SELECT *

FROM Persons

LIMIT 5

Oracle Syntax

SELECT column_name(s)

FROM table_name

WHERE ROWNUM <= number

Example

SELECT *

FROM Persons

WHERE ROWNUM <=5

What is the difference between SET and SELECT when assigning values to variables, in T-SQL?

Though you can assign values to local variables using either SET/SELECT,
There are few differences.

SET can be used for assigning one variable at a time whereas Select can be used for
multiple variable Assignments

Moving on to other differences between SET and SELECT: When using a query to
populate a variable, SET will fail with an error, if the query returns more than one
value. But SELECT will assign one of the returned rows and mask the fact that the
query returned more than one row. As a result, bugs in your code could go unnoticed
with SELECT, and this type of bugs are hard to track down too.

Here is another difference with respect to assigning values based on a query,
especially when the query doesn't return any rows. When Assigning Variable using
SET for a value returned by a subquery, and sub query does not return any value,
null will be assigned to variable. In case of SELECT, if subquery does not return any
rows, previously assigned value will still remain.

A single SELECT statement assigning values to 3 different variables, is much faster
than 3 different SET statements assigning values to 3 different variables. In this
scenario, using a SELECT is at least twice as fast, compared to SET.

Compare 2 databases indexes

Here  SR91005 is one database ,SR91006is 2nd Database

 

SELECT

tbl.name tablename,

i.name AS [Name],

'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' +quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' +quotename(SCHEMA_NAME(tbl.schema_id),'''') + ']' + '/Index[@Name=' + quotename(i.name,'''') + ']' AS [Urn],

CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],

i

.is_unique AS [IsUnique],

CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex],

case UPPER(ISNULL(xi.secondary_type,'')) when 'P' then 1 when 'V' then 2 when 'R' then 3 else 0 end AS [SecondaryXmlIndexType]

FROM

SR91005.sys.tables AS tbl

INNER JOIN SR91005.sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)

LEFT OUTER JOIN SR91005.sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id

WHERE

NOT EXISTS (SELECT 1

FROM SR91006.sys.tables AS tbl2

INNER JOIN SR91006.sys.indexes AS i2 ON (i2.index_id > 0 and i2.is_hypothetical = 0) AND (i2.object_id=tbl2.object_id)

WHERE tbl2.name = tbl.name

AND i2.name = i.name)

order

by tbl.name

Find and delete duplicate records in sql server

;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)

AS

(

SELECT

        Product, SaleDate, SalePrice,

Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)

FROM SalesHistory

)

DELETE FROM SalesCTE

WHERE Ranking > 1