Month: October 2009

Common Table Expressions (CTE)

Common Table Expressions have been available to use since SQL Server 2005 and provide a more elegant way in which to handle a temporary result set other than using a temporary table. The syntax is similar to what I show below WITH <expression name> ([column name1,column name 2,…) AS ( SELECT ….. ) One of the main advantages over temporary tables is the ability for you to populate the table with the same expression that you use to create it. If this were a temporary table then we would be stuck with first creating the table and then doing the INSERT statement on it. Using the AdventureWorks database this is a simple example of using a CTE where we want to pluck out all the loginIDs that begin with B… WITH BNameSelection (LoginID,ManagerID,EmployeeID) AS (SELECT LoginID, ManagerID, EmployeeID    FROM HumanResources.Employee    WHERE LoginID like ‘%\b%’)    select * from BNameSelection;   Now it does not just have to be a straight out query that populates the CTE…if we had stuck it in a stored procedure we might have passed a parameter like @NameBegins … CREATE PROCEDURE PickAName     — Add the parameters for the stored procedure here     @NameBegins char(1) AS BEGIN WITH BNameSelection (LoginID,ManagerID,EmployeeID) AS (SELECT LoginID, ManagerID, EmployeeID     FROM HumanResources.Employee     WHERE LoginID like ‘%\’ + @namebegins + ‘%’)     select * from BNameSelection; END...

Read More

SQL Server Saturday – Louisville

I had a great time presenting at the SQL Server Saturday event in Louisville over the weekend. Thanks to all of the people whom volunteered their time to put it on as I think that everyone there generally enjoyed themselves. Attendance was good and the crowd seemed to be fairly attentive. I did hear a couple of people saying that their brain hurt so maybe the session could have been a little longer to allow everyone’s brain to expand slowly:) For everyone that wanted to grab my slides and samples there are attached below… Cheers! Demos And Slides AJ Technorati Tags: event,SQL Saturday,extended...

Read More

SQL Server 2005 Transactional Replication: Tips & Tricks

One of the more intimating things in the database architecture for DBAs that I meet at events is replication. Especially, if the replication that you need to set up needs to have a little twist in it that is not handled necessarily by the GUI Wizards in SQL Server Management Studio. What most DBAs need to realize is that if they concentrate on learning the underpinnings of ‘how’ replication works within the SQL Server environment then manipulating that environment becomes fairly easy. Using that line of thought, I thought it would be nice if I compiled some tips and tricks dealing with transactional replication. Hopefully, these hints will shed some light on the underpinning of the system and let you see just how easy replication can be. Stored Procedures A lot of what transactional replication does in most cases is done through the use of stored procedures. Now normally the GUI would take care of going out and populating the stored procedures for you but in some instances you may need to do this manually or you may just want to have a quick script of the objects in order to modify some things. Luckily, the database has a very simple stored procedure in order to script out the stored procedure that will be used for the articles in a publication. exec sp_scriptpublicationcustomprocs [Publication Name] This stored procedure will...

Read More

SQL Saturday #23 – Louisville,KY

Just a quick hit to let everyone know again that I will be speaking at the SQL Saturday event in Louisville,KY on October 24th! If you haven’t signed up for the event then make sure you get onto the SQL Saturday site and do so today. It should be a great day to learn some interesting aspects of SQL Server.  So people in the adjoining region should be looking to come on by. If they have enough space and rooms available then I may even be talked into doing a couple of impromptu sessions…like Jim Cramer’s Lightning Round! Maybe on some cool new features of 2008 or maybe even some handy ones like customizing transactional replication! Here is the current line up: Track Starts Session Title Speaker Track 2 04:15 PM Analysis Services 2008 End-to-End Craig Utley Track 2 11:15 AM Attunity CDC: Enabling the Real-Time SSIS Story Derek Comingore Track 1 11:15 AM Comparing Clustering Methods Alexander Prusakov Track 1 09:00 AM Demystifying Transact-SQL Jason Follas Track 2 03:15 PM Do It Right: Best Practices for Analysis Services Craig Utley Track 2 09:00 AM ETL from the Trenches: SSIS in the Real World Dave Fackler Track 1 03:15 PM Powershell in SQL Server 2008 Arie Jones Track 2 01:15 PM Project Gemini Derek Comingore Track 1 01:15 PM Scalable SQL Server Virtualization Sarah Barela Track 1 04:15 PM...

Read More

PASS Blogging Contest!

PASS is putting on its 2009 Logreader Awards which can be read about here. They are looking for the best blog articles written on the 15th and 16th of October. So now is your chance to email me and tell me what kind of article you would like to see. I figure that it will save me the time of coming up with my own idea and at the same time give the multitude of people whom read the blog their chance to put their two cents in as far as content. So feel free to shoot me an email and let me know what you would like to see an article on so that I can get cracking! Cheers, AJ Technorati Tags: sqls...

Read More
  • 1
  • 2