This was suppose to be my tech briefing topic to the PHSSUG core during its 2nd meeting (the UG’s name wasn’t PHSSUG at that time ), but unfortunately there were more pressing matters to tackle so PHSSUG decided to have this posted in the forums.
MS SQL Server has had tremendous impact on me and each time there is a new version since 6.0, the more i would say that this product is the best Microsoft ever had and product to beat.
So here is my Top 10!
At #10: New Data Types
- New LVTs (Large Value dataTypes)
In SQL 2000, we had text, ntext and image. Has anyone of you tried using these datatypes with your data going beyond 8K? I am sure you will have your own stories on how you were able to process things by chunk. MS will be deprecating the old BLOB handling datatypes (text, ntext and image) so you should migrate your old BLOB datatypes to these new LVTs. The new LVTs allows you to store up to 2GB of data. Under the Unified Large Object Programming Model, LVTs can also be used in a similar fashion when handling shorter strings. No more very unusual way of handling BLOBs.
(text, ntext, image)
varchar(nmax), nvarchar(nmax), varbinary(nmax)
At long last, we now have native XML as data type in SQL Server 2005. IMHO, XML support in SQL Server 2000 was anemic. But I havent played with XML in 2005 yet so I’ll remain skeptical. But the fact now is that we have XML on the same level as our CHAR, VARCHAR, INT, MONEY datatypes. I now can imagine passing XML as a parameter in one of our stored procedures as native XML. I can now imagine not having to worry about unstructured data. As i have read, there are a wide range of XML processors/functions built into the Transact-SQL language so you can manipulate XML .. so this is gonna be exciting. Here is a small snippet so you have an idea on what we can do with an XML datatype:
CREATE PROC ProcessMyXML @XMLParam XML
— you can do whatever you want with your XML
At #9: CLR Integration
The new dotnet framework will now be hosted in SQL Server 2005. With that, our SPs, Triggers, UDFs, UDTs can now be in Managed Code. We can also take advantage of the vast functionalities found in .NET Framework Library and ADO.NET. We also now have ways to handle arrays, collections, bit shifting, or classes.Also great on string handling and regular expressions.
In SQL Server 2005, we now have 3 ways to write “Hello World”: via T-SQL, VB.NET and C#.
At #8 –> SQL Server Express
This is got to be one of the best move ever by Microsoft. Giving SQL Server for free. I have posted this somewhere already but here are some info once again:
- SQL Server 2005 Express
- Supports 1 CPU, 1GB RAM and 64-bit (WOW)
- Goodbye to query governor (very very nice MS)
- Up to 4GB of database
- Express Manager (Easy-to-use Management Tool)
- Packaged with Limited Business Intelligence features
- Seamless migration to fuller, much powerful version of SQL Server
- Not a learning tool anymore but primed for production
- It is still FREEDownload it here (SQL Server 2005 Express)
Almost everything that very small businesses need and much smaller ones are in Express. There are a lot of application that can fit in a 4GB database. I’d say that all foxpro (other xbase systems) and access installations out there should now consider switching to SQL Server via Express.
Why go for LAMP when you can have Windows, IIS, Express and ASP.NET?
At #7 –> SQL Server Report Builder
With all the high end and developer/administrator oriented features shipped with 2005, it is so good that Microsoft has at least included something for the end users, the not-so-techie-users and analysts. SQL Server Report Builder is the new web-based report authoring tool designed for less technically sophisticated business users with zero development experience. This now promotes self-service reporting whcih is very cool. And this is GOOD NEWS to developers. How many of us would try to distance ourselves when given a programming task related to reporting?
I have yet to explore this stuff for our projects. So far, i have just built a couple of simple reports and yeah it was so easy to use it. Hopefully .. this would be at least according to what i read.
At #6 –> Error Handling (TRY … CATCH)
Ho ho ho! MS is really like Santa. At last, now we have a decent horror handler in version 2005. Opps I mean error handler. I cant explain for more but by trying to look at the sample below, you will have an idea as to how errors be handle in TSQL.
UPDATE NonexistentTable SET Col1 = 100
SELECT Error_Message() As ErrorMessage
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
At #5 –> DDL Triggers
Here is once again something great and new from the MS SQL Server team. I have always wished that everytime I update my tables, views, SPs, triggers (btw, I seldom use DML triggers), SQL Server logs the updates and maintain version information on it. Now my wish was granted in Yukon.
Here is for us ———-> DDL Triggers.
These are triggers that can be fired in response to Data Definitions Language statements such as CREATE, ALTER and DROP. These things can be ideal for regulating DDL operations. It can now be used to record audit logs for schema changes, events and used for version control (thanks MS!!!).
Here is some code that would demonstrate this new SQL Server capability:
CREATE TRIGGER TableProtect
ON DATABASE FOR DROP_TABLE, ALTER_TABLE
PRINT ‘Warning! Bawal i-Drop o i-Alter ang Tables’
CREATE TRIGGER RecordSPChanges
ON ALL SERVER FOR ALTER_PROCEDURE
DECLARE @DDLEvent XML
INSERT MyEventLog (DDLEvent) VALUES (@DDLEvent)
Now I can have my own change management and version control inside SQL Server. This is COOOOL!!!
At #4 –> Data Paging
Who among us today ever developed a web-based application without a data paging functionality? None i guess. With exotic TSQL workaround to return a page of data to mimic paging and temporary tables, we all were able to survive the data-paging epidemic.
Data paging on the client side is still a challenge to most of us especially if we code ASP.NET based apps a lot. Traditionally, there is really no native way to do paging in SQL Server 2000 and older versions. We have relied on using some workaround schemes to achieve paging. On ADO.NET, the entire result set are being sent from the server to the client.
At last, we got something new in SQL Server 2005. At #4, I am picking the new data paging capabiilties. Data paging can now be done on the server side rather than depend on client-side paging techniques as provided by ADO.NET and other technologies resulting to improved application performance.
We now have Row_Number() function as the center of SQL Server 2005’s data paging features. Let us take a brief look at some TSQL snippets:
ROW_NUMBER() OVER (ORDER BY PHSSUGID)
MyRowNumber, PHSSUGID, MemberAlias
WITH OrderedMembers AS
SELECT ROW_NUMBER() OVER (ORDER BY PHSSUGID) AS MyRowNumber, PHSSUGID, MemberAlias
MyRowNumber between 6 and 10
Looks like data paging will be a piece of cake from now on 🙂
At #3 –> Horizontal Data Partitioning
Weeks prior to the Nov 25 launching of SQL Server 2005 in the Philippines, I was fortunate to attend a Microsoft sponsored 1 week boot camp for Yukon. One of the things that caught my interest was the feature on Horizontal Data Partition (HDP). At that time, this was something new for me. I only heard of this feature in the Oracle camp for so many years. Prior to this, there was nothing similar or something nearer to the concept of HDP in SQL Server. There was of course a feature taking advantage of what the Filegroups can offer in terms of performance enhancement and the occasional UNION across servers, but none has anything like HDP in SQL Server (or perhaps I was not just aware of it).
So what is new with HDP? Before anything else, do take note that I am not writing based from experience with this subject as I have very limited exposure yet to this new feature. So far I haven’t played with this feature enough to become highly competent. Nevertheless, this is my #3.
HDP allows new ways to horizontally split large amount of rows for Very Large Databases (VLDB). Why would one want to do that? There are applications that only deal with predictable chunks of data, for example, applications that deal with data on a per year or monthly basis. With HDP, you can enhance performance by isolating seldomly used or needed rows from current ones. This feature enhances data access significantly in terms of speed. I have seen that Yukon has provided ways to manage data partitioning with the use of commands like SPLIT, MERGE and SWITCH.
HDP also helps in spreading load and processing across multiple Disks (much like Filegroups), CPUs and even Servers (I still have to check on this) and can be executed in parallel. I am assuming this will be a big hit for really large databases.
So far, my comments are mostly taken out of BOL and I have yet to explore how to fully utilize HDP.
At #2 –> Database Mirroring
Too bad, PHSSUG failed to have this showcased during the launching due to some glitch/setup problem that were not solved during the early hours of the launch. Though this feature is still not supported by Microsoft in RTM, I am quite excited about the prospects of DB mirroring.
Unlike in version 2000, we are left but iron out via replication and custom solutions our mirroring capabilities.
So what is with DB mirroring?
- Now we can increase database availability
- We can mirror databases across different instances of SQL Server on Stand-by
- We also now can code client applications to connect to a fail-over or standby mirror database
- We also get Transaction Safety (when Safety Set To FULL and mirroring is operated in synchronous mode)
Let us hope MS releases this officially on Q1-2006.
At #1 –> Painless Migration To 2005
Why start on something completely new when you can start using SQL Server 2005 on something you already know. It is plain and simple! Detach SQL Server 7 or 2000 database, Attach DB in SQL Server 2005. This is the most simple and painless way to get started on SQL Server 2005. This is also the best way to test if your old databases are working in SQL Server 2005. I wanted to test a database from SQL Server 6.5 but I can’t get hold of a copy of the version.
Looks like Microsoft took the extra effort to make things easier for users that are ready to jump to 2005.
I have already tried this apporach on a number of production databases created in SQL Server 2000 and a couple of old databases still in SQL Server 7. Except on the security (logins, roles and permissions), everything worked as if the apps are still running on SQL Server 2000 or 7. What I did was just to recreate all those logins and roles and permissions. Since i already have the scripts … it was peanuts.
This first month of 2006, i’ll be deploying SQL Server 2005 in a couple of clients that already had SQL Server 2000. I already had the done some testing with this process and tested their databases. So far, everything is going well.
However, I caution you to take my word with some degree of skepticism as I have not read anything so far that Microsoft recommended this approach. Dont forget to backup your databases when doing this.