Developping for the translation industry RSS 2.0



 Monday, 11 July 2011

Sometimes we need to create a CSV list of items, containing in a single row the different values of a column in a table.

This script just accomplish that, in the simplest manner possible:

DECLARE @theListOfValues VARCHAR(MAX)
SELECT @theListOfValues = COALESCE(@theListOfValues+',' , '') +
MyColumn
FROM 
MyTable
SELECT @theListOfValues

 

Other posts:

How to remove multiple whitespaces from a string with SQL Server 2005

Which performs better: ISNULL or COALESCE

How to remove leading zeros from the results of an SQL Query

Simple way to count characters and words using T-SQL

Domain registration and one full year of Web hosting for Free!

Monday, 11 July 2011 10:19:50 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
SQL
 Thursday, 29 July 2010

Here is a simple way to get a specific character count (for example, count the number of occurences of the character ‘0’) and the word count of a varchar string using T-SQL.

-- Count the number of specific characters, in this case, ‘0’

Declare @value varchar(100)

Set @value = 'SQL Server 2000, SQL Server 2005, SQL Server 2008'

Select Len(@value) - Len(Replace(@value, '0', ''))

 

-- Count the number of words

DECLARE @String VARCHAR(100)

SELECT @String = 'SQL Server 2005 Stan test code'

SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

 

As you see this is quite straightforward, the script simply substract the length of the string minus the character searched from the full length of the string, giving as a result the character count. The script counting words is simply counting the space characters.

 

Other posts:

How to remove multiple whitespaces from a string with SQL Server 2005

How to generate random numbers with a SQL query

How to remove leading zeros from the results of an SQL Query

How to Find The List Of Unused Tables Since The Last SQL Server Restart

Which performs better: ISNULL or COALESCE

Thursday, 29 July 2010 08:58:32 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Wednesday, 24 February 2010

The SQL Server functions ISNULL and COALESCE seem to occupy the same space in terms of functionality. The only difference is that ISNULL is restricted to only two parameters, while COALESCE can take any number of parameters. So why ever use ISNULL?

First of all, the simple answer for why to prefer ISNULL over COALESCE when given the choice is that ISNULL tends to produce query plans that are more efficient than COALESCE's. Examine the query plans for the two queries given below to see the difference:

SELECT a.au_id, 
       Isnull
((SELECT price
               FROM   titles
               WHERE  title_id = ta.title_id),0)
FROM   authors a
       JOIN titleauthor ta
         ON a.au_id = ta.au_id

————————————

SELECT a.au_id, 
       Coalesce((SELECT price
                 FROM   titles
                 WHERE  title_id = ta.title_id),0)
FROM   authors a
       JOIN titleauthor ta
         ON a.au_id = ta.au_id

The first query uses one less nested loop in it’s execution plan than the second, resulting in a lower overall cost. Note that I would never recommend writing queries that nest subqueries within functions. This example was only intended to show you the difference between the two generated execution plans, not to recommend a certain T-SQL coding style.

The other bit that most people don't know about ISNULL and COALESCE is that the return data type for ISNULL is guaranteed to be the same as the data type of the first parameter. However, the return data type of COALESCE is determined by data type precedence rules (see the Books Online topic "Data Type Precedence"). Therefore, the following queries will produce two different outputs:

DECLARE  @Example CHAR(2)
SET @Example = NULL

SELECT Isnull(@Example,'abcde'),
       Coalesce(@Example,'abcde')

In this example, the first expression using ISNULL will return 'ab', which is the declared datatype of the first parameter, a char(2). The second expression using COALESCE will return the highest precedence data type, which is the longer string 'abcde.'

To summarize:

  • When you only need to coalesce two arguments, use ISNULL instead because it performs better.
  • When using COALESCE, you may want to use explicit casting to ensure you get a consistent return data type.

Other posts:

5 ways to generate random numbers with a T-SQL query

How to remove leading zeros from the results of an SQL Query

How to get the list of object modifications in SQL Server

How to use derived tables to boost SQL performance

Good Joke on SQL Injection

Wednesday, 24 February 2010 10:15:13 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Tuesday, 09 February 2010

