How to escape from the View designer in MS SQL Server Management Studio (SSMS)

I started working with SQL Server, and MS SQL Server Management Studio about a year ago, and I quickly got frustrated with the View designer module.  If you click on "New View" in the object explorer, or if you right click on an existing View and choose "Design," it opens up this multi-pane view designer with a graphical representation of the View on top, followed by some table listing the View elements, followed by the SQL for the view formatted as nearly impenetrable blocks of code.  For simple Views the View designer was fine, but for complicated Views I came to hate working with it.  I especially hated the fact that if I tried to format the SQL in a more readable layout, the View designer would throw out my formatting the next time I opened the View in Design mode.

I finally decided that there had to be a better way, and thanks to some discussions on Stack Exchange (natch) I pieced together the following:

• You can edit a View in plain SQL, and have your layout and formatting preserved when you save, by doing the following:
• Right click the View
• Select Tasks -> Script View as -> ALTER To -> New Query Editor Window
• Make your edits in the resulting Query Editor window
• Hit Execute when you are done to save your changes.  The ALTER TO command replaces the current version of the View with your edited version.
• You can quickly clean up the brain dead SSMS formatting of the SQL of existing Views using the free poorsql.com website. You block and copy the ugly SQL into the window, choose from various formatting styles, and then the site instantly prepares a properly formatted version of the SQL.

How to export a table as a SQL script using Microsoft SQL Server Management Studio

I come from the PHP-MySQL world, so I am used to being able to quickly generate a backup of a table as a SQL script (CREATE + INSERT) using the easy to find Export function in phpMyAdmin.  When I had to  use SQL Server for a project I was a bit puzzled when I couldn't find a similar feature in Microsoft SQL Server Management Studio (MS-SSMS).  However, a bit of Googling revealed the (cumbersome) solution:

• In the Object Explorer in MS-SSMS right click the database that has the table you want to export.
• Select Tasks from the right-click menu.
• Select Generate Scripts from the sub menu, which will open a pop-up called Generate and Publish Scripts.
• Click Next on the Introduction screen, which takes you to the Choose Objects screen
• On the Choose Object screen select the "Select specific database objects" option
• Expand the Tables object by clicking the little plus sign
• Select the table(s) you want to export
• Click Next, which takes you to the Set Scripting Options screen.
• Choose where you want the output to go (file, clipboard or new query window)
• Click the Advanced button, which launches a pop-up window called "Advanced Scripting Options" with a list of options
• Scroll down to the "Types of data to script" option, which is at the very bottom of the set of options called  "General."
• Change the "Types of data to script" option from "Schema only" to "Schema and data."  This is what will make the SQL include the data from the table.
•  Click OK to close the Advanced Scripting Options pop-up.
• Click Next on the Set Scripting Options screen.
• Click Next on the Summary screen where it says "Review your selections."  This is what triggers the actual export of the table.
• Click Finish to close the Generate and Publish Scripts pop-up.

There, was that so bad? Well, yes, it was a lot of clicking for something that people probably do relatively frequently, but that is Microsoft for you.

How to get MS SQL Server nvarchar() field to sort correctly using ORDER BY

I had an nvarchar field in MS SQL Server that wasn't sorting correctly when I did an ORDER BY on it.  For example, 10 would be put before 9 if I sorted ASC.  It turns out that MS SQL Server considers the position of each character in the field in sorting, and since the field is nvarchar (variable width) the digits of a number are not in consistent positions in the field.  I found the solution in this post on stackoverflow.com:

Order By Clause for NVARCHAR column in SQL Server

The specific code is:

ORDER BY RIGHT(REPLICATE(N' ', 50) + the_column_you_want_to_sort_by, 50)

What this does is virtually add enough leading whitespaces to each entry to make the column look like a fixed width column for the purposes of the query.