SQL SERVER – Author’s Book is Available in India and USA

I am feeling very good to write this short blog post. My book is now officially available on in India and USA.

In India you can get it from Flipkart – http://bit.ly/pinalbook

In USA you can get it from Amazon – http://amzn.to/pb49jq

This book is just like this blog and contains all the complex subject in very simple manner. I am confident that you will for sure like this book if you like this blog.

Here is quick video shot by my wife when I was reading my own book. See the original post to see the video.

Here is quick secret for you – there is big surprise in September for all those who own this book.

joes2pros4 SQL SERVER   Authors Book is Available in India and USA

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Table-Valued Store Procedure Parameters – Day 25 of 35

joes2pros4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Table Valued Store Procedure Parameters   Day 25 of 35Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter5.1Setup.sql script from Volume 4.

Table-Valued Store Procedure Parameters

Stored procedures can easily take a single parameter and use a variable to populate it.  A stored procedure can readily handle two parameters in this same fashion.  However, passing 1000 variables into a stored procedure would be unwieldy and would require the calling code to run 1000 times. SQL Server 2008 now offers a way to simply pass a table into a parameterized stored procedure.  That’s right – you can pass a table’s worth of data into a single parameter and accomplish all the needed processing with just one call.

Table Types

We are already familiar with data types like int, varchar, and money.  We can also create our own user-defined types. With the new “table” data type available in SQL Server 2008, we can create a user-defined data type that is based upon a table.

Our first step in preparing our table-valued parameter demonstration is to create a “table” data type. We need to consider the fields to be included in the table which we want our stored procedure to accept, as well as the data types of these fields. Perhaps your table will look just like the Employee table.  Perhaps the table this stored procedure will use is like no other table on your system.  In the latter case, you don’t have to create a new persistent table:  you can define a table design without creating a table.

Using Table Types as Variables

After creating a new table type, our next step will be to declare a variable whose data type will be our new table type.  In our previous examples, once we declare a variable, we can set it equal to a value or pass in a value. In the case of a table type, the value of that variable will be a result set.

Parameters will allow you to pass in any data type found in the database, including user-defined types. When you can create and declare a user-defined table type and pass that into a stored procedure, this is known as a table-valued parameter.

The first table type we will define we will call GrantTableType and it will be based upon two fields of the Grant table (GrantName and Amount).  The code to accomplish this is shown in the Figure below.

After you run this code and create this new type, locate your newely created GrantTableType in Object Explorer. Traverse to JProCo > Programmability > Types > User-Defined Table Type > dbo.GrantTableType. The GrantTableType can be seen in your Object Explorer.

j2p 25 1 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Table Valued Store Procedure Parameters   Day 25 of 35

Now let’s declare a variable (@GrantTVP) whose data type is GrantTableType (i.e., our newly created table type). After we declare the variable, we will insert some data into it. Looking at the SELECT statement, we know this will bring in two fields and eleven records from the Grant table.

DECLARE @GrantTVP AS GrantTableType
INSERT INTO @GrantTVP
SELECT GrantName, Amount
FROM [Grant]

The confirmation message tells us that our @GrantTVP variable has been populated with 11 rows. We get an error message if we attempt to query from @GrantTVP unless we declare our variable, fill it with data, and select from it all at once as shown seen in the figure below.

j2p 25 2 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Table Valued Store Procedure Parameters   Day 25 of 35

Table Types as Parameters

So what’s the advantage of using a table type?  To answer that question, let’s first take a look at some familiar tables and their limitations. The MgmtTraining table, contains the approved list of classes for JProCo’s managers. The MgmtTrainingNew table contains the list of classes we intend to approve soon. Currently there are only two fields and two records in the MgmtTrainingNew table. The MgmtTrainingNew table has two fields.

j2p 25 3 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Table Valued Store Procedure Parameters   Day 25 of 35

j2p 25 4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Table Valued Store Procedure Parameters   Day 25 of 35

Table-Valued Parameters

Once a class from the MgmtTrainingNew table is approved, that class record must be placed in the MgmtTraining table. Now let’s think about how we would add these two records using a stored procedure. Would we run the stored procedure twice (i.e., once for each record)?  A better choice would be to pass the entire MgmtTrainingNew table into a stored procedure and have that stored procedure populate the MgmtTraining table.

We’re going to pass in a value to our parameter @TableName and then use that parameter in the logic of our stored procedure.  Let’s add a statement to create a  new data type (MgmtTrainingType). Notice that we must add the type to the code of our sproc. When passing in a table type, you must set it to READONLY.

CREATE TYPE MgmtTrainingType AS TABLE
(ClassName VARCHAR(50) NOT NULL,
ClassDurationHours INT NULL);
CREATE PROCEDURE AddNewTraining @TableName MgmtTrainingType READONLY
AS
INSERT INTO
dbo.MgmtTraining
(ClassName, ClassDurationHours,ApprovedDate)
SELECT mt.ClassName,mt.ClassDurationHours,GETDATE()
FROM @TableName AS mt
GO

