[mssql] REPLACE Multiple Spaces with One

Introduction

Replacing multiple spaces with a single space is an old problem. If you Google the problem, you find that most folks still resort to While Loops in functions or maybe even a Tally table or (ugh!) XML in a function to solve this seemingly complex problem. The truth is that you don't need the RBAR of a User Defined Function at all.

This article explains the "puzzle solving" behind a common set based method to replace unknown numbers of adjacent spaces with a single space by establishing and replacing simple patterns in a set based fashion.

The Problem

You have a column of data that looks something like the following...

--===== Create and populate a test table.
     -- This is NOT a part of the solution.
DECLARE @Demo TABLE(OriginalString VARCHAR(8000))
 INSERT INTO @Demo (OriginalString)
 SELECT '  This      has multiple   unknown                 spaces in        it.   ' UNION ALL
 SELECT 'So                     does                      this!' UNION ALL
 SELECT 'As                                does                        this' UNION ALL
 SELECT 'This, that, and the other  thing.' UNION ALL
 SELECT 'This needs no repair.'

The goal is to convert the sections of multiple spaces of unknown length to a single space each as quickly as possible. There are some additional requirements. You can't make the data any bigger during the process because it might get too big for VARCHAR(8000) (or whatever size the column is) or you can't use VARCHAR(MAX) because you're using SQL Server 2000.

The Method Explained

I'll use the letter "O" to act as a "visible" space so you can see what I'm talking about... consider the following sets of spaces, please....
O
OO
OOO
OOOO
OOOOO
OOOOOO
OOOOOOO
OOOOOOOO

Remember the goal is to convert all of those sets of spaces to just a single space without the use of RBAR even if the RBAR is through the simple use of a UDF. I'll also state that the goal is to remove the extra spaces without making the original string any larger in the process because it may already be as large as it can be for the given datatype.

So... STEP 1 is to identify pairs of spaces. This is done by modifying the second space in each pair of spaces to be an "unlikely" character. In this case, I'll use the visible character of "X" (which isn't unlikely but serves this visual example) to represent a space that has been changed in a pair of spaces.

When we replace all pairs of space "OO" with "OX", we get the following
O
OX
OXO
OXOX
OXOXO
OXOXOX
OXOXOXO
OXOXOXOX

STEP 2 is to replace all occurrences of "XO" with NOTHING...
O
OX
OXO
OXOX
OXOXO
OXOXOX
OXOXOXO
OXOXOXOX

... and that leaves us with ...
O
OX
O
OX
O
OX
O
OX

STEP 3 is to replace "X" with NOTHING...
O
OX
O
OX
O
OX
O
OX

... and that leaves us with just singles spaces everywhere...
O
O
O
O
O
O
O
O

Again... the "O"s represent unmodified spaces and the "X"s represent spaces changed to some "unlikely character" like a special non printable, almost non type-able character like ASCII 7 (the "Bell" character). It's just a matter of 3 nested REPLACE functions to handle ANY number of spaces to accomplish the puzzle we solved above. It can all be done in a single set-based query without loops or even UDF's.

Be careful which "unlikely character" you pick, though. We'll talk more about that when we get to the "Unlikely Characters and Collation" section of this article further below.

The Code

Ok... now that you know how it works, here's the code that accomplishes the 3 steps as 3 nested REPLACE's. I've included the test table I previously covered in "The Problem" section of this article just to make things easy to run. Notice that I've also added an LTRIM/RTRIM to take out any leading or trailing spaces, as well...

--===== Create and populate a test table.
     -- This is NOT a part of the solution.
DECLARE @Demo TABLE(OriginalString VARCHAR(8000))
 INSERT INTO @Demo (OriginalString)
 SELECT '  This      has multiple   unknown                 spaces in        it.   ' UNION ALL
 SELECT 'So                     does                      this!' UNION ALL
 SELECT 'As                                does                        this' UNION ALL
 SELECT 'This, that, and the other  thing.' UNION ALL
 SELECT 'This needs no repair.'

--===== Reduce each group of multiple spaces to a single space
     -- for a whole table without functions, loops, or other
     -- forms of slow RBAR.  In the following example, CHAR(7)
     -- is the "unlikely" character that "X" was used for in 
     -- the explanation.
 SELECT REPLACE(
            REPLACE(
                REPLACE(
                    LTRIM(RTRIM(OriginalString))
                ,'  ',' '+CHAR(7))  --Changes 2 spaces to the OX model
            ,CHAR(7)+' ','')        --Changes the XO model to nothing
        ,CHAR(7),'') AS CleanString --Changes the remaining X's to nothing
   FROM @Demo
  WHERE CHARINDEX('  ',OriginalString) > 0

Because of the equivalent treatment of the Thorn (þ) character and the "th" digraph, you end up with a most undesirable result...

Results from Code Above

CleanString
is has multiple unknown spaces in it.
So does is!
As does is
is, at, and e oer ing.

... which is nothing like what we wanted.

There's also a danger in selecting the wrong "control character" (ASCII characters 0 through 31) as the "unlikely character". I chose CHAR(7) which is a very benign character in today's world of electronics. It was designed to literally ring the bell on old Tele-Type machines and is just passed through by today's electronics.

If you chose CHAR(0), you've just chosen the "NULL" character and anything that follows it's appearance will simply disappear. Using the current test table in this article, you'd get an output that looks like the following...

Results from Code Above

CleanString
This
So
As
This,that,andtheotherthing.

If you chose CHAR(1), you've just chosen the ASCII "Start of Header" character which is still in use today. When certain electronics see this character, it can cause some very strange behavior usually resulting in failure of your code.

Another good "unlikely character" is CHAR(8) which is the ASCII "Backspace" character. It's normally never included in any type of assembled text now adays. CHAR(127) also works well because it's a left over for "Delete" from the paper tape world. It actually punches all the holes in a paper tape (7 of them) to quite literally delete a character.

For more information on the ASCII "Control Characters", please see the following URL:
http://www.lammertbies.nl/comm/info/ascii-characters.html#cont

Conclusion

To borrow a phrase from R. Barry Young's series of articles on the subject, "There must be 15 ways to lose your cursors". Even the seemingly complex task of condensing multiple adjacent spaces to a single space can be done without a loop.

When you run into a problem where you're absolutely sure there's no way to do it without a loop, have another look. There's usually a high performance way to avoid the loop.
Thanks for listening, folks.

--Jeff Moden

[picture] Ctrl+Alt+Del

Have you ever thought of the person who invented "CTRL + ALT + DEL " key combination.

"David Bradley"

He is the One who spent 1 minute and 23 seconds in writing the source code that rescues

The world's PC users for decades This extraordinary IBM employee is retiring on Friday, 25th March 2005 after a prolong service of 29 years.

His formula forces obstinate computers to restart when they no longer follow other commands.

By 1980, Bradley was one of 12 people working to create the debut.

The engineers knew they had to design a simple way to restart the computer When it fails to respond the user Bradley wrote the code to make it work.

