You dont have to use SET. Instead you can do that using DTExec.exe /CONFIG parameter as below:

SQL Agent Job --> Steps --> General --> Type (Operating System(CmdExec)) ---> command

"Drive\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "D:\SSIS\SSIS_PackageName.dtsx" /DECRYPT "STRONG_PASSWORD" /CONFIGFILE "D:\SSIS\CONFIG_FILE_PATH.dtsConfig" /CHECKPOINTING OFF /REPORTING E

EDIT: Below is an excerpt from msdn

/Conf[igFile] filespec
(Optional). Specifies a configuration file to extract values from. Using this option, you can set a run-time configuration that differs from the configuration that was specified at design time for the package. You can store different configuration settings in an XML configuration file and then load the settings before package execution by using the /ConfigFile option.
You can use the /ConfigFile option to load additional configurations at run time that you did not specify at design time. However, you cannot use the /ConfigFile option to replace configured values that you also specified at design time. To understand how package configurations are applied, see SSIS Package Configurations and Behavior Changes to Integration Services Features in SQL Server 2008 R2.

You have asked on How to override SSIS 2008 package config file path?

What you are talking about is SET switch -- Overrides the configuration of a variable, property, container, log provider, Foreach enumerator, or connection within a package.

I have been dealing with SSIS dev's and I use /CONFIG when deploying it to PROD .. provided the paths are same in the package.

EDIT: 2

Agree with OP that the behaviour has changed in 2008 and up :

In SQL Server 2008 Integration Services, events occur in the following order:

The utility first applies the design-time configurations.
The utility then applies the run-time options that you specified on the command line when you started the utility.
Finally, the utility reloads and reapplies the design-time configurations.

In above scenario, you can do as below:

option 1:

DECLARE @ssis_cmd VARCHAR(4000)
DECLARE  @Packagepath VARCHAR(50)
DECLARE  @FileName VARCHAR(50)
SET @Packagepath = 'D:\SSIS\SSIS_PackageName.dtsx' -- SSIS package location
SET @FileName = 'D:\SSIS\CONFIG_FILE_PATH.dtsConfig' --Passing dynamic variable i.e 'file location' into ssis package.
SELECT @ssis_cmd = 'dtexec /F "' + @Packagepath + '"'
SELECT @ssis_cmd =
@ssis_cmd + ' /SET \Package.Configurations[Config].Properties[ConfigurationString];"' + @FileName + '"'
EXEC master..xp_cmdshell @ssis_cmd

Option 2:

As described here, you can create table SSIS_Configurations and then load them during runtime. Good example is given here.

Found a connect item here

HTH


If you really want to avoid NULLs, go with the inheritance.
Unless you want to query all posts with all replies all the time, I think joins won't be a problem if you index your tables.


The likelihood is that the H: drive is unavailable to the SQL service account as it will be local to your administrator account.

Create the backup device using the UNC path rather than the drive letter mapping. i.e. \\10.1.1.x\yedek_db.

Alternatively you could try logging in as the SQL Service account and mapping the drive letter, but this is a less optimal approach.


MySQL upgrade on Ubuntu - any heads ups?

The upgrade process depends on how you installed your mysql distribution.

If you installed the ubuntu mysql distribution you don't need to upgrade that way (but always create the backup copy of all your dbs!). You need to upgrade that way only if you installed the mysql.com mysql distribution (ex. tar.gz).

If you installed mysql packages distributed by ubuntu (you can check it running dpkg -l | grep -i mysql-server) you may just need to use the ubuntu package manager to upgrade mysql.


Check the SQL Server error log to determine the remote DAC port number.  It will be contained in the message Dedicated admin connection support was established for listening remotely on port nnnn..  Verify that port is allowed through the firewall.

You can verify TCP port connectivity with TELNET or the Powershell one-liner below, specifying the port number as the argument piped to the echo command.

nnnn | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }


PostgreSQL: how can I shorten a multi-word string to few words?

Turn the sentence into an array, then pick the first three elements and concatenate them back to a single string:

select array_to_string ((regexp_split_to_array(title, '\s+'))[1:3], ' ')
from book;

alternatively, just use split_part() three times:

select split_part(title, ' ', 1)||
       split_part(title, ' ', 2)||
       split_part(title, ' ', 3)
from book;

split_part() has the disadvantage, that you can't properly deal with multiple spaces between the words.


Using Workbench to export. Views no longer work

There's probably an error occurring somewhere in the process, because that would cause exactly what you're seeing.

Here's why:

When you define a view, the views or tables referenced by that view must already exist:

mysql> CREATE VIEW broken_view AS SELECT some_column FROM nonexistent_table;
ERROR 1146 (42S02): Table 'test.nonexistent_table' doesn't exist

For this reason, mysqldump generates a dump file that includes statements defining all of the views as equivalent(ish) tables, then goes back one by one and drops the tables and creates the views.  This avoids the problem that would occur if any view referenced another view, or referenced a table that hadn't been restored yet (tables are restored in alphabetical order).

The error could be while generating the dump file -- if any of your existing views are invalid -- for example, they reference tables or columns that don't exist, or reference DEFINER users that don't exist or don't have proper permissions, or if you don't have sufficient privileges -- their definitions can't be extracted, because SHOW CREATE VIEW only works on valid views.

If, by way of illustration, you create a view against a table, then drop the table, you'll find that you can no longer SHOW CREATE VIEW against that view.

If your views reference tables in a different schema that aren't on the destination server, you'd have a similar problem, because their definitions won't be accepted.

Being someone with a strong distaste for almost all graphical tools, I can't stand Workbench, but I believe it uses mysqldump under the hood, so this is likely to be the nature of the problem.

The export file should be human-readable.  If, at the end of the file, there are view definitions, then a problem is occurring when you're restoring the file.  If not, then you've got a problem preventing the export from finishing, and Workbench may not be showing you the error... but an incomplete export (or import) would be exactly the explanation for views being defined as tables on the destination server.


Is it suitable innodb_buffer_pool_size for MySQL Master-Slave

The buffer pool also benefits writing changes. When you change a row, it is first changed in the copy of the row's page, which resides in the buffer pool. InnoDB flushes that page back to disk later, and doesn't make you wait for the flush to finish before it calls the update complete (it only has to log the change in the InnoDB redo log to say it's safe).
The buffer pool also contains the change buffer for secondary indexes. This basically queues up updates to indexes, so when you update a table that has indexes, you don't have to wait for your updated values to be inserted into the indexes. This will also happen later in the background.
So both reads and writes benefit from having a buffer pool, and also both source and replica benefit from having a buffer pool. The larger the buffer pool, the more likely a given page may already be in RAM when you run a query either to read or write that page.
There is a point of diminishing returns on increasing the buffer pool size. If you only have a small database, say 10MB, it makes little sense to allocate many gigabytes of buffer pool.
But it's common to have a database that is larger than your RAM, so then we expect the buffer pool to be sized to hold the most frequently-accessed subset of database pages. Not all of your database is equally likely to be queried. Therefore it could be that a buffer pool 1/4 the size of your database is sufficient to make 95% of your queries run without needing to load more pages from disk. These ratios are just an example, because it depends completely on your application.


The question of whether or not this is the "Best Design" is really more of a philosophical question that needs to have more information explained. If you are looking to merely manage a simple hierarchical set of items, then this is good design.

You're definitely going to have to be very mindful of referential integrity if items are being added deleted in the various layers of the parent-child relationship.


Mariadb replication slave can only access information_schema db

Odd.

The fix seemed to be to change the password.

stop slave
change password for repl user at master.
on slave: change master to master_password = 'new pass'
start slave


Hmmm... puzzling that a 4GB operation of any sort should take a day.

I recently answered a question on binary compatibility between versions here. The relevant IMHO bit from that post is quoted below (from the documentation):

 - Export and Import (mysqldump)

You can use mysqldump to dump your tables on one machine and then
import the dump files on the other machine. Using this method,
It does not matter whether the formats differ or if your tables
contain floating-point data.

I would always advise that users, if at all possible, use plain text solutions (scripts and the like). This is in the vein of Unix philosophy and therefore, what I would strongly advise you to do is to:

Stop replication
Backup your single table as per the community wiki here, i.e. using mysqldump.
Manually edit the mysqldump script to change the engine from MyIsam to InnoDB. Check out Rolando's more detailled answer above mine - although he did say it was "short and sweet" :-). Use this command sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' your_schema_dump.sql as per here.
Reload table as per wiki.
Recommence replication (same reference as for stopping).

Et voilà!

I don't know your disk setup, but I'm willing to bet that it will take nothing like 24 hours (minutes maybe? :-) ).


How to grant access to a specific table across all databases in Mysql?

You will have to script it in a stored procedure.
here is such a script
CREATE DATABASE IF NOT EXISTS grantor;
USE grantor
DELIMITER $$
DROP PROCEDURE IF EXISTS grant_table $$
CREATE PROCEDURE grant_table
(
    tb VARCHAR(64)
   ,userhost VARCHAR(128)
   ,grantlist VARCHAR(255)
)
BEGIN
    DROP TABLE IF EXISTS DBLIST;
    CREATE TABLE DBLIST
    (
        id INT NOT NULL AUTO_INCREMENT,
        db VARCHAR(64),
        PRIMARY KEY (id)
    ) ENGINE=MEMORY;
    INSERT INTO DBLIST (db)
    SELECT table_schema FROM information_schema.tables WHERE
    table_schema NOT IN
    ('information_schema','performance_schema','mysql','sys')
    AND table_name = tb;
    SELECT MAX(id) INTO @rcount FROM DBLIST;
    SELECT @rcount;
    SET @x = 0;
    WHILE @x < @rcount DO
        SET @x = @x + 1;
        SELECT CONCAT('GRANT ',grantlist,' ON `',db,'`.',tb,' TO ',userhost)
        INTO @sql
        FROM DBLIST WHERE id = @x;
        SELECT @sql;
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$
DELIMITER ;

Here is a sample call
CREATE TABLE tryout.thistable (id INT NOT NULL PRIMARY KEY,name VARCHAR(20));
CREATE TABLE DMP492.thistable LIKE tryout.thistable;
CREATE TABLE DMP551.thistable LIKE tryout.thistable;
CREATE TABLE DMP579.thistable LIKE tryout.thistable;
CREATE TABLE DMP756.thistable LIKE tryout.thistable;
CALL grantor.grant_table('thistable','ptqd@''%''','INSERT,SELECT');

