SQL Server performance is slow when migrated from SQL Server 2012 to SQL Server 2014 We are trying to migrate from SQL Server 2012 to SQL Server 2014 on Windows 2012 R2 OS.

After the upgrade, we are doing performance testing and observed huge difference in running a simple query from only one table (No joins at all).

The query is getting executed in 4 seconds in SQL Server 2012, but taking more than 1 minute in SQL Server 2014.

The SQL Servers are installed on 2 different VMs (different IP addresses).
They have only one SQL Server instance on each machine.

The processor / ram / hard drive space is same between 2 machines.

The machines have the same applications installed. We are running the same select query from the SQL Server Management Studio directly.

This most probably has something to do with the new cardinality estimator.

See my reply to this question.

If performance improves when you change the compatibility mode you have a good chance you are running in the same problem.


Migrate SQL Server binary datadump to postgresql I just got a very big dataset (+/-55GB) in a binary SQL Server format.

Is there an easy way to import this file into a Postgresql Server? I found some converter-programs, but they 'only' convert from server to server and not file to file or file to server.

I don't have access to a SQL Server, so importing it and using this temporary as a source is not an option. Nor am I an expert in SQL Server programs...

I hope somebody here has a solution....

What you are seeing is SQL Server data pages in a backup file. Or a SQL Server data file (.MDF). Either way, is is a collection of 8k data pages.

These would be restored or attached to a SQL Server.
They are not conveniently readable without a SQL Server install.

No SQL Server = no access to the data. Quite simple.

If you don't have access to a SQL Server install, why on earth do you have a SQL Server data file/backup? What do you or your source expect you to do with it?

So, your solution is clear: ask your source to export the data to CSV and provide the database design.Then you implement and import into PostgreSQL.


sqlcmd new login/user I am  new to sqlcmd and I want to create a new user. I have done the following:

1> CREATE LOGIN FYI_DBA WITH PASSWORD = 'V_2017'
2> go
1> CREATE USER FYI_DBA FOR LOGIN FYI_DBA
2> go

Then from cmd prompt I ran  the following:

sqlcmd -s LOCALHOST -U 'FYI_DBA' -P 'V_2017'
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ''FYI_DBA''..

What am I doing wrong?

Check that your SQL Server allows SQL authentication. By default it will be windows authentication only. You can check the SQL Server errorlog which will provide the login failure reason.

The message I you would see in the log in that situation would be

  Login failed for user 'FYI_DBA'. Reason: An attempt to login using SQL
  authentication failed. Server is configured for Windows authentication
  only. [CLIENT: ]

You can change the authentication type using the GUI or T-SQL


Extremely slow query on indexed column in Postgres I'm getting a extremely slow query on an indexed column. Given the query

SELECT *
FROM orders
WHERE shop_id = 3828
ORDER BY updated_at desc
LIMIT 1

explain analyze returned:

    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..594.45 rows=1 width=175) (actual time=202106.830..202106.831 rows=1 loops=1)
   ->  Index Scan Backward using index_orders_on_updated_at on orders  (cost=0.43..267901.54 rows=451 width=175) (actual time=202106.827..202106.827 rows=1 loops=1)
         Filter: (shop_id = 3828)
         Rows Removed by Filter: 1604818
 Planning time: 98.579 ms
 Execution time: 202127.514 ms
(6 rows)

The table description is:

                                         Table "public.orders"
       Column       |            Type             |                           Modifiers
--------------------+-----------------------------+---------------------------------------------------------------
 id                 | integer                     | not null default nextval('orders_id_seq'::regclass)
 sent               | boolean                     | default false
 created_at         | timestamp without time zone |
 updated_at         | timestamp without time zone |
 name               | character varying(255)      |
 shop_id            | integer                     |
 recovered_at       | timestamp without time zone |
 total_price        | double precision            |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "index_orders_on_recovered_at" btree (recovered_at)
    "index_orders_on_shop_id" btree (shop_id)
    "index_orders_on__updated_at" btree (updated_at)

It's a Postgres server, running on an AWS RDS t2 micro instance.
The table has around 2.6 million rows.

There is a subtle problem hidden in your ORDER BY clause:
ORDER BY updated_at DESC

Would sort NULL values first. I assume you do not want that. Your column updated_at can be NULL (lacks a NOT NULL constraint). The missing constraint should probably be added. Your query should be fixed either way:
SELECT *
FROM   orders
WHERE  shop_id = 3828
ORDER  BY updated_at DESC NULLS LAST
LIMIT  1;
The multicolumn index @Ste Bov mentioned should be adapted accordingly:
CREATE INDEX orders_shop_id_updated_at_idx ON orders (shop_id, updated_at DESC NULLS LAST);
Then you get a basic Index Scan instead of the (almost as fast) Index Scan Backward, and you won't get an additional index condition: Index Cond: (updated_at IS NOT NULL).
Related:

Unused index in range of dates query
Optimizing queries on a range of timestamps (two columns)

Asides
You can save a bit of wasted disk space by optimizing the sequence of columns for your big table (which makes everything a bit faster):
id                 | integer                     | not null default nextval( ...
shop_id            | integer                     |
sent               | boolean                     | default false
name               | varchar(255)                |
total_price        | double precision            |
recovered_at       | timestamp without time zone |
created_at         | timestamp without time zone |
updated_at         | timestamp without time zone |

See:

Configuring PostgreSQL for read performance

Add NOT NULL constraints to all columns that cannot be NULL.
Consider text or varchar instead of varchar(255), timestamptz instead of timestamp and integer for the price (as Cents) or numeric (for fractional numbers) which is an arbitrary precision type and stores your values exactly as given. Never use a lossy floating point type for a "price" or anything to do with money.


Optimising a query against sys.fn_dblog Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc..

Here is the query:

SET NOCOUNT ON
DECLARE @LSN NVARCHAR(46)
DECLARE @LSN_HEX NVARCHAR(25)
DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))
DECLARE @stmt VARCHAR(256)
DECLARE @NUMOFTRANSACTIONS AS INT = 3;

-- common table expression to get the last N LSNs from the LDF
with currentlsns as (
SELECT TOP(@NUMOFTRANSACTIONS) [Current LSN] FROM fn_dblog(NULL, NULL) WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] DESC
)
-- set the @LSN to the lowest because we are reading forward
SELECT @LSN = MIN([Current LSN]) from currentlsns

-- Convert @LSN to HEX for use in fn_dblog
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 1, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 10, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 19, 4) + ' AS INT)'
INSERT @tbl EXEC(@stmt)

SET @LSN_HEX =
 (SELECT i FROM @tbl WHERE id = 1) + ':' + (SELECT i FROM @tbl WHERE id = 2) + ':' + (SELECT i FROM @tbl WHERE id = 3)

-- insert everything into a temp table so we only refer to fn_dblog once
IF OBJECT_ID('tempdb..#transactions') IS NOT NULL
DROP TABLE #transactions

SELECT * INTO #transactions
FROM fn_dblog (@LSN_HEX, NULL)

-- now show last N transactions and some connection, query info for those transactions
SELECT fd.SPID, es.login_name, text, Description, Operation, Context, [Log Reserve],AllocUnitName,[Begin Time],[Transaction Name],
[End Time], [Transaction Begin],[Number of Locks]
FROM
    #transactions,
    (   SELECT
            [Transaction ID] AS [tid],
            [Transaction SID],
            SPID
        FROM #transactions
        WHERE
        [Transaction SID] IN (select top(@NUMOFTRANSACTIONS) [Transaction SID] FROM #transactions WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] desc)
   ) [fd]
INNER JOIN sys.dm_exec_connections c on c.session_id = fd.SPID
LEFT OUTER JOIN sys.dm_exec_sessions es ON es.session_id = fd.SPID
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) as st
WHERE
    [Transaction ID] = [fd].[tid]
    AND [Transaction ID] IN (select top(@NUMOFTRANSACTIONS) [Transaction ID] FROM #transactions WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] desc
 )

This part of the query takes too long

-- common table expression to get the last N LSNs from the LDF
with currentlsns as (
SELECT TOP(@NUMOFTRANSACTIONS) [Current LSN] FROM fn_dblog(NULL, NULL) WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] DESC
)
-- set the @LSN to the lowest because we are reading forward
SELECT @LSN = MIN([Current LSN]) from currentlsns

I just want to get the Nth from the last LSN where the Transaction SID is NOT NULL. The only way I could think of to accomplish this was by creating a CTE and inserting the last N LSN's that match.

Question:
What would be a better way to find the last N'th Current LSN where [Transaction SID] IS NOT NULL?

SELECT * into dbo.dblog

FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'C:\tranlog.trn',
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
GO

Then add indexing, etc to the table dbo.dblog.


What is better - having 10 databases for 10 organisations or 1 table for 10 organisation with org_id? I am making a web service (using Laravel in case that is relevant) that will have many separate organisations  generated by the service.
Considerations

I can manage all organisation data in one database by assigning an org_id column in each table, but when a row is fetched it will have to be inquired from all organisation rows (which I think will take more time).
Another solution is to make separate database for every organisation, but then I think I have to switch databases frequently and for each request causing more database connections and in turn causing more server processing involved.

Question
So I want to ask what approach will best suit my needs.

it all depends.

from my personal experience, if you have one database for each organisation, all the data is neat and clean and it is all in one place for each different organisation.

In case there are changes specific to a particular organisation it will affect only that particular organisation database - as the changes may or may not be welcomed for the other organisations.

On the other hand, it might cause extra overhead if for each new organisation you will need to create a new database, and that involves making sure you have space for backups, and scheduling time to run all the typical database maintenance routines, etc.

Basically different databases give you more flexibility (even the permissions set could be different) but it also might cause overhead.

There are strong points and not so good point in either approach.


Unable to Watch Live Data on Extended Events Session I am running SQL Server 2014 Developer Edition on my PC.  I am trying to view the data in the system_health session.  In SSMS, I have connected to the database, expanded the server / Management / Extended Events / Sessions.  I see AlwaysON_health (stopped) and system_health (running).

When I right-click on the system_health session, I get the following error:

  The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage)
     The Extended Events session named "system_health" could not be found. Make sure the session exists and is started. (Microsoft SQL Server, Error: 25728)

I expand system_health and see the targets package0.event_file and package0.ring_buffer.  If I right-click either target and choose "View Target Data", I get this error:

  The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage)
  Cannot view the function 'fn_MSXe_read_event_stream', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

The function does exist.  I can run it:

