Postgres - syntax error near IF when using inside Execute I am using an after update trigger in order recognize and log any changes made to records in the 'tasks' table. The column, and new and old value are all dynamic, so different every time.

This is what the trigger function looks like right now:

CREATE OR REPLACE FUNCTION fn_tasks_after_update()
RETURNS trigger
AS $BODY$
DECLARE
    ri RECORD;
    col_name TEXT;
BEGIN
    FOR ri IN
        SELECT column_name FROM information_schema.columns WHERE table_name = 'tasks'
    LOOP
        EXECUTE
            'IF $1.' || ri.column_name || ' <> $2.' || ri.column_name || ' THEN
                INSERT INTO tasks_log (task_id, type, "column", old_value, new_value)
                VALUES ($1.id, $$update$$, $3, $1.' || ri.column_name || ', $2.' || ri.column_name || ');
            END IF;'
        USING NEW, OLD, ri.column_name;
    END LOOP;
    RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fn_tasks_after_update()
OWNER TO postgres;

The trigger worked before adding the IF, and the syntax does not seem any different from what is in the 'INSERT INTO tasks_log' block.

This does work:

EXECUTE
    'INSERT INTO tasks_log (task_id, type, "column", old_value, new_value)
    VALUES ($1.id, $$update$$, $3, $1.' || ri.column_name || ', $2.' || ri.column_name || ');'

This does not work:

EXECUTE
    'IF $1.' || ri.column_name || ' <> $2.' || ri.column_name || ' THEN
        INSERT INTO tasks_log (task_id, type, "column", old_value, new_value)
        VALUES ($1.id, $$update$$, $3, $1.' || ri.column_name || ', $2.' || ri.column_name || ');
    END IF;'

What is going wrong?

This is the solution thanks to Abelisto's comment:

CREATE OR REPLACE FUNCTION fn_tasks_after_update()
RETURNS trigger
AS $BODY$
DECLARE
    ri RECORD;
    is_changed BOOLEAN;
BEGIN
    FOR ri IN
        SELECT column_name FROM information_schema.columns WHERE table_name = 'tasks'
    LOOP
        EXECUTE 'SELECT $1.' || ri.column_name || ' <> $2.' || ri.column_name
        USING NEW, OLD
        INTO is_changed;

        IF is_changed THEN
            EXECUTE
                'INSERT INTO tasks_log (task_id, type, "column", old_value, new_value)
                VALUES ($1.id, $$update$$, $3, $1.' || ri.column_name || ', $2.' || ri.column_name || ');'
            USING NEW, OLD, ri.column_name;
        END IF;
    END LOOP;
    RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fn_tasks_after_update()
OWNER TO postgres;


Are autovacuum VACUUMs slower than a manual VACUUM? I have a few large tables that take days to autovacuum, sometimes resulting in poor performance until the vacuum finishes.  Most of the recommendations I've seen mention doing a manual VACUUM during off-hours so that the autovacuum VACUUMs have less work to do, but if autovacuum takes days to finish is there any way to squeeze a manual VACUUM in (given a 24-hour peak activity cycle)?

I assume a manual VACUUM would still take days to finish?  Or is my assumption incorrect?

Related: Do the different "types" of autovacuum have different performance characteristics?

"Sort-of".

Automatic VACUUM may run at a lower intensity than manual vacuum, so it pauses longer between each batch. autovacuum_vacuum_cost_delay may be set differently to vacuum_cost_delay; by default there is no delay on manual vacuum, only on autovacuum.

Autovacuum also works harder to avoid blocking concurrent queries and will abort its work if another query tries to take a lock that would conflict with vacuum. Depending on the vacuum phase this may not actually slow it down much, or it might throw away substantial work. The first phase heap sweep marking dead tuples and setting the visibility map doesn't care much if it's aborted part-way, though it might cause a subsequent vacuum to repeat some of the reads. It's much more expensive when we're updating indexes, but that's done in batches to reduce the amount of work lost if a vacuum is canceled.

See autovacuum configuration for the relevant parameters, most notably autovacuum_vacuum_cost_delay. Note that this differs from the vacuum_cost_delay setting used by manual vacuum.


Removing superfluous tables with a synchronization? I'm using SQL Server 2008 r2's replication functionality to update my subscriber database through a transactional pull subscription.  When I mark it for reinitialization, it does fix the schema and data of any modified local tables that exist in the publication snapshot, but it doesn't remove any new tables (and presumably SPs, triggers, etc.) that have been added.  Is there any way to get the synchronization to remove superfluous objects like tables that don't exist in the publication shapshot, in addition to updating and adding existing objects?

No, there's no way to do this using native functionality as replication doesn't know about the additional objects so it can't remove them.


Select not repeated results from same tables in different databases I have 3 tables in 3 different databases which are exactly the same. I'm trying to sync from this 3 databases to a master database as shown in the picture below:

Right now I am running the same script (made in PHP) and changing all the time the slave database (in the figure db1, db2, db3 act as slaves) and I want to change that. I have the following query that returns data from user table:

SELECT
  u.id AS uid,
  u.username AS username,
  u.firstname AS ufirstname,
  u.lastname AS ulastname,
  u.email AS uemail,
  u.isactive AS uisactive,
  u.lastmodifieddate AS ulmd,
  t.id AS tid,
  t.name AS tname,
  t.lastmodifieddate AS tlmd,
  ut.lastmodifieddate AS utlmd
FROM `territory` AS t
JOIN `userterritory` AS ut ON (ut.territoryid = t.id)
JOIN `user` AS u ON (ut.userid = u.id)
WHERE u.lastmodifieddate > some_date_value
   OR ut.lastmodifieddate > some_date_value
   OR t.lastmodifieddate > some_date_value

But as I said before I want to run the same query in the 3 databases at the same time and not get repeated results? Any help?

There are a few tables included on the query as you may notice but think that 3 databases are exactly the same and can share different or the same data in some cases so use or don't the tables as reference on your answer. DB's are running on the same server and share the same credentials

SELECT ... FROM db1.user
UNION DISTINCT
SELECT ... FROM db2.user
UNION DISTINCT
SELECT ... FROM db3.user
;

The DISTINCT will de-dup based on what you have in the select: ...


Get ALL information about spatial index using SQL, including CELLS_PER_OBJECT and USING GEOGRAPHY_AUTO_GRID When I create a spatial index I can specify some extra parameters, e.g.
CREATE TABLE dbo.Test(
    Id bigint not null,
    Polygon geography not null
)
GO

CREATE SPATIAL INDEX IX_Test_Polygon ON dbo.Test
(
    Polygon
)
USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 12)
GO

If I then go to SSMS and script this index, then the script will contain these USING GEOGRAPHY_AUTO_GRID and WITH (CELLS_PER_OBJECT = 12). However, the view sys.indexes does not contain this information.
How can I get this information using SQL, not SSMS.

This information can be found in the system tables, sys.spatial_indexes and sys.spatial_index_tessellations. More info for these system tables at https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/spatial-data-catalog-views?view=sql-server-ver15.


Is it possible to catch the value of any field in a table? and once catched, can it be inserted into another table? SQL I'm trying to catch a field from a table (User), for example the Id, to be inserted in another table.

Example:

DECLARE @tempUSERNAME NVARCHAR(1000) = 'HERE USERNAME'
DECLARE @tempUSERID NVARCHAR(1000) = 'HERE USERID'

SELECT Id, UserName FROM Users WHERE UserName = @tempUSERNAME;
GO

INSERT INTO Person(Id, FirstName, LastName, UserId) VALUES
(newid(), 'FIRST NAME', 'LAST NAME', @tempUSERID);
GO

And the error that I have is:

Must declare the scalar variable "@tempUSERNAME".

But is it possible, or could I catch it with a subquery?

The GO utility statement marks the end of a batch of T-SQL statements.

The variables you declared at the top of the first batch go out of scope after the first GO statement.  So you can't use them anymore in the 2nd batch (where the INSERT statement is).

To solve that, just remove the GO statement.

As far as your other goal, you can store the Id that goes with that UserName by changing your SELECT statement to this:

SELECT @tempUSERID = Id
FROM Users
WHERE UserName = @tempUSERNAME;

Notice the variable assignment in the SELECT clause.


How can I get the hierarchical values from the below query? I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID.

I need to get all the category IDs and their subcategory IDs comma separated. For Example - if parent category ID of 10 is 1 and if parent category Id of 20 is 10 then when I print category ID 20 I need to print both 1 and 10 as its parents in comma separated values.

I tried the below query but I get NULL for ParChild column. Please help.

;WITH
  cteReports
  AS
(
SELECT c.CategoryID,
       c.fParentCategoryID,
       [level] = 1,
       ParChild=cast(CAST(c.fParentCategoryID AS VARCHAR(200)) + ',' + CAST(c.CategoryID AS VARCHAR(200)) AS VARCHAR(MAX))
FROM   retail.Category c
WHERE c.fParentCategoryID is NULL
UNION ALL
SELECT c.CategoryID,
       c.fParentCategoryID,
       [level] + 1,
       ParChild = ParChild + ',' + CAST(c.CategoryID AS VARCHAR(200))
FROM   retail.Category c
        JOIN cteReports r
            ON  c.fParentCategoryID = r.CategoryID

)

SELECT *
FROM   cteReports cr

use this script to create and populate the table.
(note: there is a 30K limit to the question body..So I had to use pastebin to copy your code and reference it)

I was able to replicate your results with your example data.

Your issue is that in the "base" case of the recursive CTE (the first select statement) you are getting the records where fParentCategoryID is null and casting the fParentCategoryID to a character column ready to append the "children". However the 'string' is still a NULL value at this stage (try it with SELECT CAST(NULL AS VARCHAR(200)) or such like).

The second select statement then tries to append other string values to the NULL but by default, the database option "concat null yields null" says if you try to concatenate anything to a NULL value then you still just get NULL - rather than treat it as an empty string.

