How to set the Tablix outer border width

In your first screenshot, the BorderStyle value is None. In order for the BorderWidth setting to have an effect, the BorderStyle setting must be changed also (e.g. to Solid).

The borders in your second screenshot are cell/row borders. The weight of these borders are controlled via the BorderWidth setting of the cell or row.

The Tablix border is in addition to the cells borders, so if you want the Tablix to have a thinner border than the cells you will need to either use different colours (e.g. LightGrey for the cells and Black for the Tablix), or change the top/bottom/left/right border settings for each outer cell.


A MySQL EXPLAIN number of rows discrepancy

Try to replace AND PeriodStart >= ? AND PeriodStart < ? + INTERVAL 1 WEEK clause with precalculated version:

SET @PeriodEnd = ? + INTERVAL 1 WEEK;
SELECT SectorID, SUM(Value)
    FROM SectorGraphs2
    WHERE Kind = 1 AND Criterion = 7
      AND Period = 1
      AND PeriodStart BETWEEN ? AND @PeriodEnd
    GROUP BY SectorID;

Explicitly point the index you want to use:

SET @PeriodEnd = ? + INTERVAL 1 WEEK;
SELECT SectorID, SUM(Value)
    FROM SectorGraphs2 USE INDEX (`Producer2`)
    WHERE Kind = 1 AND Criterion = 7
      AND Period = 1
      AND PeriodStart BETWEEN ? AND @PeriodEnd
    GROUP BY SectorID;

Reorder fields in the index definition placing more selective fields first. To determine selectivity run the next query:

SELECT COUNT( DISTINCT Criterion )
  FROM SectorGraphs2;

Then replace Criterion with Kind, PeriodStart, SectorID etc.

The higher count means better selectivity and you have to arrange fields in the index from the best selectivity to worst.


Is it necessary to create foreign key in this query?

I am not entirely certain that I understand the semantics of your database design but foreign keys have a special meaning in a database.

A foregin key, investors_id in the Users table,  is a constraint saying that if a value appears in the investors_id column in the Users table, then that value must appear once in the Investor table id column.

My interpretation of that fact is that a user might be an investor as well and if he/she isn't then there must be a null value in the investors_id column in the Users table for that person.

This, in turn, means that as all investors must be users and then it is acceptable that there is the corresponding constraint the other way so you might have a foreign key in the investor table "pointing" at a record in the Useres table but it is not neccessary.

I would, however, think it over more than once. Do I really need to specifically point at this fact?

In the other two cases, foreign keys in the investors table referring to the Login_Logs and Accounts table, the same reasoning tells you that it is wrong.

As several Accounts table records may refer to the same investor in the investors table, there is from a semantic point of view no such constraint in the opposite direction.

The fact that you have a one-to-many relationship from investors to Accounts and from investors to Login_Logs makes it wrong. You would break the obvious semantics, an investor may deal   with many accounts but an account is related to a single investor.

Finally, foreign keys might have performance aspects but I never use them that way. They are the glue that determines the semantics of a database and in my opinion that is the only way to use them.

So, my conclusion is that it is possible to have a foreign key in the investors table referring to the Accounts table but not neccessary and it is wrong in the other two cases.


What is causing Waiting for table level lock errors?

INITIAL OBSERVATIONS

Process ID 42686 says its preparing to execute a SELECT query
There are some sleeping connections
All other processes cannot acquire a table lock
I would have expected an UPDATE, DELETE, or INSERT to do the lock. There are no claiming ownership of the table in question.
Can't see the full query in Process ID 42686, but I suspect it involves a JOIN, GROUP BY, or ORDER BY

WORKING THEORY

If you ran out of diskspace with the process list you gave me, then we can place blame on the MyISAM storage engine. Why?

In your particular case, it is not one of your tables. If a JOIN,GROUP BY, or ORDER BY was being executed and a temp table was being written to disk (on disk temp tables use the MyISAM storage engine) the MySQL simply freezes when out of space. How do I know that ?

According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

  If you run out of disk space while adding rows to a MyISAM table, no
  error occurs. The server suspends the operation until space becomes
  available, and then completes the operation.

I have discussed this situation before

Apr 05, 2013 : "Site Offline" MySQL server failing to start and stop
Dec 17, 2012 : How to solve MySQL “The table is full” error 1114 with Amazon RDS?
Apr 25, 2012 : Very Large Log Files
Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?

Something tells me that you have one of these two situations

disk-based temp tables for your SELECTs and competing for space with your regular data
If temp table are landing in /tmp in the root partition, that's running out of space

SUGGESTIONS

Suggestion #1: Map tmpdir to another disk

[mysqld]
tmpdir = /another/disk/besides/root/partition

Suggestion #2: Create a RAM Disk

Run this code to install a RAM disk that will available on Linux reboot.

RAMDISK_SIZE=32g
service mysql stop
mkdir /var/tmpfs
echo "none   /var/tmpfs  tmpfs  defaults,size=${RAMDISK_SIZE} 1 2" >> /etc/fstab
mount -t tmpfs -o size=${RAMDISK_SIZE} none /var/tmpfs
cp -R /var/lib/mysql/* /var/tmpfs
mv /var/lib/mysql /var/lib/mysql_old
ln -s /var/tmpfs /var/lib/mysql
chown -R mysql:mysql /var/tmpfs
chown -R mysql:mysql /var/lib/mysql
service mysql start

Then, map tmpdir to /var/tmpfs

GIVE IT A TRY !!!


Hi, I have sitution where my Database has corrupted but some how we have recovered it but while restoring the .bak file it shows an error

The error message indicates that the backup file is corrupt (something happened after a successful backup to break the file), incorrectly formed (possibly caused by a backup that failed mid way), or possibly not even a SQL Server backup (someone just named the file with a BAK extension).

When attempting to restore a backup from Management Studio Studio, SSMS uses the RESTORE HEADERONLY command before it actually restores the database. This step is the step that is being reported as failing in your error message.

This tells us two important things:

The operation fails prior to the actual restore.
At minimum, the backup header is corrupt or missing, but possibly more.

The header tells SQL Server information about this backup, as well as the database from which this backup was created. Without this information, the backup cannot be restored.

Your best bet is to move on to an older backup and attempt to restore that. If you have no good backups, then you're pretty much out of luck.


I found that after I re-install again oracle-xe and set the password same with my system password it worked.


SELECT statement not displaying data correctly when using variable and reusing it

The problem is (probably) in the definition of "later".  The query execution is free to evaluate pieces of the query in whatever order it desires.  This makes it difficult to predict whether it is trying to "use" the @ variable before or after it was "set".

And for that reason, @ variables are (or soon will be) deprecated.

If you can provide a small test case with input and desired output, perhaps we can help you rewrite the query to avoid @ variables.


MongoDB Sharded Cluster Upgrade from 2.4 to 3.2 version

Yes. Period. No "Ok, but..."
You should make a backup. Start now, continue reading later. It is presumably going to take a while.
Config servers in 2.4, 2.6 and 3.0 do *not* form a replica set. Those were basically 3 standalone instances.
Read and understand the migration guide to 2.6 for sharded clusters. Rinse, repeat.
Read and understand the migration guide to 3.0 for sharded clusters. Rinse, repeat.
Read and understand the migration guide to 3.2 for sharded clusters Rinse, repeat.
Read the guide Upgrade Config Servers to Replica Set. Rinse, repeat.
Wait until your backup is done.
Upgrade to 2.6 according to guide mentioned above. Make sure (aka "test thoroughly") your cluster works as expected. Continue at the next appropriate maintenance window.
Upgrade to 3.0 according to the guide mentioned above. Migrate to WiredTiger in the process, unless you have very strange requirements. That is: shutdown data secondary data bearing node, delete its datadir, adjust config to utilize WT, resync, proceed. Have primary step down Before shut down. Make sure (aka "test thoroughly") your cluster works as expected. Continue at the next appropriate maintenance window.
Upgrade to 3.2 according to the guide mentioned above. Make sure (aka "test thoroughly") your cluster works as expected. Continue at the next appropriate maintenance window.
Convert your config servers to a replica set according to the guide mentioned above. Make sure (aka "test thoroughly") your cluster works as expected. Continue at the next appropriate maintenance window.
Take a few notes: "Good resolution 1: read release notes and docs of my chosen persistence technology. Good resolution  2: I will only ask questions to my peers after thoroughly researching the topic myself."


Reset MongoDB root Password on Amazon Linux

Yes, you need to start your mongod to maintenance mode, without --auth. Then you can change root user password.


Can I manually reset the "internal status flag for a full database backup"?

Your primary goal here is to ensure your ability to restore the database.  As long as you have the complete chain of your log backups from the last full to now, you can perform a point in time recovery and the error is merely an inconvenience.  Differential backups are not required for full point in time recovery, just provide you with a shortcut for larger time frames.  However, in the absence of your differentials, you'll need all the log backups to cover the gap.

Otherwise, the only way to properly reset this is to execute another actual full backup.  I would recommend executing this as soon as possible which, depending on the size of your database, should be something that could be accomplished overnight in place of your normal differential.

Also, I would strongly recommend that you apply Service Pack 4 for SQL 2005 to make sure you have the most current hotfixes/updates for your platform.


Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?

Get the dynamic SQL generated by Hibernate and ask PostgreSQL to EXPLAIN PLAN.
Log the queries to your database using an aspect.  Add in wall time while you're at it so you can calculate mean wall time and standard deviation.
EXPLAIN PLAN and look for TABLE SCAN
Don't know - it's not under PostgreSQL control.  Better to look into your app server.
Aspects and JMX
Aspects and JMX


designing a database with type and sub type

This seems nearly identical to the following question, and my answer there should be detailed enough to explain the concept (and is just too much to copy and paste here).

Don't know how to transform variable entity into relational table

For your case specifically I would think it should work itself out to be something along the lines of:

Publication table is the main entity with the common properties, a PK on PublicationID, and a PublicationTypeID field that FKs to PublicationType
Lookup table for PublicationType that has a PK of PublicationTypeID. Entries would be the various genres.
Sub-class tables for the genres, such as Poetry, Fiction, etc. Each sub-class table has a PK named PublicationID that also FKs back to the Publication table, and fields for properties that are specific to the particular genre.
Where applicable, sub-class tables have sub-type field, such as PoetryTypeID and FKs to the PoetryType table.
Lookup tables for sub-classes, one table per each sub-class type. The PK would be PoetryTypeID (e.g. Prose, Haiku, Free Verse, etc).

In light of additional details being made available that give context to the scope of the request (specifically the following comment on the question:

  the data files ... are all in fact printed, no audio of image/video files. ... one sub-genre is sufficient, and ... it is possible to have just a genre without any sub sections..

), it seems clear now that the flexibility allowed for by my suggestion above is not needed as sub-types aren't really being stored. Instead, the request now seems to be for simply a means of categorization. For that, if you want a rigid structure of Genre / Sub-Genre such that:

those are the only two levels, and
you always know which level you are dealing with (in queries), as opposed to a dynamic structure where each Publication can be associated with a Genre / Sub-Genre at any level (1 through N) and you would need to query to get the heirarchy

then you can do something along the lines of:

Publication
-------------
PublicationID  INT           NOT NULL  PRIMARY KEY
SubGenreID     TINYINT       NOT NULL  FOREIGN KEY  REFERENCES(SubGenre.SubGenreID)
Title          VARCHAR(200)  NOT NULL
...

SubGenre
-------------
SubGenreID     TINYINT      NOT NULL  PRIMARY KEY
GenreID        TINYINT      NOT NULL  FOREIGN KEY  REFERENCES(Genre.GenreID)
Name           VARCHAR(50)  NOT NULL

Genre
-------------
GenreID        TINYINT      NOT NULL  PRIMARY KEY
Name           VARCHAR(50)  NOT NULL

In this model you know that what is in the Publication table is always the SubGenre, and it always has a parent Genre. You did say that it is possible to not have a SubGenre (though is that really the case?), you can use the same Name for both SubGenre and Genre (e.g. News -> News instead of Poetry -> Bad Teenage).

Or, if you want a dynamic structure of Genre / Sub-Genre such that:

There can be just one level or any number of levels, and
You can only easily tell if you are either at:

a top-level Genre (ParentID IS NULL), or
one or more levels deep in SubGenre-land (ParentID IS NOT NULL).

Else you need to query to get the full hierarchy

then you can do something along the lines of:

Publication
-------------
PublicationID       INT           NOT NULL  PRIMARY KEY
PublicationTypeID   SMALLINT      NOT NULL  FOREIGN KEY
                                              REFERENCES(PublicationType.PublicationTypeID)
Title               VARCHAR(200)  NOT NULL
...

PublicationType
-------------
PublicationTypeID        SMALLINT     NOT NULL  PRIMARY KEY
ParentPublicationTypeID  SMALLINT     NULL      FOREIGN KEY
                                              REFERENCES(PublicationType.PublicationTypeID)
Name                     VARCHAR(50)  NOT NULL

Please note that the ParentPublicationTypeID field is NULLable, which indicates a top-level item when it is NULL. This is the model that Walter Mitty suggested.


Can't Connect to MySQL database through PuTTY

If you are connecting on "localhost", then GRANT ... TO user@localhost ... applies.  "127.0.0.1" may be a synonym for "localhost".

If you are connecting via TCP/IP, then GRANT ... TO user@'11.22.33.44' ... applies.  (Replace with suitable IP or hostname or wildcard.)

It is OK to have both GRANTs in order to allow access either way.


That's most likely an NLS (language) problem and you've got customer names with characters your database cannot handle. You'll need to determine the actual type of the CUS_NM column in the Oracle database.


Determine if existing Trigger acts on field DB2

If you are asking if you can determine if the table in question has any triggers defined, then yes - you can either

Look at the view SYSCAT.TRIGGERS, filtering on the TABSCHEMA and TABNAME columns
Extract the DDL for the table using the db2look command.

If you are asking if there's any way to determine whether existing rows that have a value were updated as a result of an existing trigger, then the answer is no.  There is nothing inherent to triggers that would allow you to identify them as the source of the data in the column.  The trigger could contain some logic to identify itself as the source of the value, but that would be stored in some other column or table.


Move transactional replication subscriber to another server without re-snapshotting

My answer will assume you are doing this during downtime.

Once downtime start (confirm app servers are not making any new connection/writing to publishing articles):

Continue running the distribution agent jobs till undelivered
command count is zero (This is key that you do not have any undelivered command).
Stop the distribution agent job/jobs.
Script out replication.
Remove replication (publisher and subscriber only) but keep the
distributor as is.  That is my preference but you can also decide to remove subscriber only.
Reinstall replication with 'replication support only' when running sp_addsubscription. See details here.
Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.

  replication support only Provides automatic generation at the
  Subscriber of article custom stored procedures and triggers that
  support updating subscriptions, if appropriate. Assumes that the
  Subscriber already has the schema and initial data for published
  tables. When configuring a peer-to-peer transactional replication
  topology, ensure that the data at all nodes in the topology is
  identical. For more information, see Peer-to-Peer Transactional
  Replication.

  Not supported for subscriptions to non-SQL Server publications.

You can use this query to find undelivered commands in distribution database.

SELECT ss.srvname AS publisher,
       [name]     AS Agent,
       publication,
       a.publisher_db,
       subscriber_db,
       art.article,
       art.destination_object,
       art.source_object,
       undelivcmdsindistdb,
       delivcmdsindistdb,
       s.article_id,
       agent_id,
       a.publisher_id,
       subscriber_id
FROM   distribution.dbo.msdistribution_status AS s WITH (nolock)
       FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock)
                    ON s.agent_id = a.id
       JOIN master.dbo.sysservers AS ss WITH (nolock)
         ON a.publisher_id = ss.srvid
       JOIN distribution.dbo.msarticles AS art WITH (nolock)
         ON s.article_id = art.article_id
WHERE  subscriber_db <> 'virtual'
       AND subscriber_db = 'SubscriberDBName'
ORDER  BY undelivcmdsindistdb DESC
COMPUTE sum(undelivcmdsindistdb)


Command runs fine in elevated command prompt, but fails when run in a Windows Task Scheduler task

Thanks guys I resolved the problem. Although I'm not sure what I did that resolved it. What I did:

Created a brand new SQL Server login with sysadmin rights (I know it shouldn't need that much, but hey, I needed to fix this.)
Added a new local user login in users and computers MMC in the SQL Server hosting the machine, and made it a local admin to the machine.
Updated everything to use those users, instead of what we had before, and suddenly it started working.
So, it's something related to permissions, most likely for the SQL Server login. But still, as far as I can tell the initial user had everything it needed. So much so that I could confirm that to be the case both using a command line as well as a batch file.

Thanks for trying to help. Closing this here. Best, Raphael.


Difference between master site and materialized view site in oracle

Let's say I have two databases, DB1 and DB2. I want to replicate data from DB1 to DB2. I created materialized views on DB2 and these materialized views gets data (periodically) from tables stored on DB1.

So which is master site here?
The database from which replicated sites(DB2 which has materialized views) gets data. In other words the database which stores master tables , which is DB1 in our case.

then materialized view site?
The database which stores the materialized views, which is DB2.

Documentation:
About Master Sites, Master Tables, and Materialized View Sites


InnoDB page_cleaner error message: The settings might not be optimal

mysql> set global innodb_lru_scan_depth=256;


MySQL Cluster - Table is full [Error 1114] while still creating tables

After reading deeper the manual, storing the model in an InnoDB engine and running ndb_size.pl I have been able to find that these parameters (which had their default values) were in fact too low:

    MaxNoOfOrderedIndexes
    MaxNoOfUniqueHashIndexes
    MaxNoOfAttributes


How can user determine which hash their password uses?

No, a user would need SELECT on mysql.user to be able to read the password column, or the SUPER privilege to be able to see the password in the output of SHOW GRANTS. I think both of those would be classed as "admin rights".


Count children of a parent in recursive CTE

To count all children, there is no way around following each path to the last element. You can then show rows down to some depth while counting the rest:

WITH RECURSIVE cte AS (
   SELECT id, parent_id, content, array[id] AS path
   FROM   comment
   WHERE  id = 1

   UNION ALL
   SELECT c.id, c.parent_id, c.content, cte.path || c.id
   FROM   comment c
   JOIN   cte ON c.parent_id = cte.id
   )
SELECT DISTINCT ON (path[1:2])
       id, parent_id, content, path
     , count(*) OVER (PARTITION BY path[1:2]) - 1 AS children
FROM   cte
ORDER  BY path[1:2], path <> path[1:2]
LIMIT  200;  -- arbitrary limit, unrelated to the question

SQL fiddle.

path[1:2] is the array slice from the 1st up to the 2nd element. Details in the manual.

This works for a depth of 2. Replace all occurrences of 1:2 with 1:3 to do the same for a depth of 3 etc.

The main feature is to "group" by the first n elements of the path. Apply the technique laid out in this related question:

PostgreSQL: running count of rows for a query 'by minute'

The last ORDER BY expression path <> path[1:2] produces a boolean result that is only true for the root element of the group (where the whole path is equal to the leading elements), and FALSE sorts before TRUE. You could simply use path instead - not sure which performs better.


How can I determine who dropped a DB in SQL Server 2005 (and when)?

If it was recent enough, it may still be in the default trace. The basic assumption will be that the DROP event will be the last event recorded for this database. So if someone dropped the database 'splunge' you should be able to catch it this way (again depending on how long ago it happened):

DECLARE @FileName VARCHAR(255)

SELECT @FileName = SUBSTRING(path, 0,
    LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  --'
  FROM sys.traces WHERE is_default = 1;

SELECT TOP 1 LoginName, HostName, ApplicationName, StartTime, TextData
  FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
  WHERE EventClass = 47 AND DatabaseName = 'splunge'
  ORDER BY StartTime DESC;


Default value for "microtime" column in MySQL

You could format it with UNIX_TIMESTAMP like below:

mysql> SELECT UNIX_TIMESTAMP(NOW(6));
+------------------------+
| UNIX_TIMESTAMP(NOW(6)) |
+------------------------+
|      1442068528.543100 |
+------------------------+
1 row in set (0,00 sec)

mysql>

You can find any others date and time functions here. You could add a trigger and if you want a default value for your decimal(16, 6) microtime, use BEFORE INSERT and replace your NEW.bigintvalue=UNIX_TIMESTAMP(NOW(6));.

Example:

mysql> CREATE TABLE `test`.`test1` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `time` DECIMAL(16, 6) NULL,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER //
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`test1_BEFORE_INSERT` BEFORE INSERT ON `test1` FOR EACH ROW
    -> BEGIN
    -> SET NEW.time=UNIX_TIMESTAMP(NOW(6));
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> # Adding 1 into id field
mysql> INSERT INTO `test`.`test1` (`id`) VALUES ('1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.test1;
+----+-------------------+
| id | time              |
+----+-------------------+
|  1 | 1442069359.675330 |
+----+-------------------+
1 row in set (0.00 sec)

mysql>


You should have no problem running multiple versions of SQL Server on the same box.  By default, they will install into separate folders, and won't interfere with each other.

Having said that, I wouldn't bother with the trial version; just use the free Developer Edition assuming you're not using production data.


Application == session == connection in DB2?

For the most part, application == connection == session when looking at it from DB2's perspective.

Someone could probably make an argument that these aren't always necessarily the same in environments using connection concentrator or the database partitioning feature (DPF), but for all intents and purposes it's safe to use the terms interchangeably.

You could also make the argument that, from the client-side, a single application may open multiple connections (i.e. a connection pool), but this is dba.stackexchange.com :-)


The answer is actually pretty easy:

You have to use a Full Outer Join instead of a Left Outer Join in the Merge Join Task.

The referenced tutorials actually told me to do so, I guess I mixed it up with another tutorial that did not include the merge delete.


You're missing the small keyword BEGIN. And as you have multiple statements, you have to change the delimiter.

DELIMITER $$
Create Trigger Count_McNum
before insert on tension
for each row
BEGIN
declare rowcount int ;
set rowcount = (select count(McNum) from tension where median is null) ;
if (rowcount >1000)
then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning:  rowcount> 1000!';
    END IF;
End$$
DELIMITER ;


Performance benefits of multiple .ndf files

You should never have more than one .mdf for a database.
SQL Server doesn't care about the file extension. Best practice, though, is to have a single .mdf for the one and only primary file (boot file for the database) and .ndfs for the other files.
I understand that the question is really about having several data files (one .mdf and x number of .ndf), but I wanted to make the above clear first.
As for contention, you might be thinking of the tempdb, and how we have several data files for the tempdb. The contention we talk about here is when SQL server needs to find free space, and needs to look at page number 1,2 or 3 at the beginning of the file to find out where there is free space.
It need to protect from other doing the same thing at the same time so ot places a "latch" on the page, like a lock. Having more files allow more than one thread find free space simultaneously.
So the question is whether your particular load is limited by one data file when it need to find free space? How common is it that you have several threads that at the same time need to find free pages/extents? Not nearly as common as it is for tempdb, I would bet.
You can of course monitor this by grabbing snapshots of what we're waiting for to see if you are waiting for latches on these pages (1, 2, 3) for your database. Or use XEvents to find out.
There's no thread limitation per file in SQL Server. I.e., SQL Server can push the disk subsystem as hard with one file as with several files. But of course, your disk subsystem capacity might increase with several files (more I/O channels etc).
Then there is the topic about having more than one file group, but I'm not addressing that here since that wasn't in the question.


Find all tables in a snapshot replication that have primary keys

This is what I ended up going with.

--====================================================================================================================================================
--  Get tables in Snapshot replication for the selected DB.
--====================================================================================================================================================

IF OBJECT_ID ('tempdb..#t1') IS NOT NULL DROP TABLE #t1;

SELECT      pub.name                                                                AS [Publication]
          , CASE WHEN pub.name LIKE '%Snapshot%' THEN 'SnapShot'
                WHEN pub.name LIKE '%Transaction%' THEN 'Transaction' ELSE NULL END AS ReplicationType
          , art.name                                                                AS [Article]
          , serv.name                                                               AS [Subsriber]
          , sub.dest_db                                                             AS [DestinationDB]
          , obj.object_id
          , CASE WHEN obj.type = 'U' THEN 'Table'
                WHEN obj.type = 'V' THEN 'View'
                WHEN obj.type = 'P' THEN 'SP' ELSE NULL END                         AS ObjectType
INTO        #t1
FROM        dbo.syssubscriptions sub
INNER JOIN  sys.servers          serv ON serv.server_id = sub.srvid
INNER JOIN  dbo.sysarticles      art ON art.artid = sub.artid
INNER JOIN  dbo.syspublications  pub ON pub.pubid = art.pubid
INNER JOIN  sys.objects          obj ON obj.object_id = art.objid
WHERE       CASE WHEN pub.name LIKE '%Snapshot%' THEN 'SnapShot'
                WHEN pub.name LIKE '%Transaction%' THEN 'Transaction' ELSE NULL END = 'Snapshot';

--====================================================================================================================================================
--  Check for primary keys on the above tables
--====================================================================================================================================================
SELECT          SCHEMA_NAME (tab.schema_id)                         AS [schema_name]
              , tab.[name]                                          AS table_name
              , pk.[name]                                           AS pk_name
              , SUBSTRING (column_names, 1, LEN (column_names) - 1) AS [columns]
FROM            sys.tables  tab
LEFT OUTER JOIN sys.indexes pk ON tab.object_id = pk.object_id
                                  AND   pk.is_primary_key = 1
CROSS APPLY     (
    SELECT      col.[name] + ', '
    FROM        sys.index_columns ic
    INNER JOIN  sys.columns       col ON ic.object_id = col.object_id
                                         AND  ic.column_id = col.column_id
    WHERE       ic.object_id = tab.object_id
                AND ic.index_id = pk.index_id
    ORDER BY    col.column_id
    FOR XML PATH ('')
)                           D(column_names)
WHERE           pk.object_id IN (
                    SELECT  object_id FROM  #t1
                )
ORDER BY        SCHEMA_NAME (tab.schema_id)
              , tab.[name];


Backup Encryption was added in SQL Server 2014.
And SQL 2008 R2 is out of support.  So upgrade, and yes, otherwise you can use TDE, or write ordinary backups to an encrypted location (eg with BitLocker).


Query or ETL process to insert data into fact tables in SSMS

While you don't specify the specific problem related to loading your fact table, I'm going to assume the question approximates "how do I translate AppName to an AppKey so I can store that ID into my fact table?"

What you're looking to do is search your dimension table for a given value and return the key if it's found. Otherwise, we typically have a sentinel value to indicate this is an unknown value. Since you say you've loaded your dimension tables, then this shouldn't be an issue but it's a best practice.

If you are doing this through SSMS, then you would be writing a query that approximates the following

INSERT INTO dbo.Sentimental_Fact
SELECT
   URD.UserKey
,  AD.AppKey
,  D.Positive
,  D.Negative
,  D.Neutral
FROM
    dbo.kaggle_data AS D
    LEFT OUTER JOIN
        dbo.User_Rating_Dim AS URD
        -- I assume the appname in user rating is a typo and
        -- should be UserName
        ON URD.AppName = D.UserName
    LEFT OUTER JOIN
        dbo.App_Dim AS AD
        ON AD.AppName = D.AppName

If you were looking to use SSIS, I suggest you read the excellent Stairway to Integration Services by Andy Leonard. In short, you'll pull in in your base kaggle_data table into a Data Flow Task via an OLE DB Source Component and then use a series of Lookup Components to augment or enrich your data flow with the keys from the dimension tables. Finally, you'd land that into an OLE DB Destination (not an OLE DB Command)


There are two ways to handle this.

One option is to login to the windows machine as a local administrator, start SQL Server in single user mode (-m startup switch) and you will now be able to get into your SQL server as a sysadmin using Windows authentication.

The other option is to quite simply change back to mixed mode by modifying the registry in windows directly (not supported). On my machine, the registry path for a named instance (named "a") is: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.A\MSSQLServer. And the value for mixed authentication is LoginMode: 2.


DBCC CHECKDB command creating database file with same name but with space character in extension

Checkdb creates a snapshot in the background. Snapshots are supported by sparse files (they look large in Windows but are typically almost empty).

Could it be that you are looking at this file?


Select all rows of all user logins, where user had logged in from more than 3 ip address within the past month

We can actually do this in one scan of the table, by using window functions. Unfortunately, we cannot use COUNT(DISTINCT... as a window aggregate, so we must hack it with DENSE_RANK and MAX
SELECT UserID, [DateTime], IpAddress
FROM (
    SELECT UserID, [DateTime], IpAddress,
        distinctIps = MAX(rn) OVER (PARTITION BY UserID)
    FROM (
        SELECT UserID, [DateTime], IpAddress,
            rn = DENSE_RANK() OVER (PARTITION BY UserID ORDER BY IpAddress)
            -- row-number distinct ip addresses
        FROM UserLogins
        WHERE [DateTime] >= DATEADD(month, -1, GETDATE())
    ) ul
) ul
WHERE distinctIps > 3;


you can convert in case you have floationg number in two parts , integer and floating and double call your function.
in fact the result you need for 2053.234 is
select concat(nombreenlettre1(2053), 'point', select nombreenlettre1(234))

you need just to wrap your function with a substr of your first float to get two degits.


Select all customers and number of orders per month as csv

Step 1:  Get the data without the complexity:

CREATE TEMPORARY TABLE t
SELECT cus.customerId,
       cus.name,
       CONCAT(cus.LEFT(dateCompleted, 7), '-01') AS month_of,
       o.orderId
    FROM Customer AS cus
    JOIN orders AS o  ON ??? -- how do the table relate??
    ;

(Fox and try that query now.)

Step 2:  Compute the counts:

SELECT customerId, COUNT(*) AS ct
    FROM t
    WHERE dateCompleted
    GROUP BY customerId, month_of
    ;

(Try that query now.)

Step 3:  Use that as a "derived" table to build the ultimate table.

SELECT a.customerId AS custId,
       a.name,
       SUM(a.month_of >= CURDATE() - INTERVAL 1 MONTH) AS thisMo,
       SUM(a.month_of >= CURDATE() - INTERVAL 2 MONTH AND
           a.month_of  < CURDATE() - INTERVAL 2 MONTH) AS lastMo,
       SUM(a.month_of >= CURDATE() - INTERVAL 3 MONTH) AS last3mo,
       GROUP_CONCAT( ??? )  as monthsThisYear,  -- unclear where this comes from
       GROUP_CONCAT(t.ct ORDER BY month_of) AS ordersPerMonth
    FROM ( ..query 1, above.. ) AS a
    JOIN ( ..query 2, above.. ) AS cts  USING(customerId)
    GROUP BY customerId, a.name

There are several holes, but maybe this gives you some useful clues.

If you are using MySQL 8.0 or MariaDB 10.2, use WITH instead of a temp table.


Thanks to the suggestion on pgsql-general that I check TCP/IP with netcat I discovered that the first few bytes sent to localhost were being swallowed by a process that was intended to keep an audit trail of Internet access. Uninstalled and everything works as I expect.


Should I use UUIDs or integer primary keys to optimize for massive writes of relational data?

UUIDs are useful when you have clients independently generating unique identifiers.

id INT UNSIGNED AUTO_INCREMENT is smaller, faster, 'ordered', etc.

Use UUIDs only if you don't have a viable alternative.  More discussion:  http://mysql.rjweb.org/doc.php/uuid

In my opinion, sha256 is overkill for a 'digest'.

500K rows INSERTed per day?  That's 6/second?  Not a problem.  When you get to 100/sec, we should talk further.


You're not going to have a billion users tomorrow and MySQL can handle several million rows without any problem. I have 5 million users in my user table and trust me, it isn't even on my radar of things to worry about.

Don't worry about sharding until you need to do it. You are attempting to optimize prematurely for a problem that may or may not ever exist and in the process, you will severely cripple the rate at which you can innovate. Be fast to launch and find the problems as they come. You cannot predict in advance what your scaling challenges will be.

When and if you ever reach this scale, you will then have quite a bit of money and resources to throw at this kind of problem.


DateTime value has additional hour after insert

This is an application issue more than a database issue.

Navision treats datetime entries as UTC, and is adding the hour in its display (assuming your local timezone is UTC+1).

I have run into this myself since I work with Navision. If you have a client application, you can look at displayed datetime for a record and compare it to what you see by querying the table.


Join conditions and Where conditions in Left Join

Query 1 will show you All records from student with a Class value of 7, and then any related Library_Book records if they exist.

Query 2 will show you all records from student, and related Library_Book records if they exist and if the matching row in student has a Class of 7.

Query 3 will show you All records from student with a Class value of 7, and related Library_Book records if they exist.

I would expect query 1 and query 3 will give you the same results, and query 2 to give you  more rows.

Performance is impossible to talk about without knowing your table structure, distribution of data, indexes, and RDBMS.  I would not expect them to be very different though since your filters are all the same.


How to assign day of the week to different tills


How are my SQL Server constraints being bypassed?

Check constraints based on UDFs are rubbish. Concurrency, RBAR, isolation etc as you've found out. Some links:

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates by Alex Kuznetsov

Using a UDF in a check constraint to check validity of history windows (start - end date windows) by Tony Rogerson

Be careful with constraints calling UDFs by Tibor Karaszi

The safest way for SQL Server in this case would be to use standard constraints such as unique and foreign keys. I can't see why you check the folders table for a constraint on the files table though.
To prevent a file and a folder having the same name in a given parent folder only, use an indexed view. Duplicate files or duplicate folders requires table level uniqueness.
CREATE VIEW CheckUnique
WITH SCHEMABINDING
AS
SELECT fo.ParentFoldersID, fo.Name
FROM
   Folders fo
   JOIN
   File fi ON fo.ParentFoldersID  = fi.ParentFoldersID AND fo.Name = fi.Name
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_CheckUnique ON CheckUnique (ParentFoldersID, Name)
GO

Or a trigger.
But never a UDF in a check constraint


Inconsistent ResultSet from Procedure with Variables

CREATE PROCEDURE `getEmployees`( IN location INT(1),
                                 IN stafftype VARCHAR(1) )
SELECT ... /* output columns list */
... /* source tables with JOIN conditions */
WHERE COALESCE(parameter, table.field) = table.field /* replace with real names */
... /* another conditions, grouping, ordering */
;

