Steve Muise - SQL Server stuff

Public Presentations Book Review

2007-11-20T07:26:00Z

I started reading a book on public presentations. I thought it would be interesting to wrap up each chapter with my personal thoughts and highlights. The name of the book is "The Expectional Presenter"

Chapter 1:

The Exceptional Presenter is
   Organized
   Passionate
   Engaging
   Natural

As an Exceptional Presenter you must
   Understand your Audience
   Practice both the presentation and presenting in general

Finally every presentation counts, don't go into one half-baked, half-prepared. Never let the competition back in the game

Wrap up there are a couple of key elements in presenting; prepare, discuss things you are interested in, know the material so you can speak about it naturally, and be sure to know who you are talking to. Bad presentations just happen, good ones don't.

SQL Server Broker - Session 10

2006-05-26T18:38:00Z

Alright, I just got done trying to condense the Encyclopedia Brittanica doen to 30 slides and 60 minutes. I hope people got enough information to at least understand what the Service Broker does and why you might want to look at it. There aren't really too many got articles out there on the functionality of the Broker (except Roger Wolter's book on the Beta release). Building Enterprise Service Busses aren't for the weak of heart. They are large complex monsters that have a lot of hidden gotcha's. Here at Neudesic we are working on a cutting-edge bus that allows existing messaging systems to interact. But it is taking one of our very bright minds months to think it all through and create a bullet-proof solution. If you are interested in hearing more, let me know.

Anyway, I am sure I created more questions than answers during the webcast, so feel free to ask for additional clarifications. here is the sample code for download

http://www.neudesic.com/uploads/steve_muise/10_SqlBroker.zip

Thanks for joining me today, hope to see you back in a couple of weeks

XQuery - Session 9

2006-05-26T18:21:00Z

In SQL 2005, XML has gone native and with the now native XML type is the need to query effectively and efficiently. Thus the use of XQuery. XQuery allows a XML file to be queried in place and only the resulting values returned. XQuery is a super-set of XPath, and additional functions. It is a pretty large and complex set of functions and properties, but fairly easy to read and learn (since it is plain text, doesn't need a compiler and is fairly verbose)

Here is the link to the sample code I demo'ed during the webcast. I ran over again, so take a few minutes and look over the sample code.Most of it is pretty straight forward and makes goos sense when you see it.

http://www.neudesic.com/uploads/steve_muise/09_XQuery.zip

Let me kow if you have any further questions.

Steve

XML Data Type - updated for 2005

2006-04-28T18:18:00Z

Sorry about the rush today. I know I didn't manage my time well. No excuses (except maybe the cold medicine).

Anyways - Here is the sample code I went over, you will find a few additional samples I didn't have time to go over.

www.neudesic.com/uploads/steve_muise/08_XMLType.zip

Also here are a few links to some additional, well-written articles on the new XML type

http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/Default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp

http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/Default.aspx?pull=/library/en-us/dnsql90/html/sql2k5xml.asp

To store XML in Unicode in SQL Server use the UTF-16 Encoding.

I mentioned at the end, but it is worth repeating; in order to modify an existing Schema Collection that has been created in the database and bound to a column in a table you have to:

1. Remove the table binding (ALTER Table ...)
2. Remove the Schema (DROP Schema ...)
3. Recreate Schema with new Schema (CREATE Schema ...)
4. If necessary update XML in the table
5. Rebind Schema to table (ALTER Table ...) - be prepared to wait, it needs to re-validate all XML files in the table

For the Indexing capabilities, you can also do a FULLTEXT index over the XML column to optimize text searches

The above articles cover the XML Methods fairly well (that is the modify, query, value, etc) that I covered pretty quickly

The OPENROWSET is pretty straight forward, I know I glossed over - but take a look at the sample code and see if it doesn't just make sense. Email me if you have any questions

Again sorry about the poor time management, I will try and improve for next time.

Steve

Building a practice

2006-04-18T00:07:00Z

Here we are half way through the Webcast series ...

If any of you have ever built a Consulting practice before, you probably understand the amount of hard work that goes into wearing a number of hats.

1. Technical sales
2. Recruiter
3. Delivery
4. Public speaker

I love doing all of those things - it just gets tiring doing them all at the same time. Neudesic has built a tremendous reputation in the BizTalk, Sharepoint, Custom Application Development, and CRM practices in a number of large western US markets, and now the SQL/BI practice is growing in leaps and bounds. I have focused on building the SQL/BI practice by hiring top quality, high value consultants (it is the same model the other practice managers and company founders used). I just need more people.

Because of our exposure and my, and SQL team's experience we are often called upon to assist in 2005 implementations and migrations (heck I haven't had to work on a 2000 database in a couple of years). We also have a world-class OLTP expert (a former SQL Product team and MCS person) that has some of the largest clients in country as former customers, and the keep asking for him to come back and fix problems - that provides us an entry into some fantastic environments to work and learn.