select * from fn_MSXe_read_event_stream('system_health', 0);

When I do, I get this error:

Msg 25728, Level 16, State 10, Line 6
The Extended Events session named "system_health" could not be found. Make  sure the session exists and is started.

I know the system_health session is there.  I see in in the list of sessions:

select * from sys.dm_xe_sessions

address name       name
------------------ -------------
0x00000001FF6510C1 system_health

I have tried this with my own custom event sessions.  I can't watch the live data on them, either.

I can query the system_health ring buffer target data from sys.dm_xe_session_targets.

Why can't I watch live data for any extended events session?

(Note that there is a Microsoft feedback item for this issue.)

I found the problem.  My login had a different default database than the master database.  When I changed my default database to master, the error went away, and I was able to Watch Live Data on the extended event sessions.

To change the default database, in SSMS, I expanded the server, Security, Logins.  I right-clicked on my user credentials. On the Login Properties page for my user, I changed "Default database" to master.  The error went away when I closed and reopened the connection in the SSMS Object Explorer.

EDIT 2018-12-11

On the feedback link for this issue, Karl Fasick posted a likely solution:

  Object Explorer isn't connected to the master database...
  To fix - disconnect Object Explorer - Reconnect, but go to Connection
  Properties and explicitly choose master.


Does PostgreSQL consider how evenly data is distributed on disk when choosing a seq scan? I have a table items with an indexed column type_id.  The table contains about 80 million rows.  At the moment the first 5 million (in order of insertion) and the last 3 million have type_id = 8, and all others have type_id = 12.

I run a query with where type_id = 8 limit 10.  Sometimes it takes a fraction of a second, and sometimes it takes minutes.  Here is explain output:

inventory=# explain select id from items where type_id = 8 limit 10;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5.07 rows=10 width=118)
   ->  Seq Scan on items  (cost=0.00..4014534.00 rows=7915149 width=118)
         Filter: (type_id = 8)
(3 rows)

inventory=# explain select id from items where type_id = 8;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on items  (cost=182418.97..3251192.33 rows=7915149 width=118)
   Recheck Cond: (type_id = 8)
   ->  Bitmap Index Scan on items_db97e9bb  (cost=0.00..180440.18 rows=7915149 width=0)
         Index Cond: (type_id = 8)
(4 rows)

Looking at the answers to other questions here, I conclude that the query planner chooses a seq scan because of the low limit, because it expects to find 10 matches from the first ~100 records it scans.  But in my case, the matching records are not evenly spread on disk - they are at the very beginning and at the very end of the table.  Does the query planner take that into account?

Edit: here is my postgresql.conf (only listing changed values):

max_connections = 2000
shared_buffers = 3GB
effective_cache_size = 9GB
min_wal_size = 10GB
max_wal_size = 10GB
checkpoint_completion_target = 0.9
default_statistics_target = 10000
work_mem = 100MB
maintenance_work_mem = 2GB

The server has 16GB memory, uses Amazon EBS "General Purpose SSD" storage, and this database is the only resource-intensive thing it runs.

Edit2: I have just run analyze verbose items;, and the query planner still chooses the same plans.

In short, No.

One of the statistics which PostgreSQL collects is the correlation between the values of a column (actually the ranks of those values) versus the location of that row in the table.  You can find that result in pg_stats.correlation for your specific tablename and colname.

However, the particular query you give is not one of the situations where this information is used.  The primary use is to estimate how much of the IO done for the table-lookup step of a large index range scan (e.g. "WHERE state='Ca'", or "WHERE age is between 50 and 65") will be sequential-like IO (either really sequential, or close enough to sequential so that read-ahead still triggers, or falling into the same table-block as the previous row so requiring no IO at all) rather than random IO.

Also, since your relation is not monotonic but rather more like a parabola, the estimated correlation will undervalue the strength of the correlation in your case, as it only looks at linear correlations.


Return a record with PL/pgSQL function - to speed up the query I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is banned or if the player has a VIP status), then I use synchronous queries.

This makes the game stop for a short moment, until the value has been read from the database.

I can not rewrite my game daemon to use async queries for reading values (I tried, but it required too many changes), so my question is: would it make sense to combine several unrelated queries (that I need to make when a new player connects) to 1 procedure and how could I return several values at the same time to my Perl program?

My current queries all take a player ID as parameter and return 1 value:

-- Has the player been banned?
select true from pref_ban where id=?

-- What is the reputation of this player?
select
count(nullif(nice, false)) -
count(nullif(nice, true)) as rep
from pref_rep where id=?

-- Is he or she a special VIP player?
select vip > now() as vip from pref_users where id=?

-- How many games has the player played to the end?
select completed from pref_match where id=?

To combine the above queries I probably need a procedure like this one:

create or replace function get_user_info(_id varchar) returns XXX as $BODY$
    declare
        is_banned boolean;
        reputation integer;
        is_vip boolean;
        completed_games integer;
    begin

        select 1 into is_banned from pref_ban where id=_id;

        select
        count(nullif(nice, false)) -
        count(nullif(nice, true))
        into reputation
        from pref_rep where id=_id;

        select vip > now() into is_vip from pref_users where id=_id;

        select completed into completed_games from pref_match where id=_id;

        return XXX; /* How to return 4 values here? */

    end;
$BODY$ language plpgsql;

Please help me to declare the above procedure properly.

Using OUT parameters achieve basically the same thing as in @klin's answer, but without creating user-defined types. Just move all your variables from the declare block into the argument-list as OUT parameters:

create or replace function get_user_info(
    IN  _id varchar,
    OUT is_banned boolean,
    OUT reputation integer,
    OUT is_vip boolean,
    OUT completed_games integer
)
-- no returns clause necessary, output structure controlled by OUT parameters
-- returns XXX
as $BODY$
begin
    select true into is_banned from pref_ban where id=_id;

    select
    count(nullif(nice, false)) -
    count(nullif(nice, true))
    into reputation
    from pref_rep where id=_id;

    select vip > now() into is_vip from pref_users where id=_id;

    select completed into completed_games from pref_match where id=_id;

    -- no return statement necessary, output values already stored in OUT parameters
    -- return XXX;
end
$BODY$ language plpgsql;

This will return a record (exactly one), so you can select its values as a normal record:

-- this will return all properties (columns) from your function:
select * from get_user_info();

-- these will return one property (column) from your function:
select is_banned from get_user_info();
select (get_user_info()).is_banned;


Memory-Optimized Table Memory Allocated issues I have Some In-Memory OLTP objects in SQL Server and it's version SQL Server 2016 SP2-CU4.
I have a problem because of data row-size of some tables greater than 8060bytes, so there was some off-row storage in internal xtp DMVs and those tables allocated a memory about 120GB.

I alter that tables and reduce row-size to less than 8060 bytes, the memory allocated to those tables reduce and the allocated memory to object_id=0 is being grow in sys.dm_db_xtp_table_memory_stats and it's about 50GB. I think The garbage collector thread cant find this orphaned object , thus it cant deallocate memory.

another issue is  'Memory Allocated To Memory Optimized Objects' is 25GB  where 'Memory Used By Memory Optimized Objects' is 62GB, may be I think this is a bug

how I can deallocate this memory ?!

You should check out this blog post from the king of In-Memory OLTP, Ned Otter:

Row version lifecycle for In-Memory OLTP

In particular, this passage:

  2. Deallocating rows from memory

  ...

  So the two triggers for Garbage Collection are memory pressure and/or transactional activity. Conversely, that means if there’s no memory pressure – or transactional activity is low – it’s perfectly reasonable to have row versions that aren’t garbage collected. There’s also no way to force garbage collection to occur.

So this is just to clarify that there are situations where garbage collection may not occur, or may occur slowly.

The post goes on to discuss that long-running queries on in-memory tables will completely block garbage collection, so make sure to check for any of those.

The reason the screenshot of SSMS is off (used it higher than allocated) is because it's purely based on the DMVs, so the NULL memory_allocated_for_table_kb throws off the calculation.  This is what runs when you open the database properties window:

SELECT
    isnull((
        select convert(decimal(18,2),(sum(tms.memory_allocated_for_table_kb) + sum(tms.memory_allocated_for_indexes_kb)))
        from [sys].[dm_db_xtp_table_memory_stats] tms)
    , 0.00) AS [MemoryAllocatedToMemoryOptimizedObjectsInKB]

SELECT
    isnull((
        select convert(decimal(18,2),(sum(tms.memory_used_by_table_kb) + sum(tms.memory_used_by_indexes_kb)))
        from [sys].[dm_db_xtp_table_memory_stats] tms)
    , 0.00) AS [MemoryUsedByMemoryOptimizedObjectsInKB]

I'm still not really sure what the purpose of that object_id = 0 row is in dm_db_xtp_table_memory_stats.  I can repeat the behavior locally (create a table with LOB data, then ALTER the column to fit in-row, suddenly I have that same 0 result), but I don't know if it's a problem.

It's possible that it's normal overhead related to in-memory OLTP, but it might be a bug.  If it's causing significant problems, I'd suggest opening a case with Microsoft.  There have been bugs before related to memory usage with LOB data, see MSDN Forums | In Memory OLTP for an example, and this CU: FIX: Out-of-memory error when you run a query to access LOB columns through In-Memory OLTP in SQL Server 2016.


Rank groups based on order A          B          C
     1        132          1
     1        155          2
     1        132          3
     1        132          4
     1        155          5
     1        155          6
     1        132          7

select A, B, [...] as D
from myTable
order by C

Query must be ordered by C and should return:

     A          B          D
     1        132          1
     1        155          2
     1        132          3
     1        132          3
     1        155          4
     1        155          4
     1        132          5

All A and B pair groups must have unique D value.

Is it possible to calculate some kind of ID for groups based on order ?

Tried with RANK and GROUPING SETS but with not success.

Get the data, order by C.
Compute a temporary column: if B equals to B in the previous row (rows ordered by C), let that column be 0, otherwise 1. This column is used for the following: when B is the same as B in the previous row, we increase the value in D by 0, otherwise we increase the value by 1. Finally, get A, B, and the SUM of the temporary column up to the current row (this requires the analytic version of SUM), this will give us D.

The above translated to SQL:

with data as
(
  select 1 a, 132 b, 1 c from dual union all
  select 1 a, 155 b, 2 c from dual union all
  select 1 a, 132 b, 3 c from dual union all
  select 1 a, 132 b, 4 c from dual union all
  select 1 a, 155 b, 5 c from dual union all
  select 1 a, 155 b, 6 c from dual union all
  select 1 a, 132 b, 7 c from dual
)
select * from data order by c;

         A          B          C
