Posted on Leave a comment

Update Statistics

DECLARE @tabs TABLE (  id int identity(1,1) not null
, tabname sysname not null );

DECLARE @i int
, @tabname sysname;

INSERT INTO @tabs ( tabname )
SELECT name
FROM sysobjects
WHERE type = ‘U’;

SELECT @i = max(id) FROM @tabs;

WHILE @i > 0
BEGIN
SELECT @tabname = tabname FROM @tabs WHERE id = @i
PRINT ‘UPDATE STATISTICS ‘ + @tabname + ‘ WITH ALL’
SET @i = @i – 1
END;

GO

Posted on Leave a comment

ISNULL, COALESCE

1. Data type determination of the resulting expression – ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence

2. The NULLability of result expression can be different for ISNULL and COALESCE. ISNULL return value is considered NOT NULLable if the return value is a non-nullable one (in the case when the argument that is returns is based on a non-null column or constant). Whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.

3. Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex:

ISNULL(NULL, NULL) — is int

COALESCE(NULL, NULL) — Will throw an error

COALESCE(CAST(NULL as int), NULL) — it valid and returns int

4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters. You have to have nested ISNULL to get the same effect as COALESCE.

5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL built-in function.

6. You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and some queries with COALESCE be sub-optimal.

Posted on Leave a comment

Gerunds

The way to implement a “many-to-many” relationship requires an additional table be created consisting of the primary keys from each of the entities. This additional table goes by many different names depending on who you talk to.

Association table
Bridge table
Cross-reference (xref) table
Intersection tables
Join table (perhaps most common)
Junction table
Link table, link entity
Many-to-many relationship tables
Map table
Reference table
Relationship table
Swing table

Probably the most proper (being a mathematical model) but least used is “Gerund” — so named by E.F. Codd, creator of the relational model. This term applies because an entity is functioning as a relationship.

Posted on Leave a comment

Don’t Criticize Code

Editorial – Don’t Criticize Code
By Phil Factor
SQLServerCentral.com

After many years spent supervising development teams, I’ve come to recognize warning signs of problems ahead. There are many such signs, but the following is one that always makes me cringe: a developer looks at code that he or she is assigned to maintain, and says something like “Who one earth wrote such stupid and xxxx (unstructured/ mindless/ unintelligible/etc.) code?”

There is a developer showing his inexperience. Only adolescents writing their first application in some exciting new computer language in their bedroom ever believe that it is possible to write perfect code, or even that they are capable of doing it. Intensive commercial experience as a developer normally grinds off the idealism and naivety that inspires this attitude towards legacy code.

Never is it a good idea to criticize legacy code, let alone the poor developer responsible for it, particularly if you don’t know the history of the application and the circumstances of the code being written. If you don’t show the code due respect, you are in danger of refactoring it into something worse. You probably aren’t aware of the circumstances under which the code was written, or the real brief given to the programmer. The pressures of coding in a commercial development project are difficult to describe, but it is perfectly natural under such conditions to take decisions that, in retrospect look daft, but which actually saved the project.

Legacy code is something I pore through with eagerness, as if it were a rich historical archive; even if it merely illustrates the coder’s drift into lunacy. Many times, when doing so, I have been jolted out of a growing sense of contempt by coming across a technique or insight that is brilliant. This real sense of humility, when faced with the results of human endeavor under pressure, always served me well in my work as a consultant. When you’ve been called in to fix a project that is about to hit the wall, making any suggestion of criticism is probably the least helpful thing you could possibly do. Beyond the easy job of fixing things, comes the more difficult trick of tactfully attributing their original predicament to a cruel act of Fate.

For anyone eager for a long-term career in IT Development, humility and tact are probably more important habits to acquire than demon coding-skills.

SQLServerCentral.com

Posted on Leave a comment

3rd Party References

In Visual Studio I usually get errors when using 3rd party controls due to the assemblies not being deployed along with the application.

To resolve this, I put copies of the control assemblies in a sub-folder of the project called “References” and then add those files to the references in the project references in the solution explorer using browse.

You can then go to the properties page of the reference and set “Copy Local” to true. This will ensure that the module gets deployed to the “bin” folder of the app.

Posted on Leave a comment

Changing Collation on Columns

Had a problem with this after importing data from one database to another using “SELECT INTO” over a link. A co-worker developed this, so kudos go to him…


declare @table_name varchar(50)
declare @column varchar(50)
declare @datatype varchar(50)
declare @nullornot varchar(15)
declare @length varchar(3)
declare @getprops cursor

SET @getprops = CURSOR for

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CASE WHEN IS_NULLABLE = ‘YES’
THEN ‘NULL’
ELSE ‘NOT NULL’
END AS NLL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = ‘Metrics’
AND COLLATION_NAME = ‘SQL_Latin1_General_CP1_CI_AS’

OPEN @getprops
FETCH NEXT
FROM @getprops into @table_name, @column, @datatype, @length, @nullornot
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N’ALTER TABLE ‘ + @table_name + N’ ALTER COLUMN ‘ + @column
PRINT @datatype + N'(‘ + @length + N’) COLLATE SQL_Latin1_General_CP850_CI_AS ‘ + @nullornot
PRINT N’GO’
PRINT N”

FETCH NEXT
FROM @getprops into @table_name, @column, @datatype, @length, @nullornot
END
CLOSE @getprops
DEALLOCATE @getprops
GO

Posted on Leave a comment

MSAccess: Launch Form on Startup

I recently had to start doing stuff in MS Access 2007. It has been a great many moons since I’ve worked with Access and, of course, all of the menus have changed.

Now, to get the the settings page for the “Application”, instead of “Options”–>”Tools”, you now click the “Office Button” and down at the bottom of the menu page that pops up is “Access Options”.

This is one location you can specify which form you want to open up automatically when you open the access file.

A nice holdover from previous versions gives another, more flexible method. You can create an macro named “AutoExec” that can launch a macro, form, code module procedure, etc.

To override the default MS Access splash screen, create a 1×1 1bit bitmap file and name it the name as your ms access project (ie: “myproject.bmp”) and place it in the same folder as your ms access project “*.accdb” file.

Posted on Leave a comment

MSAccess: GetNetUser()

If you use “CurrentUser()” you will as often as not get “Admin”, which is the default Access user.

To get the user’s windows authentication login name, place the following code at the top of a standard module:

Private Declare Function WNetGetUserA Lib "mpr.dll" _
(ByVal lpszLocalName As String, ByVal lpszUserName As String, lpcchBuffer As Long) As Long

Public Function GetNetUser() As String
   Dim lpUserName As String, lpnLength As Long, lResult As Long
   'Create a buffer
   lpUserName = String(256, Chr$(0))
   'Get the network user
   lResult = WNetGetUserA(vbNullString, lpUserName, 256)
   If lResult = 0 Then
      GetNetUser = Left$(lpUserName, InStr(1, lpUserName, Chr$(0)) - 1)
   Else
      GetNetUser = "-unknown-"
   End If
End Function

It can be used in a query as GetNetUser() and in the control source of a text box on a form or report as =GetNetUser()