When we define a a geography or a geometry most of think in two dimensions.

declare @LALocation geography 
set @LALocation = geography::STPointFromText('Point(-118.24 34.06 )',4326)
declare @LALocation2 Geometry
set @LALocation2 = geometry::STPointFromText('Point(-118.24 34.06 )',4326)

But Did you know that you can also store the Z and M Values may also be stored

Z  Coordinate

The Z Coordinate represents the height of or the elevation of the given point.The Heights are points above or below the surface measured using the vertical datum.

M Coordinate

The M coordinate stores the “measure” value of  a point . This coordinate can be used to represent any additional properties of a point that can expressed as a double-precision number. For instance Time or the position in a route.

So now using the code above we could simple add the z am coordinates as so

declare @LALocation geography 
set @LALocation = geography::STPointFromText('Point(-118.24 34.06  1 1)',4326)
declare @LALocation2 Geometry
set @LALocation2 = geometry::STPointFromText('Point(-118.24 34.06 1 1 )',4326)

The static methods provided by SQL server 2008  is based on WKT syntax , such as STPOINTFromText() support the creation and storage of z and m values as part of POINT. How ever these attributes are not included in the any calculations you do on the point.

Gotcha

example)

    Distance between point (0 0 0 0 ) and (3 4 12 1) is 5 (square root of the sum of the difference in the x and y dimensions) not 13 which account for z as well..

This enables you to not let any other Spatial Reference ID inserted into a spatial column. This is inherently allowed but when you want to do operations on the column they must all be of the same spatial reference.

alter table customers 
add constraint [enforce_SRID_GeogaphyColumn]
check (location.STSrid = 4326)

create TABLE customers
(
  FirstName nvarchar(30),
  LatName nvarchar(30)
)
alter table customers 
add location geography
alter table customers 
add constraint [enforce_SRID_GeogaphyColumn]
check (location.STSrid = 4326)
drop table customers

You just found out that sql server 2008 has spatial support and you want to add a spatail refrence to that the table? Here is how

create TABLE customers
(
  FirstName nvarchar(30),
  LatName nvarchar(30)
)
alter table customers 
add location geography

One of the new things that happens in SQL Server 2008 is that it prevents saving table structure changes that require the table to be dropped and re-created. While this is a great feature to prevent accidents from occurring, on a developer machine it can be quite frustrating. This is the dialog you get when trying to make changes in a table design.

Unfortunately, this dialog doesn’t tell you where to turn this feature off! Clicking on the small “?” on the title bar does get you to a help page that tells you how to do it.

Anyway, the place to do it is Tools > Options > Designers > Table and Database Designers > Prevent saving changes that require table re-creation. Turn this option off and you will be able to save the tables again.

Download SQL Script used throughout in this article to practice along. Now, take a quick look at the following two tables I have created.

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values. 

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The above example can also be created using Right Outer Join.

NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.