You could SET CONCAT_NULL_YIELDS_NULL OFF for this query although this is deprecated and will ultimately be removed in a future version of SQL Server (so you probably shouldn't be adding it to production code!)

A better way is in the first select statement, instead of casting the fParentCategoryID to a string, just cast the CategoryID - we already know the parent should be empty as they are the NULLs.

SELECT c.CategoryID,
   c.fParentCategoryID,
   [level] = 1,
   ParChild=cast(CAST(c.CategoryID AS VARCHAR(200)) AS VARCHAR(MAX))
FROM   retail.Category c
WHERE c.fParentCategoryID is NULL

If your data could have a mixture of null and non null values in that select statement, you could also use ISNULL around it to treat any nulls as empty strings.

It probably needs some cleanup on the ParChild value it produces as you get values like " ,461,464" so you might want to strip the beginning comma off.


Rearrange a string without using loop syntax So if I have a table with values:

ID
------
a1b2c3
ac2b31
db13a2

I want the result to look like this

Id
------
abc123
acb231
dba132

In other words, rearrange the string so that all of the characters appear first, followed by all of the numbers. Order is unimportant.

(And what I meant by no loop is, no traditional loop. I was looking at some CTE examples, but couldn't adapt them here.)

On SQL Server vNext you can take advantage of the TRANSLATE function to solve this. That version isn't generally available to the public yet but it might be useful one day or to inspire an answer from someone else.

SELECT
REPLACE(TRANSLATE(@ID, '0123456789', SPACE(10)), ' ', '')
+ REPLACE(TRANSLATE(@ID, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', SPACE(52)), ' ', '');

db fiddle link for your three examples in the question.

On SQL Server 2012 you could solve your problem by applying the REPLACE function 62 times but there may be a more elegant solution:

DECLARE @T table
(
    ID varchar(10) NOT NULL
);

INSERT @T
    (ID)
VALUES
    ('a1b2c3'),
    ('ac2b31'),
    ('db13a2');

SELECT
    -- Remove numerics
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        T.ID,
        '0', ''), '1', ''), '2', ''), '3', ''), '4', ''),
        '5', ''), '6', ''), '7', ''), '8', ''), '9', '')
    +
    -- Remove alphas
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        T.ID,
        'a', ''), 'b', ''), 'c', ''), 'd', ''), 'e', ''), 'f', ''), 'g', ''),
        'h', ''), 'i', ''), 'j', ''), 'k', ''), 'l', ''), 'm', ''), 'n', ''),
        'o', ''), 'p', ''), 'q', ''), 'r', ''), 's', ''), 't', ''), 'u', ''),
        'v', ''), 'w', ''), 'x', ''), 'y', ''), 'z', ''),
        'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''),
        'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', ''), 'N', ''),
        'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', ''), 'T', ''), 'U', ''),
        'V', ''), 'W', ''), 'X', ''), 'Y', ''), 'Z', '')
FROM @T AS T;


Removing unwanted NULL results when using Right join Currently I've got two different tables, one named "Student" and another named "Class". I'd like to pick out one SPECIFIC Classcode from the Student table, while also picking out all the available Classcodes in the Class table. So far the best result I got was by using RIGHT JOIN which kinda gave me what I wanted. However instead of just displaying the specific Classcode from Student which I asked for, it displays the rest of them as "NULL". Code I used is posted below:

SELECT Student.Classcode, Class.Classcode
FROM Student
RIGHT JOIN Class
        ON Student.Classcode=Class.Classcode
       AND Student.Username='test'
ORDER BY Student.Classcode;

This provides me with the following result:

As you can see, it gives me the Classcode linked to the username on the left side (INF2), while showing all available classcodes on the right side. However for those that are NOT linked with the specified username, it shows up as NULL. Is there any way for it to show my desired Classcode without including the results that DONT match with the set username?

This is a job for INNER JOIN, not RIGHT JOIN.

Inner joins give results only when there is a match in both tables.  Right and left joins give results for all rows in either the right-side table or the left-side table with NULL values in columns from the joined table when there is no match.

Here's a an example to help visualize it:

mysql> select * from a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.01 sec)

mysql> select * from b;
+------+
| id   |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)

With the INNER JOIN, only rows with matching values of the "id" column in both tables are returned:

mysql> select a.id, b.id from b inner join a on (a.id = b.id);
+------+------+
| id   | id   |
+------+------+
|    3 |    3 |
|    4 |    4 |
+------+------+
2 rows in set (0.00 sec)

With the right join, all the rows from table "a" are returned, with NULL for the values from table "b" when there is no matching row in "b":

mysql> select a.id, b.id from b right join a on (a.id = b.id);
+------+------+
| id   | id   |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL |
|    2 | NULL |
|    5 | NULL |
+------+------+
5 rows in set (0.00 sec)

If you really, really insist on using RIGHT JOIN for this, suppress the rows with NULL values by using WHERE.  Example:

mysql> select a.id, b.id from b right join a on (a.id = b.id) WHERE b.id is not null;
+------+------+
| id   | id   |
+------+------+
|    3 |    3 |
|    4 |    4 |
+------+------+
2 rows in set (0.00 sec)


SQL Server Express Instance Memory vs Memory Limit SQL Server 2014 express edition is published as having a memory limit of 1 GB.

What do the SQL Instance Memory Properties do with respect to this limit?
For example, I can set the minimum and maximum memory limits within the properties to 4GB and according to the task manager that instance will 'use' 4GB of system memory.

Am I better off limiting the memory properties to 1 GB or is there advantage in allocating >1 GB memory here.

Community wiki answer:

The 1GB limit is only for the buffer pool, SQL Server uses other memory pools as well so it is possible to see memory usage over 1GB.

Quoting from Memory configuration and sizing considerations in SQL Server 2012 and later versions

  Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). The rest of the caches in the SQL Server memory manager can use much more memory than is specified by these edition limits. For example, a SQL Server 2012 Express edition can use only a maximum size of 1.4 GB for its database cache. Other caches (such as the procedure cache, the metadata cache, and so on) can consume memory up to the size specified by the "max server memory" configuration.

For SQL Server 2016 SP1 onward, Express can also use an additional 352MB for columnstore segments per instance and another 352MB for in-memory OLTP (Hekaton) objects per database.

The maximum buffer pool size was also changed in the documentation from 1GB to 1410MB for SQL Server 2016, though this probably applies from SQL Server 2012 (where the big memory management changes were made).

Only the buffer pool, columnstore cache, and in-memory OLTP cache sizes are restricted by quota. Other cache stores and memory consumers in Express Edition can push overall memory usage up to the configured limit in the max server memory configuration setting.

See Also:

Why does SQL Server 2012 Express use 9.5GB of RAM on my server?
Does SQL Server 2008R2 Express actually enforce the memory and CPU limit?


SQL script Scope in Microsoft SQL Server Management Studio - runs against active DB or all DB's? I'm not very familiar with SQL Server, more familiar with MySQL.

I'm used to seeing everything in code, whereas SQL Server, some things are done with buttons. In MySQL, I'd execute USE testing_database and then I'd know my subsequent queries would execute against that database.

In SQL Server Management Studio, I see that my "Testing Database" is selected in the dropdown select. Does that mean that the queries in the script will execute ONLY against that database?

The script does not include any references to database names. It uses sp_msdependencies to populate a list of Views and then acts on that list. Will sp_msdependencies execute only against the current database?

There are several other databases besides the testing database that I do not want to touch with this test.

If you execute a  use database statement, subsequent queries will only execute against that db. However: this is only for the current tab.

In SSMS to see the connection context look in the bottom right corner of the current tab. You should see the current database name there.

Or you could query the current db with:

SELECT DB_NAME() AS [Current Database];

Note: it is possible to have multiple tabs open which can be connected to different databases or servers. If you have multiple tabs open and tiled it can get a bit confusing.

As an added precaution you could disconnect and close other tabs to prevent any accidents.

It is possible that a sql script can contain many sql statements, and could include another use database command which could change the connection part way through the script. You should read the script or search for this before executing scripts supplied by other people.

One last warning: it is also possible to connect to multiple database servers simultaneously using registered servers. However unless you deliberately chose to do this its extremely unlikely you would ever  encounter this even accidentally.


Update Table 1 taking value from table 2 I want to update Table 1 by taking the value from Table 2. Both the tables have Emp No which is a key. I want to update the Salary column in Table 1 with the value that is present in Table 2

UPDATE TEST.tbl1  --< first the table that will be updated
JOIN   TEST.tbl2
ON     tbl1.IDNo = tbl2.IDNo
SET    Salary = Salary (From Second Table)

When i tried this iam getting an Error.. It says Error at or before the TEST..

Please anyone help me

UPDATE tbl1, tbl2
SET tbl1.Salary = tbl2.Salary
WHERE tbl1.IDNo = tbl2.IDNo


Replace value using several criteria in another table Brief illustration :
If I want to replace a single string using multiple criteria from another table, I can use this statement :

-- assume table_2 has column 'value' containing records such as 'd', 'f','h'.
declare @x varchar(10)
set @x = 'abcdefghij'

Select @x = replace(@x,value,'') from table_2
Select @x
-- the result will be : abcegij.

Question :
How do I implement this if I have many initial strings i.e the 'abcdefghij' one from above example, stored in a table (table_1) and would like to be replaced for any occurrence of strings contained in another table (Table_2)

Edit :
DBMS being used in this question is sql server 2008 r2.
My current solution is somewhat similar to what JoseTeixeira has proposed i.e using cursor, manually iterating each record in table_1, process it using above snippet and then store each result into a temporary table. In other words, this temporary table will hold all records from table_1 which have been cleansed (replaced for any string occurrence) using "dictionary" in table_2.

And perhaps this is the most important thing I have missed when asking this question.. I'm looking for a solution without using cursor iteration.

Creating the schema - Tables that will be used for the example:

DECLARE @table_1 TABLE (
    someText VARCHAR(10)
)
INSERT INTO @table_1 values ('abcdefghij')
INSERT INTO @table_1 values ('fajiefhda')
INSERT INTO @table_1 values ('hurineajsd')

DECLARE @table_2 TABLE (
    value VARCHAR(10)
)
INSERT INTO @table_2 values ('d')
INSERT INTO @table_2 values ('f')
INSERT INTO @table_2 values ('h');

Procedural code starts here. First a temporary table is used to get the replace strings numbered so we can iterate on them:

DECLARE @table_2_numbered TABLE (
    RowNum int,
    value VARCHAR(10)
);

INSERT INTO @table_2_numbered
SELECT ROW_NUMBER() OVER (ORDER BY value ASC) AS RowNum, value
FROM @table_2;