If parameter (location or stafftype) is not NULL then the condition is equal to WHERE parameter = table.field.

If parameter value is NULL then the condition is equal to WHERE table.field = table.field which is TRUE until table.field is NULL (if such records must be returned too then use <=> operator instead of =).


Inaccurate 'Actual' row counts in parallel plan

Rows are passed across exchanges internally from producer to consumer thread in packets (hence CXPACKET - class exchange packet), rather than a row-at-a-time.  There is a certain amount of buffering inside the exchange.  Also, the call to shut down the pipeline from the consumer side of the Gather Streams has to be passed in a control packet back to the producer threads.  Scheduling and other internal considerations mean that parallel plans always have a certain 'stopping distance'.

As a consequence, you will often see this sort of row count difference where less than the whole potential rowset of a sub-tree is actually required.  In this case, the TOP brings execution to an 'early end'.

More information:

Parallel Execution Plans Suck
Understanding and Using Parallelism in SQL Server.


how to implement read-only rows or snapshots of rows

You could make them Temporal Tables, and add a ParentObject.SnapshotTime column.  Then every time you update that column, you'll get a new version of the parent object, and you can issue temporal queries on the related tables.


Log implications of copying data from one table to another

Even with Simple Recovery Model, you still have to log every part of the insert in case it fails and you need to rollback. If you are moving all of the rows (or almost all of them) as a one off process you may be better off just renaming the table and then altering the columns to be what you need.

