Using Extended Event For Tracking Updates To A Table I have a table that keeps getting updated, and no one can figure out where the updates are coming from.  I suspect they are coming from Entity Framework, but I want to capture the UPDATE and associated information via an Extended Event to prove this theory.

I'm on SQL 2014 Enterprise, and trying to capture that information using the exec_prepared_sql and sql_statement_starting events.  Here is what I have so far:

CREATE EVENT SESSION [Query Trace] ON SERVER
ADD EVENT sqlserver.exec_prepared_sql(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TableA%') AND [sqlserver].[database_id]=(123))),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TableA%') AND [sqlserver].[database_id]=(123)))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\Query-Trace.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

This seems to work well, I have tested it and been able to captured both a regular UPDATE statement and one called with sp_executesql.  However, I am still missing something. The data in the table is still getting modified and this Extended Event is not capturing the UPDATE that does it.

So, my questions are:

1) Is there something else that I need to be watching in my Extended Event in order to capture UPDATES possibly coming from Entity Framework?

2) Is there something else I should be using instead of Extended Events for this?

Thanks!

When using SQL Server Enterprise Edition (or running any edition newer than 2016 SP1) I would recommend going with SQLAudit functionality. This will give you some great granualar information about who is touching your tables, and the commands that are being executed.

For something like this you would want to use a database audit specification along with a server audit (used to define where your audit will write to), and then scope it down to the table that you want to monitor. By then scoping changes to the public role you will capture any and all changes that happen.

This script should get you there (test in your dev environment, and replace the relevant pieces for the table(s) that you want to monitor).

USE [master]

GO

CREATE SERVER AUDIT [Audit-TblChanges]
TO FILE
(   FILEPATH = N'C:\SQLAudit'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)

GO

USE [YourDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable]
FOR SERVER AUDIT [Audit-TblChanges]
ADD (UPDATE ON OBJECT::[YourTable] BY [public]),
ADD (INSERT ON OBJECT::[YourTable] BY [public])

GO

USE [master]
GO
ALTER SERVER AUDIT [Audit-TblChanges] WITH (STATE = ON);

USE [YourDatabase]
GO
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable] WITH (STATE = ON);
GO

You can quickly read the data back using the GUI, or use sys.fn_get_audit_file to query the data directly from SQL Server.


How to update records of one table with the values of another one (PostgreSQL) I have a table t1 with columns account_id and user_id, and another table users with columns id and account_id. I want to populate t1.account_id with the values of users.account_id.

For instance having

t1
user_id account_id
1       NULL
2       NULL

and

users
id account_id
1  10
2  11

I would like to populate t1.account_id with these values:

t1
user_id account_id
1       10
2       11

I've seen this answer for MySQL but can't have it working with PostgreSQL.

update documentation

UPDATE t1 AS a SET
  account_id = b.account_id
FROM users b
WHERE a.user_id = b.id;

The key is that you need to make sure you have a link between the table you're updating (aliased as "a" in my example) and the table you're using .. which is done via the WHERE clause... a.user_id = b.id


Assign foreign keys only after populating database I am porting a MongoDB database over to a PostgreSQL (relational) database. I would like to perform this task concurrently, and I have developed code with parallelism in mind, but dependencies between tables (foreign keys) are not allowing it. This happens when a row (containing a foreign key) is added, but the table it points to hasn't had that record (primary key) added, throwing the following error:

insert or update on table "observernodeoccurrence" violates foreign key constraint "observernodeoccurrence_canonical_id_fkey"
DETAIL:  Key (canonical_id)=(ba956ef88bcc366fa7d1d5fb696fe98d) is not present in table "canonical".

This is logical, since the record being added, which contains a FK, is pointing to a record from another table which hasn't been added yet.

Possible solution: I thought about a solution consisting of declaring the foreign keys as regular TEXT attributes, containing no dependencies to other tables. As soon as the port was completed, those attributes would be converted back to foreign keys. This way, every foreign key would have a record in the destiny table.

Is there a way to achieve this in Postgresql? If not, is there any other thing I can try to achieve parallelism in populating the database?

Option 1:
Use COPY from a valid dump of the tables in order to avoid any foreign key conflict (Copy is the fastest way i've found to load bulk data)

Option 2:
Create the schema without foreign keys, load the dumps, and recreate the foreign keys after that:
ALTER TABLE table2 DROP CONSTRAINT fkey;
ALTER TABLE table2 ADD CONSTRAINT  fkey; FOREIGN KEY (column_name) REFERENCES table1 (column_name);

Option 3: If you are in a production environment you can try a replication solution, check out how to create a stream replica from mongo to postgres.

Option 4: If you are just trying to migrate some subset or data and keep it sync you can use a connector.

For sure people here can help you with other approaches.


PL/SQL: best way to count elements in an array? Given this:

DECLARE
  TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY;
  V_COUNT INTEGER;

I would like to do:

BEGIN
  -- ... some code filling the MY_ARRAY array

  -- obviously COUNT_ELEMENTS() does not exists, this is what I'm looking for :-)
  V_COUNT := COUNT_ELEMENTS(MY_ARRAY);

  DBMS_OUTPUT.PUT_LINE('My array containts ' || V_COUNT || ' elements.');
END;

Is there something better than creating a procedure doing a basic loop incrementing a counter? Maybe a PL/SQL native function already does this COUNT_ELEMENTS()?

I think this is what you're after:

V_COUNT := MY_ARRAY.COUNT;


Why is SELECT * much faster than selecting all columns (in a different column order) by name? On a table with columns a,b,c,d,e,f,g,h,i,j,k I get:

select * from misty order by a limit 25;
Time: 302.068 ms

And:

select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25;
Time: 1258.451 ms

Is there a way to make the select by column as fast?

Update:

No index on a table, newly created one

Here is the EXPLAIN ANALYZE, does not seem too helpful:

explain analyze select * from misty order by a limit 25;

Limit  (cost=43994.40..43994.46 rows=25 width=190) (actual time=404.958..404.971 rows=25 loops=1)
->  Sort  (cost=43994.40..45731.11 rows=694686 width=190) (actual time=404.957..404.963 rows=25 loops=1)
     Sort Key: a
     Sort Method: top-N heapsort  Memory: 28kB
     ->  Seq Scan on misty  (cost=0.00..24390.86 rows=694686 width=190) (actual time=0.013..170.945 rows=694686 loops=1)
Total runtime: 405.019 ms
(6 rows)

And:

explain analyze select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25;

Limit  (cost=43994.40..43994.46 rows=25 width=190) (actual time=1371.735..1371.745 rows=25 loops=1)
->  Sort  (cost=43994.40..45731.11 rows=694686 width=190) (actual time=1371.733..1371.736 rows=25 loops=1)
     Sort Key: a
     Sort Method: top-N heapsort  Memory: 28kB
     ->  Seq Scan on misty  (cost=0.00..24390.86 rows=694686 width=190) (actual time=0.015..516.355 rows=694686 loops=1)
Total runtime: 1371.797 ms
(6 rows)

This was posted to pgsql-hackers mailing list and I tried to answer in brief there. It seems if the target list (specified columns) matches the tuple descriptor of the relation exactly, that is, both in number of columns and order, then the underlying scan can return a tuple that's directly consumable by the enclosing Sort node. On the other hand, if the target list does not match (either in order or  the number of specified columns), the scan returns a form of the tuples that requires Sort's data preparation step to perform extra work (convert from an internal tuple format to the format directly consumable by the sorting code).

By the way, '*' is internally transformed into a list that (intuitively) matches the relation's tuple descriptor.

EDIT:
If you look at your latter EXPLAIN ANALYZE's Seq Scan's actual times, you can see it's more than former's. That happened because the scan performed an additional step of projection (that is, converting heap tuple into an internal values[], nulls[] format). And because that happened, the upper Sort node had to do extra work in its data initialization, that of converting it back to the tuple format that the actual sorting step understands. That's evident from the Sort's startup cost. That doesn't happen in the former case. That is, both the scan returns the tuple as it is and sort's initialization step simply copies it.


Postgres: What could cause the error "cannot call populate_composite on a scalar"? I have a column of type JSONB, where each row contains a JSON with an array of objects, eg:

[
    {
        "grade": "4.44/5",
        "endYear": 2011,
        "startYear": 2006,
        "userId": "defg"
    },
    {
        "grade": "9.133/10",
        "endYear": 2010,
        "startYear": 2006,
        "userId": "abcd"
    }
]

I'm attempting to expand those JSONB collections into rows, like:

| grade    | startYear | endYear | userId |
-------------------------------------------
| 4.44/5   |    2006   | 2011    | defg   |
| 9.133/10 |    2006   | 2010    | abcd   |
-------------------------------------------

..using the following query:

WITH arr AS (SELECT jsonb_array_elements(jsonbrecords) AS jsons
             FROM "table-with-jsonb"),
    lines AS (
      SELECT x.*
      FROM arr, jsonb_to_record(jsons) AS x(
                "field1" VARCHAR
                 )
  ) SELECT *
    FROM lines

I'm using Datagrip, which paginates results, and the query finishes fine for the first 500 rows.

However, when I try to load the last page of results, I get this error:

[22023] ERROR: cannot call populate_composite on a scalar

Googling this error shows almost no useful results (a first for me) except for the Postgres source code.

I don't speak C, but since the jsonb_array_elements CTE evaluates fine on its own, I am assuming the problem is that some row(s) have a scalar value in jsonbrecords column instead of a proper JSONB.

To ensure I had only JSONB values, I stripped the rows that didn't contain '{' with this query:

UPDATE  "table-with-jsonb" SET jsonbrecords = NULL
WHERE jsonbrecords :: TEXT !~ '{'

This deleted about a dozen rows, but the error remains.

I've also tried to find the problem by inspecting the raw JSONB rows in the table, but have found nothing.

Am I correct in thinking the error means a row has a non-JSONB value in it? If so, how can I fix? If not, how can I debug?

Unrelated, but: you can simplify your query to:

SELECT x.*
from the_table,
     jsonb_array_elements(jsonbrecords) AS t(doc),
     jsonb_to_record(t.doc) as x ("grade" text, "userId" text, "endYear" int, "startYear" int);

Now for the actual question. The error can be avoided by not using jsonb_to_record and accessing each key individually instead:

SELECT t.doc ->> 'grade' as "grade",
       t.doc ->> 'endYear' as "endYear",
       t.doc ->> 'startYear' as "startYear",
       t.doc ->> 'userId' as "userId"
from the_table,
     jsonb_array_elements(jsonbrecords) AS t(doc);

You don't really lose flexibility as you need to specify the column list with jsonb_to_record() as well.

Online example: http://rextester.com/VVGJ34083


How do I copy a SQL Server Database Schema and Objects between servers using VS2019 I've inherited a database with about 1500 tables, and 3600+ objects overall. I want to copy the schema - and just the schema - across to a different server, where I can get it into source control (TFS), and generally work out what to do with the various bits of it. Here is what I have tried so far:

Schema compare and update target using VS2019. Even using a reasonably specced development machine, VS became unresponsive.

Tasks > Generate Scripts in SSMS. The source database uses 28 different filegroups that are not available on the destination server. So far as I can see, it's not possible to turn off the setting to include filegroup when generating the script.

SSMS Copy SQL Server Objects task. Again failed because of the filegroup issue.

VS2019, new database project, and Import Database. This pulled all 3600 objects across like a charm, but there are thousands of build errors. This being the case, I can't publish it. I set the build action = none for all the objects, but then it didn't deploy anything either.

Is it possible to deploy a database project that won't build? It doesn't seem an unreasonable request. Given that I have not yet been granted eternal life, it seems unlikely that all the build errors will get fixed. But meanwhile, I would like it to be under version control, and to be able to use the bits of it that do work.
I could create the filegroups on the destination server, and, to get myself out of a hole today, I probably will. But I am a big fan of database projects, and would like to be able to make use of the rich versioning and comparison features it offers.

not exactly database project solution, but might be able to help you out with the filegroups if you don't find any other way.
dbatools offer this function - Export-DbaScript.
With that, you could try to export any object you want using SMO.
# set the option to not include Filegroup information
$options = New-DbaScriptingOption
$options.NoFileGroup = $true

# get all the tables from MyDatabase on the localhost
# generate script using $options defined above
Get-DbaDbTable -SqlInstance localhost -Database MyDatabase | Export-DbaScript -ScriptingOptionsObject $options


How can I extract a list of dependent tables from an execution plan? Is there a way to extract a full list of tables and views used in a query?

I need to replicate a portion of a database so that we can avoid locking tables when a long query runs.

I have the execution plan. Is it possible to extract all the database objects the query touches?

If this was a one-off operation, I would be inclined to use SQL Sentry Plan Explorer (it's free!), rather than faff around writing a SQL Script.

Open the Execution Plan in Plan Explorer, navigate to the Query Columns pane and you can see a list of the objects that are used in the query.


Inside SQLOS – Scheduling Using SQL Server 2012 Enterprise Edition SP1 running on Windows Server 2008 R2 SP1
For each logical processor core on SQLOS has a scheduler assigned to it. Those scheduler can be viewed as status “VISIBLE ONLINE” in sys.dm_os_schedulers

Worker threads (tasks) can run on a scheduler and those threads (tasks ) can have various status like based on a waiting queue for e.g.

a) suspended in a waiting queue for resource to be available
b) runnable queue (mostly FIFO unless controlled by Resource Governor) or
c) currently running on a scheduler.