Here is a quick and easy way to remove multiple whitespaces from a string, leaving only one space character between tokens.

CREATE FUNCTION dbo.CleanString
               (@string VARCHAR
(50))
RETURNS VARCHAR
(50)
AS
  BEGIN
    SET @string = Ltrim
(Rtrim(@string))
    
    WHILE Charindex
('  ',@string) > 1
      SET @string = Replace
(@string,'  ',' ')
    
    RETURN @string
  END

 

Other Posts:

How to generate random numbers with a T-SQL query

How to insert a file in an image column in SQL Server 2005

How to track the growth of your SQL Server database

SQL Server indexing best practices and guidelines

How to remove leading zeros from the results of an SQL Query

Tuesday, 09 February 2010 13:24:17 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Wednesday, 03 February 2010

The sys.objects system table offers you some information on the last modifications made on any database object. As a quick example, the following script queries for the list of user tables modified since the start of this year.

DECLARE  @date  AS DATETIME
SET @date = '2010-01-01'

SELECT   name, 
         type_desc,
         create_date,
         modify_date
FROM     sys.objects
WHERE    TYPE = 'U'
         AND modify_date >= @date
ORDER BY modify_date

Now this knowledge can help you manage your SQL Servers more easily. You could, for example, create a script that runs every night and send you within an email the list of objects modified the last day.

The list of available types to filter your search are:

AF Aggregate function (CLR)
C CHECK constraint
D DEFAULT (constraint or stand-alone)
F FOREIGN KEY constraint
FN SQL scalar function
FS Assembly (CLR) scalar-function
FT Assembly (CLR) table-valued function
IF SQL inline table-valued function
IT Internal table
P SQL Stored Procedure
PC Assembly (CLR) stored-procedure
PG Plan guide
PK PRIMARY KEY constraint
R Rule (old-style, stand-alone)
RF Replication-filter-procedure
S System base table
SN Synonym
SQ Service queue
TA Assembly (CLR) DML trigger
TF SQL table-valued-function
TR SQL DML trigger
TT Table type
U Table (user-defined)
UQ UNIQUE constraint
V View
X Extended stored procedure

 

Other posts:

How to: Find The List Of Unused Tables Since The Last SQL Server Restart

Differences between temporary tables and table variables

Using derived tables to boost SQL performance

How to track the growth of your database

Wednesday, 03 February 2010 11:00:54 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Monday, 01 February 2010

This script will return a list of user tables in the database, along with the last time a SELECT was executed against them (including any views that include data from that table). This can be used to determine if a table is not in use anymore.

Statistics are reset when the SQL Server sevice is restarted, so this query will only return activity since that time. Also, just because there's no activity doesn't mean it's safe to remove an object - some tables may only be used during monthly or annual processes, and so they wouldn't show any activity except during those brief intervals.

WITH lastactivity(objectid,lastaction)
     AS (SELECT object_id      AS tablename,
                last_user_seek AS lastaction
         FROM   sys.dm_db_index_usage_stats u
         WHERE  database_id = Db_id
(Db_name())
         UNION
         SELECT object_id      AS tablename,
                last_user_scan AS lastaction
         FROM   sys.dm_db_index_usage_stats u
         WHERE  database_id = Db_id
(Db_name())
         UNION
         SELECT object_id        AS tablename,
                last_user_lookup AS lastaction
         FROM   sys.dm_db_index_usage_stats u
         WHERE  database_id = Db_id
(Db_name()))
SELECT   Object_name
(so.object_id) AS tablename,
         Max
(la.lastaction)        AS lastselect
FROM     sys.objects so
         LEFT JOIN lastactivity la
           ON so.object_id = la.objectid
WHERE    so.TYPE = 'U'
         AND so.object_id > 100
GROUP BY Object_name
(so.object_id)
ORDER BY Object_name
(so.object_id)

 

Other posts:

How to generate random numbers within a T-SQL query

SQL Server SPACE function

SQL Injection humor

Differences between temporary tables and table variables

How to track the growth of your database

How to get the total number of rows in a database

Using derived tables to boost SQL performance

Monday, 01 February 2010 10:36:17 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Monday, 11 January 2010

