Hot answers tagged

26

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; To find the collation of the database, you need to query ...


24

In general, procedures should not commit. Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete. If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction ...


24

For DML actions, there are row based and statement based triggers. Row triggers fire when (before, after or instead of) each row is affected (inserted/updated/deleted). So they will fire 100 times if 100 rows are affected and not at all if 0 rows are affected. Statement triggers fire when an INSERT / UPDATE / DELETE statement is executed. It doesn't matter ...


12

REPLACE does not play with wildcards that way. I think you meant: UPDATE [table] SET [column] = REPLACE([column],'TLD.com','TLD.org') WHERE [column] LIKE '%TLD.com%'; You have no WHERE clause, so it tried to update 618 rows, but it did not find any instances of %TLD.com% in that column. To see which rows should be affected, run a SELECT instead: ...


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

For statements that are allowed to execute within an explicit transaction (i.e. BEGIN TRAN, or heaven forbid IMPLICIT_TRANSACTIONS is ON), I am only aware of the following two that will not be affected by a ROLLBACK (though technically both are somewhat "cheating" in a sense): DML statements against table variables (variable, even table variables, do not ...


7

Question asked There is a built-in way to log all statements inside plpgsql functions: auto-explain LOAD 'auto_explain'; SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries SET auto_explain.log_nested_statements = ON; -- statements inside functions Details under this closely related question: Postgres query plan of a UDF ...


6

An INSTEAD OF INSERT trigger performs actions instead of what the original INSERT would have done. In your code, if either @appr_username or @appr_id is NULL, some sort of change is made to the base table (an insert or an update). Otherwise, nothing is done by the trigger (no rows affected), so the AFTER triggers are skipped. After all, SQL Server thinks, ...


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


5

fn_dblog is the way to look backwards as the other commentators have said. Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This ...


5

I can only speak for SQL Server, but the only way this could be done is through a DML trigger (as far as I know). Take the below for example: use TestDB; go create table dbo.TestTriggerTable ( id int identity(1, 1) not null, some_int int not null default 1 ); go insert into dbo.TestTriggerTable default values; go 1000 create trigger ...


5

Starting with 12c, we have DBMS_UTILITY.EXPAND_SQL_TEXT. You basically pass the query text through the first, input parameter, and receive the rewritten query back through the second, output parameter. But since you are on 11.2, you can not use this yet. You can however collect the optimizer trace. If you already ran your query, and have its sql_id: begin ...


5

From Coding the Trigger Body Detecting the DML Operation that Fired a Trigger If more than one type of DML operation can fire a trigger (for example, ON INSERT OR DELETE OR UPDATE OF emp), the trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to check which type of statement fire the trigger. Within the ...


4

To answer your question; WHY? You probably already know this by now since the post is 2 years old. But I'll respond just for the record. The reason #1 requires a commit and #2 doesn't is because the default database setting in Oracle is to commit a transaction when a session ends. If you are in sqlplus and run your code manually, it will not commit the ...


4

The best way to support multiple database types is to optimize the sql statements for each on of them. Otherwise you will one day end up with huge performance problems on one or the other database types. Additionally to that it's unfair for your customers to tell them "I support Oracle, DB2, MSSQL, etc.) and not optimizing the SQL for each one of them. Why? ...


4

You never set the SequenceId to anything. It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation. If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY ...


3

sys.sql_modules isn't going to help you - you won't know which one is causing a specific update, or who's calling it. Here's a start - create a logging table: CREATE TABLE dbo.UpdateLog ( EventType SYSNAME, Parameters INT, EventInfo NVARCHAR(MAX), EventDate DATETIME NOT NULL DEFAULT GETDATE(), AppName SYSNAME NOT NULL DEFAULT APP_NAME(), ...


3

Imposing such rules on db level can be very expensive in terms of resources, so I don't think there is a standard way which will work everywhere. I cannot suggest anything better than raising an error inside the body of BEFORE DELETE trigger (or AFTER DELETE, but BEFORE trigger seems to be more appropriate in this case). Unfortunately, it's quite RDMS ...


2

So, my suggestion as an actual answer: If you need it only in this function, you can do a RAISE LOG '%', your_statement;, or in your actual code: ... DECLARE exec_str text; ... --Set to NULL the contents of the current 'temp_' column exec_str := 'UPDATE '||dataset_1_row.table_name|| 'SET '||dataset_1_row.column_name||'=NULL ...


2

Normally in OLTP databases, transactions are so quick that we need not to place explicit transactions.Though internally MySQL take care of this and place implicit transaction. But if you think your scripts are long and it will take time to execute and if unrealistic/unwanted results can occur if failed in middle then you must apply explicit transactions.


2

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL UPDATE table1 A LEFT JOIN table2 B ON A.code = B.id SET A.enabled = 1 - ISNULL(B.id); Why should this work ? If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2 If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) because ...


2

Have you considered using a trace for that? https://technet.microsoft.com/en-us/library/ms191006%28v=sql.105%29.aspx You would be able to configure it for specific tables and store output in a table. I also have to point out that, with the method you are trying to use, you will only be able to capture the query used to change the table. In some situations ...


2

In Oracle when you create a table or execute an alter statement there is an implied commit. You may want to create the table in a separate function returning a boolean value with "PRAGMA AUTONOMOUS_TRANSACTION" then the calling procedure would get a true/false response as to whether or not the table was created. you will then be able to commit or rollback ...


2

If you just want to know number of index updates (regardless of how many rows affected, how wide the indexes are, etc.) and as long as you can be sure the service doesn't get restarted or indexes dropped/re-created in that timeframe, then you can look at sys.dm_db_index_usage_stats now and after a couple of days. As suggested by Kin, have a look at Nacho's ...


2

Rather than calculate the value in code, consider a computed column instead. ALTER TABLE dbo.Customers DROP COLUMN Id; ALTER TABLE dbo.Customers ADD Id AS (CASE CustomerType WHEN 1 THEN N'P' ELSE N'x' END + CAST((SequenceId + 1000000) AS nvarchar(10))); Below is an example of the insert proc using this computed column: CREATE PROCEDURE ...


2

This is an expected and documented behaviour Quote from the manual Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable In your second query the database apparently chose to run t2 before t1 and thus it returns 1. In the third query there have to be two different values, ...


2

Exactly why probably differs between the architecture of different DBMS, but scanning the primary key index is usually faster than scanning the table itself. I would say that the effect in the engine is similar to the effect in the network, the less data you shuffle between different areas in the engine, the faster it goes


2

It depends on the table and indexing layout, and the database engine you are using, but generally there are two reasons for the COUNT example to be faster: 1. Less pages need to be read. After finding which rows need to be returned due to the results of the join and filter operations, the query may still need to read data pages to find the values for the ...


2

As is suggested in the comments, you should change the data type to, for example, char(3). If that is not possible, you can transform the value of the column like: SELECT SUBSTR(DIGITS(CURRENCY_CODE),1,3) AS CURRENCY_CODE FROM ... or perhaps: SELECT DIGITS(CAST(CURRENCY_CODE AS DECIMAL(3,0))) AS CURRENCY_CODE FROM ... which would be the same as: SELECT ...


2

Common table expressions (CTE) of the same statement are all based on the same snapshot of the database. I.e. all sub-statements see the same state of underlying tables. Your DELETE tries to modify the same row(s) that your UPDATE already modifies. The manual addresses your case exactly: Trying to update the same row twice in a single statement is not ...


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