are overlapping multi column statistics redundant? I have run a database engine tuning advisor session, DTA, to optimize my database.

The DTA has produced some statistics recommendations, however, I noticed some are contained in others for example on my table ProductIcon below:

    CREATE STATISTICS [_dta_stat_172123954_1_2_3_4_5_12]
ON [dbo].[ProductIcon]
([ClassID], [SegmentID], [GroupID], [Tier1], [LanguageID], [Active])
    go

    CREATE STATISTICS [_dta_stat_172123954_11_1_2_3_4_5_12]
ON [dbo].[ProductIcon]
([IconTypeID], [ClassID], [SegmentID], [GroupID], [Tier1], [LanguageID], [Active])
    go

this is the table definition:

    IF OBJECT_ID('[dbo].[ProductIcon]') IS NOT NULL
    DROP TABLE [dbo].[ProductIcon]
    GO
    CREATE TABLE [dbo].[ProductIcon] (
    [ClassID]      INT                              NOT NULL,
    [SegmentID]    INT                              NOT NULL,
    [GroupID]      INT                              NOT NULL,
    [Tier1]        VARCHAR(10)                      NOT NULL,
    [LanguageID]   SMALLINT                         NOT NULL  CONSTRAINT [DF_ProductIcon_LanguageID] DEFAULT ((1)),
    [Image]        VARCHAR(50)                      NOT NULL  CONSTRAINT [DF_ProductIcon_Image] DEFAULT (''),
    [Descr]        VARCHAR(100)                     NOT NULL  CONSTRAINT [DF_ProductIcon_Descr] DEFAULT (''),
    [SortOrder]    INT                              NOT NULL  CONSTRAINT [DF__ProductIc__SortO__6A358A53] DEFAULT ((1)),
    [URL]          NVARCHAR(512)                        NULL,
    [LinkJSClick]  NVARCHAR(512)                        NULL,
    [IconTypeID]   INT                                  NULL,
    [Active]       BIT                              NOT NULL  CONSTRAINT [DF_ProductIcon_Active] DEFAULT ((1)),
    [Tier2]        VARCHAR(10)                      NOT NULL  CONSTRAINT [DF_ProductIcon_Tier2] DEFAULT (''),
    CONSTRAINT   [PK_ProductIcon]
PRIMARY KEY CLUSTERED
([ClassID] asc, [SegmentID] asc, [GroupID] asc,
 [Tier1] asc, [LanguageID] asc, [SortOrder] asc, [Tier2] asc))

Question:

If I knew exactly how the stats would be used I would probably be able to decide whether or not I should keep both or one of them.

Is there any way I can have this vision so that I can make my decision as to which one(s) of them I should keep?

we are still on sql 2005 for this one.

This table currently does not have any other index , however the following index is appointed as missing:

CREATE NONCLUSTERED INDEX IDX_IconTypeID_Active_INC_1_2_3_4_5_6
ON [dbo].[ProductIcon] ([IconTypeID], [Active])
INCLUDE (ClassID,SegmentID,GroupID,Tier1,Descr,Tier2)

They're not redundant, no.

Each statistics object includes a histogram of the values stored in the first listed column. For the remaining columns, only density information is kept at each level (no histogram).

For example, the proposed statistic on (ClassID, SegmentID, GroupID, Tier1, LanguageID, Active) would create:

A histogram for ClassID values
A density number for:

ClassID, SegmentID
ClassID, SegmentID, GroupID
ClassID, SegmentID, GroupID, Tier1
ClassID, SegmentID, GroupID, Tier1, LanguageID
ClassID, SegmentID, GroupID, Tier1, LanguageID, Active

These densities can be useful when there is correlation between columns, but only for equality predicates (and you will need SQL Server 2016 to get this benefit when using the 'new' cardinality estimator. The original cardinality estimator used this density information by default).

You can see the histogram and density information using DBCC SHOW_STATISTICS.

Note the order of columns is important. This statistic could provide average density information for equality predicates on (ClassID, SegmentID, and GroupID) but not (ClassID, GroupID).

Statistics are quite lightweight as far as storage is concerned, but you need to be aware of the potential cost of maintaining them, especially on large tables where a high sampling rate is necessary to achieve acceptable quality.

If DTA recommends a statistic, that is a good indication that the statistic would be useful for one or more queries in your workload. I don't recall if DTA reports on the queries that contributed to a recommendation, but if not, for the example above, you would be looking for queries with equality predicates on all the columns listed. Compare the quality of the plan and cardinality estimates with and without the new statistic if you really want to dive into the detail.

The missing index would create an associated statistic on (IconTypeID, Active), with a histogram on IconTypeID, and average density information for the combination (IconTypeID, Active).

See the following for more information:

Statistics
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005


SQL Arrays are returned as strings in PHP I have a column with type Array, I need to get all the elements in the array, I tried:

SELECT managers_ids::INTEGER[] FROM teams WHERE id = 14

the result is  '{8,9,10}'. How can I iterate on the results when it's returned as a string?

Any ideas?

Using PHP with SQL Arrays

I would highly suggest never using PHP. However, when you can't avoid it you may find this post useful on reading PostgreSQL arrays as PHP Arrays. People have been complaining about this for 15 years now. PHP doesn't migrate SQL arrays into native PHP array structures.

Getting around the problem with JSON

The new method of working around PHP's long-standing inadequacies is to use JSON to move the array.

SELECT array_to_json(pg_array_result) AS new_name FROM tbl1;
$array = json_decode($returned_field);

Attribution: shamefully stolen from michaelbn's answer on StackExchange..

Getting around the problem with unnest

You have another option to use unnest and return a result set which PHP can handle.

SELECT * FROM unnest(ARRAY[1,2,3]) AS t(x);
 x
---
 1
 2
 3
(3 rows)


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

I'm trying to drop a certificate I've created but i get the following error

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

Using this query I've identified the entities in question

SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
       so.[name] AS [ObjectName],
       so.[type_desc] AS [ObjectType],
       ---
       scp.crypt_type_desc AS [SignatureType],
       ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName],
       ---
       scp.thumbprint
FROM sys.crypt_properties scp
INNER JOIN sys.objects so
        ON so.[object_id] = scp.[major_id]
LEFT JOIN sys.certificates sc
        ON sc.thumbprint = scp.thumbprint
LEFT JOIN sys.asymmetric_keys sak
        ON sak.thumbprint = scp.thumbprint
WHERE   so.[type] <> 'U'
AND ISNULL(sc.[name], sak.[name]) = 'Certificate_name'
ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];

but when I try to drop the SIGNATUREs with the following statement:

DROP SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY CERTIFICATE Certificate_name

I get them following error.

  A signature by certificate 'Certificate_name' does not exist.

What am I doing wrong?

Thanks in advance.

My bad.

I should be using

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

instead of

DROP SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY certificate_name


Unable to shrink log file due to snapshot replication Our database log (SQL Server 2008 R2) is growing too big, and truncating it via DBCC SHRINKFILE doesn't work (the database is set to simple recovery mode).

