Fragmented clustered primary key (sequential GUID) index after processing - SQL Server

Now I'm worried that this schema change won't help that much.

Changing the column from a varchar(36) to a uniqueidentifier will greatly reduce storage requirements from 36 bytes per row to 16 bytes per row; for each 100,000 row table, that equates to a savings of 2MB.  2MB may not sound like much by itself, but since the column is the primary key, that 2MB applies to each non-clustered index on the table.  If any other tables are using foreign keys to ensure relational integrity, the space savings applies to those tables as well.  Realize also that this space savings doesn't only apply to on-disk storage, it also applies equally, and one might argue, more importantly, to rows contained in the buffer-pool.

It appears from a cursory glance at your sample table above that the average row size is somewhere around 600 bytes.  Converting the varchar(36) to a uniqueidentifier will immediately offer a 3.3% space savings for the clustered index, and more than that for each non-clustered index.

As a bonus, once you convert the column from a varchar(36) to a uniqueidentifier, it will no longer be possible to insert this is a bad key into the column, as is currently possible.  This will result in much better data integrity, and will potentially save you a lot of work down the road.

Having said that, I decided to try to recreate the problems you're seeing with index fragmentation.  I created a test table with the varchar(36) primary key column with 100,000 rows.  I then converted the column to a uniqueidentifier and was unable to see a drastic increase in index fragmentation.  My test bed code is:

USE tempdb;

/* create our test table, with a VARCHAR primary key */
IF OBJECT_ID('dbo.guid_insert') IS NOT NULL
DROP TABLE dbo.guid_insert;
CREATE TABLE dbo.guid_insert
(
    PK VARCHAR(36) NOT NULL
        CONSTRAINT PK_guid_insert
        PRIMARY KEY CLUSTERED
        WITH (
            DATA_COMPRESSION = NONE
            , PAD_INDEX = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
            , FILLFACTOR = 80
            )
    , SomeData VARCHAR(600)
    , CreatedDate DATETIME
        CONSTRAINT DF_quid_insert_CreatedDate
        DEFAULT (GETDATE())
) ON [PRIMARY];

/* insert 100,000 rows into the test table */
INSERT INTO dbo.guid_insert (PK, SomeData)
SELECT NEWID(), REPLICATE('.', 600)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(num);

/* show the fragmentation details */
SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , IndexName = QUOTENAME(i.name)
    , [FillFactor] = 'FILLFACTOR = ' + CONVERT(VARCHAR(50), CASE WHEN i.fill_factor = 0 THEN '100' ELSE i.fill_factor END)
    , CompressOption = 'DATA_COMPRESSION = ' + p.data_compression_desc
    , NumberOfFragments = ips.fragment_count
    , NumberOfPages = ips.page_count
    , AvgFragmentSizeInPages = ips.avg_fragment_size_in_pages
    , AvgFragmentationInPercent = ips.avg_fragmentation_in_percent
    , IndexType = i.type_desc
    , IsUnique = i.is_unique
    , IsPrimary = i.is_primary_key
    , IsPartitioned = CASE WHEN ps.data_space_id IS NULL THEN 0 ELSE 1 END
    , IsClustered = CASE WHEN i.type = 1 THEN 1 ELSE 0 END
FROM sys.indexes i WITH (NOLOCK)
    INNER JOIN sys.objects o WITH (NOLOCK) ON i.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
    INNER JOIN sys.data_spaces ds WITH (NOLOCK) ON i.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p WITH (NOLOCK) ON o.object_id = p.object_id
                                    AND i.index_id = p.index_id
    LEFT JOIN sys.partition_schemes ps WITH (NOLOCK) ON ds.data_space_id = ps.data_space_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), o.object_id, i.index_id, p.partition_number, 'DETAILED') ips
WHERE o.is_ms_shipped = 0
    AND NOT (
        o.type = 'TF' -- table valued function
        OR o.type = 'TT' -- table type
        OR o.type = 'SO' -- sequence object
        )
    AND i.index_id > 0
    AND i.is_disabled = 0
    AND i.is_hypothetical = 0
    AND (
        ds.type = 'FG' -- filegroup
        OR ds.type = 'PS' -- partition stream
        )
    AND i.type_desc IN
        ( --we only support rebuilding/reorganizing these index types:
            'CLUSTERED'
            , 'NONCLUSTERED'
            , 'XML'
        )
    AND ips.index_level = 0 -- leaf-levels only
    AND ips.fragment_count > 1
ORDER BY ips.fragment_count * ips.avg_fragmentation_in_percent DESC;

The fragmentation query results:

/* rebuild the index to remove fragmentation */
ALTER INDEX PK_guid_insert ON dbo.guid_insert REBUILD;

/* drop the primary key, since we can't modify the PK column when it is a primary key */
ALTER TABLE dbo.guid_insert
DROP CONSTRAINT PK_guid_insert;

/* convert the VARCHAR PK column into a UNIQUEIDENTIFIER column */
ALTER TABLE dbo.guid_insert
ALTER COLUMN PK UNIQUEIDENTIFIER NOT NULL;

/* recreate the primary key */
ALTER TABLE dbo.guid_insert
ADD CONSTRAINT PK_guid_insert
PRIMARY KEY CLUSTERED (PK);

/* add a DEFAULT constraint to create sequential keys */
ALTER TABLE dbo.guid_insert
ADD CONSTRAINT DF_guid_insert_PK
DEFAULT (NEWSEQUENTIALID())
FOR PK;

/* delete 10,000 random rows */
;WITH src AS
(
SELECT rn = ROW_NUMBER() OVER (ORDER BY NEWID()), *
FROM dbo.guid_insert
)
DELETE
FROM src
WHERE rn < 10000;

/* insert 100,000 more rows */
INSERT INTO dbo.guid_insert (SomeData)
SELECT REPLICATE('.', 600)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(num);

/* show the fragmentation details */
SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , IndexName = QUOTENAME(i.name)
    , [FillFactor] = 'FILLFACTOR = ' + CONVERT(VARCHAR(50), CASE WHEN i.fill_factor = 0 THEN '100' ELSE i.fill_factor END)
    , CompressOption = 'DATA_COMPRESSION = ' + p.data_compression_desc
    , NumberOfFragments = ips.fragment_count
    , NumberOfPages = ips.page_count
    , AvgFragmentSizeInPages = ips.avg_fragment_size_in_pages
    , AvgFragmentationInPercent = ips.avg_fragmentation_in_percent
    , IndexType = i.type_desc
    , IsUnique = i.is_unique
    , IsPrimary = i.is_primary_key
    , IsPartitioned = CASE WHEN ps.data_space_id IS NULL THEN 0 ELSE 1 END
    , IsClustered = CASE WHEN i.type = 1 THEN 1 ELSE 0 END
FROM sys.indexes i WITH (NOLOCK)
    INNER JOIN sys.objects o WITH (NOLOCK) ON i.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
    INNER JOIN sys.data_spaces ds WITH (NOLOCK) ON i.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p WITH (NOLOCK) ON o.object_id = p.object_id
                                    AND i.index_id = p.index_id
    LEFT JOIN sys.partition_schemes ps WITH (NOLOCK) ON ds.data_space_id = ps.data_space_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), o.object_id, i.index_id, p.partition_number, 'DETAILED') ips
WHERE o.is_ms_shipped = 0
    AND NOT (
        o.type = 'TF' -- table valued function
        OR o.type = 'TT' -- table type
        OR o.type = 'SO' -- sequence object
        )
    AND i.index_id > 0
    AND i.is_disabled = 0
    AND i.is_hypothetical = 0
    AND (
        ds.type = 'FG' -- filegroup
        OR ds.type = 'PS' -- partition stream
        )
    AND i.type_desc IN
        ( --we only support rebuilding/reorganizing these index types:
            'CLUSTERED'
            , 'NONCLUSTERED'
            , 'XML'
        )
    AND ips.index_level = 0 -- leaf-levels only
    AND ips.fragment_count > 1
ORDER BY ips.fragment_count * ips.avg_fragmentation_in_percent DESC; --start with the most fragmented indexes first

The fragmentation details after the conversion, with deleting and inserting rows:

As you can see, after the conversion, and deleting 10,000 rows, then inserting 100,000 rows, index fragmentation for the primary key index (the table itself) is just 0.41%; hardly a concern.

If you need further help with this problem (I realize your question is a year old), you need to show the DDL for the table, its indexes, and the code you used for the conversion test.


Query Governor Cost Limit relationship with Time

Based on my notes from  SQL Server internals (by Kalen Delaney).... below is my understanding

Query Governor Cost limit is the max no. of seconds that a query can run.

So, essentially :

a non-zero or a negative value will disallow execution of any query that has an estimated cost exceeding that value.
Zero which is default - turns off the query governor and all the queries are allowed to run without any time limit.

  Note that the value set for seconds isn’t clock-based. It corresponds to seconds on a specific hardware configuration used during product development, and the actual limit might be higher or lower on your machine.

Also, it should be noted that changing the Query Governor Cost limit will flush the entire plan cache resulting in generating a new plan afterwards.


What does "Drop Connections" and "Update Statistics" in the "Detach Database" dialog do?

Drop Connections will force the database into Single User mode before detaching the database, thereby forcing all users to disconnect.  If you don't check-mark that option, and users are connected to the database, the database will not be detached.

Update Statistics will refresh any out of date statistics before detaching the database.  If you leave this option un-checked, statistics objects will remain as they were at the time you detached the database.

Microsoft has an excellent article with more details on MSDN.


How to identify the origin of a invalid connection with sql1060

The APPID in the diagnostic log entry contains the IP address and port, albeit encoded in a weird manner (see note 11). The APPID is formatted address.port.timestamp. When the connection is local the "address" portion will be something like "*LOCAL" but obviously in this case it's remote.

The remote address and port are encoded in hexidecimal but the first character (of each) is munged so that "0" maps to "G", "1" to "H", etc. (there's probably some internal requirement for APPIDs to appear non-numeric). So in this case the remote address is:

GA381C80     -- original
GA 38 1C 80  -- split it up into the octets
0A 38 1C 80  -- de-munge the first char
10.56.28.128 -- convert to decimal

Same story for the port (O107 = 0x8107 = 33031). How the timestamp is decoded I can't recall (the page linked above claims it's the "unique identifier" portion but I could swear I remember it being a timestamp, or timestamp-based).


Query a list of objects in each database

You can use the following code to list all views against all databases.
It will populate everything inside of #Result. After that, you can use your list to do joins and check whatever status you want.
IF OBJECT_ID('tempdb..#Result') IS NOT NULL  DROP TABLE #Result;
CREATE TABLE #Result
([Database] VARCHAR(100), [schema_name] VARCHAR(100), [view_name] VARCHAR(100))

DECLARE @DB SYSNAME,@cmd NVARCHAR(4000)

SET NOCOUNT ON
SET @cmd = ''

SELECT TOP 1 @DB = D.name
FROM master..sysdatabases AS D
WHERE dbid > 4
    and DATABASEPROPERTYEX(D.name, 'status') = 'ONLINE'
ORDER BY D.name;

WHILE @@ROWCOUNT = 1
BEGIN
      SET @cmd = 'USE ' + @DB + ';
            INSERT INTO #Result
            SELECT  DISTINCT ''' + @DB + ''' AS CustomerNumber ,sh.name, o.name AS ObjectName
            FROM sys.objects o
            INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
            WHERE o.type = ''V''

    ';
      EXEC (@cmd)

      SELECT TOP 1 @DB = NAME
      FROM master..sysdatabases AS D
        WHERE dbid > 4
        AND DATABASEPROPERTYEX(D.name, 'status') = 'ONLINE'
            AND D.NAME > @DB
      ORDER BY D.name
END

SELECT * FROM #Result R


Before I tell you if you following best practice or no, I suggest you understand few terms which dictates what kind of backup plan is suitable for you. This will also enable you to decide if you are risking something or no.
Recovery models

There are three recovery models:
Full recovery model (the default and the most commonly used) All modifications in the database a fully logged The log will not clear
until a transaction log backup is performed
Bulk-logged recovery model Some modifications (like an index rebuild or a bulk load) can be minimally logged, which reduces the
amount of log records generated The log will not clear until a
transaction log backup is performed, just like the full recovery model
Simple recovery model Some modifications can be minimally logged, just like the bulk-logged recovery model The log will not clear until
a checkpoint occurs (usually automatically), and transaction log
backups are not possible

Backup types

There are three commonly used backup types in SQL Server:
Full database backup
Transaction log backup
Differential database backup

RTO stands for Recovery Time Objective.

In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how
quickly must the data be made accessible again. RTO is often talked
about in terms of the number of nines of desirable up time or
accessibility for the data/database/system. For example, 5-nines means
99.999% up-time, and if that’s being measured 24 hours a day, 365 days a year, then that translates into just over 5 minutes of allowable
downtime per year. That’s really hard to achieve. It’s easier to meet
4-nines (52.5 minutes per year of downtime) or 3-nines (8.75 hours per
year of downtime).
When discussing the desired up time, you have to decide whether it’s
being measured 24×365, or 9am-5pm during weekdays, or some other time
period. You also need to decide whether the measured downtime includes
scheduled downtime for maintenance/patching or not, as it’s much
easier to meet a high number of nines if scheduled maintenance is
permitted.

RPO stands for Recovery Point Objective.

In simple terms, you can think of RPO as a measure of how much data or work it’s
acceptable to lose. It’s relatively easy to achieve very minimal or
even zero data/work loss using backups, but depending on the amount of
damage the database suffered when the disaster hit, recovering might
take a lot of time. For instance, if an entire database is destroyed,
depending on the architecture of the database and the backups that
exist, it may take a significant amount of time to recover the
database up to the point of the disaster. Most RPOs are defined as the
the amount of time for which work may be lost. For example, an RPO
might be that the database should be recoverable to a point within
thirty minutes of the disaster occurring, which means that up to 30
minutes of work may be lost.

Once you understand the above concept then you need to make a backup strategy. Make sure your business counter-part is in agreement with your strategy, especially about how much data you can lose. Read the article I listed in reference about planning a recovery strategy.
Reference:

The Accidental DBA (Day 7 of 30): Backups: Recovery Models and Backup Types by Paul Randal
Database Maintenance Best Practices Part III – Transaction Log Maintenance by Kimberly Tripp
The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy by Paul Randal
The Accidental DBA (Day 6 of 30): Backups: Understanding RTO and RPO by Paul Randal


Return results of two MySQL query in only one

First, clean up the GROUP BY:

SELECT  TIMESTAMP(sent_date) AS timestamp, COUNT(contacts.id) AS stopped
    ...
    GROUP BY  YEAR(sent_date), MONTH(sent_date)
    ORDER BY  sent_date DESC

The value of timestamp will be some random value.  Ditto for sorting on sent_date, which is not available after the GROUP BY.  Instead, consider:

SELECT DATE_FORMAT(sent_date, "%Y-%m") AS timestamp, COUNT(contacts.id) AS stopped
    ...
    GROUP BY 1
    ORDER BY 1

(Note:  "1" in these contexts refers to the 1st column of the SELECT.)

Try that; you will see that it is OK.

Then, to fix the original question:

SELECT ...
    FROM ( SELECT ... ((the first query, cleaned up)) ) a
    JOIN ( SELECT ... ((the second query, cleaned up)) ) b ON a.? = b.?
    ORDER BY ?

I'm not sure what to JOIN on; you'll still need an ORDER BY on the outside.


Making Production database as Publisher and Distributor for Snasphot Replication

Since your database is 100GB, you can use backup and restore method with compression (make sure Instant file Initialization is enabled).

Another approach will be to use logshipping with delaying the restoring of log files on the standby server and having the database in standby read mode ( WITH STANDBY).

The way you do is -

take your regular log backups on your primary (in your case every 15 mins)
keep the copy job as is to copy log files to network share.
Choose the option to delay the restoring of logs as per your threshold.
To speed up restoring the logs - change the setting of logshipping to norecovery
once done restoring the logs, change it back to standby.

If you want to automate the entire process, use dbatools - Log Shipping functions/cmdlets


Performance of using a non-clustered index as primary key

Here is what I have seen based on several years of working with GUIDs as clustered, and nonclustered primary keys...there is no one correct answer here; the big thing really comes down to the access methods that you are going to use to get to the data, and the volume of data that you are going to return.

Yes, you will have the fragmentation problem with GUID clustered indexes that will have to be managed, however, if your access method uses that GUID as the query predicate, and you return multiple columns from that table, you may be better off suffering the fragmentation hit in order to improve the performance and lessen the impact of reading that data back out.

Here's a very basic example that show that there is less work required to get the data out when it's the clustered index, and the predicate used to get at the data:

/* NonClustered PK */
CREATE TABLE #T1
    (
      C1 INT NOT NULL ,
      C2 UNIQUEIDENTIFIER NOT NULL ,
      C3 VARCHAR(100) NULL ,
      C4 VARCHAR(20) NULL ,
      CONSTRAINT PK_T1 PRIMARY KEY NONCLUSTERED ( C2 )
    );
CREATE CLUSTERED INDEX T1_C1 ON #T1 (C1);

/* Clustered PK */
CREATE TABLE #T2
    (
      C1 INT NOT NULL ,
      C2 UNIQUEIDENTIFIER NOT NULL ,
      C3 VARCHAR(100) NULL ,
      C4 VARCHAR(20) NULL ,
      CONSTRAINT PK_T2 PRIMARY KEY CLUSTERED ( C2 )
    );

/* Insert 10 rows into each table */
INSERT INTO #T1
        ( C1, C2, C3, C4 )
VALUES  ( 0, '58BBB460-1AFA-4177-BA78-798DA19E0C97', 'some text', 'C4 text')
, ( 1, '17E8163B-BE21-44C7-A7B7-4997265A139D', 'some text', 'C4 text')
, ( 2, '16AACAB8-CD77-4A8D-BE87-9E433CD157EC', 'some text', 'C4 text')
, ( 3, '787D0714-F92A-4963-89E5-3F5DBF518EA7', 'some text', 'C4 text')
, ( 4, '5C720476-D4BE-4047-9F73-DBB1B6B75208', 'some text', 'C4 text')
, ( 5, 'D70F81C5-8AFF-4ABE-BA64-8F5C1A1C6A90', 'some text', 'C4 text')
, ( 6, '1473E5DC-6F3E-4164-988C-E36EE7C695BE', 'some text', 'C4 text')
, ( 7, '648AEA46-4B45-41F9-AA9B-7129062391B4', 'some text', 'C4 text')
, ( 8, '49497ECB-774D-482D-8230-218E97FB2EB4', 'some text', 'C4 text')
, ( 9, 'B90504FA-CEBA-4383-A61F-82F33DAB7A0E', 'some text', 'C4 text');

INSERT INTO #T2
        ( C1, C2, C3, C4 )
SELECT C1 ,
       C2 ,
       C3 ,
       C4 FROM #T1;

/* Index seek on T1 - no lookup as we include the clustering key */
SELECT C1, C2 FROM #T1 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Index seek AND key lookup on T1 because there are columns not contained in the PK */
SELECT C1, C2, C3 FROM #T1 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Clustered index seek on T2 as the predicate is the PK and clustered index */
SELECT C1, C2 FROM #T2 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

/* Stil a clustered index seek on T2 as the predicate is the PK and clustered index */
SELECT C1, C2, C3 FROM #T2 WHERE C2 = '648AEA46-4B45-41F9-AA9B-7129062391B4'

The things that you need to take into account, and think about are going to be:

Storage performance
Performance expectations around reads and writes
Access methods (columns returned, and columns used for the predicates)
Space available (bear in mind the clustering key is included in all nonclustered indexes, meaning that you will use more space, and waste more in the cache

Sadly there is no single correct answer for this, you can only get the appropriate expectations, and performing extensive testing to see if those expectations can be met with whatever data model you design, and if not, look to make adjustments to improve them.

  Are there any significant disadvantages to using the approach I suggested?

It is very difficult to say. The only real disadvantage is that, if most of your queries come in via the nonclustered PK, and you are very read heavy you are going to see a great deal of increased IO as you will be performing key lookups on every call.

If your storage can withstand that, then it's great. And on the upside, you don't have the fragmentation problem which can lead to index maintenance hell over time (and can mean that you can't rebuild these things if you are running AGs and don't have maintenance periods).


Don't use '' to indicate missing lat/lng -- use NULL instead.  Meanwhile keep lat/lng in, say FLOAT.  See: http://mysql.rjweb.org/doc.php/latlng#representation_choices

If you are using MySQL 8.0 or Mariadb 10, use LEAD(1) and OVER ( PARTITION BY chargeNumber ORDER BY tag_id ) to get the calculate drive distances and the first lat-lng per charge.  In the absence of that windowing function, it would be better to do the arithmetic in your application.

Use LEAD(1) to find the distance for each distance.
SUM() with GROUP BY chargeNumber to get the total distance for each charge.

Be aware that the drive-distance falsely assumes a straight line.


SQL Server 2014 In Memory Table Creation Error

i solved the problem with this link: http://www.yigitaktan.com/archives/492

The problem's source is that: Sql Server instance's collation was Turkish_CI_AS. Memory-Optimized processes haven't supported my collation.

SELECT NAME AS [Collation]
    ,COLLATIONPROPERTY(NAME, 'codepage') AS [CodePage]
FROM sys.fn_helpcollations()
WHERE COLLATIONPROPERTY(NAME, 'codepage') = 1252

This query lists supported collations.


How to provide a SQL Server database with a C# application

I suppose that you have a setup kit to ship your application.

I also suppose that you want to ship an empty database with your application, with at most a handful of rows for built-in values in some tables.

The correct way to handle this is to include your database setup in your setup kit, so that it creates the database and all required objects (tables, views, procedures, users, logins, ...).

One possible way to do this is to include a .dacpac file in your setup kit and invoke sqlpackage to deploy it to a target sql server instance. The .dacpac file should be obtained by building a database project in visual studio (SSDT).

If you don't have a database project or you don't have a setup kit, you should probably rethink your deployment strategy completely.


Can't uninstall Microsoft SQL Server 2016 RC3

Thanks to Aaron Bertrand, I was able to resolve the issue following the manual steps outlined in his blog:

Fun with software : uninstalling SQL Server 2008 R2 Evaluation Edition

...in spite of the title, it's not just for 2008 R2 or Evaluation Edition.

In case the link ever stops working, the essential steps are:

Locate SQL Server related GUIDs under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\

Also look for GUIDs in folders under Uninstall called e.g. "Microsoft SQL Server 2008 R2"
Run for each item:

msiexec /x "{guid}"


SQL Server 2017 and TLS - client requirements

no,
TrustServerCertificate=true

means more or less
ignore certification source verification
It's used in env like your when a self-signed certificate is used for encryption.
It's better to use the certificate produced by a CA and distributed by your AD GPO.
In that case you don't need to use 'TrustServerCertificate=true'; but be sure to use in the connection string the FQDN of the server that is inside the certificate.


How to convert database mysql4 to mysql5

You can do the following on a Linux system:

sed 's/TYPE=/ENGINE=/g' booster.dump > booster_sane.dump and afterwards import the booster_sane.dump. If you don´t care about your original file you can also make the changes inplace before importing: sed -i 's/TYPE=/ENGINE=/g' (or specify a suffix to -i option to make a backup on the fly).

The reason for your error is explained in the comments to your question.


Updating Always On Availability Group server connected to Windows SQL Cluster

It will not react any different, it is the same way as you upgrade a cluster server (if you did that before). To minimize the downtime of your server make sure to set it up a manual failover. Then start with patching your secondary node after you finish that, do database consistency check, on your secondary and then failover the primary node to your secondary server (the updated server) and do the same exact process on the then primary, now secondary node. Once you finish the upgrade make sure to check the health status of the availability group and if fix if any suspended or not synchronized database on from the list of your availability database.


how to avoid duplicate record with certain conditon

A possible solution based on set operations. From my understanding you have to display all rows that contain a 'ticket' in the status fields and all rows with an 'import' in the status field where there is not a similar row with a 'ticket' in the status field.

select id,name,testfield,status
from mytable
where status='ticket'
union
select id,name,testfield,status
from mytable where
(id,name) in
((select id,name
from mytable
where status='import'
)
minus
(select id,name
from mytable
where status='ticket'
)) ;

But sqlfiddle does not like it.
It says

  Oops! Something went wrong. Try it again [...]


How to join two table without getting duplicate from right table in Oracle

Doing it on client side (sqlplus):
SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6          5
    123456          2          4          5

SQL> break on value
SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6
    123456          2          4

In SQL:
SQL> clear breaks
breaks cleared
SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6          5
    123456          2          4          5

SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, case when row_number() over (partition by t1.id order by t1.id) = 1 then t2.value end value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6
    123456          2          4


Dynamically update JSONB rows - POSTGRESQL

UPDATE table1
SET    jsonfield = (
   SELECT jsonb_set(jsonfield
                  , '{parent,0,steps}'::text[] || (idx - 1)::text
                  ,  val->'childStep'->0 || (val - text 'childStep')
                   )
   FROM   jsonb_array_elements(jsonfield#>'{parent,0,steps}') WITH ORDINALITY a(val, idx)
   WHERE  val->>'name' = 'CONFIG2'
   )
WHERE  jsonfield @> '{"parent":[{"steps":[{"name":"CONFIG2"}]}]}';
-- WHERE  jsonfield @? '$.parent[0].steps[*].name ? (@ == "CONFIG2")';  -- equivalent alternative

db<>fiddle here
I compute the new jsonb value in a correlated subquery.
WITH ORDINALITY produces the array index while unnesting the JSON array with jsonb_array_elements(). The later idx - 1 adjusts the off-by-1 error from Postgres arrays being 1-based and JSON arrays being 0-based. More about WITH ORDINALITY:

How to preserve the original order of elements in an unnested array?

I added an outer WHERE condition so that only applicable rows are processed to begin with. Avoid updating rows with unchanged values as this adds costs without benefits.
You can either use the contains operator @> or the new operator @? in Postgres 12 or later SQL/JSON path language. Either can use an index. See:

Update all values for given key nested in JSON array of objects

Index by subkey inside subkey inside array in jsonb


Using postgres to select a filtered array

Not sure if this is the most efficient way to do it, but this works.

You do have to unnest all elements from keyword_array to be able to filter the unwanted out:

with whitelist (kw) as (
    values ('a'),('b'),('c'),('f'),('h')
), raw_with_keyword_array (id, keyword_array) as (
  values
      ('1234', array['a','c','e','h','q']),
      ('1235', array['a','d','e','f'])
)
select id,
       array(select kw
             from unnest(ra.keyword_array) as t(kw)
             except
             select kw
             from whitelist)
from raw_with_keyword_array ra;

Instead of except you could also use a where not exists:

....
select id,
       array(select kw
             from unnest(ra.keyword_array) as t(kw)
             where not exists (select *
                               from whitelist wl
                               where wl.kw = t.kw)
             )
from raw_with_keyword_array ra;

I don't think it makes a big difference though.

Online example


How to avoid a select query for holding a Sch-S lock

A Sch-S lock is a Schema stability lock.  It is taken to ensure that the structure of the table doesn't change.  This includes adding/removing columns, etc.  NOLOCK hints and isolation levels affect the locking and versioning of the data in the table, not the structure of the table itself.

Even an online index operation will need to briefly take Sch-S and Sch-M locks.  You cannot avoid this for indexing or schema modification queries.


how to run limit query within condition in mysql

From your question, let's assume your table is called TNAME

You should collect the ids that are valid

First create a table to hold IDs and a process status per ID

CREATE TABLE idlist
(
    id INT NOT NULL,
    processed INT NOT NULL DEFAULT 0,
    PRIMARY KEY (key),
    UNIQUE KEY processed (processed,key)
) ENGINE=MyISAM;

Next, preload load all new IDs

INSERT IGNORE INTO idlist (id) SELECT id FROM TNAME WHERE SETNO=1 AND flag='valid';

This will load ids that are valid. IDs that were already loaded are bypassed

Using this idlist, pickup the first 200,000 ids

CREATE TABLE idlist200K SELECT id FROM idlist WHERE processed=0 ORDER BY id LIMIT 200000;
ALTER TABLE idlist200K ADD PRIMARY KEY (id);

Process the 200K rows with an INNER JOIN with queries like this:

SELECT B.* FROM idlist200K A INNER JOIN TNAME B;

When done processing, mark the IDs as processed:

UPDATE idlist A INNER JOIN idlist200K B USING (id) SET A.processed = 1;
DROP TABLE idlist200K;

All you have to do from here is automate all these steps, except the initial table creation

INSERT IGNORE INTO idlist (id) SELECT id FROM TNAME WHERE SETNO=1 AND flag='valid';
CREATE TABLE idlist200K SELECT id FROM idlist WHERE processed=0 ORDER BY id LIMIT 200000;
ALTER TABLE idlist200K ADD PRIMARY KEY (id);
SELECT B.* FROM idlist200K A INNER JOIN TNAME B; -- Whatever processing
UPDATE idlist A INNER JOIN idlist200K B USING (id) SET A.processed = 1;
DROP TABLE idlist200K;

Give it a Try !!!


Try this

create table #person (
PersonID int,
FirstName varchar(5),
LastName varchar(5)
)

create table #incident (
IncidentID int,
PersonID int
)

