Possible pitfalls of condensing 2 SQL Server instances into 1 I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active).  We are planning an upgrade to SQL Server 2008 R2, and are investigating the possibility of condensing these 2 instances into 1 for several reasons (ease of use, fewer client DSNs, cheaper licensing).  The two servers are referenced by each other as linked servers (and that link is used a lot), and there are other servers involved which are also linked servers.

I understand I'll need to refactor all the stored procedures and views which reference the linked servers, but what other potential problems should I look out for?  What should I check/do to determine if this is a good idea or not?

I believe consolidation like you are planning to do is a good thing.  Depending on how the linked servers are queried, you may find a huge jump in performance (cross server joins are usually quite horrible when compared to cross database joins on the same server).

The downside is that your entire system is now sitting on one machine.  When this machine is down, all your apps are down.

Here is what I suggest you do to prepare:

Run the SQL Server Upgrade Advisor against each database.  It will tell you of any incompatible code that may exist (e.g. "*=" join syntax, no longer supported).

Identify DTS packages running against the old systems.  DTS is no longer supported.  You will have to rewrite them using SSIS.

Make sure the new system is powerful enough to handle the workoad.


design of maintenance database Lets say i am designing a database that tracks maintenance of equipment.

Each piece of equipment has various fields that usually don't change eg. ID, model, serial number etc.

Some fields change frequently - during maintenance eg, status, location, firmware, configuration options etc. And these fields should keep a history.

Is it better to have one table with all these frequently change fields? eg tracking table, each change to any one field will create a new record, copying the field values from the previous (with updates) and the current date time.

OR better to keep separate table for each field? eg status table, location table, each table will have own date time, edited by user etc fields

One benefit i see in the first method is easier to look up the date and find the state of all fields at that time. The drawback perhaps is use up more space and some field may change more frequently than others.

IMO It is much better to have a design with multiple tables.  You will be able to track changes more effectively that way.

Also, for the fields that change, I would not go with a Name-Value pair of storage.  I would actually create tables with appropriately named columns.

Is data space really an issue for you?  You should run a few calculations estimates to see if space is really going to be an issue.  You'll be very surprised to see how much of a non-factor hard disk space is.


PostgreSQL Continuous Archiving & Point-in-Time Recovery in combination with netbackup I have the following setup:

OS: CentOS
PostgreSQL 12
Netbackup 8

What I want to achieve:
Configure continuous archiving with the possibility of Point-in-Time Recovery
My current idea:

Generate a pg_basebackup every day at midnight and put the generated files in a folder outside of the data folder
Delete the files older than the created backup from the pg_wal folder
Create a Netbackup of the VM

With these three steps I should be able to make a PITR every day starting from the last base backup until the upcoming base backup, and my pg_wal folder will never grow too much. Also the usage of the internal archiving is not required.
Somehow this sounds very simple and I would like to ask if I am missing something here?

There is one crucial thing wrong here: you never, ever delete files from pg_wal.
You need to configure archive_mode = on and set archive_command so that PostgreSQL archives transaction log files when they have been completed.
Much simpler than creating a pg_basebackup on a local disk and then backing that up would be to backup the data directory directly. You have to get PostgreSQL into backup mode first (pg_start_backup) and end backup mode once you are done backing up files (pg_stop_backup). See the documentation for more.


SQL syntax issue with geometry I must be missing something pretty basic, but i have 2 tables.  Both are basic, just a primary key (as a uniqueidentifier) and a geometry column in each.  I'm trying to find if a point in one of the table exists in the polygon in the other table.

So this works without any problems (returns a row from PolygonTable):

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT (-117.6743333 33.8343333)', 0);
select * from PolygonTable where Polygon.STIntersects(@g)= 1

and this works as well

declare @h geometry
select @h =  Point from PointTable where PointGUID=N'3B77C26E-ACAC-4328-ED94-08D5EDE20723'
SELECT @h.ToString();  // outputs POINT (-117.6743333 33.8343333) above

but for some reason, the following does NOT work

declare @h geometry
select @h =  Point from PointTable where PointGUID=N'3B77C26E-ACAC-4328-ED94-08D5EDE20723'
select * from PolygonTable where Polygon.STIntersects(@h)= 1

That query returns no rows at all.  it's the same thing at the top query basically, what am i missing?

here is a gist of the basic table settings:
https://gist.github.com/vishnu4/9bd70658b49c2cfa00ca81ff1d25bc84
you can use the queries i have above to test my results.

The issue you are having is because your GEOMETRY points are not in the same SPACE REFERECEN ID.  For the first case you are in SRID = 0, for the second case you are in SRID = 4326. This makes the Intersection returns an empty list.

Check this documentation:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stintersects-geometry-data-type?view=sql-server-2017


Differntial back up: what happens to existing data? Ok, this is a question borne purely from paranoia.  But i want to ask all the same.

If I make changes to a db, and then apply a differential back up at a later date, what happens to the changes I have made?

So:

1: Take full back up.

2: Take diff.

3: Change data in the DB.

4: Apply diff to db.

EDIT:

Basically I'm copying a db, and I'm wondering if its quicker for me to reprocess some data, rather than wait for a diff.  But if the diff arrives before the processing finishes, what would happen to the data I have reprocessed when the diff is applied.

EDIT 2:

The reason for wondering is that we are moving data centers.  So i have one currently live environment, and am in the process of moving live to a new location.  We have large DBs though that are taking days to move, and im wondering if i just re process the data (days of processing).  If the diff arrives before the reprocessing is finished, i was wondering what the result of applying the diff would be (rollback to when the diff was taken?), or if we would have to restore both the full and then apply the diff, making reprocessing pointless.

(My gut tells me it will roll back to the when the diff was taken, but i want to really make sure this is right).

Cheers.

Since you'd have to apply the differential backup's previous full backup, the end result is identical to if you'd just taken a full backup, made changes, then restored the backup you'd just taken - any changes made would be lost.

There are, however, tools and techniques which would allow you to perform partial restores, so you could limit the application of a differential backup to just objects which had not been modified by your changes.

Since you can't apply a differential to a database that has been recovered ( as in, restored from a full backup without the NO_RECOVERY option or otherwise WITH RECOVERY'd thereafter ) and you can't modify a database that is offline ( in this case, in recovery would be the offline state in question ), trying to do so would not only negate any changes that were made, but require a complete full restore to be applied again before the newly arrived differential could be applied.

More information regarding the reason behind your copy process ( DR redundancy system, development environment, read-only reporting server, etc ) would be needed to make a recommendation on a suitable technique for your specific problem.


Where is the list of attached databases stored? I know database instance names are stored in the registry in subkeys of HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL and the path to master.mdf is stored in HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\__InstanceId__\\MSSQLSERVER\Parameters@SqlArg0. However, I don't see a table in master.mdf or a registry key where the names of the other attached databases are stored.

I know I can look in sys.databases for the databases. However, what I want to know is how to find this information out without sql server running. Is it stored in the master.mdf? In the registry? Can I get that information with a hex editor, OrcaMDF or something else? How does Sql server know what databases to attach at startup?

I don't see a table in master.mdf ... where the names of the other attached databases are stored.

That would be sys.databases. Running sp_helptext 'sys.databases' will quickly reveal what is the catalog table backing it:

CREATE VIEW sys.databases AS
    SELECT d.name, d.id AS database_id,
...
FROM sys.sysdbreg d
...

To query sys.sysdbreg see Using a Dedicated Administrator Connection.


Single Publication With Multiple Subscribers What should I be aware of before adding multiple subscribers to single publication?

Obvious ones I can think of are server capacity and network bandwidth.

Should I be aware of anything else?

Make sure distribution database is set up correctly with data and log file sizes based on the volume of data you are planning to replicate. I am not sure which version of SQL you are using, but i had issues when i was replicating from 1 publisher to 2 subscribers in SQL 2008 during re initialization. My issue was related to virtual memory being maxed out at one subscriber. It may not be applicable in your case.


SSRS Data Source enabled/disabled This is completely lame that I cannot find it, so I have to ask. I know in the reportsmanager site where to enable and disable data sources. But where in the tables of the reporting database is this stored? I've found the data source in the Catalog table, type 5, but don't see anything that remotely looks like a disabled/enabled field. I've also checked several of the other tables, like Policies and DataSource and SecData and etc..., but cannot find it. Can someone enlighten me please. I want to programmtically enable and disable data sources for certain reports, hence the reason I ask. SQL 2014 Standard. Thanks.

It's stored in the Content column in the Catalog table as XML, and the Content column is in binary - so you'll need to decode it from binary and find the Enabled XML element.  For example, this will give you a list of the shared Data Sources with their enabled/disabled status:

;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.Enabled
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('Enabled[1]', 'bit') AS [Enabled]
     FROM SDS
          CROSS APPLY
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON


Split brain scenario - RAC and PXC Even though split brain scenario occurs in both Oracle RAC and Percona's XtraDB Cluster, a two node cluster is allowed and split brain scenario is resolved in RAC but a two node is not recommended in Percona Cluster ( 3 nodes is recommended ). Why is it like that? Doesn't Percona resolve split brain happening in two node cluster? I know I'm comparing two different technologies.. but still if anyone could clear this out.

Thanks!

I can see why Oracle RAC can handle split brain and PXC cannot.
What separates them in their architecture and data storage. In what ways ???
Oracle RAC

All RAC Instances deal with only one set of database files
Each RAC Instance has its own Log Buffer and LGWR (Log Writer) process
Writes to any Oracle RAC instance, even on nodes that go down temporarily, can have their log sequence numbers coalesced and serialized

Percona XtraDB Cluster

Each PXC node has its own complete set of data
Writes to one PXC node must be rolled back or committed on all nodes in unison
Writes to PXC nodes that are split in communication will only update/commit to its local copy of the data. Awareness of such writes by other unconnected PXC nodes does not exist when communication has been established again.

According to Percona's Frequently Asked Questions on PXC

Q: How would it handle split brain?
A: It would not handle it. The split brain is hard stop, XtraDB Cluster can’t resolve it. That’s why the minimal recommendation is to have 3 nodes. However there is possibility to allow a node to handle the traffic, option is:
wsrep_provider_options="pc.ignore_sb = yes"

SUMMARY

Oracle RAC is ACID compliant as a Cluster (again, one set of database files)
Percona XtraDB Cluster abides by CAP Theorm instead of ACID compliance
See YouTube video where Vadim Tkachenko (Percona CTO) discusses the split-brain scenario and how it is your responsibility (Video starts from 17:00 mark, listen up to 19:20 or more)


Expressing Constraints Formally I have been asked a question related to database constraints. I am familiar with Oracle DDLs and DMLs and know how to define FK, PK and standard constraints related to relations / tables using DDL.

What I am not so sure of was when I was asked to present constraints formally using Relational Calculus.

So my question is:

Can someone provide an example of a simple constraint expressed formally using Relational Calculus?

Relational calculus is a notation for expressing relation values in terms of given named relations and sometimes constants. So is relational algebra. A constraint is an expression making a statement about one or more relation values. But such a statement is not relation-valued, so you can't express it in relation-valued notation alone. (So you need to ask what exactly is wanted.)

You have to have operators returning equality of relations, or whether a relation is empty, etc. (Either of those two is sufficient.)

-- every teacher is a person
empty({<id>|(exists class Teaches(id, class)) and not Person(id)})

A query is typically like {tuple|formula}. You could use an expression like the formula part of a query if you consider any free variables/attributes to be implicitly universally quantified. (This is also sufficient.)

-- id is a superkey of Teaches
-- forall id, c1, c2
    Teaches(id, c1) and Teaches(id, c2) implies c1=c2

(empty({<...>|formula}) is the same as forall ... notformula.)


How do I get value from parent row I have two tables.

One contains a hierarchical structure of topics and another table that contains optional configuration of that topic, that controls it's visibility in several contexts (one column for each).

Let's call these tables Topic and Conf respectively.
The relationship between Topic and Conf tables is 1:0..1, (a particular topic might have a row for it's respective conf or not, in which case it inherits conf from it's parent topic)

Question: How can I create a query that will list all visibility options for all topics (including those inherited from parent conf)?

I do have an idea how to create a hierarchical query of topics using START WITH ... and CONNECT BY ... clauses, but I do not know how to promote values from parent row.

May be this will help.

with topicdet as
(select t.topic_id as topic,c.conf as configuration,t.parent_topic_id as parenttopic from topic t left outer join config c on t.topic_id=c.topic_id)
select a.topic, NVL(a.configuration,CONNECT_BY_ROOT configuration) as conf from topicdet a
connect by nocycle prior a.topic=a.parenttopic;

if you dont want to use hierarchical query then

with topicdet as
(select t.topic_id as topic,c.conf as configuration,t.parent_topic_id as parenttopic from topic t left outer join config c on t.topic_id=c.topic_id)
select a.topic,case when a.configuration is null then (select b.configuration from topicdet b where b.parenttopic=a.topic) else a.configuration end as config from topicdet a


Ascending keys getting bad plans in SQL 2016 (new CE), replacement for TF 4139 et al.? The new cardinality estimator (CE) in SQL Server 2016 has an unfortunate regression which hits our deployment hard. The query in question is a run-of-the-mill Hibernate generated update; however the updated table is also part of an indexed view. This is a one-row update that will typically also affect ~5 rows in the indexed view.

I've managed to narrow the problem down to cardinality estimation of the PK-column of a 2.5M table, if the query is first compiled for a PK-value inside the current statistics for this column all is well and an optimal  plan is generated.

If the PK was newer, id est outside the statistics range, CE 130 generates a very suboptimal plan, essentially estimating that the whole indexed view will be affected by the update. This was never an issue for us with CE 120 or CE 70, both estimators generating good plans. The leading column in question is "branded" as UNKNOWN.

I have however found that there are several trace-flags (2388-90, 4139) that could be used for the old query planner, but these have no effect on CE 120+.

Is there any newer settings or trace-flags I can invoke to ensure that the normal query plan will be found every time?

I have tried TF 9481 to force the old CE, then it works brilliantly, but this is hard to combine with Hibernate (it's hard to inject hints into the query, and I really don't want to muck about in Hibernate's internals). I've also tried TF 4199 and the newest CU, to no avail.

If all of these are true:

You only need to fix a relatively limited number of queries (dozens to hundreds)
You know exactly what they are, and you can get the T-SQL
They don't change (end users can't dynamically build their own, or add search conditions)
You can guarantee they won't change
You can control which context they execute in (for example, they're not executing in TempDB with fully qualified object names pointing to user databases)
They're in a limited number of databases (as opposed to a software-as-a-service approach where every client is in their own database, and you want to fix all of the queries in all of the databases)

Then plan guides are a good fix. Just know that if you violate any of these rules (like if the queries start changing) then the plan guides won't work.


How to prevent SQL files opening new instances of SSMS 17.9.1? Synopsis

This is not a new problem and there are several solutions floating around on Internet.

Problem

When you double click on a .sql file, a new instance of SSMS is opened. I did not have this problem until new SQL Server Management Studio (SSMS 17.9.1) was installed.

What I tried

This page lists several solutions suggesting registry changes and file associations, and reinstalling SSMS. None worked.
This one has almost the same as the previous link. (SO is so much better than those forums.)
Registry changes are shown here with excellent screenshots. I added "%1" but that did not make any difference.
The suggestion mentioned on SO did not work either.
On DBA, it is mostly talking about raised permissions, which is not the case with my situation.

Workarounds

Yes, I know about these workarounds:

Ctrl + O
Drag .sql files to SSMS

I am not looking for workarounds.

My environment

Even with the new SSMS 17.9.1 (build: 14.0.17289.0), I am still pointing to SQL Server 2016 (13.0.5233.0). Previously, I was using SSMS June 2016 (build: 13.0.15000.23) with the same SQL Server (13.0.5233.0).

Because of permission, I am not running any application as administrator.

SQL Server versions are listed here.

SQL Server Management Studio versions are listed here.

I have encountered this issue after installing SSMS 18 instead of 17.9. Then I have tried solutions from the list above but none worked. Uninstalling SSMS 18 and installing 17.9 again did not work either. I was finally able to resolve this issue with SSMS 18, but I hope it will fix incorrect behavior of SSMS 17 as well. First of all I've uninstalled SSMS, then I've searched through registry to find and remove all meaningful nodes containing info about SSMS or sql_auto_file. I removed the following nodes (don't forget to backup each of them prior to deleting!):

HKEY_CLASSES_ROOT.sql
HKEY_CLASSES_ROOT\Applications\Ssms.exe
HKEY_CLASSES_ROOT\sql_auto_file
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio
Removed all records for ssms in HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\ApplicationAssociationToasts
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\FileExts.sql
HKEY_USERS.DEFAULT\Software\Microsoft\SQL Server Management Studio

Then I've restarted my PC, then installed SSMS 18, then restarted again. After that SSMS opens sql files in existing SSMS instance and explorer has correct file icon assigned (document with small database icon)


Constraint on multiple columns? I have a table edges, which describes relations in a graph:

CREATE TABLE IF NOT EXISTS edges (
   src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
  ,tgt INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
  ,rel TEXT NOT NULL
  ,rel_type INT NOT NULL
  ,PRIMARY KEY (src, tgt, rel)
  ,UNIQUE (src, tgt, rel)
  );

After inserts:

select * from edges;
 src | tgt |    rel    | rel_type
-----+-----+-----------+----------
   1 |   2 | 5.4.2.2   |        2
   2 |   3 | 5.3.1.9   |        2
   ...
   5 |   6 | 2.7.1.2   |        1
   5 |   6 | 2.7.1.147 |        1
   6 |   2 | 5.3.1.9   |        2
   6 |   3 | 5.3.1.9   |        2
   ...

I am using rel_type to specify edge directionality (0: undirected; 1: source to target; 2: bidirectional).

Hence, inserting (3, 2, '5.3.1.9', 2) is redundant with respect to the second entry above (for example) -- which already expresses the reciprocal relationship 2 --> 3 and 3 --> 2.

How can I add a constraint that prevents the insertion of those redundant, reciprocal relations -- ideally ON CONFLICT DO NOTHING?

Basically, something like (these don't work: first due to syntax; second due to other issues):

ALTER TABLE edges ADD CONSTRAINT duplicate_rel_check CHECK ((src, tgt) <> (tgt, src) WHERE rel_type = 2);

or

CREATE UNIQUE INDEX ON edges ( greatest(src, tgt, rel_type=2), least(tgt, src, rel_type=2) );

A UNIQUE, multicolumn, partial expression index should do the trick:

CREATE UNIQUE INDEX ON edges (LEAST(src, tgt), GREATEST(src, tgt), rel)
WHERE rel_type = 2;

Works with INSERT ... ON CONFLICT DO NOTHING.

Related:

Unique postgres constraint for permutations across multiple columns
How to create a postgres table with unique combined primary key?
PostgreSQL multi-column unique constraint and NULL values

Aside: If those are IP addresses, consider the data type cidr or ip4 from the additional module ip4r for column rel.


Why my index don't update? I have created this index

ORACLE/VIDEO# create index titolo_index
  2  on titolo(titolo)
  3   indextype is ctxsys.context
  4  ONLINE
  5  ;

I do a research

select * from generale where CONTAINS(titolo, '%ragazzi%')>0 order by titolo;

   159|I ragazzi della roma violenta                   |1976   |Drammatico    |1:30    |Vhs       |Italiano

I change from roma to Roma

update titolo set titolo = 'I ragazzi della Roma violenta' where idtitolo = 159;

And it disappear from index!
Why?

Indexing with Oracle Text

  3.3.6.1 CONTEXT Index and DML

  A CONTEXT index is not transactional. When you perform inserts,
  updates, or deletes on the base table, you must explicitly synchronize
  the index with CTX_DDL.SYNC_INDEX.


PostgreSQL SQL syntax - JOIN and substrings I have a single table of hostnames and NFS exports, some but not all of which are related.
Example entries:

    HOST   VOLUME
    -----  ----------
    host1  vol1
    host1  vol2
    host1  vol3
    host2  bkvol1_DR
    host2  bkvol2_DR
    host2  bkvol3_DR

I'm trying to create a query which will give me the following:

   SRCHOST  DSTHOST  SRCVOL    DSTVOL
   -------  -------  ------    ------
   host1    host2    vol1      bkvol1_DR
   host1    host2    vol2      bkvol2_DR
   host1    host2    vol3      bkvol3_DR

I've tried various incantations of substring(), trim(), and join clauses, and I'm getting nowhere. This is on PostgreSQL 9.2. In non-functional code, this is what I want:

select a.host, b.host, a.volume, b.volume from table as a, table as b
    where a.volume is a substring of b.volume

select a.host, b.host, b.volume
from hosts as a
  join hosts as b
    on b.volume like '%'||a.volume||'%'
   and a.host <> b.host;

Instead of LIKE you can also use position which might be easier to read:

select a.host, b.host, b.volume
from hosts as a
  join hosts as b
    on a.host <> b.host
   and position(a.volume in b.volume) > 0

SQLFiddle: http://sqlfiddle.com/#!15/a67c5/1


Trouble producing a query that groups a selection of groups In terms of context, I believe that all that is needed is to know that I have a table, t_sale, that logs sales made. It logs a few things, such as day and location of sale in the shop, but what is important for this is that sale_time and sale_value (arbitrary categories for the sale price, LOW, MED, and HIGH) are logged.

The problem I am having is that I need to produce a report that breaks down sales in each hour, for each value category. I have successfully done both of these things individually with. For getting the number of sales by hour:

SELECT EXTRACT(hour FROM sale_time) AS Hour, COUNT(sale_time) AS Sales
FROM t_sale
GROUP BY EXTRACT(hour FROM sale_time)
ORDER BY Sales DESC;

which results in this (shop is open 0900 to 1700):

*HOUR*  *SALES*
13      99
16      99
9       94
10      91
11      89
15      87
12      82
14      79

And producing a report for sales by value, I used this query to get the data and columns I needed:

SELECT sale_value AS "Sale Value", COUNT(sale_value) AS "Total Sales"
FROM t_sale
GROUP BY sale_value
HAVING sale_value IN ('HIGH', 'MED', 'LOW');

which results in this:

*VALUE* *SALES*
HIGH    241
LOW     244
MED     235

What I am imaging is being requested is something that, in place of sales for the values example, further breaks it down by each hour. Something like:

*VALUE*    *HOUR*    *SALES*
HIGH       09        22
           10        14
           11        31
           etc...    etc...

MED        09        52
           10        16
           11        19
           etc...    etc...

LOW        09        63
           10        33
           11        28
           etc...    etc...

I've used dbForge Studio 2020 for Oracle for the report on the values due to no end of issues with Oracle Forms and Reports. I'm confident I can make the report if I knew what query to use to generate the data. However I am completely at a loss as to what functions to use.

Wouldn't this work?

SELECT
  sale_value AS "Sale Value",
  EXTRACT(hour FROM sale_time) AS Hour,
  COUNT(sale_value) AS "Total Sales"
FROM t_sale
GROUP BY sale_value, EXTRACT(hour FROM sale_time)
HAVING sale_value IN ('HIGH', 'MED', 'LOW');


Where did new database 'BackupLocDb_###" come from? Yesterday I was doing some work on a 3 Node AlwaysOn group of instances, SQL 2014 SP3, and this morning I found there was a database named BackupLocDb_1260a388-153d-4c86-a30b-0fd3feafb116 on the primary.  I did not create the database, but I am listed as the owner.

Where did this database come from?

Initial attemps to google, lead me to a few AlwaysOn posts but did not mention the database clearly.  I did not find it on DBA.SE either.

The database is created when you use the GUI to add databases to the secondary servers, and select the option Full database and log backup, you may safely delete it.

  In the Specify a shared network location accessible by all replicas: field, specify a backup share to which all of the server instance that host replicas have read-write access. For more information, see Prerequisites, earlier in this topic. In the validation step, the wizard will perform a test to make sure the provided network location is valid, the test will create a database on the primary replica named "BackupLocDb_" followed by a Guid and perform backup to the provided network location, then restore it on the secondary replicas. It is safe to delete this database along with its backup history and backup file in case the wizard failed to delete them. Source


how to deal with foreign keys with fixed values (on a db-table-basis) I have a control table that looks like this:

CREATE TABLE `tbl_control` (
  `tm_db_tbl` varchar(64) NOT NULL,
  `tm_key` int(11) unsigned NOT NULL,
  /* a bunch of other fields here */
  PRIMARY KEY (`tm_db_tbl`,`tm_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

From the perspective of my other tables, when I want to join on this control table, I join on the tm_key in my table along with a string literal designating my database and table against the tm_db_tbl field above.  With the client table below, I would join WHERE ct.tm_db_tbl='my_db.some_client_table' AND ct.tm_key=sct.tm_key.

CREATE TABLE `some_client_table` (
  `tm_key` int(11) NOT NULL,
  /* a bunch of other fields here */
  PRIMARY KEY (`tm_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is a tiny bit of optimization so I don't have a tm_db_tbl column in my client table with all the same values.  The relationship here is many-client-tables-to-one-control-table.

But now, I run into a bit of a problem when I want to define a foreign key on my client table because the corresponding tm_db_tbl is a string-literal.

Should I just bite-the-bullet and add tm_db_tbl filled all with the same value to my table or is there a better way around this problem?

If it makes any difference, I am using mysql.

The bottom line is that you can't define a foreign key constraint that uses anything other than just the columns in the primary key that you are referencing.

One of the ways that people sometimes use to consolidate keys across multiple tables is to use a GUID as the primary key.  If the tm_key in each of your tables was a GUID then you could impose the foreign key using declarative referential integrity without carrying the tm_db_tbl column in your some_client_table.

Failing that, your options are:

Forego declarative referential integrity and just ensure that you
have indexes that make your joins efficient.
Impose referential integrity procedurally.
Take ypercube's suggestion and find a more
efficient way to represent some_client_table.tb_db_tbl.


Very large table makes the application performance too slow We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.

I found one table referenced in this app with 1,332,730,786 records in it.

Table IDSelection
IDType varchar(20) NOT NULL,
SelectID int NOT NULL,
UnID int NOT NULL,
Batch int NULL

Table has following indexes

IX_IDSelection_UnID nonclustered located on PRIMARY UnID
PK_UIDSelection clustered, unique, primary key located on PRIMARY SelectID, IDType, UnID

My concerns are
1. This table has composite primary keys with some entries in IDType that are empty string (not NULL though).
2. Running simple SELECT queries like below against this table takes quite some time.

select * from IDSelection where IDType <> '' and Batch is not NULL ORDER by Batch desc  -- cluster index scan
select * from IDSelection where Batch = 9977    -- 8 minutes run for 19 records
select * from IDSelection where IDType = 'ParentID' and Batch is not NULL  -- 9 min 3614603 rows

If I use indexed UnID in WHERE clause like the one below, performance is great

SELECT * FROM IDSelection where UnID = 1093510

Index fragmentation is not an issue here. Would it help adding identity primary key and making current composite key as [non clustered unique index] help? What other options do we have?

DB version is MS SQL 2014 Enterprise

How long do you think it will take to create an additional index?
How often are DML executed? Is this table being read often?

Given this monstrosity has over 1 billion rows I would create separate filtered table with columns SelectID, IDType, UnID, Batch and apply there appropriate indexes.


pg_restore to create database before loading it? I understand that pg_restore needs to connect to a database before it can start the restore process, is there anyway pg_restore can use the "postgres" database to create another database?

I have the following command

pg_restore -h "192.168.1.212" -p 5432 -U mysuperusername -C -d bp bp.custom

when I run the command I get the following message

  pg_restore: [archiver (db)] connection to database "bp" failed: FATAL:
  database "bp" does not exist

Is there anyway for pg_restore to create the "bp" database without me having to create it manually?

Specify -d postgres on the command line.

Quote from the manual for the -C option. The database to be restored is automatically take from the backup file

  When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

pg_restore -h "192.168.1.212" -p 5432 -U mysuperusername -C -d postgres bp.custom


Failed allocate pages: FAIL_PAGE_ALLOCATION 1 We are on SQL Server 2012 SP3 Enterprise Edition on Windows 2012 R2.

I saw these errors in the sql logs:

  Failed allocate pages: FAIL_PAGE_ALLOCATION 1

  2016-06-14 04:28:27.44 spid175     Error: 701, Severity: 17, State:
  123.

  2016-06-14 04:28:27.44 spid175     There is insufficient system memory
  in  resource pool 'default' to run this query.

  2016-06-14 04:28:27.44 Server      Error: 17300, Severity: 16, State:
  1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

  2016-06-14 04:28:27.44 Server      Error: 17300, Severity: 16, State:
  1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

  2016-06-14 04:28:27.44 spid131     Error: 701, Severity: 17, State:
  123.

As far as I can tell, it looks like it ran out of memory at some point.
Is there a way to figure out what caused it to go out of memory?
The MEMORYCLERK_SQLQERESERVATIONS is pretty high, does anyone know what that is for?

MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22599824

EDIT: We have 32 gigs of RAM on the server and 28 is allocated to the SQL Server.  The max memory setting is 28gb and min server memory is 8gb.

Here is a link to the ErrorLog output:
ErrorLog

Here is the link to the sys.dm_os_process_memory output:
Query Output

Link to the wait types:  WaitTypes

I ran it during a time when we seem to have more memory usage:

  SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

Results: memory_grants

  SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
  FROM sys.dm_exec_query_memory_grants AS mg
  CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
  CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
  ORDER BY 1 DESC OPTION (MAXDOP 1)
  Results: QueryMemGrants

  SELECT top 50 t.text, cp.objtype ,qp.query_plan, cp.usecounts, cp.size_in_bytes >as [Bytes Used in Cache]
  FROM sys.dm_exec_cached_plans AS cp
  JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
  WHERE qp.query_plan.exist('declare namespace >n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; >//n:MemoryFractions') = 1
  order by cp.size_in_bytes desc
  OPTION (MAXDOP 1)

Results: CachedMemGrants

  Select granted_query_memory,session_id,command from sys.dm_exec_requests
  Results:dm_exec_requests

XML of query plan from one of the queries that was running:
QueryPlan

The output of errorlog had dbcc memorystatus dump and what I noticed was

Process/System Counts                         Value(in Bytes)
---------------------------------------- ----------
Available Physical Memory                1217605632---1.1 G
Available Virtual Memory                 140627167866880
Available Paging File                    5656502272
Working Set                               305238016
Percent of Committed Memory in WS                99
Page Faults                                27923310
System physical memory high                       0
System physical memory low                        0
Process physical memory low                       1--Memory Low
Process virtual memory low                        0
2016-06-14 04:28:27.41 Server

Please note the available physical memory is very low. There was almost no memory in buffer pool

Regarding clerk which is consuming more memory

MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22599824  --21.5 G

Page Life Expectancy                             64

Now on server where max server memory is 28 G if MEMORYCLERK_SQLQERESERVATIONS is taking 21.5 G that is definitely a problem. This is what causing the OOM condition.

What is MEMORYCLERK_SQLQERESERVATIONS

This is a memory clerk in SQL Server which tracks memory allocated to query which involves Sort or hash operations during execution. These operators can be the largest memory consumers for a query.

Why OOM error due to this

When query involving sort and hash operations is executed  it will make a reservation request based on the original query plan which contained a sort or a hash operator. Then as the query executes, it requests the memory and SQL Server will grant that request partially or fully depending on memory availability. There is a memory clerk (accountant) named ‘MEMORYCLERK_SQLQERESERVATIONS’ which tracks memory allocation to such requests . Now in your scenario following could be happening

Query is requesting so much memory grant that SQL Server is only able to provide it a limited amount, this limited amount is called "Required Memory", so that it starts executing and while executing the query, because memory requirement was large and SQL Server cannot provide it as there was no memory in resource pool, the query fails with OOM error. The memory required when query is running is called "Additional Memory"
There was Bug fixed in SQL Server 2012 Sp1 CU4 where query requested huge amount of memory grant causing it to be drastically slow or subsequently failing with OOM error. The possibility that bug resurfaced cannot be ruled out considering fact that QEReservations hogged all of the buffer pool
Since the clerk has already taken 90 % of memory. Required Memory for new query is not available and query fails with OOM error.
Your tables and indexes has skewed statistics which is forcing optimizer to build sub optimal plan causing it to request much more memory grant than actually required and in turn creating issues.
Lastly the queries running on SQL Server requires some serious tuning.

As per This Blogs.msdn article

  What Can a Developer Actually Do about Sort/Hash Operations?

  Speaking of re-writing queries, here are some things to look for in a
  query that may lead to large memory grants.

  Reasons why a query would use a SORT operator (not all inclusive
  list):

ORDER BY (T-SQL)

GROUP BY (T-SQL)

DISTINCT (T-SQL)

Merge Join operator selected by the optimizer and one of the inputs of the Merge join has to be sorted because a clustered index is

  not available on that column.

  Reasons why a query would use a Hash Match operator (not all inclusive
  list):

JOIN (T-SQL) – if SQL ends up performing a Hash Join. Typically, lack of good indexes may lead to the most expensive of join operators

  – Hash Join. Look at query plan.

DISTINCT (T-SQL) – a Hash Aggregate could be used to perform the distinct. Look at query plan.

SUM/AVG/MAX/MIN (T-SQL)– any aggregate operation could potentially be performed as a Hash Aggregate . Look at query plan.

UNION – a Hash Aggregate could be used to remove the duplicates.

To further understand the problem I would require you to add output of below queries into your question. I would also like you to add output of Paul Randal Wait stats query. The source of query is This Blog, I suggest you to read the blog.

SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

--Find who uses the most query memory grant:

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

--Search cache for queries with memory grants:

SELECT t.text, cp.objtype,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist(‘declare namespace n=”http://schemas.microsoft.com/sqlserver/2004/07/showplan“; //n:MemoryFractions’) = 1

There are few other things I would like you to check for queries running on system.

Select granted_query_memory,session_id,command from sys.dm_exec_requests

This will show you how much memory is granted to queries running on the system.

If you can see XML actual execution plan you have MemoryGrant=xxxxx can you collect this value for costly queries.

All the above will show us if there is problem in query or some other issue as to why it is requesting so much memory for execution.

EDIT

From various query outputs you pasted.

You can see the requested_memory_kb for large number of queries are approx 5G, this is large memory grant, ideally it should be few MB's. Do note that required_memory_kb is just around 5 MB and granted_query_memory is NULL this is because due to memory pressure SQL Server is just able to provide minimum memory to start the query but not able to provide additional memory for query execution resulting query to fail with OOM error.

The query costs for queries requesting huge memory is also high which leads me to believe that either statistics are skewed or queries are written poorly. Other possibility would be query not supported by proper index. Number of queries requesting such a huge memory grant is good in number.

For above queries see granted_query_memory it is all in GB. The first 3 queries running used approx 15 G of memory which almost used 50 % of memory. In SQL Server millions of process run which require memory in some way so you can see if 3 queries are using 50% of available memory OOM issue is bound to occur.

Solution

You should seriously consider tuning the first 4 queries in above screenshot

Make sure you run index rebuild and stats update at least weekly so that skewed stats does not force optimizer to produce bad plan.

Use resource governor and create a resource pool and workload group and run queries which are requesting large memory grant in this pool. You can limit the memory request with parameter request_max_memory_grant_percentage. An example is shown in this Blog. This is just alternate method till you tune all your queries.


Can we change database owner from [NT SERVICE\SQLSERVERAGENT] to [sa] I have some list of databases with different owners.

Among them [NT SERVICE\SQLSERVERAGENT] is the owner of one database (let's say db_name ='Test_db_repli').

Can we change the owner of that particular database to [sa]?

If changed, will there be any problems in the future?

Answering your first question

  Can we change the owner of that particular database to [sa]?

Yes, you can change the owner of any database using the ALTER AUTHORIZATION ON DATABASE ... command.

ALTER AUTHORIZATION ON DATABASE::Test_db_repli TO sa;

Answering your second question

  If changed, will there be any problems in the future?

This is something we cannot answer, because we don't know your environment. There might have been a reason in the past to run the database using the NT SERVICE\SQLSERVERAGENT account. Providing additional details on what the database was used for could give the community an idea why the database was set up to have that specific account defined as database owner.


How do I limit Oracle's memory use? I would like to use Oracle's automatic memory management, with a limit of around 4GB. (Past experience has shown this to be plenty for my dev PC and that it is easier to get an idea about any performance issues with less than that.)

I believe these are the instructions to follow:

Oracle 12 automatic memory management

It says in short, to enable automatic memory management, set memory_target and (optionally) memory_max_target. The former parameter should be a dynamic value and the latter a harder limit, that can only be changed when stopping/starting the database.

I set these as follows as sysdba:

SQL> alter system set memory_target = 4G scope = spfile;

System altered.

SQL> alter system set memory_max_target = 8G scope = spfile;

System altered.

and check the values with

SQL> show parameter target;

Scope can be just "memory" for the current uptime, "spfile" or "both"; I pick the spfile as I will be restarting:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

That, however, was too simplistic:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 13104M

It is not hard to revert, but in my opinion also not immediately obvious how to get the memory adjustment through, so I will post what I did below and the senior posters can correct/shred me. In either case I think it is nice to have it in one place here.

The problem was that when the database was installed, conflicting other parameters were set. So I needed to export those from the "spfile" to a "pfile", back them up, edit them, test them and import from the pfile to the spfile:

SQL> create pfile='some/file/path' from spfile;

File created.

Then I set the conflicting parameters to zero:

...
--*.pga_aggregate_target=3270m
*.pga_aggregate_target=0m
...
--*.sga_target=9811m
*.sga_target=0m
...

I then try starting up again:

SQL> startup pfile='some/file/path';

ORACLE instance started.
...

And check the memory_target and memory_max_target parameters as above. Once happy with things, to keep the settings I did:

SQL> create spfile from pfile='some/file/path';

File created.


User defined data types causing implicit conversions user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into that var from a column that uses that user defined data type, it's showing it as an implicit conversion. Is this how it functions normally?

Added

ok so under Programmability -> Types -> User-Defined Data Types: there is

dbo.BOOL(char(1), not null)

and

ImageAccessibleFLAG TYPE_NAME is BOOL

New test

I can make an implicit conversion happen by doing the following
reasonforcreatetype  is char(1) in the database

declare @test char(1)
set @test = (select top 1 reasonforcreatetype from result)
select @test

<ScalarOperator ScalarString="CONVERT_IMPLICIT(char(1),[Result].[ReasonForCreateType],0)">

Now I create a new table with a char(1) and a bool.
Run the same query and I don't get the implicit conversion.

Update 2

Ok when adding a new column under the same table having the issue I get the following

Warning: Columns have different ANSI_PADDING settings.
New columns will be created with ANSI_PADDING 'on'.

I'm assuming this has something to do with it now but I'm not sure the proper way to fix or correct these issues.

Okay, here is a repro:

USE tempdb;
GO
-- create one table with padding off:
SET ANSI_PADDING OFF;
GO
CREATE TABLE dbo.ap_off(a CHAR(1),
 CONSTRAINT ck_apOff CHECK (a IN ('N','Y')));
GO
-- and other with padding on:
SET ANSI_PADDING ON;
GO
CREATE TABLE dbo.ap_on(a CHAR(1),
 CONSTRAINT ck_apOn CHECK (a IN ('N','Y')));
GO

-- now, let's test queries
-- with padding on or off
SET ANSI_PADDING OFF;
GO
-- implicit conversion:
DECLARE @off CHAR(1) = (SELECT a FROM dbo.ap_off);
-- implicit conversion:
DECLARE @on  CHAR(1) = (SELECT a FROM dbo.ap_on);

SET ANSI_PADDING ON;
GO
-- implicit conversion:
DECLARE @off CHAR(1) = (SELECT a FROM dbo.ap_off);
-- NO implicit conversion:
DECLARE @on  CHAR(1) = (SELECT a FROM dbo.ap_on);
GO

DROP TABLE dbo.ap_off, dbo.ap_on;

Here's the same version of the plan for the first three queries:

And the fourth, missing the compute scalar and any implicit conversion:

So, as I suggested above, this doesn't have anything to do with alias types, but rather the fact that ANSI_PADDING is problematic in a whole bunch of ways (here's a timely article, a Stack Overflow question, and Microsoft's own documentation from SQL Server 2005 telling you to stop using it).

My suggested approach is to build new versions of any table with these non-ANSI_PADDING columns (this time with ANSI_PADDING ON of course), migrate the data, drop the old tables, and rename the new tables. Or do the same type of thing with just the individual columns (in either case it's going to be a complicated and disruptive thing that you'll likely want to do during a maintenance window).

You can identify the affected tables with:

SELECT s.name, t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE t.is_ms_shipped = 0
AND EXISTS (SELECT 1 FROM sys.columns
 WHERE [object_id] = t.[object_id]
 AND is_ansi_padded = 0);

I would do this with one table or columns first, to see if it's worth it. See, in addition to eliminating the implicit conversions (which you think might cause performance issues), you should also demonstrate that performance will actually be different without them - and different enough to justify the effort, risk, and potential downtime. Otherwise, is it worth doing? We can't answer that, only you and your stakeholders can.


Best way to copy a SQL Server database from a remote server to local computer? More specific my db is hosted at appharbor and I want to copy that db to my local SQL Server Express 2012. Up until now I have used Management Studio to export the data and later used the schema compare feature in Visual Studio 2012, but I guess there must be an easier way

You should talk to your support team at appharbor. Presumably there is a way to take a backup of your database, store it on the file system somewhere, and then download the .BAK file (through http or, more likely, ftp). This should be a lot less work than your current process, but it depends on your host providing the facility to do so. This also assumes that your backup file would be < 10 GB (since this is the maximum supported by Express) and that it doesn't use any features not supported in Express Edition (such as Data Compression, SQL Server Agent, etc.).

You can do this using just Management Studio, e.g. you can right-click your database, Tasks > Generate Scripts and select all objects and on the Scripting Options tab make sure to go into advanced and select the option to script both schema and data. This will generate a humongous script that you can run on your local machine after you've created an empty database. Personally I find a backup / restore much more reliable and a lot less hassle.

But asking here isn't going to be much help - unless you happen to come across someone who has done this specific thing at that specific host using the specific hosting plan you have, there is little chance any of us knows what options you have - appharbor is going to be able to help you answer that question a lot more accurately than any of us.


Having issues with a CTE in a Nested Join I'm unfortunately dealing with someone else's code here, but needing a little push.

What I have happening is that I have a CTE that is needed to run in a nested join.

...Start of CTE
INNER JOIN (
;WITH FixedQty
AS
(
SELECT CASE
       WHEN WeekJobStartDate < @BegRange
       THEN SUM(WipJobQty) * -1
       ELSE SUM(WipJobQty)
       END AS FixedAllocationQty
      ,StockCode
      ,WeekJobStartDate
  FROM [vwWipDet]
 WHERE WeekJobStartDate <= @EndRange
GROUP BY StockCode, WeekJobStartDate
)
SELECT SUM(FixedAllocationQty) AS WipJobQty
      ,StockCode
  FROM FixedQty
GROUP BY StockCode
    ) AS WipDet ON InvMaster.StockCode = WipDet.StockCode   ...

I've tried removing the semi-colon in front of the CTE, but then get a syntax error stating it is expecting that the previous statement must be terminated with a semi-colon.

I beating my head against the keyboard on this one.

This sounds like dynamic SQL where you're constructing a query from other subqueries...

The thing is that subqueries have slightly different rules. A subquery can't contain a CTE, nor can it be ordered without a TOP/OFFSET/FOR XML.

So what you need to do is have a CTE section at the start of your main query, and add your CTE to that. You don't have an ORDER BY, so that's good.

You could find it useful to make WipDet a CTE too... Like this:

WITH FixedQty
AS
(
SELECT CASE
       WHEN WeekJobStartDate < @BegRange
       THEN SUM(WipJobQty) * -1
       ELSE SUM(WipJobQty)
       END AS FixedAllocationQty
      ,StockCode
      ,WeekJobStartDate
  FROM [vwWipDet]
 WHERE WeekJobStartDate <= @EndRange
GROUP BY StockCode, WeekJobStartDate
),
WipDet AS (
SELECT SUM(FixedAllocationQty) AS WipJobQty
      ,StockCode
  FROM FixedQty
GROUP BY StockCode
    )
SELECT ...
FROM InvMaster
INNER JOIN WipDet
ON InvMaster.StockCode = WipDet.StockCode


Using unique non clustered index with unique clustered index Would there be any scenario where it would make sense to create a unique non clustered index that includes a column that is contained in a unique clustered index?

In other words, if I have a Products table with a unique clustered index on the ProductID column. Would there be an acceptable scenario where I would need to create a unique non clustered index that includes the ProductID column in addition to a couple of other columns, or, would the mere inclusion of the unique ProductID column make creating the non clustered index unique irrelevant?

Yes having a column in multiple unique keys is sometimes perfectly reasonable.  In the case that you gave above I'm not sure I would bother since the ProductId key is unique regardless.  But let's say that you have a product table like this:

ProductVendor  PK
ProductCode  PK
ProductDescription
.....

In this particular case the ProductVendor and ProductCode are together unique and are your primary key and clustered index.  However there is an additional business rule that ProductDescription must also be unique by ProductVendor.  In this case you could create a non-clustered index on ProductVendor, ProductDescription.


MySQL DB design for very long growing row size My data has the property to grow in the row direction and the column direction at the same time. The size will grow in both directions without limit while I do more research.
The number of columns grows more than 1000 now and I got this error:
 Warning: (139, 'Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.')

I read this post but increasing the page size would not help because the row size is growing and it will exceed anything at the end.
I want to restructure the table but I don't have a good idea. How can I restructure this table?

P.S. As a reference, it's time-series data and I put the timestamps as the index. And the columns can be grouped into categories and each group size grows at the same time.

P.P.S I add more details. There are a lot of columns and I decided to combine all of them into one table because each column shares the same timestamps. In addition, to track the identity of column, I made one extra table describing what the column is about.
This is the part of the data table:
+----------------------------+-----------+---------------+--------------------+
| etstamp                    | 1         | 2             | 3                  |
+----------------------------+-----------+---------------+--------------------+
| 2019-01-01 13:53:30.000000 |   3802325 |  -12298828971 |  0.964893760996075 |
| 2019-01-01 14:01:00.000000 | -13088926 |  -12298828971 |  -1.33234100135318 |
| 2019-01-01 14:06:00.000000 | -13088926 |   -5892372191 |  0.343203392148817 |
| 2019-01-01 18:35:30.000000 |  -3751101 |   -4074909927 |  -1.28552097428958 |
| 2019-01-01 23:19:00.000000 |  40493616 |   90751800131 |   1.41623954680335 |
| 2019-01-01 23:20:30.000000 |  40493616 |   47623820910 |   2.07233344695927 |
| 2019-01-02 16:46:00.000000 |  -1251261 |   10093182491 |   2.12765957446809 |
| 2019-01-02 22:57:00.000000 |  14034018 |    9768467942 |   1.32055929570171 |
| 2019-01-03 05:24:00.000000 |  -2613264 |  -56186202376 |  -1.06769174979049 |
| 2019-01-03 16:23:00.000000 | -22956977 |   -5525892956 |  -2.04214360041623 |
| 2019-01-04 00:13:30.000000 | -10053384 |   -9177100417 |  -1.39583882012115 |
| 2019-01-04 09:05:30.000000 |  -5494943 |  -21081825567 | -0.966329662393934 |
| 2019-01-04 14:43:30.000000 |   4678636 |  -70623900257 |  -1.26799630167745 |
| 2019-01-04 20:54:00.000000 |  23879504 |   41962608843 |               0.96 |
| 2019-01-04 20:59:30.000000 |  16930573 |   41962608843 |   -0.5652942203597 |
| 2019-01-04 21:33:30.000000 |  16930573 |   49439408153 |   1.63281561131024 |
| 2019-01-05 00:16:00.000000 |  15617092 |   56326921973 |  0.552369137856527 |
| 2019-01-05 22:08:30.000000 | -28280428 | -100817061177 |  -1.41613615694426 |
| 2019-01-05 22:16:30.000000 | -28280428 |  -18081058461 | -0.364971120115236 |
| 2019-01-06 02:01:30.000000 | -11802595 |  -42569384784 | -0.958499367931412 |
+----------------------------+-----------+---------------+--------------------+
20 rows in set (0.00 sec)

This is a sample table to describe the column identity.
+----------+-------------+---------------------+---------------------+----------------------------+------------+
| columnid | fcolltype   | stime               | etime               | indinfo                    | indbpsetid |
+----------+-------------+---------------------+---------------------+----------------------------+------------+
|        1 | TECHNICAL01 | 2019-01-01 00:00:00 | 2019-07-01 00:00:00 | VolImb                     |         15 |
|        2 | TECHNICAL01 | 2019-01-01 00:00:00 | 2019-07-01 00:00:00 | DolImb                     |         18 |
|        3 | TECHNICAL01 | 2019-01-01 00:00:00 | 2019-07-01 00:00:00 | PJUMP[9, high, low, close] |         50 |
+----------+-------------+---------------------+---------------------+----------------------------+------------+
3 rows in set (0.00 sec)

(The columnid corresponds to the column name of the data table.)

So the pieces are there, but not structured in a way for a database to quickly access the data you need, or to allow growth without major restructuring.
In your case the best way to do that is to arrange the data in a longer format instead of the wide format you have.  So instead of having each column represent an entity identifiable only through a metadata table, you will have a key identify each row and the information will be organized by that key/timestamp combination.  Not only will this allow your database to grow without issue, you can also add new entities through inserting rows, not rebuilding your main table.  It's also much faster for most queries.
After discussion in chat, this is the model I believe this is a good starting point for your data. (For anyone not familiar with ER Diagrams, I always recommend reading this first).

This may require some tweaks as you go along, but the general structure should be there.
Note: The current diagram allows the same ModelCode to be used with more than one Instrument.  This is useful if the code is easily understood to mean a certain type of model or prediction.  If that is the case, adding a ModelType entity to give additional meaning/integrity to ModelCode would be desirable.  But if you want ModelCode to be unique across all instruments, you would need to place an alternate key (unique constraint) on that column.


Any tools to simulate innodb crash for innodb_force_recovery levels 1-6? I am curious if there's any tools out there or methods to simulate mysql innodb crashes which would test and require the use of each of the 6 levels of innodb_force_recovery 1-6 http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html ?

What would I need to do to replicate each level of crash recovery ?

cheers

I use bvi to corrupt a tablespace. However you need to know InnoDB files format and how InnoDB works in order to make it start with particular recovery level. For example, for level 1 you need to change a checksum. For 3 - corrupt UNDO and so on.


How to escape '%' inside varchar @params for RAISERROR funcion in SQL Server 2012 How I should escape the % character in params so my RAISERROR retun my message

declare @msg varchar(max)  = (SELECT ' Message with %  ' AS MSG)

if @msg is not null
begin
    RAISERROR (@msg ,16,1);
end

This will rise error with message

  Msg 2787, Level 16, State 1, Line 4
  Invalid format specification: '%  '.

For end users this message is unreadable.

My messages is generated from database and setting this message.

Way I do avoid getting wrong message is replacing

set @msg = REPLACE(@msg,'%','P')

But I still do not know how to add % sign

Use %%:

raiserror(N'This is a message with %%', 0, 1);


What causes 'Recovery completed for database ...' log messages? I am running an MS SQL Server Express (64-bit v11.0.2100.60) database and now and then I get problems that my connections times out when I want to connect. In the server log I can see that at that time there are log messages 'Recovery completed for database ...'.

I did not run any datababase recovery nor was anything happened at that time (Windows event  log and MS SQL server logs are both 'clean').

What could cause this recovery? Is there a way to prevent it?

Check the database options for AutoClose. It looks like your database is having to start up every time someone connects to it after there have been no connections. To start a database, it also involves looking for any non-checkpointed transactions in the log, which then does a recovery to get the database ready for action.

AutoClose should be OFF.


Different type of column Maybe the title is misleading but I'll try to explain what I'm trying to do.

I'm building an e-commerce site. I have my products and my order.
I want add constrains on how the order will be deliver to the customer depending on each product.

For example if I sell a big and fragile product I want to restrict it from being send with courier, the customer should only have the option to take it from the shop.
Or a specific product can only be sent to limited countries.

I thought I could create an extra table related to products.

That way I could know which types of constrains each product have.
And I have no limitation on how many extra type of restriction I can make.

For example

restrictions(1,124,'post.courier',false)

But if do this,

restrictions(2,1322,'country','Greece')

the value field it's a different type.

My second thought was to use a field restrictions type TEXT and pass inside both values and restriction in a JSON format or XML

Another thought was to create separate tables for each kind,

and join them all, but it didn't seem proper.

What would be the best in my case?
Is there a better way I could design my DB?

Would it not be safe to assume that your products might have more than one restriction?

In that case, don't you need an intersection between product and restriction (type)?

Consider this ERD:

Note that restrictions are shown using an entity-sub-typing model.  You can keep distinct restriction value types appropriate to the kind of data that you require.  At the same time, the individual specific restriction types share a key space with the restriction header table.  That makes the intersection: PRODUCT_RESTRICTION easier to define and use.

There are different ways of doing it, but you'd probably want to consider including a partitioning attribute in the RESTRICTION table.

Walter Mitty suggests:

  If you want to model restrictions as a case of subtyping, there are
  design patterns for this case.   There are two tags over in
  StackOverflow where the info tab might help you:
  single-table-inheritance class-table-inheritance

Joel Brown responds:

The restriction model I have illustrated is one common way of implementing subtyping.  There are other variations.


Using btree_gist and <->

You really want <-> which will actually work on GiST indexes through the internal tstz_dist. This works because you don't really care about the interval. First you need to add the extension,

CREATE EXTENSION btree_gist;

then

SELECT DISTINCT ON (time_1) time_1, time_2
FROM tbl1
CROSS JOIN tbl2
ORDER BY time_1, time_1 <-> time_2;

If there are too many rows to do a cross-join, you may have better luck with the lateral solution.

SELECT time_1, time_2
FROM tbl1
CROSS JOIN LATERAL (
  SELECT time_2
  FROM tbl2
  ORDER BY time_1<->time_2
  LIMIT 1
) AS tbl2;

Would be interested in seeing a report back on the benchmarks. =) Especially if you have a GIST index on time_1 and time_2.


How do I identify tables that have a foreign key to a specific table in Sybase? I am looking for (preferably) an SQL statement that selects the table/and column names for any table with a foreign key to a given table in Sybase. I think it should be somehow possible with the sys... tables but being completely new to sybase, I cannot make head nor toe of it. So any help into the right direction is highly appreciated.

Edit: For completness' sake: SELECT @@VERSION returns

'Adaptive Server Enterprise/15.0.3/EBF 17156 ESD#3/P/Sun_svr4/OS 5.8/ase1503/2726/64-bit/FBO/Fri Feb  5 05:26:23 2010'

Edit 2
Thanks a lot for your suggestions, especially Andrew Bickerton's comment. That allowed me to construct a rudimentary SQL select statement for a starting point to go further.

In case someone else is interested in it, here it is:

select
  fko.name    "Foreign key name",
  par.name    "Referenced table name",
  fk1.name || ' -> ' || pk1.name "Reference 1",
  fk2.name || ' -> ' || pk2.name "Reference 2",
  fk3.name || ' -> ' || pk3.name "Reference 3",
  fk4.name || ' -> ' || pk4.name "Reference 4"
from
  sysobjects      tab                                       join
  sysconstraints  con on tab.id        = con.tableid        join
  sysobjects      fko on con.constrid  = fko.id             join
  sysreferences   ref on con.constrid  = ref.constrid       join
  sysobjects      par on par.id        = ref.reftabid  left join
  ---- 1. Column
  syscolumns      fk1 on ref.fokey1    = fk1.colid and
                         ref.tableid   = fk1.id        left join
  syscolumns      pk1 on ref.refkey1   = pk1.colid and
                         ref.reftabid  = pk1.id        left join
  ---- 2. Column
  syscolumns      fk2 on ref.fokey2    = fk2.colid and
                         ref.tableid   = fk2.id        left join
  syscolumns      pk2 on ref.refkey2   = pk2.colid and
                         ref.reftabid  = pk2.id        left join
  ---- 3. Column
  syscolumns      fk3 on ref.fokey3    = fk3.colid and
                         ref.tableid   = fk3.id        left join
  syscolumns      pk3 on ref.refkey3   = pk3.colid and
                         ref.reftabid  = pk3.id        left join
  ---- 4. Column
  syscolumns      fk4 on ref.fokey4    = fk4.colid and
                         ref.tableid   = fk4.id        left join
  syscolumns      pk4 on ref.refkey4   = pk4.colid and
                         ref.reftabid  = pk4.id        -- Et cetera...
where
  tab.type = 'U'      and
  tab.name = 'tq84_f' and
  fko.type = 'RI'

Been a while since I've worked in sybase but (from memory) the following sql should point you in the right direction:

select *
from sysobjects so inner join syscolumns sc on so.id = sc.id
where sc.name = 'field name'

you can also check out the sybase books online (system tables)


Total Fragments 99% and I cant get it to stay down I have a SQL Server 2008 R2 database it is of size 13000.31 MB and my largest table has around half a million records. It is a database that I have bought. It already has indexes but when I run the database it is slow. It is slower than the database it replaced (from the same company) and should be an up grade. I have tried to rebuild the indexes as most of them are sitting at 99%. after the re-build they go to 0. but with in a few minutes they are back at 99%.

Why is this happening?

I have tried to rebuild the indexes as most of them are sitting at 99%. after the re-build they go to 0. but with in a few minutes they are back at 99%.

I guess I know the answer here and have seen many times. Can you please check if auto shrink is on for the databases. You can use below query

select name,is_auto_shrink_on from sys.databases where name='db_name'

You must also check if there is any job running daily which is shrinking data file or may be shrinking whole database using DBCC SHRINKDATABASE command or may be using DBCC SHRINKFILE command.

Of course this could only be complete answer if you confirm whether any such activity is going on in database.


Is It Possible to Run Out of Log Sequence Numbers? I’ve been wondering about the algorithm for generating log sequence
numbers for transaction log records, and I’m concerned that with a
high enough workload it might be possible to run out of log sequence
numbers.

What happens in that case?

I found the following answer from a post written by Paul Randal.

Is It Possible to Run Out of Log Sequence Numbers?

Paul writes:

  There’s no need to worry because for all practical purposes it isn’t
  possible to run out of log sequence numbers. As a bit of background, a
  log sequence number is a three-part number used to uniquely identify a
  transaction log record—constructed from the sequence number of the
  virtual log file (VLF) containing the log record, the log block number
  within the virtual log file, and the log record number within the log
  block.

  This isn’t really important, but what is important is that the VLF
  sequence number is a 64-bit number. Whenever a VLF is reused in the
  transaction log, the VLF sequence is increased by 1. So let’s do a
  little math.

  Imagine a transaction log with 65,536 VLFs, each 1/4MB in size (not a
  nonsensical situation, depending on how your transaction log is being
  managed—for more details on this and on transaction log internals, see
  “Importance of proper transaction log size
  management.".
  Each time the log is completely used and wraps around to the start,
  the VLF sequence number will increase by 65,536, which is 2 to the
  power of 16 (2^16).

  A 64-bit number can support 2^64 values. To be able to exhaust the
  2^64 possible VLF sequence numbers, our example transaction log would
  have to wrap 2^64 / 2^16 = 2^48 times. That’s a lot of log wrapping.
  But how much transaction log does that equate to?

  Our example log is 65,536 x 1/4MB in size, which is 16GB. To wrap that
  log 2^48 times, you’d need to generate 2^48 x 16GB of transaction log,
  which equates to four billion petabytes (a petabyte = 1024 terabytes)
  of transaction log—quite an undertaking!

  Even being able to write that log out to a solid-state drive (SSD)
  capable of a sustained 600MBps, it would take four billion petabytes /
  600 megabytes = approximately 240 million years to generate four
  billion petabytes of transaction log. As you can clearly see, no one’s
  in any danger of running out of log sequence numbers!


"Incorrect string value" while migrating from MSSQL to MySQL with Workbench I got the task to migrate our MSSQL database into a MySQL database.
Therefore I am using the recent version of MySQL Workbench and FreeTDS Driver.

During the migration I always get errors like this:

ERROR: `dbo`.`emails_text`:Inserting Data: Incorrect string value: '\xA0</td>...' for column 'description' at row 1
ERROR: `dbo`.`prospect_lists`:Inserting Data: Incorrect string value: '\xFCrs MB...' for column 'description' at row 4
ERROR: `dbo`.`campaigns`:Inserting Data: Incorrect string value: '\xFChrung...' for column 'name' at row 24

I assume that utf8 is the troublemaker but I couldn't find any solution to my problem till now.

I had to do the following:

In the source configuration (ODBC FreeTDS), tab "Advanced", I set:

TDS_VERSION=7.2;ClientCharset=UTF-8

and check "Driver sends Unicode data as UTF-8".
(for more info in http://www.freetds.org/userguide/odbcconnattr.htm )

Then, in the target configuration, tab "Advanced", in textbox "Others" I added the following new line: (change it as you whish, always utf8* )

preInit=SET default_storage_engine=MYISAM,character_set_connection=utf8mb4,collation_connection=utf8mb4_spanish_ci,collation_server=utf8mb4_spanish_ci,character_set_server=utf8mb4

(for more info see:
https://dev.mysql.com/doc/refman/5.7/en/server-options.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
https://dev.mysql.com/doc/connector-cpp/en/connector-cpp-connect-options.html )

Finally in the Data Copy step, in Options group check "Driver sends data already encoded as UTF-8".


Can you force SQL Server to use an index on computed column for a commutative operation? Can you force SQL Server to use an index on computed column for an commutative operation?

We unfortunately have a table in SQL Server as follows

CREATE TABLE [dbo].[Data](
  [ID] [int] NOT NULL,
  [ValDate] [datetime] NOT NULL,
  [ValHour] [int] NOT NULL,
  [ValMin] [int] NOT NULL,
  [Value] [float] NULL,
  [Flag_ID] [int] NOT NULL,
  CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
  ([ID],[ValDate],[ValHour],[ValMin]) ON [PRIMARY]
) ON [PRIMARY]

Even though ValDate is a DATETIME, it only stores the date portion.

Some old applications using this database makes the following query

SELECT ID, ValDate, ValHour, ValMin, Value
FROM DATA
WHERE ID = @id
  AND @start < DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
  AND DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) <= @end

This slows down with a lot of data, as SQL can't use the dates specified in the WHERE clause to seek and must scan each table entry with ID = @id to find all the rows.

We can't change the applications, so I decided to add a computed column and put an INDEX on it

ALTER TABLE dbo.Data ADD ComputedDateTime AS
  DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
GO
CREATE NONCLUSTERED INDEX [Data_ComputedDateTime_IDX] ON [dbo].[Data]
  ([ComputedDateTime], [ID])
INCLUDE ([ValDate], [ValHour], [ValMin], [Value])

This makes the original query fast, but if I change the order of the DATEADD's around, adding the minute to the date and then the hour the index is not utilized.

So I assume this is because SQL Server doesn't realize the operations are commutative i.e. (date + hour) + minute = (date + minute) + hour

Is there any way to speed up both computation orders, without creating two computed columns and two indexes?

As far as I know, your only options are to create the second computed column, contact the vendor of the software to ask for a fix, or to submit an enhancement request to Microsoft to get better support for your scenario. On the surface, the functionality that you're asking for could be viewed as simple: why can't SQL Server figure out that the expressions are equivalent when they obviously are to a programmer? However, it requires at least all of the following:

SQL Server needs to know that DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) is equal to DATEADD(hour, ValHour, DATEADD(minute, ValMin, ValDate)). There are a lot of date equivalences that SQL Server isn't yet aware of, and this is one of them.
The query optimizer would need to look for commutative matches during computed column matching.
The query optimizer would need to be able to find your match during the query optimization process, which is designed to very quickly give you a "good enough" plan.

I am sympathetic to your problem, but my guess is that the scenario that you're describing just isn't common enough for Microsoft to make improvements in this area, especially when there is a straightforward workaround that could be accomplished through changing code or by adding another computed column to the table.


Why can't we access SQL Server when a long query is running in Management Studio? We have a SQL Server Database (2012 Standard edition). The database is accessed through an ASP.Net web page which displays the count of databases in different tables on page load.

This work fine normally, however when we run a long query from Management Studio, the web page fails to load. It seems database does not respond when the long query is running in Management Studio. Is this a normal behavior?

Web page gives the following error -

Timeout expired.  The timeout period elapsed prior to completion of the operation or
the server is not responding.
Description: An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and where
it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout
period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request.
Information regarding the origin and location of the exception can be identified using
the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to
completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
    breakConnection) +404
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412

It would be impossible to say for sure without examining your web application and the SSMS query.  My guess, however, is that the long query is locking tables which the web page wants to read, so the page has to wait for the query to finish.  The page's timeout occurs before this happens and the page fails to load.

The concepts you are dealing with are locking and concurrency.

There are several ways to get the number of rows in a table.  Some are more accurate than others; some are faster.  This post on Stack Overflow may give you some ideas to work with.

Response to comment: a select count(*).. query will read every page of the table.  Since the SSMS statements are performing updates they will have exclusive locks on some pages and prevent the count(*) from proceeding.  This is exactly what I guessed was happening.

Some options are:

Read the post from Stack Overflow and decide if any of those options would be appropriate for you.  There are ways of getting the row count without touching the data tables.
Batch the updates so you only update, say, 1000 rows before you commit the transaction.  The commit will release locks and allow the counts to proceed.
Add WITH (NOLOCK) to the counting queries, described here.
Explicitly declare an appropriate isolation level for the web page's  queries.
Change the default isolation level for the SQL Server instance.  NOTE WELL - this will have MAJOR implications for ALL activity on the instance and should only be done after CAREFUL, DETAILED discussion with an EXPERIENCED SQL Server DBA.


DbUp and script directory I'm using DbUp, starting from the example I've created a C# program. The example suggest me to put all scripts under Scripts directory. Ok, this is done but, when script number will grow up that directory will be such a big mess.

I would organize that directories with version number. For instance

1.0.0 (directory)

CS001 - baseline.sql

1.0.1 (directory)

CS001 - alter x table.sql
CS002 - create y procedure.sql

1.1.0 (directory)

CS001 - create view z.sql

Obviously, code would take the script in an ordered manner. How can I do that? Where I found documentation and other examples?

DbUp automatically takes the filenames in alphnumeric order, so prefix them with a version or timestamp.


How to Find Missing number in Sequence using mysql? SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
   FROM wp_blogs AS a, wp_blogs AS b
   WHERE a.blog_id < b.blog_id
   GROUP BY a.blog_id
   HAVING start < MIN(b.blog_id);

It's working for me thank you for this post and i need brief explanation about this query

Without much experience in writing SQL queries, it's hard to understand by simply staring it down. A good approach is to "rebuild" it to understand what each step does.

Start with some sample data:

CREATE TABLE wp_blogs
    (`blog_id` int)
;

INSERT INTO wp_blogs
    (`blog_id`)
VALUES
    (1),
    (2),
    (3),
    (6),
    (7),
    (9)
;

Now we rebuilt it step by step. Start with the JOIN. (I don't like joining in the where clause, so I've rewritten it, but it works the same):

SELECT
a.blog_id as aa,
b.blog_id  as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id < b.blog_id
ORDER BY aa, bb /*added the ORDER BY for readability in the output*/

This has the following output:

| aa | bb |
|----|----|
|  1 |  2 |
|  1 |  3 |
|  1 |  6 |
|  1 |  7 |
|  1 |  9 |
|  2 |  3 |
|  2 |  6 |
|  2 |  7 |
|  2 |  9 |
|  3 |  6 |
|  3 |  7 |
|  3 |  9 |
|  6 |  7 |
|  6 |  9 |
|  7 |  9 |

As you can see the table is joined to itself, and on the right side only the rows are displayed that are greater than the left side.

Now we continue with the GROUP BY to collapse the groups on the left side. Whatever column you have in the SELECT clause, but not in the GROUP BY clause, you have to apply an aggregate function on it. Otherwise it's not clear which row for each group should be displayed. You have to tell MySQL, that you want the smallest number in the group. This can be done like this:

SELECT
a.blog_id as aa,
MIN(b.blog_id) as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id < b.blog_id
GROUP BY aa
ORDER BY aa, bb

Result is:

| aa | bb |
|----|----|
|  1 |  2 |
|  2 |  3 |
|  3 |  6 |
|  6 |  7 |
|  7 |  9 |

What we are interested in now, are the rows where the number on the right is not the number that is followed immediately after the number on the left. We filter on this with the HAVING clause, not with the WHERE clause, because we're dealing with groups here. In your query this is done with HAVING start < MIN(b.blog_id). start refers to the alias set in the SELECT clause for a.blog_id+1.
In other words, we add 1 to the value in the left column. If this is equal to the value on the right side, don't display the row.

So the result of this is:

SELECT
a.blog_id + 1 as aa,
MIN(b.blog_id) as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id < b.blog_id
GROUP BY aa
HAVING aa < MIN(b.blog_id)
ORDER BY aa, bb

| aa | bb |
|----|----|
|  4 |  6 |
|  8 |  9 |

bb is the number that is found in the table. That's why in your statement in the SELECT clause again 1 is subtracted from MIN(b.blog_id). The final query is:

SELECT
a.blog_id + 1 as aa,
MIN(b.blog_id) - 1 as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id < b.blog_id
GROUP BY aa
HAVING aa < MIN(b.blog_id)
ORDER BY aa, bb

| aa | bb |
|----|----|
|  4 |  5 |
|  8 |  8 |

Hope it's clear now :)
Play around with it in this sqlfiddle if you like


How can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)? If I use my database for relational data and opt to store large binary blobs in the filesystem, how can I integrate the two? How does this process work?

I know that the database will need to store a directory path/link to the corresponding blob, but can someone please enlighten me on the specifics?  For example, I think I should have some shell scripts to first perform a virus check on the binary in a secure /tmp location and then move it to the actual storage location and update the path in the db. But to do this from the operating system will require a script to check periodically if there has been a change in a directory and then fire off actions. Is it possible to run a shell script from within a DB (I know the reverse is possible)?

Or are there better tools to do this sort of thing (integrate with filesystem storage) in MySQL/Postgres?

The 'big' players don't even put the images on the same machine. Instead they have "image servers" globally distributed and optimized for pumping out readonly images. In their case, the benefits of distributed processing outweighs the occasional inconsistency (link to non-existent image; vice versa). Yet other machines handle virus checking, scaling, stripping EXIF, etc.

MySQL cannot store a blob bigger than 4GB (yes, you could (should) chunk it). Even 16MB has some issues (with replication, tuning, etc.) that you may trip over.

If you have scans of meta data, and you have big blobs in the rows, the server spends a lot of time stepping over cow patties.  "Vertical partitioning" is an approach to avoid this problem.  In one implementation, I started with a thumbnail and the EXIF data in the main row.  Eventually I discovered the "cow patty" problem and changed to have those two blobs in a parallel table.  It ran noticeably faster.

I've implemented things either way.  Flip a coin to decide which to do.  Think about your decision for six months, then consider changing to the other mechanism.  Meanwhile, design your code so that you can make that change with minimal hassle.


"Error in query (1030): Got error 168 from storage engine" when creating table I'm trying to create a table on a separate directory, like this:

CREATE TABLE `abcdef` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DATA DIRECTORY='/media/sdc/mysql/';

But it returns the error:

  Error in query (1030): Got error 168 from storage engine

In the log it says:

2018-09-17T09:43:22.412636Z 2 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-17T09:43:22.412686Z 2 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.

The directory /media/sdc/mysql/ exists and I ran the following commands already:

chown -R mysql:mysql /media/sdc/mysql
chmod -R 777 /media/sdc/mysql

I'm using MySQL 5.7.

I have another server running MySQL 5.6 where I created the same table with no issue. In fact I'm replicating it on a new upgraded server.

So why can't MySQL use /media/sdc/mysql?

apparmor (and selinux) provide additional access controls beyond permissions that can cause these permission denied (13) errors. This occurs generally on systems where data or log files differ from the default.

With apparmor you would add to the bottom of /etc/apparmor.d/local/usr.sbin.mysqld:

/media/sdc/mysql/ r,
/media/sdc/mysql/** rwk,

And then reload the apparmor service:

service apparmor reload

If it was selinux:

semanage fcontext -a -t mysqld_db_t "/media/sdc/mysql(/.*)?"
restorecon -Rv /media/sdc/mysql


Possible to upgrate SQL Server 2012 express to 2012 Standard? I need to do an in place upgrade of SQL server 2012 express to standard without losing any data. Is this possible? Is it as simple as just running the setup for the standard version? TIA

Yes it's as simple as running the setup and execute the edition upgrade.

Just in case it's better to backup your database before but everything should be ok.

Details here :
https://msdn.microsoft.com/en-us/library/cc707783.aspx

Of course, you should buy the standard licence.


Is SQL Server Certificate using asymmetric encryption internally? In Books Online I've read that both asymmetric key and certificates use the idea of asymmetric encryption. For the asymmetric key it's clear, but do the certificates created in SQL Server or let's say via Always Encrypted, use the idea of asymmetric encryption? So internally they have public and private keys stored in the same place? Does it mean that when we create a symmetric key and encrypt it by a certificate it actually means encrypting a key with a asymmetric encryption?

Thanks

In Books Online I've read that both asymmetric key and certificates use the idea of asymmetric encryption.

They both are asymmetric keys, certificates just have extra metadata and can have other functions run on them such as validation and revocation.

  [...] do the certificates created in SQL Server or let's say via Always Encrypted, use the idea of asymmetric encryption?

Yes, see above.

  So internally they have public and private keys stored in the same place?

Yes, although you can change that by removing the private key from asymmetric keys and certificates. Additionally, the keys do not have to be stored in the physical database, you can use a HSM.

  Does it mean that when we create a symmetric key and encrypt it by a certificate it actually means encrypting a key with a asymmetric encryption?

No, it means that the symmetric key is protected by the asymmetric key. This means the asymmetric key much first be successfully decrypted, the private key must be available, and the symmetric key can then be decrypted using the private key if it exists. Once the symmetric key is decrypted it can be used in operations. If the asymmetric key can not be decrypted, you will get an error and be unable to use the symmetric key as it'll still be encrypted. The data itself is protected by the symmetric key.


How to use an integer pivot in SQL Server? I want to get multiples rows using a pivot:

FIXED  |    0    1    2    3    4
---- ---- ---- ---- ---- ---- ----
First  |    D1   C1   SP   A0   1B

I did it:

DECLARE @zero int = 0
DECLARE @one int = 1
DEClARE @two int = 2
DECLARE @three int = 3
DECLARe @four int = 4
DECLARE @col NVARCHAR(MAX) = '[' + CONVERT(varchar(MAX), @zero) + '], [' + CONVERT(varchar(MAX), @one) + '], [' + CONVERT(varchar(MAX), @two) + '], [' + CONVERT(varchar(MAX), @three) + '], [' + CONVERT(varchar(MAX), @four) + ']'
DECLARE @sql NVARCHAR(MAX)

 SET @sql = 'SELECT * FROM
            (
                SELECT Principal.Fixed, Code.Alpha, Code.Position
                FROM       Principal
                INNER JOIN Code ON Principal.Id = Code.PrincipalId
            ) SOURCE
            PIVOT
            (
                MAX(SOURCE.Alpha)
                FOR SOURCE.Position in ('+@col+')
            ) PIVOTABLE'

EXEC sp_executesql @sql

And the output always is NULL:

FIXED  |    0    1    2    3    4
---- ---- ---- ---- ---- ---- ----
First  |   NULL NULL NULL NULL NULL

What did I do wrong?

First I'd suggest to verify that you source query returns expected data:

SELECT     Principal.Fixed, Code.Alpha, Code.Position
FROM       Principal
INNER JOIN Code ON Principal.Id;

I've set up a single test and it should works. (dbfiddle here)

Second, you can build @col variable dynamically by using Position values returned by your main query, avoiding to build it using variables (@one, @two, ...)

DECLARE @col AS nvarchar(MAX),
        @cmd AS nvarchar(MAX);

SET @col = STUFF((SELECT distinct ',' + QUOTENAME(Code.Position)
                  FROM Principal
                  INNER JOIN Code ON Principal.Id = Code.PrincipalId
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'')

SET @cmd =   'SELECT Fixed, ' + @col
           + ' FROM'
           + ' (SELECT Principal.Fixed, Code.Alpha, Code.Position '
           + '  FROM       Principal '
           + '  INNER JOIN Code ON Principal.Id = Code.PrincipalId) src'
           + ' PIVOT (MAX(Alpha) FOR Position IN (' + @col + ')) pvt';


Cardinality , Parallel Hint alternative for MS-SQL UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS
(SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1
WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y');

This is the oracle query that I have, I want to create similar query for my MS SQL DB, Any help please

Just remove the hint for SQL Server.
The cardinality and parallel hints are designed to help control performance when you "know better" than the DBMS--they don't affect the results in any way. You can't know if you'll get acceptable performance in SQL Server until you run the query.
The different platforms will almost certainly need tuning in different places, using different tools. For example, parallelism works differently in SQL Server, so the related query tuning is approached differently. If you DO need to control parallelism, using a hint has different semantics and happens in different scenarios.
So just remove those hints (or leave them. SQL Server will just interpret them as comments), and watch performance.
Tune the queries that need it: First by making sure there are indexes and statistics so that the optimizer has the data it needs to get a good plan, then by exploring alternate ways to write your query, and only last by exploring hints.


Check for column in backupset table Created a script to show last backup locations, sizes and dates

SELECT DatabaseName = x.database_name,
        LastBackupDate = x.backup_finish_date,
        BackupSizeKB = x.backup_size,
        LastBackupFileName = x.device_name,
        left(right(x.device_name, 21),17) as Backup_Device_Date,
        type = x.type
        FROM (  SELECT  bs.database_name, bs.type,
                case
                    when exists(
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
                    )
                    then cast(round(max(bs.compressed_backup_size/1024),0) as numeric)
                    else cast(round(max(bs.backup_size/1024),0) as numeric)
                end as backup_size,
                max(bs.backup_finish_date) as backup_finish_date,
                max(bmf.physical_device_name) as device_name
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.is_copy_only = 0
          GROUP BY bs.type, bs.database_name) x
WHERE x.database_name not in ('master', 'model', 'msdb')
AND x.type IN ('D')
ORDER BY DatabaseName;

The only problem, is that I get the following error when its executed on a SQL Server 2005 Enterprise 9.00.3042 64bit

Msg 207, Level 16, State 1, Line 12
Invalid column name 'compressed_backup_size'.

If I change the then/else to

case
when exists(
 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
)
then 1
else 0
end as backup_size

I get 1 or 0 for backup_size depending on whether *compressed_backup_size* exists in backupset.

database_name   type    backup_size backup_finish_date  device_name
model   I   0   2013-09-02 18:30:19.000 Data Protector_(DEFAULT)_model_163022971_0
msdb    I   0   2013-09-02 18:30:22.000 Data Protector_(DEFAULT)_msdb_163022971_0

So in spite of using CASE WHEN THEN ELSE to check for the existence of *compressed_backup_size*, I'm still getting an error. Any thoughts on why the THEN argument is being parsed when the column *compressed_backup_size* clearly doesn't exist ?

Thanks in advance

You have to use dynamic SQL to hide that column from the parser in 2005. All columns are checked to make sure they exist before the query runs.

DECLARE @sql nvarchar(MAX);

SET @sql = N'
SELECT DatabaseName = x.database_name,
        LastBackupDate = x.backup_finish_date,
        BackupSizeKB = x.backup_size,
        LastBackupFileName = x.device_name,
        left(right(x.device_name, 21),17) as Backup_Device_Date,
        type = x.type
        FROM (  SELECT  bs.database_name, bs.type,' +
                case
                    when exists(
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
                    )
                    then N'cast(round(max(bs.compressed_backup_size/1024),0) as numeric)'
                    else N'cast(round(max(bs.backup_size/1024),0) as numeric)'
                end + N' as backup_size,
                max(bs.backup_finish_date) as backup_finish_date,
                max(bmf.physical_device_name) as device_name
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.is_copy_only = 0
          GROUP BY bs.type, bs.database_name) x
WHERE x.database_name not in (''master'', ''model'', ''msdb'')
AND x.type IN (''D'')
ORDER BY DatabaseName;';

EXEC(@sql);