SQL SERVER – Beginning SQL 2012 – Basics of CONVERT and FORMAT Function – Abstract from Joes 2 Pros Volume 4

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 4

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

From the September 17, 2011 blog post on the new SQL 2012 FORMAT function we learned how to format currency and time using different cultures. This is an improvement on what came before and also gives us new possibilities for getting date labels without needing to use DATEPART. In this post we will compare the FORMAT function to the previous techniques and also show you an easy way to grab the part of the date you need for reports.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 4” in the file SQLQueries2012Vol4Chapter8.1-10Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs

CONVERT function basics

Do you think January 5th 2013 should be shown as 1/5/2013 or 5/1/2013? The best answer is to each their own. The FORMAT function lets you pick the style, or better yet it can be relevant to the culture of the computer running the query.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 101) AS CharDate
 FROM Employee
  EmpID FirstName LastName LocationID ManagerID CharDate
1 1 Alex Adams 1 4 01/01/2001
2 2 Barry Brown 1 11 08/12/2002
3 3 Lee Osako 2 11 09/01/1999
4 4 Dave Kinnison 1 11 03/16/1996
5 5 Eric Bender 1 11 05/17/2007
6 6 Lisa Kendall 4 4 11/15/2001

 

 

 

 

 

 

20 rows

It looks like Barry Brown’s hire date is August 12, 2002. That is because in the USA culture we specify month/day/year which makes 08/12/2002 as August 12th 2002. However, if we were in Europe (where it’s day/month/year), 08/12/2002 would look a lot like December 8, 2002.

Let’s try a different formatting style using the CONVERT function. By changing the formatting style from 101 to 103 in the same query, notice we retrieve the date in the day, month, and then year.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 103) AS CharDate
 FROM Employee
  EmpID FirstName LastName LocationID ManagerID CharDate
1 1 Alex Adams 1 4 01/01/2001
2 2 Barry Brown 1 11 12/08/2002
3 3 Lee Osako 2 11 01/09/1999
4 4 Dave Kinnison 1 11 16/03/1996
5 5 Eric Bender 1 11 17/05/2007
6 6 Lisa Kendall 4 4 15/11/2001

 

 

 

 

 

 

20 rows

As we saw, style 103 returns the European day/month/year, while style 109 returns 3 letter abbreviated month/integer day/ integer year as seen in the comparison.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 103) AS CharDate
 FROM Employee
 WHERE EmpID = 2
SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 109) AS CharDate
 FROM Employee
 WHERE EmpID = 2

j2p2012 4 1 SQL SERVER   Beginning SQL 2012   Basics of CONVERT and FORMAT Function   Abstract from Joes 2 Pros Volume 4

FORMAT with Culture Dates Basics

Now we will take the previous code and utilize the new FORMAT function. Instead of going through the process of converting the field we will simply set the format we are looking for. Here we replaced CONVERT with FORMAT and instead of naming it an NVARCHAR and picking a style, we simply (in parentheses) name the field, and in single quotes we set the parameter ‘d’ for day to achieve the same result.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 101) AS CharDate
 FROM Employee
 WHERE EmpID = 4
SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd') AS CharDate
 FROM Employee
 WHERE EmpID = 4

j2p2012 4 1 SQL SERVER   Beginning SQL 2012   Basics of CONVERT and FORMAT Function   Abstract from Joes 2 Pros Volume 4

In that last example we choose d for day. You can also choose m (month) to return the month and day or y (year) as your parameters to return month and year. In all the examples so far the style is in the “en-us” culture because that’s the culture set on the server we used. It is not normally necessary to set this function however; there is a third optional parameter. What if we had done ‘en-gb’ for Great Britain? You can see here, the result set has a [HireDate] of 16/03/1996 and the numbers are separated with forward slashes ‘/’ as would be customary in Great Britain.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd', 'en-gb') AS CharDate
 FROM Employee
 WHERE EmpID = 4
  EmpID FirstName LastName LocationID ManagerID CharDate
1 4 Dave Kinnison 1 11 16/03/1996

 

 

 

 

 

 

1 rows

Let’s change it up a bit more. Let’s use the style format ‘de-de’, for German, Germany. What does that look like? We can see it gives us day, month and year, separated by dots as would be customary in Germany.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd', 'de-de') AS CharDate
 FROM Employee
 WHERE EmpID = 4
  EmpID FirstName LastName LocationID ManagerID CharDate
1 4 Dave Kinnison 1 11 16.03.1996

 

 

 

 

 

 

1 rows

FORMAT with Months

You have seen what happened to the [HireDate] field when we ran it through the format with the ‘d’ and ‘en-us’. This is great for days, but what if we put a series of “MMMM” in there? Let’s put four M’s for month and simplify the fields to show just the [HireDate] and formatted fields. What will the expression field look like? As you can see, this format will pull up just the month fully spelled out. The first record says January, the second record August, and so on.

