Get sql azure dead lock info My application has been logging dead lock exceptions return from sql azure to my .net application: Transaction (Process ID 152) was deadlocked on lock resources with another process....

I would like to investigate this further to see if I can prevent those deadlocks from happening.

How do I get the details about those past and future deadlocks? I remember that the sql server profiler is able to extract some deadlock events with some xml that shows all the queries involved in the dead lock. That is what I'm after.

However, since this runs on Sql Azure, there is no profiler.

You can use extended events on Azure to capture deadlock details.

Following article explains in details.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/21/lesson-learned-19-how-to-obtain-the-deadlocks-of-your-azure-sql-database/

Based on the comment I am adding this section.  I just tested this and it works.

You need to run the following query in Master database (Not user database where your deadlock happened) in order to get the XML of the deadlock.

WITH deadlockcte
     AS (SELECT Cast(event_data AS XML) AS [target_data_XML]
         FROM   sys.Fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL
                ))
SELECT
target_data_xml.value('(/event/@timestamp)[1]', 'DateTime2')
AS Timestamp,
target_data_xml.query('/event/data[@name="xml_report"]/value/deadlock')
AS deadlock_xml,
target_data_xml.query('/event/data[@name="database_name"]/value').value('(/value)[1]', 'nvarchar(100)') AS db_name
FROM   deadlockcte

Which will have the details about the deadlock processes and you can get a deadlock graph by:

Copy the deadlock_xml column results from the previous query and load into a text file. If more than one row is returned, you will want to do each row result separate.
Save the file as a ‘.xdl’ extension, (e.g. deadlock.xdl) which can be viewed in tools such as SQL Server Management Studio as a deadlock report/graphic.

Based on the last comment I am adding my test code which does show text in inputbuffer.

 /*

Deadlock simulation code was copied from:
http://stackoverflow.com/questions/22825147/how-to-simulate-deadlock-on-sql-server

*/

--Simulating deadlock

IF OBJECT_ID('Employees') IS NOT NULL
    DROP TABLE Employees

CREATE TABLE Employees (
    EmpId INT IDENTITY,
    EmpName VARCHAR(16),
    Phone VARCHAR(16)
)
GO

IF OBJECT_ID('Suppliers') IS NOT NULL
    DROP TABLE Suppliers

CREATE TABLE Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Fax VARCHAR(16)
)
GO

INSERT INTO Employees (EmpName, Phone)
VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
GO
INSERT INTO Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO

/*
--Window 1
BEGIN TRAN;
UPDATE Employees
SET EmpName = 'Mary'
WHERE empid = 1

--Window=2
BEGIN TRAN;
UPDATE Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1

Window 1
UPDATE Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1

window 2
UPDATE Employees
SET phone = N'555-9999'
WHERE empid = 1
--commit
*/

DROP TABLE Employees
GO
DROP TABLE Suppliers
GO


PostgreSQL upsert implications on read performance after bulk load In PostgreSQL documentation on populating databases, they mention how we can improve bulk loading operations by disabling constraints and indexes, but seeing how INSERT ... ON CONFLICT (upsert) requires the use of a unique constraint to work, and that perfectly makes sense, I've been wondering how good the following strategy is:

Create multiple connections to load data in batches in parallel into a staging unlogged table
Create deferred constraints to do integrity validation on the table's data
Upsert the staging data into a big target table (5GB+), which also would need to have a PK, unique index or unique constraint to make the upsert possible.

The insert on conflict approach is known to be better than manualing creating SQL functions or scripts to join between the two tables to discover the "new records" to insert and "common records" to be updated. That's the whole point of why INSERT ... ON CONFLICT exists.

Yet I'm still wondering the performance implications for reading the data after loading it.

Questions:

In terms of index bloat, an upsert would be worse than simple bulk copying inserts + update to a table without constraints? Is upsert bad for index maintenance?
If that is the case, I believe faster loads would also imply bad query performance after the table is loaded. Should I recreate the indexes after INSERT ... ON CONFLICT?
Compared to INSERT ... ON CONFLICT, the function/script way of doing it, while less performant for loads, would be better for index maitenance since the target table doesn't need to have an index to do the upserting?

That's the whole point of why INSERT ... ON CONFLICT exists.

I don't think that that is the case.  I think a pretty big point was a convenience so people don't have to implement a catch and retry loop for unique constraint violations upon single-row insertions.  Not rolling back entire bulk inserts due to a few violations is also a pretty good improvement, but I don't think it is the whole point.  And if you are going to have the table offline to other uses anyway, think I think it is actually none of the point of ON CONFLICT.

ON CONFLICT pays a high price to handle concurrency at a fine grained level. If you can guarantee that only this procedure is inserting new records, then one query to do the insertion of new keys and a second query to do the update of olds ones would almost certainly be more performant than using ON CONFLICT.  If your point 3 means that the big table had all constraints and indexes dropped other than the one needed to support the ON CONFLICT, then it seems likely you are in a maintenance window, and so you can probably make the guarantee about no concurrent inserts.

  In terms of index bloat, an upsert would be worse than simple bulk copying inserts + update to a table without constraints? Is upsert bad for index maintenance?

I would not expect this to be the case in general, if you are keeping the unique constraint either way.  But it would depend on things like whether the unique key values of rows being inserted are mostly occurring in order, or random.  Also, bloat would depend on whether rows taking the UPDATE path have room for the updated row in the same block the old version originally resided in (so the table fill-factor), but this would also be true if the UPDATE were a separate statement.  You would have to mock up something similar to your actual conditions and test it to get clear answers.

  Compared to INSERT ... ON CONFLICT, the function/script way of doing it, while less performant for loads, would be better for index maintenance since the target table doesn't need to have an index to do the upserting?

If you take the table offline for normal use and drop all constraints and indexes, then it should be both more performant and lead to less bloated indexes (although perhaps only slightly less bloated) once you rebuild them.  That is the case whether you compare it to either using ON CONFLICT online, or compare it to dropping all-but-one of the constraints and running ON CONFLICT offline.


MSSQL Join between big and empty tables I've got an issue with estimated cost and join predicate :

SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500
OPTION (RECOMPILE)

Table_A has 50k rows, Table_B is empty, Table_C has 2M rows
The PK and FK indexes exists and the stats are up to date.

But for some reason, SQL Server use a Clustred Index Scan on Table_C.
https://www.brentozar.com/pastetheplan/?id=r1G_LnpeU

This is a sample my problem, the prod tables are fare bigger and use too much estimated grant memory because of this issue.

Using a FORCESEEK or change the join to LEFT JOIN Table_C c ON c.Id_B = b.Id AND c.Id_B IS NOT NULL resolve the issue but it's a query (mutliple queries in fact) generate by Entity Framework so I don't have so much control on it.

Is there a way to seek Table_C without changing the query here ?

I was finally able to replicate your issue due to you noting that

LEFT JOIN Table_C c ON c.Id_B = b.Id AND c.Id_B IS NOT NULL

improved the query.

DDL & DML

CREATE TABLE Table_A (id int not null,
                     constraint ClusteredIndex_A primary key (id))

INSERT INTO dbo.Table_A(id)
SELECT top(50000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

CREATE TABLE Table_B (id int not null,
                      Id_A int
                     constraint ClusteredIndex_B primary key (id))

-- constraint FK_C_B FOREIGN KEY (id_B) REFERENCES Table_B(id)
CREATE TABLE Table_C (id int not null,
                      Id_B int
                     constraint ClusteredIndex_C primary key (id),
                     constraint FK_C_B FOREIGN KEY (id_B) REFERENCES Table_B(id))
INSERT INTO dbo.Table_C(id,Id_B)
SELECT top(2000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
NULL
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

CREATE INDEX [NonClusteredIndex-B-A] on dbo.Table_B(id_A);
CREATE INDEX [NonClusteredIndex-C-B] on dbo.Table_C(id_B);

And as a result, your query is going slow due to all the NULL values in the Table_C table.

SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT  JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500 OPTION (RECOMPILE);

Resulting to a starting point close to yours:

The easiest & best solution here would be changing the query as you pointed out.

SELECT c.id
FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT  JOIN Table_C c ON c.Id_B = b.Id and c.Id_B is not null
WHERE a.Id = 2500;

A possible but far from ideal solution would be adding OPTION(loop join) with a plan guide

exec sp_create_plan_guide
@name = N'Guide_1',
@stmt = N'SELECT c.id
FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (LOOP JOIN)';

But the issue with high estimates on dbo.Table_C persists & plan guides are a last resort (if even).

Im gonna leave this up so other people can test & replicate your problem using DDL & DML for now. Maybe statistics or indexing could prove a solution that I was unable to find.

Edit

option(FAST 1) produces single row estimates & low cost (for this single example)

SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT  JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500 OPTION (RECOMPILE,FAST 1);

Again not ideal since bigger resultsets will be impacted and other issues might arise.

What also works is inserting one row in dbo.Table_B

INSERT INTO dbo.Table_B(id,Id_A)
VALUES(1,NULL);

& update the stats UPDATE STATISTICS dbo.Table_B

So sql server does not have to join table_C to an empty table_B

Or insert -1.

INSERT INTO dbo.Table_B(id,Id_A)
VALUES(-1,NULL);

What you then could do, is generate the statistics with the 1 row, and create these on the empty dbo.Table_B.

By right clicking the database --> tasks --> generate scripts

Choose table_B

On advanced scripting options select script out statistics & histograms:

Save to new query window, next, next, finish.

And for me I got these statistics:

/****** Object:  Statistic [ClusteredIndex_B]    Script Date: 17/01/2020 10:58:36 ******/
UPDATE STATISTICS [dbo].[Table_B]([ClusteredIndex_B]) WITH STATS_STREAM = 0x0100000001000000000000000000000019B912CC000000007F020000000000003F02000000000000380300003800000004000A000000000000000000000000000700000046C8B30045AB000001000000000000000100000000000000000000000000803F000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000010000000100000014000000000080400000803F00000000000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000190400000000000000000000000000001F00000000000000BB00000000000000C300000000000000CB000000000000000800000000000000100014000000803F000000000000803FFFFFFFFF04000002000000859CB10045AB0000000000000000F03F030000000000000001000000000000000000F03F00000000000000000000000000000000000000000000004000000000000000400000000000000000BF89B00045AB0000000000000000F03F010000000000000001000000000000000000F03F00000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F01000000000000000000000000000000, ROWCOUNT = 2, PAGECOUNT = 1
GO
/****** Object:  Statistic [NonClusteredIndex-B-A]    Script Date: 17/01/2020 10:58:36 ******/
UPDATE STATISTICS [dbo].[Table_B]([NonClusteredIndex-B-A]) WITH STATS_STREAM = 0x010000000200000000000000000000000ECB838500000000CC010000000000007401000000000000380200003800000004000A00000000000000000000000000380300003800000004000A00000000000000000000000000070000007721B40045AB000001000000000000000100000000000000000000000000803F0000803F0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000014000000000080400000803F0000803F000000000000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100400000000000000000000000000000800000000000000100000000000000001000000000000000000000000000000, ROWCOUNT = 1, PAGECOUNT = 1
GO

Deleted all data:

DELETE FROM dbo.Table_B

Reran the two update statistics with stat streams.

To get a better performing execution plan with correct estimates & results


backup using gzip slow We are currently backing up some schemas in Postgres using this command:
pg_dump -h localhost -n test_schema mydb | gzip > /data1/backup/test_chema.dmp.gz

And we are getting a rate of 50 Megabytes per minute, which is pretty slow, we think this could be improved.
Disk seems to be OK so is CPU.
Any thoughts on how this can get improved?

Use directory mode and run several parallel jobs:
pg_dump -F d -j 4 -h localhost -n test_schema -f /data1/backup/test_schema mydb

That will automatically compress the backup.


Fast insert from temp table to main table - advice needed, match index order? I'm re-writing a C# console app from single insert to do bulk inserts

based on advice here, I need to bulk insert into a staging table (with id identity seed)
Then I load into temp tables, do a little processing, and then insert into the main table

This is for a real time 24/7 so it needs to be as fast and efficient as possible

1) what is the fastest way to bulk insert data from c# console app, to staging table (every 1 min, approx 50,000-150,000 records) - I'm planning on using SqlBulkCopy

2) Does having id with identity seed on the staging table slow down things ? should i be using a heap?

