Hot answers tagged

18

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


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

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


7

You do manipulate and restrict data: GROUP BY, ORDER BY, TOP, JOIN, lock hints, etc You just don't change the database state when you do. It boils down to whether you read "manipulate" to include "change state"


7

DML can be considered to exclude SELECT statements. The Wikepidia.org entry for “Data Manipulation Launguage” describes it as follows: The purely read-only SELECT query statement is classed with the 'SQL-data' statements2 and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it ...


7

ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything. Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL. Of course as each company seeks to differentiate their products, they will develop additional ...


5

At one time or another I've worked with all of the databases you mention. Unfortunately I have found that it doesn't take very long at all for the syntax to deviate in to their own flavours, for anything other than the simplest SELECT, INSERT, UPDATE and DELETE. When it gets to some of the categories you suggest it will quickly get vendor specific. I've ...


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

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


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

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


4

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


3

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


3

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

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


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

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


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

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


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

I think I just figured it out... MySQL must count the number of rows that will be updated, not all the ones that will be generated by the join that happens during the update. The difference being that if there are many [B] to one [A], and many [C] to one [B], joining all would create n([C]) rows, but updating [B] would only count n([B]). Adding a set for ...


2

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


1

From reading the documentation, it looks like DML on a synonym for a normal object will not be replicated. For example, you have a table called FOO & a synonym called BAR that points to FOO. An INSERT into FOO will succeed, but an INSERT into BAR will not, as it is a synonym for another table & not a real base table. Test this in your dev ...


1

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


1

Just tossing it out there for discussion, even tho it could've likely been a comment as well. This is why most people like using the various frameworks nowadays like EntityFramework and LINQ, where all the underlying abstraction of querying is relegated to the application layer, and the datalayer is just flat storage. Note that this gives another benefit ...


1

update table1 set flag_column = 1 where exists (select 1 from table2 where table1.columnx = table2.columnx and table1.columny = table2.columny) This will update every row in table 1 where the x, y column combination exists in table2.


1

We use ApexSQL Log for auditing our production databases. It can show you who and when has deleted or updated the records. It also tracks inserts and Create/alter/drop statements for data objects. It's good that you have copies of the database and the transaction logs when these changes took place, as ApexSQL Log can read these transactions from the ...


1

Every time you have perform something atomically you should use transactions. Please understand that (depending on the language/framework/ORM), MySQL may be in auto-commit mode, which means that, functionally speaking, if you are using InnoDB something like this: INSERT INTO innodb_table VALUES (1, 2, 3); gets converted internally into: START ...



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