Bradley says. "I did a lot of other things than Ctrl-Alt-Delete, but I'm famous for that one."

His fame and success is achieved each time a PC user fails.

He Commented His relationship with Bill gates by saying

"I may have invented it, but Bill gates made it famous by applying my formula When ever any Microsoft's Windows operating system made by him CRASHES,

Thus I win when ever he looses..........."

[picture] Mouse Inside Cat

[picture] Honesty On The Internet

[picture] What Happens to IT Professionals after Death

[mssql] How To Get Table Row Counts Quickly And Painlessly

At some point in time we've all had to find out how many rows are in a table. The first answer you'll usually get when you ask someone how to do it is select count(*) from [table], however there are two problems with this approach: First, a table scan is required to figure out the answer; do a count(*) against a million row table and you're looking at hundreds of thousands of reads (and likely several minutes waiting for the result). Second, the count(*) method doesn't work well if you want to know how many rows are in every table in your database.

It just so happens there's a system function in SQL Server that can help solve both of these problems: sp_spaceused (BOL entry here) . When run without any parameters it returns usage information about the current database its being run in; when provided a specific object name (e.g. a table name) it returns the number of rows along with the amount of space used by\allocated for the table and its indexes. Looking under the covers of sp_spaceused reveals that the rowcount information is coming from the sysindexes table on SQL 2000 and the sys.dm_db_partition_stats DMV on SQL 2005\2008. Since the counts are coming from system objects there's no table scan involved - Problem #1 solved!

To solve problem #2 you could use a cursor to iterate through all tables (or the undocumented stored procedure sp_foreachtable), calling sp_spaceused for each table and storing the output in a temporary table...or just query the system objects directly.

Row Counts Using sysindexes
If you're using SQL 2000 you'll need to use sysindexes like so:

-- Shows all user tables and row counts for the current database
-- Remove OBJECTPROPERTY function call to include system objects
SELECT o.NAME,
       i.rowcnt
  FROM sysindexes AS i
   INNER JOIN sysobjects AS o 
     ON o.id = i.id
  WHERE i.indid < 2 AND
        OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
  ORDER BY o.NAME

Row Counts Using DMVs
If you're using SQL 2005 or 2008 querying sysindexes will still work but Microsoft advises that sysindexes may be removed in a future version of SQL Server so as a good practice you should use the DMVs instead, like so:

-- Shows all user tables and row counts for the current database
-- Remove is_ms_shipped = 0 check to include system objects
-- i.index_id < 2 indicates clustered index (1) or hash table (0)
SELECT o.name, ddps.row_count
  FROM sys.indexes AS i
  INNER JOIN sys.objects AS o 
    ON o.OBJECT_ID = i.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps 
    ON ddps.OBJECT_ID = i.OBJECT_ID AND 
       ddps.index_id = i.index_id
  WHERE i.index_id < 2 AND
        o.is_ms_shipped = 0
  ORDER BY o.NAME

Are The Counts Accurate?
Some system objects are only as accurate as the current statistics and occasionally statistics get outdated and need to be refreshed. Fortunately row count information in sysindexes\DMVs does not depend on updated statistics. To put this to the test I disabled the Auto Update Statistics option on a database that sees several thousand updates each day. After several days I compared the counts returned by the select count(*) method and the system objects and they matched perfectly.

Obviously you'll need to revert to the select count(*) method if you need to filter out rows (using a where clause), but for unfiltered row count information there's no excuses not to use the system objects!

[vb6] Open Any Type Of File With Its Associated Window's Program

You simply want to be able to press a command button, which will trigger a file (e.g. "c:\media\mysong.mp3"), to be opened with by its associated application (e.g. "Media Player") just as what would happen if I went to the file in windows explorer, and double clicked it.

1. Create module modTools as following:

Attribute VB_Name = "modTools"
'*******************************************************************************
' Name        : modTools
' Author      : Chandra Gunawan
' Date        : 22-Jul-2009
' Description : Tools Library
'
' Maintenance Log
' ==============================================================================
' Date         ID   Description
' ------------------------------------------------------------------------------
'*******************************************************************************

Option Explicit

'==============================================================================
' CONSTANTS & VARIABLES DEFINITION
'==============================================================================
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"_
  (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
   ByVal lpParameters As String, ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) As Long

Private Const SW_SHOWNORMAL = 1

'------------------------------------------------------------------------------
' Name        : OpenFile
' Author      : Chandra Gunawan
' Date        : 13-Aug-2009
' Description : Open any type of file with its associated window's program
'------------------------------------------------------------------------------
Public Function OpenFile(ByVal pForm As Form, ByVal pFilePath$) As Long
  OpenFile = ShellExecute(pForm.hwnd, "open", pFilePath, vbNullString, vbNullString, SW_SHOWNORMAL)
End Function  'OpenFile

2. Execute OpenFile function inside your button's click-event

Private Sub Command1_Click()
  Call OpenFile(Me, "c:\media\mysong.mp3")
End Sub

[t-sql] Twenty tips to write a good stored procedure

By Arup Chakraborty, 2009/08/10
The writing of stored procedures is a very common task in todays database world. Not only by database developers, but also application developers are writing the procedures. DBA's also need to write procedures. In fact, I have faced a very common question in my interviews: " How many stored procedures have you written?"

Since the world is going for fat servers (a server which runs an application where most of the program code resides on it rather than on the client computers), stored procedures are gaining more and more importance. Here is my humble try to give some tips to write good procedures. I hope this will add value for novice as well as experienced programmers.

1. Keywords - Use SQL keywords in capital letters to increase readability. Also use proper indentation to increase readability.

2. SQL-92 - Always try to use ANSI 92 syntax. Till now the old syntax is working the old syntax will be deprecated in the next release of MS SQL server.
As an example, for joining, use

SELECT *

  FROM employee e1

  INNER JOIN employee _dtl e2

    ON e2.id = e1.id

Instead of

SELECT *

  FROM employee e1, employee_dtl e2

  WHERE e2.id = e1.id

3. Variables - Use as few as possible variables. It frees spaces in cache.
4. Dynamic Queries - Try to minimize the usage of dynamic queries. If you are using a dynamic query like: SELECT * FROM mydb.dbo.emp where empid = @eid then there is no problem. You can supply a value for the @eid parameter and there is no recompilation of the execution plan in the database cache. But if you are using a SQL query like SELECT * FROM emp where empid = " + @eid and supply a parameter (say 100), then the cache will keep the execution plan for the value of 100 only. If the id changes (to say 101), it will recompile the statement. Hence, this approach is slower than the previous one. (You can get the exact value of the SQL statement from Profiler)

5. Fully Qualified Names - Always use the fully qualified name when calling stored procedures. This would be the format database_name.schema_name.table_name. For example, use EXEC master.dbo.Your_Proc_name instead of EXEC Your_Proc_name This is a very common mistake, which causes an extra trip to the procedure cache to get the execution plan for execution. Also try to use the schema name while creating a procedure. Like: CREATE PROCEDURE dbo.Your_Proc_name instead of CREATE PROCEDURE Your_Proc_name