If this is a process that runs over and over again (E.g. an end of month process) then you might want to look at partition switching.

If neither of those work, then you'll probably be better off batching your inserts. Michael Swart has a good blog on how to do that. See also Take Care When Scripting Batches.


MySQL locking tables / rows on select statement

I think that a simple update statement will work just fine:

UPDATE TableName
SET col = col + @user_input
WHERE key_col = @key ;

The update will take the necessarily lock (probably on one row only) and any other update (by other users) will either wait for the lock to be released or fail.

If you want the user to see the result of the update, you can enclose in an transaction, something like this:

START TRANSACTION;
    UPDATE table_name
    SET col = col + @user_input
    WHERE key_col = @key ;

    SET @r =
        ( SELECT col
          FROM table_name
          WHERE key_col = @key
        ) ;
COMMIT ;
SELECT @r AS col_updated ;


Are string columns with very few different entries automatically compressed?

No, SQL Server does not do anything like this automatically. You can achieve some of what you're after with data compression, but its highest granularity is at the page level. So you'll get some good compression in the index pages if you have an index on only this column (or at least with this column as the leading key column), but assuming the clustered index is not on this column, you'll get much less benefit from it when the data and other index pages contain many variations of the string.

Data compression uses multiple algorithms, including dictionary, which would have the best impact if all of the values on a page are the same. Even at its best, though, this is not going to be extremely valuable on a large table - imagine you have a million pages and each page has two copies of one of those values. Sure, compression buys you some savings by storing the value only once instead of twice on each page (plus some negligible pointer overhead), but SQL Server still stores a million copies - one per page!

What I suggest, instead of storing the same dozen strings over and over again, is to create a lookup table with a TINYINT key, allowing you to only store each description string once, no matter how many times it's used. You can always retrieve the description at query time without ever having to store it with the data, and you can even create views to make this mostly transparent to queries, applications and users. This can also be a more attractive option if your workload is CPU-bound; while data compression saves you on storage and memory, there is some CPU overhead involved in compressing and decompressing each page.

For example:

CREATE TABLE dbo.TypeInfo
(
  TypeInfoID TINYINT PRIMARY KEY,
  Description VARCHAR(64) NOT NULL UNIQUE
);

INSERT dbo.TypeInfo(TypeInfoID, Description) VALUES
  (1,'very_long_descriptor_someone_came_up_with_a_long_time_ago'),
  (2,'this_desciptor_is_also_very_long'),
  (3,'nobody_knows_why_this_descriptor_is_so_long');
-- ... 9 other values ...

Now you need to add the TypeInfoID column to the original table:

ALTER TABLE dbo.OtherTable
  ADD TypeInfoID TINYINT;

Then you can update the existing data as follows:

UPDATE o
  SET TypeInfoID = t.TypeInfoID
  FROM dbo.OtherTable AS o
  INNER JOIN dbo.TypeInfo AS t
  ON o.TypeInfo = t.Description;

(And process this in batches, if the table is large, and you want to minimize impact to the log and blocked operations.)

Then you can add a foreign key constraint:

ALTER TABLE dbo.OtherTable
  ADD CONSTRAINT fk_TypeInfo
  FOREIGN KEY (TypeInfoID) REFERENCES dbo.TypeInfo(TypeInfoID);

Once all of the data is verified to be correct, you can drop the column (first drop any constraints and indexes that reference it):

ALTER TABLE dbo.OtherTable
  DROP COLUMN TypeInfo;

Then you can create a view that your queries can use to keep the semantics the same:

CREATE VIEW dbo.vOtherTable
AS
  SELECT /* o.columns */, TypeInfo = t.Description
    FROM dbo.OtherTable AS o
    INNER JOIN dbo.TypeInfo AS t
    ON o.TypeInfoID = t.TypeInfoID;

(Or in some scenarios you could even rename the table, and give the view the name the table used to have, making it completely transparent.)

And finally, if you don't want to allow NULLs in this column, and if you are on SQL Server 2012 or above, you can alter the column to NOT NULL without incurring updates and logging:

ALTER TABLE dbo.OtherTable
  ALTER COLUMN TypeInfo TINYINT NOT NULL;

If you are on an earlier version, you might consider adding a CHECK CONSTRAINT instead:

ALTER TABLE dbo.OtherTable
  WITH NOCHECK ADD CONSTRAINT ck_TypeInfoNotNULL
  CHECK (TypeInfoID IS NOT NULL);

But this may not be ideal, as an untrusted constraint can affect the ability of the optimizer to use it... in a lot of cases it is better to just take the one-time performance hit up front.

I think much of this is way beyond the scope of your original question, and you may not even consider any of this solution compelling, but if you do, you may want to do your own research and testing to ensure the least intrusive method of making this change. You can start with the following questions here and on Stack Overflow:

Quickly change NULL column to NOT NULL
Why does ALTER COLUMN to NOT NULL cause massive log file growth?
Why does simple ALTER TABLE command take so long on table with full-text index?
https://stackoverflow.com/questions/287954/how-do-you-add-a-not-null-column-to-a-large-table-in-sql-server


How to find out the account(s) under which the sql server is running?

In SQL Server 2005 and SQL Server 2008 there was no documented way. So undocumented command xp_regread was used to get the result

DECLARE @sn NVARCHAR(128);

EXEC master.dbo.xp_regread
    'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
    'ObjectName',
    @sn OUTPUT;

