Cardinality between tables in the present or over time? I have a question regarding cardinality between two tables.
I'm not sure how to approach it.
Imagine the following:
There are groups: group 1, group 2, group 3 etc
One group can have multiple items and those items can be only in one of those groups.
A store can have only one group of items and one group can be in one store at a time, so you can say a store posseses a group. This being like this, group and store would have a cardinality of 1:1.
However, over time groups will change between stores, so every stroe will change the group it has, but groups wont repeat over stores. Sö, stores will interchange groups every now and then.
The problem is I'm not sure if it still is 1:1 or it can be considered as a 1:N, if you think it as a long term thing over time, more so if I want to register the date of the change.
Also, this whole thing results in a kind of ternary relationship, since stores can sell multiple items which at the same time are in a group and only in 1 group of items, but this groups are possed only by one store at a time, but, this stores will exchange groups over time. So this is making my head explode.

I think you might be missing an entity in your model. Over time a store can be associated with multiple groups and a group can be associated with multiple stores, so unless you only want to record the current state and no history you have a N:M relationship and need a junction table. Something like:
Store      StoreGroup                        Item
=====      ==========       Group            =============
Id   <---- StoreId          ==========       Id
           GroupId    ----> Id         <---- GroupId
           StartTime        GroupProp1       ItemProperty1
           EndTime          GroupProp2       ItemProperty2

You may need to enforce the "one store has one group at a time" rule in your application layer unfortunately, or using triggers, I'm not sure SQL constraints will be sufficient.
As an alternative to directly modelling changes over time, if the database you are using supports temporal tables (sometimes called "system versioned") you could use that feature instead.
Then you can have:
Store      Group            Item
=====      ==========       =============
Id   <---- StoreId          Id
           Id         <---- GroupId
           GroupProp1       ItemProperty1
           GroupProp2       ItemProperty2

and one group has one store is enforced by only being able to have one store in the data for a group, and one group per store can be enforced by a unique constraint on Group.StoreId. But I might recommend the other approach unless you can guarantee that the one-group-one-store rule will hold for future versions (it seems unusually limiting to my mind), as the former approach would be easier to refactor to allow the greater flexibility.


Is it possible to add a certificate to a database role? I have a certificate I am using for inter-DB access to a stored procedure in SQL Server, and am able to provide access to the stored procedure by creating a new user in the database using:

CREATE CERTIFICATE testCertificate
FROM FILE = 'test_certificate6.cer';

CREATE USER testCertificateUser
FROM CERTIFICATE testCertificate;

GRANT AUTHENTICATE TO testCertificateUser;
GRANT EXECUTE ON storedProc1 TO testCertificateUser;

but need to be able to add the inter-DB access to an existing database role (for compatibility with existing systems). How can I go about doing this?

No, it is not possible to create a Role from a CERTIFICATE or ASYMMETRIC KEY like you can with Logins and Users. It is also not necessary to do this nor would it even make sense to do this. And the reason that is doesn't make sense to associate a Role with a Certificate or Asymmetric Key is that this way of doing security does not work like the standard way of granting permissions.
In the typical / common way of granting permissions, you give permission to a User / Login / Role to do a particular action (Authenticate / Insert / Create / Update / Select / Execute / etc), often on a particular object (i.e. Schema / Table / View / Function / etc). Here you are allowing someone (or some process) to do (or not do) the action that they are requesting to do.
But Certificate- / Asymmetric Key- based security is not concerned with who is making the request, but what code is making the request. This is where ADD SIGNATURE comes in since it makes the association between the code making the request and what permissions that code has. But code (i.e. Stored Procedures, Scalar T-SQL Functions, Multi-Statement T-SQL Functions, Triggers, and SQLCLR objects via the Assembly) cannot be granted (or denied) any permissions directly. Hence you need to create a Login and/or User from the Certificate or Asymmetric Key to receive those permissions and act as a proxy for those permissions to whatever code has the same Certificate or Asymmetric Key that was used to create the Login and/or User. And Logins and Users created from Certificates and Asymmetric Keys cannot log in since they are merely vessels for those proxy permissions; they do not make requests as themselves.
So, if you want a Stored Procedure to access one particular DMV that requires VIEW SERVER STATE, but you don't want to:

grant that permission to a Login because it gives access to a lot of other DMVs, or
use the EXECUTE AS statement since it requires that the User / Login -- the one who should be restricted -- has the IMPERSONATE permission for whichever User / Login they need to "execute as". This leaves the door open to use EXECUTE AS to do other things beyond this desired action, or
use the EXECUTE AS Clause because you either do not want to change the actual execution context (you just want to grant additional permissions), or you need to do something that spans databases, or there is some other nuance of the EXECUTE AS clause that is causing issues,

then you would:

Create the Stored Procedure that SELECTs from that DMV

Create a Certificate or Asymmetric Key

Create a Login from that Certificate or Asymmetric Key

Grant that Login the VIEW SERVER STATE permission

Use ADD SIGNATURE with the same Certificate or Asymmetric Key on that Stored Procedure created in Step 1.
This final step ties the Login (having the correct permission) to that Stored Procedure. Now the Stored Procedure itself, not any Login or User executing that Stored Procedure, has whatever permissions were given to the Login that has the same Certificate or Asymmetric Key.

So, if you have a Role that was used to grant object permissions to for a particular database, then you should just need to add the Certificate-based User to that Role, just like you would add any other User to that Role to grant them the permissions assigned to that Role. This is similar to how you granted EXECUTE on the stored procedure to the Certificate-based User, just like you would for other Users / Roles.


Economic Database Architecture Possibilities w/ Data of Varying Length I'm in the process of designing a MySQL database (InnoDB) that holds a large amount of economic data which our application scores. The size of the economic data varies a great deal: some measures, such as percentage changes, are fractions of a percent; others, such as national debt figures, are 14+ digits long. Furthermore, we have a business requirement that states some data points need to be correct to the 6th decimal place.

We currently have over 500,000 rows in the legacy database, but anticipate growing that number substantially in the future, as we are building the new database with point-in-time considerations and, for the most part, will not be deleting or updating rows, only adding new rows and superseding the old rows.

All potential tables containing this economic data will be structured as follows:

id | country_id | period_id | [economic_data] | data_type | date_created | date_superseded

My question is this:

Is it best to :

Break out all of these individual economic data series into their own tables, given that the data comes in varying sizes, or
Combine all this data into one massive table, given the identical structure of all economic data tables and the simplicity it would offer for writing queries?

We collect over 200 data series and are planning on increasing that number every year, so option 1 would require creating and maintaining 200+ tables.

Option 2 seems the easiest to develop and maintain, but I wonder what the implications might be on query performance and storage.

Any thoughts or suggestions?

I would separate the problem into transactions and analytics -- based on the question, seems that you are trying to find a design which would be optimal for both.

From a design point -- on the logical level -- I would use something like this, and would not worry about number of tables. Also, each attribute has proper data type, etc.

From this you may periodically (daily) publish to structures which are more analytic-friendly (flat OLAP tables, data marts ...). Depending on the performance -- and user expectation -- exposing 5NF views may be good enough.