6.SET NOCOUNT OFF - This returns the message that shows number of rows affected by SQL statement. This can cause extra network traffic and can have some serious impact on performance when the procedure is called frequently.

7. The sp_ prefix - Don't use the "sp_" prefix in a stored procedure name as the "sp_" prefix is reserved for system stored procedures. Any stored procedure that has the "sp_" prefix will cause an extra lookup in the MASTER database If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will never get executed.

8. sp_executeSQL and the KEEPFIXED PLAN options - Both sp_executesql and the KEEPFIXED PLAN option avoid the recompilation of a stored procedure. If you want to provide parameterized dynamic SQL, then go for sp_executesql instead of EXEC(proc_name). Here the execution plan for the procedure is stored with the variable name in cache memory. When the variable values are supplied, then the values are simply mapped to the query, hence no need for a recompilation.
Use the OPTION KEEPFIXED PLAN hint while selecting records from temporary tables. If the query contains this hint, then its plan is not recompiled. For more information about procedure recompilation, please go through the following article: http://technet.microsoft.com/en-us/library/cc966425.aspx

CREATE PROCEDURE my_proc

AS

  CREATE TABLE #t (a int )

  SELECT * FROM #t

  INSERT #t

    SELECT * FROM retest

   

  SELECT COUNT(*)

    FROM #t

    WHERE a = 37

    OPTION (KEEPFIXED PLAN)

GO

As an example of sp_executesql, we can write:
sp_executesql N'SELECT * FROM mydb.dbo.emp where empid = @eid', N'@eid int', @eid=40

9. SELECT vs SET - A single SELECT statement can assign values to different variables and is much faster than multiple SET statements assigning values to multiple different variables.

SELECT @Var1 = @Var1 + 1,

       @Var2 = @Var2 - 1

instead of

SET @Var1 = @Var1 + 1

SET @Var2 = @Var2 - 1

10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance.
=, >, <, >=, <=, <>, !=, !>, !<

for details, refer to the article: http://msdn.microsoft.com/en-us/library/ms190276.aspx

11. More WHERE clause hints - Avoid unnecessary conditions in the WHERE Clause. You can easily use the first example instead of the second one. The second example uses one extra OR condition which can be avoided using the first example.

SELECT emp_name FROM table_name WHERE LOWER(emp_name) = 'edu'

SELECT emp_name FROM table_name WHERE emp_name = 'EDU' OR emp_name = 'edu'

Also, try to avoid IN. While checking the existence of some values, then use EXISTS instead of IN. Because IN counts the NULL values also, hence slower than EXISTS. Since EXISTS returns Boolean(Yes/No) but IN returns all values hence result set for IN is heavier than EXISTS.

SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)

SELECT * FROM employee WHERE NOT EXISTS (SELECT emp_no FROM emp_detail)

 
12. CAST and CONVERT - Try to use CAST instead of CONVERT. CAST is ANSI-92 standard but CONVERT works in MS SQL server only. Also, Convert may be deprecated in future MS SQL releases. It is better to use CONVERT only when you need to format the DATETIME datatype with the style option. CAST cannot do this.

13. Avoid DISTINCT and ORDER BY - If you don't need the DISTINCT/ORDER BY clause, then try to avoid so. Unnecessary DISTINCT or ORDER BY clauses cause extra work for the database engine. Hence making performance slower.

14. Avoid using cursors - Try to use temporary table/table variables with identity column and then iterate all the tables using WHILE loop and a looping counter, which will map with the identity column. For details, refer my previous article. http://www.sqlservercentral.com/articles/Stored+Procedures/64523/

15.SELECT statements - Try to use only the required number of columns in the SELECT clause instead of using *. Using * returns all columns, which unnecessarily create a fat recordset.

16.Subquery vs JOINs - This is a famous debate in many online forums. In fact most sub queries can be expressed as an equivalent form of JOIN. I have a good rule of thumb: subquery is faster when we have to retrieve data from large number of tables because it becomes tedious to join more tables. JOIN is faster to retrieve data from database when we have less number of tables. But try to avoid correlated sub queries because it makes the query much slower.

17. CREATE TABLE vs. SELECT INTO - Select * INTO works fine for small tables, but when dealing with large record sets or long-running queries, it creates locks on the system objects within the tempdb database. As a result, other queries and procedures that need to create objects within the tempdb database will have to wait for the long-running query to complete. This is because when an object is created, an exclusive lock is taken against the sysobjects, syscolumns, sysindexes tables.
Also, SELECT INTO not only copies data but also it copies the table structure, hence it performs slower.

18. Try to use table variables instead of Temporary Tables - Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution.
If the result set is not containing a huge number of records then you should stick to table variable, otherwise temp table has its advantages. There is a misconception that temp tables always use the tembdb database but table variable do not. Table variables also use tempdb after a certain size. For more information refer to the following article : http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

19.Use proper indexes - You can use the help of the data tuning advisor, but it does not gives the proper result all the time. Index scans are much faster than table scans. So identify the table scans from the execution plans. But when a table returns smaller rows, then it is better to use a table scan. You can see an excellent article on execution plans by G Vijayakumara at the following link:http://www.sqlservercentral.com/articles/Administration/executionplans/1345/

20. Use Profiler - The cachemiss event class indicates that the stored procedure is not in the cache. If the SP:Cachemiss class occurs frequently, it can indicate that more memory should be available to MS SQL server, thereby increasing the size of procedure cache. The cachehit event class indicates that a stored procedure is in the cache.
Last but not the least, again I am repeating the same advice from my previous article. Keep these guidelines in your mind, but don't hesitate to break them if needed. After all, performance is the ultimate goal. If violating the general rule gives you good performance (it may happen based on your database) then don't stick with the guidelines.

[vb6] How to create list of same items?

Suppose you want this result:

    abc,abc,abc,abc,abc

But you don't want to create it in the Do or For loop. So you can use the following syntax:

    Mid(Replace(5, "~"), "~", ",abc"), 2)

[vb6] How to hide the grid from Report Designers?

Open the source code (file with .Dsr extension) using the Notepad, then update the value of _Setting property under the Designers module become 28.

[link] Cek UserName di Banyak Situs Utama

Buat koleksi aja:

1. http://namechk.com/
2. http://checkusernames.com/

[story] Cerita-cerita Kecil Seputar Google Yang Jarang Diketahui

1. Larry Page dan Sergey Brin awalnya bermusuhan.
Sebagai orang yang sama-sama cerdas, LP dan SB adalah dua orang yang sering berdebat satu sama lain meskipun untuk urusan kecil seperti siapa yang harus menutup pintu atau siapa yang lebih dulu membaca koran. Pertengkaran kecil seperti ini sering menggangu teman-teman seasrama mereka. Ternyata mereka bertengkar bukan karena membenci satu sama lain , melainkan mereka terbiasa untuk kompetitif satu sama lain. Toh pertengkaran kecil mereka selalu diakhiri tertawa bersama :)

