As the error message clearly states, the version of the *.mdf file you are trying to attach is the internal version 661.

  The database 'E:\PRAKASH_PRIVATE\ACADEMIC DOC\PROJECT_DOC\MYPROJECT R3\MYPROJECT\APP_DATA\SHOPING.MDF' cannot be opened because it is version 661.

The SQL Server you are running supports the internal version number 655 and earlier.

  This server support version 655 and earlier. A downgrade is not supported.

To attach the MDF file you would have to find the corresponding SQL Server version number that has the internal database version 661 (the MDF file). This turns out to be SQL Server version 2008 R2.

The SQL Server version you are running is less than SQL Server 2008 R2 (possibly 2008).

Reference: Microsoft SQL Server Internal Database Versions and Compatibility Levels

The table is reproduced here to provide a complete source:

SQL Server Version  | Internal Database Version | Database Compatibility Level
--------------------+---------------------------+------------------------------
SQL Server 2019 CTP | 895 / 904                 | 150
--------------------+---------------------------+------------------------------
SQL Server 2017     | 869                       | 140
--------------------+---------------------------+------------------------------
SQL Server 2016     | 852                       | 130
--------------------+---------------------------+------------------------------
SQL Server 2014     | 782                       | 120
--------------------+---------------------------+------------------------------
SQL Server 2012     | 706                       | 110
--------------------+---------------------------+------------------------------
SQL Server 2012 CTP1|                           |
(a.k.a. 2011 Denali)| 684                       | 110
--------------------+---------------------------+------------------------------
SQL Server 2008 R2  | 660 / 661                 | 100
--------------------+---------------------------+------------------------------
SQL Server 2008     | 655                       | 100
--------------------+---------------------------+------------------------------
SQL Server 2005 SP2+|                           |
VarDecimal enabled  | 612                       | 90
--------------------+---------------------------+------------------------------
SQL Server 2005     | 611                       | 90
--------------------+---------------------------+------------------------------
SQL Server 2000     | 539                       | 80
--------------------+---------------------------+------------------------------
SQL Server 7.0      | 515                       | 70
--------------------+---------------------------+------------------------------
SQL Server 6.5      | 408                       | 65
--------------------+---------------------------+------------------------------
SQL Server 6.0      | ?                         | 60
--------------------+---------------------------+------------------------------

Your assumption that....

  If I run Select @@Version I get the output that I have 2008 which is what my .mdf file requires..

...is wrong. The mdf file contains a database with the version SQL Server 2008 R2 which is not the same as your SQL Server version SQL Server 2008.

To attache the mdf file you would have to install SQL Server 2008 R2.


Check this link: http://msdn.microsoft.com/en-us/library/ms191188.aspx
It allows you to enable Ole Automation Procedures. This change would affect the entire instance. This is turned off by default due to security.

Other Ole procedures are here: http://msdn.microsoft.com/en-us/library/ms190501.aspx


mongodb setFeatureCompatibilityVersion command not found

The documentation for setFeatureCompatibilityVersion shows it being new as of MongoDB 3.4, so it can't be run on a 3.2 server process:
https://docs.mongodb.com/v3.4/reference/command/setFeatureCompatibilityVersion/

The upgrade steps describe that running this command should be done after the server processes have been upgraded to 3.4.


So, yes, the problem was the time_zone settings.

As mentioned in the docs, if time_zone=SYSTEM, MySQL will do a timezone calculation, otherwise, it (presumably) will not.

When you INSERT/UPDATE the data as a string, MySQL stores it "as is". If you INSERT/UPDATE the data with NOW(), it stores the current UTC value.

Since my slave had time_zone=US/Eastern, it never did the timezone calculation, so some of my dates were off by +4 hours.

This seems to have fixed the problem of dates in other tables as well.

That's my story and I'm sticking to it. :-)


Index optimization on a view using a full outer join

The optimizer makes choices based on costing estimates. The cost model is generic, and may not always choose optimal plans for your particular hardware, and its assumptions may not always be valid for your circumstances.

In this case, the optimizer assesses a hash join as the cheaper option over nested loops when the estimated number of rows to be joined is large. If you are certain that a nested loops join will always be preferable to a hash join, you could consider (and test!) forcing a seek instead of a scan of the FooChild table in the view:

SELECT
    f.TheDate,
    f.GroupID,
    fc.UserID,
    fc.Amount,
    b.IsComplete
FROM dbo.Foo AS f
JOIN dbo.FooChild AS fc WITH (FORCESEEK) -- New hint
    ON fc.FooID = f.ID
LEFT JOIN dbo.Bar AS b
    ON f.TheDate = b.TheDate
    AND f.GroupID = b.GroupID
    AND fc.UserID = b.UserID
UNION
SELECT
    b.TheDate,
    b.GroupID,
    b.UserID,
    x.Amount,
    b.IsComplete
FROM dbo.Bar AS b
LEFT JOIN
(
    SELECT
        f.TheDate,
        f.GroupID,
        fc.UserID,
        fc.Amount
    FROM dbo.Foo AS f
    JOIN dbo.FooChild AS fc WITH (FORCESEEK) -- New hint
        ON fc.FooID = f.ID
) AS x
    ON x.TheDate = b.TheDate
    AND x.GroupID = b.GroupID
    AND x.UserID = b.UserID;

Side note: while this transformation from the original full join is valid given the current uniqueness constraints on your tables, please review the answer to your previous question and consider rewriting the full join as suggested in my edit.


What are the side-effects of disabling nested loops in postgres?

Using debugging parameters like enable_nestloop should not change the results of a query. If it does that'd generally be a bug.

However, you should not use enable_nestloop = off or similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the correct plan node for best performance in a part of a query, and forcing PostgreSQL to prefer other options might make queries significantly slower.

Instead, you should generally adjust the cost parameters like random_page_cost to encourge PostgreSQL to choose a plan that performs better on your hardware and workload. For more information see the tuning advice on the PostgreSQl wiki and in the main documentation.


SSIS - How to parse extensions from path-filename strings

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )

In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:

Remove any trailing white-space with RTRIM( name )
Reverse the string so we are dealing with the end as the beginning with REVERSE()
Grab the first "token" delimited by a backslash using TOKEN()
Count how many "." delimiters are in the result using TOKENCOUNT()
Test the count of "." delimited tokens is equal to 1 using ?
If the count is 1, return an empty string - no extension.
Otherwise return the first token before the "." using TOKEN()
Reverse the extension to get back to "normal" using REVERSE()


Is it ok to change from full recovery to simple recovery in Sql Server

I would do this when the database is in low activity (end of day, overnight, first thing in the morning). I would NEVER recommend making changes like this to a live database in the middle of working hours. It's probably a good idea to change the database to read-only or make sure no users/applications are hitting it.

Run a full backup of the database/log file in question, while still in full recovery mode. This will give you a starting point in case anything gets borked and you can just restore it.

Switch to SIMPLE recovery, I don't like using the GUI, I'd script it out.

Here's a great article from MSDN about things to consider when changing your backup model:
Considerations for Switching from the Full or Bulk-Logged Recovery Model.


Syntax check for creating logins in different SQL versions

The trouble here is that the script evaluates the IF statement, and then the logic applies to the first statement afterwards. To handle multiple statement you need to put them within a BEGIN...END block.

Something like this should work for you

  USE master
    GO
    IF NOT EXISTS ( SELECT  *
                    FROM    sys.syslogins
                    WHERE   loginname = 'login_name' )
        BEGIN
            IF ( SELECT LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),
                             2)
               ) = '12'
                BEGIN
                    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
                    CREATE USER [login_name] FOR LOGIN [login_name];
                    ALTER ROLE [db_datareader] ADD MEMBER [login_name];
                END
            ELSE
                IF ( SELECT LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),
                                 2)
                   ) = '11'
                    BEGIN
                        CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
                        CREATE USER [login_name] FOR LOGIN [login_name];
                        ALTER ROLE [db_datareader] ADD MEMBER [login_name];
                    END
        END
    ELSE
        BEGIN
            SELECT  1;
        END


How AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime

There is no 'cache'. Is just log. The primary log is the messaging framework. Primary writes into the log file, and secondaries receive a copy. When connected the secondaries receive the log immediately and the primary is free to reuse the log. When disconnected the primary is forbidden from truncating the log and must append to it. When reconnected the secondary receives the log it missed and primary is free to reuse again the log, no longer has to append (grow).

You taking log backups on the primary does not change anything. When the log is required to be kept for a disconnected secondary, backup does not truncate the log.

There is no 'message' to sent to the secondaries to keep it updated. The secondary is simply running recovery on the database, that's all it does, applying the log received from the primary. The Write Ahead Protocol guarantees that whatever the secondary is 'recovering' is going to be identical to whatever the primary has in the database.

There are some optimization (eg. when connected the primary usually sends the log from memory, not from disk) and there are a bunch of additional control messages sent outside of the log stream, but these are details that distract from the core issue: the secondary is simply running recovery applying the log received from the primary, and the primary has to keep said log until it was acknowledged by secondary.

Your confusion arise from the believe that backup has truncated the log on the primary, therefore there must be another mechanism to synchronize. This is incorrect, as backup did no in fact truncate the log.


as well as inserting into the login_coordinates table

I don't see the benefit of redundant storage. Just write to the table login_coordinates. Easy enough to access with only 10 rows per user. Don't update the user row as well.

Basically, what @Ziggy already suggested, with more flesh. Based on this table:

CREATE TABLE login_coordinates (
  login_coordinates_id serial PRIMARY KEY
, user_id integer NOT NULL  -- REFERENCES users
, login_at timestamptz NOT NULL DEFAULT now()
, coordinates point NOT NULL
);

