Archive

Archive for January 10, 2011

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:

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].