converting latin to utf8mb4 causes questionmarks The original format of the data is unknown
The new table is in utf8mb4_general_ci

If I do CONVERT(BINARY CONVERT(column USING latin1) USING UTF8) as mentioned here - it fixes all text, but converts something like: © in the original column to ? in the new column.

If it helps to determine what original encoding it was in, the original text renders as e.g. KotaÄiÄ‡i and converts to Kotačići.

Is there a way to both preserve special characters and restore correct utf8 text format?

As requested in the comments an example via hex:

HEX(col):

C398C2A3C398C2BAC399E280A0C399C5A0C398C2A920C398C2B3C399E280A620C398C2A7C399E2809EC399E2809EC399E280A1

CONVERT(BINARY CONVERT(col USING latin1) USING UTF8):

أغنية سم الله

Just raw:

Ø£ØºÙ†ÙŠØ© Ø³Ù… Ø§Ù„Ù„Ù‡

The dump-file starts with:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

Tables get created with: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Ah, I was afraid of that.  That hex string is the "double encoding" of  أغنية سم الله Does that look about right?

This is the expression to 'fix' it:  CONVERT(BINARY(CONVERT(CONVERT(UNHEX('C398C2A3C398C2BAC399E280A0C399C5A0C398C2A920C398C2B3C399E280A620C398C2A7C399E2809EC399E2809EC399E280A1') USING utf8mb4) USING latin1)) USING utf8mb4)

This discusses how it probably came about.  You need to fix your code and fix the data:  https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored  (Look for "double encod")

(utf8 and utf8mb4 work equally well for Arabic.)

That short conversion you found needs to (sort of) be repeated to fix "double" encoding.

Further research

It is OK to be a mixture of Arabic and other languages in a utf8 or utf8mb4 column. It is not ok to have double-encoding, especially if some cells are correctly encoded. Somewhere the Arabic text was encoded an extra time, but the copyright symbol was not. Did they come from different sources? Maybe the problem came before this database we are looking at?

That is, dig into the client(s) you are using and dump the hex of text that is about to be INSERTed. Arabic should be two hex bytes: Dxyy; copyright (and many other popular symbols) should be Cxyy.

Fixing

If you find that some rows have double-encoded Arabic (or whatever) an d some rows have correctly encoded copyright, and if you can distinguish which rows are which, then applying the single-fix vs the double-fix should be 'easy'.

Ditto for columns.  Perhaps the copyright is never in the same column as the Arabic text?

Even messier is when a single cell has both.  That would strongly imply the client is "at fault".


How to query efficiently from Postgres to select special words? Let's say that I have a table called words with very many records.
Columns are id and name.

In the words table I have for example:

 'systematic', 'سلام','gear','synthesis','mysterious', etc.

NB: we have utf8 words, too.
How to query efficiently to see which words include letters 's', 'm' and 'e' (all of them)?

The output would be:

systematic,mysterious

I have no idea how to do such a thing. It should be efficient because our server would suffer otherwise.

An easy approach would be to consider the array of letters corresponding to each word, and search inside that with the @> (contains) array operator. This works independantly of the letters positions as shown in the example from the manual, i.e. ARRAY[1,4,3] @> ARRAY[3,1] is true.

This array can be easily obtained with regexp_split_to_array(name, '').
[EDIT: per @Erwin's answer, string_to_array(name, NULL) is faster so  better use that. It's a drop-in replacement in the rest of the answer]

Here's a demo that first materializes the array as a column in a test table containing a mix of english and french words (~511000 rows, average length=13 characters), and then a second test table without adding the array as a column.

=> CREATE TABLE tstword AS
    SELECT word_id as id,
    wordtext as name,
    regexp_split_to_array(wordtext, '') as arr FROM words;

To find a relatively large number of words:

=> select count(*) from tstword where arr @> array['s','m','e'];
 count
-------
 42268
(1 row)

Time: 268.809 ms

This does a sequential scan as shown by EXPLAIN ANALYZE:

 explain analyze select name from tstword where arr @> array['s','m','e'];
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on tstword  (cost=0.00..17554.46 rows=21256 width=14) (actual time=0.020..268.525 rows=42268 loops=1)
   Filter: (arr @> '{s,m,e}'::text[])
   Rows Removed by Filter: 468729
 Total runtime: 269.927 ms
(4 rows)

Time: 270.414 ms

But we can index the array with a GIN index:

CREATE INDEX idx_tst on tstword using gin(arr);

And then it's quite faster:

explain analyze select name from tstword where arr @> array['s','m','e'];
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tstword  (cost=252.74..11815.73 rows=21256 width=14) (actual time=46.378..60.203 rows=42268 loops=1)
   Recheck Cond: (arr @> '{s,m,e}'::text[])
   ->  Bitmap Index Scan on idx_tst  (cost=0.00..247.42 rows=21256 width=0) (actual time=45.202..45.202 rows=42268 loops=1)
         Index Cond: (arr @> '{s,m,e}'::text[])
 Total runtime: 61.677 ms
(5 rows)

Time: 70.185 ms

We might even avoid materializing the array as a column by directly indexing the expression, since postgres supports functional indexes.

create table tstword2 as select word_id as id,wordtext as name from words;
create index idx_tst2 on tstword2  using gin(regexp_split_to_array(name, ''));

Then the search has to be made with the exact same expression, and the index gets used:

 explain analyze select name from tstword2 where regexp_split_to_array(name, '') @> array['s','m','e'];
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tstword2  (cost=40.00..44.02 rows=1 width=14) (actual time=39.390..48.435 rows=42268 loops=1)
   Recheck Cond: (regexp_split_to_array((name)::text, ''::text) @> '{s,m,e}'::text[])
   ->  Bitmap Index Scan on idx_tst2  (cost=0.00..40.00 rows=1 width=0) (actual time=39.053..39.053 rows=42268 loops=1)
         Index Cond: (regexp_split_to_array((name)::text, ''::text) @> '{s,m,e}'::text[])
 Total runtime: 49.748 ms
(5 rows)

Time: 50.193 ms

See GiST and GIN Index Types in the manual for caveats on these types of indexes.


How do I compare Schemas for Multiple Databases to a True Copy? We have a database for each customer, over 700 databases. All the schemas are supposed to be exact same.
We have Source Control database, and want to schema compare to the 700. Sometimes schema in Production get out of line/ drift away from actual Source Control Master.

How do I compare schemas from multiple databases to a Visual Studio SSDT Project, or a gold copy empty Database in Production?

I looked at various techniques, which may not be optimal.

Compare to SSDT Project

This person utilizes a recursive script: Schema Compare multiple databases at once

However author utilizes file size, which I do not like as difference indicator.

'My technique for comparing file sizes is to open a CMD Window by using Shift-RightClick on the folder they are in, choose ‘Open Command Window Here’, then run a ‘DIR’, which will list the files with the size in bytes. '

Extract the Gold .dacpac:
 sqlpackage.exe /a:Extract /scs:Server=MyLaptopSQL2014;Database=Test; /tf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac

 Compare to the target databases:
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test1 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest1.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test2 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest2.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test3 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest3.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test4 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest4.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test5 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest5.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True

Compare to Gold Copy Database In Production:

This technique doesn't look at default indicators or index column names, triggers, stored procedures., etc

Different Ways to Compare SQL Server Tables Schema and Data

So what is the optimal way to compare schemas to many databases? I do not need to know the results, I just need a Flag indicator at end of day, Different Y/N.
It would be nice to use a DMV method and display yes/no results in a simple table, but SSDT will work also.

This will compare database scripts. Did not want to utilize sql compare, as it may take additional schema lock time on server.

#Set-ExecutionPolicy RemoteSigned

#OriginalDatabaseScript, this can be generate from source control or Master Copy database in Production
$OriginalScript = 'C:\DatabaseCompare\OriginalScript.sql'

#Source: Place in test file below, list databases by running select * from sys.databases where database_id > 4
$DatabaseSourceList = 'C:\DatabaseCompare\DatabaseList.txt'

#Destination: Database Files Generate Scripts
$Filepath='C:\DatabaseCompare\scripts' # local directory to save build-scripts to

foreach($line in Get-Content $DatabaseSourceList)
{

$DataSource='localhost' # server name and instance
$Database=$line # the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $false; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.Filename =  "$($FilePath)\$($Database)_Build.sql";
$NewFilename =  "$($FilePath)\$($Database)_Build2.sql";

$transfer = new-object ("$My.Transfer") $s.Databases[$Database]

$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()
"Created Database " + $Database

#Get-Content $CreationScriptOptions.Filename | Where { $_ -notmatch "/" } | Set-Content $NewFilename

if(Compare-Object -ReferenceObject $(Get-Content $OriginalScript) -DifferenceObject $(Get-Content $NewFilename))
    {$Database + " Files are different"}
Else
    {$Database + "Files are the same"}

 }

Edited from here:
Automated Script-generation with Powershell and SMO


Root Remove Remote % host. My SQL So i accidentally made it so root can be accessed anywhere remotely how do i revoke the % privilege. Using mysql.

+-----------+
| host      |
+-----------+
| %         |
| 127.0.0.1 |
| ::1       |
| localhost |
+-----------+

If you did it with a GRANT, run the corresponding REVOKE.

Or you could run this.  (Suggest you dump mysql.user first, just in case.)

DELETE FROM mysql.user
    WHERE user = 'root'
      AND host = '%';


mysql privilege to kill processes of other users I have a percona mysql database.  The version is 5.5.29-29.4-log.

I have created a user called admin which should be able to kill processes initiated by other users, i.e worker.

GRANT SELECT, INSERT, UPDATE, DELETE, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'admin'@'ip_' IDENTIFIED BY PASSWORD 'password';

It does had the process privilege, which I thought would do the trick. But, I can see this error

/* SQL Error (1095): You are not owner of thread 7480126 */

Would anyone be able to help?

From here

  If you have the PROCESS privilege, you can see all threads. If you
  have the SUPER privilege, you can kill all threads and statements.
  Otherwise, you can see and kill only your own threads and statements.

  You can also use the mysqladmin processlist and mysqladmin kill
  commands to examine and kill threads.

See here for privilege granting - search for SUPER.


iSeries Linked-Server SQL 2012 performance issue I have a seen a serious performance dive when moving from SQL Server 2008 R2 to SQL Server 2012 on my linked server queries.

