Speaker: Kimberly Tripp
- SQL Server stared from a team of five (Sybase) to 400 (for 7.0) to over 1,000 (for 2005). Geez, do you think that they've put some money into this thing?
- Query Analyzer and SQL Enterprise Manager are now lumped in to SQL Management Studio
- Analysis Manager turns into BI Developer Studio
- Profiler can now be run outside of the admin role.
- You can manage SQL Sever 2000, SQL Server 2005 and SQL Express from the SQL Management Studio.
- Source Safe (anything that uses the Windows Source Control API) integration is included into SQL Management Studio. ABOUT FREAKIN TIME!
- SQL Management Studio looks a lot whole like Visual Studio.
- Using SQL CMD Lines in a SQL Script allows the ability connect to multiple servers.
- If you are writing function to be run on the database work with the DBA to expect the proper results.
- Good Scenarios for CLR Usage
-
- Data validation & network traffic reduction.
- Writing general purpose function: Data passes as arguments, little/no additional data access, Complex computation applied on a row by row basis to the data.
- Scalar types & custom aggregations
- Leveraging the power of the .Net frameworks
- Bad uses of the CLR
-
- Heavy data access - SQL set based access will be faster
- Complex types 8K limitation
- TSQL Enhancements
-
- ROW_NUMBER
- RANK
- PIVOT/UNPIVOT
- Common Table Expressions
- CROSS APPLY and OUTER APPLY
- TRY/CATCH
- DDL Triggers (synchronous)
- Event notifications (asynchronous)
- Parameterized TOP
- She demoed using .Net assemblies that are calling the Amazon Web services running in SQL Server. She called the assembly function from TSQL. Very powerful.
- You can create XML Indexes in SQL Server.
- XML Data is now shown as a hyperlink in SQL Management Studio. Click on the link and it shows the XML on a separate tab.
- SQL Server 2005 can listen to HTTP traffic using kernel mode HTTP.SYS. It does NOT depend on IIS. This is off by default.
-
- Clients can use SOAP to interact with SQL Server. This is great for platforms with no SQL middleware support.
- Trade-off of flexibility for performance. SQL clients will perform better.
- This can be created in TSQL.
- Troubleshooting Tools:
-
- Debugger support for TSLQ & CLR
- Dynamic Mgt Views
- Flight Recorder
- SQLClient/OLEDB tracing
- XML Showplan
- Complete cleanup of error messages
- SQL Profiler (with Perfmon integration) - You can open a perfmon trace and a profiler trace together. Both logs must be correlated by time. Also, the servers should have their clocks synchronized.
- SQL Server Express has most of the features of SQL Server 2005!
Disclaimer: These are my notes from my experience at TechEd 2005. I do not guarantee the validity or accuracy of these notes. They are my thoughts and what information I felt was important at the time.