A bit of background. I released some changes to our live servers a few days ago, I needed to drop some articles from the snapshot replication which I did, made the changes, and re-added the removed articles.
However, when I tried to reinitialise the subscribers I got a strange error (which I didn't save unfortunately and am not allowed to recreate until the next release window). I set the replication processes running again but it seems to be holding a lock on the log file.

 select log_reuse_wait_desc from sys.databases where name = '<dbname>'

gives a result of 'REPLICATION'.

DBCC OPENTRAN ('<dbname>')

gives

  Transaction information for database '< dbname >'.

  Replicated Transaction Information:
          Oldest distributed LSN     : (0:0:0)
          Oldest non-distributed LSN : (3891297:327:1) DBCC execution completed. If DBCC printed error messages, contact your system
  administrator.

I've done some reading around and I suspect that the way to fix it is to kill off a blocking spid or use sp_repldone somehow, but...

exec distribution.dbo.sp_browsereplcmds @xact_seqno_start = '0x003B6061000001470001', @xact_seqno_end = '0x003B6061000001470001'

returns nothing. I'm not sure that I've converted the lsn correctly.

I'm not the DBA, he is unavoidably out of the country at the moment and getting him working on this would be quite bad. I'd prefer to fix the log growth problem and have him look into why reinitialisation doesn't work when he gets back, but this is a time sensitive problem since we will run out of space sometime on Sunday at the current rate of growth.

I can't do anything too drastic since we have other publications (transactional replication) running on the same server. Any ideas on what I should do would be very appreciated right now.

If you are sure that everything is replicated to all subscribers and you really need to shrink it right now, you can try this.

Execute the following snippet inside your replication database. It will mark all replications as successful and your replication queue will be empty. Be sure that this is really the case! Otherwise you may loose some elements which need to be replicated.

Script:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,
     @numtrans = 0, @time = 0, @reset = 1

Take a backup and tail the log. It should now be empty.


Disabling hash join in a procedure One of my procedures failed because it ran out of temp tablespace. 700 GB was not enough for the procedure. I looked on it, and found it was caused by an inefficient join type, chosen by the optimizer. It works well with nested loops, and is fast enough. So, I disabled the hash and merge joins inside the procedure.
I have a question. If I disabled this hash join, would that change affect other procedures?
Would it affect procedures which calls modified procedure?
And would it affect procedures which are called by the modified procedure?

It depends on how you did it.
With a plain SET, you would change the parameter for the whole session.
With SET LOCAL, you would change the parameter for the complete database transaction.
The best way would be to modify the procedure so that the setting is only active for the duration of the execution of the procedure:
ALTER PROCEDURE procname SET enable_hashjoin = off;

Let me add that this is only a last ditch measure: it would be much better if you could fix the bad row count estimates that lead to the bad choice of join strategy.


Disaster Recovery for PostgreSQL 9.0 We have a number of PostgreSQL 9.0 servers. We use binary replication to have a host standby instance of those. The only problem is that is someone drops the master, with or without intentions, this will cascade to the replicas as well. I'm looking at the possible ways to avoid this. One possible option in seems to be Point in Time Recovery. I'm just wondering what could be a good design for this. Any ideas? Let's assume the master is compromised and we lose everything we have there. How can we avoid losing the replica or at least have a way to bring it back if it's dropped?

First, repeat after me:

  A replica is not a backup

That is your first problem.  Do not use replication as a substitute for backups.  Replication is a useful part of a disaster recovery strategy.  It is not a useful disaster recovery strategy.

For PostgreSQL 9.0.x I would recommend that you take a look at barman, an open source backup and archive manager for PostgreSQL by 2ndQuadrant.  This helps manage some of the details so you don't have to.  (Full disclosure: I am a principal consultant for 2ndQuadrant.)

This being said, barman is only helpful if you are relatively familiar already with the base backup/wal shipping approach (which is what you want so you can roll back human errors like this).  This is necessary for PITR to work.

In this approach you set up two things.  The first is a base backup (see the pg_basebackup documentation for how).  Note you cannot use a logical backup (i.e. from pg_dump for this).  The second is to set up continuous archiving so that you can take your WAL segments and use them for PITR.   The specifics here depend highly on your environment and so you want to take a look at the docs and design something that works for you.  True to the UNIX way, these are largely tools for building your backup and recovery solution.  There isn't much more that can be said about this without something more specific, but start there and build a solution for your business out from it.


How to change MySQL 8.0 data folder. Can I use OneDrive folders? I'm trying to create a DataBase for a very little program that just I and 2 more friends use, so the database will be little.

What I want to do is to share this database with these 2 friends, so I thought about storing the data in a OneDrive shared folder.

At the moment, I'm using a txt file as a "database". Which is placed in a shared OneDrive folder, so when my friends execute the program, it can read the data from there and make it sort of real time "online".

The thing is that I can't find the my.ini file in my

C:\Program Files\MySQL\MySQL Server 8.0\

directory, so I can't change the data folder.

Another trouble I have is that I also don't have the Data folder in that directory, instead I've found it in this one:

C:\Program Files\MySQL\MySQL Workbench 8.0 CE

Is it possible to do what I want to do? And how should I proceed?

Do you think I should be using MySQL 5.x version?

Thanks

Even if you can use OneDrive (or similar product), I would expect it to be a disaster waiting to happen.  Neither MySQL nor XyzDrive is designed to worry about fast, real-time, actions.  So, I would expect any network hiccup, or drive hiccup, or long network delay, etc, to risk a serious "disk" error being returned to MySQL, necessitating a crash.

Also, MySQL is not prepared to have two instances talking to the same files.  Will you really disconnect from one computer before connecting another??  The day you fail to do that is the day the data is corrupted beyond repair.

Even if XyzDrive and MySQL decide to make it a viable feature, performance would probably suck -- somewhere between 10x and 100x slower than a local drive, and somewhat slower than a remote drive.

(If I am wrong, please speak up.)


Check Index Fragementation Query I'm completely stumped.  I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea.

I have a new production SQL Server (Windows Server 2003/SQL Server 2005 SP3)  that I am going to be responsible for.  Wanted to get some basic stats so I got some scripts off of SQLServerPedia to help with that. (Great stuff btw).

Did a check on index fragmentation and ran the following query (took it about 30 min to run)...

SELECT
      db.name AS databaseName
    , SCHEMA_NAME(obj.schema_id) AS schemaName
    , OBJECT_NAME(ps.OBJECT_ID) AS tableName
    , ps.OBJECT_ID AS objectID
    , ps.index_id AS indexID
    , ps.partition_number AS partitionNumber
    , ps.avg_fragmentation_in_percent AS fragmentation
    , ps.page_count
FROM sys.databases db
  INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps
      ON db.database_id = ps.database_id
  INNER JOIN sys.objects obj ON ps.object_id = obj.object_id
WHERE ps.index_id > 0
   AND ps.page_count > 100
   AND ps.avg_fragmentation_in_percent > 30
ORDER BY databaseName, schemaName, tableName
OPTION (MaxDop 1);

This came back with several rows of data with several indexes for several databases.  (I didn't save the result set of the query -- wish I had now).  Long story short I re-ran the query about ten minutes later and it ran in 2 seconds and returned no rows.  Nothing.  I have run 0 queries to manipulate indexes in that timeframe.  No jobs had run.  There is nothing in the SQL Server Log for that time period to indicate anything fishy.  And I double checked and confirmed the query was run both times on the same server.  How could this be?  How could approx. 20 indexes that were over 30% fragmentation with more than 100 page count suddenly be taken care of (or disappear from this result set) without me doing anything (or from what I can tell -- no one else or no other application) doing anything either?

I think you ran the query from 2 different databases (same server, but different databases) and are getting unexpected results due to the INNER JOIN condition.

My suggestion would be to just focus on one database at a time when analyzing indexes.


Sharing Front-End Access Database I have an Access back-end database with tables, queries, reports...and I have an Access front-end database with a data-entry form that will be used by 3 users. Both back- and front-ends are in same folder on a file server accessible to all 3 users.

What is the proper method of sharing my Access front-end database? Is it simply to have each user make a copy of it and put it on their local drive? This is my first time sharing a database, so any input would be greatly appreciated.

Is the front-end locked down (for design) and properly linked to the back-end?  If they are, you will need to go back to your development copy, save it on the end-users' machines individually, and re-link to the databases on the file server.  i have a VBA script that runs when the front-end starts up that checks for the existence of the backend.  if the backend is missing or has been moved, it prompts the user (or dba) for the new location.  I will provide you some more information later this evening if you are interested.  My front-end is packaged as a standalone application that resides on the end-users' computers. The backend resides on their server.  This is much safer and faster than having your users run the front-end from a networked location.

Edit:

I forgot to mention that, after linking, you should "lock-down" the front-end on the users' computers. You don't want them fiddling around with your form design.


How do I find the error at a line number in a Oracle Live SQL script? When someone runs an Oracle Live SQL script, each statement is executed and the final result (or errors) are displayed below each statement. However, line number errors create a bit of an issue:

The line number states is 77, however this is the 157th statement, hence one does not know what line 77 refers to.  Where can a user find a specific line number in Oracle Live SQL?

The way to find errors within a Trigger within Oracle Live SQL is as follows:

Select *
from
     user_errors

This is how Ricardo Araujo debugged his Compound trigger error in statement 22 of his Oracle Live Sql script.


Oracle case insensitive passwords seemingly ignoring SEC_CASE_SENSITIVE_LOGON I'm trying to change the SYS password of an Oracle 11g database to be case-insensitive for legacy application reasons. My research suggests all I need to do is set the SEC_CASE_SENSITIVE_LOGON flag, and change the SYS password so it will become case-insensitive.

I connect via SQL*Plus with the current password, and make these changes:

C:\Users\KWalsh>sqlplus sys/PASSWORD@database3 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 5 11:19:12 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL> alter user sys identified by password;

User altered.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\KWalsh>

Now I try reconnecting with the initial, ALL-CAPS password. I believe it should allow this with case sensitivity disabled, but this happens:

C:\Users\KWalsh>sqlplus sys/PASSWORD@database3 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 5 11:23:07 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

The lowercase password permits access to the database. Any ideas what I'm doing wrong?

Create a password file, set it to insensitive. You should have a password file, even on windows, even if all it does is define who can connect as a sysdba. I just installed a default Standard Edition and a password file was created. THe last time I built a new EE system it was the same way.

Here's the doc for how to set one up if you don't know. Make sure you use the case sensitive switch and turn it off:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba007.htm#ADMIN10241


SQL 2012 Upgrade Advisor cannot detect SQL Server I have an instance of SQL 2005 on a Windows 2003 server that I want to analyze using Upgrade Advisor. I can access SQL Server remotely via SSMS, so I assume necessary ports are open & user privileges enabled. However, Upgrade Advisor gives me the following error:

===================================

Upgrade Advisor could not detect SQL Server components on the server. Please check if current user has the privilege to access target server's registry, and if the Remote Registry service in target server is started.

===================================

Requested registry access is not allowed. (mscorlib)

Program Location:

at System.ThrowHelper.ThrowSecurityException(ExceptionResource resource)
   at Microsoft.Win32.RegistryKey.OpenSubKey(String name, Boolean writable)
   at Microsoft.Win32.RegistryKey.OpenSubKey(String name)
   at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetClusterInfo()
   at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.DetectComponents()
   at Microsoft.SqlServer.UpgradeAdvisor.FormDetectComponents.Detect()

I was going to delete my question but it got voted +1..anyhow
Needed to enable access to remote registry as the error says...

How to: http://www.windowsreference.com/security/how-to-disablerestrict-remote-registry-access-in-windows/


How can I prevent users from overwriting databases when restoring? I don't want users to restore (with overwrite) on some specific databases. How can I inhibit from restoring over these databases?

If what you're asking is how to prevent a login from restoring over an existing database, then take a look at this reference on BOL for RESTORE:

  Permissions

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
   database exists, RESTORE permissions default to members of the
   sysadmin and dbcreator fixed server roles and the owner (dbo) of the
   database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists).

  RESTORE permissions are given to roles in which membership information
  is always readily available to the server. Because fixed database role
  membership can be checked only when the database is accessible and
  undamaged, which is not always the case when RESTORE is executed,
  members of the db_owner fixed database role do not have RESTORE
  permissions.

In your case, it sounds like the database exists and users are restoring over it.  In that case, just reiterating what permissions are required, you will need to ensure that your particular logins aren't sysadmin or dbcreator members along with the owner of the database.

What it boils down to is, if you don't want people to be able to carry out certain operations, don't give them the permissions to do such.


What should be checked after an Oracle migration After a large data migration, what needs to be checked to be confident that the data move has succeeded?

the number of tables and rows in each schema is the same.
the text data in the tables has not been corrupted. This is important if non alphanumeric characters (such as double byte) are used.
the dumps or other files that have been been transferred have the same checksum.

Is there anything else that can be checked?

Are you trying to migrate the data between different versions of Oracle?

Generally, you can also look at your environmental parameter settings (init.ora parms) and your optimizer statistics after doing data migration.
See also Oracle tips for migration click here


Updating Properties of an Excel cell, with Sql statement So far, i have constructed a SSIS Package that creates multiple Worksheets in an Excel Speadsheet and populates it with values (Reports).

Then I can Execute a SQL statement, via SSIS, to selectively update values in the Sheet:

"Update [Sheet1] Set F1 = 'TOTAL:' Where F1 like 'Total:%' "

A next step would be, to Bold the Cells that contains the text "TOTAL:", using SQL.

If possible, I do not want to write an entire macro or start OLE Automation and Install EXCEL on the Server, to just do this.

Is there a way (maybe a syntax change to an Update statement) to accomplish this quickly.

"Update [Sheet1] Set F1.Bold = 'True' Where F1 like 'Total:%' "

Would this be Possibe?

That sounds mighty painful.

Is there any opportunity to use Conditional Formatting (Home, Conditional Formatting, New Rule) in your template Excel document?

Then apply that rule to the whole worksheet/workbook so that when the row with Total moves from row 20 to 25, it correctly enboldens the proper cells?


Table design problem I'm looking for a SQL design solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his data. After that I have to save it to the database. After validation of the content of the data, I have to update the original fields.

Actually each client has a photovoltaic installation. Each installation contains one or more inverters and at each one there is N solar panels that are attached. All of the inverters are attached to an electrical meter.

I choose to save only the difference between the data stored in the db and the modified data. e.g: if the client changes only the brand of his inverter I will not save all the data, but only the modified inverter brand. I then have to validate the brand and to update it in the table Inverters.

My first idea to solve that issue is to work with 2 tables: validation_data and validation_fields. The table validation_fields contains all the fields that can be updated (vaf_field). The table validation_data is a temporary table. It contains the id of the field to update (vad_vaf_id, who is also the foreign key to the table validation_fields) and the value (vad_value). vad_met_id is the foreign key to link to the meter. Nothing special :) Once the data is verified, vad_is_treated will have the value 1. The default is 0.

