SQL with Manoj

Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

SQL Jokes!!!

January 10, 2011 5 comments

Yes, SQL Jokes… there are SQL jokes also… on internet that I collated from various sources and now they are in my single post, all below… read & enjoy!!!

>> A SQL query walks into a bar and sees two tables. He walks up to them and says ‘Can I join you?
Then a waitress walks up and says ‘Nice view !

>> Joins are what RDBMS’s do for a living.

>> And afterwards…like most men…he performed a Rollback and never Commited…

>> He picked those two tables after performing a full scan of the other tables in the room.

>> A SQL query walks into a bar on Valentine’s day, and sees two tables. She says “insert all this, you cartesian pigs!”

>> Q: Why do you never ask SQL people to help you move your furniture?
A: They sometimes drops the table

>> The Query was soon surrounded by subQueries – it was then he realized he had walked into an Array Bar.

>> SQL Table walks to a psychiatrist dr. Index
Table: “Doctor, I have a problem”
Dr: “what kind a problem?”
Table: “I’m a mess. I have things all over the place, i always look for my stuff”
Dr. “No problem. I will get you in order”.

>> Index and table are reading a book “index-sutra”
Table: Oh, baby tonight we can try a clustered position”
Index: “yeah baby, we can also try covered position”
Table: “or maybe multiple clustered position”
Index: “baby, yes, that’s the one. i’m just gonna call my friends”

>> Indexianina philosophy on Fill factor 50 = “Half empty or Half full?”

>> What does an execution plan say to t-sql query? “Go f**k yourself, if you are not happy with me”

>> Execution plan to t-sql query is like alter-ego to self.

>> What does table say to a trigger: “Hey, stop it, i’m full”.

>> When did God create the DBA ? A. The day before he had his rights revoked.

>> BIT says to itself: “When I grow up, i want to be BLOB”.

>> There are two types of DBAs:
1) DBAs that do backups
2) DBAs that will do backups

>> An Oracle DBA and a DB2 DBA walk into a bar. The barman asks them what they’d like to drink and a huge debate ensues on how to optimize the query. (Boom Tish)
Their mate the SQL Server DBA rolls in after about 15 minutes only to find them still arguing. After rolling his eyes at them, he walks up to the bar and greets the barman warmly. The barman asks him “Hey, you’re a DBA too aren’t you? Why aren’t you joining in?” The SQL Server DBA grins at the barman and says “Ah… well… the reason I’m late is that this always happens when these clowns go out drinking – I work with SQL Server, so I had the option of optimizing the query using a wizard before I got here! So mine’s a scotch!”

>> NULL is the Chuck Norris of the database – nothing can be compared to it.

>> What kind of undergarments to DBAs wear?
Well, (who’d have guessed it) Depends…

>> It is March 1st and the first day of DBMS school
The teacher starts off with a role call..

Teacher: Oracle?
“Present sir”
Teacher: DB2?
“Present sir”
Teacher: SQL Server?
“Present sir”
Teacher: MySQL?
[Silence]
Teacher: MySQL?
[Silence]
Teacher: Where the hell is MySQL
[In rushes MySQL, unshaven, hair a mess]
Teacher: Where have you been MySQL
“Sorry sir I thought it was February 31st“

… comments & more jokes are welcome!!!

>> Check & Subscribe my [YouTube videos] on SQL Server.

Categories: Uncategorized Tags: sql jokes

TSQL Interview Questions – Part 2

January 10, 2011 6 comments

Next part of TSQL Interview Questions, contd. from my previous post.

26. What are the virtual tables in Triggers?
Inserted & Deleted

27. What is benefit of a having stored-procedure?

28. Can stored-procedures be recursive? And upto how much level?
Yes, 32 levels.

29. How you can load large data in SQL Server?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format, check here: https://sqlwithmanoj.com/2011/09/09/bcp-in-out-queryout-the-bcp-util/

30. What is the trade-offs of a BCP command, when various users are loading data in a particular table at same time?