SELECT HireDate,
 FORMAT(HireDate, 'MMMM', 'en-us') AS CharDate
 FROM Employee
  HireDate CharDate
1 2001-01-01 00:00:00.000 January
2 2002-08-12 00:00:00.000 August
3 1999-09-01 00:00:00.000 September
4 1996-03-16 00:00:00.000 March
5 2007-05-17 00:00:00.000 May
6 2001-11-15 00:00:00.000 November

 

 

20 rows

Let’s try a few other combinations. What does three M’s look like? It shows us a 3 letter month abbreviation, like Jan, Aug, Sep. Let’s try two M’s. This format returns a two digit numeric, like 01 for January and 08 for August. What does a single M return? This returns the whole month spelled out, with the day. For example, January 01 and August 12 and so forth.

BE CAREFULL (MM is not mm)

SELECT HireDate, FORMAT(HireDate, 'MMM', 'en-us') AS CharDate
 FROM Employee
SELECT HireDate, FORMAT(HireDate, 'MM', 'en-us') AS CharDate
 FROM Employee
SELECT HireDate, FORMAT(HireDate, 'M', 'en-us') AS CharDate
 FROM Employee
  HireDate CharDate
1 2001-01-01 00:00:00.000 Jan
2 2002-08-12 00:00:00.000 Aug
  HireDate CharDate
1 2001-01-01 00:00:00.000 01
2 2002-08-12 00:00:00.000 08
  HireDate CharDate
1 2001-01-01 00:00:00.000 January 01
2 2002-08-12 00:00:00.000 August 12

 

 

60 rows

Up to this point the month, day and year formats all seem straight forward. There are a few things to be aware of that can get a little tricky. What happens if this ‘M’ is lower case? As you can see, there is no real change in the result. How about ‘mm’? Now we are getting a different result. The lower case ‘mm’ calls for a return in minutes. Since the examples returned are all zeros it’s really hard to tell exactly what this new field means because the hire dates don’t go down to the minutes. To really see this example, let’s utilize GETDATE() and we can see the formatted field returns 06 since this was run on Oct 6th, 2012 at 11:22pm.

SELECT HireDate, FORMAT(HireDate, 'mm', 'en-us') AS CharDate
 FROM Employee
SELECT GETDATE(), FORMAT(GETDATE(), 'mm', 'en-us')
HireDate CharDate
2001-01-01 00:00:00.000 00
2002-08-12 00:00:00.000 00
(No column name) (No column name)
2012-10-06 21:22:38.643 22

 

21 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Beginning SQL 2012 – Spatial Unions and Collections – Abstract from Joes 2 Pros Volume 3

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 3

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

In business we often hear the phrase, “We had a good quarter.” Immediately, we know this means a three month span where sales and profits have been aggregated together for the company. Take a company like Costco that might have $65 billion in total sales during the 4th quarter (October, November and December). Of course, this total comes not from a single sale of a 65 billon dollar yacht, rather from millions of sales for common items like snacks, drinks, clothes, and light bulbs. We know how to use GROUP BY and SUM to calculate totals and combine similar data. In the case of Costco we group by calendar quarter and then sum on the sales. Although aggregates are commonly used with numbers, they can also be used with spatial land coordinates to assemble them together, much like a jigsaw puzzle.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 3” in the files SQLQueries2012Vol3Chapter6.0Setup.sql and SQLQueries2012Vol3Chapter6SpecialSetup.sql.

Spatial Unions and Collections

My elementary school was right across the street from my home. The playground in back of the school was very large and had 3 full sized football (soccer) fields. Not all of this land belonged to the school. Across from the play fields was the Boys and Girls Club, which is a charity for after school kids’ projects for parents that need to work late. Since they are typically not open during school hours, the land they owned was able to be enjoyed during the day by my school. In this case, the land of the school and the club are aggregated to make one giant play and exercise area. Shapes that overlap or touch can be aggregated into one single shape (known as a polygon).

OGC (Open Geospatial Consortium) Functions Basics

In this example of the school and club, we aggregated the land for two owners. This ability became available with SQL Server 2008. There would be no way to aggregate the land for three owners into a single query. Let’s see how SQL Server aggregated spatial results between two shapes with the following examples.

SELECT * FROM HumanResources.RoomChart

j2p2012 3 1 SQL SERVER   Beginning SQL 2012   Spatial Unions and Collections   Abstract from Joes 2 Pros Volume 3

By narrowing our focus to look at only the Water Tower and the Yard shapes, we can easily see that the Water Tower is only part way on the property of the Yard owner.

SELECT *
 FROM HumanResources.RoomChart
WHERE R_ID IN (5,9)

j2p2012 3 2 SQL SERVER   Beginning SQL 2012   Spatial Unions and Collections   Abstract from Joes 2 Pros Volume 3