Using OFFSET 9 LIMIT 1 to pick nr. 10:

DELETE FROM login_coordinates lc
WHERE  login_at <= (
   SELECT login_at
   FROM   login_coordinates
   WHERE  user_id = lc.user_id
   ORDER  BY login_at DESC
   OFFSET 9  -- only found if at least 10 rows present
   LIMIT  1
   )
AND    user_id = 1;

INSERT INTO login_coordinates (user_id, coordinates)
VALUES (1, '(1, 2)');


Correct. Spatial indexes don't get leveraged in that situation, sadly.

Spatial indexes provide a set of grids, allow the system to identify geometries (or geographies) that overlap these grids.

Your best bet is to set a threshold of acceptable closeness, and try that, using something like STBuffer. STIntersects works well, and you can increase this threshold if nothing is found (eg, using a second OUTER APPLY if nothing is found.

Edit: They do now! Check my post on it... http://blogs.lobsterpot.com.au/2014/08/14/sql-spatial-getting-nearest-calculations-working-properly/

In summary, to get your index to be used, you need to make sure that your ORDER BY value can't be NULL - just add a WHERE...IS NOT NULL clause and it should work.


When interrupting a query on a Master, does it replicate to the Slave?

This is a local phenomenon within the MySQL Replication Architecture. Here is a sample message that can sometimes appear when you interrupt a query on a Master

  Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: (INSERT vhc (v_id, vc_id) SELECT 7421594, vc_id FROM vc WHERE label='st' AND value='re')

Error 1053 is a Server Shutdown. Obviously, the intent was not to shutdown mysql and not tell connected Slaves. Hitting Ctrl-C on a Master would kill the DB Connection. However, such disconnects are simply interpreted as a shutdown. In my old post Does MySQL support replicating all databases?, I described how MySQL replicates from Master to Slave:

Master has Binary Logging enabled
Master records completed SQL Statements
Master will inject 'USE dbname;' among the SQL Statements it records
Slave connects to Master (IO Thread)
Slave IO Thread requests next SQL statement from the Binary Logs
Slave IO Thread copies it to its Relay Logs
Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Looking back at the error message I described, the Slave detected that a binlog event in its relay logs that came from the Master. It was anticipating another event with the actual SQL saying everything went well on the Master. Error 1053 (the phantom shutdown caused by Ctrl-C) came up instead. This will give you the chance to say OH MASTER HAD THAT QUERY INTERRUPTED. I"LL GO LOOK AT THE DATA TO SEE IF THEY MATCH.

When it comes to your actual question, I would go with #1

  Nothing happened on the slave, because master waits until the query is finished executing before it sends the query to the slave.

but with a caveat

  Since Ctrl-C was done on the Master, the Master recorded the SQL in the binlog along with error 1053. The Slave intercepted this scenario.


GRANT USAGE on all schemas in a database?

You have at least two options.

The first one makes use of a small query and a text editor.  We have to collect the schemata of our interest:

SELECT nspname
  FROM pg_namespace;

You can add a WHERE clause if you want to limit the scope.  Copy the output and amend it, so you get a number of GRANT USAGE ON SCHEMA ... TO your_role; commands.  Then just feed it to psql, for example:

psql -f multigrant.sql

A usual variant of this could be a shell script that loops over the collected names and calls psql, passing the constructed GRANT statement to the -c option.

The other solution does basically the same in one pl/pgsql block, building a dynamic query.  The core is the same - we have to collect the schemata.  Then we loop over all of them, granting the permissions schema by schema:

DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO your_role $$, sch);
    END LOOP;
END;
$do$;

Notes:

unlike for tables, sequences, functions and types, one cannot set default privileges for schemata (as of 9.4).  You will have to grant this privilege for any newly added schema manually.
here I am using dollar quoting when building the dynamic query.  This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example).  This way most editors will highlight the statements nicely.
I also use format() with the %I format specifier to have the object name properly quoted if necessary.  This approach is far more readable than building the query with concatenation of string constants and some quote_ident() calls.
pg_namespace can be found in the pg_catalog schema.  Check out the other objects in there  - they store every aspect of your schemas, tables and so on.


How to get month name from week number and year

Please try this:

SELECT MONTHNAME(STR_TO_DATE('201534  Monday','%X%V %W')) month_name

Instead of hardcoding the values, you can pass dynamic value also.


Oracle to T-SQL OPENQUERY special character conversion issues

The solution was to change the NLS_LANG value in the registry to "AMERICAN_AMERICA.US7ASCII" because of reasons described in these answers:

If we have US7ASCII characterset why does it let us store non-ascii characters?
difference between NLS_NCHAR_CHARACTERSET and NLS_CHARACTERSET for Oracle

Now the characters are coming through correctly.

Thanks @wernfried-domscheit !


Corruption in D:\$MFT. How may this progress? I don't think CHECKDB is useful here?

Here is how I would approach that issue.

Take care of SQL Databases.

Full backup of databases
Restore databases to development/test server
Run CHECKDB on development/test server on restored databases to check for errors.

If errors are encountered when running CHECKDB then you should address those issues, it means corruption happened somewhere in the line.  Seperate topic for recovering for this.

Take care of drive on server

stop SQL Server process.
run CHECKDSK /r on drive
Hope for best.  There are stress test utilities out there that can tell you if the drive is having problems.  If this is a single drive, then consider replacing it.  If part of a raid group, find a health check utility from the manufacturer and see if that tells you anything.

If CHECKDSK /r fixed the issue with the Master File Table then you should run CHECKDB on the databases again to make sure there isn't any corruption (or any corruption introduced into the files by CHECKDSK /r.

Side Notes:

Do you have PAGE VERIFY by CHECKSUM set for each of the databases?
When you do backups, do you use the CHECKSUM option?


Microsoft TDE Permissions For Changing Keys

ALTER DATABASE ENCRYPTION KEY

Requires CONTROL permission on the database and VIEW DEFINITION permission on the certificate or asymmetric key that is used to encrypt the database encryption key.

You must also add view definition according to MSDN.

https://msdn.microsoft.com/en-us/library/bb630389%28v=sql.110%29.aspx


SQL Server Basic Availability Group: config-only replica?

It is just the configuration data no user data - so probably of limited use
There is more information available here :-
https://docs.microsoft.com/en-US/sql/t-sql/statements/create-availability-group-transact-sql?view=sql-server-ver15
to quote :-
CONFIGURATION_ONLY Specifies that the primary replica synchronously commit availability group configuration metadata to the master database on this replica. The replica will not contain user data. This option:
Can be hosted on any edition of SQL Server, including Express Edition.
Requires the data mirroring endpoint of the CONFIGURATION_ONLY replica to be type WITNESS.
Can not be altered.
Is not valid when CLUSTER_TYPE = WSFC.
For more information, see Configuration only replica.


Should I maintain relationships diagrams in the back-end and the front-end?

I have never used the relationships tool for anything other than understanding the relationships between base tables on the back end database.  I could see however where it would be very useful on the front end if you have many users of your application who write a lot of ad-hoc queries and you provide them queries stored on the front end database which they can use as an abstraction layer.  On the other hand, if the users only interact with the database via forms I would think the work to add the queries to the relationship tool and connect them up would not be worth it.


You better use tinyint. It's int from 0 to 255. It's also takes only 1 byte to store value. It's enough for your 5-star rate.
If you need store values like 4.5 stars you may store stars like this:
1 star =10
2.5 stars=25
and so on
And, of course, you need check constraint on this field.

tinyint - using storage 1 byte
numeric(1)/decimal - using storage 5 bytes


Can I create a computed column that requires input to select?

I'm not sure if it's possible to do this so that none of the references to the resource table need to change. It seems like the fact that a language_id is needed is a fundamental change that all calling code will need to be aware of.

However, it is possible to design this in a way that the resource can be queried in either of the following simple ways. One of these options might have been an easier change to make and maintain across so many different places.

Table-valued function

Using an Inline Table-Valued Function, we can provide the following syntax.

SELECT resource_id, language_id, name
FROM dbo.resourceTVF(@resource_id, @language_id) r

Here is an example of how to create the function. It's essentially the same query from your question, but with the alias default changed to def (default is a SQL Server keyword).

-- Create the Table-Valued Function
CREATE FUNCTION dbo.resourceTVF (@resource_id INT, @user_language_id INT)
RETURNS TABLE
AS
RETURN
SELECT @resource_id AS resource_id,
  @user_language_id AS language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource res
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id
    AND exact.language_id = @user_language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
WHERE res.resource_id = @resource_id
GO

View

You could rename the resource table (e.g., to resource_base) and then create a resource view in order to provide the following API:

SELECT resource_id, language_id, name
FROM dbo.resource
WHERE resource_id = @resource_id
  AND language_id = @language_id

The primary downside is that the view definition needs to CROSS JOIN all resources and languages before using applying the LEFT JOIN to the local and default resources. Even so, this is going to be a fairly efficient plan with 4 singleton seeks assuming that you have the proper indexes.

CREATE VIEW dbo.resource WITH SCHEMABINDING AS
SELECT res.resource_id,
  lang.language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource_base res
CROSS JOIN dbo.languages lang
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id
    AND exact.language_id = lang.language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
GO

Full script

Here is a full script where I implemented both of these proposals, loaded a small amount of fake data, and ran a few test cases. At least for these test cases, both approaches yield the desired results and use a loop-seek based plan.

I think that the inline table-valued function is probably the approach the I'd try first. Note that you can use CROSS APPLY to "join" to the table-valued function if you need more than one resource at a time.