---------- ---------- ----------
         1        132          1
         1        155          2
         1        132          3
         1        132          4
         1        155          5
         1        155          6
         1        132          7

Get the expected result:

with data as
(
  select 1 a, 132 b, 1 c from dual union all
  select 1 a, 155 b, 2 c from dual union all
  select 1 a, 132 b, 3 c from dual union all
  select 1 a, 132 b, 4 c from dual union all
  select 1 a, 155 b, 5 c from dual union all
  select 1 a, 155 b, 6 c from dual union all
  select 1 a, 132 b, 7 c from dual
)
select
  a, b,
  sum(d) over (order by c rows between unbounded preceding and current row) as d
from
(
select
  a, b, c,
  case when lag(b) over (order by c) = b
    then 0
    else 1
  end as d
from
  data
)
order by c
;

         A          B          D
---------- ---------- ----------
         1        132          1
         1        155          2
         1        132          3
         1        132          3
         1        155          4
         1        155          4
         1        132          5


Truncated 200GB table but disk space not released I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.

If you are referencing the actual database file consumption on the volume, then SQL Server doesn't handle that automatically.  Just because you removed data from the database doesn't mean the database files will shrink to fit only the existing data.

What you'd be looking for, if you have to reclaim space on the volume, would be shrinking the particular file with DBCC SHRINKFILE.  It is worth noting a few best practices, as per that documentation:

  Best Practices

  Consider the following information when you plan to shrink a file:

  A shrink operation is most effective after an operation that creates    lots of unused space, such as a truncate table or a drop table operation.
  Most databases require some free space to be available for regular    day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
  A shrink operation does not preserve the fragmentation state of    indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
  Shrink multiple files in the same database sequentially instead of    concurrently. Contention on system tables can cause delays due to blocking.

Also of note:

  DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.

There are surely a few things to consider when doing this, and I'd recommend you take a look at Paul Randal's blog post on what happens when you do this operation.

The first step would definitely be to verify how much space and free space you are actually able to replace, as well as the used space on the file(s):

use AdventureWorks2012;
go

;with db_file_cte as
(
    select
        name,
        type_desc,
        physical_name,
        size_mb =
            convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb =
            convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
    from sys.database_files
)
select
    name,
    type_desc,
    physical_name,
    size_mb,
    space_used_mb,
    space_used_percent =
        case size_mb
            when 0 then 0
            else convert(decimal(5, 2), space_used_mb / size_mb * 100)
        end
from db_file_cte;


In mysql, find all instances of a named column that are not foreign keys This one has had me trumped for a while!

I want to run a query to retrieve all instances of a column (from information_schema, I guess) that ARE or ARE NOT foreign keys.

Now, let me back off a little and explain my real need. I have a rather large database dealing with company information. Now, I want to be able to delete (or update) a customer record, and have all related foreign keys affected by CASCADE. Now, if the foreign key constraints are not duly set everywhere, then a few records might be orphaned.

So, I want to first query to get a bird's eye view of my column. Now let's assume that all instances of this column have the same name "mycol".

I've taken note of the table REFERENTIAL_CONSTRAINTS, but got intimidated ;). One immediate constraint (pardon the pun) I've seen is: the field CONSTRAINT_NAME is populated with names like customer_ibfk_1, since the naming of FK constraints on this project was left for mysql to handle as default. This table has no field for "REFERENCED_COLUMN", and so I'm stumped.

Any help please!

Here's what I eventually came up with. Of course, I'm open to input/refinements from the DBA grandfathers around here! But for now, this approach is giving me the info I need.

USE INFORMATION_SCHEMA;

Get all tables that have at least one foreign key referencing a column in "mytable"

SELECT TABLE_NAME, COUNT(*) AS reference_count
FROM REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'mydb'
    AND REFERENCED_TABLE_NAME = 'mytable' GROUP BY TABLE_NAME;

Get all tables that have at least one foreign key on a column named "mycolumn"

SELECT TABLE_NAME
FROM KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = 'mydb'
    AND COLUMN_NAME = 'mycolumn'
    AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

Get all tables that have ANY COLUMN named "mycolumn". The subquery in the WHERE clause AND (SELECT Table_Type FROM TABLES WHERE table_schema = 'mydb' AND TABLE_NAME = COLUMNS.TABLE_NAME) is necessary, otherwise it would return also any VIEW having a column named "mycolumn"

SELECT TABLE_NAME
FROM COLUMNS
WHERE
    TABLE_SCHEMA = 'mydb'
    AND COLUMN_NAME = 'mycolumn'
    AND (SELECT Table_Type
        FROM TABLES WHERE table_schema = 'mydb'
        AND TABLE_NAME = COLUMNS.TABLE_NAME) = 'BASE TABLE';

Now, using the 2 previous queries, we can obtain any tables having a column named "mycolumn" but in which "mycolumn" is NOT A FOREIGN KEY. Note that this filtering will not respect a PRIMARY KEY, and will also return any table where "mycolumn" is a PRIMARY KEY.

SELECT TABLE_NAME
FROM COLUMNS
WHERE TABLE_SCHEMA = 'mydb'
    AND COLUMN_NAME = 'mycolumn'
    AND (SELECT Table_Type
        FROM TABLES
        WHERE table_schema = 'mydb'
            AND TABLE_NAME = COLUMNS.TABLE_NAME) = 'BASE TABLE'
    AND TABLE_NAME NOT IN
        (SELECT TABLE_NAME
        FROM KEY_COLUMN_USAGE
        WHERE CONSTRAINT_SCHEMA = 'mydb'
            AND COLUMN_NAME = 'mycolumn'
            AND REFERENCED_TABLE_SCHEMA IS NOT NULL
        );


SQL Server Always on DBCC Check DB and Backups startegey Can we run DBCC CheckDB on secondary and perform our database backups with checksum on Primary.
This will make sure if corruption happens on primary, backup will fail and Primary will use sys.dm_hadr_auto_page_repair to repair page from secondary node
Also running CheckDB on Secondary node will always keep DBCC related resource utilization on secondary only.

Brent Ozar has a good article regarding this here: Where to Run DBCC on Always On Availability Groups
Specifically these parts of the article are relevant to your question (his example is kind of the reverse of yours, but the same principal applies in regards to the importance of running DBCC CHECKDB on both replicas):

We could be backing up corrupt data every night as part of our full
backups.  If we experienced corruption on the primary server, and it
tried to take care of business with automatic page repair by fetching
a copy from the secondary server (which also happened to be corrupt),
we’d be screwed.
The moral of the story: with Availability Groups, we need to run DBCCs
on whatever server is doing our full backups, too.  Doing them on the
primary database server isn’t enough.  In a perfect world, I’d run
them regularly on any server that could serve a role as the primary
database.  Automatic page repair can only save your bacon if a replica
is online and has a clean copy of the page.

In short: By backing up the Primary replica without running DBCC CHECKDB on it, you could be backing up corrupt data without realizing, especially more so when you've experienced a failover event that caused your replicas to switch places in the interim, for your specific scenario.


What's the minimum privilege needed to alter a foreign key constraint? What's the minimum privilege needed to alter a foreign key constraint?

My migration script stopped working after MySQL 5.5.41 fixed this bug:

InnoDB permitted a foreign key to be created which referenced a parent table for which the user did not have sufficient privileges. (Bug #18790730)

I get this error:

  SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'foo'@'localhost' for table 'core.users' (SQL: alter table `user_baz` add constraint user_baz_user_id_foreign foreign key (`user_id`) references `core`.`users` (`id`) on delete cascade on update cascade)

Which means I need to fix the privileges. What's the minimum privilege I need?

You need to add the "REFERENCES" privilege to your role.


Left outer join not returning all rows in a grouping query I'm trying to query all users to find the price of orders in the last month. I'm doing this on PostgreSQL. Here's a toy database to show my problem: I have a people table and an orders table. I'm trying to find the sum of orders for all people in the last month.

Here's my data:

select * from people;
 id |     name
----+--------------
  1 | bobby lee
  2 | greg grouper
  3 | Hilldawg Ca
(3 rows)

select * from orders;
 id | person_id |    date    | price
----+-----------+------------+-------
  1 |         3 | 2014-08-20 |  3.50
  2 |         3 | 2014-09-20 |  6.00
  3 |         3 | 2014-09-10 |  7.00
  4 |         2 | 2014-09-10 |  7.00
  5 |         2 | 2014-08-10 |  2.50

And here's the query I'm trying to write:

SELECT p.id, p.name, sum(o.price)
FROM people p
LEFT OUTER JOIN orders o
ON p.id = o.person_id
WHERE o.date between date '2014-09-01' AND date '2014-09-30'
GROUP BY p.id;

Here is the return I get:

 id |     name     |  sum
----+--------------+-------
  2 | greg grouper |  7.00
  3 | Hilldawg Ca  | 13.00
(2 rows)

I'm expecting a third row, with person id 1 (bobby lee) having a sum of zero for the last month's orders (since no orders exist for bobby). What am I doing incorrectly with my query? My impression was that doing a left outer join and grouping by people.id means that the people table should set the number of rows in the output.

The WHERE o.date ... condition makes the outer join behave like an inner join, cutting out any rows of peoplethat don't have at least one matching row in orders.

You'll have to move the condition about o.date from the WHERE clause to the joining ON:

SELECT p.id, p.name, coalesce(sum(o.price), 0.00) AS total
FROM people p
  LEFT OUTER JOIN orders o
    ON  p.id = o.person_id
    AND o.date BETWEEN date '2014-09-01' AND date '2014-09-30'
GROUP BY p.id;

Test in: SQLFiddle


Resolving Cyclical Reference in Referential Relationships I'm designing a small SQL Server Compact Edition database and am attempting to enforce referential integrity. The database is a simple backend for a small desktop application that functions as an account/password manager.

Here are my relationships:

Now, I'm attempting to set CASCADE delete rules like so:

category.id ---> account.category_id
account.id ---> credential.account_id
category.id ---> field.category_id
field.id ---> credential.field_id

After adding the first 3 relationships, I get an error while adding the 4th (field ---> credential):

The referential relationship will result in a cyclical reference that is not allowed.

I'm struggling to determine why this is happening and how to fix it. Any ideas?

Since you are using SQL Server Compact Edition, which doesn't support triggers that could solve this issue, my recommendation is either:

write your own manual DELETE statements in the proper order, rather than use CASCADE
change the design
use Express / LocalDB instead of Compact Edition, which is extremely limited and has been deprecated


Decomposition of a relation to 2NF and 3NF I am trying to solve this example, but for now I can't. Can somebody show me, step by step how to decompose this to 2NF and then to 3NF?

R = {ABCDEF, ABC -> EF, B -> E, E -> D}

key of this relation is:

{ABC}

So now I try to split R into 2 relations:

R0 = {ABCEF, ABC -> EF}
R1 = {BED, B -> E, E -> D}

but its all wrong... i got other answers in my book. I dont even try to make 3NF out of it.

The goal of 3NF (and BCNF) is to avoid situations when a set of attributes is functionally determined by something that is not a key (or, more formally, a superkey), which may lead to anomalies and redundancy.

Keeping that in mind, we can describe certain functional dependencies as either "bad" (if they prevent the relation from being in 3NF) or "good" (if they do not).

So your solution is wrong, because "your" R1 has the E->D dependency and E is not R1's key. Determining a key for a relation is another topic which, I suppose, is beyond the scope of the task you need to accomplish (we are told that ABC is a key). But if you are interested, you can look the method up using "attribute closure" keywords.

Anyway, the E->D dependency is "bad". But that is not the only problem. Another one is that your decomposition is not a "lossless-join" one. In other words, if we have some real world data, split the relation according to your proposition and try to join R0 and R1 back, the data will be corrupted. Data corruption is not what normalization is designed for.

We have to solve 2 problems:

Decide which dependencies are "bad" (the definition of normal forms is the answer to this)
Decide how to split relations to prevent data corruption (we have Heath's theorem to help us)

Getting back to your problem, finally.

In R, neither B, nor E is the key. So both B->E and E->D are "bad", and we need to take measures. On the contrary ABC->EF is "good", because ABC is a key.

Currently R is in 1NF, because B is a part of a key and functionally determines E, which is not a part of a key. This kind of situation is unacceptable in 2NF.

E->D dependency is not that "bad", because E is not a part of a key.

Another thing to mention at this point is that a set of dependencies {B->E, E->D} implies the dependency B->ED (according to so called Armstrong axiom of transitivity).

So we decide to split using B->ED dependency as "the worst" one. That's the way we do this, according to Heath:

R = ABCDEF
R0 = BED (B->E, E->D, B is a key)
R1 = ABCF (ABC->F, ABC is a key)

Now we have R1, which is in 3NF and R0, which is in 2NF (it has a transitive dependency B->E->D, which is unacceptable in 3NF).

We need to split R0 and to do so we have to choose a "victim" dependency among B->E and E->D. As we see, B->E is now a "good" one (B is a key) and E->D is still "bad".

Our split:

R0 = BED
R2 = BE (B->E, B is a key)
R3 = ED (E->D, E is a key)

R1, R2 and R3 are the final decomposition.


Squashing aggregated rows with PostgreSQL Given a table my_data like this:

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | NULL
 1  | NULL | smith   | 32
 1  | NULL | NULL    | NULL
 1  | john | smith   | NULL
 1  | john | NULL    | 32

CREATE TABLE my_data(id,name,surname,age)
AS ( VALUES
  (1::int, 'john', 'smith' ,NULL::int),
  (1, NULL,   'smith' ,32),
  (1, NULL,   NULL    ,NULL),
  (1, 'john', 'smith' ,NULL),
  (1, 'john', NULL    ,32)
);

For the same id, the values (if present) in the respective columns are always the sames so how can I "squash" them to get:

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | 32

My attempt

A cross join lateral for each column is my only idea so far, but I doubt is good:

select
distinct column1, c2.value, c3.value, c4.value
from my_data md
cross join lateral (select column2 from my_data where column1 = md.column1 and column2 is not null limit 1) as c2(value)
cross join lateral (select column3 from my_data where column1 = md.column1 and column3 is not null limit 1) as c3(value)
cross join lateral (select column4 from my_data where column1 = md.column1 and column4 is not null limit 1) as c4(value);

Using percentile_disc

I would think something like this would be the fastest,

SELECT id,
  percentile_disc(0) WITHIN GROUP (ORDER BY name NULLS LAST)    AS name,
  percentile_disc(0) WITHIN GROUP (ORDER BY surname NULLS LAST) AS surname,
  percentile_disc(0) WITHIN GROUP (ORDER BY age NULLS LAST)     AS age
FROM my_data
GROUP BY id;

 id | name | surname | age
----+------+---------+-----
  1 | john | smith   |  32
(1 row)

Here we're using percentile_disc an Ordered-Set Aggregate described as "discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction". So,

if that position is "0", it's essentially first_value over the rows being aggregated.
if NULLS are last (NULLS LAST), the first value won't be null, and that's all that matters here (because you said "for the same id, the values (if present) in the respective columns are always the same")

You could also do this, I think, with first_value in a Window Function, and then pull from that with DISTINCT ON.

Using mode

If you want the consensus of the non-values, we can do that too. I was assuming percentile_disc was what the OP wanted. Another option is to use mode() which is a different Ordered-Set Aggregate Function. It's described as, "returns the most frequent input value (arbitrarily choosing the first one if there are multiple equally-frequent results)". It looks like,

SELECT id,
  mode() WITHIN GROUP (ORDER BY name)    AS name,
  mode() WITHIN GROUP (ORDER BY surname) AS surname,
  mode() WITHIN GROUP (ORDER BY age)     AS age
FROM my_data
GROUP BY id;


Is it possible to have unique together constraint on a SET of columns in PostgreSQL? What I want to achieve is the following:

CREATE TABLE foo (
    bar1 int,
    bar2 int,
    type varchar(16),
    FOREIGN KEY bar1 REFERENCES bar(id),
    FOREIGN KEY bar2 REFERENCES bar(id)
);

ALTER TABLE foo ADD CONSTRAINT unique_link (bar1, bar2);

INSERT INTO foo (bar1, bar2, type) VALUES (1, 2, 'simple link')
--next line should fail, as {2, 1} as a set is already present, just in a different order
INSERT INTO foo (bar1, bar2, type) VALUES (2, 1, 'simple link')

Is it possible in any way? Maybe with some triggers maybe, or some auto calculated hashes of the bar* columns? Thanks for the help!

Yes, you can create a unique index for that:

create unique index on foo ( least(bar1,bar2), greatest(bar1,bar2) );


How to update the statistics of an index that has just been reorganized, both with IndexOptimize script I would like to know if we can update the statistics of an index that has been reorganized, using the IndexOptimize script.
Indeed, an index that is reorganized needs to have its statistics updated. Is there a way to use IndexOptimize script to update the statistics of this reorganized index only?
For instance, is there a flag to recognize the reorganized index in order to update the statistics on this flagged index?
Thanks.

I take it that you are referring to Ola Hallengren's scripts.
I can't find any parameter combo that would do what you ask. It is a pretty reasonable thing to ask for, though. IndexOptimize is pretty much separate in the handling of defrag vs updstats. Your options, as far as I can see:

Modify the proc yourself. It is only TSQL, in the end. Add an UPDATE STATISTICS after it does index reorg.
Create a branch on GitHub and see if it is merged into the main bransch. Now others can enjoy your effort (like we all are enjoying Ola's effort).
Send an email to Ola and see if he feels like doing the change.
Schedule a separate job for statistics - but you won't get the coupling to the previous reorg.
Choose a different maint solution.
Measure whether you actually gain anything from defrag, and potentially focus on statistics update instead.

Possibly some more, but those are the ones I can think of right now. :-)


How to use floating-point storage for time data type At Postgres documentation page Date/Time Types, it says:

  For the time types, the allowed range of p is from 0 to 6 when
  eight-byte integer storage is used, or from 0 to 10 when
  floating-point storage is used.

I tried to produce SQL statement with time(p), when p=10? I always get

  WARNING:  TIME(10) precision reduced to maximum allowed, 6

No matter if I put seconds in float or not. I realize I'm missing something very obvious. Please give an example when p=10.

From the linked page you provide:

  Note: When timestamp values are stored as eight-byte integers (currently the default), microsecond precision is available over the full range of values. When timestamp values are stored as double precision floating-point numbers instead (a deprecated compile-time option), the effective limit of precision might be less than 6. ...

What this says is that in order for timestamp values to be stored as double precision floating-point (and to allow precision up to 10), you need to compile Postgres yourself with that option.

It also says that the precision is then not exactly 10 but it varies depending on the value and may even be less than 6 for some (very high or very low) values.


SELECTing an optional column, and doing SUM on it I have two tables (from Request Tracker 4, actually): Tickets, and Transactions.

Tickets.id == Transactions.ObjectID

but there aren't always Transactions for every Ticket.

I'm interested in the Ticket's "TimeWorked" column. This is often set on Ticket Creation, but it's also sometimes updated by a Transaction which updates "TimeWorked". I want to get the TimeWorked value as it originally was which means I think I have to backtrack through all the linked Transactions and subtract their values. Subsequently, I also want all the updated Transactions which modified that value, but I believe I've solved that problem.

Basically I want to see who set the TimeWorked value when, and what it was on ticket creation. Here's my SQL:

SELECT
Tickets.id                                                            AS Ticket,
Tickets.Subject                                                       AS Subject,
/*
SUM(Transactions.NewValue - Transactions.OldValue)                    AS TransactionTimeWorked,
Tickets.TimeWorked-SUM(Transactions.NewValue - Transactions.OldValue) AS InitialTimeWorked,
*/
Tickets.Created                                                       AS TicketCreated
FROM
    Tickets
         LEFT JOIN Transactions       ON Tickets.id=Transactions.ObjectId
            AND Transactions.Field="TimeWorked"
WHERE
    Tickets.Queue = 50
    AND Tickets.Created > "2015-06-01"
    AND Tickets.Created < "2015-09-01";

Including that first SUM line limits the number of rows returned to those Tickets which have updating Transactions. I sort of understand why, but I want all Tickets to be included, with their original create time calculated by SUMming the Transactions and subtracting, but only if they exist.

I think my WHERE clause is correct in making the JOIN values optional, but now I think I want the same on the SUM.

An example of the output I'd like:

+--------+---------+-----------------------+-------------------+---------------------+---------------------+
| Ticket | Subject | TransactionTimeWorked | InitialTimeWorked | TicketCreated       | TransactionCreated  |
+--------+---------+-----------------------+-------------------+---------------------+---------------------+
|  11234 | test01  |                  NULL |                60 | 2015-08-03 08:23:35 |                NULL |
|  11234 | test01  |                    20 |              NULL | 2015-08-03 08:23:35 | 2015-08-04 08:23:35 |
|  11234 | test01  |                    10 |              NULL | 2015-08-03 08:23:35 | 2015-08-06 15:34:35 |
|  11235 | test02  |                  NULL |                30 | 2015-07-31 12:50:37 |                NULL |
|  11236 | test03  |                  NULL |                20 | 2015-07-31 14:51:29 |                NULL |
+--------+---------+-----------------------+-------------------+---------------------+---------------------+

If you need only the initial and the last (current) value of TimeWorked, you only need to add GROUP BY (ticket) to your query:

-- query 1
SELECT
  ti.id                                          AS Ticket,
  ti.Subject                                     AS Subject,
  SUM(tr.NewValue - tr.OldValue)                 AS TransactionTimeWorkedTotal,
  ti.TimeWorked - SUM(tr.NewValue - tr.OldValue) AS InitialTimeWorked,
  ti.Created                                     AS TicketCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr      ON ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01'
GROUP BY
    ti.id,
    ti.Subject,
    ti.TimeWorked,
    ti.Created ;

This could also be accomplished with a different approach:

-- query 2
SELECT
  ti.id                                          AS Ticket,
  ti.Subject                                     AS Subject,
  COALESCE(ti.TimeWorked - tri.OldValue, 0)      AS TransactionTimeWorkedTotal,
  COALESCE(tr.OldValue, ti.TimeWorked)           AS InitialTimeWorked,
  ti.Created                                     AS TicketCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr
            ON  ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
            AND tr.Created =
                ( SELECT tr1.Created               -- finds the first
                  FROM Transactions AS tr1         -- transaction (if any)
                  WHERE ti.id = tr1.ObjectId       -- for the ticket
                    AND tr1.Field = 'TimeWorked'
                  ORDER BY tr1.Created
                  LIMIT 1
                )
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' ;

Both the above queries will give the initial TimeWorked and the total of the transactions TimeWorked values.

If you want all the individual changes as well - and since the Transactions table has already the old and new values - it needs something different, but not more complicated than the previous queries. using query 2 for example:

-- query 2b
SELECT
  ti.id                                      AS Ticket,
  ti.Subject                                 AS Subject,
  NULL                                       AS TransactionTimeWorked,
  COALESCE(tr.OldValue, ti.TimeWorked)       AS InitialTimeWorked,
  ti.Created                                 AS TicketCreated,
  ti.Created                                 AS TransactionCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr
            ON  ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
            AND tr.Created =
                ( SELECT tr1.Created               -- finds the first
                  FROM Transactions AS tr1         -- transaction (if any)
                  WHERE ti.id = tr1.ObjectId       -- for the ticket
                    AND tr1.Field = 'TimeWorked'
                  ORDER BY tr1.Created
                  LIMIT 1
                )
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01'

UNION ALL

SELECT
  ti.id                                      AS Ticket,
  ti.Subject                                 AS Subject,
  tr.NewValue - tr.OldValue                  AS TransactionTimeWorked,
  NULL                                       AS InitialTimeWorked,
  ti.Created                                 AS TicketCreated,
  tr.Created                                 AS TransactionCreated
FROM
    Tickets AS ti
         INNER JOIN Transactions AS tr           -- all the transactions
            ON  ti.id = tr.ObjectId             -- of a ticket
            AND tr.Field = 'TimeWorked'
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01'

ORDER BY
    Ticket,
    TransactionCreated ;


Create Pivot Based Off Variables I have DDL like the below, which returns data like such:

GP17    GP16
0       103
90      0
375     0

I am wanting to pivot the data somehow so that it returns only one row, like such:

Jan 17 ---- Jan 16 ---- Feb 17 ---- Feb 16 ---- Mar 17 ---- Mar 16
0            103         90          0           375         0

Now this is relevant to the date range being passed in below, for example, if the date range being passed in is 20170401  20170630 then I would want the dates to read Apr 17, Apr 16, May 17, May 16, Jun 17, Jun 16

How can this be achieved with SQL Server 2008 R2 and the DDL below?

Declare @Test Table
(
    dname varchar(100)
    ,tr float
    ,dc float
    ,sdate date
)

Insert Into @Test (dname, tr, dc, sdate) Values
('Green', 100.00, 10.00, '20170101'), ('Green', 200.00, 15.00, '20170201'), ('Green', 200.00, 10.00, '20170215')
,('Green', 10.00, 0.00, '20160101'), ('Green', 15.00, 10.00, '20160130'), ('Green', 110.00, 22.00, '20160101')

Declare @startdate date = '20170101', @enddate date = '20170331'

SELECT
ROUND(SUM(ISNULL(tr17,0))-SUM(ISNULL(DC17,0)),0) As GP17,
ROUND(SUM(ISNULL(tr16,0))-SUM(ISNULL(DC16,0)),0) As GP16
FROM
(
SELECT
ZT1.dname,
SUM(ISNULL(ZT1.tr,0)) AS tr17,
0 As DC17,
0 As DC16,
0 AS tr16,
sdate As activityDate
FROM @Test AS ZT1
WHERE ZT1.sdate >= @startdate
AND ZT1.sdate <= @enddate
GROUP BY ZT1.dname, ZT1.sdate
UNION ALL
Select
t1.dname,
0 As tr17,
SUM(ISNULL(t1.dc,0)) As DC17,
0 As DC16,
0 AS tr16,
sdate as activityDate
FROM @Test t1
WHERE t1.sdate >= @startdate
AND t1.sdate <= @enddate
Group By t1.dname, t1.sdate
UNION ALL
SELECT
ZT1.dname,
0 As tr17,
0 As DC17,
0 As DC16,
SUM(ISNULL(ZT1.tr,0)) AS tr16,
sdate As activityDate
FROM @Test AS ZT1
WHERE ZT1.sdate >= DateAdd(yy, -1, @startdate)
AND ZT1.sdate <= DateAdd(yy, -1, @enddate)
GROUP BY ZT1.dname, ZT1.sdate
UNION ALL
Select
t1.dname,
0 As tr17,
0 As DC17,
SUM(ISNULL(t1.dc,0)) As DC16,
0 AS tr16,
sdate as activityDate
FROM @Test t1
WHERE t1.sdate >= DateAdd(yy, -1, @startdate)
AND t1.sdate <= DateAdd(yy, -1, @enddate)
Group By t1.dname, t1.sdate
) AS A
GROUP BY YEAR(activityDate), MONTH(activityDate)

A PIVOT solution requires a well-know number of columns.
If you're looking for an answer that covers no matter which dates, I'd suggest you to search for 'Dynamic pivot'

;WITH data AS
(
    SELECT  dname,
            tr - dc as result,
            LEFT(DATENAME(MONTH, sdate), 3) + '-' + RIGHT(CAST(YEAR(sdate) AS varchar(10)), 2) as DateName
    FROM    @Test
)
SELECT dname, [Jan-16],[Jan-17],[Feb-16],[Feb-17]
FROM   (SELECT * FROM data) src
PIVOT  (SUM(result) FOR DateName IN([Jan-16],[Jan-17],[Feb-16],[Feb-17])) pvt;

GO

dname | Jan-16 | Jan-17 | Feb-16 | Feb-17
:---- | -----: | -----: | -----: | -----:
Green |    103 |     90 |   null |    375

dbfiddle here


Can a database table not have a primary key? Can a database table not have a primary key?  SQLite lets you define a table with no primary key therefore there can be no insertion anomaly.

Can you create a database table without a primary key? Well, you just said you can in SQLite. And, I believe that holds true for almost every (if not every) major DBMS platform.

Should you create a database table without a primary key? No.

Every table should have some column (or set of columns) that uniquely identifies one and only one row. It makes it much easier to maintain the data.

It's true, without a primary key (or some unique key), you don't have an insertion anomaly if you go to insert the same data multiple times.

What you do have is a user table with 300 separate entries for a user named "bob", many of which have different values in the other columns. And, if you've tried to connect rows in some other table to user without a primary key/foreign key relationship - every row in message marked as sent_by "bob" ties back to every one of those 300 user entries. Oh, and there're 39 unique passwords for "bob" (157 of the rows all show the password "password", of course).

Without primary keys, things can get really messy, really fast.


Working of postgres remote connection How does psql remote connection work (default configuration)? Does it make use of ssh or any other protocol?

I am using linux machine (centos 6.8) and postgresql9.5 version.

It is a direct tcp connection in a protocol developed by PostgreSQL. It can use  encryption if the server is configured to support it. You do not need to use SSH.

Remote connection alternatives:

unencrypted remote through postgres protocol
TLS encrypted remote through postgres protocol
SSH to the server then use psql/pgadmin local

Connection using the psql utility from remote host

psql -h yourserver.com.br -U yourusername yourdatabase

How to configure remote access through postgresql protocol

postgresql.conf

listen_address = '*'

The listen_address config must be changed to allow remote connections on the postgres protocol.

ssl_ciphers = 'HIGH:!aNULL'

The ssl_ciphers can be changed to improve encryption strength if you are more paranoid than the average admin. The example in the line reads allow only ciphers with more than 128 bit keys and do not allow unencrypted connections. If you want to know more about the ciphers you can look in the openssl man

ssl_cert_file = (...)
ssl_key_file = (...)

The ssl_(...)_file controls where to find the ssl certificates. In ubuntu and debian this is set to the self-signed certificate generated by the openssl installation. But you can change it to a valid SSL certificate. Postgres clientes such as psql, pgadmin and embedded in programming languages such as php will check the certificate chain against the trusted authorities.

pg_hba.conf

hostssl (...)

In all places you se a line host (...) in the pg_hba replace it by hostssl (...) in order to force your clients to use SSL connections.


Total number of read/writes in a day in MySQL Aside from using standard count queries with a date, is there a global way to get the number of writes (or reads for that matter) in a 24hr period on a table, or an entire schema?

If you have binary logging enabled, you can analyse your binlogs to identify types and number of statements executed.

mysqlbinlog your_binlog | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | sort -nr

You might need --base64-output=DECODE_ROWS for row based binlog format.
You can analyze your day worth of binlogs or use --start/--end options to select time.

Check out this and this blog posts.


Add Total Row To PIVOT Query I want to add a TOTAL monthly row to my query, below is my DDL, how can I have an additional row, under the last employeename that is labeled TOTAL and it shows the SUM() of the sales for ALL employees for that month?
Create Table #empSales
(
    employeename varchar(100)
    ,saleamt decimal(10,2)
    ,saleMonth varchar(100)
)

Insert Into #empSales VALUES
('James', '1.00', 'January')
,('Richard', '3.28', 'January')
,('Barb', '4.13', 'January')

Select
employeeName
,SUM(January) As JanAMt
,SUM(February) As FebAMt
,SUM(March) As MarAMt
,SUM(April) As AprAMt
,SUM(May) As MayAMt
,SUM(June) As JunAMt
,SUM(July) As JulAMt
,SUM(August) As AugAMt
,SUM(September) As SepAMt
,SUM(October) As OctAMt
,SUM(November) As NovAMt
,SUM(December) As DecAMt
FROM #empSales
PIVOT (
    SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December)
) As pvt
GROUP BY employeeName
Order By employeeName

The optional WITH ROLLUP clause can be added to your GROUP BY in order to get the grand total row.
GROUP BY employeeName WITH ROLLUP

To get "TOTAL" as the label, you can use the GROUPING function.
Select
CASE WHEN GROUPING(employeename) = 1 THEN 'TOTAL' ELSE employeeName END

The final query looks like this:
Select
CASE WHEN GROUPING(employeename) = 1 THEN 'TOTAL' ELSE employeeName END
,SUM(January) As JanAMt
,SUM(February) As FebAMt
,SUM(March) As MarAMt
,SUM(April) As AprAMt
,SUM(May) As MayAMt
,SUM(June) As JunAMt
,SUM(July) As JulAMt
,SUM(August) As AugAMt
,SUM(September) As SepAMt
,SUM(October) As OctAMt
,SUM(November) As NovAMt
,SUM(December) As DecAMt
FROM #empSales
PIVOT (
    SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December)
) As pvt
GROUP BY employeeName WITH ROLLUP
Order By GROUPING(employeename), employeeName