What happens when an object occupies space in a portion of another object? When we ran the previous code, the results show how the Water Tower is partially in the Yard. Will the STContains() function see if  the Water Tower is contained within the Yard? Will the answer to this question be YES, or NO? How about the Parking Lot, is it contained in the Yard? The results for the following code sample is shown below:

DECLARE @Yard GEOMETRY
DECLARE @Wtr GEOMETRY
DECLARE @Prk GEOMETRY
SELECT @Yard = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 5
SELECT @Wtr = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 9
SELECT @Prk = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 8
SELECT @Yard.STContains(@Wtr) AS IsTowerInYard,
@Yard.STContains(@Prk) AS IsParkInYard
  IsTowerInYard IsParkInYard
1 0 1

 

 

1 rows

We can see how to bring two shapes together by using the STUnion() function with the GEOMETRY data stored in the Yard and Water Tower fields. Building the query is identical to how we previously determined if the Park or the Water Tower were contained in the Yard with the STContains() function. All we need to do is replace the STContains() function with the STUnion() function. The graphical view can be seen if you run the code and click on the “Spatial results” tab.

DECLARE @Yard GEOMETRY
DECLARE @Wtr GEOMETRY
SELECT @Yard = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 5
SELECT @Wtr = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 9
SELECT GEOMETRY = @Yard.STUnion(@Wtr)

j2p2012 3 3 SQL SERVER   Beginning SQL 2012   Spatial Unions and Collections   Abstract from Joes 2 Pros Volume 3

Aggregate Unions

There are other functions available for aggregating shapes together that eliminate the need for declaring and selecting variables.

Let’s look at an example of how to use the UnionAggregate() method and look at what the results are by running the this code.

SELECT GEOMETRY::UnionAggregate(RoomLocation)
FROM HumanResources.RoomChart
WHERE R_ID IN (5,9)
Aggregating more than 2 shapes

Let’s look at example of some business case uses when working with shapes. We are going to use a special table named SkiAreas that is normally not in the dbBasics database. To get this table you will need to run the SQLQueries2012Vol3Chapter6SpecialSetup.sql script. The data contained in the SkiAres will help solidify how we can easily combine shapes together to support different business requirements.

First, we will need to find out what kind of data resides in this table. The next code sample will show all the fields and records of the SkiAreas table.

USE dbBasics
GO
SELECT *
FROM SkiAreas
  ShapeID ShapeCode ShapeName ShapeParentCode ShapeOgraphy ShapeMetry
1 1 ID Idaho State US 0xE6100…003 0xE610…003
2 2 MT Montana State US 0xE6100…003 0xE610…003
3 3 Kt Kootenai County ID 0xE6100…003 0xE610…003
4 4 Sh Shoshone County ID 0xE6100…003 0xE610…003
5 5 Cl Clearwater County ID 0xE6100…003 0xE610…003
6 6 Ln Lincoln County MT 0xE6100…003 0xE610…003
7 7 Sd Sanders County MT 0xE6100…003 0xE610…003
8 8 M1 Runt Mountain ** 0xE6100…003 0xE610…003

 

 

 

 

 

 

8 rows

With five counties in two different states, a travel brochure would normally show three of these counties in Idaho and two counties in Montana . We can get a little more specific, by looking for just the ShapeName and the ShapeMetry fields. Click on the spatial results tab and in the ‘Select label column:’ drop-down menu choose the ShapeName field to see the names for the shapes returned by the query.

SELECT ShapeName, ShapeMetry
FROM SkiAreas
WHERE ShapeName LIKE '%County'

 j2p2012 3 4 SQL SERVER   Beginning SQL 2012   Spatial Unions and Collections   Abstract from Joes 2 Pros Volume 3

Now that we have a working query that displays each county in the SkiAreas table. We can easily convert it to a query using the UnionAggregate() function to combine each county into a single shape for this marketing region.

SELECT GEOMETRY::UnionAggregate(ShapeMetry)
FROM SkiAreas
WHERE ShapeName LIKE '%County'

j2p2012 3 5 SQL SERVER   Beginning SQL 2012   Spatial Unions and Collections   Abstract from Joes 2 Pros Volume 3

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Beginning SQL 2012 – Aggregation Functions – Abstract from Joes 2 Pros Volume 2

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 2

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 2” in the file SQLQueries2012Vol2Chapter5.1Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs

Aggregation Functions

Most people are familiar with aggregation using a GROUP BY with some of the fields using an aggregated function like SUM or COUNT. With GROUP BY you are limited to including only aggregated data in your result set. Of the two examples below we see the first query runs and gets a total of the amounts but the second query throws an error.

SELECT SUM(Amount) AS SumOfGrants
FROM [Grant]
Messages
193700.00

1 rows

SELECT *, SUM(Amount) 
FROM [Grant]
WHERE GrantID = '001'
Messages
Msg 8120, Level 16, State 1, Line 1
Column ‘Grant.GrantID‘ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