+-----------------+   +-------------------+
| validation_data |   | validation_fields |
+-----------------+   +-------------------+
| vad_id          |   | vaf_id            |
| vad_met_id      |   | vaf_field         |
| vad_vaf_id      |   +-------------------+
| vad_value       |
| vad_is_treated  |
+-----------------+

So that logic is good, or at least it works fine for me, but only if the client possesses only one inverter. As I said I save only the difference between the existing and modified data. So if the client changes the brand of two of his inverters, in the validation_data table I will have:

+--------+------------+------------+--------------+
| vad_id | vad_met_id | vad_vaf_id |   vad_value  |
+--------+------------+----------- +--------------+
|      1 |     1234   |          2 |        Peter |
|      2 |     1234   |          3 |    SMA 1400S |
|      3 |     1234   |          3 |    SMA 1400S |
+--------+------------+------------+--------------+

Consider that if vad_vaf_id is 2, that is the firstname field, and 3 - the inverter's brand.

So okay, for the first line all is good, the client changed his firstname. But for the second and the third line there is a problem. The client changed the brand of his inverters but for which one? The first and the second inverter, or the first and the third, or the second and the third?

Do you have any idea how I can deal with that?

If your vad_vaf_id does not sufficiently designate the target value, perhaps you should add a new column in your temporary table to provide that information? Something like vad_target?

+-----------------+   +-------------------+
| validation_data |   | validation_fields |
+-----------------+   +-------------------+
| vad_id          |   | vaf_id            |
| vad_met_id      |   | vaf_field         |
| vad_vaf_id      |   +-------------------+
| vad_target      |
| vad_value       |
| vad_is_treated  |
+-----------------+

When user changes his firstname, leave the field NULL. When there are multiple possible targets (i.e. with inverters), use inverter's id.


Error Message while executing command in Arbiter for log rotation While running the command in the shell db.adminCommand( { logRotate : 1 } ) of arbiter it showing an error message as not authorized to run this command but in primary and secondary of replicaset its working. So what's the solution for this version is 3.4.3.

As per MongoDB BOL Here Rotate the log file by issuing the logRotate command from the admin database in a mongo shell:

db.adminCommand( { logRotate : 1 } )

You must issue the logRotate command against the admin database in the form:

{ logRotate: 1 }

Note : Your mongod instance needs to be running with the --logpath [file] option.


Is it better a composite index (item, item_type) or single-column index on item_type_1, item_type_2, ...? We have items of three types to be stored and queried according to some business logic.

create table a_table
(
   item_a   varchar2(30),
   item_b   varchar2(16),
   item_c   varchar2(2),
   -- other columns
);

And the indexes

create unique index idx_1 on a_table (item_a);
create unique index idx_2 on a_table (item_b);
create unique index idx_3 on a_table (item_c);

Data for a specific item of a specific type would be read simply as:

-- reading item_a
select ... from a_table where item_a = '...';

-- reading item_b
select ... from a_table where item_b = '...';

-- ...

If an item is of type a, it can't be of type b, so each row must have only one of item_a, item_b or item_c with a value, the others must be null. (This can be enforced in any ways, it does not matter if it impacts insertions.)

In theory the number of type of items could grow (maybe a fourth item type could be added somewhere in the future).

This fact pushed towards another solution which also avoid the ugly mutually exclusive (unchecked in the given example) columns:

create table a_table
(
   item       varchar2(30) not null,
   item_type  varchar2(10) not null,
   -- other columns
);

create unique index idx_1 on a_table (item_type, item);

-- reading item_X
select ... from a_table where item='...' and item_type='item_X';

Disregarding the obvious disadvantage of the first solution when the number of item types increases and other possible design flaws, is there any important performance difference between accessing (select of a row using indexed columns) a table as done in the first case and accessing a table as done in the second one?

The cardinality of item_a and item_b can be millions, the cardinality of item_c is surely under 200.

Notes

Assume that there will be an equal number of selects for item_a, item_b and item_c
In the first solution, consider a composite index (item_c, item_a, item_b) iff it would be better (performance-wise) than three separate indexes, but do not forget that the comparison must be done with the second solution where both indexed columns will be used in the where condition: assume the use of the best “correct” index/indexes for both cases.

I think, from performance perspective, it will be very close.

But some thoughts:

One column indexes do not store NULL values. So individual indexes will be smaller. Composite index (item_type, item ) size will be bigger than sum of individual indexes. In extreme cases even the height of indexes may differ.
Two of three individual indexes will be unique, so it takes one less consistent read compared to nonunique index.
For item_c column oracle can build precise histogram.
In the case of (item_type, item ), oracle will correctly calculate cardinalities for queries with item types A and B, but for "item_type='C' and item=:X" it can underestimate. This is may be not a problem, but in complex queries with joins can give ineffective plan. So you need to check.
About to compare index (item_a,item_b,item_c) with (item_type, item ) - like others said it will work almost only with queries like "item_a=:X", and will not work when "item_b=:Y". So using index (item_a,item_b,item_c) for first case is just a bad idea when we have equal number of queries on item_b and item_c columns.

    create table hr.tt nologging
    as
    select case when mod(rownum,3) = 0 then rownum end AS item_a,
           case when mod(rownum,3) = 1 then rownum end AS item_b,
           case when mod(rownum,3) = 2 then round(dbms_random.value(1,200)) end AS item_c,
           o.*
     from all_objects o,
    (select * from dual connect by level < 100) d
     where rownum <= 5e6;

    create unique index hr.item_a_idx on hr.tt(item_a) nologging;

    create unique index hr.item_b_idx on hr.tt(item_b) nologging;

    create index hr.item_c_idx on hr.tt(item_c) nologging;

    create table hr.tt2 nologging
    as
    select case when mod(rownum,3) in (0,1) then rownum
                else round(dbms_random.value(1,200))
                end AS item,
           case when mod(rownum,3) = 0 then 'A'
                when mod(rownum,3) = 1 then 'B'
                when mod(rownum,3) = 2 then 'C' end AS item_type,
           o.*
     from all_objects o,
    (select * from dual connect by level < 100) d
     where rownum <= 5e6;

    create index hr.item_item_type_idx on hr.tt2(item_type, item) nologging;

some queries

SYS@mydb> select * from hr.tt where item_a = 300;

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   197 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT         |     1 |   197 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | ITEM_A_IDX |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
...
          4  consistent gets

SYS@mydb> select * from hr.tt2 where item_type = 'A'  and item = 300;

1 row selected.

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   115 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT2                |     1 |   115 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ITEM_ITEM_TYPE_IDX |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          5  consistent gets

Below is an example where optimizer calculations are wrong (oracle 11.2).
Optimizer thinks there will be only one row in the "INDEX RANGE SCAN".
Actually we see 8438 rows. Extended optimizer statistics on these two columns may be helpful, may be not.

SYS@mydb> select * from hr.tt2 where item_type = 'C'  and item = 150;

8438 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4063424880

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   115 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT2                |     1 |   115 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ITEM_ITEM_TYPE_IDX |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_TYPE"='C' AND "ITEM"=150)

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       8618  consistent gets
       . . .
       8438  rows processed

And below is the same case for individual item_c column.
Here optimizer calculations are correct - 8133 rows estimated, 8290 rows selected.

SYS@mydb> select * from hr.tt where item_c = 150;

8290 rows selected.

Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  8133 |   826K|  7737   (1)| 00:01:33 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT         |  8133 |   826K|  7737   (1)| 00:01:33 |
|*  2 |   INDEX RANGE SCAN          | ITEM_C_IDX |  8133 |       |    19   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_C"=150)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8499  consistent gets
       . . .
       8290  rows processed


Generate multiple running totals with GROUP BY day I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running balance but for some reason I cannot get the GROUP BY portion of this query to work.

It continues to have duplicate days in the result set even when I attempt to group by the date (created_at). Sample below:

select
  t.customer_id,
  t.created_at::date,
  sum(case when t.stock_ticker = 'tsla' then t.amount end) over (order by t.created_at::date rows unbounded preceding) as tsla_running_amount,
  sum(case when t.stock_ticker = 'goog' then t.amount end) over (order by t.created_at::date rows unbounded preceding) as goog_running_amount,
from transactions t
group by t.created_at, t.customer_id, t.stock_ticker, t.amount
order by t.created_at desc;

Test setup:

CREATE TABLE transactions (
   transaction_id varchar(255) NOT NULL,
   amount float8 NOT NULL,
   stock_ticker varchar(255) NOT NULL,
   transaction_type varchar(255) NOT NULL,
   customer_id varchar NOT NULL,
   inserted_at timestamp NOT NULL,
   created_at timestamp NOT NULL,
   CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)
);

