Why does CREATE INDEX ... WITH ONLINE=ON block access to the table over a period of minutes?

When creating an index with online = on, the create index process will not block when creating the index object itself, but when it comes to near the end of the process, it will acquire a schema modification lock* for a period in order to actually add the index to the table, this lock type will block all outside operations until the lock is released, which could account for your blocking issues.

* An Sch-M lock is not required for online build of a new nonclustered index, though it is required in all other cases. A new nonclustered index requires only a table-level shared lock during the final phase, same as was needed during the preparation phase.

See this White Paper for details:

Online Indexing Operations in SQL Server 2005

As suggested by Mushtaq Mohammed in a comment on the question, also see:

Unicorns, rainbows, and online index operations by Paul Randal


This does not work because it's trying to cast a jsonb value to integer.

select data->'name' as name from persons where cast(data->'age' as int) > 25

This would actually work:

SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25;

Or shorter:

SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25;

And this:

SELECT data->'name' AS name FROM persons WHERE data->>'name' > 'Jenny';

Seems like confusion with the two operators -> and ->> and operator precedence. The cast :: binds stronger than the json(b) operators.

Figure out type dynamically

This is the more interesting part of your question:

  the type of age in the JSON document is number anyway, so why can't PostgreSQL figure out that by itself?

SQL is a strictly typed language, it does not allow the same expression to evaluate to integer in one row and to text in the next. But since you are only interested in the boolean result of the test, you can get around this restriction with a CASE expression that forks depending on the result of jsonb_typeof():

SELECT data->'name'
FROM   persons
WHERE  CASE jsonb_typeof(data->'age')
        WHEN 'number'  THEN (data->>'age')::numeric > '25' -- treated as numeric
        WHEN 'string'  THEN data->>'age' > 'age_level_3'   -- treated as text
        WHEN 'boolean' THEN (data->>'age')::bool           -- use boolean directly (example)
        ELSE FALSE                                         -- remaining: array, object, null
       END;

An untyped string literal to the right of the > operator is coerced to the respective type of the value to the left automatically. If you put a typed value there, the type has to match or you have to cast it explicitly - unless there is adequate implicit cast registered in the system.

If you know that all numeric values are actually integer, you can also:

... (data->>'age')::int > 25 ...


If there are faults found since it dropped out of the extended support period then unless you have paid Microsoft for extended extended support you can be pretty sure there is not a fix available to you.

Obviously you can mitigate the risk considerably by following standard practise and making sure the only machines that can touch your SQL instance are a select few application servers within your firewall(s) and making sure all of them are fully patched up & so forth.


Activity Monitor Blocked by Firewall. Set Static Port?

Activity Monitor requires the firewall to allow access to the SQL Server port (default 1433) for SQL query access and the RPC ports for WMI access for things like the Processor, Network, Disk IO and Memory graphs. This blog describes this and the required permissions for viewing all of Activity Monitor.

This article describes how to configure a static port range for RPC on a machine to allow you to open those ports on the firewall, otherwise, you have to open a substantial range of ports. Due to the number of applications that can rely on RPC ports, you shouldn't restrict this range to a single port, but instead, use a range size suitable for the number of applications and that your security team is comfortable with opening on the firewall.


Upgrading OS and then upgrading to SQL Server 2016

OS changes, in my opinion, should always be done side-by-side, never in-place.  In this scenario, SQL 2016 requires Windows Server 2012R2 or better, so you'll need to view it as 2 separate, yet equally important migrations.  My recommendation would be to do this in 2 phases:

1- Stand up a new server with Windows 2012R2.  Odds are there will be hardware upgrades on the new server as the old server was probably built years ago. install the current version of SQL that you are using then migrate a copy of production over to it.  Bang away at it and make sure the OS upgrade isn't causing any undesirable behavior.  After testing is complete, migrate production to the new server (SQL 2008R2, Windows 2012R2) and let it soak in for a few weeks.  If everything is good and you've addressed any issues, proceed to step 2.

2- Stand up a new server with Windows Server 2012R2 and SQL 2016, copy production DBs and test again.  Be careful with compatibility mode of the DBs as anything greater than 2014 (120) will have the new cardinality estimator which can effect query performance.  Once testing on 2016 is complete, then migrate production again to complete the upgrade.

This is by no means a comprehensive list of things that you need to accomplish but an abridged version that just hits the overall flow of doing an upgrade like this.

Can you do it all in one step? Sure, but then if you hit an issue, the line between the SQL upgrade (which is a pretty major version jump) and the OS upgrade may become blurred and you won't know which upgrade is giving you a headache.


Large files in wamp\bin\mysql\mysql5.5.24\data directory

1 - what is creating these files?

Those files are called binary logs. They contain a list of all completed SQL statements you have executed. Your mysqld process created them and autorotates at 1GB. Any restart of mysqld or issuing FLUSH LOGS; will close one binary log and open another

They got created because there is a line in my.ini that looks like this:

log-bin=mysql-bin

Note the timestamps on each file

mysql-bin.000068 has 10/31/2012 2:13 PM
mysql-bin.000069 has 11/1/2012 11:13 AM
mysql-bin.000070 has 11/5/2012 11:51 AM
mysql-bin.000071 has 11/9/2012 11:50 AM
mysql-bin.000072 has 11/13/2012 11:13 AM
mysql-bin.000073 has 11/16/2012 12:05 AM

Here is what has been going

On 10/31/2012 at 2:13 PM

mysql-bin.000068 surpassed 1GB (1048576 KB)
mysql-bin.000068 closed
mysql-bin.000069 opened

On 11/1/2012 at 11:13 AM

mysql-bin.000069 closed due to mysql restart or FLUSH LOGS;
mysql-bin.000070 opened

On 11/5/2012 at 11:51 AM

mysql-bin.000070 closed due to mysql restart or FLUSH LOGS;
mysql-bin.000071 opened

On 11/9/2012 at 11:50 AM

mysql-bin.000071 surpassed 1GB (1048576 KB)
mysql-bin.000071 closed
mysql-bin.000072 opened

On 11/13/2012 at 11:13 AM

mysql-bin.000072 closed due to mysql restart or FLUSH LOGS;
mysql-bin.000073 opened

On 11/16/2012 at 12:05 AM

mysql-bin.000073 surpassed 1GB (1048576 KB)
mysql-bin.000073 closed
mysql-bin.000074 opened

By the way, the reason it autorotates at 1GB? The default setting max-binlog-size is 1GB.

  2 - can I delete them to reclaim drive space?

As long as you do not have mysql replication installed, then yes.

First login to mysql and run

mysql> RESET MASTER;

This will erase all binary logs and leave you with mysql-bin.000001 with 107 bytes.

Next, if you comment it out like this

#log-bin=mysql-bin

and restart the mysqld process

C:\> net stop mysql
C:\> net start mysql

The logs will no longer be written.

As a final step, you can then delete mysql-bin.000001 from Windows Explorer.

Give it a Try !!!


If archiving is disabled and the last redo log file is full, why LGWR continues by overwriting the first available active file, not inactive file?

You probably misunderstood the documentation. Online Logfiles are used in a cyclic fashion, regardless whether you are in archivelog mode or not. The difference is that LGWR may have to wait until the next (in line) online log is being archived before it is allowed to write into it.

Never will LGWR overwrite an Online Logfile that is still in status ACTIVE - exactly because else an Instance Recovery would be impossible. It has to wait at least until the checkpoint, associated to that ACTIVE log is completed and it becomes INACTIVE.

Look here (again?) for a more detailed explanation:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/onlineredo001.htm


Split/explode comma delimited string field into SQL query

I don't actually have a sybase system to test with, but according to http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html the following solution should work:

SELECT *
  FROM table AS T
  JOIN sa_split_list('1234,23,56,576,1231,567,122,87876,57553,1216') AS L
    ON T.id = L.row_value;

