Browsing Category

SQL Blogs

SQL and SQL Server database topics. Includes design, issue resolution and tips.

SQL Blogsby Rudy1 min1,132

Common Table Expressions (CTE) in SQL Server 2005 and Up

Thanks to the team at TechRepublic.com and BOL, I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher. Learn more about CTEs  at TechRepublic. Here meat of the article a sample: ;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking) AS (SELECT Product , SaleDate , SalePrice , Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) FROM SalesHistory ) DELETE FROM SalesCTE WHERE Ranking > 1 Very easy and handy! Check out BOL for all kinds of information regarding Dense_Rank(), NEW_ID(), and PARTITION BY. While you’re there, check out RANK(), NTILE(), and ROW_NUMBER() I’m sure … Continue reading Common Table Expressions (CTE) in SQL Server 2005 and Up

SQL Blogsby Rudy1 min1,338

Using NOCOUNT, ANSI Warnings and Temp Tables in SQL Server

Here are a few useful code fragments that I use all the time. You see too many Rows Affected Messages. First, NOCOUNT. If you don’t want SQL Server to return the Row Count (20 row(s) affected) after you execute a query, here is the code: — Turn off the rows affected message SET NOCOUNT ON Once you have executed your queries, you can enable the row count again SET NOCOUNT OFF ANSI Warnings interrupt your inserts Next, do you get annoying ANSI errors when inserting data? You can remove those warnings using one simple command. SET ANSI_WARNINGS OFF If there … Continue reading Using NOCOUNT, ANSI Warnings and Temp Tables in SQL Server

SQL Blogsby Rudy1 min2,446

Adding Foreign Keys after a table is created in SQL Server

Well, you knew exactly what you needed when you created a table. Only now when you created a child table, the child table does not update with the information from your main table. Or worse you deleted something from your primary table and it still lives in the child table. You need a foreign key, my friend. This is how you define one after you’ve created your table. ALTER TABLE My_Child_Table ADD CONSTRAINT FK_User_ID FOREIGN KEY ( User_ID_In_Child_Table ) REFERENCES My_Primary_Table ( User_ID_In_Primary_Table ) ON DELETE CASCADE ON UPDATE CASCADE The last two lines are important. In this example, if … Continue reading Adding Foreign Keys after a table is created in SQL Server

Programmingby Rudy1 min1,019

Adding Checks after a table is created in SQL Server

Did you create a table, only to find that you need to add a check constraint? I created a quick example of how to add a check constraint after the table exists. In this example, we want to know if our user is alive or dead. 1 = alive, 0 = dead. Here is the code for SQL server: alter table dbo.Tbl_User_Table add constraint CK_User_Alive check (User_Alive in (0,1)) I don’t know if it will work with MySql, Oracle, etc, so check your documentation. Happy Coding! RudyRudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his … Continue reading Adding Checks after a table is created in SQL Server