Scripts to disable constraints and indexes for data warehouse load I load data into a data warehouse.

Presently I have a script that I use to drop foreign key constraints and indexes prior to loading the data, for convenience and speed. There is a big window in which I can do the load so I don't need to worry about users accessing the data during the load, but I don't want to impact unrelated data in other tables in the database.

I have done some research here and elsewhere to come up with this script but I wonder if there are some things that I might be overlooking which could either make performance sub-optimal or if I might be missing something important (I don't know ... calculated columns or something?) or maybe I'm doing things in the wrong order etc.

Any advice appreciated to make this robust and performant.

Disable Constraints and Indexes

Edit: I removed the WHILE loop which commenters helped me realise was redundant.

Declare @schema varchar(128) = 'dbo';
Declare @sql nvarchar(max) = N'';

-- 1. Indices
-- Select a list of indexes in the schema and generate statements to disable them.
Select @sql = @sql + 'ALTER INDEX ' + QuoteName(idx.name) + ' ON ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' DISABLE;' + CHAR(13)
  From sys.indexes As idx
  Join sys.objects As obj On idx.object_id = obj.object_id
  Where ((obj.type = 'U' And idx.type in (2,6)) -- Non-clustered index/columnstore on a table
    Or obj.type = 'V') -- All indexes on indexed views
    And obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name, idx.name;

Execute sp_executesql @sql;

-- 2. Foreign-key constraints
-- Build a list of foreign keys constraints in the schema and generate statements to disable the constraint checking.
Select @sql = @sql + 'ALTER TABLE ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' NOCHECK CONSTRAINT ' + QuoteName(fk.name) + ';' + CHAR(13)
  From sys.foreign_keys As fk
  Join sys.objects As obj On fk.parent_object_id = obj.object_id
  Where obj.schema_id = (Select schema_id From sys.schemas Where name = @schema);

Execute sp_executesql @sql;

Enable constraints, rebuild indexes and update statistics

Declare @schema nvarchar(128) = 'dbo';
Declare @sql nvarchar(max) = N'';

-- 1. Indices
-- Build a list of tables in the schema and generate statements to enable the indices on them.
Select @sql = @sql + 'ALTER INDEX ' + QuoteName(idx.name) + ' ON ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' REBUILD' + iif(idx.type = 6, ' WITH (MAXDOP = 1);', ' WITH (FILLFACTOR = 100);') + CHAR(13)
  From sys.indexes idx
  Join sys.objects obj ON obj.object_id = idx.object_id
  Where ((obj.type = 'U' And idx.type in (2,6)) -- Non-clustered index on a table
    Or obj.type = 'V') -- All indexes on indexed views
    And obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
    And idx.is_disabled = 1 -- Don't rebuild indexes that are already online
    And idx.is_hypothetical = 0 -- Don't rebuild hypothetical indexes!
  Order By iif(idx.type = 6, 1, 2), obj.name, idx.name;

Execute sp_executesql @sql;

-- 2. Foreign-key constraints
-- Build a list of foreign keys constraints in the schema and generate statements to enable them with checking.
Select @sql = @sql + 'ALTER TABLE ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' WITH CHECK CHECK CONSTRAINT ' + QuoteName(fk.name) + ';' + CHAR(13)
  From sys.foreign_keys fk
  Join sys.objects obj ON obj.object_id = fk.parent_object_id
  Where obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name, fk.name;

Execute sp_executesql @sql;

-- 3. Statistics
-- Build a list of tables in the schema and generate statements to update the statistics on them.
Select @sql = @sql + 'UPDATE STATISTICS ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' WITH COLUMNS;' + CHAR(13)
  From sys.objects obj
  Where obj.type = 'U' -- User defined
    AND obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name;

Execute sp_executesql @sql;

For a few things that you may have not considered:

If an index starts as disabled you'll end up rebuilding it after your script finishes.
If an index starts with page compression you'll rebuild it without compression.

For performance:

Since no other process is accessing the table you could rebuild with the ONLINE = OFF option for possibly a small performance boost due to less locking.
Depending on the size of tempdb and your indexes you could use the SORT_IN_TEMPDB = ON option for a performance boost.
You're updating statistics on all tables, even if that table hasn't been modified or had very few rows changed. You could try to be more restrictive in your updates and only look at tables that were modified since the last stats updated or were modified at all.
It looks like you're running all of these queries in a single session. Have you considered splitting up the work among multiple sessions?


MySQL cluster: Failover between data node Today, I do some expirements for understanding MySQL cluster better.

MySQL cluster works fine:

ndb_mgm> show

Connected to Management Server at: localhost:1186

Cluster Configuration

[ndbd(NDB)]2 node(s)

id=2@192.168.56.2  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)

id=3@192.168.56.3  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)

[ndb_mgmd(MGM)]1 node(s)

id=1@192.168.56.1  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]1 node(s)

id=4@192.168.56.4  (mysql-5.6.19 ndb-7.3.6)

Case 1:
I turn off management node by killall ndb_mgmd then kill a data node by killall ndbd. I see remain data node is turned off although I don't do anything with it. There is no live data node so sql node is impossible to get or change data.

Case 2:
I keep management node live. I kill a data node by killall ndbd. I see the remain data node is sitll living so sql node still works well.

I have a question. Should I keep the management node for controlling data nodes ? I don't read the mysql reference manual mention the similar case.

Thank you in advance.

The management node is not essential for normal mysql operations, except on:

Installation and other cluster-wide maintenance operations
It is by default the arbitrator node, which means that it holds the responsibility to decide if a particular partition continues running in the case of a split brain.

Split brain is an important concept in clustering, which happens when apparently, a group of nodes identify some other group of nodes as down (a group can be a single node). However, you cannot be sure if they are really down or the communication among them is down. In order to maintain consistency, an algorithm must decide what to do. If not, your separate partitions of the cluster could end up with different states/data sets. Typically, and that is what happens, if your partition has more than 50% of the nodes, you continue, if not, you stop yourself.

In your particular case, without an arbitrator, as 1 node is only 50% of the data nodes, it decides to "kill themself" -it is not a failure, it is a design decision. With the arbitrator, we know that the other node will not continue by himself, as the arbitrator cannot see it, so it decides to continue with 50% of the nodes (1, that has all the data, as the default is noofreplicas = 2) + arbitrator. In general, you want to run with an odd number of nodes to minimize the possibility of a full cluster shutdown.

Typically, on an NDB setup, your SQL nodes are also a primary and a secondary arbitrator node (you can modify its weights). You normally have more than one API node, to avoid a single point of failure. So the most typical setup is 2 API/SQL/arbitrator nodes and 2 NDB nodes. That way, in most cases, 2 nodes have to go down for the whole cluster to fail.


How to do a configuration on RMAN with run block without save this configuration on rman I want to do a run block with this configuration for test:

run {
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 50M;
BACKUP DATABASE;
}

But I don't want that the configuration CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 50M stay save on rman after of run the run block, how to do it?

Instead of CONFIGURE, just use ALLOCATE to allocate a channel with the desired parameters. You will also have to give a name for the channel, which is C1 in the example here:

run {
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 50M;
BACKUP DATABASE;
}


Validate application to access the database I need some help for my issue I will explain.

We have a delphi application named for instance App.exe. We use oracle database with our application.

I would like to allow just my App.exe (my valid application that was built by me) and not other App.exe that some other developer created.

At this moment we are signing our App.exe with a public CA.

So, is there any way to say to oracle to verify the App.exe and allow the connection since it is "official"? Maybe a trigger? Or validating the certificate signed?

Thanks!!

Diego

No matter what safeguards you put in place on the server side, it will always be possible for a client to fake a connection. This is an inherent limitation of the client-server model - the server can only gather information about the client by querying the client, and the client can always be configured to lie.

You cannot completely protect against this without the use of middleware. If you only allow the middleware to connect directly to the database e.g. by ip blocking, then you can have complete control of what commands can and can't be sent to the database.


Safe to use odbc link betw Filemaker 11 Server & SQL Server 2012? I’ve established a successful odbc connection between filemaker 11 Server and SQL Server 2012. Filemaker, inc has not certified this compatibility between the two but I have had no problems importing data from the sql database into filemaker. Does anyone know of risks utilizing the two databases? I need to understand better why this  connection works and why FileMaker’s specifications don’t include SQL 2012 Server!

Filemaker 11 was released in 2010. SQL Server 2012 was released in (surprisingly enough) 2012.

So, the Filemaker 11 documentation wouldn't list SQL Server 2012 as supported, because it wasn't released yet. When I looked for documentation online, I found the "Filemaker 11 ODBC and JDBC Guide", which has a 2010 copyright date.

By FileMaker 15, the documentation doesn't list explicit versions of other products it does or doesn't work with. It just days it works with products Like Oracle, SQL Server, and MySQL (no specific versions listed).

Since:

Every version of SQL Server from 2000 on that was out when FileMaker 11 was released was listed as working with it,
Later versions don't bother listing versions, but still show SQL Server as something they work with, and
You're successfully able to query it now

I wouldn't think there would be any unexpected risks in using them together.


How to enforce strict handling of GROUP BY with non-aggregate / bare columns or UPDATE with multiple-row SELECT in SQLite Unlike some other RDBMS, SQLite does allow for bare columns in SELECT querys with GROUP BY, i.e. not all selected columns need to be part of the group by or aggregate functions otherwise. In this case, the first value in the result set will be selected for the grouped output row, regardless of whether this value is constant across all entries in the result set.

Something similar seems to happen in an UPDATE query with SELECT where the SELECT returns multiple rows, where only one row can be updated. In this case the first result from the SELECT is used for updating the target row.

I'm wondering if it's possible to force SQLite to be more strict in such cases and throw an error.

For illustration, consider this fiddle. Which will update both B properties with the first result from the SELECT. What I would like instead is that an error is generated.

SQLite implicitly adds a "LIMIT 1" to all scalar subqueries. There is no built-in mechanism to change this; you would have to modify SQLite's source code.


What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log? I'm puzzled by the innodb_max_dirty_pages_pct parameter, and its relation to the redo log.

The definition itself is simple:

  InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value.

However, what confuses me is the relationship with the redo log.

Does "flush data" mean:

copy dirty pages from the log buffer to the redo log, or
from the redo log to the buffer pool file?

I can't imagine how case 1 can happen. Even in systems with innodb_flush_log_at_trx_commit = 0, logs are flushed one per second, so how can 90% of the buffer pool be changed in one second?

If the case is 2 instead (and this interpretation would be, I guess, (checkpoint age > total log size * pct), such high number of dirty pages don't fit in the redo log.

What am I misunderstanding?

You say:

logs are flushed one per second, so how can 90% of the buffer pool be changed in one second? [...] such high number of dirty pages don't fit in the redo log

You seem to think that the log buffer and the buffer pool mean the same thing; that is not true.
There is no relation between flushing dirty pages from the buffer pool and the redo log. Log buffer and the buffer pool serve different purposes and are managed separately.
As the manual points out:

The buffer pool is an area in main memory where InnoDB caches table and index data

and

The log buffer is the memory area that holds data to be written to the log files on disk

(Emphasis mine.)
By the time a dirty data or index page is flushed, the corresponding log records will have been written out already -- this is the nature of write-ahead logging. Flushing dirty pages from the buffer pool means these pages are written to the appropriate ibdata* and/or .ibd files.
EDIT By RolandoMySQLDBA
Adding Visual Representation of InnoDB from Percona CTO Vadim Tkachenko


How to accomplish such insert-or-update strategy? Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better.
I've stumbled upon a business requirement that's kind of hard for me and I was wondering if anyone had already dealt with it in their career.
There's this data in a staging table that needs to be loaded in a production table with insert for the new data and insert-update(backup like) for the old data.
The sample of data in production table looks like this:

AssetID
BeginDate
EndDate
Field1
Field2
FieldN

1
(TS)
(TS)
0.2351
1.3510
4.1234

Meanwhile in staging:

AssetID
Field1
Field2
FieldN

1
0.2351
1.3510
4.1234

In prod the PK is AssetID and BeginDate meanwhile in staging is only AssetID (because the data is flushed every time).
The requirement is to write a stored procedure that when there are new assets in the staging area, they are inserted as new in prod(with BeginDate as CURRENT_TIMESTAMP and EndDate as NULL), meanwhile if there are data(in staging) that are different on the Field1,Field2 or FieldN fields from the production one, then a new record is written with the same AssetID but BeginDate as CURRENT_TIMESTAMP and EndDate as NULL, and the old record with the same combination (BeginDate memorized and EndDate is NULL) being updated with an EndDate as CURRENT_TIMESTAMP
Any of you have dealt with a similar task/requirement? If yes, could you share your solution please?

You have to try to finalize any existent opened record for the given AssetID before insertion of the new record.

BEGIN
  DECLARE ts TIMESTAMP = NOW();
  DECLARE f1, f2, fN FLOAT;

  SELECT Field1, Field2, FieldN
    FROM prod_table
   WHERE AssetID = _givenID
     AND EndDate = NULL
    INTO f1, f2, fN
  ;

  IF ( f1 <> _field1 OR
       f2 <> _field2 OR
       fN <> _fieldN )
    THEN
      UPDATE prod_table    -- Here we suppose there is zero or one opened record
         SET EndDate = ts  -- If there is no records for _givenID, update do nothing
       WHERE AssetID = _givenID
         AND EndDate IS NULL
      ;

      INSERT prod_table (AssetID, BeginDate, EndDate, Field1, Field2, FieldN)
      VALUES ( _givenID, ts, NULL, _field1, _field2, _fieldN )
      ;
  END IF;
END


SQL AES_256 Encryption and Decryption I have an odd encryption and decryption problem. Encryption worked fine in SQL 2008 R2, we were using TRIPLE_DES_3KEY. This has been deprecated in SQL 2016, so we were are testing out AES_256. My original string keeps getting cut off, not sure what it's doing or how else to explain. I am sure I'm missing something, but not sure what. Detals below. Thanks!

My Symmetric Key:

CREATE SYMMETRIC KEY SK03
WITH
    ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'ThisIsMyAES_256EncryptionTest';

My Table:

CREATE TABLE [dbo].[_EncryptionTest](
    [OriginalValue] [varchar](max) NULL,
    [Encryptedvalue] [varbinary](8000) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My Stored Procedure:

CREATE PROCEDURE [dbo].[_usp_EncryptionTest]
    @InputString varchar(MAX),
    @InputString2 varchar(MAX)

AS
BEGIN
    SET NOCOUNT ON;

    OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'

    INSERT INTO _EncryptionTest
        (OriginalValue, Encryptedvalue)
    VALUES (
        @InputString
        ,ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY,@InputString2))
        )

    CLOSE SYMMETRIC KEY SK03;

END

My Test SQL:

EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!'

As you can see from the image below, the data gets updated to my table.

My Decryption SQL:

OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'
SELECT
    [OriginalValue]
    ,CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue]))
FROM
    _EncryptionTest
CLOSE SYMMETRIC KEY SK03;

The image below should show the results with the decryoted value cutoff.

Try defining the length of the varbinary in your convert statement as varbinary(max). Per https://technet.microsoft.com/en-us/library/ms188362(v=sql.110).aspx it would default to 30.

You can replicate that with the following script:

DECLARE @STRING VARCHAR(100);
SELECT  @STRING = REPLICATE('X', 100);

SELECT  LEN(@STRING) AS String_Length,
        LEN(CONVERT(VARBINARY, @STRING)) AS Varbinary_Length ,
        LEN(CONVERT(VARBINARY(MAX), @STRING)) AS VarbinaryMax_Length;


Query behavior - with regard to statistics A quick background of the issue: We have an application, and we have many instances of this application running for clients. While they may be on slightly different versions, they are fundamentally the same.
Yesterday, one client had an issue with SQL timeouts. Looking at the query we identified an issue with certain tables, and the use of OUTER APPLY and re-wrote it to circumvent the issue.
Inspecting the query plan today, I can clearly see that the stats are bad, as it is expecting circa 2.5 million rows, which is incorrect. I updated the stats and it has resolved the issue, and now expects 30 rows.
My confusion comes from when I inspect the query plan for other clients' databases, and the stats seem off, but, the query is returned in around 1 second, not the 45 seconds seen in the issue that was faced.
Both databases have auto stats turned on. Does this suggest a problem with auto stats on the problem database?
Whilst testing, I did clear the cache DBCC FREEPROCCACHE so the engine had to generate a plan each time. I have not done this on a database that was returning the data in a timely fashion however.
Sorry for the vagueness, I unfortunately cannot share the query plan, due to sensitive information.
Currently, we run only automatic stats updates (no scheduled statistics / index maintenance). This will change; the databases have been neglected somewhat. I should also mention, these databases are in Azure. I am not sure if that changes anything?

Auto-stats and stats per se is only one aspect. Another is the plan, the calculated selectivity, and also the fact that plans can be cached.
So imagine a plan was generated when stats were "ok" (along with other circumstances such as sniffed parameter values). This can now stick around for a while in cache, and even if the stats deteriorate (a bit), your "good plan" can linger in cache.
Then you have the aspect that autostats samples data. The larger the dataset, the smaller the part of that data set that is sampled. So even when auto-stats kicks in, with a larger dataset you may get lower quality stats.


Getting Index Scan instead of a possible Index Seek? Currently learning a few things on query optimization, and I've been trying out different queries and stumbled upon this "issue".

I am using the AdventureWorks2014 database, I ran this simple query:

table structure (taken from https://www.sqldatadictionary.com/AdventureWorks2014.pdf):

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

returns 19,720 rows

total number of rows in Sales.Customer = 19,820

And after checking to make sure CustomerID is in fact not just the PK of the table but also has a clustered index on it (yet it uses a non clustered index), that indeed is the case:

EXEC SP_HELPINDEX 'Sales.Customer'

Here is the execution plan ↓

https://www.brentozar.com/pastetheplan/?id=B1g1SihGr

I have read that when faced with huge amounts of data and/or when it returns more than 50% of the data set, the query optimizer will favor an index scan. But that table as a whole barely has 20,000 rows (19,820 to be exact), it's not a big table by any means.

When I run this query:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 30000

returns 118 rows

https://www.brentozar.com/pastetheplan/?id=Byyux32MS

I get an index seek instead, so I thought it was due to that "more than 50% case" however, I also ran this query:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 20000

returns 10,118 rows

https://www.brentozar.com/pastetheplan/?id=HJ9oV33zr

And it also used an index seek, even though it was returning more than 50% of the data set.

So what's going on here?

EDIT:

With IO Statistics turned on, the >100 query returns:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

While the >20,000 one returned:

Table 'Customer'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So added WITH(FORCESCAN) option to the >20,000 one to see what would happen:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So it ends up running better with an Index Scan (less logical reads), even though the query optimizer chose to run an Index Seek for this particular query.

You use a nonequality predicate so your "seek" operations are in fact scans which just start from some value (not from "first") and then go to the end of the clustered index leaf level.

In the other hand you return only one column which is the clustered index key so using any of indexes won't get any key lookup operations. The optimizer has to estimate what would be cheaper: scanning a nonclustered index (two int columns on the leaf level) or part scanning of your clustered index (all columns on the leaf level).

It estimates it depending on current statistics (how many rows) and metadata (what is one row size). We saw the optimizer made a mistake on >20,000 predicate.

  When faced with huge amounts of data and/or when it returns more than
  50% of the data set, the query optimizer will favor an index scan.

That is a fact when the optimizer has to choose performing clustered index or table scan versus nonclustered index seek + key lookups.

In your case if your index on CustomerID were nonclustered you would always see a seek operation on that index, but if you then added another column to your output you would see the index seek + RID lookups on short resultsets and table scan on the big ones.


Always On Listener Behavior - DBs removed from group We have multiple multi-site always on failover cluster instances in our env. We have an enforced internet outage being imposed on us and at the time the link between the data centers will be down for up to 6 hours...

Our plan is to remove the Databases from the AGs on the primary instances, but leaving the listener in place so the applications should still connect to the DBs via the VIP/DNS.

We have tested this and did not encounter any issues.

However we are struggling to find any information to say that this approach is best practice. We cannot suspend movement as the outage could be long and MS recommend movement is suspended for a short period only.

Anyone else had to deal with this situation? If yes - is our approach feasible?

Once the outage is complete we will disable log backups on the Primary nodes, delete the non-synched DBs on the secondary nodes and then add them all back in on the primary.

Any thoughts or input would be greatly appreciated.

How large are your transaction log files? I'd be prepared to try going with leaving them disconnected, provided no reindexing, bulk ops etc go on during the outage, and provided you can be 100% confident it will only be 6 hours.

In my experience OLTP transaction log growth is normally modest, and provided your t-logs are a reasonable size, you should be ok.

Difficult to test though as normal OLTP transactional throughput is tricky to simulate, although profiling for replay could be a way of measuring it.


why does my pivot give me an error? Msg 207, Level 16, State 1, Line 1
Invalid column name 'FullName'.

select FullName,
['1-Urgent'], ['2-High'], ['3-Medium (3000)']
from (select FullName,AltBusinessSeverity, total from Mdata) AS ST
PIVOT (
min(FullName) FOR  AltBusinessSeverity in (['1-Urgent'], ['2-High'], ['3-Medium (3000)'])
) AS PT

If you want to show total as your data, then you need to do the following:

select FullName,
['1-Urgent'], ['2-High'], ['3-Medium (3000)']
from (select FullName,AltBusinessSeverity, total from Mdata) AS ST
PIVOT (
min(total) FOR  AltBusinessSeverity in (['1-Urgent'], ['2-High'], ['3-Medium (3000)'])
) AS PT


Display total sales per brand I have 3 tables.

BRANDS

CREATE TABLE BRANDS (
    id_brand BIGINT(4) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(25) NOT NULL,
    UNIQUE (name)
)

PRODUCTS

CREATE TABLE PRODUCTS (
    id_product BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(6) NOT NULL UNIQUE,
    name VARCHAR(30) NOT NULL,
    price FLOAT(6,2) UNSIGNED NOT NULL,
    is_discounted TINYINT(1) NOT NULL,
    discount_percentage INT(2) NOT NULL,
    discounted_price FLOAT(6,2) NOT NULL,
    description TEXT NOT NULL,
    stock INT(4) UNSIGNED NOT NULL,
    photo_1 VARCHAR(50),
    product_type_id BIGINT(4) UNSIGNED NOT NULL,
    brand_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_type_id) REFERENCES product_types (id_product_type),
    FOREIGN KEY (brand_id) REFERENCES brands (id_brand),
    is_visible TINYINT(1) NOT NULL
)

PRODUCT SALES

CREATE TABLE PRODUCT_SALES (
    id_product_sale BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    QUANTITY INT(4) UNSIGNED NOT NULL,
    sale_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_price FLOAT(12,2) UNSIGNED NOT NULL,

    user_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (user_id) REFERENCES USERS(id_user),

    product_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_id) REFERENCES PRODUCTS(id_product),

    payment_method_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (payment_method_id) REFERENCES PAYMENT_METHODS(id_payment_method),

    sale_status_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (sale_status_id) REFERENCES SALE_STATUS(id_sale_status)

)

I need a query to display the ammount of produces sold per brand.
I should get the following

Intels - 27
Western - 5
GeForce - 10

But i get

Intels - 42

This is how i tried to get the result i need.

SELECT b.name, sum(ps.quantity)
FROM Product_Sales ps
JOIN Products p
ON ps.product_id = p.id_product
JOIN Brands b
ON b.id_brand = p.brand_id

What's wrong with my query? Thanks in advance.

Basically, you're missing a GROUP BY! To answer your question, I did the following:

Created tables (relevant fields only):

CREATE TABLE brand
(
  brand_id INTEGER,
  name VARCHAR(25)
);

CREATE TABLE product
(
  product_id INTEGER,
  brand_id INTEGER,
  name VARCHAR(30) NOT NULL
);

CREATE TABLE product_sale
(
  product_sale_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);

Then I created some sample records:

INSERT INTO brand VALUES (1, 'Ferrari'), (2, 'Lamborghini'), (3, 'Porsche');

INSERT INTO product VALUES (1001, 1, 'TestaRossa'), (1002, 1, 'Dino'), (2001, 2, 'Countach'), (2002, 2, 'Diabolo'), (3001, 3, 'Cayenne'), (3002, 3, '911'), (3003, 3, 'Boxter');

INSERT INTO product_sale VALUES (500001, 2001, 45), (500002, 1001, 12), (500003, 3003, 7), (500004, 3001, 750), (500005, 3001, 400);

and then ran this query:

SELECT
  b.name AS "Brand name",
--  p.name as "Product name",
  SUM(ps.quantity) AS "Quantity"
FROM brand b
JOIN product p
ON b.brand_id = p.brand_id
JOIN product_sale ps
ON p.product_id = ps.product_id
GROUP BY b.name;  -- p.name

I left in the extra p.name fields (commented out with --) so that you can see how the query was constructed. Take out the comments and have a look.

Result:

Brand name  Quantity
Ferrari     12
Lamborghini 45
Porsche     1157

All of this can be found at db-fiddle here. For future reference, it's better to have a Minimum Complete and Verifiable Example (preferably complemented with a fiddle) which a) means that everyone is singing off the same hymnsheet - i.e. your data and b) there's no duplication of effort. You will obtain quicker and better answers with a well-constructed fiddle. You might also like to take a look here, and maybe take the tour and visit the helpdesk.

These sites are a great resource, all the more so if you take the time and effort to follow the guidelines. p.s. welcome to the forum!


Risks of manual failover in Always On Availability Groups In my environment, I have SQL 2014 with Always On Availability Group for databases in asynchronous with manual fail over.

We have 1 primary and 2 secondary replicas--secondary1 and sec2.

I have to do failover from primary to secondary1. I know some data loss will happen as it is a forced failover.

My questions:

What will be prerequisite for this?
Can we do in busy hours or after busy hours?
Is there any risk involved during failover.

Appreciate your suggestions as first time I will do failover.

Do yo have a test environment?  If you don't, get a $200 free Azure credit and set your environment up there (same service pack level) and try it, it is one of the best way to gain confidence and find edge cases.  Nothing can really beat testing a like to like environment except experience.  Also check out the official support docs from Microsoft.

Per the doc you'll want to check out several items, including:

To determine the failover readiness of an secondary replica, query the is_failover_ready column in the sys.dm_hadr_database_cluster_states dynamic management view, or look at the Failover Readiness column of the Always On Group Dashboard.

To answer the question though, AlwaysOn failovers are very fast as the service is running on both instances and the listener then points to the new host.  Ensure the hosts are connecting to the listener instead of the IP of the machine.

Thus, if tested and running properly in your environment it is much faster than FCIs (Failover Cluster Instances) during busy hours.  Note that AlwaysOn only works per database and not per server so you will want to ensure jobs are setup right on the new primary replica, users, permissions/sids, and if you need to maintain databases in sync you will want to ensure that you do that somehow before failing one of them over and not the other.  Also, if you use MSDTC for cross database transactions you'll want to be very careful as it can cause irreparable data corruption in many cases where you use cross db transactions within the same instance.

Perhaps Sean will be able to give you more of the issues you might face if he sees your thread.


where are the infos from 'SHOW CREATE TABLE' coming from? It's not INFORMATION_SCHEMA Quote from MariaDB 10.2 CHECK and DEFAULT clauses:

  Another limitation is that CHECK metadata are not accessible via the
  INFORMATION_SCHEMA. The only way to find out if a table has CHECK
  clauses is parsing the output of SHOW CREATE TABLE.

I also found in MariaDB KB

  Note that SHOW CREATE TABLE is not meant to provide metadata about a
  table. It provides information about how the table was declared, but
  the real table structure could differ a bit. For example, if an index
  has been declared as HASH, the CREATE TABLE statement returned by SHOW
  CREATE TABLE will declare that index as HASH; however, it is possible
  that the index is in fact a BTREE, because the storage engine does not
  support HASH.

So from where does SHOW CREATE TABLE collect its output?

Ever since long, long, ago the info has come from the .frm file.  The I_S is relatively new.  In the future (5.8 maybe) the .frm will be replaced by an InnoDB-based table.

CHECK (and a few other things) are syntactically allowed but are not handled anywhere in the code.  (Another example is a DESC index.)  It could be that the .frm contains these strings, but there is no need for such unused information to be replicated in the I_S.


SQL Query, get count() to output null instead of 0 I have the following query, which basically counts the how many orders a customer has done:
SELECT name, count(order_id)
FROM customers
LEFT JOIN  orders
ON customers.name == orders.customername
GROUP BY name;

Output is something like this:
-------------
|adam |    2|
|bob  |    0|

Here bob had no entries in the orders table. So I believe the left join creates a table where adam has his order columns filled but bob does not.
But I want 0 to instead be NULL. Like this:
-------------
|adam |    2|
|bob  | NULL|

How can I do this?

Use NULLIF.
SELECT name, NULLIF(count(order_id), 0)
FROM customers
LEFT JOIN  orders
ON customers.name = orders.customername
GROUP BY name;


Skip multiple rules during SQL Server installation I want to install SQL Server 2008, but I get 2 errors which prevent me from going forward.  I know from this post how to skip one rule. Is there a method to skip both?

I am using Wine (PayOnLinux) on Linux and simulated an environment with help of this post. I managed to skip first rule, but I also need to skip the PowerShell rule. I am just interested to know if the below syntax can be altered to skip 2 rules:

setup.exe /ACTION=install /SKIPRULES=PerfMonCounterNotCorruptedCheck

Yes, just separate the rules with a space, for example:

/SkipRules=PerfMonCounterNotCorruptedCheck FacetPowerShellCheck

While SkipRules is undocumented, the list of rules is documented at:

Edition Upgrade Rules

Even more information at:

SQL 2012 Setup Rules – The 'Missing Reference'


ORDER BY in WHERE clause I am filtering my data with:

$sel_query="Select * from details where account_id=".$_SESSION['admin_id'];

I want to display records in descending order by using query:

$sel_query="Select * from details where account_id=".$_SESSION['admin_id']
ORDER BY time DESC;

Both queries working separately. When I write two conditions, the query is not working.
What do I need to change here?

It looks like your PHP is invalid. Shouldn't the second line be more like?

$sel_query="Select * from details where account_id=".$_SESSION['admin_id']." ORDER BY time DESC";


How can I make a host show up by hostname in 'show processlist' rather than by IP address? I thought this might be something controlled via RDNS, but I updated that and am still getting connections for a couple machines showing in 'show processlist' as the IP address (and port) rather than hostname and port. I'm wondering how/where this is controlled.  2 out of about 20 machines are showing by their IP address.

By default, MySQL makes a DNS lookup to resolve the client hostnames. You can disable this lookup with the option skip_name_resolve, check if your server wasn't started with this option:

[22:54:53] [DEV\(none)] mysql> show variables like "skip_name_resolve";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

Best Regards,

Max.


Does pg_dump flag -j give performance improvement up to -j 16 or -j 32 or even -j 64? I am backing up and restoring a large DB (7+TB) ... I am using

pg_dump -j 8 -Fd

(8 parallel dump threads with directory formatting).

I just wondered if anybody has tested with a higher -j value like 16 or 32 or even 64?

Please advise ... Thanks much!

You will probably not see a performance increase.

"pg_dump" only dumps multiple tables in parallel.  It does not parallelize the dumping of data for any single table.  So the entire process will always take at least as long as it takes to dump the single slowest table, no matter how much parallelism you throw at it.  Most applications are dominated by a handful of large tables, so won't see much increase to assign more jobs than the number of large tables + 1.

And beyond that, you would need enough CPU and IO.  '-Fd' uses compression by default, and that compression is often the bottleneck (on the CPU) rather than IO.  But if you have enough large tables and enough cores, then IO would probably become the bottleneck.


Does an Oracle user have all privileges in its schema? I'm learning Oracle and did some testing. I used the sys user and created a table t in the scott schema and I found t's owner is scott, and scott has full access to t. I want to confirm, does scott always have full access in the schema scott? No matter who created these objects?

SQL Language Reference

  Prerequisites

  For you to select data from a table, materialized view, analytic view, or hierarchy, the object must be in your own schema or you must have the READ or SELECT privilege on the table, materialized view, analytic view, or hierarchy.

but

  To issue an Oracle Flashback Query using the flashback_query_clause, you must have the READ or SELECT privilege on the objects in the select list. In addition, either you must have FLASHBACK object privilege on the objects in the select list, or you must have FLASHBACK ANY TABLE system privilege.

So for a simple select it is sufficient to own the objects. But if you want to make a Flashback query on one of your tables, you need additional flashback-related privileges.

Also you have UPDATE, INSERT and DELETE privileges on the tables of your schema.

Details you need to execute a statement you can find in the prerequisite sevtion at the beginning of the chapter that describes a statement in this manual.


Bad query plan only on first execution, not in SSMS We have a very customizable report query system where the user can pick and chose which columns to include in the results as well as a lot of filters.

I'll be mapping what we're doing onto an example orders system.  Customers, orders, line items of orders and the like.  For purposes of the example problem also assume we've got a very free-form chunk of customer information like "wife's name", "anniversary", "names of pets", etc (stuff you might want around to personalize interactions with the customer)

To avoid massive denormalization of the order details in our orders report (when they select that output), we're populating a DataSet with 2 queries - the first query is for the orders that meet the filter criteria; the second is for the order details that match the output from the first query.

Our qa dept recently started running into some problems in the C# middleware because the output of the 2 queries is disjoint, even though the same filter criteria are used in both queries.  Both queries start by asking for the top 20 orders that match the filter criteria.

Further complicating things, the qa automation suite does a lot of database restores, setting a few data conditions, running a test and then doing that again for the next test case.  After a lot of back-and-forth, we found that they are only having the problem the first time this query is run after a restore.  Every execution after the first one (with the same query) produces the correct results.

I even got their seed database and started doing the restores myself.  If I took the SQL code and ran it in SSMS immediately after the restore, I couldn't reproduce the problem.  I can only reproduce it if I run through the app code and ADO.NET to run the query.

I used Profiler to capture the query plan on that first execution and for reasons I can't fathom, only on that first execution it completely upends the query and starts by looking for that free-form blob of pet names as the seed kernel of the results.  e.g.

SELECT TOP (20) bunch of configurable columns, xml blob of customer preferences, ...
FROM
ORDERS
INNER JOIN CUSTOMERS ON CUSTOMERS.ID = ORDERS.CUSTOMERID
INNER JOIN
    (SELECT sums, avgs, etc FROM ORDERDETAILS
     GROUP BY ORDERDETAILS.ORDERID) orderavg on orderavg.ORDERID = ORDERS.ORDERID
... whatever other joins needed based on configuration ...
INNER JOIN CUST_PREFS ON CUSTOMERS.ID = CUST_PREFS.CUSTOMERID
WHERE ORDERS.DATE BETWEEN @from and @to
ORDER BY ORDERS.DATE

SELECT ORDERID, orderdetails
FROM
    (SELECT TOP(20) ORDERID
     FROM ORDERS
     WHERE ORDERS.DATE BETWEEN @from and @to
     ORDER BY ORDERS.DATE) ORDERS
INNER JOIN ORDERDETAILS ON ORDERDETAILS.ORDERID = ORDERS.ORDERID

Seemed straightforward enough - start from the orders table and work your way down.

But for this case QA ran into, only on the first execution after a db restore, and only through ADO.NET (not SSMS), the query plan comes out starting the execution with the CUST_PREFS "find me the random customer notes blob" join (the 7th join in the query).  Re-run the same query in the app again, and it goes back to starting with the root table (ORDERS).

A) I'm at a loss to figure out why it decides the 7th join is the right place to start immediately after a restore.  The stats don't bear it out and it goes back to something more understandable on the 2nd execution.

B) I could try to encapsulate the core filter query in a subselect (like the 2nd query has) to make the filter evaluation more predictable and only join for detail after the right subset is selected, but that seems a lot of reshuffling.

C) I'm debating adding an OPTION (FORCE ORDER) hint to the first query to try and make this more predictable, but I thought I'd toss this out amongst a group with deeper knowledge than I before pulling on that thread.

Any pointers from those wiser than me would be appreciated.

When application is run for the first time of day,then it is usually slow,so problem may be in Middleware logic also.

Like you said you are using Dataset,if your were using Generic collection like LIST and populate them with SqlDataReader,then you can see the performance improvement your self.

Second thing is How you are using those 2 ResultSet ?Are the subject to further filter between datatable. ?

As far as your query is visible, I see that you are querying Order Table twice using same predicate and Order by clause

your query can be rewritten like this,

Create TABLE #tempOrder(all required columns)
insert into #tempOrder
SELECT TOP (20) bunch of configurable columns, xml blob of customer preferences, ...
FROM ORDERS
INNER JOIN CUSTOMERS ON CUSTOMERS.ID = ORDERS.CUSTOMERID
INNER JOIN CUST_PREFS ON CUSTOMERS.ID = CUST_PREFS.CUSTOMERID
WHERE ORDERS.DATE BETWEEN @from and @to
ORDER BY ORDERS.DATE

SELECT bunch of configurable columns, xml blob of customer preferences, ...
FROM
#tempOrder ORDERS
INNER JOIN
    (SELECT sums, avgs, etc FROM ORDERDETAILS
     GROUP BY ORDERDETAILS.ORDERID) orderavg on orderavg.ORDERID = ORDERS.ORDERID
... whatever other joins needed based on configuration ...

SELECT ORDERID, orderdetails
FROM
    (SELECT ORDERID
     FROM #tempOrder ORDERS
INNER JOIN ORDERDETAILS ON ORDERDETAILS.ORDERID = ORDERS.ORDERID

Create Non Clustered index on ORDERS(DATE)

There is no point in creating index on #tempOrder since it will contain only 20 records always(Top 20 indicate)

  bunch of configurable columns

It means that query must be dynamic so it all depend upon your real query.

Use SET NOCOUNT ON


Add value to a column in a while loop in SQL Server I have a stored procedure which looks like that :

ALTER PROCEDURE psu_auto_segment_folder_create

@Folder VARCHAR(MAX), /* for example : 'folder1/folder2/folder3' */
@ListId INT

AS BEGIN

SET NOCOUNT ON

DECLARE @FolderName VARCHAR(2048)
DECLARE @SQL VARCHAR(MAX)
DECLARE @ServerIp VARCHAR (15)

SELECT @ServerIp=SQL_IpAddress FROM xxxxxxxxx
(NOLOCK)
INNER JOIN xxxxxxxxx (NOLOCK)  ON xxxxxxx.Serverid = xxxxxx.Serverid
WHERE xxxx = @ListId AND Enable = 1

IF OBJECT_ID ('tempdb..#Folder') IS NOT NULL DROP TABLE #Folder
CREATE TABLE #Folder (ID INT IDENTITY(1,1) PRIMARY KEY, FolderName VARCHAR(MAX), Rank VARCHAR(256) NULL, Status INT NOT NULL DEFAULT(-1))

INSERT INTO #Folder (FolderName)
SELECT VALUE FROM dbo.f_Split(@Folder, '/')

WHILE (SELECT COUNT(*) FROM #Folder WHERE Status = -1) > 0
BEGIN

    SELECT @FolderName = FolderName
    FROM #Folder
    WHERE ID IN(SELECT MIN(ID) FROM #Folder WHERE Status = -1)

    SET @SQL = '
                DECLARE @IdBranch INT
                DECLARE @Rank VARCHAR(256)

                IF (SELECT COUNT (*) FROM [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxxxx WHERE Name = ''' + @FolderName + ''' AND objectid = 0) = 0
                BEGIN

                    IF OBJECT_ID (''tempdb..#IdBranch'') IS NOT NULL DROP TABLE #IdBranch
                    CREATE TABLE #IdBranch (IdBranch INT)

                    IF (SELECT COUNT(*) FROM #Folder
                    INSERT INTO #IdBranch (IdBranch)
                    EXEC [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxxx @Folder_name = ''' + @FolderName + ''', @Rang = '''', @Table = ''xxxx'' /* This stored procedure give me an ID in return */

                    SELECT @IdBranch = IdBranch FROM #IdBranch

                    SELECT @Rank = rank FROM [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxx WHERE idbranch = @IdBranch

                    UPDATE #Folder
                    SET Rank = @Rank
                    WHERE FolderName = ''' + @FolderName + '''

                    SELECT * FROM #Folder

                END
                ELSE
                BEGIN
                    PRINT ''error''
                END
    '

    EXEC(@SQL)

    UPDATE #Folder
    SET Status = 1
    WHERE FolderName = @FolderName

END

SELECT * FROM #Folder

SET NOCOUNT OFF

END
GO

Now if i run the stored procedure i will get something like

Id | Foldername | rank | status
--------------------------------
1  | folder 1   | 1    | 1
2  | folder 2   | 2    | 1
3  | folder 3   | 3    | 1

But i want the result to be

Id | Foldername | rank | status
--------------------------------
1  | folder 1   | 1    | 1
2  | folder 2   | 12   | 1
3  | folder 3   | 123  | 1

Like folder / subfolder / sub-sub-folder

thanks for your help !

Here is an example of how using recursion might solve your problem.

Assuming that my demo table variable @Folder is equal to what comes our of your #folder at the very bottom of your procedure:

--demo setup
declare @Folder Table (
  Id INTEGER,
  Foldername VARCHAR(8),
  rank INTEGER,
  status INTEGER
);

INSERT INTO @Folder
  (Id, Foldername, rank, status)
VALUES
  ('1', 'folder 1', '1', '1'),
  ('2', 'folder 2', '2', '1'),
  ('3', 'folder 3', '3', '1');

--the solution
;
WITH x
AS (
    SELECT id
        ,Foldername
        ,convert(VARCHAR(10), rank) AS rank
        ,STATUS
    FROM @Folder
    WHERE id = 1

    UNION ALL

    SELECT y.id
        ,y.Foldername
        ,convert(VARCHAR(10), convert(VARCHAR(10), x.rank) + convert(VARCHAR(10), y.rank)) AS rank
        ,y.STATUS
    FROM x
    INNER JOIN @Folder AS y
        ON y.Id = x.Id + 1
    )
SELECT id
    ,Foldername
    ,rank
    ,STATUS
FROM x
ORDER BY id
OPTION (MAXRECURSION 10000);

| id | Foldername | rank | STATUS |
|----|------------|------|--------|
| 1  | folder 1   | 1    | 1      |
| 2  | folder 2   | 12   | 1      |
| 3  | folder 3   | 123  | 1      |


PostgreSQL nondeterministic collations are not supported for LIKE I am using Postgresql v12.
I created a collation like this:

CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false);

I used that collation in a table:

create table testtable1 (
    id serial  primary key,
    name text  COLLATE "ci"
);

And I inserted sample data:

insert into testtable1 values(3,'abc');

When I query that table using LIKE, it returns the following error:

select name from testtable1 WHERE name LIKE '%a%'

  ERROR:  nondeterministic collations are not supported for LIKE
  SQL state: 0A000

But I need to use LIKE. Is there any way to allow this?

Assuming that most of the time the custom, non-deterministic collation works for you and you only need something deterministic occasionally, then you can simply provide the deterministic collation when needed via the COLLATE clause. For example, in your case, you can use the tr-TR-x-icu collation as follows:

select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr-TR-x-icu"

You can see a working demo of this, using your sample code posted in the question, on db<>fiddle.

Of course, that will be case-sensitive. For this you need to use the ILIKE operator which is case-insensitive. For example:

select name from testtable1 WHERE name ILIKE '%A%' COLLATE "tr-TR-x-icu"

But, the = operator is still case-sensitive because the collation as defined in the CREATE COLLATION statement did not alter the default sensitivities, and by default collations are everything-sensitive (which is not the same as binary collations, just FYI).

To create a collation that uses the "tr-TR" locale and is also case-insensitive, you need to specify the locale property in the CREATE COLLATION statement as follows:

locale = 'tr-TR-u-ks-level2'

To see that in action, please see: db<>fiddle example 2

Please see "Unicode Technical Standard #35: UNICODE LOCALE DATA MARKUP LANGUAGE (LDML), PART 5: COLLATION" for the full list of collation options.

And, please visit Collations Info for collation-related info. It's geared mostly towards SQL Server and .NET, but has some MySQL and PostgreSQL stuff, and the key concepts are generally the same across platforms and languages.


Migrate Oracle 12c to 18c in AWS RDS Currently we have an Amazon RDS Oracle 12c instance and we need to upgrade it to Oracle 18c in a new RDS instance.

What is the correct approach to do this?

Can we set up an 18c instance staright away and then restore a 12c snapshot or do we need to Set up a new 12c instance and restore a snaphot and then upgrade to 18c?
Can we use AWS DMS to migrate from RDS 12c to RDS 18c?

I was able to do this and it was a very smooth process at the end.

Just took a snapshot of the current 12c RDS instance and restore it. Restoration creates a new 12c RDS instance, but have to be careful about the instance type.

After all I upgraded it to 18c with a one click as they say in the documents.

The schema, users, passwords were all there in the new RDS as the old one. Did not require any kind of Oracle administrator skill.


sql server data classification I am working on a data governance enhancement at my work.  My current task is to use ssms classify data feature.  However, I want to customize the "sensitivity level" for my work (government) we have hipaa, cjis, NFIRS, NFPA, CISSP commercial classification and a few others related to the legal process.  Does anyone know how to customize the list?

Currently,  SSMS has: public, confidential, confidential - GDPR, highly confidential, highly confidential - GDPR, and [n/a].

Thanks for your help

I haven't yet found a way to customize the dropdown list in the GUI, but you can use the Add Sensitivity Classification statement (only in Azure SQL and SQL 2019 so far -- Ugh! Sorry.) to apply any label you want to classify a column.

See this Microsoft documentation for details on the Add Sensitivity Classification statement. But here are the basics:

ADD SENSITIVITY CLASSIFICATION TO
<object_name> [, ...n ]
WITH ( <sensitivity_label_option> [, ...n ] )

<object_name> ::=
{
    [schema_name.]table_name.column_name
}

<sensitivity_label_option> ::=
{
    LABEL = string |
    LABEL_ID = guidOrString |
    INFORMATION_TYPE = string |
    INFORMATION_TYPE_ID = guidOrString
}

Also see this Aaron Bertrand article at MSSQLTips.com.

That being said, even though you have to deal with HIPAA, CJIS, NFIRS, NFPA, and CISSP, and they might not use exactly the same terms as each other, you should still be able to map the sensitivities to the built-in labels. Customizing the labels might work for now, but you'll have to do extra work to maintain that customization, making sure it is consistent across tables, database, and instances, and documenting it well.

I recommend you work with your auditing and compliance folks to reach a mutual understanding of what each sensitivity label means to your organization, and how to map that to the various regulatory expectations.


Set statistics time showing different elapsed_time than dm_exec_query_stats view When I exec a SQL statement after setting set statistics time on and running dbcc freeproccache and dbcc dropcleanbuffers I get the following output:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 17 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 796 ms, elapsed time = 891 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(0 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 9 ms.

SQL Server Execution Times:
   CPU time = 796 ms,  elapsed time = 900 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 796 ms,  elapsed time = 917 ms.

I then query the the sys.dm_exec_query_stats table using the following query:

select
    OBJECT_NAME(q.objectid,q.dbid) AS ProcedureName,
    highest_cpu_queries.creation_time,
    highest_cpu_queries.total_worker_time,
    highest_cpu_queries.total_elapsed_time,
    highest_cpu_queries.last_worker_time,
    BlockTime = highest_cpu_queries.total_elapsed_time - highest_cpu_queries.total_worker_time,
    highest_cpu_queries.execution_count,
    highest_cpu_queries.total_worker_time / highest_cpu_queries.execution_count AS avg_time,
    highest_cpu_queries.min_worker_time,
    highest_cpu_queries.max_worker_time,
    highest_cpu_queries.max_elapsed_time,
    DB_NAME(q.dbid),
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from
    (select top 50
        qs.plan_handle,
        qs.creation_time,
        qs.total_worker_time,
        qs.total_elapsed_time,
        qs.last_worker_time,
        qs.min_worker_time,
        qs.max_worker_time,
        qs.max_elapsed_time,
        qs.execution_count
    from
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

This returns 1 row as expected (since the dbcc commands reset the view). The total_elapsed_time column shows 8830 (946 total_worker_time + 7884 blocktime).

Total_elapsed_time according to msdn is in microseconds but only accurate to milliseconds.

So this should be 8-9ms. Set Statistics time above is showing 917ms.

My understanding was the dm_exec_query_stats view captures the same numbers as Set Statistics Time. Why are the elapsed times reported different?

What version of SQL Server are you using?  This is/was a known issue and was fixed in SQL 2008 SP1 but wasn't ported back to SQL 2005.

http://connect.microsoft.com/SQLServer/feedback/details/487838/cpu-time-sys-dm-exec-query-stats-vs-set-statistics-time-on


Parallel option performing worse on cluster I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not worse results. I am wondering why this is the case, and if there is anything wrong with the way I am using the parallel option on my cluster.

I am using a degree of 4 when the number of CPU's I have is 8. I have tried adding parallel to the cluster directly ALTER CLUSTER cluster PARALLEL 4 as well as at the statement on both the index /*+ PARALLEL_INDEX(clust_index, 4) */and tables /*+ PARALLEL(4) */,

Here is my trace from the parallel:

Without parallel:

If you want to run in parallel you have to design for it. Things to consider is using partitions so they can be scanned in parallel. Don't forget that parallelism in general will try to use full scans on segments. Partition pruning can help reducing the size of your scans.

If you really are using pq for this sort of small queries, make sure you have enough PQ slaves ready for use. Otherwise, the starting of new slaves is added as overhead to your query. Not that bad for dss type queries that run for hours but deadly for short running queries.

Also think about using queuing your PQ queries to guarantee that when a query starts, it has all the slaves it needs. This can be done in Oracle Resource Manager.


Distributed Always-On without Domain We have a requirement to configure a distributed availability group from Data center1 to Data Center2 using SQL Server 2017 Enterprise Edition.

DC1 (two nodes WSFC setup): SQLDB1, SQLDB2

DC2 (two nodes WSFC setup): SQLDB3, SQLDB4

The servers are in workgroups, not in a domain. Is it possible to configure distributed availability groups from DC1 to DC2 without domain?

As per my understanding, using certificates it's possible to configure Distributed SQL always-on if all four nodes are in same cluster. Is my understanding is correct?

Please guide me.

The documentation at Distributed availability groups states:

  The individual WSFC clusters and their corresponding availability groups follow traditional rules. That is, they can be joined to a domain or not joined to a domain (Windows Server 2016 or later).

Regarding your question about all four nodes being in the same cluster--if they are in the same cluster, then there would be no need to have a distributed availability group (DAG), you would just use an availability group with four replicas.  Additionally, the documentation referenced above states:

  A distributed availability group spans multiple availability groups, each on its own underlying WSFC cluster.

So if it is possible to set up a DAG with all nodes in one cluster, it is contrary to the documentation and may not be supported.


Database Over Normalized How can you tell when a database is over normalized? Example: I have a database for employee. These are the following tables involved:

User - contains email, password, companyId
Profile - profile of the employee (first name, last name, address)
Group - Department list
Company - list of companies
Position - List of positions
Employee - employee information like id numbers, height, weight, contract info, etc.

First, if it ain't broke, don't fix it. If no-one is complaining about the speed of the system then leave it alone. Spend time and money on other things.

Second, no, I don't think it is possible to over-normalise. That is, I think it is possible to spend more time on the normalisation process than your application might need, but unless you've spent tens and tens and tens of hours doing so I don't think the time is really wasted. The time getting to 3rd normal form is most certainly never wasted.

In my (admittedly limited) experience, the problem is usually under-normalisation, otherwise known as "laziness". Database designers often claim it is for speed purposes, throwing the term "denormalised" about like a holy mantra, but if you investigate closely, all they've actually done is half a job.

Denormalisation is not "stopping before you reach a certain normal form"; it is "reaching a certain form (usually 3NF or BCNF) and then undoing some of that normalisation for well-documented, well-benchmarked reasons."

Links

Tony Davis, The myth of over-normalization.

Louis Davidson, Can you over-normalize?.


Rewrite SQL query in CTE query I have this table which I would like to store a chain of records.

CREATE TABLE table_name (
    id INT,
    unique_id varchar,
    reference_id varchar,
);

I want to implement SQL query for MariDB which prints all records by id with all records with reference_id. Something like this:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |
| 99 | 454       | 33           |   |   |

I would like when I select record 66 to get all up and down transactions because each other are using id which points to them. How I can implement this using Recursive CTE? Is there a better way?

Expected result for record with unique_id 66:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |

This SQL query works:

select t.id, t.unique_id, @uid := t.reference_id reference_id
from (select * from mytable order by id desc) t
join (select @uid := 66) tmp
where t.id = @uid or reference_id=66

https://www.db-fiddle.com/f/33mbBmQRiF5Z8eRoCR3oco/0

But when I tried this CTE query I can't get the above rows.

with recursive cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on cte.id = t.reference_id
     )
select *
from cte;

https://www.db-fiddle.com/f/ppmxX12PJ1rhszp85nJcWp/0

Can you give me a hand how to rewrite the SQL query properly, please?

You have to choose a direction when using the recursive CTE. You could add another recursive CTE to account for the descending reference_id's

with recursive
cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on t.id = cte.reference_id
     ),
 cte2 as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on cte.id = t.reference_id
     )
SELECT *
FROM cte
UNION
SELECT *
FROM cte2
ORDER BY id ASC;

Result

| id  | unique_id | reference_id |
| --- | --------- | ------------ |
| 43  | 55544     |              |
| 45  | 45454     | 43           |
| 66  | 55655     | 45           |
| 78  | 88877     | 66           |

DB-Fiddle

EDIT:

added version with UNION ALL

with recursive
cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on t.id = cte.reference_id
     ),
 cte2 as (
      select t.*
      from mytable t
      where t.reference_id = 66
      union all
      select t.*
      from cte2 join
           mytable t
           on cte2.id = t.reference_id
     )
SELECT *
FROM cte
UNION ALL
SELECT  *
FROM cte2
ORDER BY id ASC;

DB-Fiddle


Improving query time for auto complete box with millions of record I have a very basic requirement. I have a table that store city id (primary key) and city name (nvarchar value). The remaining information is stored in a separate table. In my UI, I have an auto complete box, that lists all cities when user start typing. After selection, application gets the data for that particular city to fill up.

The main table with just two columns for city id and name, is indexed on name. I am currently running following query to get the list of names.

Select cityname from citylist where cityname like '%' + citynamevar + '%'

This query is supposed to give me the list of all names that contain the value typed by user. It currently takes about 2 minutes to run on approx. 600k records.

I have also added a way to display 10 records in the beginning, while the query executes. But even then, it is not helping the case as remaining data loads way too slow.

I am trying to find ways I can improve this query time. Till now I have found about full text search, which I believe should help. I was just wondering if there is any other method I can try or any trick to get the query to respond fast enough.

I am currently using SQL server 2012. I tried running the query directly outside the application, using ssms too. And even then it took the same amount of time. Indexing sped up the query by about 20% but not enough to be meaningful.

Edit:

No idea why I am not able to comment anymore on this post. Anyway, it is a test build to see how much impact auto complete boxes will have. It is running on my personal laptop (core i5, basic mobile GPU). I am using SQL server 2012, .net 4.5 with wpf.

I can ask user to reduce the search space by entering country name. But this is supposed to be a generic solution. In some cases, we don't have any way to reduce the search space. And it has to be a "contains" search, instead of "starts with". Which might be the reason why indexing hardly had any impact.

The table is just 600k records, out of which we need to display 1000-10k. The query is executed only once when user type the third character, after which remaining characters are sorted within the application from that 1000-10k records. I can reduce the impact by increasing the character count to 5, but then user will be unable to see any suggestions for most city names.

Have you thought about restricting the search in the UI?  Don't start the autocomplete query until after they've typed 3 or 4 characters. Then instead of looking for 6000 A*, the first jump would be a fraction of that number still.

Also, @Jonathan Fite 's comment about removing the leading % is also a good way to reduce time.  Unless, your users start looking for ew Yor, or shington...


Best value for fill factor in index I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also how I can meter query performance by set deference value to this property.

Thanks a lot in advance.

There is no one answer for all with fill factor.  It largely depends on how your data modifications take place database-wide or in particular indexes.  The default, out-of-the-box is 0 (100) which leaves virtually no space in a page.  What this means is an insert into the middle of a page will cause a page split.

Monitor your Page Splits/sec counter and if you are seeing a high rate, then you may want to reconsider your fill factor.  But don't just blindly set that low, as you will be using more disk space with a lower fill factor.  And naturally, it will cause higher IO reads, as there is more pages to house the data.

I recommend you look at my answer here regarding fill factor.  It's a different type question, but it should give you a better idea about fill factor and how it affects you.


table size seems high for the amount of records in db I have a simple table

and I have blanked out the twitterdetail and rawsource and set it as null so there is no data in them, however the space for that table is 350mb.

There is 67k records so if one does math on the filled fields, it should not be that high.

Am I missing something?

It's hard to say anything definitive without knowing how the table was defined or what you did to "blank out" the columns, but NULLs still take up space and you still have an NVARCHAR(MAX) MatchedKeywords column which could be taking up plenty of space.

Sometimes tables can change in size after a REBUILD. If you feel that the table is taking up too much space right now I suggest trying that.


Stored procedure with dynamic sql. Is it isolation safe by default? I would appreciate confirmation on the answer I found digging through T-SQL's documentation.

I have a stored procedure that references a view twice.  Once in the procedure and then a second time when executing the dynamic sql statement.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM CourseSalesView) AS Courses

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning)
          FOR Course IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

My database has SQL Server's default isolation level of 'ReadCommited'.  If something else changes my view in-between my procedure and sp_executesql will I have an concurrency problem?  I.E.  Could a row being removed in my view by another user at the exact right time cause a mismatch between this procedure's two statements?

I am under the assumption that the 'ReadCommited' isolation level prevents this from happening and that I do not need to explicitly declare the isolation level anywhere in my Stored Procedure as it is inherited from the default setting.

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, HOLDLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

So the following should work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning)
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;

Beyond that basic interaction, there are two other options:

Storing the results of the View into a local Temp Table (i.e. #TableName). This has the benefit of not locking the underlying table(s) of the View, but it will have to write the entire results of the view into tempdb and that does not sound like a scalable solution if the View returns 10k rows or more.
Enable SNAPSHOT ISOLATION (introduced in SQL Server 2005) which handles this for you without needing to explicitly lock the table:

Snapshot Isolation in SQL Server
ALTER DATABASE SET Options
You will still need to wrap the two queries in an explicit Transaction, but no need for the Table Hint
If you don't enable READ_COMMITTED_SNAPSHOT then you will need to issue SET TRANSACTION ISOLATION LEVEL SNAPSHOT before the BEGIN TRAN


Encapsulate table with view I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive so I need to solve it on the database level.

My idea is to create a new view which will encapsulate the table and just change the order of columns. Current table will be renamed and new view will have the original name. I found here that encapsulating a table with a view is some kind of database design pattern.

Here are my questions:

Do I lose performance when I encapsulate a table with a view and call the view only?
Do you see any risk with naming the view (there will not be V_ prefix)?
Do you see any other advantage / disadvantage in encapsulating a table with a view?

Well,

A view increases the overhead but if the view only contains a select with a different column ordering, the overhead will not be noticeable.
You can also rebuild the table with the column order you want (a rather expensive one time operation)
Most people agrees that views should follow the same naming standard as tables (i.e. no V_, or any other, special prefix)

Having said that, software should almost never do selects or inserts, without specifying the columns.


How to create and populate a PostgreSQL table from CSV automatically? I am new to database management and we are using PostgreSQL. All I need to do is to migrate CSV files (corresponding to around 200 tables) to our database. Manually creating tables for every CSVfile is a bit tiresome, so please help me out. Is there any way not only to import data, but also to generate tables from CSV files?

You might look at PgFutter to help you out. It sounds like it would fit your use case well, because it creates tables for CSV formatted data based on the header line, and then loads the data into the tables.


Amount of data in a database table I am trying to run an alter query on a table, and its giving me a

Lost connection to MySQL server during query
MySQL server has gone away.

The query that I am running is

 alter table table_name modify column_name DOUBLE ( 16, 2) default 8.5;

I am guessing that the table contains a lot of data, and hence the alter is taking a lot of time, and the server connection is lost.

My query is,

Firstly,

How do I find out the amount of data present in the table ? ( in MB/GB i suppose).

Secondly,
 In case the table does contain a huge amount of data, then how do I perform the alter on this table ?

You need to check the server's error log(s).  There are too many situations that could cause this problem.  For example:

The table is corrupt, and InnoDB detects this while reading an invalid page checksum.  The default behavior here is to shutdown to force an administrator to intervene.
Your server is over configured to use way too much memory.  The OOM killer terminates mysqld.
There is no problem at all.  The client disconnects, but the server keeps running the ALTER statement, and it eventually finishes.


heroku postgres sql changes not reflected in db I wrote this program

import psycopg2

try:
    #connect to db

    cursor = connection.cursor()

except Exception as e:
    raise e
else:
    print("connection Success")
cmd = ""
while(True):
    cmd= input("Enter cmd")
    if cmd == "CLOSE":
        break
    try:

        cursor.execute(cmd)
        pass
    except Exception as e:
        print("Err")
        print(e)
        pass
    else:
        record = ""
        try:
            record = cursor.fetchall()
        except Exception as e:

            print(e)

        print("Success ")
        print(record)

        pass
    finally:
        pass

cursor.close()
connection.close()
print("all close")

Weirdly, it worked fine yesterday for sometime.
However now changes are not reflected in db (when checked through heroku dataclip) but can be seen through python app before closing the session.Only .
I an sure I haven't crossed any limit of the hobby dev plan.

Curious enough the primary key still increments.
Output example-

this query(insert into info(name,nickname,ismetro) values('abc', 'xyx','n') worked a while ago.

You're never committing your transaction. Add connection.commit() before you call cursor.close(). Docs here.


Postgres REINDEX time estimate I've got an older DB (postgres 10.15) that's not yet been upgraded. One problematic table had a few large indexes on it, some of which were corrupt and needed reindexing. Since it's not on version 12+, I can't concurrently reindex the table (which means I need to do it non-concurrently, which requires a table write lock) - so I wanted to know how I could do some rough calculations on how long the reindex would take so I can plan some maintenance. Most of my research ends up in the "just use pg_stat_progress_create_index! (which isn't available in 10), or people just saying to use CONCURRENTLY.
The table is ~200GB, and there are indexes are 7 indexes which are 14GB each (as per pg_relation_size). I can get ~900M/s constant read-rate on the DB for this task. Is there a simple metric I can use to determine how much data will be required to be read to reindex fully?

You could just create new index with different name by
create index concurrently index_new on ...

Then drop corrupted index with
drop index concurrently index_old;

Then you could rename new index to old name:
alter index index_new rename to index_old;

Latter will require lock, but for few milliseconds of runtime after acquire the lock. So you do not need downtime due to write lock.
The definition of the index can be obtained from the command pg_dump -s -t tablename --no-acl
This is exactly the same procedure that does reindex concurrently under the hood. But reindex concurrently is a bit cheaper since do not need lock for index rename phase.

Also widely known pg_repack has feature to reindex table with option --only-indexes. This option is implemented as create + drop index concurrently.

Is there a simple metric I can use to determine how much data will be required to be read to reindex fully?

Well, any index creation without concurrently will read the entire table sequentially (concurrently will read the table twice). Something else depends on access method. Btree will sort all live tuples. This is the most time-consumption part of create index, for large indexes the work will be done in temporary files (remember increase maintenance_work_mem). This part also depends on datatypes and values. Text with small selectivity (e.g. some status field) will be noticeable slower to build than integer sequences.
I have no way to estimate, except for one: to measure the creation time of an index on some data sample:
create table estimate_table as (
  select * from tablename
  where created_at > '2020-01-01'
);
\dt+ estimate_table
\timing on
create index on estimate_table ...

Reindex is just a special form of index creation. Hmm, and an important point: reindex table has no difference with several reindex index in terms of resourse usage. reindex table is implemented by calling reindex_index for each individual index on table. So, table with 5 indexes will be scanned 5 times.


What should I set my table_definition_cache to? I just discovered table_definition_cache and I am trying to decide what to set it to. I am messing with my config because of performance issues.

On one server I have 36599 tables and when I run SHOW GLOBAL STATUS the value for Opened tables is 930312. table_definition_cache is set to 20k.

On another server I have 45349 tables and when I run SHOW GLOBAL STATUS the value for Opened tables is 94383. table_definition_cache is set to 40k.

I am not sure what to set it to table_definition_cache value to because it seems like the server doing a lot of opening / swapping.

Both servers are CentOS 6 and running

Server version: 5.6.32-78.0 Percona Server (GPL), Release 78.0, Revision 8a8e016

Thank you for looking! I appreciate your feedback greatly.

I hope this links can help you to decide your fine tunning. A little later my answer, sorry about.
I could resolve some problems in 5.5 MySQL version using this information.
Here: you can check how to calculate your table_open_cache

Find current value of open_tables and opened_tables

mysql> show global status  like 'open%';

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable.  If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.

Find out Table cache hit rate

Table cache hit rate = table_open_cache*100/Opened_tables

In general it should be more than 50%. So you need to increase value of table_open_cache, if you are under this value, though there are lots of reasons to have a high value of Opened_tables. Like FLUSH TABLES will close all open tables and reopen it which significantly increases Opened_tables value.

Calculate  the tune value of table_open_cache and set it

Table_open_cache = total_tables*Threads_connected

As all the threads (user) are not generally access all tables. I think you should set 50% of the value calculated.  Because too big value of this variable has some other side effects.  So the formula becomes

Table_open_cache = total_tables*Threads_connected*.50

Along with table_open_cache you should also tune open_files_limit system variable.

In general it is 2x of  table_open_cache.
open_files_limit= Table_open_cache*2

open_files_limit is not a dynamic variable. So you should set it in my.cnf file and restart MySQL.
Make sure that your operating system can cope with the number of open file descriptors required by the table_open_cache setting.

Ref. https://techinfobest.com/optimize-mysql-table_open_cache/
And here: MySQL Documentation give us the formula to set table_definition_cache based on table_open_cache size. (yes, I know, the link is for 5.7 version, but in 5.5 doc version there isn't any mention about the formula), so you need first define the size of table_open_cache, this is the reason why I give the link above.
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_definition_cache
Next, You must monitoring the status of COM_STMT_REPREPARE and Open_table_definitions to see what is happening in the server.
If you have applications that are using prepared statements and getting the message error code [1615]Prepared statement needs to be re-prepared, is a signal that MySQL is flushing that cached definition tables..
(https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_need_reprepare)
At last: Information about why MySQL is reprepared stmt
https://dev.mysql.com/doc/refman/5.5/en/statement-repreparation.html


SqlPackage does not pick up variables from profile I want to upgrade a database using .dacpac and sqlpackage.exe

here is how I run sqlpackage:

SqlPackage.exe
    /Action:Publish
    /SourceFile:"my.dacpac"
    /Profile:"myprofile.publish.xml"

The error I get is:

* The following SqlCmd variables are not defined in the target scripts: foo.

I have verified that myprofile.publish.xml file does contain that var:

<ItemGroup>
  <SqlCmdVariable Include="foo">
    <Value>bc\local</Value>
  </SqlCmdVariable>

I also verified that project that creates dacpac does publish successfully from within visual studio using myprofile.publish.xml

What else could I be missing?

(I'm using SQL Server 2012)

Look at the references in your database solution. You will probably see them there. You can use the SQLPackage Script action to see the variables actually included in your dacpac.


SSISDB master key in an Availability Group I activated Always On support for SSIS and everything works fine until a fail over operation happens. Turns out the secondary nodes have a different service master key than the primary node and after each fail over I need to run this at the new primary node:
USE SSISDB
GO
open master key decryption by password = 'secretpassword'
Alter Master Key Add encryption by Service Master Key

I've thought about including this steps as part of the "SSIS Failover Monitor Job" to check if the node has changed to primary and then alter the DMK but I wouldn't want to leave the password explicit.
Does anybody have an idea how to deal with this?

We had similar case where the DMK being used within the DB to protect some columns in the database, and DMK protected by Server Master Key (SMK), this is good as always the DMK is opened as long as the SQL Service running with same SMK, there is no need of password in which case the application need to explicitly OPEN and CLOSE DMK using password.
I agree with:

You should make all the nodes in your Always On Group have the same service master key, then failover would be seamless.  https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql

Having same SMK across the replicas in availability group would be seamless. It may considered at some-point as less secure, well we still maintain the boundary of AG. i.e. the database cannot be functional when it is restored into a instance other than AG replicas, and we need to extend that boundary (one instance to multiple) as we need to compliance to the High Availability.
Consider it like maintaining same SID of logins across Availability Replicas. To make SMK same in all replicas, you can do following (if there are no other things protected already by SMK on secondary):
---- On Primary replica
BACKUP
SERVICE MASTER KEY TO FILE = '\\Sharedfolder\SMK-202005121328.key'
ENCRYPTION BY PASSWORD = '*************';

---- On Secondary replica
RESTORE
SERVICE MASTER KEY FROM FILE = '\\Sharedfolder\SMK-202005121328.key'
DECRYPTION BY PASSWORD = '*************';


Top 2 rows per partition from an absolute value date difference I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images.

Location + Camera + image capture_date is the clustered primary key, and currently the only index on the table. The kicker is searching a single camera takes <1 millisecond from SSMS and ~70ms from my web application. My current working CTE solutions take around 3 minutes for three cameras.

To give an overview of the cameras at a location I need to select 2 images from each camera nearest a given date (such as the current date). Because of this I need an absolute value (dates before or after the search date are equally valid), thus I'm searching by the smallest ABS(@date -capture_date).

Here's the current code. It works but it's not SARGable and it's extremely slow. I also only need the top 2 rows per camera in the CTE, since there may be hundreds of thousands of images per partition.

DECLARE @date datetime,
        @location varchar(4)
SET     @date ='2011-12-13 12:00:00'
SET     @location='CS01';
WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION  BY Camera ORDER BY  abs(datediff(second,@date, [capture_date]))) AS Ranking
    FROM rs_camera_pictures
    WHERE
        location=@location)
SELECT * FROM CTE WHERE Ranking <= 2

This is just a starting point and will likely need some tweaking.

Essentially this will get you the 4 closest capture dates to your specified date (2 closest after and 2 closest before).

You will need to add some logic to your outer select to pick which ones to use, but you will be doing a DATEDIFF on 4 fields instead of all of them.

WITH CTE AS
(
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
WHERE r1.capture_date > @date
AND r2.capture_date < @date
GROUP BY camera
UNION ALL
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
INNER JOIN CTE c
    ON r1.location = c.location
    AND r1.camera = c.camera
WHERE r1.capture_date > c.[After]
AND r2.capture_date < c.Before
GROUP BY camera
)


Searching from Database having billion records, which database should i use We are having 1 billion phone numbers and we have to search them against numbers which we send for promotion to filter out which are not promotional registered. That is we have lets say 1 million record which we need to search against 1 billion database records.

What would be best and optimized way to search these records and which database should we use?

We are currently using SQL Server and in a batch of 10000 searches it gives result in 20 minutes.

We want real time searching.

You could use a parallel system such as Azure SQL DW. This would basically split your data up into sixty parts, and should be able to optimise just as well as your regular SQL database, but spreading the work out across sixty databases. You would still need to make sure that your design was optimal, in regard to table structures and indexing, but you should be able to shrink the size of your problem down by a large factor. Your 20 minutes could become half a minute quite easily.

Of course, there are probably good ways to tune your current system.


Do I need a separate Id column for this "mapping" table? I have a table of Producers and a table of Products, both of which are of the form:

Id - int, Primary key
Name - nvarchar

A Producer can carry multiple Products, so I was going to create a table called ProducerDetails that would have:

ProducerId - int, Foreign key to Producers.Id
ProductId - int, Foreign key to Products.Id

Then I started to question myself, so I thought I'd ask the experts.  Would it be better database design to have an additional Id (int, Primary key) column in my ProducerDetails table?  Or is that unnecessary?

I'm using SQL-Server 2008 R2 if that make any difference at all.

EDIT - The relationship between these tables would be many-to-many I believe, sorry I didn't make that clear.  A producer can carry multiple types of products, and the same product could be produced by multiple different producers.

I apologize if this question is overly simple, referential integrity / database design is not my strongsuit (although I'm trying to improve that).

No, there is no value in adding an additional "primary key" to this table. Your joins are only ever going to refer to ProducerID and ProductID, so it is just dead weight. IMHO.

Though I agree with @Shark that the join table doesn't even seem to be needed here, unless you are going out of your way to not change the schema of the existing tables in any way.

As an aside, I also think it is worthwhile to name your primary identifier in full (e.g. Products.ProductID instead of Products.ID) so that the identifier is consistently named throughout the schema.


MySQL: NOT EXISTS vs LEFT OUTER JOIN...IS NULL Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better:

SELECT table1.id FROM table1 WHERE table1.a='FOO' AND
NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

or

SELECT table1.id FROM table1 LEFT OUTER JOIN table2
ON table1.id = table2.id WHERE table1.a='FOO' AND table2.id IS NULL;

NOT EXISTS performs in most straightforward way: just checks equality and returns TRUE or FALSE on the first hit / miss.
LEFT JOIN / IS NULL either makes an additional table look-up or does not return on the first match and performs more poorly in both cases.


Linked server from MSSQL to Oracle Issue Last week we began having an issue with a Linked Server to an Oracle database on one of our MSSQL Servers. If I use the linked server on the MSSQL server it works fine. However, if i try to use it remotely through SSMS it throws the below error. But, if i log in to the SQL Server remotely with SSMS and an SQL sysadm account it does work. Server is on mixed mode and normally we connect using window credentials.

OLE DB provider "OraOLEDB.Oracle" for linked server "*****" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "*******".

This worked fine up until last week when it just seemed to stop working.

The same server on our DR site with the same linked server works fine.

We have checked the provider settings against a working Linked Server and they are fine. We have rebooted the Server. Checked TNSNAMES is matching and correct. I am now completely out of ideas of what to try next and wondered if anybody out there has had a similar problem and has been able to resolve the issue. I could try to reinstall the Oracle ODAC but trying to avoid this.

Thanks for any help or advice you can give.

Richard

If you're able to select the 'Oracle Provider for OLE DB' option in the "Provider" dropdown, then I'd suggest trying the following (via the GUI):

Product Name: ###.###.#.###:####/SRV1

Data Source: ###.###.#.###:####/SRV1

Where ###.###.#.###:### is your Oracle Instance's IP Address (with standard 1521 or 152# port), and SRV1 is your Service name.

Just make sure the ORACLE user you're using to get in is correct in the "Security" section.

This option allows you to bypass the TNSNames file if you want to confirm that there's a connection (TNSPing is my go to, just like Gerald H. Pille said in the comments above).

Or if you want to go about it via T-SQL:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName'
                             , @srvproduct=N'###.###.#.###:####/SRV1'
                             , @provider=N'OraOLEDB.Oracle'
                             , @datasrc=N'###.###.#.###:####/SRV1'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerName'
                                ,@useself=N'False'
                                ,@locallogin=NULL
                                ,@rmtuser=N'OracleUserHere'
                                ,@rmtpassword='passwordhere'
GO

Then go back in and change your options via the GUI (RPC, RPC Out, etc.)


Design of small tables: clustered index or heap I'm trying to design a database schema to hold an entity that has a couple of one-to-many associated collections.

The challenge here is that the related objects have few properties, and so the tables are "small" (at least in terms of amount of columns). This makes it difficult to make a decision on two fronts:

Do I have a primary key or not
Do I create a clustered index, or make it a heap + have a covering non-clustered index

This is my situation (note - actual entity is replaced with a generic example):

-----------------------             --------------------------
|         User        |             |      UserSituation     |
-----------------------         =>  --------------------------
| + UserId [NOT NULL] |             | + UserId [NOT NULL]    |
-----------------------             | + StartDate [NOT NULL] |
                                    | + EndDate [NULL]       |
                                    | + IsActive [NOT NULL]  |
                                    --------------------------

                                    --------------------------
                                    |      UserCategory      |
                                =>  --------------------------
                                    | + UserId [NOT NULL]    |
                                    | + StartDate [NOT NULL] |
                                    | + EndDate [NULL]       |
                                    | + Category [NOT NULL]  |
                                    --------------------------

Again, I've generalised the example, please don't try to give me advice to re-design the database structure by trying to derive any business meaning behind the fields.

To design the tables, I've reasoned about the following, which is valid for both tables:

A primary key cannot exist because it needs to cover all columns and EndDate is nullable. Still, if that was not the case, would it make sense to have a primary key which is so wide?
A covering non-clustered index can use the RID to lookup table data. This will make leaf nodes smaller, because the alternative is having "UserId + Uniquefier" in all entries. How much does that matter?
A clustered index on UserId -> this means a Uniquefier will be created, so I can't seem to decide whether that would be good or bad.
In any case I have a UNIQUE NONCLUSTERED index over all columns.

I've read Kimberly Tripp's articles on the relevant topics but also stumbled across Marcus Winand's blog post about clustered indexes. He has a rather opposing vision about clustered index than the majority of people, but he seems to make sense all the same.

Other notes:

The data in the tables will not be updated, only deleted/inserted because my app treats the collection entries as immutable.
I'm using SQL Server, and need to support 2008 R2 and up.
The sub-tables will not really be queried, mostly joined to the main entity.
Having read a substantial amount of information on indexes and table design, both here and on blogs, I can't decide on this particular scenario.

As for subtables, I have a rule of always adding a primary key. In this case I would go with a new autoincrement column as a PK, as the index will be pretty narrow.

Secondly, I would consider propagating information like IsActive from UserSituation to User with a trigger (I assume user can only have one situation at the given moment). This may eliminate the need for one JOIN in most cases. Not sure it will work with UserCategory (it will not if it's a true 1:N relation).

As for clustered index on UserId, it sounds like a good idea on table User and a not-so-good idea on subtables. But I have to admit it is not my field of expertise.