Notice that I tweaked the ORDER BY so that the total row comes last in the resultset.


declare table variable inside function In my stored procedure, I have table variable with a lot of columns. Because I want to simplify the stored procedure I thought i could declare table variable in function like

CREATE FUNCTION [dbo].[ufn_DeclareMaterialTableVariable]
(
)
RETURNS @returntable TABLE
(
    [Id] [int] NULL,
    [Number] [int] NOT NULL,
    [Name] [nvarchar](255) NULL,
    ... dozens of columns
)
AS
BEGIN
    RETURN
END

but i realized i don't know, how to use it. My idea was something like

select *
into @table
from [dbo].[ufn_DeclareMaterialTableVariable]

but this is not gonna work, unless i use temporary table.

The second idea is to declare table with custom data type.

declare @table TABLE as CustomTableType

but this is also not working. Any idea ?

You need to declare a table variable with the same shape as the TVF result, then use INSERT INTO not SELECT INTO, and remember to add brackets/parentheses to the end of the function name when invoking it, i.e. Foo() not just Foo:

DECLARE @Table TABLE (
    [Id] [int] NULL,
    [Number] [int] NOT NULL,
    [Name] [nvarchar](255) NULL,
    ... dozens of columns
);

// not best practice, but...
INSERT INTO @Table
SELECT *
FROM dbo.ufn_DeclareMaterialTableVariable();