SELECT @sn;

Since SQL Server 2008R2 SP1 we have a documented way to get to this information:  sys.dm_server_services here

You can use below query to find the same:

SELECT  DSS.servicename,
        DSS.startup_type_desc,
        DSS.status_desc,
        DSS.last_startup_time,
        DSS.service_account,
        DSS.is_clustered,
        DSS.cluster_nodename,
        DSS.filename,
        DSS.startup_type,
        DSS.status,
        DSS.process_id
FROM    sys.dm_server_services AS DSS;


I suspect you're suffering 'out-of-the-box' syndrome. For the whole world to know... MySQL config out of the box sucks.

Go to https://tools.percona.com/wizard and create better configuration based on the questions asked in the form. This should provide you with a better starting point. Once the output is generated, add it to your configuration file (/etc/my.cnf) and restart MySQL; review the mysql error log to ensure that your mysql instance started cleanly (beware of paths, permissions and the innodb_log_file_size size differences since that will require disposal of the old ib_log* files before new ones can be created). Once done, try your import again.


How to find all schemas where a table has records?

DBA_TABLES.NUM_ROWS column can be used to identify tables that are empty. But this column is updated by DBMS_STATS, so it is not 100% accurate.

There is no view that contains all records.


MySQL - Call a View inside a Stored Procedure

... I was told to create a stored procedure, and call the view inside it.

You don't need a View.
You don't need a Stored Procedure.
Just select the data that you want to display, based on the criteria that you have.
select id, name, status, usertype, flag
from tbl_1
where usertype = ?
order by id ;

The usertype argument will come from [the value of the selected "option" element within] the "select" element.
Depending on how your page(s) are structured, you might need an AJAX request to get the data or submitting a form containing the "select" element and processing that.


Can not find 'NT AUTHORITY\NETWORK SERVICE'. on Sql Server 2012

You can't find the NT AUTHORITY\NETWORK SERVICE because it hasn't been added. You need to explicitly add it.

Goto Security-> Logins (in SSMS) and add that user.  Then go to your database and add as a login with relevant permissions.

Better still, script it:

CREATE LOGIN

CREATE USER


Validation of views even though auto_reval is set to DEFERRED

I think the DEFERRED setting only affects CREATE OR REPLACE statements. If you want simple CREATEs to behave in this manner, set AUTO_REVAL to DEFERRED_FORCE.


Mysql master-master: single table out of sync

You could use pt-table-checksum and pt-table-sync to perform an optimized synchronisation while online and it will backoff to avoid replication lag.


Select query takes more time than it should

You asked "why this is taking too long?". You also said "Unfortunately, this took more than 5 seconds to retrieve the data and show them to me". Also, you reported the profiling output of your query.

As you can see yourself, the sum of times reported by the profiler for each steps count to 0.000154 seconds. So, from the point of view of the profiler, the query was completed in such a time (0.000154).

So why you're getting results in "...more than 5 seconds?".

You said you are filtering a 23 millions record table with a 3 char field. Unfortunately you don't tell us how many records your query is returning... but thanks to the provided EXPLAIN SELECT, it seems your query returned 336052 records.

It seems, also, that all of your activity run through some GUI (PHPMyAdmin?).

So, after all of the above, we can reformulate your original question as:

"why am I getting, within my GUI, 336.052 records shown in more than 5 seconds, if MySQL execution time for the related query is 0.000154 seconds?"

The answer, in my opinion, is quite simple: 5 seconds is the (really low, indeed) time to let 336.052 records travels along the path: MySQL engine => MySQL client libraries => PHP MySQL module => Apache => Network => your PC TCP/IP stack => Browser => DOM parser/builder/etc. => Rendered HTML page.

As for my previous experience, time required by the transmission of results is "normally" much higher than the time needed to retrieve such data. This is expecially true when libraries like PHP-MySQL or Perl-DBD-MySQL are involved: they really require lot of time to retrieve the records, after MySQL has properly identified (...and extracted) all of them.

How to solve this problem?

Again, quite easily: are you really sure that you need ALL of the 336.052 record, in a single, whole, dataset?

If your answer is really "YES! I need the all of them", than your application will handle PAGINATION and/or USER-Interaction by itself and... once it has gathered all of such data, it will probably spend lots of time interacting with the user without requiring any further MySQL interaction. In a such a case, waiting for 5 seconds (or even more) should not be a problem;
If your answer is "NO, I want to deal with a more 'human' dataset size", than you have to refine your query (at least) so that it will give back to you a more "human" dataset (tens or, hundreds, at most, records). In such a case, I bet that you'll get your result in a shorter time.

BTW: this is exactly the same issue you experienced in this other post, at ServerFault: 88 seconds to let 132M records travels along the.... not-mysql-strictly-related magic path :-)


Get Master Server Information from Slave instance

Look in the mysql database.  You will see that some things that used to be files are now tables.  I think you are looking for slave_master_info.  It can be treated like a table for doing SELECTs.


Your tables mean something like

Student /* [StudentID] is a student AND ... */
Enrollment /* enrollment [EnrollID] is of student [StudentID] AND ... */
Payment /* payment [PaymentID] is enrollment [EnrollID]
    AND enrollment [EnrollID] is of student [StudentID]
    AND ... */

You must decide exactly what these mean and what situations can arise. Together those determines the constraints (PKs, FKs etc). You must give the meanings to users.

In the case of payments, purchases, orders, prices etc finalized transactions are usually recorded with the data from that time, even if a customer ceases to be a customer or prices changed. Does this matter to you? Is there is a FK Payment (StudentID) to Enrollment (StudentID)? I gave Payment a meaning that would lead to that FK.

The integrity problem is that your design allows Enrollment and Payment to disagree about EnrollID and StudentID even when the DBMS is enforcing the constraints. That can never arise, and the database would describe an impossible situation, so it's an error, but the DBMS doesn't know that, so it can't reject it.

That problem is gone with FK Payment (EnrollID, StudentID) to Enrollment (EnrollID, StudentID). Then the DBMS catches those errors.

But then of you update one of those tables then you have to update the other. That is called an update anomaly. (This one doesn't lead to errors when you have the 2-column FK; it's just inconvenient.)

You can see that the table meanings are simpler when you just have

Payment /* payment [PaymentID] is enrollment [EnrollID] AND ... */

If you do want to involve the previous Payment meaning in a query then you use JOIN to get the table whose meaning is the AND of the simpler meanings.

PS You don't need to declare a FK Payment (StudentID) to Student (StudentID) because it will be enforced by the combination of FKs in Payment and Enrollment.


MySQL: Why is the `length` (byte length) of a string in a `varchar` field shorter than the actual string?

The table character set is different to your client character set. So when you type 'ÂÂÂÂ' into your client, you are referring to a different string than the result of select testdata from test1; When you ran insert into test1 (testdata) VALUES('ÂÂÂÂ'); there was an implicit conversion to the latin1 charset, which only needs 1 byte per character for the string. No such conversion happens when you type length('ÂÂÂÂ') into your client.


Find all the SP's where a particular column is not updated

Try this:

SELECT DISTINCT OBJECT_NAME([object_id])
FROM sys.sql_dependencies AS d
WHERE referenced_major_id = OBJECT_ID('dbo.YourTable')
AND is_updated = 1
AND NOT EXISTS
(
  SELECT 1 FROM sys.sql_dependencies AS id
   INNER JOIN sys.columns AS c
   ON id.referenced_major_id = c.[object_id]
   AND id.referenced_minor_id = c.column_id
   WHERE id.object_id = d.object_id
   AND id.referenced_major_id = d.referenced_major_id
   AND c.name = 'bar'
);

Caveats:

You didn't mention your version of SQL Server. I'm assuming you are on 2005+.
I'm not sure how reliable the is_updated column is. I've seen major inconsistencies with the is_selected column.
This does depend on your stored procedure being created after the objects and not being subject to deferred name resolution. You may want to recompile all of your stored procedures before relying on the output.
For a lot more about how dependencies can't be relied upon, see this post.

Parsing is a real pain in the rear because all of your statements could have slight variations. You may be able to search for patterns like:

SELECT name FROM sys.procedures
  WHERE LOWER(OBJECT_DEFINITION([object_id]))
        LIKE '%update%yourtable%'
  AND LOWER(OBJECT_DEFINITION([object_id]))
      NOT LIKE N'%bar%=%';

But this has all kinds of ways to go astray - some of these patterns could appear in comments, false positives could come because you have an update to yourtable2, you could miss a row because an update references the column bar2, etc. etc.

I wrote a pretty comprehensive search procedure here, but it will only be as reliable as the search pattern you define:

A handy search procedure


Concurrent MySQL updates hang with InnoDB (on Amazon RDS)

I'm not looking for alternative ways to do the updates. Having things
like a tmp table [will] lock all the rows that are being updated until
they all finish (which could be hours), which won't work for me. They
MUST be in these awful loops.

I disagree.
The strength of an RDBMS is in performing set operations like "update all these rows plz". Given this, your intuition should tell you that these "awful loops" are not the best way to go except under very rare circumstances.
Let's take a look at your current update logic and understand what it's doing.
First off, the set autocommit=0 line in your script is unnecessary.  Because you explicitly open a transaction immediately after that with start transaction, autocommit automatically becomes disabled until you end the transaction with COMMIT or ROLLBACK.
Now for the meat of the logic: You've wrapped all these individual updates inside the loop in one big transaction. If your intention behind the iterative updates was to reduce locking and increase concurrency, the wrapped transaction defeats that intention. MySQL must maintain locks on every row it updates until the transaction commits so it can roll them all back at once if the transaction fails or is cancelled. Furthermore, instead of knowing in advance that it is about to lock this range of rows (which would enable MySQL to issue locks with the appropriate granularity) the engine is forced to issue a large number of row-level locks in rapid-fire. Given that you are updating 1 million rows, this is a massive burden on the engine.
I propose two solutions:

Turn autocommit on and remove the transaction wrapper.  MySQL will then be able to release every row lock right after it finishes updating the row. It is still forced to issue and release a massive number of locks in a short period of time, so I doubt this will be an appropriate fix for you. Furthermore, if some error occurs halfway through the loop, nothing will be rolled back since the work is not transaction-bound.