31. How can you copy schema from one SQL Server to another?
DTS, import/export wizard.
Scripting out Database objects.

32. What is the benefit of a Temporary Table, how would you define it?
Benefits of a Temp #Table: http://wp.me/p12rgl-r | YouTube

33. What is a table called that has ## before its name, what is its scope?
Table with ## (double pound signs) is called Global Temp table. Scope is outside the session but only till the original session lasts.

34. What is the scope of a temporary table?
Scope is limited to its session only.

35. What is Mutex error in Triggers?
MSDN link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c86c97a7-73ea-482e-9529-e2407bd7018c

36. What is the use of WITH (nolock)?
https://sqlwithmanoj.com/2013/10/04/difference-between-nolock-and-readpast-table-hints/
MSDN: http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx?wa=wsignin1.0

37. What are the various Isolation levels?
a) Read Uncommitted Isolation Level
b) Read Committed Isolation Level
c) Repeatable Read Isolation Level
d) Serializable Isolation Level
e) Snapshot Isolation Level
f) Read Committed Snapshot Isolation Level
http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

38. What are implicit & explicit cursors?
http://geekexplains.blogspot.com/2008/11/implicit-vs-explicit-cursors-static-vs.html

39. Define the life cycle of a Cursor.
https://sqlwithmanoj.wordpress.com/2010/10/24/sql-server-cursor-life-cycle/

40. How would you know that if a cursor is open or closed?

declare @mycursor cursor
declare @FirstName varchar(12)

select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable)

set @mycursor = cursor for
select FirstName from Person.Contact
select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed)

open @mycursor

select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row)

fetch next from @mycursor into @FirstName
select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row)

close @mycursor

select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed)

deallocate @mycursor

select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable)

select CURSOR_STATUS('variable','@nocursor') --// -3 (A cursor with the specified name does not exist)

41. How many non-clustered indexes can you have in a table?
Upto 999 non-clustered indexes can be created in a table in SQL Server 2012/2014.

42. What all indexes can you have in a table?
One Clustered Index, one or more than one non-clustered index, unique index, filtered, spatial, xml, etc.
MS BOL: http://msdn.microsoft.com/en-us/library/ms175049.aspx

43. How will you know what indexes a particular table is using?
1. By checking the graphical Execution Plan.
2. By using sys.dm_db_index_usage_stats DMV, link: https://sqlwithmanoj.com/2015/04/02/index-usage-stats-indexes-used-unused-and-updated/

44. What is the benefit of cross joins? How would you use a where clause with Cross Joins?
The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx
http://www.dotnetspider.com/forum/44591-why-we-use-cross-join-sqlserver.aspx

45. Difference between VARCHAR & VARCHAR2?
VARCHAR2 is specific to Oracle. MS SQL Server has VARCHAR & VARCHAR(MAX) data types.

46. What is de-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8c54d1d2-5fbd-4b62-a07f-16c34a863668
Normalization: https://sqlwithmanoj.com/2009/03/25/database-normalization/

47. How would you get @@ERROR & @@ROWCOUNT at the same time?
Both the statements should be applied immediately after the SQL query you want to get the details of. If either of them is used later you will miss the information. So to get @@ERROR and @@ROWCOUNT details you need to execute them together in one statement like:
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

48. What is Collation?
https://sqlwithmanoj.com/2010/02/23/collation-conflict-in-sql-server/
https://sqlwithmanoj.com/2010/03/25/change-a-database-collation/
MSDN: http://msdn.microsoft.com/en-us/library/aa174903(v=sql.80).aspx

49. Types of Replication? Difference between Merge & Transactional Replication.
MS BOL link: http://msdn.microsoft.com/en-us/library/ms165713(v=sql.90).aspx

50. What can you do with COALESCE function?
COALESCE() and difference with ISNULL(): https://sqlwithmanoj.com/2010/12/23/isnull-vs-coalesce/

… more questions on next post [Part-3].