Probably not.  Violating a declarative constraint will always raise an error and prevent the transaction from committing writes that would violate the constraints.  The point of database constraints - checks, foreign keys, uniques etc. - is that they prevent invalid data from being recorded in the database by aborting writes that will violate the constraints.  The constraint violation raises an error and rolls back the transaction.

If you want the transaction to fail gracefully you will either have to deal with the exception or pre-validate the data before attempting to write it.  In the latter case you will have to replace the validations in the stored procedures or implement validation in the application.


According to the Microsoft Docs for xp_cmdshell:

xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

Thus, if you attempt to execute xp_cmdshell as a non-sysadmin user,  assuming you have not configured the proxy account, it will fail.  This means only sysadmins can run it.  Do you trust your sysadmins?
Once xp_cmdshell is enabled, the shell it spawns has the same security as the account used to run SQL Server itself (or the proxy account, if configured).  If that account has full permissions on the local machine, then the shell created by xp_cmdshell will have full permissions on the local machine.  Same thing if the SQL Server Service Account is a domain admin (which I would never suggest).  Following the principle of least-privilege should mean SQL Server has the minimum rights it needs to function, and xp_cmdshell will have those same set of rights.
If you configure xp_cmdshell to use the proxy account, then the shell spawned by it will have the rights of that account, which can be locked down.  However, any sysadmin can modify the proxy account settings, so ensuring you limit who has sysadmin rights is extremely important.
You've mentioned that your application team is wanting to use xp_cmdshell inside a trigger.  This is going to really limit the performance of your system, and you really should re-think that design.  If, for instance, xp_cmdshell returns an error, the insert into your table will fail.  If the call to xp_cmdshell takes several seconds to complete, the rows affected by the insert will be locked during that time, and may cause other sessions to be blocked.
As @DanGuzman pointed out, you should instead use a service broker queue to provide an asynchronous guaranteed message delivery mechanism that won't interrupt the insertion of rows into the affected table.  Service Broker queues can be consumed by apps outside of SQL Server in a responsive, reliable, and scalable manner.


Mirroring not working after a server crash

The likely issue is that the principal has had log backups performed since the mirroring session was paused and it cannot catch up, so re-initializing the mirror with a full and log backup should fix the problem.  First you will need to remove the mirroring from both servers with:

alter database [mydb] set partner off

Then restore the full and log backup with NORECOVERY and re-run through the mirroring wizard.


Automatically create ER diagram from database schema

The relationship between tables in any relational database is done using Foreign Keys.

From the Wikipedia definition:

  Foreign keys play an essential role in database design. One important part of database design is making sure that relationships between real-world entities are reflected in the database by references, using foreign keys to refer from one table to another.

Without FKs there will be no way for any schema modelling tool of knowing that two tables are related.

A solution might be for you to script out FKs by parsing the names of the tables (if the related tables have similar names), then names and data types of columns that define the relationship. But that's no trivial task.


Consider Confirmationas a separate  entity which stores info on what admin confirmed that user action and when it happened. Status of user (action?) is derived as the confirmation with the max level of admin.
One option I would add is a requried confiration level attribute of user, ie. when admin with required level confirms user, the confirmation process is considered finished successfully. Probably in some future moment more levels will be added to the system and new levels probably should not render old users unconfirmed.


How do I determine if a database is the principal in a mirroring setup?

At first I was expecting your error to be located at the point of retrieving SP_Z procedure. Sorry for being hasty.

This link gives an explanation on how to query database mirroring status of your database. So, you do the following:

DECLARE @MirroringRole int;
SET @MirroringRole = (SELECT mirroring_role
    FROM sys.database_mirroring
    WHERE DB_NAME(database_id) = N'DB_X');   -- your database name here
IF @MirroringRole = 2 -- Mirror
    -- connect to the failover partner server, using your database
ELSE IF @MirroringRole = 1 -- Principal
    -- connect to this server
END IF


Speedup restore of 160 GB SQL in 24 core + Optane NVME system

Restoring from an SQL dump will be always slow. You can make it faster by:

Increasing innodb_buffer_pool_size to size of the old datadir or what available RAM allows. 220G in your config is a good value, so this option not for you.
Setting innodb_flush_log_at_trx_commit to 0 or 2.
Increasing innodb_log_file_size to several GB. 4GB will be OK.
Disable query_cache.
disable binlogs.

Do not disable innodb_doublewrite - unsafe.

Yet, I doubt you'll gain much.

To restore the database faster use XtraBackup or cold copy (if you shutdown the old database and copy datadir to the new server it will pick it up w/o problems assuming same MySQL version).


Don't try that approach.  Instead, create another column for the 'sequence number' of the actions.  Or simply use a DATETIME for ordering.  (Note, that has a resolution of 1 second; can two things happen in a single second?  If so, there are workarounds.)


This simple query should work in your outdated version:

SELECT DISTINCT member_id
FROM   tbl t1
JOIN   tbl t2 USING (member_id)
JOIN   tbl t3 USING (member_id)
JOIN   tbl t4 USING (member_id)
JOIN   tbl t5 USING (member_id)
JOIN   tbl t6 USING (member_id)
JOIN   tbl t7 USING (member_id)
WHERE  t2.data_date = t1.data_date + 1
AND    t3.data_date = t1.data_date + 2
AND    t4.data_date = t1.data_date + 3
AND    t5.data_date = t1.data_date + 4
AND    t6.data_date = t1.data_date + 5
AND    t7.data_date = t1.data_date + 6;

In Postgres, you can just add integer to a date to get the next day.

And probably fast, too - as long as you don't have much longer streaks of entries producing many dupes in the first step.

Or:

SELECT DISTINCT member_id
FROM   tbl t1
JOIN   tbl t2 USING (member_id)
WHERE  t2.data_date BETWEEN t1.data_date + 1
                        AND t1.data_date + 6
GROUP  BY t1.member_id, t1.date
HAVING count(*) = 6;

Update to a current version of Postgres at the earliest opportunity.


Auditing authentication issues in a distributed environment

As per the definition of your database link, it's a fixed user database link.

If a link includes a fixed user, the fixed user's username and password are used to connect to the remote database.

For example, you have connected as kjohnston and created a fixed user private database link, If kjohnston uses the fixed user link in a query, then kjohnston is the user on the local database, but you connect to the remote database as testuser.

So you can't get the session information of testuser in the local database. DB one is just acting as client tool for the testuser.

SQL> SELECT users.username, users.lock_date, aud.os_username, aud.userhost, aud.sessionid, aud.os_process
  FROM dba_users users
  JOIN dba_audit_trail aud
    ON aud.username = users.username
   AND aud.timestamp = users.lock_date
 WHERE users.account_status in ('LOCKED', 'LOCKED(TIMED)')
   AND aud.action = 100
   AND aud.returncode = 1017;  2    3    4    5    6    7    8

USERNAME               LOCK_DATE
------------------------------ ---------
OS_USERNAME
--------------------------------------------------------------------------------
USERHOST
--------------------------------------------------------------------------------
 SESSIONID OS_PROCESS
---------- ----------------
TESTUSER               10-MAR-17
oracle
testserver1
   1590012 3348

You can get the locked username using the above query.

On DB1, you can query the dba_db_links view to know who is the owner of that dblink.

SQL> select * from dba_db_links where username='TESTUSER';

And you can get all the SQL fired by that user using either auditing or tracing.

  I've tried querying v$session (on SID and AUDSID columns) and v$active_session_history (on SESSION_ID) on both DB1 and DB2 in an attempt to get more information. However, all attempts to query these tables on either database using either SESSIONID or OS_PROCESS values have failed (0 results returned).

You can't get information from these views on DB1 because that information is belongs to testuser which has created session on DB2.


How do I place an UpdateLock on a row with an index

Two seeks, and lock on PK,

select id
from Accounts WITH (UPDLOCK)
where id = (select a.id from Accounts a where a.OwnerId=1)


We talked about a similar issue before here and here and what worked in that case was setting the SQL Server to be dependent on the disk drivers.  You'll want to test this to make sure this works in your setup but it should do the trick.


Are all matching indexes (indices) searched for a query?

It depends very much on the DBMS being used. Some can never user more than one index per table and query other can do so.

But if there are multiple indexes sharing the same column, I doubt that even the DBMS that can use more than one index would do it, especially if the leading columns are identical.

If there is an index on (foo)  and one on (foo, bar) the first one is redundant as every query that would benefit from it could also use the second one.

I highly recommend the site "Use the index Luke", it has a lot of in-depth information about how indexes work and how they are used including the differences for most of the popular DBMS


Do I need to update an index include columns every time I add a new column

The missing index code is extremely simple in what recommendations in suggests. One thing is that it doesn't care about selectivity.

The reason to include columns is to cover a query. I.e., so you don't have to do a bookmark lookup for each row. If you have a rather high selectivity, then these lookups don't matter much, and it is extreme overkill to include bunch and bunch of columns. I.e., skip the included columns and just focus on the WHERE cause and the index keys.

So, don't give those "missing index" recommendations much credit. At all. But they can serve as starting points for your own analysis.

To cluster the "child table" om the FK is another often forgotten trick. As already mentioned by Laughing Vergil.


Calculating time spent on query cache lock

Half a gigabyte is too large for the query cache.  This write-up is several years old but still very applicable:

http://www.mysqlperformanceblog.com/2007/03/23/beware-large-query_cache-sizes/

In MySQL 5.5., you can benchmark this using profiling but the real problem isn't how long any particular query waits for the lock, because the answers will generally be all across the board and largely unrelated to the query itself or the tables, because the query cache is accessed by a global mutex that every query contends for.

There is apparently supposed to be a timeout to prevent long waits, but which does not work correctly, according to Bug #68573.