We will declare a variable named @ClassTVP using the table type (MgmtTrainingType) we created earlier.  This table-type variable (@ClassTVP) is then populated with records from the MgmtTrainingNew table.  We then can call upon the stored procedure AddNewTraining and pass this variable into the table-valued parameter.

j2p 25 5 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Table Valued Store Procedure Parameters   Day 25 of 35

Let’s run a query on the MgmtTraining table and check to see whether the new class records appear. You can see in the Figure below was have success!  Both of the new records now show up in the MgmtTraining table.

j2p 25 6 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Table Valued Store Procedure Parameters   Day 25 of 35

Question 25

You need to create a stored procedure which accepts a table-valued parameter named @Suppliers. What code will achieve this result?

  1. CREATE PROCEDURE AddSuppliers
    @Suppliers Float READONLY
  2. CREATE PROCEDURE AddSuppliers
    @Suppliers Int READONLY
  3. CREATE PROCEDURE AddSuppliers
    @Suppliers Money READONLY
  4. CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY
  5. CREATE PROCEDURE AddSuppliers
    @Suppliers GeographyType READONLY

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options – Day 24 of 35

joes2pros4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Easy Introduction to CHECK Options   Day 24 of 35Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

Using Check Option

CHECK OPTION is a very handy tool we can use with our views. If I give you the definition right away and you don’t already know what it does then is just confusing. However the examples make perfect sense. So let’s save the definition for the end of this post. First let’s look at the creation of the vHighValueGrants view.

CREATE VIEW vHighValueGrants
AS
SELECT
GrantName, EmpID, Amount
FROM [Grant]
WHERE Amount > 20000

JProCo already offered to do a $1,000 match for each grant larger than $20,000.  The $1,000 match is currently included in our vHighValueGrants amounts as seen in the figure below.

j2p 24 1 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Easy Introduction to CHECK Options   Day 24 of 35

We have just been informed that the campaign didn’t receive the necessary approval from the stakeholders. Therefore, we must remove all of the $1,000 matching amounts. After we run this UPDATE statement, the grants will all be returned to their baseline values.

UPDATE vHighValueGrants
SET Amount = Amount - 1000

The large grants are decremented by $1,000 and thus returned to their baseline values. The $1000 increase has been removed as seen in this figure below.

j2p 24 2 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Easy Introduction to CHECK Options   Day 24 of 35

Suppose you accidentally ran the decrement step twice.  Before we “accidentally” run the UPDATE statement again in order to create this scenario, let’s consider the amounts currently shown by the view. The smallest grant in the vHighValueGrants view is $21,000.  If we rerun the UPDATE statement, this grant will become $20,000. Recall that each grant must be greater than $20,000 in order to appear in the view.

Run the UPDATE statement again and then run a SELECT statement to see all of the records in the vHighValueGrants view.  The $21,000 grant (contributed by Big 6’s Foundation %) was reduced to $20,000 and thus has fallen out of the view.  Now that this grant has fallen outside the criteria of vHighValueGrants, the view no longer has the ability to “see” or manipulate this record using DML statements.  For the five remaining grants, you can correct their amounts and reverse the “accidental” run of the UPDATE statement by incrementing each grant by $1000.  However, the only way to correct the amount of the missing grant is by running an UPDATE statement directly against the Grant table. There are only 5 large grants remaining in the view after the second $1000 decrement.

j2p 24 3 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Easy Introduction to CHECK Options   Day 24 of 35

This action was clearly a mistake.  We didn’t intend to remove a record from the view, but as it is currently configured, vHighValueGrants isn’t protected against these kinds of mistakes.  In order to prevent data updates which would cause records to disappear from our view, we can either place a trigger on the Grant table, or we can use CHECK OPTION.

CREATE TRIGGER trg_UpdateGrant
ON dbo.[Grant]
AFTER
UPDATE
AS
BEGIN
IF
EXISTS( SELECT * FROM Inserted ins
INNER JOIN Deleted del
ON ins.GrantName = del.GrantName
WHERE del.Amount > 20000
AND ins.Amount <= 20000)
ROLLBACK TRAN
END

If you create the trigger and then attempt to decrement the vHighValueGrants view, you’ll find that the trigger will not allow the transaction to fall to $20,000 and thus it won’t meet the criteria of the vHighValueGrants view.

j2p 24 4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Easy Introduction to CHECK Options   Day 24 of 35

The trigger has protected our view.  The transaction which attempted to reduce a large grant from $21,000 to $20,000 was forbidden and ended in the trigger.

But let’s recognize that the trigger would also prevent any existing grant from ever being changed to an amount $20,000, or lower.  In other words, the trigger is so restrictive that even a DBA would be disallowed from directly updating the Grant table if the change would reduce an existing grant amount to become $20,000 or lower. The trigger is more restrictive than we intended.

Our goal was simply to restrict users from making an accidental data change through the view which would result in a grant being removed from the view. Let’s reattempt our goal by using CHECK OPTION by rebuilding the vHighValueGrants view to include CHECK OPTION.