INSERT INTO transactions(transaction_id, amount, stock_ticker, transaction_type, customer_id, inserted_at, created_at)
VALUES
  ('123123abmk12', 10, 'tsla', 'purchase', 'a1b2c3', '2020-04-01 01:00:00', '2020-04-01 01:00:00')
, ('123123abmk13', 20, 'tsla', 'purchase', 'a1b2c3', '2020-04-03 01:00:00', '2020-04-03 01:00:00')
, ('123123abmk14',  5, 'goog', 'purchase', 'a1b2c3', '2020-04-01 01:00:00', '2020-04-01 01:00:00')
, ('123123abmk15',  8, 'goog', 'purchase', 'a1b2c3', '2020-04-03 01:00:00', '2020-04-03 01:00:00');

CREATE INDEX ix_transactions_customer_id ON transactions USING btree (customer_id);

The result here always comes back with multiple rows per day, when I want them to be grouped all into one day.

After doing some research I attempted to cast created_at to date in the GROUP BY clause as well, but I get this error:

Column t.created_at must appear in the GROUP BY clause or be used in an aggregate function

In addition, the results are only going to show days in which a transaction has happened for a user. I need to be able to show a row for each day in a time series (1 year) even if the user did not make a transaction that day. (Using the most recent running balance on the row instead.)

I think that generate_series() is the way to go, but I am having trouble understanding how to fit it in.

I see a couple of issues. This should do it:

SELECT * -- ⑥
FROM   (  -- ①
   SELECT the_day::date
   FROM   generate_series(timestamp '2020-01-01', date_trunc('day', localtimestamp), interval '1 day') the_day
   ) d
LEFT   JOIN ( -- ②
   SELECT customer_id
        , created_at::date AS the_day -- ⑥
        , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'tsla')) OVER w AS tsla_running_amount -- ③
        , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'goog')) OVER w AS goog_running_amount
   FROM   transactions t
   WHERE  created_at >= timestamp '2020-01-01'  -- ④
   GROUP  BY customer_id, created_at::date  -- ⑤
   WINDOW w AS (PARTITION BY customer_id ORDER BY created_at::date) -- ③
   ) t USING (the_day) -- ⑥
ORDER  BY customer_id, the_day; -- ⑦

db<>fiddle here

① Generating all days of the current year in optimized fashion. See:

Generating time series between two dates in PostgreSQL

② The LEFT JOIN you had in mind. You may really want one row per combination of (customer_id, day). See:

Fill missing dates within groups

③ While you also GROUP BY customer_id the Window frame needs to lead with PARTITION BY customer_id.

Drop rows unbounded preceding. The default window frame should be just fine. The manual:

  The default framing option is RANGE UNBOUNDED PRECEDING, which is the
  same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Use the aggregate FILTER clause instead of the CASE expression. Shorter, faster, cleaner. See:

Return counts for multiple ranges in a single SELECT statement

Finally wrap the result of the aggregation in another sum() for the window function. See:

Get the distinct sum of a joined table column

The separate WINDOW clause avoids spelling out the same repeatedly. No effect on performance.

④ The WHERE is logically redundant, but assuming there are older rows, too, it excludes irrelevant rows early, improving performance.
  Asuming no or almost no future timestamps, so no upper bound.

⑤ You cannot have t.stock_ticker, t.amount in GROUP BY as you want one row per (customer_id, the_day).

⑥ Use the same column alias (the_day in my example) to allow the simple USING (the_day) in the JOIN clause, and SELECT * at the top level.

⑦ Not sure about the final sort order. This seems more useful to me. Adjust to your preference.


Migration from Cassandra 2.x to 3.x: schema_keyspaces table is gone. How do you query system keyspaces? I just migrated from Cassandra 2.x to 3.x (specifically, 3.4). Now the system tables have completely changed, and these changes are not in the documentation. If you look at the current CQL docs for system tables, they only describe the system schema as it was in 2.X.

CQL 3.3 Docs for Cassandra 2.X and greater

Does anyone out there have access docs that detail this change? How do I query to see what keyspaces are available?

There is a new system keyspace called "system_schema", but there are only two tables in it: system_schema.tables and system_schema.columns. So I'm at a loss as to where to look for the information I need (I need to be able to effectively do a DESCRIBE KEYSPACES command, but outside of cqlsh).

Is the answer this: SELECT keyspace_name FROM system_schema.tables; the new way to do this?

There is a new system keyspace called "system_schema", but there are only two tables in it:

Ok something must have gone wrong in your upgrade, because that's not right.  When I check my system_schema 3.4, I see this:

[cqlsh 5.0.1 | Cassandra 3.4 | CQL spec 3.4.0 | Native protocol v4]
Use HELP for help.
aploetz@cqlsh> use system_schema ;
aploetz@cqlsh:system_schema> desc tables;

tables     triggers    views    keyspaces  dropped_columns
functions  aggregates  indexes  types      columns

There are definitely more than two tables in that keyspace.

  How do I query to see what keyspaces are available?

The new way to do this, is to query system_schema.keyspaces:

aploetz@cqlsh:system_schema> SELECT * FROM keyspaces;

 keyspace_name          | durable_writes | replication