// better - be explicit about the columns of interest
INSERT INTO @Table (
    [Id],
    [Number],
    [Name],
    ... dozens of columns
)
SELECT
    [Id],
    [Number],
    [Name],
    ... dozens of columns
FROM dbo.ufn_DeclareMaterialTableVariable();

If you've declared a table type, then the syntax for declaring a variable of that type is the following (note no TABLE keyword);

DECLARE @Table CustomTableType;

That said, annoyingly, you can't declare the result type of a TVF to be a table type - i.e. you always have to declare the returned columns again, explicitly - which partially negates the point of the custom type in the first place, in this situation.


Supplying root password to MySQL V8 sort of fails On Ubuntu 20.04 I have installed MySQL V8.0.25. But I kind of fail to apply a (valid) root password. The apt-get process for installing mysql-server did not ask for a root password.
And I cannot enter mysql with the "mysql -u roor -p" command, I always do with "sudo mysql".
So i first tried with the process described by this MySQL page. But this did not work at all, because V8 seems not to support the PASSWORD-function.
So instead of running
UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

I used this:
UPDATE mysql.user
   SET authentication_string = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('MyNewPass'))))), password_expired = 'N'
   WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

which I found described here.
The query
select host, user, authentication_string, password_expired from mysql.user;
shows a nice table:
   +-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | joomla           | $A$005$MF`6ea"OfH5v1kTuRW0zJS5MKk82btugdAz62uWe6QkxnrXtTLtx5M. | caching_sha2_password |
| localhost | debian-sys-maint | $A$005$l%.r}2CBQT:+DV)a9S/UJUDJoFA8PhnCIE.E3zDFbBeUZ5vTrNSZpZDDv05 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
s%nn69n9�NkFf7xoPdW/CCD/NjvLhTKXtx8gQmTX.RpIbOcHWsA. | caching_sha2_password |
| localhost | root             | mypass                                                                 | auth_stock            |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+

but still "mysql -u root -p" does not work with the suppiel password.
I still get message:
$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I also tried this - without any success:
UPDATE mysql.user
   SET authentication_string = 'mypass', password_expired = 'N', plugin = ''
   WHERE User = 'root' AND Host = 'localhost';

The ALTER cmd did not work
neither
ALTER USER 'root'@'localhost' IDENTIFIED BY 'oldpass';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

nor
ALTER USER 'root'@'localhost' IDENTIFIED BY 'oldpass' REPLACE 'mypass';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

I spent now many hours in this and have no clue how to preceed. Any help is appreciated.

Step 1 : Add below parameter in my.cnf file under [mysqld] section.
skip-grant-tables
Step 2 :  Restart MySQL instance.
Step 3 : Login MySQL instance. (This will not ask you password)
mysql -uroot -p
Step 4 : Reset root password.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewRoot@123Pass';
Step 5 : Remove skip-grant-tables parameter from my.cnf file & restart MySQL instance.
Step 6 : Now Login MySQL instance using root user & new password.


Mongo Map-Reduce or Sharding? So I asked this question:

Setting up Mongo with clustering

But I didn't know enough until I read the answers and did more research.  Is it better to use Map-reduce or sharding for fast-paced systems with ~ 100 queries all asked at the same time?  Sharding will displace my data across clusters and it appears it essentially does a map-reduce or sorts.  If I were to use map-reduce for such queries, should I do so with a cluster as well?  If so, how easy is it to start with one Mongo server, then later use a cluster and distribute the map-reduce tasks to the cluster?  Or am I confused in what I think I need to accomplish?

It's unclear what you mean by comparing map-reduce to sharding. But the short answer is: sharding.

Generally speaking you design-out map-reduce queries, you do not want 100s of map-reduce queries being executed at once - you'd just overload mongo since that essentially means 100s of full collection scans all being run at the same time.

If you have an example of one of your existing map-reduce queries - please add it to your question.

Regarding sharding, it all comes down to what you use for the sharding key.

If you shard a users collection on username for example,

db.users.find()

will cause mongos to send the query to all shards and merge the result sets together (intelligently). Adding the sharding key to the query:

db.users.find().sort({username: 1}).limit(100);

would give mongos the option to talk to less mongod at a time.

a better example, if you query for:

db.users.find({username: /^bob/})

mongos will send the query to the shards whose shard keys indicate that they could contain answers, most likely that is one server only, resulting in a fast query and no extra load on the part of mongos.

Maybe the above examples are not-news to you.

The queries you send to mongo now are same syntax you'd use to send to a sharded database. The only thing you'd do differently is (previously) analysing on what keys to shard so that you can, where necessary, modify your queries to incorporate the sharding key and thereby enable mongos to act like a proxy instead of an agregator.

A poor sharding key,  or simply not taking advantage of sharding in the queries you are generating, would result in mongos needing to query all mongod servers for all queries resulting in high load and poor performance.


Rollback is not working if the storedproce call another storedproc Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit?

example:

pseudo procedure proc1

create procedure proc1(in var1 int, out var2 int)
begin
  start transaction;
  -- some code here
  commit;
end

pseudo procedure proc2, the one will call the proc1

create procedure proc2()
lbl_begin:
begin
  start transaction;
    -- modify some data here
    call proc1(0, var2);

    if var != 0 then
      rollback;
      leave lbl_begin;
    end;
  commit;
end;

Even the rollback on proc2 is executed, the data modified before the call of the proc1 is still committed.

Is it a bug or its an intended behavior?

Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.

-- https://dev.mysql.com/doc/refman/8.0/en/commit.html

and

  Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

-- https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

That is, proc1's START closed proc2's START.

The first ROLLBACK closes the only open transaction, hence the second ROLLBACK has nothing to "roll back".


How to select name by 2 fields in another 2 tables in mySQL I got confused trying after making several searches on google to find a way to do the following:

I have table called students that contains 3 colums (student_id(primarykey), firstname,lastname),
the second table called courses contains 2 columns (course code,course_name) and
the third table called registration contains (student_id,course_code),

I need to select the name of students that are registered in course that includes the word for example "Structures" in the course_name field.

For example (consider the snap-shot)

Regardless the concat of the firstname and lastname, this is easy to do it.

So I will get these student names : SomeOne & GoodStudent

P.S. I created table Registration by inserting from the first two tables together.

So I tried like the following:

select concat(firstname,lastname)
from students
  left join registration
where students.student_id = registration.student_id
  and registration.course_code like 'Structure%';

as correctly mentioned in comments - LEFT JOIN not proper choice in Your case, even if You fix the syntax (JOIN require ON condition)

You need use INNER JOIN:

SELECT
     CONCAT(s1.firstname,s1.lastname) as full_name
FROM students s1
INNER JOIN registration r1 ON s1.student_id = r1.student_id
INNER JOIN courses c1 ON c1.course_code = r1.course_code
WHERE c1.course_name LIKE '%Structure%';

or You need swap tables

SELECT
     CONCAT(s1.firstname,s1.lastname) as full_name
FROM courses c1
LEFT JOIN registration r1 ON c1.course_code = r1.course_code
LEFT JOIN students s1 ON s1.student_id = r1.student_id
WHERE c1.course_name LIKE '%Structure%';

as You can see LIKE - must have % from both ends for found what You are looking for


Lock a MySQL database for upgrade I've looked around for solutions to lock a whole database, and the only one I found so far is FLUSH TABLES WITH READ LOCK.

This is fine for backups, but unfortunately I can't use that to prevent other accesses to the database while I'm patching my database with schema changes. If I run an ALTER TABLE after FLUSH TABLES WITH READ LOCK, I get the following message:

  Can't execute the query because you have a conflicting read lock

Is there a way to just prevent other database connections from accessing the database temporarily while I'm patching the database?

I don't want to actively refuse the other connections, I'd just like to put them "on hold" until the patching is finished (a matter of seconds).

How can I do that?

You should be able to lock all of the tables you intend to alter (or otherwise want to isolate, in order to modify triggers) with one statement, and your session will hold the lock while you make the changes, until you release the lock.

Example, I have 3 tables here, t1, t2, and t3.  I'm going to make changes to t2 and t3, and I'm going to use t1 to illustrate whether I am actually still holding locks on t2 and t3.

Obtain write locks on t2 and t3.  Any time you lock table you implicitly release any other table locks, so you have to lock them all together.  Your session will block until you get all the locks, then your prompt returns.

mysql> lock table t2 write, t3 write;
Query OK, 0 rows affected (0.00 sec)

Table t2 is locked, and I can alter it.

mysql> alter table t2 add column foo int default 1;
Query OK, 25 rows affected (0.81 sec)
Records: 25  Duplicates: 0  Warnings: 0

I also have access to t3 (which happens to be empty):

mysql> select * from t3;
Empty set (0.02 sec)

However, I didn't lock t1, so I can't even select from it.  When you hold one or more table locks, you can only access tables you have locks for.

mysql> select * from t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Now, to test creating a simple trigger on t2, which is still locked.

mysql> create trigger t2_ai after insert on t1 for each row set @foo = now();
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Ha! That's a typo: I incorrectly said "after insert on t1" instead of "t2" and the server stopped me from touching a table I didn't lock, while I hold other locks.  I can, however, modify triggers on a table I have actually locked, if I type this correctly.

mysql> create trigger t2_ai after insert on t2 for each row set @foo = now();
Query OK, 0 rows affected (0.08 sec)

After all this, I can confirm that I still have locks on t2 and t3 by selecting from them, and I can also confirm that I have locks on "something" by trying to touch a table that I know I didn't lock, (t1 again):

mysql> select * from t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Changes complete, release the all of the locks you originally acquired.

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

The only caveat that comes to mind is that if something fairly serious goes wrong (not a syntax error, but a low level error like IO errors, data corruption, and OS permissions) while altering a table, the table can become implicitly unlocked... and the fact that, as I mentioned, you have to obtain all of the table locks in a single statement, because the next LOCK TABLE will unlock anything you already have locked.

Other threads trying to access the tables you've locked should block until you are done or lose your connection, which would implicitly release the locks, since they're associated with your connection/session.


update with nested join problem First of all newbie here and to the world of Oracle, just trying to get some code working:

UPDATE CUSTOMERS
SET RE.LASTNAME = 'NO REFERRAL'
WHERE RE.LASTNAME IS NULL
   (SELECT CU.CUSTOMER#, INITCAP(CU.LASTNAME || ', ' || SUBSTR(CU.FIRSTNAME,1,1)) ||'.' AS "CUSTOMER NAME",INITCAP(RE.LASTNAME) AS "REFERRED LAST NAME", INITCAP(RE.FIRSTNAME) AS "REFERRED FIRST NAME"
FROM CUSTOMERS CU LEFT OUTER JOIN CUSTOMERS RE
ON CU.REFERRED = RE.CUSTOMER#);

The nested block works fine by giving me a listing of all customers and the person first and last name who referred them including customers who have no one who has referred them. The problem I'm having is the first and last name of referrer will come up as NULL for the people with no referrer. (as it should with the left join), but instead of saying null I would like it to say "No Referral" in the column instead.
I also had the referrer column concatenated together like the customer name at one point, but couldn't get it to do as I wanted hence splitting the name back up. If there is a solution to either problem I would be grateful as it has me stumped.

You can use NVL to return a default string when a null value is encountered:

SELECT NVL(fieldname,'Show this text instead of null')
FROM myTable;

In your case, I would try something like this:

SELECT CU.CUSTOMER#, INITCAP(CU.LASTNAME || ', ' || SUBSTR(CU.FIRSTNAME,1,1)) ||'.' AS "CUSTOMER NAME",
   INITCAP(NVL(RE.LASTNAME,'No Referral')) AS "REFERRED LAST NAME",
   INITCAP(NVL(RE.FIRSTNAME,'No Referral')) AS "REFERRED FIRST NAME"
FROM CUSTOMERS CU
LEFT OUTER JOIN CUSTOMERS RE ON CU.REFERRED = RE.CUSTOMER#;


table partition in order to solve frequent deadlock issues - how would I know if it will work? I use sql server 2005 enterprise edition and have a subscriber DB  - part of a transactional replication - that is used for Business Intelligence purposes - they run reports out of it, and SSIS packages read from it and import data into SSAS.

This DB has over 800 GB, some tables have hundreds of millions of records.

All these reports and packages need to read data using READ COMMITTED isolation level.

As this DB is a subscriber DB and the publisher is very busy, replication keeps updating it all the time - whenever the publisher DB is updated.

Sometimes the replication procedures require exclusive locks on tables (or pages or records),
and it might be that the reports or packages are running from those tables (or pages or records) and as a result DEADLOCKS are more frequent than what I would like to admit.

Example of a replication procedure involved in a deadlock today:

create procedure [sp_MSdel_dbotblBOrder]
        @pkc1 varchar(20)
as
begin
    delete [dbo].[tblBOrder]
where [strBxOrderNo] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end

Just for illustration, I will post below the process that was deadlocked against the procedure above: (it is a monster - and it should be at least a procedure but it is an AD HOC query at the moment)

I could not post the process here, because it is over 30,000 lines.

Anyway, I have been thinking about PARTITION these tables, one by one, starting with the ORDERS table, which is the one that causes most of the DEADLOCKS.

Other than create missing indexes, I just cannot tune all the processes because there are too many.

I believe that my partitioning the orders table (and all of its indexes)
I can get a better management of LOCKS, and quicker updates from the replication agents.

I would partition the order table by DAY OF THE WEEK.
I would have to create another 6 different filegroups.
I have one single drive where I can put the data.

What can I monitor in order to guarantee that this project would work?
By work I mean - eliminate or at least reduce a lot of the deadlocks.

How would I know that partition by day of the week is the best way to partition the order table?
I could partition by month, but I believe the chunks would still be too big.

I could partition the previous years in an Archive partition, and the current year partition by day of the week.

How would I know that this is the best choice over enabling SNAPSHOT ISOLATION level for this database?

Since your problem is a conflict between constant updates and being able to run reports without facing deadlocks, I would recommend you consider using READ_COMMITTED_SNAPSHOT isolation set ON.

This also has overhead, but usually prevents most deadlocking situations. (Nothing is magic, of course.)  You should read the details at:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Note that there is overhead in tempdb required in order to hold a consistent view of the data, which is needed to avoid the deadlocks.  There is another SNAPSHOT isolation level that can be turned on per transaction.

Since this changes the blocking behavior, you may run into some transactions that need to be tuned or given a new index in order to avoid problems.

Therefore, if you use the READ_COMMITTED_SNAPSHOT isolation level, which works fine for me, you will need to monitor it until you are sure that all is well in your instance.


Same query on same same table, different response time I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes.
The setup is:

* virtual server;
* 4 virtual CPU;
* 8 GB of virtual memory ;
* 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available;
* OS Debian 9;
* Mysql 5.6.47;

To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed.
I had abilited the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE `MALICIOUS_TABLE` (
  `column_1` int(11) NOT NULL AUTO_INCREMENT,
  `column_2` varchar(8) NOT NULL,
  `column_3` datetime NOT NULL,
  `column_4` int(11) NOT NULL,
  `column_5` int(11) DEFAULT NULL,
  `column_6` int(11) DEFAULT NULL,
  `column_7` int(11) DEFAULT NULL,
  `column_8` tinyint(1) DEFAULT NULL,
  `column_9` datetime DEFAULT NULL,
  `column_10` int(11) DEFAULT NULL,
  `column_11` varchar(2048) DEFAULT 'column_11',
  `column_12` tinyint(1) DEFAULT NULL,
  `column_13` datetime DEFAULT NULL,
  `column_14` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`column_1`),
  KEY `fk_ual_aut_idx` (`column_2`),
  KEY `fk_aul_c_idx` (`column_4`),
  KEY `kf_ual_po_idx` (`column_5`),
  KEY `fk_ual_ute_idx` (`column_10`),
  KEY `column_1` (`column_1`),
  KEY `column_2` (`column_2`),
  CONSTRAINT `fk_aul_c` FOREIGN KEY (`column_4`) REFERENCES `t_table2` (`column_4`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_aut` FOREIGN KEY (`column_2`) REFERENCES `t_tabl3` (`column_2`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_po` FOREIGN KEY (`column_5`) REFERENCES `t_table4` (`column_5`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_ute` FOREIGN KEY (`column_10`) REFERENCES `t_table5` (`column_10`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1

The table has a not so small number of records:

select count(*) from `MALICIOUS_TABLE`;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time.  The RAM of the server it's good, still getting about 2 giga of free RAM. The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no.
Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself?

For MySQL to handle your query efficiently, you need to create a compound index. Without an index, MySQL must examine every row in your table to count the ones you want. That means it has to read every row from the SSD. That takes time.

Your filtering criteria are ...

WHERE column_4 = 1
  AND column_8 is null
  AND column_3 > CURDATE() - INTERVAL 30 DAY

You need an index that can satisfy your query efficiently. Because two criteria are matches to constant values, those two  columns should come first. Then, the last criterion filters a range of values. Starting with some date in the past it filters records.

So, your index should be on (column_4, column_8, column_3). You create it like this:

 CREATE INDEX tocount ON malicious_table (column_4, column_8, column_3)

You can think of these indexes (they use an indexing technique called BTREE) as lists of data in order. So, the query planner can random-access the index to the first eligible row, and then scan the index sequentially until the last eligible row. That's far more efficient than reading the whole table.

You mentioned your query time is unpredictable. You're right that's unpleasant for your users. It's hard to know exactly why, even for an expert with access to your server. But this general observation applies: If others are using the same server for other things, their operations and yours may interfere with each other in unpredictable ways.

Pro tip Notice that the index I've given you is designed to match the query you gave. A different query might need a a different index. For example,  if your query were

  SELECT COUNT(*), MAX(column_13), SUM(column_7)
    FROM malicious_table
   WHERE column_4 = 1
     AND column_8 is null
     AND column_3 > CURDATE() - INTERVAL 30 DAY

it would be satisfied with this covering index:

CREATE INDEX tosummarize ON malicious_table
    (column_4, column_8, column_3, column_7, column_13);

The index I first suggested is made redundant by this index because this one contains the same columns in the same order as that one.

In general, you should avoid creating lots of one-column indexes in hopes of making queries faster. As a database grows, it generally needs indexes to be added (or sometimes dropped) to match the data. The cool thing about indexes is you can add and drop them without affecting your data or programs. Here's a good reference:  https://use-the-index-luke.com/

Edit: Performance variations between queries can be due to many hard-to-discern factors. Is your MySQL server running on a virtual machine on a busy VM host? Does your MySQL server process serve applications other than yours?  How busy is your system? Are lots of other processes INSERTing or UPDATing rows in your table concurrently with your counting queries? If so, the variability may be due to moment-to-moment changes in that concurrent workload. Bluntly, good luck figuring out that one in any detail! You may want to move on to other database performance issues.

There is one thing to try. Precede your counting query with this transaction isolation level setting:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This reduces the chance that your counting query locks up waiting for other operations on the table to complete. Read this for more details: https://stackoverflow.com/questions/7937472/setting-transaction-isolation-level-of-mysql Your query will still return a useful result, but it might not pick up changes to the table that happen concurrently to your counting request. It is probably acceptable for your application to miss a few most-recent rows in this count.

And, version 5.6 of MySQL is quite old. It was first released in February 2013. There have been bug and security fixes since, Still, many dozens of years of programmer effort have been spent on later versions since 5.6 came out. Most of them have been spent improving performance and reliability. Upgrade if you can.


PostgreSQL insert into table (not origin) based on a condition on fields on different tables Lets say you have 4 different tables:

table_1 is a table holding the original data:

table_1

id1  |  id2  |  score1  |  score2  |  name_1         |  name_2
-----+-------+----------+----------+-----------------+---------
123  | 89898 | 0.5      | 0.8      |Jack             |Joe
129  | 89999 | 0.55     | 0.3      |Chris            |Don
150  | 90098 | 0.8      | 0.1      |Susan            |Anna
170  | 99898 | 0.7      | 0.14     |Ben              |Lisa

table_2 is a table where we would like to insert fields from table_1 (id1, id2, score1,score2) plus a comment string based on a condition on table_3 and table_4 (where both fields name_1 and name_2 are empty)

table_2 (the result table) should look like:

id1  |  id2  |  score1  |  score2  |  comment_string
-----+-------+----------+----------+-----------------
129  | 89999 | 0.55     | 0.3      | 'removed_because_no_value_in_fields_name_1_and_name_2'

table_3 and table_4 are tables which are holding data of two colomns from table_1 and some rows that have been modified previously. The modified fields are empty. The amount of rows is the same like in the origin table table_1.

table_3:

name_1  |
--------+
Jack
[empty]
Susan
Ben

table_4:

name_2  |
--------+
Joe
[empty]
Anna
Lisa

Note:
[empty] means that the field is empty. ids are not representing primary keys. The result table (table_2) should only contain the fields id1, id2, score1,score2 plus a comment string if the fields with the same row number in table_3 and table_4 are empty.

How to achieve this using PostgresSQL 9.1?

You seem to be under the impression that some kind of automatic "row number" would exist. That is not the case. Unlike rows in a spreadsheed, tables in a relational database have no natural order.

This query should do the job, but it relies on the the contents of name_1 and name_2 to make the connection. If you rely on a row number, you have to add an actual column for that.

INSERT INTO table2 (id1, id2, score1, score2, comment_string)
SELECT t1.id1, t1.id2, t1.score1, t1.score2
      ,CASE WHEN t3.name_1 IS NULL
            AND  t4.name_2 IS NULL THEN 'removed_because ...'
       ELSE END AS comment_string
FROM   table_1 t1
LEFT   JOIN table_3 t3 USING (name_1)
LEFT   JOIN table_4 t4 USING (name_2)
ORDER  BY id1; -- undeclared in Q

Based on the assumption that table_3.name_1 and table_4.name_2 are unique. Else, the query could create a "proxy cross join", possibly multiplying rows, if there are several matches.
More about this caveat in this related answer on SO.


How can I correctly choose maximum number of occurrences of a string while grouping by another field? I am using Postgresql 9.0. I have the following fields in a table: id, name.

 id    name
 1     John
 1     Mary
 1     Mary
 1     Mary
 1     John
 1     Mary
 3     Paul
 3     Paul
 3     George
 .     .
 .     .

For each id, I want to select the name that occurs the most. How can I do that?

I tried with the following query but it doesn't work:

select id, max(name)
from table
group by id;

This isn't trivial. First, you want group by id and name and count the rows:

SELECT COUNT(*)
...
GROUP BY id, name

Then select the maximum count for every id. One way to achieve this is by window functions. The RANK() function:

RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC)

assigns a number to every row of the result (after the grouping is done), arranging them (the rows) in partitions with the same id and ordered by COUNT(*) DESC, so for every (partition of) id, the row(s) with the maximum count are assigned a rank of 1. Thus we need to put the above in a derived table and use a WHERE condition to keep only these rows:

WHERE rnk = 1

The final query is like this:

SELECT
    id, name, cnt
FROM
    ( SELECT id, name, COUNT(*) AS cnt,
             RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC) AS rnk
      FROM tableX
      GROUP BY id, name
    ) AS tg
