Web daugava.com
Tips for SQL, T-SQL, Query Analyzer and Enterprise Manager

T-SQL
  • ISNUMERIC function does not evaluate to true/false, which can cause
    error "Incorrect syntax near the keyword...". This code does not work:
    IF ISNUMERIC('1') BEGIN
    PRINT 'true'
    END
    but this works:
    IF ISNUMERIC('1')=1 BEGIN
    PRINT 'true'
    END
  • Example of search-n-replace:
    UPDATE sometable SET somecolumn = REPLACE(somecolumn, CHAR(9), '')
  • Message "The select list for the INSERT statement contains fewer items than the insert list"
    may show up when a comma is missing between field names. The

  • COMMIT TRANSACTION may fail when there are open recordsets.

  • sp_depends {proc name} - shows who is calling that procedure

  • Use RETURN to quit a procedure at any time

  • Faster way to do some OR conditions:
    select * from items where id in (112,214)

  • One of the reasons for "incorrect syntax near..." error could be missing comma
    in the line before

  • "The column prefix ... does not match with a table name or alias name used in the query" -
    could happen when you use wrong table name inside the Join, such as
    Select codes cd
    Join respondents resp
    On cd.value = 1
    Another reason for this error could be that you are using the table prefix before it
    has been joined:
    select data dt
    join codes cd
    on cd.code = ab.text <---
    join abcd ab
    on ab.value =1
    where dt.id = 1

  • Common error is to omit the variable type in declare statement - easy to do after
    programming in ASP, where all you do is DIM {variable_name}.

  • You can feed the output of one command into the other:
    select * from ABC where id in (select id from XYZ x where x.NUM=13)
    This will retrieve IDs of all records in XYZ where value of field NUM is 13
    and then display records from ABC with matching IDs.

  • You can get better lookng output in Query Analyzer by setting an option Query/Results in Grid.
    Also, in Grid mode you horizontal scrolling is faster when you have a lot of columns.

  • You cannot copy values from the output window using the standard
    Ctrl-INS or Ctrl-C keys. Instead you will need to Right Click/Copy.

  • When debugging a stored procedure with multiple joins in Query Analyzer, replace the intial
    SELECT field list with SELECT * - this way you won't have to change it when you comment out pieces
    of code.

  • SQL moves in mysterious ways. For example, you cannot name a description field 'DESC' - apparently
    because it's a keyword for 'descending'.

  • nvarchar stands for 'national varchar' - every character takes 2 bytes.

  • CHARINDEX(needle, haystack, start) - returns position of needle in haystack or 0 if not found

  • WHILE..BEGIN..END will be rejected by Query Analyzer if there's nothing between BEGIN and END.

  • When you use ODBC, it's permissible to have stuff like debug PRINT statements in stored
    procedures. With OLEDB, this will break the ASP page.

  • Getting difference between 2 dates:
    DATEDIFF(day, @testdate, getdate())

  • You can read the output of SELECT command sequentially:
    DECLARE
    @s varchar(50)

    DECLARE fil SCROLL CURSOR FOR
    SELECT Testdata FROM interactionhistory WHERE alarmdate is not NULL

    OPEN fil

    FETCH NEXT FROM fil INTO @s
    -- Need to do FETCH before the loop - because value of @@fetch_status can be still set from previous time
    WHILE @@fetch_status = 0 BEGIN
    print @s
    FETCH NEXT FROM fil INTO @s
    END

    CLOSE fil
    DEALLOCATE fil

  • There are statements for breaking out of loop - BREAK and starting next iteration - CONTINUE

  • Single statement IF/THEN/ELSE clause:
    if 1=1 print "true"
    else print "false"

  • Counting number of records:
    ELECT COUNT(*) AS 'count' FROM url_link

  • SQL allows you to declare variable as varchar (without specifying the size), but then it may
    not work right in comparisons.

  • Error message "Syntax error converting the varchar value 'abc' to a column of data type int"
    really means "You are trying to concatenate string with a number". To fix this, use convert(varchar, X).

  • Query Analyzer - after a long query finished you can see the number of rows displayed -
    it's in status line, bottom right side of window.

  • When joining several tables, you can use 'tablename.*' instead of '*' if you want to see all
    fields from one table only:
    SELECT
    p.name,
    i.*
    FROM Inter i
    JOIN pro p on (p.id=i.id)

  • When specifying parameters for stored procedure, you need to set max length for strings, i.e. varchar(100),
    otherwise length of 1 is assumed.

  • The following func will return value of x, or space if x is null:
    IsNull(x, '')

  • If you want to have DROP PROCEDURE and CREATE PROCEDURE in the same file,
    put 'Go' before CREATE.
  • You can output stuff inside stored procedure:
    print "Hello, world!"

  • "(x row(s) affected)" is shown when query contains INSERT statement.

  • Message "Incorrect syntax near the keyword ..." usually means a missing colon at the
    end of multi-line SELECT or DECLARE.

  • Cryptic error message " 'char' is not a recognized CURSOR option " can
    occur when you forget to preface variable name with '@', for example:
    declare X As Numeric

  • When generating code dynamically, enclose it in double quotes - so Query
    Analyze will preserve syntax hiliting. If you use single quotes, everything inside
    will show in red - as a string constant.

  • When 'End' in a procedure is missing, Query Analyzer will tell you
    "incorrect syntax" till it's blue in a face (never admitting that it wants to
    see 'End'). Sounds like bad case of denial.

  • Building SQL code on the fly:
    declare @s nvarchar(60)
    declare @x nvarchar(60)
    select @x=" table1"
    select @s="select *
    from" + @x
    Execute sp_ExecuteSQL @s
  • Branching:
    IF ... BEGIN
    ..
    END
    ELSE .. BEGIN
    ..
    END
    If you only have ine statement per clause, you can use simplified form:
    IF ..
    stmt1
    ELSE IF ..
    stmt2
    ELSE
    stmt3
  • Variable assignment:
    SELECT
    @X=1,
    @S='XX'