0 rows

The OVER() clause

If we want to show the total amount next to every record of the table – or just one record of the table we can use the SUM aggregation function with OVER() instead of GROU P BY,

SELECT *, SUM(Amount) OVER()
FROM [Grant] 

j2p2012 2 1 SQL SERVER   Beginning SQL 2012   Aggregation Functions   Abstract from Joes 2 Pros Volume 2

Adding PARTITION BY to OVER()

Adding the OVER() clause allows us to see the total amount next to each grant record. OVER() applies the aggregation SUM(Amount) across all rows of a query. Here we have left the parentheses blank, which causes OVER() to apply the aggregation across all rows of each query. We could get the sub total for each employee. See how Employee 10 has the biggest grant of $41,000. If you look at Employee 7 who has three smaller grants but a total of more than $41,000.  We can see this example where all of Employee 7’s individual grants are next to his total using the following code:

SELECT *, SUM(Amount) OVER(PARTITION BY EmpID)
FROM [Grant]

j2p2012 2 2 SQL SERVER   Beginning SQL 2012   Aggregation Functions   Abstract from Joes 2 Pros Volume 2

Market Share (Comparing individuals with totals)

By listing the total amount of all grants next to each individual grant, we automatically get a nice reference for how each individual grant compares to the total of all JProCo grants. The sum of all 10 grants is $193,700. Recall the largest single grant (007) is $41,000. Doing the quick math in our head, we recognize $41,000 is around 1/5 of ~$200,000 and guesstimate that Grant 007 is just over 20% of the total.

j2p2012 2 3 SQL SERVER   Beginning SQL 2012   Aggregation Functions   Abstract from Joes 2 Pros Volume 2

Thanks to the OVER clause, there’s no need to guess. We can get the precise percentage. To accomplish this, we will add an expression that does the same math we did in our head.  We want the new column to divide each grant amount by $193,700 (the total of all the grants). The new column is added and confirms our prediction that Grant 007 represents just over 21% of all grants.

SELECT *, SUM(Amount) OVER() AS CompanyTotal, Amount / SUM(Amount) 
OVER() 
FROM [Grant]

j2p2012 2 4 SQL SERVER   Beginning SQL 2012   Aggregation Functions   Abstract from Joes 2 Pros Volume 2

Notice that the figures in our new column appear as ratios. Percentages are 100 times the size of a ratio. Example: the ratio 0.2116 represents a percentage of 21.16%. Add a * 100 to the expression to turn the ration into a percentage.  To finish, give the column a descriptive title, PctOfTotal.

SELECT *,
 SUM(Amount) OVER() AS CompanyTotal,
 Amount / SUM(Amount) OVER() * 100 AS PctOfTotal
 FROM [Grant]
  GrantID GrantName EmpID Amount CompanyTotal PctOfTotal
1 001 92 Purr_Scents %% team 7 4750.00 193700.00 2.45
2 002 K_Land fund trust 2 15750.00 193700.00 8.13
3 003 Robert@BigStarBank.com 7 18100.00 193700.00 9.34
4 004 Norman’s Outreach NULL 21000.00 193700.00 10.84
5 005 BIG 6’s Foundation% 4 21000.00 193700.00 10.84
6 006 TALTA_Kishan Internatio… 3 18100.00 193700.00 9.34
7 007 Ben@MoreTechnology.com 10 41000.00 193700.00 21.16

 

 

 

 

 

 

10 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Beginning SQL 2012 – Why we use Code Comments – Abstract from Joes 2 Pros Volume 1

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 1

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

Old classic movies utter this famous phrase “Gentlemen, this is off the record”.  In movies this is used when talking to the press and letting them know a certain comment or two will be said, however it is not meant for publication in the media. Sometimes, we want to use words or phrases within a query window that we want SQL Server to ignore when executing the code. Fortunately, SQL Server allows us to write words or phrases that are “off the record”, with a coding technique called a commenting.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “Beginning SQL 2012 Joes 2 Pros tutorial series Volume “.

In the file SQLQueries2012Vol1Chapter11.0Setup.sql, if you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

Why we use Code Comments

Thus the main purpose of commenting techniques is to allow us to write words or phrases that are often descriptive notes or warnings of what the code is doing, when to run it and why it is necessary. This is a big benefit when other people, such as our team members, supervisors, or software testers are reviewing the code for their own purposes. In other words, comments are commonly used as a mini informational ‘readmefile allowing other people reading it to learn more about the code being used.

Single-Line Comments for Communicating

In the example below we see a query with a single-line comment written as the first line, describing the purpose of the code below it. The comment in this example shows other developers and testers what action the code is expected to accomplish. Indeed, when we run this code block, the result set contains all the fields from the Employee table and the Location table based on the matching LocationID field using the INNER JOIN keyword.

j2p2012 1 SQL SERVER   Beginning SQL 2012   Why we use Code Comments   Abstract from Joes 2 Pros Volume 1