Anybody interested?

I hope the followers of my MSDN webcasts have found the information useful. I have been trying to follow the good feedback I have received to improve the delivery. There is always a bit of gap between what a person knows and what they can communicate, so while I have used a lot of the features we have discussed, the ability to clearly describe them in a coherent fashion is a skill that needs to be developed. Thank you all for your feedback. Keep it coming. I hope the second half continues to improve.

Please let me know if you start implementing any of the features we have discussed, I would love to hear more cases where the new features apply and add value.

Steve Muise
Neudesic LLC
SQL/BI Practice Manager
Steve.Muise<RemoveThisToEmailMe>@Neudesic.com

T-SQL Enhancements (Section 7)

2006-04-14T20:42:00Z

Today's section was a blur - 42 slides and 7 demos in 55 minutes. I have included in my download my copy of the slides which hass some of my talking point notes (somethings I spoke off-the-cuff) for those you will need to catch the webcast again.

Anyway today we discussed:

The Try-CATCH exception handling, and the additional information methods to return error information

The new and VERY useful Common Table Expressions (Use these often)

The PIVOT and UNPIVOT - which when needed are invaluable

The modified EXCEPT and INTERSECTION - not necessarily new, but still useful (they were available in a different form previously)

The CROSS and OUTER Apply and the ability to JOIN with an UDF

The modified TOP feature

And the very useful OUTPUT feature to pipe back results as a table from UPDATE, DELETE, and INSERT statements

I hope you found it useful, I know I spoke a bit fast. If you have any additional questions fee free to email me. Here is the files for Download - http://www.neudesic.com/uploads/steve_muise/07_TSQL_DML.zip

Thanks again and see you in 2 weeks

Section 6 - New SQL Engine Features

2006-04-14T20:33:00Z

Today we talked about

the new SQL Data Types (varbinary, varchar(max)),

how to keep readers from blocking writers and writers from blocking reader using the new isolation and versioning features

Enhanced Triggers and Notifications

New new BULK INSERT capabilities using the newer drivers

Ways to enhance the Query Execution plan using hints and optimizations

Here is the sample code and slides for you offline perusal - http://www.neudesic.com/uploads/steve_muise/06_EngineFeatures.zip

Upcoming Schedule of webcasts

2006-03-17T19:12:00Z

There have been a few questions about the upcoming topics, and I know MSDN only show a few weeks out at a time. So here is the rest of the schedule. Each webcast is scheduled for every other week, so you can figure out the dates.

6. New SQL Engine Features -
SQL Server 2005 includes snapshot isolation, in which versioning rather than locking is used to ensure multiuser semantics. We examine the details and implementation. Common table expressions and using them with recursive queries are covered. The new DML syntax enhancements and extended triggers round out this module.

7. New Transact-SQL Features
SQL Server 2005 includes a number of Transact-SQL enhancements. Exception handling using TRY-CATCH syntax has been added. Common table expressions and using them with recursive queries are covered. The ranking and partitioning functions and other new DML syntax enhancements round out this module.

8. The XML Data Type
SQL Server 2005 supports native XML storage, schema validation, and indexing using a SQL-2003 compliant data type. We'll not only show how to use the datatype, but discuss when to use native XML as an adjunct to relational data. We also explore enhancements in XML composition and decomposition functions.

9. XQuery
This module begins with a brief overview of the XQuery language for the uninitiated. Then we explore the implementation of XQuery using the native XML data type in SQL Server 2005. Finally we see how to mix relational data and parameters with in-database XQuery.

10. SQL Server Service Broker
SQL Server 2005 introduces asynchronous queuing inside the database through the SQL Server Service Broker. We'll set up a simple Service Broker-based application using the new broker DDL and objects, then program the application using DML extensions. We'll also discuss how Service Broker solves some very difficult problems in producing stateful but scalable applications.