I have set the linked server on the 2012 server as a Microsoft OLE DB Provider for ODBC Drivers that links through a DSN which uses the IBM/iSeries driver version 13.64.12.00 (I have also tried the link as provider IBM DB2 for IBMDASQL OLE DB Provider). The 2008 R2 server was setup with a linked server as Microsoft OLE DB Provider for ODBC Drivers that links through a DSN which uses the IBM/iSeries driver version 13.00.05.00.

I have worked with both Microsoft and IBM support for months and they are pointing fingers at each other and the issue is still not resolved. I have done more traces than I care to count. What I do know from all of these:

All other queries through linked servers that are not to the iSeries are faster on the new server than the old.
The iSeries drivers are significantly slower on the new server, within and outside of the SQL environment. The SQL environment does add additional slowness, but even using a separate tool to run the queries it is twice as slow on the SQL 2012 server.
Putting the updated IBM iSeries drivers on did not help (used to have the same version as the SQL2008R2 server but updated in attempt to find speeed).
The OLE DB linked server is slower than the ODBC linked server.

Has anyone else had this issue and what did you do to resolve it?

I have the OLE DB for DB2 provider linked (uses DB2OLEDB) server setup, but this is also much slower than the IBM ODBC connection. I already have the Allow In Process and Dynamic Parameter options set to 1.  I ran the query as an OPENQUERY and as 4 part name linked server query.

Community Wiki answer originally left as a comment by the question author

Resolution: I unchecked “Automatically set processor affinity mask for all processors” and checked the individual CPUs.  I had also already changed the MaxDOP =  0 (it was even faster than 1 or 3).

Results are blazing fast!  So the bug must be that the "Automatically set processor affinity mask for all processors" box does not actually use all the CPUs, it must only see one of them.  Manually selecting all the CPUs is required.  This was true for both the 2008 R2 and 2012 servers.


Having a PostgreSQL database exposed to the internet I've seen on DBA and other sites that exposing a database to the Internet is a particularly bad idea, but I noticed that PostgreSQL has SSL-certificate-only authentication. Does this make it any safer to expose a database to the internet (cert-only auth from non-localhost connections) or would it still be better to establish a VPN, or tunnel through SSH etc?

If the latter is safer, what exactly makes it safer? I feel like I am overlooking something glaringly obvious in this regard.

I've set up a handful of databases before but never actually needed to set one up that requires some sort of remote administration (“remote” in this case meaning over the Internet or WAN rather than a LAN).

It's not really about it being a bad idea in general, more that there are very few instances where you really need to expose your database to a public interface.

Much of security is about reducing the number of attack surfaces you expose. If your application does not absolutely have to expose your database to the public then you can hide it away behind a firewall and have another level of protection from any potential exploit that might appear in the future.

I would not consider "remote administration" a good enough reason to expose postgres. As you say; you already need SSH access to your box so you could easily keep postgres behind a firewall and gain access via that without any problems.

RE: SSL connections. This would be a must unless you were happy with third-parties just reading your data while you send it over the wire. The considerations here are the same as any traffic over the public internet.


SQL Developer code formatting I have just cut 'n' paste some SQL code into SQL developer (4.0.2.15). I have a right margin set and selected CTRL+F7 to format it. However I have two problems

The code is wider that the right margin I have set
The keywords are in capitals and the coding standard here is lowercase.

I have been unable to find how to change this or is this possible?

I did a google search for the phrase "sql developer code formatting", and the first hit was this:

http://www.thatjeffsmith.com/archive/2014/03/how-to-make-your-code-look-like-steven-feuersteins-in-oracle-sql-developer/

Have a read through that blog post from Jeff Smith, product manager for SQL Developer.  That should explain what you need to do.

Edited to add picture of the relevant options.


How safe is it to widen a column in MySQL? Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot.

A MySQL database I volunteered to help maintain has a table that looks like this:

CREATE TABLE person_per (
   [...]
   per_EnteredBy smallint(5) unsigned NOT NULL default '0',
   per_EditedBy smallint(5) unsigned default '0',
   [...]
) TYPE=MyISAM;

This works fine, except that recently our user IDs started to be larger than 65535, which caused the values stored into the above fields to be incorrect (i.e. large values get capped at 65535 rather than the expected value).

What I'd like to do is 'widen' those two columns of the table so that user IDs greater than 65535 can be correctly stored in them.  I think the commands to do it are:

ALTER TABLE person_per MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0';
ALTER TABLE person_per MODIFY per_EditedBy MEDIUMINT unsigned default '0';

My questions are:

Are the ALTER TABLE commands above correct?  (In particular I'm not sure if I should append a number-in-parenthesis to MEDIUMINT, and if so, what number it should be)
Will this alteration correctly preserve the data that is already stored in those two columns?  I don't want to lose it.
Are there any obvious 'gotchas' that I might run into if I widen these columns?  AFAICT SQL queries don't depend too much on integer columns being a particular width, but perhaps there is some non-obvious dependency that I should be aware of?

Here are the answers to your questions

  1.Are the ALTER TABLE commands above correct? (In particular I'm not sure if I should append a number-in-parenthesis to MEDIUMINT, and if so, what number it should be)

ALTER TABLE person_per MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0';
ALTER TABLE person_per MODIFY per_EditedBy MEDIUMINT unsigned default '0';

Yes, they are correct. You could also do this in a singe command:

ALTER TABLE person_per
    MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0',
    MODIFY per_EditedBy MEDIUMINT unsigned default '0'
;

The parentheses are not needed. In fact, the number will become 8 or 9.

  2.Will this alteration correctly preserve the data that is already stored in those two columns? I don't want to lose it.

The data will be preserved. In effect the alter table will behave like this:

CREATE TABLE person_per_new LIKE person_per;
ALTER TABLE person_per_new
    MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0',
    MODIFY per_EditedBy MEDIUMINT unsigned default '0'
;
ALTER TABLE person_per_new DISABLE KEYS;
INSERT INTO person_per_new SELECT * FROM person_per;
ALTER TABLE person_per_new ENABLE KEYS;
ALTER TABLE person_per RENAME person_per_old;
ALTER TABLE person_per_new RENAME person_per;
DROP TABLE person_per_old;

Under the hood, these are the steps that are executed. I trust that MySQL will perform your ALTER TABLE in like manner.

  3.Are there any obvious 'gotchas' that I might run into if I widen these columns? AFAICT SQL queries don't depend too much on integer columns being a particular width, but perhaps there is some non-obvious dependency that I should be aware of?

AFAICT you would only need to consider the size of the .MYD and .MYI files. Since SMALLINT is 2 bytes, and MEDIUMINT is 3 bytes, look for the files to increase byte 1 byte for each record. Thus, 1,000,000 rows in a 10MB MYD will now be about 11MB.

MyISAM tables with a wider row may need some adjustments (increases) in things like

key_buffer_size
join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
tmp_table_size

to support data pages, index pages, and temp tables that involve per_EnteredBy and per_EditedBy


Distributed Replay DCOM issue I have 2 SQL Servers in a Domain. Windows Firewall is disabled on both servers and there is no hardware firewall. Both servers are in the same subnet.

One server, called Controller is configured as a Distributed Replay Controller. The other sever, called Client is configured as a Distributed Replay Client.

The Distributed Replay Controller service is started, but when I try to start the Distributed Replay Client service, I get the following error in the Client Log:

[0xC8100005 (6)] Failed to connect controller with error code 0x800706BA.

In the Windows System log, I see the following error:

DCOM was unable to communicate with the computer controller using any of the configured protocols; requested by PID      830 (C:\Program Files (x86)\Microsoft SQL Server\120\Tools\DReplayClient\DReplayClient.exe).

The same account is running both services and this is actually a Domain Admin (Yes I know that's bad practise, but its a sandbox environment), and I have checked that the account specifically has the following DCOM permissions set on the Controller:

Local Launch
Local Activation
Remote Launch
Remote Activation
Local Access
Remote Access
and Full Control for configuration

Both servers running Windows Server 2012 Datacentre and SQL 2014 Enterprise

I have even tried giving the Domain's Eveyone group these permissions.

I have even rebuilt the Controller.

The servers can ping each other via both IP Address and Name

You need to configure DCOM permissions as DCOM is used for the RPC communications between controller and clients as well as the controller and administration tool. I think this link might help you (under the heading DCOM permissions). msdn

I won't repeat all the info on here since its on the msdn page but following all the steps on there allowed me to get distributed replay working.


Can a MSSQL job change its own active schedule? I am planning a new job for MSSQL Sever 2016.

I'd like the job to run on Sunday, every 10 minutes, until it sees a confirmation record is added to a table it checks as the final step in the job.  If the record is there, the job modifies its current schedule so it will not run again until next Sunday.  This leads me to wonder when the schedule updates in a "natural" job cycle.  I wonder if my change would be overwritten before it could be used.

So, can I use msdb.dbo.sp_update_jobschedule in a job's final step to do this to the current job and what technical pitfalls might I encounter doing that?

Examples and/or alternate solutions are always welcomed.

You can, using the stored procedure sp_update_schedule

    declare @shcedule_id int

    if exists (select 1 from your_table where condition = 'met') begin

    select @shcedule_id  = s.schedule_id from sysjobs j
    inner join sysjobschedules s on j.job_id = s.job_id
    where j.name = 'test_job'

    sp_update_schedule @schedule_id = 9, @freq_type = 4, @freq_recurrence_factor = 1,
    @active_start_date = 20190529, @active_start_time = 155500

    end

This will change it to weekly, recurring every one 1 week at 3:55 pm.

you can set it back to daily by setting @freq_type = 4

here's the Microsoft doc on the SP:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-2017


What are the canonical NoSQL resources for people who never used this technology? I am getting more and more interested by the NoSQL technology and I can read several posts on SE about how it works and the different products available.

However, I wonder if there are some canonical references, books or articles, which we can site in a research paper for example and which we can read to have a good overview of:

What the benefits/disadvantages are?
How it works?

This is a tough question in the sense that there are several NoSQL databases out there, and they're all slightly (sometimes radically) different from each other.

An important concept to understand for NoSQL technology is that of Brewer's CAP Theorem, so I've provided a link to a good article on it.  For a brief, graphical overview on the CAP Theorem, you should also look at Nathan Hurst's blog, a Visual Guide to NoSQL Systems.

Personally, I have experience with MongoDB (school) and Cassandra (work).  Now that I've used Cassandra for a few years, there are a few good resources that I can recommend:

Cassandra High Availability by Robbie Strickland
Webinar - Getting Started with Apache Cassandra by Rebecca Mills
About Apache Cassandra - DataStax's documentation

As for MongoDB, I have read several books on the subject and here are two (that I think) are really good:

MongoDB In Action by Kyle Banker
MongoDB: The Definitive Guide by Kristina Chadrow


Display both null and not null under same id but not both null or not null like XOR logic Consider the following Employee table:

In this table I need to select only the ids which have both null and not null marks, and it should not be both not null or both are null in col1.

Select NullTest.ID, NullTest.Col1
    From NullTest
    Join (Select ID From NullTest Where Col1 Is Not Null Group By ID)
         NotNulls
         On NotNulls.ID = NullTest.ID
    Join (Select ID From NullTest Where Col1 Is Null Group By ID)
         Nulls
         On Nulls.ID = NullTest.ID ;


How to apply a regex on a column fetched from DB I am using IBM DB2.

So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column:

([1-57-8][0-9]{2}
|0([1-9][0-9]
|[0-9][1-9])
|6([0-57-9][0-9]
|[0-9][0-57-9]))
([1-9][0-9]|[0-9][1-9])([1-9]\d{3}|\d[1-9]\d{2}|\d{2}[1-9]\d|\d{3}[1-9])

Someone suggested to me that I have to write a function/procedure in the query to validate that the SSN picked from the db follows this regex else it should not be stored in the result set of the query.

I don't know how to write a function or a procedure in SQL, can you please help me out here?

You can use Regex with UDFs (user-defined functions). What you need is to create a Function that will call a Regex library. After that, your SQL will call this Function to execute the Regex.

First, read the following article as an example.

It says that you can create this Function as:

CREATE FUNCTION regex1(pattern VARCHAR(2048), string CLOB(10M))
    RETURNS INTEGER
    SPECIFIC regexSimple
    EXTERNAL NAME 'regexUdf!regexpSimple'
    LANGUAGE C
    PARAMETER STYLE DB2SQL
    DETERMINISTIC
    NOT FENCED
    RETURNS NULL ON NULL INPUT
    NO SQL
    NO EXTERNAL ACTION
    ALLOW PARALLEL;

The article also shows the C library code and indicates how you can compile it.

After that, you may use your function like that:

SELECT c1, str
FROM   strTable
WHERE  regex1('#\s*\w+', str) = 1;

With that in mind, I suggest that you Google a little more to find a more complete Regex library to resolve your problem.

Edited: as suggested by Chris Aldrich, you may consider on running this function as fenced for safety. Read more here.


How mysqlcluster can be durable and consistent? I'm little noob and learning on how to use mysqlcluster,i have 1 management 2 datanode and 2 sqlnodes.According to a book "mysql cookbook" -alex davies and localcheckpoints and globalcheckpoints

By default, a running MySQL Cluster will commit a Local Checkpoint (LCP)—a copyof all the local data held on disk—every time 4 MB of changes are made to the cluster (since
the previous LCP) and global checkpoint (all transactions that have occurred since the last LCP) to disk every two seconds. if the data are fragmented and parts of large tables are striped on those datanodes(database "A" is 1000G and may be divided into two 500G parts) ,

Q1) How can a database still consistent and durable if any one of the datanode server is crashed ?

With Reference to restore DB with ndb_restore if i take backup
using "start backup" on management node, it will create backup on the backup directory on each Data Nodes but each backup will only hold the different fragment of table data instead of complete backup(backup taken by mysqldump). I tested backup and it was true where complete database size was around 44G and the backupdir was around 22G on each datanode backup directory. I'am confused and please correct me if I have misconception

Q1:  Data is both sharded and duplicated.  Probably each of your two datanodes has the entire dataset.  If you had, say, 5 nodes, each one could have 2/5 of the data, thereby spreading across 5 machines, yet having 2 copies of everything.  And loss of any one node can be recovered from.

Consistency is handled by a "eventual consistency" model.  If you write conflicting things to different nodes, the system will 'eventually' discover this.  Then some rule (which you established) will kick in to decide which data to keep.


When to run ETL for dimensions We have a suite of restaurant web applications that stores data in SQL Server 2012 on database server. We have now started to move data for reporting onto a separate data warehouse server. The data needs to get into the data warehouse quite soon after being updated/created.

Knowing when to move transactions to the data warehouse (and into fact tables) is easy -- we do that periodically or when certain events occur (like the manager finalising something).

But how do I know when to move the definitions to the data warehouse (and into dimension tables)? We are storing definitions as SCD Type 1.

We’re considering...

On a schedule, checking all the definitions on the database server with dimensions and updating any changes. And assigning transactions to “Unknown” is the dimension hasn’t arrived yet.
Putting triggers on all the definition table and updating the data warehouse as the changes happen
Updating the stored procedures to update the data warehouse every time a change is made
Using something like Microsoft Sync Framework, and incorporating the transform in there.

The database and data warehouse servers are on separate servers – which we are planning to move to SQL Azure.

So, how do I know when to run the ETL for dimensions? And, any suggestions on technology would be helpful (we are currently using SQL Server linked servers).

You have several options - all of them require some scheduling decision on your part. The one with the least impact to your source system would be, as mentioned in the comments, looking for an audit date/time stamp from your OLTP system. Many systems put a datetime stamp on rows whenever there is an insert or update, and as such can be used reliably to capture changes. This is safest if you use a >= pattern, so that you don't miss anything committed while you read. The likely small record set duplicated from the last millisecond records shouldn't cause much of an issue for standard upsert logic.

You can keep a table that tracks the latest Audit DateTime per source and use this though it may become unwieldy if you are pulling from numerous tables for a single dimension. If you know your system you may also use things like PK increments or a similar dependable data change pattern to restrict your source query.

CREATE TABLE [UTIL].[IncrementalLoadHelper]
(
     [Id] INT NOT NULL IDENTITY PRIMARY KEY
    ,[TargetTableSchema] VARCHAR(20) NOT NULL
    ,[TargetTableName] VARCHAR(100) NOT NULL
    ,[IncrementalValueInt] INT NULL
    ,[IncrementalValueDateTime] Datetime NULL
    ,[IncrementalValueVarchar] Varchar(500) NULL
    ,[IncrementalValueDecimal] Decimal NULL
    ,[SuccessfulLoadFlag] bit NOT NULL
    ,[InsertRowCount] INT NULL
    ,[UpdateRowCount] INT NULL
    ,[SSISPackageName] Varchar(500) NOT NULL
    ,[AuditInsertDate] DATETIME NOT NULL DEFAULT GETDATE()
    ,[AuditUpdateDate] DATETIME NULL
    ,[AuditUser] VARCHAR(200) NOT NULL DEFAULT SUSER_NAME()
)

.

SELECT [Columns]
FROM OLTP.SourceSystem
WHERE AuditDateTime >=
(
 SELECT MAX(AuditDateTime)
 FROM TrackingTable
 WHERE TableName = 'TblName'
)

You could also enable Change Tracking which uses built-in SQL Server functionality and doesn't require a helpful OLTP DB design. It will use less resources than Change Data Capture, which is a similar solution. Either one of these creates easily queried tables to identify changes that have occurred since a certain time and allow you to greatly reduce the total data that moves during each ETL phase.

I'd recommend dropping the linked servers and use SSIS to move your data. It will likely be easier to maintain and design. It has pre-built tasks that work with CDC. Either put SSIS on your DW machine or create a dedicated ETL processing machine and move as much of the ETL resource demand off of the DW and the OLTP.

Since you mentioned getting the facts first ensure you have the right inferred member logic to allow resolution at a later date when all data is available.

Also, consider, or make the business consider, the value of faster loads vs resource and development/maintenance costs. "Real time data" sounds great until you see it's alias: $$$$.


Fetch column data against group by data The source table structure is as defined in the image. I have fetched the data grouped by VesselID, with max function applied on cargodate(Date), as shown in the next image.

Now I need balance of the row fetched using group by. Please help.

According to your SQL statement you always get a unique date from a group VesseleID since you are using MAX from group. By using join statement on date you can get your desired result.

Explanation:

-- Complete Data Set
1. Select * from DailyYardBalTable;

-- Identical to your Query
2. SELECT VesseleID, Max(CargoDate) as CargoDate FROM DailyYardBalTable
   WHERE CargoDate<'2015-11-10 16:47:49.493' GROUP BY VesseleID

--Desired Result Set

3. SELECT T2.VesseleID,T2.CargoDate,T1.Balance FROM DailyYardBalTable T1
   INNER JOIN
    (SELECT VesseleID, Max(CargoDate) as CargoDate FROM DailyYardBalTable
    WHERE CargoDate<'2015-11-10 16:47:49.493' GROUP BY VesseleID) T2
  ON T1.CargoDate=T2.CargoDate GROUP BY T2.VesseleID,T2.CargoDate,T1.Balance

Here is the data set respective to the SQL statement #1, #2, #3.


sql server database size shown different in properties pane while monitoring when checking  database space used,my favorites are

use indexinternals
sp_spaceused

select (sum(size)*8)/1024 as size From sys.master_files where name like 'indexinternals%'

but for a curiosity I also checked the properties of database
but when I take the properties of the database its shows the size 380MB

Its just different on this database only ,all other databases shows correct values.Any suggestion where to look at and why its showing like that ?

Properties dialog shows data and log file size. It's the same as sp_spaceused without any arguments. Use sp_spaceused table returns size of the table (i.e., part of data file) - it never takes into account transaction log - that's the missing part in the discrepancy you see.


Fastest way to migrate database to AlwaysOn cluster I want to make the fastest migration plan to move a SQL Server database in simple recovery from a standalone server (SQL 2005) to a two node AlwaysOn cluster in full recovery.

Some databases are larger than 200gb so restore them in primary node, switch to full mode and add them to AlwaysOn by selecting full in initial data synchronization is not an option because that would make another backup/restore, taking a lot of time and resources (Time is critical!).

What I want is to restore the database in parallel in all the nodes at the same time (using norecovery I think? ) and then join them to the AlwaysOn group.
I have already read the documentation about AlwaysOn and it's not clear about this.

Would it be ok if I follow these steps:

In Standalone server: Set database to single user mode, switch
    database to full recovery
Backup database from standalone server and restore it in primary
    node with recovery and restore it in secondary node with no recovery
Add database to AvailabilityGroup by selecting "Join only" in
Initial Data Synchronization

Extra info:

The standalone  server is 2005 standard, so it's  not  possible to
join it as  a new node.
I want to make as less backup/restore as possible.
What I meant with set the database to single user is that there will be no changes in the database during the migration. It's important because I could make a full backup and then I wont need a differential backup.

Questions:

After restoring database in Primary node and Secondary node: if I want to join the database to an AG Do I also need to make log backup/restore even though the database had
   just change to full recovery and no one but me has connected to it?
Wich option should I choose in Select Initial Data Synchronization
   Page on the AG Wizard ? Join Only or Skip data synchronization?

Thanks!

The fastest way would be to create a cluster an join your current stand alone server to the cluster as a new node. Set the database to full recovery model, take a full backup and a transaction log backup and restore them to the instance on the other node with norecovery.

If you have a good full backup (most recent), it would be possible to set the recovery model to full, take a differential and then take a log backup using the last full from the simple recovery model as your base. The differential should bridge the lsn gap.

To answer your questions:

I'm not sure why you're switching the database into single_user except that you want no changes to be made. If you add the server as a node as I've set forth, this won't be an issue. Otherwise, yes, you'll need downtime to migrate the database. Your other option would be to set the recovery model to full, get it in full, setup mirroring to the new instance on the cluster and then cut over during a downtime which would be much less time.
That's entirely possible to do, but in your initial question you said you didn't want to do this.
You must restore at least one log or differential to bring the lsn gap as close as possible.
I would not use the wizard, but the join only means you've staged a database that is currently in norecovery and it only needs to join the AOAG. The wizard will want to pre-stage the databases for you and ask you for shared locations, etc. Like I said it's best to pre-stage and not use the wizard for the best flexibility.


How to change MySQL InnoDB cluster from single primary mode to multi primary mode? My goal is to setup, in a lab environment, a MySQL InnoDB cluster in multi-primary mode.

I have read much, but I cannot get this setup to work.

Ubuntu 16.04.3 server LTS x 3
SQL0 - SQL2

Ubuntu 16.04.3 server LTS x 2
REP0 - REP1

All server instances are in Virtual machine except REP0 is on bare metal and REP1 is off line.

Installed MySQL server 8.0 from Oracle APT repo using mysql-apt-config_0.8.10-1_all.deb.

These servers have been up and down with many config changes trying to find a setup that works.

Set MySQL server 8.0 with Legacy Authentication Method on all boxes.

My current /etc/mysql/my.cnf is based on Justin Ellingwood @ DigitalOcean.

This is /etc/mysql/my.cnf from SQL0. This machine was at one time the primary in a single-primary setup that did not work correctly.

The other /etc/mysql/my.cnf file are very similar to this one with the # Host specific replication configuration section changed.

The error I am trying to resolve now is from /var/log/mysql/error.log.

[ERROR] Plugin group_replication reported: 'It is not allowed to run single primary mode with 'enforce_update_everywhere_checks' enabled.'

Yes I do have these set in my.cnf because I want multi-primary mode. I do not want to run in single-primary mode.

loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

Where, How, What else do I need to change for these to be in multi-primary mode and not error out?

Any reason why you're not using MySQL Shell and its AdminAPI to set up and manage your InnoDB cluster?

You can create a multi-primary cluster very easily using the AdminAPI. Example:

var cluster = dba.createCluster("myCluster", {multiPrimary: true})

Also, if you already have a running Group Replication group that you'd like to manage using the Shell you can simply "adopt" it:

var cluster = dba.createCluster("myCluster", {adoptFromGR: true})

And since 8.0.14, you can change a running InnoDB cluster from single-primary mode to multi-primary mode "live". The AdminAPI provides a function for that operation:

<Cluster.>switchToMultiPrimaryMode()>

Also, it is possible to switch back a running cluster to single-primary mode:

<Cluster.>switchToSinglePrimaryMode([instance])

And even to elect a member of the cluster to be the new primary:

<Cluster.>setPrimaryInstance(instance)

Please take a look at the blog post that announced those new features:

https://mysqlserverteam.com/mysql-innodb-cluster-changing-cluster-topology-modes-live/

And for further information check the official userguide.

Cheers,

Miguel


How to change column name in mysql if column name has space? ALTER TABLE studentrecord CHANGE COLUMN  Full name fullname VARCHAR(255) NOT NULL;

I just want to change 'Full name' to 'Fullname'

Enclose the field name in back-ticks like this:

ALTER TABLE studentrecord CHANGE COLUMN `Full name` Fullname VARCHAR(255) NOT NULL;


SQL Server Concurrent Updlock Serializable vs Try Catch We import multiple records per second from different flat files. Sometimes we encounter a racing condition, and duplicate Unique error constraint. We are inserting and retrieving records,

I hear there are two methods to handle this.
Which is the better way, I heard UPDLOCK, SERIALIZABLE is the standard way. However, try catch prevents checking the additional If statement. Are both ways full proof, and will stop duplicate inserts? What is best coding practice wise, and performs better?

CREATE TABLE dbo.Customer
(
    RowId bigint IDENTITY(1,1) NOT NULL,
    CustomerId guid NOT NULL,
    Name varchar(255) NOT NULL,
    CONSTRAINT PK_RowId PRIMARY KEY CLUSTERED([RowId] ASC)
)
create unique nonclustered index [UN_CustomerId] ON [dbo].[Customer] ([CustomerId] ASC) include (Name)
create nonclustered index [UN_Name] ON [dbo].[Customer] ([Name] ASC) include (CustomerId)

Method 1:

IF NOT EXISTS
(
    SELECT *
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)
BEGIN
    INSERT INTO dbo.Customer(CustomerId, Name) VALUES (@CustomerId, @Name)
    SELECT @CustomerId
END
ELSE
BEGIN
    SELECT CustomerId FROM dbo.Customer WHERE Name = @Name
END

Method 2:

BEGIN TRY
    INSERT INTO dbo.Customer(CustomerId, Name) VALUES (@CustomerId, @Name)
    SELECT @CustomerId
END TRY
BEGIN CATCH
    SELECT CustomerId FROM dbo.Customer WHERE Name = @Name
END CATCH

Are both ways full proof, and will stop duplicate inserts?

Method 2 is not safe under concurrency as written. There is no guarantee that the row that caused the insert to fail will continue to exist when the select in the catch clause runs.

In addition, the catch clause could execute for errors other than a duplicate key violation, because the code does not check the error number.

You should also be aware of the potential for a doomed transaction.

Aaron Bertrand wrote about the overhead of try/catch. The overhead is usually higher than checking first.

  What is best coding practice wise, and performs better?

Method 1 is a common pattern, but needs a transaction to be safe. Performance depends on local factors, so you should conduct your own testing. As a side note, you can avoid one query by using the output clause instead:

DECLARE
    @CustomerId uniqueidentifier = {guid '16D39773-9CC2-4CCF-A6A8-ACF1465030CC'},
    @Name varchar(255) = 'name';

BEGIN TRANSACTION;

    IF NOT EXISTS
    (
        SELECT *
        FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
        WHERE Name = @Name
    )
    BEGIN
        INSERT dbo.Customer(CustomerId, [Name])
        OUTPUT @CustomerId AS CustomerId
        VALUES (@CustomerId, @Name);
    END;
    ELSE
    BEGIN
        SELECT CustomerId FROM dbo.Customer WHERE [Name] = @Name;
    END;

COMMIT TRANSACTION;

As an alternative, you may want to compare the performance of a safe merge solution:

DECLARE
    @CustomerId uniqueidentifier = {guid '16D39773-9CC2-4CCF-A6A8-ACF1465030CC'},
    @Name varchar(255) = 'name';

MERGE dbo.Customer WITH (SERIALIZABLE) AS C
USING (VALUES(@CustomerId, @Name)) AS I (CustomerId, [Name])
    ON I.Name = C.Name
WHEN NOT MATCHED
    THEN INSERT (CustomerId, [Name])
    VALUES (I.CustomerId, I.[Name])
WHEN MATCHED THEN UPDATE
    SET @CustomerId = C.CustomerId,
        @Name = C.[Name]
OUTPUT @CustomerId AS CustomerId;


Is there a way to trace the DDL and DML activities performed on Microsoft SQL Server manually? I had C4 audit enabled to log all the activities previously and it mysteriously turned off. So I was looking for a way to trace changes/updates made to the complete database in terms of it's schema and the data from an year ago to current date.
Most sources pointed towards the data stored in default trace. Unfortunately, it only stores data for last seven days.
I tried using the fn_dblog() and only saw very recent data present. Generating Standard Reports also resulted in same.
So, is there any other way to view this historical data?

I'm guessing your Audit was turned off long enough ago to explain why you're only seeing recent data (couldn't tell you how it was turned off though).
Other tools built into SQL Server that might help you with auditing is Change Data Capture and/or you could also look into DDL Triggers.


MariaDB XtraDB Performance Tuning I believe that innodb has a single read thread. However, with MariaDB you can set multiple read threads. I'm unsure what the recommended value would be.

Questions

Does anyone have any experience with MariaDB and performance tuning?
Can you think of any features within MariaDB that XtraDB uses that MySQL does not?
Are there any similar settings that can I can use to fully exploit XtraDB and Maria's enhanced functionality?

InnoDB in Percona Server 5.0.91 and MySQL 5.5.x has three options

innodb_read_io_threads
innodb_write_io_threads
innodb_io_capacity

These options were introduced in MySQL 5.1.38 InnoDB Plugin

Tuning these will engage more CPUs within InnoDB/XtraDB

I have discussed this in past posts in the DBA StackExchange along with other InnoDB tuning suggestions

Multi cores and MySQL Performance
About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?
Query runs a long time in some newer MySQL versions

Click here for the XtraDB features that the InnoDB Plugin does not have.


error recovering pg_dump compressed file I produced this backup:

pg_dump -v -Fc -Z5 -f thebackup.dmp.gz thedb

I can't open the dump with gzip, 7zip... I could recover the same files in the past without using -Fc parameter. Is it possible that using -Fc and -Z5 might have corrupted the compressed file?

Just use pg_restore:

  pg_restore is a utility for restoring a PostgreSQL database from an
  archive created by pg_dump in one of the non-plain-text formats

Using -Z5 -Fc on pg_dump does not transform the backup into a gzip format. It adjusts the compression level, but the structure of the file is still the specific custom archive format that non-database commands like gzip or 7zip don't know.


Index options and applying partitioning to an index I have following two questions:

I have a SQL Server index with the following options:

PAD_INDEX = OFF
STATISTICS_NORECOMPUTE = OFF
SORT_IN_TEMPDB = OFF
DROP_EXISTING = OFF
ONLINE = OFF
ALLOW_ROW_LOCKS = ON
ALLOW_PAGE_LOCKS = ON
FILLFACTOR = 70

Where are these details are stored in the database? I have found tables like sys.indexes and sys.stats where a few of these options are stored.  I need the complete list, if possible.
To partition an index in SQL Server, we need to drop it and recreate it by associating it with partition scheme.  Is this the only way to apply partition on an existing index?

Not all index create options are persisted as meta-data; these (e.g. SORT_IN_TEMPDB, ONLINE, etc.) are specified only as run-time DDL options.

Rather than drop and recreate the index entirely, an index can be partitioned by using CREATE INDEX...WITH (DROP_EXISTING=ON).

CREATE INDEX YourTable_Index ON dbo.YourTable(IndexColumn)
    WITH (DROP_EXISTING=ON) ON PS_YourTable(PartitioningColumn);

EDIT:

Be aware that SQL Server does not consider the existing index FILLFACTOR with CREATE INDEX...DROP_EXISTING=ON because a new index is created. Although, the existing index FILLFACTOR can be used with ALTER INDEX...REBUILD (by omitting the FILLFACTOR clause), that method does not allow changing partitioning or the filegroup whereas CREATE INDEX...DROP_EXISTING=ON does and avoids sorting the rows during the index recreate. The desired FILLFACTOR needs to be explicitly specified when using CREATE INDEX...DROP_EXISTING=ON when a non-default value is desired.

You'll need to generate the DDL dynamically if you want to retain the old index FILLFACTOR (and other persisted options of the original index) without manually specifying the value.


How to migrate a database that has a database master key and certificate for encryption Morning y'all.

I'm a little stumped at this one. We have a database that we encrypted a column in a table. This is the master key that I created and the Certificate & Symmetric key. I need to migrate this database to another instance of SQL, but after I migrate the database, I cannot decrypt the information.

Help?

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'omitted';

CREATE CERTIFICATE SecureLoginPasswords
   WITH SUBJECT = 'SecureLogin Customer Passwords for Website';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE SecureLoginPasswords;
GO

I need to migrate this database to another instance of SQL, but after I migrate the database, I cannot decrypt the information.

You have a database master key (DMK), which is protected by both the service master key (SMK) and a password. Since you're migrating to a different instance of SQL Server you can still access the DMK through the password, however automatic key decryption will no longer work because the SMK has changed.
The fix is easy enough. When you restore the database to the new instance, run the following:
use databasename
go

open master key decryption by password = 'MyPassword'
go
alter master key drop encryption by service master key
go
alter master key add encryption by service master key
go
close master key
go

This will drop the encryption of the DMK by the SMK, since it can't decrypt it on the new instance. Then it will add the encryption through the SMK back to the DMK so that automatic key decryption will work.
The reason I don't say to backup and restore the SMK, as the other answer shows, is that it affects all of the databases on the instance whereas just fixing the broken one won't cause any other issues on any current databases.


How to track how many times the data is read? I want to track how many times records are read in a specific table.

Let's say that we have this table

ID      Name
1       Cat
2       Dog
3       Fish

I want to know how many times the record "Cat" has been read and how many times the record "Dog" has been read and so on.

What is the best way to achive this goal?

Write a stored procedure and make sure the data is only accessed through that procedure.
In it, you can increment a counter in another table.


Start Windows Service If Replication Fails Setup:

We have SQL Server Management Studio 2005 SP3 on all of our Servers.
We have 1 Main Server, and 1 Secondary Server that has the database from the Main Server being replicated on it. (If it matters, this is a "Pull" replication instead of a "Push" replication)

Question:
Is there a way to use the Alerting tools in SSMS 2005 to start a Windows Service on the Secondary Server in the event of Replication Failure?

If not, is this feature available in a newer version of SSMS?

As a result of the replication job failure, you could respond with an xp_cmdshell statement to perform a Windows function: http://msdn.microsoft.com/en-us/library/ms175046.aspx

xp_cmdshell needs to be enabled in your Surface Area config: http://msdn.microsoft.com/en-us/library/ms190693.aspx


Retrieve linked servers out of system database files? I have the data and log files of a SQL-Server that has crashed. It's user databases have been restored. I am looking for the jobs and linked servers that have been there before the crash. I attached the old system databases and looked ad some views. Jobs have been found quickly in msdb.
but sys.servers and sys.sysservers seem to query and display the current server always.

I tried:

select * from sys.servers
select * from msdb_old.sys.servers
select * from master_old.sys.servers

but all of them return the same, only the current server config.

Are the linked server objects persisted and queryable anywhere in the old files?

The answer placed here for future reference if needed. It looks as though this data may be in a hidden table in the master database sys schema named sysxsrvs.

According to what I could find this may be available to be queried if connected through the DAC.

It also uses sysobjvalues (contained in all databases). I believe to populate the srvproduct/providername fields.

reference: https://msdn.microsoft.com/en-us/library/ms179503.aspx


HADR wait on servers without HA configured I am investigating a slow SQL Server 2012 server in a virtualized (VMWare 6.5) environment, and have come across three different waits on the server that are... confusing. These are HADR_FILESTREAM_IOMGR_IOCOMPLETION, DIRTY_PAGE_POLL, and SQLTRACE_INCREMENTAL_FLUSH_SLEEP waits.

All three of these have essentially the same wait time (about 1000ms / sec). From research, I see that the DIRTY_PAGE_POLL and SQLTRACE_INCREMENTAL_FLUSH_SLEEP waits are (usually) nothing to be concerned about. However, documentation for the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait states that it is a wait generated by the system while waiting for HA replication to complete.

This particular Dev server has never had HA replication configured on the server (AFAIK / AFAICT), and does not now have replication or Always On High Availability of any type configured.

In this case -

Why would the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait occur at all?
Would this wait possibly cause, or indicate a cause of, a slowdown in a data load situation (5x-10x longer data loads than expected)
Since all three waits are running the same duration, is there any significance to the other two waits in this specific case, and could they indicate a slowdown situation on the server?

This server appears to be configured identically to the live server (also virtualized) that we are attempting to duplicate, with the exception of the OS version (Nt6.3 Build 9600 on live, NT6.1 SP1 Build 7601 on Dev). None of these three waits show up significantly on the live server.

Any ideas?

Why would the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait occur at all?

According to Paul Randal's wait stats library entry for this, waits are to be expected whether AGs are configured or not:

  This wait occurs every 0.5 seconds for 0.5 seconds, regardless of whether any availability groups are configured.

Note however that this wait type was introduced in SQL Server 2012, so if your "live server" is an older version, you won't see this wait type on it.

  Since all three waits are running the same duration, is there any significance to the other two waits in this specific case, and could they indicate a slowdown situation on the server?

It's also not surprising that the wait duration is similar for these three waits, as they each occur on a routine schedule.

  Would this wait possibly cause, or indicate a cause of, a slowdown in a data load situation (5x-10x longer data loads than expected)

Unfortunately, these three specific waits are very unlikely to be the culprit for your slower-than-expected data load.  I would filter them out, and check wait stats again.  Consider filtering out all "benign" wait stats (example here: Wait statistics, or please tell me where it hurts) and reviewing what's high.


Why these two queries have different result? Why these two queries have different result?

SELECT lf_transit_new.trayNo
FROM lf_transit_new
WHERE lf_transit_new.trayNo NOT IN
(
    SELECT lf_transit_new.trayNo
    FROM lf_transit_new
    WHERE lf_transit_new.trayNo IN
        (SELECT lf_transit_cage.trayNo
         FROM lf_transit_cage)
)

SELECT lf_transit_new.trayNo
FROM lf_transit_new
WHERE lf_transit_new.trayNo NOT IN
(SELECT lf_transit_cage.trayNo
FROM lf_transit_cage)

First query show 561 rows but second query show none.

I suspect that lf_transit_cage.trayNo contains some NULL values, which causes problems for a NOT IN check but not an IN check. This is an issue that we've all hit at some point as it doesn't seem intuitive, you expect that the two checks will work more similarly.

The quick fix is to alter SELECT lf_transit_cage.trayNo FROM lf_transit_cage to SELECT lf_transit_cage.trayNo FROM lf_transit_cage WHERE lf_transit_cage.trayNo IS NOT NULL, and/or if there shouldn't be NULL values in that column fix the data and perhaps set the column to not accept NULLs.

With ANSI_NULLS set off NOT IN does work as you were expecting. See this answer for an explanation as to why in both cases.


Insert into DB2 table a result from temp table Is there a way to insert a temp table results to DB2 table, for example:

WITH TEMP1 AS (
SELECT CUSTOMER_ID
FROM T_CUSTOMER WHERE CUSTOMER_ID BETWEEN 100 TO 300
),
TEMP2 AS (
SELECT CUTOMER_ID, ORDER_ID
FROM TEMP1 T1
JOIN T_ORDER R1 ON T1.CUSTOMER_ID = R1.CUSTOMER_ID)

SELECT CUTOMER_ID, ORDER_ID
FROM TEMP2

I tried the below way, but it did not work:

INSERT * INTO T_STORAGE (CUTOMER_ID, ORDER_ID)
SELECT *
FROM TEMP2

I searched a lot but couldn't find any way. Only to modify my query without temp tables, but then the query will be very slow.

Since TEMP2 only exists in the scope of the statement, you can't reference it from another statement. You can insert directly like:

INSERT INTO T_STORAGE (CUSTOMER_ID, ORDER_ID)
WITH TEMP1 AS (
    SELECT CUSTOMER_ID
    FROM T_CUSTOMER WHERE CUSTOMER_ID BETWEEN 100 AND 300
), TEMP2 AS (
    SELECT CUSTOMER_ID, ORDER_ID
    FROM TEMP1 T1
    JOIN T_ORDER R1
        ON T1.CUSTOMER_ID = R1.CUSTOMER_ID
)
SELECT CUTOMER_ID, ORDER_ID
FROM TEMP2

If you need to use the content of TEMP2 in several statements, you can declare a global temporary table and insert to that first.

I'm not sure why you need the CTE though, this should be equivalent:

INSERT INTO T_STORAGE (CUSTOMER_ID, ORDER_ID)
SELECT T1.CUSTOMER_ID, R1.ORDER_ID
FROM T_CUSTOMER T1
JOIN T_ORDER R1
    ON T1.CUSTOMER_ID = R1.CUSTOMER_ID
WHERE T1.CUSTOMER_ID BETWEEN 100 AND 300


Does table size matter? I have a table like this (with more columns though):
CREATE TABLE [dbo].[MyTable](
    [SnapKey] [int] NOT NULL,
    [SnapDt] [smalldatetime] NOT NULL,
    [Company] [varchar](4) NOT NULL,
    [ProfitCenter] [varchar](10) NOT NULL,
    [CostCenter] [varchar](10) NOT NULL,
) ON [MyPartition]([SnapKey])

CREATE CLUSTERED INDEX [IDX1] ON [dbo].[MyTable]
(
    [SnapKey] ASC
)

Table is partitioned on SnapKey. SnapKey is the date part of SnapDt, stored as an integer, for example 20160131 and 20160229.
Each partition contains only 1 SnapKey. For each SnapKey partition, I have about 5 million rows. Currently I'm keeping only the last day of each month in my table.
I always use SnapKey for querying. No updates happen to the data. For each day, data is populated into the table and then we run some reports on it during the month.
Question: If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower?
I couldn't find any clear answer, so I tried to populate the table with data but I ran out of storage, so I decided to ask you, to see if there is any theoretical explanation.
Clarification
By storing 5 more days, we will store 6 times the data (for historical reporting). We will keep the last 6 days of the month instead of only the last day.
Our queries will not change and our reports are still over 1 single day (one SnapKey).
We have one SnapKey per month. For now we have
20160131
20160229
20160330

...and so on. One SnapKey for each month-end.
By having 5 days more, SnapKey will look like:
20160126, 20160127, 20160128, 20160129, 20160130, 20160131
20160224, 20160225, 20160226, 20160227, 20160228, 20160229
20160325, 20160326, 20160327, 20160328, 20160329, 20160330 ...and so on

You'll see that we store 6 times more data, but still work on only one SnapKey in our queries. This means we always have:
WHERE SnapKey = xxxxxxxx

in all of our queries.

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower?

It depends.

No - if you run exactly the same queries as before (no access to the new data at all).

SQL Server's partitioning implementation creates a separate rowset for each partition, so when you create a partitioned index, it creates a separate b-tree structure* for each partition (note that partitioned heaps also exist).

Simply adding more partitions therefore changes nothing from the point of view of the existing partitions - the indexes are exactly the same. Your queries access only a single partition, so nothing changes.

Maybe - if you query the new data at any stage. Bringing the new data into memory may displace data needed by the original queries, depending on the amount of memory you have. If the change results in new physical I/O, you will see an impact on performance, with the severity depending on the capability of the storage subsystem.

* This is mentioned in many places in the product documentation, for example:

Clustered Index Structures
Nonclustered Index Structures
Heap Structures

From those links:

  When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition.
  When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition.
  When a heap has multiple partitions, each partition has a heap structure that contains the data for that specific partition.

You can also explore this for yourself by looking at the system catalog views like sys.partitions, which shows the hobt_id (heap or b-tree id) of the structure that contains the rows for a particular partition.


Changing table types from MyISAM to InnoDB in a MySQLdump file I have a few hundred systems running running on a MariaDB 10.1.x. On these systems there is a master database which, for various legacy reasons, has to have MyISAM tables. I wish to produce slaves for these masters which have InnoDB versions of all the master MyISAM tables. For operational reasons, I would prefer not to have to perform various ALTER TABLE operations on the slaves.

I was thinking (possibly simplistically) of taking the MySQLdump file from the master required as part of the initial slave setup, and then using some sort of script simply to alter its CREATE TABLE commands to specify a table type of InnoDB rather than MyISAM.

Is this a sensible approach, and has anyone else tried it? Is there a better approach which would get me a slave which uses InnoDB without having to do loads of ALTER TABLEs.

I am keen for this to be something which can be automated/script based, as we have literally hundreds of these systems in the field and so a manual upgrade process would be labour intensive and very error prone.

As an aside, I note that MySQL/MariaDB itself uses many MyISAM tables internally e.g. the mysql.user table. Do they use MyISAM for any particular reason, or could these tables also safely be converted to InnoDB. Or am I better ensuring that any script I use to do the conversion avoids the tables in the mysql database altogether?

I have created scripts where I flip the storage engine from MyISAM to InnoDB as follows:

mysqldump ... | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > dump.sql

If you want to see if this really works, just dump the schema as follows:

mysqldump --no-data ... | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > schema.sql

With reference to the mysql schema, please don't touch it. Why ???

In MySQL 5.5, all tables in the mysql schema were MyISAM.

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|          22 | MyISAM |
|          22 | Total  |
+-------------+--------+
2 rows in set (0.03 sec)

In MySQL 5.6, that began to change

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|           2 | CSV    |
|           5 | InnoDB |
|          21 | MyISAM |
|          28 | Total  |
+-------------+--------+
4 rows in set (0.00 sec)

Here are the non-MyISAM tables for MySQL 5.6:

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine in ('InnoDB','CSV')
    -> order by engine,table_name;
+----------------------+--------+
| table_name           | engine |
+----------------------+--------+
| general_log          | CSV    |
| slow_log             | CSV    |
| innodb_index_stats   | InnoDB |
| innodb_table_stats   | InnoDB |
| slave_master_info    | InnoDB |
| slave_relay_log_info | InnoDB |
| slave_worker_info    | InnoDB |
+----------------------+--------+
7 rows in set (0.00 sec)

The two CSV tables are the slow log and error log, should your change log_output to FILE. The 5 InnoDB tables were introduced to support crash-safe replication (See Documentation on this).

It does not stop there. MySQL 5.7 introduced more InnoDB tables.

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|           2 | CSV    |
|          19 | InnoDB |
|          10 | MyISAM |
|          31 | Total  |
+-------------+--------+
4 rows in set (0.01 sec)

Here are the non-MyISAM tables for MySQL 5.7:

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine in ('InnoDB','CSV')
    -> order by engine,table_name;
+---------------------------+--------+
| table_name                | engine |
+---------------------------+--------+
| general_log               | CSV    |
| slow_log                  | CSV    |
| engine_cost               | InnoDB |
| gtid_executed             | InnoDB |
| help_category             | InnoDB |
| help_keyword              | InnoDB |
| help_relation             | InnoDB |
| help_topic                | InnoDB |
| innodb_index_stats        | InnoDB |
| innodb_table_stats        | InnoDB |
| plugin                    | InnoDB |
| servers                   | InnoDB |
| server_cost               | InnoDB |
| slave_master_info         | InnoDB |
| slave_relay_log_info      | InnoDB |
| slave_worker_info         | InnoDB |
| time_zone                 | InnoDB |
| time_zone_leap_second     | InnoDB |
| time_zone_name            | InnoDB |
| time_zone_transition      | InnoDB |
| time_zone_transition_type | InnoDB |
+---------------------------+--------+
21 rows in set (0.17 sec)

Please note that there are still 10 MyISAM in MySQL 5.7. Here they are:

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine='MyISAM';
+------------------+--------+
| table_name       | engine |
+------------------+--------+
| columns_priv     | MyISAM |
| db               | MyISAM |
| event            | MyISAM |
| func             | MyISAM |
| ndb_binlog_index | MyISAM |
| proc             | MyISAM |
| procs_priv       | MyISAM |
| proxies_priv     | MyISAM |
| tables_priv      | MyISAM |
| user             | MyISAM |
+------------------+--------+
10 rows in set (0.00 sec)

Why are there 10 tables still in MyISAM ?

The tables user, db,procs_priv, proxies_priv, tables_priv, and columns_priv are tables that store user grants.
func contains stored functions
proc contains stored procedures
event contains schedules for executing events
ndb_binlog_index contains info needed for the NDB storage engine (MySQL Cluster)

These 10 tables are rarely used. Their contents are loaded into RAM on startup and then referenced from RAM. There is no need for them to be InnoDB because they are system-level tables that should not be changed via transaction.

Look at the potential problems that could occur if these 10 tables were InnoDB:

Running a GRANT command in a transaction and it rolls back
Creating a stored procedure in a transaction and it rolls back
Creating a new user and the user table crashes and gets corrupted

These and other problems can result and you would not even know it until mysqld restarts and nobody can log in, execute a stored procedure, or schedule a cron'd event.

Therefore, with good reason, these 10 tables were left as MyISAM. Again, please don't change the storage engine of anything in the mysql schema.


What is Clustered index data page behavior on inserts if all columns make up the index? What really happens at the data page level if you have, for example, a 5 column table with all 5 of those columns being part of the primary key and you cluster the PK so every column is part of the index?

You expect 1000’s of inserts on the table and you arbitrarily set fill factor to 80%.  Since the pages have 20% free space for the inserts, what happens during the inserts to the records that already exist on the page and the new record would fits in between them?  Does a page split occur or does the rest of the records move on the page or something else?

It's not clear to me how your first paragraph relates to the second, but I can answer the following bit:

  Since the pages have 20% free space for the inserts, what happens
  during the inserts to the records that already exist on the page and
  the new record would fits in between them? Does a page split occur or
  does the rest of the records move on the page or something else?

When there is enough free space on the page for the record being inserted (whether in the middle or at the end), there will be no page split, as the page can simply be rewritten with the new data inserted.
If there is insufficient space, then there will be a page split, with approx half the data moved to a new page.
Note that if the insert is to the end of the very last page, and there's no space, then a new page will be created just for the new row and subsequent appends. That's what's known as a "good" page split, though it really isn't a split at all even though SQL server counts it as one.


How to efficiently handle querying for different combinations of optional parameters Consider the following Tables:

Table: Parts
    part_id INT PK
    part_number VARCHAR

Table: Jobs
    job_id INT PK
    job_number VARCHAR

Table: Job_Parts
    part_id INT FK PK
    job_id INT FK PK
    quantity INT

Running the following statement:

SELECT
    p.part_number AS [Part],
    j.job_number AS [Job],
    jp.quantity AS [Quantity]
FROM
    dbo.parts AS p
    LEFT OUTER JOIN
        dbo.job_parts AS jp
        ON jp.part_id = p.part_id
    Left OUTER JOIN
        dbo.jobs AS j
        ON j.job_id = jp.job_id

Will give a table that can give results like the following:

Part        |Job        |Quantity
Part1234     Job1        10
Part1234     Job2        5
Part1234     Job3        7

Now let's pretend I have that above statement inside of a Function:

CREATE FUNCTION JobParts(
    @PartNum VARCHAR = NULL,
    @JobNum VARCHAR = NULL
) RETURNS TABLE AS (
    --SELECT STATEMENT
)

Is there a way for me to modify the table join in the select statement such that:

If @PartNum or @JobNum have a value, it joins where part_number or job_number equal the provided value
If @PartNum or @JobNum are NULL, it will only return the last record put in for a part.

To give an example, if I enter @JobNum='Job2' I want the result I get to be:

Part        |Job        |Quantity
Part1234     Job2        5

Otherwise, if @JobNum is NULL is defaults to just showing the last entry in the table, so my result would be:

Part        |Job        |Quantity
Part1234     Job3        7

I highly recommend that you don't go down that path.
You are mixing business logic with relational querying, and that's a sure recipe for many headaches later on, especially in terms of performance.
Keep in mind the rule of '1 query for 1 task', and the simpler and more granular, the better. You are trying to write 1 query, to handle different tasks.
Use a business control layer either on the client side, a dedicated business tier, or even in a stored procedure on the server, and execute different dedicated, simple SQL queries based on your logic.
For example, in your case you could write a procedure that will accept both parameters and execute the right query based on the values provided.
The procedure has negligible overhead, and you get the huge advantage of having separate, optimized query plans created for every combination.
CREATE PROCEDURE SomeProc
@PartNum VARCHAR,
@JobNum VARCHAR
AS
BEGIN
IF @PartNum IS NULL AND @JobNum IS NULL
-- Specific Query 1 (or execute sub-procedure) goes here
ELSE IF @PartNum IS NOT NULL AND @JobNum IS NOT NULL
-- Specific Query 2 (or execute sub-procedure) goes here
ELSE IF @PartNum IS NULL AND @JobNum IS NOT NULL
-- Specific Query 3 ... etc.
ELSE --
-- Generic query, not optimized
END

UPDATE:
If you need this to work for a large number of parameters, I highly recommend that you read this article by Erland Sommarskog. It is the best article that I know of for this type of challenge, and will offer you several solutions, with thorough analysis of the pros and cons of each. It's long, it's not an easy read, but it will be worth every minute you spend on it.
What worked well for me in previous similar cases, is a hybrid approach.
I start with a generic solution, you will find a few options in the article.
Then, I set up a trace, or a log table to record actual usage stats by the users and let it run for some time.
Although product and marketing will say that all combinations are equally important, you will find that in real life, most users tend to use only a few and repeat the same pattern.
Then, take the top used combinations, and write specific queries just for those, and use the generic query for the few instances that a unique combination of parameters is used. I've added the line of code to the example.
This way you get the best of both worlds - for most executions, you will get excellent performance, and still support all possible combinations.
BTW - An interesting side effect of this approach, is that over time users start to notice that it takes much longer when they use the non-standard combinations, and then they either complain about it, so you can write a specific query for that case and make them happy, but usually they tend to just use the common combinations more, that are way faster :-)
HTH


Multiple copies (cp) with Oracle ASM asmcmd tool? In order to import a transportable tablespace into an Oracle 11 database with ASM, we use the cp command of the asmcmd tool.

How can we copy more than one file at a time?

Wildcards are not supported, copying directories either, and I can't find any hint if asmcmd has a scripting mechanism.

Since no platform or version is mentioned, I assume Unix/Linux and 11gR2.

First of all, check Running ASMCMD in Noninteractive Mode. This explains that we can use ASMCMD in a script so whatever we can dream of, we can script. In this case in order to copy files we can use

asmcmd command options

A simple example is shown here Running ASMCMD commands in a script
Change this example slightly to get it to copy all files in a local directory, also using this Using the ASMCMD cp command as input:

ls | while read FILE
do
   asmcmd cp $FILE +data/orcl/datafile/$FILE
done

I am not sure how smart it is to run multiple copies in parallel. It all depends on what your hardware can handle. If you want to run in parallel, run the copies in the background but be a little careful, when running too much in parallel, it will definately cause a slower throughput than running in a sequential way.

For windows it will be slightly different, the loop construct will be different.

I hope this helps.


mySQL Data loading: Better to column map or INSERT SELECT INTO ETL? I've got 125 csv files to upload into a database. The files are typically ~200K rows and 30 columns.

In order to make the data more relational I was going to column map during LOAD DATA INFILE process (specific columns into specific tables) via bash script. But now I'm wondering if it makes more sense to LOAD all the files and use INSERT SELECT INTO to do my ETL'ing.

Any recommendations?

Since you have CSV files, it makes a lot more sense to use LOAD DATA.  Put them into 125 tables.  After that, you can do the massaging that you suggest doing with bash.


How to make the SQL query more efficient? I use mysql under Linux. My table is about 8000000 rows. The table has index(advertiser_id,activity_date),(activity_date,advertiser_id,activity_type).
The table is looks as following:

activity_date        advertiser_id activity_type message
2014-06-01 00:00:16  80149         11            Raised max bid
2014-06-01 00:00:16  80149         12            Set Bid Management
2014-06-01 00:01:49  80149         11            Raised max bid
2014-06-01 00:01:49  80149         12            Set Bid Management
2014-06-01 00:03:17  37            12            Lowered daily budget
2014-06-01 00:03:17  59            11            Lowered max bid
2014-06-01 00:03:17  59            14            Raised daily budget

Each row is an activity. I try to write SQL for each time one aid of activity_type:11(UPDATE_AD_BID), count the all other actions of this aid within one day. if one aid have a activity_type:11(UPDATE_AD_BID) at 2014-03-14 16:21:02, I need all the activity of this aid from 2014-03-13 16:21:02 to 2014-03-14 16:21:02. The SQL query I wrote is as following

SELECT bid_change.advertiser_id,bid_change.activity_date,
    CASE bid_change.activity_type WHEN 11 THEN IF(bid_change.message Like '%Raised%', 1, -1) ELSE 0 END as bid,
    sum(CASE change_type.activity_type WHEN 11 THEN 1 ELSE 0 END) as UPDATE_AD_BID,
    sum(CASE change_type.activity_type WHEN 12 THEN 1 ELSE 0 END) as UPDATE_AD_BUDGET,
    sum(CASE change_type.activity_type WHEN 14 THEN 1 ELSE 0 END) as CREATIVE,
FROM (select * from post_funnel) as bid_change
LEFT JOIN (select * from post_funnel) as change_type
ON bid_change.advertiser_id = change_type.advertiser_id
AND change_type.activity_date BETWEEN bid_change.activity_date - INTERVAL 1 DAY AND bid_change.activity_date -INTERVAL 1 SECOND
WHERE bid_change.activity_type = 11
GROUP BY bid_change.advertiser_id, bid_change.activity_date;

The SQL work fine with small test data set. But when I use it with large data set. It took forever to run. Is there anyone I can make the SQL more efficient? or I need to look for a big data solution with Hadoop or so?

update:
output should be as following

activity_date advertiser_id bid  UPDATE_AD_BID UPDATE_AD_BUDGET CREATIVE
2014-06-01 00:00:16  80149  1    0             0                0
2014-06-01 00:01:49  80149  1    1             1                0
2014-06-01 00:03:17  59     -1   0             0                0

Some notes:

No need for the derived tables. Most, if not all MySQL versions will try to materialize the subresults of the derived tables and the subsequent join operations will not be able to use any indexes. Check the EXPLAIN output to verify this. Moreover, if the resulting tables do not fit in memory, they will be saved temporarily into disk, causing more delay.
I think that two indexes, on (activity_type, advertiser_id, activity_date) and on (advertiser_id, activity_date, activity_type) will be more useful than those you have. The first will be used for the bid_change and the second for joining the post_funnel.
If the message column is not some huge varchar or text datatype, including it in the 1st (only) above index will further improve efficiency.
I strongly suggest you don't use BETWEEN for date and datetime comparisons but inclusive-exclusive ranges (qt >= start_datetime AND qt < end_datetime).
(detail) No need to use CASE bid_change.activity_type WHEN 11 in the SELECT when you have WHERE bid_change.activity_type = 11 in the query.
(detail) You can rewrite the other CASE using either COUNT() instead of SUM() or using the SUM(condition) mysql trick which makes them a bit simpler:

In all, the query can be rewritten:

SELECT
    bid_change.advertiser_id,
    bid_change.activity_date,
    IF(bid_change.message LIKE '%Raised%', 1, -1) AS bid,
    SUM(change_type.activity_type = 11)           AS update_ad_bid,
    SUM(change_type.activity_type = 12)           AS update_ad_budget,
    SUM(change_type.activity_type = 14)           AS creative
FROM
    post_funnel AS bid_change
  LEFT JOIN
    post_funnel AS change_type
      ON  change_type.advertiser_id = bid_change.advertiser_id
      AND change_type.activity_date >= bid_change.activity_date - INTERVAL 1 DAY
      AND change_type.activity_date < bid_change.activity_date
WHERE
    bid_change.activity_type = 11
GROUP BY
    bid_change.advertiser_id,
    bid_change.activity_date ;


Oracle analytic functions - how to get the highest RANKing row? Test data:

create table test (
  grp varchar2(16)
, mbr varchar2(16)
, reading1 number
, reading2 number
);

-- group A: 3 members, 1 duplicate set
-- group B: 2 members, 1 duplicate, one reading NULL
-- group C: 2 members, no repeats, no NULLs
begin
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'x', '1.0', '2.0' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'y', '1.1', '2.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'z', '1.2', '2.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'x', '1.0', '2.0' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'y', '1.1', '2.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'z', '1.2', '2.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'B', 'y', '20.2', null ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'B', 'x', '20.4', '40.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'B', 'y', '20.2', null ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'B', 'x', '20.4', '40.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'C', 'r', '100.1', '200.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'C', 's', '100.2', '200.4' ) ;
end;
/

See dbfiddle.

select * from test;
GRP  MBR  READING1  READING2
A    x    1         2
A    y    1.1       2.2
A    z    1.2       2.4
A    x    1         2
A    y    1.1       2.2
A    z    1.2       2.4
B    y    20.2      NULL
B    x    20.4      40.4
B    y    20.2      NULL
B    x    20.4      40.4
C    r    100.1     200.2
C    s    100.2     200.4

Problem:

Write a query that does all of the following:

{1} Find unique rows.

{2} Find the last 2 members (mbr) of each group (grp).  Assumption: when the members are put into alphabetical order, the last member is the one with the last letter (eg if we have 'x','y','z', the last letter is 'z').  Do not hard-code the letter into the query.

{3} Perform the following calculation: when rows are grouped (according to their grp letter), for each row containing the last letter:
reading1 - preceding reading2 ( ie reading2 of the row containing the letter 'y' ), and reading2 - preceding reading1.  Treat NULLs as 0.

Using our sample/test data:

-- {1}
GRP  MBR  R1     R2
A    x    1      2
A    y    1.1    2.2
A    z    1.2    2.4
B    x    20.4   40.4
B    y    20.2   0
C    r    100.1  200.2
C    s    100.2  200.4

-- {2}
GRP  MBR  RESULT1  RESULT2  RANK_
A    x    1        2        1
A    y    -0.9     1.2      2
A    z    -1       1.3      3
B    x    18       39.2     1
B    y    -20.2    -20.4    2
C    r    100.1    180      1
C    s    -100     100.3    2

-- {3} required/final result
grp  result1  result2
A      -1.0      1.3    -- (result1: 1.2-2.2)     (result2: 2.4-1.1)
B     -20.2    -20.4    -- (result1: 20.2-40.4)   (result2: 0-20.4)
C    -100.0    100.3    -- (result1: 100.2-200.2) (result2: 200.4-100.3)

Existing code:

This query returns result set {2}.

-- {2}
  select
    grp
  , mbr
  , r1 - lag( r2, 1, 0 ) over ( order by grp ) as result1
  , r2 - lag( r1, 1, 0 ) over ( order by grp ) as result2
  , rank() over ( partition by grp order by mbr ) as rank_
  from
  (
    select distinct
      grp
    , mbr
    , nvl( reading1, 0 ) r1
    , nvl( reading2, 0 ) r2
    from test
    order by grp, mbr
  ) ;

Question:
How can we get to resultset {3}, without using hard-coded values (such as rank_ = 2 in a WHERE clause)?
Not sure whether RANK() is needed (for the final query) at all...

I fail to see the point of a requirements such as avoiding WHERE rank_ =, but here it is, without RANK(), or hardcoding a constant (still, hardcoding is done by using FIRST_VALUE):

select distinct grp,
  first_value(result1) over (partition by grp order by mbr desc) as result1,
  first_value(result2) over (partition by grp order by mbr desc) as result2
from (
select
  grp, mbr,
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
);

GRP                 RESULT1    RESULT2
---------------- ---------- ----------
A                        -1        1.3
B                     -20.2      -20.4
C                      -100      100.3

When this is easier to read in my opinion:

select grp, result1, result2 from (
select
  grp,
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2,
  rank() over (partition by grp order by mbr desc) as rank_
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
) where rank_ = 1
;


Error: 18456, Severity: 14, State: 46 I am facing the below error :

Login failed for user 'NT AUTHORITY\SYSTEM' :
Reason: Failed to open the explicitly specified database.
Reason: Failed to open the database configured in the login object while revalidating the login on the connection.

Few points here :

Login NT AUTHORITY\SYSTEM has SYSADMIN Role along with Public Role
Default Database is Master for NT AUTHORITY\SYSTEM Login .
Master DB is Online and AutoClose is False
The Database which the login is trying to access is a temporary Database and is in Single user mode for last 15 days failing with above error.
When I check SP_HELPDB it doesnot show the Database where as SYS.Database shows the database in name column ?
When I tried to set MULTI_USER it says DB is already open and can have only one user at a time and database is in single user mode .

I have checked Aaron Bertrands post on login issues and found below :

Please help me what else I am missing ?

Do I need to change the default database to the database which the login is trying to access ?

Please advice .

Reason: Failed to open the explicitly specified database.
...
The Database which the login is trying to access is a temporary Database
and is in Single user mode for last 15 days failing with above error.

The error clearly says that the database specified in the connection string cannot be accessed.
So you have these possibilities:

change the connection string with master database
make your database accessible, put it in multi_user


How does "OtherServer" in SSDT database references work? I want to create a SSDT database project that is supposed to be deployed on a Sql Server that has several linked servers to other servers. The database ("MyBusinessDB") on those remote servers is in another SSDT solution, so I have this as "MyBusinessDB.dacpac".

Since we are allowed to add references to other databases or DACPAC files to correctly reference database objects, I would like to add the MyBusinessDB.dacpac as a reference to my project.

This article by Peter Schott explains the basics about adding database references.

There it's also possible to define that the referenced database resides on "Other/Different Server".

I referenced the MyBusinessDB.dacpac file and configured it to be on "same server". It's possible to use it's schema objects and reference them in Sql code.

But since I want to use this database on remote servers, I need to add a linked server name in advance. The Linked Server name should be variable because there are several instances. Unfortunately as soon as I configure the database reference to be on another/different server, visual studio fails to validate the schema objects of the referenced MyBusinessDB.dacpac file.

I tried different syntax, also the suggested syntax by the reference creation dialog itself (sample/suggestion is visible on bottom of this window):

SELECT * FROM [$(OtherServer)].[$(MyBusinessDB)].[Schema1].[Table1]

My naive expectation is that it's understood as: "I know what you mean, you are referencing the MyBusinessDB.dacpac by the variable "MyBusinessDB" and telling me that you want to use this on "OtherServer" you are going to fill with a value lateron..."

But using this I also get compiler errors. The dacpac's schema is never resolved.

Since I did just reference the exact same dacpac file and just added another variable (servername) in front of it, why is this failing to correctly read the dacpac's schema? How should this "feature" be used correctly?

I just tried this locally and it resolves fine and also complains about the one column that doesn't exist.

Check your reference properties I have highlighted the ones used in the four part name.


How to find the logical ordering of a page When I have a clustered index with logical ordering ASC, is there a way to find the logical ordering inside a data page in the output of DBCC PAGE?

The row offset array in an index leaf node page determines the logical order of rows within the page, which may differ from the physical order. Below is a demo script that uses the undocumented DBCC IND. Run only on  a test system.

USE tempdb;

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.ClusteredIndexExample', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.ClusteredIndexExample;
END

CREATE TABLE dbo.ClusteredIndexExample(
     KeyData varchar(10) NOT NULL
        CONSTRAINT PK_ClusteredIndexExample PRIMARY KEY CLUSTERED
    ,OtherData varchar(1000) NOT NULL
    );
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 1', REPLICATE('1', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 3', REPLICATE('3', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 4', REPLICATE('4', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 2', REPLICATE('2', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 5', REPLICATE('5', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 6', REPLICATE('6', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 7', REPLICATE('7', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 8', REPLICATE('8', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 9', REPLICATE('9', 1000));

IF OBJECT_ID(N'tempdb..#dbcc_results', 'U') IS NOT NULL
BEGIN
    DROP TABLE #dbcc_results;
END

CREATE TABLE #dbcc_results(
     PageFID    int
    ,PagePID int
    ,IAMFID int
    ,IAMPID int
    ,ObjectID int
    ,IndexID int
    ,PartitionNumber int
    ,PartitionID bigint
    ,iam_chain_type varchar(20)
    ,PageType int
    ,IndexLevel int
    ,NextPageFID int
    ,NextPagePID int
    ,PrevPageFID int
    ,PrevPagePID int
    );

DECLARE
     @DBID int
    ,@ObjectID int
    ,@PagePID int
    ,@PageFID int;

SELECT
     @DBID = DB_ID()
    ,@ObjectID = OBJECT_ID(N'dbo.ClusteredIndexExample');

INSERT INTO #dbcc_results
    EXEC sp_executesql N'DBCC IND(@DBID, @ObjectID, 1, 1) WITH NO_INFOMSGS;'
        ,N'@DBID int, @ObjectID int'
        ,@DBID
        ,@ObjectID = @ObjectID;

--return DBCC results to client
DBCC TRACEON(3604) WITH NO_INFOMSGS;

--get data pages for this table, following forward pointers in logical order
DECLARE data_pages CURSOR LOCAL FAST_FORWARD FOR
WITH data_pages AS (
    SELECT
         PageFID
        ,PagePID
        ,0 AS PageSequence
    FROM #dbcc_results
    WHERE
        PageType = 1 --data page
        AND PrevPageFID = 0
    UNION ALL
    SELECT
         #dbcc_results.PageFID
        ,#dbcc_results.PagePID
        ,data_pages.PageSequence + 1 AS PageSequence
    FROM #dbcc_results
    JOIN data_pages ON
        data_pages.PageFID = #dbcc_results.PrevPageFID
        AND data_pages.PagePID = #dbcc_results.PrevPagePID
    )
SELECT
     PageFID
    ,PagePID
FROM data_pages;

OPEN data_pages;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM data_pages INTO @PageFID, @PagePID;
    IF @@FETCH_STATUS = -1 BREAK;

    --dump page: NOTE physical order within page may be different than logical order
    DBCC PAGE(@DBID, @PageFID, @PagePID, 2) WITH NO_INFOMSGS;

END
CLOSE data_pages;
DEALLOCATE data_pages;

--don't return DBCC results to client any more
DBCC TRACEOFF(3604) WITH NO_INFOMSGS;

--cleanup
DROP TABLE dbo.ClusteredIndexExample, #dbcc_results;
GO