Here was a sample test I did
MySQL://localhost/root/mysqld.sock/grantor> CREATE DATABASE IF NOT EXISTS grantor;
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> USE grantor
Database changed
MySQL://localhost/root/mysqld.sock/grantor> DELIMITER $$
MySQL://localhost/root/mysqld.sock/grantor> DROP PROCEDURE IF EXISTS grant_table $$
Query OK, 0 rows affected (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> CREATE PROCEDURE grant_table
    -> (
    ->     tb VARCHAR(64)
    ->    ,userhost VARCHAR(128)
    ->    ,grantlist VARCHAR(255)
    -> )
    -> BEGIN
    ->     DROP TABLE IF EXISTS DBLIST;
    ->     CREATE TABLE DBLIST
    ->     (
    ->         id INT NOT NULL AUTO_INCREMENT,
    ->         db VARCHAR(64),
    ->         PRIMARY KEY (id)
    ->     ) ENGINE=MEMORY;
    ->     INSERT INTO DBLIST (db)
    ->     SELECT table_schema FROM information_schema.tables WHERE
    ->     table_schema NOT IN
    ->     ('information_schema','performance_schema','mysql','sys')
    ->     AND table_name = tb;
    ->     SELECT MAX(id) INTO @rcount FROM DBLIST;
    ->     SELECT @rcount;
    ->     SET @x = 0;
    ->     WHILE @x < @rcount DO
    ->         SET @x = @x + 1;
    ->         SELECT CONCAT('GRANT ',grantlist,' ON `',db,'`.',tb,' TO ',userhost)
    ->         INTO @sql
    ->         FROM DBLIST WHERE id = @x;
    ->         SELECT @sql;
    ->         PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    ->     END WHILE;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> DELIMITER ;
MySQL://localhost/root/mysqld.sock/grantor> CALL grantor.grant_table('thistable','ptqd@''%''','INSERT,SELECT');
+---------+
| @rcount |
+---------+
|       5 |
+---------+
1 row in set (0.01 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP492`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.02 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP551`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.02 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP579`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP756`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `tryout`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

MySQL://localhost/root/mysqld.sock/grantor> show grants for ptqd@'%';
+------------------------------------------------------------+
| Grants for ptqd@%                                          |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ptqd'@'%'                           |
| GRANT SELECT, INSERT ON `DMP492`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `tryout`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP756`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP551`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP579`.`thistable` TO 'ptqd'@'%' |
+------------------------------------------------------------+
6 rows in set (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor>

GIVE IT A TRY !!!


What type of database would you suggest for a large scale delivery company? RDBMS or NoSQL?

Transactional data that is highly structured is a perfect fit for relational databases.  It's not a matter of scale, it's a matter of what fits your data.  NoSQL databases are for unstructured (or highly variable) data.


How to modify maintenance plan using T-SQL script in SQL Server 2008

There are 2 parts to this question, I'll treat them one by one.

Modifying the schedule

Modifying the schedule isn't really changing the maintenance plan as much as changing the SQL Agent schedule.

Have a look at sp_update_schedule for this need.

Modifying the Maintenance plan itself

This will be hard using pure T-SQL as the maintenance plan is stored as an SSIS package. You will have to resort to CLR integration for this.

If you want to do that you would have to export the package from the SSIS package store. (see here) and then modify them through c#.

See Building Packages Programmatically and Modifying an SSIS Package through code


SQL Always On automatic failover incase of multiple secondary

Disable automatic failover for Node 3. This way it will always automatically failover between Nodes 1 & 2 only. If you encounter a scenario where one of these nodes dies and you need Node 3 to become an automatic failover node, simply change the setting.


Installing SQL Server 2012 SP2 on instance with Cumulative Update #10

Yes, this is how it works: You lose the updates that were made in any CUs not included in the service pack (sometimes it is more than one CU that is "lost" temporarily).

And no, you can't then apply a Service Pack 1 Cumulative Update to Service Pack 2: the installer simply halts and won't let you do it.

The reason it works this way is that the Service Pack code goes through a longer and more rigorous testing cycle, so needs to be "locked down" months before release, unlike Cumulative Updates which go through less rigorous testing in their - typically - 8-week cycle.

While the SP is being tested, issues continue to be discovered, fixes continue to be checked in, and Cumulative Updates continue to be released. The world doesn't stop because a Service Pack is being tested.

In no situation that I have seen has an SP cycle been halted so that it can include a fix for some issue that has been discovered in the meantime - depending on the nature and severity of the issue, they may "fast-track" a post-SP CU, or they may issue a separate on-demand hotfix. I've seen both of these scenarios happen (more below on the latter). Typically, though, the first CU after the SP was released includes all of the fixes from the last CU or two from the previous branch, kind of a "catch-up."

So, please, wait for Service Pack 2 Cumulative Update 1 (should be end of July, according to this post) before installing Service Pack 2, or be willing to lose the fixes in CU #10.

(And in either case, if you are on Enterprise and run your rebuilds online, please see this blog post about an IMHO critical hotfix addressing a data loss/corruption issue.)


SQL SERVER - GROUPING and Convert Unit of measurement

Lets say you have table:

ProductCode ProductDescription  ProductQty
1           Apple               19
2           Banana              1
1           Apple               2
1           Apple               4
2           Banana              10
3           Clementine          8
3           Clementine          1
4           Lemon               12

Query:

Select * into #temp from (
select ProductCode,
       ProductDescription,
       sum(ProductQty) / 12 as Qty,
       case when (sum(ProductQty) / 12) > 0 then 'BOX' else 'PACK' end Package
from Products
group by ProductCode, ProductDescription
union all
select ProductCode,
       ProductDescription,
       sum(ProductQty) % 12 as Qty,
       case when (sum(ProductQty) % 12) = 0 then 'No' else 'PACK' end Package
from Products
group by ProductCode, ProductDescription) as tmp

select * from #temp
where Package<>'No'
and Qty<>0
order by ProductCode

As a result I get this:

ProductCode ProductDescription  Qty Package
1           Apple               2   BOX
1           Apple               1   PACK
2           Banana              11  PACK
3           Clementine          9   PACK
4           Lemon               1   BOX

I hope this is what you are looking.


You could use a CTE to get a table with a sequence of numbers up to the highest rank in your table, and then join with that:

with nums as (
  select
    level-1 lvl
  from dual
  connect by
    level-1 < = (select max(rang) from foo)
)
select
  foo.event_date + nums.lvl
, rang
from foo
join nums
  on nums.lvl <= foo.rang
order by
  foo.event_date + nums.lvl;


Know when MySQL server was slow shutdown?

Priviet Юрий

You question puzzled me a bit first. The answer to your question was too easy and this is dangerous...

About time - was, is and will be

How can I find out if the database "was" shutdown slowly? This is quite easy: Look into your MariaDB/MySQL Error Log and there you will find a log sequence similar to the following:

  2020-03-30T08:03:36.928017Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

Ups! There are no more "shutting down ..." messages like in MySQL 5.7:

  2020-03-30T08:04:49.898254Z 0 [Note] Giving 1 client threads a chance to die gracefully
  2020-03-30T08:04:49.898266Z 0 [Note] Shutting down slave threads
  2020-03-30T08:04:51.898389Z 0 [Note] Forcefully disconnecting 1 remaining clients
  2020-03-30T08:04:51.898433Z 0 [Warning] bin/mysqld: Forcing close of thread 115  user: 'enswitch'
  2020-03-30T08:04:51.898512Z 0 [Note] Event Scheduler: Purging the queue. 0 events
  2020-03-30T08:04:51.924644Z 0 [Note] Binlog end
  2020-03-30T08:04:51.938518Z 0 [Note] Shutting down plugin 'ngram'
  ...
  2020-03-30T08:04:53.296239Z 0 [Note] Shutting down plugin 'binlog'
  2020-03-30T08:04:53.296805Z 0 [Note] bin/mysqld: Shutdown complete

So you cannot find out, when shutdown started any more and thus you cannot say how long it took. So MySQL messed it up somehow in 8.0. Too much clean-up!

If you want to get the old behaviour you can stop MySQL 8 as follows:

  SQL> SET GLOBAL log_error_verbosity = 3;
  SQL> SHUTDOWN;

or just add the variable to your MySQL configuration file (my.cnf).

Then you will find the old shutdown sequence in your error log as before:

  2020-03-30T08:13:55.071627Z 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.19).
  2020-03-30T08:13:55.178119Z 0 [Note] [MY-010067] [Server] Giving 1 client threads a chance to die gracefully
  2020-03-30T08:13:55.178210Z 0 [Note] [MY-010117] [Server] Shutting down slave threads
  ...
  2020-03-30T08:13:56.588574Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

If you want to know where your MySQL Error Log File is located you can find it like this:

  SQL> SHOW GLOBAL VARIABLES LIKE 'log_error';
  +---------------+---------------------------------------------+
  | Variable_name | Value                                       |
  +---------------+---------------------------------------------+
  | log_error     | /home/mysql/database/mysql-80/log/error.log |
  +---------------+---------------------------------------------+

Typical locations are: /var/lib/mysql/.log or /var/log/mysqld.log or /var/log/mysql/mysqld.log or similar.

Now about the "is"

When you are currently shutting down MySQL it is already to late to find it out. Because you cannot connect to the database any more to change the settings and you do not see anything in the MySQL Error log. Possibly you can look at the error log with stat and you can see when the last message was written to it to find the start of the shutdown.

  shell> stat error.log
    File: error.log
    Size: 29929           Blocks: 64         IO Block: 4096   regular file
  Device: 801h/2049d      Inode: 5373953     Links: 1
  Access: (0640/-rw-r-----)  Uid: ( 1001/   mysql)   Gid: ( 1001/   mysql)
  Access: 2020-03-30 10:13:59.491446560 +0200
  Modify: 2020-03-30 10:13:56.587467485 +0200
  Change: 2020-03-30 10:13:56.587467485 +0200
   Birth: -

Symptoms for a working shutdown is either heavy writing to disk (iostat -xk 1) or heavy swapping in (vmstat). You have to wait until finished. Some brave people use a kill -9 in such a case, if they have InnoDB only and if they know exactly what they are doing and how much time a following crash recovery will take.

And finally about the question "how long will the shutdown take" (will be)

This is not so easy to predict. It depends on several things:

How many memory blocks of your database are swapped out.
How many pages are dirty and must be written to disk.
How fast is your I/O system (IOPS).

How much Swap must be swapped in you can find here: https://fromdual.com/mariadb-and-mysql-swap-analysis

The number of dirty pages (pages modified but not written to disk yet) you can find with:

  SQL> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

And about the IOPS you have to ask your hardware spec sheet.

I hope with this answer I have covered all your possibly questions about shutting down MySQL 8.0?


SELECT same columns of Foreign table multiple time

as showing in question - no chances (You show result exactly for rows not presented in source tables), but:

SELECT
    o.id,
    o.Name,
    o.media_budget,
    u.fname AS PrimaryContact,
    usr.fname AS SecondaryContact
FROM org o
JOIN users u ON o.p_contact = u.id
JOIN users usr ON o.s_contact = usr.id

must work for You

When You use subquery, You must control - subquery must return 0 or 1 records, this is mandatory, for example like this:

SELECT
    o.id,
    o.Name,
    o.media_budget,
    (SELECT u.fname AS primaryName    FROM users u WHERE u.id = o.p_contact LIMIT 1) AS PrimaryContact,
    (SELECT usr.fname AS SecondaryName  FROM users usr WHERE usr.id = o.s_contact LIMIT 1) AS SecondaryContact
 FROM org o;


I've found the problem. It's because one of the columns in the partitioned table has a varchar(max). Limiting it to varchar(120) resolves to problem. It seems ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON requires the online index build.

I should have said I'm on 2008 R2

Thanks though.


SQL Server Service Broker Compatability across versions?

The answer is yes, you can do cross SQL Server versions service broker as long as all instances are 2005 or newer, and only one of the instances can be SQL Server Express Edition.

Here are some helpful links:
http://blogs.msdn.com/b/stuartpa/archive/2005/07/25/443229.aspx
http://msdn.microsoft.com/en-us/library/bb839483(v=sql.105).aspx


Here, I have tried and tested it and it gives the output you wanted (this was tested in Oracle):

SELECT
    CASE
        WHEN dr <> 1 THEN NULL
        ELSE demand_ind
    END demand_ind,
    batch_number,
    debit_status,
    customer
FROM (
SELECT
    demand_ind,
    batch_number,
    debit_status,
    customer,
    ROW_NUMBER() OVER(PARTITION BY demand_ind ORDER BY rowid) AS dr
FROM test2
) test3;


SSIS Get max value in column and create new column?

I took a different approach and well, mine's going to be a bit easier to maintain. ;)

I have created a Variable named SourceDate. I'm going to run a query against my Source A table and derived the maximum date in there. Due to data type quirkiness, even though I used a date datatype, SSIS 2008 freaks out and thinks I'm changing data type so I explicitly cast it to datetime

My control flow looks like the following.

SQL Derive MAX date

I return a single result set and assign into my variable ordinal position 0. This gives me the thing I need to ensure I'm only pulling current data out of table A and since it's the same for all values, it'll used after the merge join.

SELECT
    CAST(MAX(SA.CalendarDate) AS datetime) AS CalendarDate
FROM
    dbo.SourceA AS SA;

In my data flow, I'm going to use two OLE DB Sources. If this were all on the same source, I'd have you craft the appropriate query there and be done with it but this approach allows you to use two different servers and/or RDBMSes.

Source Query A

Since I like to make things complex, I assumed that Source A could have many days worth of data in there but we only wanted the most recent. Therefore, I'm going to use a parameter of ? and map that to my Variable @[User::SourceDate]

SELECT
    SA.ItemNumber AS ItemNumberA
,   SA.UnitsSold
FROM
    dbo.SourceA AS SA
WHERE
    SA.CalendarDate = ?
ORDER BY
    SA.ItemNumber ASC;

Source Query B

SELECT
    SB.ItemNumber AS ItemNumberB
,   SB.InventoryUnits
FROM
    dbo.SourceB AS SB
ORDER BY
    SB.ItemNumber ASC;

The important thing is that for a Merge Join to work, you must have sorted data. Sort in your database. It has indexes which might mean the data is already sorted for you. But, when you do that, you have to tell SSIS that this data is sorted. You do this by going to the Advanced tab after right clicking on the OLE DB Source component. You mark the Output as Sorted and then you need to identify the SortKey position. Since I'm sorting on ItemNumber I will put a value of 1 there.

In my source queries, note that I'm renaming ItemNumber. After the join, I don't know if my Left, right or Both sources had an ItemNumber.

I merge both data sets together based on the ItemNumber matching and use a type of Full Outer Join

I then add in Derived columns.

The first adds my Variable @[User::SourceDate] as a column called CalendarDate.

The second derived column helps me render a single column called ItemNumber I use an expression to pick the first non-null version of my two columns ItemNumberA and ItemNumberB

ISNULL([ItemNumberA])? [ItemNumberB] : [ItemNumberA]

My Data Flow ends up looking like

Database setup

I spun up a little database with some data to demo against. Nothing fancy here.

CREATE TABLE dbo.SourceA
(
    CalendarDate date NOT NULL
,   ItemNumber int NOT NULL
,   UnitsSold int NOT NULL
);

CREATE TABLE dbo.SourceB
(
    ItemNumber int NOT NULL
,   InventoryUnits int NOT NULL
);

-- add 3 values, 2 for today, one for yesterday
INSERT INTO
    dbo.SourceA
(
    CalendarDate
,   ItemNumber
,   UnitsSold
)
VALUES
    (CURRENT_TIMESTAMP,100,10)
,   (CURRENT_TIMESTAMP,200,20)
,   (DATEADD(d, -1, CURRENT_TIMESTAMP),100,10);

-- Add inventory. Something for items sold + something not sold
INSERT INTO dbo.SourceB
(
    ItemNumber
,   InventoryUnits
)
VALUES
    (100, 30)
,   (200, 10)
,   (300, 7);

Biml

Biml, the Business Intelligence Markup Language, describes the platform for business intelligence. Here, we're going to use it to describe the ETL. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that addresses a host of shortcomings with it. Specifically, we're going to use the ability to transform a Biml file describing ETL into an SSIS package. This has the added benefit of providing you a mechanism for being able to generate exactly the solution I'm describing versus clicking through many tedious dialogue boxes.

So, install BIDS Helper if you haven't already. Run my table create scripts. Fix the Connection String to point to wherever you created the tables and Whoosh, you have my solution. Adapt for your actual table and servers.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection ConnectionString="Provider=SQLNCLI11;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=tempdb" Name="CM_OLE" />
    </Connections>
    <Packages>
        <Package Name="dba_77666" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="DateTime" Name="SourceDate">2014-01-01</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL Derive MAX date" ResultSet="SingleRow">
                    <DirectInput>
SELECT
    CAST(MAX(SA.CalendarDate) AS datetime) AS CalendarDate
FROM
    dbo.SourceA AS SA;
                    </DirectInput>
                    <Results>
                        <Result Name="0" VariableName="User.SourceDate" />
                    </Results>
                </ExecuteSQL>
                <Dataflow Name="DFT Make Data">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC SourceA">
                            <DirectInput>
SELECT
    SA.ItemNumber AS ItemNumberA
,   SA.UnitsSold
FROM
    dbo.SourceA AS SA
WHERE
    SA.CalendarDate = ?
ORDER BY
    SA.ItemNumber ASC;

                            </DirectInput>
                            <Columns>
                                <Column SourceColumn="ItemNumberA" SortKeyPosition="1"></Column>
                            </Columns>
                            <Parameters>
                                <Parameter Name="0" VariableName="User.SourceDate" />
                            </Parameters>
                        </OleDbSource>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC SourceB">
                            <DirectInput>
SELECT
    SB.ItemNumber AS ItemNumberB
,   SB.InventoryUnits
FROM
    dbo.SourceB AS SB
ORDER BY
    SB.ItemNumber ASC;

                            </DirectInput>
                            <Columns>
                                <Column SourceColumn="ItemNumberB" SortKeyPosition="1"></Column>
                            </Columns>
                        </OleDbSource>

                        <MergeJoin JoinType="FullOuterJoin" Name="MJ Mash all">
                            <LeftInputPath OutputPathName="OLE_SRC SourceA.Output" />
                            <RightInputPath OutputPathName="OLE_SRC SourceB.Output" />
                            <JoinKeys>
                                <JoinKey LeftColumn="ItemNumberA" RightColumn="ItemNumberB"></JoinKey>
                            </JoinKeys>
                        </MergeJoin>

                        <DerivedColumns Name="DER Add Calendar Date">
                            <Columns>
                                <Column DataType="Date" Name="CalendarDate">@[User::SourceDate]</Column>
                            </Columns>
                        </DerivedColumns>

                        <!--
                        We have two ItemNumbers, One of them is populated, possibly both.
                        Create a single column that has the ItemNumber
                        -->
                        <DerivedColumns Name="DER Unified ItemNumber">
                            <Columns>
                                <Column DataType="Int32" Name="ItemNumber">ISNULL([ItemNumberA])? [ItemNumberB] : [ItemNumberA]</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                        Perform any NULL handlings here
                        -->

                        <DerivedColumns Name="DER Fix outstanding nulls">

                        </DerivedColumns>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Update

With the updated information that this will be coming from a file source, the package will change in 3 places.

The first will "SQL Derive MAX date". Instead of using an Execute SQL Task, you will need to use another Data Flow and wire the Source to an Aggregate transformation. Find the MAX date and then use the ... variable sampling thing to assign 1 row into our Variable (or use a Script Component, which is what I'd do).

If the source file that has a date could hold a mix of older and newer data, then you'd need to add a Conditional Split transformation after the data source A to filter out anything older than Max datetime derived in addendum 1 in "DFT Make Data".

Finally, you will need to add a Sort transform immediately after both of your data sources/Conditional Split in the "DFT Make Data" step.

1-2-3, solution holds true. Too lazy to update the Biml as I don't know your file definitions.

Future reader note, this does force you to reprocess one of your source files. If it's a few hundred/thousand/million rows, depending on your IO subsystem, that may not be a concern. If you have a slow IO sub, then you'll need to investigate alternate mechanism of


How to step through linked servers after failing to connect to one

You just need to add the BEGIN/END for your WHILE LOOP and move the FETCH NEXT outside of the TRY/CATCH

ALTER PROC [dbo].[spLogBackup]

AS

TRUNCATE TABLE dbo.tLogBackup

DECLARE servers_cursor CURSOR
FOR
select  distinct LinkedserverName
from dbo.Environment
join master..sysservers on srvname COLLATE DATABASE_DEFAULT  =
LinkedserverName COLLATE DATABASE_DEFAULT
where LinkedServerName not in ('TestDB')

and ServerUse in ('PROD', 'DR')
and IncludeInstats = 1
order by LinkedServerName

OPEN servers_cursor
DECLARE @Servername varchar(250)

DECLARE @sql varchar(8000)

FETCH NEXT FROM servers_cursor INTO @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN

BEGIN TRY
--Looping throught the servers.
set @sql = 'insert into dbo.tLogBackup (servername, rundate, runtime,
jobname, Status, CurrentStatus, enabled) select ''' + @ServerName +
''',last_run_date, last_run_time, name, last_run_outcome,
 current_execution_status, enabled
 from openquery([' + @ServerName + '],''exec
 msdb.dbo.sp_get_composite_job_info'' )'
 print @ServerName
exec (@sql)
--SELECT (@sql)

END TRY

BEGIN CATCH
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

FETCH NEXT FROM servers_cursor INTO @ServerName

END

CLOSE servers_cursor
DEALLOCATE servers_cursor


Improving performance when ordering by a column of a joined table

Let's start by thinking about that first query.
You're joining between Parent and Lookup, but it's an outer join, so Parents are never removed from the results. I'm going to guess that Lookup.Id is unique, so therefore, no Parent is going to have multiple Lookups that it joins to.
Therefore, the 50000th row in Parent (ordered by Parent.Id) is going to be the 50000th row in the results if we don't have the OFFSET clause.
Therefore, the query can move past the 50000 rows for the offset, look at the next 50 rows, and use this to join to the Lookup table. It doesn't matter if the join doesn't find anything, it's a left outer join and it'll just return NULL.
If you order by a different column in Parent, and that's indexed, it can move past those 50000 rows just as quickly.
Now let's consider the second query.
You want the 50000 rows that you ignore (by the offset) to be the first 50000 based on the results of the join. Those 50000 rows may include some that are NULL, where the Parent.LookupId value doesn't exist in the Lookup table. Even if you have a nice index on Parent.LookupId, you'll probably need to involve most of the rows, because unless you find 50050 rows that don't join successfully, you'll need to keep going. Even 50050 is way more than the 50 rows you join on in the first query.
Now, if you have a foreign key in place then things might be a little different. Then, the SQL engine ought to know that if it has a value at all, then Lookup.Name isn't going to be null. So it could theoretically start by finding the ones that are null, to see if there are 50000 of them. But that's still a bit of a stretch, and the SQL engine is unlikely to produce a plan like this.
But you could.
So to solve the performance of the second query, I would do a few things.
Start by considering the ones that aren't null. That means the rows which are part of an inner join. You can make an indexed view on this, so that you can have an index which is in the order you want.
But you will also need the ones where Parent.LookupID is null - except that for these ones, you don't need the join at all.
If you do a UNION ALL across these two sets (and maybe include a constant column in both, to make sure that the NULL rows appear before the NOT NULL ones in your order by), you should be able to see some improvement.
Something like this:
SELECT ID, Name
FROM
(
  SELECT i.ID, i.Name, 2 as SetNumber
  FROM dbo.MyIndexedView i
  UNION ALL
  SELECT p.ID, NULL, 1 as SetNumber
  FROM dbo.Parent p
  WHERE p.LookupID IS NULL
) u
ORDER BY u.SetNumber, u.Name
OFFSET 50000 ROWS FETCH NEXT 50 ROWS ONLY;

Hopefully your plan will include a Merge Join (Concatenation) operator, so that it only pulls the rows that it needs from an Index Scan on the indexed view (in Name order) and an Index Seek on Parent (for the LookupIDs).


Importing and exporting data from a database to another using SyBase is taking a long time

Without getting too involved in specific details on the language required to do this in Sybase SQL Anywhere 8, which is now more than 15 years old, I'd recommend something like the following:

create a table on the laptop database that contains a column which uniquely identifies rows that have already been exported to the main database.
the export function would do something like the following:

SELECT *
FROM tableName
WHERE tableName.KeyColumn NOT IN (
    SELECT KeyColumn
    FROM PreviouslyExportedTable
    )
OUTPUT TO 'C:\path' FORMAT ASCII DELIMITED BY ';';

Exporter then inserts rows into the PreviouslyExportedTable that don't already exist.  Next time the exporter runs, it will only export new rows that weren't already exported.  The insert code might look like:

INSERT INTO PreviouslyExportedTable
SELECT KeyColumn
FROM TableName
WHERE KeyColumn NOT IN (
    SELECT KeyColumn
    FROM PreviouslyExportedTable
    );

This should drastically reduce the number of rows that need to be exported and imported each time.  It is uncertain to me if SQL Anywhere 8 supports using WHERE NOT EXISTS clauses instead of WHERE NOT IN; the NOT EXISTS variant might be faster, if it works.


GRANT select on function FN_DUMP_DBLOG in SQL Server without GRANTing sysadmin

Not sure how this would be affected (if at all) by Azure SQL Database, but in general you should use Module Signing to grant higher level permissions in the most targeted, secure manner.

You would create a stored procedure or multi-statement TVF containing the SELECT ... FROM sys.fn_... command. Then, you would sign the stored procedure or TVF with an Asymmetric Key or Certificate.

If you just need to make use of the db_owner role, then you can probably get away with an Asymmetric Key, then create a User from that Asymmetric Key, and finally add that User to the db_owner role.

A Certificate works in both cases. But, if you need to make use of the sysadmin server role, then you need a Certificate. So you would create a Certificate in the DB containing the stored procedure or TVF, sign that module, then create that same Certificate in master using only the public key, then create a Login from that Certificate, and finally add that Login to the sysadmin server role.

For details, please see the following posts:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level


What are reasonable privileges to grant typical users?

A typical user should have:

SELECT, INSERT, DELETE, UPDATE, CREATE TEMPORARY TABLES, EXECUTE

The first four are pretty obvious -- though you may also set up "read only" users with only SELECT.

CREATE TEMPORARY is also handy and typically harmless: temporary tables can help in optimizing queries, breaking them into smaller & faster parts. They are limited to the executing connection, and are automatically dropped when it is closed.

EXECUTE depends on your type of system. Do you have stored routines? Would you like your users to access them? Make sure you also know about the SECURITY=DEFINER/INVOKER definition for stored routines.

At any case, make sure to apply all the above on specific schemas. Avoid using:

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'some_user'@'some_host';

as the above also grants privileges on the mysql system tables, effectively allowing any user to create new accounts or upgrade their own set of privileges.
Instead, do:

GRANT SELECT, INSERT, UPDATE, DELETE ON some_schema.* TO 'some_user'@'some_host';
GRANT SELECT, INSERT, UPDATE, DELETE ON another_schema.* TO 'some_user'@'some_host';


Best way to add a new column to a large table? (MySQL - MyISAM)

To make online changes to a table you can use pt-online-schema-change

    pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

The table will be blocked for a few seconds when it switches new and old tables. The table though must have a primary keys and not have triggers.


Can't add another node from primary node with MongoDB replication set

Answer is simple. You have not open your firewall for port 27017, is must be done on both nodes.

And asked documentation can be found here!


'Tempdb' is full warning message in SQL Server 2005

Without a suitable index, SQL Server has to perform a full sort of the data to satisfy your query. You can verify this is occurring by looking for a Sort or Top N Sort operator in the execution plan:

A suitable covering index that would avoid the need to sort in your query is:

CREATE NONCLUSTERED INDEX nc1
ON dbo.total (T_Date)
INCLUDE (T_idx, T_TAG);

When an explicit sort is needed, if SQL Server does not (or cannot) allocate sufficient memory to perform the sort entirely in memory, it will use tempdb. The allocation of sort run space in tempdb is a logged operation, which directly contributes to tempdb log space usage.

In addition, the sort starts a system transaction in tempdb to ensure sort allocations are correctly undone in case the transaction aborts.  This open transaction can prevent tempdb log space being reused until the sort completes. Executing DBCC OPENTRAN (tempdb) may show an open transaction with sort_init in the description.

You should also review the SQL Server product documentation and this article to determine how to configure your tempdb database optimally for your workload.


How many passes of a table are done with UNION ALL?

Each query in UNION ALL is executed separately, union only "merges" the result sets so if each query needs to read entire table, it will be done twice. You can check that with EXPLAIN in MySQL or similar commands in other RDBMS.

One usage for UNION (ALL) is actually optimizing queries with OR conditions, where separate queries can use indexes better than one "bigger" query.

In your case it seems you might still do it with only one query. Just quessing here from what you wrote but did you consider using

SELECT count(col) AS totalCount, count(distinct col) AS uniqueCount FROM ...

?

Or WITH ROLLUP modiffier for GROUP BY? (It is MySQL - other RDBMS will probably have something similar)


Depicting permissions to access table via E -R diagram

Entity-Relationship diagrams show entities and relationships, including the cardinality of the relationships.

Some people and tools expand this to show attributes, primary keys and foreign keys.  I've even seen some attempts at visually representing some contraints like attribute nullability and basic data domains.  Some of the classic graphical conventions, like IDEF1X also include information about the nature of the relationships (whether they are determinants of the children or not).

I'm not aware of any graphical conventions for showing access rules in an ER diagram.

I would argue that trying to pack too much information into a visual representation of the schema will make the diagram so difficult to understand that it becomes unhelpful.  I would say that this applies to something like access rules.

In order to show access rules, you really need to show the accessors (i.e. the users) since different accessors will generally have different access rules.  There is no established convention for including users in an ER diagram so I wouldn't try to do it that way.

As an alternative, I would suggest using tables or charts to indicate which users have access to which entities.  You could include a column that shows data-level rules that expand on the table-level rules.  A data level rule could be something like "users can only access sub categories which belong to categories that they own".

It won't result in a pretty picture, but I believe that understandable documentation will beat a pretty picture when it comes to trying to explain your design to a developer or customer.


Please do not just delete them in the OS.
You need to let mysqld do that for you. Here is how mysqld manages it:
The file mysql-bin.[index] keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index] are:
PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE 'datetimestamp';

These will clear all binary logs before the binlog or timestamp you just specified.
For example, if you run
PURGE BINARY LOGS TO 'mysql-bin.000223';

this will erase all binary logs before mysql-bin.000223.
If you run
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

this will erase all binary logs before midnight 3 days ago.
If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:
mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf
[mysqld]
expire_logs_days=3

and mysqld will delete them logs for you
SHOW SLAVE STATUS\G
This is critical. When you run SHOW SLAVE STATUS\G, you will see two binary logs from the Master:

Master_Log_File
Relay_Master_Log_File

When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File is, and go back to the Master and run
PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';

That way, replication is not interrupted.


Get previous field's value in MySQL trigger

You are treating Handler like an AUTO_INCREMENT.  Why not get rid of ID?  Or use it instead of having an extra column?

Edit

Since you want to "reset to 0" the number at the start of the year...  Assuming this is what you want, then converting it to a TRIGGER should work:

START TRANSACTION;  -- To keep other connections from overlapping
SELECT @year := ...,
       @next := MAX(Handler)+1
    FROM ...;
IF (YEAR(now() > @year) THEN
    SET @next := 0;
ENDIF
INSERT ... @next ...;


SQL MS ACCESS: Split row into multiple based on column value

I create number table which contain value from 1 to 10,you may populate it with more as per your requirement.

Table name tblNumber (RowNumber number)

SELECT country, rownumber
FROM tblNumber AS N INNER JOIN table3 AS t ON n.rownumber<=t.noofpages;

here table3 is your table.


store sql handle from xml to table for later use

Objects pointed to by handles are temporary.  Do not store them, however if you do store them, use a varbinary(64) datatype and realize the object they refer to may not exist in memory later on.

Instead of storing the handle, it's likely much better to pull the actual sql text or plan text, and store that.

From Microsoft Docs:

  The lifetime of sql_handles used to retrieve the SQL text [...] outlives the cached plan used to execute the query. If the query text is not available in the cache, the data cannot be retrieved using the information provided in the function result.


DB2 LUW: how to influence query planner's choice of join

Hash joins require an "equijoin predicate".  So I rewrote the query as an explicit join (instead of IN...(subselect)), and instead of using A.key = B.key as the join condition, I used A.key > B.key - 1 AND A.key < B.key +1.


microsoft sql express 2012 authentication connection issues - can't login

More information would help here and I've asked those questions in the comments. That said - a few thoughts:

Basically to access a SQL Server database you need a few things:

A login - this gets you into the server itself. This is not a database user, but a login at the instance level (In SSMS this is the top most security, not the security under a database.)
That login being mapped to a user - now there are exceptions but for the purposes here - you need that login to then be granted access to the database. This would be security found at the database level.
Permissions - you'll need permissions of some sort to do something in SQL.
SQL Server needs to have the protocol enabled that you are trying to connect with. Also if there is a firewall, the port (default is 1433 for TCP port) needs to be open.

When there are connection issues like you describe it is normally around something like not actually being on a domain but trying to use windows auth, a password mismatch, not having access to a default database selected for a login or an actual network issue like a firewall.

These are two older posts but they are fantastic resources for troubleshooting connectivity to SQL Server. This first link is from the SQL Server protocols team and describes the steps to troubleshooting connectivity issues. This second link is from the SQL Server connectivity team blog describing the various login failure messages and their states and how to troubleshoot each state.


Adding field constraints on child tables based on parent table

A CHECK constraint can only reference the table or column it's attached to. You'll have to validate the values as part of the query that's inserting into the referencing table. Something like this:

DECLARE @Cost INT
DECLARE @PersonID INT

INSERT INTO ChildTable
        ( cost,person )
SELECT @Cost
       ,@PersonID
FROM ParentTable PT
WHERE pt.id = @PersonID
      AND ((SELECT SUM(cost) FROM ChildTable CT WHERE CT.id = PT.id) + @Cost) < PT.money

You could also create a DELETE trigger that queries the other table and immediately removes the row if it fails the criteria. However that is a very sub-optimal choice.


Filtering UNION ALL result is much slower than filtering each subquery

What you're asking the DB to do in Query one is:
Give me ALL from table A FILTERED
Give me ALL from table B FILTERED
Give me ALL from table C FILTERED
Give me ALL from table D FILTERED
And then Union.

In the second query you first get all the data, and only after that you do the join and the filter. JOIN and WHERE on a UNION query, which doesn't really enable you to index anything, obviously runs slower.
(It has nothing to do with the Server variant or the OS).


BUILTIN\Administrators allowing access via linked server but not locally

This is expected. LUA strips administrators membership from your token. From Getting Started with User Account Control on Windows Vista:

  To help prevent malware silent installation and computer-wide infection, Microsoft developed the UAC feature for Windows Vista. Unlike previous versions of Windows, when an administrator logs on to a Windows Vista computer, the userâ€™s full administrator access token is split into two access tokens: a full administrator access token and a standard user access token. During the logon process, authorization and access control components that identify an administrator are removed or disabled, resulting in a standard user access token. The standard user access token is then used to launch the desktop, the Explorer.exe process. Because all applications inherit their access control data from the initial launch of the desktop, they all run as a standard user as well. Contrasting with this process, when a standard user logs on, only a standard user access token is created. This standard user access token is then used to launch the desktop.

Since your local desktop token has the administrator membership removed, it cannot log in locally to SQL Server if you expect to log in through builtin\Administrators. On remote access your token preserves the Administrator membership and you gain access.

The solution is to grant SQL Server login rights through an explicit domain group account and add all users that require access to your SQL instance to this domain group. Do not rely on Administrators membership, is a bad practice.


Here is one approach:

CREATE TABLE columns (
    id       INTEGER,
    ordinal  INTEGER);

CREATE TABLE rows (
    id       INTEGER,
    ordinal  INTEGER);

CREATE TABLE cells (
    rowid     INTEGER,
    columnid  INTEGER,
    value     TEXT);

This way, you will still need to +1/-1 the ordinals behind the new/deleted position, but fortunately you can do it all with a single statement, UPDATE rows SET ordinal = ordinal + 1 WHERE ordinal > 42. Although it updates many rows, the update statement should execute in less than a second.

Benefits:

Updating a rows table with N rows is lighter than updating a cells table with N*M rows.
Explicitly storing the ordinal means fast random access to an ordered subset of the data.


This is an administration task, and the sql standard does not define such things; so a DBMS is not required to have this. But since it is obviously very useful to be able to make a backup, most systems do have similar capabilities, although they might be implemented or named in a different way.

The corresponding function to the "simple" recovery model is a mysqldump or a pg_dump.

An equivalent to the transaction log backups which will be created in the mssql-"full" recovery model are the mysql binary log and the WAL-log for postgres. All methods will allow you to continuously backup and (more or less comfortably) recover a database to a point after the last complete backup. Many technical details, e.g. on how to recover, if the file created by the "simple" backup can be used for it, the file formats, or how or if the files used internally (e.g. after a crash) differ.

These methods also resemble each other in that they will be used (and are required) for replication purposes in all three database systems. For such a functionality, a different server needs to be continuously informed about all the changes that oocured, which is exactly what these logs contain.

For the MSSQL-"Bulk logged" recovery model, there is no direct equivalent; it is a special version of the "full" model that does not log some bulk operations.


Cross posting from StackOverflow:

I've just struggled with this for several hours, so I'm posting this for anyone else who winds up here.

Initially, all I could find were two bug reports on the pgsql-bugs mailing list:

BUG #15856: The program "postgres" was found by "initdb" but was not the same version as initdb.

BUG #15970: Db initialization error - initdb.exe and postgres not same version

The specific symptoms are:

The program "postgres" was found by ".../initdb.exe" but was not the same version as initdb.

and although the versions match, you get this:

C:\Program Files\PostgreSQL\11\bin>postgres -V
WARNING:  01000: could not determine encoding for locale
"<some encoding>.utf8": codeset is "CPutf8"

If these are your symptoms, the issue is that you have your region/language settings set to use UTF-8 (beta setting).  This causes problems with lots of programs, and PostgreSQL is one of them.  Disable this and re-install and you should be fine.


Looks like a background task; those usually run all the time, so they generate high waits but they're benign and there's not really much you can do about them (and you'll find this wait is filtered out of the popular diagnostic scripts out there).

Questions to ask yourself, when investigating a wait you can't get much information on, there's usually a reason (it's obscure because it's a nothin-burger).

Do you see an actual problem associated with this wait?
Do you see any actual sessions waiting on this (check sys.dm_exec_session_stats, but I doubt you'll find anything, because these types of waits aren't waits users wait on).

You'll always have a highest wait even if your system is completely idle, it doesn't mean the highest wait is a problem.

This post might be worth a read, but the TL;DR is that you're always going to have waits and the highest wait isn't necessarily a problem you need to solve. The analogy I use when teaching is that you're always going to have a worst employee, but you can't constantly fire your worst employee for very long.

FWIW I also see this as one of my top waits, but like SP_SERVER_DIAGNOSTICS_SLEEP, XE_TIMER_EVENT, and SOS_WORK_DISPATCHER, I'm not panicking. When I see evidence that there is a performance problem and I can tie it to this wait (or resources that are congruent), I'll dig.


Query to insert data into columns and check for pre-existing values for those columns data

SQL Server:

INSERT INTO t1 (a,b)
SELECT a, b
FROM t2
EXCEPT
SELECT a, b
FROM t1

The EXCEPT operator compares the result sets of two queries and eliminates those rows in the first result set that match in the second result set. It uses an exact match to identify and eliminate rows.

There are other options of course, such as MERGE or using NOT EXISTS in your WHERE clause, however, for a simple query such as this EXCEPT will likely perform better.


How to design a database with rare multiple values

If it was me who doing the design what I will do is create a table with name nationality each nationality have ID  ...and since the the user have multiple nationality ...in the user table I will create two columns (foreign key) for nationality nationality1 and nationality2 referencing the nationality table .

the above scenario in case for two nationality .

for multiple nationality (unlimited) check the below scenario

the scenario for this situation will be similar to Customer and orders (one-to-many) ...one customers have many orders ...you will need 3 tables user nationality user_nationality in the user_nationality table you will the following columns id,user_id,nationality_id ...the user_id is foreign key column referencing the user_id in the user table and the nationality_id is a foreign key referencing nationality_id for the nationality table .

hope that will help .


Availability Group- Redo Rate displayed on AG Dashboard vs Perfmon counter

Those two numbers are measuring slightly different things.  You're right that they are both measure redo, but they do it in different ways.
The Perfmon counter is updated in near-real-time - it's the number of bytes redone in the last second:

Amount of log records redone in the last second to catch up the database replica

The AG dashboard is based on the sys.dm_hadr_database_replica_states DMV, specifically the redo_rate column:

Average Rate at which the log records are being redone on a given secondary database, in kilobytes (KB)/second.

So the AG dashboard is based on an average, but over what period?  I suspect it's "the last active period" based on the phrasing in the log_send_rate description from the same DMV:

Average rate at which primary replica instance sent data during last active period, in kilobytes (KB)/second.

Let's try and see.  I'll open up a lab AG dashboard, and first thing I notice is that the redo rate is not zero, despite the fact that I haven't used this thing in a couple weeks:

Perfmon is flat on the secondary, as I'm not doing anything yet:

Now I'll insert some data into my test database:
INSERT INTO dbo.A
SELECT TOP (1000)
    REPLICATE(N'A', 50)
FROM master.dbo.spt_values;

Now Perfmon on the secondary shows a brief blip:

And if I open up the AG dashboard, I can see the redo rate changed (from 3535 to 3873), but it didn't drop back down to zero:

So it looks like this DMV (and the dashboard) is only updated when redo is actually happening, and it holds the last value that it calculated.


MySQL: Delete a record from all tables in one shot

The automated way of doing so is by defining foreign keys on your tables, with a DELETE CASCADE in your case. This way, a DELETE on the master table will propagate and delete matching rows from all derived tables.

You will have to use InnoDB tables for that.

I assume your tables do not have foreign keys at the moment, otherwise you wouldn't ask. In which case you may try using triggers, see this post for an example. Triggers are bad for performance, mind you.

Those who do not define foreign keys tend to work out all DELETEs in application code.


UPDATE t2
  SET flag = 1, flagdate = CURRENT_TIMESTAMP
  FROM dbo.table2 AS t2
  INNER JOIN dbo.table1 AS t1
  ON t2.id = t1.id
  WHERE t1.amount > 10000
    AND (t2.flag IS NULL OR t2.flag = 0);

You can avoid the OR condition if you change the flag column to NOT NULL with default 0.


Confused about maximum connections in PostgreSQL

In a web application you use a connection pool that establishes a fixed amount of database connections that are then shared between all sessions in your web applications.

Even when a web application has thousands of concurrent users, it might only need as much as 50 database connections (if the connection pooling is done right).

  Meaning, if a user loads a page and it queries the database, does it open and close a connection before and after?

No, you shouldn't do that. Opening and closing connections is a relatively expensive operation. The connection pool will typically keep the connections open - at least for a defined amount of time. Your application takes connections from the pool and returns them to the pool.


Converting a graph database schema to a relational database schema

Starting at the end - if you have, say, 20 large tables of 10 millions rows each at 200 bytes per row that works out at just under 40GB.  Add twice as much for indexes and your entire database will fit into memory.  With sensibile indexes in place you really shouldn't worry too much about joining multiple tables.  Joins are what relational databases do, it's what their code is optimised for.  Don't be frightend of using your new software for its intended purpose.

Have you considered having different views for each specific use-case?  These will be easier to write and maintain.  The optimiser will likely do a better job, too, if it has fewer objects to consider in each view.

If you want an alternative, however, you could try modeling your data in a star schema, like a data warehouse. Task will take on the "fact" role and the others taking on the "dimension" roles.  You'll still have multiple joins but the opportunity for successive calculations on statistics producing silly answers is much reduced.

I would not try to shoehorn all the task-related values into one generic mega-table.  Think what the SQL would look like if you implemented task_relationship.  Does that really look better than joins on normalised tables?


PostgreSQL: How to restrict resources per DB, Schema or User

You can set many parameters on the database or user level with

ALTER DATABASE ... SET ...;
ALTER ROLE ... SET ...;

Connection limits can also be set on that level.

But a lot of things are global to the cluster (like shared_buffers), and a lot of resources (like CPU and I/O) cannot be limited at all.

I second the horse's comment to look at pg_cgroups, but I should mention that I am the author. That doesn't work on a database or user level though, but if you want to separate databases, it is always commendable to put them in different clusters anyway.


I lost my database in SQL server 2008 R2

You can try attaching the missing databases using the files. You can right click on the databases folder in Management Studio and select "Attach".

Another option, and the one I would generally go for, would be to use any backups you have. You will experience some data loss though.  If you are using the full recovery model for your databases, you should be able to restore to the last transaction log backup. Otherwise if you are using the simple recovery model you can only restore from the latest full backup.


Reporting services scheduled reports are clogging up sql job history log

Write your own job that clears out the entries (and its own!) every couple of minutes.

You do have the ability to clear out history details by simply issuing:

DELETE msdb.dbo.sysjobhistory WHERE run_status = 1 AND job_id = 'guid';


How to best handle the situation where my "table creation code" and my actual live tables have become desynced?

There are times where someone wants to write code and not specify each individual column. If I write a script to split out one customers data to another database, not specifying the column names means that I don't need to change the script every time I add a new column. As long as the column order is constant. If there is one table that you want to change the column order, you can drop the constraints and indexes, rename the table, create the table again without the indexes and constraints, insert the data by specifying column names and create the indexes and constraints. It is a lot of work and requires downtime. But it would allow you to change the column order. Once you do that, you should update the tables using a tool like Flyway or ant where you can run code against each database as a release to make sure that the changes happen in the same way.


Powershell Invoke-Sqlcmd Replacing £ with?

I managed to test that the text file may have been the issue. When I opened up the text file and changed the encoding to Unicode, the £ and the € were inserted into the databsase.


You can use ALTER VIEW in conjunction with the information schema. You mentioned dumping it out to a text file, so perhaps something like this:

SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW `",table_name,"` AS ", view_definition,";")
FROM information_schema.views WHERE table_schema='databasename'

Mix this with the mysql command line (assuming *nix, not familiar with windows):

> echo "*abovequery*" | mysql -uuser -p > alterView.sql
> mysql -uuser -p databasename < alterView.sql

Sidenote: You can't alter the information_schema entries directly.
Note2: This works for only one database at a time, if you leave off WHERE table_schema you need to insert USE commands between each.


Override/kill when Code 3/SQLSTATE=57016 Occurs

Your LOAD was interrupted so the table was placed into a LOAD PENDING state. To take it out of the LOAD PENDING state you will need to issue one of the following:

LOAD TERMINATE to roll back the operation
LOAD REPLACE to reload the entire table
LOAD RESTART

Also, do not take a backup before you take the table out of LOAD PENDING state.

See Restarting an interrupted load operation in Information Center for details.


What will happen if the primary shard for a mongodb database is down?

I guess you mean primary shard is down, because the term primary database is not applicable. The behavior will be the same as losing any other shard. The difference is that both sharded and unsharded collections will get affected.


How do I build index for MySQL table that inserts a lot and queries only most recent data

PRIMARY KEY (OrgID, MachID, Date) would be good for that query.  But, a PRIMARY KEY is necessarily "unique"; is that combination unique?  If not unique, then make it a plain INDEX and have something else as the PRIMARY KEY.

If we are talking about millions of rows per day, then Summary tables would be worth doing.

If it is usually "yesterday", then consider:

AND Date >= CURDATE() - INTERVAL 1 DAY
AND Date  < CURDATE()

Or, more generically, (where you insert the days ago for '?'):

AND Date >= CURDATE() - INTERVAL ?   DAY
AND Date  < CURDATE() - INTERVAL ?-1 DAY

More

Insuring sequential -- not that important.  With that 3-part PK, you will be inserting sequentially in several spots, namely each combination of OrgID and MachID.

Insert do need to update secondary keys, but this is usually not a big deal.  (To get really technical, see "InnoDB's change buffering".)

If you are going to be inserting more than 100 rows per second, then I recommend gathering them up and batch inserting.  Also, see innodb_flush_log_at_trx_commit = 2.


At what point is SQL Server's is_policy_checked enforced?

The CHECK_POLICY option is used to enforce Windows Password Policy restrictions and mechanisms for SQL Server logins. This applies when a login is created or when a password is changed for a SQL Server login as it affects the validity of a chosen password based on the setting state and the Windows password policy. It does not perform any validation or checks when a user logs on.

If you're trying to force passwords to be validated after enabling this option on existing logins, you should also enable the MUST_CHANGE option to force users to change their password on next login as the new passwords would then have to comply with the new policy.


SQL extended event captured only the estimated execution plan but no actual execution plan

I imagine that you aren't seeing the "post execution" event because the client canceled the execution.


How to count max date row if current status column is 0 or 2?


Tuning a query to fetch all the records in a day with certain status

B-tree Index on Status won't help - it has 3 distinct values, and full table scan will be much faster. Bitmap index should work better in such case, but TRANSACTION table name implies lots of DML , so it shouldn't be used.

Some improvement may be gained by partitioning transaction table by status, and then create local index on created_time. In a sense it's similar to (3), but don't require application changes, and moving rows to proper partition is done by the engine itself. However, it is quite an expensive option, and should be used for really huge databases .

In my opinion, index on created_time will work just fine for most of the cases.


How to constrain many-to-many to mark a single record as a "default"?

You could use a filtered unique index.

create unique nonclustered index ixf_customer_template_default
  on dbo.customer_template(customerId,is_default)
    where is_default = 1;

That will allow only one default and any number of non default rows per customer.

rextester demo: http://rextester.com/HLQB10886

create table customer_template(
    id int identity(1,1)
  , customerId int
  , templateId int
  , is_default bit
);

create unique nonclustered index ixf_customer_template_default
  on dbo.customer_template(customerId,is_default)
    where is_default = 1;

insert into customer_template values
 (1,1,0),(1,2,0),(1,3,1);

/* error when adding another default */

insert into customer_template values
 (1,4,1)

  Cannot insert duplicate key row in object 'dbo.customer_template' with unique index 'ixf_customer_template_default'. The duplicate key value is (1, 1).
  The statement has been terminated.


How can I get a value of a column to 2 decimal points?

You can use the ROUND() function

SELECT
CONCAT("£",ROUND(SUM(cost),2)) AS EndOfYearForecast,
CONCAT("£",ROUND(SUM(cost/12),2)) AS MonthlyAverage,
CONCAT("£",ROUND(SUM(cost/53),2)) AS WeeklyAverage
FROM yearlyAnalysis;

You can also use the FORMAT() function if you want commas in the display

SELECT
CONCAT("£",FORMAT(SUM(cost),2)) AS EndOfYearForecast,
CONCAT("£",FORMAT(SUM(cost/12),2)) AS MonthlyAverage,
CONCAT("£",FORMAT(SUM(cost/53),2)) AS WeeklyAverage
FROM yearlyAnalysis;

EXAMPLE

mysql> SET @NUM=1234.56789; SELECT ROUND(@NUM,2) RoundNum,FORMAT(@NUM,2) FormatNum;
Query OK, 0 rows affected (0.00 sec)

+----------+-----------+
| RoundNum | FormatNum |
+----------+-----------+
|  1234.57 | 1,234.57  |
+----------+-----------+
1 row in set (0.00 sec)

mysql>


Reducing I/O operations for often-updating data in MariaDB

There are two sources of write I/O when updating table data: transaction log flushes and checkpoints (dirty buffer pool page flushes). You cannot do much about the former, as it happens every time you commit, apart from placing your log onto faster storage.

Checkpoint activity can be somewhat configured (for InnoDB) by adjusting innodb_max_dirty_pages_pct, innodb_adaptive_flushing*, innodb_adaptive_checkpoint, and innodb_io_capacity parameters, among others -- there are some more details in this blog post. You'll need to monitor checkpoint activity to figure out what changes make sense in your situation.


MySql - set my.cnf location at server start

mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status.

These links can be helpful to you
Running multiple instances on the same host
http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html


Several Foreign Key and Cascade Delete SQL Server

You can create a trigger to update the UserID columns in related tables, such as:

USE Tempdb;

CREATE TABLE dbo.Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , ApplicationID INT NOT NULL
    , Country VARCHAR(255) NOT NULL
    , DefaultTenantID INT NOT NULL
);
GO
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL
    , TenantID INT NOT NULL
    , CreatedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_CreatedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , ModifiedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_ModifiedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , AssignedTo_UserID INT NULL
        CONSTRAINT FK_Tasks_AssignedTo
        FOREIGN KEY REFERENCES dbo.Users(UserID)
);
GO
INSERT INTO dbo.Users (UserID, ApplicationID, Country, DefaultTenantID)
VALUES (1, 1, 'CA', 1);

INSERT INTO dbo.Tasks (TaskID, TenantID, CreatedBy_UserID, ModifiedBy_UserID, AssignedTo_UserID)
VALUES (1, 1, 1, NULL, NULL);
GO

CREATE TRIGGER TG_Users_Trigger
ON dbo.Users
INSTEAD OF DELETE
AS
BEGIN
    UPDATE dbo.Tasks
    SET CreatedBy_UserID = NULL
    WHERE EXISTS (
        SELECT 1
        FROM deleted d
        WHERE d.UserID = CreatedBy_UserID
        );
    UPDATE dbo.Tasks
    SET ModifiedBy_UserID = NULL
    WHERE EXISTS (
        SELECT 1
        FROM deleted d
        WHERE d.UserID = ModifiedBy_UserID
        );
    UPDATE dbo.Tasks
    SET AssignedTo_UserID = NULL
    WHERE EXISTS (
        SELECT 1
        FROM deleted d
        WHERE d.UserID = AssignedTo_UserID
        );
    DELETE
    FROM dbo.Users
    WHERE EXISTS (
        SELECT 1
        FROM deleted d
        WHERE d.UserID = UserID
        );
END
GO

DELETE
FROM dbo.Users;

SELECT *
FROM dbo.Users;
SELECT *
FROM dbo.Tasks;

/* - cleanup removed for safety
DROP TRIGGER TG_Users_Trigger;
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Users;
*/

However, instead of deleting rows from the Users table, I would recommend updating a bit column to indicate the User has been deleted.  This allows a history of users, along with the actions taken by those users in the Tasks table.  For instance:

CREATE TABLE dbo.Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , ApplicationID INT NOT NULL
    , Country VARCHAR(255) NOT NULL
    , DefaultTenantID INT NOT NULL
    , IsDeleted BIT NOT NULL /* Default this to false */
        CONSTRAINT DF_Users_IsDeleted DEFAULT ((0))
);

CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL
    , TenantID INT NOT NULL
    , CreatedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_CreatedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , ModifiedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_ModifiedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , AssignedTo_UserID INT NULL
        CONSTRAINT FK_Tasks_AssignedTo
        FOREIGN KEY REFERENCES dbo.Users(UserID)
);

/* Note we are not specifying the state of the IsDeleted column here
    since it defaults to zero, which indicates they are active
*/
INSERT INTO dbo.Users (UserID, ApplicationID, Country, DefaultTenantID)
VALUES (1, 1, 'CA', 1);

INSERT INTO dbo.Tasks (TaskID, TenantID, CreatedBy_UserID, ModifiedBy_UserID, AssignedTo_UserID)
VALUES (1, 1, 1, NULL, NULL);

/*  This is how we "delete" a user */
UPDATE dbo.Users
SET IsDeleted = 1
WHERE UserID = 1;

/* Any queries that access the Tasks table now need to be cognizant
    of the state of the user (deleted or not)
*/
SELECT T.*
FROM dbo.Tasks T
    INNER JOIN dbo.Users U ON T.CreatedBy_UserID = U.UserID
WHERE U.IsDeleted = 0;


MySQL Query running slow when using REPLACE function instead of CASE statement

Instead of trying to parse a string column, use the database's built-in relational capabilities as they were intended to be used.
First, we need three tables, Users, Roles, and UserRoles.  UserRoles is a cross reference table that joins Users to their Roles.
CREATE TABLE Users
(
    UserID int NOT NULL primary key
    , UserName varchar(100) NOT NULL
);

CREATE TABLE Roles
(
    RoleID int NOT NULL primary key
    , RoleName varchar(50) NOT NULL
);

CREATE TABLE UserRoles
(
    UserID int NOT NULL
    , RoleID int NOT NULL
    , FOREIGN KEY UserRolesUserIDFK (UserID) REFERENCES Users (UserID)
    , FOREIGN KEY UserRolesRoleIDFK (RoleID) REFERENCES Roles (RoleID)
    , PRIMARY KEY (UserID, RoleID)
);

In the UserRoles table above, you'll note we have two foreign keys, one each for the User entry and the associated Role entry.  This provides referential integrity that prevents deletion of a role that is in use by a user, and adding a user to a non-existent role.  I've also added a compound primary key to the table, on (UserID, RoleID), which means you cannot add a user to a single role more than once.  This eliminates the need to DISTINCT the output queries below, which for a system with a large number of Users and Roles would make a significant positive difference to performance.
Next, we'll insert some sample data:
INSERT INTO Users (UserID, UserName)
VALUES (1, 'Sarah')
     , (2, 'Hannah');

INSERT INTO Roles (RoleID, RoleName)
VALUES (1, 'Administrator')
     , (2, 'User');

INSERT INTO UserRoles (UserID, RoleID)
VALUES (1, 2)
     , (2, 1)
     , (2, 2);

Now, when we want to get the results, we do this simple query:
SELECT u.UserName
    , r.RoleName
FROM Users u
    INNER JOIN UserRoles ur ON u.UserID = ur.UserID
    INNER JOIN Roles r ON ur.RoleID = r.RoleID
ORDER BY u.UserName;

Results look like:
╔══════════╦═══════════════╗
║ UserName ║   RoleName    ║
╠══════════╬═══════════════╣
║ Hannah   ║ Administrator ║
║ Hannah   ║ User          ║
║ Sarah    ║ User          ║
╚══════════╩═══════════════╝
If you need the role membership on a single line, along with the count of the number of roles each user is a member of, then the following should work:
SELECT u.UserName
    , (
        SELECT GROUP_CONCAT(r.RoleName SEPARATOR ', ')
        FROM Roles r
            INNER JOIN UserRoles ur ON r.RoleID = ur.RoleID
        WHERE ur.UserID = u.UserID) AS Roles
    , (
        SELECT COUNT(1)
        FROM UserRoles ur
        WHERE ur.UserID = u.UserID) AS CountOfRoles
FROM Users u
ORDER BY u.UserName;

Output looks like:
╔═══════════╦══════════════════════╦══════════════╗
║ UserName  ║        Roles         ║ CountOfRoles ║
╠═══════════╬══════════════════════╬══════════════╣
║ Hannah    ║ Administrator, User  ║            2 ║
║ Sarah     ║ User                 ║            1 ║
╚═══════════╩══════════════════════╩══════════════╝
The queries above can efficiently use indexes without any headaches, and they easier to comprehend than a a large number of REPLACE or CASE statements.
Multiple REPLACE or CASE statements, where not necessary, only introduce complexity that may mask the real intention, and confuse the query optimizer, producing poor plans and possibly slow results.
A significant benefit of using discrete rows for each user/role relationship is you can have an unlimited number of roles per user, without needing an unlimited size string column to hold the membership details.  This also makes it super easy to determine if someone is a member of a specific role, or to see all the members of specific roles.
For example, to see who's a member of the User role, you could do this:
SELECT u.UserName
FROM Users u
    INNER JOIN UserRoles ur ON u.UserID = ur.UserID
    INNER JOIN Roles r ON ur.RoleID = r.RoleID
WHERE r.RoleName = 'User'
ORDER BY u.UserName;

Results:
╔══════════╗
║ UserName ║
╠══════════╣
║ Hannah   ║
║ Sarah    ║
╚══════════╝
This design also makes it easy to see who is not a member of a specific role:
SELECT u.UserName
FROM Users u
WHERE NOT EXISTS (
    SELECT 1
    FROM UserRoles ur
        INNER JOIN Roles r ON ur.RoleID = r.RoleID
    WHERE r.RoleName = 'Administrator'
        AND ur.UserID = u.UserID
    );

Results:
╔══════════╗
║ UserName ║
╠══════════╣
║ Sarah    ║
╚══════════╝
I've created a fiddle for the above code here.


We have quite some mixed servers, using encryption, depending upon the need of business. For very critical servers, we decided to upgrade to Enterprise edition as it not only provides TDE but other benefits as well when it comes to performance or troubleshooting.

Yes, TDE is quite effective and very good, but since it comes with a cost we decided for medium and low priority businesses to use a third party tool like NetLib.

I would like to highlight a few of its benefits, per our usage:

Provides Transparent Data Encryption and Column Encryption for all
versions of SQL Server from 2000 through 2014, and for all editions of
SQL Server from Express through Enterprise. SQL Server's TDE is
available only in the Enterprise edition of SQL Server 2008 and
later.
Easy to set up and maintain. Took us hardly 5-6 minutes to do so,
once we were aware of what needs to be done.
Database keys are stored outside of SQL Server, including alternate
locations such as network, removable media, etc.
Encryptionizer's Transparent Data Encryption has virtually no impact
on database performance (<1%) on a properly sized server. Some
benchmark reports show SQL Server TDE to have greater impact on
performance between 5-10%.
Support for FILESTREAMS (SQL Server 2008 and SQL Server 2014).
Supports SQL Server Compressed Backups (WITH COMPRESSION).
Possible to encrypt system databases including the master database and the tempdb database.

I have not heard much about DbDefence, but yes I believe it supports Replication, Log Shipping and Mirroring. Read here for more info.

I believe you can have a trial of the above products and decide as per the business needs which suits your environment the best.


Yes, all the storage that you can attach to a single Azure VM is 16TB so that's the practical limit on storage for a single server.

Now that said, there isn't a problem that can't be worked around.

If you needed say a 100TB database, you could spin up 7 VMs, attach 16TB of storage to each one.  Then spin up an 8th VM which has SQL Server on it, then create the database using SMB network shares on the 7 other machines storing the database on the 7 remote machines.


External compression tools for database backup files

Since the backup is just a file, you can use whatever file tools there exists, including any and all compression tools.

In order to restore, you will ofcourse need to uncompress the file first.

A far better solution is to use any built-in compression of the backups from whatever sql engine you use. That saves both IO, space and time.


I do not believe it is possible to use the LIKE wildcard in an IN statement.  To achieve what you want to do, you need to take out all your LIKE wildcards and put them in a separate OR clause.

WHERE string_col IN ('string1', 'string2')
   OR string_col LIKE '%string3%'
   OR string_col LIKE '%string4%'

But this seems like a REALLY bad idea ... I think you should update your post to answer Martin's question (in the comment) to get a better solution for what you want to do.  (or ask a different question)


Streaming replication and lag when performing VACUUM on master

Nick, based on the information we've gathered, I'm going to venture a guess, but if I'm wrong, come back and let us know and there may still be something we can do. I wouldn't say I'm confident about the internals of auto-vacuum processes on hot standby servers.

Streaming Replication, aka Log-Shipping

Streaming replication, hot standby, log shipping...there's a lot of different names that people use, but the general idea is that you replicate your back up server by implementing a system where the transaction logs from your primary server are sent and re-implmented on a secondary server to keep the two identical.

Now, you've stated that when you VACUUM your table on the primary, you have significant lag on your standby, and also that your configuration for log_autovacuum_min_duration is set to -1, which disables logging of autovacuum actions.  As a potential remedy, you could set this value to 0, indicating you wish to log all autovacuum operations.

My guess is that, while autovacuuming is occurring periodically on your master, since it isn't being logged, it isn't replicated on the standby.  When you run a manual VACUUM, only then does the logged VACUUM become replicated, which may be what's eating into your performance on the standby.

How are things if you run a VACUUM on the master, followed immediately by another VACUUM on master?  This will give us some indication of if I'm right or if I'm full of hot air.


Make a query fail in SSMS for higher versions of SQL Server?

Besides doing your research on what features you should deliberately not use, the only reasonable solution here is to have a robust testing structure where you run your application against all possible versions of SQL server that you're intending to be compatible with.

Even for queries that might work on multiple SQL versions, they might behave slightly differently.

99 times out of 100, you're unlikely to see new versions of SQL break existing code, but there is a difference between "(shrug) it'll probably work" vs "my application is fully tested and supported on the following SQL versions:..." If you're throwing a helpful script on your blog, the former is probably ok. But if you're selling and supporting a commercial application, you definitely want the latter.


SP2-0584: EXIT variable was non-numeric error

It should be a simple boolean.
This should work and be more efficient.
The SQL%ROWCOUNT attribute will be populated with the number of rows deleted. If you didn't delete any then just exit.

FOR i IN 1..100
LOOP
  DELETE
  FROM mytable
  WHERE date <= (SYSDATE - 30)
  AND ROWNUM < 5000;

  EXIT WHEN sql%rowcount = 0;

  COMMIT;

END LOOP;

If you aren't familiar with the implicit cursor attributes take a look at this in the Oracle docs - https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/sql_cursor.htm

EDIT: Just noticed the title of the post. The exit that the message is referring to is the exit from SQL*Plus and as such I'm not sure you are running this the right way. Please post all the code and tell us how this is being run.


I couldn't compile this little query (needs 2 tables)(mysql)

select f.friendid
from friends f
  inner join user u
    on u.userid = f.userid
where u.country='usa'


Crazy automatic float rounding in MySQL 5.7

This is not just a SQL thing: floating-point numbers should always be considered approximations in any language. See https://floating-point-gui.de/ amongst other references.

You can use the DOUBLE type instead which would help, as that can represent integers up to 53 bits in length accurately, but you would have problems later with bigger numbers.

Unless you absolutely must have floating-point numbers, these types should be avoided. Use integer (int, bigint, ...) or fixed-point types (decimal, numeric) instead. As you don't say what you are using these numbers to represent we can't make a more specific recommendation.


Export a subset of data, from mysql production database, to make a test database

I doubt that there's a cookie-cutter approach, but the way I did it in the past went roughly as follows:

Pick some critical tables that must be populated for proper testing, preferably which have many dependencies, e.g. OrderDetail.
Create a new database with the same schema except no foreign key enforcement.
Randomly pick some rows from the source database, e.g. top 50 ordered by something pseudo-random, and insert into the new database.
Get all rows that they depend on and insert into the new database.
Repeat until no more rows are inserted.
Turn foreign key enforcement on.

It's slow and has many manual steps, but it worked for me.


your application must use port 5005 for each query known to be read-only (not only SELECT, because some SELECT can call custom functions)


Storing date/time data in a character-based column is a seriously bad idea.  And, no, datetime2/timestamp values are not represented as strings in SQL Server.  See Aaron Bertrand's excellent bad-habits blog series on the topic.

DB2's timestamp data type1 is:

  a seven-part value representing a date and time by year, month, day, hour, minute, second, and microsecond, in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with nanosecond precision. Timestamps can also hold timezone information

This equates nearly perfectly to a datetime2(7) data type in SQL Server2.

  Time portion:  0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.

Unless you are measuring exceptionally accurate particle accelerator physics, you likely don't need precision beyond 1 ns.

What time source are you using?

You mention in your question you are using these high-precision timestamp values as keys.  I would investigate the appropriateness of storing those values in a numeric(32,12) column in SQL Server.

This is a sample table with that type of key column:

IF OBJECT_ID(N'dbo.TimeTesT', N'U') IS NOT NULL
DROP TABLE dbo.TimeTest;
CREATE TABLE dbo.TimeTest
(
    ItemKey numeric(32,12) NOT NULL
        CONSTRAINT PK_TimeTest
        PRIMARY KEY CLUSTERED
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

Here, I insert a very large and precise value into the column:

INSERT INTO dbo.TimeTest (ItemKey)
VALUES (99999999999999999999.123456789012);

What that looks like:

SELECT *
FROM dbo.TimeTest;

+-----------------------------------+
|              ItemKey              |
+-----------------------------------+
| 99999999999999999999.123456789012 |
+-----------------------------------+

In order to successfully import your DB2 timestamp(12) values into SQL Server you'll need to export them as numeric values with the appropriate magnitude and precision.  If you can do that, you won't lose any data, and will gain the ability for SQL Server to properly validate data used in the key columns.   A numeric column is also extremely efficient for sorting, since it is not locale or collation sensitive.

Using a varchar(45) would allow keys to be created like this is a very long, non-numeric key; which would clearly be a bad idea.


Is it possible to limit timeout on Postgres server?

Yes, it's possible

There are two settings mentioned in the docs (idle_in_transaction_session_timeout is new to version 9.6x)

statement_timeout (integer)

  Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

  Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

idle_in_transaction_session_timeout (integer)

  Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.

  The default value of 0 disables this feature.

It's important to not set the statement_timeout in postgresql.conf unless you want amusement.

Here is an example of it working

SET statement_timeout = 10000;
SET
test=# SELECT pg_sleep(15);
ERROR:  canceling statement due to statement timeout


Unable to write to ndf after shrink emptyfile

What I learned,

I Shrunk emptyfile back to .mdf no problem, but when I tried to shrink emptyfile back to Data3.ndf same error.

It seems once you have shrunk emptyfile from an .ndf you have to delete (Remove) it, you can't write to again.

Additionally if you try to recreate the ndf with the same name, you will not be able to, until you run a t-log backup.

Optimal, don't expect to reuse an ndf once you have shrunk emptyfile.  If you need to write back to an ndf use a different name.

If you do need (or want) to write to the same ndf name:

Delete the ndf file (On Properties GUI select file and click remove)
Run a t-log backup
Recreate (Add) the ndf file


mysql.proc keeps crashing, cannot do a mysqldump?

I just had the same problem, this was fixed by issuing a command, assuming mysql data dir is on /var/lib/mysql/mysql

myisamchk -q -r /var/lib/mysql/mysql/proc

Source : http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html