Hot answers tagged

26

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 a new table including the uuid column using CREATE TABLE AS Why? What causes large INSERT to slow ...


20

Since you already know which character is hidden, you can easily construct the current name using a variable, pass that into sp_rename, and then drop the table using the newly acquired name: DECLARE @t NVARCHAR(128) = N'dbo.Table' + NCHAR(31) + N'_Name'; EXEC sys.sp_rename @t, N'NewTableName', N'OBJECT'; DROP TABLE dbo.NewTableName; (Always be careful ...


16

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


15

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


14

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

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


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


10

Like @ypercube commented, check the CREATE TABLE page of the manual: There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in ...


10

TWO TABLES IN THE CURRENT DATABASE If you want to know if two tables are different, run this SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM ( SELECT column_name,ordinal_position, data_type,column_type,COUNT(1) rowcount FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name IN ...


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

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]


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


9

DDL (Data Definition Language): Used for defining data structures and schema like CREATE and ALTER commands. DML (Data Manipulation Language): Used for managing data with schema objects like SELECT commands. DCL (Data Control Language): Used to control data like REVOKE and GRANT commands. TCL (Transaction Control Language): Used to manage the changes made ...


8

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


8

All you need is configured ODBC connection to Oracle DB 1. Install Oracle Client SW (download from otn.oracle.com). I recommend installing same version as your Oracle DB 2. Define connection in tnsnames.ora 3. Create and test ODBC connection to Oracle DB in Control Panel > Administrative Tools > Data Sources (ODBC) - use Oracle's driver in your ORA_HOME, ...


8

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


8

I don't have a "best" answer, but I have a "least bad" answer that might let you get things done reasonably fast. My table had 2MM rows and the update performance was chugging when I tried to add a secondary timestamp column that defaulted to the first. ALTER TABLE mytable ADD new_timestamp TIMESTAMP ; UPDATE mytable SET new_timestamp = old_timestamp ; ...


8

You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id for those object types. DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + N' ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' DROP CONSTRAINT ' + QUOTENAME(c.name) + ';' FROM sys.objects AS c INNER JOIN sys.tables AS t ...


7

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


7

You didn't copy the full example and are concatenating NULL into the SQL command string which causes it to be NULL. If you do this: IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database') BEGIN DROP TRIGGER ddl_trig_database ON ALL SERVER; END GO CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS ...


7

This has nothing to do with object name reservations in TempDB or anything to do with runtime. This is simply the parser not able to follow logic or code paths that assures that your code couldn't possibly try to create that table twice. Notice that you get the exact same (non-runtime!) error if you just click the Parse button (Ctrl+F5). Basically, if you ...


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

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


6

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


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


6

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


6

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the chicken-and-egg problem of inserting a user and his active email, ...



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