Your fix would be a more reasonable query cache.

I dissected and described a little bit of the query cache's inner workings here.


The convert function can do that. And if you want dd mm yyyy format 103 (with / separator) or 104 (with . separator) would be fine.

select c.InFavourOf as name,
       convert(varchar(10), c.ChequeDate, 103),
       p.Amount as amount,
       m.No as CNo
from Cheque c
  inner join PaymentDetail p
    on c.MasterID= p.PaymentMasterID
  inner join PaymentMaster m
    on p.ID=m.ID

If you want space separator, use replace function.

replace(convert(varchar(10), c.ChequeDate, 103), '/', ' ')


You can select from your table into a semicolon-delimited string.  Pass that string variable to the SQL send mail procedure.

DECLARE @EmailRecipients VARCHAR(MAX);

select @EmailRecipients = COALESCE(@EmailRecipients + ';', '') + Email
from EmailMe

SELECT @EmailRecipients


Need a better way to insert non-existent values in referenced table during insert/update in referencing one

Instead of trying to insert directly into the ball table you could insert your data into a temporary newdata table ...

CREATE TEMPORARY TABLE newdata (
    name VARCHAR(255),
    colorname VARCHAR(255)
    );

INSERT INTO newdata (name, colorname) VALUES
    ('Big Yellow Ball', 'yellow'),
    ('Small Purple Ball', 'purple');

... then perform an INSERT INTO color to create entries for any color names that don't already exist ...

INSERT INTO color (name)
SELECT colorname FROM newdata
WHERE colorname NOT IN (SELECT name FROM color);

... and then INSERT INTO ball with

INSERT INTO ball (name, color)
SELECT newdata.name, color.id
FROM newdata INNER JOIN color ON newdata.colorname = color.name;


To "improve query" response on reading data, replication will help. You could write away to your primary and read from secondaries to distribute the queries. Also the primary then is relieved of the expensive reads, and can be busy with only writing. Just be aware that there can be some lag on the data. For example a forum, it's not 100% necessary that readers can see the post within milliseconds after the insert. 1 or 2 seconds are acceptable.

The biggest advantage of replication is "backup and high availability" in case your primary goes down.

There is a some improvement with sharding if you choose a good shard key. Writing queries away 'might' be distributed if you do that correctly.

The main reason for sharding is to "horizontally expand your database". Working with big data, and not wanting to create/insert bigger and bigger disks ... you can just create new servers next to it, as much as you want. So, instead of maintaining 1 disk of 10TB, you could have 10 server is 1TB and grow bit by bit.

So, both can improve, ... on a write intensive system you might want to choose sharding, ... on a read intensive system, replica might be advised. But if you have a lot of data, choose sharding with replicas.

Example:

Server 1 :  Shard1Repl1 + Config    (datacenter/zone/network 1) write
Server 2 :  Shard1Repl2             (datacenter/zone/network 2) read
Server 3 :  Shard2Repl1             (datacenter/zone/network 1) write
Server 4 :  Shard2Repl2 + Config    (datacenter/zone/network 2) read
Server 5 :  Shard1Arbiter + Shard2Arbiter + Config  (datacenter/zone/network 3)  small server addon


Transferring Mongodb database with rsync while working, is it okay?

No, not directly! If your data is top of LVM, do a snapshot and copy that to another node, start it and connect it to the replica set. This only if you have lot's of data and normal initial sync (as @SqlWorldWide wrote) would take too long time.


You've seen the right behaviour since the default storage engine in 3.2 is WiredTiger(WT). The Wired Tiger provides up to 80% of the data compression to your data.

Just to verify please note db stats before migrating and then after migrating the dbs to 3.2 to ensure you have got your data back.


Vulnerability in Server from SQL Server 2008 R2

The web components are/were used for the cube browser in SSMS (That changed in SQL Server 2012 IIRC), so you could probably remove it if you are not using that functionality and only browse the cubes from other clients, but that's an untested configuration so who knows for sure. It will definitely be unsupported.

As you state:

  Can we remove that component without breaking SQL server engine

If all you care about is the SQL Server engine (and not the Analysis server) you could consider removing Analysis services altogether.

However, I don't see why you would want to do that as according to the documentation this is fixed in SQL Server 2008 R2 by simply installing updates for Office.

Also according to that page it doesn't seem like it's the office 2003 web components you have installed with your version of SQL Server but the Office 2007 web components as the link for SQL Server 2008 R2 is pointing to the update for Office 2007

If I were you I would prefer patching the system than taking the risk of removing a component that came with the installation.

And as vonPryz mentioned in a comment. It's about time to look at upgrading to a more recent version of SQL Server if you are worried about support anyway.


Error when restoring SQL Server 2008 R2 backup on 2012

Do a Restore Headeronly With File = '...'  If it still fails it's not a valid native SQL Server backup file. It's not an issue of versions.

Open it with a hex or text editor, what's the file header look like? Maybe it's compressed with a 3rd party solution like LiteSpeed and those require you to use a tool to convert to native format if you don't have it installed in this environment.


PostgreSQL: set default privileges on schemas themselves?

As mentioned by @a_horse_with_no_name, this is now implemented in PostgreSQL v10

  ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered.

Example,

ALTER DEFAULT PRIVILEGES
  GRANT USAGE ON SCHEMAS
  TO role;


In your configuration you got max-connecions = 500 this means you acutally have 500 connections available to your server, and mysql adds an extraslot for and adminuser who's able to access over a terminal.

So you actually should always be able to connect to your Database even when all connections are used up.

Try to evaluate who/what is using up your administration connection, and get rid of it. Reasons i encountered are poorly written cronjobs which connect via shellscript to your DB (for example a self written backupscript) this takes a long time to run and locks you out of your Database.

Also check your open files limit on your OS in most distribution these values need to be raised when you have some heavy traffic on your server.


Issues deploying SSIS package to SQL Server in Azure AD Domain Services (AADDS) environment

My solution was to create a shortcut to the following command:
C:\Windows\System32\runas.exe /netonly /user:targetuser@aaddscontoso.com "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\devenv.exe"

Replace targetuser@aaddscontoso.com with the user name of your Azure Managed Domain (AADDS).
Make sure you are pointing to the path of the correct Visual Studio shell. In my case I have SSDT for Integration Services install on the shell of Visual Studio 2019 Enterprise.

If you want this on your start menu, you can open the start menu location on Windows Explorer at: C:\Users\YourLocalHostUser\AppData\Roaming\Microsoft\Windows\Start Menu\Programs
I created a shortcut just like this:

I might even create my own icon (some sort of mesh between VS icon and the Azure AADDS icon).
Now, the only caveat (not really) is that you must provide the password of the the target credentials at a command prompt upon launching the shortcut. I think it is a nice compromise, to be honest.
Alternatively, you could download Sysinternals ShellRunAs to have a nicer GUI at the start, and use it instead of the runas.exe. But not only would you still need to provide the password upon launching the shortcut, but the username as well!


Following is a short graphic which I will be using to explain when orphans are created in the incarnations of a database. It is a variation of the graphic I used to explain incarnations in my answer to the question Can anyone explain to me the concept “incarnation” in Oracle database in an easy-to-understand way?

I hope you enjoy the journey.

                                          restore db    +-----+     +-----+     +-----+
                                          recover db    | 2>3 | --> |  3  | --> |  3  | -->  ...
                                          resetlogs     +-----+     +-----+     +-----+  ^
                                                            ^ Incarn   3           3     |    3
                                                           /  SCN #   500         600    |   700
                                                          /                              |
                                                         /                               |
             restore db    +-----+          +-----+     +-----+                          |
             recover db    | 1>2 | -------> |  2  | --> |  2  | -->  ...                 |
             resetlogs     +-----+          +-----+     +-----+  ^                       |
                           ^       Incarn.     2 \         2     |    2                  |
                          /        SCN #      300 \       400    |   500                 |
                         /                         \             |                       |
                        /                           + --------------------+              |
        +-----+     +-----+     +-----+                          |         \    +-----+  |  +-----+
    --> |  1  | --> |  1  | --> |  1  | -->   ...                |          +-> | 2>4 | --> |  4  |
        +-----+     +-----+     +-----+  ^                       |   restore db +-----+  |  +-----+
Incarn.    1           1           1     |     1           2     |   recover db          |     4
SCN #     100         200         300    |    400         400    |   resetlogs           |    400
                                         |                       |                       |
Backup   11:00 ----- 12:00 ----- 13:00 ----- 14:00 ----- 15:00 ----- 16:00 ----- 17:00 ----- 18:00
                                         |                       |                       |
Restore/                                (1)                     (2)                     (3)
Recovery

Restoring the Database to Point in Time (1)

