DB4Breakfast

Last 2 posts we learned some cool ways to use the STUFF function. This post, we will combine the power of recursive common table expression ( CTE ), STUFF and PATINDEX function to write our own script to replace a string pattern. Some of you may ask why bother when SQL Sever already has the built-in REPLACE function? Well, this is just for fun and it doesn't hurt to practice writing sql. : ) /***********************************************************************  Replace a pattern with another string pattern.  This is almost the same as the built-in TSQL REPLACE function.  Ex: remove all commas within a string.  ************************************************************************/ -- DECLARE @original_str VARCHAR(100) = 'The, quick, brown, fox, jumps, over, the, lazy, dog' , @pattern_str VARCHAR(100) = ',' , @replacement_str VARCHAR(100) = ''; -- recursive CTE: replace @pattern_str with @replacement_str ; WITH phrase(string, row_i...

Last post, we explored some useful cases where STUFF function can be used. This post we'll take a step further by combining STUFF with XML PATH to combine rows to a single string and separate the values with commas. Let's remind ourselves the STUFF function syntax: STUFF ( original_string, start_position , length , replaceWith_string )  /**************************************************************/  /* Delimit a string of text. Insert a comma between strings.*/ /**************************************************************/  DECLARE @LogicalQueryProccessingOrder TABLE ( phase VARCHAR(10) ); INSERT INTO @LogicalQueryProccessingOrder  VALUES ('FROM'), ('WHERE'), ('GROUP BY'), ('HAVING'), ('SELECT'), ('ORDER BY'); /* this doesn't work because result has an extra comma at the end. Note this return in xml format */ SELECT phase + ', ' FROM @LogicalQueryProccessingOrder FOR XML PATH(...

STUFF ( original_string, start_position , length , replaceWith_string ) Insert a string into another. Start_position: position where the replace starts on the originalString Length: # of character to be replaced(deleted) on the originalString If start_position <= 0, then return NULL If length < 0, then returns NULL If start_position >  length of originalString, then returns NULL If length >= length of originalString, will be deleted to 1st character of originalString. /***************************************/ /* STUFF function rules with examples */ /***************************************/ DECLARE @original_str VARCHAR(50) = 'Hello World',/* length = 11 */ @replaceWith_str VARCHAR(50) = 'ABC'; SELECT -- If start_position <= 0, then return NULL STUFF(@original_str, -1, 1, @replaceWith_str) AS output1, -- If length < 0, then returns NULL STUFF(@original_str, 1, -1, @replaceWith_str) AS output2, -- If le...

String Concatenation with  plus (+) operator Example: the result of this query will return NULL because @mname is NULL.  Note: NULL mark represent missing data. Thus known data + missing data = unknown DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20); SELECT @lname + ', ' + @fname + ' ' + @mname AS fullName; Example: this query result an error because can't convert 'x, y' string to an TINYINT. DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT = 2; SELECT@lname + ', ' + @fname + ' ' + @a_number AS fullName_and_number; Result: Conversion failed when converting the varchar value 'y, x ' to data type tinyint. To avoid the error above we can convert number 2 to a string like this: DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT ...

SELECT Not good practice to use SELECT * because       All columns will returned (I'v seen tables with 100+ columns)      Reduce performance      Table structure can be changed later. Good practice to use schema name. Example: Sales = schema, Orders = table SELECT TOP(10) orderid, custid FROM Sales.Orders. Alias Once the assigned an alias to a table,  the original table name is NOT visible for the duration of the query. Example: this query will fail with error: The multi-part identifier "Orders.OrderId" could not be bound. SELECT Orders.OrderId, CustId FROM Sales.Orders AS O; Good practice to use keyword AS to name alias - help improve code readability  SELECT empid, lastname + ', ' + firstname AS FullName FROM HR.Employees; Example: this query only return the OrderId column with alias Custid because missing a comma between OrderId and CustId. SELECT OrderId CustId FROM Sales.Orders; ...

I think this is one the most import concept to master when learning SQL. Keyed-in order: the syntax order SELECT FROM WHERE GROUP BY HAVING ORDER BY Logical Query Processing (with easy to remember phrase, credit to my co-worker): the order logically query is processed FROM           -- For WHERE        -- Wives GROUP BY   -- Good HAVING        -- Husbands SELECT        -- Sing ORDER BY   -- Often Logical Query Processing detail FROM JOINS      WHERE     GROUP BY   HAVING       SELECT Column, and Expressions Window Functions Prepare Aliases for final output DISTINCT   ORDER BY   TOP/LIMIT Each phase output is served as input for the next phase For optimization, SQL Server may process the query in any order. Different between WHERE and HAVING clause: WHER...

SQL based on set theory & predicate logic Declarative language not procedural. Is 3-valued predicate logic: true, false, unknown Relational Model not equal Relationship based on 2 mathematics branch: set theory and predicate logic All attribute must has a name All attribute names must be unique No ordering for column or row T-SQL is a dialect extension of SQL base on ISO standard. Based on multiset theory (may include duplicates). Allows column ordering based on the order that column is create/defined Recommendation  to use standard syntax such as CAST (standard), CONVERT(T-SQL specific) Your code and knowledge more portable. A Relation: Is a like a Tables in SQL include a heading and a body Heading of a relation is a set of attributes Body of a relation is set or rows(tuples) Tuples:     like rows A Set: consider as a whole (interact with a set as a whole not row by row, RBAR) no order no duplicate A Predic...

I was browsing the Microsoft website to see what subjects are in 70-461 exam, and I stumbled upon this blog  yesterday. The blog author is very motivated and eager to learn even he failed the 70-461 exam.  As right now I have no intention taking this exam. I just want to blog about random topics that would be covered in the exam. I've been working with SQL Server and database for 3 years now.  The main reason I want to blog about this exam is I want to strengthen my knowledge and pick up new things along the way. Also, I promised my previous co-worker, she now lives in San Francisco, that I would starting writing more about database stuff.