j2p 24 5 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Easy Introduction to CHECK Options   Day 24 of 35

This tells the view to disallow data changes through the view which would cause any record to fall outside of the criteria of the view. Does it work? Now attempt to decrement though the view and the CHECK OPTION will block you. Using the code below you get the following error message.

UPDATE vHighValueGrants
SET Amount = Amount – 1000

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

You will however be allowed to update to the table directly.

UPDATE [Grants]
SET Amount = Amount – 1000

OK as promised here is the short definition or description of what CHECK OPTION does for views. Each time a DML statement is run against the view, CHECK OPTION validates that the resulting record set will be true to the SELECT statement which built the view. If a modification would remove a record defined by the view, then CHECK OPTION prevents the transaction from being committed.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter5.1Setup.sql script from Volume 4.

Question 23

You have a table named dbo.Sales. You need to create three views from the sales table.

vSalesSeattle

vSalesBoston

vSalesSpokane

Each view will be used by each region to make changes to their rows.  One day a Seattle sales manager updated his sales data to have a new LocationID and the record showed up on the vSalesBoston view. Changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view.  Which view should you create for Region1?

  1. CREATE VIEW dbo.vSalesSeattle
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
    WITH DIFFERENTIAL
  2. CREATE VIEW dbo.vSalesSeattle
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
    WITH CHECK OPTION
  3. CREATE VIEW dbo.vSalesSeattle
    WITH SCHEMABINDING
    AS
    SELECT SalesID,OrderQty,SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
  4. CREATE VIEW dbo.vSalesSeattle
    WITH NOCHECK
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Views – Day 23 of 35

joes2pros4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Views   Day 23 of 35Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

View Options

Not every query may be turned into a view.  There are rules which must be followed before your queries may be turned into views.

View Rules

This query includes a simple aggregation which totals the grant amounts according to each EmpID.  It’s a handy report, but we can’t turn it into a view. The error message shown displays when you attempt to run this code and create the view.  Notice that it says “…no column name was specified for column 2.”

j2p 23 1 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Views   Day 23 of 35

We must first make certain this expression field column has a name before we can create this view. Alias the expression field as “TotalAmount” and then run this CREATE VIEW statement for vEmpGrantTotals.

CREATE VIEW dbo.vEmpGrantTotals
AS
SELECT
EmpID, SUM(Amount) AS TotalAmount
FROM [Grant]
GROUP BY EmpID

Encrypting Views

Suppose you want to make sure that people can utilize this view to run reports, but you don’t want them to be capable of seeing or recreating the underlying code.  The sp_HelpText system stored procedure reveals the code which created an object.

j2p 23 2 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Views   Day 23 of 35

We want to alter this view so that the source code is encrypted.  Two modifications to the code for vEmpGrantTotals will make this change:

1)        Change CREATE VIEW to ALTER VIEW.

2)        Add WITH ENCRYPTION before the AS keyword.

ALTER VIEW dbo.vEmpGrantTotals
WITH ENCRYPTION
AS
SELECT
EmpID, SUM(Amount) AS TotalAmount
FROM [Grant]
GROUP BY EmpID

The best practice after we create or alter an object is to run a SELECT statement to confirm that it produces the expected result.  Look at Object Explorer and notice that a small padlock now appears on the icon for vEmpGrantTotals.

j2p 23 3 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Views   Day 23 of 35

Often times you can just right-click a view in Object Explorer and choose “Script View as” to see the code for the view.  Now when we attempt that maneuver for our encrypted view, SSMS gives us a message saying that the text is encrypted and we can’t script this view. Management Studio (SSMS) will not allow us to generate code for the encrypted view. The properties dialog for vEmpGrantTotals also tells us that the view is now encrypted.

j2p 23 4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Views   Day 23 of 35

j2p 23 5 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Views   Day 23 of 35

Attempt to run the sp_HelpText sproc and notice the message, “The text for object ‘dbo.vEmpGrantTotals’ is encrypted.”.

sp_helptext 'dbo.vEmpGrantTotals'

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

The object ‘dbo.vEmpGrantTotals’ does not exist in database ‘JProCo’ or is invalid for this operation.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter4.2Setup.sql script from Volume 4.

Question 23

What are the two ways to see the code that created a view? (Choose Two)

  1. WITH SCHEMABINDING
  2. WITH ENCRYPTION
  3. sp_helptext
  4. sp_depends
  5. sys.syscomments

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Constraints – Day 22 of 35

joes2pros4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Constraints   Day 22 of 35Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

Check Constraints

My old track coach would tell us to give 110% effort. However, had my math teacher heard this, he would have explained that a percentage value exceeding 100% in this context is not possible. For the coach it was a fun way that implies that you will give all you have, but then somehow you will give 10% more than “all.” Does that mean 110% is always an incorrect value? No, it depends on the situation. For example, this year’s sales could be 110% of last year’s sales. The range of acceptable values defined for a field depends upon the context.

