Hot answers tagged

45

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


38

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 the referenced table. ...


36

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


34

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


29

You can reset the identity value by DBCC CHECKIDENT('tableName', RESEED, 0) So next time you insert into TableName, the identity value inserted will be 1. When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case. Now when you truncate the table, it will reset the ...


23

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


23

Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table, the columns are actually materialized from sys.system_internals_partition_columns, which prevents all the values from having to be written (until they are ...


22

Kin has shown you how you can reset the IDENTITY value, but outside of a development environment when you're really removing all of the data, why do you need to do this? I hope you are not intending to maintain a contiguous sequence of IDENTITY values when you are in production. And I hope you aren't really writing your code to hard-code the IDENTITY ...


22

It is absolutely not a requirement in this very specific case, but it is a requirement in many other scenarios. If you're creating a database called Sales, and you arelady have a database called Sales, you'll need to change your database context before you: Restore with replace; or, Drop the current database and then: Create from scratch; or, Create for ...


21

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


19

You can create stored procedures that reference objects that don't exist yet (e.g. tables and functions). You cannot create stored procedures that reference columns that don't exist yet in objects that do already exist. This is the double-edged sword of deferred name resolution - SQL Server gives you the benefit of the doubt in some cases, but not all. See ...


19

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


17

USE msdb; GO CREATE USER shims FROM LOGIN shims; GO ALTER ROLE SqlAgentUserRole ADD MEMBER shims; GO Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


17

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


17

I believe it's not a requirement that you should use the master database to create a database. Since the create database command should be run in a database context the documentation always uses a default database which is master and it's a system database which will be always there no matter what so the script doesn't fail!


17

Generally speaking, no. SQL Server compiles the whole batch at the current scope before execution so referenced entities have to exist (statement-level recompilations may also happen later). The main exception is Deferred Name Resolution but that applies to tables, not columns: Deferred name resolution can only be used when you reference nonexistent table ...


15

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


15

You can use the syntax below; CREATE TABLE old_table_name ( id serial, my_data text, primary key (id) ); CREATE TABLE new_table_name ( like old_table_name including all, new_col1 integer, new_col2 text ); The fiddle is here


14

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


14

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


14

You've forgotten about other types of constraint than primary key (also applies to unique, check, and foreign key constraints) but that's basically it. A column-level constraint can only reference the column that it is declared next to. A table-level constraint can reference multiple columns.


14

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


13

One sure way to speed up an ALTER TABLE is to remove unnecessary indexes Here are the initial steps to load a new version of the table CREATE TABLE s_relations_new LIKE s_relations; # # Drop Duplicate Indexes # ALTER TABLE s_relations_new DROP INDEX source_persona_index, DROP INDEX target_persona_index, DROP INDEX ...


12

The correct answer depends on the version of the MySQL engine you're using. If using 5.6+, renames and adding/removing indices are performed online, i.e. without copying all the table's data. Just use ALTER TABLE as usual, it'll be mostly instant for renames and index drops, and reasonably fast for index addition (as fast as reading all the table once). ...


12

The way I prefer to do this: put the function in a utility database, and create a synonym to it in each regular database. This way you get the best of both worlds: there is only one copy of the object to maintain the developer doesn't have to provide three- or four-part names e.g. USE UtilityDB; GO CREATE FUNCTION dbo.LastIndexOf(...) ... GO USE otherDB; ...


12

Is this what you're looking for? BEGIN TRANSACTION; ALTER TABLE foo ADD b varchar; EXEC sp_executesql N'UPDATE foo SET b = CAST( a AS varchar )'; ALTER TABLE foo DROP COLUMN a; COMMIT;


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

ALTER TABLE .. DROP COLUMN ... marks the column as deleted in the system table pg_attribute. The table itself is not otherwise manipulated until rows are rewritten some way or another. The drop itself is very fast, but it does take a brief ACCESS EXCLUSIVE lock. I would not call that "downtime", though. Actually reclaiming disk space is the tricky part. You ...


11

The syntax for inline index declaration was added in SQL Server 2014, though that is was absolutely unclear in the official CREATE TABLE documentation. After speaking to the documentation owners, that topic now accurately reflects that inline index syntax is only valid starting with SQL Server 2014 (and some variations in 2016): The other instances, ...


11

You would need to use dynamic SQL, and the EVENTDATA() function. USE master GO CREATE TRIGGER trg_DDL_ChangeCOllationDatabase ON ALL SERVER FOR CREATE_DATABASE AS SET NOCOUNT ON; DECLARE @databasename NVARCHAR(256) = N'' DECLARE @event_data XML; DECLARE @sql NVARCHAR(4000) = N'' SET @event_data = EVENTDATA() SET @databasename = @event_data.value('(/...


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