On the physical level, I am not so sure  :(

Structures like this are usually exposed to users as flat views (5NF) and via point-in-time functions. The main problem here is that the question is tagged MySQL. MySQL has a limit on number of tables that can appear in a join (61) and the query optimizer does not support table elimination; hence, forget the views. You would have to use the application level to "run-around" and join tables based on the ID and date; the application may be the ETL code that exports to analytic tables.

So, now it depends on how do you expose this to final users -- if they are supposed write custom queries this will not work.

It is a common approach to design a DB on a logical level without a regard for the target DB, but in this case the selection of the DB limits design options.


How not to have SQL Server to restart on the same node but failover on 1st failure in a cluster By default SQL Server tries to restart on the same node in case of a failure, next it does failover.

Personally I am good with the default behavior , but I am being asked a interesting question - is it possible to have SQL Server NOT try a restart on the same node and just failover in case of a failure?

And where is it done from, is it this:

In cluadmin-SQL Server Properties-Policies-

There is a option

  Max restarts in the specified period - Will making it zero do the needful?

Or there is some other way? Thanks.

By default SQL Server tries to restart on the same node in case of a failure, next it does failover.

Yes correct. Just to add, in Windows Server 2003 WSFC would attempt 3 times to bring resource online(By default) but from Windows Server 2008 it would just attempt 1 times( By default). Of course you can change the value. What i have said above is documented in This Support Article

  is it possible to have SQL Server NOT try a restart on the same node and just failover in case of a failure? Max restarts in the specified period - Will making it zero do the needful?

Yes I believe so, but this should not be done. I must also say I have never changed this option to zero, never seen it people using zero in their cluster configuration, and dont know how cluster would behave( when multiple failover happens) in case it is set to zero. You must test this before proceeding with the change in prod. In almost all cases at least one restart is necessary to check, because a failover can cause longer downtime( depending on storage and complexity) and downtime for all resources which are part of resource group even if it is not their fault.


Including a SQL file within a SQL query In Oracle SQL Developer, is there any way to include a SQL file within a SQL query? Like the command \include{myfile.tex} in LaTeX.

Example:

file1.sql contains FROM my_table;
file2.sql contains SELECT * \include{file1.sql}

Executing file2.sql from Oracle SQL Developer should be equivalent to executing SELECT * FROM my_table;.

In SQL*Plus, execute external scripts using @filename (http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#i1210918).

This also works in SQL Developer, just be aware of the differences between relative & absolute directory.


How can I detect the use of SQL Server replication on a server (all versions) preferably using PS or CMD I am trying to find a way to detect if a customer has SQL Server replication on. This helps decide whether they can use Express (as they frequently request) and this is one of the blocking factors.

Preferred responses would use a Powershell or command line script and would not require anything additional to be installed as this would need to work on many different server configurations. Also, the version of SQL Server would vary.

I do not need to know status or do monitoring, just check whether it is in use.

Windows/MSSQL are not my strong suit, so if there are any incorrect assumptions in this question, please feel free to point them out.

The goal is to login via RDP, open PS or CMD, paste in a command and know right away whether customer has replication and will not be able to utilize SQL Server Express (I know there are other factors, but this one is the first I am tackling).

As a quick note, I did do a lot of research on this before I came to ask. I found tons of information on monitoring replication, but nothing to just check if it was there. I wanted to put this to make sure nobody thinks I skipped the "rtfm" or "google it" steps.

You can use Replication Management Objects. You can interrogate them locally or remotely (the better option). The DLLs should be on some systems. If you still have known SQL 2000 instances please test them as it's unclear if modern versions of RMO will still talk down to SQL 2000 instances; 2005 and above should be more reliable.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo")

$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer(".")
if ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
    "Has Replication"
}

There are other flags you can check out. I should tell you that RMO is deprecated (much to my chagrin) but it still works for now.

Another option was to

Import-Module SQLPS -DisableNameChecking
Get-ChildItem SQLSERVER:\SQL\...(server)...\...(instance or DEFAULT)...\Databases | Where { $_.ReplicationOptions -ne "None" }

But SQLPS is part of the 2008 R2 Feature Pack or 2012+. So if you have a server like that you can run it remotely. However also this replication flag can be enabled for other reasons like CDC and so is usually a little misleading but in your case maybe it's just what you're after.


Is it possible to block the access of a super user? In a scenario that the databases run on the client's server.
Company "A" has a database installed on that server. The customer hires the services of a company "B", which will also install its database on the server.
If company "B" uses a super user, it can have access to the company "A" database. It may even try to harm company "A" by changing information in the database.
Is there a way to block super user access to company "A" database?

That should be easy. Put the databases in different PostgreSQL clusters on the same server.
But even if both databases are in the same cluster, a superuser can only access the database if there is a line in pg_hba.conf that allows it. So you can achieve the goal with a properly configured pg_hba.conf file. That requires that the superuser is known by name – you cannot add a line that says “block all superusers from that database”.


Select only one duplicate with different time column So I have data that is structured like this:

|ID | Reference| Name | Datetime |
 1       abc     John      2016-02-08 08:25:35
 2       abc     John      2016-02-08 08:25:37
 3       bnm     Eliza     2016-02-08 08:25:39
 4       abc     John      2016-02-08 08:25:42
.......
 351     cdf     Eliza     2016-06-01 15:25:42
 352     abc     John      2016-06-02 15:25:42

Due to an error in a logging system, which for the time being is outside my reach, I get several rows of data for what should just be one. I can have up to 20-30 duplicates within a 20 second timeframe. I want to select only one record of those duplicates where the time only differs by 20 seconds, but at the same time I want to include records, such as id 352, which would be excluded if I used a group by clause. I also have other records inside the that 20 second timeframe that I want to include, such as id 3, so I cant directly filter out records that are too close.

Is an option to make a kind of clustered column that combine Reference, name and datetime down to f.ex 20 second precision, and group by that?

I want to keep the duplicate with the earliest starttime.

You can achieve this goal by using the windowing functionlag and self joining the table onto itself.  I've included an example below.  You may need to tweak some of the code to get exactly what you want, but I think this comes really close.

The Setup

DECLARE @TestData AS TABLE
    (
    ID INT NOT NULL
    , Reference CHAR(3) NULL
    , RefName CHAR(20) NULL
    , RefTime DATETIME2(7) NULL
    );

INSERT INTO @TestData
(ID, Reference, RefName, RefTime)
VALUES (1,'abc', 'John', '2016-02-08 08:25:35')
    , (2, 'abc', 'John', '2016-02-08 08:25:37')
    , (3, 'bnm', 'Eliza', '2016-02-08 08:25:39')
    , (4, 'abc', 'John', '2016-02-08 08:25:42')
    , (351, 'cdf', 'Eliza', '2016-06-01 15:25:42')
    , (352, 'abc', 'John', '2016-06-02 15:25:42');

The Query

WITH CTE_FirstPass AS
    (
    SELECT T.ID, T.Reference, T.RefName, T.RefTime
        , LAG(T.ID) OVER (PARTITION BY T.RefName, T.Reference ORDER BY T.RefTime) AS PrevIDForReference
    FROM @TestData AS T
    )
    , CTE_SecondPass AS
    (
    SELECT P.*
        , PriorT.RefTime AS PriorRefTime
        , ABS(DATEDIFF(SECOND, PriorT.RefTime, P.RefTime)) AS PriorTimeDiff
        , IncludeData = CASE    WHEN P.PrevIDForReference IS NULL THEN 1
                                WHEN ABS(DATEDIFF(SECOND, PriorT.RefTime, P.RefTime)) <= 20 THEN 0
                                ELSE 1
                                END
    FROM CTE_FirstPass AS P
        LEFT OUTER JOIN @TestData AS PriorT ON PriorT.ID = P.PrevIDForReference AND PriorT.Reference = P.Reference AND PriorT.RefName = P.RefName
    )
    SELECT P.ID
        , P.Reference
        , P.RefName
        , P.RefTime
    FROM CTE_SecondPass AS P
    WHERE P.IncludeData = 1
    ORDER BY P.ID;


Prevent users from disabling FK checks in InnoDB In MySQL, it is possible to disable FK checks using something like the following:

SET FOREIGN_KEY_CHECKS=0;

Is there a way to prevent this, i. e. preventing users from disabling foreign keys?  I don't ever want to allow that or do it myself.

You can set triggers on the relevant tables to check for foreign_key_checks=0 and signal an error if that's the case.

Call this procedure to check for the setting and signal the error:

DELIMITER $$
CREATE PROCEDURE force_foreign_key_check ()
BEGIN
    IF @FOREIGN_KEY_CHECKS = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'foreign_key_checks is set to 0. Operation canceled.';
    END IF;
END$$
DELIMITER ;

And set your triggers like this:

CREATE TRIGGER force_foreign_key_check_insert_table1
BEFORE INSERT ON table1
FOR EACH ROW CALL force_foreign_key_check;

CREATE TRIGGER force_foreign_key_check_update_table1
BEFORE UPDATE ON table1
FOR EACH ROW CALL force_foreign_key_check;

CREATE TRIGGER force_foreign_key_check_delete_table1
BEFORE DELETE ON table1
FOR EACH ROW CALL force_foreign_key_check;


Is a manual log file grow procedure working different that autogrowth? I wonder if the internal methods of growing a file using

ALTER DATABASE testdb
MODIFY FILE
(
        NAME = testdb_Log
      , SIZE = 40960MB  --40 GB
)

work different then letting the file autogrow to the same size over time?

Background of my question:
I face very high (>10K) VLF counts on several Log files of different live DBs with full recovery that are sized around 100 GB.

While testing strategies to get this fixed, I resized a Log file to 16 MB having 4 VLFs and let it grow to 40960 MB then with an AUTOGROW setting of 100 MB. Afterwards I had 20 VLF in the file.

My expectation was to see additional

  (40960 MB - 16 MB) / 100 MB * (8 VLF per autogrow) = 3276 VLF in the
  file.

Instead I now have only 20. Why?

Second part of my question:
So far I understood that an inappropriate AUTOGROW setting cause the high VLF counts, that is defenitely the case in our dbs where this setting has not been looked after for a long period of time. All the articles explaining how to handle this situation advice to choose a proper value for AUTOGROW. But if I am right assuming that if the manual grow process (first part of this post) does not even use the autogrow setting, and I grow the log file this way to a proper size that does not require any automatic growing, then the autogrow settings is not that important at all afterwards?

Third part of my question?
Is the VLF count of 20 on a 40GB log file now too low? Is this the reason I read we should increment the file back up again in increments? What VLF count might be a GOOD (performant) number for a 40 G or 80 G log file?

VLF Calculations
The number of the VLFs is not different for a manual or automatic growth of the transaction log file.
SQL Server < 2014
Up to version 2014 of SQL Server the algorithm for the number of VLFs is as follows when you create, grow or auto-grow the TLog file:

Less than 1 MB : Complicated ignore
Up to 64 MB : 4 new VLFs
64 MB to 1 GB : 8 new VLFs
More then 1 GB : 16 new VLFs

SQL Server 2014 and higher
As of SQL Server 2014 and higher the algorithm is as follows:

Growth of TLog less then 1/8 th size of current TLog size?

Yes: Create 1 new VLF
No: Use the above formula (for previous version)

Your calculation for the number of VLFs should be correct.
Is your database set to AUTOSHRINK? Are you executing any other maintenance tasks that could be resizing the TLOG size?
Auto-growth: yes or no?
If you manually grow your TLog file then indeed you could turn off the auto-growth setting. But be aware: What will happen if you are unavailable and the database has to grow? The database will grind to a halt with an error message.
Baseline Size
At some point in time your database will have a TLog size that will have adequate space for the general imports that occur and the everyday workload and the TLog/Diff/Full backups occurring.
Use this baseline size as a starting point when you create a new TLog from scratch. If the database's TLog is really huge then create a TLog with a base size of 8 GB and manually grow in 8 GB steps until you have reached the "baseline" size.
Then leave some additional space on disk to allow for one or two "auto-growths" of 8GB size.
This will leave you with the best settings according to the article Transaction Log VLFs – too many or too few? where they recommend VLFs to be 512 MB in size.
Disk Space
If you don't have infinite disk space, then ensure that the TLog file has a maximum size set, that will not fill you disk to the maximum.
Monitoring
The most important point is to have a monitoring tool in place that monitors the database's "free space", because you never know if a rogue process is going to do things you hadn't anticipated.
Transaction Log Physical Architecture
There is a good article on TechNet that explains the physical architecture of the Transaction Log. This gives you some insight as to why the TLog rolls over or why it might have to grow.
References
Important change to VLF creation algorithm in SQL Server 2014
Initial VLF sequence numbers and default log file size
Transaction Log VLFs – too many or too few?
Transaction Log Physical Architecture


Does a subscriber node in pglogical "pull", or are updates "pushed"? I've configured a simple pglogical replication between two nodes.

What I've not found yet is if the subscriber node pulls data, or if the publisher node initiates a push of data.

I'm intending to have geographically distributed nodes behind firewalls, which at this stage (without doing something like a VPN) would mean I have to have the subscribers pull data.

The subscribers initiate the TCP connection to the publisher.


Informatica Source Qualifier not working from Windows ODBC data source (system DSN) from Excel xlsx file Reference:

https://community.informatica.com/message/62128

Windows ODBC Workflow:

Windows > Control Panel > Administrative Tools > Data Sources (ODBC) > click "System DSN" menu > click "Add" > choose "Microsoft Excel Driver (*.xlsx) >

Data Source Name: "aaaaaaaaa"

Version: Excel 12.0

click "Select Workbook" button > choose xlsx file

Informatica PowerCenter Designer Workflow:

Sources > Import from Database >

ODBC Data Source: "aaaaaaaaa"

click "Connect" button > the button's name changes to "Re-Connect"

Error:

.. it doesn't seem to find the data in the worksheet ... I have 3 columns with 10 rows of data

To use an Excel file as a source you need to define a named range containing the cells with data (that's Step 1 of the instruction you referenced in the question).

All the named ranges found in the file will be shown in the Select tables pane. After you select a table (i.e. a range) and click OK, a new source will be created.

You can also consider converting the Excel spreadsheet to the CSV format and using simple flat files as a source. If you're in a Windows environment and have have Excel installed, there's a VBS script that can help you automate this process.


Optimization causes tmpdir to fill up with 'deleted' file handle Using Percona 5.6.34-79.1. Storage Engine is InnoDB.
One of the table to be optimized is 300Gb in size.
tmpdir is a separate partition, and datadir is separate.

datadir has 500Gb of space, so optimize should work (taking its time).
tmpdir has less space than table size.

Its a standby slave, so no query of any kind is hitting this db instance, so tmpdir partition is un-used. Before I run any query, if I do lsof | grep -i deleted, I don't see any thing related to mysql.

Once I fire the optimize command, the tmpdir starts to fill up, and doing lsof | grep -i deleted shows large number of deleted but yet held files related to mysql.

Also on the datadir, the new file by the name #sql-ib4586-1246909784.ibd forms but remains in few kilobyte in size.

Normally tmpdir should not fill up and the new file (#sql-ib4586-1246909784.ibd) should increase in size taking up datadir space. Why is that not happening?

Short answer:  Can't be done.

Long answer:  There is a glimmer of hope...

Assuming that the optimized table will still be more than 200GB, you cannot successfully run OPTIMIZE TABLE in the small space you allotted.  Are there other things you can delete?  Can you OPTIMIZE the smaller tables first - that might free enough room.  I assume you have innodb_file_per_table=ON?  How big is ibdata1?

Please provide SHOW TABLE STATUS LIKE 'tablename'.

Please explain why you want to run OPTIMIZE.  It is rarely worth doing for InnoDB tables.


SQL server Login failed for user My MS Access Application uses SQL Database as backend.
The permission to SQL database is granted with Windows Authentication mode to a specific user group.
Most of users in the group can get access to SQL database but there are some users reported to fail for login in the database.
The error message:
Connection failed
SQLstate: '28000'
SQL Server Error: 18456
Login failed for user Domain\UserID

I spent sometime searching for the solution but I could not find one with the same problem.
Could anyone give me an advice on this?
Many thanks.

To fix it you need a State of error message - explanation why it failed. It could be found in error message or SQL Error log if failed logins added
Login failed for user - Error 18456 - Severity 14, State 38


Oracle, Create table with inline constraint declarations using a different tablespace I have two questions about inline constraints declarations for Oracle tables:

Is it a bad practice? If so, why?
How could one declare a different tablespace for the primary key and index like it's done when using the outline declaration? Something like

    create table THIS_TABLE (
        id number,
        constraint THIS_TABLE_PK (id) tablespace INDEX_TABLESPACE
    ) tablespace DATA_TABLESPACE;

Like this:

create table THIS_TABLE (
  id number NOT NULL,
  constraint THIS_TABLE_PK PRIMARY KEY(id)
             USING INDEX TABLESPACE INDEX_TABLESPACE
) tablespace DATA_TABLESPACE;

USING INDEX TABLESPACE is the syntax - you weren't far off.

As far as good/bad practice is concerned, that's opinion-based, so not really something that should be asked here. The alternative is to use the ALTER TABLE .... syntax after table creation.


SQL Server - backup restore - lose indexes? It looks like at a client site, after a database backup and restore (from SQL Server 2008 to SQL Server 2012), it looks like they have lost indexes from the tables and we had to manually create them. We have seen two instances of this.

I thought backup and restore keeps all the objects intact. Is this possible? Is there a setting that actually lets users omit objects during a restore?

There is no setting that allows indexes to be omitted from the backups or restores.  Such a feature would be nice as it would result in much smaller backups, but Microsoft states that there are many hurdles to adding such a feature (see here).

It's most probable that the indexes did not exist when the backup completed.  There may be an ETL job that removes indexes before a data load, and then creates the indexes again after the load is complete.  Look for other types of activity that occur on the server leading up to the backup window.  If it were such an ETL job doing this, for example, some loads may be taking so long that they aren't complete when the backup ends.  So you'd have a timing issue where sometimes the backup has the indexes, and sometimes it doesn't.

It's also possible, but I would expect you'd know if this is the case, that the backup was actually created on another server that had a copy of the database, and the backup job drops indexes first to make the backup smaller.  This would not be a hit or miss situation as you've reported.

You can also read  here  for another way to accomplish this using filegroups, but again, this wouldn't be hit or miss as you've reported.


What does enable mean in Oracle DDL constraint? My understanding is the different between

NOT NULL

and

NOT NULL ENABLE

is that the later will also be be applied to existing data in the table.  If then you are starting from scratch is there any point in doing

NOT NULL ENABLE

Thanks

not null is a constraint. The enable that follows it is optional if you want that constraint to be active (which is the default). So

NOT NULL
NOT NULL ENABLE
NOT NULL ENABLE VALIDATE

all mean the same thing (validate is also the default, novalidate being the other option), so there really isn't an "advantage" in specifying it fully (except perhaps for documentary purposes).

If you wanted that not null constraint to be disabled, you could say:

NOT NULL DISABLE -- equivalent to NOT NULL DISABLE NOVALIDATE

or

NOT NULL DISABLE VALIDATE

See the Specifying Constraint State docs for the details and use cases on this topic.


What's the difference between partitions and many tables? PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site:

  "Partitioning refers to splitting what is logically one large table
  into smaller physical pieces"

And the benefit are mainly performance gains in querying/deleting. But if we create many tables based on the partitioning criteria, wouldn't the benefit be the same?

I had a DB with 10K tables to store financial data, which worked fine for me, except for slow loading in pgAdmin and later a foreign key issue that made me struggle since it's a huge pain to update a foreign key. So I decided to rebuild the DB with one or two big tables. Then this new feature gave me hope again. I'm not sure if it's so good. What's the major difference between multiple physical or logical tables?

EDIT:
   I studied Pg11 partition for a while. It seems it's a good solution for my DB need. But, the question is even more annoying to me.
  As I undersatand, partitioned tables are stored separately, I suppose there would be some internal "index/planning" in paritioned tables. So it would be slightly faster than multiple(<10k) physical tables. Then while partitioned tables could have partitions as well, it's even more interesting and that's what I wanted actually.
  Then I found this post "https://stackoverflow.com/questions/6104774/how-many-table-partitions-is-too-many-in-postgres" saying several hundreds partitions are proper. I will assume he's talking about earlier version and I believe PG11 should be better which I'm not sure. And I saw from another post that more than 1000 partitions would slow query down due to planning which I think should more or less improved in PG11 too.
   Anyway, for now, I'm sure partition is what I'm looking for but I'm going to try a  10k paritions(with sub-partitions). Hopefully I'll be lucky enough to get a good result.
   BTW, pgAdmin4 slow down somehow is a common problem for earlier version and latest 4.4 more or less fixed this, based on my search in stackoverflow. So probably 10k physical tables are not that bad, except for foreigh key update, which I decided not to use anymore.

The key difference between having many smaller tables and having one partitioned table is that the partitioned table can appear as a single table in SQL statements. You don't have to mess around with views and triggers (the latter are probably also less efficient), it is all handled in core.

However, you shouldn't have too many partitions, else query planning will become too slow, and you will suffer from all the other problems that probably made you abandon a design with tens of thousands of tables.

Maybe you can use partitioning to your advantage if you manage to split your table into fewer partitions.

Partitioning is a comparatively new feature in PostgreSQL, and v11 has many notable improvements in this area. So if you want partitioning, use v11 by all means.


How bigger page size effect at performance? I know in case of Cluster index bigger page size improve peformance for range and multipoint query.

Does bigger page size can effect to degrade performance when we have non cluster index .Because normally rows are not existed near each other. In this situation, big page size read more rows which it does not need to read .

Same case for  Write opeartion (insert and update) .Normally we need to update and insert sigle row .

Does one IO take same time for 4K and 16 KB ?

For bulk operations in index order a larger page size can give a noticeable, sometimes significant, performance increase. For very random access, particularly many small writes, there is similar potential for significant performance reduction.

A single read of a 16K block will take four times longer than the read for a 4K block, assuming the drive heads are in the right place at the time, but reading four sequential 4K blocks will be almost the same as reading a single 16Kb one.

Unfortunately it isn't something we can give you more precise advice on, especially without a lot of information about your application's I/O patterns - it is something that you probably need to benchmark with your specific application in your test environments to be sure about.

4Kb is usually picked for the default (at least on x86 or a64 based CPUs) as it matches pages sizes in the underlying OS and sometimes hardware so in the general case is more efficient because it is the "natural size". DB specific considerations override this though, which is presumably why MS SQL Server uses 8Kb page sizes.


Is it a good idea to add a reserved field to a large MySQL table? As you may know adding a new column to a MySQL table with millions of records is  hard and in some cases impossible.

Is it a good idea to add a text column to each table (say, meta) to save some headache in future? Of course, they are empty in most cases.

These data can be stored as JSON. I know this field can not be accessible easily but it can avoid another join and it's important for this performance-sensitive app.

It uses InnoDB.

I echo the "bad form" comment of @JohnM - design the thing properly, and if you have new requirements (or your design isn't perfect first time - unlikely I know :-) ), then choose to add new fields. Use JSON if it suits your clearly demonstrated requirements, otherwise stick with "normal" field types.

I've seen too many systems where these "spare fields" get used for anything and everything and it ultimately becomes an undocumented, alphabet soup of an unmaintainable mess.

"Hey Jimmy, what does field XYZ__001 do?"

"I don't know, ask Billy"

"But Billy's on holiday in the Algarve"

"Ooops!'


Oracle 11g alternative options for oracle 12c with function I am using (with function) in Oracle 12c. in 11g this is not available. Is there any other way we can implement with function in 11g?

with
  function x(p_NUM in number) return number
  is
    n number;
  begin
    SELECT 1 INTO N FROM DUAL;
    --
    return N;
    --
  end ;
  --
  function Y(p_NUM in number) return number
  is
    N1 NUMBER;
  begin
    SELECT 2 INTO N1 FROM DUAL;
    --
    return N1;
    --
  end ;
--
select X(1), Y(1)
from dual;

Create the functions within a PACKAGE.

Beyond that, you are using a feature that was introduced in 12c.


Merging multiple client-specific databases into a single one We have multiple databases as various clients. All the databases have the same schema (or structure). Now we want to merge the databases into one. We DON'T want to merge their data. We want a new design that allows multiple clients while retaining as much of the original schema as possible.

Is the simplest way to do it as follows?

Create a client table
On each of the other relevant tables add a foreign key constraint referencing the client table

Note: The database schema ISN'T complex at all.

Yes, that's normally how it is done.

Note that this is a non-trivial application change, pretty much every query interacting with the database (inserts, updates, selects) is going to have to include that ClientID.

Depending on what else you're storing, some tables might not need a ClientID. To make up some examples:

If your products table is available to all clients, that won't need a ClientID
A child table (like OrderDetail) might not need a ClientID if its parent record (the Order table) already has a ClientID.

Please see Brent's excellent post about some additional tradeoffs/consideration of this design.


Should a filtered index theoretically perform faster in this example? I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown".
It also has columns called "MovieId" (primary key) and "MovieTitle".
(There are also other columsn in SuperheroMovies, which we can call OtherField1...OtherField25.)

The clustered index is on the primary key MovieId.
There's also a nonclustered index IX_MovieTitle on the table for the MovieTitle column.

This is the query that will be ran the most against the SuperheroMovies table:

DECLARE @MovieTitlePrefix VARCHAR(50) = 'F'; -- This can be any single character from A to Z

SELECT MovieId, MovieTitle, UniverseName, OtherField1, OtherField2, OtherField3, ROW_NUMBER() OVER (PARTITION BY UniverseName ORDER BY MovieTitle) AS SortId
FROM SuperheroMovies
WHERE MovieTitle LIKE @MovieTitlePrefix + '%'
    AND UniverseName <> 'Unknown'

If a nonclustered index IX_MovieTitle_FilteredOnUniverseName ON SuperheroMovies (MovieTitle) WHERE UniverseName <> 'Unknown' was created and used in the query instead of IX_MovieTitle, should it perform faster?

I would still need the filter on UniverseName <> 'Unknown' in the query with IX_MovieTitle_FilteredOnUniverseName to achieve the same result set, right?

It would be at least a little faster, and also use less disk space, depending on how many rows there are with UniverseName = 'Unknown'.

The original index would allow the query to seek to the correct movie title starting character, and apply a residual predicate to each row to make sure it's not from an 'Unknown' universe.

The filtered index would still seek to the movie title starting character.  But it wouldn't have to apply the residual predicate (it already knows none of those rows will be present).  And it also might have to read less data pages, if there are a lot of rows with UniverseName = 'Unknown'.

Also, you should probably include UniverseName in the index definition, for the reasons outlined here: Should the filtering column(s) always be in the keys / includes?

Sidebar: the variable with string concatenation will likely result in a dynamic seek plan, unless you include a RECOMPILE hint on the query.  This may or may not matter for your use case, but is something to be aware of.


Concurrent backups in MS SQLServer 2008 R2 Can anyone please point me to a piece of documentation that suggest how many concurrent backups initiated by different BACKUP statements are possible in Microsoft SQL server 2008?

The documentation suggests that you can specify up to 64 different backup devices in one statement, but I have noticed today that SQL server was not treating my BACKUP statement while the one initiated by SQL Server Agent was running.

I have been closely observing the Performance Monitor and could tell that at one time only one .bak file was being written to.

Another proof of that would be a screenshot I have with SQL Management Studio query running time being 15 minutes, and the actual query result stating:

  BACKUP DATABASE successfully processed 3422697 pages in 389.656
  seconds (68.624 MB/sec).

So, how many simultaneous backups is SQLServer meant to handle (if any)?

SQL Server backups are serial on a per-database basis.

This page gives you which administrative tasks can and cannot be run concurrently Concurrent Administrative Operations.


Installation SQL Server 2012 - “Ready to Install” page missing I need to create config file, so I could install sql server from cmd. I need basic server installation so I've downloaded SQLEXPR32_x86_ENU

I need to go through whole installation process til page "Ready to Install", where should be link to config file I need. BUT, there is no such thing in my installation setup window.

On microsoft webpage there is: http://i.msdn.microsoft.com/dynimg/IC505108.png

Although, the screen from MS is sql-server 2008.. How to get config file from installation sql-server-2012?

Here's my screen:

Ok, I found the solution...

When you have SQL Server 2012 EXPRESS edition, and you want to get ConfigurationFile.ini from your installation process, you have to:

1) Run downloaded exe, which will unpack your setup.exe to C:/231231231231...sth

2) Cancel installation. Go to that dir via cmd.

3) In cmd run:
setup.exe /ACTION=INSTALL /UIMODE=Normal

Express edition is run in AutoAdvance which skips "Ready to install" and ConfigurationFile in general.. not sure why.


Catalog of LinkedServer2 from Server1 shows databases on Server1 I'm on Server1 and set up a linked server to LinkedServer2, but the catalog shows the databases on Server1!

I am sure I've done something bone-headed or am overlooking something horrendously obvious, but I need a nudge in the right direction.

Server1: Enterprise SQL 2012
LinkedServer2: Standard SQL 2008 R2

Check the definition of the linked server by running:

SELECT s.name
    , s.product
    , s.data_source
    , s.location
    , s.catalog
FROM sys.servers s

Almost certainly, the linked server is connected to server1, which will likely be confirmed in the contents of the data_source column in the query above.


Unable to drop the table since its showing waiting for meta data lock We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on that particular table. Does anybody know how to resolve it?

If an InnoDB table is being accessed at all via SELECT or DML (INSERT, UPDATE, DELETE), you should rightly expect a metadata lock.
According to the MySQL Documentation on MetaData Locking:

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

That being said, you should determine whether or not another DB Session is holding locks on the table. If such a session is an uncompleted transaction, there is where the hold up may be.
If you simply need the diskspace back quickly, you could run TRUNCATE TABLE.
SUGGESTION
Try renaming the table before attempting to drop it as following:
ALTER TABLE mydb.mytable RENAME mydb.mytabletodrop;
TRUNCATE TABLE mydb.mytabletodrop;
DROP TABLE mydb.mytabletodrop;


Perfmon Counters that relate to SQLIO I am trying to benchmark a new virtual SQL server.  this will be the first time I have virtualized a production SQL server.    I don't think this is a supper busy server.   100-200 users, of varying activity.

I have been capturing statistics from the current physical server for quite a while.

I am running SQLIO on the new server.  The full scope, read/write random/sequential,  2-64 Threads 1-128 outstanding requests.  I captured 5 minute runs with 1 minute pauses in between against a 20GB dat file.
...none of this is really relevant to my question.

I am able to capture the physical disk counters and I can see the latency (avg disk sec/read or write) statistics, that match. I am also checking out the (avg disk byte/read or write) to see how much activity SQLIO produces.  It produces way more disk activity than our production server.

My question is, are there counters I can also grab, to verify the thread and outstanding request options in SQLIO?  I have not been able to find a clear answer.  What I am wanting to do, is look at the statistics from our production server, and gauge what SQLIO settings are most representative of our actual server.

SQLIO is really stress testing the storage system,  and I would like to be able to set it in a way, so that it is a real representation.

I am not sure how to identify the real (avg) thread or (avg) outstanding requests of the current physical server.

Thank you for your time,

SQLIO is technically deprecated - it's been replaced by the newer utility Diskspd. However, the same basic answer applies.

If you look at an existing server and guess how much storage throughput it's USING, then look at the counters:

IOPs - Physical Disk Reads/Sec and Writes/Sec
IO size - Physical Disk Read Bytes/sec divided by Reads/sec (and same for writes)
Latency - Physical Disk Avg Sec/Read and Sec/Write

However, this only reflects what your current storage is able to deliver, not what your server would rather have. Think about it as drinking water through a small straw. If I ask how fast you want to drink, and I measure your consumption through that tiny straw, it doesn't tell me how fast you could actually drink if you weren't constrained by such a small straw. Instead, I need to hook you up to a pipe (or straw) with unlimited throughput, and then measure how much you actually consume - and then use that to size the storage you need. (Or want, anyway.)


Migrate large postgresql database (windows -> linux) I have a "large" PostgreSQL (9.6 with timescale plugin) database (about 1Tb). And would like to migrate it to a Linux machine. The naive approach would be pg_dump and pg_restore.
(Not sure how long that might take on a powerful machine with SSD on both sides, maybe you have an idea?)

Just wanted to ask if this is the way to go or if there might be a better more convenient solution. What I saw so far were pgBackRestore and barman.

I'm asking this question since I already had some trouble migrating a MongoDB database which took really very long. So now I would like to avoid errors : )

Thanks a lot for any hint!

With the directory format (-F d) of pg_dump, you can parallelize dump and restore, which will help if there are several large tables.


Identifying blocking queries on MySQL 5.5 For an application using MySQL 5.5.60 under Debian, lately we experience transactions with database writes, which do not succeed within the 50s time limit of the innodb_lock_wait_timeout setting.

I need to identify the transactions / statements which hold those locks, thus the cause, to start resolving the issue.

My hope was to get this information from the MySQL side, but so far it seems embarrassingly difficult to identify the blockers.
Best information was in the TRANSACTIONS section from the report given by a show engine innodb status statement, e.g.:

---TRANSACTION 6FEE8DD5, ACTIVE 13021 sec
1314 lock struct(s), heap size 129464, 1599 row lock(s), undo log entries 1212
MySQL thread id 16165411, OS thread handle 0x7f1491f53700, query id 1056480972 host.domain.tld 217.69.64.139 username
Trx read view will not see trx with id >= 6FEE8DD6, sees < 6FEE0FE9

Though I do not see any SQL statement here.

For unknown reason such a transaction does not show up in the MySQL slow query log (or processlist, innotop, mytop etc.). And if it would show up there, it will not list the MySQL thread id or the query id.

The thread id seems to be listed in the MySQL global log, which I dared not to activate during business hours, due to the mass of information it has to store.

Is there a better approach?

My only hint for the invisibility so far is that multiple statements might be displayed differently.

  Note transaction can be active even if the connection is in “Sleep” stage – if it is multiple-statement transaction.

(Source)

Can this be the case? And how would one identify /debug such a source for locking?

This query may assist in your search for locking and blocking ids.

# IS-tx-wait-block-qrys.sql to help research BUSY systems Last Updated 20191227 wh
# if this helps you, please email SHAREABLE comments to info@mysqlservertuning.com
# From article at
# https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-server-activity/
# 20191227 tested on MariaDB 10.3.10

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;


Check if query will cause duplicate row I have a database with a primary key on the first column (words)
I want to trim the trailing and leading ends of my data, but I get an error that this causes duplicate data

EX: ' David ' will become 'David' and that is already in the database

Is there anyway to compare what I already have in the database to my Select statement (without spaces) and remove any that will cause a duplicate row?

SELECT Func(PK),count(*)
FROM tab
GROUP BY Func(PK)
HAVING Count(*)>1 ;

Where Func() is whatever you're using to clean up the PK column spaces

Example:

SELECT LTRIM(RTRIM(PK)),count(*)
FROM tab
GROUP BY LTRIM(RTRIM(PK))
HAVING Count(*)>1 ;

Example2: (as suggested by Martin in the comments)

WITH cte AS
(SELECT ROW_NUM() OVER (PARTITION BY LTRIM(PK) ORDER BY LTRIM(PK)) as rn
FROM tab)
DELETE FROM cte where rn > 1;


How many nodes can you install SSRS on when using basic availability groups? I have migrated a SQL Enterprise standalone SSRS installation to a new SQL Standard 2 node Basic AG recently.  I got the expected "scale-out-deployment is not supported error", and I resolved that by removing the old server's information from the keys table in the reportserver DB. I now have just 2 lines in that table, a NULL machine name and Node1.  the report server works perfectly fine on Node1 right now, but when i fail over to node2 I get the "scale out feature is not supported" error when I go to the web service URL link(http://Node2:80/Reportserver), but NOT when i go to the web Portal URL link (http://node2:80/Reports).  That gives me access to the reports fodlers, but when I try to launch a report I get the Scale out error.

If I restore the encryption key, it works fine, despite having already restored the encryption key previously.

Can I not have SSRS installed on both nodes of a basic availability group?  Should it be installed on Node1 only?  If so, will I still be able to access the shared reportserverDB?

This is my first attempt with basic availability groups, I didn't run into this issue at all with my last enterprise install since I was using scale-out deployment.

Just had another thought...  the source DB that the reportserver is pulling data from is in a different AG since basic AG's can only have 1 DB in them.  if that DB is on a different node then what reportserver and reportservertempDB are running on, could that be a problem?  would the source DB need to be failed over as well?

A SSRS scale-out deployment is an Enterprise Edition ONLY feature.

I don't like providing link-only answers, but this alternative approach outlined by Rodney Landrum, is much too long to copy/paste into an answer, and I suspect his blog entry will be around for quite some time as it's hosted by Red-Gate.

Long story short, you can't approach this via a scale-out deployment, but there is an alternative solution available if you don't mind some additional administration and automation required to make it happen.


What do columns with a "dot" mean when listing Sql Server user permissions using sp_helprotect? I have a DB that I did not setup and I used sp_helprotect to list the permissions, the output is as in the image below.

I don't understand the dot's in the Owner, Object and Column columns.  And I can't find a similar example in the few db's I have.

What to they related to or what do they mean?  I assume not applicable or the like, is that correct?

And how can I set permission like this on another DB ... I'm trying to setup another db with a user that has the same permissions.

.

Stop using sp_helprotect - it's bad news, mostly because it hasn't been updated since before SQL Server 2005 was released. Instead, you can generate the script you need based on sys.database_permissions and possibly sys.database_role_members. For example, let's say you have a user named foo and you want to apply the same permissions as foo to a new user you just created, bar. You can see the permissions foo has (at least for the database itself, tables, views, columns, procedures and functions) and the role(s) they're in using the following script:

DECLARE @pid INT;

SELECT @pid = principal_id FROM sys.database_principals
  WHERE name = N'foo';

SELECT r.name
FROM sys.database_principals AS r
INNER JOIN sys.database_role_members AS rm
ON r.principal_id = rm.role_principal_id
WHERE rm.member_principal_id = @pid;

SELECT p.state_desc,
 p.[permission_name],
 [oschema] = QUOTENAME(s.name),
 [object]  = QUOTENAME(o.name),
 [column]  = QUOTENAME(c.name),
 tschema   = QUOTENAME(ts.name)
FROM sys.database_permissions AS p
LEFT OUTER JOIN sys.objects AS o
ON p.major_id = o.[object_id]
AND p.class_desc = N'OBJECT_OR_COLUMN'
LEFT OUTER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
LEFT OUTER JOIN sys.columns AS c
ON p.major_id = c.[object_id]
AND p.minor_id = c.column_id
LEFT OUTER JOIN sys.schemas AS ts
ON p.major_id = ts.[schema_id]
AND p.class_desc = N'SCHEMA'
WHERE grantee_principal_id = @pid;

So now to generate those scripts to apply to bar:

DECLARE @pid INT, @sql NVARCHAR(MAX) = N'', @trg SYSNAME = QUOTENAME(N'bar');

SELECT @pid = principal_id FROM sys.database_principals
  WHERE name = N'foo';

SELECT @sql += CHAR(13) + CHAR(10)
  + N'ALTER ROLE ' + QUOTENAME(r.name)
  + ' ADD MEMBER ' + @trg + ';'
FROM sys.database_principals AS r
INNER JOIN sys.database_role_members AS rm
ON r.principal_id = rm.role_principal_id
WHERE rm.member_principal_id = @pid;

SELECT @sql += CHAR(13) + CHAR(10) + N''
  + CASE p.state WHEN N'W' THEN N'GRANT' ELSE p.state_desc END
  + ' ' + p.[permission_name] + ' ON '
  + COALESCE(
    N'SCHEMA::' + QUOTENAME(ts.name),
    QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
      + COALESCE('.' + QUOTENAME(c.name), ''),
    N'DATABASE::' + QUOTENAME(DB_NAME())
  ) + ' TO ' + @trg + CASE p.[state] WHEN 'W'
    THEN N'WITH GRANT OPTION' ELSE '' END + ';'
FROM sys.database_permissions AS p
LEFT OUTER JOIN sys.objects AS o
ON p.major_id = o.[object_id] AND p.class = 1
LEFT OUTER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
LEFT OUTER JOIN sys.columns AS c
ON p.major_id = c.[object_id]
AND p.minor_id = c.column_id
LEFT OUTER JOIN sys.schemas AS ts
ON p.major_id = ts.[schema_id] AND p.class = 3
WHERE p.grantee_principal_id = @pid
AND p.class IN (0,1,3); -- database, object/column, schema

PRINT @sql;
-- EXEC sp_executesql @sql;

Result will be something like this (now, PRINT has a limit of 8K, so you may not see the whole script in PRINT's output, but it's all there), but obviously not exactly:

ALTER ROLE [db_owner] ADD MEMBER [bar];
GRANT CONNECT ON DATABASE::[wp] TO [bar];
GRANT DELETE ON [dbo].[repro] TO [bar]; -- table
GRANT SELECT ON [dbo].[whatever].[a] TO [bar] WITH GRANT OPTION; -- column
DENY UPDATE ON [dbo].[whatever].[b] TO [bar]; -- column
DENY ALTER ON [dbo].[x] TO [bar]; -- procedure
GRANT EXECUTE ON [dbo].[x] TO [bar]; -- procedure
GRANT SELECT ON SCHEMA::[y] TO [bar];

Your goal is actually to apply the same permissions to a similar user in a different database, so you may need to adjust the target user (@trg) and replace DB_NAME() with the hard-coded name of the target database.

I am not sure if there are any cases where these permissions need to be applied in any sort of dependency order. I'm also not sure about other types of permissions that may interfere here (hence the WHERE clause - you can see the full set of class values in the documentation) - you may need permissions on things like TYPE, ROUTE, SERVICE_CONTRACT etc. You can expand the above script to cover those if necessary.


Delete with subquery with no unique columns I have a table LIKES (ID1, ID2). Both id1 and id2 are non unique columns.

Database is SQLite

delete from LIKES where ID1 = 10   /// Wrong there more then one record with ID1=10
delete from LIKES where ID2 = 's ...'  /// Wrong there more then one record with ID1=155
delete from LIKES where ID1 = 10 and ID2='s ....' /// right query but i have to find out
             values of id1 and id2 before to make run this query

delete from LIKES where id1=m and id2=t (select m, n  from table where ...some condition)

The last query does not work

Try using an EXISTS operator in your WHERE clause. I am not 100% of the exact syntax for sqlite but in SQL Server I would do something similar to the following:

DELETE from likes
WHERE  EXISTS (SELECT *
               FROM   table
               WHERE  likes.id1 = table.m
                      AND likes.id2 = table.n
                      AND your other conditions )


DB2 9.7 LUW - How do I SET SESSION_USER when I am DBADM,SECADM? Based on a previous question I have asked, I am attempting to work on a solution as recommended by mustaccio's answer, whereby I have another ID to create my DDL objects.

I have been trying to follow an IBM developer works article called "DB2 security, Part 8: Twelve DB2 security best practices" in my attempt to do it. I have noticed it appears the article was written for DB2 8.2 at the time. I am currently working on DB2 9.7 fix pack 4 ESE for LUW. Specifically I am on AIX. I am currently testing out how to do this on my local system, which is Windowx XP SP3 and using DB2 9.7 fix pack 4 Express-C for LUW.

I have my local user account (which is the instance owner) called db2admin in this case (not so on our AIX systems). This ID belongs to the DB2ADMNS group. Since it is different from my Windows account I make sure I attach to the instance first and then create my database to assure that db2admin is granted DBADM,SECADM,DATAACCESS,ACCESSCTRL on my database.

db2 attach to DB2 user db2admin using ********
db2 create database SECTEST automatic storage yes on 'C:\' dbpath on 'C:\'
  using codeset UTF-8 territory US collate using system

After that I connect to the system as db2admin

db2 connect to sectest user db2admin using ********

Then to test things out, I made sure I granted CONNECT and SECADM to my Windows account

db2 grant connect,secadm on database to user myid

And then I wished to set up my bogus ID to create objects under. I intentionally create this ID as nine characters to help with preventing connection to DB2 from the command line, but still allow it via SET SESSION_USER. For the sake of simplicity I have named my ID bobabob97.

So I have done the following:

db2 grant CREATETAB,IMPLICIT_SCHEMA,BINDADD,CREATE_NOT_FENCED_ROUTINE,
  CREATE_EXTERNAL_ROUTINE on database to user bobabob97
db2 grant use of tablespace userspace1 to user bobabob97

At this point I think I have everything set up correctly. I have explicitly not granted bobabob97 CONNECT on the database as I do not want anyone to be able to connect to the database with this ID.

So now I try to switch over to that ID to create test creating my objects and owning them under a generic ID.

db2 set session_user=bobabob97

But then I get the following error:

  DB21034E  The command was processed as an SQL statement because it was
  not a valid Command Line Processor command.  During SQL processing it
  returned: SQL0552N  "DB2ADMIN" does not have the privilege to perform
  operation "SET SESSION AUTHORIZATION".  SQLSTATE=28000

So, now I am puzzled. The ID I am currently connected to the database as (db2admin) has DBADM,SECADM,DATAACCESS,and ACCESSCTRL on the database (and of course it implicitly holds CONNECT,CREATETAB,BINDADD,QUIESCE_CONNECT,IMPLICIT_SCHEMA,LOAD,CREATE_NOT_FENCED_ROUTINE, and CREATE_EXTERNAL_ROUTINE). I am essentially "god" on the database. Why can I not switch to this ID?

I tried playing with GRANT SETSESSIONUSER, but I know SECADM can't grant to itself. What do I do here?

Ok, I think I actually figured out how to handle this. I needed to grant the ability for my DBADM,SECADM to transfer to any ID through the group not the specific ID. In this way I get around the SECADM restrictions.

db2 grant setsessionuser on public to group DB2ADMNS

Since my ID db2admin is in this group, it can now switch to any ID (the public part). If I only wanted to be able to switch to that ID, I could have used:

db2 grant setsessionuser on user bobabob97 to group DB2ADMNS

So now I can perform

db2 set session_user=bobabob97

and it works.

To switch back I only need to run

db2 set session_user=system_user


Zero Rows return on querying DMV sys.dm_os_performance_counters I have a user on SQL Server 2014 Standard Edition (RTM) with SYSADMIN role with Server View State Permissions but when I execute DMV sys.dm_os_performance_counters it returns no record.

Any idea what is wrong with permissions?

Output of @@Version:

  Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation Standard Edition (64-bit) on
  Windows NT 6.3  (Build 9600: ) (Hypervisor)

If you're sure the user in question has View Server State (and it looks like in your screenshot he does).

Then there are a number of reasons previously put into an msdn blog. Ranging from:

Performance Objects and counters set-up during the SQL Server installation failed.
A mixture of 64 and 32 bit platforms.
Registry permissions have been skewed

To resolve this we can use the same steps outlined in the guidelines for reinstalling the performance counters in a different stack-exchange post:

Using an elevated administrator command prompt perform the following steps.

Change the path to the BINN directory of the SQL Server instance you desire to correct.
(Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)
Execute unlodctr <<REGISTERED SERVER NAME>>
For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008 ...
Execute lodctr /T:<<perf-sql* matching the counters you desire to load>>
For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent. The /T is important to load the SQL Server performance counter provider as a trusted provider.
Cycle the remote registry service:
net stop "Remote Registry" then net start "Remote Registry"
Force a WMI synchronization using winmgmt /resyncperfctr "<<PID>>"
where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

The following may also be required:

Make sure that the correct security permissions have been granted to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009
registry key:

Grant Full Control permissions to the Creator Owner account.
Grant Full Control permissions to the Administrators account.
Grant Read permissions to the SQL Administrators on that box/Node.
Grant Full Control permissions to the System account


Multiple MongoDB databases (1 per client) and sharding - are they compatible? Currently I'm working with one MongoDB database per client, on a single replica set. We would like to keep one database per client because of security concerns.

Each client has the same 15 collections and we are growing our customer base so we need to scale this into different machines with groups of customers.

I'm reading the MongoDB docs about sharding but the approach is by collection and the shard key so I'm concerned about how it would be when you have many databases.

Is it possible to use sharding having multiple databases? and if it is possible, what would be a good approach?

Yes, they are compatible: if you have multiple shards, then different databases will be automatically allocated to different shards in a balanced way.

The sharding system does distribute large collections across shards by using a shard key, but it also distributes separate databases across different shards. The shards documentation explains:

  Each database in a sharded cluster has a primary shard that holds all the un-sharded collections for that database. Each database has its own primary shard. The primary shard has no relation to the primary in a replica set.
  The mongos selects the primary shard when creating a new database by picking the shard in the cluster that has the least amount of data.

You can use that to let each client's database be allocated to different shards, as follows:

Set up multiple shards (perhaps 3 for now)
When you add a new database for a new client, mongos will allocate the database to whichever shard has least data
This has the effect of 'balancing' the database among the available shards

A few warnings about this approach:

When you first add new shards, mongos does not automatically move databases from one shard to another to balance the system. If you need to do that, you will have to do so deliberately using the movePrimary command
If your databases grow, again you might need databases moved from one shard to another to give them enough space; again, you will have to do this yourself using the movePrimary command
If a single collection in one of your databases is getting too large for a single shard, you will have to manually enable sharding for that collection; then mongos will do the allocation of chunks to different shards, automatically.


MyISAM with Innodb in the same Wordpress database reduce performace? My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine.
I use MySQL 5.5.35-cll i686.

If I use two different table engines in a same database, it will reduce my site performance or are there any downside?

Having InnoDB won't make MyISAM slower and vice versa. However you have to be more careful in memory allocation for InnoDB and MyISAM. Because they store data and indexes in different buffers.
MyISAM:

Indexes in the key buffer
Data in file system cache

InnoDB:

Data and indexes (well, in InnoDB everything is indexes) in the buffer pool

So if you allocate more than necessary to InnoDB then MyISAM will have to access disk more often (and vice versa)


Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance? I installed SQL 2000 Developer (yes, old, don't ask) on my laptop ... with the wrong collation.  Oops!

It's showing Latin1_General_CI_AS, but it turns out I need to use SQL_Latin1_General_CP1_CI_AS to match another instance we're working with.

I found some references with Google on how to change the collation for a specific database, but I'd like to change it for the entire server instance.  Is there a way to fix this without having to re-install SQL Server 2000? It's a pain with the service pack, security fixes, etc.

Thanks

Quite honestly, I think your easiest approach will be:

backup your user databases
uninstall SQL Server
reinstall SQL Server with the right collation
restore your databases
fix the collation on the user databases

Also you know that SQL Server 2000 is well out of mainstream maintenance, right? And that quite soon there will have been FOUR major releases since then?


Usage of aggregate function with when self-joining a table I want to find people from a database who has a weight bigger then the average weight:

SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)

But I have an error:

Result: misuse of aggregate function avg()

I know that I can write:

SELECT *
FROM Player
WHERE weight > (SELECT AVG(weight)
                FROM Player)

But how can I achieve the same by using self join and without subquery?

Assuming a table like:

CREATE TABLE player
    ( player_id int not null primary key
    , weight int not null
    , nationality char(2) not null -- just any additional attribute
    );

insert into player (player_id, weight, nationality)
values (1,70,'SE'), (2,75,'RU'), (3,60,'US');

The following query appears to work in sqllite (as far as I can tell this is valid SQL99 since p1.weight, p1.nationality is functionally dependent of p1.player_id. Most vendors still implement the more restrictive SQL92 rule for group by). Also note that I prefer ansi join over "," joins, I find them easier to read.

select p1.player_id, p1.weight, p1.nationality
from player p1
cross join player p2
group by p1.player_id
having p1.weight > avg(p2.weight);

having is just syntactic sugar for the chattier:

select player_id, weight, nationality
from (
    select p1.*, avg(p2.weight) as avg_weight
    from player p1
    cross join player p2
    group by p1.player_id
) as t
where weight > avg_weight;

Another variant is to join against a derived table, pretty much the same query as your original:

select p1.*
from player p1
cross join (
  select avg(weight) as avg_weight
  from player
) as p2
where p1.weight > p2.avg_weight;

On the other hand you can use window functions to achieve the same thing:

select player_id, weight, nationality
from (
    select p.*
         , avg(weight) OVER () as avg_weight
    from player p
)
where weight > avg_weight;

You can find an example at: db-fiddle


manual transfer fails with table does not exist I performed a manual transfer of data manually (zip up the dir and rsync'ed it over. I moved the members.ibd (the only .ibd file) to a save place, extracted and then replaced members.ibd with the original.
But only the members table can be found.

The rest of the tables are broken.

error:

mysql> select * from transactions;
ERROR 1146 (42S02): Table 'nyc_primary.transactions' doesn't exist

I can see the tables:

mysql> show tables;
+---------------------------+
| Tables_in_nyc_primary     |
+---------------------------+
| accounting                |
| domains                   |
| members                   |
| phpbb_acl_groups          |
| phpbb_acl_options         |
| phpbb_acl_roles           |
| phpbb_acl_roles_data      |
| phpbb_acl_users           |
| phpbb_attachments         |
| phpbb_banlist             |
| phpbb_bbcodes             |
| phpbb_bookmarks           |
| phpbb_bots                |
| phpbb_config              |
| phpbb_config_text         |
| phpbb_confirm             |
| phpbb_disallow            |
| phpbb_drafts              |
| phpbb_ext                 |
| phpbb_extension_groups    |
| phpbb_extensions          |
| phpbb_forums              |
| phpbb_forums_access       |
| phpbb_forums_track        |
| phpbb_forums_watch        |
| phpbb_groups              |
| phpbb_icons               |
| phpbb_lang                |
| phpbb_log                 |
| phpbb_login_attempts      |
| phpbb_migrations          |
| phpbb_moderator_cache     |
| phpbb_modules             |
| phpbb_notification_types  |
| phpbb_notifications       |
| phpbb_oauth_accounts      |
| phpbb_oauth_tokens        |
| phpbb_poll_options        |
| phpbb_poll_votes          |
| phpbb_posts               |
| phpbb_privmsgs            |
| phpbb_privmsgs_folder     |
| phpbb_privmsgs_rules      |
| phpbb_privmsgs_to         |
| phpbb_profile_fields      |
| phpbb_profile_fields_data |
| phpbb_profile_fields_lang |
| phpbb_profile_lang        |
| phpbb_ranks               |
| phpbb_reports             |
| phpbb_reports_reasons     |
| phpbb_search_results      |
| phpbb_search_wordlist     |
| phpbb_search_wordmatch    |
| phpbb_sessions            |
| phpbb_sessions_keys       |
| phpbb_sitelist            |
| phpbb_smilies             |
| phpbb_styles              |
| phpbb_teampage            |
| phpbb_topics              |
| phpbb_topics_posted       |
| phpbb_topics_track        |
| phpbb_topics_watch        |
| phpbb_user_group          |
| phpbb_user_notifications  |
| phpbb_users               |
| phpbb_warnings            |
| phpbb_words               |
| phpbb_zebra               |
| transactions              |
+---------------------------+
71 rows in set (0.00 sec)

rebuilding manual fails:

mysql> select * from transactions;
ERROR 1146 (42S02): Table 'nyc_primary.transactions' doesn't exist
mysql> CREATE TABLE accounting (
    ->  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  trans_date TIMESTAMP,
    ->  paid_current BOOL NOT NULL DEFAULT '0',
    ->  active BOOL NOT NULL DEFAULT '0',
    ->  cust_id int(10)
    -> );
ERROR 1146 (42S02): Table 'nyc_primary.accounting' doesn't exist

The database exist:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nyc_primary        |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> desc transactions;
ERROR 1146 (42S02): Table 'nyc_primary.transactions' doesn't exist

How do I recover my missing data/tables?
I cannot even manually add the table structure:

You cannot move an InnoDB table in that manner.  The problem is that there is info in ibdata1, even if the table has its own "tablespace" (.ibd).

With 5.6, and if you created the table as "file per table", you can use "transportable tablespaces".  See online docs for details.


Selecting the highest value in a column? I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple entries but each entry will have an ever increasing ChldLevel starting at 1) How can I write a select statement to pull the row with the highest ChldLevel value? (I.E If PrntP_PstnGrpID = 10 has 3 entries of ChldLevel 1,2,3 I want to get the row with ChldLevel 3 but if PrntP_PstnGrpID = 5 has 5 entries of ChldLevel 1,2,3,4,5 I get the row with ChldLevel 5)

CREATE TABLE [dbo].[P_PositionGroupFlat] (
    [ID]                      INT            NOT NULL,
    [PrntP_PstnGrpID]         INT            NOT NULL,
    [ChldLevel]               INT            NOT NULL,
    [Sort]                    VARCHAR (8000) NOT NULL,
);

Beyond ypercube's answer, I would solve this with either of the following queries. I like the first because it's easier for my poor head to comprehend.

The second usually provides better performance but it always takes me a bit to "get it."

-- this approach uses row_number to provide an ordered window based
-- resetting whenever the group id changes
SELECT
*
FROM
(
    SELECT
        P.*
    ,   ROW_NUMBER() OVER (PARTITION BY P.PrntP_PstnGrpID ORDER BY P.ChldLevel DESC) AS rn
    FROM
        dbo.P_PositionGroupFlat P
) D
WHERE D.rn = 1
ORDER BY 1;

-- this approach is my new favorite
-- has shown better performance than the above approach
SELECT
P.*
FROM
    dbo.P_PositionGroupFlat P
WHERE
    P.ChldLevel =
    (
        SELECT
            MAX(PI.ChldLevel)
        FROM
            dbo.P_PositionGroupFlat PI
        WHERE
            PI.PrntP_PstnGrpID = P.PrntP_PstnGrpID
    )
ORDER BY 1;

http://sqlfiddle.com/#!3/3a958/4


How to get multiple "last values" for different criteria from a single column? Our interest_log table stores events of users expressing interest on foo.

CREATE TABLE interest_log (
    id bigint NOT NULL PRIMARY KEY,

    user_id bigint REFERENCES foo_user(id),
    foo_id bigint NOT NULL REFERENCES foo(id),

    action varchar(255) NOT NULL,
    comment varchar(300) -- nullable
);

Some of the available actions are:

VIEW, FAVOR, UNFAVOR, DISMISS, and UNDISMISS.

I want to create a materialized view that provides the latest state of whether a user favors or dismisses a foo.

CREATE MATERIALIZED VIEW interest
AS
SELECT
    il.user_id,
    il.foo_id,
    ??? AS is_dismissed,
    ??? AS is_favored
FROM
    interest_log AS il,
WHERE
    ???

I came up with this query to select is_dismissed - but it feels too complicated and I don't see how I can combine it with fetching the latest favored state as well.

SELECT
  il.action = 'DISMISS' AS is_dismissed
FROM interest_log AS il
WHERE
  il.action IN ('DISMISS', 'UNDISMISS')
  AND NOT EXISTS (
    SELECT 1
    FROM interest_log AS il2
    WHERE
      il.action IN ('DISMISS', 'UNDISMISS')
      AND il2.id < il.id

What could the materialized view query look like and what fields/indexes should we add to make refreshs fast?

postgres:10.11

Assuming only few entries per (user_id, foo_id), the query you have is typically faster this way:

SELECT DISTINCT ON (user_id, foo_id)
       user_id, foo_id
       action = 'DISMISS' AS is_dismissed
FROM   interest_log
ORDER  BY user_id, foo_id, id DESC
WHERE  action IN ('DISMISS', 'UNDISMISS');

But this only produces rows for (user_id, foo_id) that actually have (un-)dismissed.

If there are relatively few rows per (user_id, foo_id) for both items of interest (is_dismissed and is_favored), just run a FULL [OUTER] JOIN on two of these queries to produce the set of all that either (un-)dismissed or (un-)favored:

SELECT *
FROM  (
   SELECT DISTINCT ON (user_id, foo_id)
          user_id, foo_id
        , action = 'DISMISS' AS is_dismissed
   FROM   interest_log
   WHERE  action IN ('DISMISS', 'UNDISMISS')
   ORDER  BY user_id, foo_id, id DESC
   ) d
FULL   JOIN (
   SELECT DISTINCT ON (user_id, foo_id)
          user_id, foo_id
        , action = 'FAVOR' AS is_favored
   FROM   interest_log
   WHERE  action IN ('FAVOR', 'UNFAVOR')
   ORDER  BY user_id, foo_id, id DESC
   ) f USING (user_id, foo_id);

About DISTINCT ON and few vs. many:

Select first row in each GROUP BY group?

Support with two partial indexes:

CREATE INDEX interest_log_dismiss ON interest_log (user_id, foo_id, id DESC, action)
WHERE  action IN ('DISMISS', 'UNDISMISS');

CREATE INDEX interest_log_favor ON interest_log (user_id, foo_id, id DESC, action)
WHERE  action IN ('FAVOR', 'UNFAVOR');

Or using a custom aggregate function last():

CREATE OR REPLACE FUNCTION public.last_agg (anyelement, anyelement)
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS 'SELECT $2';

CREATE AGGREGATE public.last(anyelement) (
  SFUNC = public.last_agg
, STYPE = anyelement
);

Then this simpler query does the same:

SELECT user_id, foo_id
     , last(action) FILTER (WHERE action IN ('DISMISS', 'UNDISMISS')) = 'DISMISS' AS is_dismissed
     , last(action) FILTER (WHERE action IN ('FAVOR', 'UNFAVOR'))     = 'FAVOR'   AS is_favored
FROM  (
   SELECT *
   FROM   interest_log
   WHERE  action IN ('DISMISS', 'UNDISMISS', 'FAVOR', 'UNFAVOR')
   ORDER  BY user_id, foo_id, id
   ) sub
GROUP  BY user_id, foo_id;

Faster with the additional module first_last_agg providing a C implementation. See:

Calculating follower growth over time for each influencer

There are more ways (have a look at the linked answer), and depending on undisclosed information, different query styles may be (much) faster.


Can't uninstall Microsoft SQL Server 2016 RC3 Microsoft SQL Server 2016 RC3 was installed on our server (not by me) Windows Web Server 2008 R2 64bit.  Visual Studio 2015 Community was also installed.  When I went to work on the server I could not connect to the MS SQL Server, so I went to see if the service was running and the only MS SQL service in the services manager was:

  SQL Server VSS Writer Provides the interface to backup/restore
  Microsoft SQL server through the Windows VSS infrastructure.

None of the core services were installed.  When I looked in the start menu all the Microsoft SQL Server 2016 RC3 programs were installed.  When I ran the Installed SQL Features Discovery Report, it showed MS SQL 2016 installed, but no instances.

So I uninstalled VS, and attempted to uninstall MS SQL Server.  I uninstalled the main program and all the components and then attempted to uninstall Microsoft SQL Server 2016 RC3 Setup, but it pops up the following message:

  Warning 26003. Microsoft SOL Server 2016 Ro3 Setup Support Files
  cannot be uninstalled because the following products are installed:
  SOL Server 2016 Ro3 Batch Parser SOL Server 2016 Ro3 SOL Diagnostics
  SOL Server 2016 Ro3 XE vent SOL Server 2016 Ro3 Shared Management
  objects

  Extensions SOL Server 2016 Ro3 SOL Diagnostics SOL Server 2016 Ro3
  Batch Parser SOL Server 2016 Ro3 XE vent SOL Server 2016 Ro3 Shared
  Management objects

Yet none of these show in the Add/Remove Programs. (I refreshed it several times)

I then ran the Microsoft SQL Server 2016 Release Candidate 3 (RC3) Setup Discovery ReportMicrosoft SQL Server 2016 Release Candidate 3 (RC3) Setup Discovery Report, and it just has a single line with None in all the boxes.

I then attempted to reinstall Microsoft SQL Server 2016 Release Candidate 3, by running setup from the downloaded ISO, and Installation/New SQL Server stand-alone installation or add features to an existing installation/

I am trying to install the evaluation version and that is what was previously installed, when I get to the Feature Selection of the wizard it shows the following:

  Shared Features/Reporting Services - SharePoint Shared
  Features/Reporting Services Add-in for SharePoint Products

So none of the core services are available to install.

The ISO I am installing from is: SQLServer2016RC3-x64-ENU.iso, downloaded from Microsoft, and I have used the exact same ISO to install on a Windows 10 machine with VS 2015 installed with no problems and did not have this issue.

How can I resolve this and get Microsoft SQL Server 2016 Release Candidate 3 installed and running?

Thanks to Aaron Bertrand, I was able to resolve the issue following the manual steps outlined in his blog:

Fun with software : uninstalling SQL Server 2008 R2 Evaluation Edition

...in spite of the title, it's not just for 2008 R2 or Evaluation Edition.

In case the link ever stops working, the essential steps are:

Locate SQL Server related GUIDs under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\

Also look for GUIDs in folders under Uninstall called e.g. "Microsoft SQL Server 2008 R2"
Run for each item:

msiexec /x "{guid}"


Uber-wide tables: Time to switch to Mongo? As a web developer, I've typically only worked with nice, normalized tables in SQL-based RDBMS, but I'm doing some data consolidation for a researcher who will be analyzing correlations between all items of several very long questionnaires and wants a giant CSV with data for all takers and all items.  So, say, 3000 rows and 5000 columns (?).

I set up tables for each questionnaire (each has its own CSV), but we're talking tables with 1000+ columns representing item responses for each taker / pkid.  Obviously I can't join up four, five different questionnaires like that based on pkid -- I'd have 5K or more columns and I'm already at the outer limits of MySQL just on the individual tables.

So do I:
1) look into NoSQL at last,
2) just programmatically write a one-off script to consolidate this data
3) convince scientist to buy Oracle or MS SQL
4) other option I as a non-data-scientist don't know about?