Let’s consider negative numbers for a moment. If a medical form asked for my height in inches, and I wrote negative -67.5, you would think this was either a typo or a joke.  -67.5 would be easily accepted by a decimal or float data type, but in the context of height, a negative number simply isn’t acceptable. A person’s height will always be a positive number, never a negative number. For that reason, a “Height” field in a database should be restricted to only positive values. If you want to add your own logic on top of a data type, you can use what is known as a check constraint.

In the StateList the “District of Columbia” record should consist of 68 square miles of land. However, we see a negative LandMass value for that record and a few other records.

j2p 22 1 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Constraints   Day 22 of 35

Similar to the concept of height, there really is no such thing as a negative land mass (coastal erosion anomalies aside).  We know that the absolute value for each LandMass number in our table is correct, so we essentially just need to clean up any negative figures to positive numbers (e.g., -68 should be 68).  The UPDATE statement below multiplies the LandMass value by -1 where the existing LandMass value is < 0 (less than zero). The SELECT query in the below Figure shows that no negative LandMass values remain following the update.

j2p 22 2 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Constraints   Day 22 of 35

Creating Check Constraints

Our LandMass data has been scrubbed and now we want to create the check constraint to maintain this field’s data integrity during future inserts or updates. We want SQL Server to say “Yes” to legitimate LandMass values (even if 2 states are the same size) but “No” to any update or insert resulting in a negative LandMass values. The code below will check to see if the value is positive before accepting the transaction. The preferred naming convention for the check constraint is CK for check (constraint) plus the table name and the fieldname, with each part separated by an underscore: CK_StateList_LandMass.

ALTER TABLE StateList
ADD CONSTRAINT CK_StateList_LandMass
CHECK(LandMass >=0)

To prove that our check constraint will prevent DML statements which try to introduce negative LandMass numbers, we will run a quick test. Below you see an UPDATE statement which attempts to change some of the LandMass values into negative numbers. The CK_StateList_LandMass check constraint guards against updates which would result in invalid LandMass values.

j2p 22 3 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Constraints   Day 22 of 35

In the figure below we see the CK_StateList_LandMass check constraint that we just added. In Object Explorer, expand the “Tables” folder of the JProCo database and expand Constraints.  Note: You may need to right-click to refresh the Constraints folder and see newly created items.

j2p 22 4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Constraints   Day 22 of 35

Let’s consider other errors which could occur in our LandMass data. Our largest current LandMass is Alaska at just over 650,000 square miles. A typo adding one or two extra zeros would really be a problem (i.e., distorting Alaska’s LandMass to either 6 million or 65 million miles).  Let’s attempt a bad UPDATE to help us recognize that our check constraint isn’t currently protecting our table from these kinds of mistakes.

UPDATE StateList
SET LandMass = 65642500
WHERE StateID = 'AK'

Since the UPDATE succeeded, our StateList table now incorrectly indicates that Alaska’s LandMass exceeds 65 million square miles. While constraints can’t guard against all potential data integrity problems, we should constrain the upper limit of the LandMass field in this example to disallow such glaringly large, erroneous values. Before proceeding, let’s reset Alaska’s LandMass back to its proper value:

UPDATE StateList
SET LandMass = 656425
WHERE StateID = 'AK'

Changing Existing Check Constraints

We want to add another condition to CK_StateList_LandMass, our existing check constraint for the LandMass field. There isn’t a T-SQL statement which allows us to directly modify our existing check constraint.  But we can easily accomplish our purpose by dropping the current constraint object and then rebuilding it with both conditions (values for LandMass must be non-negative and less than 2 million square miles).  Re-create CK_StateList_LandMass with expressions covering both of the conditions which we want the constraint to prevent (LandMass >= 0 and LandMass < 2000000) with the following formula.

ALTER TABLE StateList
DROP CONSTRAINT CK_StateList_LandMass
GO
ALTER TABLE StateList
ADD CONSTRAINT CK_StateList_LandMass
CHECK(LandMass >=0 AND LandMass < 2000000)
GO

To test whether our revised constraint works, let’s reattempt our earlier UPDATE statement. Our second requirement (LandMass < 2000000) is similarly enforced by the revised check constraint. Values in excess of 2 million (square miles) are disallowed for the LandMass field. You may also wish to perform additional testing to observe the check constraint allows valid transactions into the StateList table.

j2p 22 5 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Constraints   Day 22 of 35

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter1.0Setup.sql script from Volume 4.

Question 22

You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint. You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

  1. CHECK (Rating BETWEEN 1 and 100)
  2. CHECK (Rating <=90 AND Complaint = 1)
  3. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
    OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
  4. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
    AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

SQL SERVER – SQL Query Techniques For Microsoft SQL Server 2008 – Book Available for SQL Server Certification

