Archive

Posts Tagged ‘SQL’

My Notes from the first UG meet of SQLBangalore

November 25th, 2012 4 comments

Yesterday I had the opportunity to attend Bangalore’s first SQL UG Meet.  It was amazing, lots of learning, and I had the opportunity to meet our favorite leads and see some of the people I have never heard about.  Lynn Langit and Raj Chauduri are those I see for the first time. Raj is an amazing SQL expert, his talks were fully on the practical side of the technology. You can find the full agenda of the event in my earlier post here.

Two rock starts of the Bangalore IT community took their debut as speaker.  Both of them did a great job. Finally there were cool snaps taken by event photographer Sandip (just kidding) and all of them is posted in Group Page.

 

Below are my notes from the sessions:

1) Short Circuit in SQL Server

SELECT * WHERE 1 / 0 = 1 AND 1 = 2    => No Error

SELECT * WHERE 1 / 0 = 1 OR 1 = 2       => Throws error

What I understood is  in such queries for an AND to be True, both condition should be true, SQL evaluates one condition and if its false, it doesn’t go to evaluate second, so there will be no error and no output as the query returns no match. when 1=2 is false, it does not have to check 1/0 = 0.

Same time when you use OR, or the query to be true one of the condition needs to be true, so for that SQL has to evaluate both, and while doing that in above example it reaches division by zero here. when 1=2 is false, it still need to check 1/0=0 because of OR.

What I am not yet clear is how can is say in the above Queries 1=2 is evaluated first, is there any precedence pattern for such queries.

2) DISTINCT is not a function when used in a SELECT Statement. (DISTINCT is used to get unique rows.)
3) New Functions in SQL Server 2012

EOMONTH() – this function returns the last day of the month, and can also be used to get last day of any given month, it accepts a second parameter, by which you can move months ahead or backwards.

For Example:

EOMONTH(GETDATE()) – End of this month

EOMONTH(GETDATE(),1) – End of Next Month

EOMONTH(GETDATE(),-1) – End of Last Month

4) When to use GETDATE()

When system generates the Date and its stored in the system, use GETDATE(). And to avoid time zone issues, always use GETUTCDATE() which will avoid time zone issues.

5) JOIN

I am a pure SQL User in most of my interactions with SQL, and I use SQL queries to retrieve data from tables for reporting or to make decisions. JOIN was something I wanted to learn as I knew it’s the way to collect related data from multiple tables. In Raj Chauduri’s session I have learned a major part of my Basics about JOINS.

To make a note to myself:

SELECT * FROM [Events] JOIN [Speakers] ON [Events].[SpeakerId] = [Speakers].[Id]

Note:- Best practice – Never use * in any SELECT statements.

What does a JOIN do in SQL, it first creates a Cartesian Product  of tables. What does it mean is each row from the second table is combined with each row of first table and with all fields. Then the Join Predicate is applied, meaning – from the combined table only rows matching the JOIN Condition is retrieved.

 There are three types of JOINS in SQL:  INNER, OUTER, and FULL

6) There are objects which are not migrated when you migrate a Database from one SQL Instance to Another:
  1. Logins
  2. Jobs
  3. Linked Servers
  4. CLR
  5. DB Mail
  6. Etc..

Contained databases in SQL Server 2012 comes as rescue for this, once this feature is enabled these configuration and metadata settings are included within the database.

Logins leaving behind when migrating a database was one problem I did see at job with SQL Server 2005, now I know what is the solution at least with SQL Server 2012.

7) ConlumStore Index makes you increase the speed of query without reducing the weight of the query.

 

I have posted in SQLBangalore to get answer to what I forgot, the products of the code names Ex-Velocity and Hackathone  – I will update here once I get an answer. Before I publish this post itself, I got the response:

Link Reference

 

A question asked by Vinod in his T-SQL Session:

Can you create a view when there is no table?

I guess yes, we can. As I understand a View is just like a definition or query, so you can create it when there is no table, but while retrieving the data from the View the Table should be existing.

I will update this once I get the correct answer.

The event was full of SQL and not to mention Pinal’s session was as usual full of laughter, but all those were well related to the subject he started from the 46th slide.

 

Below are a part of my collection of tweets from #SQLBangUG:

 

We will have such great meetings in future as well.

Happy Weekend!

 

Categories: Community Tags: