Scripting Tables with Indexes in SQL Server Management Studio

 OK, so you got some tables you want to script out for whatever reason.

You notice when you do, some of your indexes are missing.

What the heck?

No biggie...just need to set some default from false to True.

SSMS->Tools->Options->SQL Server Object Explorer->Scripting->Table and view options (on right)

Toggle Script Indexes to True.
If you use triggers or other options there, I would toggle them to true as well.

Reference...

https://stackoverflow.com/questions/3067599/script-table-as-create-via-ssms-doesnt-show-unique-index

SQL Dates in Where Clauses

I always forget how to do this, so I'm making a post more for my quick reference than anyone else's.  Anyway, this format should work not matter what culture you format your dates in...using Microsoft SQL Server variants anyway...

WHERE datetime_column BETWEEN '20081220 00:00:00.000'
                          AND '20081220 23:59:59.997'

'YYYYMMDD HH:MM:SS:XXX' xxx being milliseconds.

More information here-

http://stackoverflow.com/questions/1947436/datetime-in-where-clause

SQL Server Join Hints

Here is a link to a great article explaining SQL Server Join hints...the how, the when, and the why to use them.

http://www.mssqltips.com/sqlservertip/2917/sql-server-join-hints/

From the article-

"In summary, here's when to use the various types of join:

LOOP JOIN
Query has a small table on the left side of the join
One or both tables are indexed on the JOIN predicate

HASH JOIN
Tables are fairly evenly-sized or are large
Indexes practically irrelevant unless filtering on additional WHERE clauses, good for heaps
Arguably most versatile form of join

REMOTE JOIN
Same as hash join, but good where right side is geographically distant
Only suitable for INNER JOINs
Not suitable for local tables, will be ignored.

MERGE JOIN
Tables are fairly even in size
Works best when tables are well-indexed or pre-sorted
Uses very efficient sort algorithm for fast results
Unlike hash join, no memory reallocation, good for parallel execution

And if in doubt - let the optimizer decide!"

Using Bit Flags in SQL

Using bit flags is a great way to store a lot of information in a single place that can be used in a variety of ways, of which the most common I've seen is to use big flagging for permission checks. I could write the following post myself, but I don't think I could add much to the following excellently done reply to a question on StackOverflow about using bit flags in SQL, so here you go-

[Link]

Also if you are a little rusty on using bit flags in .Net, here is a refresher article link-

[Link]

Using SQL Server Dates Without Time

I often find myself in a scenario where I want customers to be able to select dates, delete items by setting an inactive date to today rather than actually removing something from a database, etc...

Sometimes it helps to only deal in dates, rather than in date/time. Using the GetDate() function in SQL brings back a full date with the time string, but here is a great method to fix that.

declare @somedate = cast(floor(cast(GetDate() as float)) as DateTime)

There are a few other ways to do this, but this one seems to perform the best. Tested with SQL 2008 and works great.

Hat tip too Ben Nadal's blog, more info here-

http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm

Using SQL Hints

I can get around SQL Server fairly well, but I'm not a guru. One trick I was shown today was the use of SQL Hints. Here is a real world scenario where they really help out.

I had a query that was taking about 44 seconds in enterprise manager. My boss came over and showed that the main drag was nested queries (by viewing the execution plan in the query analyzer).

So we added the following hint.

Select blah blah

from blah blah

where blah blah

option ( hash join )


Wow, 44 seconds to less then 1 second. Also option ( merge join ) dropped the query execution time down to less then a second as well.

Is it then or than? Oh well. Public school system

MS SQL 2008 Developer Training Kit

This might be of interest, a training kit for MS SQL 2008.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e9c68e1b-1e0e-4299-b498-6ab3ca72a6d7

Source: Pinal Dave

Great SQL Server Maintenance Article

I know a lot of people are using SQL Server 2005/2008 Express for production (though they shouldn't). I also know folks are using the cheaper SQL Server 2008 Web version for web databases. Both of these databases lack some maintenance and backup features that their more expensive cousins have. If you find yourself needing those features but unable to afford them, this article might help.

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ExpressMaintenance&referringTitle=Home

Abyss and Sql Server 2008

You might be wondering how hard it is to get php and SQL Server 2008 talking to each other on an abyss web server. Turns out it is pretty easy. Here are the basics (which is pretty much exactly how you think you would do it). Below assumes you already have Abyss, php, and sql server up and running.

1. Download and "install" (unpack into a folder) the latest php/SQL Server driver (it says 2005, works wth 2008, but you have to get funky if you want to use some of the new 2008 data types...update in the works).

http://www.microsoft.com/DOWNLOADS/details.aspx?FamilyID=61bf87e0-d031-466b-b09a-6597c21a2e2a&displaylang=en

2) Copy the php_sqlsrv_ts.dll from your driver's install folder into your php folder.

3) (Optional: Back up your php.ini file in your php folder, then...) Add the following line into the extensions section of your php.ini file-

extension=php_sqlsrv_ts.dll

4) Restart Abyss, and you should be done. You can check for a sqlsvr section in php_info.

I haven't done to much testing with this, but it says it has installed, so for now I will assume good. Also if you have sql server running on a different box, you may have to tweak your php interpreter settings in abyss from named pipes local to named pipes tcp/ip. Not sure as I'm just using a local version of sql express.

So I won't forget this the double split

coming back and editing this later. nothing that anyone couldn't figure out on their own, just something I bump into, here for ref

declare @p_value_rows as varchar(300)
set @p_value_rows = '234f~asd4adsfa~0dfsg'


      SELECT
          SUBSTRING(value_desc,0,CHARINDEX('~',value_desc))
        , SUBSTRING(value_desc,CHARINDEX('~',value_desc)+1,LEN(value_desc) - CHARINDEX(value_desc,'~'))
      FROM [db splitter function here](@p_value_rows, '')

Saving XML to SQL and the Dreaded Parse Error

I recently at work tried to save an XML string to SQL Server 2005 and I kept getting the following error...

XML parsing: line 1, character 38, unable to switch the encoding

Turns out the solution was to just change the paramter datatype that I'm passing to the stored proc to a varchar (even though the stored proc expects XML) and the problem goes away and data is saved fine. Example of the parameter I passed to the stored proc-


SqlParameter p_xml = new SqlParameter ();
p_xml.ParameterName = "@p_xml";
p_xml.SqlDbType = SqlDbType.VarChar;
p_xml.Value = "your xml string";


The issue has to do with my string XML being UTF-8 based, and SQL 2005 wanting UTF-16. The above hack fixes the issue, and you can still pass UTF-16 based XML and the varchar handles that as well.

Credit for the fix goes to Bob Beauchemin at this link-

http://bytes.com/forum/thread498342.html

Stored Procs vs Dirty SQL

Every once in a while I stumble across a blog that says stored procs are no longer the way to go in database development. I believe I see this for a few reasons

1) This view is often exposed by younger developers who, though extremely talented, do not have heavy enterprise data manipulation experience.

2) Advocates of this view typically are heavily into auto code generation tools, which don't play well with stored procs.

Here is an example of the type of scare crow testing I see (make a weak comparison, beat it up, then declare your opinion superior).

http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx

Wow, a select statement is almost as fast with dynamically generated SQL then with a stored proc, and even in some cases, a stored proc might be slower.

Well, if you work with data heavily on a regular basis, you wish you were writing select statements all day :) The case of comparison here is bogus. If select and basic CRUD on a single table is all you are using SQL for, you probably shouldn't talk about how your are an expert on the intricacies of SQL performance.

In my enterprise experience, it is not uncommon for a query will pull back data from five or six tables, which often times aren't tables at all but subqueries themselves. CRUD operations sometimes spam five or six tables. Putting all your code in a stored proc allows you to have a single place to change things, and often times updating the database in an enterprise environment is a lot easier then touching production code. I also don't believe that these types of queries are faster with dynamic sql. The dynamic SQL must be generated on a webserver or a client, so you take a hit there that isn't often factored into test data. Execution plans are generated at query runtime, while a stored proc has its execution plan cached. Big performance boost. Though, as pointed out, sometimes dynamically generated plans might run slightly faster. With complex SQL operations, I doubt that though. You also are slightly more open to SQL injection attacks by building your app using dynamic SQL, but if you know what you are doing you should be able to avoid vulnerabilities there. Stored procs also aren't invulnerable to attack either, they just tend to be more so. Also by using dynamically generated SQL you are loosing out on the CLR integration into SQL Server (if using MS tech), and depending upon what you are trying to accomplish using CLR stored procs might be a powerful, fast option that you are passing up.