We recently give away 7 physical books of Joes 2 Pros Book Volume 2. The response to following questions was overwhelming and was excellent. The book is available to purchase now in India and USA. This is great news as I often get request that where one can learn SQL Server, how to prepare for SQL Server Certifications. This book with its innovative visual approach lets you have firm hands-on experience as a SQL Server 2008 Developer. It is highly interactive with sections that challenge the student to play “Bug Catcher” in code, and do other interesting quiz games. All objects in SQL support the need to get data in and out of databases, and writing queries is the key to capturing the data you want. “SQL Queries Joes 2 Pros” shows you a proven roadmap that has helped many beginning SQL students get their certifications and launch their careers. If you want to get ready for MCTS certification, this is the second book of the Joes 2 Pros series.

United States:

India:

Interesting Feature of Book1:

  • Chapter on writing Efficient Queries
  • Videos for each chapter
  • Query Strategies and Maintaining Tables
  • Chapter on DML, DDL, TCL and DCL
  • 100+ Quiz Questions
  • 50 Hands-on skill labs
  • “Bug Catcher” Code Game

Less than 30 seconds Video

View Video on YouTube.

Checkout following questions which are based on Volume 2.

Questions – Book 2

Q 8) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35

Q 9) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Overriding Identity Fields – Tricks and Tips of Identity Fields – Day 9 of 35

Q 10) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35

Q 11) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Advanced Aggregates with the Over Clause – Day 11 of 35

Q 12) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35

Q 13) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – Advanced NTILE in Detail – Day 13 of 35

Q 14) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Output Clause in Simple Examples – Day 14 of 35

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Statistics – Day 21 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from United States will get Joes 2 Pros Volume 3.

Real Life Statistics

We are not surprised to see warm ski jackets appearing on display shelves starting in September. It’s not yet cold, but we know that winter time is a few months away based on our own recollection of the weather, which we’ve observed in previous seasons and prior years.  Our own memory of temperature and weather patterns is a knowledge store we informally draw upon when planning for steps we will take before the cold weather arrives (e.g., pull your boots, mittens, scarves, and heavy jacket out of storage after Halloween; winterize your vehicle prior to November by flushing the car radiator and checking the condition of your snow tires; if it snows before U.S. Thanksgiving (fourth Thursday of November), then you know it likely will be a harsh winter; etc.).  By sampling existing data, we can make reasonable decisions about things which have not yet happened.

This is precisely what SQL Server does when it comes to statistics. Similar to how we might step outdoors to sample the temperature, SQL Server observes your data to understand how selective certain values are within a field. With these statistics collected, SQL Server’s query optimizer can make good seek and scan decisions on fields with covering indexes.

SQL Statistics

SQL Server looks at the data in its tables long before you run your first SELECT statement. Because it’s already done this pre checking, SQL Server knows how to best run a query when the time comes. Sampling of this data is stored in statistics, so the query optimizer can make the right decisions.

Sampling to save time is something we do in our daily lives. How long do you think it will take you to drive to work in the morning? You already have a good estimate, because you have done this many times. We frequently need to predict how long it will take to do some combination of errands we have never done before. Suppose, for example, you have your first appointment with a new dentist in the morning and need to pick up your dog from the poodle parlor afterwards. Based on your sampling of the general area, your knowledge of traffic patterns in the area, the weather, and the time of day, you estimate how long it will take and the best way to go. SQL Server takes data samples, as a fraction of the real data in a table, so the query optimizer can decide the best way to run a query. These small samplings of data from a table are known as Statistics.

Shown here are the first 12 records of the SalesInvoiceDetail table. By visually inspecting this figure, we can sample our way to a few conclusions. The values in the InvoiceDetailID field isunique, which means those values in that field are highly selective. The InvoiceID field looks fairly selective with just 5 distinct values. The values in Quantity repead many times appear less selective. With this very small amount of data, it’s hard to tell for certain. We probably should sample more data to know which fields are selective enough to benefit from an index seek. Statistics are used by the Query Optimizer to know how selective certain query results will be based on their criteria.

j2p 21 1 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Statistics   Day 21 of 35

If there were millions of records in the table and you had just five records with an InvoiceID of 1, that’s highly selective. If that query has a covering index on that type of selective field then it will benefit by doing a seek operation. But in order to know exactly how selective the values are within each field, SQL Server would first have to scan the entire table of millions of records. Scanning your table then generating statistics would be slower than just scanning the table when the query is run. Because of the statistics which SQL Server has gathered, the query optimizer knows how selective each value in each field is without doing an entire scan of the table at query time.

Sometimes, statistics are gathered by scanning the entire table and sometimes, statistics are gathered by scanning a sample of a table. Right now, the JProCo database reflects a retail operation with small scale customers whose invoices contain just a few products, at most. If we later shifted to a wholesale operation, where we had invoices with thousands of products on each bulk order, then you could have the same InvoiceID value repeated for each line item of a single order. Today our table shows that InvoiceID is a selective field; however it could slowly become less and less selective as time goes on and we start doing bulk sales. Statistics need to constantly be updated to know the most recent selectivity information for an index. If a field does not have an index, then there is no urgent need to retain statistics: any query which predicates on the field will always use a scan instead of a seek.