Use Comments to Say “Why”

It is important to use comments wisely, by considering who, what, when, where, and why for describing the purpose of the code. The challenge is to write notes that balance being descriptive and brief, so anyone else that may read it days, weeks, months or even years from now can understand why it exists and what it is meant to achieve.

j2p2012 2 SQL SERVER   Beginning SQL 2012   Why we use Code Comments   Abstract from Joes 2 Pros Volume 1

Single-Line Comments for Trouble-Shooting

Another great use of commenting techniques is to troubleshoot or test smaller segments of code without needing to modify or delete surrounding blocks of code that are around the segment we want to run by itself.

We suspect there is a problem with the join to the Location table and believe that the code for the Employee table segment is correct. The ability to temporarily prevent a segment of the code block from being able to execute, while the remaining code runs is a valuable troubleshooting tool. We want to test our theory by only running the segment of code with the SELECT statement for the Employee table, without having to rewrite it as a separate query, or deleting any of the code in the INNER JOIN segment.

SELECT *
 FROM Employee AS em
 --INNER JOIN Location as lo
 --ON em.LocationID = lo.LocationID
  EmpID LastName FirstName HireDate LocationID ManagerID Status
1 1 Adams Alex 2001-01-01… 1 11 NULL
2 2 Brown Barry 2002-08-12… 1 11 NULL
3 3 Osako Lee 1999-09-01… 2 11 NULL
4 4 Kennson David 1996-03-16… 1 11 Has Tenure
5 5 Bender Eric 2007-05-17… 1 11 NULL
6 6 Kendall Lisa 2001-11-15… 4 4 NULL

 

 

 

 

 

 

 

12 rows

Multi-Line Comments

When we want to comment out many consecutive lines of code, we have two different commenting techniques to accomplish this goal. Use single-line comments for each and every line to be prevented from running like the last example. The second option is to use multi-line comments by placing a ‘/*’ (forward slash, asterisk with no spaces) sign at the start of the segment to be commented out and then close the comment with a ‘*/’ (asterisk, forward slash with no spaces).

SELECT *
 FROM Employee AS em
 /*INNER JOIN Location as lo
 ON em.LocationID = lo.LocationID*/

In-Line Comments

What if we wanted to disable a specific part of code located on the same line as other code that we wanted to remain unaffected? The goal might be to disable one word in a long line of words within the code. We can achieve this goal by using an in-line commenting technique. In-line comments are best accomplished by using the same delimiters we used for making multi-line comments. We simply mark the beginning and ending of the word, or words, that we need to comment out of the code when the query is run. In this example, we want to display a report without the GrantID field. If we try to accomplish this by placing the ‘–‘ (double hyphens) delimiter, in front of the GrantID field, it’s going to prevent all the fields in the SELECT statement from being seen by SQL Server.

SELECT --GrantID, GrantName, EmpID, Amount
 FROM [Grant]
 WHERE EmpID IS NULL
 -- OR Amount < 10000
 OR EmpID = 7
Messages
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘FROM’

0 rows

The best way to accomplish the task of preventing the GrantID field from being displayed in our result set is to use the ‘/*’ ‘*/’ delimiters. Using this technique, we can easily prevent the smallest elements of a code block from executing, without moving or affecting the code surrounding it. The following example demonstrates how this is achieved.

SELECT /*GrantID,*/ GrantName, EmpID, Amount
 FROM [Grant]
 WHERE EmpID IS NULL
 -- OR Amount < 10000
 OR EmpID = 7
  GrantName EmpID Amount
1 92 Purr_Scents %% team 7 4750.00
2 Robert@BigStarBank.com 7 18100.00
3 Norman’s Outreach NULL 21000.00
4 @Last-U-Can-Help 7 25000.00

 

 

 

4 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Get Free Books on While Learning SQL Server 2012 Error Handling

Fans of this blog are aware that I have recently released my new books SQL Server Functions and SQL Server 2012 Queries. The books are available in market in limited edition but you can avail them for free on Wednesday Nov 14, 2012. Not only they are free but you can additionally learn SQL Server 2012 Error Handling as well.

My book’s co-author Rick Morelan is presenting a webinar tomorrow on SQL Server 2012 Error Handling.

rickerrorwebinar SQL SERVER   Get Free Books on While Learning SQL Server 2012 Error Handling

Here is the brief abstract of the webinar:

People are often shocked when they see the demo in this talk where the first statement fails and all other statements still commit. For example, did you know that BEGIN TRANCOMMIT TRAN is not enough to make everything work together? These mistakes can still happen to you in SQL Server 2012 if you are not aware of the options. Rick Morelan, creator of Joes2Pros, will teach you how to predict the Error Action and control it with & without structured error handling.

Register for the webinar now to learn:

  • How to predict the Error Action and control it
  • Nuances between successful and failing SQL statements
  • Essential SQL Server 2012 configuration options