insert into #person values (1,'a','b')
insert into #person values (2,'c','d')
insert into #person values (3,'e','f')
insert into #person values (4,'g','h')
insert into #person values (5,'i','j')

insert into #incident values (1,1)
insert into #incident values (1,2)
insert into #incident values (2,3)
insert into #incident values (2,4)
insert into #incident values (3,5)

 select t.IncidentID,
   stuff(
    (select ';' + p.firstname + p.lastname
      from #incident i
      join #person p
        on i.personid = p.personid
      where i.incidentid = t.incidentid
     for xml path('')),
     1,
     1,
     ''
   )
 from #incident t
group by t.IncidentID

Documentation on stuff() command


Before spending much more time on it, upgrade to 9.2. New versions aren't just released for kicks, they tend to contain significant performance, tuning, and scalability improvements. Quite a lot of work has been done on read- and write-scaling recently.

Lower max_connections. Even unused entries in max_connections have a cost. On your machine you'll probably want to max out somewhere around 300. If you need more concurrent connections, put a pgbouncer in front of PostgreSQL.

Watch out for the known issues with NUMA and transparent huge pages in the Linux kernel; there are some pretty pathalogical interactions with PostgreSQL. I don't have details immediately to hand, but it's been discussed several times on the mailing lists. Some of the people on the team I work with do a lot of work with big NUMA systems and find Linux's handling of huge pages and page migration to be areas that require tuning for acceptable Pg performance.

On 9.0 your shared_buffers might be a bit big; you may actually see better results by lowering it. You'll need to play around with this as it varies a bit based on kernel and hardware.

Finally, make sure you run pgbench from a separate host, or it'll create contention for the same resources that it's trying to benchmark.

Set effective_cache_size to around 1TB. It should be higher, but IIRC there are some limitations in the code for handling multi-terabyte quantities of RAM, at least in 9.

If that RAID 50 is comprised of spinning rust it's going to limit performance unless your DB fits in RAM - which it probably does at  scale factor of 300, but might not on your real DB.


My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is stored in some physically ordered manner...

All columns are sorted in the same order. I say this to distinguish from a possible reading of your statement that each column is sorted in the best way for that column, which isn't true.
Each column is encoded and compressed individually in a segment within each row group. The order of operations is:

Encode values in all columns (e.g. value offset, or using a dictionary).
Determine the single 'optimal' row ordering.
Compress each column (e.g. RLE, bit-pack).

The common sort order chosen may be great for some segments, but not others from a compression point of view (primarily run length encoding). SQL Server uses Vertipaq technology to choose a sort order that gives a good overall compression result.

...implying that each column already has what amounts to a clustered index on it

This isn't the right mental picture because column store 'indexes' do not support seeks, only b-trees do. SQL Server can locate an individual row in a column store for lookup purposes (as in the linked Q & A) but that isn't the same as supporting key seeks in general.
Demo of a lookup on a clustered columnstore index.

The distinction is between finding row #n in row group #m versus finding rows with a given value 'x' for a particular column. A column store index can do the former, but not the latter.
Additionally, b-tree indexes can return ordered data; column store indexes cannot (due to encoding) -- except for the trivial case of a lookup where at most one row will be returned.

Does it make sense to add indexes to a clustered columnstore index table?

Additional b-tree indexes make sense when we need to locate a single value, or a small range of values. The alternative would be to scan the column store index (albeit with possible row group elimination if the stars align correctly - as well as partition elimination if the table is partitioned).
More generally, b-tree indexes support OLTP-type queries; column store is for analytics. Providing both on the same table enables a hybrid approach (HTAP - Hybrid Transactional/Analytic Processing).
From the Microsoft Research Paper Columnstore and B+ tree –
Are Hybrid Physical Designs Important? (PDF):

B+ trees outshine columnstores when query
predicates are selective even when all data is memory resident; and
the trade-off shifts further in favor of B+ trees when data is not
memory resident. Likewise, B+ trees can be a better option for providing data in sorted order when server memory is constrained. On
the other hand, columnstores are often an order of magnitude faster
for large scans whether or not the data is memory resident. For
updates, B+ trees are significantly cheaper. Secondary columnstores
incur much lower update cost compared to primary columnstore
indexes, but are still much slower than B+ trees. This empirical
study indicates that for certain workloads, hybrid physical designs
can provide significant performance gains.


Want to delete then insert with single statement using a CTE in Postgres

Without a UNIQUE constraint standing in the way, your original query works just fine. See below.
I would throw in a separate CTE to provide all input values once:
WITH input(a, b, c) AS (VALUES ('abc', 2, 'new value'))  -- provide values once
   , del AS (
   DELETE FROM foo AS f
   USING  input i
   WHERE  f.a = i.a
   AND    f.b = i.b
   )
INSERT INTO foo (a, b, c)
TABLE input;

The DELETE cannot delete rows from the INSERT in the same statement, as both see the same snapshot of underlying tables. Meaning, the DELETE cannot see the rows entered by the INSERT in the other CTE. Both are executed at the same time, virtually. Related:

Why can't rows inserted in a CTE be updated in the same statement?

That's also the reason why this cannot work with a UNIQUE index on (a.b). Uniqueness is enforced at all times. The INSERT still sees rows being deleted in the other CTE. The obvious alternative would be an UPSERT. But that cannot be the case, since you mentioned that there is no PK and the DELETE can delete 0-n rows.
As a_horse commented: would work with a deferrable constraint, though. See:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

But deferrable constraints are considerably more expensive and don't work with FK constraints, nor as arbiter in UPSERT statements ...
Note that a free-standing VALUES expression may require explicit type casts. See:

Casting NULL type when updating multiple rows

Related:

How to use RETURNING with ON CONFLICT in PostgreSQL?

That said, I don't see how this is superior to a DELETE and a separate INSERT in a single transaction - which also works with UNIQUE constraints. You commented:

It's a lot easier to use db parameters with a single statement and our db library.

If the statement is used a lot, consider a function:
CREATE OR REPLACE FUNCTION f_foo_delins(_a text, _b int, _c text)  -- actual types
  RETURNS void LANGUAGE sql AS
$func$
   DELETE FROM foo
   WHERE  a = _a
   AND    b = _b;

   INSERT INTO foo ( a,  b,  c)
   VALUES          (_a, _b, _c);
$func$;

Then the call is simple:
SELECT f_foo_delins('abc', 3, 'new value');

If you only need it for some sessions, a temporary function might be an option. See:

How to create a temporary function in PostgreSQL?

Or a prepared statement with the above CTE:
PREPARE foo_delins(text, int, text) AS
WITH del AS (
   DELETE FROM foo
   WHERE  a = $1
   AND    b = $2
  )
INSERT INTO foo ( a,  b,  c)
VALUES          ($1, $2, $3);

Call:
EXECUTE foo_delins('abc', 4, 'new value');

Most languages have their own implementation of prepared statements using libpq ...
Prepared statement and function know their input types. Explicit type casts are not required (except special cases).
Related:

What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?


Error Code : 1248 Every derived table must have its own alias in MySQL

You need to mention an alias for all your inline views / derived tables in MySQL, even when they are nested. In your code you are using a derived table inside another derived table but you seem to have forgotten to mention an alias for your inner derived table. Put an alias before the 4th line from the bottom which is GROUP BY REF_ID. Put an alias before this group by clause and you will be fine.


PostgreSQL PSQL client-side commands and new lines (\COPY)