My question is say for e.g. a task (SELECT query in this case) first goes to a scheduler (let's call it scheduler A) but found that it has to wait until pages are brought into memory (IO Operation) it is now put into waiting queue in suspended state, once the IO is complete than that particular worker thread (tasks) is signaled and has been put into runnable queue in order in which it was arrived into that queue.

Does the task always have to go to scheduler A to finish its quantum or it can switch its context and can now run on any idle scheduler (let's call it scheduler B) with no runnable tasks? If there is a context switch what is its effect?

It has to wait on the scheduler A runnable queue, there is no switching of tasks to alternative schedulers.  The time accumulated waiting for the scheduler to be available is referred to as a signal wait.

  If a thread is ready to go, but the scheduler to which it’s assigned
  currently has another session running, then it will be placed in the
  “Runnable” queue. This means it’s in the queue to get on the
  processor. This is referred to as a signal wait (ref).

This is different and separate to the time accumulated while Suspended on the wait list.

  The signal wait time is exposed by the signal_wait_time_ms column, and
  is solely CPU wait time. If a session is waiting for another resource
  to become available in order to proceed, such as a locked page, or if
  a running session needs to perform I/O, then it’s moved to the wait
  list. This is a resource wait and the waiting session’s status will be
  recorded as “Suspended.” (ref).

The relationship from sessions to schedulers maps out as:

Session -> Task -> Thread -> Scheduler -> Logical CPU

Once a thread is allocated to a scheduler it remains on that scheduler until it completes. It may move repeatedly between Running, Suspended and Runnable queues but will remain assigned to one scheduler.

A session may of course be comprised of multiple tasks and threads which are distributed across multiple schedulers as the result of a parallel execution plan.


MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, and I'm sure there is no long sql statement running before adding index.

What's more confused is that I saw the ALTER statement copying to tmp table in processlist. According to official docs innodb-create-index-overview, INPLACE method should be taken by default, right? ( I just add one secondary index and delete one).

Also I checked information_schema.innodb_trx to see whether uncommitted transactions exist. No.

Can someone could explain why this happens for me?

By the way, my (alter) table t1 has triggers on it. And there's trigger on other table t2 to update t1. Thought I know it's ugly ·_·

show processlist after alter table t1 add key idx_c1_c2(c1,c2), drop key idx_c2：

There are good reasons ALGORITHM=INPLACE may not be used and switches to ALGORITHM=COPY.
REASON #1
In the link you gave in the question, there is a chart that shows the following:

Adding a FULLTEXT index trigger a table copy is not supported for online operation
Many operations can revert to copy because it says in the notes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.

Some online DDL may still lock and copy because of an index's interaction. Don't forget that each BTREE entry in a secondary index has a hook back to the primary key. Adding a new secondary index may induce row level locks and index locks.
REASON #2
According the limitations page : InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY.
I am sure there are other reasons that can be researched an discussed along these lines.


Segmenting data by percentage of a column total I need to separate a list of records by the percentage of a column when they are summed to all records larger than the current record until a MAX percentage.

Records are sorted DESC in column A, and I need all rows in A summed up until (from large to small) they equal 60% of the sum(A) (col B). Then, I need to perform the same for the rest of the row to 30% of the sum(A), and then the last 10% or the rest of the records.

The top 60% records need to be marked as A, middle 30% records marked B and last ~10% marked C.
I have tried NTILE but can't get the segments to match up correctly.

╔══════════════════════╦══════╦═══╦══════╦═════╗
║            A         ║   B  ║ C ║      ║     ║
╠══════════════════════╬══════╬═══╬══════╬═════╣
║     197              ║  197 ║ A ║      ║     ║
║     192              ║  389 ║ A ║      ║     ║
║     190              ║  579 ║ A ║      ║     ║
║     185              ║  764 ║ A ║      ║     ║
║     184              ║  948 ║ A ║      ║     ║
║     184              ║ 1132 ║ A ║      ║     ║
║     181              ║ 1313 ║ A ║      ║     ║
║     180              ║ 1493 ║ A ║      ║     ║
║     175              ║ 1668 ║ A ║      ║     ║
║     172              ║ 1840 ║ A ║      ║     ║
║     171              ║ 2011 ║ A ║      ║     ║
║     170              ║ 2181 ║ A ║      ║     ║
║     169              ║ 2350 ║ A ║      ║     ║
║     169              ║ 2519 ║ A ║ 2602 ║ 60% ║
║                      ║      ║   ║      ║     ║
║     167              ║  167 ║ B ║      ║     ║
║     167              ║  334 ║ B ║      ║     ║
║     167              ║  501 ║ B ║      ║     ║
║     166              ║  667 ║ B ║      ║     ║
║     166              ║  833 ║ B ║      ║     ║
║     166              ║  999 ║ B ║      ║     ║
║     165              ║ 1164 ║ B ║ 1301 ║ 30% ║
║                      ║      ║   ║      ║     ║
║     164              ║  164 ║ C ║      ║     ║
║     164              ║  328 ║ C ║      ║     ║
║     163              ║  491 ║ C ║      ║     ║
║     163              ║  654 ║ C ║  867 ║ last
║                      ║      ║   ║      ║     ║
║     TOTAL A:A = 4337 ║      ║   ║      ║     ║
╚════════════════════=╩═════════╩══════╩═════╝

You could calculate the percentage of the sum of a you've reached up till the current row, then segment that based on your thresholds.

with pct as (
  select a
  , (sum(a) over (order by a desc))/sum(a) over() pct
  from foo
)
select a
, pct
, sum(a) over (partition by
    case
      when pct <= .6 then 'A'
      when pct <= .9 then 'B'
      else                'C'
    end
    order by a desc) partial_sum
from pct
order by a desc;

Expanded version that actually shows the category (second subquery isn't necessary, just avoids repeating the partial sum expression):

with pct as (
  select a
  , (sum(a) over (order by a desc))/sum(a) over () pct
  from foo
), category as (
  select a
  , pct
  , case when pct <= .6 then 'A'
         when pct <= .9 then 'B'
         else                'C'
    end cat
  from pct
)
select a
, cat
, to_char(pct*100, '999.99')||'%' pct_of_total_a
, sum(a) over (partition by cat order by a desc) sum_by_category
from category
order by a desc;


Does READ COMMITED commited blocks any rows? I know that there are four diffrent types of isolation levels in databases. I've read many articles about that but still, I don't get the idea of Read Commited level. Does this level lock the rows which we are trying to get?

For example:

Transaction 1 - READ COMMITED

begin;

select * from table <- Is this query locking anything?

update table set id = 2 where id =3;

Is this query locking anything?

If after this query will be another query from transaction REPETABLE READ will we be able to execute select from second transation?

Locks is a way to implement isolation levels, exactly what gets locked and not is implementation specific. So it may be better to think in terms of what the isolation level guarantees. READ COMMIT guarantees that there are no dirty reads. I.e. we won't see uncommitted data that is later on rolled back. However, both Nonrepeatable reads and Phantom reads are possible. Non-repeatable means that if we do:

BEGIN T
select * from tbl -- s1
select * from tbl -- s2
END T

s1 and s2 is not guaranteed to return the same result, i.e. it is valid for another transaction to delete or update from tbl so that s1 and s2 differs. Phantom read typically means that another transaction inserted a row between s1 and s2.

Depending on the implementation, some rows may be looked during REPEATABLE READ, but probably not all.

Db2 as an example (not considering CURRENTLY COMMITED here), locks a row that matches the predicate with a Shared-lock[1] (in the example there is none so all row matches) until it finds a new row that matches the predicate. This may be different than how it is implemented in other DBMS, so you should check out what mechanism the DBMS you have in mind is using.

[1] Shared means that other readers can read the row, but writers must wait


Update column by comparing date and string values together I have problem in updating table.

I need to update table by comparing date and name with another table.
I have two tables:

 table 1 : dim_sesid

    name(varchar)                       role
    20111012133513aaa123
    20110908072611aaa121
    20111002210235bbb853
    20120113113353bbbl971

The role column is updated later.

and the other table is

 employeerole
    username(varchar)       role(varchar)           thedate(varchar)
    aaa                     technician              2011-10-12 14:35:13
    aaa                     technician              2011-09-08 07:26:11
    aaa                     technician              2011-09-08 07:26:11
    bbb                     day guard               2011-10-02 20:02:35
    bbb                     day guard               2012-01-13 10:33:53
    bbb                     night guard             2012-01-13 21:30:00

I want to update role in the dim_sesid table depending upon username and thedate. The same user can have different role in different date.  There is no common column in two tables and the only column to perform join is "name" in dim_sesid. There are some duplicates values in employeerole.

I write the following query to update the dim_sesid table.

UPDATE dim_sesid ds
       SET role =
             (SELECT min (er.role)
                FROM EMPLOYEEROLE er
               WHERE SUBSTR(
                       ds.name
                      ,1
                      ,length(to_char(thedate::timestamp, 'yyyymmdd') || er.username)) =
                       to_char(thedate::timestamp, 'yyyymmdd') || er.username );

But the problem is, no rows are updated.

If I check with  to_char(thedate::timestamp, 'yyyymmddhh24miss') condition, then it checks both date and time, but i want to compare only with date and username.

I am wondering how to check only 'name' and 'date' columns in two tables.

If you want to ignore the time part and match where the days are the same then:

UPDATE dim_sesid ds
       SET role =
             (SELECT min (er.role)
                FROM EMPLOYEEROLE er
               WHERE substr(DS.NAME, 1, 8) || 'xxxxxx'
                  || substr(DS.NAME, 15, length(USERNAME))
               like to_char(thedate::timestamp, 'yyyymmdd') || 'xxxxxx' || USERNAME);

At the moment, for the first row, the to_char(thedate::timestamp, 'yyyymmdd') || er.username would return 20111012aaa which doesn't exist. By replacing the time portion with the same set of characters (xxxxxx) then we can ignore that and it becomes:

           WHERE '20111012' || 'xxxxxx'
              || 'aaa'
           like '20111012' || 'xxxxxx' || 'aaa'

The problem is though that you have two roles for 13th January 2012. The min will always return the day guard leaving the night guard superfluous.


How can I reset Aborted_connects in MySQL? A recent issue in our network caused a large number of aborted connects, which is now being displayed on the health dashboard of our web application as a potential issue.

Aside from restarting MySQL, is there a way to reset Aborted_connects to 0? I have tried flush hosts and flush status, but they do not reset Aborted_connects

EDIT : MySQL 5.7.24

The reason FLUSH STATUS doesn't reset this is because its a function in MySQL rather than a simple variable (code ref).

Using gdb assignment, you should be able to:

gdb -p $(pidof mysqld) -batch \
  -ex 'set variable Connection_handler_manager::m_instance->m_aborted_connects = 0L' \
  -ex continue \
  -ex quit

Otherwise you don't have much choice apart from a restart.


If a CTE is defined in a query and is never used, does it make a sound? Do unused CTEs in queries affect performance and / or alter the generated query plan?

+1 to Erik, but wanted to add two things (which did not work well in a comment):

You don't even need to look at execution plans to see that they are ignored when not used. The following should produce a "divide by 0" error but does not due to cte2 not being selected from at all:

;WITH cte1 AS
(
  SELECT 1 AS [Bob]
),
cte2 AS (
  SELECT 1 / 0 AS [Err]
  FROM cte1
)
SELECT *
FROM   cte1;

CTE's can be ignored, even if they are the only CTE, and even if they are selected from, if logically all rows would be excluded anyway. The following is a case where the query optimizer knows ahead of time that no rows could be returned from the CTE, so it doesn't even bother to execute it:

;WITH cte AS
(
  SELECT 1 / 0 AS [Bob]
)
SELECT TOP (1) [object_id]
FROM   sys.objects
UNION ALL
SELECT cte.[Bob]
FROM   cte
WHERE  1 = 0;

Regarding performance, the unused CTE is parsed and compiled (or at least compiled in the case below), so it is not 100% ignored, but the cost would have to be negligible and not worth being concerned about.

When only parsing, there is no error:

SET PARSEONLY ON;

;WITH cte1 AS
(
  SELECT obj.[NotHere]
  FROM   sys.objects obj
)
SELECT TOP (1) so.[name]
FROM   sys.objects so

GO
SET PARSEONLY OFF;
GO

When doing everything just short of execution, then there is a problem:

GO
SET NOEXEC ON;
GO

;WITH cte1 AS
(
  SELECT obj.[NotHere]
  FROM   sys.objects obj
)
SELECT TOP (1) so.[name]
FROM   sys.objects so

GO
SET NOEXEC OFF;
GO
/*
Msg 207, Level 16, State 1, Line XXXXX
Invalid column name 'NotHere'.
*/


Postgres Database too large to start service Due to some craziness on a server I am looking after, I have a huge database that needs to be truncated (44GB despite being a simple site). However, I can't start the postgres service as there is not enough room on the server. How can I delete records, vacuum or do anything else that will make space without being able to start the pgsql service?

Try start with --single-mode to truncate (PG document)

Usage

postgres --single -D /usr/local/pgsql/data other-options my_database

Note

  The postgres command can also be called in single-user mode. The
  primary use for this mode is during bootstrapping by initdb. Sometimes
  it is used for debugging or disaster recovery; note that running a
  single-user server is not truly suitable for debugging the server,
  since no realistic interprocess communication and locking will happen.
  When invoked in single-user mode from the shell, the user can enter
  queries and the results will be printed to the screen, but in a form
  that is more useful for developers than end users. In the single-user
  mode, the session user will be set to the user with ID 1, and implicit
  superuser powers are granted to this user. This user does not actually
  have to exist, so the single-user mode can be used to manually recover
  from certain kinds of accidental damage to the system catalogs.

  Note that the single-user mode server does not provide sophisticated
  line-editing features (no command history, for example). Single-User
  mode also does not do any background processing, like automatic
  checkpoints.

Example

postgres@xxx-yyy$ ./bin/postgres --single  -D data/ postgres

PostgreSQL stand-alone backend 9.6.0
backend> truncate t1 ;
2016-11-23 07:53:17.340 UTC: proc=18481,user=,db=,host=LOG:  statement: truncate t1 ;
backend> vacuum full t1 ;
2016-11-23 07:56:44.541 UTC: proc=19135,user=,db=,host=LOG:  statement: vacuum full t1 ;

In terms of postgres process related to this mode

ps -ef | grep postgres
postgres 19135  4510  0 07:56 pts/1    00:00:00 /opt/PostgreSQL/9.6/bin/postgres --single -D data/ postgres

If --single-mode does not help you, please add more space (as @ypercube mentioned), then start postgres server and truncate data.


Common term for a table (heap) without any supporting structures Maybe this is off topic, and please VTC if so, but I couldn't think of a better place to ask.

Is there a common term for a table without any other structures (indexes, statistics, etc) - something like a "naked" table? I use the term heap but understand it just to apply to nonclustered tables, rather than to tables without other support - a heap can still have other indexes associated with it.

I had copied some tables into a new database but didn't bring over the partition scheme, indexes, triggers, or anything else - it made me wonder if there's a term for just the data itself (and minimal schema needed to hold it).

We refer to these as heaps in SQL Server. I understand that Non-Clustered Indexes complicate things, but that's their name.

For what it's worth, on many RDBMS platforms, with auto statistics enabled, there's very little chance a table will not have statistics on it.

I'd be comfortable calling these data structures unindexed heaps.


Multi-core vs Multi-CPU I am requesting a new server from our Server team. Which is better for SQL Server performance: 2 CPUs with 8 cores per CPU or 1 CPU with 16 cores?

This question is very broad, and the answer depends very much on the nature of your workloads and your planned SQL Server setup. However, assuming your future hardware supports NUMA (why wouldn't it?) and given that

  With SQL Server 2016 (13.x), whenever the SQL Server Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. [...] the SQL Server Database Engine creates soft-NUMA nodes that ideally contain eight cores

one might reason that the vendor thinks 8 cores per socket is the optimal configuration in most cases.

You might also find this old article useful.


How to query results from a huge postgres database? I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly!

Even to find unique rows of a column it takes more than 10 minutes. Either I use a command line or tool like PHPMyAdmin to fire the query the wait time remains the same.

For example:

SELECT COUNT (DISTINCT column_name) FROM table_name;

I am worried that if use joins operations on these tables this wait time will increase significantly and will be more painful.

Could anyone suggest what could be a possible solution to get the statistics back quickly?

SELECT COUNT (DISTINCT column_name) FROM table_name;

The part of the PostgreSQL code that implements "COUNT(DISTINCT...)" is quite old and hasn't had much performance work done on it recently.  It can't take advantage of either parallel processing, or hash tables, for example.  You could rewrite with a subquery to possibly take advantage of some newer techniques:

select count(*) from
    (select distinct column_name from table_name where column_name is not null) as foo

But still, this is not as trivial a task as you seem to think it is.  It is a hard task and may take a long time when fed huge amounts of data.

  I am worried that if use joins operations on these tables this wait time will increase significantly and will be more painful.

Don't invent things to worry about.  The performance of a COUNT(DISTINCT...) tells you very little about the performance of a JOIN.  Write the query you want, and then worry about optimizing that query, not some other query that has little to do with the one you want.

It can make sense to simplify a query to see how long component parts of it might take.  But COUNT(DISTINCT...) is unlikely to be such a simplifying component part.


Not all MySQL processes are killed when restarting We have a MySQL 5.6 server running on Ubuntu 12.04 using the MySQL 5.6 PPA.

The service comes up fine when rebooting the box, but whenever we restart the MySQL process via /etc/init.d/mysql restart, some of the child processes are not killed, resulting in file lock errors when the daemon tries to start again:

2015-04-16 08:40:23 2713 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-04-16 08:40:23 2713 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2015-04-16 08:40:24 2713 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-04-16 08:40:24 2713 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2015-04-16 08:40:25 2713 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-04-16 08:40:25 2713 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

Full config file:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
skip-external-locking
basedir                         = /usr
tmpdir                          = /tmp
pid-file                        = /var/run/mysqld/mysqld.pid
socket                          = /var/run/mysqld/mysqld.sock
datadir                         = /var/lib/mysql
lc-messages-dir                 = /usr/share/mysql
log-error                       = /var/log/mysql/mysql.err
user                            = mysql
port                            = 3306
bind-address                    = 192.168.50.253
explicit_defaults_for_timestamp = 1

max_allowed_packet              = 1024M
thread_stack                    = 192K
thread_cache_size               = 32
query_cache_limit               = 128M
query_cache_size                = 32M
max_heap_table_size             = 512M
tmp_table_size                  = 2G
key_buffer_size                 = 1024M
sort_buffer_size                = 1024M
read_buffer_size                = 1024M
bulk_insert_buffer_size         = 1024M

myisam_sort_buffer_size         = 1024M
myisam_max_sort_file_size       = 20G
myisam-recover                  = BACKUP

innodb_thread_concurrency       = 8
innodb_flush_method             = O_DIRECT
innodb_buffer_pool_size         = 5120M
innodb_buffer_pool_instances    = 4
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = 1
innodb_log_file_size            = 1G
innodb_log_buffer_size          = 8M
innodb_strict_mode              = 0
innodb_autoinc_lock_mode        = 0

[mysqldump]
quick
quote-names
max_allowed_packet  = 1024M

[mysql]

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

Is there anything anyone can see that could cause this behaviour? The debian-sys-maint user exists and has the appropriate grants.

Seems the issue for us was the use of the old-style /etc/init.d/mysql script, rather than the service mysql restart script provided by Upstart. We haven't had these issues when using the Upstart commands.


Getting deadlock on insert and select statement(sql server 2014) in java application In my application there are many threads which uses insert and select statement to store/retrieve data from database.

I am getting deadlock in my production environment, I have observe deadlock from application log file.

I am not able to reproduce same situation on my development environment.

Please give me suggestion to:

How to resolve this kind of deadlock?
How to get more clear understanding to capture deadlock?

Thank you

How to resolve this kind of deadlock?

As you have not provided your deadlock graph it will be impossible to give an answer about how to resolve the deadlock. Even with just deadlock graph sometime it is not possible to give a solution.  You might have to trace your calls and see the sequence of calls (involved in the deadlock), look at the execution plan, isolation level etc. Based on what you said (insert and select statement) I suggest you read this post which will give you a good start.

SELECT/INSERT Deadlock

How to get more clear understanding to capture deadlock?

As suggested by @spaghettidba in comment section you can look at the article mentioned there.

Few more articles for you:

Detecting and Ending Deadlocks
SQL Server Deadlocks by Example


Searching all databases in environment I need to be able to search for SQL objects (logins, etc). With a smaller number of servers, I would connect to them all via a registration group in SSMS and do the search.

Does anyone have a better way with a large number of instances?

Thanks

You can use SQL Power doc for this .

SQL Power Doc was written by Kendal VanDyke. It is a command line PowerShell manifest consisting of several PowerShell modules that will help you build a solid foundation to Discover, Document and Diagnose SQL Server instances and Windows Operating system details. Additionally, you’ll gain some insight into what directions you can go for future exploration. It basically invokes PowerShell cmdlets and give you the necessary details front and center.

How to use has been described here

You can just filter out in the report for the objects you want or even before executing PS you can adjust the parameters as needed.


MySQL: Incorrect binary log format - OFF.000 I have a Percona MySQL 5.5.30 as a master(Debian 6.0.7). I got a weird issue that all my binary logs started saving in format OFF.00* instead of the specified format in .cnf file.

mysql> show binary logs;
+------------+------------+
| Log_name   | File_size  |
+------------+------------+
| OFF.000084 | 1073744171 |
| OFF.000085 | 1073741909 |
| OFF.000086 | 1073742904 |
| OFF.000087 | 1073745627 |
| OFF.000088 | 1073742910 |
| OFF.000089 | 1073742081 |
| OFF.000090 | 1075694984 |

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

bash-4.1# grep log-bin /etc/mysql/conf.d/mycustom.cnf
log-bin = /var/log/mysql/acct-db1-bin.log

Haven't restarted or done anything on MySQL service, but wondering how the format of binary logs got changed.

I have a setup a new slave to this master, and don't have any issue with the replication. But I am thinking whether it will cause any issue in future.

I have seen your problem before

Problem #1

You have /etc/mysql/conf.d/mycustom.cnf and the log-bin line is not being read

Problem #2

Chances are /etc/mysql/conf.d/my.cnf exists and has the following line

[mysqld]
log-bin=OFF

That's why the binlogs start with a filename of OFF

Some feel log-bin=OFF disables binlogging. The MySQL Documentation on log-bin says the permitted value is a filename. Thus, it is not boolean. Simply removing the line or commenting it out disables binlogging.

Suggestion #1

You may want to start mysqld manually using

service mysql stop
mysqld --defaults-file="/etc/mysql/conf.d/mycustom.cnf"

Suggestion #2

Just change log-bin in /etc/mysql/conf.d/my.cnf

Then service mysql restart

Epilogue

Don't restart mysql just yet. This problem is one the Master.

STEP 01 : Stop all writes to the Master

STEP 02 : Go to the Slave and run STOP SLAVE;

STEP 03 : Restart mysql on the Master

STEP 04 : On the Slave, run this

CHANGE MASTER TO master_log_file='acct-db1-bin.000001',master_log_pos = 4;
START SLAVE;

STEP 05 : Start up all writes to the Master

This should complete your maintenance


CDC error - Error converting data type varchar to uniqueidentifier I have CDC and Replication configured on an Always On cluster.
I had to patch the SQL Server on Node 1 so I failed over the instance to node 2 and started patching node1.

After about an hour one of the log reader agent jobs started failing with the below error

  "The process could not execute  sp_replcmds..."

When I looked at the start of the failure in history, it says

  "Failed to insert rows into Change Data Capture change tables."

So I queried sys.dm_cdc_errors and the error was

  Error converting data type varchar to uniqueidentifier.

I am a noob with CDC.  Looks like it has nothing to do with Replication and its a CDC error.

Looking for suggestions on how to narrow down the issue, may be to the table causing the error?

Update
Now another log reader agent is failing with the similar error.

  Procedure or function sp_batchinsert_1350295870 has too many arguments
  specified. Failed to insert rows into Change Data Capture change
  tables.

I think its the same as this

But I was upgrading to SP2 CU7.  Anyway I disabled and re-enable the CDC and the log reader agent resumed.


group by+where on calculated column SELECT
   SUM(a)-SUM(b) AS c1,
   SUM(a) AS c2,
   SUM(b) AS c3
FROM tbldoc
GROUP BY Cid
WHERE c1<>0

If you take ( WHERE c1<>0 )  not error

Thank you Answer

I presume you mean that you get an error with this query, and that if you remove the where clause you get no error, but not the right answer.

You need a having clause, which is like where but can be applied to the aggregated results:

select
  sum(a)-sum(b) AS c1,
  sum(a) AS c2,
  sum(b) AS c3
from tbldoc
group by Cid
having sum(a)-sum(b) <> 0;

Alternatively, you can move the whole statement to a subquery and perform the where on the outside:

select *
from (
  select
    sum(a)-sum(b) AS c1,
    sum(a) AS c2,
    sum(b) AS c3
  from tbldoc
  group by Cid
) s
where c1 <> 0;


Functional Dependencies and Normal Forms I'm trying to grasp the concepts of functional dependencies and normal forms, specifically first (1NF), second (2NF), and third (3NF) normal forms. The link here was extremely helpful in deciding the distinction between the three forms, but I have a much more abstract problem.

The accepted answer to this post gives wonderful examples of tables and which normal form they are in. However, my question poses the following:

For (W X Y Z), determine the form of: WX->Y, WX->Z, Y->Z. (Note: 'WX' should be underlined as the combined primary key, but I couldn't find that in the formatting, so it's bold)

I don't get it; how can I even begin to do this? The textbook I'm referencing only gives concrete examples, and I don't understand the theory well enough to apply it to the abstract. Any insight?

1NF satisfied, as Y and Z depend on the key WX;
2NF satisfied as Y and Z depend on the whole key WX, and not on any subset of the key;
3NF not satisfied as Z depends on Y, which is not part of the key.

To put this into 3NF one would restructure as (W X Y) and (Y Z).


Excessive CPU use in vendor SQL Server DB Background: I've got a SQL Server 2012 DB provided by a vendor so modification of queries and tables is limited. We do own the DB though, so we can add and maintain indexes.

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use, but while we work on fixing this I'm investigating other issues.

I'm not seeing any significant memory or disk IO pressure. This is a relatively lightly used OLTP system and has been well provisioned for resources... it really shouldn't be having any issues, or at least should only have noticeable spikes, no constant CPU use.

Two questions:

Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use?
Does the combination of wait stats listed below from this system discredit the index fragmentation explanation?

Information:

WaitType                                    Wait_S
---------------------------------           -----------
CXPACKET                                    773345.21
PAGELATCH_UP                                737295.83
SOS_SCHEDULER_YIELD                         140425.24
LATCH_EX                                    69877.95
RESOURCE_SEMAPHORE_QUERY_COMPILE            60985.48
LCK_M_SCH_S                                 39488.17

Source query for the wait results:

WITH [Waits] AS
(
 SELECT
     [wait_type],
     [wait_time_ms] / 1000.0 AS [WaitS],
     ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
     [signal_wait_time_ms] / 1000.0 AS [SignalS],
     [waiting_tasks_count] AS [WaitCount],
     100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
     ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
 FROM
     sys.dm_os_wait_stats
 WHERE
     [wait_type] NOT IN (... common waits )
     AND [waiting_tasks_count] > 0)
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95;

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use...
and
Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use?

This is partially true. Index fragmentation wont cause HIGH CPU. Internal Fragmentation means that you have lots of free space on the pages and it will take more time to scan the index. This will incur more disk IO and will require more memory to store the index (due to free spaces in the index pages) which means more wasted space in buffer pool.
Bad statistics will cause the query optimizer to generate inefficient(bad) plans causing a degraded performance e.g. queries that took 2 secs to complete will take 2 mins or 2 hours, etc as sql server will make a bad guess (e.g. will estimate 1 row as opposed to actual 2M rows) and may choose an inapproprate join performing high number of reads or can choose a bad join e.g. nested loop where a hash or merge join would have been a better choice. Bad (outdated or old) stats will peg your CPU at a much higher level.
So keeping your Statistics and Indexes defragmented will definitely help. Instead of crafting your own solution, I would recommend to use Ola's Index Maintenance solution.
Refer to Kendra's excellent post : Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video) ?
Remus Rusanu has a really good blog post on : The Bizzaro Guide to SQL Server Performance (caution : Don't follow it !)

Does the combination of wait stats listed below from this system discredit the index fragmentation explanation?

I believe that there might be more things to address at your sql server configuration level than just to worry about Index fragmentation. Also CXPACKET wait in itself is not a problem.
Things to check :

MAXDOP - set it away from default to a more sensible value.
Make sure you have TF 1117 and 1118 turned on for addressing tempdb contention. and have multiple tempdb data files as per kb-2154845
Start looking at your queries - Top 20 CPU Consumers to see if they are using proper indexes , not doing any implicit conversions, etc.
Turn ON optimize for ad hoc workloads
Refer to MS KB 2964518: Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads
Make sure that the server is using  High Performance power plan option.

You can use Glenn Berry's diagnostic queries - 2012 version
-- Signal Waits for instance  (Query 27) (Signal Waits)
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [% Signal (CPU) Waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [% Resource Waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK) OPTION (RECOMPILE);

-- Signal Waits above 15-20% is usually a sign of CPU pressure

and
-- Top Cached SPs By Total Worker time (SQL Server 2012). Worker time relates to CPU cost  (Query 44) (SP Worker Time)
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

Additionally Joe Sack talks about Troubleshooting Methodology for SQL Server CPU Performance Issues


Slow subquery in SELECT statement Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows.

The table is a list of hotel bookings, containing (most relevant) Check-In date and Booking Value

My boss wanted a diagram where the values are summed for every month/year in a special way:

Row 1: Sum of value between Jan 2012 - Jan 2012
Row 2: Sum of value between Jan 2012 - Feb 2012
Row 3: Sum of value between Jan 2012 - Mar 2012
Row 4: Sum of value between Jan 2012 - Apr 2012
...
Row 5: Sum of value between Jan 2012 - Dec 2012

And so on for every year. Basically row 5 in this example would return the summed value for the entire year.

Table:

CREATE TABLE IF NOT EXISTS `bookings` (
  `DBID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `checkin` date NOT NULL,
  `checkout` date NOT NULL,
  `total` int(11) NOT NULL,
  PRIMARY KEY (`DBID`,`checkin`),
  KEY `total` (`total`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11171 ;

My query:

SELECT
    YEAR(checkin) as year,
    CONCAT(UCASE(LEFT(MONTHNAME(checkin), 1)),SUBSTRING(MONTHNAME(checkin), 2)) as month,
    MONTH(checkin) as monthnum,
    (
        SELECT SUM(total)
        FROM bookings as book2
        WHERE YEAR(book2.checkin) = year AND MONTH(book2.checkin) <= monthnum
    ) as sum
FROM `bookings`
GROUP BY YEAR(checkin),MONTH(checkin)
ORDER BY YEAR(checkin) ASC, MONTH(checkin) ASC

So the subquery is the magic behind this query, it all produces the exact result I want, but the execution time is unacceptable.

I'm not sure how to optimise this further, I can't use joins since it's on the same table, I've indexed everything neccessary, etc.

Try rewriting the sub-query as a join:

SELECT YEAR(b1.checkin) as year
     , MONTH(b1.checkin) as monthnum,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON YEAR(b2.checkin) = YEAR(b1.checkin)
   AND MONTH(b2.checkin) <= MONTH(b1.monthnum)
GROUP BY YEAR(b1.checkin),MONTH(b1.checkin)
ORDER BY YEAR(b2.checkin) ASC, MONTH(b2.checkin) ASC

I also removed CONCAT(UCASE(LEFT(MONTHNAME(checkin), 1)),SUBSTRING(MONTHNAME(checkin), 2)) as month, I don't think that will matter but it might be beneficial to do this once, and add it to the report in the application-layer.

You don't mention version of MySQL, but later versions have generated columns. You can try generating columns for YEAR and MONTH:

CREATE TABLE IF NOT EXISTS bookings
( DBID int unsigned NOT NULL AUTO_INCREMENT
, checkin date NOT NULL
, checkout date NOT NULL
, total int NOT NULL

, checkin_year SMALLINT as (YEAR(checkin))
, checkin_month SMALLINT as (MONTH(checkin))

,    PRIMARY KEY (DBID, checkin)
,    KEY total (total)

,    KEY (checkin_year, checkin_month)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11171 ;

and then use those columns in the query:

SELECT b1.checkin_year
     , b1.checkin_month,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON b2.checkin_year = b1.checkin_year
   AND b2.checkin_month <= b1.checkin_month
GROUP BY b1.checkin_year, b1.checkin_month
ORDER BY b1.checkin_year, b1.checkin_month

BTW, what is the purpose of the index on TOTAL?


From the looks of the two EXPLAIN plans, you may need another index.
From the WHERE clause of
SELECT count(*) as total_students FROM students s,subjects b
WHERE s.subjectid = b.subjectid AND s.attended>'2012-05-22 00:00:00'
AND s.classid=47;

I suggest creating the following compound index
ALTER TABLE subjects ADD INDEX subjectid_attended_ndx (subjectid,attended);

Give it a Try !!!
CAVEAT
As suggested in the comment from @AaronBertrand, the cardinality of the index may be causing random results depending on different values for subjectid (47 vs 43 vs some other value).


How does a subquery work in sql server 2012? I have created one temp table and inserted 10 records into it. The query against the table gets the 3rd highest salary. This query has a sub query. I want to know which query executes first: the inner query or outer query? Kindly check the query which is written below.

CREATE TABLE #tmpTbl
(
id int,
sal money
)

insert into #tmpTbl values('1','12000')
insert into #tmpTbl values('1','22000')
insert into #tmpTbl values('1','50000')
insert into #tmpTbl values('1','100000')
insert into #tmpTbl values('1','100')
insert into #tmpTbl values('1','5000')
insert into #tmpTbl values('1','15000')
insert into #tmpTbl values('1','18000')
insert into #tmpTbl values('1','19000')
insert into #tmpTbl values('1','21000')

SELECT *
FROM #tmpTbl
order by sal asc

SELECT DISTINCT sal
FROM #tmpTbl t1
WHERE 3 = (SELECT COUNT(DISTINCT SAL)
           FROM #tmpTbl t2
           WHERE t1.SAL <= t2.SAL)

Kindly run both queires. The first query will give you salary in ascending order and second will give you 22000.00.

Which runs first: the outer query or the inner query?

The answer is that they run in parallel (although the sub-query starts first, according to the execution plan) and then are merged to give you the result you want.

You can see this for yourself if you select the button to display either the estimated execution plan or the actual execution plan.  The last item is your query.  The execution plan is read from bottom right to upper left.

But as a bonus, there is a better way to get that answer, and will allow you to get the answer for any Nth highest salary.  The query below uses the temp table you have defined above.  It uses ROW_NUMBER() with the PARTITION and ORDER BY clauses to rank the salary per ID.

;WITH CTE_Temp AS
    (
    SELECT id
        , sal
        , SalRank = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY sal DESC)
    FROM #tmpTbl
    )
SELECT * FROM CTE_Temp WHERE SalRank = 3
;


A better way to write this query? I have the following schema for my database (Postgresql 10):

CREATE TABLE "PulledTexts" (
"Id" serial PRIMARY KEY,
"BaseText" TEXT,
"CleanText" TEXT
);

CREATE TABLE "UniqueWords" (
"Id" serial PRIMARY KEY,
"WordText" TEXT
);

CREATE TABLE "WordTexts" (
"Id" serial PRIMARY KEY,
"TextIdId" INTEGER REFERENCES "PulledTexts",
"WordIdId" INTEGER REFERENCES "UniqueWords"
);
CREATE INDEX "IX_WordTexts_TextIdId" ON "WordTexts" ("TextIdId");
CREATE INDEX "IX_WordTexts_WordIdId" ON "WordTexts" ("WordIdId");

Some sample data:

INSERT INTO public."PulledTexts" ("Id", "BaseText", "CleanText") VALUES
(1, 'automate business audit', null),
(2, 'audit trial', null),
(3, 'trial', null),
(4, 'audit', null),
(5, 'fresh report', null),
(6, 'fresh audit', null),
(7, 'automate this script', null),
(8, 'im trying here', null),
(9, 'automate this business', null),
(10, 'lateral', null);

INSERT INTO public."UniqueWords" ("Id", "WordText") VALUES
(1, 'trial'),
(2, 'audit'),
(3, 'creation'),
(4, 'business'),
(5, 'automate');

INSERT INTO public."WordTexts" ("Id", "TextIdId", "WordIdId") VALUES
(1, 1, 2),
(2, 1, 4),
(3, 1, 5),
(4, 2, 1),
(5, 3, 1),
(6, 4, 2),
(7, 6, 2),
(8, 7, 5),
(9, 9, 4),
(10, 9, 5),
(11, 2, 2);

The database itself is created through Entity framework migration for now.

I would like to know if there's a better, specifically more performance efficient way of writing this query because the WordTexts table will contain hundreds of thousands of records and eventually millions. I am also open to going a NoSql route if that would be more efficient for these kinds of queries.

SELECT *
FROM "PulledTexts"
WHERE "Id" IN (
 SELECT "TextIdId"
 FROM "WordTexts" AS "wordTexts"
 LEFT JOIN "UniqueWords" AS "wordTexts.WordId" ON "wordTexts"."WordIdId" = "wordTexts.WordId"."Id"
 WHERE "wordTexts.WordId"."WordText" = 'automate'

 OR "TextIdId" IN (
  SELECT "TextIdId" and1
  from "WordTexts" AS "wordTexts"
  LEFT JOIN "UniqueWords" AS "wordTexts.WordId" ON "wordTexts"."WordIdId" = "wordTexts.WordId"."Id"
  where "wordTexts.WordId"."WordText" = 'audit' INTERSECT

  SELECT "TextIdId" and2
  from "WordTexts" AS "wordTexts"
  LEFT JOIN "UniqueWords" AS "wordTexts.WordId" ON "wordTexts"."WordIdId" = "wordTexts.WordId"."Id"
  WHERE "wordTexts.WordId"."WordText" = 'trial'
 )
);

At a high level, this query is supposed to return PulledTexts whose IDs match those returned by the subqueries. The subqueries are simply meant to return the list of IDs of PulledTexts that contain lets say ('audit' AND 'trial') OR 'automate' from the UniqueWords table. This is exactly what the example query I gave does. The WordTexts table is a simple mapping of UniqueWord to PulledText.

While your query is valid, there is a lot I would differently.

Don't use CaMeL-case names in Postgres if it can be avoided. Your unnamed entity framework may force this nonsense upon you, but I don't want to deal with the double-quote mess, so I tested with your schema after removing all double-quotes - effectively making all identifiers lower-cased.

Are PostgreSQL column names case-sensitive?

Don't use illegible or illegal column and table aliases (like "wordTexts.WordId"). That's a matter of taste and style (and sanity), but you also omitted the keyword AS where you should not and kept it where you could omit it.

Date column arithmetic in PostgreSQL query

I also formatted some more to make it easier for me to grok the query. That last part is totally optional. But use some consistent formatting style.

Arriving at this:

SELECT *
FROM   PulledTexts
WHERE  Id IN (
   SELECT w.TextIdId
   FROM   WordTexts w  -- AS can be omitted for table alias
   LEFT   JOIN UniqueWords u ON w.WordIdId = u.Id  -- LEFT JOIN might be necessary here
   WHERE  u.WordText = 'automate'
   OR     w.TextIdId IN (
      SELECT w.TextIdId  -- AS and1 -- column alias only documentation here, not visible
      FROM   WordTexts w
      JOIN   UniqueWords u ON w.WordIdId = u.Id  -- LEFT JOIN misleading here
      WHERE  u.WordText = 'audit'

      INTERSECT
      SELECT w.TextIdId  -- AS and2  -- but don't omit AS for column alias
      FROM   WordTexts w
      JOIN   UniqueWords u ON w.WordIdId = u.Id
      WHERE  u.WordText = 'trial'
      )
   );

Which can be simplified to:

SELECT *
FROM  (
   SELECT w.TextIdId AS Id
   FROM   WordTexts   w
   JOIN   UniqueWords u ON w.WordIdId = u.Id  -- now we don't need LEFT any more
   WHERE  u.WordText = 'automate'

   UNION
   SELECT w.TextIdId
   FROM   WordTexts w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'audit'

   INTERSECT
   SELECT w.TextIdId
   FROM   WordTexts w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'trial'
   ) w
JOIN   PulledTexts p USING (Id)

We do not need additional parentheses because, according to the manual:

  INTERSECT binds more tightly than UNION. That is, A UNION B INTERSECT C will be read as A UNION (B INTERSECT C).

But this will be faster when replacing multiple intersected subqueries:

SELECT *
FROM  (
   SELECT w.TextIdId AS Id
   FROM   WordTexts   w
   JOIN   UniqueWords u ON w.WordIdId = u.Id
   WHERE  u.WordText = 'automate'

   UNION
   SELECT TextIdId
   FROM   WordTexts w1
   JOIN   WordTexts w2 USING (TextIdId)
   WHERE  w1.WordIdId = (SELECT Id FROM UniqueWords WHERE WordText = 'audit')
   AND    w2.WordIdId = (SELECT Id FROM UniqueWords WHERE WordText = 'trial')
   ) w
JOIN   PulledTexts p USING (Id)

The INTERSECT part can be cast as relational division problem. Explanation in this related answer from just yesterday:

Counting with multiple joins

db<>fiddle here

Most important for performance is to have the right indexes. You probably should have a UNIQUE constraint on (WordIdId, TextIdId) in table WordTexts, which implements the currently missing index on those two columns in this order.

Does creating a unique constraint on a Postgres column remove the need to index it?


Update Statement takes forever to complete I have table around 1250000 records, and 80 columns

Table Structure

CREATE TABLE [dbo].[Table] (
[Column1] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Column2] [int] NULL, [Column3] [int] NULL,
[Column4] [int] NULL, [Column5] [int] NULL, [Column6] [int] NULL, [Column7] [int] NULL, [Column8] [int] NULL,
[Column9] [int] NULL, [Column10] [varchar](20) NULL, [Column11] [datetime] NULL, [Column12] [int] NULL,
[Column13] [money] NULL, [Column14] [money] NULL, [Column15] [varchar](40) NULL, [Column16] [int] NULL,
[Column17] [int] NULL, [Column18] [varchar](20) NULL, [Column19] [varchar](50) NULL, [Column20] [int] NULL,
[Column21] [int] NULL, [Column22] [int] NULL, [Column23] [int] NULL, [Column24] [int] NULL,
[Column25Date] [datetime] NULL, [Column26] [int] NULL, [Column27] [int] NULL, [Column28] [int] NULL,
[Column29] [int] NULL, [Column30] [int] NULL, [Column75] [varchar](50) NULL, [Column31] [int] NULL,
[Column32] [varchar](15) NULL, [Column33] [int] NULL, [Column34] [money] NULL,
[Column35] [dbo].[T_Boolean2] NOT NULL, [Column36] [money] NULL, [Column37] [money] NULL,
[Column38] [int] NULL, [Column39] [int] NULL, [Column40] [varchar](255) NULL, [Column41PrintDate] [datetime] NULL,
[Column42PostDate] [datetime] NULL, [Column43FirstName] [varchar](30) NULL, [Column44LastName] [varchar](30) NULL,
[Column45Email] [varchar](50) NULL, [Column46PrimaryPhone] [varchar](10) NULL,
[Column47SecondaryPhone] [varchar](10) NULL, [Column48PrimaryPhoneType] [smallint] NULL,
[Column49SecondaryPhoneType] [smallint] NULL, [Column50PrimaryPhoneType] [smallint] NULL,
[Column51SecondaryPhoneType] [smallint] NULL, [Column52EndDate] [datetime] NULL,
[Column53] [dbo].[T_Boolean2] NOT NULL, [Column54] [int] NULL, [InsertTimeStamp] [datetime] NOT NULL,
[UpdateTimeStamp] [datetime] NULL, [Column55ID] [int] NULL, [Column56] [int] NULL, [Column57] [int] NULL,
[Column58] [int] NULL, [Column59] [money] NULL, [Column60] [money] NULL, [Column61] [money] NOT NULL,
[Column62] [money] NOT NULL, [Column63] [varchar](30) NULL, [Column64] [varchar](30) NULL,
[Column65] [varchar](10) NULL, [Column66] [varchar](6) NULL, [Column67] [varchar](10) NULL,
[Column68] [varchar](35) NULL, [Column69Date] [datetime] NULL, [Column70Date] [datetime] NULL,
[Column71Date] [datetime] NULL, [Column72] [varchar](255) NULL, [Column73PIN] [varchar](6) NULL,
[Column74Date] [datetime] NULL, [Column76Date] [datetime] NULL,
CONSTRAINT [XPKColumn1] PRIMARY KEY CLUSTERED (
[Column1] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

It has 2 Update triggers also to perform background calculations
which will update same Table when status changes and some other conditions

When I Update one is its column with normal where condition it is running more than 1 hour but still couldn't able to update the records so i am forced to kill the statement

Update Table Column5 = 12345 where Column5 = 6789

It has to update around 35000 records

Table does have proper indexes with this column and i manually update  statistics of the table but still taking too much time to update

Around 10 indexes are there for table

Then I tried update in 2 batches it updated but took very time

I tried to update other column of same table it took few seconds to update

Update Table Column3 = 6789 where Column3 = 12345

how i can improve update statement queries speed fast and using above simple logic

Please suggest any solution,

Thanks in advance

It's difficult to say exactly what's causing it without seeing the code for the trigger, but I'd bet some non-zero amount that misuse of the INSERTED and DELETED virtual tables is to blame.

The thing about INSERTED and DELETED is that they have NO INDEXES. If you join them directly back to the base table (or to each other), you can end up with some very awful performance.

You can generally mitigate this by loading the two into temp tables and creating indexes for however you'll be joining them (usually on the primary key).

SELECT *
INTO #i
FROM INSERTED

CREATE CLUSTERED INDEX CX_i ON #i (primary_key)

SELECT *
INTO #d
FROM DELETED

CREATE CLUSTERED INDEX CX_d ON #d (primary_key)

Something like that, anyway. Then only use your temp tables, and don't touch INSERTED and DELETED.

Be aware that the temp table names have to be unique per session. If you have multiple triggers cascading, they need to use unique table names. You may want to use table variables instead if this becomes a problem, as they are scoped to the trigger/procedure they are running in, with no risk of name collisions. (However, the only way to index a table variable is to declare a clustered primary key in your DECLARE statement.)


Accessing an external database through pgAdmin installed on my local machine? I want to query a database stored in an external location. Up to now, I was using Microsoft Access for querying, but due to limitation of 2 GB output file, I am trying to switch to another DBMS, PostgreSQL.

How do I connect to the external database using pgAdmin, installed on my machine?

The short answer is choose Add server from the file menu and enter the details of the server. However there are many reason's why it may not work. I'll itemize the most common problems:

postgresl isn't listening
postgresl's pg_hba.conf doesn't allow access from outside
the server's firewall is blocking access
a router is blocking access

If you run into problems, update your question and please include any error messages you get.

BTW When opening up access to postgres from the outside you may want to enforce the use of SSL.


Could not connect to the specific instance. MySQL error number 1045. Access denied for user 'root'@'my public ip' (using password:YES) I was using MySQL query Browser in my system and type the IP address of the remote Server and log in. I tried to create a store procedure and scheduled an event to call the procedure every two minutes. I got the error "Access denied for user 'root'@'%'". To solve this error I tried the below queries.

GRANT ALL PRIVILEGES ON database.* TO 'root'@'%' IDENTIFIED BY 'PWD';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'root'@'%
select * from mysql.user where User='root';

Now after executing these queries the error was solved and the event scheduler worked.

But now I am unable to login using the query browser in my system. I am getting another error when I try to login using the query browser from my system.

The error I am getting when I try to login using the query browser from my system is this --> "Could not connect to the specific instance. MySQL error number 1045. Access denied for user 'root'@'my public ip' (using password:YES)". How can I solve this error?

1) 'my public ip' is this ip allowed to connect your mysql server?
note : i know you have allowed from everywhere '%' but better to check again, whether your server ('my public ip') is allowed to connect to mysql server

2) If 1st is ok then please check your password. Looks like something wrong with the password (typo, or copy issue etc).

Hope this helps.


Security Considerations for AG vs Log Shipping we are considering removing our log shipping server and adding another node to our Availability Groups. While I understand that this should be better cause it's a/synchronous and will move the data to the node faster and can also be used for reporting, I don't know from a security point of view what might be the implications and concerns for the organisation.

Example: in case the primary node becomes infected from some malicious software and effects the logs, what will be the implications on all the other passive nodes.

We still plan to continue taking backups from the primary node frequently, but maybe someone has some experience on the topic.

I don't know from a security point of view what might be the implications and concerns for the organisation. [sic]

Nothing changes. It's still a separate OSE (operating system environment), still only has the logins you give it, etc., the only difference is now it's using a continuous log stream and will be coupled with the cluster (so availability may be impacted, but not the system, as compared to log shipping).

  Example: in case the primary node becomes infected from some malicious software and effects the logs, what will be the implications on all the other passive nodes.

None. We send logical changes, if those changes are corrupt or otherwise incorrect it'll be thrown out and you'll receive errors that the log stream is bad.


How to improve performance of a query with an IN subquery Get the count of other users having the x tag with order_id in order_id of users having the y tag

table: MyCustomTable (200K rows)

order_id
user_id
type ('x' and 'y') my custom tags

and the query:

SELECT   COUNT(order_id),user_id
FROM     MyCustomTable
WHERE    type='x'
AND      order_id IN (SELECT order_id
                      FROM   MyCustomTable
                      WHERE  type   ='y'
                      AND    user_id='56')
AND      user_id <> '56'
GROUP BY user_id

Create table output

CREATE TABLE `MyCustomTable` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `order_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `type` enum('x','y') NOT NULL DEFAULT 'x',
 PRIMARY KEY (`id`),
 KEY `customeKey` (`user_id`,`type`,`order_id`),
) ENGINE=InnoDB AUTO_INCREMENT=360806 DEFAULT CHARSET=utf8

One thing to try with IN is to change it to EXISTS (with the proper modifications to the subquery):

SELECT   COUNT(order_id), user_id
FROM     MyCustomTable a
WHERE    type='x'
AND      EXISTS (SELECT 1
                      FROM   MyCustomTable b
                      WHERE  type   ='y'
                      AND    user_id='56'
                      AND    a.order_id = b.order_id)
AND      user_id <> '56'
GROUP BY user_id

Another one is to get rid of the subquery:

SELECT   COUNT(distinct a.order_id), a.user_id
FROM     MyCustomTable a
JOIN     MyCustomTable b
             ON a.order_id = b.order_id
WHERE    a.type='x'
AND      b.type='y'
AND      a.user_id <> '56'
AND      b.user_id = '56'
GROUP BY a.user_id

Test them and use EXPLAIN to check the plans used to execute them. Your question does not show indexes in that table, for fast execution some indexes will be needed - a multicolumn index on (type, user_id, order_id) covers both the main query and the subquery for the first case (and the same for your original query with IN) and works for the second case too.


Is VACUUM FULL necessary on a DROP TABLE / TRUNCATE approach for a datawarehouse We are updating a datawarehouse on a weekly basis using a series of

TRUNCATE source_table1
COPY source_table1 FROM [...]

... for data import
and:

DROP TABLE IF EXISTS my_table
CREATE TABLE my_table AS SELECT [...]

for table updates.

We end our update process with a VACUUM FULL [VERBOSE] ANALYZE, because, as the documentation suggests, VACUUM should be done when a significant share of tuples has been updated or removed.
Here, as it is a share of 100% for all tables, we reasonably thought that VACUUM should be applied.

As we see the ouptput of the verbose option, it seems that Postgresql has not much to do, as every table VACUUMed gives:

INFO:  vacuuming "public.table345"
INFO:  "table345": found 0 removable, 9831703 nonremovable row versions in 62538 pages
DETAIL : 0 dead row versions cannot be removed yet.

I guess that on the contrary ANALYZE is more than helpfull to update the internal statistics.
Most tables are 10-100m rows big.

But we were wondering if VACUUM FULL or just VACUUM were really necessary in that case?

(Or may be the whole update process (DROP / CREATE TABLE AS) is not the right way to do?)

Point about TRUNCATE/COPY

Unrelated note, depending on the WAL level, wrapping the TRUNCATE and COPY in the same transaction things may go faster, because WAL will be skipped. Further the CTAS will always skip most WAL.

  In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include
  source

  CREATE TABLE AS
  CREATE INDEX
  CLUSTER
  COPY into tables that were created or truncated in the same transaction

    BEGIN;
      TRUNCATE source_table1
      COPY source_table1 FROM [...]
    COMMIT;

Issue of VACUUM FULL

There is no need to do a VACUUM FULL on a new table. The table is already new in those transactions so there is no need to rewrite it, as FULL does. There is also no need to VACUUM it because there are no movable rows that VACUUM will act on. When you run VACUUM FULL VERBOSE, you can see nothing was removable, and no dead rows were removed. VACUUM FULL can be useful if

you're staging INSERTS though a temp table, and adding them in batches into a dirty table. Then it can often pay off at the end of the process.
you're running UPDATE on a large batch because UPDATE generates new rows forcing VACUUM to later mark the old rows as dead.

A simple ANALYZE will work fine. This will update the statistics on the table.


Schema for a Soccer club I am designing a database for a Soccer club. A soccer club has several teams. Those teams have several players and a coach.

Right now I have three tables:

Club
Team (foreign key to club)
Player (foreign key to team)

My main concern is that, each season a Team brings in new players and therefore, I need somehow to state that. I want to be able to maintain a record of the Team along the different seasons, and also be able to know what a Team is in the current season. How could I model that?

You'll want to add another table Season, and another table SeasonPlayer with an FK to player, an FK to team and an FK to Season.

Then drop the FK from Team to Player in your current schema


Is it possible to use variables in MySQL schema definition? I need to do something like this:

use mydb;
SET @DBNAME = 'users';
CREATE TABLE `some_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`) USING BTREE,
  CONSTRAINT `key_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES @DBNAME.`users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
);

REFERENCES @DBNAME.users - is the part I'm interested in.

Is it possible to somehow parametrize DB name in schema definition?

Thanks!

You cannot use variables for object names. What you can do is compose the statement string dynamically and PREPARE then EXECUTE it.

SET @DBNAME = 'users';
SET @STMT = CONCAT(
'CREATE TABLE `some_table` (',
'  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,',
'  `user_id` bigint(20) unsigned DEFAULT NULL,',
'  PRIMARY KEY (`id`),',
'  KEY `user_id` (`user_id`) USING BTREE,',
'  CONSTRAINT `key_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES ', @DBNAME, '.`users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION'
);
PREPARE s1 FROM @STMT;
EXECUTE s1;


Create Enumerated Function Parameter Type I want to create a function similar to DATEDIFF in MS SQL Server 2008 R2.  The first parameter in DATEDIFF is a datepart.  Can I use datepart as a parameter in my function?  If not, how do I create a parameter similar to datepart?  I don't want to make the parameter text.

For example, I would like to have a function like this: MYFUNC(datepart, timestart, timeend)

So when I call the function, it would look like this: MYFUNC(Hour, N'08:00:00', N'12:00:00')

I'm currently using a nvarchar for the datepart param so my function looks like this: MYFUNC(N'Hour', N'08:00:00', N'12:00:00')

You can't do what Microsoft does with built-ins, sorry. You will need to write your function to accept a string, for example, and evaluate it inline, e.g.

CREATE FUNCTION dbo.CustomDateDiff
(
  @datepart VARCHAR(32), -- does not need to be NVARCHAR
  @start    DATETIME,
  @end      DATETIME
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT CASE @datepart
      WHEN 'HOUR'   THEN DATEDIFF(HOUR,   @start, @end)
      WHEN 'MINUTE' THEN DATEDIFF(MINUTE, @start, @end)
      WHEN 'WEEK'   THEN DATEDIFF(WEEK,   @start, @end)
      ELSE               DATEDIFF(DAY,    @start, @end)
    END);
END
GO

Not that you can't use a conditional inside DATEDIFF - a lot of people think you could say:

DATEDIFF(@datepart,

Or

DATEDIFF(CASE WHEN @datepart = 'HOUR' THEN HOUR END,

But neither of these will work - both yield:

  Msg 1023, Level 15, State 1, Line 2
  Invalid parameter 1 specified for datediff.


Running total with count? As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count:

sum(count (distinct (customers)))

Say if I ran the count alone, the result would be:

Day | CountCustomers
----------------------
5/1  |      1
5/2  |      0
5/3  |      5

I need output with the sum to be :

Day | RunningTotalCustomers
----------------------
5/1  |      1
5/2  |      1
5/3  |      6

I've done running totals before using the coalesce method, but never with a count. I'm not sure how to do it now that I have the count.

Here are a few methods you can compare. First let's set up a table with some dummy data. I'm populating this with a bunch of random data from sys.all_columns. Well, it's kind of random - I'm ensuring that the dates are contiguous (which is really only important for one of the answers).
CREATE TABLE dbo.Hits(Day SMALLDATETIME, CustomerID INT);

CREATE CLUSTERED INDEX x ON dbo.Hits([Day]);

INSERT dbo.Hits SELECT TOP (5000) DATEADD(DAY, r, '20120501'),
  COALESCE(ASCII(SUBSTRING(name, s, 1)), 86)
FROM (SELECT name, r = ROW_NUMBER() OVER (ORDER BY name)/10,
       s = CONVERT(INT, RIGHT(CONVERT(VARCHAR(20), [object_id]), 1))
FROM sys.all_columns) AS x;

SELECT
  Earliest_Day   = MIN([Day]),
  Latest_Day     = MAX([Day]),
  Unique_Days    = DATEDIFF(DAY, MIN([Day]), MAX([Day])) + 1,
  Total_Rows     = COUNT(*)
FROM dbo.Hits;

Results:
Earliest_Day         Latest_Day           Unique_Days  Total_Days
-------------------  -------------------  -----------  ----------
2012-05-01 00:00:00  2013-09-13 00:00:00  501          5000

The data looks like this (5000 rows) - but will look slightly different on your system depending on version and build #:
Day                  CustomerID
-------------------  ---
2012-05-01 00:00:00  95
2012-05-01 00:00:00  97
2012-05-01 00:00:00  97
2012-05-01 00:00:00  117
2012-05-01 00:00:00  100
...
2012-05-02 00:00:00  110
2012-05-02 00:00:00  110
2012-05-02 00:00:00  95
...

And the running totals results should look like this (501 rows):
Day                  c   rt
-------------------  --  --
2012-05-01 00:00:00  6   6
2012-05-02 00:00:00  5   11
2012-05-03 00:00:00  4   15
2012-05-04 00:00:00  7   22
2012-05-05 00:00:00  6   28
...

So the methods I am going to compare are:

"self-join" - the set-based purist approach
"recursive CTE with dates" - this relies on contiguous dates (no gaps)
"recursive CTE with row_number" - similar to above but slower, relying on ROW_NUMBER
"recursive CTE with #temp table" - stolen from Mikael's answer as suggested
"quirky update" which, while unsupported and not promising defined behavior, seems to be quite popular
"cursor"
SQL Server 2012 using new windowing functionality

self-join
This is the way people will tell you to do it when they're warning you to stay away from cursors, because "set-based is always faster." In some recent experiments I've found that the cursor out-paces this solution.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
)
SELECT g.[Day], g.c, rt = SUM(g2.c)
  FROM g INNER JOIN g AS g2
  ON g.[Day] >= g2.[Day]
GROUP BY g.[Day], g.c
ORDER BY g.[Day];

recursive cte with dates
Reminder - this relies on contiguous dates (no gaps), up to 10000 levels of recursion, and that you know the start date of the range you're interested (to set the anchor). You could set the anchor dynamically using a subquery, of course, but I wanted to keep things simple.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
), x AS
(
    SELECT [Day], c, rt = c
        FROM g
        WHERE [Day] = '20120501'
    UNION ALL
    SELECT g.[Day], g.c, x.rt + g.c
        FROM x INNER JOIN g
        ON g.[Day] = DATEADD(DAY, 1, x.[Day])
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

recursive cte with row_number
Row_number calculation is slightly expensive here. Again this supports max level of recursion of 10000, but you don't need to assign the anchor.
;WITH g AS
(
  SELECT [Day], rn = ROW_NUMBER() OVER (ORDER BY DAY),
    c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
), x AS
(
    SELECT [Day], rn, c, rt = c
        FROM g
        WHERE rn = 1
    UNION ALL
    SELECT g.[Day], g.rn, g.c, x.rt + g.c
        FROM x INNER JOIN g
        ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

recursive cte with temp table
Stealing from Mikael's answer, as suggested, to include this in the tests.
CREATE TABLE #Hits
(
  rn INT PRIMARY KEY,
  c INT,
  [Day] SMALLDATETIME
);

INSERT INTO #Hits (rn, c, Day)
SELECT ROW_NUMBER() OVER (ORDER BY DAY),
       COUNT(DISTINCT CustomerID),
       [Day]
FROM dbo.Hits
GROUP BY [Day];

WITH x AS
(
    SELECT [Day], rn, c, rt = c
        FROM #Hits as c
        WHERE rn = 1
    UNION ALL
    SELECT g.[Day], g.rn, g.c, x.rt + g.c
        FROM x INNER JOIN #Hits as g
        ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

DROP TABLE #Hits;

quirky update
Again I am only including this for completeness; I personally wouldn't rely on this solution since, as I mentioned on another answer, this method is not guaranteed to work at all, and may completely break in a future version of SQL Server. (I'm doing my best to coerce SQL Server into obeying the order I want, using a hint for the index choice.)
CREATE TABLE #x([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x([Day]);

INSERT #x([Day], c)
    SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
    ORDER BY [Day];

DECLARE @rt1 INT;
SET @rt1 = 0;

UPDATE #x
SET @rt1 = rt = @rt1 + c
FROM #x WITH (INDEX = x);

SELECT [Day], c, rt FROM #x ORDER BY [Day];

DROP TABLE #x;

cursor
"Beware, there be cursors here! Cursors are evil! You should avoid cursors at all costs!" No, that's not me talking, it's just stuff I hear a lot. Contrary to popular opinion, there are some cases where cursors are appropriate.
CREATE TABLE #x2([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x2([Day]);

INSERT #x2([Day], c)
    SELECT [Day], COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
    ORDER BY [Day];

DECLARE @rt2 INT, @d SMALLDATETIME, @c INT;
SET @rt2 = 0;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR SELECT [Day], c FROM #x2 ORDER BY [Day];

OPEN c;

FETCH NEXT FROM c INTO @d, @c;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @rt2 = @rt2 + @c;
  UPDATE #x2 SET rt = @rt2 WHERE [Day] = @d;
  FETCH NEXT FROM c INTO @d, @c;
END

SELECT [Day], c, rt FROM #x2 ORDER BY [Day];

DROP TABLE #x2;

SQL Server 2012
If you are on the most recent version of SQL Server, enhancements to windowing functionality allows us to easily calculate running totals without the exponential cost of self-joining (the SUM is calculated in one pass), the complexity of the CTEs (including the requirement of contiguous rows for the better performing CTE), the unsupported quirky update and the forbidden cursor. Just be wary of the difference between using RANGE and ROWS, or not specifying at all - only ROWS avoids an on-disk spool, which will hamper performance significantly otherwise.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
)
SELECT g.[Day], c,
  rt = SUM(c) OVER (ORDER BY [Day] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[Day];

performance comparisons
I took each approach and wrapped it a batch using the following:
SELECT SYSUTCDATETIME();
GO
DBCC DROPCLEANBUFFERS;DBCC FREEPROCCACHE;
-- query here
GO 10
SELECT SYSUTCDATETIME();

Here are the results of the total duration, in milliseconds (remember this includes the DBCC commands each time as well):
method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1296 ms   1357 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1655 ms   1516 ms
recursive cte with row_number   19747 ms  19630 ms
recursive cte with #temp table   1624 ms   1329 ms
quirky update                     880 ms   1030 ms -- non-SQL 2012 winner
cursor                           1962 ms   1850 ms
SQL Server 2012                   847 ms    917 ms -- winner if SQL 2012 available

And I did it again without the DBCC commands:
method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1272 ms   1309 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1247 ms   1593 ms
recursive cte with row_number   18646 ms  18803 ms
recursive cte with #temp table   1340 ms   1564 ms
quirky update                    1024 ms   1116 ms -- non-SQL 2012 winner
cursor                           1969 ms   1835 ms
SQL Server 2012                   600 ms    569 ms -- winner if SQL 2012 available

Removing both the DBCC and loops, just measuring one raw iteration:
method                          run 1     run 2
-----------------------------   --------  --------
self-join                         313 ms    242 ms
recursive cte with dates          217 ms    217 ms
recursive cte with row_number    2114 ms   1976 ms
recursive cte with #temp table     83 ms    116 ms -- "supported" non-SQL 2012 winner
quirky update                      86 ms     85 ms -- non-SQL 2012 winner
cursor                           1060 ms    983 ms
SQL Server 2012                    68 ms     40 ms -- winner if SQL 2012 available

Finally, I multiplied the row count in the source table by 10 (changing top to 50000 and adding another table as a cross join). The results of this, one single iteration with no DBCC commands (simply in the interests of time):
method                           run 1      run 2
-----------------------------    --------   --------
self-join                         2401 ms    2520 ms
recursive cte with dates           442 ms     473 ms
recursive cte with row_number   144548 ms  147716 ms
recursive cte with #temp table     245 ms     236 ms -- "supported" non-SQL 2012 winner
quirky update                      150 ms     148 ms -- non-SQL 2012 winner
cursor                            1453 ms    1395 ms
SQL Server 2012                    131 ms     133 ms -- winner

I only measured duration - I'll leave it as an exercise to the reader to compare these approaches on their data, comparing other metrics that may be important (or may vary with their schema/data). Before drawing any conclusions from this answer, it'll be up to you to test it against your data and your schema... these results will almost certainly change as the row counts get higher.

demo
I've added a sqlfiddle. Results:

conclusion
In my tests, the choice would be:

SQL Server 2012 method, if I have SQL Server 2012 available.
If SQL Server 2012 is not available, and my dates are contiguous, I would go with the recursive cte with dates method.
If neither 1. nor 2. are applicable, I would go with the self-join over the quirky update, even though the performance was close, just because the behavior is documented and guaranteed. I'm less worried about future compatibility because hopefully if the quirky update breaks it will be after I've already converted all my code to 1. :-)

But again, you should test these against your schema and data. Since this was a contrived test with relatively low row counts, it may as well be a fart in the wind. I've done other tests with different schema and row counts, and the performance heuristics were quite different... which is why I asked so many follow-up questions to your original question.

UPDATE
I've blogged more about this here:
Best approaches for running totals – updated for SQL Server 2012


Case Behavior Changed Between 11.2.0.1 and 11.2.0.2 I have a case open with Oracle and they say they can't reproduce this.  Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query returns three rows unless the case statement is removed.  Can anyone confirm this behavior?

create table t1 as (select rownum+1 id1 from dual connect by rownum<=3);
create table t2 as (select rownum+1 id2 from dual connect by rownum<=2);

--Correct version that returns two rows.
SELECT t1.id1 id1, t2.id2 id2, t3.id3 id3
, DECODE(1,1,'a','b') "Case/Decode"
FROM t1
LEFT JOIN t2 ON t1.id1 = t2.id2
LEFT JOIN (SELECT 1 id3 FROM dual) t3 ON t3.id3 = t1.id1
WHERE (t2.id2 IS NOT NULL or t3.id3 IS NOT NULL);

--Incorrect version that returns three rows when it should return two.
SELECT t1.id1 id1, t2.id2 id2, t3.id3 id3
, case when 1=1 then 'a' else 'b' end "Case/Decode"
FROM t1
LEFT JOIN t2 ON t1.id1 = t2.id2
LEFT JOIN (SELECT 1 id3 FROM dual) t3 ON t3.id3 = t1.id1
WHERE (t2.id2 IS NOT NULL or t3.id3 IS NOT NULL);

The same issue seems to be effecting the following, which returns the wrong data even though the number of rows is the same.

create table t1 as (select rownum id1 from dual connect by rownum<=3);

--Correct version.
SELECT t1.id1, NVL(t2.id2,'NULL') id2, 'No' Case_Present, t2.Constant_Value
FROM t1
LEFT JOIN (select null id2, 'Should Be NULL' Constant_Value from dual) t2
ON t1.id1 = t2.id2;

--Incorrect version shows values for Constant_Value when it shouldn't.
SELECT t1.id1, NVL(t2.id2,'NULL') id2
, case
when 1=1 then ('Yes')
else ' '
end as Case_Present
, t2.Constant_Value
FROM t1
LEFT JOIN (select null id2, 'Should Be NULL' Constant_Value from dual) t2
ON t1.id1 = t2.id2;

Can reproduce on the 11gR2 database hosting apex.oracle.com

The following query results in 3 rows:

SELECT t1.*, t2.*, t3.dummy, case when 1=1 then 'a' else 'b' end "Case/Decode"
FROM t1
LEFT JOIN (select 1 dummy from dual) t3 ON t3.dummy = t1.id1
LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE (t2.id2 IS NOT NULL or t3.dummy IS NOT NULL)

Removing the CASE selection gives two rows and changes the value of t3.dummy from '1' to null.

Using the EXPLAIN PLAN, you can see that adding the CASE removes the FILTER

"T2"."ID2" IS NOT NULL OR CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NOT NULL"

I suspect the optimizer is rejigging the link to dual. An outer join to dual, performed in a inline view, is very odd thing so I suspect it won't affect many people.