Register for the Webinar and be present during the webinar. My co-author will announce a winner (may be more than 1 winner) during the session. If you are present during the session – you are eligible to win the book.
The webinar is scheduled for 2 different times to accommodate various time zones. 1) 10am ET/7am PT 2) 1pm ET/11am PT. Each webinar will have their own winner. You can increase your chances by attending both the webinars. Do not miss this opportunity and register for the webinar right now.
The recordings of the webinar may not be available.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – 2 New Books – FREE Books and Book Signing at #SQLPASS 2012

As an author the most interesting task is to participate in Book Signing Events. If you are at SQLPASS – we are going to have a lot of book signing events. Here is the good news!

MY NEW BOOKS ARE OUT!

SQL 2012 Functions Limited Edition

SQL2012Functions SQLAuthority News   2 New Books   FREE Books and Book Signing at #SQLPASS 2012

Click to Expand

This book is a very special edition book. Our current plans is to run this book for the limited edition. You can avail this book from Amazon and it will soon come to India. Join following book signing events where you will get this book for free.

Wednesday, November 7, 2012
7pm-8pm – Embarcadero Booth Book Signing (FREE BOOK)

Thursday, November 8, 2012
12pm-1pm – Embarcadero Booth Book Signing (FREE BOOK)

SQL Queries 2012 Joes 2 Pros Volume1

SQLQueries2012Vol1 SQLAuthority News   2 New Books   FREE Books and Book Signing at #SQLPASS 2012

Click to Expand

This is my first book this year which will be available in bookstores. Last Year I published 3 books and this year this is my first book. This book is available on Amazon over here and it will come to India very soon. Join following book signing events where you will get this book for free.

Wednesday, November 7, 2012
12pm-1pm – Book Signing at Exhibit Hall Joes Pros booth#117 (FREE BOOK)

If you are attending SQLPASS you may get this book’s Autographed Special Edition for FREE if you attend following book signing events.

Rest all the time – I will be at Exhibition Hall Joes 2 Pros Booth #117. Stop by for the goodies!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Last Two Days to Get FREE Book – Joes 2 Pros Certification 70-433

Earlier this week we announced that we will be giving away FREE SQL Wait Stats book to everybody who will get SQL Server Joes 2 Pros Combo Kit. We had a fantastic response to the contest. We got an overwhelming response to the offer. We knew there would be a great response but we want to honestly say thank you to all of you for making it happen. Rick and I want to make sure that we express our special thanks to all of you who are reading our books.

getfree SQL SERVER   Last Two Days to Get FREE Book   Joes 2 Pros Certification 70 433

The offer is still on and there are two more days to avail this offer. We want to make sure that everybody who buys our most selling combo kits, we will send our other most popular SQL Wait Stats book. Please read all the details of the offer here. The books are great resources for anyone who wants to learn SQL Server from fundamentals and eventually go on the certification path of 70-433.

Exam 70-433 contains following important subject and the book covers the subject of fundamental. If you are taking the exam or not taking the exam – this book is for every SQL Developer to learn the subject from fundamentals.

  •  Create and alter tables.
  • Create and alter views.
  • Create and alter indexes.
  • Create and modify constraints.
  • Implement data types.
  • Implement partitioning solutions.
  • Create and alter stored procedures.
  • Create and alter user-defined functions (UDFs).
  • Create and alter DML triggers.
  • Create and alter DDL triggers.
  • Create and deploy CLR-based objects.
  • Implement error handling.
  • Manage transactions.
  • Query data by using SELECT statements.
  • Modify data by using INSERT, UPDATE, and DELETE statements.
  • Return data by using the OUTPUT clause.
  • Modify data by using MERGE statements.
  • Implement aggregate queries.
  • Combine datasets.
  • INTERSECT, EXCEPT
  • Implement subqueries.
  • Implement CTE (common table expression) queries.
  • Apply ranking functions.
  • Control execution plans.
  • Manage international considerations.
  • Integrate Database Mail.
  • Implement full-text search.
  • Implement scripts by using Windows PowerShell and SQL Server Management Objects (SMOs).
  • Implement Service Broker solutions.
  • Track data changes.
  • Data capture
  • Retrieve relational data as XML.
  • Transform XML data into relational data.
  • Manage XML data.
  • Capture execution plans.
  • Collect output from the Database Engine Tuning Advisor.
  • Collect information from system metadata.

Availability of Book USA – Amazon | India – Flipkart | Indiaplaza

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Free Print Book on SQL Server Joes 2 Pros Kit

Rick Morelan and I were discussing earlier this month that what we can give back to the community. We believe our books are very much successful and very well received by the community. The five books are a journey from novice to expert. The books have changed many lives and helped many get jobs as well pass the SQL Certifications. Rick is from Seattle, USA and I am from Bangalore, India. There are 12 hours difference between us. We try to do weekly meeting to catch up on various personal and SQL related topics. Here is one of our recent conversations.

 SQL SERVER   Free Print Book on SQL Server Joes 2 Pros Kit