WHERE
    rnk = 1 ;

Tested at SQL-Fiddle

Note that if you have ties in the first place (two or more names with the same maximum count), all these will be returned. If you want strictly one row per id in the final results, you have to use the ROW_NUMBER() instead of the RANK() and possibly alter the ORDER BY clause to explicitly select how the ties will be resolved:

ROW_NUMBER() OVER (PARTITION BY id ORDER BY COUNT(*) DESC, name ASC) AS rnk

Tested: SQL-Fiddle test-2.


SQL database in status SUSPECT I had a database in status SUSPECT (No items).  I have not Backup. I've only .mdf and .ldf files.
After reading all answer regarding this topic, I executed all commands to fix this status, but without success.
I also executed the following commands for recovering my database :

sp_detach_db 'Mydb'

and the following message:

  "Successfully detached database 'Mydb'." )

then executed
    sp_attach_db @dbname = N'Mydb',
       @filename1 = N'E:\MSSQL7\Data\Mydb_Data.MDF',
       @filename2 = N'E:\MSSQL7\Data\Mydb_Log.LDF'

and the following error message :

  ("Server: Msg 823, Level 24, State 1, Line 1 I/O error 38(Reached end
  of file.) detected during read of BUF pointer = 0x11c01980, page ptr =
  0x1f6bc000, pageid = (0x1:0x7bea01), dbid = 7, status = 0x801, file =
  E:\MSSQL7\Data\Mydb_Data.MDF. Connection Broken "