11. SQL Server And Web Services
SQL Server 2005 can be used to directly expose industry-standard web service functionality from inside the database. In this module we go over the details of this functionality and then define and build stored procedures that are exposed as web services. Finally we'll build a web service consumer using Visual Studio 2005.

12. SqlClient and the new Data Types
The new user-defined type and XML data type must be useable on the client to be useful for application programmers. This module shows how to use the new types in ADO.NET and System.Xml as well as classic ADO and MSXML. We discuss when to do user-defined type processing on database or client. Finally we explore using SqlTypes on the client.

13. SqlClient Provider - New Features
The client side ADO.NET data provider, SqlClient contains many new features to enhance ASP.NET and Windows Forms programming. Some are SQL Server 2005 specific, while some can be used with other versions of SQL Server.

14. ADONET2
ADO.NET 2.0 shifts the managed provider access model from an interface-based model to a base class-based model. We'll discuss the new model, which includes provider registration, provider factories, data source enumerators, connection string handling, and generalizatart.

15 - TBD - This is one I have left open and we are discussing what we should cover - Any suggestions?

Security - SQL 2005 - New features

2006-03-17T19:08:00Z

Today we discussed the updated Authorization Model, then enhanced Authentication model (using Certificates), how easy it is to encrypt and decrypt data at the field level. The new Database Schema model and a few examples of how to use it. I know a rna few mintutes over again, but I thought the topic was pretty important and even at a high-level still takes a bit to discuss fully.

here is the sample code, play with it a bit and come back with additional questions - there is abunch I didn't cover so as you start to apply this technology you will most likely run into a few stopping points - feel free to ask.

Steve

http://www.neudesic.com/uploads/Steve_Muise/05_Security.zip

User-Defined Types and Aggregates

2006-03-03T19:33:00Z

I know we tried to cover too much information today. I had to speak faster than I would have liked, and pass over code that may have been interesting or valuable to some in the audience. I apologize and hope to add some additional information here attempt to further explain.

First here is the Sample code you saw today. http://www.neudesic.com/uploads/Steve_Muise/04_UDT-Agg.zip
In the zip file is the T-SQL code, the C# and VB.NET code.

I will post the sample code that shows the Serialization for the Format.UserDefined, later on this afternoon.

Finally there was a question about Indexing on a UDT. I tested real quick and confirmed you can create an Index on a UDT field. I will do some more research and make sure there aren't any gotcha's we need to look out for.

See you in 2 weeks

Steve

Data Provider from within SQL Server's hosted CLR

2006-02-17T20:33:00Z

Today we discussed the advantages to using the Context Connection = "true" when connecting to SQL from a cataloged assembly.

Here is the demo code.

http://www.neudesic.com/uploads/Steve_Muise/03_SqlServer_Provider.zip

Sorry about the short post, but I have a few things I need to get up here, so check back soon.

Steve

Catalog Assembly -- Clarification --

2006-02-14T07:15:00Z

I had an attendee send me a question, where he heard me say that you could have the same assembly cataloged twice using 2 different names. And in fact, I have in the demo code an example of an attempt to catalog the same assembly under the name of "MetricConverter" and the name "foo" - The fact is you can not catalog the same assembly twice using 2 different SQL names. You can't register the assembly, copy it and/or rename it and register it under a different name (though I couldn't imagine why anybody would want to do this)

You can register 2 assemblies (that happen to be a version of one another) that have 2 different names as 2 different names in the database. So for example, I took the MetricConverter code, compiled and catgaloged. Then opened the code added another method, changed the assembly name and re-compiled, then went back and cataloged the versioned assembly with a new name. I now had 2 versions of the same assembly with 2 different names.

But this of course, begs the question "why ..."

- I could only imagine the pain and confusion that could be created by cataloging multiple versions of the same assembly.
I tend to think of assemblies as interface contracts, once they are published, they are golden until all dependencies are hunted down and destroyed (or simply modified). If in fact, I want to create a new version of a method, I would create a new signature (thus a new interface contract) and start migrating clients over. Or if I need to fix a bug in a method, then I want to know that all methods are calling the one version of code I have available with the interface contract being used - so when the bug is fixed all versions now are using the new version of code.

Can anybody come up with a good example of a reason to use multiple versions of the same assembly?

Today's MSDN Webcast - Stored Procs, Functions, and SQLTypes

2006-02-10T19:29:00Z

So today I was able to advance the .NET CLR conversation, with some additional examples, discuss the differences between a Procedure and a Function, the importance of using SQLTypes, requirements to using a function as an index-able column. Then I touched breifly on Table-Valued Functions (this we will discuss further, later on).

