sql – Page 13 – Tarik Billa

How to add a column with a default value to an existing table in SQL Server?

January 9, 2024 by Tarik

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} WITH VALUES Example: ALTER TABLE SomeTable ADD SomeCol Bit NULL –Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol –When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)–Optional Default-Constraint. WITH VALUES –Add if Column is Nullable and you want the Default Value for Existing Records. Notes: … Read more

Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

January 9, 2024 by Tarik

I know most databases have restrictions, but Postgres doesn’t. You can run any number table creations, column changes and index changes in a transaction, and the changes aren’t visible to other users unit COMMIT succeeds. That’s how databases should be! 🙂 As for SQL Server you can run DDL inside of a transaction, but SQL … Read more

Twisted + SQLAlchemy and the best way to do it

January 9, 2024 by Tarik

In the intervening couple of years, Alex Gaynor created https://github.com/alex/alchimia which may be a better central repository for doing integration with SQLAlchemy and Twisted.

Whats the size of an SQL Int(N)?

January 9, 2024 by Tarik

It depends on the database. MySQL has an extension where INT(N) means an INT with a display width of 4 decimal digits. This information is maintained in the metadata. The INT itself is still 4 bytes, and values 10000 and greater can be stored (and probably displayed, but this depends how the application uses the … Read more

Curious issue with Oracle UNION and ORDER BY

January 9, 2024 by Tarik

This doesn’t really answer the question, but it seems to be a parser bug (or ‘feature’) rather than a language requirement. According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It’s also mentioned in community thread 3561546. You need a MOS account, or at least … Read more

Why is running a query on SQL Azure so much slower?

January 9, 2024 by Tarik

Updating and join on multiple rows, which row’s value is used?

January 8, 2024 by Tarik

I don’t think there are rules for this case and you cannot depend on a particular outcome. If you’re after a specific row, say the latest one, you can use apply, like: UPDATE a SET a.Value = b.Value FROM tableA AS a CROSS APPLY ( select top 1 * from tableB as b where b.id … Read more

How to store only time; not date and time?

January 8, 2024 by Tarik

You could try the INTERVAL DAY TO SECOND data type but it won’t save you any disk space … it is very suitable for this purpose though. create table t1 (time_of_day interval day (0) to second(0)); insert into t1 values (TO_DSINTERVAL(‘0 23:59:59’)); select date ‘2009-05-13’+time_of_day from t1; 11 bytes though.

Does SQLite support replication?

January 8, 2024 by Tarik

Brute force approach: Send it the “.dump” command to create a text representation of the data. Read that data in into the second database. Not sure you can use that. If you need a fine grained update (sending a copy of each upto the other copy), have a look at sqlite3_update_hook But how do you … Read more

Oracle SQL – max() with NULL values

January 8, 2024 by Tarik

max(end_dt) keep (dense_rank first order by end_dt desc nulls first) upd: SQL Fiddle Oracle 11g R2 Schema Setup: CREATE TABLE t (val int, s date, e date) ; INSERT ALL INTO t (val, s, e) VALUES (1, sysdate-3, sysdate-2) INTO t (val, s, e) VALUES (1, sysdate-2, sysdate-1) INTO t (val, s, e) VALUES (1, … Read more