Rick and Pinal

Pinal: Good Morning Rick!

Rick: Good Morning…err… Good Evening to you – Pinal!

Pinal: Hey Rick, did you read the recent email which I sent you – one of our reader is thanking us for writing Joes 2 Pros series. He wants to dedicate his success to us. Can you believe it?

Rick: Yeah, he is very kind but did you tell him that it is all because of his hard work on learning subject and we have very little contribution in his success.

Pinal: Absolutely, I told him the same – I said we just wrote the book but it is he who learned from it and proved himself in his job. It is all him! We were just igniters.

Rick: Good response.

Pinal: Hey Rick! Are we doing enough for the community? What can we do more?

Rick: Hmmm… Let us do something more.

Pinal: Remember once we discussed the idea of if anyone who buys our Joes 2 Pros Combo Kit in the next 2 weeks – we will send them SQL Wait Stats for free. What do you say?

Rick: I agree! Great Idea! Let us do it.

Free Giveaway

Well Rick and I liked the idea of doing more. We have decided to give away free SQL Server Wait Stats books to everybody who will purchase Joes 2 Pros Combo Kit between today (Oct 15, 2012) and Oct 26, 2012. This is not a contest or a lucky winner opportunity. Everybody who participates will qualify for it.

Combo Availability

USA – Amazon

India – Flipkart | Indiaplaza

Note1: USA kit contains FREE 5 DVDs. India Kit does not contain 5 DVDs due to legal issues.
Note2: Indian Kit is priced at special Indian Economic Price.

getfree SQL SERVER   Free Print Book on SQL Server Joes 2 Pros Kit

Qualify for Free Giveaway

  • You must have purchased our Joes 2 Pros Combo Kit of 5 books between Oct 15, 2012 and Oct 26, 2012.
  • Purchase before Oct 15, 2012 and after Oct 26, 2012 will not qualify for this giveaway.
  • Send your original receipt (email, order details) to following addresses: “books@SQLAuthority.com;info@Joes2Pros.com” with the subject line “Joes 2 Pros Kit Promotion Free Offer”. Do not change the subject line or your email may be missed. 
  • Clearly mention your shipping address with phone number and pin/zip code.
  • Send your receipt before Oct 30, 2012. We will not entertain any conversation after Oct 30, 2012 cut off date.
  • The Free books will be sent to USA and India address only.
  • Availability USA – Amazon | India – Flipkart | Indiaplaza

Do leave a comment if you have question or comment.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Memories at Anniversary of SQL Wait Stats Book

SQL Wait Stats

SQL Wait Stats

About a year ago, I experienced a very proud moment. I published my second book, SQL Server Wait Stats, also acting as its primary author. It has been a long journey since then. The book received a generally great response and it has been widely accepted in the Community ever since its release. It was actually a first-of-its-kind book written to concentrate on the subject of Wait Stats and Performance. The book was based from my month-long blog series about the same subject, SQL Server Wait Stats. Today’s the first anniversary of the book, and lots of things come to my mind. Let me share a few here.

Idea behind Blog Series

A very common question I receive is why I wrote a 30 day series on Wait Stats. There were two reasons behind this series:

1) I have been working with SQL Server for a long time now and have troubleshoot more than hundreds of SQL servers which are related to performance tuning. It was a great experience and it taught me a lot of new things. I always documented my experience. After a while I found that I was able to rely completely on my own notes when I had to troubleshoot any server. It is right then that I decided to document my experience for the Community.

2) While working with Wait Stats, there were a few things which I thought I knew well enough since they were working. However, there was always a fear in the back of mind about what happens if what I believed was incorrect and I was on the wrong path all the time. There was only one way to get my belief validated -put it out in front of all the Community with my understanding and request further help to improve my understanding. And it worked – it worked beautifully. I received plenty of conversations, emails and comments. I refined my content based on the various conversations with others and made my understanding more relevant and accurate.

I guess that these two are the major reasons for beginning my journey on writing Wait Stats blog series.

Idea behind Book

After writing a blog series there was a good amount of request from my readers saying that I should convert the series into an eBook or a hardcopy. They said that reading the blog posts is great, but it does not give a comprehensive understanding of the subject as much as a book does. The most common feedback from users who were beginning to study the subject was that they prefer to read all about Wait Stats in a structured method. After receiving the same feedbacks for more than 4 months, I decided to write a book based on the blog posts. When I envisioned the book, I wanted to make sure that this book would address the Wait Stats concepts from the fundamentals and fill the gaps on the blog posts I wrote earlier.

Rick Morelan and Joes 2 Pros Team

