Tag Info

Hot answers tagged

18

It very much depends on the details of your requirements. If you have sufficient free space (at least 110% of pg_size_pretty((pg_total_relation_size(tbl))) on disk and can afford a share lock for some time and an exclusive lock for a very short time: Create the new table including the uuid column with a column default. CREATE TABLE tbl_new ( tbl_uuid ...


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 ...


12

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( ...


12

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 ...


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 ...


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

You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair. @Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. ...


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! */ ...


9

Like all coding standards, every project is going to have preferences. I am not saying you will like my preferences but I will offer them. Keep in mind that LedgerSMB is heading towards a model where half of the code will be SQL so we have had to put some thought into these. The first thing we did was decide that DDL should be documented for ...


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 ...


7

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 ...


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

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 ...


6

Why isn't it caught? Because TRY / CATCH doesn't catch all types of errors. But you can get it to catch the non-system-critical stuff by wrapping it in an EXEC(), such as: BEGIN TRY EXEC('ALTER TABLE...'); END TRY The error in the ALTER will fail out to the EXEC, which in turn will return to the TRY / CATCH block reporting a simple, catchable error. ...


6

As you already have data in the table, you cannot add a NOT NULL field, because the existing data will already violate the NOT NULL constraint. You therefore have 2 choices: a) Add the column with a DEFAULT value... ALTER TABLE dbo.Tmp_tbl_post_category ADD NgoId INT DEFAULT 1; ...then run an update statement to correct the values to what they should ...


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

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text. It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL. The script you need ...


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

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

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 ...


5

Make all identifiers snake_case Don't use keywords for identifiers. Name function params with a leading underscore to avoid ambiguities with column names. Use := for assignment in PL/pgSQL instead of = (both are allowed but := is official) Maximum identifier length is 63 bytes. PostgreSQL will silently truncate if too long, so don't make them any longer ...


5

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 ...


5

You can give VIEW DEFINITION at the object, schema or database level. GRANT VIEW DEFINITION ON dbo.objectname TO [user/role]; GRANT VIEW DEFINITION ON SCHEMA::schemaname TO [user/role]; GRANT VIEW DEFINITION ON DATABASE::databasename TO [user/role]; http://msdn.microsoft.com/en-us/library/ms173848.aspx


5

If you can trust the IDENTITY column to be ever-increasing, I see no reason to store this information. It becomes redundant because it's information you can obtain from data already stored in the table (as you have already shown). Storing it is wasteful in terms of: additional disk space additional memory, since the data will hopefully be in memory most of ...


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

Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem. e.g. DECLARE data CLOB; objType varchar2(30) := 'TABLE'; objSchema varchar2(30) := 'SCOTT'; objName varchar2(30) := 'EMP'; fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql'; ...


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 ...



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