Tag Info

Hot answers tagged

14

The reason why this works in PostgreSQL is that the system catalogs are regular tables. So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on. Since tables are transactional anyway, you'd almost have to go out ...


11

CREATE TRIGGER PreventCursorUDFs ON DATABASE FOR CREATE_FUNCTION AS BEGIN SET NOCOUNT ON; DECLARE @EventData XML = EVENTDATA(); IF LOWER(@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)')) LIKE N'%declare%cursor%fetch%' BEGIN RAISERROR('Yo, no cursors in functions!', 11, 1); ROLLBACK; END ...


11

I've seen this very issue and the hotfix that was ultimately released to fix it was actually a direct result of my case with Microsoft CSS. There is no public KB article for the fix. Please make sure you've applied Service Pack 4 and the most recent cumulative update to SQL Server (at the time of writing, that's Cumulative Update #3 (9.00.5259)). Until the ...


10

Yes. Transactions apply to DDL and span batches. I'd do something like this. Note the use of SERIALIZABLE ISOLATION to ensure full isolation and XACT_ABORT which will force a rollback on any error. SET XACT_ABORT ON GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO begin Transaction GO exec sp_rename LogTable, LogTableOld; GO CREATE TABLE dbo.LogTable( ...


10

To check for non-default collations on columns, you can use the following query: select table_schema, table_name, column_name, collation_name from information_schema.columns where collation_name is not null order by table_schema, table_name, ordinal_position; Edit: to find the collation of the database, you need to ...


9

The answer, at least on 11.2, is "It depends": This create is rolled back: create trigger trig_foo after create on schema begin raise_application_error(-20001, 'Dont do it!'); end; / -- create table foo as select level as id from dual connect by level<=10000; /* SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: Dont do it! */ ...


8

ALTER TABLE yourTable ADD CONSTRAINT constraintName DEFAULT ('XYZ') FOR [YourColumn] To change the default, drop the constraint and re-add it with the new value: ALTER TABLE yourTable DROP CONSTRAINT constraintName ALTER TABLE yourTable ADD CONSTRAINT constraintName DEFAULT ('ABC') FOR [YourColumn]


7

Most don't? Bummer. I principally use SQL Server and it does. I know Oracle doesn't but I thought Oracle might be an aberration. In SQL Server, I'm quite certain you can run multiple DDL statements in a single transaction although I also think there's a couple of restrictions (which I have all forgotten). You can do a create or an alter or a drop of ...


6

No there's no way of configuring SQL Server to do what you want to do. Under snapshot isolation the call to sp_tables gets blocked waiting for a shared key lock on one of the system base tables (sysschobjs) when doing a SELECT from sys.all_objects The Using Row Versioning-based Isolation Levels topic in BOL does say: SQL Server does not keep multiple ...


6

As the error message says, any partition-aligned unique index has to include the partitioning key in the index key. This requirement exists so the engine can enforce uniqueness on updates without checking every partition. In your case, this means including OrderDate in the nonclustered index key, or having a non-aligned index. Both are potentially valid ...


5

You probably need trace flag 1118 See Paul Randal's myths about tempdb first, and his TF 1118 article too The TF is described here in KB 328551 I have no direct experience of this but it sounds like what I've read


5

I presume that you've already split out your TempDB data-files to try to alleviate contention (via pre-production first obviously). If you're braver, consider the trace flag that Paul Randal authoratively refers to: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx In ...


5

Oracle has shared query parsing, so a SELECT * FROM table_a done by one session is (normally) the same as that of another session. That would break if one session thought there was ten columns in the table and another thought there were eleven.


5

In SQL Server we can rollback DDL statements, it's not using auto commit at the end of the statement. In other DBMS I don't know, but I remember that in Oracle one can't do the same. I believe it's specific to each DBMS, not sure what would the SQL standard say about this, but I'm sure no producer implements 100% the standard. There's a similar question on ...


5

Advice Never manually apply database changes; use scripts that are in a version control system (VCS). After initial deployment, store the "delta" scripts in the VCS. When upgrading, apply all scripts that are missing. Tracking the scripts that have been applied is a standard problem. Solutions There are tools to automate this task, including: Liquibase ...


5

The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes. Oracle clearly classifies TRUNCATE as DDL in the Concepts Guide, but DELETE as DML. The main points that put TRUNCATE in the DDL camp on Oracle, as I understand it, are: TRUNCATE can change storage parameters (the NEXT parameter), and those are part ...


5

With no data in the column you can just drop it and add it again. alter table MyTable drop column MyColumn; go alter table MyTable add MyColumn float; If you have data in your column and it makes sense to convert the values to a float value you can rename the column, add a new column, move the data using convert and then drop the old column. exec ...


4

To address your concerns about BIT: You can set your BIT column to NOT NULL. You can use -1 when setting a BIT column to "true". You can create a view that translates to -1, but +1 should be fine anyway unless your application explicitly checks for the numeric -1 (anything but zero should yield true in your client language). CREATE TABLE dbo.foo(bar BIT ...


4

You will need VIEW DEFINITION on the database and that will give you access to the DDL. USE <database>; GRANT VIEW DEFINITION TO <user>;


4

I believe it will mean the same thing for DDL as it does for DML. The msdn article on the topic actually gives you a pretty clear idea under the SERIALIZABLE section: This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction. Basically as long as your transaction is running, no DDL can be performed on ...


4

If you are still looking to track this down, I recently had a similarly strange performance issue with synchronous table drops. If you have large numbers of databases (> 100 or so) on a sql instance running SQL 2005 and you have lots of temp table create and drop statements you can get slow temp table drops. Checking row count returned from ...


4

The reason you're seeing this result is that SQL Server is not actually catching your ALTER TABLE error. You'll notice that when you run this, you see the red error message rather than a printed line--you can verify this by changing print @@error to something like print 'HELLO!'; in that case, you will NOT see 'HELLO!' printed; you will see the error ...


4

The error is happening because the error being thrown part of a recompile error due to deferred name resolution. Looking at SQL BOL those aren't trapped when they happen at the same level as the try...catch. However, if it's happening at a different level, either as dynamic SQL or a SP call, then it will get caught and rolled back. Using Profiler you can ...


4

You might want to have a look at Liquibase - according to the Liquibase documentation, it can perform diffs (I'm not 100% sure whether this requires using Liquibase to version your schemas, though).


4

re 1: that's the default value you define when you create the table: create table foo ( id integer default 42, last_modified timestamp default now() ) Details are in the manual: http://www.postgresql.org/docs/current/static/ddl-default.html http://www.postgresql.org/docs/current/static/sql-createtable.html re 2: an index can be defined on ...


4

There is no such mechanism in PostgreSQL. However, you can still avoid the excessive effects of such a table change. The following statement acquires an access exclusive lock on the table for the duration of the statement/transaction: ALTER TABLE your_table ADD COLUMN new_column integer NOT NULL DEFAULT 0; This statement changes the catalog, then ...


4

This article explains how DDL statements work and how they need exclusive table locks, which plays out nicely when there are transactions running against the table. To summarize: If the table cannot be locked due to another transaction having a lock on the table ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired would be ...


3

It's all about clustered vs non-clustered indexes actually. Primary key has nothing to do with it. A clustered index IS the data. The actual table data is contained in the data pages of the clustered index. If you put a clustered index on a table then the data is stored where you tell the clustered index to be. If you changed your code above to be a ...


3

Depending on the version of Oracle and whether you have the appropriate privileges (FLASHBACK ANY TABLE) and if the change was relatively recent, SELECT text FROM dba_views AS OF TIMESTAMP( systimestamp - interval '1' hour ) WHERE owner = <<owner of view>> AND view_name = <<name of view>> will give you the text of the view ...


3

Don't use DDL triggers. Use Event Notifications instead. Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled. You get notified only after the original DDL committed. If the DDL rolls back, you don't get any notification. It seems to be exactly what you need.



Only top voted, non community-wiki answers of a minimum length are eligible