Sql – Auto trim Database entries

databasedatabase-designsql

Is it possible edit a table definition to automagically trim entries of all trailing whitespace when they are entered?

Or would this be more efficient/easier/normally done in the code befeore submitting the entries??

Best Answer

If you are working within SQL Server you can make this automatic but you should only really do this if you are sure and with appropriate consideration. The ANSI_PADDING decides in SQL as to whether it trims the trailing spaces of a value being inserted. The setting is applied at the time that table is created, and is not altered after, and the setting will work for varchar, but does not work for nvarchar.

A test script to show the difference is as follows:

set ansi_padding on
create table foo (myID int identity, myString varchar(50))
insert into foo values ('abcd      ')
select datalength(mystring) from foo

drop table foo
set ansi_padding off
create table foo (myID int identity, myString varchar(50))
insert into foo values ('abcd      ')
select datalength(mystring) from foo

On the first table the data length returns as 10 since the spaces were inserted, on the second example they are trimmed on insert.

I would personally prefer the code controlling what was needed to be done instead of leaving it to the setting, but I included the examples to show it can be done at the DB level if required.

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Sql – When should I use CROSS APPLY over INNER JOIN

Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?

See the article in my blog for detailed performance comparison:

INNER JOIN vs. CROSS APPLY

CROSS APPLY works better on things that have no simple JOIN condition.

This one selects 3 last records from t2 for each record from t1:

SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o

It cannot be easily formulated with an INNER JOIN condition.

You could probably do something like that using CTE's and window function:

WITH    t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT  t1.*, t2o.*
FROM    t1
INNER JOIN
        t2o
ON      t2o.t1_id = t1.id
        AND t2o.rn <= 3

, but this is less readable and probably less efficient.

Update:

Just checked.

master is a table of about 20,000,000 records with a PRIMARY KEY on id.

This query:

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ),
        t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
JOIN    q
ON      q.rn <= t.id

runs for almost 30 seconds, while this one:

WITH    t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
CROSS APPLY
        (
        SELECT  TOP (t.id) m.*
        FROM    master m
        ORDER BY
                id
        ) q

is instant.

Best Answer

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Sql – When should I use CROSS APPLY over INNER JOIN

Related Topic