The following CTE will pick up the original strings that we want to update (@table_1) and will iterate through each row of the table with the replacing strings, which are now numbered (@table_2_numbered), and recursively apply them to replace every desired occurence. Notice we keep track of the original strings so we know which replaced strings correspond to which original strings:

WITH CTE (originalText, replaced, iteration)
AS
(
    SELECT someText AS originalText
        , someText AS replaced
        , 1 AS iteration
    FROM @table_1 t1
    UNION ALL
    SELECT CTE.originalText
        , CAST(REPLACE(CTE.replaced, value, '') AS VARCHAR(10)) AS replaced
        , iteration + 1 AS iteration
    FROM CTE, @table_2_numbered t2
    WHERE t2.RowNum = iteration
)
UPDATE t1 SET t1.someText = CTE.replaced
FROM @table_1 t1, CTE
WHERE CTE.iteration = (SELECT MAX(RowNum)+1 FROM @table_2_numbered)
    AND t1.someText = CTE.originalText

SELECT * FROM @table_1

And here are the results:

someText
--------
abcegij
ajiea
urineajs


SELECT id, name, empid
    MAX(c_lan)    AS c_lan,
    MAX(Java_lan) AS Java_lan,
    MAX(Dot_lan) AS Dot_lan
FROM tbl
GROUP BY id, name, empid;

If you really need no instead of NO, then add LOWER(...).

Edit.

I am assuming a table structure something like

 CREATE TABLE Lans (
     // id NAME empid c_lan Java_lan Dot_lan
     id SMALLINT UNSIGNED NOT NULL,
     name VARCHAR(50) NOT NULL,
     empid SMALLINT UNSIGNED NOT NULL,
     c_lan    VARCHAR(10) NULL,
     java_lan VARCHAR(10) NULL,
     dot_lan  VARCHAR(10) NULL,
     PRIMARY KEY(id, name)
 ) ENGINE=InnoDB;


Wise to use trigger to update another table? I have an Object table which is populated from an Integrated service (which I can change if needed) from another database. At certain points we need to manually add posts in another table ObjectObjectGroup (ObjectId, ObjectGroupId) which is needed if Object.ObjectType have a certain integer value. Since the integration service doesn't handle that kind of update, I'm thinking of adding a trigger to the Object table which in pseudo-code would be the following:

if Object.ObjectType = 10
    begin
        if Object.ObjectNumber like '<string pattern>'
        begin
            insert into ObjectObjectGroup values...
        end
    end

Is this setup wise, or is there a better way in terms of performance?

Mostly Copy/Pasting my response from this question on stackoverflow

Triggers can be very alluring, when you first start using them they seem like a magic bullet to all kinds of problems. But, they make "magic" stuff happen, if you don't know the database inside out, it can seem like really strange things happen (such as inserts into other tables, input data changing, etc). Before implementing things as a trigger I'd seriously consider instead enforcing the use of an API around the schema (preferably in the database, but outside if you can't).

Some things I'd still use triggers for

Keeping track of "date_created" and "date_last_edited" fields
Inserting "ID"'s (in oracle, where there is no auto id field)
Keeping change history

Things you wouldn't want to use triggers for

business rules/logic
anything which connects outside of the database (eg a webservice call)
Access control
Anything which isn't transactional ( anything you do in the trigger MUST be able to rollback with the transaction )


SQL Server Error log - strange date The dates in the SQL Server error log look very strange.
A subset for messages between Sep/29/2014 8:00 am and Sep/29/5:40pm show a date of Feb/16/2014. Have you seen this before? Any idea why the date would look different?

Given that at 2/16/2014 12:23:46 AM the error message states that the instance has been using a process ID since 9/19/2014, I would suspect the system clock was rolled back after the instance startup. If this would be true we would also expect SSPI handshakes to fail due to clock difference between parties, and sure enough there are SSPI failures logged while the clock was adrift.


Postgres Slow ROW_NUMBER() function I have created the following table in postgres.

test=# \d leaderboard_scores;
                                        Table "public.leaderboard_scores"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 id         | integer                     |           | not null | nextval('leaderboard_scores_id_seq'::regclass)
 user_id    | character varying(45)       |           | not null |
 score      | integer                     |           |          |
 created_at | timestamp without time zone |           |          |
Indexes:
    "leaderboard_scores_pkey" PRIMARY KEY, btree (id)
    "leaderboard_scores_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
    "score_back_user_id" btree (score DESC) INCLUDE (user_id)

My table size is 2M rows

My Use cases are:

To fetch users with max scores (this is running fast)
To get the rank of a particular user

To acheive number 2 I was hoping to use row_number window function but that is running very very slow.

The query I am using

select user_id, row_number() over (order by score desc) from leaderboard_scores order by score desc offset 500000 limit 20;

This query is taking around 900 ms which is too much to get a rank for a given user.

                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=52083.47..52085.56 rows=20 width=49) (actual time=1074.712..1074.755 rows=20 loops=1)
   ->  WindowAgg  (cost=0.43..208332.50 rows=1999999 width=49) (actual time=0.086..1043.536 rows=500020 loops=1)
         ->  Index Only Scan using score_back_user_id on leaderboard_scores  (cost=0.43..178332.52 rows=1999999 width=41) (actual time=0.074..807.340 rows=500021 loops=1)
               Heap Fetches: 500021
 Planning Time: 0.097 ms
 Execution Time: 1074.783 ms
(6 rows)

How can I optimize to get a user's rank?

You say you want the rank of a particular user, but that is not what your query does.

  Heap Fetches: 500021

Vacuum your table to reduce the heap fetches.  If the table is updated a lot, you may need to make changes to its autovac parameters to keep autovacuum running on it often enough.

For example,

alter table leaderboard_scores set (autovacuum_vacuum_scale_factor =0);
alter table leaderboard_scores set (autovacuum_vacuum_threshold =<rel_pages / 10>);

Where <rel_pages / 10> needs to manually computed and the numeric value plugged in.


mySQL Quickly find rows linked to another row For example if I have a posts table with id and a likes table width id post_id user_id, how can I set up the database to organize the likes table by post_id in order so queries are faster? I can't make the post_id the primary key because there are more than one likes for each post.

Presumably, any given user can only like a given post once, so the likes table does not need and should not have an id column... Instead, define that table with PRIMARY KEY (post_id,user_id).  The likes for a given post will be quickly retrievable using the leftmost column of the primary key for lookups and you then have automatic prevention of duplicate likes for the same post by the same user as a secondary benefit.


Does Amazon Redshift support MVCC? According to documentation, Amazon Redshift is derived from PostgreSQL 8.

PosgreSQL 8 supports Multiversion Concurrency Control (MVCC) which is in my understanding achieved on physical level by appending new row versions rather than updating in place or marking rows for deleting rather than deleting rows in place. Advantages of MVCC are:

  MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading. PostgreSQL maintains this guarantee even when providing the strictest level of transaction isolation through the use of an innovative Serializable Snapshot Isolation (SSI) level.

Redshift documentation states that

  the specialized data storage schema and query execution engine that Amazon Redshift uses are completely different from the PostgreSQL implementation

Also, unlike Postgres which has row-based storage, Redshift is column-based store.

At the same time, Redshift has vacuuming which makes it similar to PostgreSQL. From Redshift documentation:

  Amazon Redshift does not automatically reclaim and reuse space that is freed when you delete rows and update rows. To perform an update, Amazon Redshift deletes the original row and appends the updated row, so every update is effectively a delete followed by an insert. When you perform a delete, the rows are marked for deletion, but not removed. The query processor needs to scan the deleted rows as well as undeleted rows, so too many deleted rows can cost unnecessary processing. You should vacuum following a significant number of deletes or updates to reclaim space and improve query performance.

In essence, this is similar to underlying MVCC mechanisms in Postgres. However, I wasn't able to find confirmation that Redshift supports MVCC. What I am looking for is confirmation that parallel read and write transactions on Redshift do not interfere because they operate on independent versions of rows, therefore the question:

Does Amazon Redshift support MVCC?

I found an answer in Redshift documentation soon after asking the question. The chapter is called Managing Concurrent Write Operations:

Amazon Redshift allows tables to be read while they are being incrementally loaded or modified.
In some traditional data warehousing and business intelligence applications, the database is available to users only when the nightly load is complete. In such cases, no updates are allowed during regular work hours, when analytic queries are run and reports are generated; however, an increasing number of applications remain live for long periods of the day or even all day, making the notion of a load window obsolete.
Amazon Redshift supports these types of applications by allowing tables to be read while they are being incrementally loaded or modified. Queries simply see the latest committed version, or snapshot, of the data, rather than waiting for the next version to be committed.

That corresponds to MVCC definition from Wikipedia:

MVCC [...] approach: each user connected to the database sees a snapshot of the database at a particular instant in time. Any changes made by a writer will not be seen by other users of the database until the changes have been completed (or, in database terms: until the transaction has been committed.)
When an MVCC database needs to update an item of data, it will not overwrite the old data with new data, but instead marks the old data as obsolete and adds the newer version elsewhere. Thus there are multiple versions stored, but only one is the latest. This allows readers to access the data that was there when they began reading, even if it was modified or deleted part way through by someone else.

Connected with Redshift vacuuming description that I posted in the question, it makes me believe Amazon Redshift does support MVCC. Although I still was not able to find any mention of MVCC in official docs for Redshift.


Estimating IO Requirements for Bursty Usage We have an application that queries a SQL database periodically throughout the day.  There are periods of zero or only light activity, interspersed with individual requests for relatively large amounts of data.  When those requests come in, the primary objective is to deliver the data quickly, and the secondary objective is to do that cost-effectively.  Due to the nature of the application, it is fairly unlikely that the data/indexes will have been cached in RAM from the previous query (different users, working on different portions of the data).

For a system that experiences relatively steady usage, I have heard the rule of thumb to observe the disk queue length and keep that number relatively small.  This will specifically run in AWS, where I have seen the rule of thumb that a disk queue length of 1 per 100 IOPS is reasonable.

How can I estimate the IO requirements for such a system?  Is the disk queue length a reliable indicator when dealing with individual, bursty queries?  Are there other metrics that I should consider?

The primary metric I've always considered for IO in SQL Server is not IOPs or Disk Queue Length, but disk throughput (sec/reads and sec/writes).  Overall, databases aren't about how many operations you can throw at a disk, but how quickly those operations are completed.  The general rule of thumb is to have less than 20ms/operation (though lower is always better).  More detail can be found in this article.