Enterprise Manager:
  • Ctrl-Tab switches between open E-Man windows. Same key works for QU-Zer.

  • Sometimes E-Man installation gets corrupted - happened to me twice in one month.
    Easy way to safeguard against this - make a backup copy of C:\MSSQL7\Binn\SQL Server Enterprise Manager.MSC
    and restore it when it gets lost or garbled.

  • After database restore you may need to close and re-open the Enterprise Man,
    before you see updated data.

  • You can create a "diagram" - it will graphically show the relation between
    tables (foreign keys and stuff). To create one, right click on database name and
    pick New/Database Diagram.
    The picture you get will not show what keys are related, but you can get this info
    by right clicking on the key name and picking Properties.

  • Sometimes you have to close current query for the changes to be saved.

  • When creating a table, you can type several characters to select a field type
    from the pulldown list.

  • When scripting out tables, E-Man can sit at 22% for a long time, before it
    starts moving again.

  • When you load a table, the Page Down and other navigation keys don't work.
    The quick way to get them working is to click on any column title.

  • Quick search allows you to enter several characters to find the table name.
    However, you have to type them fast - if you pause, then E-Man will start over
    and try to match next character with first letter of the table.

  • You can test changes in stored procedure without leaving it - click on
    'Apply' and then reaload the webpage.

  • To quickly get name of stored procedure into the clipboard,
    double click on it's name in Enterprise Manager. Once inside the proc,
    double click on name again, right click and 'Copy'. This sure beats retyping
    something like sp_report_list_people_recruited_on_first_monday_of_this_year.

Query Analyzer:
  • You can connect to different SQL servers in separate QA windows - just do File/Connect -
    this will automatically open a new window.

  • You can have different databases open in diff windows.

  • Alt-D is the shortcut for "database selection" list.

  • Use F6 to toggle between Command and Results windows.

  • Bug - when you do Edit/Advanced/Make Selection Uppercase, the change cannot
    be reversed with Undo.

  • When you do sp_help to see table structure, the field intfo is spread
    out, so it may be hard to follow what's in the same row. Workaround -
    scroll, so that the row you are looking at is the last one in the window.

  • Ctrl-O does not open file - in fact there's no shortcut for opening,
    you must pick it from the menu... Alt-F-O is semi-shortcut.

  • Cryptic message "A SELECT statement that assigns a value to a variable
    must not be combined with data-retrieval operations" will show up if you accidentally
    specify data type in the assignment statement:
    SELECT
    @X = 1,
    @Y Varchar(20)

  • You can have multiple windows - press Ctrl-N to start a new one.

  • Query Analyzer has multiple Undo levels - apply them by repeatedly press Ctrl-Z.

  • 'execute' command is optional - you can simply type
    the name of stored procedure to run it.

  • Use sp_help <tablename> to display table structure in Query Analyzer.
    It displays field types too - scroll right to see them.