The question and answer period, was once again an excellent experience - great questions (if anyone is leaving early - don't). There were a few questions that I took away for further discussion.

1. Discuss in further details the Nullability of SQL data types
2. How, when and why to use the @@rowcount from within .NET methods
3. How to send an email using databasemail from within a .NET procedure
4. Is it possible to use a config file (e.g. MyAssembly.dll.Config) for the attributes
5. What kind of context is available in the 2 methods necessary for building a Table-Valued Function

I will begin to discuss each one of these here, I look forward to some interesting conversation around these topics.

Here is the link to the sample code from todays webcast (the VB.Net code is included)

http://www.neudesic.com/uploads/Steve_Muise/02_CLRProcs_Demo.zip

See you next week

Steve

SQLTypes compared to SQL Server Types

2006-02-10T03:24:00Z

Tomorrow I will be speaking on writing SQL CLR Stored Procedures. Among other things, one thing you always want to insure is that you limit type coersion as much as possible. This means that you should use the SQLType library in your .NET code and the appropriate SQL Server types in the T-SQL Function that calls the .NET method. To insure that you use the right types here is the MSDN reference page of the data types mapped to each other.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlTypes.asp

For other tips come listen to the webcast (or play the recorded version if you're busy tomorrow morning)

Introductions are in order

2006-02-04T05:50:00Z

I just wanted through a quick introduction out, so people know who I am. My name is Steve, I have worked for a number of companies large and small, including Microsoft. I was born and raised in So Cal, moved the Seattle to work at MS, then joined a consulting firm and haven't looked back. I now work for a mid-sized firm back in So Cal, I get to manage the SQL/BI Practice.

I am married, have 5 children, and don't get much sleep.

I have been working on/with SQL 2005 since before it was even a Beta. I was with a company called Aspirity, and worked with Reed Jacobson early on to develop the BI training. I also worked with Stacia Misner, Dave Duvarney, Tom Huguelet, Scot Regin, Dan Reh, and a host of other VERY smart people in the Business Intelligence space. I have been lucky enough to continue working on SQL 2005 for clients, and now I get to speak to others about it as well. Though, when I got cut off half-way through my SQL Launch event session, I was a bit bummed (anybody at the Anaheim SQL Launch - that went to the Managing and Administering SQL session?

Anyway, welcome. I know some topics will become religious in nature, but let's try and all keep an open mind, and "Do no harm"

-- Steve

Steve Muise - SQL Server stuff

Public Presentations Book Review

SQL Server Broker - Session 10

XQuery - Session 9

XML Data Type - updated for 2005

Building a practice

T-SQL Enhancements (Section 7)

Section 6 - New SQL Engine Features

Upcoming Schedule of webcasts

9. XQueryThis module begins with a brief overview of the XQuery language for the uninitiated. Then we explore the implementation of XQuery using the native XML data type in SQL Server 2005. Finally we see how to mix relational data and parameters with in-database XQuery.

13. SqlClient Provider - New FeaturesThe client side ADO.NET data provider, SqlClient contains many new features to enhance ASP.NET and Windows Forms programming. Some are SQL Server 2005 specific, while some can be used with other versions of SQL Server.

14. ADONET2ADO.NET 2.0 shifts the managed provider access model from an interface-based model to a base class-based model. We'll discuss the new model, which includes provider registration, provider factories, data source enumerators, connection string handling, and generalizatart.

Security - SQL 2005 - New features

User-Defined Types and Aggregates

Data Provider from within SQL Server's hosted CLR

Catalog Assembly -- Clarification --

Today's MSDN Webcast - Stored Procs, Functions, and SQLTypes

SQLTypes compared to SQL Server Types

Introductions are in order

9. XQuery
This module begins with a brief overview of the XQuery language for the uninitiated. Then we explore the implementation of XQuery using the native XML data type in SQL Server 2005. Finally we see how to mix relational data and parameters with in-database XQuery.

13. SqlClient Provider - New Features
The client side ADO.NET data provider, SqlClient contains many new features to enhance ASP.NET and Windows Forms programming. Some are SQL Server 2005 specific, while some can be used with other versions of SQL Server.

14. ADONET2
ADO.NET 2.0 shifts the managed provider access model from an interface-based model to a base class-based model. We'll discuss the new model, which includes provider registration, provider factories, data source enumerators, connection string handling, and generalizatart.