Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Server 2005 does what? 1
- Thread starter Luzian
- Start date
-
1
- #2
mrdenny
Programmer
- May 27, 2002
- 11,595
Well, to start with in my performance testing SQL 2005 process data about 30% faster than SQL 2000 when running on identicle hardware. My test results are posted around here somewhere.
Standard Edition gets you up to 4 Gigs of RAM while SQL 2000 Standard only gives you 2 Gigs. SSIS gives you a host of new features for your ETL needs.
SQL 2005 now supports schemas. The security is much more grannular and functional.
Non-sysadmins can run profiler if granted the correct rights.
Service broker allows you to send fire and forget messages from one SQL Server to another without needing another message queue system (MSMQ, etc).
Tighter integration with VSS and Visual Studio.
The ability to load up Visual Studio dll's into the SQL Server and use them as functions, data types, procedures, etc (if the server is configured to allow this).
The dedicated admin connection which will allow an admin to connect via SQLCMD (the new osql) and see what processes are killing the system if all the schedulers are hung (happens every once and a while).
SMTP based email is supported out of the box.
Database mirroring.
Standard Edition supports Clustering.
Enterprise Edition supports online index rebuilding.
Is that enough info? I'm sure I can come up with some more. Check out the Microsoft site, they've got a full feature breakdown.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
Standard Edition gets you up to 4 Gigs of RAM while SQL 2000 Standard only gives you 2 Gigs. SSIS gives you a host of new features for your ETL needs.
SQL 2005 now supports schemas. The security is much more grannular and functional.
Non-sysadmins can run profiler if granted the correct rights.
Service broker allows you to send fire and forget messages from one SQL Server to another without needing another message queue system (MSMQ, etc).
Tighter integration with VSS and Visual Studio.
The ability to load up Visual Studio dll's into the SQL Server and use them as functions, data types, procedures, etc (if the server is configured to allow this).
The dedicated admin connection which will allow an admin to connect via SQLCMD (the new osql) and see what processes are killing the system if all the schedulers are hung (happens every once and a while).
SMTP based email is supported out of the box.
Database mirroring.
Standard Edition supports Clustering.
Enterprise Edition supports online index rebuilding.
Is that enough info? I'm sure I can come up with some more. Check out the Microsoft site, they've got a full feature breakdown.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
- Thread starter
- #3
follow this link to see an example of creating User Defined Aggregate functions. A virtually impossible task in SQL 2000, but much simpler in 2005.
A useful example of using VS to add functionality to SQL 2005 - how many times have you hit your head against the fn_joinstring myth?
A useful example of using VS to add functionality to SQL 2005 - how many times have you hit your head against the fn_joinstring myth?
mrdenny
Programmer
- May 27, 2002
- 11,595
A schema allows you to group the objects together under a common ownership. There are a couple of default schemas that are created. They are sys, dbo and INFORMATION_SCHEMA.
Schemas are a new concept for SQL Server. Prior to this a user would own an object. In order to delete the user account you would have to delete the object or change the owner of the object. In SQL 2005 you can change the owner of the schema, without changing the schema name.
As an example in SQL 2000 you have users called Bill and Fred. Bill creates a table called Bill.Invoices. An application is created to use this table. Bill leaves the company and Fred takes over the application. All of Bill's tables must be changed from Bill to Fred as the owner and the application updated and redeployed before Bill's account can be dropped.
Now in SQL 2005 you have the same users called Bill and Fred. But now you have a schema called BillingApp. Bill owns the schema and creates a table called BillingApp.Invoices. When Bill leaves the company you simply change the owner of the schema from Bill to Fred, with no changing the schema or the application.
With SQL 2000 you could duplicate this setup but you could have to create a server login, and create a user called BillingApp.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
Schemas are a new concept for SQL Server. Prior to this a user would own an object. In order to delete the user account you would have to delete the object or change the owner of the object. In SQL 2005 you can change the owner of the schema, without changing the schema name.
As an example in SQL 2000 you have users called Bill and Fred. Bill creates a table called Bill.Invoices. An application is created to use this table. Bill leaves the company and Fred takes over the application. All of Bill's tables must be changed from Bill to Fred as the owner and the application updated and redeployed before Bill's account can be dropped.
Now in SQL 2005 you have the same users called Bill and Fred. But now you have a schema called BillingApp. Bill owns the schema and creates a table called BillingApp.Invoices. When Bill leaves the company you simply change the owner of the schema from Bill to Fred, with no changing the schema or the application.
With SQL 2000 you could duplicate this setup but you could have to create a server login, and create a user called BillingApp.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
mrdenny
Programmer
- May 27, 2002
- 11,595
When creating unmanaged code for SQL 2005 (.NET dlls that you attach to the SQL Server) you need to be very carefull. You can easily cause all sorts of performance problems which are ver hard to track down if the .NET code isn't optomized correctly.
The shop that I'm a DBA at we don't allow unmanaged code on our SQL Servers unless the developers show that it's something that can't be done with T/SQL.
Unmanaged code should be used for high end math, and hard core string manulipulation. These are things that SQL Server isn't very good at, but .NET is. Inserts, updates, deleted, selects, and basic logic should be kept in T/SQL.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
The shop that I'm a DBA at we don't allow unmanaged code on our SQL Servers unless the developers show that it's something that can't be done with T/SQL.
Unmanaged code should be used for high end math, and hard core string manulipulation. These are things that SQL Server isn't very good at, but .NET is. Inserts, updates, deleted, selects, and basic logic should be kept in T/SQL.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
- Thread starter
- #7
Thanx, mrdenny.
Your explaination of schemas makes more sense now after I've tried to create a database user, then tried creating a stored function: [inet].[fn_Md5Hash], but wasn't able to, because there was not such schema as [inet]. I barely understood security in 2000, but that's how I must have misunderstood the prefixes(like [dbo]) before an object.
Your explaination of schemas makes more sense now after I've tried to create a database user, then tried creating a stored function: [inet].[fn_Md5Hash], but wasn't able to, because there was not such schema as [inet]. I barely understood security in 2000, but that's how I must have misunderstood the prefixes(like [dbo]) before an object.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