USE AdventureWorks
GO
CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,‘First’
UNION ALL
SELECT 2,‘Second’
UNION ALL
SELECT 3,‘Third’
UNION ALL
SELECT 4,‘Fourth’
UNION ALL
SELECT 5,‘Fifth’
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,‘First’
UNION ALL
SELECT 2,‘Second’
UNION ALL
SELECT 3,‘Third’
UNION ALL
SELECT 6,‘Sixth’
UNION ALL
SELECT 7,‘Seventh’
UNION ALL
SELECT 8,‘Eighth’
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
USE AdventureWorks
GO
/* INNER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO
DROP TABLE table1
DROP TABLE table2
GO

I hope this article fulfills its purpose. I would like to have feedback from my blog readers.  Please suggest me where do you all want me to take this article next.

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

Original Post here

Adding this here for my own refrence

Don’t ask why  I had to do this, but I think some other people might find this helpfull expsecially those who have hosted sql server.

Drop All Tables Views and Stored Procedures in a database SQL

   1: --delete our tables first

   2: declare @table_count int;

   3: declare @iter int;

   4: -- we know we're going to have tables the first time through

   5: set @table_count = 1 ;

   6: set @iter = 0;

   7: -- check to see how many tables we have in the database

   8: while @table_count > 0

   9: begin

  10: exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' ";

  11: SET @table_count = (select COUNT(*) from sysobjects where type = 'U'and name <> 'dtproperties');

  12: SET @iter = @iter + 1;

  13: end

  14: print 'iterations: ' + CAST(@iter as varchar);

  15:  

  16: -- variable to object name

  17: declare @name varchar(100)

  18: -- variable to hold object type

  19: declare @xtype char(1)

  20: -- variable to hold sql string

  21: declare @sqlstring nvarchar(1000)

  22:  

  23: declare SPViews_cursor cursor for

  24: SELECT sysobjects.name, sysobjects.xtype

  25: FROM sysobjects

  26: join sysusers on sysobjects.uid = sysusers.uid

  27: where OBJECTPROPERTY(sysobjects.id, N'IsProcedure') = 1

  28: or OBJECTPROPERTY(sysobjects.id, N'IsView') = 1 and sysusers.name =

  29: 'USERNAME'

  30:  

  31: open SPViews_cursor

  32:  

  33: fetch next from SPViews_cursor into @name, @xtype

  34:  

  35: while @@fetch_status = 0

  36: begin

  37: -- test object type if it is a stored procedure

  38: if @xtype = 'P'

  39: begin

  40: set @sqlstring = 'drop procedure ' + @name

  41: exec sp_executesql @sqlstring

  42: set @sqlstring = ' '

  43: end

  44: -- test object type if it is a view

  45: if @xtype = 'V'

  46: begin

  47: set @sqlstring = 'drop view ' + @name

  48: exec sp_executesql @sqlstring

  49: set @sqlstring = ' '

  50: end

  51:  

  52: -- get next record

  53: fetch next from SPViews_cursor into @name, @xtype

  54: end

  55:  

  56: close SPViews_cursor

  57: deallocate SPViews_cursor

Be Careful She’s a dozy

I just ran over some code I remembered grabbing from the web forever ago i thought i would share  the wealth. It works like a charm!

USE [EmailGenerator]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE  function [dbo].[InString](

      @string varchar(200), 

      @searchfor varchar(50), 

      @position int

      ) returns int

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Returns the position of the character AFTER the nth instance 

of the string

-- TEST CASE #1

-- should return 18

select dbo.InString('123456 123456 123456 123456', '23', 3) 

                    as [Test Case #1]

-- TEST CASE #2

-- should return 25

select dbo.InString('test1/test2/test3/test4/test5/', '/', 4) 

                     as [Test Case #2]

-- TEST CASE #3

declare @teststring varchar(50)

set @teststring = 'test1/test2/test3/test4/test5/'

select substring(@teststring, dbo.Instring(@teststring,'/',3),5)

                     as [Test Case #3]

-- should return 'test4'

-- TEST CASE #4 (variable length delimited fields

declare @teststring2 varchar(50)

set @teststring2 = 'test123/test/testtestestest/testxyz/test/'

select substring( @teststring2, 

                  dbo.Instring(@teststring2, '/', 3),

                  (dbo.Instring(@teststring2, '/', 4) -1) 

                           - dbo.Instring(@teststring2, '/', 3)

                  ) as [Test Case #4]

-- should return 'testxyz'

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

as

begin

declare @lenstring int, @poscount int, @stringpos int

set @lenstring = datalength(@searchfor)

set @poscount = 1

set @stringpos = 1

while @poscount <= @position and @stringpos <= len(@string) 

      begin

            -- if we find the string segment we're looking for 

        if substring(@string, @stringpos, @lenstring)=@searchfor

                  begin

                -- is the instance of the string the one we are

                --  looking for?

                        if @poscount = @position 

                              begin

                        set @stringpos = @stringpos + @lenstring

                                    return @stringpos

                              end

                -- else look for the next instance of the string

                -- segment

                        else 

                              begin

                                    set @poscount = @poscount + 1

                              end

                  end

            set @stringpos = @stringpos + 1

      end

return null

end

I just used this for the 1000th time so I thought i would blog about it. Just replace getdate() and with your date...

-- just the date

SELECT CONVERT(char(10), getdate(), 101) AS [Date]

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

-- just the time

SELECT (getdate() - 

    CAST(ROUND(CAST(getdate() AS float), 0, 1) AS datetime))

SELECT CONVERT (datetime, (1 - (CONVERT (float, getdate() ) - 

     CONVERT (int, CONVERT (float, getdate())))) * - 1 - 1)