I must acknowledge my co-author Rick Morelan for his unconditional support in writing this book. I had already authored one book before I published this book. The experience to write the book was out of the world. Writing blog posts are much much easier than writing books. The efforts it takes to write a book is 100 times more even though the content is ready. I could have not done it myself if it wasn’t for the tremendous support of my co-author as well as my editor’s team. We spend days and days researching and discussing various concepts that were to be covered in the book. When we were in doubt, we reached out to experts to improve the content, and also did a practical reproduction of the scenarios to validate the concepts and claims. After 3 months of continuous hard work, we were able to get this book published into the Community.

September 1st – the lucky day

Well, we had to select any day to publish the books. When the book was completed in the last week of August, we felt very glad. Every time my books are published, I feel the same joy which I had when my daughter was born. The feeling of holding a new book on my hand feels (almost) the same as holding newborn baby. I was excited. For me, September 1st was (and has always been) the luckiest day of my life.

 SQLAuthority News   Memories at Anniversary of SQL Wait Stats Book

My daughter Shaivi was born on September 1st. Since then, every September first has been a special day for me, and has always taken me to the next step in life. I believe anything and everything I do on September 1st is turning out to be successful and blessed.

Rick and I had finished a book in the last week of August. We sent it to the publisher (printer) and asked him to publish the book as soon as possible. We did not decide on any date as we wanted the book to get out as fast as it can. Interestingly, the publisher/printer selected September 1st for publishing the book. Because it was published on 1st September, I got a feeling that this book would reach the next level.

Book Model – The Most Beautiful Girl

We were done with book. We had no budget left for marketing. Rick and I had a long conversation about how to spread the word for the book so it can reach as many people as it can. While we were talking about marketing Rick come up with the idea that we should hire the most beautiful girl around who acknowledges our book and genuinely cares for the book.

I am a father and the most beautiful girl for me is my daughter. This was not a difficult task for me. Rick had given me the task to find the most beautiful girl and I just could not think of anyone else than my own daughter. I still do not know what Rick thought about this idea but I had already made up my mind. You can see the detailed blog post here.

The Fun Experiments

Book Signing Event

Book Signing Event

We had lots of fun moments along this book. Actually, we have given away more books to people for free than we have sold them. We went through book signing events, contests, and just plain give away events when we found out that more people could benefit from this book. There was never an intention to make money and get rich. We just wanted to see more and more people learning about this new concept and learn from it. Today when I look back at the earnings in dollars, there’s nothing much really. However, the best reward we received has been the relentless satisfaction and love of community. The amount of emails, conversations we have received for this book is over thousands as of the moment.

We had fun writing this book; it was indeed a very satisfying journey. I have earned lots of friends while learning and exploring.

Availability

The book is one year old, but the content is still very relevant when it comes to performance tuning. It is available at various online book stores. If you have read the book, please let me know what you think of it.

Amazon | Kindle | Flipkart | Indiaplaza

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

 

 

 

SQL SERVER – Winners – Contest Win Joes 2 Pros Combo (USD 198)

combos SQL SERVER   Winners   Contest Win Joes 2 Pros Combo (USD 198)

Earlier this week we had contest ran over the blog where we are giving away USD 198 worth books of Joes 2 Pros.

We had over 500+ responses during the five days of the contest. After removing duplicate and incorrect responses we had a total of 416 valid responses combined total 5 days. We got maximum correct answer on day 2 and minimum correct answer on day 5. Well, enough of the statistics. Let us go over the winners’ names. The winners have been selected randomly by one of the book editors of Joes 2 Pros.

SQL Server Joes 2 Pros Learning Kit 5 Books

Day 1 Winner

USA: Philip Dacosta
India: Sandeep Mittal

Day 2 Winner

USA: Michael Evans
India: Satyanarayana Raju Pakalapati

Day 3 Winner

USA: Ratna Pulapaka
India: Sandip Pani

Day 4 Winner

USA: Ramlal Raghavan
India: Dattatrey Sindol

Day 5 Winner

USA: David Hall
India: Mohit Garg

I congratulate all the winners for their participation. All of you will receive emails from us. You will have to reply the email with your physical address. Once you receive an email please reply within 3 days so we can ship the 5 book kits to you immediately.

Bonus Winners

Additionally, I had announced that every day I will select a winner from the readers who have left comments with their favorite blog post. Here are the winners with their favorite blog post.

Day 1: Prasanna kumar.D [Favorite Post]

Day 2: Ganesh narim [Favorite Post]

Day 3: Sreelekha [Favorite Post]

Day 4: P.Anish Shenoy [Favorite Post]

Day 5: Rikhil [Favorite Post]

All the bonus winners will receive my print book SQL Wait Stats if your shipping address is in India or Pluralsight Subscription if you are outside India.

If you are not winner of the contest but still want to learn SQL Server you can get the book from here. Amazon | 1 | 2 | 3 | 4 | 5 | Flipkart | Indiaplaza

Reference: Pinal Dave (http://blog.sqlauthority.com)