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

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

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)

About these ads

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

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)

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)

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'

 

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'

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] 

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]

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.

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]

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.

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.

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.

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

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

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.

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)