Somewhere slightly after 13:00 (1pm)  somebody decides that the database has to be restored to 12:00 (12 o'clock midday). The DBA either sets off a bunch of RMAN commands to restore the database to that point in time or clicks his way through a fantastic GUI to initiate a restore/recovery from a 3rd-party vendor.

RMAN retrieves the FULL backup of the database and all Archive Log backups from disk/tape and restores them to the disk. In the recovery phase RMAN will check that all relevant information is available and roll forward all finished transactions to the Point in Time and roll back all unfinished transactions to the Point in Time, to ensure the database is in a consistent state.

Before the database can be opened to the general public, the database has to ensure that all future backups don't conflict with the previous backups. This is when a new incarnation should be created and it happens when you execute the following command to open the database:

ALTER DATABASE OPEN RESETLOGS;

You can run the following script against your instance to retrieve a hierarchical view of your (current) incarnations:

set pages 50               --- repeat header every 50 records
set lines 230              --- set lines(ize) length to 230
column path format a40     --- set column path to alpha-numeric 40
alter sessiosn set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
                           --- set date format of date columns to something more detailed
select
    INCARNATION#,
    PRIOR_INCARNATION#,
    RESETLOGS_CHANGE#,
    RESETLOGS_TIME,
    STATUS,
    SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path
    FROM v$database_incarnation
    WHERE LEVEL >=1 START WITH INCARNATION# = '1'
        CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION#
    ORDER BY LEVEL, Path, RESETLOGS_TIME;

The current incarnation of the database will be similar to this:

INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS  PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
           1                  0                 1 2017-03-08 15:57:31 PARENT   -> 1
           2                  1               200 2018-07-27 13:20:00 CURRENT  -> 1 -> 2

Using the graphic we can see that we have moved from the path containing the incarnation 1 to the path with the incarnation 2, because we have opened the database with RESETLOGS and the database has created a new incarnation.

Restoring the Database to Point in Time (2)

Let's again assume the database keeps on running after the first restore/recovery action and slightly after 15:00 (3pm) somebody decides there needs to ba a new restore/recovery back to full hour at 15:00 (3pm) the same day.

RMAN will restore the files, recover the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 3 and the first backup at 16:00 will contain the information:

INCARNATION#    3
SCN#           500
Time......... 16:00

If we query the incarnations in the database using the above script we will get something like this:

INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS  PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
           1                  0                 1 2017-03-08 15:57:31 PARENT   -> 1
           2                  1               200 2018-07-27 13:20:00 PARENT   -> 1 -> 2
           3                  2               400 2018-07-27 15:20:00 CURRENT  -> 1 -> 2 -> 3

Restoring the Database to Point in Time (3)

Let's again assume the database keeps on running after the second restore/recovery action and slightly after 17:00 (5pm) somebody decides there needs to be a new restore/recovery back to 14:00 (2pm) the same day.

RMAN will restore the files, recovery the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 4 and the first backup at 18:00 will contain the information:

INCARNATION#    4
SCN#           400
Time......... 18:00

If we query the incarnations in the database using the above script we will get something like this:

INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS  PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
           1                  0                 1 2017-03-08 15:57:31 PARENT   -> 1
           2                  1               200 2018-07-16 13:20:00 PARENT   -> 1 -> 2
           3                  2               400 2018-07-17 15:20:00 ORPHAN   -> 1 -> 2 -> 3
           4                  2               300 2018-07-17 17:20:00 CURRENT  -> 1 -> 2 -> 4

What has happened? We have an orphan!

Orphaned Incarnations...

If you look at the graphic, we are currently standing on the square at 18:00 (6pm) with the Incarnation 4 and the SCN 400. Now if you follow that line back to the beginning, you can see that we would go from incarnation 4 back up to incarnation 2 and then back down to incarnation 1, which is when the database was created.

This also corresponds to the (simplified) output of my scripts.

INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS  PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
           4                  2               300 2018-07-17 17:20:00 CURRENT  -> 1 -> 2 -> 4

So what happened with incarnation 3? Is the Incarnation 3 bad or stale or what gives?

Answer

No, the incarnation 3 isn't bad, it's just orphaned.

On a larger scale with more time between backups and restores, you could still restore/recover the database to a point in time in the lineage of incarnation 3. You would set off the following command:

RESET DATABASE TO INCARNATION 3;

...and then restore/recover the database to that point in time like you would other restore/recover a database.

What the ORPHAN status does tell you, is that the incarnation 3 is no longer related to the current state of the database with the current incarnation 4. The orphaned incarnation 3 is no longer required to restore/recover the database along the current timeline.

...Result in Obsolete Backups

Now looking at the database backups in relation to the orphaned incarnation, well RMAN determines that the orphaned incarnation's backups are OBSOLETE. But that is a story for a different Q & A...


How to ensure the quick switch from Log shipping to Mirror in SQL Server 2012

Below would be my steps :

disable all backup and copy jobs on primary and secondary.
manually run the copy and restore jobs couple of time to ensure that all the log backups are applied to secondary.
remove logshipping using GUI
restore database on secondary using NORECOVERY - if db is readonly or standby mode.
Enable mirroring using scripts or wizard

this is your logshipping to mirroring conversion.

Make sure you take regular log backups on the principal else your log file will grow.


There are generally 2 approaches:

Store all in one table, with a timestamp / version number.
Store the current version in one table, with an archive table for the older versions.

The first makes sense in a data warehouse mostly, while the second approach allows applications to be easier (as long as they do not care about the historical data). The archive table can be automated via triggers.

Having one table per state is the worst of post worlds - it makes application development horrendous, particularly as you have to rework all applications when you add an interim state. It also makes referential integrity painful.


Cannot Connect to Oracle Database running on Windows 8 Hyper V Virtual Machine

Ultimately, this came down to a networking issue -- configuring the guest VM's network adapter (internal switch) with a static IP (this was the hard part), assigning a hosts file entry for that IP in both the Host and the Guest machines and then altering both tnsnames.ora files (host and guest) and listner.ora (guest) to use the mapped host name.

Then everything just worked.

I've pasted my internal switch config for both the host and guest and the listener and tnsnames.ora files below.

STEPS TO CONFIGURE THE GUEST VM / HOST:

Assuming that you've already created a guest VM, with a Windows Server OS, here are the networking steps...

Using Hyper-V manager, use the virtual switch manager add an "Internal" type switch
Using Hyper-V manager, open the settings dialog for the guest VM (right click on the VM).  Add a network adapter using the internal switch you just created.
Here are my internal switch adapter settings for the host.  Note that there is a baked in assumption here that you're outside the range of IP's that would otherwise appear on your network.  I know that my home network uses this range (192.168.x.x), and I know I'm ok.  Also, I know that my workplace uses 10.x.x.x for wireless, so I'm good there, too.

4 . And here's how I configured the guest VM:

5 . I added hosts file aliases for the guest VM on both the host and the guest:

# internal vm's
192.168.0.120    brettvm

6 . Here's the listner.ora on the guest vm (database server):

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

ADR_BASE_LISTENER = c:\oracle

7 .  Here's the tnsnames.ora on the guest VM:

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

8 . Finally, here's the tnsnames on the client (host machine):

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Bon appetit!

--Brett


Can I secure SQL Server symmetric key from [sa] account?

Nope. The best you can do (and should be doing) is to:

Rename SA
Use Windows authentication only
Disable SA (also see #2)

However, SA is just a login. The server roles of sysadmins gives full privileges over the system.

The only way to secure a symmetric key using SQL Server against a sysadmins is to use multiple encryption techniques involving the users and/or the application.

Note that always encrypted and HSMs use Asymmetric keys.

  Can I create symmetric key using local user certificate via MMC?

That's not going to help you with this issue.


Return a default value in group by if selected column is null

You should be able to use COALESCE(your_column, 'Unknown'):

  The COALESCE function returns the first of its arguments that is not null.

This is not specific to GROUP BY, although you'll certainly need to use it both in your SELECT column expression and in the GROUP BY expression (as usual when using GROUP BY).


You should not install more than one Oracle Client (per architecture) on one machine. For sure they do not work in parallel.

By heart I know several issues:

You would have to change PATH settings in order to determine which Client you want to use. TNS_ADMIN environment variable is used by both versions and must be set accordingly. Oracle OleDB provider can be installed only once (per architecture). Version of Oracle Data Provider for .NET (ODP.NET) must match the client version. Using ODBC can be also a challenge.


The cardinalities are estimates.
They have limited use, so don't worry if the values are as far off as you see.

If you are worried that some queries are being Optimized inadequately, let's see the queries, SHOW CREATE TABLE, and EXPLAIN SELECT ...  There are many things that can be looked at; most do not involve cardinality.

A typical solution involves a "composite" index.  For example:

WHERE a=1 AND b=2

If you have INDEX(a), INDEX(b), the Optimizer will use only one of the indexes and may use cardinality to decide between them.

If you have INDEX(a,b), the decision is obvious, and cardinality is irrelevant, and the query will run faster.


Users Export From Mysql Version 8.0 to 5.7

Issue fixed. I found mysql tables (db & user) columns available in 5.7. So i copy these tables from 8.0 to 5.7. And copy data only for available columns in tables db & user of mysql schema. Now its working fine.


Calculating the median value in a MySQL table with CodeIgniter

Not being familiar with codeigniter, I am doing this first in SQL, and then make a guess on how to translate it back into codeigniter:

I translated your query to SQL first"

SELECT budget_breakdown.breakdown_grants
FROM survey_respondent_info
LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
WHERE budgets.budget_option_id  = 2;

Now, I alter the query to find the midpoint:

SELECT CEIL(COUNT(*)/2
INTO @midpoint
FROM survey_respondent_info
LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
WHERE budgets.budget_option_id  = 2;

Finally, I use the @midpoint variable to get the median:

PREPARE STMT FROM
   'SELECT max(budget_breakdown.breakdown_grants) FROM (
    SELECT budget_breakdown.breakdown_grants
    FROM survey_respondent_info
    LEFT JOIN budgets ON budgets.survey_id=survey_respondent_info.survey_id
    LEFT JOIN budget_breakdown ON budgets.budget_id=budget_breakdown.budget_id
    WHERE budgets.budget_option_id  = 2
    ORDER BY budget_breakdown.breakdown_grants LIMIT ?) x';

EXECUTE STMT USING @midpoint;

I went through the PREPARE and EXECUTE, because LIMIT doesn't work directly with a variable, this is a workaround.

Now put it back into codeigniter:

function reports_median() {

    $this->db->select('CEIL(COUNT(*)/2');
    $this->db->from('survey_respondent_info');
    $this->db->join('budgets',
                    'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
    $this->db->join('budget_breakdown',
                        'budgets.budget_id=budget_breakdown.budget_id' , 'left');
    $this->db->where('budgets.budget_option_id', 2);
    $query1 = $this->db->get();
    $midpoint = $query1->result();
    $midpoint = $midpoint/2;

    $this->db->select('max(budget_breakdown.breakdown_grants');
    $this->db->from('survey_respondent_info');
    $this->db->join('budgets',
                    'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
    $this->db->join('budget_breakdown',
                        'budgets.budget_id=budget_breakdown.budget_id' , 'left');
    $this->db->where('budgets.budget_option_id', 2);
    $this->db->order_by('budget_breakdown.breakdown_grants', 'asc');
    $this->db->limit($midpoint);
    $query2 = $this->db->get();
    $median = $query2->result();

}

As I said, I am not familiar with codeigniter and can't test that part. It is a good guess.


How do I calculate working business minutes?

If your reports are based on a data warehouse that is dimensionally modelled (generally the case for business intelligence solutions), it is highly probable there is a date dimension and time dimension.

If there are no such objects, it may be very useful to create them as they become highly re-usable as other reports are required.

In the date dimension, there should be an IsHolidayFlag column. In the time dimension, there should be an IsBusinessMinuteFlag column (depending on the grain of this dimension, it may be IsBusinessSecondFlag).

The query would then be relatively straight forward and quick, provided the indexes are correct. There is an overhead with the CROSS JOIN (which could be created as a view for re-usability), but for 200,000 rows, it should be negligible.

SELECT  i.IncidentID,
       'WorkMinutesInRange' = SUM(CASE WHEN d.IsHolidayFlag = 'N' AND t.IsBusinessMinuteFlag = 'Y' THEN 1 ELSE 0 END)
FROM    dbo.Incidents i
        INNER JOIN Dim_Date d
            ON d.CalendarDate BETWEEN i.CreatedOn AND i.ResolvedOn
        CROSS JOIN Dim_Time t
GROUP BY i.IncidentID


Very slow IndexOnlyScan on partial ~16MiB indexes

If 15% of the table rows are dead, and those dead rows are concentrated in the rows which meet the partial index conditions, then that could be up to 1500% bloat in the index itself.  (For this reason, tables with partial indexes often need to be vacuumed much more aggressively than the default settings).  But, it might not be necessary to use this high bloat to explain the slowness, because the lack of visibility map setting might be sufficient to explain it.

  My coworker mentioned that maybe it is checking visibility map to see if tuples in index are still live or not, but would that explain queries to take 30-70 minutes on a 17MiB index?

Yes.  17MiB of index can reference gigabytes of table, and since it has to check that table for visibility, that means it can be thrashing through gigabytes of table data in random order being driven off of 17MiB of index.

Looking at your slowest plan, if all 239413 buffer misses had to be read from disk and you have 7200 rpm disks, that alone would explain half your execution time.  If there were several such queries running at the same time competing for disk access, it could explain all of the execution time.

While it is technically using an index only scan, you are getting basically zero benefit from this because of the visibility map issue.

Either the table isn't being vacuumed enough, or the effects of hot_standby_feedback are rendering the vacuuming largely ineffective.  (Or possibly the table has such a high churn rate that trying to keep the visibility map topped up is a hopeless task, and you need to give up on index-only-scans being a useful tool for you on that partition)


SQL Server 2016 Availability Group on DPM 2016

If you have added the cluster, as in the WSFC computer name, to DPM then this may not be correct according to the MS documentation and may explain why you have nothing to backup - https://docs.microsoft.com/en-us/system-center/dpm/back-up-sql-server?view=sc-dpm-1807

Try adding the Availability Group Replicas individually. DPM should detect the AG configuration automatically and, provided all replicas are readable, it should backup up according to the AG backup preferences.


How do I optimize my transaction level running balances cartesian join?

You can get the day of the first transaction for every customer dynamically. With proper indexing this is only half painful. There is ongoing work to include "index skip scan" in the next version of Postgres (13 as of writing) to make it less painful. See:

Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest row per user

But it would be simpler and cheaper to save that information. It would seem that the date of the first transaction per customer should not change later, like a birthday? (And you confirmed as much in an update.) Let's call it first_transaction_at to fit in with your current schema.

For customers that have yet to make their first transaction (first_transaction_at IS NULL) you can still check dynamically, or attempt to update every time before you run the query.

Or if you can't or won't change the table definition you might add a MATERIALIZED VIEW for just that purpose: to keep track of first_transaction_at per customer.

The updated table can look like:

CREATE TABLE customers (
   customer_id varchar(255) PRIMARY KEY,
   city varchar(255) NULL,
   state varchar(255) NULL,
   postal_code varchar(255) NULL,
   first_transaction_at timestamp,   -- can be NULL !
   inserted_at timestamp NOT NULL,
   updated_at timestamp NOT NULL
);

Then you can use a query like this:

SELECT c.customer_id, d.the_day
     , sum(t.tsla_amount) OVER w AS tsla_running_amount
     , sum(t.goog_amount) OVER w AS goog_running_amount
FROM   customers c  -- !
CROSS  JOIN LATERAL (
   SELECT the_day::date
   FROM   generate_series(first_transaction_at  -- !
                        , date_trunc('day', localtimestamp)
                        , interval '1 day') the_day
   ) d
LEFT   JOIN (
   SELECT customer_id
        , created_at::date AS the_day
        , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount
        , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount
   FROM   transactions t
   WHERE  created_at >= timestamp '2020-01-01'
   GROUP  BY customer_id, created_at::date
   ) t USING (customer_id, the_day)
WINDOW w AS (PARTITION BY customer_id ORDER BY the_day)
ORDER  BY customer_id, the_day;

db<>fiddle here

The point being that you don't use the complete range of days for every customer, but a customized range, generated in the LATERAL subquery.

The CROSS JOIN removes customers with first_transaction_at IS NULL from the query as no rows are produced in the subquery.

Aside 1: The data type varchar(255) typically is a misunderstanding in Postgres - and a suboptiomal idea for a PRIMARY KEY. See:

Refactor foreign key to fields

Aside 2: Consider timestamptz instead of timestamp. See:

Ignoring time zones altogether in Rails and PostgreSQL


MySQL refuses UPDATE with (1071, 'Specified key was too long; max key length is 1024 bytes') sometimes

(nimrodm's answer is better, but here are some workarounds)

VARCHAR(255) -- check the data; you probably do not need 255 in most fields.
COLLATE utf8_unicode_ci may be unnecessary for some of your columns.  Consider CHARACTER SET ascii.  utf8 takes 3 bytes toward the limit nimrodm mentioned; ascii takes only 1.
Consider normalizing those long strings.  MEDIUMINT UNSIGNED, for example, is only 3 bytes (1..16M range) -- a lot less that 3*255.


Neither MySQL, nor Mariadb (https://mariadb.com/kb/en/library/revoke/), has implemented RESTRICT or CASCADE.

In general, a REVOKE can negate only an identical GRANT.  You cannot, for example, GRANT ALL ..., then REVOKE UPDATE ....

That is, the GRANT/REVOKE feature of MySQL and MariaDB is quite limited.

"Roles" are available in MySQL 8.0 and MariaDB 10.0.  (But I don't think that is relevant to your question.)

  Syntax naturally differs a little from dbms to dbms.

Change "a little" to "sometimes a lot".


Free space from image column after update

Removing data will not release the space used by to the filesystem unless you then perform a database (or per file) shrink. The newly unallocated space will get used by new data as it comes in before any more is claimed from the OS by growing the relevant files.

If you need to fully reclaim the space then you need to look at DBCC SHRINKDATABASE and DBCC SHRINKFILE, though don't do this unless you have to as it can cause internal fragmentation that if allowed to build up can impact performance.

When you take a backup only used pages are included, so you will see backup sizes drop even though the actual database size has not reduced.


Add database to existing MySQL master-master replication which uses binlog_do_db?

Master-Master is simply Master-Slave in both directions.  So all the settings work the same.

Data does not endlessly loop between the two Masters as long as they don't have the same server_id.

I'm pretty sure you simply have multiple lines.  See https://forums.mysql.com/read.php?26,255998,255998

M-M is fraught with perils, but I guess you have gotten past them.  So I won't harp on it.


How to connect to a Database made by Oracle SQL Database?

Try to connect with

 sqlplus SYS/PASSWORD@ORCL

If you can connect with this from command line you can connect with Sql Developer.

You have two options

Connection Type : Basic

SID = orcl
Username = SYS
Password = password
Hostname = YOUR_MACHINENAME_OR_IP

See Image

Connection Type : TNS

Choose SID from dropdownlist. It should read your TNS File and auto complete your dropdown list.
Username = SYS
Password = password

The catch here. Oracle Connection string  for OCI expect same argument in your tnsnames.ora file. If you use basic connection you should be able to connect from everywhere without modification. But if you need to connect with other connection types, you may need to enter your IP address in your tnsnames.ora file.


Modeling an entity-relationship diagram about Clients, Orders, Payments and Bills

First of all you have to define your entities.

So I see 4 entities:

Client
Order
Product
Payment

With these 4 entities you can create your bill from the information that is already stored in the database.

After you have decided on your entities you need to normalise them before you can go near the ER diagram.  With the ER diagrams that you provide, I do not see 1:1 or 1:M relationships.  I can see that order has a 1:M relationship with product and that client has a 1:1 relationship with Order.  Perhaps there is just a 1:1 relationship between client a payment or can there be many payments?

In any case, the ER diagram should start with client as they start the transaction.  I can see a ternary relationship between order, product and payment.

Does this help?  I hope it does help you to think more about it.  I would not be happy with either diagram because they do not contain 'product'.  Can you try and add product and the type of relationships?


Where/How to generate the validation key and decryption key for SSRS View State Validation

You can generate the machine key through IIS Manager.

Use Server Manager -> Roles -> Web Server (IIS) -> IIS Manager. Click on the Web Server in the IIS Manager, then in the right pane, double click Machine Key in the ASP.NET section.


Get the 2nd or 3rd occurrence of a value in a delimited string

You can use cross apply and the third parameter of charindex to get the position of the underscores.

declare @T table
(
  Name_Level_Class_Section varchar(25)
)

insert into @T values
('Jacky_1_B2_23'),
('Johnhy_1_B2_24'),
('Peter_2_A5_3')

select substring(Name_Level_Class_Section, P2.Pos + 1, P3.Pos - P2.Pos - 1)
from @T
  cross apply (select (charindex('_', Name_Level_Class_Section))) as P1(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P2.Pos+1))) as P3(Pos)

Result:

-------------------------
B2
B2
A5

Update: Using your table the query would look like this:

select *,
       substring(Name_Level_Class_Section, P2.Pos + 1, P3.Pos - P2.Pos - 1) as CLA
from Bookings
  cross apply (select (charindex('_', Name_Level_Class_Section))) as P1(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex('_', Name_Level_Class_Section, P2.Pos+1))) as P3(Pos)
order by CLA asc,
         Name_Level_Class_Section asc

Update 2:

If you know for sure that your value never contains a period . and that it is always a four part name you can use parsename.

select *,
       parsename(replace(Name_Level_Class_Section, '_', '.'), 2) as CLA
from Bookings
order by CLA asc,
         Name_Level_Class_Section asc


get datetime difference of a specific time and value of date time column

This was trickier than I expected (been a while since I've used the date and time functions) - so I did the following.

Created a table:

CREATE TABLE times (t timestamp);

Then put in some random data:

INSERT INTO times VALUES ('2016-06-27 01:01:12');
INSERT INTO times VALUES ('2016-06-27 07:34:17');
INSERT INTO times VALUES ('2016-06-27 14:56:03');
INSERT INTO times VALUES ('2016-06-27 23:24:56');

And then ran the best function that I could find:

SELECT TIMESTAMPDIFF(MINUTE, t, '2016-06-27 12:00:00') AS tdiff FROM times;

And obtained the result (correct - in minutes):

tdiff
-----
658
265
-176
-684

You can, of course, divide (or multiply) by whatever factor you choose to convert the outputs to the units of your choice.

Depending on your needs, these might suit you better:

- SELECT TIMEDIFF(t, '2016-06-27 12:00:00') AS tdiff FROM times;

- SELECT UNIX_TIMESTAMP(t) - UNIX_TIMESTAMP('2016-06-27 12:00:00') AS tdiff FROM times;

- SELECT TO_SECONDS(t) - TO_SECONDS('2016-06-27 12:00:00') AS tdiff FROM times;

Following @AndrewBrennan's answer below, I've expanded on it somewhat - i.e. if one wished to run the query over a period of a week/month/quarter - whatever. It will perform the relevant calculation comparing datetimes on a given day in the datetime field with the fixed definite time - i.e. comparing like with like on the particular day in question.

SELECT TIMESTAMPDIFF(MINUTE,
                     t,
                     CONCAT(YEAR(t), '-',
                            MONTH(t), '-',
                            DAY(t),
                            ' 12:00:00'))
AS tdiff
FROM times;


How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2?

Search all code using sys.sql_modules

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules sm
WHERE definition LIKE '%Mytable%'

Or use Red Gate SQL Search which is completely free

Do not use syscomments or INFORMATION_SCHEMA.RUBBISH


How to alter an existing partitioned table to add sub-partitions to it?

Scenario 1 - redefine the table

Your SQL is adding a sub-partition, not redefining the table.  I suspect you need to redefine the table to have sub-partitions before you can add a sub-partition.

12.2c+

The ability to change a non-partitioned table into a partitioned table via ALTER TABLE was introduced in 12.2c.

Looking at the documentation, it appears that you can change a RANGE partitioned table into a RANGE-LIST partition in the same way.  I haven't tried; be cautious; use DEV for practice.

pre-12.2c

DBMS_REDEFINITION is the de facto way of changing a table between non-partitioned and partition designs.

Google is your friend.  But, here is my recommended link:

Scenario 2 - convert back to non-partitioned table.

This requirement perplexes me.  Doing such a massive change, multiple times, is probably a Tell-Tale sign that you have a more serious problem that you should be solving.

Any way, I suspect that the only way to do this is with DBMS_REDEFINITION.


UnknownError: error while getting last oplog entry for begin timestamp: find command not yet implemented

You cannot add a MongoDB 3.4.x member to a MongoDB 3.0.x replica set.

Replication between mixed versions is only supported for adjacent MongoDB major releases (for example, 3.0 and 3.2 or 3.2 and 3.4), and even then generally only intended to be used transiently for the purposes of a rolling upgrade.

  what does it mean this find command not yet implemented?

The find command was introduced in the MongoDB 3.2 production release series, and MongoDB 3.4 replication expects this command to be available.

  any workaround for having this 3rd server joined in the replica set?

Install MongoDB 3.0 or 3.2 for compatibility with your existing MongoDB 3.0 replica set members. If you add a new member with MongoDB 3.2, I would recommend upgrading your other replica set members to the same release version.

For more information see:

Upgrade a replica set to MongoDB 3.2
Upgrade a replica set to MongoDB 3.4


Mysql server requires to explicitly state the database in the query

It sounds like 5.7.19 jumped the gun.

----- 2016-02-05 5.7.11 General Availability -- Functionality Added or Changed -- -----

  The mysql_kill() ( /doc/refman/5.7/en/mysql-kill.html ), mysql_list_fields() ( /doc/refman/5.7/en/mysql-list-fields.html ), mysql_list_processes() ( /doc/refman/5.7/en/mysql-list-processes.html ), and mysql_refresh() ( /doc/refman/5.7/en/mysql-refresh.html ) C API functions are deprecated and will be removed in a future version of MySQL. The same is true of the corresponding COM_PROCESS_KILL, COM_FIELD_LIST, COM_PROCESS_INFO, and COM_REFRESH client/server protocol commands. Instead, use mysql_query() ( /doc/refman/5.7/en/mysql-query.html ) to execute a KILL ( /doc/refman/5.7/en/kill.html ), SHOW COLUMNS ( /doc/refman/5.7/en/show-columns.html ), SHOW PROCESSLIST ( /doc/refman/5.7/en/show-processlist.html ), or FLUSH ( /doc/refman/5.7/en/flush.html ) statement, respectively.

Recommend you post a bug with bugs.mysql.com .


Oracle multiple databases on same server

You can use the Multitenant option, create a container database, create 2 pluggable databases called db1 and db2, then import the first dump file to db1, the other dump file to db2.

You can switch between pluggable databases with as:

alter session set container=db1;
alter session set container=db2

But this requires at least version 12c, and it is an extra cost option.

For lower versions, or without the Multitenant option:

Simply create 2 traditional databases on the server: db1 and db2. Import one dump to db1, the other to db2. You can specify in your connection string the service (or SID) for the database you want to connect to. For example:

sqlplus user/password@hostname/db1
sqlplus user/password@hostname/db2


SQL Server 2008R2 XML Column Index strategy for large tables?

You should have a look at Property Promotion in XML Best Practices for Microsoft SQL Server 2005.

Create a user defined scalar valued function that extracts the value you need and use the function as a computed column in your table. You can persist the column of you like and you can create a regular index on the column. You don't have to persist the column in order to create the index.


Workaround for the limit of the number of missing indexes suggested by SQL Server's DMVs

You can't reset the DMVs, however you can work around this limitation and remove rows from the DMVs by creating a small filtered index on the tables mentioned in the DMVs then immediately dropping that index.

For instance:

CREATE INDEX IX_temp
ON dbo.SomeTable(SomeKey)
WHERE SomeKey IS NULL;

DROP INDEX dbo.SomeTable.IX_temp;

I've created a script to automate this process.

IF OBJECT_ID('dbo.RemoveMissingIndexSuggestions') IS NOT NULL
DROP PROCEDURE RemoveMissingIndexSuggestions;
GO
CREATE PROCEDURE dbo.RemoveMissingIndexSuggestions
(
    @Database SYSNAME = NULL --optional, if NULL, clear all suggestions
                             --if specified, only clear suggestions for that database
    , @Table SYSNAME = NULL --if not NULL, only clear suggestions for the specified table
)
AS
BEGIN
    /*
        Max Vernon, 2016-04-08
        Inspired by work by Joe Sack and Glenn Berry at
        http://www.sqlskills.com/blogs/joe/clearing-missing-index-suggestions-for-a-single-table/

        Creates one index for each table that is mentioned in sys.dm_db_missing_index_details
        then promply drops that index.  The index is created with a WHERE clause that is likely
        to eliminate all or almost all rows, and therefore will be created quite quickly.
    */
    SET NOCOUNT ON;
    DECLARE @ObjectName SYSNAME;
    DECLARE @DatabaseName SYSNAME;
    DECLARE @CreateStmt NVARCHAR(MAX);
    DECLARE @DropStmt NVARCHAR(MAX);
    DECLARE @stmt NVARCHAR(MAX);
    DECLARE @msg NVARCHAR(2000);
    DECLARE @vars NVARCHAR(1000);
    DECLARE @Uniquifier NVARCHAR(48);
    SET @vars = '@stmt NVARCHAR(MAX)';
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
    FOR
    WITH cte AS
    (
        SELECT ObjectName = d.name + '.' + s.name + '.' + o.name
            , DatabaseName = d.name
            , CreateStmt = N'CREATE INDEX [IX_temp]
ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N'(' + mid.equality_columns + N')
WHERE '
              + (
                SELECT TOP(1) cols.ColName FROM (
                    SELECT TOP(1) ColName = QUOTENAME(c.name) + N' IS NULL'
                    FROM sys.columns c
                        INNER JOIN sys.key_constraints kc ON c.object_id = kc.parent_object_id
                    WHERE c.object_id = o.object_id
                        AND kc.type_desc = N'PRIMARY_KEY_CONSTRAINT'
                    UNION ALL
                    SELECT TOP(1) QUOTENAME(c.name) + N' = -2147483648'
                    FROM sys.columns c
                        INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
                    WHERE ty.name IN
                        (
                              N'bigint'
                            , N'binary'
                            , N'hierarchyid'
                            , N'int'
                            , N'uniqueidentifier'
                            , N'varbinary'
                        )
                    ) cols
                )
                + ';'
                , DropStmt = N'DROP INDEX ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + '.[IX_temp];'
                , rn = ROW_NUMBER() OVER (PARTITION BY mid.object_id ORDER BY mid.index_handle)
        FROM sys.dm_db_missing_index_details mid
            INNER JOIN sys.objects o ON mid.object_id = o.object_id
            INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
            INNER JOIN sys.databases d ON mid.database_id = d.database_id
        WHERE o.name NOT LIKE '#%' -- ignore temp tables
            AND (d.name = @Database OR @Database IS NULL)
            AND (o.name = @Table OR @Table IS NULL)
    )
    SELECT cte.ObjectName
        , cte.DatabaseName
        , cte.CreateStmt
        , cte.DropStmt
    FROM cte
    WHERE rn = 1;
    OPEN cur;
    FETCH NEXT FROM cur INTO @ObjectName, @DatabaseName, @CreateStmt, @DropStmt;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @msg = 'Flushing ' + @ObjectName + ' indexes.

';
        RAISERROR (@msg, 0, 1) WITH NOWAIT;
        SET @stmt = 'EXEC ' + QUOTENAME(@DatabaseName) + '.sys.sp_executesql @stmt;'
        SET @Uniquifier = CONVERT(NVARCHAR(48), NEWID(), 0);
        SET @CreateStmt = REPLACE(@CreateStmt, '[IX_Temp]', '[IX_Temp_' + @Uniquifier + ']')
        SET @DropStmt = REPLACE(@DropStmt, '[IX_Temp]', '[IX_Temp_' + @Uniquifier + ']')
        SET @CreateStmt = 'SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
' + @CreateStmt + '

';
        SET @DropStmt = 'SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
' + @DropStmt + '

';
        RAISERROR (@CreateStmt, 0, 1) WITH NOWAIT;
        RAISERROR (@DropStmt, 0, 1) WITH NOWAIT;
        EXEC sp_executesql @stmt, @vars, @stmt = @CreateStmt;
        EXEC sp_executesql @stmt, @vars, @stmt = @DropStmt;
        FETCH NEXT FROM cur INTO @ObjectName, @DatabaseName, @CreateStmt, @DropStmt;
    END
    CLOSE cur;
    DEALLOCATE cur;
END
GO

This can be executed with the @Database parameter set to the name of a database to eliminate recommended indexes only related to that database, or without parameters to eliminate all recommended indexes.  One can optionally limit this to the suggestions for a single table by passing the name of the table into the @Table parameter.

EXEC dbo.RemoveMissingIndexSuggestions @Database = 'tempdb', @Table = 'SomeTable';

It creates at most one index per table. The index has a unique name and is constructed using a single column, giving priority to using the table's PRIMARY KEY, if it has one.  Tables will be missed by this proc if the table does not have a primary key or at least one of the following types of columns:

bigint
binary
hierarchyid
int
uniqueidentifier
varbinary

I've written a short blog post on this issue over at SQLServerScience


WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance

The Problems
You have two things working against you:

Local variables often have weird side effects
MAX datatypes can't be pushed, so you end up with late filters in your query plans

Some further reading:

Yet Another Post About Local Variables
MAX Data Types Do WHAT?
Why You Shouldn’t Ignore Filter Operators In Query Plans Part 1 | Part 2
Why does SQL Server use a better execution plan when I inline the variable?

In Particular
In your worse plan, there are two filter operators:

Each one is there to deal with the MAX type on @ProgramId. The length of the parameter doesn't matter much until you hit the max type, so long as it's the correct type, e.g. varchar = varchar or nvarchar = nvarchar
You may also see GetRangeThroughMismatchedType or GetRangeThroughConvertthere to deal with a date \ datetime mismatch:

Cast to date is sargable but is it a good idea?

You may find some overall relief by adding a recompile hint, but it won't fix the issue you're having around the MAX variable.


Indexing: Invalid default value for '[column]'

@jerichorivera is on point with the problem, I will just expand on what he is meaning.

From MySQL 5.6 to MySQL 5.7, MySQL changed the default SQL_Mode from a "loose" one to a stricter, standards-complient "MySQL strict mode". On it, dates like '0000-00-00' are not allowed, the first allowed data is '1000-01-01'. Other examples can be seen here: https://www.slideshare.net/jynus/query-optimization-with-mysql-57-and-mariadb-10-even-newer-tricks/45

If you want a quick fix now so the application unbreaks, you can reset the sql mode by setting it to NO_ENGINE_SUBSTITUTION, nothing will break and you will get the 5.6 behaviour.

However, you may want to eventually "fix" your schema- using '0000-00-00' for an invalid date is as bad as using the empty string ('') for an invalid string, or a variable with an uninitialized value in C. SQL has a value to represent invalid/unknown states, and that is NULL- my suggestion is to use it to represent invalid dates (could be done implicitly with the property NULL), however, you will have to work for the application to have into account this new "state" for the column, plus any other design supposition regarding ordering and comparison both on application and on database levels (e.g. supposing any comparison will always return true or false, and not  a third state NULL).

If using NULL is not possible or desirable, you can use a special value within the range allowed, it is not best practice, but at least it is not mixed with invalid inputs.

Maybe you can drop the column if it is no longer in use?

Once you have fixed all database issues, the TRADITIONAL/Strict modes will give you less headaches in data consistency.


I believe the best way to achieve that is by using the aggregation framework:

db.collection.aggregate(
    {$unwind : "$ccNameCount"},
    {$match : {
                "ccNameCount.ccName" : "ATCC"
    }},
    {$group: {
        _id : "$ccNameCount.paperCount"
    }},
    {$group: {
        _id : "count",
        total : {"$sum" : 1}
    }}
);

This will give you the distinct count of paperCount for ccName="ATCC" in the results total field.

Also, If you need to know each of the distinct values besides the total count you can use the $addToSet operator:

db.collection.aggregate(
    {$unwind : "$ccNameCount"},
    {$match : {
                "ccNameCount.ccName" : "ATCC"
    }},
    {$group: {
        _id : "$ccNameCount.paperCount"
    }},
    {$group: {
        _id : "count",
        total : {"$sum" : 1},
        distinctValues : {$addToSet : "$_id"}
    }}
);


As I said in my previous comment, you should have two main tables.

Observation (the origin of all cases)
Clinical_case (linked to an observation - note that "case" is an SQL keyword).

It appears to me that you are overcomplicating things with so many tables. I think that any report made by a health professional should have the same status - be it a health care worker or a vet - you can keep track of this with staff_id. Have a closed field to indicate the mouse is getting well or (gulp...) being euthanized.

What database are you using to track these experiements/observations? I would recommend PostgreSQL if you haven't yet made a choice. What is your development environment?

Go with the simple system system first - remember Occam's razor - "Entities should not be multiplied beyond necessity". But, should you really need to, you can make it more complex - as Einstein said, "Everything should be made as simple as possible, but not simpler." :-). Better to start simple rather than over-engineering.

[Edit - in response to OP's question about why PostgreSQL is better than MySQL]

more datatypes (IP address...)
CHECK CONSTRAINTS MySQL has workarounds (Triggers...) but these are clumsy and error prone,
CTEs - common table expressions - also known as the WITH clause or subquery refactoring,
Analytic functions,
a richer procedural language (or should I say language(S)),
a CREATE DOMAIN command - very useful and supported by Firebird, Oracle as well as PostgreSQL. Not by MySQL, but maybe by other RDBMSs.
generally (opinion now) - more of a focus on data integrity and correctness. MySQL's fame is built on the fact that it was quick (and nasty) - just perfect for the first Internet boom (1995 - 2000). It also was easy to set up on Windows (unlike PostgreSQL until version 8 (.3?)). It was also trendy with the GPL rather than the *BSD licence.


I have solved the problem. Within the loop through the 300 tables, the code was creating a new ADOX.Catalog object, appending a new linked table, then setting the catalog object to nothing.

I made one simple change: create the catalog object before the loop, and set it to nothing after the loop. After this change, the relinking takes only about 10 seconds.


What is the difference between these two index declarations and do I need both?

They satisfy different queries.

The first one will be useful for queries that seek on ColA = X and return ColB (without the INCLUDE this would require a lookup back to the base table)

The second one will be useful for queries that seek on ColB = Y

Both of them can be used for queries like ColA = X AND ColB = Y but the second one will do this more efficiently as the first index would require a seek on ColA then the residual predicate be evaluated against all matching index rows.