3) My main question and the highest risk area is inserting to the main busy table so this needs to be as fast as possible to reduce locking: when doing the bulk insert into main table, should i be pre-ordering the data so it matches the main clustered index (its on 3 columns: sensorId, DateTime, pkId) or will the I just be sorting twice? this table is extremely busy.
Any other advice on this area appreciated.

this whole bulk insert process is just a Stored Procedure, and i was thinking of calling it every 1 min from sql agent to make sure it runs (not sure if this is best solution)

sql server 2017 ent.

I think that you may need to look at redesigning the clustered index on your main table.  Best practices for a clustered index is to make it unique and ever increasing, but you have the first column being sensorid.  Which means that (assuming I have inferred correctly) that you have a relatively stable set of sensors with lots of date/time and measurements.

This will lead to lots of page forwards and an extremely fragmented, which increases your maintenance duration as your index defragmentation process works.  Without knowing your actual query load, I can only make broad suggestions.

Change your clustered index to be Date/Time, SensorID, pkID
Add a non-clustered index on SensorID, Date/Time

With that in place, your inserts won't fragment the clustered index as badly.  BulkInserts won't cause blocking to read workloads.  Having a clustered index on the stage table that matches it's target will help.


PostgreSQL: Executing DDL on every schema I've a multi-tenant DB setup, and need to add some columns.  I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my databases.  Initially, I'd thought I might be able to do it as a single query (cursor on pg_catalog), but thinking a command-line invocation of psql -f might be the preferred way.

I would prefer the latter solution.  You can collect the schema names into a file (one schema per line) in psql:

\o change_schema.sql
\t on

SELECT n.nspname
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema';

-- reset the output
\o
\t off

Then you can easily do the following:

Have a DDL changing script (for example, change_schema.sql), without reference to the including schema

SET search_path TO :schema;

BEGIN;
...
...
ALTER TABLE orders
ADD COLUMN last_modified timestamp;
...
...
COMMIT;

Then you can turn every line of the schema list into a line like

psql -h dbhost -d targetdb -f change_schema.sql -v schema=<schema_name>

with a simple sed command, for example - then you just have to run these commands.   Of course, you can turn it into a proper shell script if you like.


Please review the My.INI settings of my PC. File Attached Below is the my.ini file of one of my PC running MySQL 5.5 with 1 GB RAM. It is not using concurrent connections and we are using a stand-alone PC for a single application and single user.

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------

# CLIENT SECTION

[client]

port=3306

[mysql]

default-character-set=latin1

# SERVER SECTION
[mysqld]
log-bin=E:/CRITICAL_MYSQL_LOGGING/AUTOMATED_BINARY_LOGS/MySqlBinLog

# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow.
max_connections=02

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again.
query_cache_size=15M

# The number of open tables for all threads.
table_cache=256

# Maximum size for internal (in-memory) temporary tables.
tmp_table_size=9M

# How many threads we should keep in a cache for reuse. When a client
# disconnects
thread_cache_size=8

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=20M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=500M

innodb_log_file_size=10M

innodb_thread_concurrency=8

log-queries-not-using-indexes

log-warnings

log=E:/CRITICAL_MYSQL_LOGGING/QUERY_LOGS/QueryLog

log-error=E:/CRITICAL_MYSQL_LOGGING/ERROR_LOGS/ErrorLog

log-slow-queries=E:/CRITICAL_MYSQL_LOGGING/SLOW_QUERY_LOGS/SlowQueryLog

log-bin-index=E:/CRITICAL_MYSQL_LOGGING/BinLogIndexFile

long_query_time=2

I have updated the original my.ini file. Please suggest better values for a 1 GB single user PC.

I also want to know how to flush logs and when.

You need more RAM.  Windows can barely survive with a Gig of RAM.  I would put at least 4 Gigs in the machine.


show users doesn't show anything, although it has users So I'm using MongoDB, and this is my first time dealing with a VPS on my own, I'm trying to view the users i have on the website. I know for a fact that there are signed up users, because I can signup & Login and it all works.
I Also tried to connect to the database from MongoDB Compass but failed.

First of all, I thought you are talking about MongoDB users. So, I had to change my answer.
You can try the below command for seeing to the collection which is users
db.users.find({})


Fix line by line parameterization in a table This table has a lot of columns but I need to fix "patrimonio".

It looks like this:

As you can see, there are a lot of different values. The correct way to use this table is with S-1234. But theres a lot of S1234,S 1234,X124 and etc.

I'm trying to update ( dealing with one type of error at a time ) all rows following a certain contidion using stuff.

But this query is returning  The subquery is returning more than 1 value....

begin transaction
            update frotas.Viaturas
            set patrimonio =
            (select stuff(patrimonio,2,0,'-')
             from   Frotas.Viaturas
             where  patrimonio not like 's-%'
             and    patrimonio not like 's %')
        where patrimonio=patrimonio
        rollback

This is because, is not updating row by row. How can I achieve this ( to update row by row )? I'm trying to think in something with dynamic sql but I can't think in a logic.

The Idea is to update each row with its own value, but with the stuff.

I think you might consider removing all the non-numeric values, then update it with the 'S' hyphen prefix.  Two transactions. I'm operating from the position that you don't really trust the mask/format that was used when inserting the data but the numeric values are okay (e.g. 'As you can see, there are a lot of different values').

Here's a function that removes non-numeric characters (from here: How can I strip non-numeric characters out of a string?)

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

Usage:

UPDATE Viaturas SET patrimonio = dbo.fnRemoveNonNumericCharacters(patrimonio)

Then:

UPDATE Viaturas SET patrimonio = CONCAT('S-', patrimonio)


Get SSIS to check if excel file is updated? So I have a SSIS package that reads from an excel file and populates a Sql server database. It assumes that the file will be updated monthly, and consequently, the data pull to update it is also done monthly.
However, as this excel file needs to be updated manually, there may be a case in which the user forgets to re-upload a new excel file. Naturally, I want to detect if that is the case. How would I be able to do this?
The best that I could think of was to use a script task to alter the excel file's sheet name to something like "already processed" after the data pull, and check for that condition at the beginning of the task. However, this seems like a work around solution that's overly complicated. Any ideas for better ways to do this?

You could have a meta data table that stores the file name, processing date, and last modified date. Then you can have your job run daily and identify whether the file's last modified date is greater than the stored value.
If the file hasn't been modified in 32 (arbitrary number but base it on business tolerance) send a notification to you and/or the business process owner saying no new file detected.
This won't cover cases where they've opened the file, changed nothing but clicked save none-the-less (or Save As and overlaid the file). At that point, you'd get to write actual change detection. Which could be a weird kind of fun but surely there's more important priorities for the business.
I'd advise against changing the tab name. Lots of reasons for it but the easiest one to enumerate is technical/license based. Programmatically renaming an Excel worksheet is an easy technical change. But, to overcome a business process deficit, you're going to take a dependency on the Excel object library. You get to read/write to Excel via SSIS for free. Reading/Writing to Excel from a Script Task is going to use the COM interop functionality. To use that, you need to have Excel installed. Excel on a desktop, yes. Excel on a Server, no (it kills your uptime + costs you an license for the Office installation)
Yes, I am quite aware of nuget packages, the ACE library etc that are free and can provide interactivity with Excel but then that gets us into server installation, GAC requirements, 32 vs 64 bittedness discussions etc.


DatabaseError: 1366 (HY000): Incorrect string value: '\xF0\x9F\x92\x96' I'm using MySQL 5.7.20. My schema, tables and columns are all supporting utf8mb4. I used the following:

ALTER SCHEMA `MyDB`  DEFAULT CHARACTER SET utf8mb4  DEFAULT COLLATE utf8mb4_general_ci ;
ALTER TABLE MyDB.Videos CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

I don't understand why I'm still getting the following error:

DatabaseError: 1366 (HY000): Incorrect string value: '\xF0\x9F\x92\x96' for column 'Content' at row 1

I can't figure out the reason, please help.

When I specified CHARSET='UTF8MB4'in my connection parameters, it worked.


Query performance degrades with time and use I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my application's ODBC SQLExecute() call.

Rebuilding the indexes manually (in SQL Server Management Studio) "fixes" both problems.

Here is some more information;

DB is ~13 Gig in size
Using ODBC ver 3.5.1 in the app
The apps are C++ with ODBC interface to the SQL DB
Test case is on a static DB - no inserts, thus no fragmentation should be possible!
Small queries are initially less than 1 sec, and degrade to over 50 seconds
Have seen the problem on all (3) machines it's run

I'm having a hard time understanding why the same query works fine for a while, but then starts slowing waaay down??

Any ideas would be greatly appreciated.

Queries can start to slow down over time for a few reasons and you rebuilding the indexes can be fixing the problem a few ways. I'll share some of the more common reasons in my experience but there could be other causes as well. My guess is you are suffering from one of these issues.. I've also asked some questions as a comment to your question to see if we can get more details. But a few thoughts:

Statistics Getting Stale SQL Server maintains column and index statistics. These essentially tell the Query Optimizer how your data is distributed. This information is critical to the optimizer in choosing the right access method for data (Seek vs Scan) and then choosing the join method being used. If you have auto update statistics enabled (default setting in SQL.. At the database level) these get recomputed, but only when "enough" data changes. So if you have some inserts into your table but never manually update statistics and the inserts/updates are not enough to trigger an auto stats update you could be suffering from poor plans for your data distribution... Rebuilding your indexes also recomputes your index statistics I would create a job to manually update statistics on a regular basis, this is a best practice anyway - and the next time this happens try and just run sp_updatestats in your database and see if you notice a difference

Query plan issues You could be suffering from parameter sniffing - basically the first time a query runs one value is passed in - the query gets optimized for that value. When you next run it with a different value that would benefit from a different query plan, it suffers with the original query plan resulting in a slow query. When things run slow for the app - are they also slow if you run the same query in SQL Server Management Studio? If it is fast in SSMS but slow in the app - that can be a good sign pointing towards parameter sniffing. If it is consistently slow across the board over time for all queries and regardless of parameters, then I wouldn't look here. This article talks quite a bit about parameter sniffing.

Not enough memory/too many ad hoc plans It sounds like you are sending ad hoc SQL to SQL Server. This can bloat your plan cache sometimes, especially if you have a separate plan for each execution of a query. Depending on the memory on your server, this can also lead to the issue. How much memory is on your server? Check out this link on the problem with single use plans. You don't have a lot of great solutions in SQL Server 2005 for this problem, if you have it. If you can recreate this problem in a non-prod environment, I would suggest running DBCC FREEPROCCACHE in your non-prod environment if this happens again. Please note! This is an instance wide setting, if you do this on production - any stored query plans in cache for any database will no longer be there. It means you have to "pay" for compilations again. If you have high concurrency and a busy system, this could prove to cause issues. If this is the only real database and you are suffering from performance issues anyway, it doesn't hurt to try this in production.. If you have other Databases and just want to do it for this database, this blog post explains how to approach a clear for just one DB.

Index Fragmentation - It is possible that index fragmentation is the actual issue here, but I'm surprised it gets so bad so quick. If your tables are clustered on a key that causes fragmentation quickly and you have a lot of inserts, this could be the case. It would be made much worse if you were underpowered in terms of memory and disk IO. Setting up a job to rebuild/reorganize your indexes on a regular basis would be good. Based on your answers to some questions in the comments above there may be other things to do to minimize the impact of this.


How to find if SQL server backup is encrypted with TDE without restoring the backup Is there a way to find from the SQL Server Backup file or MSDB tables if the backup is encrypted with TDE without trying to restore the backup file?

Thanks

I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data.

However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after.  There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted."

I looked at some of my backups which were both TDE encrypted and not TDE encrypted.

The backups of my TDE databases had the certificate thumbprint in that column and the backups that did not have TDE databases had null.


How to design database tables using normalization in better manner in SQL? I have designed my tables in below manner.

Countries Table:-

PkId --- + Country Name --- + StatusFlag
1        | India            | L
2        | China            | L
3        | Sri Lanka        | L

StateNames Table:-

PkId --- + CountryFkId --- + State Name --- + StatusFlag
1        | 1               | Maharastra     | L
2        | 1               | Madhya Pradesh | L
3        | 1               | Utter Pradesh  | L

Cities Table:-

PkId --- + StateFkId --- + City Name --- + StatusFlag
1        | 1             | Mumbai        | L
2        | 1             | Pune          | L
3        | 1             | Nagpur        | L

PinCodes Table:-

PkId --- + CityFkId --- + PinCode --- + Area ---       + StatusFlag
1        | 1            | 400037      | Antop Hill     | L
2        | 2            | 412206      | Ambade         | L
3        | 3            | 441108      | Ashta          | L

Customers Table:-

 PkId --- + CustomerId --- + CustomerName --- + PinCodeFkId--- + StatusFlag
 1        | C00001         | John             | 1              | L
 2        | C00002         | Ram              | 2              | L
 3        | C00003         | Anwar            | 3              | L

Query:-

Select C.CustomerId, C.CustomerName, P.Area, CT.CityName, S.StateName, CNT.CountyName
From dbo.Customers C
Inner Join dbo.PinCodes P On C.PinCodeFkId = P.PkId
Inner Join dbo.Cities CT On P.CityFkId = CT.PkId
Inner Join dbo,StateNames S On CT.StateFkId = S.PkId
Inner Join dbo.Countries CNT On S.CountryFkId = CNT.PkId

But my colleagues told that if I write like this, then huge logical reads will occur. Also they told that these table structures follow too much normalisation. Instead of designing Customers table like above, they have given other suggestion. i.e., Maintaining CountryName, StateName, CityName, PinCode and Area in Customers table itself.

Restructured Customers Table:-

PkId --- + CustomerId --- + CustomerName --- + PinCode ---   + Area ------- + CityName --- + StateName --- + CountryName--- + StatusFlag
1        | C00001         | John             | 400037        | Antop Hill   | Mumbai       | Maharastra    | India          | L
2        | C00002         | Ram              | 412206        | Ambade       | Pune         | Maharastra    | India          | L
3        | C00003         | Anwar            | 441108        | Ashta        | Nagpur       | Maharastra    | India          | L

Restructured Query:-

Select CustomerId, CustomerName, Area, CityName, StateName, CountyName
From dbo.Customers

In above query, I didn't put any join conditions with other relevant tables. Which kind of table design best suitable for normalisation and better performance when table has huge number of records?

The table structure that your colleagues have suggested might be appropriate for a data warehouse, but it would typically not reduce I/O in an OLTP database.

Consider this--if you run a query for all of the customers in a city that returns 1,000 rows, with the denormalized table, the database engine will have to read the CityName, StateName, and CountryName 1,000 times.  Fewer rows will fit on a database page, so it will end up reading more pages to fulfill the query.

With normalized tables, it will only have to read the IDs for those columns (which is very small compared to text fields), and read the actual CityName, StateName, and CountryName only once.  More rows will fit on a database page, so fewer pages will need to be read to fulfill the query.

Additionally, think what the denormalized table does to the buffer.  If there are 1,000 customer records in the buffer, then the city, state, and country names are also in the buffer 1,000 times.  With normalized tables, those names are only in the buffer one time, using much less space.  So you'll have more data in buffer with normalized tables.


SSMS: Show execution plan for a stored procedure as a single tree I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan.

MSDN states the following:

  If the statement is a stored procedure
  or Transact-SQL statement, it becomes
  the root of the graphical execution
  plan tree structure. The stored
  procedure can have multiple children
  that represent statements called by
  the stored procedure. Each child is a
  node or branch of the tree.

In this case I expect to run a stored procedure and see only one tree where the root node is the stored procedure.

How it is possible to switch on such plan visualization for a stored procedure?

UPDATE:
It looks like an estimated subtree cost is not the same for actual and estimated execution plans.

Is there a way how one can see Estimated execution cost for the whole stored procedure and not only for its statements?

The Estimated plans (CTRL + L) show a tree structure. Actual plans (CTRL + M) do not.

I've never seen a tree for a procedure in an actual plan (and I never use estimated plans anyway)


Clustered Index Scan (Clustered) vs. Index Seek (NonClustered) + Key Lookup (Clustered) I have the following tables and content

create table t(i int primary key, j int, k char(6000))
create  index ix on t(j)

insert into t values(1,1,1)
insert into t values(2,1,1)
insert into t values(3,1,1)
insert into t values(4,1,1)
insert into t values(5,1,1)
insert into t values(6,1,1)
insert into t values(7,1,1)
insert into t values(8,2,2)
insert into t values(9,2,2)

select * from t where j = 1
select * from t where j = 2

I'm really confused on why the first SELECT uses only a Clustered Index Scan (Clustered) while the second one uses a Index Seek (NonClustered) and a Key Lookup (Clustered).

Can someone clear this up for me?

Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria.

Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria.  Once the query returns a certain percentage of the table (AKA The tipping point) the optimizer falls back to a  Clustered Index scan, as that is the more efficient plan.

See Kimberly Tripp's blog post about The Tipping Point


CSV (UTF-8) to Postgres database (UTF-8), doesn't show äöå characters I have csv-file that is UTF-8 encoded. I'm using python (psycopg2) to copy it into a Postgres database. My Postgres database is using UTF-8. However I have problems to show Ä, Ö and Å characters.