Categories: Interview Q Tags: SQL Server Interview Questions

2010 in review…

January 2, 2011 Leave a comment

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

Healthy blog!

The Blog-Health-o-Meter™ reads This blog is on fire!.

Crunchy numbers

A helper monkey made this abstract painting, inspired by your stats.

A Boeing 747-400 passenger jet can hold 416 passengers. This blog was viewed about 1,400 times in 2010. That’s about 3 full 747s.

In 2010, there were 25 new posts, growing the total archive of this blog to 37 posts. There were 3 pictures uploaded, taking up a total of 236kb.

The busiest day of the year was December 21st with 85 views. The most popular post that day was TSQL Interview Questions.

Where did they come from?

The top referring sites in 2010 were social.msdn.microsoft.com, en.wordpress.com, social.technet.microsoft.com, google.co.in, and google.com.

Some visitors came searching, mostly for sql server denali, manub22, sql server denali features, t sql interview questions, and smtp security settings sql 2008 from_address.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

TSQL Interview Questions December 2010
2 comments

SQL Server 11, codename Denali – New Features November 2010

Linked Server in MS SQL Server November 2010

Combine multiple ROWS to CSV String… and vice-versa September 2010
4 comments and 1 Like on WordPress.com,

Database Mail Setup – SQL Server 2005 September 2010
5 comments

Categories: SQLwithManoj Tags: annual report, SQLwithManoj in 2010

Why UNION ALL is faster than UNION?

December 30, 2010 9 comments

UNION and UNION ALL are used to combine data or record sets from two different tables. One could combine more than 2 tables. Just like JOINS, UNION combines data into a single record-set but vertically by adding rows from another table. JOINS combine data horizontally by adding columns from another table.

UNION insures you get DISTINCT records from both the tables.
UNION ALL pulls out all records from both the tables with duplicates.

SQL Scripts below proves why UNION ALL is faster than UNION:

-- Create First table: #tempTable1
select FirstName, LastName into #tempTable1
from Person.Contact
where ContactID <= 100 -- Create Second table: #tempTable2 select FirstName, LastName into #tempTable2 from Person.Contact where ContactID > 100 and ContactID <= 200

Comparison:

SET STATISTICS PROFILE ON

-- Using UNION - Query Cost: 35%
select * from #tempTable1
UNION
select * from #tempTable2

Query Plan for UNION:
  |--Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC))
       |--Concatenation
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

-- Using UNION ALL - Query Cost: 13%
select * from #tempTable1
UNION ALL
select * from #tempTable2

Query Plan for UNION ALL:
  |--Concatenation
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

-- Using UNION ALL with DISTINCT - Query Cost: 52%
select DISTINCT * from #tempTable1
UNION ALL
select DISTINCT * from #tempTable2

