Category Archives: SQL Server
Nice Tutorial – 31 Days of SSIS
Database Architect Jason Strate composed a 31-Day series containing tips for SQL Server Integration Services. I recommend checking it out when you have time.
–
Happy Learning!
Houston Area SQL Server User Group – 03/08/11
I attended the March Houston Area SQL Server User Group meeting. Below are my notes.
Announcements
- SQL Saturday – Dallas is April 2/2011
- Tables and Views
- Derived tables
- Common Table Expressions
- Table Value Functions
- Join
- Apply
- Pivot & Unpivot
- Matching records from both tables
- Often used for code lookup
- Can also be used in an Update and Delete statement
- Partial Join (Left or Right) – All records from one table and matching records from another table (example: orders and shipments)
- All records from each table combined where matching
- Null values for columns not matched
- Example: Customer activity combining order and shipments
- Nulls on a full outer join – do not compare
- Every row in one table is joined with every row in another table
- Visualize as a matrix or spreadsheet
- Example: Tally table ( http://www.sqlservercentral.com/articles/t-sql/62867)
- Right side is evaluated for each record on the left side
- Outer Apply/Cross Apply
- Best used with Table-Value Functions
- Example: Geo-Spatial functons
SQL Saturday #57 – Houston
I attended/volunteered at the first SQL Saturday (57) Houston. The event took place this passed Saturday at the Bammel Church of Christ and with about 185 in attendance (350 registered), the event was a success (IMO).
Main Picasa Photo Set for SQL Saturday #57
My Picasa Photo Set for SQL Saturday #57
Photos taken by Sri Sridharan of SQLRocks.com
–
Below is a roundup of SQL Saturday 57 recaps and reviews. I will update it as I see new posts come up.
- SQLSaturday #57 Thanks – @NancyHidyWilson
- SQLSaturday #57 Recap – @NancyHidyWilson
- SQLSaturday #57 – @sqlrocks
- SQL Saturday #57 – Houston – @midbightdba
- Review – SQL Saturday #57 Houston – @sqlavenger
- Wheeling, WV to Houston, TX – A SQL Saturday #57 Recap.. – @johnsterrett
- SQLSaturday #57 – @ryanjadams
- SQLSaturday Round-Up (Jan. 27-Feb. 2)
–
Can’t wait to see what the next Houston SQL Saturday holds…
SQL Server String Functions
Manipulating strings is a common task in T-SQL. Below are a few of SQL’s built-in functions that make dealing with strings easier.
LEN – determines the length of a string value.
Syntax: LEN ( string_expression )
LEFT – returns the specified number of characters from the left side of the string value.
Syntax: LEFT ( character_expression, integer_expression )
RIGHT – returns the specified number of characters from the right side of the string value.
Syntax: RIGHT ( character_expression, integer_expression )
SUBSTRING – returns the specified number of characters from the middle of the string value.
Syntax: SUBSTRING ( expression, start, length )
REPLACE – replaces characters in the string
Syntax: REPLACE ( string_expression, string_pattern, string_replacement)
STUFF – inserts characters in the string
Syntax: STUFF ( character_expression, start, length, character_expression )
T-SQL Code Sample
DECLARE @testString VARCHAR(20) SET @testString = 'Big Bang Theory' SELECT LEN(@testString) AS _Length, LEFT(@testString,3) AS _Left, RIGHT(@testString,6) AS _Right, SUBSTRING(@testString,5,4) AS _Substring, REPLACE(@testString,'Bang', 'Bazinga') AS _Replace, STUFF(@testString,5,0,'Big ') AS _Stuff
T-SQL Code Sample – Result
–
^..^
Great T-SQL Resource: Beyond Relational
I recently ran across the site Beyond Relational. It is full of great information on SQL Server. My favorite areas of the site are T-SQL Challenges and T-SQL Challenges for Beginners.
Whether you are just learning T-SQL or sharpening your querying skills, these two areas contain some great assignments to learn from. Check them out.
–
^..^
HASSUG Meeting – 04/13/10 – Recap
For the first time in a while, I attended the Houston Area SQL Server User Group meeting and I am glad I did. Especially since I walked away with a free (signed) copy of the book SQL In A Nutshell.
The speaker was Kevin Kline (@kekline) and the topic was The Top 10 Mistakes on SQL Server.
High Points Gleaned
10. Disks – Thinking space not IO (insufficient I/O, poor choice of RAID type, not enough spindles, etc.)
09. Ignorance – As an IT professional you should know how SQL Server works at an “internals” level.
08. No trouble-shooting methodology
07. Going with the defaults – Autogrow/autoshrink on DB’s, default filegroups, etc.
06. Security as an afterthought – SQL attacks are the #1 hack on the internet today.
05. Inadequate automation
04. Wrong feature or technique for the job
03. Apathy without change management
02. Inadequate preventative maintenance
01. Backups <> Recovery
Links
Announcements
May Meeting: TUE 5/11 @ 11:30AM – How to Protect SQL Server with DPM 2010 – Presenter: Javier Calvillo, Microsoft
Special Meeting: TUE 5/18 @ 6:30PM – Essential Database Maintenance – Presenters: Kimberly Tripp & Paul Randal, SQLSkills
July Meeting: TUE 7/13 @ 11:30 – Topic TBD – Brent Ozar
–
^..^
SQL Pass 2009 Recaps and Live Blogs
For people (like myself) who could not be present at SQL Pass 2009, I have listed some great recaps and live blogs below.
Michelle Ufford – Live Blog Posts
- Live Blogging: Keynote at PASS, Day 1
- Live Blogging: Keynote at PASS, Day 2
- Live Blogging: Keynote at PASS, Day 3
Pinal Dave – Recaps
- SQL PASS Summit, Seattle 2009 – Day 1
- SQL PASS Summit, Seattle 2009 – Day 2
- SQL PASS Summit, Seattle 2009 – Day 3
- SQL PASS Summit, Seattle 2009 – Day 4
Brent Ozar – Live Blog posts
Jack Corbett – Daily Recaps
- My First PASS Experiences
- Let the Learning Begin
- PASS Summit Day 2 – What More Can I Learn
- PASS Summit Day 3 – Brain on Overload
- PASS Summit 2009 – Wrap-up – What’s the Value?
Denny Cherry – Recap
–
Until next time…
SQL Pretty Printer Add-In for SSMS – Review
I came across an online Instant SQL Formatter a while back and have been using that for some time to format SQL code that I get from other people. Now I see there is an add-in for SQL Management Studio from the same author of the online version. I downloaded and installed the SQL Pretty Printer Add-In For SSMS (not free) and have really found it very helpful.
Once installed, you will see the following toolbar in SSMS.
I created a couple of quick queries without paying too much attention to formatting.
Here is the same code after using the Pretty Printer SQL Formatter. Nice!
There are also numerous settings for the formatting, which is also very cool.
In closing, if you are like me and have an OCD when it comes to consistently formatted code, you will find this tool useful. The place where it is really useful and time-saving is on REALLY long Stored Procedures. I have had to deal with this lately, so I use this tool daily.
SQL Pretty Printer Add-In for SQL Server Management Studio
–
Until next time…
SSMS Tip – Display Results in a Separate Tab
One of my co-workers pointed out a setting in SQL Server Management Studio that has been extremely useful to me. It is the [Display Results in a Separate Tab] setting. This setting is helpful if you want to see more T-SQL code and/or more data in the return set. I am a fan of both.
To enable this setting go to Tools > Options…
Select Query Results > SQL Server > Results to Grid — and check Display results in a separate tab as well as Switch to results tab after the query executes
As you can see, when you execute a query, the results are displayed in their own tab.
I have found this setting helps a lot with really long stored procedures. It is nice to be able to see more code at one time.
–
Until next time…
SSMS Tools Pack – Review
I downloaded the SSMS Tools Pack about 3 weeks ago at the urging of a co-worker. Boy I am glad I did. There are many useful tools in this collection from Mladen Prajdic.
Below are the tools that I have used the most (and LOVE)
- Text document Regions and Debug sections – This helps with very large Stored Procedures. I know this because for the passed month, I have had to wade through very large ones.
- Window Connection Coloring – This tool is awesome if you are having to constantly switch between Development, Staging and Production servers. It has already saved me several times.
- New Query Template – Nice tool to help speed up creating a new query by defaulting in items from a template.
Other useful tools included in the pack
- Query Execution History (Soft Source Control) and Current Window History.
- Search Table or Database Data.
- Uppercase/Lowercase keywords and proper case Database Object Names.
- Run one script on multiple databases.
- Copy execution plan bitmaps to clipboard.
- Search Results in Grid Mode and Execution Plans.
- Generate Insert statements for a single table, the whole database or current result sets in grids.
- Running custom scripts from Object explorer’s Context menu.
- CRUD (Create, Read, Update, Delete) stored procedure generation.
So far, I find the SSMS Tools Pack extremely useful and am happy my co-worker recommended it.
–
Until next time…