A little quicky post on the SQL Server SPACE function. The SPACE function returns a string that consists of a specified number of spaces.

The following command will generate 10 space characters:

SELECT SPACE(10)

The use of SPACE() function is demonstrated very clearly in BOL:

USE AdventureWorks;
GO
SELECT RTRIM(LastName) + ',' + SPACE(2) + LTRIM(FirstName)
FROM Person.Contact
ORDER BY LastName, FirstName;
GO

A very useful little function.

 

Others posts:

How to generate random numbers within a T-SQL query

How to remove leading zeros within an SQL query

Some tips to enhance your SQL Server security

The T-SQL LoginProperty function in SQL Server 2005

Monday, 11 January 2010 09:42:00 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Tuesday, 22 December 2009
 Friday, 20 November 2009

There are many ways to generate random numbers in SQL Server. Here are some scripts that will let you accomplish this.

Method 1 : Generate Random Numbers (Int) between Rang
---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

Method 2 : Generate Random Float Numbers
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (
DATEPART(ss, GETDATE()) * 1000 )
+
DATEPART(ms, GETDATE()) )


Method 3 : Random Numbers Quick Scripts

---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())


Method 4 : Random Numbers (Float, Int) Tables Based with Time

DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET
@cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (
DATEPART(ss, GETDATE()) * 1000 )
+
DATEPART(ms, GETDATE()) )
END
SELECT
randnum, COUNT(*)
FROM @t
GROUP BY randnum


Method 5 : Random number on a per row basis

---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber

 

Other posts :

How to convert dates in T-SQL

How to remove leading zeros within an SQL Query

How to insert a file in an image column in SQL Server 2005

 

Friday, 20 November 2009 10:21:08 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Monday, 08 June 2009
Just found this excellent blog post (continually updated) that lists a ton of free SQL Server tools for all sorts of purposes. Check it out at http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/Free-SQL-Server-tools-that-might-make-your-life-a.aspx.
Monday, 08 June 2009 10:04:24 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
SQL | Tools
 Monday, 19 January 2009

1072482_calendar

I wanted to write this down for some time now…

The Convert function in SQL Server provides a means to translate SQL Server’s default date format to just about any format. However, the convertion format isn’t intuitive at all. I wanted to have a quick reference for the future and help those who had trouble finding a list of all the possible convert options in T-SQL.

The syntax for the Convert function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Available Date Formats:
  

Other Posts:

The T-SQL LoginProperty function in SQL Server 2005

How to restart a Windows service using C#

How to insert a file in an image column in SQL Server 2005

Monday, 19 January 2009 15:07:23 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
SQL
 Thursday, 04 December 2008

Open DBDiff is an open source database comparison tool for SQL Server 2005 that generates synchronization SQL. It has options to customize the comparison and SQL generations. The database differences are identified at a high level. The synchronization SQL it generates can be copied to a query tool to be executed after you review it.

Open DBDiff can synchronize:

  • Tables (including Table Options like vardecimal, text in row, etc.)
  • Columns (including Computed Columns, XML options, Identities, etc.)
  • Constraints
  • Indexes
  • XML Indexes
  • XML Schemas
  • User Data Types (UDT)
  • CLR-User Data Types (CLR-UDT)
  • Assemblies
  • Triggers (including DDL Triggers)
  • Synonyms
  • Schemas
  • File groups
  • Views (Including SCHEMA BINDING option)
  • Functions (Including SCHEMA BINDING option)
  • Store Procedures
  • Users
  • Roles

When you are using such tools, always remember to double check the generated SQL script for anything suspicious that may harm your environment.

OpenDBdiff2

 

Other posts:

Free software tools for students

My 6 favourites file hosting services

Tools for web developers

Tool of the day: Copy source as HTML

Thursday, 04 December 2008 09:58:23 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
SQL | Tools
 Tuesday, 02 December 2008

Did you change the “sa” password recently? As a DBA, you should be aware that there is a great security risk linked to the sa account. You should always use strongs password for this account and change the password frequently.

You can easily check when the “sa” password was last changed in SQL Server 2005 by executing the following T-SQL code:

SELECT LOGINPROPERTY ('sa', 'PasswordLastSetTime')

The LOGINPROPERTY function gives you lots of information on the logins properties and password policy information for these logins.

Another thing you can do with this function is to look for security attacks. For example if you want to look for brute-force or dictionnary attack on the “sa” account, you can use the following query:

SELECT LOGINPROPERTY ('sa', 'BadPasswordCount')

This will return the number of failed consecutive attempts to login since the last successful login. So if this value goes over a certain value, you can easily see that something might be wrong.

Here is the complete list of properties you can query for using the LoginProperty function:

BadPasswordCount
Returns the number of consecutive attempts to log in with an incorrect password.

BadPasswordTime
Returns the time of the last attempt to log in with an incorrect password.

DaysUntilExpiration
Returns the number of days until the password expires.

DefaultDatabase
Returns the SQL Server login default database as stored in metadata or master if no database is specified. Returns NULL for non-SQL Server provisioned users; for example, Windows authenticated users.

DefaultLanguage
Returns the login default language as stored in metadata. Returns NULL for non-SQL Server provisioned users, for example, Windows authenticated users.

HistoryLength
Returns the length of time the login has been tracked using the password-policy enforcement mechanism.

IsExpired
Returns information that will indicate whether the login has expired.

IsLocked
Returns information that will indicate whether the login is locked.

IsMustChange
Returns information that will indicate whether the login must change its password the next time it connects.

LockoutTime
Returns the date when the SQL Server login was locked out because it had exceeded the permitted number of failed login attempts.

PasswordHash
Returns the hash of the password.

PasswordLastSetTime
Returns the date when the current password was set.

 

Other posts:

Differences between temporary tables and tables variables

How to insert a file in an image column in SQL Server 2005

How to add a row number in an SQL Query

Tuesday, 02 December 2008 10:40:46 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Security | SQL
 Tuesday, 28 October 2008

There are some important differences between temporary tables:

create table #T (…)

And table variables:

declare @T table (…)

 

Temporary Tables

  • They are created using the “Create table” syntax, preceding the table name with a ’#’ for a local temp table and ‘##’ for a global temp table.
  • The allocated storage space within the TempDB database and entered into the TempDB system tables.
  • The table’s actual name is the name is was created with, a large number of underscores and a hash value, to prevent object name collisions if two connections create a temp table with the same name.
  • Can have a primary key, defaults, constraints and indexes (however the names of these are not hashed, possibly leading to duplicate object errors for constraints and defaults).
  • Can’t have triggers.
  • Foreign keys are permitted, but are not enforced.
  • Have column statistics kept on them. The algorithm for determining when to update is different to permanent tables.
  • Exist until they are dropped, or the connection closes.
  • Are visible in any child procedures called from the one where the table was created. Are not visible to parent procedures.

Table Variables

  • Created with a Declare statement, prefixing the table name with ‘@’, like all other variables.
  • Allocated storage space within the TempDB database and entered into the TempDB system tables.
  • The table variable’s name within TempDB starts with a #, followed by a hex string.
  • Can have a primary key and defaults. May not have constraints or indexes.
  • Can’t have triggers or foreign keys.
  • Do not have column statistics maintained on them.
  • Exist only while they are in scope, as any other variable, and are automatically dropped when they go out of scope.
  • Are not visible in any procedures other than the one they were created in.
  • Do not take part in transactions. Data modification done to a table variable within a transaction will remain if the transaction is rolled back.

Aside from those differences, which one will give the best performance you’ll ask me.  Well, like a lot of things in the SQL world, it depends.

I would say first that, when in doubt, you should try both solutions as the fastest solution is not always the most obvious one.

Also, be sure to note these important points:

  • Table variables are MUCH slower in SQL Server 2005 in comparaison to SQL Server 2000 (I don’t know for 2008).
  • Creating a primary key or a clustered index on a temporary table is useful only for large datasets (I would say above 50k records).  It as no significant impact on small sets of data.  In fact, it can lower your execution performance if you’re using stored procedures as clustered indexes creation forces the recompilation of the stored procedure, which can take more resources than the original query.
Other popular SQL Posts :

How to insert a file in an image column in SQL Server 2005

How to get the total number of rows in a database

How to remove leading zeros within an SQL Query

How to track the growth of your database
Tuesday, 28 October 2008 12:12:19 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
SQL
 Monday, 20 October 2008

Today’s portable app is not a simple application but a full featured database engine: SQLite. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

From SQLite Website:

SQLite does not need to be "installed" before it is used. There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured. There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.

SQLite just works.

 

For those who didn’t heard the SQLite name before and have some doubts on the stability and performance of this engine, don’t fear.  This is probably the most widely deployed database engine as of today. Here are some stats on where SQLite is currently used:

  • 125 million copies of Mozilla Firefox [1]. Firefox 3 uses Sqlite for bookmarks and history.
  • 20 million Mac computers, each of which contains multiple copies of SQLite
  • 20 million websites run PHP which has SQLite built in. [2] We have no way of estimating what fraction of those sites actively use SQLite, but we think it is a significant fraction.
  • 300 million downloads of the Skype client software and 100 million registered users [3]. All recent versions of the Skype client use SQLite internally.
  • 20 million Symbian smartphones shipped in Q3 2007 [4] Newer versions of the SymbianOS have SQLite built in. It is unclear exactly how many Symbian phones actually contain SQLite, so we will use a single quarter's sales as a lower bound.
  • 10 million AOL subscribers use SQLite in the AOL email client that comes bundled with their subscription.
  • 10 million Solaris 10 installations, all of which require SQLite in order to boot.
  • Millions and millions of copies of McAfee anti-virus software all use SQLite internally.
  • Millions of iPhones use SQLite
  • Millions and millions of other cellphones from manufactures other than Symbian and Apple use SQLite. This has not been publicly acknowledged by the manufactures but it is known to the SQLite developers.
  • There are perhaps millions of additional deployments of SQLite that the SQLite developers do not know about.

 

Other Portable software posts:

Portable software – Part 1 (Developer tools)

Portable software – Part 2 (Graphic tools)

 

Other posts:

Chuck Norris Programming facts

Monday, 20 October 2008 09:18:02 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Portable Software | SQL | Tools
 Thursday, 16 October 2008

As a DBA, you may want to know the evolution, in time, of your databases size. To do so, you just need to execute the following script, after having changed ‘databaseName’ with your database’s name.  This script won’t look for the actual database size but for the backup size, which is a very good indicator. It’s good to note that only full backups are logged in this table, so, if you have a full backup every night and 4 differential scattered through the day, only the full backup will be logged.

        Select backup_start_date, SizeInMegs=floor(backup_size/1024000)
        from msdb..backupset
        where database_name = 'DatabaseName' and type = 'd'

        order by backup_start_date asc

 

Other interesting SQL posts :

SQL Server indexing best practices and guidelines

How to insert a file in an image column in SQL Server 2005

How to remove leading zeros within an SQL query


Other popular posts

How To: Create an Outlook 2003 addin using VSTO SE and Visual Studio 2005

Thursday, 16 October 2008 09:00:37 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Friday, 22 August 2008

Here are some guidelines that I gathered on indexing and boosting SQL Server query performance. I hope that those tips will be as useful to you as they were to me!

  1. Periodically, run the Index Wizard or Database Engine Tuning Advisor against current Proiler traces to identify potentially missing indexes.
  2. Remove indexes that are never used.  This will enhance Inserts/Updates/Deletes performance because the database engine will have fewer indexes to maintain when those operations occurs.
  3. Normally, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases — such as an identity column. In many cases, the primary key is the ideal column for a clustered index.
  4. Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
  5. When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once the needed record is found, SQL Server doesn’t have to look any further.
  6. If a column in a table is not at least 95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, you generally don’t want to addnon-clustered indexes to columns that aren’t at least 95% unique.
  7. This seems obvious but some people forget to follow this simple advice : Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.

If you are like me and want to know more about how SQL Server manages his indexes, take a look at the sysindexes table that is part of every database.  You simply have to run “SELECT * FROM sysindexes”.

Here are some of the more interesting fields found in this table:

  • dpages: If the indid value is 0 or 1, then dpages is the count of the data pages used for the index. If the indid is 255, then dpages equals zero. In all other cases, dpages is the count of the non-clustered index pages used in the index.
  • id: Refers to the id of the table this index belongs to.
  • indid: This column indicates the type of index. For example, 1 is for a clustered table, a value greater than 1 is for a non-clustered index, and a 255 indicates that the table has text or image data.
  • OrigFillFactor: This is the original fillfactor used when the index was first created, but it is not maintained over time.
  • statversion: Tracks the number of times that statistics have been updated.
  • status: 2 = unique index, 16 = clustered index, 64 = index allows duplicate rows, 2048 = the index is used to enforce the Primary Key constraint, 4096 = the index is used to enforce the Unique constraint. These values are additive, and the value you see in this column may be a sum of two or more of these options.  For example a valeu of 2066 means that the index is clustered, unique and that it’s used to enforce the Primary key constraint.
  • used: If the indid value is 0 or 1, then used is the number of total pages used for all index and table data. If indid is 255, used is the number of pages for text or image data. In all other cases, used is the number of pages in the index.

Other popular SQL Posts :

How to insert a file in an image column in SQL Server 2005

How to get the total number of rows in a database

How to remove leading zeros within an SQL Query

Friday, 22 August 2008 11:40:31 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
SQL
 Wednesday, 30 July 2008

A common thing you may want to do when dealing with transactions involving various documents and files is insert them into you SQL Server database.  The following code snippet let you load a file from the disk and insert it into your database.

INSERT INTO myTable (documentData)
SELECT * FROM
OPENROWSET(BULK N'c:\myDocument.doc', SINGLE_BLOB) as dt

Note that you need to name the select statement (here, I named it “dt”) or you will get this error message:

Server: Msg 491, Level 16, State 1, Line 3
A correlation name must be specified for the bulk rowset in the from clause.


Wednesday, 30 July 2008 12:38:15 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Thursday, 10 July 2008

Using SQL Server 2005 new function ROW_NUMBER() makes this really easy.

All you have to do is to add the function ROW_NUMBER, with the OVER() clause as such :

SELECT ROW_NUMBER() 
        OVER (ORDER BY EmployeeName) AS Row, 
    EmployeeId, EmployeeName, Salary 
FROM Employees
The OVER clause needs an “order by” parameter to know how to sort the rows for proper numbering.
 
Thursday, 10 July 2008 15:58:08 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL

There is a very simple script to accomplish this and this can be really helpful for generating stats on a week-by-week basis:

SELECT DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0)

Replace GetDate with a datetime column and you could generate, for example, a sales report, week-by-week.

Thursday, 10 July 2008 12:25:54 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Wednesday, 02 July 2008

This isn’t necessary a critical part of a DBA’s job but, at times, it can be useful to have an idea of how many rows are in your databases.

The simplest way to get it is with this query:

select sum(rowcnt) from sysobjects, sysindexes
where sysindexes.id = sysobjects.id and sysindexes.indid in (0, 1) and sysobjects.xtype = 'u'

This will get you the sum of rows in the entire database, for users objects only.  If you want the table-by-table breakdown, you can simply add the name of the object in the query:

select sysobjects.name, sysindexes.rowcnt from sysobjects, sysindexes
where sysindexes.id = sysobjects.id and sysindexes.indid in (0, 1) and sysobjects.xtype = 'u'
order by sysobjects.name

Wednesday, 02 July 2008 09:43:51 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Monday, 26 May 2008

The LTRIM function in T-SQL only removes whitespaces. But, in some instances, you need to do more complex trimming. The trick is to use the PATINDEX function in conjunction with the STUFF function.  PATINDEX will return the index of the first occurence of a specified pattern.  In my example below, PATINDEX will return the index of the first non-zero character.  STUFF takes 4 parameters:

– the affected column

– the the starting point of the deletion

– the length of the deletion

– a string to insert at the deletion point

So the code below will delete all characters, starting at the first, and finishing before the first non-zero character.  Why not use SUBSTRING instead?  Because STUFF is a little more robust than SUBSTING and will not raise an error on you if the PATINDEX returns -1.

SELECT STUFF(myColumn, 1, PATINDEX('%[^0]%', myColumn) - 1, SPACE(0)) FROM mytable

Also, if you happen to have a column of type float and you want to get the values without the leading zeros (for example, 0.75 would become .75), you simply need to cast your column as varchar and you’re set.


Monday, 26 May 2008 16:49:50 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
 Thursday, 20 December 2007

If you’re using SQL Server 2005, you probably have encountered this error at least once.  When people see this error, they may not know where to start searching.  This post is a recap of every possible cause to this problem (that I know of).

The error message you get is :

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connection. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

The possibles causes are:

Server accessibility and availability:

  • Depending on your connection type, make sure that TCP/IP or Named Pipes are enabled in the Surface Area Configuration tool.
  • Also make sure that TCP/IP or Named Pipes are enabled in the SQL Server Configuration Manager.
  • Make sure that you can ping the server from the client.
  • Make sure that your SQL Server is started (obvious but still needs to be on the list).

Connection String:

  • You should also double check your connection string to be sure that there is not a typo in the server name or instance name.
  • You also should try both Server\Instance and Server\\Instance in your connection string.  This is because some applications convert \\ to \. 
  • You may also try to add the SQL port to the connection string (ex: Data Source=mysqlserver\myinstance,1433).

If you are using SQL Server Express:

  • Check that SQL Server Browser is enabled and is started in the SQL Server Configuration Manager.  If it was disabled, also make sure that you set the Start Mode to “Automatic”.

If your SQL Server is behind a Firewall:

  • Check that there is an exception in your firewall configuration to let the traffic through port 1433 (for SQL Server) and 1434 (for an UDP packet sent to get information on the SQL Instance like the protocol, the TCP port to use, etc).

 If your SQL Instance is on a cluster:

  • UDP packets are dropped in some cases when dealing with an instance on a cluster.  This is a known issue and I don’t know if Microsoft will do something about it.  For more details and workarounds, you can go here, here or here.

if you’re trying to connect to your SQL Server with an ASP.NET application:

  • Make sure that the version of the .NET Framework loaded in IIS on the server for this specific Web Site is the right one (I experienced this error when I deployed an application built with the version 2.0 Framework on a web server with 1.1 loaded by default).
  • The process account of the ASP.NET application must have rights to connect to the SQL Server database.

This list may not be complete. So if you know another cause or element that needs to be checked for this particular scenario, drop me a line at stan@stanbiron.com and I’ll update this post.

Thursday, 20 December 2007 17:37:05 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
SQL
 Thursday, 22 November 2007

Some complex queries involving more than one layer of calculations cannot be easily done in SQL.  Let’s say you need to compute the average number of sales per customer.  Your first thoughts may be something in the lines of :


SELECT AVG(COUNT(SALE_ID)) FROM SALES GROUP BY CUSTOMER_ID

But when you try to execute this kind of query, you will get an error message looking like this :

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

At this point, your instinct should tell you that the average of sales per customer should be something relatively easy to compute using SQL.  Some folks use temporary tables as a workaround to this limitation.  This solution is very bad for your query performance, especially when you have to deal with large databases.  The flow for such a task is :

1– Create a new temporary table to hold the computed values.

2– Insert the values in the new table.

3– Select the value you need from the temporary table.

4– Delete the temporary table.

The easier way that’s a lot faster both to write and to execute is to use derived tables.


SELECT AVG(number_of_sales) FROM (SELECT COUNT(SALE_ID) AS number_of_sales FROM SALES GROUP BY CUSTOMER_ID) dt_sales


The magic here lies in the FROM part.  The FROM keyword can use tables OR derived tables.  A derived table is simply created by writing the query and giving it an alias (in my examplen it is dt_sales).  Derived tables are created only in memory and thus are a lot faster than on-disk temporary tables.

Derived tables can be used exactly as regular database tables.  For example, you can make joins between them or between regular tables and derived tables.

Tags:
Thursday, 22 November 2007 13:13:09 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
SQL

Navigation
Advertisement
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
Stanislas Biron
Sign In
Statistics
Total Posts: 135
This Year: 0
This Month: 0
This Week: 0
Comments: 1
All Content © 2017, Stanislas Biron