psql "meta-commands"

Commands in psql that start with back-slash (\) are called "meta-commands" and they don't follow the usual semicolon rules but are instead terminated by a newline. A small excerpt from man psql

  Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

Much more said on this in the man pages. Yes, it's weird and annoying. It's because historically psql commands were a sort of metacommand you'd want to run without interrupting any query you were building, like \d to see columns of a table. But for pseudo-statements like \copy it gets confusing.

What's happening here

In your case, what's happening is that psql runs the first line, then sends the second to the postgres server, which recognises WITH as a valid CTE statement token then gets confused by FORMAT.

What you want is,

\copy table_name from '/path/to/csv/file.csv' with format csv, header true


Replication monitoring refresher for distribution job affecting performance

Generally, replication monitor doesn't do a very good job.
Have a look at this script by Robert Davis that measures latency:

http://www.sqlsoldier.com/wp/sqlserver/measuringtransactionalreplicationlatencywithouttracertokens


Name already been used by an existing constraint after renaming the table and creating a new one

After renaming the table, rename the constraint and the index as well:

alter table cba rename constraint abc to cba;
alter index abc rename to cba;


Unable to update MySQL table from linked server on SQL Server 2014

Configure the MSDASQL provider

I believe the following should fix it. Turn on the following for SQL Servers MSDASQL provider:

AllowInProcess
IndexAsAccessPath
LevelZeroOnly
DynamicParameters
NestedQueries

Other options that seemed to work was creating a primary key:

https://www.sqlservercentral.com/Forums/657910/OPENQUERY-UPDATE-problem-with-MySQL-as-linked-server


As I said in the question a lot of resources dealt with this issue but not when it pertained to a view. The solution I found was to

1- create a 2nd DB on my source server.
2- create a view on the 2nd db using the linked server to the remote DB
3- alter the view on the 1st DB to use the view on the 2nd DB
4- alter the 2nd DB to use ANSI WARNINGS ON at a DB level


Have you updated statistics lately? Estimated Number Of Rows is 1 while Actual Number Of Rows is 10,259,536... Dated stats can lead to bad query plans (insufficient memory allocated for joins and sorts, bad join algorithm choices...).

When Auto Update Statistics is set to true on the database, SQL Server will automatically update statistics based on a percentage (around 20%) of changed rows in the statistic. This works well when tables are small but stats can get dated on bigger tables. A statistic in a table with 10000 records will automatically be updated when around 2000 of it's records get changed (and life is good). For a table with 10000000 records however, the auto update will still occur at 20% or 2000000 changed records (resulting in some very bad plans and long gaps between stat updates).

With the release of SQL Server 2008 R2 SP1 (and all future versions) the threshold can be set to a dynamic value by using trace flag 2371. This addresses the big table problem. You can read more about it here: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx


Insert performance increases under load: Why?

One possible reason is that four concurrent processes generate a more favourable pattern of log flushes - typically meaning that each log flush writes more data than is the case with a single executing process.

To determine if transaction log throughput/flush size is a factor, monitor:

sys.dm_os_wait_stats for WRITELOG and LOGBUFFER waits
sys.dm_io_pending_io_requests for IO performance
Performance Monitor counters (or sys.dm_os_performance_counters) for:

Log Bytes Flushed/sec
Log Flushes/sec
Log Flush Wait Time

Look for internal limits being reached. In SQL Server 2008 R2, there can be a maximum of 32 outstanding (asynchronous) log flush I/Os per database on 64-bit versions (only 8 on 32-bit). There is also a total size limit on the outstanding IOs of 3840KB.

More information and further reading:

Transaction Log Monitoring
Trimming Transaction Log Fat
Diagnosing Transaction Log Performance Issues and Limits of the Log Manager
Optimizing Transaction Log Throughout


with MySQL, are multiple single column indexes faster than one multi-column index?

SHORT ANSWER

Only as a last resort

LONG ANSWER
Having multiple indexes can be a rather arduous adventure for MySQL Query Optimizer.
I have written about this before

Sep 18, 2012 : How are multiple indexes used in a query by MySQL?
Apr 19, 2014 : Optimizing indexes (Under the Heading ANSWER TO QUESTION #2)

In essence, MySQL will do lookups along multiple indexes and perform an index merge of the results. This would only be done as a last resort if there are no compound indexes that are usable for your query's WHERE, GROUP BY, and ORDER BY clauses.
The order of the columns in the multiple-column index also has a bearing. If you have the following:

Table with indexes (a),(b),(c)
Same table with index (a,b,c)

The three-column index would be better if the WHERE, GROUP BY, and ORDER BY clauses harness the indexed columns in the order inside the index (a,b,c). Anything different combination could possibly result in an index range scan of (a,b,c). Next best choice could be full index scan of (a,b,c) . If these are not possible, the last resort becomes the merge of searching (a),(b),(c) separately.


How postgresql full text search disable file parser?

You would have to make your own parser.  This is not easy to do.  You would also have to decide what features of the parser to keep and what to throw away.  It isn't clear to me that the file token type is the only one that will give you problems.  There is a test module 'test_parser' which could serve as an example of creating your own, but it much too simple, while the default one is much too complex.
create extension test_parser ;
CREATE TEXT SEARCH CONFIGURATION testcfg ( PARSER = testparser );

A more pragmatic solution might be to just push the text through replace(col_name,'.',' ') before passing it to to_tsvector.  If you were using plainto_tsquery, etc., you would also want to pass the query through that, but since you are just using to_tsquery, you would not need to do that.


Run a PowerShell script on a different server - from inside a SQL Server job

If you are within a domain environment you can setup a proxy account that has permissions on SQLBILON1. However, using remote PowerShell is a bit of overkill and adds an unneeded layer to troubleshoot. If you do not have remote PowerShell enabled on both servers and firewall access between the servers configured properly it will have issues.

I would simply use UNC paths to the server. Whether you use the admin share like \\SQLBILON1\H$\SQLBackups or create share directly to \\SQLBILON1\SQLBackups. Unless you want to give the SQL Agent service on MYSQLSERVER1 permissions to that backup directory you would need to create a proxy account that has the appropriate permissions.

Your script will be much more simply if you just go this route:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
#
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------

#-- set the Path where the backup files (.abf) are located
#
$path = '\\SQLBILON1\H$\SQLBackups' # OR \\SQLBILON1\SQLBackups

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item


Retrieve the triggering SQL statement as is

Have a look at MS-Docs about DBCC INPUTBUFFER

  Displays the last statement sent from a client to an instance of Microsoft SQL Server.

Add WITH NO_INFOMSGS just to suppress all informational messages.

Permissions

On SQL Server requires one of the following:

User must be a member of the sysadmin fixed server role.
User must have VIEW SERVER STATE permission.
session_id must be the same as the session ID on which the command is being run.

CREATE TABLE Test (Id int PRIMARY KEY, Val varchar(10));
CREATE TABLE Audit (Id int IDENTITY PRIMARY KEY, EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000));
GO

CREATE TRIGGER AuditTest
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO Audit EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS');
END
GO

INSERT INTO Test VALUES(1, 'John');
GO

2 rows affected

INSERT INTO Test VALUES(2, 'Anna');
GO

2 rows affected

UPDATE Test SET Val='Peter' WHERE Id=1;
GO

2 rows affected

DELETE FROM Test WHERE Id=2;
GO

2 rows affected

SELECT * FROM Audit;
GO

Id | EventType      | Parameters | EventInfo
-: | :------------- | ---------: | :--------------------------------------
 1 | Language Event |          0 | INSERT INTO Test VALUES(1, 'John');
 2 | Language Event |          0 | INSERT INTO Test VALUES(2, 'Anna');
 3 | Language Event |          0 | UPDATE Test SET Val='Peter' WHERE Id=1;
 4 | Language Event |          0 | DELETE FROM Test WHERE Id=2;

As sepupic has pointed out on his comments if you are using an stored procedure, DBCC INPUTBUFFER returns the EXEC sentence instead of the SQL statement.

CREATE PROCEDURE dbo.InsertTest(@Id int, @Val varchar(10))
AS
BEGIN
    INSERT INTO Test VALUES (@Id, @Val);
END
GO

EXEC dbo.InsertTest @Id=5, @Val='Peper';
GO

2 rows affected

Id | EventType      | Parameters | EventInfo
-: | :------------- | ---------: | :---------------------------------------
 1 | Language Event |          0 | INSERT INTO Test VALUES(1, 'John');
 2 | Language Event |          0 | INSERT INTO Test VALUES(2, 'Anna');
 3 | Language Event |          0 | UPDATE Test SET Val='Peter' WHERE Id=1;
 4 | Language Event |          0 | DELETE FROM Test WHERE Id=2;
 5 | Language Event |          0 | EXEC dbo.InsertTest @Id=5, @Val='Peper';

dbfiddle here

Let me suggest you another approach, instead of store SQL commands, you can keep track of all changes of your data on this way:

1- Create a duplicate of your table and add some audit fields like, action, date, user, etc.

CREATE TABLE Test (Id int PRIMARY KEY, Val varchar(10));
CREATE TABLE Audit (Action varchar(10), CreatedAt datetime, [User] nvarchar(128), Id int, Val varchar(10));
GO

2- Lets the trigger to store all new changes.

CREATE TRIGGER AuditTest
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Action varchar(10);
    IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @Action = 'UPDATE'
    ELSE IF EXISTS(SELECT 1 FROM inserted)
        SET @Action = 'INSERT'
    ELSE
        SET @Action = 'DELETE';

    IF @Action = 'UPDATE' OR @Action = 'INSERT'
    BEGIN
        INSERT INTO Audit
        SELECT @Action, GETDATE(), SUSER_NAME(), Id, Val
        FROM   inserted;
    END
    ELSE
    BEGIN
        INSERT INTO Audit
        SELECT @Action, GETDATE(), SUSER_NAME(), Id, Val
        FROM   deleted;
    END
END
GO

Now the final result will show something like this:

SELECT * FROM Audit;
GO

Action | CreatedAt           | User                                    | Id | Val
:----- | :------------------ | :-------------------------------------- | -: | :----
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  1 | John
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  2 | Anna
UPDATE | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  1 | Peter
DELETE | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  2 | Anna
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  5 | Peper

And it doesn't matter if you use a direct command, an stored procedure or you're calling it from an external application.

dbfiddle here


How to get the timezone from a PostgreSQL timestamp

The documentation on Postgres timestamps says:

  For timestamp with time zone, the internally stored value is always in
  UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
  Time, GMT). An input value that has an explicit time zone specified is
  converted to UTC using the appropriate offset for that time zone. If
  no time zone is stated in the input string, then it is assumed to be
  in the time zone indicated by the system's TimeZone parameter, and is
  converted to UTC using the offset for the timezone zone.

Contrary to what a reasonable person might expect when reading timestamp with time zone, this Postgres datatype doesn't actually store a timezone. Internally, the value is always converted to UTC.

The information about the input timezone is lost when saving the value to the database. As far as I know, there is no way to get the information you seek.


Sanitizing a form's database input while retaining basic HTML markup

There have been a few great discussions of this at Stack Overflow.  This one covers methods and processes, but don't try to create a catch-all function as a replacement for understanding your data flows.

For your particular problem, it looks like HTML Purifier might be a good fit.  That way, you could whitelist very specific HTML functionality (e.g., strong, ol, em) to prevent XSS.  In addition, you can parameterize your queries (check out Bobby Tables for PHP specifics) to prevent SQL injection.

I wouldn't think of these two as one process, though, because they're really useful at different points in your application lifecycle:  parameterizing your SQL queries is important when you're about to make a SQL call, but XSS protection is important when displaying the saved data in a Javascript-equipped browser (as opposed to saving text to the database).


Powershell SQL Script Execution in Selected Schemas Only

Hopefully I can help you answer this question if I understand the question correctly. You wish to run the same query against multiple schemas in a database. I assume each schema has identical tables. As suggested below by Shawn you will need to import the module SQLPS in Powershell to run the scripts below.

Assume the database with 3 schemas as follows

    CREATE DATABASE [aSchemaCheck]
    GO

    USE [aSchemacheck]
    GO
    CREATE SCHEMA [Aschema] AUTHORIZATION [db_owner]
    GO

    CREATE TABLE ASchema.Name
    (
        time time NOT NULL,
        Name char(10) NULL,
    )
    GO

    CREATE SCHEMA [BSchema] AUTHORIZATION [db_owner]
    GO

    CREATE TABLE BSchema.Name
    (
        time time NOT NULL,
        Name char(10) NULL,
    )
    GO

    CREATE SCHEMA [CSchema] AUTHORIZATION [db_owner]
    GO

    CREATE TABLE CSchema.Name
    (
        time time NOT NULL,
        Name char(10) NULL,
    )
    GO

What you can do is use Invoke-SQLCmd and create a $query string and replace the Schema in the query as follows

    $query = "INSERT INTO [$Schema].[Name]
               ([time]
               ,[Name])
         VALUES
               (CONVERT(time,GetDate())
               ,'AName')
    GO"

    Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $query

You can specify the schemas in the script and loop through them as follows by putting the above query inside a loop as follows

    $Schemas = 'ASchema','BSchema','CSchema'
    foreach($Schema in $Schemas)
    {
    }

If we you want to run the query against all user defined schemas you can do the following

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
    $db = $srv.Databases[$Database]
    $Schemas = $db.Schemas|Where-Object{$_.IsSystemObject -eq $false}|select Name -ExpandProperty Name

    foreach($Schema in $Schemas)
    {
    }

If you want to select the Schemas you can do so using Out-GridView -Passthru press CTRL and click to choose multiple

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
    $db = $srv.Databases[$Database]
    $Schemas = $db.Schemas|Where-Object{$_.IsSystemObject -eq $false}|Out-GridView -PassThru|select Name -ExpandProperty Name

    foreach($Schema in $Schemas)
    {
    $query = "INSERT INTO [$Schema].[Name]
               ([time]
               ,[Name])
         VALUES
               (CONVERT(time,GetDate())
               ,'AName')
    GO"

    Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $query
    }

if you wanted to use a .sql file against each schema you would use

Invoke-Sqlcmd -ServerInstance $Server -Database $Database -InputFile $SQLFile


Role-Schema Permission issue (Management studio)

Instead of relying on SSMS (its flakey sometimes - you might have to refresh, etc), always use T-SQL.

Below script will provide you comprehensive result of what permissions you have set (filter out as per your need)

/*
****I cant find the reference, but very descriptive script(I have it in my repository for years now - very useful for auditing).****

Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectName      : Name of the object that the user/role is assigned permissions on.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.
*/

--List all access provisioned to a sql user or windows user/group directly
SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]


Generate test data distrubution in Oracle DB


Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?

I should have tried a bit more first. Now that I run a few examples, I seems like yes, the above queries can all use an index-only scan. As soon as a column outside of the covering index is referred to, a regular access method is used (e.g. an index scan id a fitting index is in place).

It would be nice if the Postgres documentation would make this more clear (or I overlooked the information).


Performance: storing list of thousands of items in postgresql's JSON field vs having separate table for items?

You will need to measure the two ways against your performance requirements.

That said, in the world of RDBMS (which Postgresql is), generally it's better to keep tables rather than JSON/XML/Blobs etc.

The databases are optimised and designed for relational data and while it's true they have supporting features for JSON/XML etc, often these are not as mature, performant or generally as well thought out as the relational side.

Additionally there are more third party data analysis, reporting, ETL, ORM systems for relational than JSON.

Any or all of those statements may well become more or less true over time so you really need to decide for yourself as there's not generally a right way for all cases.

Me personally?  If it's data in a known, structured format and your DB and requirements tend towards relational then go relational and get rid of your JSON storage completely.


So I came to find out that my heroku instance wasn't in the same availability region as my rds instance.  Once I changed it over to east coast the RDS DB was slightly quicker then my heroku DB.

Incase anyone is stuck on how to create a rds instance in a different region through aws console, you need to click on region (i.e. Oregon) button in the top right corner and then create your instance.


Room should have PK of (hotel_id,room_number).
Weak entities have compound keys with the strong entity key/foreign key as the leading column(s).
There are people who disagree with this approach, but they are wrong.

How can i find the room belonging to specific hotel which has the different number of beds limiting the attributes in the domain of my problem

Something like:
select *
from rooms
where hotel_id  = 1001
  and beds = 3


One table or duplicate table with same properties but different dependencies

When you have a many to many relationship it's better to introduce a table which holds the relationship between the two tables which is much more normalized.


What is contained in the controlfile of oracle database?

Lets define the controlfile in oracle: it is a small binary file that records the physical structure of the database and its include:

1) The database name.

2) Names and locations of associated datafiles and redo log files.

3) The timestamp of the database creation

4) The current log sequence number

5) Checkpoint information


SQL Server: Accurate Row Estimates without Histogram?

First, as you noticed, SQL Server will automatically create missing statistics (where possible) when compiling an execution plan, if the database option AUTO_CREATE_STATISTICS is on.

When statistics are not available, the default guess for an unknown inequality predicate is 0.3 (30%). When you use a local variable, the value in the variable cannot be sniffed (unless OPTION (RECOMPILE) is also specified). With a table cardinality of 316, the estimate is 0.3 * 316 = 94.8.

When you use a constant literal, the value is sniffed. Without statistics, it cannot use this sniffed value to check the histogram as it normally would, but it can judge its impact on the CHECK constraint (the one limiting Rate values to between $6.50 and $200).

If the sniffed value does not exclude the check constraint range completely, the estimate is based on the 0.09 (9%) guessed selectivity for a BETWEEN predicate (from the check constraint). 316 * 0.09 = 28.44.

If the sniffed value does exclude the check constraint range completely, the estimate is always 1 row (the cardinality estimator (almost) never produces an estimated number of rows less than 1).

If the query is simple enough to qualify for a trivial plan, and it is considered safe for simple parameterization, the constant literal is replaced with a parameter marker e.g. @1. This happens for a query like:

SELECT *
FROM HumanResources.EmployeePayHistory AS EPH
WHERE EPH.Rate > $200;

The execution plan shows an estimate of 1 row:

And the Scan Predicate shows a parameter marker:

If simple parameterization is prevented, e.g. by adding a clause that compares a constant with a constant:

SELECT *
FROM HumanResources.EmployeePayHistory AS EPH
WHERE EPH.Rate > $200
AND 1 = 1;

Without parameterization, this plan can never be reused with a different value for the parameter, so the optimizer can statically eliminate table access on the basis that the check constraint prevents any rows being returned. The resuting plan is:

Finally, be careful with types. The type of the Rate column is money, not decimal. Conversions can affect cardinality estimation in complicated ways. Specify a money literal with the $ prefix, or use an explicit CAST or CONVERT.

The internal details of cardinality estimation are not documented publicly (probably to avoid endless ever more detailed questions and complaints when things change), but there are a range of resources out there to aid you in this area. Just remember most of this is unoffical and so not supported by anyone. Some aspects are actually dangerous.

Some apply only to the original (pre-2014) cardinality estimator, some explain general principles applicable to both, and some are applicable to the "new" CE only. The following is not meant as an authoritative or complete list, just the ones that came to mind immediately:

The SQL Server 2014 Cardinality Estimator
13 Things You Should Know About Statistics and the Query Optimizer
Cardinality Estimation (SQL Server)
Constant Folding and Expression Evaluation During Cardinality Estimation
Statistics Used by the Query Optimizer
Plan Caching and Recompilation
Nacho Alonso Portillo's blog (MSFT) (search for cardinality)
Dima Piliugin's blog
SQLblog.com posts
SQLperformance.com posts
Microsoft SQL Server Internals (book) Kalen Delaney et al.


XQuery on Extended Events joining with dm_exec_query_plan not working

This is still a string, regardless of how it looks to you when you copy the output:

'0x' + CAST(...) AS varchar(4000)) AS plan_handle

Try using a style parameter on the CONVERT, so that the string isn't converted to a binary representation of the string, but rather converted to the string's literal binary value:

CONVERT(VARBINARY(32), '0x' + CAST(...) AS varchar(4000), 1) AS plan_handle


How to connect two PostgreSQL databases in two different locations

I would create a materialized view on the foreign tables.

You can refresh the materialized view while the remote database is available and use it in between.


The error  ORA-12162 is return because the client (sqlplus) doesn't manage to resolve the tns name provided:

 sqlplus user/passwd@ALIAS_TNS

The ALIAS_TNS have to be described in the tnsnames.ora file on the client side. Usually, this file is under ORACLE_HOME/network/admin.

Note that since Oracle 10g another connection syntax exist and does not require the tnsnames.ora. the syntax is:

sqlplus username/password@[//]host[:port][/service_name]


Adding TIME data to a DATE type column in time series table, and what sort of foreign key

When in doubt, keep it simple - just use DATETIME.  I can think of two reasons to flake TIME off into another table, but neither are commonly applicable:

Your table is very large and in use constantly, so you can't afford to block it while changing a data type.  I'd still work around this by adding a new DATETIME column, populating it gradually, and then dropping the old DATE field when possible.
It's very important that you keep your table narrow, and you'll need the time values very rarely.

You've stated that performance is more important than space, but I really wonder if six bytes for DATETIME2(0..2) is really going to make much difference over three bytes for DATE.  You need to value your own time, and the time of everyone who tries to understand your system in the future.

Your question about the PK really should be asked separately.  However, I concur with Rick James that, in this case, you probably don't need a synthetic key (your id field); just index on symbol_id and date.  Assuming you're clustering on the PK, consider putting date first, so you can easily query date ranges.  If you primarily filter (not just join) by symbol_id, then put it first in the index instead.  If you do both, cluster on { date, symbol_id } and index on symbol_id.


Can't connect to Analysis Services or Reporting Services on localhost

I believe the original installation was bad. I uninstalled and re-installed and now everything is working as it should.


filter table rows within range of related column

Fiddle
with user_defined_params as (
  select 2 as target_group_id,
  8 as first_id,
  11 as second_id
),
target_value_bounds AS (
  select min(value) as minval, max(value) as maxval
  from data where group_id=(select target_group_id from user_defined_params)
  AND ((id=(select first_id from user_defined_params))
       OR (id=(select second_id from user_defined_params)))
)
select * from data
where group_id=(select target_group_id from user_defined_params)
           AND value >= (select minval from target_value_bounds)
           AND value <= (select maxval from target_value_bounds);

If you're using this from a programming language, you'll definitely want to do away with the user_defined_params CTE and just use named query parameters.


You can grant "View Definition" permissions at the schema level in most SQL databases. I would create a new role in the database, for example schemareader, and assign permission like this:
GRANT SELECT, VIEW DEFINITION ON SCHEMA::[dbo] TO schemareader;


Difference Between Database Mail Profile and SQL Server Agent Operator in MS SQL Server

I believe Database Mail profiles are purely for the set up of what mail server to use and what email address the emails appear to be sent from.

SQL Server Agent Operators are essentially user(s) who are able to receive notifications of job statuses, effectively the receivers of email.

So Database Mail is for sending email, Operators is for receiving email.

https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/ssms/agent/operators?view=sql-server-2017

Note on this link https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail?view=sql-server-2017 it discusses setting up Database Mail for use in SQL Server Agent and it lists setting up the operators as a follow up task.


pg_restore error from pgadmin3 - Postgresql

lock_timeout is a new parameter that appeared with PostgreSQL 9.3

Per the release notes:

  Add configuration variable lock_timeout to allow limiting how long a
  session will wait to acquire any one lock (Zoltán Böszörményi)

That would indicate that pgadmin is using a PostgreSQL 9.3 pg_dump command to take the backup, instead of 9.0.

This is recommended only when migrating to 9.3. Since the target server is 9.0, better use the pg_dump binary that has been installed with the 9.0 server.

The path of external binaries can be set in pgAdmin preferences. To fix the problem, you may set it to the bin directory of your 9.0 installation and try to backup again.

Other than that, as a one-time fix you may edit the dump file if it's in plain text, remove the offending line SET lock_timeout = 0; in the hope that it's the only problem caused by the version mismatch.


ORA-01658 on index tablespace when trying to insert into empty table of new database

Oracle docs note this about ORA-01658:

  ORA-01658: unable to create INITIAL extent for segment in tablespace string

  Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

  Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL.

The obvious cure for the ORA-01657 error is to:

Make the INITIAL extent size smaller
Empty the recycle bin
Add a datafile the tablespace to provide a larger contiguous chunk size.
Reorganize the tables  in the tablespace

Try this query for your tablespace maximum chunk size tsfree.sql.

This case of ORA-01658 concerns needing to make the tablespace bigger, which can be done by either extending your file, or adding another one.  This is a good solution for ORA-01658 because repository tables need to be meticulously created keeping the extents of the table in mind.

This information was taken from ORA-01658: unable to create INITIAL extent tips from the dba-oracle.com website.

Disclaimer:
I am in no way affiliated with the company.


How do I create a view with new snake_case columns (replacing camelCase) and remaining original columns? (using the output of `FORMAT`)

If you're the TL:DR; type, please find the solution here

with snakes as (
    select
        ordinal_position,
        table_catalog,
        table_schema,
        table_name,
        format('%I.%I.%I',table_schema,table_name,column_name) as full_name,
        lower(
            -- replace all spaces with _, xX and Xx becomes x_x
            regexp_replace(
                -- First, replace spaces with an _
                replace(table_name, ' ', '_'),
                '([[:lower:]])([[:upper:]])',
                '\1_\2',
                'g'
            )
        ) as snake_case_table,
        lower(
            -- replace all spaces with _, xX and Xx becomes x_x
            regexp_replace(
                -- First, replace spaces with an _
                replace(column_name, ' ', '_'),
                '([[:lower:]])([[:upper:]])',
                '\1_\2',
                'g'
            )
        ) as snake_case_column
    from information_schema.columns
    where table_catalog =  current_database()
      and table_schema in ('public')
), tables as (
    select
        format('%I.%I',table_schema,'vw_'||snake_case_table) as view_name,
        string_agg(
                full_name||' as '||snake_case_column,
                ','||chr(10)||'        ' order by s.ordinal_position

        ) as column_list,
        format('%I.%I',table_schema,table_name) as base_table
    from snakes s
    group by
        s.table_catalog,
        s.table_schema,
        s.table_name,
        s.snake_case_table
)
select
    'create or replace view '||view_name||' '||chr(10)||
    'as '||chr(10)||
    '    select '||chr(10)||
    '        '||column_list||chr(10)||
    '    from '||base_table||';'||chr(10)||chr(10)
    as create_view_command
from tables;

As you can see on the fiddle, the output of this should be one create view command for every table in the current database. Please note I haven't worked with postgres foreign data wrappers, so I've removed the cross-database references (but they should be rather trivial to add back in).

The only material additions I've made to your code are the use of string_agg() and group by. However, re: format('%I."%I"',...), you can lose the double quotes here. As per the docs

  I treats the argument value as an SQL identifier, double-quoting it if necessary. ...


How to subtract fields and insert the value in other field automatically?

If this requirement is very important to you, take a look here - calculated fields in MariaDB - never any need to perform calculations - it's all taken care of for you! Although, as has been pointed out, you can always perform the calculations on the fly.


Check out this link. It has clear explanation of digging down the issue.

https://stackoverflow.com/questions/8390322/cause-of-a-process-being-a-deadlock-victim


See this article about the performance problems and possible solutions using ORDER BY ... LIMIT.

I would create an index on ord.ID DESC and remove the subquery. This is assuming that ID is not a primary key and indexed already.

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
FROM table_orders_1_products AS op
LEFT JOIN table_orders_1 AS ord ON ord.ID=op.orderID
LEFT JOIN cms_user_aditional AS USER ON ord.userAdditional=USER.ID
LEFT JOIN table_status AS stat ON stat.ID=op.statusID
LEFT JOIN table_product_1 AS sp ON op.productID=sp.ID
LEFT JOIN table_partners AS spp ON sp.supplier=spp.ID
WHERE op.statusID!='0'
ORDER BY ord.ID DESC LIMIT 1000

See this page on creating indexes. (Or a Primary Key if it does not already exist. Creating a primary key on unique columns, usually ID's in this case, will help as they are automatically indexed.)

To add a Primary Key:
ALTER TABLE table_orders_1 ADD PRIMARY KEY(ID)

To create an Index:
CREATE INDEX ID_index ON table_orders_1 (ID DESC)

EDIT:

In general it is good practice to look at and understand your execution plans to see if you can identify any performance bottlenecks. See if you can narrow the scope of your table selects, for example, do you really need to LEFT JOIN to all the tables in your query? INNER JOIN is typically faster, unless the tables are very small (~10 rows) or if the tables do not have sufficient indexes to cover the query. Also on second examination it does not look like you are selecting anything from table_partners SPP, if not it does not need to be included. It may also be faster to split the table joins into two queries like below.

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, ord.userAdditional, op.statusID, op.productID
INTO #TEMP
FROM table_orders_1_products AS op
INNER JOIN table_orders_1 AS ord
  ON ord.ID=op.orderID
  AND op.statusID <> '0'
ORDER BY ord.ID DESC LIMIT 1000

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
FROM #TEMP T
INNER JOIN cms_user_aditional AS USER
  ON T.userAdditional = USER.ID
INNER JOIN table_status AS stat
  ON stat.ID = T.statusID
INNER JOIN table_product_1 AS sp
  ON T.productID = sp.ID


Converting SQL SERVER Procedure to POSTGRESQL

It's way easier in plpgsql:

CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30)) RETURNS VOID AS $$
DECLARE
  _r RECORD;
  _id_kraj int;
  _nazwa_zespolu text;