The bigger the table, the more statistics there will be for each index on the table. To easily see how the statistics are created for a table, let’s choose a small table in JProCo. The HumanResources.RoomChart table has just eight records. Looking closer at these records, we see an ID which is very selective – in fact, this ID is unique. We have a field called [Code] which is also unique. The field RoomName is unique. None of these first three fields contains a duplicate value: each field has 8 different values. The NULL value is duplicated several times in the RoomNotes field and RoomDescription fields.

j2p 21 2 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Statistics   Day 21 of 35

Statistics Metadata

SQL Server has a handy system stored procedure (“sproc”) for showing the available statistics for a table. Let’s run sp_Helpstats to see some statistics for the HumanResources.RoomChart table.

j2p 21 3 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Statistics   Day 21 of 35

Notice this statistic (_WA_Sys_00000001_59063A47) is on the first field of the HumanResources.RoomChart table. As well, it is the only statistic currently being tracked for this table – SQL Server isn’t tracking the other fields with statistics. None of the fields have an index on this table.

Histogram

To look at the statistics details of the HumanResources.RoomChart table, use the DBCC SHOW_STATISTICS. You will need to supply the table name and the name of the statistic. Pass in each value as a parameter. Keep in mind our table has ID values 1 through 8, all of which are unique.

j2p 21 5 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   All about SQL Statistics   Day 21 of 35

In the second result set we see the ID column has four bytes, because the ID field of the HumanResources.RoomChart is an integer. The bottom result set is known as a histogram which shows you the spread of values for a field, and how many values are repeated for that field. For example, ID 1 appears once. ID 3 is listed once, as is ID 5. Notice it didn’t list all of the IDs; we see just a sampling.

In this histogram you see records with a small number of rows for each value. In fact there is only 1 EQ_ROWS count for each value, which signifies that this data is selective. However, if you see very few values with a high EQ_ROWS count, this indicates low selectivity. Note for each ID, the count of EQ_ROWS in this histogram is 1.

Updating Statistics

As new data gets added to a table the histogram record counts can get out of date. SQL Server already updates statistics as necessary to keep the histogram up to date with the data in the table that it represents. The auto-update statistics option is less than perfect, so you have the option of updating it at the time of your choosing. One way to get better statistics is to manually update the statistics for the table using the UPDATE STATISTICS command. The example below would update all the statistics for the Customer table:

UPDATE STATISTICS Customer

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLArchChapter12.0Setup.sql script from Volume 3.

Question 21

What advantage do SQL statistics offer the query optimizer?

  1. They allow the table to save space.
  2. The query optimizer knows the selectivity level of values before a query is run.
  3. They show performance statistics history since the last SQL restart.
  4. They prevent page splits by buffer data page memory for later.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Page Split – Day 20 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from United States will get Joes 2 Pros Volume 3.

From yesterdays post we learned that the clustered index is the placement order of a table’s records in memory pages. When you insert new records, then each record will be inserted into the memory page in the order it belongs.

Rick Morelan’s SSN (555-55-5555) belongs with the 5’s, so his record will be physically inserted in memory between Jonny Dirt and Sally Smith. Is there enough room in this page to accommodate his record without having to move other record(s) to a new page? Yes there is, and afterwards the first memory page is full. If you insert a new record(s), it is inserted into the memory page in the order it belongs.

J2P 20 1 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

Next, we have another new record coming in, Vince Verhoff. His record belongs in sequence after Irene Intern, so he will begin occupying the next page of memory.

J2P 20 2 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

The clustered index is the placement order of a table’s records in memory pages. When you insert new records, then each record will be inserted into the memory page in the order it belongs.

Page Splits

Page splits arise when records from one memory page are moved to another page during changes to your table. Here we see another new record (Major Disarray) being inserted, in sequence, between Jonny and Rick. Since there’s no room in this memory page, some records will need to shift around. The page split occurs when Irene’s record moves to the second page.

Page splits are considered very bad for performance, and there are a number of techniques to reduce, or even eliminate, the risk of page splits.

J2P 20 3 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

Next we’ll see this data entered into the HumanResources.Contractor table, which contains a clustered index on the SSN field.

J2P 20 4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

Notice from the figure above that SSN is set as a primary key. A primary key is a constraint which ensures non-nullability and uniqueness (i.e., no duplicate values) in a field. Only one primary key per table is allowed. When you create a primary key, SQL Server creates two objects:  the primary key and an index (which by default is clustered).  The data in this table will be physically ordered by SSN.  Now insert the first three records into the table.

J2P 20 5 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

Let’s use our same assumption that four records fit into a page of memory. These three records would all occupy the same memory page.

J2P 20 6 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

So that means there is room in the same memory page for an INSERT to add one additional record to this table, for a total of four records.

J2P 20 7 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