------------------------+----------------+-------------------------------------------------------------------------------------
        zeroreplication |           True |       {'DC1': '0', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
            system_auth |           True | {'class': 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
          system_schema |           True |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
          experfy_class |           True |       {'DC1': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
                 system |           True |                             {'class': 'org.apache.cassandra.locator.LocalStrategy'}
          stackoverflow |           True |       {'DC1': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
              eqcontrol |           True |       {'DC1': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}

The main difference between system.schema_keyspaces and system_schema.keyspaces, is that system_schema.keyspaces only has 3 columns instead of two (strategy_class and strategy_options were combined into replication).


SQL Agent PowerShell task never finishes I'm quite new to PowerShell, and now I've found dbatools.io, where I would like to run a PowerShell task in SQL Agent like

$ExportPath = $env:TEMP + '\DriveSpace.csv'
$datatable = Import-Csv $ExportPath | Out-DbaDataTable
Write-DbaDataTable -SqlServer MyServer -Database Utils -InputObject  $datatable -Table dbo.FreeSpaceOnDiskDrive -AutoCreateTable

It starts, but it never finishes.
I've also tried to have a task that is doing this:

Get-DbaDatabaseSpace -SqlServer MyServer -IncludeSystemDBs | Out-DbaDataTable | Write-DbaDataTable -SqlServer MyServer -database utils -Table dbo.DiskSpaceExample -AutoCreateTable

Same problem. It runs ok in Windows PowerShell ISE, but just hangs in SQL Agent.
So now it has created a new table for me, and populated it with data, but the job just keep on running.

The problem you are experiencing is with the PowerShell subsystem in SQL Server Agent. It is a bit flakely with using other modules becuase you are put in the context of the SQL Server PowerShell Provider (SQLPS.exe). So it works the same way as if you opened up sqlps.exe and then try to execute your code.

One thing to keep in mind with dbatools module is that it will conflict with both SQLPS and the sqlserver module that MS now maintains separate for SQL Server. Last I checked the main thing that it conflicted against was TEPP that we have in the module now, it just can't load that code. [Caveat: I'm a major contributor to this module.]

The dbatools module has custom types and styles built in so when you run the scripts under PowerShell host that also has SQLPS or the SQLServer module imported your results will vary.

To utilize dbatools in a SQL Agent step make sure you only use the CmdExec subsystem (step type) and then call PowerShell host to execute your code. If you do not want to maintain a file for each script you can put your code in a SQL Agent CmdExec step in the manner illustrated below, but more complex script it is easier to maintain via files.

USE [msdb]
GO

/****** Object:  Job [dbatools_example]    Script Date: 2017-08-30 8:53:15 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2017-08-30 8:53:15 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'dbatools_example',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [dbatools_command]    Script Date: 2017-08-30 8:53:15 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'dbatools_command',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'CmdExec',
        @command=N'powershell.exe -ExecutionPolicy Bypass -Command "Import-Module dbatools; $server = ''manatarms''; Get-DbaDatabaseSpace -SqlInstance $server -IncludeSystemDbs | Out-DbaDataTable | Write-DbaDataTable -SqlInstance $server -Database db1 -Table dbo.FreeSpaceOnDiskDrive -AutoCreateTable"',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Running the job above gives me this in my database and table db1.dbo.FreeSpaceOnDiskDrive


SQL Azure large multi-tenant table / lock escalation issue / partitioning / full text index Our app organizes its data in "projects" that are saved in an SQL Azure database. A project can contain 100s or 100'000s of rows (spread over 35 tables). Since we have 1000s of projects, using a database or schema per project is not an option (we would end up with 10'000s of tables). We now save every entity in one single table that has a ProjectKey and use row level security.

Occasionally, some users import a large amount of data in one batch / transaction (100'000s of rows). This is a legitimate scenario in our app. For such large inserts, SQL Server performs a lock escalation and locks the complete table. This means that all other users are blocked from saving their data.

We could disable lock escalation, but this does not seem to be a good idea for 100'000s of rows. Another option is to partition the table so that (nearly) every project has its own partition and use (lock_escalation = auto). This way, SQL Server locks the respective partition only. I have made some experiments with 13'500 partitions and things seem to work well.

We also use SQL Server's full text search to index some of our tables / columns. FTS requires a single column unique index. By default, partitioned tables require partitioned indexes, resulting in more than one column. I found out that I can create a single column unique index over all partitions with a "non-aligned" index.

However, this destroys my locking-per-partition strategy. Since the non-aligned index sits on partition 1 and is updated by every insert, a large insert will lock it completely and again lead to the locking of all other users...

Is there a way to use FTS with a partitioned table and have a single-column index for a full text index?

I have found a solution.

For the tables that need full text indexing, I create an additional column, computed/persisted from the concatenation of the ProjectKey and an int identity column. This new primary key column is partitioned with PartitionFunction/Scheme A and is used as the single column unique index for the full text index.
All non-full text indexed tables are partitioned with the ProjectKey, using PartitionFunction/Scheme B.

The learning point was: Multiple different partition functions / schemes can be created in SQL Server. If the functions produce equal results, the respective tables will be "splitted" to the same partition. Using (lock_escalation = auto), every project will now be locked separately from the others.


With foreign keys automagically dropping out, can I set up an alert? A strange thing happened to my company's Oracle database:

  Foreign key constraints keep dropping out by themselves.

If you could solve this problem, that would be great. But even if you can't, another method would be great, that is,

  Setting up an alert that will send an email when a foreign key constraint is dropped.

If you could solve any of the above 2 problems, it would be great to me.

Foreign keys kept dropping out by themselves.

Unless there is a major bug (and that would be talked about a lot online) in the version you are running, then this should not happen. Something is asking for the keys to be dropped rather than the engine doing it automatically.

  Setting up an alert that will send an email when a foreign key is dropped.

You could use a DDL trigger (search the documentation for "DDL Trigger", on online more generally for examples) to log the exact time that a DROP statement takes action. I don't know if sending mail is possible/practical in Orcale but you can definitely log the events to a table that you can refer to later.

The trigger may give you access to the user account that is used, though this may not be useful if it is being caused by an application user rather than a more specific login (i.e. a human admin). Knowing the exact time will allow you to more easily work out what process (an errant index maintenance script, someone deploying updates that are incorrect and reset the table's keys to an older incorrect setup, and so on) is asking for the keys to be dropped.


Is it possible to edit a partition function on MSSQL 2016? I've inherited a partitioned database with millions of rows and I've noticed there is a typo in the partition function:
CREATE PARTITION FUNCTION [Partition_by_customer](varchar(10)) AS RANGE RIGHT FOR VALUES (N'DMA', N'DMB', N'DMC', N'DMD', N'DME')

Is there an easy way to remove those unicode converters (as they are causing implicit conversions), everything should be a varchar(10)
Thanks
Roger

The implicit conversion of the Unicode literal to varchar is a one-time operation done when the DDL is executed. The "typo" ('N' national character prefix) is not part of the partition function definition nor used by SQL Server after creation. The Unicode literal is generated by SMO when the function definition scripted.
The actual boundary values are a SQL_VARIANT type and match the partition function data type definition regardless of the specified literal. The catalog view query below shows this:
SELECT
      prv.value AS BoundaryValue
    , SQL_VARIANT_PROPERTY(value, 'BaseType') AS DataType
    , SQL_VARIANT_PROPERTY(value, 'MaxLength') AS Length
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv ON prv.function_id = pf.function_id
WHERE pf.name = N'Partition_by_customer';

Results:
+---------------+----------+--------+
| BoundaryValue | DataType | Length |
+---------------+----------+--------+
| DMA           | varchar  |     10 |
| DMB           | varchar  |     10 |
| DMC           | varchar  |     10 |
| DMD           | varchar  |     10 |
| DME           | varchar  |     10 |
+---------------+----------+--------+


Dumping stored procedure result to a text file programmatically How can I save stored procedure execution result to a text file? I need to do this from SQL script (not Management Studio nor external utility) and I need to preserve correct characters encoding (Unicode in this case).

The simplest method would be a 2 step approach :

Log the output of stored procedure to a physical table in the sql server database. e.g. insert into dbo.X_sp_name_out exec dbo.sp_name
Use BCP OUT or SQLCMD to export data to text file.
(Optional.. but important to manage table growth) Have a purging process to purge out the old records or before next execution of the SP, truncate the table.

Make sure you use N'your unicode data' when storing data.

You can think of using SSIS to do the same and schedule the package using SQL Agent job.


Can I do a backup without extension functions? I use the PostGIS extension on my database. The thing is when I do a backup and then a restore, all the PostGIS functions give a warning during the restore process, because the functions already exist, as the extension is already installed.

I have to scroll through all the warning to make sure there isn't a real error.

So what is the right way to backup / restore, so I can have a clean restore process?

The best solution is to restore into a database that does not have PostGIS installed yet.

If PostGIS is installed in the template1 database, use template0 to create the new database.


Setting a column's default value by adding a constraint I wrote this query:

ALTER TABLE test2 add constraint setting_value33 default male FOR namee;

But I get this error when running it:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default male FOR namee' at line 1

This is a rather contrived example, but you'll get the idea.

mysql> create table b(a int primary key, c varchar(3), unique key x_b_uk (c));
Query OK, 0 rows affected (0.46 sec)

mysql> alter table b alter c set default 'xyz';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into b (a) values(1);   <<== didn't set field c!
Query OK, 1 row affected (0.39 sec)

mysql> select * from b;
+---+------+
| a | c    |
+---+------+
| 1 | xyz  |                    <<===== value of c = 'xyz'!
+---+------+
1 row in set (0.16 sec)

mysql>

Check here.


Listener pointing to incorrect AlwaysOn Availability Group All, Thanks in advance
I have two instances of SQL Server 2012 SP1 on a three node WSFC with a HAdr group on each instance with a corresponding listener.  Instance 1's listener points to Instance 1, no issue.
Instance 2 listener also points to instance 1.  I'm at a loss why listener #2 is showing databases for Instance 1.  Ive recreated instance 2's HAdr groups from scratch but still no change.  Here is some additional information -

Both instances Database mirroring endpoints are configured on different ports.
The SQL Server instances are named instances also using different ports.
Listeners have different names and different IP addresses and ports.
Opening up each SQL instance on each of the three nodes show the correct HADR information and databases.

The Listener seems to be getting confused and is looking at the wrong instance.

Any help would be greatly appreciated
thanks
James

An Availability Group Listener simply connects the user to a database instance. Once they are connected to the instance they can access all the databases on the instance. It doesn't matter of the databases are part of the availability group or not. This is by design.

In other words everything is working exactly as it should be.


Join Query effects natural ordering If we have 2 tables

**user**
+----+------+------+-----------+
| id | user | pass | gender_id |
+----+------+------+-----------+
| 1  | lisa | good |     11    |
| 2  | jack | pass |     10    |
| 3  | paul | 5555 |     10    |
| 4  | rich | none |     10    |
+----+------+------+-----------+

**gender**
+----+--------+
| id | gender |
+----+--------+
| 11 | female |
| 10 | male   |
+----+--------+

If we join  them like so

SELECT user.id, user.user, user.pass, user.gender_id, gender.gender FROM `user` JOIN `gender` on user.gender_id

The result of query

array(4) {
  [0]=>
  array(4) {
    ["id"]=>
    string(3) "2"
    ["type"]=>
    string(6) "jack"
    ["pass"]=>
    string(11) "pass"
    ["gender"]=>
    string(1) "male"
    }
  [1]=>
  array(4) {
    ["id"]=>
    string(3) "3"
    ["type"]=>
    string(6) "paul"
    ["pass"]=>
    string(11) "5555"
    ["gender"]=>
    string(1) "male"
    }
  [2]=>
  array(4) {
    ["id"]=>
    string(3) "4"
    ["type"]=>
    string(6) "rich"
    ["pass"]=>
    string(11) "none"
    ["gender"]=>
    string(1) "male"
    }
  [3]=>
  array(4) {
    ["id"]=>
    string(3) "1"
    ["type"]=>
    string(6) "lisa"
    ["pass"]=>
    string(11) "good"
    ["gender"]=>
    string(1) "female"
    }

The order has now changed even though we did not specify this.(user lisa is last)
If we change lisa gender_id to 11, and run the same query, the result will be

array(4) {
  [0]=>
  array(4) {
    ["id"]=>
    string(3) "1"
    ["type"]=>
    string(6) "lisa"
    ["pass"]=>
    string(11) "good"
    ["gender"]=>
    string(1) "male"
    }
  [1]=>
  array(4) {
    ["id"]=>
    string(3) "2"
    ["type"]=>
    string(6) "jack"
    ["pass"]=>
    string(11) "pass"
    ["gender"]=>
    string(1) "male"
    }
  [2]=>
  array(4) {
    ["id"]=>
    string(3) "3"
    ["type"]=>
    string(6) "paul"
    ["pass"]=>
    string(11) "5555"
    ["gender"]=>
    string(1) "male"
    }
  [3]=>
  array(4) {
    ["id"]=>
    string(3) "4"
    ["type"]=>
    string(6) "rich"
    ["pass"]=>
    string(11) "none"
    ["gender"]=>
    string(1) "male"
    }

This result is how the rows are positioned in the table(user lisa first), which is represented correctly in the results as we did not specify any order.

Why is the first result set being ordered when we are not specifying this. It does not seem correct, I want the results to be pulled exactly how they are positioned in the table.

Thanks

In a relational database, there is no such thing as a "natural order".  If you care about the order of your results, you have one and only one option-- specify an ORDER BY clause.

Without looking at the query plan, I would guess that your gender table is the driving table of a nested loop join which happens to return all the male rows first.  It would be perfectly valid, though, for the order of results to change if the optimizer decides to use a different plan, if the underlying data changes, if you add and remove rows over time, etc.  If you care about the order, specify an ORDER BY clause.

Adding an ORDER BY does not necessarily force the database to do any more work.  It is entirely possible that it simply causes the database to choose a query plan that returns data in the order you want as a side effect.


What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive? Possible Duplicate:
  Files - in the database or not?

I've been reading "Programming Entity Framework Code First" (http://www.amazon.com/Programming-Entity-Framework-Code-First/dp/1449312942).

There are a number of examples given where information about a particular person is stored in a SQL table, including the person's photograph (a digital image stored in a VARBINARY field).

In all of the systems that I have worked on, any kind of file associated with a record in SQL is stored on an ordinary network drive; not in SQL.  I would imagine that there must be some reasons for this, so here's my question:

What are the advantages and disadvantages of storing binary data in a SQL table vs storing the same binary data on a network drive given that the binary data is associated with a record in SQL?

One advantage is that if the program that needs to access the data does not have access to the file system, it's not a problem! If the database is accessed by multiple applications on different systems, they probably shouldn't have file-system access to your database server. You could always store a path and they create a web service to allow these applications to request a file on a specific path, but that's a bit more work so you'd better be sure it's worth it. Keeping the binary data in the database and returning it in the record set is simpler.

A disadvantage is that your database will now require more storage space and this will impact backups and other things. Sometimes having large amounts of binary data in a database can also have an impact on performance. Some DBAs handle this by moving BLOB data to tables in separate schemas that are managed differently from the main "data" schemas. While such techniques may work, you might need a deeper understanding of database management and tuning to get it working well, if you have LOTS of binary data.


Pgpool - 3.5 configuration [enterprisedb@pgdvdb1dr bin]$ 2017-06-20 11:34:41: pid 103313: WARNING:  could not open configuration file: "pgpool.conf"

2017-06-20 11:34:41: pid 103313: DETAIL:  using default configuration parameter values
2017-06-20 11:34:41: pid 103313: FATAL:  initializing pool password, failed to open file:"/mnt/hgfs/edb-postgres.auto/pgpool/staging/linux-x64/pgpool/etc/pool_passwd"
2017-06-20 11:34:41: pid 103313: DETAIL:  file open failed with error:"No such file or directory"

[1]+  Exit 3                  ./pgpool -n

you need to set up backend PostgreSQL servers for pgpool-II. These servers can be placed within the same host as pgpool-II, or on separate machines. If you decide to place the servers on the same host, different port numbers must be assigned for each server. If the servers are placed on separate machines, they must be configured properly so that they can accept network connections from pgpool-II.

if you will place two servers within the same host as pgpool-II, let's  suppose that you want to assign 5432, 5433 port numbers respectively. To configure pgpool-II, edit pgpool.conf as follows.

backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1

For backend_hostname, backend_port, backend_weight, set the node's hostname, port number, and ratio for load balancing. At the end of each parameter string, node ID must be specified by adding positive integers starting with 0 (i.e. 0, 1, 2, …).

backend_weight parameters are all 1, meaning that SELECT queries are equally distributed Between two servers.

Starting/Stopping pgpool

$pgpool

The above command, however, prints no log messages because pgpool detaches the terminal. If you want to show pgpool log messages, you pass -n option to pgpool command so pgpool-II is executed as non-daemon process, and the terminal will not be detached.

$ pgpool -n &

The log messages are printed on the terminal, so the recommended options to use the following.

$ pgpool -n -d > /tmp/pgpool.log 2>&1 &

The -d option enables debug messages to be generated.

To stop pgpool, execute the following command.

$ pgpool stop

If any client is still connected, pgpool waits for it to disconnect, and then terminates itself. Run the following command instead if you want to shutdown pgpool forcibly.

$ pgpool -m fast stop

For further your ref Here


Figure out what IP address MySQL resolves a host name to I'm setting up a MySQL slave, but it can't connect to master (error 2003). Master is available. I suspect that MySQL resolves Master's hostname to some unexpected IP address and consequently chooses different route which is not allowed to connect to the master. Both master and slave are RDS MySQL in different VPCs :)

So how can I figure out what IP address MySQL gets using only MySQL commands or logs?

Alright.. It was wrong TCP port. Sorry :) So I solved my problem with RDS connectivity. But the second part still stands: MySQL server as a "DNS resolver". While dealing with RDS I tried to go with PostgreSQL, because if it were DNS problem it would affect PostgreSQL as well. I figured that I can do it in PostgreSQL with various extensions like plperlu. Unfortunately, all of them are blocked in RDS :)


How to code something like 'Update on duplicate key update to something else'? As part of dealing with the UK's GDPA 'right to be forgotten' in a small database I want to update the (autoinc) PK of a row in table 'member' to an obfuscated value and let it cascade down through the FKs in related tables, such as the dates they held certain positions.
If I then set the names of the member in table 'member' to empty strings I can still use my related tables, for example to determine that there was someone holding a certain position at a certain time but I will no longer be able to ascertain either the name or the id of the person who held it, which is what I want.
I have written a simple a UDF obfuscate_id() that takes a positive integer ID and returns a negative ID calculated by combining the id passed with a random number.
FUNCTION obfuscate_id(id INT) RETURNS INT
COMMENT 'returns the negative version of the id passed, obfuscated by a random number'

BEGIN
  DECLARE result INT;
  SET result =  (FLOOR(RAND(id)*10000) + id ) * -1;
  IF result > 0  THEN -- ensures result is -ve even if called with a -ve parameter
     SET result = result * -1;
  END IF;
  RETURN  result;
END;

And I am using the sql
UPDATE member
SET  member_id = obfuscate_id(member_id)
WHERE member_id = 1234;

However, this might generate a negative id that already exists from a previous update of another member_id. If so I want to set it to something else, maybe by obfuscating the id again as in
SET  member_id = obfuscate_id(obfuscate_id(member_id));

Is there a simple way, like ON DUPLICATE KEY ... to check if the value I am about to update to already exists and if so update to something else?
I did look at
how-to-use-on-duplicate-key-for-update
and
update-on-duplicate-key-update
and
mysql-update-with-on-duplicate-key-update
but am no wiser
I would add that given the size of table 'member' and the particular application, even the first update is going to be a very rare occurrence, so the chance of having any obfuscated IDs is extremely slim, let alone duplicate ones, but I'd like to cover the eventuality if I can.

Wouldn't it be simpler / easier to just choose a random number who's range is outside your current range of values in the member table? E.g. something like (FLOOR(RAND(id)) * -1) + (SELECT MIN(id) FROM member) (semi-pseudocode)?


TDE using EKM device We use an external HSM device to create and store keys and use it for TDE.
Our auditors are asking questions about where the keys are store.  So obviously the key is stored in the external HSM device.

In this link, under step 5,

USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_512,
PROVIDER_KEY_NAME = 'SQL_Server_Key' ;
GO

Does this create a copy of the key in master DB also?
In case of TDE, how often does SQL Server has to contact the EKM device?  Does it have to contact the ekm device veytime while reading
data file from disk?

Does this create a copy of the key in master DB also?

AFAIK, no.

  In case of TDE, how often does SQL Server has to contact the EKM device? Does it have to contact the ekm device veytime while reading data file from disk?

This depends on the HSM, the driver they provide which you need to have installed and loaded into the SQL Server process space but generally it will be cached for an undetermined period of time.


Query 'optimizing' state is taking too long I have a MySQL 5.6 on Amazon RDS that I'm using for testing some data archiving scripts. I'm removing oldest data based on a "updated_date" column and index. Curiously, after removing a few million rows, my script gets stuck on the initial query it does for determining data bounds.

I run a query like this:

SELECT min(updated_date) as oldest, max(updated_date) AS newest FROM `order`;

The explain command on this query shows:

'1', 'SIMPLE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Select tables optimized away'

So, it's supposed to hit the index and run almost instantly, and it did when the testing started, but now, after millions of rows were removed, it gets stuck in the 'optimizing' state for several minutes.

The script is the only thing running on the database.

Any ideas on what's wrong with it? Am I supposed to do something when removing lots of rows like that? Do I have to run optimize table, even though I'm not using delete quick?

Update #1

The result from show table status like 'order':

Name,Engine,Version,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Collation,Checksum,Create_options,Comment
order,InnoDB,10,Compact,568037197,280,159252496384,0,180806041600,37692112896,4052226884,"2015-01-26 17:27:20",NULL,NULL,utf8_general_ci,NULL,,

The result from select count(*) from order is 618376777 rows.

Unfortunately, I can't post the whole schema here, but where it bears on the issue, the result from show create table order is:

CREATE TABLE `order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  // 31 data columns here
  `updated_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_order_updated_date` (`updated_date`),
  // 9 indexes here
) ENGINE=InnoDB AUTO_INCREMENT=4052226884 DEFAULT CHARSET=utf8;

Update #2

By separating the min() and max() calls in two queries, I noticed only the min() query is affected. The max() returns almost immediately, so it looks like the min() is traversing the index for all index entries that existed but are now empty. Is there any way to prevent that from happening other than rebuilding the index?

Update #3

RickJames nailed the problem with the hint about change buffering, but disabling it entirely compromises performance for all inserts, deletes and updates. Eventually, I figured out the time it took to flush the changing buffer was reasonable with the production server, so problem solved for me, but if you run into the same issue with a low-end server with magnetic storage, good luck with it.

SELECT min(updated_date) as oldest, max(updated_date) AS newest FROM `order`;

is well optimized if you have INDEX(updated_date) (which you do).  It will do two probes.  No need for two queries, etc.

The EXPLAIN said 'optimized away' because it could see that it did not need to do any work.

I checked with a similar table, then checked the Handler% STATUS values -- Handler_read_first and Handler_read_last incremented by 1 each.  This is indicative of optimizing MIN and MAX.

Do not use OPTIMIZE TABLE it is (usually) a long wast of time.

It is a huge table.

DELETEing a million rows will queue up a lot of stuff, especially because of 10 secondary indexes.  I'll bet that when you asked for MAX and MIN, it had to finish up all the pending index updates.

If that is the case, none of the suggestions will really solve the problem.  Chunking the deletes (which is a good idea anyway) might have "hidden" the problem by slowing down the delete task.  As @eroomydna says, the undo logs must have been huge.

On what basis are you DELETEing?  If it is "purge all records older than X", then this is a cure:  PARTITION BY RANGE(...) on the date (to use for purging) and DROP PARTITION to jetison old rows.  This is also much cleaner (in the sense of OPTIMIZEing to defragment) than DELETEing.

Rolando, ANALYZE TABLE will recalc the stats "instantly".  OPTIMIZE TABLE rebuilds (and ANALYZEs) the table but takes forever on this sized table.

Potentially valid is to copy over the useful rows, a la Rolando's #4.  But only if you are jettisoning "most" of the table.  I suggest that is not the case here -- "deleting a few million rows" out of 618M.

10 secondary keys is a lot.  Would you care to show them; we may have suggestions on pruning the list.  For this sized table, it is costly to maintain that many.

Edit -- "Change buffering"

I believe what I have described is called change buffering for DELETEs.  More discussion:
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-performance-change_buffering.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-change_buffering.html

You could try innodb_change_buffering = none.


Affinity Mask and Affinity I/O Mask Using: SQL Server 2008 R2

Under what circumstances would you set the Affinity Mask and Affinity I/O Mask options in sp_configure in SQL Server?

We are currently running on a machine with 16 cores dedicated to SQL Server.  We are also seeing slowness in our I/O operations.  Some writes (insert 1 row) can take up to 500 ms.  We would like to support many writes per second.  The process that inserts data can spawn up to 4 threads for executing the insert operations.

I'm considering dedicating 8 cores to the affinity mask and the other 8 to affinity I/O mask.

Is this recommended?

I'd consider many other options before I played with these

Some things to check here on SO: https://stackoverflow.com/a/2009693/27535

Additionally...

indexes: are your writes scanning not seeking?
key: do you have GUIDs as clustered indexes (bad idea!)
triggers: no emails being sent on INSERT or CURSORs?
disk layout: separate fast RAIDs for data and logs, per database?
autoshrink/autoclose + associated file growth
unmaintained statistics and indexes
..

Basically, your problem will be poor design poor indexing or poor hardware...


object oriented database I am new to object oriented databases.

What is the error in this database schema?

It gives error after insertion query and not before that

insert into student_table values
(
  student_detail
  (
    name_student('abc','pqr'),add_student('ABCD','pqrs'),2000
  )

);

sqlfiddle

This works OK (tested on Oracle 11.2):

create or replace type name_student as object
(
  fname varchar2(10),
  lname varchar2(10)
);
/

create or replace type add_student as object
(
  street varchar2(10),
  city varchar2(10)
);
/

create or replace type student_detail as object
(
  name name_student,
  address add_student,
  salary number(10),
  member function getsalary return number
);
/

create or replace type body student_detail as
  member function getsalary return number as
  begin
    return (salary*10);
  end getsalary;
end;
/

show errors;

create table student_table(S student_detail);

Test:

SQL> insert into student_table values
(
  student_detail
  (
    name_student('abc','pqr'),add_student('ABCD','pqrs'),2000
  )
);  2    3    4    5    6    7

1 row created.

SQL> select * from student_table;

S(NAME(FNAME, LNAME), ADDRESS(STREET, CITY), SALARY)
--------------------------------------------------------------------------------
STUDENT_DETAIL(NAME_STUDENT('abc', 'pqr'), ADD_STUDENT('ABCD', 'pqrs'), 2000)

SQL Fiddle doesn't seem to like it too much!


Choosing Consecutive Seats by Seatrow SELECT TOP N seats.*, eventtickets.eticketprice from seats
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype
WHERE ((Seats.seatType ='BOXSEAT')
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
ORDER BY convert (int, seats.seatSection), seats.seatrow ASC

My query above generates the best available # of N seats, but it will fill up one section and then the rest of that order is put in the next section.  I need it to find a new section that has all N seats available.  IF a seat is in TicketSales table it is not available.  If salecategory is Null then that means ticket has not been SOLD/BLOCK/HOLD so its Available.

It is obviously limited to 6 seats together, but I do have one section which has over 30 seats in one row.

Section 1:
Row 1: 1-2-3
Row 2: 4-5-6

Section 2:
Row 1: 1-2-3
Row 2: 4-5-6

etc.

I am at my wits end.  Been staring at this for 2 days straight and my mind is mush. I am pretty weak at SQL, amazing I got this far above.  I think I have gotten close, but no enchilada.

See pics of the 2 main tables attached.  I just need the seatprice from eventtickets table so it's not that important.

I thank you for any help you can give me on this.

If you're happy with what you have already, try starting with a set of the distinct sections, cross joining on that, and then filtering your subquery to only that section. That way, it's like considering each section as if it's its own event.

But... why not consider looking at all the seats and finding blocks where the (N-1)th next one is the actual right number away:

SELECT * FROM
(SELECT seats.*, eventtickets.eticketprice,
    LEAD(seats.SeatID,@N-1) OVER (PARTITION BY seats.seatSection ORDER BY seats.SeatID) AS SeatNAway
from seats
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype
WHERE ((Seats.seatType ='BOXSEAT')
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
) s
WHERE s.SeatId = s.SeatNAway - (@N-1)
ORDER BY convert (int, s.seatSection), s.seatrow ASC

See how you go with this maybe... check that you understand the inner subquery, of course.


Can't restore database with (apparently) same version I'm trying to restore a SQL Server database instance in my local machine I've backed up from an Azure Cloud environment but I'm getting the following message:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 15.00.2000. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

enter image description here
The version seems to be the exactly the same, so it doesn't make any sense to me. Am I missing something? Or you just can't restore a backup from an Azure Cloud at all?
Thanks in advance.

The two managed types of SQL Server in Azure (SQL Database and Managed Instance) are version 12.0.2000.8. Don't be fooled by the low version number, it is always higher than on-prem meaning you can never restore an "Azure SQL" backup to on-prem.
You cannot perform a backup from SQL Database, but you can do a copy only backup from Managed Instance. This would be my guess.
Perhaps the error message is incorrect, and should have said that backup is from 12.00.2000
If it is the 3:rd type of "SQL Server in Azure", a VM with SQL Server installed, then it is of any version you like. This should be restoreable.
So, find out what type of SQL you have in Azure, then take it from there.


Inherited Permissions: SQL Server 2005 vs 2016 Question:

I have a SQL Server 2005 server and a SQL Server 2016 server.
On each server is a database (A) with a stored procedure that selects data from a view in Database (A)
The view in database (A) is referencing a table in Database (B) on THE SAME SERVER
I am using a logon that has ONLY got execute permission on the stored procedure on database (A).
This setup is the same for BOTH servers.
On the 2005 server, I can run the SP as the described logon and retrieve data via the view in database (A) from the table in database (B) - as I would expect.
On the 2016 server, I get an error because from the database (A) I cannot select, via the SP and the view, data from the table in database (B).

Both servers are running SQL Server Standard Edition - (though one is 2005 and one is 2016).

To summarize:

SQL Server 2005

DATABASE A = VIEW = TABLE in DATABASE B
DATABASE A = STORED PROCEDURE = select from VIEW
LOGON = ONLY EXECUTE ON STORED PROCEDURE
RESULT = SUCCESS

SQL Server 2016

SET UP APPEARS IDENTICAL
RESULT = FAIL

Why is this?
What is different between these two versions of SQL with regards to inheritance of permissions?
Is it something very obvious that I am missing?

Any pointers would be very helpful!

I would not expect the different SQL Server versions to make a difference here. Although the setup might appear identical, there are likely subtle differences between the databases on the 2 servers or the server config.

The first thing I suggest is to verify requirements cross-database ownership chaining are met:

Ensure the 'cross db ownership chaining' configuration option is the same on both servers
Make sure the DB_CHAINING database option is identical
Check that the database owners are the same to maintain an unbroken ownership chain for dbo-owned objects

If the culprit is the 'cross db ownership chaining' server option, it would be better leave that off and instead turn on the more granular DB_CHAINING database option for the SQL 2016 databases in question.

Post the actual error message if you still have problems after the above.


SSIS via Import Wizard SQL Server 2008 R2 I have a flat file i need to import into SQL. For a lot of boring reasons, I curently don't have access to use BIDS, only the import wizard in SSMS. The flat file looks sort of like:

A
2
B
G
-blank line here-
8
R
F
6

Basically, a few lines, separated by an empty line, Repeat a few thousand times. The text field here doesn't show it all that well, if you have questions about that, I can try to clarify.

What i need is for each of those groups to be its own row, merged together with a delimiter. So, the data in the table would look like:

A,2,B,G
8,R,F,6

Only one column in the table, with a delimited string of data in that column.

Can this be accomplished via the wizard? If not, can it be accomplished with BIDS and a real SSIS package? That may be an option, but it will probably be a while before I have access to that, and by that time, it may be moot.

You could use the wizard and load the data into a temp table then process it from there.  That's probably your easiest option.  Unfortunately while I know there is a relatively easy way to do the processing I just can't remember what it is.

The other option is going to be to use BIDS and you will probably have to use a script source to construct your input.


Query Only Returning Matching Data I am attempting to use a Left Outer Join to return data from two tables.  MetaKnight has the most storeNames and I want all of those to be returned, and of course the corresponding data from KnightNinja.  I threw together the below syntax, but my syntax is only returning the name of the stores in both tables that meet the criteria.  For example, Store D & Store E should be returned since the date is in January of 2017 - but the syntax does not return it.  Why?

Here is sample garbage DDL & Query

Create Table MetaKnight
(
    storeName varchar(500) NOT NULL
    ,storeNumber varchar(100) NOT NULL Primary Key
    ,openDate date NOT NULL
)

Create NonClustered Index snIndex
On MetaKnight (storeName ASC)

Create Table KnightNinja
(
    storeName varchar(500) NOT NULL
    ,ssID varchar(100) NOT NULL Primary Key
    ,c1 int not null Default(0)
    ,c2 int not null Default(0)
    ,c3 int not null Default(0)
    ,c4 int not null Default(0)
    ,d1 date not null
)

Create NonClustered Index storeIndex
On KnightNinja (storeName ASC)

Insert Into KnightNinja (storeName, ssID, c1, c2, c3, c4, d1) Values
('Store A', '1234', '7018', '0',    '4635', '6',    '2017-03-13'),
('Store B', '5678', '0',    '0',    '0',    '0',    '2017-01-28'),
('Store C', '9101', '219',  '898',  '154',  '11',   '2017-01-10'),
('Store D', '8842', '25019',    '258947',   '5746', '0',    '2017-01-31'),
('Store E', '6643', '26682',    '25920',    '3410', '281',  '2017-02-15'),
('Store F', '2211', '57',   '0',    '392',  '22',   '2017-03-11'),
('Store G', '1432', '398',  '1007', '407',  '0',    '2017-01-10'),
('Store G', '8879', '534',  '3125', '954',  '19',   '2017-03-11'),
('Store H', '8675', '40',   '0',    '146',  '147',  '2017-01-01'),
('Store I', '2411', '0',    '4899', '0',    '0',    '2017-01-20')

INSERT INTO MetaKnight (storeName, storeNumber, openDate) Values
('Store A', '2.2',  '2017-01-19'),
('Store B', '8103', '2017-10-25'),
('Store C', '5522', '2017-10-25'),
('Store D', 'Blue', '2017-09-26'),
('Store E', 'B6565','2017-04-28'),
('Store G', 'E999', '2017-01-14'),
('Store G', 'L1354','2017-01-14'),
('Store G', 'R2288','2017-01-14'),
('Store H', 'A5678','2017-01-28'),
('Store I', 'C1234','2017-01-14')

Select
mk.storeName
,[Ninja Count] = COUNT(kn.ssID)
,[Meta Count] = COUNT(mk.storeNumber)
,[C1 Total] = SUM(COALESCE(kn.c1,0))
,[C2 Total] = SUM(COALESCE(kn.c2,0))
,[C3 Total] = SUM(COALESCE(kn.c3,0))
,[C4 Total] = SUM(COALESCE(kn.c4,0))
FROM MetaKnight mk
LEFT OUTER JOIN KnightNinja kn
ON mk.storeName = kn.storeName
AND kn.d1 >= '2017-01-01'
AND kn.d1 < '2017-02-01'
WHERE mk.openDate >= '2017-01-01'
AND mk.openDate < '2017-02-01'
GROUP BY mk.storeName
ORDER BY mk.storeName ASC

Drop Table KnightNinja
Drop Table MetaKnight

Have a look at this interesting answer of Aaron Bertrand and you'll get a detailed answer about what is happening.

Return All Data From Left Table

You should move all conditions of WHERE clause to the LEFT JOIN clause.

Select mk.storeName
       ,[Ninja Count] = COUNT(kn.ssID)
       ,[Meta Count] = COUNT(mk.storeNumber)
       ,[C1 Total] = SUM(COALESCE(kn.c1,0))
       ,[C2 Total] = SUM(COALESCE(kn.c2,0))
       ,[C3 Total] = SUM(COALESCE(kn.c3,0))
       ,[C4 Total] = SUM(COALESCE(kn.c4,0))
FROM   MetaKnight mk
LEFT OUTER JOIN KnightNinja kn
ON     mk.storeName = kn.storeName
AND    kn.d1 >= '2017-01-01'
AND    kn.d1 < '2017-02-01'
AND    mk.openDate >= '2017-01-01'
AND    mk.openDate < '2017-02-01'
GROUP BY mk.storeName
ORDER BY mk.storeName ASC

GO

storeName | Ninja Count | Meta Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | ---------: | -------: | -------: | -------: | -------:
Store A   |           0 |          1 |        0 |        0 |        0 |        0
Store B   |           0 |          1 |        0 |        0 |        0 |        0
Store C   |           0 |          1 |        0 |        0 |        0 |        0
Store D   |           0 |          1 |        0 |        0 |        0 |        0
Store E   |           0 |          1 |        0 |        0 |        0 |        0
Store G   |           3 |          3 |     1194 |     3021 |     1221 |        0
Store H   |           1 |          1 |       40 |        0 |      146 |      147
Store I   |           1 |          1 |        0 |     4899 |        0 |        0

Warning: Null value is eliminated by an aggregate or other SET operation.

dbfiddle here

Update

On this case there is a problem because none of your queries returns all storeName.

SELECT    mk.storeName,
         [Meta Count] = COUNT(mk.storeNumber)
FROM      MetaKnight mk
WHERE     mk.openDate >= '2017-01-01'
AND       mk.openDate < '2017-02-01'
GROUP BY  mk.storeName;

storeName | Meta Count
:-------- | ---------:
Store A   |          1
Store G   |          3
Store H   |          1
Store I   |          1

SELECT   kn.storeName,
         [Ninja Count] = COUNT(kn.ssID),
         [C1 Total] = SUM(COALESCE(kn.c1,0)),
         [C2 Total] = SUM(COALESCE(kn.c2,0)),
         [C3 Total] = SUM(COALESCE(kn.c3,0)),
         [C4 Total] = SUM(COALESCE(kn.c4,0))
FROM     KnightNinja kn
WHERE    kn.d1 >= '2017-01-01'
AND      kn.d1 < '2017-02-01'
GROUP BY kn.storeName
GO

storeName | Ninja Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | -------: | -------: | -------: | -------:
Store B   |           1 |        0 |        0 |        0 |        0
Store C   |           1 |      219 |      898 |      154 |       11
Store D   |           1 |    25019 |   258947 |     5746 |        0
Store G   |           1 |      398 |     1007 |      407 |        0
Store H   |           1 |       40 |        0 |      146 |      147
Store I   |           1 |        0 |     4899 |        0 |        0

Let me suggest another approach:

WITH sn AS
(
    SELECT DISTINCT storeName
    FROM MetaKnight
)
SELECT sn.storeName,
       kn1.[Ninja Count],
       cnt.[Meta Count],
       kn1.[C1 Total],
       kn1.[C2 Total],
       kn1.[C3 Total],
       kn1.[C4 Total]
FROM   sn
LEFT JOIN (SELECT    mk.storeName
                    ,[Meta Count] = COUNT(mk.storeNumber)
           FROM      MetaKnight mk
           WHERE     mk.openDate >= '2017-01-01'
           AND       mk.openDate < '2017-02-01'
           GROUP BY  mk.storeName) cnt
ON        sn.storeName = cnt.storeName
LEFT JOIN (SELECT   kn.storeName,
                    [Ninja Count] = COUNT(kn.ssID),
                    [C1 Total] = SUM(COALESCE(kn.c1,0)),
                    [C2 Total] = SUM(COALESCE(kn.c2,0)),
                    [C3 Total] = SUM(COALESCE(kn.c3,0)),
                    [C4 Total] = SUM(COALESCE(kn.c4,0))
           FROM     KnightNinja kn
           WHERE    kn.d1 >= '2017-01-01'
           AND      kn.d1 < '2017-02-01'
           GROUP BY kn.storeName) kn1
ON        sn.storeName = kn1.storeName
ORDER BY  sn.storeName ASC

GO

storeName | Ninja Count | Meta Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | ---------: | -------: | -------: | -------: | -------:
Store A   |        null |          1 |     null |     null |     null |     null
Store B   |           1 |       null |        0 |        0 |        0 |        0
Store C   |           1 |       null |      219 |      898 |      154 |       11
Store D   |           1 |       null |    25019 |   258947 |     5746 |        0
Store E   |        null |       null |     null |     null |     null |     null
Store G   |           1 |          3 |      398 |     1007 |      407 |        0
Store H   |           1 |          1 |       40 |        0 |      146 |      147
Store I   |           1 |          1 |        0 |     4899 |        0 |        0

dbfiddle here


What is the difference between integrity and consistency? Whether I read about CAP or ACID, I see that consistency is referred to ensure the DB integrity constraints. So, I do not understand, why two terms are used to refer the same thing or there is a difference between the integrity and consistency?

I read that

  Anyhow, Atomic, Consistent, Isolated, Durable are properties of the transactions. It is true that atomicity + isolation is enough for you to roll-your-own consistency. But we can also roll our own atomicity, roll our own isolation, and roll our own persistence (durability). When we roll our own, we must pay for features with our own blood, sweat, and parentheses. We don't say the properties were given to us by a transaction system.

which suggests that consistency is what user application can provide himself on top of database integrity constraints. This is not property that is provided by database, as AID properties are. Why to give C the title as you do to the other, system-provided AID properties?

I think the difference is that the two terms refer to different things:

Consistency refers to Transactions (it's the C in ACID).
Integrity refers to Databases.

A transaction is said to be consistent if it keeps the integrity of the database (i.e. if it transforms the database from a valid state to another valid state).


Joining two fast subqueries is very slow DB: MySQL 5.5.20 (WampServer)
OS: Win 7
HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX)
Memory: 8GB
my.ini: http://pastie.org/private/s7l9aadyi0qhfl5bcrnnqq

I've got two tables:

CREATE TABLE `person_seen` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `person_id` int(11) NOT NULL,
 `role_id` tinyint(3) unsigned NOT NULL,
 `kind_id` tinyint(3) unsigned NOT NULL,
 `seen` smallint(5) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `person_id` (`role_id`,`kind_id`,`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=63501 DEFAULT CHARSET=utf8

CREATE TABLE `person_total` (
 `person_id` int(11) NOT NULL,
 `role_id` tinyint(3) unsigned NOT NULL,
 `kind_id` tinyint(3) unsigned NOT NULL,
 `total` smallint(5) unsigned NOT NULL,
 KEY `person_id` (`role_id`,`kind_id`,`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

person_seen has the number of films I've seen from each person_id for given role_id and kind_id. It only has entries where seen is > 0.

person_total has the total number of films by each person_id for given role_id and kind_id.

Roles are things like director, writer, actor, etc. Kinds are things like movie, tv movie, tv series, etc.

The user should be able to choose the exact combination of values they want to access.

One such query I'm trying to run is:

SELECT name.id, name.name, ds.is_favorite, ds.is_disliked,
    ds.is_watchlist, ps.seen, pt.total
FROM (
    SELECT person_id, SUM( seen ) AS seen
    FROM person_seen
    WHERE role_id = 8 AND kind_id IN ( 1, 3, 4, 5 )
    GROUP BY person_id
) ps
LEFT JOIN (
    SELECT person_id, SUM( total ) AS total
    FROM person_total
    WHERE role_id = 8 AND kind_id IN ( 1, 3, 4, 5 )
    GROUP BY person_id
) pt ON pt.person_id = ps.person_id
LEFT JOIN name ON name.id = ps.person_id
LEFT JOIN person_status ds ON ds.person_id = ps.person_id

You can see EXPLAIN of the query here: http://pastie.org/private/tv9cp9y6pfa44glzynuowa

The query means:

  Get a list of all directors (role_id=8) I've seen at least one film from (movie, tv movie, video movie, tv mini-series), their total number of films for those categories, their names, and if I have them in my favorites/dislikes/watchlist.

The slowness of the query is JOINing the two subqueries. By slow I mean no patience to wait for it to return anything.

I've tried creating VIEWs for those subqueries but JOINing them is still equally slow.

Running those subqueries separately is very fast though, below 0.1 seconds.

This pattern is very slow:

FROM ( SELECT ... )
JOIN ( SELECT ... )

especially when both subqueries have lots of rows.  This is because there are no indexes to speed up the "join".  MySQL 5.6 will create the optimal index (on the fly), but you appear to be using an older version.

Suggest upgrading.


VACUUM FULL restored very small amount of disk space I have a large table with ~130m entries. I recently removed unnecessary entries and it should be left with ~60m.
I ran VACUUM(FULL) [table_name] and after it finally finished it restored only 2GB of disk space, while I expected it to be ~20GB. Also it seems that only 10m entries were removed after vacuuming. The VACUUM(FULL) finished successfuly and it had enough disk space.
What could cause this? Can this occupied space be recovered somehow?

If there is an open snapshot that can theoretically still see the deleted data, then that data has to be copied over into the new table by VACUUM FULL.  Check pg_stat_activity.