If the id_list is actually stored in another table (say list_table) you need to also use a CROSS APPLY operator (http://dcx.sybase.com/1101/en/dbusage_en11/apply-joins-joinsasp.html):

SELECT *
  FROM list_table AS LT
 CROSS APPLY sa_split_list(LT.id_list) L
  JOIN table T
    ON T.id = L.row_value
   AND <optional: some other condition that ties rows in list_table to rows in table, like a group membership or location>

This all requires that only numbers are part of these list as the split pieces will be automatically cast to the datatype of the id column. If that is not the case you can use this simpler, but slower technique:

SELECT *
  FROM table
 WHERE ','+@id_list+',' LIKE '%,'+CAST(id AS VARCHAR(20))+',%'

That last example assumed that the id list is in a variable @id_list. if it is in another table you can use the same CROSS APPLY technique from above. The additional commata make sure that the first or last entry can be matched.


Setting up database structure for Students, Managers, and Administrators

I think the best thing to do is this:
1. Have a User table.
2. Have a User_Role table.
3. Have a User_Assigned_Role table.

Since every user of the system is a user. There is no need separating users table-wise, that means if there is a new requirement tomorrow another table will emerge.

You can create roles like Student, Manager, Administrator and assign resources to these roles and afterwards assign the roles to users. So if you want Mr. A to be an Administrator, assign an administrator role to him.

Table Design

User Table
--------------
id
user_name
password
firs_name ...

Role Table
---------------
id
role_name
role_description
role_status (To deactivate or activate a role)

User Assigned Role Table
----------------------------
id
user_id (References User Table)
role_id (Reference Role Table)

Hope this solves your problem. Contact me if you need more clarification.


MySQL - How to replace a given value from multiple columns when using SELECT?

SELECT IF(A01 = 99999, NULL, A01) AS A01,
       IF(A02 = 99999, NULL, A02) AS A02,
       ...
    FROM ...

(For empty string, use '' instead of NULL.)


SQL query using an adjacent field value as a criterium

You just need to make your tables have the proper Foreign Key Constraints

CREATE TABLE categories
(
  /* The category name is actually its natural primary key */
  category CHARACTER(100) PRIMARY KEY
) ;

CREATE TABLE subcategories
(
  /* All subcategories have a 'parent' category and a 'subcategory' itself */
  /* REFERENCES is one way to define a Foreign Key constraint */
  category CHARACTER(100) NOT NULL REFERENCES categories(category),
  subcategory CHARACTER(100) NOT NULL,

  /* The primary key is actually the 2 columns */
  PRIMARY KEY (category, subcategory)
) ;

CREATE TABLE parts
(
  part_number CHARACTER(10) PRIMARY KEY,
  category CHARACTER(100),
  subcategory CHARACTER(100),
  part CHARACTER VARYING (255),

  /* The pair (category, subcategory) must appear on the 'subcategories' table */
  /* This is actually a second way of defining a Foreign Key Constraint */
  CONSTRAINT
      FOREIGN KEY              (category, subcategory)
      REFERENCES subcategories (category, subcategory)
) ;

Some sample data:

INSERT INTO
  categories (category)
VALUES
  ('Capacitor'),
  ('Resistor') ;

INSERT INTO
  subcategories (category, subcategory)
VALUES
  ('Capacitor', 'Mylar'),
  ('Capacitor', 'Ceramic'),
  ('Resistor', 'Carbon'),
  ('Resistor', 'Film'),
  ('Resistor', 'Mylar' /* if it ever made sense */) ;

INSERT INTO
  parts
  (part_number, category, subcategory, part)
VALUES
  ('DC23', 'Capacitor', 'Mylar',   'High voltage capacitor'),
  ('DC24', 'Capacitor', 'Ceramic', 'Higher voltage capacitor') ;

You can check it at SQLFiddle

The SQL that gets the list of all possible subcategories, given one category, is straightforward:

SELECT
     subcategory
FROM
     subcategories
WHERE
     category = 'Resistor'
ORDER BY
     subcategory ;


Drillthrough action on semi-additive measure group

Assuming it is SSAS Multidimensional you can deploy ASSP and build a rowset action which returns the right rows. The rowset action expression would be something like:

ASSP.GetDefaultDrillthroughMDX(Tail(NonEmpty(Existing [Date].[Date].[Date].Members), 1).Item(0))

To explain that code, we get the existing dates (the dates in context of the current cell) then we filter down to only dates with a non empty value for the current measure (inheriting all the other filters on the current cell) then find the most recent date.

More info on this stored procedure can be found here:
http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

Note a rowset action can't be the double click drillthrough action in Excel unfortunately. You will have to expose your action when users right click on the cell and choose Additional Actions.

Excel has a double click behavior. Double click performs a drillthrough returning either the default columns or returning the columns you define in the drillthrough action you define for that measure group as default. If I execute a DRILLTHROUGH SELECT statement manually without specifying the columns I want then I will get back the columns defined in the drillthrough action flagged as the default


Automatic recovery of the failed postgresql master node is not working with pgpool II

It is as designed: when pgpool does the failover, the failed master is ejected from the pool so that there is a protection against a split-brain. If the failed master comes back again (degenrated master), pgpool will not automatically perform the recovery operation.  You have to manually execute the command pcp_recovery_node command.

If you want to automate this, then you can have a cron job running on each database, this cron would check the status of the database and when it detects a degenerated master it would execute the pcp_recovery_node command. This script can also take care of re-attaching failed standby databases (when a standby fails, pgpool ejects it from the pool and it does not re-attach it when the standby comes back).

I did something like that, it is a bit tricky, you must make sure that the script is never executed twice, for example if the cron interval is shorted than the time to perform the recovery (have a mechanism of a pid file to protect agains that). Otherwise it works.

As for repmgr, I use their scripts to perform failover, recovery, etc. but I don't use repmgrd to automate the failover, I found it safer to have the automatic failover done by pgpool.


Find a longest sequence that doesn't have duplicates inside

You want the smallest id for each Text that appears multiple times.

SELECT MIN(id) AS SmallestId, Text
FROM Table
GROUP BY Text
HAVING COUNT(*) > 1;

Edit:
Now that you've changed your question...

What about deleting later instances of the same text?

DELETE t
FROM Table t
WHERE EXISTS (SELECT *
    FROM Table t2
    WHERE t2.Text = t.Text
    AND t2.Id < t.Id);

This should leave you with just the smallest Id for each text. It doesn't answer what you're asking, but is hopefully closer to what you need.

If you have anything referring to this table, you will need to keep a copy of the rows you delete.


How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

Here is a query I wrote up to give you the most recent thursday and the ending wednesday

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

Here is an example for today, 2011-09-21

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2011-09-15 00:00:00 | 2011-09-21 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Just replace the NOW() function calls with whatever datetime you like and you will have the week starting Thursday all the time for the give datetime you choose.

Here is another example using the specific date '2011-01-01'

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE('2011-01-01') - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE('2011-01-01') dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2010-12-30 00:00:00 | 2011-01-05 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Your query of table referencing today would resemble something like this:

SELECT * from `table`,
(SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A) M
WHERE `date` >= thu_beg
AND `date` <= wed_end;

Give it a Try !!!

UPDATE 2011-09-22 16:27 EDT

This was my proposed query for marking Thu-Wed.

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

How about other weeks ???

(SELECT SUBSTR('6012345',wkndx,1) does the week starting Mon ending Sun
(SELECT SUBSTR('5601234',wkndx,1) does the week starting Tue ending Mon
(SELECT SUBSTR('4560123',wkndx,1) does the week starting Wed ending Tue
(SELECT SUBSTR('3456012',wkndx,1) does the week starting Thu ending Wed
(SELECT SUBSTR('2345601',wkndx,1) does the week starting Fri ending Thu
(SELECT SUBSTR('1234560',wkndx,1) does the week starting Sat ending Fri
(SELECT SUBSTR('0123456',wkndx,1) does the week starting Sun ending Sat


The simplest way would be to use a subquery:

SELECT count(*) AS user_count FROM
(
    -- Original query, untouched
    SELECT
        user.id,
        COUNT( DISTINCT usp.user_id) AS user_sub_plans
    FROM user
    RIGHT JOIN user_preferences AS up ON up.user_id = user.id
    LEFT JOIN user_subscription_plan AS usp ON usp.user_id = user.id
    AND user.inactive = 0
    GROUP BY 1
    -- end of original query
) AS original_query
WHERE user_sub_plans >= 1;

If you use recent version of MySQL, you can also use the WITH construct if it looks simpler to you:

WITH original_query AS ( -- original query ...)
SELECT count(*) AS user_count
FROM original_query
WHERE user_sub_plans >= 1;

Given you want to do a nested count, I don't see an obvious way to do it without a subquery/WITH (nested querying). However, if you would only want to filter our some columns, without counting again, you can use HAVING, which is basically a post-GROUP BY, late executing WHERE:

SELECT
    user.id,
    COUNT( DISTINCT usp.user_id) AS user_sub_plans
    ...
    GROUP BY ...
    HAVING user_sub_plans >= 1;

Outside of your query, there are a couple of not-wrong-but-weird decisions you took, like the RIGHT JOIN, and the AND user.inactive = 0 as part of the ON clause. I am not saying those are bad, but pointing you to double check those are things you want to do, and not unintentional bugs.


How can I build a risk matrix in databse tables?

OK based on @Lennart's feedback, this is a pretty good design for a risk matrix in a database, to suit my needs as described in the question.  I've removed the Unique Key, which was duplicating the Primary Key.

CREATE TABLE IF NOT EXISTS `likelihoods` (
  `likelihoodid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `shortname` varchar(50) NOT NULL,
  `longname` tinytext DEFAULT NULL,
  PRIMARY KEY (`likelihoodid`)

CREATE TABLE IF NOT EXISTS `consequences` (
  `consequenceid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `shortname` varchar(50) NOT NULL,
  `longname` tinytext DEFAULT NULL,
  PRIMARY KEY (`consequenceid`)

CREATE TABLE IF NOT EXISTS `riskmatrix` (
  `likelihoodid` tinyint(3) unsigned NOT NULL,
  `consequenceid` tinyint(3) unsigned NOT NULL,
  `levelid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`likelihoodid`,`consequenceid`),
  KEY `FK_riskmatrix_consequences` (`consequenceid`),
  KEY `FK_riskmatrix_risklevels` (`levelid`),
  KEY `FK_riskmatrix_likelihoods` (`likelihoodid`),
  CONSTRAINT `FK_riskmatrix_consequences` FOREIGN KEY (`consequenceid`) REFERENCES `consequences` (`consequenceid`),
  CONSTRAINT `FK_riskmatrix_likelihoods` FOREIGN KEY (`likelihoodid`) REFERENCES `likelihoods` (`likelihoodid`),
  CONSTRAINT `FK_riskmatrix_risklevels` FOREIGN KEY (`levelid`) REFERENCES `risklevels` (`levelid`)


Postgresql: Does autovacuum impact query performance? Does it apply locks which impact the performance of other queries?

It's memory usage is constrained by autovacuum_work_mem or maintenance_work_mem.  It's IO consumption is constrained by autovacuum_vacuum_cost_delay and friends, and the defaults for these lead to quite low IO demand.  The CPU usage is not directly constrained, but is indirectly constrained by the IO consumption throttle, and with the default setting will usually be close to negligible.

Autovacuum does take a lock on the table, but it is a weak lock which does not interfere with normal operations (SELECT, UPDATE, DELETE) but will interfere with things like adding indexes, or truncating the table.  Autovacuum will usually cancel itself and surrender the lock when it detects it is blocking something, but it won't do that if it is in "to prevent wraparound" mode.

Autovacuum is on by default.  You should not turn it off.  Running manual vacuums during off-peak hours is fine, but leave autovacuum on as a back-stop to catch whatever your manual vacuum misses.


There are multiple ways to get to the intended target recovery point with your setup.

A few things:

You cannot eliminate log backups for the most important reason of transaction log re-use.
It's not possible to know when an issue will occur and thus having multiple ways to get to the RPO is useful and sometimes necessary.
Differentials aren't a substitute for log backups, they will lower the RTO as they are much faster to apply than log backups but ultimately aren't worth much on their own (spanning broken lsn points, etc, is useful)
How are you going to know which ones you need and don't need ahead of time?

I would not stop taking log backups but you could put into place an aging mechanism in your backup software or process to eliminate files that are no longer needed.

Using your example, you have many paths but the basis is as such:

Always take a tail of the log backup first

Restore the full backup, latest differential, all logs.
Restore the full backup, all transaction logs.

This is more flexible as a corrupt backup file (say a log file) could be spanned by a differential or a corrupt full could be spanned by differentials, etc.

I would not change how your strategy is currently, but I would make sure it meets your RPO and RTO requirements.


Yes, if a user can have many (e.g. up to 2) types, and each type of course has many users, then this is a many-to-many relationship.

Users >--------< UserTypes

Any logical many-to-many relationship needs a third physical table that references both.

Users ----< UserHasType >---- UserTypes

For example, this table could look like the following:

CREATE TABLE UserHasType (
  user_id INT,
  type_id INT,
  PRIMARY KEY (user_id, type_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (type_id) REFERENCES UserTypes(type_id)
);


Most useful stored procedures for a new DBA?

What are the best or most helpful Stored Procedures for a New DBA to have in their tool kit, for SQL 2012/2014?

You are asking Tools that are most useful to DBA

In no particuliar order ...

Brent Ozar Crew has sp_Blitz®, sp_BlitzCache®, sp_BlitzIndex®, sp_AskBrent®, sp_BlitzRS™, sp_BlitzTrace™ - This helps to do health check for sql servers.
To migrate sql server from one server to another use powershell - dbatools [it migrates over 25 components] from Chrissy LeMaire
To document sql server use - SQL Power Doc from kendalvandyke
For login transfer use Transferring Logins - By SQLSoldier
Monitoring SQL Server use sp_whoisactive from Adam Machanic
Analyzing Query plans - use SQL Sentry's Free Plan Explorer

The inbuild system SP are limited to what they can report or show. The best way is to create your own (use existing ones and build your own <-- make sure you give proper credit ! ) using DMVs.

e.g. sp_who is way ancient vs sp_whoisactive is what you will install on your server to quickly findout whats going on with the server.

Remove sp_change_users_login 'report' as it is deprecated. Instead you should use ALTER USER


SqlDependency was not really intended for real time type processing.  3 ms is still pretty good.

You should only retrieve required columns and you should use a Reader.  DataTable has a lot of overhead.  And you should be using Using.

string commandText = "SELECT PK FROM [" + DBName + "].[LB].[Orders] " + Environment.NewLine() +
                     "WHERE [AccountIdent] = 'LB:B' AND [Exchange] = 'FX' AND [Status] = 'Sent' " + Environment.NewLine() +
                     "ORDER BY [OrderDate], [OrderUTC] DESC";
using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = commandText;
    conn.Open();
    using (SQLdataReader rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
           list.Add(rdr.GetInt(0));
        }
    }
    conn.Close();
}


MySQL: Show list of tables with paging in Terminal

You can use the pager (see docs) command in MySQL console or use the --pager startup option (docs again) to redirect the output of your commands to an external executable.

To use the pager command and list the tables try this:

mysql> pager less
mysql> show tables

You can also start MySQL to send all your output to an external executable like this

mysql --pager=/usr/bin/less


What does having the primary key as the last column in a composite secondary index in an InnoDB table do?

No. If your table has the InnoDB engine and the PRIMARY KEY is (pet_id), then defining a secondary index as (person_id) or (person_id, pet_id) makes no difference.

The index includes the pet_id column as well so values are sorted as (person_id, pet_id) in both cases.

A query like the one you have:

SELECT pet_id FROM yourtable
WHERE person_id = 127
ORDER BY pet_id ;

will need to access only the index to get the values and even more, it won't need to do any sort, as the pet_id values are already sorted in the index. You can verify this by looking at the execution plans (EXPLAIN):

First, we try with a MyISAM table:

 CREATE TABLE table pets
 ( pet_id int not null auto_increment PRIMARY KEY,
   person_id int not null,
   INDEX person_ix (person_id)
 ) ENGINE = myisam ;

INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets
               WHERE person_id = 2
               ORDER BY pet_id asc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pets
         type: ref
possible_keys: person_ix
          key: person_ix
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

Notice the filesort!

Now, MyISAM with composite index:

 DROP TABLE IF EXISTS pets ;

 CREATE TABLE table pets
 ( pet_id int not null auto_increment PRIMARY KEY,
   person_id int not null,
   INDEX person_ix (person_id, pet_id)            -- composite index
 ) ENGINE = myisam ;

INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets
               WHERE person_id = 2
               ORDER BY pet_id asc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pets
         type: ref
possible_keys: person_ix
          key: person_ix
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using index
1 row in set (0.00 sec)

Filesort is gone, as expected.

Now lets try the same with InnoDB engine:

 DROP TABLE IF EXISTS pets ;

 CREATE TABLE table pets
 ( pet_id int not null auto_increment PRIMARY KEY,
   person_id int not null,
   INDEX person_ix (person_id)            -- simple index
 ) ENGINE = innodb ;                      -- InnoDB engine

INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets
               WHERE person_id = 2
               ORDER BY pet_id asc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pets
         type: ref
possible_keys: person_ix
          key: person_ix
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using index
1 row in set (0.00 sec)

No filesort either! Even though the index does not explicitly have the pet_id column, the values are there and sorted. You can check that if you define the index with (person_id, pet_id), the EXPLAIN is identical.

Lets actually do it, with InnoDB and the composite index:

 DROP TABLE IF EXISTS pets ;

 CREATE TABLE table pets
 ( pet_id int not null auto_increment PRIMARY KEY,
   person_id int not null,
   INDEX person_ix (person_id, pet_id)    -- composite index
 ) ENGINE = innodb ;                      -- InnoDB engine

INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets
               WHERE person_id = 2
               ORDER BY pet_id asc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pets
         type: ref
possible_keys: person_ix
          key: person_ix
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using index
1 row in set (0.00 sec)

Identical plans with the previous case.

To be 100% sure, I also run the last 2 cases (InnoDB engine, with single and composite indexes) enabling the file_per_table setting and adding a few thousands rows in the table:

DROP TABLE IF EXISTS ... ;
CREATE TABLE ... ;

mysql> INSERT INTO pets (person_id)
       VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id)
       VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id)
       VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id)
       SELECT a.person_id+b.person_id-1
       FROM pets a CROSS JOIN pets b CROSS JOIN pets c ;
Query OK, 54872 rows affected (0.47 sec)
Records: 54872  Duplicates: 0  Warnings: 0

In both cases, checking the actual file sizes, yields identical results:

ypercube@apollo:~$ sudo ls -la /var/lib/mysql/x/ | grep pets
-rw-rw----  1 mysql mysql     8604 Apr 21 07:25 pets.frm
-rw-rw----  1 mysql mysql 11534336 Apr 21 07:25 pets.ibd


How to restore truncated MySQL table from binary logs?

You can restore the database from binary logs only if they were enables from the very beginning and if there are all of them starting from .00001.

Then to restore the database you need to replay all binlogs on a fresh MySQL instance:

for log in mysql-bin.*
do
    mysqlbinlog $log | mysql -h <destination host>
done

If you truncated a table you probably need to restore just it. Then restore the database on some other host and move the table to the original server.


How to change directory of binlogs on Ubuntu 18.04

I already got it to work after I added the 3rd line.


Precedence constraint does not run in SSIS package

When you run the stored procedure from the Execute SQL Task add the OUTPUT keyword to the parameter that will be returned.  For instance

EXEC dbo.OpenExcel ?, ?, ? OUTPUT


Oracle 11g ORA-01190: control file or data file 1 is from before the last RESETLOGS

Since you've done an open resetlogs, you've changed the incarnation and are now trying to restore to a previous incarnation.  From the Backup and Recovery User's Guide:

  The procedure for DBPITR within the current incarnation is different
  from DBPITR to an SCN in a noncurrent incarnation. In the latter case,
  you must explicitly execute the RESET DATABASE to reset the database
  to the incarnation that was current at the target SCN. Also, you must
  restore a control file from the database incarnation containing the
  target SCN

After the above paragraph the manual goes on to detail the procedure, step by step.

It doesn't matter if you specify 'to time' or 'to scn'.  If you specify 'to time', rman will internally convert that to an SCN.

.


To answer your immediate question, how to count rows of a subquery, the syntax is as follows:

SELECT COUNT(*) FROM (subquery) AS some_name;

The subquery should immediately follow the FROM keyword. (In MySQL it is also mandatory to assign a name to a subquery of this kind (it is actually called a derived table), which is why you can see the AS some_name following it.) The way you have written it, MySQL interprets your script as two independent queries, that is why you are getting two result sets.

So, since the subquery in your case is

SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE

the complete query would look like this:

SELECT COUNT(*) FROM (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
) AS derived;

But, as Julien has suggested, you can rewrite your query just like this:

SELECT COUNT(DISTINCT ip_address) FROM `ports` WHERE status IS TRUE;

This way you do not need a subquery/derived table at all, because with the DISTINCT keyword the COUNT function will count only distinct occurrences of ip_address in the ports table.


Honestly, I don't think mysqld is dying.
The message you posted reminds me of someone or something trying to restart mysqld in the absence of the PID file. When there is no PID file, doing service mysql stop does not work. I wrote about this in Percona-server time out on /etc/init.d/mysql start back on Dec 14, 2012 and MySQL Start/Stop 3 days later.
What's happening is that mysqld is already running and a mysqld start is being issued. This destroys the PID file (See MySQL Start/Stop under the heading PROCESSES (Shutdown)). The messages you are seeing is the second attempt to start mysqld only to find out that the plugins are already loaded.
What makes this more obvious is something I wrote back on April 24, 2014 in Two MySQL Server accessing same database over NFS:

Only one mysqld instance can attain an exclusive log buffer thread on a system tablespace
Only one mysqld instance can attain an exclusive insert buffer thread on a system tablespace

It only makes sense that the Buffer Pool cannot be allocated because a Buffer Pool already exists
SUGGESTION
Try shutting down mysqld with
mysqladmin -h127.0.0.1 -P3306 --protocol=tcp -uroot -p shutdown

This will prompt for password. After entering the password, if this returns no output, then mysqld performed a proper shutdown. (BTW this is handled in MySQL 5.7 because you can run the SHUTDOWN command from within a mysql client session). Then, you can run service mysql start with the PID file situation straightened out.
I hope you have the user root@127.0.0.1 or mysqld is smart enough to use root@localhost credentials (See my old post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) on how mysqld authenticates users)


Accuracy of mysqldbcompare vs other tools like pt-table-checksum

It is a non goal of pt-table-checksum to show you the actual differences.  However, there is a companion tool pt-table-synch  which can read the checksum table generated by pt-table-checksum.  It will take the bounds of chunks where master/slave checksums differ and then scan with in those to identify rows that don't match.

It will even generate replace statements ready to execute on the slave to get it in synch with the master.


write SQL query that takes its own output as input

You can use a subquery with the same table in both the primary and sub queries, join on trade_id = keyword_value

Depending on how many levels a trade could have it might get complex, but this should get you going in the right direction.


It was decided to go with something between second and third options - adding additional table with columns Value type and separate columns for each Resource type.

(+) easier to manage in code
(+) availability of Foreign Keys on Resource ID
(+) no overhead on table count
(+) ability to add multiple different Resource type's to each Item
(-) uses extra space for each unused Resource type for each field
(-) lacks ability to cascade delete related Item, when resource with Resource ID is deleted.


Manually Grow DBs in an Availability Group

First one.

  The operation gets synced to the other secondary replicas.

Because it is a logged operation.


If I were you I would make sure to separate all of the data. Is this going to be completely controlled in the database, or is there going to be an application? If it is an application I would have a users table and permissions tables for the people using the application. I would do a separate a customers table for your customers. I agree with the availability table, but if you are using a price based system I would store that separately. For billing I would set up a few tables. I would have a payment type table and a transaction table with due dates. I like the idea of a check in and out table as well.


CREATE SEQUENCE using expressions with psql variables for parameters

You need \set (not \pset!) to set psql variables. Unlike assignment in a Unix shell the psql assignment is not capable of arithmetic operations. You could use the psql command \! to execute shell commands, but I'll suggest two different approaches:

Option 1: Let Postgres calculate and set new variables with \gset

The manual about \gset:

  Sends the current query input buffer to the server and stores the query's output into psql variables

Then you can interpolate the calculated numbers as text and everything works.
psql code:

test=# \set servernum 5
test=# \set stride 300
test=# SELECT :servernum * :stride AS minvalue, (:servernum + 1) * :stride - 1 AS maxvalue \gset
test=# CREATE SEQUENCE key_seq MINVALUE :minvalue MAXVALUE :maxvalue;
CREATE SEQUENCE

A nice blog explaining \gset:

http://marxsoftware.blogspot.co.at/2015/08/postgresql-psql-variable-from-query.html

I calculated and set :minvalue and :maxvalue in a single SELECT to optimize performance. Still, the downside of this approach is that you need an extra round trip to the Postgres server.

Option 2: Function with dynamic SQL for repeated use

You could use a DO statement, but (the manual again):

  Variable interpolation will not be performed within quoted SQL literals and identifiers.

This would complicate string concatenation. I suggest to create a function and use format() for clean code. If it's for the current session only, you can make it a temporary function by schema-qualifying with pg_temp. So the function is only visible to your current session and dropped at the end of the session. Temporary functions are an undocumented feature - even though suggested by Tom Lane. Details:

How to create temporary function in Postgres

So:

test=# CREATE FUNCTION pg_temp.f_my_seq(_seq text, _servernum int, _stride int)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s'
                 , _seq, _servernum * _stride, (_servernum + 1) * _stride - 1);
END
$func$ LANGUAGE plpgsql;

test=# \set servernum 5
test=# \set stride 300

test=# SELECT pg_temp.f_my_seq('key_seq', :servernum, :stride);

We need dynamic SQL with EXECUTE. The manual about Variable Substitution:

  Variable substitution currently works only in SELECT, INSERT, UPDATE,
  and DELETE commands, because the main SQL engine allows query
  parameters only in these commands. To use a non-constant name or value
  in other statement types (generically called utility statements), you
  must construct the utility statement as a string and EXECUTE it.

Related answer on SO:

Error when setting n_distinct using a plpgsql variable


Database structure for object with dynamic attributes

It looks like you want to fill out the email templates using only the unique data for each recipient.  If this is true then I would recommend option 3.  I would have a recipient table that is not directly linked to the email, but is instead linked to the email via a join table.  I would also put a FK on the recipient table to the email sender.  Then I would basically follow your EAV model.

This way your "recipient" table is more like a contact table for the sender.  The EAV model allows you to easily select the custom data for the templates, gives you the ability to painlessly extend the data points, and gives you the opportunity to allow your users to create their own templates using the fields they want.  The senders also win because they can reuse the 50+ data points for future emails because we decoupled the recipient table from the email.


performance: {ts 'datetime'} vs DATEADD()

I recommended (originally, in the comments) that HEDMON try some different dates. Since his test date and his production dates were different, it seemed like it might be possible that there was a selectivity issue with the dates; that SQL could return the data much faster with a more recent date than with an older one.

HEDMON did try that, and found (from his comments) that dates from 2017-02-25 forward responded quickly, while dates older than that were slow.

With this, he felt comfortable that the presentation of the date (using the {ts } syntax) had nothing to do with the issue.


SQL Error(1054): Unknown column in 'where clause' when trying to update. Column is actually available

That is not valid SQL - you are not referencing table_b in any FROM or JOIN clauses so can not use columns from it in SELECT, SET, WHERE or other parts.
Sticking to standards compliant SQL you need to use a sub-select here:
UPDATE table_a
   SET table_a.course_start_date = (
           SELECT table_b.fee_pay_date
             FROM table_b
            WHERE table_b.student_id = table_a.student_id
            );

Some databases support using JOINs in UPDATE statements so avoid sub-selects. This can often be easier to read but will break if you need to support different database back-ends. For mySQL and MariaDB this would be something like:
UPDATE table_a
  JOIN table_b
       ON (table_a.student_id = table_b.student_id)
   SET table_a.course_start_date = table_b.fee_pay_date
       ;

Not that both of these statements will update every row in table_a even if the row already has the right value so could be very inefficient if only a few rows actually need updating. This is by design, as you might want triggers to fire and so forth even for a NoOp update. To avoid this if it is not desirable add a WHERE clause to block unneeded updates:
UPDATE table_a
  JOIN table_b
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
 WHERE table_a.course_start_date <> table_b.fee_pay_date
    OR (table_a.course_start_date IS     NULL AND table_b.fee_pay_date IS NOT NULL)
    OR (table_a.course_start_date IS NOT NULL AND table_b.fee_pay_date IS     NULL)
       ;

or:
UPDATE table_a
  JOIN table_b
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
 WHERE COALESCE(table_a.course_start_date, '9999-01-01') <> COALESCE(table_b.fee_pay_date, '9999-01-01');

(you can simplify those WHERE clauses a bit if table_b.fee_pay_date can never be NULL)

As a related side-issue: to illustrate the concern of using non-standard syntax, the SQL Server equivalent to
UPDATE table_a
  JOIN table_b
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
       ;

would be
UPDATE table_a
   SET course_start_date = table_b.fee_pay_date
  FROM table_a
  JOIN table_b
       ON table_a.student_id = table_b.student_id
       ;

and in postgres you would need:
UPDATE table_a
   SET course_start_date = table_b.fee_pay_date
  FROM table_b
 WHERE table_a.student_id = table_b.student_id
       ;

Not vastly different, but if you want to support multiple databases or later move from one to another, you need to be careful of this sort of difference. The sub-query version should behave the same in all three (and most other places).


what effect does changing the data type of an existing indexed field have on the index?

Changing the data type of a column causes a table restructure, which will have the side-effect of rebuilding all indexes for the table (not only those referencing the column you altered, but all indexes in that table).

You don't need to drop or recreate the indexes yourself; it's already done by the time the ALTER TABLE finishes.


Connecting remote MySQL database to local MySQL database?

There are 3 methods to set this up
METHOD #1 : MySQL Replication
Setup MySQL Replication where the Slave has this option
replicate_do_table=mydb.mytable

Then, any DML (INSERT, UPDATE, DELETE) or DDL (ALTER TABLE) you execute will go immediately to serverB. This makes Method #1 is the fastest and most granular approach.
METHOD #2 : Copying the table to the other server
Rather than rehash, Here is an earlier post I did May 31, 2011 for this method : How do you copy a table from MySqlServer_A to MySqlServer_B?
METHOD #3 : FEDERATED Table (MyISAM Only)
Suppose mytable on serverA looks like this
CREATE TABLE mydb.mytable
(
...
) ENGINE=MyISAM;

You can a mapping of the target table in serverB by running this on serverA like this
CREATE TABLE mydb.mytable_remote LIKE mytable;
ALTER TABLE mydb.mytable_remote ENGINE=FEDERATED
CONNECTION='mysql://username:password@serverB/mydb/mytable';

I wrote an earlier post back on Jan 4, 2012 : fetching externally hosted db's table from within the local mysql server
Then you would have to bulk copy everything in serverA to serverB running the following on serverA:
INSERT IGNORE INTO mydb.mytable_remote SELECT * FROM mydb.mytable;


Cancel SQL Job execution make automatic rollback

No, it probably won't. I can't say for sure, but client "aborts" normally don't rollback if the connection is returned to the connection pool

Add SET XACT_ABORT ON to your code or stored procedure

See (SO) https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 and SQL Server Transaction Timeout


Physical Move of Secondary Replica to a new Location

Is there anything that would need to be done on the secondary replica prior to shutdown.

I would suggest you to suspend data movement prior to the move (and resume it once the secondary server is up). This will have a side-effect of primary database accumulating the unsent transaction log records in the send queue. This means that for your maintenance time, transaction log on primary database cannot be truncated.

Remember that primary database is exposed - if it goes down, there will be data loss. Make sure you have a good tested full backup + T-log backups.

  Suspending an Always On primary database suspends data movement on all the corresponding secondary databases, and redundancy and failover capabilities cease for that database until the primary database is resumed.

You must remember to resume data movement once secondary database is online.

Also, depending on the database size (if its small), you can think of removing the database from AG and putting it back again once the move is successfully completed.


EAV-like table with both primary column pointing to the same foreign column

A simple CHECK constraint works just fine:

$ sqlite
SQLite version 3.8.4.1 2014-03-11 15:27:36
...
sqlite> CREATE TABLE table_B(
   ...>     user1,
   ...>     user2,
   ...>     [other stuff],
   ...>     CHECK (user2 < user1)
   ...> );
sqlite> INSERT INTO table_B VALUES (1, 0);
sqlite> INSERT INTO table_B VALUES (2, 3);
Error: CHECK constraint failed: table_B

(This has nothing to do with EAV.)


SQL Server won't automatically restore a database to the folder where the .BAK is located. This would be a disaster waiting to happen (say, that drive is out of space, or is encrypted or compressed, or SQL Server only has read access). Instead, it will try to place the files using the same configuration they had on the system they were backed up from. As Tibor alluded to in a comment below, you certainly could standardize all of your users' installs to have SQL Server installed to the same data folders as the source system, or at least all have the same data folder available to write to.

Barring that, you need to use RESTORE ... WITH MOVE in order to place the data and log files in the right folder for this instance. First verify what that is:

EXEC master.sys.sp_helpfile;

That will give you two rows with paths like:

filename
------------------------
C:\some\path\master.mdf
C:\some\path\mastlog.ldf

Now run the following from the backup file:

RESTORE FILELISTONLY FROM DISK = 'C:\path\file.bak';

This will give you a row for each file in the backup, corresponding to each data/log file you'll need to restore (and move). For example:

LogicalName
-----------
AdventureWorks2016_Data
AdventureWorks2017_Log

Take the C:\some\path\ part from the first query, and the logical names from the second query, into a statement like this:

RESTORE DATABASE AdventureWorks
  FROM DISK = 'C:\path\file.bak'
  WITH REPLACE, RECOVERY,
    MOVE 'AdventureWorks2016_Data' TO 'C:\some\path\aw.mdf',
    MOVE 'AdventureWorks2016_Log'  TO 'C:\some\path\aw.ldf';

There are likely complications when your backup includes things like filestream or In-Memory OLTP.

But if your goal is to make a "distributable" backup that will do all that work for the end user, I don't think a traditional SQL Server backup is what you are looking for. Maybe you should look into database projects or providing scripts instead of backups.


How can a weak entity be related to a weak entity?

All tables, according to Codd's definition, must have a primary key. The key can be simple, consisting of a single column, or compound, consisting of multiple columns. It is perfectly acceptable for those columns to be foreign keys. When FKs are part of the PK it is sometimes called a weak entity. I guess because the entity type cannot stand on its own - it requires another in order to make sense.

For the given diagram a valid implementation would be

Table: College
Columns: Name
Foreign Keys: none
Primary key: Name

Table: Attendance
Columns: CollegeName, StartDate, EndDate
ForeignKeys: CollegeName references College(Name)
PrimaryKey: CollegeName, StartDate

Table: Transcript
Columns: CollegeName, StartDate, CourseName, Semester etc.
ForeignKeys: (CollegeName, StartDate) references Attendance(CollegeName, Startdate)
PrimaryKey: CollegeName, StartDate, CourseName

These primary keys consist of attributes that are placed in the entity types "naturally" through analysis and normalisation. Hence they are called "natural keys." As you can see natural keys can quickly become unwieldy. That is why they are often replaced by a unique integer. This integer is called a surrogate key, because it stands in for the natural key.

Inserting a surrogate key in the database design typically converts a weak entity to a strong one i.e. the surrogate replaces the whole of the natural key. This is certainly the easiest to code and is supported by the SQL IDENTITY feature. It need not be so, however. Say we added a surrogate to Attendance - let's call it AttendanceId. It would still be legitimate to model Transcripts as (AttendanceId, CourseName). It would still be a "weak" entity.


In the real world, a lot will depend on how many customers you have, how paranoid those customers are/ how valuable the data is, and how skewed your customers are (i.e. Mom & Pop with 10 items and 100 sales a day vs Hostess Inc with millions of sales a day).

Separate databases create a variety of administrative problems.  Every time you want to do an upgrade, for example, you have to ensure that the same script gets run in every database.  If you support 10,000 bakeries, that's challenging.  Particularly when the script works perfectly on 9,900 of them and generates slightly different errors in 100 databases because they happen to have a data condition that you didn't anticipate or because different background jobs were running at the time and now you have to manually look at 100 error logs to see how to fix each database (meanwhile your middle tier code got pushed successfully and assumes that the new objects are present which isn't correct for 100 customers).  You can, of course, mitigate these problems by having fully automated deploys and fully tested rollback scripts and rolling back the 9900 databases where the upgrade succeeded because the 100 databases failed.  But that requires a pretty sophisticated deployment pipeline that has, for example, all 10,000 databases available in lower environments to test deployment scripts against and that everyone scrupulously verifies their rollback scripts (which would be rare).

If you have relatively small numbers of clients that are paying relatively large amounts to use your service to handle their very sensitive information, it may be perfectly reasonable to do this.  If you have 100 databases because you have 100 hospital chains as customers putting patient information in your system, this level effort is probably very reasonable.  It's probably not reasonable if you're trying to provide a low-cost way for lots of small bakeries to manage a relatively small number of employees and products.

If you have a mix of very large and very small customers such that different customers will likely want the same query to use different plans or such that certain large customers want to be able to partition out their workload (i.e. Hostess wants to know that there are 4 app servers and a database server that is theirs alone and that when you add 4 cores to the database server that they're going to get all of that and you want to charge Hostess for the Enterprise Edition of your application to give them the privilege of using dedicated hardware), it may make sense to put your handful of giant customers in their own database and group all the small fry into their own shared database.

Practically, that means that you'd probably design it with a bakery entity in the knowledge that some day in the future you might actually choose to deploy to multiple databases some of which would only have 1 row in the table.  Perhaps initially you put everything in one database, then move to a couple geographically separate databases (i.e. all the Aussie customers go in the database that you run out of the Aussie data center), then move the really large customers to their own database where the bakery entity only has a single row.  Then when Hostess comes to you in a few years and wants to separate Hostess Europe from Hostess America with separate sets of employees and products, you can set those up as separate bakeries in the same database.


Duplicate all stored procedures with a new name

How about such a little script, to generate new scripts for all procedures, replacing all old names with new names (definition and calls):

SELECT
    O.Name as ProcName
    ,REPLACE(REPLACE(REPLACE(
        M.Definition,
            'old_proc_name_1', 'new_proc_name_1'),
            'old_proc_name_2', 'new_proc_name_2'),
            'old_proc_name_3', 'new_proc_name_3')
        -- etc.
as CreateScript
FROM sys.sql_modules as M INNER JOIN sys.objects as O
ON M.object_id = O.object_id
WHERE O.type = 'P'
and O.name not like 'sp%' -- exclude special system procs

Then you can execute all the scripts, either by hand, or using a cursor (more prefered for hundreds of procs).

But begin with checking carefully at least a couple of scripts before executing them.


IF statement not skipping TempDB when Looping through Databases with sp_MSForEachDB

NOTE TO READERS: Please read the entire question, including the example code (i.e. not just the title). This question is not about how to best cycle through databases, nor is it about why does [tempdb] receive this error. The O.P. is already trying to avoid executing the ALTER statements on all system databases (well, the 4 visible ones) and is asking why the IF statement that should be skipping [tempdb] does not appear to be skipping it.

  Why does the following script gives me an error relating to tempdb?

The reason is that the IF statement only affects what happens when the code is actually running, but SQL Server still has to parse and compile the batch prior to executing it. Parse errors are ones related to syntax, such as making sure that SQL statements are properly formed and that variables have been properly declared:

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
SELECT @Bob;

gets the following error:

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@Bob".

And the following:

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
CREATE TABLE b

gets the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'b'.

If the batch parses successfully, then it is compiled, at which time things like permissions are checked and some other checks are performed.

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
IF (1 = 0)
BEGIN
  ALTER AUTHORIZATION ON DATABASE::[tempdb] TO [sa];
  ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
END;

The above SQL is properly formed so the batch parses successfully. Now try to execute the above SQL by hitting F5 or Control-E or the ! Execute button, etc.

This time you get the following error:

Msg 5058, Level 16, State 1, Line 4
Option 'RECOVERY' cannot be set in database 'tempdb'.

even though the IF (1 = 0) ensures that the code will never run. This means that you are running into a compilation error. You can get around these types of errors by moving the offending code into a subprocess via an EXEC call.

Execute the following and it will complete successfully since what is inside of the EXEC() is not parsed or compiled until that statement is executed at runtime.

IF (1 = 0)
BEGIN
  EXEC('
    ALTER AUTHORIZATION ON DATABASE::[tempdb] TO [sa];
    ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
  ');
END;

To summarize:
First, consider that sp_MSForEachDB cycles through the databases and executes your passed-in SQL after replacing the ? with the current database name. So when the cursor inside of sp_MSForEachDB gets to [tempdb], it effectively does the following:

IF ( (select database_id from sys.databases where name = ''tempdb'') > 4)
BEGIN
  ALTER AUTHORIZATION ON DATABASE::tempdb TO [sa];
  ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
END

Second, there are several steps that SQL Server takes when you execute a query batch:

Parse
Compile
Actual Execution

It is important to understand that these are separate steps and can generate errors before proceeding to the next step (and hence cancel further processing before going on to that next step). In the case here, the error is happening in Step 2 -- Compile -- as proven in the 2nd to last example above (the first one to start with IF (1 = 0)). The IF (1 = 0) prevents the code inside of the BEGIN...END block from ever running, yet the error still occurs. Hence the error is not happening due to an actual attempt to run the two ALTER statements.

The reason that wrapping the ALTER statements inside of the EXEC() function works is because SQL Server will not parse and compile what is inside of the EXEC() until the EXEC() is actually running. At that point, the IF ( (select database_id from sys.databases where name = ''?'') > 4) statement will be allowed to run since the batch won't fail during Compilation and will make it to Execution, and the IF statement will skip over [tempdb] and the "Option 'RECOVERY' cannot be set in database 'tempdb'" error will not occur.


Why did my database size almost double over night?

Can't do much without more info; as a start run the following query on your database:

SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    TotalMB = CONVERT(decimal(12,1),size/128.0),
    UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
    FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
    MaxSizeMB = CASE WHEN max_size = -1 THEN NULL
        ELSE CONVERT(DECIMAL(18, 1), max_size / 128.0) END,
    GrowthRate = CASE WHEN is_percent_growth = 1 THEN CONVERT(varchar(12),growth) + '%'
            WHEN growth = 0 THEN 'FIXED'
            ELSE CONVERT(varchar(12), growth/128) + 'MB' END,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

This query looks into the detail of the database files, and tells you whether you are simply dealing with a lot of empty space, or actual data, and whether it is located in the data file or the log file.

You can also use the "growth rate" to determine if it simply grew in one big chunk, instead of smaller amounts.

Any further action depends on where the unexpected growth actually took place.


You'll have to write your own, it's not a native feature of oracle, it's something that has been implemented in Oracle tools. Grab the plan from v$sql_plan for your SQL_ID and then grab the delta of session stats before and after you run your query.

It's a little more involved in that, but that's the basic mechanics.


Missing date handling between subquery and OUTER APPLY

First, the 2 original subqueries have different correlation conditions. The first one has and tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid (which is a redundant repeat of the ON condition) but the 2nd has and tblSchedule.fldScheduleVisitID = tblpatientvisit.fldvisitid which is a correlated condition to the main query.
So, the two subqueries may have been giving you NULL in some results but they are incorrect (one of them!) if they are supposed to give you related results.

Second and based on the (broken) results from the OUTER APPLY where the conditions from the first subquery is used, we can deduct that the 2nd subquery has the correct/wanted conditions.

Other points of trouble is the random formatting of the code, the mixing of upper and lower case SQL keywords and the lack of aliases which makes the code rather unreadable. The more the code in nicely formatted, the easier would be to catch similar errors.

Columns and table aliases should be quoted (if needed) with double quotes ("some alias") or square brackets ([some alias]) and not with single quotes. While this is allowed for column aliases, it is very confusing and error-prone, as single quotes are used for string literals.

Columns should always (unless in ORDER BY) be prefixed with the table name or alias. This is mostly fine in the code, except for one miss.

Based on the above, I'd rewrite the OUTER APPLY like this:
OUTER APPLY
  ( SELECT TOP 1
        v  . fldVisitDate,
        vt . fldVisitTypeName
    FROM tblVisit AS v
        LEFT JOIN tblVisitTypes AS vt
            ON  v.fldVisitTypeID = vt.fldVisitTypeID
        INNER JOIN tblSchedule AS s
            ON  s.fldScheduleVisitID = v.fldvisitid
    WHERE
        -- replace "xxx" with table name or alias
        xxx.fldVisitPrerepID = tblRunnerPrereps.fldPrerepID
      AND
        s.fldScheduleVisitID = tblPatientVisit.fldvisitid
    ORDER BY
        fldVisitDate
  )
    AS FirstVisitData

and then double and triple check that the joining conditions are correct and matching business logic and the intention of the query.
All the above may still not resolve the weird results (1900-01-01) you get. It may be that they are caused due to the changed conditions (in which case, the rewrite will resolve it) or as @Duffy has commented they may due to some implicit conversion. The empty string '' when converted to DATE becomes by default 1900-Jan-01. A UNION can have this effect. Try this for example and you'll get the 2nd row as 1900-Jan-01:
SELECT getdate() AS SomeDate
UNION
SELECT '' ;

Without knowing what transformations are happening in the external query, we are not sure about that either nor why this happens only with the OUTER APPLY but not with the subqueries method.


How to calculate a value for all rows in sql server?

This query solved my problem.

SELECT DISTINCT
  UPL.memberid,
  UPL.membershipdate,
  PaymentSum.sumamount
FROM user_project_linkingtable AS UPL
LEFT JOIN (SELECT
            memberid,
            Sum(amount) AS SumAmount
            FROM payment
            GROUP BY memberid) AS PaymentSum
 ON UPL.memberid = PaymentSum.memberid;


Availability Group database stuck in Not Synchronizing / Recovery Pending mode

Since the server had been offline for a while we thought it may have gone outside the recovery window of the primary. We decided to try applying the latest transaction logs on the database to see if that would kick-start the recovery process:

-- Remove database from Availability Group:
Alter Database [StackExchange.Bicycles.Meta] SET HADR OFF;

-- Apply t-logs to catch up. This can be done manually in SSMS or via:
RESTORE LOG [StackExchange.Bicycles.Meta] FROM DISK = '\\ny-back01\backups\SQL\_Trans\SENetwork_AG\StackExchange.Bicycles.Meta\StackExchange.Bicycles.Meta_LOG_20160217_033201.trn' WITH NORECOVERY;

-- Re-join database to availability group
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR AVAILABILITY GROUP = [SENetwork_AG];
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR RESUME;

Afer running the above on the secondary server for both databases they were able to start synchronizing again.

UPDATE: We had a similar issue where after a Manual AG Failover one of the databases on the new primary replica was stuck in Not Synchronizing mode (switched to Not Synchronizing / Recovery Pending after restarting SQL Server), and the above steps worked to resolve that issue as well.


Check out repmrg:

  repmgr is a set of open source tools that helps DBAs and System
  administrators manage a cluster of PostgreSQL databases..

  By taking advantage of the Hot Standby capability introduced in
  PostgreSQL 9, repmgr greatly simplifies the process of setting up and
  managing database with high availability and scalability requirements.

  repmgr simplifies administration and daily management, enhances
  productivity and reduces the overall costs of a PostgreSQL cluster by:

  monitoring the replication process; allowing DBAs to issue high
  availability operations such as switch-overs and fail-overs.

It does two things:

repmgr: command program that performs tasks on your cluster and then exits
repmgrd: management and monitoring daemon that watches the cluster and can automate remote actions.

For automatic failover, repmgrd does the trick and is not a SPOF in your network, like pgPool. However, it is still important to monitor all deamons and bring them back up after failure.

Version 2.0 is about to be released, including RPM's.


Preventing SQL Server Data Tools from Inserting Spaces in Object Names

I don't think it's possible to turn the "feature" off, but the way it works is that it interprets caps and underscores as a new word so a field in your datasource MyAttribute or my_attribute becomes a member named My Attribute.

The only way I can think of to prevent this is to have your column names all lower case without underscores in your datasource so myattribute as a column name will stay myattribute as your member name.

I'm not sure how your comparison scripts work and how you generate them, but I think you'll have to resort to either removing the space when you generate an SQL query or parsing and adding a space according to the same logic when generating your MDX. In powershell that should be fairly easy.


Grant usage to a schema from another database

You cannot change an object in one database while you are connected to the other database. You'll have to connect to the database where you want to grant the permission.

You can still do it in a single script if you use psql, because that has the \c command to connect to a different database. So your script could look like that:

CREATE DATABASE second;
\c second
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA newschema;
GRANT USAGE ON SCHEMA newschema TO normal_user;

You should always revoke the CREATE privilege on schema public from PUBLIC for security reasons, and you don't need to grant USAGE on that schema, since that is already granted to PUBLIC.


how to get the difference between 2 tables in 2 different schema's in Oracle?

To mustaccio's point, there's nothing particular about a schema that prevents you from querying objects from different schemas within the same query (putting privileges aside - since that's a function of security, not a unique feature of schemas).
So you can use a FULL JOIN between the two tables across schemas with an appropriate WHERE clause like so to answer your questions:
-- Gets all rows that have the same primary key (ticket_number) between the two tables
SELECT * -- Replace * with only the columns you need, as using * is generally bad practice
FROM tickets1.Tickets T1
FULL JOIN tickets2.Tickets T2
    ON T1.ticket_number = t2.ticket_number
WHERE T1.ticket_number IS NOT NULL
    AND T2.ticket_number IS NOT NULL

-- Gets all rows that have different primary keys (ticket_number) between the two tables
SELECT * -- Replace * with only the columns you need, as using * is generally bad practice
FROM tickets1.Tickets T1
FULL JOIN tickets2.Tickets T2
    ON T1.ticket_number = t2.ticket_number
WHERE (T1.ticket_number IS NOT NULL AND T2.ticket_number IS NULL)
    OR  (T1.ticket_number IS NULL AND T2.ticket_number IS NOT NULL)

Note the above only compares the two tables by the primary key (ticket_number). Depending on your definition of "same data" / "different data", you may want to compare by other fields in the tables as well.


What makes you think you need to rewrite it? SQL Tuning is more than just trying to rewrite your query. If you do not understand what happens behind the scenes, then how will you rewrite your query?

The estimated cardinality in step 10 and 21 is 1. Still the database decides to scan the whole CARI table. Twice.

Is the above estimation correct? Would those steps really return just 1 or a few rows? If you run this query (with actual values), will this return 1 or hundreds, thousands, millions?

select count(*) from cari where id = baslik and yilid = :yilid;

Are the table statistics up-to-date?

select count(*), count(distinct yilid) from cari;
select blocks from user_segments where segment_name = 'CARI';

Compare the above values to:

select num_rows, blocks from user_tables where table_name = 'CARI';
select num_distinct, histogram from user_tab_columns where table_name = 'CARI' and column_name = 'YILID';

If there is a big difference, gather statistics.

Is there any correlation between the columns BASLIK and YILID? If there is, did you gather extended statistics on them, so the database can estimate cardinality more accurately?

If both the statistics and the above estimation are good enough, then why isn't there an index on the YILID (and BASLIK) column that the database could use to access the required rows effectively?

If both the statistics and the above estimation are good enough, then why does the database decide to use parallel query at all? Did you force it or set it at table level?

In the Predicate Information section:

10 - filter("K"."ID"="K"."BASLIK" AND "K"."YILID"=SYS_OP_C2C(:YILID))
21 - filter("ID"="BASLIK" AND "YILID"=SYS_OP_C2C(:YILID))

This is really bad: "YILID"=SYS_OP_C2C(:YILID). You did not bind the variable properly. SYS_OP_C2C means char to nchar conversion, so the column type and variable type is different. The database will implicitly convert one type to another, this time you got lucky, but the other way around it would make impossible to use an index, unless you have a function-based index especially for the affected columns - do not do that, bind properly. If the column YILID is an nvarchar2 type column, then bind :yilid as an nvarchar2 type variable.

If the above still does not help, did you actually measure the steps in the execution plan? Did you identify the steps that take long? Run the below and report back with the output:

alter session set statistics_level = all;
-- run your query here in the same session --
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));


Based on Phil's test case I backed up the the production database using exp.

I dropped the editioned user, recreated them and imported the data with no issues.  The user had views and packages which were versioned but as long as you set the edition to the most current one you are good to go.

This query showed that the recreated user was not using editioning

SELECT username,editions_enabled
FROM   dba_users
WHERE editions_enabled = 'Y';


Is it a good idea to split an auditable table into two tables based on the update frequency?

I think your assessment is heading in the right direction. You do appear to be capturing too much information. Based  on your description, I think you are describing a consumption event and not product history.
My recommendation is to look at splitting out the consumption attributes from the PRODUCT_HISTORY table into a new table.  Without knowing the omitted columns, I am unable to provide a recommendation. Here are some thoughts:

if the "name" column is the product's name, that doesn't change with a consumption event and should remain in product history.

The consumption_limit might need to be in the product history and consumption event tables. You may need to know what the limit was when the consumption occurred and the limit may change over time, thus that fact should be recorded in the product history table.

A relationship between the consumption event and the operations table might be needed

Does a row in the operations table describe a collection of individual operations (and perhaps there is an operation table) or is it a singular operation?  That is an important detail on figuring out the relationship with consumption events. If it is singular, I recommend you rename the table.


Can't find process that is creating backups

2015-09-03 19:02:30.71 Backup      Database backed up. Database:
  db_name, creation date(time): 2012/10/12(20:52:11), pages dumped:
  3290021, first LSN: 276028:152755:172, last LSN: 276028:152827:1,
  number of dump devices: 1, device information: (FILE=1,
  TYPE=VIRTUAL_DEVICE: {'{95380B0A-D50B-408F-B95F-1AB8975BA7F8}5'}).
  This is an informational message only. No user action is required.

The fact that VIRTUAL_DEVICE comes into the backup message clearly says that this is not backup scheduled by T-SQL Job or SQL Server so you wont find it anywhere in SQL Server. You either have some third party tool or windows inbuilt backup mechanism running which is taking this backup.

Now if you really want to find out the source of backup consult windows team or storage team and you would get a fair idea. If all the stakeholders say they do not have idea about the source of backup ( which is highly unlikely) you can stop such backup from happening by disabling SQL Server VSS writer from Services.msc.

Such Third party backups rely on SQL Server VSS writer services to connect to database and take backup.

Before proceeding above I would suggest you to talk to backup/windows admin if they are not aware about any such backup you can go ahead and disable this service. Otherwise you must not and you should consult more regarding this with them.

Please read Information Shedding Light on VSS Backups


SQL Server Management Studio does not load

Please try:

  • Start regedit.exe

  • Go to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management
  Studio

  • Delete 11.0_Config

  • Restart ssms.exe

There is a similar problem in this feedback to Microsoft, please refer the below link..

http://connect.microsoft.com/VisualStudio/feedback/details/690773/after-installing-visual-studio-11-my-sql-server-denali-cannot-be-opened


(AWS) Mysql memory alert threshold(for monitoring) and innodb_buffer_pool_size

With the buffer_pool set at 75% of RAM, it will grow until it consumes that much of RAM.  But there is a lot of other things in RAM -- the OS, MySQL code, other caches, buffers, etc.  The real goal (for a server running only MySQL) is to consume 100% of RAM, but no more.

The buffer_pool is the biggest item that can be tuned.  75% is a good number (assuming you have at least 4GB of RAM).  Only in extreme cases (or you fiddle with other tunables), will you blow out 100%.

Using more memory than you have is really bad because it leads to "swapping".  But MySQL is not coded to understand that.  It assumes, for example, that the entire buffer_pool can be accessed without any disk I/O.  Disk I/O is a performance killer.

I don't know what AWS means by FreeableMemory, but 66% seems fine.  Even 25% seems fine.  5% would seem dangerously close to swapping, and I would worry.  None of the buffer_pool should be "freeable"; it sounds like the buffer_pool has not yet grown to its stated max.


How to grant SQL Agent proxy to a public role in MSSQL 2014

Before I answer your question I want to draw your attention about granting permissions to Public Msdb Role on the Proxy.

Reference: Best Practices in Configuring SQL Server Agent Proxy account.

  Proper care must be taken when granting permissions to a security
  principal on a Proxy especially when granting permissions to Public
  Msdb Role on the Proxy. Every user in the database is a member of the
  public role and as a result every user gains access to the Proxy when
  Public Msdb role is granted access to the Proxy. You can query the
  sysproxylogin system table to get the list of SQL Server logins that
  are associated with each SQL Server Agent proxy account.

You can achieve this by using the following TSQL (assuming your proxy is created properly).

USE msdb ;
GO

EXEC dbo.sp_grant_login_to_proxy
    @msdb_role = N'public',
    @proxy_name = N'Proxy Name' ;
GO

Details about using sp_grant_login_to_proxy.


Migrate SQL Server to Oracle 11G Offline (OMWB)

The resolution is:

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SESSION TO YOUR_USER

Or grant DBA Privilege
Before you granted the privileges, delete the repository associate and create again with new privileges or create the repository before you grant the privilege.

This way resolve the problem.


Do i need to rebuild the indexes on the table after changing column length on SQL table?

As far as I know you won't run into issues with expanding the length of a VARCHAR column. However, there are certain types of datatype changes that do you require you to drop and recreate indexes. Perhaps you are thinking of those?

This is something that's fairly straightforward to test. You don't even need data in your test table. For example:

CREATE TABLE #X_TEST_IX (COLUMN1 VARCHAR(100) NOT NULL, PRIMARY KEY (COLUMN1));

-- runs without an error
ALTER TABLE #X_TEST_IX
ALTER COLUMN COLUMN1 VARCHAR(200) NOT NULL;

-- throws an error
ALTER TABLE #X_TEST_IX
ALTER COLUMN COLUMN1 INTEGER NOT NULL;

The error thrown by the last statement is:

  Msg 5074, Level 16, State 1, Line 19 The object
  'PK__#X_TEST___7116E265BDC9D4B5' is dependent on column 'COLUMN1'

  Msg 4922, Level 16, State 9, Line 19 ALTER TABLE ALTER COLUMN COLUMN1
  failed because one or more objects access this column.


Row count from a certain table in multiple databases

I would use the system metadata to make all of this an easier experience. The query from this article will show you how to use the metadata to determine the rowcount for all your tables in a database.

It'd look like

SELECT
    s.[Name] as [Schema]
,   t.[name] as [Table]
,   SUM(p.rows) as [RowCount]
,   db_name() AS dbname
FROM
    sys.schemas s
    LEFT OUTER JOIN
        sys.tables t
        ON s.schema_id = t.schema_id
    LEFT OUTER JOIN
        sys.partitions p
        ON t.object_id = p.object_id
    LEFT OUTER JOIN
        sys.allocation_units a
        ON p.partition_id = a.container_id
WHERE
    p.index_id  in(0,1) -- 0 heap table , 1 table with clustered index
    AND p.rows is not null
    AND a.type = 1  -- row-data only , not LOB
    and t.[name] = 'sia_pais'
GROUP BY
    s.[Name]
,   t.[name]
ORDER BY
    1
,   2;

Great, now you know the row count for a single table. You can then patch that into your existing cursor. The other nice thing about this route is it doesn't matter whether the table exists or not. It will simply not return a row if the table is gone.

And for the sake of providing a mostly full solution

DECLARE
    @NomeBase as char(60)
,   @base as nvarchar(4000)
,   @select as nvarchar(4000);

-- Create a template query to capture table counts
-- Since it's lightning fast, I delay filtering until the end
SELECT
    @base = N'USE <dbname/>;
    INSERT INTO
        ##rowCounts
    SELECT
        s.[Name] as [Schema]
    ,   t.[name] as [Table]
    ,   SUM(p.rows) as [RowCount]
    ,   db_name() AS dbname
    FROM
        sys.schemas s
        LEFT OUTER JOIN
            sys.tables t
            ON s.schema_id = t.schema_id
        LEFT OUTER JOIN
            sys.partitions p
            ON t.object_id = p.object_id
        LEFT OUTER JOIN
            sys.allocation_units a
            ON p.partition_id = a.container_id
    WHERE
        p.index_id  in(0,1)
        AND p.rows is not null
        AND a.type = 1
    GROUP BY
        s.[Name]
    ,   t.[name]
    ';

--
if object_id('tempdb..##rowCounts') IS NOT NULL
BEGIN
    DROP TABLE ##rowCounts;
END;

-- Create a global temporary table to keep track of table counts
CREATE TABLE
    ##RowCounts
(
    [SchemaName] sysname
,   [TableName] sysname
,   [RowCounts] bigint
,   [DatabaseName] sysname
);

-- Enumerate through all of your databases
DECLARE CBases CURSOR FOR
-- select name from sys.databases
select databasename from sig_orgao_web
 --gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO  @NomeBase

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    -- Replace our token with the actual database name
    SET @select = replace(@base, '<dbname/>', @NomeBase);

    EXEC(@select);
    FETCH NEXT FROM CBases INTO  @NomeBase;
  END;
CLOSE CBases;
DEALLOCATE CBases;

-- Retrieve actual counts
SELECT
*
FROM
    ##RowCounts RC
WHERE
    RC.TableName = 'sia_pais'
    AND RC.SchemaName = 'dbo';

What remains? Your account might not have access to a given database. The template could be further armored by wrapping a try/catch around the whole thing. But for this case, I assume you have access and if you don't, it comes roaring back at you instead of silently swallowing errors.


The best first thing you can do is to look around at Open Source offerings in the same area as your own interest. I'm not exactly sure what you mean by "action plan creator" (clarify?). The beauty of Open Source is that you can run the stuff, see what you like and then open up the hood and see how it's done for the applications that appeal to you. Don't forget the KISS and YAGNI principles (Keep It Simple Stupid and You Ain't Gonna Need It). Don't be overambitious on your first pass and don't forget that you'll (very probably :-) ) have to throw the first app away anyway (Fred Brooks).


How can I integrate a new system to a design scheme I previously created?

Would it not solve your problem to redefine the GRADE_LEVEL table such that it contains either the grade of a course or the level of the course (which ever applies)?

The rows in GRADE_LEVEL could contain values such as:

GRADE 7
GRADE 8
GRADE 9
GRADE 10
GRADE 11
GRADE 12
LEVEL 1
LEVEL 2
LEVEL 3

You might consider adding a partitioning attribute to the GRADE_LEVEL table that would indicate which educational system is being applied.  This may be helpful for some of your application logic that might need to apply different processing depending on which system is in use for a given COURSE.


Possible Causes of a Slow Sql View When Filtered

It seems like a small portion of the plan is different depending on whether the filter is present. I'm trying to piece together what is happening, but that is some serious obfuscation trying to match up everything. Without knowing the definitions of things like Table1.Index9 and Table2.Index38 it's going to be very difficult to get specific help with the most expensive operators you have here.

In the meantime I have a couple of generic performance suggestions, other than reducing the complexity of the view and maybe considering having simpler views that don't output 130 columns (try not to go cross-eyed looking at this join diagram):

(1) make sure your statistics are up to date on all the underlying tables. Almost every single operation is showing a massive gap between estimated and actual rows, which could help to explain suboptimal optimizer choices.

(2) get rid of two lookups by adding the following columns to the following indexes (probably as INCLUDE columns):

Table1.Column8  => Index3
Table1.Column43 => Index1

(3) some of these expressions are maddening, e.g.

CASE WHEN (
 Database0.Schema0.Table3.Column42 IS NOT NULL OR
 Database0.Schema0.Table3.Column42 IS NULL
)
AND Column56 IS NOT NULL
AND Database0.Schema0.Table3.Column55 IS NULL
AND Database0.Schema0.Table3.Column43 IS NULL
THEN (1) ELSE (0) END

Seriously? Look at that clause closely (and the view seems to be littered with dozens of these). No human could have done this on purpose. So what brain dead code generator produced this mess?


The problem here is the approach. Looking at table values and guessing keys is not a way to go.

Simply write down the predicate and constraints for the table, everything else follows from this.

Here is one example. If your business rules (predicate and constraints) are different then keys are going to be different too, so table may or may not have to be decomposed.

Note:

[Px]   = predicate x
[cx.y] = constraint x.y

KEY = PK or AK

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY

[P1] Test number (TEST_ID) of test type (TEST) with total score of (TOTAL_POINTS) was administered on date (DATE) by teacher (TEACHER_ID).

[c1.1] Test is identified by test number.

[c1.2] Each test is of exactly one test type; for each test type it is possible that more than one test is of that test type.

[c1.3] Each test has exactly one total score, for each total score it is possible that more than one test has that total score.

[c1.4] Each test was administered on exactly one date; for each date it is possible that more than one test was administered on that date.

[c1.5] Each test was administered by exactly one teacher; for each teacher it is possible that more than one test was administered by that teacher.

test {TEST_ID, TEST, TOTAL_POINTS, DATE, TEACHER_ID}
 KEY {TEST_ID}

As far as the TEST column is concerned, it simply contains  valid values from the domain of this attribute. You may for example add a predicate.

[P2] Test type (TEST) exists.

test_type {TEST}
      KEY {TEST}

and populate with valid domain values

test_type (TEST)
{
  ('Pronoun Quiz')
, ('Solids Quiz')
, ('China Test')
, ('Grammar Test')
}

Use this to constrain values in the test table, so it would look like this:

test {TEST_ID, TEST, TOTAL_POINTS, DATE, TEACHER_ID}
  PK {TEST_ID}
 FK1 {TEST} REFERENCES test_type {TEST}

When considering physical design, you may want to use an integer for this foreign key -- so this may look like:

test_type {TEST_TYP_ID, TEST}
       PK {TEST_TYP_ID}
       AK {TEST}

test {TEST_ID, TEST_TYP_ID, TOTAL_POINTS, DATE, TEACHER_ID}
  PK {TEST_ID}
 FK1 {TEST} REFERENCES test_type {TEST_TYP_ID}

And so on; however, without explicitly defining business rules -- in form of predicates and constraints -- everything is open to misinterpretation and endless arguing.


Is there a syntax for accessing table names containing slashes?

I'd try escaping stuff and see what happens

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM //MYWHATEVER//MYTABLE')
SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM \/MYWHATEVER\/MYTABLE')
...

I assume that the local ODBC layer is bollixing you. The bit inside OPENQUERY should be a literal to SQL Server so it is external to SQL Server. So it could be be the ODBC layer: can you profile (or log) what happens on the MaxDB to see what gets through?

Another option would be use a view or proc in the MaxDB to give "proper" names


How can I create a stored procedure using xp_fixeddrives?

I've recreated your scenario on my test server that has 2 linked servers that were created manually.

CREATE TABLE SERVERNAME
(
    [Name] nvarchar(255)
);

INSERT INTO SERVERNAME ([Name]) SELECT name from SYS.SERVERS;
GO

CREATE PROCEDURE freespace
AS
BEGIN
    CREATE TABLE #freespace
    (
        drive VARCHAR(50)
        , freedrivespace VARCHAR(50)
    );
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE curspace CURSOR LOCAL FORWARD_ONLY FOR
    SELECT [Name] FROM SERVERNAME;
    OPEN curspace;
    FETCH NEXT FROM curspace INTO @servername;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = '[' + @servername + ']...xp_fixeddrives';
        INSERT INTO #freespace
        EXEC @sql;
        FETCH NEXT FROM curspace INTO @servername;
    END
    CLOSE curspace;
    DEALLOCATE curspace;
    SELECT * FROM #freespace;
END
GO

EXEC FREESPACE;

The results for my server are:

drive   freedrivespace
C       75305
I       277458
K       861878
C       24378
D       90630
F       7575392
G       972578
H       768495
I       50952
J       7150657
C       13799
D       25162
F       6745607
G       982818
H       580167
I       50881
J       7224593

Granted the results are not exactly usable since the name of the server is not shown, however it does work.  This tells me the problem is not in your dynamic SQL; the problem lies with your linked server configuration.

Are you able to manually run a simple query such as:

SELECT * FROM [server1].master.sys.tables;

This should either return results, or most likely the same error you are seeing above.


mysql 5.6 gtid replication: is log_slave_updates required?

Yes, you must set it when using gtid, otherwise mysql won't start, providing you the following error:

[ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires
--log-bin and --log-slave-updates

The slave requires its own executed binary logs in order to auto-position itself in the case of a stop (for example, if it crashes) or a master change (the hardware on the master fails without a possibility of a fix and another slave is promoted as the new slave), even if it is not going to be a master itself never. Although I suppose that can be controlled exclusively in-memory when running, I would bet it needs its own binary logs as a failsafe in case of a crash.

You may think that that is a lot of trouble, but it is a small burden in exchange for not having to deal with binlog files and binlog pos addresses anymore. Also, binary logs on the slave was always a good idea. You can still purge them automatically with expire-logs-days/max-binlog-size if the size is the problem. And with group-commit in 5.6, the performance penalty should be minimal. The other thing why I did not used log_slave_updates before (checking that no writes have been done on the slaves) can now be controlled with gtid_executed values.

In a nutshell, set it up, and do not worry on most cases.


optimize a 'having count (distinct ) ' query for big data

A big part of the performance problem is DELETE, which will save the old values in case of a crash or other ROLLBACK.

If there is a composite INDEX(DID, week_no) (in that order), there is some hope.  With that index, walk through it a few DIDs at a time.  That is, put the delete in a loop that gradually increases DID.

See this for discussion of ways to do the walking, especial with a composite index.

The overall time taken by chunking may actually be less, but it will still be a long time.  If you need to run this query frequently, let's discuss other techniques to prepare for the need.


MySQL Tables Require Daily Repairs - Server, Table or HD?

You should get away from MyISAM as soon as possible. Why ???

MyISAM caches only index pages in the Key Buffer
InnoDB caches Data Pages and Index Pages in the InnoDB Buffer Pool

I have mentioned this many times before

May 06, 2013 : MySQL high CPU usage (MyISAM table indexes)
Apr 08, 2013 : If MySQL isn't caching, then what is?
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Apr 04, 2011 : What are the main differences between InnoDB and MyISAM?

This is very critical because data pages must rely solely on the OS for cached writes to the .MYD of a MyISAM table. In a highly trafficked website, the OS will experience a glut in memory with swapping as a consequence and foregone conclusion. An OS crash or a mysqld crash  will yield corrupt MyISAM tables for every MyISAM table that was open and not fully flushed.   A crashed MyISAM table could simply be just a bad open file count in it header. In other case, it could be incomplete writes to the .MYD file. In the two repair messages in the question, you lost three row both times.

IMHO this problem is very apparent when running MySQL in Windows (Imagine : Windows handling disk writes ???) and no less different for Linux. MyISAM will suffer just the same.

I highly recommend three(3) things:

Switch to InnoDB
Increase installed RAM
Configure as much buffer pool as possible

Give it a Try !!!


There are several things you can count with COUNT() function:

count(*) : rows
count(col1) : rows where col1 is not null
count(col2) : rows where col2 is not null
count(distinct col1) : distinct col1 values.
count(distinct col2) : distinct col2 values.
count(distinct col1, col2) : distinct (col1, col2) values combinations.

Tested at SQLfiddle:

    select
        count(*)             as count_rows,
        count(col1)          as count_1,
        count(col2)          as count_2,
        count(distinct col1) as count_distinct_1,
        count(distinct col2) as count_distinct_2,
        count(distinct col1, col2) as count_distinct_1_2
    from demo ;

But count(col1, col2) is not valid syntax:

select
    count(col1, col2)
from demo ;

gives an error:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'col2) from demo' at line 2.


How to avoid implicit conversion for an Integer column

It is the parameter that has been implicitly converted, not the column.

The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The implicit cast of a tinyint parameter to an int won't cause any problems.

However to avoid having multiple plans in cache for int, smallint, tinyint and get rid of the implicit cast you could explicitly parameterize the query yourself - with a parameter of datatype int rather than having it be parameterized automatically.

EXEC sys.sp_executesql
  N'SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = @BusinessEntityID',
  N'@BusinessEntityID INT',
  @BusinessEntityID = 5;

One other alternative would be to block simple parameterisation by adding a redundant AND 1=1 as below. But I don't recommend this as then you will get plans compiled and cached for every different literal value that you pass.

SELECT [BusinessEntityID],
       [NationalIDNumber],
       [LoginID],
       [OrganizationNode]
FROM [AdventureWorks2014].[HumanResources].[Employee]
where BusinessEntityID = 5
      AND 1=1


What is going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced?

If initdb is run through a run-level script of the OS. This script might not pass on the parameters.

I couldn't run initdb directly as a superuser. Gave an

initdb: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

error. After logging in as an unprivileged user I was able to initialize the db with the proper encoding.

To initilize the db, run initdb directly. The command

/usr/bin/initdb --pgdata=/var/lib/pgsql9/data/ -E 'UTF-8' \
--lc-collate='en_US.UTF-8' \
--lc-ctype='en_US.UTF-8';

was run under the non-superuser postgres.


Time Difference Between Two Consecutive Rows

You can use a window function (aka "Analytic Function") to access values in the rows outside of the "current" row.

If you do that, it is mandatory to specify a criteria that defines the sort order of the rows otherwise there isn't such a thing as "the previous rows".

Given the extremely limited information you have shown us, you are probably looking for something like the following:

select subno,
       contrno, orderby, prepost_paid,
       time_stamp,
       lag(time_stamp) over (order by time_stamp) - time_stamp as timestamp_diff
from the_table
order by time_stamp;