2. Sergey Brin lebih genit daripada Larry Page
Kalau urusan wanita, Larry Page memang lebih konservatif dibanding Sergey Brin.

3. Pendiri Google pernah frustasi
Setelah berturut-turut ditolak AltaVista, Excite, dan (terakhir) Yahoo, LP dan SB sama-sama frustasi. Buat mereka Teknologi Page Rank Google seperti layu sebelum berkembang. Mereka tidak pernah bermimpi Google akan bisa sebesar sekarang. Saat itu pikiran mereka cuma satu: menyelesaikan Phd mereka secepatnya dan menyempurnakan Google sambil jalan. Bahkan sangking frustasinya, pernah Google tidak dilirik selama 1 minggu.

4. LP dan SB menggojlok Eric Schmidt ketika pertama berkantor di Google.
LP dan SB bersekongkol mengerjai Eric dengan cara membagi-bagikan secara gratis kartu kredit perusahaan ke para karyawan. Tentu saja Eric pusing, dan dapat pekerjaan tambahan: menarik kembali kartu kredit tersebut dari para karyawan yang juga berusaha menyembunyikan. Di lain hari Eric dikerjai dengan tiba-tiba ada telepon umum di dalam ruang kerjanya. Juga Kulkas dan kursi pijat. Jika Eric tidak punya selera humor, bisa-bisa ngamuk dia. Tapi dia sadar sedang dikerjai dua orang pendiri. Jadi sing waras ngalah….

5. Cerita Palsu untuk nama perusahaan
Nama asli Google dipublikasikan oleh perusahaan sebagai cerita salah tulis dari kata Googol yang artinya angka 1 diikuti 100 angka nol dibelakangnya menjadi Google. Padahal, menurut bocoran salah seorang karyawan awal Google, sebenarnya kata Google berasal dari kata “Go Girl !”, dimana Sergey Brin suatu saat memperoleh ide tersebut ketika sedang menonton pertandingan olahraga dengan para Cherleader yang meneriakkan kata Go Girl ! Go Girl ! (gogel) Cerita meragukan ini didukung 2 fakta: Sergey Brin suka menonton para Cherleader, dan kedua: Google mensponsori para Cherleader dengan memasang tulisan Google di kaos para Cherleader. Mengapa perlu dibuat cerita palsu yang masuk akal, karena konotasi GoGirl yang negatif tidak sesuai dengan citra perusahaan jika suatu saat ingin Go Public.

6. Larry Page ingin membuat Alat Transportasi otomatis, Sergey Brin ingin membuat koloni di Mars
Ini di kemudian hari memang benar-benar diwujudkan. Setidaknya, berusaha diwujudkan. Ide Larry dimulai dengan project mobil listrik, ide Sergey diwujudkan dengan Project Virgle.

7. Tanda ~ yang lebih powerfull daripada tanda * yang jarang dipakai
Dahulu di jaman DOS masih jaya, tanda * sangat berguna untuk menemukan semua file yang bernama tertentu. Sekarang di jaman DOS-nya Internet (Google) maka tanda ~ menggantikan fungsi wildcard dengan sedikir perbedaan penting: lebih cerdas. Tanda ~ jika diketikkan didepan kata tertentu, berarti kita menginginkan Google untuk mencari semua data yang berkaitan dengan keyword tertentu dan sinonimnya. Misalnya ~motor, maka semua halaman yang mengandung kata motor dan sinonimnya, seperti motor show, sepeda motor, atau motor boat akan ditampilkan.

Sumber: http://tech19.wordpress.com/2008/10/27/cerita-cerita-kecil-seputar-google-yang-jarang-diketahui/

[t-sql] What is the difference between SET and SELECT when assigning values to variables?

Traditionally, SQL Server database developers are accustomed to using SELECT for assigning values to variables. This was fine and a perfectly valid practice right until SQL Server 6.5. Microsoft released SQL Server 7.0 in 1999. SQL Server 7.0 introduced the new SET statement for initializing and assigning values to variables. SQL Server 7.0 Books Online also stated: "It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable."

This caused some confusion in the database developer community, as Microsoft never mentioned, why SET is recommended over SELECT for assigning values to variables. In this article, I will highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT.

If you are completely new to T-SQL, then the following examples give you an idea of what I am talking about:

    /* Declaring variables */
    DECLARE @Variable1 AS int, @Variable2 AS int

    /* Setting @Variable1 to a value of 1 using SELECT */
    SELECT @Variable1 = 1 

    /* Setting @Variable2 to a value of 2 using SET */
    SET @Variable2 = 2

Now coming to the differences between SET and SELECT! Are standards important to you? If your answer is 'yes', then you should be using SET. This is because, SET is the ANSI standard way of assigning values to variables, and SELECT is not.

Another fundamental difference between SET and SELECT is that, you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. Here's how:

    /* Declaring variables */
    DECLARE @Variable1 AS int, @Variable2 AS int

    /* Initializing two variables at once */
    SELECT @Variable1 = 1, @Variable2 = 2

    /* The same can be done using SET, but two SET statements are needed */
    SET @Variable1 = 1
    SET @Variable2 = 2

So far so good. But if you ever wrote error handling code in T-SQL, you most probably are aware that, the system variables @@ERROR and @@ROWCOUNT must be captured in one statement, immediately after a data manipulation (DML) statement like INSERT, UPDATE, DELETE, or else, these system variables get reset to 0. So, if you want to stick to the standards and use SET in this scenario, you are out of luck. The following example demonstrates the problem:

    DECLARE @Error int, @RowCount int
    SELECT price/0 FROM dbo.titles
    SET @RowCount = @@ROWCOUNT
    SET @Error = @@ERROR
    SELECT @Error AS Error
    GO

If you run the above piece of code in pubs database, the value of @@ERROR system variable will be displayed as 0, even though the 'division by zero' resulted in error 8134. So, in this particular scenario, forget about standards and use SELECT, as shown below:

    DECLARE @Error int, @RowCount int
    SELECT price/0 FROM dbo.titles
    SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR
    SELECT @Error AS Error

But if you insist on using SET even in this scenario, there's always a way out. Here's one example, though not readable and recommended:

    DECLARE @ErrorAndRowcount AS varchar(25), @Error int, @RowCount int
    SELECT price/0 FROM dbo.titles

    /* Capturing @@ERROR and @@ROWCOUNT into a dot separated string */
    SET @ErrorAndRowcount = CAST(@@ERROR AS varchar(12)) + '.' + CAST(@@ROWCOUNT AS varchar(12))

    /* One way to separate the string into error and rowcount variables */
    SET @Error = CAST(PARSENAME(@ErrorAndRowcount, 2) AS int)
    SET @RowCount = CAST(PARSENAME(@ErrorAndRowcount, 1) AS int)
    SELECT @Error AS Error, @RowCount AS Row_Count

    /* Another way of splitting the string into error and rowcount variables */
    SET @Error = CAST(LEFT(@ErrorAndRowcount, CHARINDEX('.', @ErrorAndRowcount)-1) AS int)
    SET @RowCount = CAST(RIGHT(@ErrorAndRowcount, CHARINDEX('.', REVERSE(@ErrorAndRowcount))-1) AS int)
    SELECT @Error AS Error, @RowCount AS Row_Count
    GO