In our example, the first memory page is now full, since it contains four records. The second memory page is empty. The layout in the memory page is depicted with the fourth record (highlighted) added between Jonny Dirt and Sally Smith. The two bottom records (Sally Smith and Irene Intern) shifted down to make room for the “Rick Morelan” record to be inserted in proper sequence, according to the value of the clustered field (SSN).

The next insert (for Vince Verhoff – see figure below) goes straight into the second memory page and does not cause a page split. Now look at the SSN values in the table. Now the SSN value of the next record to be added will tell us whether there will be a page split. Any subsequent INSERT with an SSN value within the range 888-88-8889 through 999-99-9998 would go into one of the three available rows in the second memory page.

J2P 20 8 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

Any SSN value below 888-88-8888 will cause a page split. The 444-44-4444 Major Disarray value must be inserted in Page 1 between Jonny and Rick, a page split is caused and Irene Intern’s record must move to Page 2.  Notice that it won’t take long before almost every insert causes a page split.

J2P 20 9 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Introduction to Page Split   Day 20 of 35

NOTE: Please note that this is Level 100 explanation and how the rows are ordered and B Trees are organized are out of the scope of this article.

NOTE1: This article is just a introduction and not a deep dive into the storage structure. Here is the MSDN page for complete reference.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLArchChapter4.2Setup.sql script from Volume 3.

Question 20

When do page splits happen?

  1. When records from one memory page are moved to another page during changes to your table.
  2. When records from one memory page are collapsed into fewer pages from excessive deletes.
  3. When you insert records in order by the clustered index and you table needs to claim more memory space.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding – Day 19 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from United States will get Joes 2 Pros Volume 3.

Since the physical storage of data impacts the speed and efficiency of our queries, in tomorrow’s post we will explore how clustered indexes can impact the physical location of data and the way SQL Server retrieves query data. For today we will need to know the basics of the Clustered Index.

The Clustered Index

What is clustering or a clustered index? Let’s take a real life example. The store names in a shopping mall are not lined up in alphabetical order. In other words, Zales could be right next to Benetton. These two stores might occupy Units #410 and #411. Thus, the stores in a mall are clustered by unit number. The clustered index represents the actual physical order of your data. A paperback book (like Joes 2 Pros) is ordered by page numbers. If you locate page 99, then you know exactly where page 100 is. Thus, you could say the pages in a book represent a clustered index and they are clustered by page number.

Data Storage Terms

Think of a memory page as a carton of eggs and think of each egg as a row of data. If you only had two eggs to store, then you would just need one carton. With just two eggs you have 10 available slots for when you insert more eggs. Eventually, you could acquire more eggs than could fit into one carton. What would you do then? You would fill up your first carton and get another one. Once you get to 13 eggs you need 2 cartons to hold them. Similarly, only so many records will fit into one memory page. If more records come in, then SQL Server will use more memory pages for that table.

Records

While a table can have a maximum of just 1024 columns, or fields, it can have trillions of records. What makes a table take up space is the number of records in that table. If a table had no records, then it would take up very little space.

Memory Pages

A page can hold up to 8K of data. If each record takes up 2K of data, then 4 records would fit in each datapage. If such a table had 12 records, then it would use 3 memory pages. In this pseudo example, 12 records from this table would fill up three memory pages.

j2p 19 1 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   The Clustered Index   Simple Understanding   Day 19 of 35

Clustered Index Data in Memory

In Volume 2 (SQL Queries Joes 2 Pros), you learned that with the use of an ORDER BY clause you can view a table’s records in any order you like. This is true regardless of the order in which the table has its rows stored. If you don’t use an ORDER BY clause, then you get the table’s natural sort order. What is a natural sort order? It is the sequence, from beginning to end, in which each row is stored in the table. You can tell a table how it should store its data or let the table just store data in order as it is entered.

Absent a clustered index or constraints (e.g., a primary key, a foreign key, etc.), the default order of records is the order in which they were entered into the table. The clustered index represents the actual physical order of your data but it does not guarantee order when retrieved.

If we decide to add a clustered index to this table based on the SSN field, then regardless of the order in which you insert these records, each record will be stored in order of SSN. If you insert SSN 888-88-8888 first and then later add 222-22-2222, SQL Server would physically reorder the records in storage so they line up by SSN.

NOTE: Please note that this is Level 100 explanation and how the rows are ordered and B Trees are organized are out of the scope of this article.

NOTE1: This article is just a introduction and not a deep dive into the storage structure. Here is the MSDN page for complete reference.

Question 19

A heap has:

  1. No clustered indexes.
  2. One clustered index.
  3. Many clustered indexes.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from United States will get Joes 2 Pros Volume 3.

Geography Data Type

New to SQL Server 2008 are the spatial data types called Geography and Geometry. The Geography data type can store information for areas and points on the earth. It also provides a built-in function to calculate distance and overlaps with other locations. This data type stores and handles calculations based on round-earth (or ellipsoidal) data, which relates to coordinate systems such as GPS and longitude-latitude.