Batch your updates in a temp table. You mentioned and then dismissed this solution, but I bet it will perform best. Have you already tried it? I would first test the full million-row update.  If that takes too long then batch the work into progressively smaller chunks until you've found the sweet spot: the batches are big enough to get the total work done quickly, but no individual batch blocks other processes for too long. This is a common technique DBAs use when they have to modify a large number of rows during live operations. Remember, since your goal is to maximize your concurrency, keep autocommit on and don't wrap any of this work into a massive transaction so MySQL releases its locks as soon as possible.
Notice that as the batches become progressively smaller, this solution eventually approximates the first one. That is why I am confident this solution will perform better: When the database engine can group its work into chunks, it flies.


How database users see their objects in sql server

Sounds like you need to employ some database schemas to separate out user objects:

http://msdn.microsoft.com/en-us/library/ms190387.aspx

A database schema is nothing more than a collection of objects within a database.  DBO and SYS are common built-in schemas in SQL Server.  You can create a schema that houses a user's objects (tables, functions, stored procs . . .) and assign a level of security to that schema (owner, reader . . . ).  These help especially in terms of object-level administration.


Index Defrags during a Full Database Backup

What are the performance drawbacks when running an Index Defrag during a Full Database Backup?

Any database operation will generate extra log that the full backup needs to capture (the Full backup contains, at least, all log denerated during the backup). However, defrag is fairly small log overhead. I would not be worried.

  Is there a way to pause/stop an Index Defrag when a backup starts?

No, but Defrag is perfectly OK to stop and resume later. If you interrupt defrag  you won't loose the work so far. Restarting a defrag later will continue from where it was interrupted.

  I utilize Reorganize when frag is between 10 and 30%,

That is, most likely, useless. Defrag is largely a myth. Stop Worrying About SQL Server Fragmentation


Copy Azure SQL Database Between Azure Subscriptions

Another option is to use CREATE DATABASE, as per Richard Hauer's answer on Stackoverflow (which I executed from SQL Server Management Studio v17.5):

CREATE DATABASE db_copy AS COPY OF ozabzw7545.db_original;

Some important notes:

SQL admin account and password were the same on both servers.
Servers were in different subscriptions.
Servers were on the same Tenant, checked via Azure PowerShell console with:

(Get-AzureRmSubscription -SubscriptionName <your-source-subscription>).TenantId
(Get-AzureRmSubscription -SubscriptionName <your-destination-subscription>).TenantId

You can monitor the progress with:

SELECT state_desc, name
FROM sys.databases

Reference Links:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-azure-sql-database?view=azure-sqldw-latest#creating-a-copy-of-a-database-on-another-server
https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-move-resources
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy


Can't figure out this "trick" to optimize MySQL query