Thanks for the advice!

Joy

1) Consider how this information will be used.  Will the owner want to try lots of ad hoc analysis and data exploration?  Does your potential data store have tools or connection libraries that allow this?  Will the user have to resort to writing Map and Reduce functions for every question posed?

2) Do you mean that the same question is on different questionaires and you're wondering if you should recognise they're the same in your data store design?  Yes, absolutely you should, whatever thechnology you end up with.

3) The DBMSs have column limits, too.  They may be larger, but they're not infinite.

4) Store your data as one row per answer.  There are several qustions here on quiz design. Here's one picked at random.  These will give you pointers and any detailed questions remaining will be good material for future DBA.SE posts.


Generating a password that matches the Password Validation Component MySQL 8 has a pretty sophisticated "Password Validation Component" (this replaces the validate_password plugin). It has a lot of options,

validate_password.number_count
validate_password.mixed_case_count
validate_password.special_char_count
validate_password.dictionary_file
validate_password.length

Given that these can all be set by the user, is there mechanism guaranteed to generate a random password that satisfies this? Is the only way to write a UI that changes the password to suspend the validation component when you set it?

To generating random password for user while creating new one you can use :

mysql> CREATE USER ran_user@localhost IDENTIFIED BY RANDOM PASSWORD;
+----------+-----------+----------------------+
| user     | host      | generated password   |
+----------+-----------+----------------------+
| ran_user | localhost | UN:5_z05J._*VtU3K%Qu |
+----------+-----------+----------------------+
1 row in set (0.10 sec)