Databases have been storing positional data for years, like the sample data below where we see two dedicated fields to store longitude and latitude for each JProCo location. Please note that your if you ran the SQLArchChapter5.0Setup.sql script  then the Location table in the figure below does not currently have the latitude and longitude fields. This is our first goal we will do very soon after some more explanation.

J2P 18 1 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Geography Data Type   Calculating Distance Between Two Points on the Earth   Day 18 of 35

If you were to ask this table in a query what the longitude-latitude difference between Seattle and Boston is, it would have no idea. To the query, these are just arbitrary flat data values in a table. In order to turn each pair of numbers into a meaningful geographical point, we would have to extract this data into a custom application outside of SQL Server (e.g., into a C# application) and then run calculations using the customized app.

In other words, the true ellipsoidal nature of this geographical data wasn’t stored in the database prior to SQL Server 2008. The database couldn’t have differentiated our sample longitude-latitude data above from any other kind of information in the database. Now, thanks to SQL Server 2008, you no longer need a separate custom application, because both of these pieces of data can be stored in one Geography field. As well, the Geography type makes available all the built-in functionality, to perform calculations involving round-earth data, which would have been contained in a custom app.

Storing Latitude and Longitude

Prior to SQL Server 2008, two float or decimal fields would be used to house latitude and longitude. Now you can store these, as well as other geospatial data, in one Geography field.

Let’s begin by looking at all the records in our Location table, as well as its design. We see typical location data – city, state, street – and the five JProCo office locations (Seattle, Spokane, Chicago, Boston, and Philadelphia). Into this table we will add fields for latitude and longitude, and then we’ll see how to combine those into one Geography field..

J2P 18 2 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Geography Data Type   Calculating Distance Between Two Points on the Earth   Day 18 of 35

J2P 18 3 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Geography Data Type   Calculating Distance Between Two Points on the Earth   Day 18 of 35

Let’s start by adding latitude and longitude fields and populate these fields using the values based on the code below:

ALTER TABLE Location
ADD Latitude FLOAT NULL
GO
ALTER TABLE Location
ADD Longitude FLOAT NULL
GO
UPDATE Location
SET Latitude = 47.455, Longitude = -122.231
WHERE LocationID = 1;
UPDATE Location
SET Latitude = 42.372, Longitude = -71.0298
WHERE LocationID = 2;
UPDATE Location
SET Latitude = 41.953, Longitude = -87.643
WHERE LocationID = 3;
UPDATE Location
SET Latitude = 47.668, Longitude = -117.529
WHERE LocationID = 4;
UPDATE Location
SET Latitude = 39.888, Longitude = -75.251
WHERE LocationID = 5;

Creating Geography as a Field in a Table

We’re now going to add another field called GeoLoc (short for geographical location), which will use the new Geography data type. We have the latitude and longitude fields populated with values for each JProCo location. The Geography field GeoLoc has also been added to the table but is not yet populated.

J2P 18 4 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Geography Data Type   Calculating Distance Between Two Points on the Earth   Day 18 of 35

J2P 18 5 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Geography Data Type   Calculating Distance Between Two Points on the Earth   Day 18 of 35

Populating a Geography Data Type

Based on the two data points, latitude and longitude, we can generate the geospatial locations for the GeoLoc field. Use the Point-static function called GEOGRAPHY to pass in the latitude and longitude values along with a style specifier value (4326 is the standard which is used the most).

UPDATE Location
SET GetLoc = GEOGRAPHY::Point(Latitude , Longitude , 4326)

J2P 18 6 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Geography Data Type   Calculating Distance Between Two Points on the Earth   Day 18 of 35

The style specifier we used to format our Geography value is also known as a spatial reference identifier or SRID and identifies which spatial reference system the coordinates belong to. The SRID 4326 represents WGS 84, which is the most commonly used system and is used by many GPS systems.

Calculating Distance between two points on the earth

We have successfully combined latitude and longitude into the GeoLoc field. However, since the values in the GeoLoc column are a little cryptic to read by the human, we can create a variable and capture each city’s GeoLoc value into its respective variable.

J2P 18 7 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Geography Data Type   Calculating Distance Between Two Points on the Earth   Day 18 of 35

STAsText( ) is one of the Spatial Type methods you can use with the Geography type. The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points. To have STDistance( ) return the distance from Seattle to Boston in kilometers (KM), we have divided the returned value by 1000). Without this step, the result is just over 4 million meters (4,014,163 meters).  Calculating the distance from Seattle to Boston, which is just over 4,014 KM.

J2P 18 8 SQL SERVER   Tips from the SQL Joes 2 Pros Development Series   Geography Data Type   Calculating Distance Between Two Points on the Earth   Day 18 of 35

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLArchChapter5.0Setup.sql script from Joes 2 Pros Volume 3.

Question 18

The STDistance function of the Geography Data type calculates the distance between two points in …

  1. Feet
  2. Meters
  3. Kilometers
  4. Miles
  5. Units

Do not forget to participate in special question over here: Pluralsight Giving Away Free Subscription to Quiz Participants

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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