Partition an unpartitioned primary key index to become locally partitioned by primary key

You cannot convert a global index into a local index with an ALTER INDEX command. You need to drop the index and recreate it using the LOCAL keyword.


This is happening at parse time, and there is little you can do to change the actual behavior. SQL Server does some checking of the statements within the batch, and for all intents and purposes, pretends that your conditional logic does not exist.

For the same reasons, you can't use conditional logic to determine which of two possible #temp tables to create (the most common symptom for this problem). Even though looking at this as a human, you see that only one version of #t could ever be created, SQL Server doesn't see it that way. It will report the error message whether you choose Parse (Ctrl+F5) or Execute (just F5):

IF (1=1)
BEGIN
  CREATE TABLE #t(a INT);
END
ELSE
BEGIN
  CREATE TABLE #t(b INT);
END

Result in either case:

  Msg 2714, Level 16, State 1, Line 7
  There is already an object named '#t' in the database.

The error message makes very little sense when all you've done is parse the batch; obviously #t can't exist yet, but it demonstrates that SQL Server is evaluating the statements in your batch, but ignoring those statements that deal with conditional logic.

To get around this problem, you can use dynamic SQL, which will not be parsed prior to execution:

IF EXISTS (SELECT NULL FROM sys.databases WHERE [name] = 'DR' AND [state_desc] = 'ONLINE')
BEGIN
    EXEC DR.sys.sp_executesql N'DBCC SHRINKFILE (DR_log, 2048);';
END

However, this seems really, really, really wrong to me. You are constantly shrinking your log file? Why? This is such a self-defeating process. Please read this post in its entirety:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Then, size your log file appropriately - ONCE - and make sure you are using the right recovery model so that you're not constantly playing this grow-shrink-grow-shrink-grow-shrink cycle. If the log is too big for the mirror, get a bigger disk. Doesn't seem right to cripple the primary to compensate for the mirror's inadequacies.


calling mysql procedure return empty result

Basically

'column1' 'column2' 'column3' is different than 'column1,column2,column3'

So, you must compare the whole string:

 BEGIN
IF (col_names  = 'column1,column2,column3')
THEN
.
.
.

If you want to compare each variable separately, then you must pass them separate in your procedure call:

Call:

call hussian('prstate','table_1','column1','column2','column3',1)

Procedure:

CREATE PROCEDURE hussain(IN src_col varchar(20),
                         IN tblname varchar(100),
                         IN col_names1 varchar(100),
                         IN col_names2 varchar(100),
                         IN col_names3 varchar(100),
                         IN _leadid int)


Repairing Broken Binary Replication in PostgreSQL 9.0

Database files and transaction logs tend to be very fat , so compression makes a big difference try rsync -z or compress the data directory beforehand. Try with  xz (former lzma) you wont believe the compression ratio for postgres files.


statistics are up to date, but estimate is incorrect

There is a simple solution to this:

Drop all of the _dta_... statistics and stop blindly applying DTA recommendations.

More information

The particular problem was that there were multiple sets of statistics for the column in question. The extra dta statistics were created by sampling the data (the default behaviour for statistics not associated with an index).

As is often the case with sampled statistics, the resulting histograms did not cover the full range of the unerlying data. The query in the question happened to choose a value that was outside the histogram, resulting in a 1-row estimate.

The exact behaviour of the query optimizer when multiple sets of statistics exist for the same column is not fully documented. It does tend to prefer 'full scan' statistics over sampled, but it also prefers more recently-updated statistics to older ones.


Managing and Removing data from huge table

First off, a couple of articles:

Break large delete operations into chunks
Delayed Durability while Purging Data

You didn't mention the version of SQL Server, so I'm not sure if the second one is relevant to you, but it might be to other readers. I'll focus on the former.

Typically, deleting a lot of data causes a wide array of symptoms:

more rows affected = greater likelihood of lock escalation
longer transactions = lengthy blocking (and sometimes deadlocks, as you've found)
heavy writes to transaction log = write latency (including growth events)

So my idea in that article was to lock fewer rows by deleting in "chunks" - each delete operation (or a finite number of them) would be in a transaction, thereby reducing the number of locked rows, the length of the transaction, and the impact to the log.

So I might suggest trying something like this:

SELECT key_column
  INTO #work
  FROM dbo.big_table
  WHERE -- however you identify rows to delete;

CREATE CLUSTERED INDEX x ON #work(key_column);

DECLARE @rc  int = 1,
  @counter   int = 1,
  @batchsize int = 100; -- this may or may not be an optimal chunk size

WHILE @rc > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (@batchsize) t
    FROM dbo.big_table AS t
    INNER JOIN #work AS x
    ON t.key_column = x.key_column;

  SET @rc = @@ROWCOUNT;

  COMMIT TRANSACTION;

  SET @counter = @counter + 1;

  IF @counter % 10 -- or maybe 100 or 1000
  BEGIN
    CHECKPOINT; CHECKPOINT;
  END
END

The checkpoints make sure that the log space from the transaction can be re-used (the second one ensures the log properly "wraps around"). In your full recovery environment, you'd just need a single BACKUP LOG command there... or you might consider switching to simple if you can time this maintenance work very closely to your regularly scheduled full backup where you can restart the log chain.

The trick is to find the sweet spot of balancing the reuse of the log and the work the checkpoint or log backup has to do. (In my article the frequency of checkpoints actually caused the overall operation to be slower, which might be ok, it doesn't really matter how long this takes if it truly acts like a background process - the problem isn't duration, it's interference. I just didn't fish around for the sweet spot.)

The batch size effectively limits the number of rows locked within any given transaction and reduces the amount of work required by that transaction. You can use delayed durability here if you have a modern enough version; the biggest risk is that you lose a transaction, but since what you "lose" is data removal, you haven't really lost anything.


TempInsertStateItemShort procedure on ASPState is taking more than 1 second to execute

As stated by Max Vernon:

  800,000 rows seems to indicate the site has 800,000 sessions active (or at least active in the last 20 minutes - which is the default session timeout). Is that in line with the actual traffic you have? If not, I'd say the website does not have correct rights to the ASPState database.

The fragmentation would be "normal" for a table where the PK is a randomly generated GUID, as in this case.

I've note that ASPStateTempSessions was not being clean up expired sessions.

There is a job to clean up expired sessions ASPState_Job_DeleteExpiredSessions that was disable. I just enabled it and things are back to normal.

one more thing, just a good link I just found that helps to optimize ASPState.
link


What units are 'reads' and 'writes' in dm_exec_query_stats

Reads and writes are expressed in terms of "the number 8K pages." It should be documented better on the page you reference, but you can piece this together from other areas of the documentation, e.g. from Reading Pages:

  A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache.

And from Understanding Pages and Extents:

  In SQL Server, the page size is 8 KB.


What "fromRouter" option means on MongoDB aggregations?

It just means that this is a sharded cluster and the aggregation is happening via a mongos (also known as a query router).  This is important for aggregations internally because it means that the output from multiple shards may need to be merged but it's not really relevant for most end users (you usually already know you are running in a sharded environment).


changing tablespace seems to waste space

The comment about file system block size may be relevant. Please note that it is about file system blocks, not hard disk blocks. This is normally referred as a cluster, in NTFS terminology. You may check it with command fsutil fsinfo ntfsinfo [your drive] on a cmd opened as Administrator.

You might also check the storage parameter, like fillfactor. I think (althought I am not sure) that moving a table to a new tablespace will not just copy tha table blocks, but will reorder them. If you copy a table where you never issued update or delete statements, the table on the new tablespace will be as the original one, but if you updated/deleted records, the the table on the new tablespace will be "compacted" and will fill all new blocks only until the fillfactor, leaving some space for future updates on every block. If the fillfactor is too large, this is a waste of disk space.


Why does my transaction log use so much space?

Transaction logs are, as far as I'm aware, not stored in what we would consider a very disk space friendly manner.  This is because the logs have to be able to do several things.  Almost all the neat tricks of ACID compliance are not because the database data files exist, it's because the transaction log files exist.

Think about some of the things the logs have to do:

Allow a transaction to be rolled back to the state the database was in prior to the query running.  This means the database has to know not just what was in place, but how to get back there.
Keep the rest of the queries and transactions running from interfering with or being interfered by the query you're trying to run.  Even if you're not doing these kinds of things, the database will do them unless you specifically tell it not to.  The database engine needs to be able to seamlessly treat data not yet written to the database as though it were and as though it weren't.  Potentially at the exact same time.
The database employs write-ahead-logging, meaning it will write everything to the log before the transaction is considered complete.  Note that committing a transaction doesn't write the data to the database.  It means it's finished write-ahead-logging!  This means the data is first written to the log, and then later is copied to the actual database.  Writing data to logs is necessarily faster at the cost of not being space-efficient.
Assuming you're using the full recovery model, the database logs must support point-in-time recovery.  This means the system has to be able to recover the database to a specific point in time at all times regardless of what the database has undergone.  So the transaction logs would have to show how to recover the database to how it was before your massive query as well as after it.  This is different than the need to be able to rollback the transaction.  This is about recovery hours or days after the data has been committed.
Save to disk all of the above as fast as possible because performance is really important, too.

You may find it useful to read about the transaction log architecture.  It's really, really not surprising that transaction logs eat up so much disk space.

A database I work on is about 5 GB in size.  We restored the DB to a testing area so I could test log growing.  This was on a DB using Simple recovery.  One table in this database is about 2.5 million rows and is about 5% of the database.  It has a clustered index for the primary key, but no other indexes, foreign keys, triggers, or check constraints.  I ran two statements, essentially update bigtable set int_field = int_field + 1 followed by update bigtable set int_field = int_field - 1.  This does nothing at all but creates an awful lot of work for the database, especially because int_field is part of the clustered index.  Each execution caused the transaction logs to grow about 1 GB.

I'm assuming you ran (essentially):

insert into production.table (id, field, ...)
select id, field, ...
from staging.table

That will work, but it means the database has to keep track of an awful lot by default.  However, you can use it in a minimally logged manner that will help a lot.  If you really have to do this kind of thing, here's what I would do:

Do a full backup of the DB and logs.
Set the database to single-user mode.
Disable any nonclustered indexes on the target table.
Set the recovery mode to bulk logged.

From here, I'd follow MS's best practices for using INSERT INTO ... SELECT for large operations with minimal logging:

  Using INSERT INTO…SELECT to Bulk Import Data with Minimal Logging

  You can use INSERT INTO  SELECT  FROM
   to efficiently transfer a large number of rows from one
  table, such as a staging table, to another table with minimal logging.
  Minimal logging can improve the performance of the statement and
  reduce the possibility of the operation filling the available
  transaction log space during the transaction.

  Minimal logging for this statement has the following requirements:

  The recovery model of the database is set to simple or bulk-logged.
  The target table is an empty or nonempty heap.
  The target table is not used in replication.
  The TABLOCK hint is specified for the target table.

  Rows that are inserted into a heap as the result of an insert action
  in a MERGE statement may also be minimally logged.

  Unlike the BULK INSERT statement, which holds a less restrictive Bulk
  Update lock, INSERT INTO…SELECT with the TABLOCK hint holds an
  exclusive (X) lock on the table. This means that you cannot insert
  rows using parallel insert operations.

Once I was done, I'd undo the three steps I'd done above.  I'd revert the recovery model, index and single-user mode change, and do another backup.  You'll then probably want to rebuild your indexes.

I'm not sure if you actually need to change the recovery mode to bulk logged to get some advantage from the minimal logging.  I've never had to do exactly what you describe, so I can't be sure.


Insert into DynamoDB when data has different attributes

val paramsMap = params.iterator.toMap
val item: Item = new Item()
  .withPrimaryKey(Partition_Key_Name, partition_key)
  .withKeyComponent(Sort_Key_Name, sort_key)

paramsMap.map { case (k, v) => item.withString(k, v) }
table.putItem(item)

can be solved like this in scala


How to find physical_device_name on backup by extending great query from Ozar?

WITH backups (DatabaseName, LastBackupFinishDate)
AS
(
    SELECT d.name AS DatabaseName, MAX(b.backup_finish_date) AS LastBackupFinishDate
    FROM master.sys.databases d WITH (NOLOCK)
    LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK)
        ON d.name = b.database_name AND b.type = 'D'
    WHERE d.name <> 'tempdb'
    GROUP BY d.name
)
SELECT
    b.DatabaseName,
    b.LastBackupFinishDate,
    bmf.physical_device_name
FROM backups b
LEFT JOIN msdb.dbo.backupset bs ON b.DatabaseName = bs.database_name
LEFT JOIN msdb.dbo.backupmediaset bms ON bs.media_set_id = bms.media_set_id
LEFT JOIN msdb.dbo.backupmediafamily bmf ON bms.media_set_id = bmf.media_set_id


New IDs will always INSERT into the table, not the index. Your proposed INDEX would merely provide a more efficient lookup for some ID values.

Some thoughts though

The index may not be used too: it would have to cover all columns in the table. Or searches would have to return just a few rows so the key lookup needed (for a non-covering index). For more, see "Using Covering Indexes to Improve Query Performance".

As it stands, it may help, it may not.

Deleting or archiving old records can be made less painful too, which is probably the better solution. Example (SO): https://stackoverflow.com/a/2126482/27535


Summarizing what we discovered in the chat.

When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to connect with Integrated Authentication will get the Kerberos token from AD for the AD object that matches the server name to attach to the authentication.  Kerberos won't be used when either the server that is being connected doesn't have an SPN or is being connected using SQL Authentication.

In this case, because "OLD-SQL" is still registered in AD and has an SPN registered spoofing, the DNS in the local host file won't work when using Integrated/Windows Authentication, as there is nothing adjusting which token is returned/used from AD. There doesn't appear to be a way to have the client know to use Kerberos token for "NEW-SQL" with the original "OLD-SQL" server still being in service, except to remove the "OLD-SQL" SPN from AD to force the authentication to fall back to NTLM.  Spoofing the DNS works for aliases in which are not actual computer names registered in AD or do not have an SPN registered, as AD doesn't return a token for them, so the authentication falls back to using NTLM.

References

Understanding Kerberos Keys

Registering an SPN


replicate master/slave without disturbing master for dump

I would recommend Percona's Xtrabackup for this, especially since you mentioned all of your tables are InnoDB. From the page:

  Percona XtraBackup is designed to make backups a seamless procedure without disrupting the performance of the server in a production environment.

The tool will allow you to take a backup without locking innodb tables. It will do a lock to copy any MyISAM tables (such as the tables mysql in the mysql database) but those shouldn't take long.

I cannot tell you how long it will take on 400GB database, but it should do it without downtime.


Time-series window query for calculating rolling average across group by subquery is inaccurate due to missing rows

As Lennart mentioned, you'll need to CROSS JOIN your generated series with your users to create the required combinations of (Login,Date):
SELECT
  U.Login
 ,t.Date::date AS Day
 ,COUNT(UserItem.UserId) AS Count
 ,AVG(COUNT(UserItem.UserId)) OVER (PARTITION BY U.Login ORDER By t.Date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS RollingAvg
FROM
  "User" U
CROSS JOIN
  generate_series(date '2020-01-01' - 3, date '2020-01-01' + 3, interval '1' DAY) AS t(date)
LEFT JOIN
  UserItem UserItem
    ON UserItem.UserId = U.UserId
        AND UserItem.Updated_At = t.Date
GROUP BY
  U.Login
 ,t.Date
ORDER BY
  U.Login
 ,t.Date

You can restrict to only those users with activity in the date range by adding:
WHERE
  U.UserId IN
    (
      SELECT
        UserId
      FROM
        UserItem
      WHERE
        Updated_At >= date '2020-01-01' - 3
        AND Updated_At <= date '2020-01-01' + 3
    )

Fiddle here.


It has nothing to do with the underlying OS. It has to do with the collation of the instance and the collation of the database. Here is an example that shows that database names, table names, column names, and data are not subject to case sensitivity by default.

If you want those things, you can turn them on, by specifying a case-sensitive collation for the column, for the database, or - I think - for the instance. (The images I've been using for SQL Server Docker containers don't have the traditional setup package where you can pick instance collation prior to setup, and changing instance collation after the fact might not be fun.)

Perhaps you're thinking about physical files? I know when I was moving my stuff from IIS to a Unix host I had to change references to things like file.css because the file on disk was actually File.css and the server wouldn't be able to locate it. SQL Server doesn't work that way.

Regardless

Yes, you should absolutely stick to the proper casing of things, and that future-proofs you no matter what operating system you use to host SQL Server. Anybody can come along and change the collation of various bits of your installation (or deploy your code to a place where the collation is already different). See these articles:

Bad Habits : Being CarELesS about cAsE
#BackToBasics : Why I use lower case for data type names (now)


Just got an answer from the maintainer of the nodejs driver, deprecation has been removed!


Can I recover a TDE certificate by restoring the MASTER database?

Since TDE relies on a certificate stored in master (which is used to encrypt the database encryption key), then this would work only work if you could restore the master database to another server in such a way that the certificate could be decrypted.

This is the TDE encryption hierarchy:

Service master key (protected by Windows; tied to the service account credentials, and a machine key)
Database master key (in this case, the one for the master database)
Certificate
TDE encryption key

The first three items are stored in the master database, and can all be backed up. The fourth is stored (encrypted by the certificate from #3) in the header of the encrypted database.

So in a failure scenario, you would have to restore enough of the encryption hierarchy to allow you to read the TDE key. SQL Server creates the service master key at installation; thus while restoring the master database to a different instance will also restore items 2 and 3, the necessary key(s) to decrypt them will not be present. Result: unreadable data.

The two best options are to either restore the certificate (#3) from a backup (a good option if master cannot be restored for whatever reason), or restore your master database and its master key (#2) from a backup. Restoring the master key may be a better option if you have a lot of certificates/keys protected by this key, and need to make them all accessible at once. This comes with the same precautions normally associated with restoring the master database (collations, logins, database names and file paths, etc.)

Generally, I'd only recommend restoring master in a recovery scenario. For a migration/scale-out scenario (such as using Availability Groups/mirroring with a TDE-encrypted database), it's better to backup/restore the certificate (#3) so that it's encrypted using the master keys unique to each instance it's moving to. You will need to include the private key with the certificate backup.

In any case, you're going to have to make key/certificate backups, so guard them well, and store them in redundant, secure locations. Simply having a backup of master will not get you out of a TDE disaster; you're going to need a backup of at least one key or certificate.


Error: The certificate cannot be dropped because one or more entities are either signed or encrypted using it

My bad.

I should be using

DROP COUNTER SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY CERTIFICATE certificate_name

instead of

DROP SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY certificate_name


You can use DROP TABLE <schema_owner>.<table_name> CASCADE CONSTRAINTS to drop the referential integrity too. Notice that there must be a reason to have a referential constraint in this table. Check it twice before drop. (I recommend to read documentation first http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm )

SYS is a special user, has some restrictions (like objects owned by SYS cannot be exported), but can alter any object in the database, including droping or alter some DB engine objects. That's why (and many other reasons) you should NEVER create user objects in SYS schema.
Objects created in SYS schema has a different treatment, and that's why you couldn't drop the column (or constraint, but I've never tried this).

I can say that the source of the problem was not the DROP TABLE syntax. The problem is that your objects where created in SYS schema ;). Oracle was designed to create and manipulate user objects within user's schemas.
It's like if you have a hammer and try to use it grabbing it by the head. It's not effective , It wont work as expected and you could brake it.

The good practice is to create USERS, and then create objects within user's schema.
You can create a new user just with:

CREATE USER <username> IDENTIFIED BY <password> ;

This will create a User (and a Schema), but it wont have any privileges. Logged in as SYS you can create and manipulate objects in this (and any) user schema, but this user will never be able to connect nor create objects. This are system privileges that can be easily granted with the roles CONNECT (just grant the CREATE SESSION privilege) and RESOURCE (grant a bunch of privilege to create several DB objects in his own schema):

GRANT CONNECT TO <username>;
GRANT RESOURCE TO <username>;

With this, you'll have a user with the DEFAULT profile (search in documentation for User Profiles) which can log in and can create user objects in his schema. A typical oracle db user.

These are all Oracle Administration basics. I've recommend to read the 2 Day DBA, DB Concepts, and DB Administrator's Guide documentation. All available at http://www.oracle.com/pls/db112/homepage (or, easy to remember, http://tahiti.oracle.com )

Regards.

-- edit- The OC4J_DBConsole_SID is related with EM configuration. I wouldn't mess around with this until you dominate Oracle's concepts. A GUI won't teach you the basis and it will progressively make you dependent of it.


Optimizing table/indices for getting the latest row (filtering by one additional column)

Given the low rate of inserts, your proposed index will be completely fine, and perfect for the usage goal.

Given a fresh index with fill factor 100%, and sufficient history for each organisation to fill a page, there will be a page split on the first subsequent insert for each organisation. But then there won't be another page split for that organisation until the new page is filled up.
Even these splits and fragmentation can be mitigated by starting with a <100 fill factor, and regular reorganise.

Aaron's thought regarding using non-clustered was probably because that would allow you to cluster on the always ascending identity column, ensuring the only splits and fragmentation you will get is in a very compact separate index. But I suspect he was only mentioning that in the context of you wanting very much to avoid fragmentation, and not as something really necessary in this scenario.


Availability Group of SQL Servers 2012, 2012 and 2016 possible?

1) --> What are potential problems if we add SQL Server 2016 as a third node to existing Availability Group that has two SQL Servers 2012?

Once you failover(or failover happens) to 2016, you wont be able to sync other replicas. You should use this when you want to do upgrade with minimal downtime.

Another issue is about the new query optimizer introduced in sql server 2014 and up. I have seen some queries perform badly on the newer version. So putting 2016 as new node introduces extra risk.

  2) --> Will AG be working properly or ? If you have any practical experience of doing so, please share

Above answer covers this question. A failover to 2016 will break replica sync status. Once you upgrade to higher version, the boot page gets upgraded which prevents tlog restoration of higher version to lower version.

  3) --> Do we really need to deploy server 3 as SQL Server 2012 (instead of 2016) in order for AG to be working ?

Yes - if you are planning to keep all the 3 replicas in sync.

No - if you are planning to upgrade with minimal downtime.

Make sure to sync sql agent jobs, logins, etc since they are not automatically synced.

  4) --> What are best options (aside of AG) to keep SQL servers 1, 2, and 3 in sync if we chose to deploy server 3 as SQL Server 2016 ? is that transactional replication

Replication is the only option available with some additional things to consider

Replication will be hard to maintain when using with AGs.
Depending on how you implement sync status in AGs, you will need to enable trace flag 1448
All the tables should have PKs defined if they are needed for replication.
You wont be able to truncate tables once you publish articles in replication.

My advise is to use sql server 2012. You can do an upgrade later by properly testing your application on sql server 2016.


Should I always make an attribute id that's an primary key?

There are two schools of thought about this and some of it is database dependent.

In general, on PostgreSQL, I define natural primary keys wherever I can (and make these composite keys where appropriate) and I (usually) add a secondary, surrogate id key which is an integer and is used as a surrogate for the primary key in joins.  I see this as a good way to navigate the positives and negatives of natural primary keys.  Note that if I was on MySQL, I would probably set the id as a primary key just because of how InnoDB works regarding indexes and primary keys.

Benefits of Natural Keys

A natural key is a piece of collected data which identifies a row in a relation.  Occasionally there are no natural keys but this is rare.  Natural keys can be a single column or they can be a composite key of several columns.  There are several important benefits to natural keys.

Defining natural keys is necessary for normalization.  Defining them in your DDL makes normalization mistakes more obvious.  This leads to clearer database design.
Forcing you to think about natural keys keeps data clear and avoids missing unique constraints.

Drawbacks of Composite Keys

Composite keys however pose performance issues when used in JOIN conditions.  Heuristic planners have a harder time estimating numbers of rows returned by multi-column keys because the co-incidence rate is not necessarily known between key columns, and these may not be well distributed and this can throw planner estimates off.

Additionally this leads to wider join criteria which in turn leads to more memory used in joins.  Additionally, and for obvious reasons, it takes significantly more computing power to calculate a multi-column join than a single column one.

Finally, natural keys are often "discovered" rather than defined.  Changing a composite key as a join condition is a really error-prone hassle I am quite happy to live without.

Where a key is text, definitional, and the most commonly used piece of information in a table, I will often use it as the join key, but it is quite rare that all of these are met.

Database Implementation Variables

Consider two different databases, PostgreSQL which uses heap tables with external indexes (so primary key indexes are not privileged over unique indexes, and tables can be scanned in physical order), and InnoDB which uses tree structures in the table, and contains the row, essentially, in the primary key btree index.  With MySQL's InnoDB, you must traverse the designated primary key on every query, even if other indexes are used, and physical-order scans are not supported.

In the first, case, performance is not adversely affected by using a non-pkey index, but in the second case it is and so you really want to have your primary key be the most frequently used unique index.


Getting count and percentage of valid and invalid records, group by day

SELECT user_id,
       DATE(created_at) created_at,
       SUM(valid=1) valid,
       100*SUM(valid=1)/COUNT(*) pvalid,
       SUM(valid=0) invalid,
       100*SUM(valid=0)/COUNT(*) pinvalid
FROM test
GROUP BY user_id, DATE(created_at)
ORDER BY user_id, DATE(created_at);

fiddle


Odd inconsistent behavior of SQL Server "IN" clause

NOT IN will not evaluate to true when one or more NULL values are returned by the subquery.

Use NOT EXISTS to avoid this common gotcha. See this answer for more information and links to more detailed information.


How to fix a messed up replication on MS SQL Server

TLDR:

It appears that disabling and re-enabling replication probably fixed the issue:

exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false'
exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true'

I guess this is the equivalent of switching it off and then back on again...

Longer version:

A workmate had a go at trying to fix it. He tried a few things but didn't get very far. The one change he did make before giving up was to disable the replication.

I then tried Cody's suggestion. The sp_dropsubscription command complained that no subscriptions exists. So I tried the sp_droppublication command. This complained that replication was not enabled on the database. So I enabled it and re-ran the command. This time it complained that the publication did not exist. I refreshed the Local Publications node in SSMS and sure enough it had gone. I ran the replication set up script, generated a new snapshot and every thing is now working properly. Joy!

I'm not 100% certain that disabling and enabling the replication is what actually fixed the problem, but it is definitely worth trying if the replication gets messed up.


MySQL 5.1.x: Performance improvement in Enterprise edition Vs community edition

I don't think you can get a MySQL Enterprise edition for v5.1.X version.

You can see the diffrence in performance between Community and Enterprise version here:

http://www.mysql.com/products/enterprise/scalability.html

You can also download Enterprise product guide whitepaper to know more details.

In summary additional features in MySQL Enterprise version are MySQL Thread Pool, MySQL Enterprise audit and a Technical support for your server related issues.


MySQL: querying for latest messages in flat reply system

You can find the latest sent message in each thread and then join that with your tables:

select M.*, R.*
FROM messages M
JOIN recipients R
    ON M.Message_ID = R.Recipient_Message_ID
JOIN (
    select Message_Root_ID, max(Message_Sent_Time) as Message_Sent_Time
    from messages
    group by Message_Root_ID
) as X
    ON M.Message_root_id = X.Message_root_id
   AND M.Message_Sent_Time = X.Message_Sent_Time


How to handle id's referring to multiple tables in an SQL database

Don't "over-normalize".

The table stats, items, and skills are just normalizations of short, unique, names.  Consider just having the names in the other tables.

Or...  have just one table for id<->name mapping, perhaps with an ENUM to say which of stat/item/skill/etc it is.

Based on your Schema, what queries will you be performing?  Do you ever need to 'start' with action_influence?  Or is it just some extra info to enter and later display?  If the latter, consider putting that into a JSON string of all the reqs, influences, etc that are associated with an action.  Then have that JSON string in the actions table.  If you use MySQL 5.7 or MariaDB 10, there are even tools for having SQL reach into the JSON, but try to avoid that.


Changing collation of SQL Database in Production environment to minimize downtime

I just tried this and it worked in Transactional Replication:

Server Level Collation is Finnish_Swedish_CI_AS
Database Level Collation of both sides of the databases are the same which is SQL_Scandinavian_Pref_CP850_CI_AS
Column Level Collation at publisher side database is SQL_Scandinavian_Pref_CP850_CI_AS and subscriber side database is Finnish_Swedish_CI_AS

The above setup delivered correct data from the publisher side database to the subscriber side database.
Its column-level collation change, which takes time and that depends upon how much data the table contains. Database level collation change can be managed in downtime by just dropping the dependencies first and recreating them. So, its the Database level collation which was causing this data issue, when it was differing, not column-level collation.


Database restore time increased after we changed from SQL Server 2014 to SQL Server 2016

For those who may have the same problem, we noticed that when we did the CHECKDB we had an error with PFS pages (https://www.sqlskills.com/blogs/paul/pfs-corruption-after-upgrading-from-sql-server-2014) and thus the solution was to correct these error with a DBCC CHECKDB(database_name, REPAIR_ALLOW_DATA_LOSS).
We also move the disk from 7K to 10K to upgrade performances.


Lock only a row in insert command sql server

Make sure you have an index on the Nome column, allowing SQL Server to "bypass" (not having to read) those rows you exclude in your WHERE clause. I.e.
CREATE INDEX yourindexname ON Teste_Nolock(Nome)


Time sheet Database design with multiple clients

There is no perfect answer for this only you can decide the best option. And it has a lot to do with tradeoffs, and preferences. How much time you have now vs maintenance in the future.

You could have:

1 table for all customers.
A table(s) for each customer
A database for each customer.

Option 1 is ideal if you hope to reuse and resell to many customers. The downside is that you cant use the db schema to enforce rules per column unless the rule applies to all customers. Which makes enforcing business rules difficult.

Option 2 allows you to use the schema to enforce rules per customer. But the queries and app design become more complex.

Option 3 means you can create a customised solution per customer, but it will require more effort to maintain.

Start by modelling the requirements and see how much commonality there is and what is or is not mandatory.

Understanding business rules and things like one to many relationships will be key to getting it right.

If you dont expect the db to enforce mandatory fields and can handle that in the application personally i would lean towards option 1 with as much re-use as possible.


cross database way to force join strategies

First of all, note that not all databases support all three (Loop, Hash, Merge) types of join. For example, you cannot hash join in all version MySQL (thanks for the comment  pointing out that this now works in MariaDb).

Second, databases will often have vendor specific syntax to force certain join strategies and orders. For example, SQL Server uses the keyword LOOP JOIN to force a loop, Oracle uses USE_NL, MySQL uses the syntax STRAIGHT_JOIN to force join order.

All that being said, there are often ways to write a query in such a way that it forces certain behaviours across database platforms. Here are the strategies I have found useful:

Forcing Loop Joins:
Expressing a JOIN as a nested, correlated subquery instead of a regular join will typically force the loop strategy.

For example, instead of:

SELECT ...
FROM t1 JOIN t2 ON t1.key = t2.key

Write:

SELECT ... (SELECT ... FROM t2 WHERE t2.key = t1.key)
FROM t1

Forcing Hash Join:
Either remove the indexes on the small table or force a table scan of the table. As a generic method for disabling indexes, you can purposefully confuse the optimiser. For example, instead of:

SELECT ...
FROM t1
JOIN t2 ON t1.key = t2.key

Write (Assuming key is INT):

SELECT ...
FROM t1
JOIN t2 ON t1.key / 1 = t2.key / 1

This trick can be expressed more elegantly using functions like COALESCE And NVL (in Oracle)

Forcing Merge: I have not found a generic way to do this. But having the right indexes in place and then forcing the indexes does the trick in the cases I have seen.

Forcing Join Order: Join order is a little easier to force. The trick is to create artificial dependencies between the join conditions. For example, let us say you wanted to force this to be joined in the order t1, t2, t3:

SELECT ...
FROM t1
JOIN t2 ON t2.k = t1.k
JOIN t3 ON t3.k = t2.key

You would write:

SELECT ...
FROM t1
JOIN t2 ON t2.k = t1.k / 1       /* For t2 to be looked up first */
JOIN t3 ON t3.k = t2+0*t1.k     /* Force t1 to be evaluated first */

For a general treatment of query optimisation across database platform, I would recommend picking up a copy of this: http://www.amazon.co.uk/SQL-Tuning-Dan-Tow-ebook/dp/B0026OR32Q/


Should I create new table for each User to store their product List?

Creating a unique table for every user to hold their billing/purchase details would be an absolute nightmare to maintain.

It would be best to create different tables for each entity. For example, you could create one for all users, one for all bills/purchases, and one for all products. You can connect the user table and billing table by a user ID. In the billing table, each row can have a user ID to signify which user that purchase record belongs to. The user ID would be a reference back to the user table. The billing table should also have a reference to a products table which would hold the product name, stock left, etc.

This is just an example though and depending on your system design, you may need more tables.


Do applications have to specify TCP: in the connection string to availability group listeners?

No, it's not necessary. The articles may be including it for completeness sake, but it's not required at all.

The TCP: prefix isn't unique to AG listeners - you can connect directly to a standalone SQL instance by adding the TCP: prefix, but it doesn't change how the connection is established, unless you're connecting locally.

If you connect to an instance locally it will default to using Shared Memory for the connection. If you instead prefix the connection with TCP: it will force the connection to use TCP as the protocol. You can test verify this using the following script.

select c.net_transport, s.host_name, s.program_name
from sys.dm_exec_connections c
join sys.dm_exec_sessions s
on c.session_id = s.session_id
where s.host_name = host_name()

Open up a connection to a local instance without the prefix and you'll only see Shared Memory connections. Then try with the prefix and you'll see it switch to TCP.

Connections to AG listeners however (even if you're connecting from the primary replica), will always use TCP, prefix or not.


how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers

I think you are looking at this from the wrong point of view.  You don't want SQL to upate your spreadsheet, you want your spreadsheet to connect to your database and get the latest data.

Create a local DSN  -- using "setup data sources (ODBC) -- then connect to that data source from excel.


Is it a bad practice to keep Master and Slave (Replica) Database on Same Server?

I would say Yes, it is a bad practice to keep master database in same server than replica database. I have good three(3) reasons why, but there are possible workarounds if you cannot acquire another DB Server. Before I discuss those reasons, have a look at a Pictorial Representation of the InnoDB Architecture (from Percona CTO Vadim Tkachenko)

REASON #1 : Disk I/O
Look at InnoDB's system tablespace file ibdata1. It has lots of moving parts

Data Dictionary
Double Write Buffer (to prepare for crash recovery)
Insert Buffer (manages changes to secondary indexes)
Redo Logs (ib_logfile0,ib_logfile1)
Undo Logs
Rollback Segments

You have to create two different datadir locations. If they reside on the same disk, you will have two ibdata1 files being updated on the same disk, which will suffer increased disk I/O and will slow down both MySQL instances.
Suggestion #1: You could accommodate this having the datadir of each MySQL Instance on a different disk using its own disk controller (Make sure your disk controllers have up-to-date kernels).
This is the same concept you would apply if

You use Amazon MySQL RDS, having a Master and Slave in different availability zones
You have VMWare, having Master VM and Slave VM live on different disks

REASON #2 : RAM
Look back at the InnoDB Buffer Pool. You will have two of them. If you set these too large, you could trigger some swapping in the OS.
Suggestion #2: If the Slave instance is just for aggregation (or aggrevation), you could tune the Slave's my.cnf to use a lot less memory (Smaller Buffer Pool, Smaller MyISAM Key Buffer)
Suggestion #3: Increase RAM
REASON #3 : Data Redundancy / High Availability
If mysqld goes down on the Master, you have mysqld for the Slave still running. What if you have a hardware failure and the database is mission critical ? There is nowhere to failover.
Suggestion #4: You should get a cheap server with the same amount of disk and half the memory of of the Master and configure a Slave.
Suggestion #5: If you applied Suggestion #1, you could move the disk holding the Master to another machine. If that disk is fried, you have the disk holding the Slave. Hopefully, the one DB Server did not having a real disaster and and damage both Master and Slave disks.


Postgres table growing on massive updates

The reason that your table is growing while only making updates is that PostgreSQL is that "tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done."

Of course, while you are updating 1000 rows per second your updates are in contention with VACUUM FULL and perhaps REINDEX.

You should probably not be using the FULLoption.  See:
http://www.postgresql.org/docs/9.1/static/sql-vacuum.html

"Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained."

If that is not enough to resolve your problem, perhaps you could include the VACUUM and REINDEX in your job being driven by your application server.  Then periodically switch from updating rows to run a VACUUM.

EDIT: Note that 3 comments have pointed out autovacuum as a process that can run continually.  However, dezso mentioned that configuring it for a heavy load may not be straightforward.


Are there any guidelines for the "IBM Premier Level Business Partner"?

I do not know if what you are looking for is stated in some place. The closest thing could be PartnerWorld in IBM: https://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/pw_home_pub_index

I think a partner is just people close to IBM, with a lot of IBM product knowledge, but they can do almost whatever they want with the client, they just have to follow some criteria based of sells, courses, etc. This is the reason, there is a big competence between partners.


What exactly does "No Join Predicate" mean in SQL Server?

Your question is similar to this one. Sometimes SQL Server can remove a join predicate from the original query.

In the case where you see a join predicate warning SQL Server detects at compile time that the table of constants only has one distinct value and that value is 1 so rewrites the query as

SELECT *
FROM   (SELECT *
        FROM   #temp1 t1
        WHERE  t1.i = 1) t1
       CROSS JOIN (SELECT 1 i
                   UNION ALL
                   SELECT 1) t2

There is a predicate on the table scan of #temp as follows [tempdb].[dbo].[#temp1].[i] =(1)

The join predicate of on t1.i = t2.i can't be removed in this way at compile time when using two tables or if the table of constants contains more than one distinct value.

More info about this can be found in Paul White's Query Optimizer Deep Dive series.


Condition in WHERE vs condition in CONNECT BY

After reading the documentation, I came to the following conclusions.

One difference between the two queries is that the first query has two condition in the connect by , in order to identify a relationship - a row must fulfill not only the parent_id = prior concept_id condition but also the exists clause.

 connect by parent_id = prior concept_id
 and exists (..)

While the second one has only one condition.

 connect by parent_id = prior concept_id;

The part of the query that makes the distinction more obvious is the start with.

In the first query, all the rows with PARENT_ID = '11025' will be returned and used as root rows. The two conditions of the connect by will be used to find the children of those rows. The exists condition will not be applied to the root rows.

On the other hand, the second query will fetch the rows with PARENT_ID = '11025' and filter them with the exists statement.

As a result, the results will be totally different.


The 4.44089e-015 is most likely giving you the error by trying to convert it to a decimal. Try using float instead:

SELECT  nomen,
        ROUND(SUM(CAST(vtc AS float)),0.00) FormatedInfo,
        d1
FROM @GreenHouse
GROUP BY nomen, d1
ORDER BY d1 DESC;

The results with your sample data are:

╔═══════╦══════════════╦════════════╗
║ nomen ║ FormatedInfo ║     d1     ║
╠═══════╬══════════════╬════════════╣
║ Green ║         1850 ║ 2016-04-30 ║
║ Green ║          649 ║ 2016-01-19 ║
║ Green ║          201 ║ 2016-01-18 ║
║ Green ║          507 ║ 2016-01-14 ║
║ Green ║            0 ║ 2016-01-03 ║
╚═══════╩══════════════╩════════════╝

(yeah, I know that it converts the 4.44089e-015 value to 0, but it is expected for a float)


Oracle SQL Developer 3.2.09 is consuming lot of memory on windows 7

Add below lines to C:\Users\<<user>>\AppData\Roaming\sqldeveloper\4.2.0\product.conf

For 64 bit SQL Developer add:

Add64VMOption -XX:+UseG1GC
Add64VMOption -XX:MinHeapFreeRatio=15
Add64VMOption -XX:MaxHeapFreeRatio=30

For 32 bit SQL Developer add:

Add32VMOption -XX:+UseG1GC
Add32VMOption -XX:MinHeapFreeRatio=15
Add32VMOption -XX:MaxHeapFreeRatio=30

Restart SQL Developer.

To reduce the memory close only the results pane that has huge result set.

TIP: I typically increase JVM memory to 2 GB so SQL Developer does not crash for queries with large result sets. This can be done by adding below line to product.conf:

Add64VMOption -Xmx2048m


You have to call

SET DEFINE OFF

before installing the package to ensure that &quot and &gt are not recognized as substitution variables. See also docs.


Repeating rows of date intervals based on another table in Oracle

Returning maintenance dates for the next 365 days:

variable n number;
exec :n := 365;

with generator as
(
  select rownum - 1 as r from dual connect by level <= (select max(ceil(:n / day_interval)) from maint_sched)
)
select ms.maint_id, ms.start_date + g.r * ms.day_interval as date_due
from maint_sched ms join generator g on (ms.start_date + g.r * ms.day_interval between sysdate and sysdate + :n)
order by 2, 1;

  MAINT_ID DATE_DUE
---------- ---------
         1 30-JUN-15
         3 01-JUL-15
         3 31-JUL-15
         3 30-AUG-15
         1 28-SEP-15
         3 29-SEP-15
         2 18-OCT-15
         3 29-OCT-15
         3 28-NOV-15
         1 27-DEC-15
         3 28-DEC-15
         3 27-JAN-16
         3 26-FEB-16
         1 26-MAR-16
         3 27-MAR-16
         3 26-APR-16
         2 05-MAY-16


Column level security does not work that way. I'm aware of no mechanism to globally deny access to a certain column for a given user. The GRANT/DENY only works on specific statements like SELECT, UPDATE and so on in combination with a given object.

So in your case, if you removed access on SELECT for column X on table Y the user can still happily execute "select *" views on that table because the view is a different object and is unaffected by this security setting!

The good news is that you can use column permissions on views also. It works just the same as with tables, but you have to set the permission on every view that includes the SSN column.


SQL Server 2012, rebuild not lowering avg fragmentation

Is it a small index? Small indexes will always show high levels of fragmentation. You can read more at Ola Hallengren's site which has a highly rated SQL Server maintenance package - look at the PageCountLevel setting, which specifies that Microsoft recommends leaving indexes under 1000 pages - with the link.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


Updating Multiple Tables with single Update query

WITH cte
AS ( UPDATE tableB
     SET salaryb = tableA.salary % 1000
     FROM tableA
     WHERE tableB.empid = tableA.empid
     RETURNING *
   )
UPDATE tableA
SET salary = salary / 1000;


Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again

The issue was the setting "Auto close" was set to true on a series of databases on the server. Even though the databases were unrelated their "auto close" setting was flushing all execution plans on the server.

Here are the types of log issues you will see with auto_close set to true:

SQL Server has encountered 24 occurrence(s) of     cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

As you can see in this article, NEVER SET AUTO CLOSE TO TRUE!!! http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases


Trying to understand TID definition from PostgreSQL documentation

“Page” and “block” are synonyms in PostgreSQL and refer to the 8KB block of data that is the unit for all object storage. One tends to use the term “block” when speaking of disk storage and “page” when the data reside in memory, but it is the same.

So a tid of (42,9) means that the item is the ninth element in the 43rd 8KB block of the table or index (blocks are counted from 0).


Cannot stop MySQL 5.5 replication threads

To halt replication from starting up on a restart of mysqld you should add this to my.cnf

[mysqld]
skip-slave-start

Don't worry about the SQL Thread. It reads from the local relay logs. Once all SQL statements and changes are read from the relay logs and processed, the SQL threadwill just sit there. You can prove that by running SHOW SLAVE STATUS\G and watching

Relay_Log_Space: If the IO Thread is disabled, this should no longer increase.
Exec_Master_Log_Pos:  Once all SQL is processed from relay logs, this remains still

As long as the IO Thread is dead (run STOP SLAVE IO_THREAD;), replication from the remote master is no longer possible, especially since you set the address to a nonexistent server.

My guess is that you are using row-based replication, If you are, the SQL thread is probably staring at an incomplete transaction and waiting for the IO thread to complete a block of changes that never comes over from the master. When that is the case, you can kill the SQL thread by running

KILL connectionid;

for the connection of the system user still active for replication's SQL thread. I say this because the MySQL Documentation on STOP SLAVE says:

  In MySQL 5.5, STOP SLAVE waits until the current replication event group affecting one or more nontransactional tables has finished executing (if there is any such replication group), or until the user issues a KILL QUERY or KILL CONNECTION statement. (Bug #319, Bug #38205)


So on the SQL server, I was able to go to the "Event Viewer -> Windows Logs -> Application" and was able to match the event time with my time stamp in the SQL error log.
Then i was able to find the user that was causing the error.
Since we have a newly introduced AlwaysOn environment we went through his options to make sure it was set up correctly, that didn't help.

The issue was that he was on an old verision of SSMS that when he tried to connect seemed to cause the error. We updated his SSMS to latest version and it stopped.


DESTDIR is a variable inserted at the beginning of each target file

If you wanted to install MySQL in the /usr/local/mysql, you could use the following command sequence

cd /name/of/source/directory
make clean
./configure --prefix=/usr/local/mysql
make && make install

MySQL would thus be installed in the /usr/local/mysql directory.

The ./configure allows you to control and define how your program will be installed and what options will be included or excluded.

MySQL Source Configuration Options


Displaying Parent Child Information, With Certain Parent Columns Only Shown Once

A general policy is to let the reporting layer handle things like only printing ParentIncome once.  However, since you are delivering a spreadsheet that will be used by others in who knows what manner, then I suppose you are stuck.

Because of the knowledge required you will need to develop some extra information (MIN, MAX, first, last, etc.) that is not known by a single row.  There are dodges different from ROW_NUMBER() OVER (PARTITION...), but there will still be an extra step.

See the following:

CREATE TABLE #parent
(ParentNum INT,
 ParentName VARCHAR(20),
 ParentIncome INT);

CREATE TABLE #child
(ChildNum INT,
 ChildParentNum INT,
 ChildName VARCHAR(20),
 ChildAllowance INT);

INSERT INTO #parent VALUES(10,'John',50000);
INSERT INTO #parent VALUES(20,'Jane',55000);
INSERT INTO #parent VALUES(30,'Jackie',90000);

INSERT INTO #child VALUES(1,10,'Johnny',5)
INSERT INTO #child VALUES(2,20,'Jackie',10)
INSERT INTO #child VALUES(3,20,'Billy',5)
INSERT INTO #child VALUES(4,20,'Sally',5)
INSERT INTO #child VALUES(5,30,'Monique',0)

-- Basic approach you may be using

See this example SQL Fiddle #1

SELECT pc.ParentName, pc.ParentNum,
    CASE WHEN pc.RowNum = 1 THEN CAST(pc.ParentIncome AS VARCHAR(10)) ELSE '' END as ParentIncome,
    pc.ChildName, pc.ChildNum, pc.ChildAllowance
FROM (SELECT p.ParentNum, p.ParentName, p.ParentIncome,
         c.ChildNum, c.ChildParentNum, c.ChildName, c.ChildAllowance,
         ROW_NUMBER() OVER (PARTITION BY ParentNum ORDER BY ParentNum) AS RowNum
       FROM #parent p JOIN #child c ON p.ParentNum = c.ChildParentNum) AS pc
ORDER BY pc.ParentNum, pc.ChildNum

    -- An alternative, but still using a subselect for one element

See this example: SQL Fiddle #2

SELECT  p.ParentName, p.ParentNum,
        CASE WHEN c.ChildNum = mc.MinChild THEN CAST (ParentIncome AS VARCHAR(10)) ELSE '' END AS ParentIncome,
        c.ChildName, c.ChildNum, c.ChildAllowance
       FROM #parent p
          JOIN #child as c
             ON p.ParentNum = c.ChildParentNum
          -- This subselect gives the MIN (or First) ChildNum per Parent
          JOIN (SELECT ChildParentNum, MIN(ChildNum) AS MinChild
                  FROM #child
                  GROUP BY ChildParentNum) AS mc
             ON mc.ChildParentNum = c.ChildParentNum
ORDER BY p.ParentNum, c.ChildNum

drop table #parent
drop table #child

Notice that I cast the ParentIncome as a VARCHAR(10) so that the datatype would be of the same type as the empty income of ''.  I originally used a NULL instead of a blank, but that might give you EXCEL problems.

Is it worth doing things this way?  It is up to you, but it primarily depends on what you like best.  The ROW_NUMBER() is a more powerful operator than MIN() and gives you more options, but in this case it appears that a MIN() will work for you.


In Oracle, why would an OR / LIKE(A,B) take far longer than running the same query for each condition separately and unioning

If you're not clearing the buffer cache between runs, you don't know how much faster the query's running due to optimization and how much of that is due to the data already being cached.  Using the AUTOTRACE functionality in SQL*Plus allows one to see how much work (physical reads and logical reads) go into obtaining the result set.

Setting event 10053 to show to the optimization of the SQL statement would show why Oracle made the choice it did to not perform OR-expansion, which is what transforming a query with an IN condition or an OR statement into a UNION ALL query of disjoint queries.

Without more data, I suspect my_column is indexed, but you don't have a histogram on it.  A histogram would tell Oracle how relatively common or rare those values are in your table.  If that doesn't fix it, the USE_CONCAT hint can force Oracle to change Query 2 above into Query 5.


You need to ALTER a column from TEXT to MEDIUMTEXT?

Such an ALTER will copy the table over, making the change as it goes.  At the end, rebuild the indexes and will RENAME the table back into place.  The table will be blocked from (at least) writes for the duration, which could be hours.

For a less invasive approach, see pt-online-schema-change.  It will add a Trigger to the table so that writes are tracked while it copies the table over.  It ends with a brief finishing lock to flip the table into place.

Either approach will take about 5.7GB of spare disk space.  Caveat:  If you have innodb_file_per_table = OFF, the spare disk space will not be freed when finished.  I recommend ON when creating 'big' InnoDB tables.


Using Ola Hallengren’s Maintenance in SQL Server 2016 with TDE Enabled databases and compression

According to Ola's SQL Server Maintenance Solution Version History
(highlighting mine)

  25 Sep 2016

  Improved handling of databases with Transparent Data Encryption (TDE)
  together with backup compression. If you have databases with TDE in
  SQL Server 2014 and earlier, backup compression is not used. If you
  have databases with TDE in SQL Server 2016, backup compression is used
  if MAXTRANSFERSIZE is greater than 65536.

You should make sure that you can restore these compressed backups.  There were some problems as referenced by this Stack Exchange post (Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used)


MySQL has built in deadlock detection and detects a deadlock as soon as it occurs.  If you need to detect deadlocks, you will have to examine the error message coming back from the failed transaction, or use pt-deadlock-logger to log deadlocks external to the application.

The code you have supplied doesn't detect deadlocks.  It detects failed transactions and retries 3 times.  If the transaction took a long time, you will occupy an apache worker for three times as long as the transaction took.  If the failure occurs due to a lock timing out (note locks and deadlocks are very different), the code above could take up to 2.5 minutes to return to the browser, assuming you have innodb_lock_wait_timeout set to the default of 50 seconds.  That's a long long time.

Can we step back and ask what is it that you are trying to accomplish with this code path?

(side note, I think this belongs on stackoverflow, not here as this seems more like a PHP/development question).


Insert mysql query results into database column with corrensponding ids

It ais not a good ides to have a comma separated column in your database see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad
But you can make following

CREATE TABLE Table_A
    (`Submission_id` int, `Salesman` varchar(9), `Customer name` varchar(9), `Hobby` varchar(13), `Carline` varchar(9), `Fuel` varchar(5), `Marketing_data` varchar(24))
;

INSERT INTO Table_A
    (`Submission_id`, `Salesman`, `Customer name`, `Hobby`, `Carline`, `Fuel`, `Marketing_data`)
VALUES
    (1, 'salesman1', 'customer1', 'hobby1', 'car1,car2', 'Fuel1', 'hobby1,car1,car2,Fuel1'),
    (2, 'salesman2', 'customer2', 'hobby1,hobby2', 'car1', 'Fuel2', 'hobby1,hobby2,car1,Fuel2')
;

CREATE TABLE Table_B
    (`option_value` varchar(6), `option_name` varchar(8))
;

INSERT INTO Table_B
    (`option_value`, `option_name`)
VALUES
    ('hobby1', 'Football'),
    ('hobby2', 'Golf'),
    ('car1', 'SUV'),
    ('car2', 'Cabrio'),
    ('Fuel1', 'Gas'),
    ('Fuel2', 'Hybrid')
;

UPDATE
Table_A a
SET `Marketing_data` = (SELECT GROUP_CONCAT(DISTINCT c0.option_name)Marketing_data
FROM (SELECT * FROM Table_A) d
JOIN Table_B c0 ON FIND_IN_SET(c0.option_value, d.Marketing_data)
 WHERE a.`Submission_id` = d.`Submission_id`
 GROUP BY d.Marketing_data)

SELECT * FROM Table_A;

Submission_id | Salesman  | Customer name | Hobby         | Carline   | Fuel  | Marketing_data
------------: | :-------- | :------------ | :------------ | :-------- | :---- | :-----------------------
            1 | salesman1 | customer1     | hobby1        | car1,car2 | Fuel1 | Cabrio,Football,Gas,SUV
            2 | salesman2 | customer2     | hobby1,hobby2 | car1      | Fuel2 | Football,Golf,Hybrid,SUV

db<>fiddle here


Declare the @pname as varchar(x)  where x is the length of the Physician.Name column.  Make the change in both pieces of code.

Declaring the variable as just varchar indicates you only want a single character at most.


You must go with innodb_file_per_table and you need to do some how cleaning with InnoDB's current infrastructure.

I have seen many DB hosting clients setup MySQL and leave InnoDB in its default state. This causes the system tablespace (better known as ibdata1) grow wildly.

Even if you switched to innodb_file_per_table, the .ibd file would have to be extracted from ibdata1 and ibdata will never shrink. For example, if you have a table called mydb.mytable that is inside ibdata1 taking up 2GB, to extract it you have to the following:

STEP 01) Add this to /etc/my.cnf

[mysqld]
innodb_file_per_table

STEP 02) service mysql restart

STEP 03) ALTER TABLE mydb.mytable ENGINE=InnoDB;

That will make the file /var/lib/mysql/mydb/mytable.ibd

Unfortunately, the 2GB of space that was occupied by the table before the change cannot be reclaimed. I wrote past posts about how and why to cleanup InnoDB's infrastructure:

Why does InnoDB store all databases in one file?
Scheduled optimization of tables in MySQL InnoDB
ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
My Original Post in StackOverflow

Once you make this major change, don't forget to increase innodb_open_files (default 300). Otherwise, disk access is very limited.

With regard to joins, make sure you have proper indexes that support the join criteria.

UPDATE 2012-04-02 11:30 EDT

Using innodb_file_per_table in a fresh installattion cause ibdata1 to grow very slowly because all DDL is done external to ibdata. You can shrink any InnoDB table as I mentioned before like this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

UPDATE 2012-04-02 16:50 EDT

When it comes to backups, be extremely careful making copies of .ibd files. Why?

Inside every .ibd file is special value known as the tablespace_id. There is a list of tablespace_id values in ibdata1. If you ever perform table maintenance that requires dropping and recreating the table, the tablespace_id will become different. Making a copy of such  a .ibd file can only be intergated back into the database for usage if you also make a copy of ibdata1. That endangers the tablespace_id of all other InnoDB tables. In light of this, it is preferable that you perform mysqldump backups because mysqldumps are logical copies of the data. In other words, the backup is independent of the point-in-time of the ibdata1 and you are free to reload without operability issues.


How do I view operators in a Notify Operator task in a Maintenance Plan?

Two ways come to mind, there may be more. I prefer the second but that's just my opinion

Import-Module SQLPS -DisableNameChecking
Get-ChildItem SQLSERVER:\SQL\ServerNameHere\DEFAULT\jobserver\Jobs |Select Name, OperatorToEmail

The first line below loads the 2012 assembly since that's the version I'm running on my laptop, adjust to suit your needs.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$srvObject = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'ServerNameHere'
$srvObject.JobServer.Jobs| Select Name, OperatorToEmail


Socket here means physical CPU plugged into the motherboard.

Note that a single socket will consist at least one "core".

From the MySQL products page:

  5 Socket: is defined as a slot that houses a chip (or a multi-chip module), which contains a collection of one or more cores. Regardless of the number of cores, each chip (or multi-chip module) counts as a single socket. All occupied sockets on which the Oracle programs is installed and/or running must be licensed.

** I use "plug" loosely nowadays of course


How do you reset the master key in sql server 2005?

Assuming that you are trying to reset the database master key you just need to use the ALTER MASTER KEY statement adding in the keyword FORCE.

ALTER MASTER KEY
FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'YourNewPassword!';


When to apply normalization in database design

It sounds like you learnd it somewhere in an CS course or similar (which is a good thing IMO). I learnd it in some classes as well and I liked the approach that was taken by the teacher:

colleagues from other universitites, courses,... told me they were given big papers that explained normalization in a very theoretical (therefore rather boring) manner and they had no clue how to move this theretical knowledge into an ER-diagram.

We, on the other hand spend many hours learning how properly design ER-diagrams, the ideas that make up a good and maintainable database, how to solve arising problems in the diagram as well as in the database (with SQL) itself and much more. After these concepts were thought, we were told "what you did is called 'normalization'". You have done this and that when designing the ER, this is the 1st form of normalization.....

A sentence that got stuck in my head for such questions from my teacher is "with a well-though and properly designed ER-model, you ALREADY have a normalized database, so you do not need to think about all the forms of normalization".

Since your question is not about HOW/WHY use normalization but WHEN in the designing process, I think my long text above gives you a possible answer: when you understood the principles of good DB design, than your final ER-model IS normailzed (e.g. you created proper join tables, used foreign keys were applicable,...). I think it is a process that needs to go hand-in-hand with the setup of the tables, their relations and so on.

Of course, normalization is not always needed or wanted but in most cases, "drawing" the ER and thinking it through saves you from applying normalization by looking at the model and thinking: "OK, 1st form, are my values as atomic as possible"


Reference a schema from public table column

You can store everything in public.messages, add column that describes the actual schema and make views in every schema, that are selecting only messages that needs to be visible in particular schema.
Depending on you needs you can create rules on views for data modification, partition that big table, add row level security.


How do you find SQL Server version/edition without SSMS installed?

You can try the command line tool:

C:\>sqlcmd -S [SERVER] -d [DB_NAME] -E -Q "SELECT @@VERSION"


Can I create a Azure SQL Server (server) with a different collation?

The collation for user databases can be specified for SQL Azure Database user databases nowadays (e.g. CREATE DATABASE YourDatabase COLLATE Latin1_General_CI_AS;).

EDIT:

The logical Azure SQL Server collation is fixed at SQL_Latin1_General_CP1_CI_AS and is not configurable (as of this writing). However, this collation is only used for system objects in master, tempdb, and user databases. It does not affect the collation of user objects in tempdb, which is the collation of the current Azure SQL Database context (not logical SQL Server) unless otherwise specified. This behavior differs from on-prem and IaaS SQL Server instances where the instance collation also determines the tempdb default database collation used for both system and user objects, therefore having a more significant impact.

With this in mind, you might think of system and user collations orthogonally in the Azure SQL Database world because you won't run into collation conflict errors in the user space as long as you use the same collation consistently. Also, if you use either Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS as your user database collation, you'll still get the same sorting and comparison behavior as system object names because Windows sorting and comparison rules are used for Unicode data and system object names are stored as nvarchar(128) (a.k.a sysname).


How can I maintain a "virtual column" which is composed of data from 2 tables?

You probably want to store event dates as TIMESTAMP WITH TIME ZONE because just the HH:mm:ss+TZ time without the corresponding date might be ambiguous or misleading -- for example, America/New_York timezone points to two different times during the summer and the winter due to daylight saving changes. Same goes for times that you query against (12:00):

CREATE TABLE users (
    id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR NOT NULL,
    timezone VARCHAR NOT NULL
);

CREATE TABLE events (
    id SERIAL PRIMARY KEY NOT NULL,
    user_id INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    start_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    end_timestamp TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX events_start_timestamp ON events (start_timestamp);
CREATE INDEX events_end_timestamp ON events (end_timestamp);

INSERT INTO users (name, timezone) VALUES
    ('Linas', 'GMT+2');
INSERT INTO events (user_id, start_timestamp, end_timestamp) VALUES
    (1, '2018-02-27 12:00:00-03', '2018-02-27 15:00:00-03');

With that in place, one can query against precalculated timestamps pretty easily (correct timezone conversion will be done behind the scenes by the database itself) and convert the resulting timestamp to user's timezone too:

SELECT users.id,
       users.name,
       (events.start_timestamp::TIMESTAMP AT TIME ZONE users.timezone) AS user_start_timestamp
FROM events
    INNER JOIN users ON events.user_id = users.id
WHERE start_timestamp >= '2018-02-27 11:00:00-03'::TIMESTAMP WITH TIME ZONE
  AND end_timestamp < '2018-02-27 16:00:00-03'::TIMESTAMP WITH TIME ZONE;

(SQL Fiddle)


Identify cause of unusual transaction log growth

This SQL shows queries with most "logical_writes":

SELECT TOP 100
    st.text,
    execution_count,
    total_elapsed_time,
    total_worker_time,
    total_logical_reads,
    total_logical_writes,
    total_physical_reads,
    total_clr_time,
    creation_time, last_execution_time,
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
ORDER BY total_logical_writes desc
OPTION (RECOMPILE);

This T-SQL could help to find direction, but it is not really what you asked for, as logical write != write. and logical write != log write.

It could be very very interesting to find more complete answer to your question.


Page Number in SQL Server database is not starting from zero

Let me preface this answer with the statement that there could be entire books written on this subject and this is without a doubt some of the most interesting and complicated parts of SQL Server.

This answer will not attempt to go into great lengths of detail, but a simplified overview of certain items.

  Q 1). Why my page not starting from 1:0 or 1:001?

In SQL Server there is the logical structure of your database and the physical. Since you're asking about the physical structure, there are two things at play here:

Internal data structures and tracking
Internal metadata storage

Your table doesn't start at page 0 of file 1, however that space in indeed occupied and used. This page is reserved and called the file header page, each file will have one of these.

Additionally there are GAM intervals which happen roughly every 4 GB, PFS that happen every roughly 64 MB, etc.

Finally there are the metadata tracking tables, such as sys.sysschobjs, which aren't readily available for consumption yet do need space in the database.

  Q 2).Will this page number have dependency from model database?

Simplistically speaking, yes. The more objects created and inherited from model, the more internal tracking and space that will be needed. This is grossly over simplified but should do, given the previous explanation as data can be extrapolated from it.

That's just the beginning ...

Allocation algorithms in SQL Server may be different between base objects. For example, when to allocate a new page may differ between heaps and clustered indexes. Where the data is allocated depends on how long the database has been in use, where it has been internally last filled to, and a host of many other different variables.

It truly is very interesting stuff and it's great to have fun with.


Database design : multiple category of users login into same system

The standard way to model permissions for a system is through Role-Based Security.  The typical model looks something like this:

The way it works is you have three tangible things: Users, Allowable Actions (the access that is being controlled) and Roles.  Roles are groups of both users and allowable actions, therefore there are two intersection tables which record the people in each role and the actions that role permits.  Some people include additional information in the ROLE MEMBERSHIP and PERMISSION intersection tables, like when the record was created and by whom.  Some people keep that kind of audit log information in a separate table.

In this model every type of user is stored in your USER table.  The rules that you have stated for cardinality of users (one owner, one assistant) are frankly very brittle if you ask me.  I don't like to cast rules into my data model which are almost certainly going to change.  It seems unreasonable to me that a business will use your system if it doesn't allow a manager to delegate their responsibilities for when they are off duty, on vacation or away from the business.  Your cardinality rules should be enforced in application logic or with additional tables related to USER if you really feel they need to be in the data model somehow.


Select into query, insert fails, but the table is created

In autocommit mode, SELECT...INTO executes 2 separate transactions. The first creates the table and the second loads the table. The table create will be committed even if the subsequent insert portion of the statement fails.

In old versions of SQL Server, both operations were done in a single atomic transaction but this was notorious for locking system tables and causing excessive blocking.


Finding violations of the symmetry constraint

To find the rows, use a left outer join:

select
    a.Friend1ID, a.Friend2ID, b.Friend1ID, b.Friend2ID
from
    Friends a left join Friends b
        on (a.Friend1ID=b.Friend2ID and a.Friend2ID=b.Friend1ID)
where
    b.friend1ID IS NULL ;


Retrieving most recent X rows for each given user from a table

I hope you are willing to do some programming outside of SQL.

To find the recipients that need the most pruning:

SELECT recipient
    FROM inbox
    GROUP BY recipient
    HAVING COUNT(*) > 30
    ORDER BY COUNT(*) DESC
    LIMIT 100

Then, for each recipient, first find the 31st id:

SELECT id
    FROM inbox
    WHERE recipient = $recipient
    ORDER BY id DESC
    LIMIT 30,1

And delete the excess baggage:

DELETE FROM inbox
    WHERE recipient = $recipient
      AND id < $id

You would need INDEX(recipient, id).

This process could be running continually.  The first SQL would be the slowest, but it would run "Using index" and probably not be too bad, maybe 0.1 sec for 200K rows in inbox.

If you want to stay in SQL, this would let you do one recipient at a time:

BEGIN;
SELECT @recipient := recipient
    FROM inbox
    GROUP BY recipient
    HAVING COUNT(*) > 30
    ORDER BY COUNT(*) DESC
    LIMIT 1
    FOR UPDATE;
SELECT @id := id
    FROM inbox
    WHERE recipient = @recipient
    ORDER BY id DESC
    LIMIT 30,1
    FOR UPDATE;
DELETE FROM inbox
    WHERE recipient = @recipient
      AND id < @id;
COMMIT;

Then, put that in a Stored Procedure with a loop around it.  And, if you want to be further 'nice', add a SELECT SLEEP(1); in the loop but outside the transaction.

(Trying to do the entire thing in a single statement makes my brain hurt.)


Foreign key as part of composite primary key or not?

It depends on the specific scenario.

For example, if you have a many-to-many relationship, then it is common to have a join table between the two tables. The join table will contain a foreign key to each of the primary keys and most of the time, the combination of the two foreign keys will become the primary key:

CREATE TABLE users
(
    id INT NOT NULL PRIMARY KEY
);
CREATE TABLE roles
(
    id INT NOT NULL PRIMARY KEY
);
CREATE TABLE users_roles
(
    user_id INT NOT NULL REFERENCES users(id),
    role_id INT NOT NULL REFERENCES roles(id),
    PRIMARY KEY (user_id, role_id)
);

For the most part, I prefer natural keys rather than surrogate keys - especially if I will never use the surrogate key to ever query the table.

With that being said, to answer your question directly:

Firstly, can a string be a primary key? Though the name may be its natural key, you have to take into consideration the size of the key if you made it the primary key. Not just in your main table, but also in the tables that refer to it. Remember, that in order to create a foreign key you must copy the key value into any dependant tables - this can quickly bloat your database and slow down queries depending on the size and type of data. For instance, short fixed-length text columns can perform better than variable-length text columns and can be indexed easier than longer text values. A good example of this that I use each day is a currency table. My primary key is a 3 character currency code - it is short, of a fixed length, and I am always going to query the table using it even if the table had a surrogate key defined on it.

CREATE TABLE DimCurrency
(
    Code CHAR(3) NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);
CREATE TABLE FactSales
(
    CurrencyCode CHAR(3) NOT NULL REFERENCES DimCurrency(Code),
    Date DATE NOT NULL,
    SalesAmmount DECIMAL(18,2) NOT NULL
);

Secondly, does the product id need to be part of the productions primary key? Well, this depends on a few factors. If you made the name part of the primary key, and the name was short then potentially, yes it could. But, does any other table have a reference to the productions table? If so, then by adding the product id into the tables primary key you would also need to add it into all of the foreign keys that refer to it. Personally, I don't like this. I am happy to do it if I can guarantee that no table will ever need a foreign key to it (such as a fact table in a dimensional model) but otherwise I think it is wasteful and makes querying your tables over complicated.

You should also consider that if you define further non-clustered indexes on the production table when you have a composite key, the row locator for the non-clustered index is a copy of the primary key itself. This can cause all of your non-clustered indexes to bloat out of control very quickly without your realising it. For reference: Clustered and Nonclustered Indexes Described

I think given your particular scenario, it is perfectly reasonable to add a foreign key in your production table which refers to the id of the product that is produced. As Gulrez Khan stated in his answer, you may want to add a non clustered index on the foreign key - it is not required, but can enhance performance in some queries.

CREATE TABLE production
(
    id INT NOT NULL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES product(id),
    name VARCHAR(50) NOT NULL UNQIUE
);

CREATE INDEX ix_production_product_ncl ON production(product_id);

Remember that all indexes need to be built, and rebuilt when they are modified. The more fields you cram into them, the longer it takes to perform actions against them. Especially clustered indexes, which are generally defined upon the primary key. These indexes physically sort your data at the storage level. If you insert a value that happens to not be at the end of the existing chain of values, you could cause performance problems due to page splits. For me, this is the biggest advantage of a surrogate key over a natural key - the ability to ensure that new data is always appended and not injected into the middle of an existing index.

Finally, if in doubt - try it out! There is no substitution for a good testing strategy. Try querying the data against both structures, look at the query plans, do some capacity planning and choose the one that is right for you.


Suppressing the SSMS prompt to save changes

In SQL Server 2016 this is apparently coming.

The pre-release docs says

  New option to skip prompt to save T-SQL files We have added a new
  option under "Tools|Options|Query Execution|SQL Server" to control if
  a prompt is shown for unsaved T-SQL files.

In previous versions there is no setting to suppress this warning.

A connect item was raised about this by Erland Sommarskog and closed as won't fix with the explanation.

  Hello Erland -

  I did a review with some of the other product teams and to stay
  consistent within our guidelines we're not going to be able to do
  this. Excel, Visual Studio and other products won't let you save with
  changed content, so we have to pop the message.

  Thanks!

  Buck Woody, SQL Server Program Manager

The Connect page is returning an error at the moment so here is a cached copy of it.


To insert lobs over a database link, your inserts need to be in the form:
INSERT INTO table1@remote_site SELECT * FROM local_table;
http://docs.oracle.com/database/121/ADLOB/adlob_working.htm#ADLOB45148
You can do this by:

Creating a temporary table
Loading your clob data into the temp table
The insert over the DB link with "insert@link .. select"

For example:
create table t (
id int,
x clob
);
create global temporary table gtt (
id int,
x clob
) on commit preserve rows;
declare
l clob;
begin
insert into gtt values ( 1, empty_clob() )
returning x into l;
for i in 1 .. 10 loop
dbms_lob.writeappend(l, 4000, lpad('x', 4000, 'x'));
end loop;
commit;
insert into t@loopback
select * from gtt;
end;
/
select id, length(x) from t;
    ID  LENGTH(X)

     1      40000

(Loopback is a DB link pointing back to the same database)


Index SEEK is not used unless OPTION (RECOMPILE)?

Summarizing some of the main points from our chat room discussion:

Generally speaking, SQL Server caches a single plan for each statement. That plan must be valid for all possible future parameter values.
It is not possible to cache a seek plan for your query, because that plan would not be valid if, for example, @productid is null.
In some future release, SQL Server might support a single plan that dynamically chooses between a scan and a seek, depending on runtime parameter values, but that is not something we have today.
General problem class
Your query is an example of a pattern variously referred to as a "catch all" or "dynamic search" query. There are various solutions, each with their own advantages and disadvantages. In modern versions of SQL Server (2008+), the main options are:

IF blocks
OPTION (RECOMPILE)
Dynamic SQL using sp_executesql

The most comprehensive work on the topic is probably by Erland Sommarskog, which is included in the references at the end of this answer. There is no getting away from the complexities involved, so it is necessary to invest some time in trying each option out to understand the trade-offs in each case.
IF blocks
To illustrate an IF block solution for the specific case in the question:
IF @productid IS NOT NULL AND @priceid IS NOT NULL
BEGIN
    SELECT
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid
        AND T.priceID = @priceid;
END;
ELSE IF @productid IS NOT NULL
BEGIN
    SELECT
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid;
END;
ELSE IF @priceid IS NOT NULL
BEGIN
    SELECT
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.priceID = @priceid;
END;
ELSE
BEGIN
    SELECT
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T;
END;

This contains a separate statement for the four possible null-or-not-null cases for each of the two parameters (or local variables), so there are four plans.
There is a potential problem there with parameter sniffing, which might require an OPTIMIZE FOR hint on each query. Please see the references section to explore these types of subtleties.
Recompile
As noted above an in the question, you could also add an OPTION (RECOMPILE) hint to get a fresh plan (seek or scan) on each invocation. Given the relatively slow frequency of calls in your case (once every ten seconds on average, with a sub-millisecond compilation time) it seems likely this option will be suitable for you:
SELECT
    T.productID,
    T.priceID
FROM dbo.Transactions AS T
WHERE
    (T.productID = @productid OR @productid IS NULL)
    AND (T.priceID = @priceid OR @priceid IS NULL)
OPTION (RECOMPILE);

It is also possible to combine features from the above options in creative ways, to make the most of the advantages of each method, while minimizing the downsides. There really is no shortcut to understanding this stuff in detail, then making an informed choice backed by realistic testing.
Further reading

Parameter Sniffing, Embedding, and the RECOMPILE Options
Dynamic Search Conditions in T-SQL by Erland Sommarskog
High Performance Procedures by Kimberly Tripp


postgresql : **ERROR: path element at position 1 is not an integer:**

Your json value is an array, not a single value. So you need to pick the array element you want to change by including the index in the array you pass to jsonb_set()
UPDATE temp_table
  SET owner_property = CASE
                        WHEN owner_outgoing_edge IS NOT NULL
                          THEN jsonb_set(owner_property, '{0, OutgoingVertexid}', '""')
                        ELSE
                          jsonb_set(owner_property, '{0, OutgoingVertexid}', to_jsonb(owner_incoming_edge))
                       END


This should work for you.  At least for the entries in the PowerCurve table that I copied over, it works correctly.  This should get you close.  See comments in the code for more details.

Assistance from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql on how to round to nearest .5/0.

/** FIRST - we setup the data and tables we need.

    Please note that I didn't copy the entire table of dbo.PowerCurve
    across, so my output is not as complete as yours, but this should
    get you close.
    */

DROP TABLE IF EXISTS dbo.Windspeed
DROP TABLE IF EXISTS dbo.PowerCurve
DROP TABLE IF EXISTS dbo.NameLookup

CREATE TABLE dbo.Windspeed
    (
    [TimeStamp] DATETIME NOT NULL
    , [A-004] DECIMAL(5,2) NOT NULL
    , [A-005a] DECIMAL(5,2) NOT NULL
    , [A-006a] DECIMAL(5,2) NOT NULL
    , [E-009] DECIMAL(5,2) NOT NULL
    )

INSERT INTO dbo.Windspeed
([TimeStamp], [A-004], [A-005a], [A-006a], [E-009])
VALUES ('4/9/2019 00:00:00', 13.09, 13.95, 10.16, 11.71)
    , ('4/9/2019 00:05:00', 12.17, 11.98, 12.54,10.66)

CREATE TABLE dbo.PowerCurve
    (
    WindSpeedBIN DECIMAL(5,2) NOT NULL
    , Power_Wdspd DECIMAL(10,2) NOT NULL
    , Turb_Num INT NOT NULL
    )

INSERT INTO dbo.PowerCurve
(WindSpeedBIN, Power_Wdspd, Turb_Num)
VALUES (13.0, 1840, 1)
    , (13.5, 1846.1, 1)
    , (13.0, 1833.1, 2)
    , (13.5, 1846.1, 2)
    , (12.0, 1798.3, 1)
    , (12.5, 1828.5, 1)
    , (12.0, 1713.3, 2)
    , (12.5, 1801.5, 2)

CREATE TABLE dbo.NameLookup
    (
    TName VARCHAR(10) NOT NULL
    , Tag VARCHAR(10) NOT NULL
    , TNum INT NOT NULL
    )

INSERT INTO dbo.NameLookup
    (TName, Tag, Tnum)
VALUES ('A-004', 'A04', 1)
    , ('A-005a', 'A05', 2)
    , ('A-006a', 'A06', 3)
    , ('A-008a', 'A08', 4)
    , ('E-009', 'E09', 5)

Get the data

/** GET THE DATA
    - CTE_Source - unpivots so we can get the TNum and PowerCurve
    - CTE_Join - looks up the TNum in dbo.NameLocation and dbo.PowerCurve
    - CTE_Pivot - pivots the data back to make the output what we want
    **/

;WITH CTE_Source AS
    (
    SELECT [TimeStamp]
        , [TName]
        , [Value]
    FROM dbo.Windspeed AS W
        UNPIVOT ([Value] FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    )
, CTE_Join AS
    (
    --NOTE rounding answer from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql
    SELECT S.[TimeStamp]
        , S.[TName]
        , S.[Value]
        , CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2)  AS RoundedValue
        , NL.TNum
        , PC.Power_Wdspd
    FROM CTE_Source AS S
        LEFT OUTER JOIN dbo.NameLookup AS NL ON NL.TName = S.TName
        LEFT OUTER JOIN dbo.PowerCurve AS PC ON PC.WindSpeedBIN = CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2) AND PC.Turb_Num = NL.TNum
    )
, CTE_Pivot AS
    (
    SELECT [TimeStamp]
        , MAX([A-004]) AS [A-004]
        , MAX([A-005a]) AS [A-005a]
        , MAX([A-006a]) AS [A-006a]
        , MAX([E-009]) AS [E-009]
    FROM CTE_Join AS J
        PIVOT (MAX(Power_Wdspd) FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    GROUP BY [TimeStamp]
    )
SELECT * FROM CTE_Pivot


Designing a database structure for Partners and Websites

I would suggest that you would still want the relationships listed in your junction table, so that your data model is consistent. Also otherwise you end up having to UNION two results together all the time (one side for the "all"s and one side for the "some-or-none"s) or having filtering clauses of the form property1 = value OR property2 IN (<set>) - both of which can cause efficiency issues depending on the query planner of the engine you are using.

To enforce that P3 relates to all new entries in WebSites without needing to do that everywhere in your business logic that might add sites, you could perhaps define a trigger on that table which checks for Partner entries that are set as global and adds the relationship rows as needed. You need to take care with triggers as they can be quite inefficient (though for most database entities a performance hit on writing is worth a boost when reading, as read operations are far more numerous and concurrent). The other thing to be wary of with triggers is that magic actions behind the developer's back can make debugging more difficult, but if you make sure all is well documented and understood by your team that need not be an issue.


Here's a version that does not give a syntax error:

delimiter !!
drop function _increase_num !!
CREATE FUNCTION _increase_num()
returns int
begin
    return IF(@counter, @counter:=@counter+1, @counter:=1);
end !!
delimiter ;

A bit of warning though, whenever counter looses its value it will start from 1. Also, I would not put any money on how well this works when the function is called in parallel from several processes.

Edit:
";" has a special meaning in stored procedure language, it separates statements within the procedure. This is why you normally change the statement terminator to something else:

delimiter @

However, for this trivial procedure, we don't need the begin/end block. That is:

CREATE FUNCTION _increase_num()
returns int
    return IF(@counter, @counter:=@counter+1, @counter:=1);

will do.


Postgres full text search on words, not lexemes

Just use the simple text search configuration, which works with the simple dictionary and does not do any stemming:

CREATE INDEX ON tbl USING GIN (to_tsvector('simple', data));

And query accordingly:

SELECT *
FROM   tbl
WHERE  to_tsvector('simple', data) @@ to_tsquery('simple', 'foo');

Related:

Get partial match from GIN indexed TSVECTOR column
Finding the most commonly used non-stop words in a column
PostgreSQL full text search on many columns


Transactional Replication - Replication Order of Related Tables

Looks like the pertinent part (in this case) was to ensure that both the parent and child tables are contained within a single publication; once we did this, then everything started to behave as it should, and 30 or 40 tests later we haven't seen any failures.

At a guess... this means that both tables' log entries will be read by the same log reader job on the distributor, and so will be read in the correct order. Having separate publications allows separate log reader jobs to run in parallel, leading to the situation we were seeing.


Install Oracle 11gR2 driver for Node.js 0.10.20

This means that you have to include Oracle instant client libraries into the database used by Linux dynamic linker when it has to resolve run time bindings.

Instructions tells you to create new .conf file in /etc/ld.so.conf.d/ directory with the Oracle instant client libraries directory. And then you have to run ldconfig with root permissions so that it will create new database with the Oracle instant client libraries included.

Example from one of our servers:

# cat /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/11.2/client64/lib/

.conf file name does not matter.


Setting up the Listeners for Oracle Dataguard

You should have a TNS entry, in the client machine, similar to following to connect to the data guard environment.

BR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BR)
    )
  )

BR_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BR_STBY)
    )
  )

You also should have database service configured according to their role-primary or standby as shown below(if you are using Grid Infrastructure).

srvctl add service -d BR  -s BR -l PRIMARY -m BASIC -e SELECT -w 1 -z 180
srvctl add service -d BR  -s BR_STBY -l PHYSICAL_STANDBY –m BASIC –e SELECT –w 1 –z 180
srvctl start service -s BR -s BR

If you are not using Grid Infrastructure then(AS SYSDBA on Primary)-

SQL>exec dbms_service.create_service(service_name => 'BR', network_name => 'BR', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 10, failover_delay => 1);
SQL> exec dbms_service.create_service(service_name => 'BR_STBY', network_name => 'BR_STBY', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 10, failover_delay => 1);
SQL> create or replace trigger MANAGE_SERVICES
  after startup on database
declare
  db_role varchar2(16);
  db_mode varchar2(20);
begin
  select database_role, open_mode into db_role, db_mode from v$database;
  if db_role = 'PRIMARY' then
    dbms_service.start_service('BR');
    dbms_service.stop_service('BR_STBY');
  elsif (db_role = 'PHYSICAL STANDBY') and (db_mode = 'READ ONLY') then
    dbms_service.start_service('BR_STBY');
    dbms_service.stop_service('BR');
  else
    dbms_service.stop_service('BR');
    dbms_service.stop_service('BR_STBY');
  end if;
end;
/
SQL>

Modify the attributes used in the above examples according to your need and refer to the Oracle documentation for details.