BEGIN
  _id_kraj := id_kraj FROM kraj WHERE nazwa = $1;

  FOR _r IN
    SELECT imie, nazwisko, id_zespol, id_kraj FROM pilkarz
  LOOP
    IF r.id_kraj = _id_kraj THEN
      _nazwa_zespolu := nazwa FROM zespol WHERE zespol = _r.id_zespol;
      RAISE NOTICE '% %, %', _r.imie, _r.nazwisko, _nazwa_zespolu;
    END IF;
  END LOOP;
END;
$$ LANGUAGE PLPGSQL;

But the following even simpler one would do the exact same thing:

CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30)) RETURNS VOID AS $$
DECLARE
  _r RECORD;
BEGIN
  FOR _r IN
    SELECT imie, nazwisko, id_zespol, id_kraj, (SELECT nazwa FROM zespol WHERE zespol = p.id_zespol) nazwa
    FROM pilkarz p WHERE id_kraj = (id_kraj FROM kraj WHERE nazwa = $1)
  LOOP
    RAISE NOTICE '% %, %', _r.imie, _r.nazwisko, _r.nazwa;
  END LOOP;
END;
$$ LANGUAGE PLPGSQL;

And you can achieve the same thing straight without even creating a function:

DO $$
DECLARE
  _r RECORD;
BEGIN
  FOR _r IN
    SELECT imie, nazwisko, id_zespol, id_kraj, (SELECT nazwa FROM zespol WHERE zespol = p.id_zespol) nazwa
    FROM pilkarz p WHERE id_kraj = (id_kraj FROM kraj WHERE nazwa = $1)
  LOOP
    RAISE NOTICE '% %, %', _r.imie, _r.nazwisko, _r.nazwa;
  END LOOP;
END;
$$;


SQL Server renders a handful of DVMs that can provide you with this information.  See the TechNet reference on About the Missing Indexes Feature.

Mainly, you will be dealing with four DMVs:

sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_details
sys.dm_db_missing_index_columns

Note: This is how you do it.  But be very careful when considering "missing indexes".  You could end up with a large amount of indexes and data manipulation will take a hit from that.  This does not replace prudent index consideration/creation.


First of All indexes wont be used if used %keyword% , it should be keyword%.

after you do that add index on

index (status, s_name, f_name)


Horizontally scaling SQL Server, distributing the database with sharding

Is there material on how?

Yes. You can read about it at https://msdn.microsoft.com/en-us/library/dn589797.aspx

  Is the process as tough as it seems?

Sort of. It is time consuming in that it requires careful planning in advance on how you want to structure the data, but if you're doing something with a massive database, I've personally been very pleased with the performance enhancement.


There are hundreds of answers.

Get rid of redundant indexes.  Index cookbook
Batch the INSERTs
Build "summary tables" (very useful in DW type apps).  Summary tables
Get a bigger AWS instance
With very rare exceptions Partitioning does not speed up anything.  Partition blog
Sharding is useful only if the data can be split is certain ways; meanwhile it slows down other operations.
AWS does a decent job of setting the tunable VARIABLES, so there is not likely to be any benefit in further tuning them.  Further analysis
Turn on the slowlog to find out which queries are the 'worst'.  Slowlog
If possible, avoid using GUIDs/UUIDs.  UUIDs don't scale
Don't do pagination via OFFSET.  Pagination blog

Please provide some clues of what the application is about.  Include SHOW CREATE TABLE and sample INSERTs and SELECTs.


SQL Query in suspended state causing high CPU usage

Although you've accepted an answer that informs you how to kill the session, this is a kind of problem that could repeat itself and so you should probably be aware that the CXPACKET wait type in itself is not a problem, but tends to indicate an issue with the execution of a parallel query.

Brent Ozar has posted a good explanation (with a video) of what a CXPACKET wait is and how you go about reducing them. As an example, if your parallel query runs across 5 CPUs and 4 of them have completed their allocated workload, the waiting on the 5th CPU to complete is what is identified as the CXPACKET wait.

You can start to alleviate CXPACKET waits by looking into setting the Maximum Degree of Parallelism (MAXDOP) on the individual query and/or the Cost Threshold for Parallelism on the instance, both of which are explained in the article.


How to find out what is causing my Identity column to keep going up

The most likely scenario based on the test JNK did, is transactions that are rolling back, but still causing the seed to increment.

The best route is to trace (profile) the activity so you can catch the offending transaction(s). Here's a starting point for using Profiler:

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

When you're starting to learn tracing, err on the side of tracing less than you think you need. tracing is "invasive", and you can imperil a DB instance if you go after too much info.

In this case you're looking for transaction based info. Feel free to post back if you have specific questions while setting up your trace. Note that you'll probably want to log to files unless you can leave the GUI up overnight. Logging to files is preferred overall unless you're trying to catch something in real time.


Should I be concerned about excessive Com_change_db commands?

MONyog
In MySQL 5.6, 341 different status values are being recorded.
If all values are retrieved with:
SHOW GLOBAL STATUS;

Then, you are retrieving all 341 values. Com_change_db is just coming along for the ride.
If each value is retrieved individually, such as with
SHOW GLOBAL STATUS LIKE 'Com_change_db';

this should not be a problem since you can set MONyog's interval for collecting status values.
In either scenario, MONyog is a non-factor in your case.
Source Code
You may want to leave your code alone since you are only generating a Com_change_db 58 times per minute (about 1 per second). Just to be hypothetical, if each Com_change_db took up 2K in transmission over the DB Connection through the network at a rate of 1 per second:
86400 sec X 2K = 168.75 MB per day
That's not a lot to worry about in a day.
Therefore, changing your code just to lower the Com_change_db count would be the only gain you would make. There would be no discernable performance improvement.
Epilogue
I would be more concerned with status variables that count temp table creation or all the handler counts as that would be earmarks for bad queries or large temp tables do to key distribution or setting for temp tables that are too small.
UPDATE 2013-09-26 19:38 EDT
OK my mistake. You said 58 per second, not per minute.
Let me redo my hypothetical example
86400 sec X 2K X 58 =

9.558 GB per day
407.8 MB per hour
6.7968 MB per minute

This shows that could reduce the amount of network traffic by cutting out Com_change_db requests. If you have a good network with 10Gig E, this may not be an issue.
Summary
Think of the tradeoff you were proposing: If you are qualifying all tables with the database name prepended, then you are transmitting more bytes in the SQL text and giving the Query Parser more to work with anyway.
Another way to approach it would be to have a different DB Connection connecting to a separate database. That way, one DB Connection would not have to switch databases and run up the Com_change_db counter and add to the network traffic. The tradeoff would be to have multiple DB Connections per client (requiring more memory).
In other words, something has to bite the bullet in the end.
Again, I suggest leaving everything alone because all the alternatives require major code changes without knowing if the enhancements were worth it in the end.


Designing a tracking system for ordered workouts

Well, there's a simple 1 table solution right here:

| exercise_type (not null) | amount (not null) | other_field (null) | date (not null) |

To get the exercises for a particular date, you'd run a simple select statement (psuedo code):

select * from exercises where date in between (start, finish)

And you can refine to particular exercise types by adding:

and exercise_type = 'plank'