So, if you are fortunate enough to be able to pull off simple queries for your application, and you have a robust app that is responsive and meets your client's needs, good for you. Keep at it. I'm not a huge fan of code generation tools, as it seems like when you go outside of the vanilla, you end up spending a lot of time and effort getting your non-vanilla task done. If you know the base tech really well, typically you already have a library of code that acts almost like a code generation tool. Plus there is the additional learning curve of a code generator, do I want to spend all the time learning something that's features might be included in future versions of visual studio? Or will my code gen tool be around five years from now? What if I want to pass my code of to a junior developer? Will I have to spend time teaching them, or allocating time for them to learn, a code gen tool that might be eclipsed by another tool that is more in vogue that wasn't around when my base code was written? But that is neither here nor there. Separate discussion.

I've written dynamic SQL apps before, and they have worked well. If your app works well with dynamic SQL and using dynamic SQL allows you to gain efficiencies by using your code gen tools, good for you. Keep at it. But save your lectures about stored procs being dead, because depending on your environment, they are far from dead. Often times stored procs are the only practical way to go.

PHP Sql Server 2005 Native Driver

Yep, its here.

http://www.microsoft.com/downloads/details.aspx?FamilyId=61BF87E0-D031-466B-B09A-6597C21A2E2A&displaylang=en

Referrenced from -

http://www.misfitgeek.com/

SQL Identities

I always get confused, is it scope_identity, @@ident, or what to get the primary key of a row I just inserted?

The SQL Authority Blog does a good job of sorting things out.

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

The short answer is that you want to use SCOPE_IDENTITY()

And if you are stuck with developing with Access, here is your answer-

http://databases.aspfaq.com/general/how-do-i-get-the-identity/autonumber-value-for-the-row-i-inserted.html

Another technique is to generate a unique key on the server and then insert that as your primary key into the database. Since you insert it with the other info you usually know what it is. Usually developers use GUID's for this, which you can create both in classic ASP (check out my classic ASP post) and natively in .NET. There is a performance hit using GUID's though, but you can minimize that hit by using COMB GUID's. I've got some code for generating those somewhere, but I'm not going to hunt for it tonight. But here is a discussion thread with some code samples-

http://www.informit.com/discussion/index.aspx?postid=a8275a70-0698-46f0-8c8f-bf687464628c

NEWSEQUENTIALID is also available, but that is created on the database side, so the purpose is kind of lost, so I'd stick with COMB GUID's.

I used to get pretty religious about this type of stuff (hated GUID's), but ultimately most of us aren't building the next Amazon, Google, MySpace, etc..., so getting anal about primary keys to get that last ounce of efficiency isn't worth the time unless you approach that scale.

It seems like things are moving several steps away from the database in abstraction levels anyway (with O/R mappers and technology like LINQ). We have so much computing horsepower now that bloat seems to be the way of the future...

SQL Tip

I've had to create a lot of dynamic queries in the past, and today I stumbled upon something so simple I can't believe I missed it before. Let's say you are doing a query with 100 parameters. In the past sometimes I would generate a dynamic sql statement either on the client side (less then desirable) or in a stored proc (still undesirable). Here is a simple technique that helps eliminate MOST (not all) instances where you need to do this.

set parameters that may or may not exist in the query to default to null in your stored proc.

Then you can do things like...

where
(mytable.value = @myparameter or myparameter is null)

Amazingly simple!

So I get a big Duhhh for not using this in a few projects in the past.

Inserting a Null Value in SQL Server Through Enterprise Manager

I always freaken forget this, so I'm putting it here so I won't.

To add a null value to a column in SQL Server Enterprise Manager, just hit [CTRL] [0].

SQL Table to Comma Delimted String

I always wondered how to turn the results of a SQL query INTO a delimited string. On a whim I did a google search today and found out how. The technique actually isn't that complex.

Check out how by clicking [HERE].

Here is a primitive example of how it is done from the above page.

DECLARE @p_str VARCHAR(1000)
SET @p_str = ''

SELECT @p_str = @p_str + ',' + CAST(productid AS VARCHAR(6))
FROM [order details]
WHERE orderid = @p_order_id