Moving on to other differences between SET and SELECT: When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row. As a result, bugs in your code could go unnoticed with SELECT, and this type of bugs are hard to track down too. Here is an example:

    /* Consider the following table with two rows */
    SET NOCOUNT ON
    CREATE TABLE #Test (i int, j varchar(10))
    INSERT INTO #Test (i, j) VALUES (1, 'First Row')
    INSERT INTO #Test (i, j) VALUES (1, 'Second Row')
    GO

    /* Following SELECT will return two rows, but the variable
       gets its value from one of those rows, without an error.
       This may not be what you were expecting. Since no error is returned,
       you will never know that two rows existed for the condition, WHERE i = 1 */
    DECLARE @j varchar(10)
    SELECT @j = j FROM #Test WHERE i = 1
    SELECT @j
    GO

    /* If you rewrite the same query, but use SET instead,
       for variable initialization, you will see the following error */
    DECLARE @j varchar(10)
    SET @j = (SELECT j FROM #Test WHERE i = 1)
    SELECT @j

    Server: Msg 512, Level 16, State 1, Line -1074284106
    Subquery returned more than 1 value.
    This is not permitted when the subquery follows =, !=, <, <= , >, >= or
    when the subquery is used as an expression.

Based on the above results, when using a query to populate variables, I suggest you always use SET, if you want to be sure that only one row is returned. If you hate SET for some reason, you could get the same behavior of SET, using SELECT, as shown below:

    DECLARE @j varchar(10)
    SELECT @j = (SELECT j FROM #Test WHERE i = 1)
    SELECT @j

Here is another difference with respect to assigning values based on a query, especially when the query doesn't return any rows. Run the following example in the pubs database, and you will see what I mean:

    /* Returns NULL */
    DECLARE @Title varchar(80)
    SET @Title = 'Not Found'

    SET @Title = (SELECT title
                    FROM dbo.titles
                    WHERE title_id = 'InvalitTitleID')

    SELECT @Title
    GO

    /* Returns the string literal 'Not Found' */
    DECLARE @Title varchar(80)
    SET @Title = 'Not Found'

    SELECT @Title = title
      FROM dbo.titles
      WHERE title_id = 'InvalitTitleID'

    SELECT @Title
    GO

Last, but not the least! Is there any performance difference between SET and SELECT? Is one faster or slower than the other? This is one question most database developers and DBAs are not so sure about. So I decided to conduct a test and come up with some conclusive results. I picked a development SQL Server for this test. Closed all applications, and stopped all unnecessary services running on that machine. Stopped SQL Server agent service, to make sure, no jobs kick in during the performance test. Also, unplugged the machine from the network. So, this is one isolated SQL Server box, with nothing but just SQL Server service running on it. Then I created a test script, that continuously assigns values to variables inside a loop (of configurable iterations) using SET, SELECT and measures the time taken to complete each loop.

Here are the results:

There is hardly any performance difference between SET and SELECT, when initializing/assigning values to variables. BUT, I made one startling discovery. As you all know, one single SELECT statement can be used to assign values to multiple variables. This very feature of SELECT makes it a winner over SET, when assigning values to multiple variables. A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables. In this scenario, using a SELECT is at least twice as fast, compared to SET. So, the conclusion is, if you have a loop in your stored procedure that manipulates the values of several variables, and if you want to squeeze as much performance as possible out of this loop, then do all variable manipulations in one single SELECT statement (or group the related variables into few SELECT statements) as show below:

    SELECT @TestVar1 = @TestVar1 + 1, @TestVar2 = @TestVar2 - 1, @CTR = @CTR + 1

I ran this test on SQL Server versions 7.0, 2000 and SQL Server 2005 (Yukon), and the results were consistent. I even tested this on single and multi-processor boxes, and the results were the same. If you want to test this yourself, feel free to use the following test script. A word of caution though, do not run this script on a production SQL Server, as it could lead to 100% CPU utilization for the duration of the test. Also, if you think the test is taking too long, reduce the value of the variable @TimesToLoop2, to reduce the number of iterations. At the end of the test, the script displays how much time (in Seconds) it took to assign values to variables using SET, SELECT and SELECT with multiple assignments. Here's the script:

    DECLARE @Test1 int,  @Test2 int, @Test3 int, @TestVar1 int, @TestVar2 int
    DECLARE @Loop int, @Start datetime, @CTR int, @TimesToLoop1 int, @TimesToLoop2 int

    SET @Test1 = 0
    SET @Test2 = 0
    SET @Test3 = 0
    SET @Loop = 0
    SET @TestVar2 = 0
    SET @TimesToLoop1 = 10
    SET @TimesToLoop2 = 50000
    WHILE @Loop < @TimesToLoop1
    BEGIN
      SET @Start = CURRENT_TIMESTAMP
      SET @CTR = 0

      /* Testing the performance of SET */
      WHILE @CTR < @TimesToLoop2
      BEGIN
        SET @TestVar1 = 1
        SET @TestVar2 = @TestVar2 - @TestVar1
        SET @CTR = @CTR + 1
      END

      SET @Loop = @Loop + 1
      SET @Test1 = @Test1 + DATEDIFF(ms, @Start, CURRENT_TIMESTAMP)
    END

    SET @Loop = 0
    SET @TestVar2 = 0
    WHILE @Loop < @TimesToLoop1
    BEGIN
      SELECT @Start = CURRENT_TIMESTAMP
      SELECT @CTR = 0

      /* Testing the performance of SELECT */
      WHILE @CTR < @TimesToLoop2
      BEGIN
        SELECT @TestVar1 = 1
        SELECT @TestVar2 = @TestVar2 - @TestVar1
        SELECT @CTR = @CTR + 1
      END

      SELECT @Loop = @Loop + 1
      SELECT @Test2 = @Test2 + DATEDIFF(ms, @Start, CURRENT_TIMESTAMP)
    END

    SET @Loop = 0
    SET @TestVar2 = 0
    WHILE @Loop < @TimesToLoop1
    BEGIN
      SELECT @Start = CURRENT_TIMESTAMP, @CTR = 0

      /* Testing the performance of SELECT with multiple variable assignments */
      WHILE @CTR < @TimesToLoop2
      BEGIN
        SELECT @TestVar1 = 1, @TestVar2 = @TestVar2 - @TestVar1, @CTR = @CTR + 1
      END

      SELECT @Loop = @Loop + 1, @Test3 = @Test3 + DATEDIFF(ms, @Start, CURRENT_TIMESTAMP)
    END

    SELECT
      (@Test1/CAST(@TimesToLoop1 AS decimal(7,2)))/1000.00 AS [SET],
      (@Test2/CAST(@TimesToLoop1 AS decimal(7,2)))/1000.00 AS [SELECT],
      (@Test3/CAST(@TimesToLoop1 AS decimal(7,2)))/1000.00 AS [SELECT with Multiple Assignments]

[t-sql] Catch return SELECT value of Store Procedure

I have a store procedure which use the SELECT statement to return the recordset to caller.
How do I catch the data into variable or temp-table? The value will be used for next process.

Answer: Use OpenQuery() to Local Link Server (you must create the link server first)

Sample store procedure which return the recordset:

    CREATE PROCEDURE usp_test
    (  @pInp varchar(10)
    )
    AS
    BEGIN
      SET NOCOUNT ON;
      SELECT 'aaaa' AS ret_val
    END
    GO

To create link server for local server:

    IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'loopback')
      EXEC sp_dropserver 'loopback'
    GO
    EXEC sp_addlinkedserver
           @server = N'loopback',
           @srvproduct = N'',
           @provider = N'SQLOLEDB',
           @datasrc = @@servername
    GO

Procedure to catch the value into variable:

    DECLARE @cReturn varchar(1000)
    SELECT @cReturn = ret_val FROM openquery(loopback, 'mydb..usp_test ''input''')
    PRINT @cReturn

Suppose you have dynamic input parameter into store procedure usp_test() then you must use sp_executesql to execute the command in string.

    DECLARE @cReturn varchar(1000),
            @cSql    varchar(1000),
            @cInput  varchar(1000),
            @nExec   nvarchar(4000)

    SET @cInput = '''''a'''''
    SET @cSql = '''ttsdb..usp_test ' + @cInput + ''''

    SET @nExec = 'SELECT @cReturn = ret_val FROM openquery(loopback, ' + @cSql + ')'
    EXEC sp_executesql
           @nExec,
           N'@cReturn varchar(100) output',
           @cReturn output

    PRINT @cReturn

Have a lot of fun...

[t-sql] Can I create an index on a BIT column?

Try this. In Enterprise Manager, right-click the Tables view, and select New Table. Create a BIT column named "MyBitColumn", and then click on the "Manage Indexes / Keys..." button to create a new index:


When you do this, you are offered a "New" button. Click it, and under "Column name" try to select the "MyBitColumn" column. It's not there! Why? Because in this interface, Enterprise Manager does not offer you the ability to create an index on a BIT column. When you try to type the name yourself, you are rewarded with an annoying barrage of error dialogs. Each time you click "No" the first dialog reappears, and every time you try to click OK on the second, the same thing happens.


So, can you create an index on a BIT column? Of course. You can even still do it in Enterprise Manager. Save your table, and then right-click it in the Tables view and choose All Tasks > Manage Indexes... give your index a name, check the "MyBitColumn" column, and hit OK. Voila!


Another way is to create the index using T-SQL, which is the preferred way of generating DDL anyway:

    CREATE TABLE dbo.blat
    (
        MyBitColumn BIT
    )
    GO

    CREATE INDEX MyBitIndex ON dbo.blat(MyBitColumn)
    GO

Now, the question is, do you really WANT an index on a BIT column? We're going to run some experiments, but in general, it is highly unlikely that you will get much use out of such an index. The exception is when the data is heavily weighted towards, say, 1 (e.g. 95-99% of the table), and you are searching for 0. Or vice-versa.

What I'd like to test is the effect on execution time and the execution plan if you run different queries against large tables with a BIT column whose values are evenly distributed, or weighted heavily (in this case 97% - 3%), and in both cases, compare clustered to nonclustered to no index.

So, let's create six different tables in their own database:

    CREATE DATABASE Splunge
    GO

    USE Splunge
    GO

    -- 50/50, no index
    CREATE TABLE dbo.Test1
    (
      myBit BIT NOT NULL
    )

    -- 50/50, nonclustered index
    CREATE TABLE dbo.Test2
    (
      myBit BIT NOT NULL
    )

    CREATE INDEX bitIndex ON dbo.Test2(myBit)

    -- 50/50, clustered index
    CREATE TABLE dbo.Test3
    (
      myBit BIT NOT NULL
    )

    CREATE CLUSTERED INDEX bitIndex ON dbo.Test3(myBit)

    -- 97/3, no index
    CREATE TABLE dbo.Test4
    (
      myBit BIT NOT NULL
    )

    -- 97/3, nonclustered index
    CREATE TABLE dbo.Test5
    (
      myBit BIT NOT NULL
    )

    CREATE INDEX bitIndex ON dbo.Test5(myBit)

    -- 97/3, clustered index
    CREATE TABLE dbo.Test6
    (
      myBit BIT NOT NULL
    )

    CREATE CLUSTERED INDEX bitIndex ON dbo.Test6(myBit

And let's populate each with 100,000 rows, the first three will have 50,000 of each value (0,1), and the second three will have 97,000 0's and 3,000 1's.

    DECLARE 
      @i INT, 
      @ff BIT, -- 50/50 flag 
      @nf BIT -- 97/3 flag 
     
    SELECT 
      @i = 1, 
      @ff = 0, 
      @nf = 0 
   
    WHILE @i <= 100000 
    BEGIN 
      IF @i > 50000 
        SET @ff = 1 
     
      IF @i > 97000 
        SET @nf = 1 
   
      INSERT dbo.Test1(myBit) SELECT @ff 
      INSERT dbo.Test2(myBit) SELECT @ff 
      INSERT dbo.Test3(myBit) SELECT @ff 
   
      INSERT dbo.Test4(myBit) SELECT @nf 
      INSERT dbo.Test5(myBit) SELECT @nf 
      INSERT dbo.Test6(myBit) SELECT @nf 
   
      SET @i = @i + 1 
    END

On my system, this took roughly seven minutes. Your mileage may vary.

So now that we have the data in there, let's run the following sets of queries.

    SELECT COUNT(*) FROM dbo.Test1 
    SELECT COUNT(*) FROM dbo.Test2 
    SELECT COUNT(*) FROM dbo.Test3 
    SELECT COUNT(*) FROM dbo.Test4 
    SELECT COUNT(*) FROM dbo.Test5 
    SELECT COUNT(*) FROM dbo.Test6 

    SELECT COUNT(*) FROM dbo.Test1 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test1 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test2 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test2 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test3 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test3 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test4 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test4 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test5 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test5 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test6 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test6 WHERE MyBit=1 

If you observe the execution plan and statistics, you will see that those with the table scan (Test1 and Test4) require the least amount of reads and percentage of work.

However, these results change ever so slightly if you are performing grouping and aggregates in the same query:

    SELECT MyBit, COUNT(*) FROM dbo.Test1 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test2 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test3 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test4 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test5 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test6 GROUP BY MyBit 

Here we see that the clustered index has a slight edge in query cost, but slightly higher I/O cost:

And in SQL Server 2005, the clustered index scan has an even greater advantage:

So, the answer to this one is yes, you can create a clustered index on a BIT column.

However, as for whether you SHOULD—as with so many other choices—it depends.

Jeff Gray correctly points out that the optimizer will do a little bit better if you explicitly tell it that you are dealing with a BIT (since the engine assumes INT), e.g.:

    WHERE MyBit = CONVERT(BIT, 0) 
    --or 
    WHERE MyBit = CAST(0 AS BIT)

Though that can come with a trade-off as well; namely, remembering to explicitly convert values on every statement. Whereas, if you use a CHAR(1) constrained to 'T'/'F', for example, no explicit conversion is necessary for the proper index to be used.

---

Now clean up, because you probably don't want this data to hang around:

    DROP TABLE 
      dbo.Test1, dbo.Test2, dbo.Test3, 
      dbo.Test4, dbo.Test5, dbo.Test6 

Or, just:

    DROP DATABASE Splunge

[vb6] Get object reference by its name in a form

In some cases you might want to activate an object in a form by given its name. To get it, you can use the controls collection of the form object:

  Private Sub Form_Load()
    MsgBox GetCtlByName(Me, "Label1").Caption
  End Sub

  Private Function GetCtlByName( _
          ByVal pForm As Form, _
          ByVal pName$, _
          Optional ByVal pIndex% = -1) As Control

    On Error GoTo PROC_ERR

    If pIndex < 0 Then
      Set GetCtlByName = pForm.Controls(pName)
    Else
      Set GetCtlByName = pForm.Controls(pName, pIndex)
      If pIndex <> GetCtlObj.Index Then GoTo PROC_ERR
    End If

    Exit Function

  PROC_ERR:
    Set GetCtlByName = Nothing
    MsgBox "Error in procedure GetCtlByName for item name = '" & pName & "'.", vbExclamation
  End Function 'GetCtlByName

[mssql] A Deadlock Occurence and Resolution

Introduction

An upgrade was performed at my company recently to the application code on the SQL Server, which involved basically the addition of columns to few tables. After the upgrade, the customer started facing severe deadlocks. Around 8 out of 10 transactions were getting deadlocked and were failing.

Steps Taken in resolving the deadlocks:

I will walk through the steps we took to determine what was causing the deadlock and resolve the issue.

1. First I enabled the trace flag 1204 on the server to capture the deadlock details in the error log with the command listed below:

  DBCC traceon(1204,-1)

Next I confirmed that the trace was enabled with the below command:

  DBCC Tracestatus(-1)

The output shown below indicated that the trace flag 1204 was enabled.

  TraceFlag Status Global Session
  --------- ------ ------ -------
  1204      1      1      0

2. I then gathered the deadlock details from the SQL error log with the command 'xp_readerrorlog'. One instance of deadlock occurred was given below:

  Deadlock encountered .... Printing deadlock information
  Wait-for graph
  Node:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2
  Grant List 1::
  Grant List 2::
  Owner:0x27c007e0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:84 ECID:0
  SPID: 84 ECID: 0 Statement Type: UPDATE Line #: 11
  Input Buf: RPC Event: dbo.Example_Stored_proc
  Requested By: 0
  ResType:LockOwner Stype:'OR' Mode: IX SPID:78 ECID:0 Ec:(0x44AA55F0) Value:0x3affcd00 Cost:(0/0)
  Node:2 PAG: 9:1:18134 CleanCnt:2 Mode: SIU Flags: 0x2
  Grant List 1::
  Owner:0x28e6f060 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:78 ECID:0
  SPID: 78 ECID: 0 Statement Type: UPDATE Line #: 11
  Input Buf: RPC Event: dbo. Example_Stored_proc
  Grant List 2::
  Requested By:
  ResType:LockOwner Stype:'OR' Mode: IX SPID:84 ECID:0 Ec:(0x239955F0) Value:0x3affc940 Cost:(0/0)
  Victim Resource Owner:
  ResType:LockOwner Stype:'OR' Mode: IX SPID:84 ECID:0 Ec:(0x239955F0) Value:0x3affc940 Cost:(0/0)

3. From the captured details, it was found that the deadlocks were occurring due to UPDATE statements in the stored procedure 'Example_stored_proc' while trying to get an Intent exclusive lock on the pages.

4. My next step was to read the contents of the stored procedure 'Example_stored_proc' through sp_helptext.

5. The procedure was calling around 5 procedures and each of those were calling 4 more procedures.

6. As going through all the procedures to identify the deadlock statements was difficult, I was able to get to the correct update statement through the Page numbers in the deadlock details captured earlier. The deadlock details captured had the page numbers that are involved in the deadlock as shown below:

  Node:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2

7. I gathered the Page numbers that were being deadlocked and used the undocumented DBCC command 'DBCC Page' to get the table name associated with the page. The details of the command syntax are in this link:http://support.microsoft.com/kb/83065

Though the article refers to earlier SQL versions, the command is still valid in SQL Server 2000 and provides the page information.

I then used this command to get the page details:

  DBCC page(9,1,18061,0)

The parameters were taken from the deadlock details shown above. The output obtained was pasted below. It contains the object name associated with that page. Under the PAGE HEADER, the value m_objId gives the table name associated with the deadlock:

  PAGE: (1:18061)
  ---------------
  BUFFER:
  -------
  BUF @0x01665900
  ---------------
  bpage = 0x1DF58000 bhash = 0x00000000 bpageno = (1:18061)
  bdbid = 9 breferences = 1 bstat = 0xb
  bspin = 0 bnext = 0x00000000
  PAGE HEADER:
  ------------
  Page @0x1DF58000
  ----------------
  m_pageId = (1:18061) m_headerVersion = 1 m_type = 1
  m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
  m_objId = 1013578649 m_indexId = 0 m_prevPage = (0:0)
  m_nextPage = (0:0) pminlen = 52 m_slotCnt = 82
  m_freeCnt = 3075 m_freeData = 5009 m_reservedCnt = 0
  m_lsn = (2689:87968:2) m_xactReserved = 0 m_xdesId = (0:0)
  m_ghostRecCnt = 0 m_tornBits = 0
  .................

Once the object id was obtained, table name was found out with the command:

  Use DBNAME
  Select object_name(OBJECT_ID)

8. I then reviewed the code of the dependent stored procedures on the table and found one update procedure among the list.

9. The code of the update procedure was similar to that shown below:

  UPDATE {table}
    SET {column1} = @C1,
        {column2} = @C2,
        {column2} = @C3,
    WHERE ID = @id

10. I then verified the indexes on the table and found that the table did not have an index on the ID column. This was forcing the update to perform a table scan causing it to take an INTENT exclusive lock on all the pages thereby causing the deadlock in the process.

11. As there was only one non-clustered index existing on that table, I created a clustered index on the table on the ID column with this command:

  IF NOT EXISTS (SELECT name FROM sysindexes
                   WHERE name = 'IX_Clustered_index_column')
  CREATE CLUSTERED INDEX IX_Clustered_index_column
    ON dbo.TABLE(ID) ON [PRIMARY]

12. After the index creation, the intent exclusive lock was not requested on all the pages of the update statement and as a result the deadlocks got resolved.

Conclusion

On further discussion with the application team about the reason for the deadlock occurrence after the upgrade performed by them, I came to know the following:

* Before the upgrade, for every insertion, one record was inserted or updated in the table identified in the deadlock
* As part of the upgrade, the application team performed modifications to the stored procedure code such that every insertion may cause up to 10 updates in the table.

This confirmed the reason why the deadlocks started occurring after the upgrade. As the table access increased heavily due to table scans, transactions took a longer time for each update thereby causing the deadlock scenario.

[vb6] Shuting down/Unload form in Form_Load event

You are doing some validation code in the Form_Load event. If that validation
code fails, then you do not want to show the form. If you call "Unload ME", you will get an error in the form that called ".Show" of the form loading, saying that the form is already unloaded.

  Private mbCancelForm As Boolean

  Private Sub Form_Activate()
    If mbCancelForm = True Then Unload Me
  End Sub

  Private Sub Form_Load()
    mbCancelForm = False

    If {SomeCondition} = True Then
      MsgBox "False condition, unable to load the form", vbExclamation + vbOKOnly
      mbCancelForm = True
    End If
  End Sub

[mssql] Server Side Paging

The script below can be used to fetch the record according to certain page number and size. The syntax is:

  SELECT {field_list} FROM
    (SELECT TOP {page_size} {field_list} FROM
      (SELECT TOP {page_size * page_number} {field_list}
        FROM {table} (NOLOCK)
        WHERE {where_criteria}
        ORDER BY {field_to_order} {order_type}
      ) AS SortedData
      ORDER BY {field_to_order} {reverse_order_type}
    ) AS PagedData
    ORDER BY {field_to_order} {order_type}

Let's say we would like to fetch the data from table customer for page number 10 where the page size is 20, which descending order by username.

  SELECT username, fullname FROM
    (SELECT TOP 20 username, fullname FROM
      (SELECT TOP 200 username, fullname
        FROM customer (NOLOCK)
        WHERE fullname like '%ali%'
        ORDER BY username DESC
      ) AS SortedData
      ORDER BY username ASC
    ) AS PagedData
    ORDER BY main_username DESC

[vb6] Convert formatted string into numeric

The function below is used to convert formatted string that produced by Format() function into numeric value (Double datatype). This function will remove all non-numeric string except minus and decimal delimiter.

Create new module and paste the source below inside your module file (.bas).

  '****************************************************************************
  ' Name        : StrToNumeric
  ' Author      : Chandra Gunawan
  ' Date        : 22-Jan-2009
  ' Description : Convert formatted string into numeric
  '****************************************************************************
  Public Function StrToNumeric( _
         ByVal pString As String, _
         Optional ByVal pDecimalDelimiter As String) As Double
         
    Dim I%, J%, strVal$
    
    If pDecimalDelimiter <> "." And pDecimalDelimiter <> "," Then
      pDecimalDelimiter = Trim(Format(0, "#.#"))
    End If
    
    'Remove all non-numeric except dot and comma
    For I = 1 To Len(pString)
      If InStr(1, "-0123456789" & pDecimalDelimiter, Mid(pString, I, 1)) > 0 Then
        strVal = strVal & Mid(pString, I, 1)
      End If
    Next
    
    'Remove unused delimiter
    J = Len(strVal) - Len(Replace(strVal, pDecimalDelimiter, ""))
    If J > 1 Then
      strVal = Replace(strVal, pDecimalDelimiter, "", 1, J - 1)
    End If
    
    'Replace to proper decimal delimiter
    If strVal = "" Or strVal = "." Then strVal = "0"
    StrToNumeric = CDbl(Replace(strVal, pDecimalDelimiter, "."))
  End Function  'StrToNumeric

[vb6] Numeric input mask for Textbox

Place the 2 Textbox objects in your form with name Textbox1 and Textbox2.
And then paste the code below into your .frm file.

  Private Sub Text1_KeyPress(KeyAscii As Integer)
    Call KeyPressNumeric(KeyAscii)
  End Sub

  Private Sub Text2_KeyPress(KeyAscii As Integer)
    Call KeyPressNumeric(KeyAscii, True, Text2.Text, True, Text2.SelStart)
  End Sub

  Private Sub Text1_GotFocus()
    Text1.Text = Replace(Replace(Text1.Text, ",", ""), ".", "")
  End Sub

  Private Sub Text1_LostFocus()
    Text1.Text = Format(Text1.Text, "###,###,##0")
  End Sub

  Private Sub Text2_GotFocus()
    Text2.Text = Replace(Replace(Text2.Text, ",", ""), ".", "")
  End Sub

  Private Sub Text2_LostFocus()
    Text2.Text = Format(Text2.Text, "###,###,##0.00")
  End Sub

Create new module and place the code below inside your module file (.bas).

  '****************************************************************************
  ' Name        : KeyPressNumeric
  ' Author      : Chandra Gunawan
  ' Date        : 22-Jan-2009
  ' Description : For use with Textbox_KeyPress events
  '               Allows only numeric entries
  ' Note        : If you set pAllowDecimal = True
  '                 then you must set pTextBox_Text = TextBox.Text
  '               If you set pAllowMinus = True
  '                 then you must set pTextbox_SelStart = TextBox.SelStart
  '*****************************************************************************
  Public Sub KeyPressNumeric( _
         pKeyAscii As Integer, _
         Optional pAllowDecimal As Boolean = False, _
         Optional pTextBox_Text As String, _
         Optional pAllowMinus As Boolean = False, _
         Optional pTextbox_SelStart As Long = 0)

    Dim strDecDlm As String

    'Note: 8=backspace, 13=enter, 27=escape
    strDecDlm = Trim(Format(0, "#.#"))
    If Chr(pKeyAscii) = strDecDlm Then
      If Not pAllowDecimal Or InStr(1, pTextBox_Text, strDecDlm) > 0 Then pKeyAscii = 0
    ElseIf Chr(pKeyAscii) = "-" Then
      If Not pAllowMinus Or pTextbox_SelStart <> 0 Then pKeyAscii = 0
    ElseIf InStr(1, "0123456789", Chr(pKeyAscii)) < 1 And _
           Not (pKeyAscii = 8 Or pKeyAscii = 13 Or pKeyAscii = 27) Then
      pKeyAscii = 0
    End If
  End Sub  'KeyPressNumeric

[mssql] How to count records with distinct option?

SELECT
  (SELECT count(*) FROM
    (SELECT DISTINCT City, Gender
      FROM Database1..Person (NOLOCK)
      WHERE Age > 18
     UNION
     SELECT DISTINCT City, Gender
      FROM Database2..Person (NOLOCK)
      WHERE Age > 18) x)