Query Plan for UNION ALL with DISTINCT:
  |--Concatenation
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable1].[FirstName] ASC, [tempdb].[dbo].[#tempTable1].[LastName] ASC))
       |    |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable2].[FirstName] ASC, [tempdb].[dbo].[#tempTable2].[LastName] ASC))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

SET STATISTICS PROFILE OFF

-- Final Cleanup
drop table #tempTable1
drop table #tempTable2

The Query plan for:
– UNION ALL SELECT above shows it just does a concatenation.
– UNION SELECT above shows it does a SORT on final concatenation.
– UNION ALL with DISTINCT SELECT above shows it does a SORT of individual tables than does the final concatenation.

The Query cost with:

– UNION ALL = 13%
– UNION = 35%
– UNION ALL with DISTINCT = 52%

This proves that:

UNION ALL is faster and more optimized than UNION. But this does not mean you use UNION ALL in every scenario.
UNION is not equivalent to “UNION ALL with DISTINCT”.

Categories: Differences, Optimization Performance Tags: UNION, UNION ALL, union vs union all

ISNULL vs COALESCE – expressions/functions in SQL Server

December 23, 2010 20 comments

ISNULL & COALESCE with some common features makes them equivalent, but some features makes them work and behave differently, shown below.

– Similarity
Both can be use to build/create a CSV list as shown below:

USE [AdventureWorks]
GO

DECLARE @csv VARCHAR(2000)

SELECT @csv = ISNULL(@csv + ', ', '') + FirstName
FROM Person.Contact
WHERE ContactID <= 10
ORDER BY FirstName

select @csv

set @csv=NULL
SELECT @csv = COALESCE(@csv + ', ', '') + FirstName
FROM Person.Contact
WHERE ContactID <= 10
ORDER BY FirstName

select @csv

Both will give the same output:
Carla, Catherine, Frances, Gustavo, Humberto, Jay, Kim, Margaret, Pilar, Ronald

– Difference #1
ISNULL accepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter.
COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter, example below.

DECLARE @str1 VARCHAR(10), @str2 VARCHAR(10)

-- ISNULL() takes only 2 arguments
SELECT ISNULL(@str1, 'manoj') AS 'IS_NULL' -- manoj

-- COALESCE takes multiple arguments and returns first non-NULL argument
SELECT COALESCE(@str1, @str2, 'manoj') AS 'COALESCE' -- manoj

-- ISNULL() equivalent of COALESCE, by nesting of ISNULL()
SELECT ISNULL(@str1, ISNULL(@str2, 'manoj')) AS 'IS_NULL eqv' -- manoj

– Difference #2
ISNULL does not implicitly converts the datatype if both parameters datatype are different.
On the other side COALESCE implicitly converts the parameters datatype in order of higher precedence.

-- ISNULL Does not do Implicit conversion
select ISNULL(10, getdate()) as 'IS_NULL' -- Errors out

Error Message:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed.
Use the CONVERT function to run this query.

-- COALESCE Does Implicit conversion and gets converted to higher precedence datatype.
select COALESCE(10, getdate()) as 'COALESCE' -- 1900-01-11 00:00:00.000, outputs 10 but convert it to datetime [datetime > int]
select COALESCE(getdate(),10) as 'COALESCE' -- {Current date} 2010-12-23 23:36:31.110
select COALESCE(10, 'Manoj') as 'COALESCE' -- 10 [int > varchar]
select COALESCE('Manoj',10) as 'COALESCE' -- Errors out, it does an implicit conversion, but cannot change 'Manoj' to Integer.

Error Message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Manoj' to data type int.

– Difference #3
Similar to above point ISNULL always returns the value with datatype of first parameter.
Contrary to this, COALESCE returns the datatype value according to the precedence and datatype compatibility.

DECLARE @str VARCHAR(5)

SET @str = NULL

-- ISNULL returns truncated value after its fixed size, here 5
SELECT ISNULL(@str, 'Half Full') AS 'IS_NULL' -- Half

-- COALESCE returns full length value, returns full 12 char string
SELECT COALESCE(@str, 'Half Full') AS 'COALESCE' -- Half Full

– Difference #4
According to MS BOL, ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. Thus to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute.

-- ISNULL() is allowed in computed columns with Primary Key
CREATE TABLE T1 (
	col1 INT,
	col2 AS ISNULL(col1, 1) PRIMARY KEY)

-- COALESCE() is not allowed in non-persisted computed columns with Primary Key
CREATE TABLE T2 (
	col1 INT,
	col2 AS COALESCE(col1, 1) PRIMARY KEY)

Error Message:
Msg 1711, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on column 'col2' in table 'T2'. 
The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

-- COALESCE() is only allowed as persisted computed columns with Primary Key
CREATE TABLE T2 (
	col1 INT,
	col2 AS COALESCE(col1, 1) PERSISTED PRIMARY KEY)

-- Clean up
DROP TABLE T1
DROP TABLE T2

MSDN BOL links:
ISNULL: http://msdn.microsoft.com/en-us/library/ms184325.aspx
COALESCE: http://msdn.microsoft.com/en-us/library/ms190349.aspx
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE