Split date range (timestamp) into equal parts by Month- SQL Server Experts,
Have a question regarding splitting a date range into equal parts by months including the time part
example - fromdate - 06/29/2020 09:00:00 and todate - 06/29/2021 09:00:00
Want to split this date range into twelve equal parts like below
06/29/2020 09:00:00 - 06/30/2020 12:59:59
07/01/2020 00:00:00 - 07/31/2020 12:59:59
.........
.......
06/01/2021 00:00:00 - 06/29/2021 09:00:00

I cant write recursive CTE as this is a sql synapse module I am running against..
With below query I am able to split the date part, but time part is not coming properly as above.. Please help me as this is a blocking my development
declare @FromTs DATETIME
declare @ToTs DATETIME
SET @FromTs = GetDate()
SET @ToTs = DATEADD(month, 12, @FromTs)

;WITH n(n) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(qi,qrt,qtt,n,f,t,md,bp,ep,rn) AS
(
    SELECT
    ,n.n, @FromTs, @ToTs,
    DATEDIFF(MONTH, @FromTs, @ToTs),
    DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY( @FromTs),  @FromTs)),
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n,
    DATEADD(DAY, 1-DAY( @FromTs),  @FromTs))))
    FROM n INNER JOIN <datetbl> AS d
    ON @ToTs >= DATEADD(MONTH, n.n-1, @FromTs)
)
SELECT qi,qrt,qtt,
new_from_date = CASE n WHEN 0  THEN f ELSE bp END,
new_to_date   = CASE n WHEN md THEN t ELSE ep END,rn
FROM d WHERE md >= n

WITH
-- Range borders
src_data AS (SELECT CAST('2020-06-29 09:00:00' AS DATETIME) AS fromdate,
                    CAST('2021-06-29 09:00:00' AS DATETIME) AS todate),
-- numbers table
numbers AS (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
             UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
             UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11)
-- The query
SELECT num + 1 id,
       DATEADD(SECOND, num * DATEDIFF(SECOND, fromdate, todate) / 12, fromdate) fromdate,
       DATEADD(SECOND, (num + 1) * DATEDIFF(SECOND, fromdate, todate) / 12, fromdate) todate
FROM src_data, numbers
ORDER BY id;

fiddle
The range is divided to equal (in seconds) parts. You may alter the calculations granularity (for example, use HOURS instead of SECOND), parts lengths will be adjusted and rounded automatically.
You may replace numbers CTE with any other which gets/generates the integers list from 0 to parts_amount-1, and parametrize the divident used in a query (12, may be replaced with, for example, SELECT COUNT(num) FROM numbers).


How can I tell how many Cores SQL Server is actually using? I have two servers running SQL Server.

Server 1: SQL Server 2008 R2 Express (4 core)
Server 2: SQL Server 2012 Developer Edition (8 core)

As far as I am aware SQL Server 2008 R2 Express should only use one core.

SQL Server 2012 Developer version should use all 8 cores.

However if I run the following command inside of SQL query on SQL Server 2008 R2 Express it's showing 4 cores.

select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'

Am I using the right command to gauge the usage?

Express edition is limited by 1 processor, not 1 core:

  SQL Server supports the specified number of processor sockets
  multiplied by the number of logical CPUs in each socket. For example,
  the following is considered a single processor for purposes of this
  table: A single-core, hyper-threaded processor with 2 logical CPUs per
  socket. A dual-core processor with 2 logical CPUs. A quad-core
  processor with 4 logical CPUs. SQL Server is licensed per processor
  socket, and not per logical CPU basis.

http://msdn.microsoft.com/en-us/library/ms143760(v=sql.105).aspx

And yes, sys.dm_os_schedulers can be used to ensure, that all 4 cores are mapped with schedulers.


Pros and Cons of virtualisation technology for database-server and datastorage Yesterday we had a chat about performance and restorability and I realized how many good things a virtualisation environment can bring to me - but as I'm a little sceptic concerning performance I'm asking here. It could be a little GIS specific, but over at the gis-users they said it's to database specific... ;)

Will a database server suffer from severe performance loss through virtualisation? I don't understand the technology to the last detail, but somehow it's a 'blackbox' more, that needs to be processed through hardware. Also will disk access, with all the tricks PostGIS gives us be granted? (clustering, index etc.) - fragmented clustering is like no clustering!

The big advantage is maintenance and scalability. In case of a severe hardware malfunction I can migration within minutes or even realtime to another physical machine.

Who has experience and can point me to good websites or literature on this topic? I remember a few things from the last fossgis and a few in-house benchmarks on ESXi and native Servers and somehow I can't make up my mind whether it's good or not.

This is one of those "It depends" questions.

Performance depends on resources, contention, configuration, and the VM engine

Uncontended VM host: If you properly resource a VM with uncontended high performance locally-attached or SAN storage, low contention for CPU resources, no memory overcommit or contention, fast dedicated network access, etc, it'll generally perform very well on a properly tuned VM engine. Exact results will depend on the VM system used, on how you provide access to resources, and lots more.

You can get great results on high end VPS plans with guaranteed low contention ratios and good storage.

Contended/under-resourced VM host: If you put it on the same box as three other application servers and a file server, all of which share the same RAID 5 array and are fighting over RAM and CPU, it'll perform terribly.

If you put it on a cheap over-subscribed and overcomitted VPS host somewhere you'll get similarly poor results. If half your RAM is really swap on the host's disk, nothing is going to be fast.

"In the cloud": If you put it on EC2, Azure, or whatever, then performance will depend on the contention ratios for the service, the storage they're using, what other users are doing, how good their QoS is, and lots more.

At least for EC2 the disk subsystem has horrible performance (on standard VMs, at least in 2012) so it only performs OK if you have enough RAM to cache at least your indexes. Amazon has introduced new high I/O instances that might be better but I haven't seen benchmarks yet.

Usually, you'll get something in-between if you choose lightly contended hosting with decent disks, like high quality higher-end virtual private server hosts.

Direct vs VM guarantees

Re specific guarantees about things like file ordering, that depends on your VM setup. Are you using VMs backed by files? By raw block devices? By an iSCSI SAN? It also depends on how your VM engine is configured, and exactly which VM system you're using.

Best case

In the best case - a system with all-paravirt drivers, VT-x, VT-IO, uncontended access to host resources, etc - you'll probably get performance pretty close to the host. If you give the VM direct block devices not host files for storage then you'll get proper file ordering without host-side fragmentation. Exactly how close will depend on your particular hardware, host and guest, configuration, and more; benchmark it with your workload.

fsync() and write durability

One thing to watch out for with VMs is that you must make sure the disk system tells the truth about fsync(). A very easy way to make VMs a lot faster is to ignore fsync() requests. That's fine until the VM host crashes or loses power, at which point your databases are likely to be hopelessly corrupted. The VM host must either honour fsync() requests by respecting the guest OS's disk flush commands, or must offer non-volatile write cache that won't go away if power is lost. Some SANs use SSDs for that, most other systems use battery backed RAID controller cache memory. If your VM can process more than a few hundred transactions per second it's likely to either be ignoring fsync or on write-caching storage, and you should find out which before it eats your data.

But why?

(Updated): As noted by Chris Travers, why should you virtualise DB servers? Why not handle replication, heartbeat and failover at the DB server level, migrate via promotion of replicas, and get the full performance of the bare metal?

I wrote this original response with the mindset that a VM was a given, and the question was how to get the best results. The best virtualisation for a DB server is still, in my mind, no virtualisation. That said, I've only managed fairly small sites.


set global sql mode but showing a different mode I have this command to run in my docker file

mysql -e "SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';"

But when i check the mysql mode it shows as

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.01 sec)

Does anyone has any experience with this ?

Your problem is quite simple.
When you change the value of a global variable, the current session still retains the session value.
To prove that, please run this
SELECT * FROM information_schema.global_variables
WHERE variable_name='sql_mode'
UNION SELECT * FROM information_schema.session_variables
WHERE variable_name='sql_mode';

You should get this output if the global and session values are the same
+---------------+------------------------+
| VARIABLE_NAME | VARIABLE_VALUE         |
+---------------+------------------------+
| SQL_MODE      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

You should get this output if the global and session values are different
+---------------+------------------------+
| VARIABLE_NAME | VARIABLE_VALUE         |
+---------------+------------------------+
| SQL_MODE      | STRICT_ALL_TABLES      |
| SQL_MODE      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

To see that change, you must logout and login. Then, run the UNION query.
If you cannot logout of the current session, you must run
SET SESSION sql_mode = 'STRICT_ALL_TABLES';

to see the difference.
GIVE IT A TRY !!!
UPDATE 2015-09-03 18:14 EDT
You just asked me

Why doesnt it has the STRICT_ALL_TABLES in the global session though ?

Guess what ?? @@sql_mode is not global. It's session.
Let me run this query for the difference:
mysql> select @@global.sql_mode,@@session.sql_mode,@@sql_mode;
+-------------------+------------------------+------------------------+
| @@global.sql_mode | @@session.sql_mode     | @@sql_mode             |
+-------------------+------------------------+------------------------+
| STRICT_ALL_TABLES | NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+-------------------+------------------------+------------------------+
1 row in set (0.00 sec)

Therefore, if you want global, you must use @@global.. Otherwise, it's session.


Verifying Oracle 12 client server encryption I'm getting conflicting information on whether encryption is active between my Oracle server and a Linux client:

SQL> select NETWORK_SERVICE_BANNER from v$session_connect_info where SID =
sys_context('USERENV','SID');
NETWORK_SERVICE_BANNER
----------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
Encryption service for Linux: Version 12.2.0.1.0 - Production
RC4_40 Encryption service adapter for Linux: Version 12.2.0.1.0 Production
Crypto-checksumming service for Linux: Version 12.2.0.1.0 - Production
SQL>

Linux application server sqlnet.ora looks like below

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA1)
SQLNET.CRYPTO_CHECKSUM_CLIENT = required
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
SQLNET.ENCRYPTION_CLIENT = required
TRACE_LEVEL_CLIENT=16

Server rebooted for good measure.

The traffic looks encrypted from a tcpdump's output, verified with wireshark:

tcpdump -n -A -vvv -i eth0 host client_IP and Oracle_server_IP -w ./output

Trace files (.trc) confirm the above too:

na_tns: Encryption is active, using AES256

Why is the NETWORK_SERVICE_BANNER not reporting it correctly?

SOLVED - My .bash_profile was referencing Oracle 11 and thus the wrong sqlnet.ora was being loaded.


Mongoimport 'error connecting to db servers' for cluster I'm unable to use mongoimport to load some documents to a cluster hosted by MongoDB. I followed this tutorial Load File with mongoimport and then used this command in Powershell to try and load my documents:

.\mongoimport.exe --host "mongodb://clusterads-shard-00-00-qlf9n.mongodb.net:27017,clusterads-shard-00-01-qlf9n.mongodb.net:27017,clusterads-shard-00-02-qlf9n.mongodb.net:27017/admin?replicaSet=ClusterADS-shard-0" --ssl -u <USER> -p '<PASSWORD>' --authenticationDatabase <AUTHENT> --db adscoursework --collection volcanos --drop --jsonArray --file C:\data\volErups.json

This gives the error:

[........................] adscoursework.volcanos       0B/844KB (0.0%)
[........................] adscoursework.volcanos       0B/844KB (0.0%)
Failed: error connecting to db server: no reachable servers

I can connect to the above host using Mongo Shell so I don't understand where the issue is.

Replacing mongodb:// at the start with ClusterADS-shard-0/ (which is the replica set) and removing /admin?... made it work.


When are privileges listed in \l and when not? When are access privileges listed by \l, and when are they not?  The access privileges listed by \l can change after a grant and revoke:

$ createuser -EP my_readonly
$ psql development
development=# \l
                                           List of databases
            Name             |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------------------------+----------+----------+-------------+-------------+-----------------------
 development                 | vagrant  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
...
development=# grant usage on schema public to my_readonly;
development=# grant connect on database development to my_readonly;
development=# \l
                                             List of databases
            Name             |  Owner   | Encoding |   Collate   |    Ctype    |     Access privileges
-----------------------------+----------+----------+-------------+-------------+----------------------------
 development                 | vagrant  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/vagrant               +
                             |          |          |             |             | vagrant=CTc/vagrant       +
                             |          |          |             |             | my_readonly=c/vagrant
...
development=# revoke connect on database development from my_readonly;
REVOKE
development=# revoke usage on schema public from my_readonly;
REVOKE
development=# \l
                                           List of databases
            Name             |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------------------------+----------+----------+-------------+-------------+-----------------------
 development                 | vagrant  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/vagrant          +
                             |          |          |             |             | vagrant=CTc/vagrant

Why is that?  What state changed?  I believe the my_readonly user's ability to connect was unchanged through this whole psql session (because I'm guessing the PUBLIC role has connect privileges), but clearly something changed: what is that thing?

Side question: how can I explicitly ask postgres whether PUBLIC in fact does have connect privileges (they may have been revoked -- see Why can a new user select from any table?)?

The backslash commands in psql are shortcuts for a query or queries that look through the system catalogs. The \l command looks at information in pg_catalog.pg_database, specifically, this query:

SELECT d.datname as "Name",
   pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
   pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
   d.datcollate as "Collate",
   d.datctype as "Ctype",
   pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;

You can make psql show what it is using for the backslash commands by passing the -E flag to it when you invoke it on the command line.

If the permissions on a database or other object are the defaults that PostgreSQL creates them with, the *acl column will be NULL. If you change the defaults, as you have, the ACL column will be populated with information related to the GRANT and/or REVOKE statements you have ran.

You can see the permissions/ACLs specifically via either \z or \dp

If you read further here:

http://www.postgresql.org/docs/9.4/static/sql-grant.html

If you scroll down, (or search for the word psql), you can look at the table that shows you how to interpret the ACLs that you see with \l or in an ACL column.

For example:

=Tc/vagrant

means that PUBLIC (the implicit role that contains all roles) has permissions to create temporary tables T and connect c, because the ACL line =xxxxx denotes permissions applied to PUBLIC, while rolname=xxxx applies to that specific role.

This presentation from Dalibo should also help clarify this further: Managing Rights in PostgreSQL

Hope that helps. =)


Recommendations for MS SQL Server monitoring software or service? I have a client with MS SQL Server instances (2000, 2005) scattered across 20-30 servers. I've been engaged to get things organized and will be periodically reviewing the state of the database infrastructure for health, testing backups, etc. We also plan to get as many of these databases up to 2008 as possible.

I'm aware of services like New Relic for Linux stack web apps and databases, and I stumbled onto the SQL Monitor product (RedGate) just today via Google. Can anyone provide some direction in terms of services or tools (that you have experience with) that would enable me/my client to monitor and manage their MS SQL instances?

You've got lots of options.  The venders to look at that are SQL specific are:

Quest Software - Spotlight
Red Gate Software - SQL Monitor
Confio - Ignite
Idera - SQLdm
Microsoft - System Center Operations Manager
Microsoft - SQL Server 2005 Performance Dashboard
SentryONE - Performance Advisor
ApexSQL Monitor

They will all do the job nicely, it just really depends on which one works best for you in your shop.  They all have free trials so you can try all of them and make an informed decision.


Moving from Azure to Hyperscale Does Azure Hyperscale allow for Memory optimised tables and does it allow/have rollback facilities

Check out the Docs on Hyperscale:

  Hyperscale only supports non persistent In-Memory objects (table
  types, native SPs and functions). Persistent In-Memory tables and
  other objects must be dropped and recreated as non-In-Memory objects
  before migrating a database to the Hyperscale service tier.

As for rollback, it depends what you mean. Can it rollback an uncommitted transaction? Yes, it is a SQL Server after all. Can it restore databases to a point-in-time? Yes, see here and here. For anything else, you'd have to clarify what you mean by rollback.


What are the practical impacts of the sys.dm_exec_query_stats warning in the documentation? The documentation for sys.dm_exec_query_stats states the following:

  An initial query of sys.dm_exec_query_stats might produce inaccurate
  results if there is a workload currently executing on the server. More
  accurate results may be determined by rerunning the query.

I sometimes query that DMV during an active workload and would prefer accurate results. I do not know how to apply the above warning in practice. Should I always query the DMV twice and use the second result set because that will be more accurate? That feels a bit far-fetched. Do I need to be aware of the ways in which the DMV can be inaccurate so I can factor that into my analysis? If so, what kind of inaccuracies can appear: missing rows, outdated values, inconsistent rows, or something else?

What are best practices when using sys.dm_exec_query_stats during an active workload?

I've updated the documentation to be more straight-forward on the wording. The intent was to tell the user that running a query against the DMV will produce output only for items that had completed and not for items in-flight. Thus, run the workload in its entirety to make sure all is captured.

The new wording will be the following and applied the various DMVs the previous wording was incorporated into.

  The results of sys.dm_exec_query_stats  may vary with each execution
  as the data only reflects finished queries, and not ones still
  in-flight.


Can i make a relationship between two relationships in the database? I am making a database system where there are patients, doctors, categories and medical records.

doctor and patient has a relationship called treatments ( many-to-many )
patient and a relationship with medical record called patient-records ( one-to-many )
medical record and categories has relationship called record-category (many-to-many)

My questions:

How do I include the details and information of the medical record in the treatment relationship?  Should I make a joined query and get the details of record from the patient?

My goal is to create a full record for the patient where all their history, diagnoses, and treatments are available as a sheet or web page.

Yes! You can make a new table which has foreign keys to both treatments and patient-records.

But be careful either in your model or in your code that you don't associate patient-records from a different patient with the patient associated with the treatment... using natural keys is one way to solve this:

treatments has a primary or unique key (doctor, patient) -- Doctor and Patient can be surrogate keys -- and both of these are foreign keys to doctor and patient, respectively.
patient-records has a primary or unique key (patient, medical-record) -- similarly, these are both foreign keys.
your new table has a primary or unique key (doctor, patient, medical-record) -- and there are two foreign keys: one on (doctor, patient) to treatments, and the other on (patient,medical-record) to medical-records.


Best Practice for Handling Table with Large Amount of Transaction Data I have a transaction table storing large amount of transaction data. Only the most recent data of one month are required to be available for transactional use in real-time. Less recent data may only be used for report generation or offline analysis.
What is the best practice for handling such transaction data?
Should I create a history table with same columns and move data with age over one month from the transaction table to the history table with a batch job?
Should I create a history table per year? Or just use a single history table to host all past data?
Or Should I use database partitioning instead of history tables?
Or should I create a data warehouse and move data into it?

