SQL Server 2005 useful info [includes links to download it]

Note that post #4 contains links for downloading SQL Server Express and various other items, and links to more useful information.



Saw this yesterday - thought it was a really neat T-SQL trick...

Code:

---

update sometable
 set somecol = 'ABC'
 OUTPUT inserted.keycol 'Key', deleted.somecol 'Old', inserted.somecol 'New'
 where keycol = 1

---

Will return the 3 columns indicated after the OUTPUT keyword as a SELECT statement after the UPDATE is performed...

Note that the columns come from those "trigger" like tables - the INSERTED and DELETED tables...

I get excited easily ;)

edit: it's only SQL 2005 that will support this

Here's what it looks like:

Code:

---

begin tran
select * from lettertyp_t where lettertyp='AP'
update lettertyp_t set lettertype='ABC'
  OUTPUT inserted.lettertyp 'Key',deleted.lettertype 'Old'
                  ,inserted.lettertype 'New'
  where lettertyp='AP'
rollback

---

comes back with...

Code:

---

LetterTyp LetterType                    TDate                                                 
--------- ------------------------------ ------------------------------------------------------
AP        Acknowledgement of Paternity  NULL

(1 row(s) affected)

Key      Old                            New                           
--------- ------------------------------ ------------------------------
AP        Acknowledgement of Paternity  ABC

(1 row(s) affected)

---

Apparently SQL 2005 has a TRY/CATCH construct.

SQL 2000

VB Code:

Use Funds
Go
Select * from Funds_T Where ConfItem='AMC'
Go
Insert into Funds_T Values (null,null)
Go
.
. returns in the Message pane
.
ConfItem                                           ConfData                                           
-------------------------------------------------- -------------------------------------------------- 
AMC                                                4.9.4
 
(1 row(s) affected)
 
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ConfItem', table 'Funds.dbo.Funds_T'; 
column does not allow nulls. INSERT fails.
The statement has been terminated.

Blows up because we don't allow NULL values in the columns of this table...

SQL 2005

VB Code:

Use Funds
Go
Select * from Funds_T Where ConfItem='AMC'
Go
Begin Try
    Insert into Funds_T Values (null,null)
End Try
Begin Catch
    Select 'Insert did not work!'
End Catch
Go
.
. returns
.
ConfItem                                           ConfData                                           
-------------------------------------------------- -------------------------------------------------- 
AMC                                                4.8.4
 
(1 row(s) affected)
 
 
(0 row(s) affected)
 
                     
-------------------- 
Insert did not work!
 
(1 row(s) affected)

The books online has a nice example of an error function - check this out

VB Code:

Use Funds
Go
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
GO
Select * from Funds_T Where ConfItem='AMC'
Go
Begin Try
    Insert into Funds_T Values (null,null)
End Try
Begin Catch
    exec usp_GetErrorInfo
End Catch
Go
.
. returns...
.
ConfItem                                           ConfData                                           
-------------------------------------------------- -------------------------------------------------- 
AMC                                                4.8.4
 
(1 row(s) affected)
 
 
(0 row(s) affected)
 
ErrorNumber 
ErrorSeverity 
ErrorState  
ErrorProcedure
ErrorLine   
ErrorMessage
515
16
2           
NULL
2
Cannot insert the value NULL into column 'ConfItem', table 'Funds.dbo.Funds_T';
 column does not allow nulls. INSERT fails.
 
(1 row(s) affected)

I had to cut up the results so it would post properly - but you get the idea!

Appears we are finally getting a real PIVOT construct in T-SQL with MS SQL 2005.

The example below shows counts of attendance for 4 buildings - the first query uses the old SUM/CASE syntax you are all probably used to.

The second query uses the new PIVOT syntax.

VB Code:

Use Stufiles
Go
Select Bldg,Sum(Case When AttType='AE' Then 1 Else 0 End) "Absent"
    ,Sum(Case When AttType='TE' Then 1 Else 0 End) "Tardy"
    From Attendance_T Where Yr=2006 and Bldg between 10 and 56 Group by Bldg Order by Bldg
Go
Exec sp_dbcmptlevel StuFiles,90
Go
 
Select Bldg,[AE] AS Absent, [TE] AS Tardy
From
(Select 1 AS AttValue,AttType,Bldg From Attendance_T Where Yr=2006 and Bldg between 10 and 56) a
PIVOT (Count(ATTValue) For AttType in ([AE],[TE])) as pvt
Order by Bldg

Both return the same results...

Code:

---

Bldg Absent      Tardy     
---- ----------- -----------
10  3235        1344
30  2397        1282
52  892        196
56  1463        316

(4 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Bldg Absent      Tardy     
---- ----------- -----------
10  3235        1344
30  2397        1282
52  892        196
56  1463        316

(4 row(s) affected)

---

Note that you must set compatibility level to 90 (MS SQL 2005 - version 9.0).

If this was one of my financial packages - this line:

Select 1 AS AttValue

Could just as easily been a column name containing dollar values.

Combine this with COLUMNS that are FORMULA's and you have some truly powerful query abilities!

Useful (free) downloads:

SQL Server 2005 Express Edition (SP 1) (a free version of SQL Server!)

SQL Server 2005 Books Online (the help files for all editions of 2005, in various languages)

Management Studio Express. (graphical management tool for the Express Edition).
An unofficial alternative is Toad

Native Client (OLE DB provider and ODBC driver, to make best use of 2005 features from your applications)

Other official downloads can be found here.

And some useful links I've found on my travels, or been told about by forum members:

SQL Server 2005 Features Comparison (compares the features in different Editions)

Video Series: SQL Server 2005 Express Edition for Beginners

Using SQL Server 2005 Express from Visual Basic 6

The Fundamentals of the SQL Server 2005 XML Datatype

Introducing SQL Service Broker Part 1 (messaging between applications and BizTalk). [by our own Sgt-Peppa]

Avoid using the ntext, text, and image data types (Use nvarchar(max), varchar(max), and varbinary(max) instead)

Cross Apply with Table Value Functions


.