Disk Queue Length is a bogus stat and no longer relevant.  The problem with it is that the value measures the queue for a single drive, but now that we live in an age of RAIDs, SANs, and other distributed storage, there's no way to properly translate this value to a meaningful number.  A great starting place for performance metrics is this poster from Quest/Dell that gives you a lot of stuff and explanations for why or why not they are important.  You don't have to use all of them, but they are a start.

In order to test your IO, you have to understand your workload at its peak.  How many transactions and how much is cached?  Unless you know and have measured these, it's really hard to judge.  You can create work loads and use tools like SQLIO to test your storage, but you will need workload patterns in order to build a proper test.

Finally, a note on AWS:  To my knowledge, Amazon will not guarantee IO performance in AWS.  This is primarily because the storage is a massive shared resource and it's impossible to gauge the patterns of you and your neighbors on a particular area of storage (see the Noisy Neighbor problem).

My recommendation would be to allocate as much memory as possible.  SQL Server will only push stuff out of memory if it's under pressure and space in the buffer pool (based on LRU-K).  So if you're buffer pool can store most of the database in memory, you can mitigate some of the bursty performance.  Also, consider tactics that can keep cache objects "warm".  Finally, keep an eye on SQL 2014 and the new Hekaton feature.


SQL Server Express 2012 Repair I have a copy of SQL server Express 2012 database which may be corrupt.  When I tried to run a repair on it I was asked for the original installation media.  The Options were for an MSI or ISO image.  However, SQL sever express came as an exe file.
How do I go about repairing an express version ?

Run the setup.exe for express again and you can find the option to repair the installation on the from the installation screen instead of doing it through add and remove programs. You can also install 7-zip and use that to extract the setup file to get the msi files for the repair


Failed to import a mongodb database with duplicate fields I'm trying to import a mongodb database and I've been using the mongorestore command, the import starts without problems until it gives the error:
Failed: dbprod.mys_account: error creating collection dbprod.mys_account: error running create command: BSON field 'OperationSessionInfo.create' is a duplicate field

I already tried the option --drop but without success, I had never worked with mongodb ... I must clarify that I saved it in a mongodb 2.4 with mongodump and I try to import for a mongodb 3.6

I have successfully upgraded a database from 2.4 to 4.0 using the following 3 step method:

use 2.4 mongobackup on the 2.4 database
delete all .json files from the outputted dump directory
use 4.0 mongorestore on the remaining .bson files


Transactional Replication: When to delete the initial snapshot I would like to delete the initial snapshot after I setup transnational replication and everything is working fine.

When is it OK to the delete the snapshot that initializes a subscriber?

You dont have to manually cleanup the old/static snapshot files, the distribution cleanup agent will purge the old folders for you.

A bit of under the hood story of what and how snapshot files gets generated :

Basically, the snapshot agent creates snapshots for the subscribers to initialize. The snapshot folder will contain various types of files like -

.bcp => actual data files - either using native or character mode
.idx => Index creation statements
.sch => schema and table creation scripts
.dri => foreign and check constraints

These files are always kept in \\unc\folder with a timestamp value for the folder name. The folder name is the publication name.

So, everytime a snapshot is generated, a new timestamp value is used for the folder name and then the snapshot files are written to that folder.

Also, if you see that your distribution agent is not cleaning up the files, you can delete the older snapshot folders. But I would still troubleshoot - why distribution agent is not cleaning it up !!


Who can explain mongodb with ssl? I created certificates according to the instructions on the official website of MongoDB, but it doesn't work properly.

I have a replica set and self-signed certificates.

I need a full encryption of traffic between replica members. How do I properly set up these replica members? And how do I enter the MongoDB shell?

I created certificates according to the instructions, in
mongod.conf:

sslMode = requireSSL
sslPEMKeyFile = /etc/ssl/mongo.pem ...

and try the add the parameter:

sslCAFile =  /etc/ssl/mongo.crt

and tried to enter:

mongo --ssl --sslPEMKeyFile /etc/ssl/mongo.pem --sslCAFile /etc/ssl/mongo.pem or
mongo --ssl --sslPEMKeyFile /etc/ssl/mongo.pem --sslCAFile /etc/ssl/mongo.crt

I get the error -  The server certificate does not match the host name.

If I use the client certificate then error: SSL peer certificate validation failed:self signed certificate.

the Mongod service is up and running. Mongo version - 3.0.2. Centos - 6.5 x64.

I went through a lot of links but haven't found a working solution.

If it is possible can you please give me the steps?