You should specify which database system and version you're using as this will affect your actual choices available for how you structure and manage the data.
You mentioned large in your context means 100,000 new records a day, so let's plan for something an order of magnitude bigger, 1 million records a day. In a month that's 30 million new records, in a year that's ~360 million records. While 360 million records is starting to get a little beefy, it's by no means something unmanageable or needing special treatment in most modern database systems.
It's going to depend on the actual queries you're running, the concurrency of your server with writes vs reads, and the acceptable runtime for reporting off the data, but 360 million records in a year shouldn't scare you with modern RDBMS. I've managed tables with 10s of billions of records in them, and reported directly off them usually querying for about 1 million records at a time in under 10 seconds on fairly modest hardware.
If you want to reduce the concurrency of reporting then you can look into archiving the older data (i.e. anything older than the last one month of data) or migrating it into a data warehouse. Depending on the type of querying being done, and the database system you're using, you might have features available like columnstore indexing and filtered indexes that could help you improve reporting directly from the table itself without the need to archive.
Correctly architecting your schema and indexing your data will go the longest of ways in improving your reporting capabilities.
Finally, as Jonathan Fite mentioned, partitioning won't directly improve your querying performance but it can be used to improve your management of the table because you can smartly divide your data into partitions that allow you to manage the less active data while the more active partitions are in use. You'll see improvements in reduced concurrency when you need to do things like archiving your data (through partition swapping) or index maintenance.

If your estimate of 100,000 rows per day is accurate, and you don't anticipate any meaningful growth from that over the next few years in actuality, then you're only looking at 36 million rows a year. I wouldn't even think about partitioning or archiving at that point. Regular indexing on a well architected schema should provide you very quick query runtimes. But I personally like to plan for a magnitude more of data then I actually expect, like above.


Suitable database software for small business First of all, I apologise if this is in the wrong area.

I'm looking for a suitable database program for a small engineering business, which needs to log various jobs. Currently, we're just using an Excel spreadsheet shared over the cloud, however this has problems with conflicts when two people edit the file at the same time.

What we want is something that can operate over the internet (so that multiple computers can access it from different locations), allowing multiple editors simultaneously. It also needs to be capable of conditional formatting. Essentially, it needs to be a kind of hybrid between a desktop database and a server database. Something that can be saved to the cloud would be the most preferable for sharing reasons.

A screenshot example of what we currently have is here

From my research I have a vague inkling that SQL would be a good base, but I'm really looking for a program that requires absolutely no background knowledge of how the language works. i.e something that isn't going to take a significant amount of time to learn to use. If there's info that I've not included, please let me know.

Thanks in advance.

For something simple like what you have, I would highly recommend Google Docs or one of variants. It's basically a spreadsheet where everyone that you give permissions to can edit at the same time. Everyone can see what everyone else is doing, so there aren't many conflicts.

A SQL database would certainly support simultaneous accesses, but I think it's overkill for what you're looking for. You would need to build an application on top of the database just to use it. If none of you are willing to spend the time to write and maintain this application, I would not do it.


Timezone offset displayed though it shouldn't be stored I new to Postgres and v9.6.3 on a Linux system.

From the docs and the PostgreSQL: Up and Running book, I learned that the timestamptz type just subtracts the offset of a timestamp on insertion and then just stores UTC on disk. The timezone information is 'lost' so to say.

Now I'm wondering how the psql client can display the timezone correctly despite that:

The following row was created by something like:

insert into table values ('1999-12-31T00:00:00+0100'::timestamptz, ...);

and is displayed as

select time from table;

          time          | field1 | field2 |
------------------------+--------+---------
 1999-12-31 00:00:00+01 |      1 |       1 |
...

and

select time AT TIMEZONE 'Europe/Berlin' from table;

          time
---------------------
 1999-12-31 00:00:00
...

You can see in the first snippet that postgres seems to know the correct timezone from the insertion ('Europe/Berlin' which has +01h offset to GMT in winter) But how does this come?
I would have expected it do display UTC time.

From the PostgreSQL documentation you link to, about halfway down the page:

  When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct.

So (as Evan Carroll hinted in his comment), if your system has 'Europe/Berlin' set as timezone, you would expect to see the UTC date/times output using that timezone.

To confirm, try inserting a date/time from another timezone; that should also be output showing the 'Europe/Berlin' time instead. Or, try changing timezone, and confirm that the dates that were showing the time from Europe/Berlin now show the time from the new time zone instead.


Inner join on output of a subquery We have Slot table which tells us about freetime for an employee between starttime and endtime.

We need to find Slot which have freetime > 'x' mins. This could be either be a complete slot or it can be consecutive slot for same employee(in this case we show the first slot to user).

We are using MySQL 5.7

  `CREATE TABLE `Slot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `freetime` time NOT NULL,
  `assigned_To_Id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1

We tried following query :

Select        distinct S1.starttime,
               S1.endtime
from
    (Select * from `Slot`
     where     `assigned_To_Id` in (2, 3, 4)
     and       `freetime`  > 0
     and       `starttime` >= '2018-08-08 00:00'
     and       `endtime`   <= '2018-08-08 23:59'
     order by `Slot`.`starttime` ASC) as S1

inner join S1 as S2
 on        (S1.freetime >'00:10:00')
 or        (S2.starttime = S1.starttime + INTERVAL 60 MINUTE
 and       ADDTIME(S1.freetime, S2.freetime) > '00:10:00'
 and       S1.assigned_To = S2.assigned_To)

We are getting stuck as to how  to run self join on output of S1- its gives error saying that S1 table does not exist.

We read online and found about Common Table Expressions but we are using 5.7 version.

We are not sure how to go about doing inner join on same query.

Example :

|id| startime            | endtime             | assigned_To_Id | freetime
|1 | 2018-07-07 11:00:00 | 2018-07-07 12:00:00 |   2            | 00:00:05
|2 | 2018-07-07 12:01:00 | 2018-07-07 13:00:00 |   2            | 00:00:15
|3 | 2018-07-07 13:01:00 | 2018-07-07 14:00:00 |   2            | 00:00:05
|4 | 2018-07-07 15:01:00 | 2018-07-07 16:00:00 |   2            | 00:00:15

Output Should be :

|1 | 2018-07-07 11:00:00 | 2018-07-07 12:00:00 |   2            | 00:00:05
|2 | 2018-07-07 12:01:00 | 2018-07-07 13:00:00 |   2            | 00:00:15
|4 | 2018-07-07 15:01:00 | 2018-07-07 16:00:00 |   2            | 00:00:15

You can do all that without subqueries:

SELECT DISTINCT S1.starttime
     , S1.endtime

  FROM Slot AS S1
  JOIN Slot AS S2  ON S2.assigned_To_Id = S1.assigned_To_Id
                  AND S1.assigned_To_Id IN (2, 3, 4)
                  AND S1.id != S2.id -- a little trick to prevent joining rows with itself
                                     -- slightly reduce the derivative table size
                                     -- and speed up the overall performance

 WHERE S1.starttime >= '2018-08-08 00:00'
   AND S1.endtime <= '2018-08-08 23:59'
   AND S1.freetime > 0
   AND ( S1.freetime > '00:10:00'
         OR ( S2.starttime = S1.starttime + INTERVAL 60 MINUTE
              AND ADDTIME(S1.freetime, S2.freetime) > '00:10:00' ))

 ORDER BY S1.starttime ASC
;

In fact aliasing is not transitive operation in the mysql. Say, simple query

SELECT 1 AS a, a AS b;

yeild an error Unknown column 'a' in 'field list'. So you can create an alias for the real object not for another alias.


Retreive a Customized List with input data I have a database that has about 223 tables and I need to locate the name of the constraint.

My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the name of the table and its column. In other word, two column. You show the list that is using the specific constraint based on input data.

I do not know how to create it. I'm a rookie in SQL server

Thanks!

My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the name of the table and its column.

use AdventureWorks2012;

declare @ConstraintName varchar(max)

set @ConstraintName = 'CK_Product_ProductLine' --<--- Change  here with your constraint Name

if exists (
        select 1
        from sys.objects
        where OBJECT_NAME(OBJECT_ID) = '' + @ConstraintName + ''
        )
begin
    select OBJECT_NAME(OBJECT_ID) as NameofConstraint
        ,SCHEMA_NAME(schema_id) as SchemaName
        ,OBJECT_NAME(parent_object_id) as TableName
        ,[type_desc] as ConstraintType
    from sys.objects
    where type_desc like '%CONSTRAINT'
        and OBJECT_NAME(OBJECT_ID) = '' + @ConstraintName + ''
end
else
    select 'There is no constraint with the name that you specified'

Below will be output :


Is `mysql_stmt_send_long_data` supported? I am trying to insert large blobs into MySQL but fails because of ER_NET_PACKET_TOO_LARGE. I don't want to change the setting of the MySQL that's why I was hoping if this approach is supported here?

This module does not (currently) support prepared statements functionality MySql Todo Github


How to Apply a Title-Case RegEx Condition for Object Names with Policy Based Management I'm implementing PBM on a SQL Server 2014 instance and want to enforce a condition that all objects are created outside the dbo schema and all object names are created in title-case (i.e., OrderDetail). I have the regular expression drafted [A-Z]{1}[a-z]+[A-Z]?[a-z]* but am not able to create a satisfactory object name with it. An example output is here.

Policy condition: '@Schema != 'dbo' AND @Name LIKE '[A-Z]{1}[a-z]+[A-Z]?[a-z]*''
Policy description: ''
Additional help: '' : ''
Statement: 'CREATE TABLE Test.OrderDetail (
    Col1 int)

Is this particular expression not supported by PBM or is there alternate syntax I should use in the condition?

SQL Server's implementation of the Like operator only supports a really cutdown kind of regEx which includes basic character matching and wildcards, but does not include Number of repeats like {1} or the other kind regEx anchors like \b or \s.  It is also generally speaking, not case-sensitive.

That said, you can sometimes spoof these kind of things with Like.  Say you wanted to find 3 alpha characters and two numbers you could do something like:

[a-z][a-z][a-z][0-9][0-9]

You can also force case-sensitve matches by using a case-sensitive collation, eg

DECLARE @s VARCHAR(10) = 'orderDetail'

-- Check case;
SELECT
    CASE
        WHEN UPPER( LEFT( @s, 1 ) ) = LEFT( @s, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1
        ELSE 0
    END

SET @s = 'OrderDetail'

-- Check case;
SELECT
    CASE
        WHEN UPPER( LEFT( @s, 1 ) ) = LEFT( @s, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1
        ELSE 0
    END

However, isn't TitleCase more of a philosophical position than a policy?  How would you determine whether TitleCase or Titlecase is correct?  In fact they both are.

Your particular regEx pattern would also match 'orderDetails' and the 'Details' secton of 'orderDetails'.  What you could do is add the start and end word anchors, eg

^[A-Z]{1}[a-z]+[A-Z]?[a-z]*$

but as described, this won't work with Like and anyway and I'm not convinced it's worthwhile.  All you really want to do (apart from the dbo schema check, which is easy and worthwhile), is check the first character is upper case.  So in theory you could do something like this with the extremely powerful PBM ExecuteSql function:

ExecuteSql('Bool', 'SELECT CASE WHEN UPPER( LEFT( @@ObjectName, 1 ) ) = LEFT( @@ObjectName, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1 ELSE 0 END')

I think I got this to work in the short time I spent on this and besides, you should probably be using camelCase : )

Another interesting example:
https://www.simple-talk.com/blogs/2013/09/16/sql-server-policy-based-management-creating-a-custom-condition/

HTH


What permission needs to be added to see the Security folder in Azure SQL? I have the Security folder, and I am trying to setup other Users with the same permissions that I have.

My User is not the Administrator. Though I was granted all of my permissions (which is about everything) by the Administrator. But this was done months ago and the Administrator (just a dude) does not remember what permissions they gave me.

As a minimum you need to be a member of loginmanager role to see Security folder.

Ref:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

  Login managers

  The other administrative role is the login manager role. Members of
  this role can create new logins in the master database. If you wish,
  you can complete the same steps (create a login and user, and add a
  user to the loginmanager role) to enable a user to create new logins
  in the master. Usually logins are not necessary as Microsoft
  recommends using contained database users, which authenticate at the
  database-level instead of using users based on logins. For more
  information, see Contained Database Users - Making Your Database
  Portable.

Creating a login:

    USE [master]
    GO

   CREATE LOGIN [testPerm2] WITH
   PASSWORD=N'strong PW'

Creating a user in master with no other privilege.

USE [master]
GO
CREATE USER testPerm2
    FOR LOGIN testPerm2
    WITH DEFAULT_SCHEMA = dbo
GO

Now connect using SSMS and this is what you see (Notice no security).

Adding testPerm2 user to loginmanager role.

EXEC sp_addrolemember N'loginmanager', N'testPerm2'
GO

Again connect with SSMS or refresh your previous connection and now testPerm2 user can see security tab.


Arrange two tables and get result I have these two tables:

Center

+--------+---+--------+--------+---------+
|IdParent|Row|IdCenter|IdNodeIn|IdNodeOut|
+--------+---+--------+--------+---------+
|1       |1  |470     |1       |5218     |
|1       |2  |22      |5298    |1        |
|1       |3  |20      |5242    |5284     |
+--------+---+--------+--------+---------+

Connection

+--------+---+------------+--------+----------+---------+
|IdParent|Row|IdCenterFrom|IdNodeIn|IdCenterTo|IdNodeOut|
+--------+---+------------+--------+----------+---------+
|1       |1  |470         |5218    |20        |5242     |
|1       |2  |20          |5284    |22        |5298     |

I need to get result like this:

+--+--------+------+
|Id|IdCenter|IdNode|
+--+--------+------+
|1 |470     |1     |
|2 |470     |5218  |
|3 |20      |5242  |
|4 |20      |5284  |
|5 |22      |5298  |
|6 |22      |1     |

Detail:

Center table has 3 rows (can have more, but minimum 2 rows) indicating 3 center, each has tow connection points

As part of network, input point in first center and output point in last center may connect to no node, so it's Id can be 1. Centers are not sorted.

Connection table shows that two connections has connected these three centers. Connection table is sorted, means that first row IdNodeIn is the first connection start point, then if goes to IdNodeOut on the same row ( show start and end of connection). Then this circuit continue to second row, and connect to IdNodeIn and the last end point is IdNodeOut.

So Center by Id = 470 is connected to 20 and then to 22.

Or by connection by Node Id is 5218 to 5242, then to 5284 and last to 5298

I want all of these in above result table, I can get them all in one table but I can not arrange them, here is my select statement:

SELECT
    m.Id, m.IdCenter, m.IdNodeIn
FROM
    Link.LinkCenter m
LEFT OUTER JOIN
    Link.LinkConnection c ON c.IdNodeOut = m.IdNodeIn

UNION ALL

SELECT
    m.Id, m.IdCenter, m.IdNodeOut
FROM
    Link.LinkCenter m
LEFT OUTER JOIN
    Link.LinkConnection c ON c.IdNodeIn = m.IdNodeOut
ORDER BY
    Id, IdC

and the result is:

+----+--------+------+
|Id  |IdCenter|IdNode|
+----+--------+------+
|NULL|470     |1     |
|1   |470     |5218  |
|NULL|22      |1     |
|2   |22      |5298  |
|1   |20      |5242  |
|2   |20      |5284  |

here is a graph:

+---------------+       +---------------+       +---------------+
|IdCenter : 470 |       |IdCenter : 20  |       |IdCenter : 22  |
|IdNodeIn : 1   |   +-->|IdNodeIn : 5242|   +-->|IdNodeIn : 5298|
|IdNodeOut: 5218|>--+   |IdNodeOut: 5284|>--+   |IdNodeOut: 1   |
+---------------+       +---------------+       +---------------+

Please help, although it may sound easy.

I'm not sure this is what are you looking for because IMHO you must deal with recursive functions.

Using only centers table

select IdCenter, IdNode
from
(
    select     ce.IdCenter, ce.IdNodeIn IdNode, 0 as nodeOrder, Row
    from       center ce
    union all
    select     ce.IdCenter, ce.IdNodeOut IdNode, 1 as nodeOrder, Row
    from       center ce
) x
order by Row, idCenter, nodeOrder;

Using connection table to set the correct order

with rowOrder as
(
    select    ce.IdCenter, ce.IdNodeIn, ce.IdNodeOut,
              case when co.Row is null then 3 else co.Row end Row
    from      center ce
    left join connection co
    on        co.IdCenterFrom = ce.IdCenter
)
select IdCenter, IdNode
from (
       select IdCenter, IdNodeIn IdNode, Row from rowOrder
       union all
       select IdCenter, IdNodeOut IdNode, Row from rowOrder
     ) x
order by Row;

This is the final result on both cases:

| IdCenter | IdNode |
|----------|--------|
| 470      | 1      |
| 470      | 5218   |
| 22       | 5298   |
| 22       | 1      |
| 20       | 5242   |
| 20       | 5284   |

Rextester here


overhead for INSERTS using change tracking and/or change data capture (CDC) We are going to be implementing a mechanism to track changes in our SQL Server 2008 application database.  The programmers are concerned about INSERT speed being negatively impacted, so I have set out to test three cases: no mechanism to track changes, change tracking, and Change Data Capture (CDC).

In all of my tests, there appears to be no significant difference in the speed of INSERTs.  Does this make sense?  I want to make sure I am not overlooking anything before I remove this concern from our list of requirements.

(Note: my testing has shown differences with UPDATES, but we are only concerned about INSERTS)

EDIT: My tests were relatively simple INSERTS into a production-like table, but 250K of them.  The processes ran over 5 minutes, so I'm comfortable it was enough for our environment.

Yes it makes sense, CDC is maintained via a Log Reader, which other than the overhead of the reads on the log drive, and the writes to the capture tables, should not affect the insert rate. They're thinking about (old school :)) tracking via triggers, which can easily impact the insert rate.

But to be safe, make sure your tests run long enough for the overhead of the reader to be actively capturing the inserts.


Postgres : prevent altering join condition in plan I have a modified query from JOB benchmark:

SELECT *
FROM   company_type AS ct,
       info_type AS it,
       movie_companies AS mc,
       movie_info_idx AS mi_idx,
       title AS t
WHERE  ct.ct_kind = 'production companies'
AND    it.it_info = 'top 250 rank'
AND    t.t_production_year >2010
AND    ct.ct_id = mc.mc_company_type_id
AND    t.t_id = mc.mc_movie_id
AND    t.t_id = mi_idx.mii_movie_id
AND    it.it_id = mi_idx.mii_info_type_id;

Now optimizer is introducing condition

mc.mc_movie_id = mi_idx.mii_movie_id

in plan.

Here is the plan

Hash Join  (cost=127405.50..221937.06 rows=150 width=275)
    Hash Cond: (t.t_id = mc.mc_movie_id)
    ->  Seq Scan on title t  (cost=0.00..91054.20 rows=926896 width=94)
            Filter: (t_production_year > 2010)
    ->  Hash  (cost=127398.60..127398.60 rows=552 width=181)
            ->  Hash Join  (cost=37908.32..127398.60 rows=552 width=181)
                    Hash Cond: (mc.mc_movie_id = mi_idx.mii_movie_id)  // HERE
                    ->  Hash Join  (cost=18.93..89399.66 rows=20806 width=114)
                            Hash Cond: (mc.mc_company_type_id = ct.ct_id)
                            ->  Seq Scan on movie_companies mc  (cost=0.00..75323.67 rows=3693067 width=28)
                            ->  Hash  (cost=18.88..18.88 rows=4 width=86)
                                    ->  Seq Scan on company_type ct  (cost=0.00..18.88 rows=4 width=86)
                                            Filter: ((ct_kind)::text = 'production companies'::text)
                    ->  Hash  (cost=37676.66..37676.66 rows=17019 width=67)
                            ->  Hash Join  (cost=2.43..37676.66 rows=17019 width=67)
                                    Hash Cond: (mi_idx.mii_info_type_id = it.it_id)
                                    ->  Seq Scan on movie_info_idx mi_idx  (cost=0.00..30292.03 rows=1923203 width=49)
                                    ->  Hash  (cost=2.41..2.41 rows=1 width=18)
                                            ->  Seq Scan on info_type it  (cost=0.00..2.41 rows=1 width=18)
                                                    Filter: ((it_info)::text = 'top 250 rank'::text)

This condition

Hash Cond: (mc.mc_movie_id = mi_idx.mii_movie_id)

is not present in original sql query.

My question is, how can I prevent postgres from introducing this condition in plan?

The application is written in our lab as a proof of concept which assumes that there are only pk-fk joins in the database that's why I need it this way. The input to the application is the query plan.

I know it's better this way but I'm testing an application which only supports pk-fk joins. None of mc_movie_id or mii_movie_id is a pk.

Note: Using explicit JOIN syntax is not helping - the same plan is obtained.

Workarounds by changing the query

By changing one part to be a sub query and using OFFSET 0

SELECT *
FROM   company_type AS ct,
       movie_companies AS mc, (
           SELECT t_id
           FROM   info_type AS it,
                  movie_info_idx AS mi_idx,
                  title AS t
           WHERE  it.it_info = 'top 250 rank'
           AND    t.t_id = mi_idx.mii_movie_id
           AND    it.it_id = mi_idx.mii_info_type_id
           AND    t.t_production_year >2010 OFFSET 0
           ) AS var
WHERE  var.t_id = mc.mc_movie_id
AND    ct.ct_kind = 'production companies'
AND    ct.ct_id = mc.mc_company_type_id;

OFFSET 0 won't allow optimizer to pull subquery up in plan tree.
One more workaround is to change query such that joins in query are in
order which we want and setting join_collapse_limit = 1 in PostgreSQL
configuration file which essentially tells optimizer not to change join
order. More about join_collapse_limit here
Note : Explicit JOIN syntax should be used

So new query with explicit join order will look like this

SELECT *
FROM   title AS t
JOIN   movie_info_idx AS mi_idx ON t.t_id = mi_idx.mii_movie_id
JOIN   info_type AS it ON it.it_id = mi_idx.mii_info_type_id
JOIN   movie_companies AS mc ON t.t_id = mc.mc_movie_id
JOIN   company_type AS ct ON ct.ct_id = mc.mc_company_type_id
WHERE  ct.ct_kind = 'production companies'
AND    it.it_info = 'top 250 rank'
AND    mc.mc_note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
AND    (
     mc.mc_note LIKE '%(co-production)%'
  OR mc.mc_note LIKE '%(presents)%');

Corresponding plan

Hash Join  (cost=331522.33..438739.98 rows=1 width=281)
  Hash Cond: (mc.mc_company_type_id = ct.ct_id)
  ->  Hash Join  (cost=331503.40..438720.17 rows=234 width=195)
        Hash Cond: (mc.mc_movie_id = t.t_id)
        ->  Seq Scan on movie_companies mc  (cost=0.00..107038.63 rows=46879 width=34)
              Filter: ((mc_note !~~ '%(as Metro-Goldwyn-Mayer Pictures)%'::text) AND ((mc_note ~~ '%(co-production)%'::text) OR (mc_note ~~ '%(presents)%'::text)))
        ->  Hash  (cost=331290.67..331290.67 rows=17019 width=161)
              ->  Hash Join  (cost=174945.39..331290.67 rows=17019 width=161)
                    Hash Cond: (mi_idx.mii_info_type_id = it.it_id)
                    ->  Hash Join  (cost=174942.96..323906.04 rows=1923203 width=143)
                          Hash Cond: (mi_idx.mii_movie_id = t.t_id)
                          ->  Seq Scan on movie_info_idx mi_idx  (cost=0.00..30292.03 rows=1923203 width=49)
                          ->  Hash  (cost=82545.76..82545.76 rows=3403376 width=94)
                                ->  Seq Scan on title t  (cost=0.00..82545.76 rows=3403376 width=94)
                    ->  Hash  (cost=2.41..2.41 rows=1 width=18)
                          ->  Seq Scan on info_type it  (cost=0.00..2.41 rows=1 width=18)
                                Filter: ((it_info)::text = 'top 250 rank'::text)
  ->  Hash  (cost=18.88..18.88 rows=4 width=86)
        ->  Seq Scan on company_type ct  (cost=0.00..18.88 rows=4 width=86)
              Filter: ((ct_kind)::text = 'production companies'::text)


Multi-Subnet Network Setup We currently have a 4 Node cluster used for our Availability Groups.  2 nodes in local data center and 2 nodes in remote data center.

Currently the 2 nodes local are Synchronous and the 2 remote nodes are ASynchronous.

They are setup in a multi-subnet network.

We are wanting to make one of the remote nodes Synchronous so that we can manually failover to the remote data center to run production traffic.

Our testing turned up a network issue that we did not think about.

We use DNS aliases to connect to the listener. (ex. product.company.com) that resolves to the primary through the listener IP address. When we failover manually to the remote node the listener IP stays in the local subnet IP.

My question is how can we configure our DNS Alias to automatically resolve to the remote listener to hit the remote as primary with either CNAME or other DNS options.

Below is a crude diagram I tried to work up.

how can we configure our DNS Alias to automatically resolve to the remote listener to hit the remote as primary with either CNAME or other DNS options

So you don't want to configure applications to connect to the AG Listener's Hostname?

If you use a CNAME pointing to the Listener hostname, it should always return both IP addresses, and clients will handle picking between the IP addresses.


How to add generated column to db2 table? I've got a table with the following:
Columns:
---
URL [nullable, e.g. "https://dba.stackexchange.com/"]
APPFK [nullable, e.g. 654654]
LINKTYPE ["Page", "Link", or "App"]

Check Constraints:
---
(URL IS NULL AND LINKTYPE <> "Link") OR (URL IS NOT NULL AND LINKTYPE = "Link")
(APPFK IS NULL AND LINKTYPE <> "App") OR (APPFK IS NOT NULL AND LINKTYPE = "App")

Since LinkType is calculable based on the nullability of URL and APPFK, I figured this was a perfect opportunity for a computed/generated/virtual column.
So, I tried to run:
alter table MYLIB.MYTABLE
add column testcol VARCHAR(4) generated always as
(
    CASE
        WHEN URL IS NOT NULL THEN 'LINK'
        WHEN APPFK IS NOT NULL THEN 'APP'
        ELSE 'PAGE'
    END
)

Annnd DB2 spat this back at me:

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token ( was not valid. Valid tokens: IDENTITY. Cause . . . . . :   A syntax error was detected at token (.  Token ( is not a valid token.  A partial list of valid tokens is IDENTITY.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token (. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

I tried running the first example in this DB2 documentation, but it gave me the same error.
What's going wrong, here? Am I doing something wrong? Is the documentation wrong? Is it just because our DB2 is outdated (we're running Version 7, Revision 1), and if so is there a workaround?

Db2 versions for different platforms tend to have different features and statement syntax details, so you should always consult documentation for your exact database version and platform. If you do so, you will see that you can only use a limited selection of expressions for generated columns -- either an identity or a row change timestamp.
Instead of a generated column I suggest you simply create a view with your CASE expression for an extra column.


How does Apache Cassandra handle dynamic column set conflicts? I know that each column in Apache Cassandra has a timestamp attached and that read conflicts for a single column is resolved deterministically by looking at timestamp or by comparing the value.

Let's say I add a column to a dynamic column set. I write this column to a single node. I then later add another column to the same column store, but this time to another node. How does Apache Cassandra merge these two? Will both columns exist after the merge?

The best way to think of a cassandra database is not as a set of databases on different nodes, but as one single database. Adding the column to the first node adds it to all other nodes. The actual number of times your data is written is determined by your replication strategy and replication factor, but each copy of the data will be the same.

Thus, if you tell the first node about a new column, the second node will automatically understand it and be able to access that data. If you tell the second node about different data in the same column, it will either overwrite the old data or add new data, depending on whether the row you're writing into already has data in it.

If you're adding a new column to the second node, then all the data in that column and the data in the first column will simultaneously exist and can be queried through either/any node.


Cannot connect to database hosted at CentOS VM through SQL Developer on Windows I've installed Oracle 12c smoothly today and was able to start lsnrctl and database.
SQL*Plus connection is okay inside VM.
I've set the IP address to be static @ 192.168.0.20 (CentOS VM).

I am able to ping the VM address from Windows but I cannot connect to the database through SQL Developer inside Windows. This is the error I got:

  The network adapter is unable to establish the connection

I suspect it may be a firewall problem? But I have opened port 1521 using firewall-cmd.

try to setting listener, for example

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

tnsname

DBORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 2001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dboracle)
    )
  )


SQL Server log for revoke statements I am trying to figure if SQL Server has a feature to log when a user fires statements as revoke, delete from, etc.

It would be great to see which user has fired the command, date and time, and which command has been run.

Is there a way to accomplish this in SQL Server 2008?

I have seen this but it is not what I am looking for.

SQL server does not trace revoke or deletes by default. In order to trace those activities, you need to run a server side trace (or XEvents as Aaron pointed out).

Below trace will help you to find command (Text), start date, end date, login, database (I have filtered it for test_kin), and many other fields.

Adjust the filters as per your need and environment. Also, specify how big the trace file would be through @maxfilesize.

/****************************************************/
/* Created by: SQL Server 2014 Profiler          */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 4, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 7, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 26, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 41, @on
exec sp_trace_setevent @TraceID, 41, 48, @on
exec sp_trace_setevent @TraceID, 41, 50, @on
exec sp_trace_setevent @TraceID, 41, 64, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 9, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 4, @on
exec sp_trace_setevent @TraceID, 40, 5, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 7, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 26, @on
exec sp_trace_setevent @TraceID, 40, 29, @on
exec sp_trace_setevent @TraceID, 40, 30, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
exec sp_trace_setevent @TraceID, 40, 41, @on
exec sp_trace_setevent @TraceID, 40, 49, @on
exec sp_trace_setevent @TraceID, 40, 50, @on
exec sp_trace_setevent @TraceID, 40, 51, @on
exec sp_trace_setevent @TraceID, 40, 55, @on
exec sp_trace_setevent @TraceID, 40, 60, @on
exec sp_trace_setevent @TraceID, 40, 61, @on
exec sp_trace_setevent @TraceID, 40, 64, @on
exec sp_trace_setevent @TraceID, 40, 66, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'test_kin'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Below is the screen shot :


Speed up large clustered index creation? I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB.

And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit windows server 2008 R2 enterprise.

I run

create unique clustered index MyTable_IXC on tblFactFoo(barKey) on [PRIMARY]

But it took more than 6 hours (actually, it reported an error of duplicated key after 6 hours).

When running it, the cpu was less than 10%, and disk IO was less than 20M/s, normally about 15M/s, I wonder how to improve the performance of creating a clustered index with such powerful hardware.

You'll need to look at the wait type when the query is running.  Odds are you need faster disks as building an index on a table that large is going to cause MASSIVE amounts of reads and writes.

In a nutshell you'll need to read the 120 Gig table, sorting it based on the clustering key (which is going to cause a ton of spill to tempdb writing probably 100 Gigs to tempdb), then write the clustered index to the database which causes 120 Gigs of writes.

On top of this any non-clustered indexes on the table will need to be rebuilt at the same time so those all need to be read and rewritten as well (along with all the memory spill that goes with rebuilding the non-clustered indexes).

You can speed things up by disabling the non-clusterd indexes, then manually building them after the clustered index has been rebuilt.  You'll want to make sure that end users aren't able to touch the system while the non-clustered indexes are being built as their query performances will be awful until the non-clustered indexes are created.

Assuming Enterprise Edition you can create the commonly used indexes then let the users back in, then create the other indexes online so that users an work.

No matter what you'll be looking at a REALLY long maintenance window where you'll be slamming the disks as hard as they can go.


Role-Schema Permission issue (Management studio) When designing a new database I organized the table under different schemas. I've also created a new role that should have select/insert/update/delete rights on one schema and only select on another one. So, using management studio, I went in the first schema properties window, under the permission section I added that role and granted it select/insert/update/delete rights.

Problem is, the next time I went in schema properties window, I did not see the role and the rights I had just assigned. Did it worked? How can I verify?

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

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

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

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

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

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


Help needed with SQL Server query: Accessing columns not in GROUP BY clause I have a table whose columns are as follows:

CREATE TABLE Tests (
  TestDate date,
  EntityID int)

Using a stored procedure, I want to find the ‘academic year’ of pairs of tests that happened in the same ‘academic year’, where ‘academic year’ is defined as year part of the test date after a fixed number of months (@AcYearOff, value is typically equal to 7) is subtracted. I do this using the following code:

CREATE TABLE #tmpTable (
  vYear char(4),
  vDate char(8))

INSERT # tmpTable (vYear, vDate)
SELECT DATEPART(yyyy, dateadd(MONTH, @AcYearOff, TestDate)), format(TestDate, 'MMM yyyy')
FROM Tests
WHERE Tests.EntityID = @ EntityID

SELECT vYear
FROM # tmpTable
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear

The above code functions exactly as expected. I now want to add to this query a string that shows the actual month and year in which the two tests were taken. I added the column vDate to the temporary table for this purpose, but I cannot figure out how to combine the two vDate values and return them as a string.

To be clear, here is some example data from the Tests table:

2018-08-12, 21
2018-09-04, 22
2019-04-17, 21
2019-08-03, 21

and the stored procedure should return:

‘2018’, ‘Aug 2018 and Apr 2019’

I look forward to seeing your suggestions. Thank you.

In SQL Server 2017 or Azure SQL Database:

SELECT vYear, STRING_AGG(vDate, ' and ')
FROM #tmpTable
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear;

In SQL Server 2016 or lower:

SELECT vYear, STUFF((SELECT ' and ' + vDate
  FROM #tmpTable
  WHERE vYear = t.vYear
  ORDER BY vDate
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,5,'')
FROM #tmpTable AS t
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear;


Much slower SQL application performance after upgrading from Windows Server 2008 R2 to Windows Server 2012 R2 Has anyone seen much slower SQL Server application performance when upgrading from Windows Server 2008 R2 to Windows Server 2012 R2?

We recently did a side-by-side upgrade of an important server with this newer operating system, SQL Server 2012 to SQL Server 2014 SP1, Citrix XenServer to vmWare, and an older Nimble SAN to a newer Nimble SAN with a lot more SSD, but one Delphi XE application went from running a process in 2 minutes to about 42 minutes!

We saw similar performance issues with our PowerBuilder application on Windows 8 and Windows Server 2012 / 2012 R2 and changing from the SQLOLEDB driver to the SQL Server Native Client driver SQLNCLI helped a lot. In this case, we tested the Delphi application with the latest SQL Server Native Client 2012 SQLNCLI11.1 and it didn't help much at all. I read at https://msdn.microsoft.com/en-us/library/cc280510.aspx that Microsoft recommends using the new ODBC Driver 11 for SQL Server - Windows, so that's something else we can try. This really seems like a driver-related issue.

I really didn't find much on the web about similar problems except the Balanced Power savings option as Glenn Berry describes at https://sqlserverperformance.wordpress.com/2010/09/28/windows-power-plans-and-cpu-performance/. I verified that we are using the High Performance setting and confirmed this with the CPU-Z utility.

I have heard of some people experiencing regression queries with the new SQL Server 2014 Cardinality Estimator (CE), but when I changed the database compatibility mode from 2014 (120) back to 2012 (110), it didn't help. Our testing also found that performance was fine on Windows Server 2008 R2 with SQL Server 2014 SP1 and the 2014 (120) compatibility level.

This application is unfortunately using a lot of client-side cursors to query the data one row at a time with many round trips to the server, and the developer has rewritten much of it in a set-based manner with stored procedures which performs much better, but the new approach still has to go through additional testing and QA before we roll it out. I am also concerned about other applications that may suffer from similar problems.

In the meantime we rolled back to the old Windows Server 2008 R2 server with an in-place upgrade to SQL Server 2014 SP1. We will do more comparison testing in a test environment.

Since this may affect other applications, I may have to open a Microsoft ticket to find out what the root cause is, but I wanted to check here first.

Originally self-answered in comments:

After doing a lot of testing, this issue appears to occur when crossing the temporary network bridge from our new environment over to our old environment. Although we haven't figured out the root cause at the network layer, we have decided to leave the centralized server on our old environment and move it last during our infrastructure migration.

We kept seeing a lot of ASYNC_NETWORK_IO wait types with the queries. After doing a lot of testing with different configurations and opening tickets with Cisco and vmWare, vmWare asked us to try to disable Receive Side Coalescing (RSC) on hosts.

After making that change, performance was back to normal (actually better on the new equipment, which is what we expected). See the VMware Knowledge Base entry:

After upgrading a virtual machine to hardware version 11, network dependent workloads experience performance degradation (2129176).


How to grant access to IP range in Postgres? I would like to configure my Postgres DB to be accessed by any IP 10.8.101.*. I know I have to create a rule in pg_hba.conf but I'm not sure what it should look like. My LAN IP is currently 10.8.101.128 so I thought this would work:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             10.8.101.0/128         md5

But I'm getting an error could not load pg_hba.conf. Does anyone know how to do this properly?

The /128 in the address is the NUMBER OF BITS to match...  not a range.

So you would want 10.8.101.0/24 (match the first 24 bits).

As an additional example, if you wanted to restrict it to 10.8.101.128 to 10.8.101.255 then you'd do 10.8.101.128/25

Please see the address subsection in the documentation


SQL Case Sensitive Query I have two databases which return different results based on the case of the query.

On the first database I run the query

FROM [Temp].[Team].[CLASS]
  WHERE [TABLE_NAME] like '%Link%'

and on the other database I run this query

FROM [Temp].[Team].[CLASS]
  WHERE [TABLE_NAME] like '%LINK%'

The first query returns nothing but the second query returns what I'd expect it to.

Checking the collation between the databases, I find they are both set to SQL_Latin1_General_CP1_CI_AS so I wouldn't expect them to behave differently.

What other settings do I need to alter to give me case insensitive queries without changing my queries?

You need to check that the collation of the servers and columns match (collation isn't only determined at the database level).

To check the servers:

SELECT SERVERPROPERTY('COLLATION');

To check the columns (a much, much, much more likely culprit for this symptom IMHO):

SELECT collation_name
  FROM [Temp].sys.columns AS c
   INNER JOIN [Temp].sys.objects AS o ON c.[object_id] = o.[object_id]
   INNER JOIN [Temp].sys.schemas AS s ON o.[schema_id] = s.[schema_id]
WHERE UPPER(c.name = N'TABLE_NAME')
  AND UPPER(o.name = N'CLASS')
  AND UPPER(s.name = N'TEAM');

If these don't match, then your choices are:

Make them match. E.g. on the server where the collation is case sensitive, you can make it not case sensitive using:

ALTER TABLE [Team].[CLASS]
  ALTER COLUMN [TABLE_NAME] VARCHAR(32) -- guessing
  COLLATE SQL_Latin1_General_CP1_CI_AS
  NOT NULL; -- also guessing

Use a case-insensitive query, e.g.

WHERE UPPER([TABLE_NAME]) LIKE '%LINK%';

-- or

WHERE [TABLE_NAME] COLLATE SQL_Latin1_General_CP1_CI_AS
  LIKE '%LINK%';

Also be sure to use an N prefix if the column is NVARCHAR.


Finding repeating sets of data by foreign key I have a table, ResultItem:

CREATE TABLE ResultItem (
    IDResultItem INT PRIMARY KEY IDENTITY(1,1),
    ItemValue VARCHAR(20) NOT NULL,
    ResultID INT FOREIGN KEY REFERENCES Result(IDResult)
)

I need to find all the most recent records in Result that have the same group of values in ResultItem in a previous Result, and concatenate the ItemValues in another column; for example:

INSERT INTO ResultItem
    (ItemValue, ResultID)
VALUES
    ('a', 1),
    ('b', 1),
    ('a', 2),
    ('b', 2),
    ('c', 3),
    ('d', 3),
    ('c', 4),
    ('d', 4),
    ('a', 5),
    ('d', 5),
    ('a', 6),
    ('a', 7) ;

the query should return:

| 2 | 'ab' |
| 4 | 'cd' |
| 7 | 'a'  |

as 1 and 2 are the same, 3 and 4 are the same, and 6 and 7 are the same.  5 has not yet been repeated so I do not want it.  How would I go about doing this?

This is a hard one. I'll add a solution that will only work in vNext version, that has the new STRING_AGG() function for string concatenation:

WITH g AS
  ( SELECT
        ResultID,
        Items = STRING_AGG (ItemValue, ',')
                    WITHIN GROUP (ORDER BY ItemValue)
    FROM dbo.ResultItem
    GROUP BY ResultID
  )
SELECT
    ResultId = MAX(ResultId),
    Items
FROM g
GROUP BY Items
HAVING COUNT(*) > 1 ;

The separator (',') should be a character that doesn't appear anywhere in
ItemValue column.

For current and older versions, I've adapted the FOR XML PATH method, found in many articles (see Aaron Bertrand's blog post Grouped Concatenation in SQL Server which also has many other methods to do string concatenation):

WITH g AS
  ( SELECT
        ResultID = r.IDResult,
        Items = STUFF(
                   ( SELECT N',' + ri.ItemValue
                     FROM ResultItem AS ri
                     WHERE ri.ResultID = r.IDResult
                     ORDER BY ri.ItemValue
                     FOR XML PATH(N'')
                   ), 1, 1, N'')
    FROM Result AS r
  )
SELECT
    ResultId = MAX(ResultId),
    Items
FROM g
GROUP BY Items
HAVING COUNT(*) > 1 ;

Tested in rextester.com

See also the answer by Martin Smith from a similar problem: How to find parent rows that have identical sets of child rows?, where he adds an temporary table (appropriately indexed) to improve efficiency of this type of query.


Why is my primary key a separate relation taking up storage? I have a table with a primary key. Postgres automatically created an index on this primary key, and it is separately taking up +34% more storage.

Why does Postgres not simply keep the table sorted by the primary key? It's auto increment so it will always be in order. I tried CLUSTER but that did not decrease the size of this primary key index. Why is a primary key index even needed if the table itself will always be in that order?

In PostgreSQL, the only way to guarantee uniqueness is with a unique index. So a unique index is used to implement all primary key and unique indexes in PostgreSQL.
The extra space that the primary key takes is to be expected.

Also, PostgreSQL does not maintain a certain order in the table. Whenever you update a row, it moves somewhere else, and the free space left by past updates and deletes is reused for future inserts.


How to determine CHECK or NO CHECK for foreign Key using SQL Server System Tables I need to know at a low level whether a foreign key is to be exported as CHECK or NO CHECK but can't find out where to find that information.  I am using

-- using INFORMATION_SCHEMA to enumerate foreign keys
-- for a particular referenced table
SELECT
C.CONSTRAINT_NAME [constraint_name]
,C.TABLE_NAME [referencing_table_name]
,KCU.COLUMN_NAME [referencing_column_name]
,C2.TABLE_NAME [referenced_table_name]
,KCU2.COLUMN_NAME [referenced_column_name]
,RC.DELETE_RULE delete_referential_action_desc
, RC.UPDATE_RULE update_referential_action_desc
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
     ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
        AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
     ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
        AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
     ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA
        AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
     ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
        AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
        AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY' AND  C2.TABLE_NAME = 'SalesOrderHeader'

with the code coming from https://www.mssqltips.com/sqlservertip/4753/list-dependencies-for-sql-server-foreign-keys/

However I am unable to determine which field will tell me if a FK constraint should be CHECK or NO CHECK.  How can I determine this, thanks.

You will need to query "sys.foreign_keys" system table.
Column "is_disabled" will provide you this info.
As far as I know this information is not available in INFORMATION SCHEMA.

You can use following query to get similar results:

SELECT
FK.name [constraint_name]
,T.name [referencing_table_name]
,TabC.name [referencing_column_name]
,RefT.name [referenced_table_name]
,RefC.name [referenced_column_name]
,FK.delete_referential_action_desc delete_referential_action_desc
,FK.update_referential_action_desc update_referential_action_desc
,FK.is_disabled
,FK.is_not_trusted
FROM   sys.foreign_keys AS FK
   INNER JOIN sys.foreign_key_columns FKC
     ON FK.object_id = FKC.constraint_object_id
   INNER JOIN sys.tables AS T
     ON T.object_id = FK.parent_object_id
   INNER JOIN sys.columns AS TabC
     ON TabC.column_id = FKC.constraint_column_id
     AND TabC.object_id = FKC.parent_object_id
   INNER JOIN sys.tables AS RefT
     ON RefT.object_id = FK.referenced_object_id
   INNER JOIN sys.columns AS RefC
     ON RefC.column_id = FKC.referenced_column_id
     AND RefC.object_id = FKC.referenced_object_id


Backup of FileGroups Including Primary I want to back up my DB. I have some huge tables in some specific FileGroups which I want to exclude them to be getting backup.
I have tried this script:

BACKUP DATABASE TestDB
   FILEGROUP = 'PRIMARY',
   FILEGROUP = 'Temp_FG',
   FILEGROUP = 'Index_FG'
TO DISK = 'E:\TestBackup\Test.bak';
GO

But I am getting this error:

The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.

What can I do to get the backup including only these 3 FileGroups or excluding the 'Dump_FG'?

'Dump_FG' FileGroup is not ReadOnly.

That is just the way that SQL Server work. If you were able to exclude read-write filegroups in simple recovery, then you wouldn't be able to restore them. A restore need log backups to apply modifications to the data you just restored. Since you can't do log backup in simple mode, then those filegroups in your backup would be "doomed" - for even inaccessible (I believe that the state is "defunct" in SQL Server). And you cannot have a doomed primary filegroup. If you have data which is irrelevant for you,then consider truncating those table to get rid of the data or set them to read-only and exclude them.


What server configuration and database should I use? I am new to Database's and want to work toward installing a Database in the small company I work at for production and eventually other uses. The company has only 15 employee's and three departments. There will not be more than five computers accessing the database.

Currently the setup is one physical Domain Controller/file server running windows server 2012 r2 standard. This is a small warehouse that has many part numbers with instructions and pictures, all in multiple books. The office uses quick books and spreadsheets.

My goal is to have each department to access each job by sales order which will pull up all the items instructions, procedures and specifications for the job as it pertains to the department and eventually track production and inventory.

I will appreciate any assistance or guidance and I know there is much to learn but I am willing to take my time and build it in a virtual network for testing.

My thoughts on how and what database to use based on a small company and small budget.

Using SQL Server Express because has more storage than Access and would be scalable, secure, and cost effective. Is this correct?
Installing the SQL Server Express on the Domain Controller because it is a small company and would be cost effective and more simple. I am not sure if this is correct and if this is not recommended can I enable Hyper V on the Domain Controller and install server 2012 again using the same product key without having licensing issues in order to run the server for the database.

If there is more information needed then I will give it and I appreciate any advice and guidance.

I am a student while working and just started Database Design class and have a long way to go but I learn better when I am trying to build something in a real world application. I am willing to make mistakes it the virtual network in order to learn but not in my companies network. I believe I need a Database mentor.

Thanks in advance

This site, is a question and answer site, not a mentoring site.

You are already taking a Database Design class, which is much closer to the mentoring that you are seeking.  Also, you must have instruction materials that will explain at least the basics.

Questions are welcome, but you will get better results by:

Working on the problem yourself first.
Describe briefly your overall project.
Focus on one or two points, not a sweeping discussion of your project.
Clearly describing the problem that you are currently trying to resolve and what you have already tried.

General questions about databases, costs, et cetera are things that you need to study up on in advance. You must know the basics or your questions will get little useful response.

Search DBA Stackexchange and other sites to see what answers are already available to you.  You often just need to search.

Specific responses:

SQL Server Express 2014 can be a fine, fully featured database server.  It does not have all the auxiliary tools that the more expensive editions.  There are limitations: only uses one CPU, no single database can be bigger than 10 GB, the use of memory is limited, and so forth.
It is better to keep the domain controller and the SQL Server on separate machines, even if virtual, because of the increased exposure to security problems.

Your goal to create a simple system for a small group of users is a fine place to start.  You will learn a lot in the process that will help prepare you for bigger tasks.


Can't connect to MySQL from remote computer A MySQL Server is newly installed on linux computer LPNBUNTU.
On local linux I can get a DB-connection and I was finally able to set a root password.
When connected I get
MYSQL>status
root@localhost

I rather think MySQL should be aware on that computer it is installed. Thus I guess the user should be root@LPNBUNTU. Just an idea.
I open the port 3306 on linux
sudo ufw allow 3306
My problem is, I can't connect from my Window-10 computer via mysqlsh. I think should doing this like
mySQL\SQL>
 \connect --mysql root@lpnbuntu:3306
Creating a Classic session to 'root@lpnbuntu:3306'
Please provide the password for 'root@lpnbuntu:3306': *********
after some timeout I get
MySQL Error 2003 (HY000): Can't connect to MySQL server on 'lpnbuntu' (10061)

I guess the MYSQLServer should be made aware, that it on computer lpnbuntu, and should react to all connection request addresses from any computer on port lpnbuntu:3306.
How can I make this happen?

I believe this can be resolved by setting the "bind-address " to 0.0.0.0 or the specific IP address of your win-10 computer.
this is done by accessing the MySQL config file and physically typing in the desired ip.
the file path is usually:
/etc/mysql/my.cnf

0.0.0.0 will allow access from all networks.


Index not used with many WHERE LOWER(column) IN (...) conditions, works fine with just a few I have the following table:

CREATE TABLE public.devices
(
  id integer NOT NULL DEFAULT nextval('devices_id_seq'::regclass),
  token text NOT NULL,
  ...some other columns...,
  CONSTRAINT devices_pkey PRIMARY KEY (id, token)
)
CREATE INDEX IF NOT EXISTS idx_devices_lower_token ON devices (lower(token));

If I run the following query, everything is fine, the query runs instantly, and the index is used:

SELECT *
FROM "devices"
WHERE (id > '0')
AND ((lower(token) IN ('92134f4bb6a2263fd044a5a04440bc1a','cbf7d4a094bd266fd3f1b7ef4196172a', 'aa0f7650cf28010b5c01b77ae206f7fb')))
ORDER BY id LIMIT 1000;

"Limit  (cost=58116.96..58119.46 rows=1000 width=345)"
"  ->  Sort  (cost=58116.96..58189.47 rows=29005 width=345)"
"        Sort Key: id"
"        ->  Bitmap Heap Scan on devices  (cost=1061.91..56526.65 rows=29005 width=345)"
"              Recheck Cond: (lower(token) = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a}'::text[]))"
"              Filter: (id > 0)"
"              ->  Bitmap Index Scan on idx_devices_lower_token  (cost=0.00..1054.65 rows=29005 width=0)"
"                    Index Cond: (lower(token) = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a}'::text[]))"

However, if I add a lot of conditions (e.g. 1k) for the (lower(token) IN (...)), the index will no longer be used anymore and the query will be VERY slow:

SELECT *
FROM "devices"
WHERE (id > '0')
AND ((lower(token) IN ('92134f4bb6a2263fd044a5a04440bc1a','cbf7d4a094bd266fd3f1b7ef4196172a', 'aa0f7650cf28010b5c01b77ae206f7fb', ... up to 1k tokens ...)))
ORDER BY id
LIMIT 1000;

"Limit  (cost=3900016.07..3900018.57 rows=1000 width=345)"
"  ->  Sort  (cost=3900016.07..3907219.10 rows=2881214 width=345)"
"        Sort Key: id"
"        ->  Seq Scan on devices  (cost=0.00..3742042.21 rows=2881214 width=345)"
"              Filter: ((id > 0) AND (lower(token) = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a,6116af468e0017ffe5c49309869be773,4a5ebc1cec6c452e74fbf1a4e22fe804,0a9981eaf530d22ad9fe12a73a0b486c,61eb008e86a5afeaa9edd3e56ba01e0b,a31775a866571d9c2ecadc6b2a8127a3,dfcb4bc34771883bb2a54ee44299b058,7c93581a24053021bd41b7cc4654bf9e,43182b0010cea255fef1d10849858d7c,c3f6503eafeaa1d25a6f5672310ef4e5,672641b1fcd23f67deab34deaf28f1d8,cb52e085618ecbdf6eef1669b7b38008,335626a32455e61e1b1de16550e43af0, (...)"

Ok so, it must not like many conditions, right?

Well, I tried replicating the same scenario but without using lower(token), and it works correctly:

create index IF NOT EXISTS idx_devices_token on devices (token);

SELECT *
FROM "devices"
WHERE (id > '0')
AND ((token IN ('92134f4bb6a2263fd044a5a04440bc1a','cbf7d4a094bd266fd3f1b7ef4196172a', 'aa0f7650cf28010b5c01b77ae206f7fb', ... 1k tokens ...)))
ORDER BY id
LIMIT 1000;

"Limit  (cost=25.70..25.71 rows=3 width=345)"
"  ->  Sort  (cost=25.70..25.71 rows=3 width=345)"
"        Sort Key: id"
"        ->  Bitmap Heap Scan on devices  (cost=13.69..25.68 rows=3 width=345)"
"              Recheck Cond: (token = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a,aa0f7650cf28010b5c01b77ae206f7fb}'::text[]))"
"              Filter: (id > 0)"
"              ->  Bitmap Index Scan on idx_devices_token  (cost=0.00..13.69 rows=3 width=0)"
"                    Index Cond: (token = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a,aa0f7650cf28010b5c01b77ae206f7fb}'::text[]))"

So this seems to only happen if I use an index with lower and many WHERE IN conditions.

I have no idea what is going on here, any ideas?

I am using PostgreSQL 9.5

Don't do that. Use citext

CREATE EXTENSION citext;
CREATE TABLE public.devices
(
  id integer NOT NULL DEFAULT nextval('devices_id_seq'::regclass),
  token citext NOT NULL,
  ...some other columns...,
  CONSTRAINT devices_pkey PRIMARY KEY (id, token)
)
CREATE INDEX IF NOT EXISTS idx_devices_lower_token ON devices(token);

Then

AND token IN ('92134f4bb6a2263fd044a5a04440bc1a','cbf7d4a094bd266fd3f1b7ef4196172a', 'aa0f7650cf28010b5c01b77ae206f7fb', ... up to 1k tokens ...)))

And if you're talking about a thousand tokens you should probably use a temp table..

CREATE TEMP TABLE query_params ( token citext );
INSERT INTO query_params(token) VALUES ...

then

SELECT *
FROM devices
WHERE (id > '0')
AND EXISTS (
  SELECT 1
  FROM query_params
  WHERE query_params.token = devices.token
)
ORDER BY id
LIMIT 1000;

You can even index query_params.token


Error running sp_BlitzCache (v5.2) I've installed sp_BlitzCache (and others) from the First Responder Kit, and I am receiving the following error:

EXEC sp_BlitzCache;

  Msg 208, Level 16, State 0, Procedure sp_BlitzCache, Line 3878 [Batch Start Line 0]

  Invalid object name '#trace_flags'.

All of the other scripts appear to be working as expected, but I am not sure if I've encountered a defect, or if it has something to do with my use and/or configuration.

Also worth noting, the procedure does return 10 results, but then fails with the above error.

I've done some searching online and through the stored procedure itself. While I am confused that the #trace_flags temp table does not appear to have been created like the others, I cannot find code on GitHub that seems to indicate that it ever had.. So since I imagine that it has been working for others it leads me to believe that I am assuming the wrong things and/or running it incorrectly.

Server: Microsoft SQL Server Standard Edition
Version: 10.50.6220.0 (SQL Server 2008 R2)

Thank you in advance! Both for the assistance, and for any of you who have been involved in developing the First Responder Kit.

That was fixed. Upgrade to the current version, v5.3 via the Github repository or our site. (Disclaimer: I'm Brent Ozar.)


SQL server Installation - SQL server agent is enabled by default? When SQL server is installed on a machine, is the SQL Server Agent enabled and running by default, or does it need starting manually ?

This is an installation option. In the installer GUI or command line you can leave it as enabled or disabled and automatic or manual. By default in most versions of SQL it is not enabled and started. I always change it when installing - or forget and do it shortly thereafter.


How do I use the input from a \prompt in a conditional (\if)? Let's say I get value from a prompt?

\prompt 'Write [Yes] or [No]\n' store

Which does this,

Write [Yes] or [No]
Yes

I can verify I have that works,

\echo :store
Yes

But, I can't get it to work with \if

\if :store='Yes'
unrecognized value "Yes=Yes" for "\if expression": Boolean expected

\prompt's doc don't say much on this

  \prompt [ text ] name Prompts the user to supply text, which is assigned to the variable name. An optional prompt string, text, can be specified. (For multiword prompts, surround the text with single quotes.)

  By default, \prompt uses the terminal for input and output. However, if the -f command line switch was used, \prompt uses standard input and standard output.

You can accomplish this using using \gset to coerce the variable into a boolean which \if can use,

$ cat if_prompt.sql
\prompt 'enter abc here: ' x
select ('abc' = :'x') as is_abc \gset
\if :is_abc
  \echo you can read
\else
  \echo you are illiterate or impertinent
\endif

Testing it,

$ psql test -f if_prompt.sql
enter abc here: abc
you can read

$ psql test -f if_prompt.sql
enter abc here: 123
you are illiterate or impertinent