For altering password of current/existing user you can use this statement :

mysql> ALTER USER ran_user@localhost IDENTIFIED BY RANDOM PASSWORD;
+----------+-----------+----------------------+
| user     | host      | generated password   |
+----------+-----------+----------------------+
| ran_user | localhost | (YU>{}tu&6@[U%!Pdw>n |
+----------+-----------+----------------------+
1 row in set (0.16 sec)

This RANDOM PASSWORD keyword satisfies password validator component :

mysql>  show global variables like '%validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.23 sec)


sql server-percentage calculation Sample data :

LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS  FAMILY  CACHE_FAMILY    Count
23053B  00000   00000   00000   S           SUMMIT      WER           43
23053B  00000   00000   00000   T           SUMMIT      WER           144
23053B  00000   00000   00684   T           SUMMIT      WER           2
23053B  00353   00418   00684   T           SUMMIT      WER           1
23053B  00353   00418   00763   T           SUMMIT      WER           1
23053B  00353   00418   01512   T           SUMMIT      WER           1
23053B  00353   00418   06797   T           SUMMIT      WER           1
23053B  00353   00418   30228   T           SUMMIT      WER           1
23053B  00353   00418   31935   T           SUMMIT      WER           2
23053B  05601   01402   00758   T           SUMMIT      WER           1
23053B  05601   01402   09091   T           SUMMIT      WER           1
23053B  05601   01402   65053   T           SUMMIT      WER           1

This is my query:

SELECT LINE_NO,
  E_FIELD,
  F_FIELD,
  G_FIELD,
  HSA_STATUS,
  FAMILY,
  CACHE_FAMILY,
  Count = ((SUM(TOTAL)) )
FROM
(
  SELECT LINE_NO,
    E_FIELD,
    F_FIELD,G_FIELD,
    HSA_STATUS,
    FAMILY,
    CACHE_FAMILY,
    Count(LINE_NO) as Total
  FROM TX_HSA_SUMM
  WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00') AND ('2013-03-08 10:20:00')
  GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY
) as a
GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY,total
ORDER BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY,total

This is my sample data actually. I already make the count. As You can see the column header. My problem is I need to sum the count and the for each row I need to get the percentage. For example the total for the above record is 199 so for the first record count is 43 so the calculation should be 43/199 * 100. How can I view the percentage? Please help me I need this urgently.

This is a great example of how the Window Functions can be used. Based on your data, I'm assuming you want to do this for every unique LINE_NO. I'll leave it to you to format the percentage column.

SELECT      LINE_NO,
            E_FIELD,
            F_FIELD,
            G_FIELD,
            HSA_STATUS,
            FAMILY,
            CACHE_FAMILY,
            COUNT(*) AS CT,
            COUNT(*) / SUM(COUNT(*) * 1.0) OVER (PARTITION BY LINE_NO)

FROM        TX_HSA_SUMM

GROUP BY    LINE_NO,
            E_FIELD,
            F_FIELD,
            G_FIELD,
            HSA_STATUS,
            FAMILY,
            CACHE_FAMILY