When you create the self signed certificate, you need to use a valid host name in the "Common Name" field, e.g.:

  Common Name (eg, your name or your server's hostname) []: host.domain.com

If you want use a domain certificate, you need to change "host" with "*", e.g.:

  Common Name (eg, your name or your server's hostname) []: *.domain.com


Problem with PostgreSQL and the geqo optimizer I'm trying to compare the two execution plans (exhaustive search and geqo) from the optimizer, but I always get the same plan from both types.
These are the variables I use for geqo:

SET geqo = true;
SET geqo_threshold = 2;

My queries have 8-12 FROM items involved, so the optimizer would definitely use the geqo algorithm.

I tried to add more variables to my queries to force the optimizer to choose an other plan:

SET geqo_effort = 2;
SET geqo_pool_size = 2;
SET geqo_generations = 1;

But like I said, I always get the same plan as the plan from exhaustive search.
Is there any possibility to show which algorithm (exhaustive search or geqo) the optimizer used to create the execution plan? Or are there other ways to force the optimizer to use geqo?

I believe the settings you mentioned already ensure that geqo will be used, except that the values for geqo_pool_size and geqo_generations are so low that the results won't be good.

Apart from that, to get different plans, you should play with geqo_seed.

From the doc's section Generating Possible Plans with GEQO:

  As long as geqo_seed and the other GEQO parameters are kept fixed, the
  same plan will be generated for a given query (and other planner
  inputs such as statistics). To experiment with different search paths,
  try changing geqo_seed.

About geqo_seed:

  Controls the initial value of the random number generator used by GEQO
  to select random paths through the join order search space. The value
  can range from zero (the default) to one. Varying the value changes
  the set of join paths explored, and may result in a better or worse
  best path being found.


Installing Anti-Virus on Availability Group databases Is it worth the risk to use Anti-Virus on Availability Group databases? Even following the guidelines given by Microsoft in How to choose antivirus software to run on computers that are running SQL Server.

We are planning to install Kaspersky.

It is a department decision to have all servers installed with Anti-Virus.

Theoretically, whatever box it's on shouldn't be doing to sort of activity that leads to virus and malware. If you follow that guide things for the most part should be fine to install the software but it will still be using some hardware resources.

I'd recommend you just follow the Microsoft laid path. You may also want to talk to the software vendor to make sure they don't have any specific recommendations. Beyond that just beware that the software will still have processor overhead as well. You'll likely not want to have SQL Server maintenance jobs at the same time as whatever scanning this virus software scheduled to run. - zane


What are the differences between Mirroring and Failover Clustering and when is each one recommended over the other? What are the main differences between Mirroring and Failover Clustering?

What kind of problem each one solves and in what kind of scenarios is each one recommended over the other?

Mirroring is a technology that increases the high availability for user databases and creates a copy of the source db that is maintained in real time. This is done by delivering all the active transaction log records to the mirror server.

From MSDN:

"Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Database mirroring is supported in SQL Server Standard and Enterprise. "

Clustering is a technology that provides high availability for the entire SQL Server instance. This means that it's possible to have two servers with identical structure (OS, applications, services, service packs, patches...etc) and with shared storage (the data itself).

From MSDN:

"SQL Server failover clustering provides high-availability support for an entire SQL Server instance.  SQL Server failover clusters are built on top of Windows Server failover clusters. To create a SQL Server failover cluster, you need to first create the underlying Windows Server failover cluster."

Here's a course from the Microsoft Virtual Academy about High Availability as offered today by MS: Understanding Microsoft’s High-Availability Solutions.


Displaying user's password I need to know if there is a way in SQL*Plus to display the password of a certain username?

At my job, sometimes the developers ask me what happened to their account, and what their password is. When I try to display the username and password, the password shows as blank.

Could you please help?

What you are asking is impossible, unless you brute-force the password hash for the user.

How do you expect a system to be secure if passwords are stored in plain text?


Is it possible to pass parameters to pt-table-checksum using an options file? We're using Percona's pt-table-checksum for MySQL replication integrity check.
Is it possible to pass some or all of the options using some sort of configuration file?

For instance, can we write the list of tables we want checked in a file?
I understand the manual does not talk about such a file: http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html

Yes, you can do this.

pt-table-checksum --config FILENAME

Where FILENAME looks like

tables                  = table1,table2,table3
ignore-tables-regex     = ^tmp
chunk-time              = .2
max-load                = Threads_running=10
chunk-size              = 100000
user                    = myuser
pass                    = mypass

If --config is not specified, pt-table-checksum will read from /etc/percona-toolkit/pt-table-checksum.conf by default.  Nearly all Percona Toolkit tools have this ability.

Details on how the config files work is available here


Can we back up mysql databases by copying files? I tried to reinstall mysql on my machine, but it keeps failing. Everytime it fails, it does this locking and that locking and future effort would fail again.

Finally I decided to do truly clean install

Delete everything
Reinstall

The procedure is listed here : http://stuffthatspins.com/2011/01/08/ubuntu-10-x-completely-remove-and-clean-mysql-installation/

I do not want to lose data already there. Yea, it's backed up but I kind of like it there.

So, can I just mv content of /etc/mysql and then copy it back after I reinstall everything?

My programmer said it wouldn't work

If not, what else I should know?

I sort of forget where mysql store data. It's the default for ubuntu I guess.

Update: I tried that on some local databases using available computers. The Myisam table get transferred. However the innodb tables do not.

Also http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file shows that innodb won't get transferred as easily by just copying files. Am I missing something?

Another Update: The answer is yes we can. We did it :D

Update guys.

The short answer is yes we can. My programmer just did it. The key is to copy thew whole file and realize that innodb store database in one big file. Not sure why.

The other answer is better so I pick that (I don't think it's objective to pick my own answer). I just want to share that it works.

If you are able to shut your database server down (and my understanding is that you are indeed able to) - thereby disabling your application - then that's the easiest indeed.

You can, after shutdown, just copy+paste your data directory. Where is your data in the first place? Just issue:

SELECT @@datadir;

From within your MySQL server.

By the way, while you must backup your data someplace safe, I do not expect any of the apt-get or aptitude commands to actually touch your data.

NOTE that the page you've linked to has some pretty crazy ideas, like:

[!!DO NOT DO THIS!!] find / -iname ‘mysql*’ -exec rm -rf {} \;

I encourage you to look someplace else, given the above query. It will remove any and all mysql* file from your root filesystem. Including your own application's mysql-config.ini files (if any, of course), including libdbd-perl-mysql -- without clean uninstall... I mean, this is really BAD advice.

There's no point in backing up your my.cnf file, delete the file then put it back again. The same holds for your datadir.

Just uninstall MySQL and re-install is enough -- but that, too, is kinda funny, since both are done via apt-get -- so you basically reinstall the same version... At least get yourself newer version of MySQL.


Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app- For a new SASS app with postgres 9, that will support disconnected clients.

The client not will use the regular IDs, only the UUID. The clients will be on iOS, html5/python and perhaps later on other platforms. THe client database will be sqlite.

Each customer will use their own schema, so hopefully not will exist a too big table, but still because this is for invoicing & financial stuff a lot of data will be generated, and because the cloud hosting cost are tied to CPU/RAM overage charges make sense to have the best performance from the start (plus faster responses times is good for the end-user).

So,I need to use a UUID for support in the client to create new records. Now, I'm debating if use the UUID ONLY for the sync part and regular IDs for JOINS & primary keys.

However, also I feel is duplication, and if I will store the uuid why not drop the int keys at all in the server, but worry if this will give me performance issues later and if which index type I need to use (btree will be good enough?)

Of course, integer columns are smaller and faster to operate on. The data type uuid has a size of 16 bytes, integer has 4 bytes. Smaller indexes, faster JOIN operations, etc. But not a dramatic difference.

If you only ever query by UUID, additional integer IDs will give you nothing. Also, if your tables are small, the difference is negligible and you will do just as well without additional integer IDs.

If you have to join multiple tables on the ID, if you have foreign keys referencing the ID or if your tables grow huge (as well as your indexes), then operate with integer IDs internally.


Display department name,the number of employees and their names I have two tables (employees) (departments)

I want to generate the following output:

num_of_employees   department       employee_name
10                 marketing            john
10                 marketing            mark

so on and so forth.

I tried using this`

select count(employee_ID) as "num of employees", department_name, employee_name
from employees e, departments d
where e.dept_id = d.dept_id
group by department_name, employee_name

Without adding the emp name I get the correct solution, but with the employee I get 1 in the num of employees

You need to fetch the number of employees in a sub-query and then use this:

select
  q.total as "num of employees",
  d.department_name,
  e.employee_name
from
  departments d join
  employees e on d.dept_id=e.dept_id join
  (
    select
      count(dept_id) as total,
      dept_id
    from
      employees
    group by department_id
    ) q on d.dept_id=q.dept_id
group by d.department_name, e.employee_name


Get Position of row from query I have a database that looks like the following:

+-------------------+------------+-----------+-------+------------+------+
| SteamID           | Name       | Map       | Time  | Date       | id   |
+-------------------+------------+-----------+-------+------------+------+
| 76561198059749096 | BÃ¸lle     | surf_mesa | 51.74 | 1454877544 | 3757 |
| 76561198141936247 | SlachtHuis | surf_mesa | 51.76 | 1454783151 | 2110 |
| 76561198059749096 | BÃ¸lle     | surf_mesa | 51.83 | 1454876964 | 3729 |
| 76561198065863390 | dLGHT      | surf_mesa | 51.98 | 1455017166 | 4988 |
| 76561198059749096 | BÃ¸lle     | surf_mesa | 51.98 | 1454877206 | 3741 |
| 76561198065863390 | dLGHT      | surf_mesa | 52.02 | 1455017046 | 4986 |
| 76561198141936247 | SlachtHuis | surf_mesa | 52.03 | 1454839146 | 2838 |
+-------------------+------------+-----------+-------+------------+------+

This has thousands of results in for each map (Note the table isn't already sorted by time), what I'm wanting to do is select a SteamID and find his fastest time for the selected map. This would then return the total position out of all the other times.

So for example, I want to get 76561198065863390's position or rank out of all other times for surf_mesa. This would return a table or something similar to:

+-----+-------------------+
| pos | SteamID           |
+-----+-------------------+
| 4   | 76561198065863390 |
+-----+-------------------+

I started messing around with queries and came up with  this:

SELECT * FROM
(
    SELECT (@pos:=@pos+1) pos,Map,Time
    FROM `surf_times`
    WHERE `SteamID` = "76561198065863390"
    ORDER BY `Time` DESC
) `surf_times`
WHERE `Map` = "surf_mesa" LIMIT 1

Got myself confused in that query and seemed to get further away from the answer :)

This should work:

SELECT pos, SteamId
FROM
(
    SELECT (@pos := @pos+1) pos
        , Map, Time, Date, SteamID
    FROM `surf_times` S
        , (SELECT @pos := 0) p
    WHERE `Map` = "surf_mesa"
    ORDER BY `Time`
) `surf_times`
WHERE `SteamID` = "76561198065863390"
ORDER BY pos
LIMIT 1;

SQL Fiddle


SQL Server 2008 backup script I have a lot of SharePoint 2013 databases to backup and if update fail to  restore, so I want to automate this operations.

ALTER DATABASE [TEST_1] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

BACKUP DATABASE [TEST_1]
TO DISK = 'C:\path\test_1.bak' WITH NOINIT, STATS = 10
GO

ALTER DATABASE [TEST_1] SET ONLINE WITH ROLLBACK IMMEDIATE

But there is and error msg:

  Msg 942, Level 14, State 4, Line 1
  Database 'TEST_1' cannot be opened because it is offline.

  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

Is it possbile to switch database offline, make backup and turn it in online mode after this?

It is not possible, because you cannot create a backup of an offline database.

From MSDN:

  Without the NO_TRUNCATE option, the database must be in the ONLINE
  state. If the database is in the SUSPENDED state, you might be able to
  create a backup by specifying NO_TRUNCATE. But if the database is in
  the OFFLINE or EMERGENCY state, BACKUP is not allowed even with
  NO_TRUNCATE


Database Merging and Replication So we have seven servers, one for each of our locations, each server has the exact same schemas/table setup. We were making a reporting server to be able to look at the data from all sites at once. In order to do this at first what we did was a daily load of each server unto the reporting server. We added a SourceServerID column to each table and had that generated by the SSIS package. These were put into a database named ODS.

Our seven servers are going through an upgrade right now. With this upgrade changes are being made that will allow us to use replication instead of the daily load like we were before. With the replication we are creating a staging database on the reporting server for each of the seven servers naming them like SourceServer1. Then creating views on the ODS that do simple Select... Union... type of query to combine the data from all the SourceServerX databases.

This is causing the reports we run to go much slower (ex: from 5 min. to 2 hours).

Questions

Could the slow down be due to pulling from the multiple SourceServerX databases as opposed to the one ODS database like before even though these are all on the same instance of SQL Server?
If we combined the tables into one database and then added the SourceServer name to the front of the tables would the views then run faster?
With replication could we combine the tables from all the SourceServers into one set of tables adding in the SourceServerID column like we did for the daily load?
Is there a different method that would allow for better performance while maintaining the up to date data that replication provides?

Note: I had first put this on Stack Overflow (see https://stackoverflow.com/questions/45017951/database-merging-and-replication) but realized this might be a better location for this question.

Could the slow down be due to pulling from the multiple SourceServerX databases as opposed to the one ODS database like before even though these are all on the same instance of SQL Server?

Yes.

    If we combined the tables into one database and then added the SourceServer name to the front of the tables would the views then run faster?

Probably not.

    With replication could we combine the tables from all the SourceServers into one set of tables adding in the SourceServerID column like we did for the daily load?

Technically yes, but you would have to write custom replication procedures complicated to handle re-initialization.  So I wouldn't recommend it.

    Is there a different method that would allow for better performance while maintaining the up to date data that replication provides?

Replicate into separate tables/databases and periodically MERGE into a single ODS database.


Fatal NI connect errors I'm wondering if there are any auditing processes I can enable to see what is causing these errors, like sql statements being run, or any other metadata that I could use to track down the source of these errors?

I am aware of DBA_AUDIT_TRAIL (which uses SYS.AUD$), but it isn't giving me what I need.  Fine Grained Audit Trail may be what I need?  Not sure....

Here are my auditing settings for your review, anything I can change here to give me more to work with?

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------
audit_file_dest                      string      /01/daa01/oracle/db/admin/DAA/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

In addition to Phil suggestion, I'll activate tracing on sql net.

Just add:

TRACE_LEVEL_SERVER=USER

to: $ORACLE_HOME/network/admin/sqlnet.ora (create it if don't exist)

As for the listener, you can increase the tracing level using ADMIN or SUPPORT options instead of USER.
When you found the client who is causing it, you can enable client sql net traces as well.

Fatal NI connect errors are hard to determinate. But in my experience, most of them was caused by a client who left a session opened in TOAD, and then the computer goes to sleep mode.

note* If you have MOS, you should check note [1116960.1] and [1121357.1]


information_schema in this context is a string value, not an object name, so you need to enclose it in quotes like so:
SELECT *
FROM TABLES W
WHERE TABLE_SCHEMA NOT IN ('information_schema')


Index Maintenance on SQL Server 2008 R2 Web edition I've inherited of a couple of databases which indexes have never been maintained (rebuilt, reorganized).

I'm using Ola Hallengren's Index maintenance script, I've used the following script to record the difference between the pre-maintenance and post-maintenance values.

Script :

SELECT  OBJECT_NAME(IPS.object_id) AS [TableName] ,
    SI.name AS [IndexName] ,
    IPS.Index_type_desc ,
    IPS.avg_fragmentation_in_percent ,
    IPS.avg_fragment_size_in_pages ,
    IPS.avg_page_space_used_in_percent ,
    IPS.record_count ,
    IPS.ghost_record_count ,
    IPS.fragment_count
   -- ,IPS.avg_fragment_size_in_pages
FROM
    sys.dm_db_index_physical_stats(DB_ID(N'test_db'), NULL, NULL, NULL, 'DETAILED') IPS
JOIN
    sys.tables ST WITH (NOLOCK) ON IPS.object_id = ST.object_id
JOIN
    sys.indexes SI WITH (NOLOCK) ON IPS.object_id = SI.object_id
                                 AND IPS.index_id = SI.index_id
WHERE
    ST.is_ms_shipped = 0
ORDER BY
    3 Desc, 4 Desc

However, I didn't notice any changes on the various indexes having an avg_fragmentation_in_percent above 30%.

Are index maintenance tasks supported on SQL Server 2008R2 Web Editions?

For small tables (typically reduced pages count), fragmentation cannot be avoided due to how sql server handles extents.

More info : http://www.practicalsqldba.com/2012/04/sql-server-measuring-index.html


Can the SQL Server optimizer use indexes to update auto created statistics? Consider the scenario where auto created statistics are created on a column to satisfy a query.

Afterwards; a nonclustered index is created on the column, with or without other key columns.

Could the SQL Server optimizer leverage indexes to update these auto created statistics when running UPDATE STATISTICS statements?

Is there a difference between (default) sample rates & fullscan statistic updates when adding these indexes?

SELECT @@VERSION;

Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64)   Oct 18 2018 23:11:05   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

TL;DR

Auto created statistics will be improved by an index if you are using fullscan updates & have the column as the first key column in an index.

(Default) sample rate statistic updates will use the clustered index / heap table scan even when a NCI exists with the column as the first key column.

Even when updating the statistic of the NCI.

As such, updating statistics with a sample rate will not be positively impacted by adding an index.

Setup

Creating a table with a primary key & 10M rows:

CREATE TABLE dbo.testing(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, val nchar(500));

INSERT INTO dbo.testing  WITH(TABLOCK) (val)
SELECT top(10000000) REPLICATE('b',((ROW_NUMBER()OVER (ORDER BY (SELECT NULL))) % 400))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
CROSS APPLY master..spt_values spt3;

A nchar(500) value is used to show a clear difference between statistic updates.

We can create an auto created statistic on the val column with this query:

SELECT val
FROM dbo.testing
where val = 'bla'
order by val;

Full scan stat updates

Updating the statistic with fullscan:

SET STATISTICS IO, TIME ON;
UPDATE STATISTICS dbo.testing([_WA_Sys_00000002_4F5F3681]) WITH FULLSCAN;

Results in 127 seconds of cpu tme on my machine:

CPU time = 127141 ms,  elapsed time = 80147 ms.

Now when we look at the query that the stat updates actually runs:

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [val] AS [SC0] FROM [dbo].[testing] WITH (READUNCOMMITTED)  ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 2);

We can see that it went parallel on the two cores on my machine & did order by the val column.

As a side note, there was also a memory grant & tempdb was used to support the ordering, The memory grant was as high as 12484 MB or ~ 12 GB

Let's see what happens when we create this index:

CREATE INDEX IX_val
on dbo.testing(val);

And re-run the stat update:

SET STATISTICS IO, TIME ON;
UPDATE STATISTICS dbo.testing([_WA_Sys_00000002_4F5F3681]) WITH FULLSCAN;

This results in an execution time of

 CPU time = 10578 ms,  elapsed time = 10882 ms.

And the memory grant & tempdb usage is gone since it does not have to sort anymore.

The statistic update with fullscan on the auto created stat is able to use the index.

This would also work for an index like this:

CREATE INDEX IX_val_val2
on dbo.testing(val,val2);

But not when creating an index like this:

CREATE INDEX IX_val2_val
on dbo.testing(val2,val);

Sample rates

If we change the update stats to sample 10 percent instead of doing a fullscan:

UPDATE STATISTICS dbo.testing([_WA_Sys_00000002_4F5F3681]) WITH SAMPLE 10 PERCENT

Without an index this amounts to

CPU time = 10500 ms,  elapsed time = 11100 ms.

With an index this amounts to

CPU time = 10312 ms,  elapsed time = 11038 ms.

Auto created statistics with sample rates are not positively impacted by indexes on the column where the column is the first key column.
Why this happens is explained further below

Comparison with updating the index' stats

As expected, updating the statistic on the index takes the same amount of time as the auto created statistics.

UPDATE STATISTICS dbo.testing(IX_val) WITH SAMPLE 10 PERCENT

CPU time = 11203 ms,  elapsed time = 11849 ms.

UPDATE STATISTICS dbo.testing(IX_val) WITH FULLSCAN;

CPU time = 10938 ms,  elapsed time = 11005 ms.

Sampled statistic update on an indexed column stat

The query used when updating statistics with a sample rate of 10 percent is different:

SELECT StatMan([SC0], [SC1], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], step_direction([SC0]) over (order by NULL) AS [SB0000]
FROM (SELECT [val] AS [SC0], [ID] AS [SC1]
FROM [dbo].[testing] TABLESAMPLE SYSTEM (9.999986e+000 PERCENT) WITH (READUNCOMMITTED) )
AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

TABLESAMPLE will give us random pages and it's explanation can be found here.

The statistic orders by val,id,step_direction([SC0]) over (order by NULL)

We can trace the query plan of this StatMan query with the profiler & the Showplan XML Statistics Profile event:

UPDATE STATISTICS dbo.testing(IX_val) WITH SAMPLE 10 PERCENT

The execution plan shows the clustered index being used, instead of the expected nonclustered index:

I cannot share the query plan with paste the plan unfortunately, it is malformed.

The same is true for heap tables. The usage of the NCI would not make a difference here, the TABLESAMPLE SYSTEM (9.999986e+000 PERCENT) retrieves 10 percent of the data pages in a random order. This is why the CI was used.


cluster_database TRUE/FALSE what does it mean in Oracle RAC? I have 3 databases on an Oracle RAC (two nodes) one database which is running with CLUSTER_DATABASE=TRUE on both nodes, the other two runs on each node with CLUSTER_DATABASE=FALSE

what does it mean the CLUSTER_DATABASE value?
and if it is CLUSTER_DATABASE= FALSE, is it as a normal single node Oracle Database?
what happen to the database instance that runs as (CLUSTER_DATABASE=FALSE) when the node is offline? dose it goes online on the other node or it goes offline as well?

What the SIDs on all the nodes? Is this really the same DATABASE. For example you can have 3 node cluster having a single ASM on all the nodes, but database A is clustered on node1/node2. But database B runs only on the node 3.

The database B then is still started and stopped by the Clusterware, but is not clustered. You can also check output from:

ps -ef | grep -e lmd

The lmd process is started on clustered instances only.

EDITED: maybe I understand now. You do not have RAC database. RAC is active-active cluster. .i.e databases instances run on multiple nodes at the same time.

You have standalone instances guarded by Oracle Clusterware. So you have active-passive failover cluster. As described here :Using Oracle Clusterware to Protect
A Single Instance. Then you do NOT need the parameter cluster_database set to true. It only applies to RAC databases.


Opening a .backup file for PostgreSQL I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise.

In short, I have what I think is a PostgreSQL dump file that someone sent me, and I need to be able to open it up to extract the information. I was expecting a simple tabular format (such as an Excel spreadsheet), but I got something way more complicated (a .backup file). I know how to run some basic SQL queries, but I think whoever sent this to me strongly overestimated my knowledge of database programs. Could someone give me a very, very, VERY simple, completely 110% spoon-fed explanation on how to open this?

(As an aside, I know the person who sent this to me and I know the nature of the file, so I know it's not something completely different.)

You cannot just "open" a database backup. You have to restore the database. For this, you first need to install PostgreSQL. The latest version should be capable of taking backups from older versions, too. But older version may have problems with newer features in the backup. So your version should be at least as recent as the source.

How you install PostgreSQL depends on your OS that you cunningly kept a secret. In Debian it's basically a matter of:

apt-get install postgresql-8.4

(Or you take postgresql-9.1 from the backports.)

Depending on the format (there are various) you can then restore the backup. If the backup holds a complete database cluster (produced with pg_dumpall - ask your friend!), you can simply:

psql postgres -f /path/tp/file.sql

To view the data you would best use a GUI like pgAdmin. Or you learn how to use SQL. :)

For more advise you would need to provide more information. Like a short(!) sample of the backup and how it was produced.

For more about PostgreSQL start by reading the manual or one of the thousand pages on the web.

But really, your friend should just export the relevant data do a CSV file that you can open in Open Office Calc or some other spreadsheet application. Or even any text editor.


MySQL - Unable to Convert Primary Key to Auto Increment I have a MySQL table where the Primary Key (int) was set by the software to be equal to the Primary Key (auto incremented) of the main table. Because of a change in design I now need the field to auto increment. When I tried to ALTER TABLE it came back with the following:-

ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
SQL Statement:
ALTER TABLE `Clinic`.`ClientSupplementary` CHANGE COLUMN `idClientSupplementary` `idClientSupplementary` INT(11) NOT NULL AUTO_INCREMENT

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'ClientSupplementary' already exists

This has me totally puzzled as '1' is definitely unique as are all the other Primary Key values. Where should I be looking?

Your post intrigued me. There is a "workaround" here, but I was curious, so I performed the following tests. You might like to try what worked for me below before trying the workaround. If there are FOREIGN KEYs, and potential conflicts (duplicates and/or KEY violations), then this won't work. It may be the reason for your error.

I created a table (bill) as follows.

mysql> CREATE TABLE bill(fred INT, paul VARCHAR(10));
Query OK, 0 rows affected (0.31 sec)

Then inserted some sample values.

mysql> INSERT INTO bill VALUES(1, 'adsfd');
    Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO bill VALUES(2, 'adsfd');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO bill VALUES(3, 'xxxx');
Query OK, 1 row affected (0.05 sec)

I then performed a SHOW CREATE TABLE My_Table\G (the \G gives clear output that can be cut and pasted).

mysql> SHOW CREATE TABLE bill\G
*************************** 1. row ***************************
       Table: bill
Create Table: CREATE TABLE `bill` (
  `fred` int(11) DEFAULT NULL,
  `paul` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Just to see the results of my INSERTs

mysql> SELECT * FROM bill;
+------+-------+
| fred | paul  |
+------+-------+
|    1 | adsfd |
|    2 | adsfd |
|    3 | xxxx  |
+------+-------+
3 rows in set (0.00 sec)
mysql>

Now, issue your ALTER TABLE statement.

ALTER TABLE bill MODIFY COLUMN fred INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Recheck the definition of your table.

mysql> SHOW CREATE TABLE bill\G
*************************** 1. row ***************************
       Table: bill
Create Table: CREATE TABLE `bill` (
  `fred` int(11) NOT NULL AUTO_INCREMENT,
  `paul` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`fred`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And the field fred is indeed now a PRIMARY KEY and also an AUTO_INCREMENT.

And to check that it's working.

mysql> INSERT INTO bill (paul) VALUES('yyyy');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM bill;
+------+-------+
| fred | paul  |
+------+-------+
|    1 | adsfd |
|    2 | adsfd |
|    3 | xxxx  |
|    4 | yyyy  |
+------+-------+
4 rows in set (0.00 sec)

So, it's also AUTO_INCREMENTing correctly. I'm not sure why your original statement gave the strange ALTER TABLE causes auto_increment resequencing error - maybe a bug of some sort? While performing these tests, I made a number of small errors - MySQL appears to be very fussy about the exact order of steps and the particular syntax of the ALTER TABLE statement.


DataGrip Issue Load Data Local Infile I am having trouble using the LOAD DATA LOCAL INFILE statement in Datagrip. Suppose I have source_data.csv such as:

rating,statement
-2,"I'm hungry!"
5,"I'm satisfied."
1,"Ok, but could be better."

After running the following code block in DataGrip:

# loading_sample_table.sql

USE sample_db;

DROP TABLE IF EXISTS sample_table;
CREATE TABLE sample_table (rating int, statement varchar(50), id serial);

LOAD DATA
    LOCAL INFILE 'absolute/path/to/my/source_data.csv'
    INTO TABLE sample_table
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY  '\''
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (rating, statement);

I receive the following error:

[42000][1148] The used command is not allowed with this MySQL version

Running SHOW VARIABLES LIKE "%version%"; in either MySQL shell or DataGrip produces

+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.18                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.18                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | macos10.14                    |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+

Then, if I try the same same command above but just drop LOCAL from the code block, I receive the following error:

[HY000][13] Can't get stat of 'absolute/path/to/my/source_data.csv' (OS errno 13 - Permission denied)

Also, I checked SHOW GLOBAL VARIABLES LIKE 'local_infile'; and can see:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

However, if from my terminal I run:

mysql --local-infile=1 -uroot -p < 'absolute/path/to/my/loading_sample_table.sql'

After entering my root password, the file runs successfully. Ideally, I would like to do everything in DataGrip. Unsure on how to debug this issue.

I was able to add the following parameter to the server-side connection string and it worked without having to downgrade the driver version:

jdbc:mysql://localhost:3306/?allowLoadLocalInfile=true


Slow MYSQL performance due to sub queries Here is my query:

SELECT BAL,NAME,
   (SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR ,
   (SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS CR
FROM main
GROUP BY MARK,NAME WITH ROLLUP

This query takes at least 20 seconds and sometimes even up to 45 seconds when  ‘DATE’ increases(example:2012-05-01).

There are about 500,000 records in the transaction table. After hours of testing I think the problem might be with the 2 sub queries:

(SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR ,
(SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS CR

I tried adding an index to DATE and TNAME, it speeded things up a little bit, but still not enough.Would be great if I could atleast shave off atleast 10 more seconds
Is there anything I could do to make it faster?

Table details:

 CREATE TABLE ` transaction` (
  `DATE` DATE NULL DEFAULT NULL,
  `DOCNO` VARCHAR(10) NULL DEFAULT NULL,
  `TCODE` VARCHAR(6) NULL DEFAULT NULL,
  `TNAME` VARCHAR(50) NULL DEFAULT NULL,
  `CR` DECIMAL(12,2) NULL DEFAULT NULL,
  `DR` DECIMAL(12,2) NULL DEFAULT NULL,
   INDEX `TNAME` (`TNAME`),
   INDEX `DATE` (`DATE`),
   INDEX `CR` (`CR`),
   INDEX `DR` (`DR`)
  )
  COLLATE='latin1_swedish_ci'
  ENGINE=InnoDB;

  CREATE TABLE `main` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `CODE` VARCHAR(50) NOT NULL,
    `NAME` VARCHAR(70) NULL DEFAULT NULL,
    `BAL` DECIMAL(12,2) NULL DEFAULT '0.00',
    `MARK` VARCHAR(35) NULL DEFAULT NULL,
    PRIMARY KEY (`CODE`),
    UNIQUE INDEX `NAME` (`NAME`),
    PRIMARY KEY (`id`),
    INDEX `MARK` (`MARK`)
   )
   COLLATE='utf8_general_ci'
   ENGINE=InnoDB
   ROW_FORMAT=COMPACT
   AUTO_INCREMENT=545451;

Of course it is the subqueries' fault. For each row of table main, this query goes through the whole transactions table twice (once for each subquery). That's a lot of load.

How about a simple join? This should have the same results and take much less than 50% of your processing time even with no indexes.

SELECT main.BAL,main.NAME,SUM(transaction.DR),SUM(transaction.CR)
FROM main
LEFT JOIN transaction on transaction.TNAME = main.NAME and transaction.DATE < '2000-05-01'
GROUP BY main.MARK,main.NAME WITH ROLLUP


MS Access Data Type for time in hh:mm:ss format My database contains three different sporting events with finishing times imported from an Excel spreadsheet in the hh:mm:ss format.

How do I customize the Access data type field to accept this format?  Additionally, I then need to add three different event times per participant to create a total time, but need assistance in the format.

You can store the data in Excel as the amount of time from the default date.  If you just enter the time in the format you wish, it will automatically add the default date.  Enter "4:30:00" as in [FinishTime] and it will save the data like in [Unformated Date], but will be visible format you wish and retain the proper time value.

When you create the table in Access you can just change the format to change how it displays itself.  Note, in access Minutes are stored as "nn" instead of "mm", distinguishing it from Months.

It will look the way you want.  Note, although the unformatted default date will appear different in Access than it will in Excel, the time will remain relative to the default start point.

From there, simply import your Excel into the Access table normally or enter the time in the same "hh:mm:ss" directly into the Access table.

When you want to see the data summed up, you can do so in a query, making sure you convert it to a date and format it to display how you wish.

In this case:

Format(CDate(Sum([FinishTime])),"hh:nn:ss")

Resulting in the total time that you wish

If you anticipate the total time going over 24 hours, there are more things to consider, but it doesn't sound like this project will.


T-SQL query - how to AVG() the SUM() of columns' rows to a new row column? What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.

How should the query look like to achieve this, since the AVG function is an aggregate one that is retrieving the vertical average column-wise but not the horizontal. Is there an elegant way to achieve this by a query?

Could you please elaborate the below query on the screenshot or modify it so that it works? Is there an elegant way to make this using AVG and COUNT() only without the trivial sum/count?

I have the following solution on the screenshot to extend more so that it does the job but for me it seems not to be correct:

Please find the below code (my trivial solution as a sum and division by the number of the summed fields):

USE [Students]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Students](
 [StudentID] [int] NOT NULL,
 [StudentName] [nchar](100) NOT NULL,
 [StudentSurname] [nchar](100) NOT NULL,
 [Biology] [decimal](3, 2) NOT NULL,
 [Maths] [decimal](3, 2) NOT NULL,
 [Geography] [decimal](3, 2) NOT NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
 [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (1, N'Mike                                                                                ', N'Manson                                                                                ', CAST(2.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(1.00 AS Decimal(3, 2)))
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (2, N'Timo                                                                                ', N'Torn                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (3, N'Jeffrey                                                                                ', N'Jones                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(3.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO

SELECT * FROM Students;

SELECT
(SELECT Biology + Maths + Geography)/3 AS Mark_average
FROM Students;

Here is the output of the above query:

Mark_average
1.666666
2.333333
2.666666

Is there an alternative and more elegant/sophisticated way (similar to what is on the screenshot, by e.g. a subquery using AVG() of SUM() function) to retrieve the result?

Thanks much in advance!

If you are looking for a different solution, you could always unpivot the data, aggregate and then join back on the source table.  Here is an example.

Also, thank you so much for including sample table and data to work with, so much easier.

;WITH CTE_Unpivot AS
    (
    SELECT StudentID, MarkSubject, Mark
    FROM dbo.Students AS S
        UNPIVOT (Mark FOR MarkSubject IN (Biology, Maths, [Geography])) AS upvt
    )
, CTE_Average AS
    (
    SELECT StudentID, AVG(Mark) AS StudentAverage
    FROM CTE_Unpivot AS U
    GROUP BY StudentID
    )
SELECT S.StudentID
    , S.StudentName
    , S.StudentSurname
    , S.Biology
    , S.Maths
    , S.[Geography]
    , A.StudentAverage
FROM dbo.Students AS S
    LEFT OUTER JOIN CTE_Average AS A ON S.StudentID = A.StudentID

I feel the need to point out that this is less efficient than just adding the fields together and dividing them.  Manipulating the data like this touches it multiple times, which means that it is more likley to spill to tempdb and have a higher cpu cost.  Probably negligible, but still.


Does MariaDB-Galera Cluster support LAST_INSERT_ID and mysql_info? As far as I understand, Galera does not support table locking, but what information can be retrieved about the last query?

  LOCK/UNLOCK TABLES cannot be supported in master-master replication. Lock functions (GET_LOCK(), RELEASE_LOCK(),…) are not supported.

What about LAST_INSERT_ID()?  At some point I read, that the nodes use different auto increment values, but it said nothing whether the last insert id will be returned.

And a related question: In PHP I use the mysql_info function to retrieve the "rows matched" on UPDATE.  Is this information expected to be availible in a Galera cluster?

I know this is a really old question, but yet both are supported just as they are in standard mysql.


Why keeps Oracle writing into WARNING_SETTINGS$? For tests we have Oracle 11 instance running on a RAM disc. Since the RAM disc is limited in size the database must not do any auditing or anything that keeps increasing the size of anything.

We managed to fix many such leaks but have one left which we don't know how to handle:

The database tries to write to the table

WARNING_SETTINGS$

resulting (after a couple of weeks) in a

ORA-01653: unable to extend table SYS.WARNING_SETTINGS$ by 8 in tablespace SYSTEM

while trying to create a database object (a trigger right now)

We tried to disable all writes to that table with this statement

begin
  dbms_warning.add_warning_setting_cat('ALL', 'DISABLE', 'SYSTEM');
end;
/
commit;

The questions are:

What is the purpose of that table?

Why does Oracle keep writing into it?

How do we make Oracle stop doing that?

Oracle inserts into warning_settings$ when you compile an object that produces a compilation warning.

I traced an object creation with (ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL') & without (ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL') warnings enabled to show this.

With warnings set, we can see the following SQL in the trace file:

PARSING IN CURSOR #5 len=94 dep=1 uid=0 oct=2 lid=0 tim=1334927684277374 hv=2385338619 ad='d891dc48' sqlid='fdvfrjy72us7v'
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
END OF STMT
PARSE #5:c=0,e=250,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1334927684277373

Interestingly, ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL' only seems to suppress the output of the warnings to SQL Plus. A trace still shows the table being populated by Oracle:

oracle@ironforge:/u01/app/oracle/diag/rdbms/phill11g2/PHILL11G2/trace$ tail -f PHILL11G2_ora_26759_PHIL_DBA.trc | grep -i warning
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'
delete from warning_settings$ where obj# = :1
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  WARNING_SETTINGS$ (cr=2 pr=0 pw=0 time=0 us)'
STAT #6 id=2 cnt=1 pid=1 pos=1 obj=254 op='INDEX RANGE SCAN I_WARNING_SETTINGS (cr=2 pr=0 pw=0 time=0 us cost=1 size=5 card=1)'
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)

Test PL/SQL snippet used for this & the warning it generated:

PHIL@PHILL11G2 > ALTER SESSION SET sql_trace = true;

Session altered.

PHIL@PHILL11G2 > ALTER SESSION SET tracefile_identifier = PHIL_DBA;

Session altered.

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Session altered.

PHIL@PHILL11G2 > CREATE OR REPLACE PROCEDURE phil_1
  2  IS
  3  BEGIN
  4    NULL;
  5  END;
  6  /

SP2-0804: Procedure created with compilation warnings

PHIL@PHILL11G2 >
PHIL@PHILL11G2 > show errors;
Errors for PROCEDURE PHIL_1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1  PLW-05018: unit PHIL_1 omitted optional AUTHID clause; default
     value DEFINER used

PHIL@PHILL11G2 >


Table valued function VS Stored procedure There's a function that :

returns only 1 row
has only 1 parameter
has multiple joins on different tables, each only to  return a single scalar value
is called only once at each login of a web application

It currently is written in c# linq-to-sql and takes 150-300ms to execute.

After re-writing it in SQL using a very simple SELECT statement, it takes 30ms to execute.

Now I want to encase this select in a database function, what is the best (fastest execution) way to do so ?

Make a view and at every call do something like  select * from viewXX where id=123
Make a stored procedure
Make a table-valued function

Are there any tricks I could take advantage of in such a case ?

Here is an example of what the query looks like

declare @id int=162;
select top 1 i.id, i.name, itd.description,
(select top 1 case when count(*)>0 then '1' else '0' end from category where category_desc=itd.description) "HasCategory",
(select top 1 number from wheels where wheel_item_id=i.id) "WheelCount"
from items i left items_desc itd on i.id=itd.itemid where i.id=@id

If the data changes infrequently, package the fast-running SELECT as a view and define an index on it.

An indexed view comes with some restrictions which minimally limits flexibility in future changes. There is nothing terribly bad, however.

If the data changes very often, and updates are performance-critical, the extra time to maintain the view may be a concern, but no more than for any other index.


How can I specify the DDL of the index that is implicitly created when creating a materialized view log? When I create a materialized view log, Oracle implicitly creates an index on that log. For example, when creating a materialized view log on table foo, Oracle creates the view log table mlog$_foo and the index i_mlog$_foo. Oracle creates the index in the default tablespace, and I need it on a different tablespace. I can't change the default tablespace for the user. How can I specify the DDL (or at least the tablespace) for the index?

This is in fact a bug: Materialized View Log (Mlog$) Index Not Created in its Default Tablespace (Doc ID 1959658.1). Excerpt:

  The index created when a materialized view log is created appears to be created in the user's default tablespace rather than the same tablespace as the MV log table.
  Additionally, if the user has no quota on their default tablespace, the index is not created and an error is not reported, leading to possible performance issues.

There is no workaround stated though, and no indication that you can force the tablespace yourself (the create materialized view log statements has no such option).

The solution section of that article states that it will be fixed in 12.2 (currently scheduled for first half of 2016), but that you can request a backport from Oracle support. So the best course of action might well be to open an SR and request that backport or some other workaround.

The only possible workaround that I know would be to change the default tablespace for the user before the mview log creation, then change it back. This is obviously not good – who knows what other objects could be created in the mean time – but might be all you've got.

@a_horse_with_no_name has different suggestion: rebuild the index after the creation is done. This allows you to change the tablespace (and other storage options):

SQL> alter index "I_MLOG$_FOO" rebuild tablespace YOURTBS online ;


How should I store changes to items in the database? I have items (tracked with itemid) that have attributes assigned to them (field1,field1 for example).  At any given time, field1=val1, field2=val1, ..., fieldn=valn where val1,val1,...,valn = 1..100. and n = 1..20

I need to design a DB to track the changes for each field, and I'm not sure if I should list each field as a column (for example table1), or list each change as a row (for example table2).

create table1 (datetimeofchange datetime2, itemid bigint, field1 smallint, field2 smallint, field3 smallint, field4 smallint, ..., fieldn smallint)

create table2 (datetimeofchange datetime2, itemid bigint, fieldval smallint, changeval smallint)

create table2lookup (fieldval smallint, fieldname varchar(50))

What are the trade-offs I get from choosing between the two designs?  Is there another design which would have different trade-offs?  Is there some terms I can google to research this issue?

Thanks.

I'm no expert but I see couple of potential pros and cons.

So if you use table1, and you list each row for after every data change, what will happen? Well if you have lot of small updates like only changing one field at a time, then you will have a lot of redundant data from the unchanged fields. If you only list the fields that have changed and leave the rest NULL, it might be tricky to figure out what your data was like, but not a bad solution. Table1 is ideal for changes to multiple columns

Table2 on the other hand is the opposite. Let's say you update 10 fields for 1,000 rows. Then in table 2, you get 10,000 rows. Each data change also ends up in 10 rows. If you are updating only one field at time for the most part, it will be quite comparable to table1. Table2 works better with updates that only affect one or tops a few columns at a time.

Personally, I'd use the table1 approach. It's simpler and more straightforward. If you do update multiple columns, it only records one row. And if you record only when a field is changed, then you eliminate storing redundant data although your scripts to figure out what the data changes were might be a more complex. On the other hand, if you only have maybe a few hundred thousand rows, then storing redundant data might not be so bad, but if you have a hundred million rows, then you might have some issues.

Hope this helps at least a little bit.


Does restoring a database in Microsoft SQL Server add objects to Resource Database? As far as I know the resource database contains information on all objects in its server instance, so I would think it's only natural that when you restore a database from a backup file, part of the process would be to register its objects in the server's resource database but I can't find any concrete information on the subject and I'm unable to test it locally.

Well, reading the documentation for Resource Database (highlighting mine)

  The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server
  system objects, such as sys.objects, are physically persisted in the
  Resource database, but they logically appear in the sys schema of
  every database. The Resource database does not contain user data or
  user metadata.

Given the above and the fact that it is READ-ONLY, I don't see how restoring a database updates the resource database.


Designing One-To-One Relationships To begin with, I have to admit that I don't have great experience in designing database schemas. Being influenced by OOP , I find that by dividing the info we have about a object into different entities, we achieve better info organization.

For example , let's say we have a basketball player. For each player we have some general info like their name , their age , the position they play etc. We also have some info about their performance during his whole career (eg total points scored , total blocks etc). It is obvious that the relation between a player and their lifetime stats is 1:1.

Let's see some designing options for this relation now:

The most simple option would be to include the player's general info with their stats in a single table. But this seems to unorganized to me ; let's just imagine that we have more than 50 columns for the stats.
The second option would be to create a table player_stats and have as primary key the player_id and referencing through it the primary key of the players table. I think that is called One-To-One Bidirectional relationship.

The third option would be to make put a foreign key in the player referncing his stats. That would be a One-To-One Undirectional relationship.

The fourth option is to make a join table combining both info. Placing unique constraints ensures that there will be no duplicates of a player or of a stat.

So let's summarize. Is it a good idea to break information to different tables? And if yes, what design should be preffered in One-To-One relationships and when ?

No. There's nothing unorganized having 50 columns of statistics, if they are normalized.   But these columns may contain redundant information, if details are available concerning the player's career.


Equivalence of correlated DATEADD intervals Is there any difference between the following statements?

SELECT DATEADD(MONTH, @SomeInteger * 12, @SomeDate)

SELECT DATEADD(YEAR, @SomeInteger, @SomeDate)

In other words, are there any cases in which they would yield different results for the same value of @SomeInteger and @SomeDate?

I ran a quick test via the following:

SELECT
    *
FROM dbo.DimDate dd
WHERE DATEADD(MONTH, 12, dd.Date) <> DATEADD(YEAR, 1, dd.Date)

which returned zero records, but that's far from proving the two statements are equivalent.

The computer scalar execution plan component has the exact same cost in both queries, but the ScalarOperator definitions are different - it would appear that the Query Optimizer is not converting one statement into another.

Thanks!

In that case they are equivalent behaviors even though the ScalarOperator definitions are different. This is because in other cases they can't be swapped. Think about:

datediff in days, which depends on which month and also must account for leap year.
datediff in seconds which will be non-deterministic when there is a leap second.
etc.

SQL Server doesn't gain any advantage changing one scalar operator's definition to look more like another one's. What would be the benefit? If you said WHERE column = 4 * 5 wouldn't it be confusing if the scalar operator said WHERE column = 2 * 10? Even if you have another query somewhere that uses 2 * 10?

In other words, if you said DATEADD(MONTH, n*12, ... I see no reason at all for SQL Server to change that expression to DATEADD(YEAR, n, ... just because we all happen to know that there are 12 months in a year. You asked for a delta by months, not years, so that's what the expression should show.