The table has a secondary index on name(let's call it idx_name for the reference). Internally MySQL stores records in secondary indexes as a pair of (key, value), where key is indexed field and value is the primary key. In this case it will be (name, id).

To execute the first query MySQL decided to use index idx_name. But in order to satisfy the query it has to take id for each value of name and go to PRIMARY index in order to get address and phone values. That's why they call it "additional lookup".

For the second query MySQL has all necessary fields in index idx_name. Remember, id is the part of the index?


The new 9.5 version has added CUBE, GROUPING SETS and ROLLUP extensions to GROUP BY which can be used for such queries. If you have to do this in previous versions, one way is using a CTE, with something like (note that it's not the same exact output as your query, since I didn't know the type of created_at. I guess it's a timestamp and didn't want to mix it with the integer count):

WITH grp AS
  ( SELECT key_id,
           sum(salary) AS salary,
           sum(bonus)  AS bonus,
           created_at,
           count(*)    AS full_count
    FROM table
    WHERE emp_id = 1
    GROUP BY key_id, created_at
  )
SELECT *
FROM grp

UNION ALL

SELECT key_id,
       sum(salary),
       sum(bonus),
       NULL,
       sum(full_count)
FROM grp
GROUP BY key_id

ORDER BY key_id, created_at NULLS LAST;


Stored Procedure result is not showing in Datagrid pane in Toad for Oracle

I think you want an output parameter of the type sys_refcursor. Currently your cursor is assigned to a local variable, which gets destroyed when the procedure terminates.

CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(
   OpNo CTS_OPERATIONS.OPERATIONNUM%Type
   ,BName CTS_OPERATIONS.BLENDNAME%Type

   ,p_recordset OUT SYS_REFCURSOR

)
AS
BEGIN
    OPEN p_recordset FOR

    SELECT ...
END;


MongoDB aggregate performance compared to Postgres

One difference to consider is the amount of IO happening. For Postgres data is normalised into tables and tables are read one page at at time. To complete the SQL it need only read however much disk is required to store all of table ppd_2015_perf.

Mongo's unit of IO, however, is a whole document.  All the denormalised/ duplicated stuff comes in along with the three fields in which you're interested.

Of course there a lot of things assumed here - RAM, compression, disk performance and data models being some.


Shrinking the SYSTEM tablespace in Oracle

Query the DBA_EXTENTS view to see what objects are present towards the end of the data files. It is possible that they are not system objects, or are system objects which can be safely modified so that they are towards the beginning of the files.

However I suspect that you have a big problem here -- you may be looking at creating a new database and moving all your user objects to that.


Why do I get an error when filtering subSelect?

You can't use a column alias on the same level where you defined it. You need to wrap the query:

select *
from (
  select masters.*, (select avg(rating)
                     from reviews
                     where master_id = masters.id) as rating
  from masters
) t
where rating > 3;

A more efficient solution is however, to do the aggregation once for all rows and then join to the result:

select m.*, r.rating
from masters m
  join (
     select master_id, avg(rating)  as rating
     from reviews
     group by master_id
     having avg(rating) > 3
  ) r on r.master_id = m.id;


240 msec isn't that bad for a complex view involving unions and subqueries. It's not great, but I've seen a lot worse. Beyond this, I'm afraid I have more questions than answers.

It's not clear from your question whether this is currently an indexed view. Is it?

It sounds like the first user to hit stale data will have to wait at least a full second for the table to be repopulated. Is that right? Does that matter?

Look at the execution plan for both the complicated view and for the join on the view that the stored proc does.  Are both execution plans reasonable?
Are existing indexes being used?
Are existing indexes the best you can do? (There are a lot of options to the CREATE INDEX statement that most people don't know about.)
Do other indexes need to be built?
Can you use an indexed view?
Did you try the Database Engine Tuning Advisor?
Calculate the cost of the existing, slower process against the programmer time needed to "fix" it. How many times per day does this procedure get executed? How long will it take you to recover the cost of "fixing" this?
What's the cost of a user getting stale data using the new "create a table from this view" process?
What's the likelihood of a user getting stale data using the new process?
What's the cost of repopulating your table for 1 second out of every 5 seconds?
How does this new table (and its log files) affect disaster recovery?
Are the underlying tables reasonable? (Statistically, most performance problems are structural problems.)  Look hard at the use of ID numbers, and at how normalization has been carried out.


I assume all "characters" in result of second query exists in result from first query:

SELECT a.character_name
     , SUM(ROUND(a.minutes_played / 60, 0))
     - SUM(COALESCE(ROUND(b.minutes_played / 60, 0), 0))
FROM outfit_member a
LEFT JOIN outfit_member b
     ON a.character_name = b.character_name
WHERE a.timestamp =  (
      SELECT MAX(c.timestamp)
      FROM outfit_member c
)
AND b.timestamp < NOW() - INTERVAL 1 WEEK
GROUP BY a.character_name;

I added a sum since I assume that each character has several rows.

EDIT: since this was an accepted answer I won't change it but suspect that it may give the wrong answer under certain conditions. Anyhow, another option is to add the conditions to the aggregate functions like:

SELECT a.character_name
     , SUM(CASE WHEN a.timestamp =  (SELECT MAX(c.timestamp)
                                     FROM outfit_member c)
           THEN ROUND(a.minutes_played / 60, 0)
           ELSE 0
           END)
     - SUM(CASE WHEN a.timestamp < NOW() - INTERVAL 1 WEEK
           THEN ROUND(a.minutes_played / 60, 0)
           ELSE 0
           END)
FROM outfit_member a
GROUP BY a.character_name;

The latter query potentially scans more rows, but avoid a join


I'm not exactly sure what you're trying to demonstrate, in the bottom you have a NOT EXISTS. That's entails either an

anti-join or
as PostgreSQL and Oracle plan it, a correlated index only scan

PostgreSQL plan,

test=# explain analyze insert into test_insert(id,value)
select 3,'111' where not exists (select 1 from test_insert where id =3);
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Insert on test_insert  (cost=8.17..8.19 rows=1 width=4) (actual time=0.190..0.190 rows=0 loops=1)
   ->  Subquery Scan on "*SELECT*"  (cost=8.17..8.19 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)
         ->  Result  (cost=8.17..8.18 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)
               One-Time Filter: (NOT $0)
               InitPlan 1 (returns $0)
                 ->  Index Only Scan using test_insert_pkey on test_insert test_insert_1  (cost=0.15..8.17 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)
                       Index Cond: (id = 3)
                       Heap Fetches: 0
 Planning time: 0.294 ms
 Execution time: 0.241 ms
(10 rows)

So of course it's slower. It's checking the index before it inserts, and then the btree-insertion for the PRIMARY KEY is checking again (as it always must).

If you want to roll that into a try-or-skip, use ON CONFLICT DO NOTHING

EXPLAIN ANALYZE
INSERT INT test_insert(id,value)
VALUES (3,111)
ON CONFLICT DO NOTHING;

---------------------------------------------------------------------------------------------------
 Insert on test_insert  (cost=0.00..0.01 rows=1 width=0) (actual time=0.034..0.034 rows=0 loops=1)
   Conflict Resolution: NOTHING
   Tuples Inserted: 0
   Conflicting Tuples: 1
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.054 ms
 Execution time: 0.065 ms
(7 rows)

Here is an example of your problem.. Sample schema,

CREATE TABLE f (id PRIMARY KEY);

Sample transactions,

TXN1                                TXN2

INSERT INTO f(id)
SELECT x FROM (VALUES (1))
  AS g(x)
WHERE pg_sleep(10) IS NOT NULL
AND NOT EXISTS (
  SELECT 1
  FROM f
  WHERE g.x = f.id
);

                                     INSERT INTO f(id) VALUES (1);

What happens in the above?

The EXISTS tests operating in the default READ COMMITTED ISOLATION level gets a snapshot. And then it holds up and goes to sleep.
The second transaction inserts a value.
The first transaction wakes up, and tests its snapshot for exists (or ran the test prior to going to sleep)
The first transaction tries to update the btree which is implementing the unique constraint.
The first transaction throws an error,

ERROR:  duplicate key value violates unique constraint "f_pkey"
DETAIL:  Key (id)=(1) already exists.

Switch the order of those transactions though and the first transaction sees that the key is there and never tries to insert into the btree.

Hopefully that helps you understand what's happening.


Getting the last data update time of a specific table in Oracle

You should change the order of MAX and SCN_TO_TIMESTAMP

SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) from mytablename;

This might still fail if it has been long time since table has been modified, but should work for active tables.


... if it doesn't exist ...

See
INSERT IGNORE ...

And
INSERT INTO .. ON DUPLICATE KEY UPDATE ...

Both require a suitable UNIQUE key to catch its "existence".


check key if exists in other table without fk constraint

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select *
from (
   values
      (1,2), (2,3), (3,4)
) as t(id_b, id_a)
where exists (select 1
              from a
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.


Run SQL Server agent job in a particular resource governor pool

Yes, your classifier function can identify the job that's running through APP_NAME(), which will actually contain a representation of the job_id. This means your jobs don't have to run as different users, but the classifier function does need to be able to identify jobs by id.

See the white paper I wrote back in 2008 for an example, in the section "Limiting Parallelism for Intensive Background Jobs." In that case I simply created a table with the mapping to the set of job_ids I cared about, and set the workload group in those cases.


What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?

For reporting purposes splitting the field out into date and time has some benefits.  Some possible benefits you could realise include:

You can make a date reference table (much the same as a date dimension in a data warehouse) with your breakdown into weeks, months etc.  This can be keyed on the date and used with a join.
Analysis by time of day is easier with a separate time field.  You can also round the time to an appropriate grain and make a reference table.
The index would be slightly narrower although each leaf row still has a (IIRC) 6 byte page reference, so it's not such a great saving overall.

For your application you might get a win from a date reference table (make a clustered PK on date for efficient lookup), which will probably be more efficient than de-normalising the week and month onto your large table.


How do you ensure accuracy of your query

You can do this pretty easily using SUM and CASE:

SELECT
    COUNT(*) as 'Total Rowcount',
    SUM(CASE WHEN LEFT(BARCODE, 1) = 0 THEN 1 ELSE 0 END) as 'Leading Zero Barcode',
    SUM(CASE WHEN LEFT(Foo, 1) = 'X' THEN 1 ELSE 0 END) as 'Leading X in Foo'
....

Then you get a number for the WHOLE table/query, and a breakdown of each category, all on the same line.  It'll be easy to see if you have a bad query since you'll get a low/high rowcount or something else.

This has the added advantage or processing more efficiently than a bunch of separate queries, since you just evaluate the whole table once.


PostgreSQL concurrency control and Serializable transactions

You don't get a serializable violation, because there is not one.
If the actual order is:

T1 begin serializable
T2 begin serializable
T1 see that account is not disabled
T2 disable account
T1 insert request
T2 commit
T1 commit

The outcome of the above is compatible with the serial order of:

T1 begin serializable
T1 see that account is not disabled
T1 insert request
T1 commit
T2 begin serializable
T2 disable account
T2 commit

Because the outcome is compatible with some serial execution, it is not a violation.  If the serial execution it is compatible with is not the one you want, that is an error in your expectations, not in the execution.
If T2 were both to disable the account, and delete all pending requests for that account, in one serializable transaction, then that would be a violation.  One of the two would get served an ERROR when it tried to commit.  But if T2 only disables the account without deleting pending request against it, your business logic allows that outcome, so that is not a violation.


MySQL - Creating a user and specifying a password


Logging procedure, package errors to OS's Syslog

Of course it can be done, the question is, do you really want this?

Scheduler jobs can raise events (put in a queue). You can capture (dequeue) those events using Advanced Queueing. For example:

Monitoring Job State with Events Raised by the Scheduler

How To Use DBMS_SCHEDULER To Publish Events For Oracle Advanced Queueing (Doc ID 871718.1)

Once you have the events and details, you need to write them to syslog. You can do this by using external procedures, for example:

write to syslog from pl/sql

Or, you could keep it nice and simple, and use the built-in e-mail notification support for Scheduler, for example:

BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  job_name   =>  'EOD_JOB',
  recipients =>  'jsmith@example.com, rjones@example.com',
  sender     =>  'do_not_reply@example.com',
  subject    =>  'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
  body       =>   '%event_type% occurred at %event_timestamp%. %error_message%',
  events     =>  'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
END;
/

Monitoring Job State with E-mail Notifications


Combining log shipping and regular log file backups

Your log backups are being done by the Log shipping process, that is after all what is there to do. So, you will generally not include log backups in your backup strategy but take into account how often your log shipping process runs.

You would only worry about taking full and/or differential backups against the databases involved in log shipping.


SQL 2017 CU10 on Windows Server 2019 - Availability Group Listener fails to obtain IP from DHCP

Just wanted to confirm that the trouble was Server 2019 - after we reinstalled to Server 2016 everything works fine.


Query External LDAP Server from SQL Server

What stands out the most in your second error message is the message below:

  The provider indicates that the user did not have the permission to perform the operation

This tells me that the user in your linked server configuraiton doesn't have access to the AD / LDAP server to perform that query. Here is my ADSI configurations along with an example query. You may need to engage your SA team to ensure the account configured in your linked server has the correct access to query AD / LDAP.

    USE [master]
GO

/****** Object:  LinkedServer [ADSI]    Script Date: 11/10/2017 3:46:31 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\accountname',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Here is an example of using the the ADSI linked server using openquery, but you need to understand the OU structure and the domain structure of AD before building the LDAP connection string.

SELECT sn, displayName, givenName, mail, telephoneNumber, ipPhone, sAMAccountName
FROM OPENQUERY(ADSI,
'SELECT sAMAccountName, displayName, telephoneNumber, ipPhone, mail, givenName, sn
FROM ''LDAP://DC=test,DC=intranet,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')
AS derivedtbl_1

NOTE: I changed the DC name in the LDAP string for security reasons, but you should be able to get the idea.


How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

I do not think that your requirements are complete as there are reasons that your prior attempts did not work that are not reflected in the stated Requirements. You mentioned in attempted solution #1 that you need to be able to debug the code / know what version it is, and the client needs to be able to replicate the code. Are these two issues current requirements?

If the need for replication is still a requirement, then that conflicts with the requirement to be "compiled (secured)".

I will assume that the client's server is something they have full sa access to such that DENYing the VIEW ANY DEFINITION permission is not a viable approach.

One thing to try is taking the T-SQL of your Stored Procedures and encapsulating each one, unchanged, into CLR Stored Procedures. You would place one or more related Stored Procedures into an Assembly.  Here are some thoughts about this:

Assemblies conveniently have a version number that can be seen in the sys.assemblies catalog view, SSMS (right-click on the Assembly to view the Properties), and the ASSEMBLYPROPERTY function.
You probably don't want to put all of the Stored Procedures into a single Assembly, but you also don't want to have one Assembly per Stored Procedure. You can have one class file per Stored Procedure and several of those included in a single Assembly.
All of the Assemblies should use WITH PERMISSION_SET = SAFE
The only code in each CLR Stored Procedure would be:

Create a SqlCommand with a CommandType of CommandType.Text and CommandText set to the current T-SQL Stored Procedure content
No SqlConnection should be needed (if it is, then it is just "context connection=true")
Map the SqlParameterCollection of the SqlCommand to the input parameters of the CLR Stored Procedure
Call SqlContext.Pipe.ExecuteAndSend(_YourSqlCommand);

The Assembly and related Stored Procedures would be loaded into each Client DB
CLR Stored Procedures are not able to be replicated, but that could probably be worked around


Try this:

select emp.*, loc.*
from employee as emp
  inner join location as loc
    on emp.locationId = loc.id
where not exists
      ( select *
        from location_flag as lf1
        where lf1.locationId = loc.id
          and lf1.flag_id = 1
          and lf1.value <> 'YES'
      )
  and not exists
      ( select *
        from location_flag as lf2
        where lf2.locationId = loc.id
          and lf2.flag_id = 2
          and lf2.value <> 'YES'
      ) ;


Does HBase support spatial functionality?

HBase is simply a non-relational database that runs on HDFS which is essentially a filesystem abstraction layer on top of Hadoop which is map-reduce framework.

  Apache HBase provides Bigtable-like capabilities on top of Hadoop and HDFS.

So the breakdown is like this,

HDFS is a distributed file system that is well suited for the storage of large files. Its documentation states that it is not, however, a general purpose file system, and does not provide fast individual record lookups in files.
HBase, on the other hand, is built on top of HDFS and provides fast record lookups (and updates) for large tables. This can sometimes be a point of conceptual confusion. HBase internally puts your data in indexed "StoreFiles" that exist on HDFS for high-speed lookups.

Moreover, HBase doesn't support data types,

  HBase supports a "bytes-in/bytes-out" interface via Put and Result, so anything that can be converted to an array of bytes can be stored as a value. Input could be strings, numbers, complex objects, or even images as long as they can rendered as bytes.

So If you want to store GIS objects you would simply ignore HBase and use the underlying Hadoop MapReduce framework with Spatial Hadoop.

HBaseSpatial is just a dead research project. There are millions of them. It's quite possible the source code was never published. The only entry on GitHub for it is here. That's from the author of the paper too, Ningyu Zhang


Things I see wrong right away

Way to many instances
Not enough vCPUs
vNUMA probably doesn't match NUMA
I'm guessing there's a ton of missing indexes
MAXDOP of 1 or 2
Cost threshold of 50
Optomize for adhoc workloads = on (it's probably off)

Questions

What do the waits look like?
What are all the memory configs setup for?

Fixing this is going to take an outage.  Probably a few of them.


What load does SQL Server Reporting Services (SSRS) have on the server?

Reporting Services  can be demanding in terms of memory and CPU, so it often makes sense to separate it from the database server element of SQL Server - obviously this isn't cheap in terms of licencing. One thing to bear in mind is that the metadata for SSRS is stored in a SQL database, but this can be on a different box. Where SSRS gets the business data from depends on where that data resides - it could be anywhere, but it might be a SQL database somewhere.

If your SSRS deployment uses subscriptions to generate reports out-of-hours, and your SQL database is used for other requirements during business hours, then it might make sense to keep them on one box.

Depending on your SQL edition, and your requirements, you can also treat the separate SSRS boxes like webservers, and have them in a high availability Network Load Balanced cluster - you can't cluster SSRS in the same way that you can with the database engine for high availability.

Like anything, try to test it all in a pre-production or Proof Of Concept environment.


Connection to PostgreSQL disconnects every hour regardless of activity

Turns out, it uses a connection pool by HikariCP. The default behavior of the maxLifetime and idleTimeout configurations are causing the reconnection. Setting both configurations to 0 disables them and removes the reconnecting issue.

Shoutout to this comment for helping me understand my problem.


It looks like your data types don't match. In order to create a foriegn key between two tables the data types must be the same. You have comment_id in the longdesc table as INT(11) and comment_id in the trace table as MEDIUMINT(9).

I hope this helps you.


Change Data Capture in a services environment

So, after a bit of thought, I realized that I can just treat my User Information as another piece of data.

So I can add to each table a "ChangedBy" and a "ChangedWhen" column.  Whenever my service updates a record, it will update the ChangedBy and ChangedWhen.   Then in the Change Data Capture history, the changes include needed data.


select/union output from two columns into three

After your clarifying comment I suggest:

SELECT hostname, r.value AS role, e.value AS env
FROM   hostname_facts r
JOIN   hostname_facts e USING (hostname)
WHERE  r.name = 'role'
AND    e.name = 'env'
ORDER  BY hostname;

It does the same as the query @zgguy already posted, just simpler.


Export to format compatible with SQL Server

There are some issues with this request:

What version and edition of SQL Server is the target system?
How much data is being imported? 10k, 10 Mb, more?
How many CSV files are there?
You have stated that handling of double-quotes is required, implying text-qualified fields and embedded text-qualifiers. Will there also be embedded field delimiters (i.e. , )? Those usually aren't a problem, but will there also be embedded row delimiters (i.e. \r\n or just \n )? These are the biggest problem for most CSV parsers.
In a comment on Scott's answer you raise a concern about: "Double quotes is one example and I expect there are other shortcomings if such a basic feature is missing - unicode, size limits, etc.". Can you please update the question to include these and any other missing requirements?
In a comment on the question, you ask, regarding SSMS: "will this create the required tables or they must be predefined?". Is creating the tables a requirement of this import process? If so, or even if creating the tables is an optional benefit, can it please be stated clearly in the question?
If the tables do not already exist, what needs to happen with regards to Foreign Keys, Indexes, Default Constraints, Check Constraints, etc?
What collation is being used, or at least is desired for the SQL Server tables? Is the plan to simply inherit the current default collation for the database that you are importing into? And to be clear about the term "collation" since it can have slightly different meaning depending on the system you are using, I am speaking of: Locale / LCID / Culture / Code Page (if applicable). It would also help to know if the source is sensitive or insensitive for case, accents, etc, or if a binary collation is being used.

  Is there a more reliable intermediary format that could be generated from Linux/OSX? For example MySQL dump, Excel, Sqlite, XML, etc?

The only truly reliable format will be something generated by SQL Server. XML is generally very reliable for transporting the data (this is what XML was meant to do), and doesn't have the parsing issues that are inherent in delimited files (i.e. embedded text qualifiers and delimiters). BUT, you still need to create the tables, and you need to write, and test the parsing of the XML into a result set so that it can be inserted into the tables. HOWEVER, (continued in the next item)...
You stated in that comment on Scott's answer:

  Problem is I won't be able to test the import because I don't have direct access to the MS SQL database, so am looking for a more reliable approach.

"Reliability" can only be determined through testing. It doesn't matter what should work, anything can go wrong. For example, many folks are not aware that it is common for XML to have an encoding of UTF-8, yet SQL Server only handles UTF-16 (Little Endian) for XML or even NVARCHAR data. Seeing as how this data is coming from "Linux/OSX", I would expect the default encoding to be UTF-8.

The encoding issue and other nuances should all (well, "mostly") reveal themselves in testing, but you have no way to test. Hence it will be difficult to get a very reliable answer regarding a reliable import mechanism.
What is the source of the data? I assume it is either Linux or OSX as those were mentioned in the question. But is it coming from MySQL specifically (since a "MySQL dump" was also mentioned)? Knowing if the source is MySQL or PostgreSQL or flat files, etc will help determine what tools are both available and best suited to this request.
Regardless of the source, however, keep in mind that the destination (i.e. Windows / SQL Server) is most "comfortable" dealing with UTF-16 (Little Endian) encoded data. So while UTF-8 might be the default output type for Linux/OSX-based systems, if there is an option for using UTF-16 Little Endian for the output encoding, that will help reduce potential issues once the script is moved over to the destination system.

One last thing to consider: You mention "MySQL dump" as a potential "reliable format", and those dumps include the DDL (i.e. CREATE TABLE) and DML (i.e. INSERT) statements. If you have the ability to write a script that contains the DDL statements (if necessary) and DML statements, then you don't have to worry about any formatting issues. Just convert the CSV data into INSERT statements and be done. The only issue you would be left with is not being able to test the scripts. But still, outside of which type of quote or brackets to use for object names and string literals, it's really hard to go wrong with:

INSERT INTO table (column1_name, column2_name, ...) VALUES (column1_val, column2_val, ...);

And to make things even easier:

Enclose table and column names in square brackets: [ and ] (e.g. [Customers]), or double-quotes: " (e.g. "Customers")
Enclose string, date, and GUID literals in regular single-quotes / apostrophes: '.  (e.g. 'some text')
If any string literals are for Unicode / NVARCHAR / XML columns, prefix those literals with a capital-"N": N  (e.g. N'some unicode text')
Make sure that any date / datetime values are formatted in a compatible way (there are several options and some depend on the current language setting in SQL Server, but generally YYYY-MM-DD HH:mm:ss.mmm should be fine)
Max size/length for CHAR / VARCHAR and BINARY / VARBINARY columns is 8000. Anything larger will need to use VARCHAR(MAX) and VARBINARY(MAX), respectively.
Max size/length for NCHAR / NVARCHAR columns (i.e. Unicode) is 4000. Anything larger will need to use NVARCHAR(MAX).

Since MySQL was mentioned in the question, even if indirectly, I figured it wouldn't hurt to test using mysqldump to see how close it can actually get. I used the following options:

--flush-logs
--compatible=mssql
--no-create-db
--no-tablespaces
--order-by-primary
--default-character-set=utf8
--flush-logs
--skip-set-charset
--hex-blob
--result-file
--skip-add-locks
--skip-triggers
--skip-comments
--skip-disable-keys
--compact
--skip-extended-insert
--skip-add-drop-table
--quote-names

Some notes:

I used utf8 as the charset since utf16le, ucs2, and a few others are not allowed in this particular context :-(
The bottom two options -- --skip-add-drop-table and --quote-names -- might not need to be explicitly stated as they might be implied by --compatible=mssql
At minimum you will need to do some string replacements on the output file to adjust the syntax for SQL Server. In the list below, pay close attention to the space before, and sometimes after, the "before" and "after" strings as they are necessary to prevent matching parts of valid strings.

" int(11) " -> " INT "
" timestamp " -> " DATETIME "
" text " -> " VARCHAR(MAX) "
" blob " -> " VARBINARY(MAX) "
" DEFAULT NULL" -> " NULL"  (no space at the end of either the "before" or "after" strings)

After making the 5 adjustments noted directly above, you are still left with a few potential issues:

Did any of the string replacements above accidentally match text within any string columns?
If there are any varchar columns with a length of 8001 or more, those lengths will need to be translated to the string MAX so that the resulting data type will be VARCHAR(MAX) instead of VARCHAR(20000).
If you have any Unicode data, especially any characters that do not fit into the Code Page of the default collation of the destination database, it will get converted into question marks ( ? ) unless you can find a way to prefix those string literals with a capital-N. Along these same lines, the CREATE TABLE statements will need to be adjusted for any Unicode columns such that they are NCHAR / NVARCHAR instead of CHAR / VARCHAR. Non-Unicode columns can remain as CHAR / VARCHAR.
potentially other nuances / differences that I have missed.

P.S. For the sake of completeness as it relates to migrating data from MySQL to SQL Server, Microsoft does have a Migration Assistant, but from what I can tell so far it requires a connection from SQL Server to MySQL and will not work on a dump file.

UPDATE

New info (from comment on this answer):

  The source is scraped data from German web pages currently stored in a CSV file. I could easily import it from there into MySQL / Postgres if that was a reliable vector.

So here's a thought. If you can get this data into one RDBMS, then why not another? Assuming you were going to set up a temporary Windows VM for testing anyway, you could just:

Install SQL Server 2008 Express Edition (i.e. free) on the VM
Create a database to hold this data (it will make life much easier if you can find out from your client what default collation their database is using and use the same one for this new database!)
Create the intended schema (tables, etc) for this data
Import this data directly into these tables
Take a BACKUP of the this database
Create another database that will act as your client's database
Write a script that will:

RESTORE the backup file into a new database with a new name (not the original name)
copy the tables / data from the new database into the "customer" database
DROP the new / data migration database

TEST!
Give the backup file and the script that makes use of it to your client.

While this does require that you write a T-SQL script, it does not require any additional software or install, and the data is already in columns of the proper datatypes and doesn't need to be parsed :-).

P.S. Again, for the sake of completeness I was also looking at SQL Server Data Tools (SSDT) as a possible solution as it can export schema and data into a single .dacpac file and import that into a destination. However, this does require installing SSDT. But, you wouldn't have to write a T-SQL script as SSDT handles all of the SQL for you.


Using PostgreSQL 8.4, how to convert bytea to text value in postgres?

For followers, since this seems to be the canonical question for "converting bytea to text" (i.e. so you can actually see it in pgAdmin etc.) . Here's how to just get it viewable (assuming your_column_name is
type bytea)
select encode(table.your_column_name, 'escape') as your_alias_name from table_name


MySQL Count(Table1.ColId) Where IN (any of Table2.Col1 ,Table2.Col2)

Look for

SELECT COUNT(t1.Id) FROM Table1 As t1
{ INNER | LEFT } JOIN Table2 AS t2 ON t1.Id IN (t2.T2Column_1,
                                                t2.T2Column_2,
                                                t2.T2Column_3,
                                                t2.T2Column_4,
                                                t2.T2Column_5);