I executed command with the Service Manager stopped (I don't know if it's important)
Please I'm desperate. What can I do to recover my db.
I'm not expert in SQL Server and it would be appreciate if the answers were very clear

If your database is marked suspect and you are desperate, then I'll advice you to set the status to that database, like:

update master.dbo.sysdatabases
set status = 20
where name = 'YourDatabaseName'
Very sorry that you have no backup.
Also have a look this Recommended       actions http://www.techrepublic.com/forums/discussions/it-seems-our-sql-database-is-corrupted/


Get the application name Is it possible -- does a perfmon counter exist -- that would show the name of the end-user application that is connecting to SQL Server?

There is no perfmon counter that would you show you this information.  Why don't you just pull this directly from SQL Server (at least as how SQL Server sees it)?

select
    session_id,
    program_name
from sys.dm_exec_sessions
where is_user_process = 1;

If you're looking for data collection, you can periodically run this and store the output of the above query (or a variation of it, maybe with aggregating by program_name instead of grabbing session_id, depends on really what you're looking for) with a datetime stamp for subsequent querying.


Point PostgreSQL on Ubuntu to a new datadir I have a new Ubuntu 14.04 install on a new drive, with the old main drive containing a PostgreSQL install from the previous Ubuntu 14.04 install.

I've been trying to move the PG dbs from the old location (/olddrive/var/lib/postgresql/9.3/main) to a new one on the new drive (/newdrive/postgresdbs), but haven't succeeded.

This is what I've done:

sudo service postgresql stop  # stop PostgreSQL

sudo pg_dropcluster 9.3 main  # remove the default cluster created during the new Ubuntu install

sudo pg_createcluster -d /newdrive/postgresdbs/ 9.3 main  # recreate the default cluster in the new location (pg_createcluster is a wrapper for initdb on Debian/Ubuntu)

sudo cp -R /olddrive/var/lib/postgresql/9.3/main/base/nnnnn /newdrive/postgresdbs/base/  # copy across each of the data directories from the base dir in the old location to the base dir in the new location, where nnnnn is a number

sudo chown -R postgres:postgres /newdrive/postgresdbs/  # set postgres to be the owner of the new location

sudo service postgresql start  # start PostgreSQL

This seems as if it should work, and PostgreSQL starts fine, but I have to recreate my superuser, and none of the data tables show up - there's just the postgres table there.

I'm now wondering if this is even possible.  If not, is there any way to access the data tables from the old location?  I could initialise everything from a backup, but that may not be entirely up-to-date (and in any case, where's the fun in that?).

I did look at tablespaces, but would they work here, and would I have to give the tablespace name against every query I ran on tables in the old location?

EDIT after Craig's answer:

For reference, the commands I used to implement Craig's answer were:

sudo service postgresql stop
sudo pg_dropcluster 9.3 main
sudo pg_createcluster -d /newdrive/postgresdbs/ 9.3 main
sudo cp -a /olddrive/var/lib/postgresql/9.3/main/. /newdrive/postgresdbs/
sudo chown -R postgres:postgres /newdrive/postgresdbs/
sudo service postgresql start

You can't just copy base from the old cluster to the new one. It doesn't work like that. You'll be missing the transaction logs, transaction commit statuses, global catalogs, and all the other components that make those databases make any sense.

Instead, pg_createcluster in the new location, then replace the entire data directory with the one from your old system. That's the directory with base, pg_xlog, global, etc in it.