http://www.sqlservercentral.com/Content tagged T-SQL, Programming, Miscellaneous posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)The use of bitmasking is usually limited to decoding values in the system views, but it can be useful in other places when you want to save space. New author Lee Everest brings us an introduction to bitmasking and its use in SQL Server 2005. http://www.sqlservercentral.com/articles/Miscellaneous/2748/2007/12/05http://www.sqlservercentral.com/articles/Miscellaneous/2748/The use of bitmasking is usually limited to decoding values in the system views, but it can be useful in other places when you want to save space. New author Lee Everest brings us an introduction to bitmasking and its use in SQL Server 2005. http://www.sqlservercentral.com/articles/Miscellaneous/2748/2007/12/05http://www.sqlservercentral.com/articles/Miscellaneous/2748/Testing is an important part of programming and it is no different with SQL Server development. Longtime author Grant Fritchey brings us a look at the new testing features available in Visual Studio 2005. http://www.sqlservercentral.com/articles/Miscellaneous/2689/2006/11/15http://www.sqlservercentral.com/articles/Miscellaneous/2689/SQL Server does a great job of handling concurrency & ensuring that users can make changes in multi-user systems without conflict. However there are times a strict calling order is needed. http://www.sqlservercentral.com/articles/Miscellaneous/2649/2006/10/17http://www.sqlservercentral.com/articles/Miscellaneous/2649/Not many people enjoy using Globally Unique Identifiers (GUIDs), partly because they are cumbersome to type and work with for humans. However they fill a need and can provide some interesting benefits. SQL Server expert Andy Warren takes us through what a GUID is and how you can use it easily in your code. http://www.sqlservercentral.com/articles/Miscellaneous/alookatguids/2497/2006/07/27http://www.sqlservercentral.com/articles/Miscellaneous/alookatguids/2497/Part 1 of a 4 part series about ADO, this is a beginner level article designed to get you started using the ADO connection object. If you haven't used ADO so far, why not see what it's all about? http://www.sqlservercentral.com/articles/Miscellaneous/introductiontotheadoconnectionobject/510/2006/05/26http://www.sqlservercentral.com/articles/Miscellaneous/introductiontotheadoconnectionobject/510/For many of us, working with the identity property allows us to easily generate a sequential series of numbers. But what if you need to generate a sequence according to some pattern, like one that includes the year? New author Asif Sayed brings us a technique and the code for generating a patterned sequence. http://www.sqlservercentral.com/articles/Miscellaneous/generatingasequentialpattern/2390/2006/05/17http://www.sqlservercentral.com/articles/Miscellaneous/generatingasequentialpattern/2390/Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing! http://www.sqlservercentral.com/articles/Miscellaneous/howtobuilddynamicstoredprocedures/968/2005/12/16http://www.sqlservercentral.com/articles/Miscellaneous/howtobuilddynamicstoredprocedures/968/A short but interesting article, the author has figured out a way to create a PDF from a stored procedure without using a third party library. http://www.sqlservercentral.com/articles/Miscellaneous/creatingapdffromastoredprocedure/1104/2005/09/02http://www.sqlservercentral.com/articles/Miscellaneous/creatingapdffromastoredprocedure/1104/We've had a lot of coverage of dynamic sql (including another great one from Robert Marda later this week) but this one is a little different. Done in a question/answer format, Andy tries to explain to junior developers why dynamic sql is to be avoided, how to do so, what to do when you can't. http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicsqlorstoredprocedure/969/2005/08/26http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicsqlorstoredprocedure/969/SQL Server 2000 is an extremely powerful and flexible development platform, but if the users mess up the data, there's nothing you can do. Protecting the integrity sometimes means severely limiting access to users. Roy Carlson brings us a technique using sp_makewebtask that you can easily use to provide users with some data without giving them query access. http://www.sqlservercentral.com/articles/Miscellaneous/youwanttodowhatwithmydatabase/1882/2005/05/17http://www.sqlservercentral.com/articles/Miscellaneous/youwanttodowhatwithmydatabase/1882/Do you use the maintenance plans or hate them? Wish they would do more? Curious about how they work under the hood? Cmon, you gotta read this one! Trust us, it's not another "how-to" article! Well, maybe just a little bit! http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/2005/03/04http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/Mindy explores the metadata stored in SQL 2000 to show you how to produce a simple and useful data dictionary! http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/2005/02/04http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/This is the first article by Steve Jones that examines a programming technique for handling operations that may be too large to run in a single query. http://www.sqlservercentral.com/articles/Miscellaneous/batching/151/2004/11/05http://www.sqlservercentral.com/articles/Miscellaneous/batching/151/A new article from MSDN magazine on stopping SQL Injection attacks by examining how the SQL can be exploited. It's a good basic article for developers as well as DBAs who might have to review code.http://www.sqlservercentral.com/redirect/articles/1470/2004/08/20http://www.sqlservercentral.com/redirect/articles/1470/What do your programmers do with nulls? Do they know nullif, isnull, what about the powerful coalesce function? Do they think DBA's look at the world differently? Why, it's all code isn't it? http://www.sqlservercentral.com/articles/Miscellaneous/thinklikeadba/1410/2004/07/29http://www.sqlservercentral.com/articles/Miscellaneous/thinklikeadba/1410/Know anything about bowling? Or writing hard core TSQL? Or test driven development? Even if you don't, this might be a good way to build those skills. Write a stored procedure that can score a bowling game and you might win one of our books and a shirt! http://www.sqlservercentral.com/articles/Miscellaneous/thebowlingchallenge/1440/2004/07/22http://www.sqlservercentral.com/articles/Miscellaneous/thebowlingchallenge/1440/Reducing the amount of round trips between a server and client is something that can give you a great boost in performance. David Poole looks at how he solved a problem with HTML checkboxes and the challenges they solve in a programming environment. Without Dynamic SQL! http://www.sqlservercentral.com/articles/Miscellaneous/reducingroundtripsworkingwithhtmlcheckboxes/1398/2004/06/11http://www.sqlservercentral.com/articles/Miscellaneous/reducingroundtripsworkingwithhtmlcheckboxes/1398/This article shows some options to retrieve all the metadata you'll need to write scripts that write scripts. No, that's not a goof, this article is about code generation. http://www.sqlservercentral.com/articles/Miscellaneous/speedupdevelopmentonbackendandmiddletierwithsqlscr/1327/2004/04/09http://www.sqlservercentral.com/articles/Miscellaneous/speedupdevelopmentonbackendandmiddletierwithsqlscr/1327/Dinesh ran into a situation where a client had very specific requirements for generating keys. Take a look at the solution, can you better alternatives? Sometimes you just can't talk the client into changing! http://www.sqlservercentral.com/articles/Miscellaneous/generatenextnumberswithsqlserver/1304/2004/03/05http://www.sqlservercentral.com/articles/Miscellaneous/generatenextnumberswithsqlserver/1304/David recently worked on a project where it turned out storing the answers to a survey using bitmapping was a good approach. He was good enough to write some of it down and share. As he notes bitmapping isn't used as often as it used to be, but it can still be a useful technique to have around. http://www.sqlservercentral.com/articles/Miscellaneous/usingbitstostoredata/1251/2004/01/06http://www.sqlservercentral.com/articles/Miscellaneous/usingbitstostoredata/1251/As Chris points out, in most applications these days you end up having to go with optimistic locking, which presents a few challenges. Chris works through the list of options. If you're building web/disconnected apps and need anything besides last update wins, this one is for you. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/dealingwithchangingdata/1233/2003/12/19http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/dealingwithchangingdata/1233/Full text indexing is interesting, but not always easy for the user to apply well. Chris has some ideas about to make that http://www.sqlservercentral.com/articles/Miscellaneous/fulltextindexingtextparsingroutine/1207/2003/12/11http://www.sqlservercentral.com/articles/Miscellaneous/fulltextindexingtextparsingroutine/1207/Another product review! Chris put AdeptSQL (a schema comparison and sync product) to work and wrote up the results. This article also includes some feedback from the vendor that is worth reading. http://www.sqlservercentral.com/articles/Administering/reviewofadeptsql/1173/2003/11/03http://www.sqlservercentral.com/articles/Administering/reviewofadeptsql/1173/A short but interesting article, the author has figured out a way to create a PDF from a stored procedure without using a third party library. http://www.sqlservercentral.com/articles/Miscellaneous/creatingapdffromastoredprocedure/1104/2005/09/02http://www.sqlservercentral.com/articles/Miscellaneous/creatingapdffromastoredprocedure/1104/Andy rates this one 4.5 out of 5 stars and likes it enough to recommend it's use at work. This is a book that should teach a developer how to use the key abilities of SQL. If you're looking for a book to guide your beginning/intermediate developers, this might be it. http://www.sqlservercentral.com/articles/Miscellaneous/reviewofmurachssqlforsqlserver/1110/2003/08/06http://www.sqlservercentral.com/articles/Miscellaneous/reviewofmurachssqlforsqlserver/1110/Bad data is almost a given, but true duplicate data can really cause you some headaches. How do you remove all the duplicates and still leave a 'keeper' record? If you think procedural code it's not too hard, but can you do a set based solution? Chris shows you show! http://www.sqlservercentral.com/articles/Miscellaneous/findinganddeletingduplicatedata/1075/2003/07/25http://www.sqlservercentral.com/articles/Miscellaneous/findinganddeletingduplicatedata/1075/As you might guess from the title, this is an extended stored procedure that you can use to interact with MSMQ. It's supposed to be extremely fast, able to handle 4-5k messages per second and have a very small memory footprint. (Press Release) http://www.sqlservercentral.com/redirect/articles/1053/2003/07/17http://www.sqlservercentral.com/redirect/articles/1053/Pedro Estrada has published a free tool that according to his site "This is a complete application to have an easier interface to work with BCP against MSSQL servers. Just specify a server, database, table user name and password to BCP out or IN." Worth a try, and he has some other free utilities (some SQL, some not) on the site.http://www.sqlservercentral.com/redirect/articles/1026/2003/06/19http://www.sqlservercentral.com/redirect/articles/1026/This article shows you how to identify SQL Server performance problems and explains how the SERIALIZABLE transaction isolation level effected the performance of an OLTP application.http://www.sqlservercentral.com/redirect/articles/931/2003/05/22http://www.sqlservercentral.com/redirect/articles/931/