If I'm using query SET client_encoding = 'ISO-8859-1'; It would fix the issue.

My database is looking like this (psql -l):

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
 data      | postgres | UTF8     | fi_FI.UTF-8 | fi_FI.UTF-8 |

My server locale is fi_FI.utf8

My CSV file is UTF-8. Its made with python command:

with open('users.csv', "w+", encoding="UTF-8", newline='') as f:
     writer = csv.writer(f)
     writer.writerows(list_table)

I'm writing it to Postgres table with this code:

cur = conn.cursor()

cur.execute("CREATE SCHEMA IF NOT EXISTS data;")

cur.execute("DROP TABLE IF EXISTS data.users"`)

cur.execute("CREATE TABLE data.users(name VARCHAR(40), lastname VARCHAR(40), phone VARCHAR(13), email VARCHAR(100), iban VARCHAR(18), id VARCHAR(11))")
copy_ = """
           COPY data.users FROM stdin WITH CSV HEADER`
           DELIMITER as ',' ENCODING 'UTF-8'
           """
with open('users.csv', 'r') as f:
    cur.copy_expert(sql=copy_sql, file=f)
    conn.commit()
    cur.close()

First I used this code

with open('users.csv', 'r') as f:
    next(f)
    cur.copy_from(f, 'data.users', sep=',')
    cur.close()

I switched to Copy_expert to get that ENCODING 'UTF-8' into the string. However it didn't do anything.

Now when I use pgadmin4 or console (Linux ubuntu 18.04LTS terminal) I can't get ÄÖÅ characters to show up. Mökkisuo is "MÃ¶kkisuo" and Lammasjärvi is "LammasjÃ¤rvi". I'm using basic query like select * from data.users to check results.

However if I change my client encoding to ISO-8859-1 everything shows like it should? (SET client_encoding = 'ISO-8859-1';).

Start of EDIT

Here is my locales.

~$ locale
LANG=fi_FI.utf8
LANGUAGE=
LC_CTYPE="fi_FI.utf8"
LC_NUMERIC="fi_FI.utf8"
LC_TIME="fi_FI.utf8"
LC_COLLATE="fi_FI.utf8"
LC_MONETARY="fi_FI.utf8"
LC_MESSAGES="fi_FI.utf8"
LC_PAPER="fi_FI.utf8"
LC_NAME="fi_FI.utf8"
LC_ADDRESS="fi_FI.utf8"
LC_TELEPHONE="fi_FI.utf8"
LC_MEASUREMENT="fi_FI.utf8"
LC_IDENTIFICATION="fi_FI.utf8"
LC_ALL=

~$ locale charmap
UTF-8

And here is how I get bad result!

:~$ sudo su - postgres
postgres@Server:~$ psql
postgres=# \c data
data=# select * from data.users;

result is like this:

 Piritta      | MÃ¶kkisuo        | 0    | piritta.mokkisuo@notreal.com

Oh and data is just generated. It's not real people data.

What I have to change to get this show correctly in UTF-8? I don't understand where that conversion to ISO-8859-1 is coming from?

I don't understand why file that is UTF-8, Database that is UTF-8. Locale that is UTF-8 etc. Has to be changed to Latin-1 (client encoding) to show up correctly? What is causing this? Everything should already be UTF-8? So why ISO-8859-1? Why UTF-8 doesn't show these characters?

I solved my own problem. Everything was right from server side and problem was few little things. My first mistake was that I made two text files that I used in my code to generate users. These two text files was in ANSI encoded. I made them with notepad in windows and didn't think much of it.

Even though Visual Studio Code show these ANSI encoded text-files correctly and writes them inside of UTF-8 csv-file correctly. Even if I open that csv-file with notepad++ it's show's those characters as UTF-8 encoded. However when I pushed them to database they all broke up. Not sure why they show up correctly in UTF-8 file in notepad++? Is there some weird translation correction?

I solved this to convert those ANSI text files to UTF-8 with notepad++.

However my second mistake was that I didn't open those files as UTF-8! So even when I converted them to UTF-8 my Visual Studio tried to read them as latin-1 (ISO-8859-1). This round they where broken before I pushed them to database. I took me while to notice, because end result was still the same as ANSI coded files.

I corrected this with adding encoding="utf-8" to all my open statements. Example here with open('users.csv', 'r',encoding="utf-8") as f: I wanted to be sure that Visual Studio Code will open them as UTF-8 and not some weird format.

This finally solved my issue!! This was really specific problem, so I'm not sure if this will help anyone else. However I wanted to post solution here just in case. I hate those post that just writes up that this is now solved and never tells how.


SSIS / SSAS: Handling Age Columns I have some columns representing Age in a table, taking values from 0 to 100, but also -1 for 'Missing Value'. I don't want to store them as strings to be able to find average age etc., but SSAS turns 0 into NULL by default, and I also want to replace -1 with NULL, so there is the classic "duplicate attribute key" error.

Is there a proper way / type to handle age values?

You are confusing 2 things in your question. Since you state you have duplicate attribute key errors you are using the the age table as a dimension, but since you also want to find averages you are using the age table as a fact table.

When using ages as a dimension you probably don't want every single age on your axis, so what is typically done is divide the ages in buckets and have the buckets stored as strings so you get something like this

+-------+--------+
|  Age  |  sales |
+-------+--------+
| <10   |      0 |
| 10-20 |    100 |
| 21-30 |    250 |
| 31-40 |    124 |
| 41-50 |   3598 |
| > 50  |    715 |
+-------+--------+

You could either use discretization buckets in your dimension or create the buckets manually in your dimension table.

However if you want to average ages too, I suppose you want to see the average across some dimensions (such as average age of people ordering product X) so you need to create a fact table with the keys of the dimensions you want to report across and then define relationships between your fact table and dimension tables

Mixing the 2 concepts (dimensions and facts) in the same table will lead to headaches over time so you should either solve that in your ETL process or using named queries in your dmv in SSAS.


Data redaction with regex in Oracle I would like to redact data with regex in oracle database. I know the procedure but I get unpredictable results.

I use the following script to apply redaction to a certain column with card numbers.

BEGIN
DBMS_REDACT.ADD_POLICY (
   object_schema          => 'my_schema',
   object_name            => 'my_table',
   column_name            => 'card_number',
   policy_name            => 'redact_my_table',
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => null,
   regexp_pattern         => '(............)(.+)(........)',--DBMS_REDACT.RE_PATTERN_CC_L6_T4,
   regexp_replace_string  => '\1****\3', --dbms_redact.re_redact_ccn, --DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST,
   regexp_match_parameter => 'i',
   policy_description     => 'Regular expressions to redact credit card numbers',
   expression             => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''my_schema'''
   );
END;
/

Expected result would be first 6 and last 4 unmasked like 123456****1234, but I get this instead:

From the documentation I would suppose the below script would work by default, but it performs a full redaction instead of just masking some chars. I get a whitespace per row.

BEGIN
DBMS_REDACT.ADD_POLICY (
   object_schema          => 'my_schema',
   object_name            => 'my_table',
   column_name            => 'card_number',
   policy_name            => 'redact_my_table',
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => null,
   regexp_pattern         => DBMS_REDACT.RE_PATTERN_CC_L6_T4,
   regexp_replace_string  => DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST,
   regexp_match_parameter => 'i',
   policy_description     => 'Regular expressions to redact credit card numbers',
   expression             => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''my_schema'''
   );
END;
/

Database used is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

This are the constants in DBMS_REDACT package:

RE_PATTERN_CC_L6_T4        CONSTANT   VARCHAR2(33) := '(\d\d\d\d\d\d)(\d\d\d*)(\d\d\d\d)';
RE_REDACT_CC_MIDDLE_DIGITS CONSTANT   VARCHAR2(10) := '\1XXXXXX\3';

If i put the \d in regex patter i always get full redaction. Using dot i managed to get the scrambled data from the image.

If anyone could try here is also the drop statement:

exec dbms_redact.drop_policy('my_schema','my_table','redact_my_table');

Any ideas what am I doing wrong?

Thanks to @mustaccio's question got me thinking about datatype issues.

NVarchar2 seems to be the issue in this problem.

Creating a copy of the column in varchar2 fixed the issue. Data redaction on varchar2 is working as expected.


Mysql select rows with 7 continuous dates Imagine a simple table with these columns:
item_id,  date
And values:

CREATE TABLE foo (item_id int, date date);

INSERT INTO foo(item_id, date)
VALUES
    ( 1, '2017-02-10' ),
    ( 2, '2017-02-10' ),
    ( 1, '2017-02-11' ),
    ( 1, '2017-02-12' ),
    ( 1, '2017-02-13' ),
    ( 2, '2017-02-13' ),
    ( 1, '2017-02-14' );

How to select item_ids that have 7 continuous day records in table?

Start and end dates are unknown. It should just be available from any starting date up to 7 continuous days after.

MySQL 8

MySQL 8 provides window functions...

SELECT item_id
FROM (
  SELECT
    item_id,
    date,
    count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
  FROM (
    SELECT
      item_id,
      date,
      date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
    FROM foo
  ) AS t
) AS t2
GROUP BY item_id
HAVING max(seq) > 7;

Explanation

This is what we're doing on the inner.

SELECT
  item_id,
  date,
  date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
FROM foo

 item_id |    date    | diff
---------+------------+------
       1 | 2017-02-10 |
       1 | 2017-02-11 |    1
       1 | 2017-02-12 |    1
       1 | 2017-02-13 |    1
       1 | 2017-02-14 |    1
       2 | 2017-02-10 |
       2 | 2017-02-13 |    3
(7 rows)

Here we return the differences. What we need to now do is isolate the ones where the date difference is 1. We assume here if the result of the difference is null it is because there was no previous date to subtract, so we set it to 1. Then we if we don't have a 1, we set the value to null so count() skips it.

SELECT
  item_id,
  date,
  count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
FROM (
  SELECT
    item_id,
    date,
    date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
  FROM foo
) AS t;

 item_id |    date    | seq
---------+------------+-----
       1 | 2017-02-10 |   1
       1 | 2017-02-11 |   2
       1 | 2017-02-12 |   3
       1 | 2017-02-13 |   4
       1 | 2017-02-14 |   5
       2 | 2017-02-10 |   1
       2 | 2017-02-13 |   1
(7 rows)

From this point, it's just a GROUP BY and HAVING.

This was tested in PostgreSQL because MySQL 8 wasn't out yet. If you haven't used PostgreSQL, download it for free and check it out. It's like MySQL but better in every single way.


mysql “Lock wait timeout exceeded” two transaction with different keys indexed I have set of tables that I need to delete their records with a specific key and insert new records. Different transactions are used one for each key:

Transaction 1:

DELETE FROM table1 WHERE id=1; -- many records
DELETE FROM table2 WHERE id=1; -- many records
INSERT INTO table1 VALUES (1,...),...; -- many records
INSERT INTO table2 VALUES (1,...),...; -- many records

Transaction 2:

DELETE FROM table1 WHERE id=2; -- many records
DELETE FROM table2 WHERE id=2; -- many records
INSERT INTO table1 VALUES (2,...),...; -- many records
INSERT INTO table2 VALUES (2,...),...; -- many records

When the two transactions are running at the same time, I get the lock wait timeout exceeded error for one of them. The id for both table is indexed, so it should be row based locking, why do I still get this error?

Using InnoDB tables.

If the values for the 'id' are adjacent, like '1' and '2' in your example, including the case where they are not adjacent but there are no rows in the index with values in between them, then I would suggest that you are bumping into the index gap lock... which is part of "row" locking.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
— http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html

As that page suggests, a transaction isolation level of READ COMMITTED might be helpful, if that's not what you're using and if the gap locks are the issue.
It also might be contention for the primary key on the inserts.
The INNODB_LOCKS and INNODB_LOCK_WAITS tables in information_schema could provide additional diagnostic information, as might SHOW ENGINE INNODB STATUS; though it can be cryptic.


Not getting CLI Trace log DB2 Currently I am working on fixing an issue with the Java-DB2 connection leak. For which I require the CLI Trace logs. In my db2cli.ini I have added the below parameters, but still I am not getting the the trace logs.

    [COMMON]
    JDBCTrace=1
    JDBCTraceFlush=1
    JDBCTracePathName=C:\tracejdbc

Earlier we were using DB2 9.7 in which I was able to get the logs for the above changes. And now we are using DB2 10.5.3. I am trying it myself. Meanwhile it would be helpful on getting an Expert advice.

Looks like you are confusing the JDBC trace and the CLI trace. To enable the JDBC trace in the JCC driver, which is the only one available in DB2 10.5, you need to set the JDBC connection properties as described in the manual, for example, via the JDBC URL:

String url = "jdbc:db2://my.host.com:50000/mydb" +
             ":traceFile=/tmp/jcc.trc;" +
             "traceLevel=" + com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS + ";";

Various trace levels are described here.

Only the Type 2 JDBC driver will read db2cli.ini, so setting CLI properties has no effect if you are using Type 4 connectivity. Type 2 connectivity is deprecated an is not recommended for use.


Calculating the FileStream Data Columns Length matching used FS Drive used space Kindly advice me on below :

I am process of calculating the total size of LOB objects with below data types (includes Views and user schema tables) .

select * from information_schema.columns where data_type in
    ('TEXT', 'NTEXT','IMAGE' ,'XML', 'VARBINARY')
    or
    (data_type = 'VARCHAR' and character_maximum_length = -1)
    OR
    (data_type = 'NVARCHAR' and character_maximum_length = -1)

Once I get the output of below query , I am using DATALENGTH function to get the size of each column .

SELECT SUM(DATALENGTH('ColumnName')) / 1048576.0 AS ColumnName  FROM DatabaseName.schema.Tablename

NOTE :  The output will be Megabytes as I am using / 1048576.0

After all the above , my total drive size meant for storing File Stream Data is 1 TB  . But post calculating the length of each LOB Column with DATALENGTH function , it does not match up to the current used space on Drive .

For example :

Current Drive size is 1 TB out of which 951 GB is free space 0.99 TB . which means 50 GB is used by File Stream data type columns . But when I check all columns from above T-SQL I could not get exact size matching used space (50 GB) . Not even close size . they show very minimal usage .

What else could be using the drive apart from the data types mentioned above  ?

How to calculate the size of columns using DATALENGTH function which will match up to used space in File Stream Drive ..

Space for each file on the file stream volume is allocated in units of the file system allocation unit (blocksize). When the actual data size is not an exact multiple of the allocation unit size, allocated space is rounded up to the next allocation unit size multiple.

The PowerShell command below will show allocation unit size for each volume on the server:

Get-WmiObject `
    -Query "SELECT Name, Label, Blocksize FROM Win32_Volume WHERE FileSystem='NTFS'" `
    | Select-Object Name, Label, Blocksize;

Using the returned blocksize value from the above command, you can determine the actual space allocated for each file stream file with a query like:

DECLARE @AllocationUnitSize decimal(10,0) = 65536;
SELECT
      CEILING(DATALENGTH(file_stream)/@AllocationUnitSize)*@AllocationUnitSize AS AllocatedSpaceBytes
    , DATALENGTH(file_stream) AS UsedSpaceBytes
FROM dbo.YourFileStreamTable;

The amount of unused allocated space will be significant for small files and a large allocation unit size. For example, consider that an actual file size of 1K will require 64K with that AU size, wasting 63K of storage per file.


Moving Postgres 10 data dir on Ubuntu 18 I am trying to move the initial Postgres data dir from
/var/lib/postgresql/10/main

to another harddrive. The harddrive is accessable within the terminal via
/media/chris/Datenspeicher

I used this guide to switch my data dir. Postgres is starting up after modifying the postgres.conf file but im not able anymore to access the database through pgadmin or the terminal.
When trying to access the database its asking if the database is running on port 5432 and accepting connections.
Why? What am I doing wrong?
Update:
After clean install of ubuntu and postgres all errors still remain.
The postgres log output is:

2018-07-06 14:16:04.958 CEST [5664] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-07-06 14:16:04.998 CEST [5664] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-07-06 14:16:05.124 CEST [5665] LOG:  database system was shut down at 2018-07-06 14:00:21 CEST
2018-07-06 14:16:05.167 CEST [5664] LOG:  database system is ready to accept connections
2018-07-06 14:16:05.714 CEST [5672] [unknown]@[unknown] LOG:  incomplete startup packet

Please check the socket file is in the /tmp. If it is there then create a link to /var/run.

OR,
I prefer this method to move the data directory on Ubuntu.

I hope you have the backup copy of the old directory which is in /var/lib/postgresql/10/main.

service postgresql stop
rm -rf /media/chris/Datenspeicher/*
chown -R postgres:postgres /media/chris/Datenspeicher/
chmod 700 /media/chris/Datenspeicher
/usr/lib/postgresql/10/bin/initdb -D /media/chris/Datenspeicher
rm -rf /media/chris/Datenspeicher/*
sudo rsync -av /var/lib/postgresql/10/main/* /media/chris/Datenspeicher
in postgresql.conf file,  data_directory = '/media/chris/Datenspeicher'
service postgresql start


Mysterious inequality behavior when comparing date strings Can someone explain this mysterious Postgres behavior? I'm performing the following queries at the psql command line.

mydb=> select '20150526' > '2015-05-25';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' > '2015-05-26';
-[ RECORD 1 ]
?column? | f

mydb=> select '20150526' > '2015-05-27';
-[ RECORD 1 ]
?column? | f

If Postgres was actually comparing the strings directly, either all three would be true or false, depending on how character ordering is defined. This seems to establish that Postgres is trying to convert the strings to dates before comparison, presumably based on some heuristic about the formats. However:

mydb=> select '20150526' < '2015-05-27';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' < '2015-05-26';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' < '2015-05-25';
-[ RECORD 1 ]
?column? | f

Note that the result of the middle query is incorrect, if interpreted heuristically as dates! The inverse issue occurs with the >= operator. This caused an extremely subtle bug in a trigger function I'm using.

So, two mysteries:

Postgres seems to be heuristically interpreting these strings as dates in order to do comparisons. This is a little surprising, so I would like confirmation.
The results are wrong for the < and >= operators when the dates are the same but in different formats. This seems like a bug.

What makes you think you are comparing dates?

Actually, you are comparing string literals which - in the absence of a cast context and any explicit cast - default to text:

SELECT '20150526' > '2015-05-26' AS text2text
     , '20150526'::date > '2015-05-26'::date AS date2date;

text2text | date2date
----------+----------
 t        | f

Try:

SELECT pg_typeof(col) FROM (VALUES ('20150527')) t(col);  -- no cast

pg_typeof
---------
text

The result of your comparison of text values depends on your locale, on COLLATION rules to be precise. To see the default COLLATION of your session:

SHOW lc_collate;

COLLATION can be also be set per column or even per expression. To see the actual COLLATION for any expression use the collation for (any) construct:

SELECT collation for (my_column) FROM tbl LIMIT 1;

To test how strings are sorted in your locale:

SELECT *
FROM  (
   VALUES
     ('20150526')
   , ('2015-05-25')
   , ('20150525')
   , ('2015-05-26')
   , ('20150527')
   , ('2015-05-27')
   ) t(col)
ORDER BY col;    -- COLLATE "C"

Compare this to the sort order by pure byte-value by appending COLLATE "C" to ORDER BY. To see the result of your comparison with a different COLLATION:

SELECT '20150526' > '2015-05-26' COLLATE "C" AS text2text_c;


Numa Node configuration for SQL server 2016 I have a question on one of my new build sql servers where we are planning to migrate our SQL databases from older SQL2012 running on windows 2012.

New build- SQL2016 on Windows 2016

My current [SQL2012]==> When querying os_nodes dmv shows what i am assuming as 4 numa nodes config with memory node id=0,1,2,3 & processor group 0,1,0,1 and scheduler count= 20 for all id's

My new [SQL2016]==> When querying os_nodes dmv shows what i am assuming as 8 numa nodes config with memory node id=0,0,0,0,1,1,1,1 & processor group 0,0,0,0,1,1,1,1  and scheduler count= 9 for all id's

Below is O/P from os_nodes dmv between old and new configured SQL servers

Questions

  When viewed via task manger/ssms it shows 2 Numa nodes. Is my above
  understanding wrong of 8 Numa nodes?

  If those are 2 Numa nodes will i have any performance impact compared
  to current setup?

Also note softnuma_configuration_desc shows ON for sql2016

Please suggest

When viewed via task manger/ssms it shows 2 Numa nodes. Is my above understanding wrong of 8 Numa nodes?

No, SQL Server 2016 started to ship with automatic numa node partitioning (soft numa) which is what you're seeing here.

  If those are 2 Numa nodes will i have any performance impact compared to current setup?

Impossible to say. Automatic Soft-Numa was added as a default to the product starting with 2016 as certain tasks and threads are on a per-numa basis. Thus, in many environments that had high core count and low numa node count there would be an overall performance improvement by using soft-numa.

Your mileage may and will vary. If you're unsure or don't want to test the new configuration, I would suggest you turn the automatic soft-numa off for now until you can test. This is based on absolutely no data but configuration stabilization between upgrades.

If you want to turn soft-numa off, you can run the following command:

ALTER SERVER CONFIGURATION SET SOFTNUMA OFF


Having trouble with interface table structures in MSSQL Hi I'm currently working on a project that involves a third party database and application. So far we are able to successfully TEST and interface data between our databases. However we are having trouble when we are extracting a large set of data (ex 100000 rows and 10 columns per row) and suddenly it stopped at the middle of transaction for whatever reason(ex blackouts, force exit or etc..), missing or duplication of data is happening in this type of scenario.

Can you please give us a suggestions to handle these types of scenarios.

Here's our current interface structure

OurDB -> Interface DB -> 3rdParty DB
OurDB: We are extracting records from OurDB (with bitcolumn as
false) to the InterfaceDb
InterfaceDB: After inserting records from OurDB, we will update OurDB
bitcolumn as true
3rdPartyDB: They will extract and delete all records from InterfaceDB
(they assume that all records is for extraction)

Did you consider to run in loop and extract each time TOP 10000 (or 5000)?

It is possible only if you can identify them later for delete / bitcolumn update. For example, if you have and identity column ID, it could look like

declare @id int
set @id = (select min(id) from yourtable where bitcolumn = false)
while exists (select top 1 1 from yourtable where bitcolumn = false)
begin
    select c1,c2
    from yourtable
    where bitcolumn = false
        and ID between @id and @id + 5000
    [your code here]
    update yourtable
    set bitcolumn = false
    where  ID between @id and @id + 5000

    set @id = @id + 5001
end


Why do people recommend not using the name "Id" for an identity column? I was taught not to use the name Id for the identity column of my tables, but lately I've just been using it anyways because it's simple, short, and very descriptive about what the data actually is.

I've seen people suggest prefixing Id with the table name, but this just seems to make more work for the person writing the SQL queries (or the programmer if you're using an ORM like Entity Framework), particularly on longer table names such as CustomerProductId or AgencyGroupAssignementId

One third-party vendor we hired to create something for us actually named all their identity columns Ident just to avoid using Id. At first I thought they did that because Id was a keyword, but when I looked into it, I found that Id isn't a keyword in SQL Server 2005, which is what we are using.

So why do people recommend not using the name Id for an identity column?

Edit: To clarify, I am not asking which naming convention to use, or for arguments to use one naming convention over the other. I just want to know why it's recommended to not use Id for the identity column name.

I'm a single programmer, not a dba, and to me the database is just a place to store my data. Since I usually build small apps, and typically use an ORM for data access, a common field name for the identity field is much easier to work with. I want to know what I am missing out on by doing this, and if there are any really good reasons for me not to do this.

The table name prefix has very good reasons.

Consider:

TableA (id int identity, stringdata varchar(max))

TableB (id int identity, stringdata varchar(max))

We want to DELETE from TableA records that exist in both tables.  Easy enough, we will just do an INNER JOIN:

DELETE a
FROM
  TableA A
INNER JOIN
  TableB B
    ON b.id = B.id

....and we just wiped out all of TableA.  We inadvertently compared B's ID to itself -   every record matched, and every record got deleted.

If the fields had been named TableAId and TableBId this would be impossible (Invalid field name TableAid in TableB).

Personally I have no issue with using the name id in a table, but it's really a better practice to preface it with the table name (or entity name, if TableA were people then PeopleId would work fine too) to avoid accidentally comparing to the wrong field and blowing something up.

This also makes it very obvious where fields come from in long queries with lots of JOINs.


connection pooling, transactions, nested transaction and rollback I've got strange behavior on production.

First user request thru application failed, I've got SQLServerException with "Lock request time out period exceeded." After that following requests are successfully executed. After some time I've closed client application and all requests disappeared. I know that requests were successfully executed because I have JDBC logging on my client application.

Is there any other possible scenario other than nested transactions and rollback?

Connection pooling uses same spid (session) when it's possible so that must have been the case here.

Every request is just one stored procedure which has explicit transaction defined. I'm not sure how this could happened because transaction would either be committed or rollback because of this block.

begin try

  begin tran

  set lock_timeout x

  ...

  commit tran

end try

begin catch

  if @@trancount > 0

    rollback tran

  ...

end catch

Also, would it be wise to add on start just after "begin try" rollback tran if @@trancount > 0? That way I would prevent future issues like this.

Note, yes, I know that SQL Server doesn't have nested transactions and I don't use them. I'm mentioning them because I don't see other way that this could happened. Thanks!

EDIT:

I believe I have more conclusions about this.

BEGIN TRY
  BEGIN TRAN
      SET LOCK_TIMEOUT 10000
      UPDATE tbl ...
  COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN
  INSERT INTO dbo.ErrorLog ...
END CATCH

I think that my update tbl went into lock timeout. After that it went to catch block and I believe that rollback tran went into timeout also. Is this possible? Imagine database maintenance job (index rebuilds) and synchronous mirroring running in parallel with this query.

That would match my application logs because I see 20 seconds (10 on update and 10 on rollback) difference between call to stored procedure and sql exception timeout.

I also don't have record in dbo.ErrorLog for that entry.

It must have been lock timeout on rollback because I wouldn't get such exception in application logs if it happened in try block because it's contained by catch so it must have been raised in catch.

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

  It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.


How to create a Table in Oracle which has a variable of type number? Suppose I want to store the social security numbers with a prefix letter 'S' (eg. S123456789) or a driving license number D (eg. D12345).

Is there any function or method to prefix it? Or patters?
I just need a hint.

You could use a "code", whereby the first two digits represent your letter 'D' = X or 'S' = Y (X and Y are integers) and the rest of your number could be your numerical part - that would mean a lot of parsing though and my scheme won't work for more than 1 letter (without complex coding).

Why not put the letter in a separate column? What is your actual business requirement? 0-padding is out because 0-padded numbers appear to have to be VARCHARs - which is not what you want as I understand it.

In fact, you'll have to start with 'K' = 10 (then 'Z' = 36, A = 37) &c., because if you start with 'A' = 10, then what's 'K' going to be? Again, why do you want to do this? You could also have 'A' = 10, 'B' = 11... 'Z' = 36. If you only have a couple of letters in your system, I would use a small lookup table for the code and have a pure number in a INTEGER column.

What's wrong with parsing VARCHARs? You'll be parsing your numbers anyway. Personally,  I'd go with a lookup table and separate field or a VARCHAR. But, I may have given you a couple of ideas? P.s. welcome to the forum!


Dynamic column selection mysql I have following code, for this, I have two tables, one table contains questions and another table contain answers.
answers table something like following : (ans_question_1 to ans_question_95)

| answersId |ans_question_1| ans_question_2| student_id
|-----------|--------------|---------------|-----------
| 1         | Male         | 23            |11
| 2         | Female       | 21            |12

Questions table (questions 1 to 95)

| question_id | Question|
|-------------|---------|
| 1           | Gender
| 2           | Area code

Want table something like this (Do not want manual code like 'left join')

| student_id  | Question  | Answer
|-------------|-----------|-------
| 11          | Gender    | Male
| 11          | Area code | 23
| 12          | Gender    | Female
| 12          | Area code | 21

i tried following code:

SELECT DISTINCT q.`question_id`,
CONCAT("ans_question_", q.question_id),
    (
        SELECT (CONCAT("ans_question_", q.question_id))
        FROM `answers` a
        WHERE REPLACE(CONCAT("ans_question_", q.question_id), 'ans_question_', '')=q.`question_id`
    ) AS answer
FROM questions q, options_type o
WHERE o.`options_type_id`=q.`questions_type_id`
AND q.questions_status=1

SELECT (CONCAT("ans_question_", q.question_id)) i tried to get column value of the answer table, but its printing column name, not value, if somehow I can extract value by this code then my problem could solve if anyone can help.
Thanks

Answer something Like :

| student_id| Gender| Area code| Other question
|-----------|-------|----------|---------------
| 11        | Male  | 23       |Other answers
| 12        | Female| 21       |Other answers

You need to UNPIVOT the current result.

I've set up the next example:

create table if not exists questions (question_id int, question varchar(20));
create table if not exists answers (student_id int, answer_id int, ans_question_1 varchar(20), ans_question_2 varchar(29));

insert into questions values (1,'Gender'),(2,'Area code');
insert into answers values (11,1,'Male','23'),(12,2,'Female','21');

This solution uses a CROSS JOIN with the questions table and it assumes that exist one question_id for each ans_question_xx.

Important

  It must exists a question_id = 1 and one ans_question_1 column, that belong s to this question.

select     student_id, aq.question,
           case aq.question_id
                when 1 then ans_question_1
                when 2 then ans_question_2
           end Answer
from       answers
cross join (select question_id, question from questions) aq
order by   student_id, aq.question_id;

| student_id | question  | Answer |
|------------|-----------|--------|
| 11         | Gender    | Male   |
| 11         | Area code | 23     |
| 12         | Gender    | Female |
| 12         | Area code | 21     |

Rextester here

Using a dynamic query

First build the whole 'when xx then ans_question_xx' using group_concat().

select @caseWhen := group_concat(concat('when ',
                                        cast(question_id as char(20)),
                                        ' then ans_question_',
                                        cast(question_id as char(20))) separator ' ')
from questions;

when 1 then ans_question_1 when 2 then ans_question_2

Then build the sql query sentence:

set @sql = concat('select student_id, aq.question, case aq.question_id ',
                  @caseWhen,
                  ' end Answer from   answers
                    cross join (select question_id, question from questions) aq
                    order by student_id, aq.question_id;');

Finally execute it:

PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

The result is the same:

| student_id | question  | Answer |
|------------|-----------|--------|
| 11         | Gender    | Male   |
| 11         | Area code | 23     |
| 12         | Gender    | Female |
| 12         | Area code | 21     |

Rextester here

Using an stored procedure

You can use previous dynamic query within an stored procedure:

create procedure getStudentAnswers(StudentId int)
begin

    set @caseWhen := (select group_concat(concat('when ',
                                        cast(question_id as char(20)),
                                        ' then ans_question_',
                                        cast(question_id as char(20))) separator ' ')
                     from questions);

    set @sql = concat('select student_id, aq.question, case aq.question_id ',
                      @caseWhen,
                      ' end Answer from   answers
                        cross join (select question_id, question from questions) aq
                        where student_id = ', cast(StudentId as char(20)),
                       ' order by student_id, aq.question_id;');

    PREPARE dynamic_statement FROM @sql;
    EXECUTE dynamic_statement;
    set @sql := null;
    set @caseWhen := null;
    DEALLOCATE PREPARE dynamic_statement;

end \\

call getStudentAnswers(11);

call getStudentAnswers(12);

Rextester here


Data Modeling constraint issue Can someone help me with this school question? I have this data model shown below:

here is the question:

You are modeling an emergency communication system used to contact teachers in case inclement weather emergencies cause delayed openings or closures of the school. You may reach teachers by phone and/or email. The school must have at least one means of contact (phone or email) for each teacher. Each teacher may have more than one of each contact type. Which type of entity would you use in conjunction with the TEACHER entity to model a teacher’s contact information?

You decide to model a CONTACT table with primary key of Teacher-ID and Contact-Info, which is the contact information itself. The school principal, a major stakeholder, tells you she wants to track the area code for each teacher’s phone number(s):

Will your original modeling concept work? If not, how would you change the model to reflect the principal’s request?
How many entities would you need to represent the unique attributes of telephone numbers and email addresses so that no entity contained null values?
The principal reminds you that every teacher must have at least one phone number, but having an email address is not a requirement. Where would you reflect the phone number constraint in your model?

Can someone explain how to enforce the constraint?

The problem statement says: You decide to model a CONTACT table with primary key of Teacher-ID and Contact-Info, which is the contact information itself.
Your solution doesn't do this, which is why your professor didn't like your answer, I suspect.
Your solution has a few other issues that may have affected the professor's assessment:

You have abstracted out the email domain to a separate table. I'm guessing you did this because you thought that you were normalizing out the domain name. This is not really how normalization works. It's not about removing any data that appears to be repeated. It's about removing out data which is semantically identical so that when it changes you only have to change the data in one place. The only rational reason to "normalize" out the email domain is if you expect email domains to change and you want to get every email address with that domain updated in one operation. The chance of this happening realistically is vanishingly small, so it's not worthwhile to do.
The problem statement states outright that the contact table key is a composite key which includes an element which is natural
Your solution tries to address the requirement for a constraint that enforces the "at least one phone number" business rule by including a foreign key to phone in the Teacher table, but this relationship is not reflected in the diagram with a relationship line.
Your solution may or may not address the part of the problem that states your entities should represent the unique attributes of telephone numbers and email addresses so that no entity contained null values
Your Phone table does not have a primary key indicated

I believe what you were expected to do was to propose an Entity Sub-type solution for this problem.  Consider the following:

You want a single CONTACT table with a primary key consisting of a Teacher-ID and Contact-Info, which is the contact information, itself, as per the problem statement
You want to represent the distinct attributes of email addresses and phone numbers
You want to avoid nullable attributes
You want to have a constraint which enforces the requirement for one (primary) phone number included in the model itself (as opposed to code)

The parts of a phone number (in USA and Canada) would include:

Country Code (1)
Area Code (000) (aka NPA)
Exchange (000) (aka NXX)
Subscriber (0000)

The parts of an email address are:

user
domain

Note that email domain is a tricky one because the arbitrary constraint in the problem statement precludes dividing the email domain into subdomain (optional), domain name and TLD.
So taking all of this into account, you might have an ERD that looks something like this:

NOTE: This is not a good solution to a real-world problem, but it addresses the arbitrary requirements of the problem statement.
By using entity-subtyping, you can respect the problem constraint that says you have a CONTACT table with a compound key made up of the TeacherID and the contact information itself.
Furthermore, the two entity subtypes (PHONE_CONTACT) and (EMAIL_CONTACT) can each represent the parts of those types of contact methods without nullable fields. Note that in order to do that and also respect the foreign key needs of the subtypes you would want the whole contact information to be a derived column (made up of the individual parts) if your RDBMS has a feature which supports this, otherwise you would have to resort to redundant data, surrogate keys, violating the business rule that CONTACT includes the contact information itself in it's primary key, or a complex check constraint on each subtype.
The other issue with this solution for practical purposes is that it will require a complicated transaction to establish a new TEACHER record because the teacher and their primary phone number will all need to be established within a single unit of work and constraints may need to be relaxed inside that unit of work an re-imposed and checked at the end.  Such is the way of canned problems with arbitrary rules. They are good for learning concepts but they don't always make for sensible, supportable business applications in the real world.


This behavior is documented:

  In to_timestamp and to_date, weekday names or numbers (DAY, D, and related field types) are accepted but are ignored for purposes of computing the result. The same is true for quarter (Q) fields.


Does Greenplum support dynamic sql? PostgreSQL implements the execute ... using option to pass parameters into dynamic SQL, and as far as I can tell this feature was introduced in version 8.4. We are using Greenplum, which is forked from PostgreSQL at version 8.2, so it does not have this feature.

Is there any other way to do the same thing in Greenplum or PostgreSQL 8.2?

I cannot tell for sure, but there is a hint in the CREATE FUNCTION documentation:

  langname
  The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined procedural language. See CREATE LANGUAGE for the procedural languages supported in Greenplum Database.

In turn, the referenced place says

  The PL/pgSQL language is installed by default in Greenplum Database.

Now the EXECUTE ... USING is a PL/pgSQL feature, so the only question is which PostgreSQL version it comes from.  The documentation points to 8.2 - and this way it looks like you are out of luck.

I've contacted the Greenplum support to get a definitive answer, but never got an answer.

As for working around the lack of that construct, you can obviously concatenate together any SQL string you want and execute it.  Be careful to correctly quote the passed-in values and avoid SQL injection.


Understanding COUNT() as `count`, I'm currently learning how to build a site in PHP mysql. However, I seem to fail to understand COUNT() as count and wouldn't mind some further explanation.

I get the principles of COUNT, 0 || 1, and how it returns all the values that pertain to that query. But, don't see how COUNT as count works.

Anyhow, this is how the code I'm writing goes - so we have a working example - and where I first became perplexed.

SELECT COUNT(`id`) as `count`,
       `id`
FROM `user`
WHERE `email`='$email'
AND `password`='".md5$password."'";

If anyone can explain be a great help!

That is called a column alias.

An alias is used to give columns a new name for the result set. This can be used to name a column based on an expressions (because otherwise the expression wouldn't "have" a name) or to rename columns, e.g. because the select list contains columns from different tables with the same name:

select t1.foo as t1_foo,
       t2.foo as t2_foo
from t1
   join t2 on ...

returns a result with the columns t1_foo and t2_foo

or

select avg(salary) as avg_salary
from employees

Btw: using a reserved word as an object name (count) isn't such a good idea in the first place.


Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0 I didn't know if this is possible using just a query or if I need some procedural logic on this one.

I have this table:

CREATE TABLE `default_messages_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `owner_user_id` mediumint(8) NOT NULL,
  `to_user_id` mediumint(8) NOT NULL,
  `read_message` tinyint(1) NOT NULL DEFAULT '0',
  `date_read` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_by_owner` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_by_to` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `message_id_owner_user_id_to_user_id_UQ`
      (`message_id`,`owner_user_id`,`to_user_id`)
) ENGINE=MyISAM
  AUTO_INCREMENT=2
  DEFAULT CHARSET=utf8
  COLLATE=utf8_unicode_ci

And a simple record for testing purpose:

INSERT INTO `default_messages_users` (`id`, `message_id`, `owner_user_id`,
    `to_user_id`, `read_message`, `date_read`, `deleted_by_owner`, `deleted_by_to`)
VALUES
    (1, 1, 1, 2, 0, '2012-09-04 14:09:14', 0, 0);

For the query:

SELECT Count(*)
FROM default_message_users
WHERE owner_user_id = @SomeValue and deleted_by_owner = 0
    or to_user_id = @SomeValue and deleted_by_to = 0

If @SomeValue = 1 then the query should return this row if owner_user_id = 1 && deleted_by_owner = 0 or to_user_id = 1 && deleted_by_to = 0, is the same as owner_user_id == to_user_id == 1 but always checking in pairs as explained before.

Is that possible using just a query or do I need programming help here?

Unless I'm misunderstanding your question, I think this solves your problem:

SELECT Count(*)
FROM default_message_users
WHERE (owner_user_id = @SomeValue and deleted_by_owner = 0)
    or (to_user_id = @SomeValue and deleted_by_to = 0)

Note the addition of ( and ) to denote the order of operations for the AND and OR operators.

EDIT:  As andry-m pointed out in the comments below my answer, order of operations dictates that AND conditions will be evaluated prior to OR conditions. My intention with this answer is that in more complicated cases it is easier to explicitly define the desired logic rather than to rely on the implicit order of operations.


ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.

Do you think that indexes going to reduce query run time?

Any ideas how can i pass through the error code?

SQL Query takes 10.1 secs and got over 380 times execution in slow log.

SELECT
  l.ID,
  post_title,
  post_content,
  post_name,
  post_parent,
  post_author,
  post_status,
  post_modified_gmt,
  post_date,
  post_date_gmt
FROM
  (
    SELECT
      wp_posts.ID
    FROM
      wp_posts
    WHERE
      wp_posts.post_status IN ('publish')
      AND wp_posts.post_type = 'post'
      AND wp_posts.post_password = ''
      AND wp_posts.post_date != '0000-00-00 00:00:00'
    ORDER BY
      wp_posts.post_modified ASC
    LIMIT
      100 OFFSET 214000
  ) o
  JOIN wp_posts l ON l.ID = o.ID

Indexes that i need to create;

MariaDB [database]> ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_type_post_passw_post_statu` (`post_type`,`post_password`,`post_status`);

  ERROR 1071 (42000): Specified key was too long; max key length is 1000
  bytes

MariaDB [database]> ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_modified` (`post_modified`);
Query OK, 453289 rows affected (10.839 sec)
Records: 453289  Duplicates: 0  Warnings: 0

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT 0,
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`),
  KEY `wp_posts_idx_post_modified` (`post_modified`)
) ENGINE=MyISAM AUTO_INCREMENT=463265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

You mentioned a problem with creating an INDEX, but I see several other problems.  Many of them, if fixed, could avoid the index problem.

Huge OFFSET

  LIMIT 100 OFFSET 214000

This sounds like a crawler is scraping your site.  Block the crawler.

Huge number of "pages"

If it is not a crawler, then please explain who would would have the patience t0 step through to page 2141 !!

More than that, why do you have that many pages??  That is not what WP is designed for.

Can't fully index

Because of this "range", it is not possible to build an truly efficient index for the query:

AND wp_posts.post_date != '0000-00-00 00:00:00'

Do you really have 'zero' dates in your table?

Engine

MyISAM is deprecated and going away; switch to InnoDB.  InnoDB has different limitations on indexes, so it may make your index problem go away.

1000

Do you really have long passwords?  SELECT MAX(CHAR_LENGTH(post_password)) FROM wp_posts;  If it is significantly less than 255, shrink the column definition.

Do you need utf8mb4 for the password?  Is it a "plaintext" password; if so, then that charset is probably valid.  If it has been hashed, consider ascii, which won't have the 4x issue.


Separate SQL Logins for reading from and writing to table good design? This is a question for SQL Server but could probably apply to nearly any SQL implementation.

A client will be consuming a web service API. From a security standpoint, my idea was to ETL all the pertinent data into one table and let the client pull from that table. Let's call the table FoobarTbl. I also am locking down the SQL Login to only have access to this one table and nothing else.

I took it further by creating two SQL Logins:

SQL Login which can execute SELECT to the table. (i.e. FoobarLoginRead (
SQL Login which can execute INSERT and UPDATE to the table. (i.e. FoobarLoginWrite).

Is this a good design for security or is it overkill and problematic? I observed that the UPDATE operation didn't function without adding SELECT, which kinda defeats the purpose of having separate logins for reading from and writing to the same table.

My thought process is: If somehow a nefarious user got the creds for FoobarLoginWrite, they would only have visibility to the one table but also they couldn't actually grab any data. A user nefariously updating or inserting data is an acceptable loss, since the source data is being ETL'd to this table. The bigger concern is grabbing the data from the table.

Am I overthinking this or is this a reasonable approach to locking down SQL Logins?

By "locking down SQL Logins", I mean effective permissions for what resources a SQL Login can access such as tables, views, and so on. In my case, I created two SQL logins. Both have permissions to access one table. And my idea was to give one login SELECT permissions to this one table and the other login gets INSERT and UPDATE permissions to this same table.

In short, what I'm asking is is having two separate logins for the same table, with what I outlined above, a good approach for securing SQL data or am I overdoing it and this would be considered poor design?

You simply need to follow the Principle of Least Privilege.

If you have one entity, whether it be a user or an application, that entity should have an account with the minimum permissions required to do its work.  So if you will have a web API that will only read from one table, then its website should be using an account that only has permission to read that one table.

Your question doesn't seem to indicate who or what will be using the read/write account.  If nobody will be writing to the table (which I'm guessing is correct, based on the fact that the data came from another source), then only the ETL process should have permission to write to the table.


Select column values linked to exact set of values in another column Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example:

user_id IN (1, 3)    => chat_id 2
user_id IN (1, 2, 3) => chat_id 1
user_id IN (1, 2)    => no result

Is there a simple join to return the correct chat_id?

|-----------------------|
| chats_users           |
|-----------|-----------|
| chat_id 1 | user_id 1 |
| chat_id 1 | user_id 2 |
| chat_id 1 | user_id 3 |
| chat_id 2 | user_id 1 |
| chat_id 2 | user_id 3 |
| chat_id 3 | user_id 2 |
| chat_id 3 | user_id 3 |
|-----------------------|

Assuming a PRIMARY KEY on (chat_id, user_id) - or a UNIQUE constraint plus NOT NULL on both columns.

To allow any number of IDs, use a 1-dimensional array parameter:

SELECT chat_id
FROM   chats_users c
WHERE  user_id = ANY ('{1,2,3}'::int[])
GROUP  BY 1
HAVING count(*) = array_length('{1,2,3}'::int[], 1)
AND    NOT EXISTS (
   SELECT 1
   FROM   chats_users
   WHERE  chat_id = c.chat_id
   AND    user_id <> ALL ('{1,2,3}'::int[])
   );

Typically, in function or prepared statements, you provide the array parameter only once. Example for prepared statement:

PREPARE foo(int[]) AS
SELECT chat_id
FROM   chats_users c
WHERE  user_id = ANY ($1)
GROUP  BY 1
HAVING count(*) = array_length($1, 1)  -- all required user_id
AND    NOT EXISTS (
   SELECT 1
   FROM   chats_users
   WHERE  chat_id = c.chat_id
   AND    user_id <> ALL ($1)  -- no additional user_id
   );

Tests:

EXECUTE foo('{1,2,3}');  -- 1
EXECUTE foo('{1,3}');    -- 2
EXECUTE foo('{2,3}');    -- 3
EXECUTE foo('{1,2}');    -- empty

db<>fiddle here
Old sqlfiddle.

That's basically the first solution by Martin in this related answer on SO (with some modifications to fit your case):

How to filter SQL results in a has-many-through relation?

There are faster solutions for a small, given number of IDs.


Trying to get count of childs rows I'm trying to get the count of child of a parent row. The sql query I'm using as follow only pulls the immediate count, how can I get total count as childs can have more childs.

select  c.id, c.base32_id, c.author, c.message, c.title, c.type, c.datetime,
(select count(*) from comment where parent_id= c.id ) as ChildCount
from comment c where c.parent_id is null order by id asc limit 25

http://sqlfiddle.com/#!15/158ea

You need to first retrieve the whole tree of comments using a recursive common table expression. Then you can count the number of children for each root node:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select root_id, count(*) as comment_count
from all_comments
group by root_id;

SQLFiddle: http://sqlfiddle.com/#!15/158ea/15

If you don't want to get the count per "thread" but for each node, you can use something like this:

with recursive all_comments as (
   select id, parent_id, id as root_id, 1 as level
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id, p.level + 1
   from comment c
     join all_comments p on c.parent_id = p.id
)
select ac.id,
       ac.parent_id,
       ac.root_id,
       count(*) over (partition by ac.root_id) - count(*) over (partition by ac.root_id order by level) as child_count
from all_comments ac
order by ac.id;

SQLFiddle: http://sqlfiddle.com/#!15/158ea/17

The above is using window functions to calculate the total number or nodes for a root node and the number of nodes in each level:

count(*) over (partition by ac.root_id) counts the number of children (including the root) for each root node.

count(*) over (partition by ac.root_id order by level) counts the number of nodes up to the current's node level. The difference between the total count and the ones "till now" is the number of child nodes.

Btw: comment is a SQL keyword, you shouldn't use it as a table name.


Best practise for copying a database from production Currently after releasing a new major or minor version of our application, we take a copy of the production database and perform the below process:

Copy production database to test server
Obfuscate the data and setup the test users
Switch the old test database with the obfuscated new database
Repeat for the development database.

The intention of this is to ensure all our environments are as close to one another as possible.  This originated as a manual process but due to increasing complexity we've started to automate it.

However what is the best way to automate a post deployment process such as this?  Our currently automation is a bespoke c# program - however, would an SSIS package be more appropriate?  Or is there a tool out there that is made for the job?

SSIS could certainly take care of this for you but as with many things there are several ways to complete the task.

1) You have several options within SSIS to complete the copy process, Copy Database is one of them. You could backup the database and restore it using a SQL Script. You may need to use the file system task to move the backup file but that depends on the configuration of your infrastructure.

2) Easily taken care of in SSIS by executing a no. of scripts against a target database.

3) Again, easily completed by running a SQL script against the target server.

4) You could use package configurations to specify a different environment/connection strings very easily.

I hope that's some help.