Showing all values when at least match a given one

You need 2 joins to itemGenres, separating the "check" logic (WHERE genres.id IN (1)) and the "gathering of all related genres":

SELECT
  items.*,
  GROUP_CONCAT(genres.id ORDER BY genres.id)    AS genreIds,
  GROUP_CONCAT(genres.genre ORDER BY genres.id) AS genres
FROM items
  LEFT JOIN itemGenres ON
    items.id = itemGenres.itemId
  LEFT JOIN genres ON
    genres.id = itemGenres.genreId
WHERE EXISTS
  ( SELECT *
    FROM itemGenres AS genre_check
    WHERE genre_check.itemId = items.id
      AND genre_check.genreId IN (1)
  )
GROUP BY
  items.id ;

The LEFT joins can be replaced with INNER joins, the results would be identical.

Actually, it could be done with a change of your query, moving the check from WHERE to HAVING but the condition has to be modified:

SELECT
  items.*,
  GROUP_CONCAT(genres.id ORDER BY genres.id)    AS genreIds,
  GROUP_CONCAT(genres.genre ORDER BY genres.id) AS genres
FROM items
  JOIN itemGenres ON
    items.id = itemGenres.itemId
  JOIN genres ON
    genres.id = itemGenres.genreId
GROUP BY
  items.id
HAVING
  COUNT(CASE WHEN itemGenres.genreId IN (1) THEN 1 END) > 0 ;

My personal preference would be the 1st solution but you can test in your database, which of the two is more efficient.


Does MySQL or MariaDB support double literals (double precision)?

Though not in the docs explicitly on Numeric Literals. You can make a double precision literal with scientific notation (using e0) which seems to work,

MariaDB [test]> CREATE TABLE foo AS SELECT 1.0e0 AS "se_decimal", 1e0 AS "se_nodecimal", 1.0 AS "decimal";
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> SHOW CREATE TABLE foo;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `se_decimal` double NOT NULL,
  `se_nodecimal` double NOT NULL,
  `decimal` decimal(2,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This is actually supported by the SQL Spec, which shifts the literal from <exact numeric literal> to <approximate numeric literal> when it sees the e or E. From SQL 2011n spec,

<approximate numeric literal> ::=
  <mantissa> E <exponent>

This is what the docs on Numeric Literals presumably refer to when it says,

  Approximate-value numeric literals are represented in scientific notation with a mantissa and exponent. Either or both parts may be signed. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Though the docs don't say the type, it's double (see above).

See also,

How does the SQL Spec specify literals with a decimal should be handled?


Rewriting query with subquery to improve performance

topics: INDEX(bumped, date) -- in that order
answers: INDEX(topic_id)

Please provide SHOW CREATE TABLE if you need to discuss this further.

500 is a lot of results -- more than a self-respecting UI should return.  What will you do with them?

Also Consider this approach:

SELECT  topics.name, topics.topic_id, topics.creator, replies.ct
    FROM
    (
        SELECT  topic_id, count(1) AS ct
            FROM  answers
            GROUP BY topic_id
    ) AS replies
    JOIN  topics  ON replies.topic_id = topics.topic_id
    ORDER BY  topics.bumped DESC, topics.date DESC
    LIMIT  500

It may run faster because of reaching into answers only once.

If, however, there could be zero replies in some cases, you need a LEFT JOIN:

SELECT  topics.name, topics.topic_id, topics.creator, replies.ct
    FROM  topics
    LEFT JOIN (
        SELECT  topic_id, count(1) AS ct
            FROM  answers
            GROUP BY topic_id
              ) AS replies  ON replies.topic_id = topics.topic_id
    ORDER BY  topics.bumped DESC, topics.date DESC
    LIMIT  500

This does need a relatively new version of MySQL so that the derived table can automatically be given an index.

Which approach is fastest?  I can't tell; try them all.


How to create Relationship between multiple databases?

Because all databases (schemas) on same server You can operate with data easy - just add schema name before table, like:

SELECT t1.* FROM database1.table1 t1 LEFT JOIN database2.table1 t2 ON t1.id = t2.id

same for compare and save result, like

INSERT INTO database2.table3
SELECT * FROM database1.table1 t1 WHERE NOT EXISTS (SELECT id FROM database2.table1 t2 WHERE t2.id = t1.id)

and etc

MySQL workbench can help You with compare schema

You can also use - mysqldbcompare


Can I have more than one db_recovery_file_dest?

You can have only one Flash Recovery Area defined by the db_recovery_file_dest. If you do not want to use one directory then you can go for ASM where you can create and use a disk group that can span multiple disks/partitions.

You can also go for LVM if you use Linux. This is simular to ASM. Create a physical group that contains multiple partitions. On that physical group you create a logical volume and mount it to a directory.


Why SQL Server Migration Assistant keeps crashing on the 'Migrate Data' step?

One more thing that helped me in addition to setting affinity to Core 0 only:

SSMA stopped crashing after I set Parallel Data Migration Thread Count to 1.
You can find it in Tools > Project settings > Genaral > Migration (in the left pane) > Parallel Data Migration. Set Parallel data migration mode to Custom and then Thread count to 1.

My current version is 7.0.0, MySQL ODBC Driver version is 5.3.6.


Try commenting out the echoing of the uid

create procedure TigAddUser(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)
begin
    declare res_uid bigint unsigned;

    insert into tig_users (user_id, sha1_user_id, user_pw)
        values (_user_id, sha1(lower(_user_id)), _user_pw);

    select LAST_INSERT_ID() into res_uid;

    insert into tig_nodes (parent_nid, uid, node)
        values (NULL, res_uid, 'root');

    if _user_pw is NULL then
        update tig_users set account_status = -1 where uid = res_uid;
    end if;

    -- select res_uid as uid;
end //

Try throttling the output of the ids in groups of 1250

create procedure TigUserGen(usr_count INT)
begin
    DECLARE cnt,len,echo_count,echo_limit INT;
    DECLARE usr CHAR(6);
    SET cnt = 0;
    SET len = 0;
    SET usr = '';

    SET echo_limit = 1250;
    SET echo_count = 0;
    WHILE cnt < usr_count DO
        SET cnt = cnt + 1;
        SET echo_count = echo_count + 1;
        SET usr = CAST(cnt AS CHAR(6));
        SET len = CHAR_LENGTH(usr);
        CASE len
            WHEN 1 THEN
                call TigTestAddUser(CONCAT('+39100000', usr), CONCAT('pwd_00000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 2 THEN
                call TigTestAddUser(CONCAT('+3910000', usr), CONCAT('pwd_0000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 3 THEN
                call TigTestAddUser(CONCAT('+391000', usr), CONCAT('pwd_000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 4 THEN
                call TigTestAddUser(CONCAT('+39100', usr), CONCAT('pwd_00', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 5 THEN
                call TigTestAddUser(CONCAT('+3910', usr), CONCAT('pwd_0', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            ELSE
                call TigTestAddUser(CONCAT('+391', usr), CONCAT('pwd_', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
        END CASE;
        IF echo_count >= echo_limit THEN
            SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
            SET echo_count = 0;
        END IF;
    END WHILE;
    IF echo_count >= 0 THEN
        SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
    END IF;
end //

delimiter ;

call TigUserGen(200000);


How does optimistic locking actually enforce re-read/update?

The basic technique is quite straightforward.  When you read the record you take a note of the version or timestamp column, e.g.

Select FooID
      ,Foo
      ,Bar
      ,TS      -- timestamp
  from Foobar
 where FooID = @FooID

When you go to write out the record you filter the write by the timestamp/version so that the write writes nothing if the timestamp/version has changed.  This makes the write atomic, e.g.

update Foo
   set Foo = @foo
      ,Bar = @bar
      ,TS = @timestamp
 where FooID = @FooID
   and ts = @timestamp

select @row_count = @@rowcount  -- specific to t-sql, but this is a system variable
                                -- that holds the number of rows affected by the
if @@rowcount = 0               -- most recent operation.  Other DBMS platforms do
    [deal with outdated record] -- this differently.

This allows an application to do the update without holding locks open.  This is necessary
for n-tier systems working through a connection pool, and prevents a class of deadlocks
that used to be common on two-tier client-server systems.

There is nothing enforced in the database about this.  It's all done explicitly by the application.


Why full index scan over full table scan?

If the index contains both cid and email, there is no need to look at the records.  All the data will be retrieved from the index.  This is a common optimization.

The where clause should (on average) reduce the record count to less than four percent of the records.  This should be a small enough set to make using the index faster than table scanning.  Different engines have a different cost cutover point. Matching the wildcard on the index is likely faster than than matching against the record (column position matters).

In a case like this, the optimizer will be guessing data distribution.  The optimizer likely doesn't have stats on a trailing match, so it will likely be guessing based on 5 random characters.  This would severely tilt the query in favor of an index scan, as the expectation is that only a small (much less than 1) percentage of records will match.


Inserting BLOBs through PHP are always 0 bytes

After a lot of searching, I found the keyword, send_long_data.

$stmt = $conn->prepare($SQL);
$stmt->bind_param('ssb', $name, $desc, $image);
$stmt->send_long_data(2, $image);

$stmt->execute();

A simple fix if you know the magic word.


SQL Server does not failback to the primary node

I set up a virtual lab environment simulating a clustered network where I unticked one of the nodes as the preferred owner. I can confirm that whenever the ticked node goes offline, the unticked one comes into play and the system failbacks onto the ticked node once it comes online again.


Finding stored procedures with missing indexes

Having not thought a whole lot about this, and answering just the specific question asked, here is a way to identify all the stored procedures that reference tables that have at some point been identified as candidates for additional indexes. Unfortunately this is a very loose relationship ... I don't believe there is any straightforward way to say "this stored procedure queried this table and could have used this suggested index" unless you started parsing the sql text and fuzzy matching against the columns etc. recommended by the missing index DMVs. This is a start toward that process. That said, I kind of agree with Henry, there are plenty of more direct ways to analyze and address performance problems in stored procedures you have identified. Examining the execution plan, for example, should provide some clues much more intuitively than this round-about method you want to pursue. In any case, here is a query to start you off:

SELECT
  [Procedure] = QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id]))
        + '.' + QUOTENAME(OBJECT_NAME(s.[object_id])),
  [Table]     = QUOTENAME(r.referenced_schema_name)
        + '.' + QUOTENAME(r.referenced_entity_name),
  i.equality_columns,
  i.inequality_columns,
  i.included_columns,
  s.execution_count,
  avg_time = s.total_elapsed_time*1.0/s.execution_count
FROM sys.dm_exec_procedure_stats AS s
CROSS APPLY sys.dm_sql_referenced_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id]))
  + '.' + QUOTENAME(OBJECT_NAME(s.[object_id])), 'OBJECT'
) AS r
INNER JOIN sys.dm_db_missing_index_details AS i
ON i.[object_id] = r.referenced_id
WHERE r.referenced_minor_id = 0;

Note: the DMVs usually only have data since the last restart.


Try This one

DROP PROCEDURE IF EXISTS daily_backup;

DELIMITER //

CREATE PROCEDURE daily_backup()
BEGIN
    DECLARE given_datetime VARCHAR(25);
    DECLARE backup_table VARCHAR(64);

    SET given_datetime = DATE_FORMAT(NOW(),'%Y%m%d_%H%i%s');

    /**We need to add backticks to forcibly create table name as current date and time **/
    SET backup_table = CONCAT('db.table1_',given_datetime);
    SET @modified_date = CONCAT('`',backup_table,'`');
    SET @create_sql := CONCAT('CREATE TABLE ', @modified_date, ' LIKE db1.table1;');
    prepare stmt from @create_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @insert_sql := CONCAT('INSERT INTO ', @modified_date, ' SELECT * FROM db1.table1;');
    prepare stmt1 from @insert_sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

END //
DELIMITER ;
CALL daily_backup();

UPDATE

  We cant create table directly as CREATE TABLE 2013-06-14 16:58:37 LIKE db1.table;

  But we can create it forcibly by adding backtics in table name as

  CREATE TABLE 2013-06-14 16:58:37 LIKE db1.table;

UPDATE by RolandoMySQLDBA

  I changed the code to have datetime appended to the backup.

  The backup will be named db1.table1_YYYMMDD_HHMMSS

  The algorithm itself is fine (+1) !!!


Presumably you have business logic that relates to how you process data in the DB. That data is likely to be table based and the tables are likely to be per business entity.

For example, there might be a table that holds details of customer accounts. Therefore all business logic pertaining to creating, maintaining, validating, listing, etc. of customer accounts would exist in the package PKG_ACCOUNTS.

Without knowing how granular or generic your procedures are trying to be its hard to see how you would need something more that that. That sort of approach, with packages covering logic that can apply to multiple objects, has always worked very well for me.

Perhaps some examples would help if you don't see that this is sufficient.


Sql server table insert performance optimization

What are the possible reasons of the slow table insert?
  What are ways to identify this bottleneck without the execution plan?

Read How to analyse SQL Server performance, specially the part about Analyzing individual query execution wait times.

  What actions can I take to reduce the cost of the table insert?

That would depend largely on the result of the performance analysis. First and foremost, ensure the SELECT part is as fast as possible. Assuming that problem is the single threaded fully logged insert, some solutions are:

Use partition switch to move 'in' the data. This is, by far, the best solution. Prepare the staging data in a separate staging table, then switch this staging table into the DW table. Read Transferring Data Efficiently by Using Partition Switching.
Make sure the INSERT is minimally logged. Read Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging. Even if you use partition switch operations, is still worth making sure the build of the staging table is minimally logged.
Make sure your IO subsystem is capable of driving a fast load. Read Introducing SSDs.


SQL Errorlog Constant FlushCache Messages

Switch to indirect checkpoints (generally, set recovery interval time to 60 seconds, at least for user databases).
I wrote about this issue here and here, and you should read those posts in full before making the change, though it’s pretty much a no-brainer IMHO.
The impact in our environment was drastic and immediate, and it was simple to prove the change was responsible. Every database with the symptom went from 30-60 second checkpoints, and errorlogs full of these messages, to sub-second checkpoints and no more errorlog entries.
There are probably mitigation techniques for your workload, too, for example see this post by Itzik Ben-Gan but making checkpoints more efficient is a quicker and easier path. Your storage and VM folks aren’t off the hook, though; there is definitely a disk issue they need to address (but I disagree that these FlushCache messages were the root cause of any failover; more likely they are just a different victim/symptom).


Update: How to update the first two rows with a Y flag

You don't need a cursor for this:

update IMAGES
  set OMG='Y'
where image_identifier in (select image_identifier
                             from (select image_identifier
                                     from IMAGES
                                    order by IMAGE_IDENTIFIER)
                            where rownum<=2)

If you want 2 rows per TP_IDENTIFIER, I'd go for a window function:

update IMAGES
  set OMG='Y'
where exists   (select 1
                  from (select image_identifier,tp_identifier,
                               row_number() over (partition by tp_identifier order by image_identifier) as rn
                          from IMAGES
                       ) t
                 where t.image_identifier=IMAGES.image_identifier
                   and t.tp_identifier = IMAGES.tp_identifier
                   and rn<=2)


Find what values are not in multiple columns

Try this:
with tRMS_CODE as
   (SELECT DISTINCT RMS_CODE FROM RMS_Table)
   , tRMS_ALT1_Cde as
   (SELECT DISTINCT RMS_ALT1_Cde FROM RMS_Table)
SELECT DISTINCT * FROM RMS_Table
WHERE
   SUBSTR(CHILD,6,4) NOT IN (SELECT * FROM tRMS_CODE) AND
   SUBSTR(CHILD,6,4) NOT IN (SELECT * FROM tRMS_ALT1_Cde) AND
   APC_Spec='36559'


If you have something THAT similar and you need to call it more than once or twice, I would put it into another stored proc that you can call:

CREATE PROCEDURE dbo.InsertStuff (@id int, @org varchar(100), @name varchar(100), @lang int)
AS

DECLARE @SQL nvarchar(max)

SET @SQL = 'SELECT id = ' + @id + ' ' +
           'org = ''' + @org + ''' ' +
           'name = ''' + @name + ''' ' +
           'lang = ''' + @lang + ''' ' +
           'cnt 1 = CASE WHEN Col1 = 1 THEN 1 ELSE 2'
           -- All your other common stuff here

EXEC (@SQL)


Is this a new common pattern in Oracle Where exists ( Select NULL FROM...)?

Short answer: I would hope not!

Even the SELECT 1 idiom may have outlived its usefulness, as database engines have advanced enough to recognize that you don't really want the rows, you just want to know if the rows exist. I've looked at execution plans in SQL 2005, and have generally seen these re-written as joins, even where SELECT * is used.


It's like every sp_executesql call triggers a compile but the query plan is not cached.

SQL Server does not cache a query plan for batches containing only an sp_executesql call. Without a cached plan, a compilation occurs each time. This is by design, and expected.

SQL Server avoids caching batches with a low cost to compile. The details of what is and is not cached has changed many times over the years. See my answer to Trace flag 2861 and what a 'zero-cost' plan actually means for details.

In short, the probability of reuse (including specific parameter values) is small, and the cost of compiling the ad hoc text containing the sp_executesql call is very small. The inner parameterized batch produced by sp_executesql is of course cached and reused - this is the value of it. The extended stored procedure sp_executesql itself is also cached.

To be cached and reused, the sp_executesql statement would have to be part of a larger batch that is considered worth caching. For example:

-- Show compilation counter
SELECT
    DOPC.[object_name],
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.counter_name = N'SQL Compilations/sec'
GO
-- This is only here to make the batch worth caching
DECLARE @TC integer =
(
    SELECT TOP (1) @@TRANCOUNT
    FROM master.dbo.spt_values AS SV
);

-- Example call we are testing
-- (use anything for the inner query, this example uses the Stack Overflow database
EXECUTE sys.sp_executesql
    N'SELECT LT.Type FROM dbo.LinkTypes AS LT WHERE LT.Id = @id;',
    N'@id int',
    @id = 1;
GO
-- Show compilation counter again
SELECT
    DOPC.[object_name],
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.counter_name = N'SQL Compilations/sec'

Run that code several times. On the first time though, many compilations are reported as expected. On the second time, no compilations are reported, unless optimize for ad hoc workloads is enabled (so only a Compiled Plan Stub is cached). On the third time, no compilations are reported in any case, since any stub is promoted to a fully cached ad hoc plan.

Remove the DECLARE @TC statement to see that the sys.sp_executesql statement is never cached without it, regardless of the number of times it is executed.

View the associated plan cache entries with:

-- Show cached plans
SELECT
    DECP.refcounts,
    DECP.usecounts,
    DECP.size_in_bytes,
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DECP.parent_plan_handle,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    DEST.[text] LIKE N'%sp_executesql%'
    AND DEST.[text] NOT LIKE N'%dm_exec_cached_plans%';

Related Q & A: Do triggers compile each time?


Update and Insert Statement taking forever

Thanks for updating that you are using Microsoft SQL Server, which does support Full Text Indexing. (As do many other versions of SQL servers.)

First of all your query is using a WHERE (Word LIKE @partialWord) which means that if there is a prefix wildcard (e.g. '%tion') the whole table has to be scanned and the contents of the Word column likewise scanned.

That is the base cost of this query.

Therefore, you need to investigate some other method of finding the partial words. For example, full text indexing (if available to you) may give you other options to use when finding %partial% words.

Create a full text index on the Predictions table for the Word column. This can be done through SSMS or by a script. This will populate the keywords table in the FTI catalog tables. Example from: http://msdn.microsoft.com/en-us/library/ms187317.aspx

CREATE UNIQUE INDEX pk_PredictionsID ON dbo.Predictions(PredictionsID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Predictions(Word)
   KEY INDEX pk_PredictionsID
   WITH STOPLIST = OFF;

Then here is a sample script that would help you to then find all the rows with wildcards.

DECLARE @SearchText NVARCHAR(2000)
SET @SearchText = ''

DECLARE @Pattern NVARCHAR(100)
SET @Pattern = '%colat%'

SELECT @SearchText = @SearchText + display_term + ' OR '
FROM sys.dm_fts_index_keywords ( DB_ID('DBName'),
    object_id('Predictions') )
   WHERE display_term like @Pattern

SELECT @SearchText = LEFT (@SearchText, LEN(@SearchText) - 3)
-- Contents "chocolate OR percolator" in my test case

SELECT * FROM Predictions
WHERE CONTAINS(*, @SearchText)

By building a full text query string that includes all strings containing the pattern joined by the OR operator, it allows Full Text Search to then directly access the rows that contain one (or more) of the words.  This should be a lot faster than scanning the table.


This can work for you:

SELECT *
FROM Persons a
LEFT JOIN Employee b ON 1=2
UNION
SELECT *
FROM Persons a
RIGHT JOIN Employee b ON 1=2;


Could a null column be part of a primary key?

To answer the question in the title, no, all the primary columns have to be NOT NULL.

But without altering the design of the tables, you could add a filtered index on the Code (HelperCodeId) column:

CREATE UNIQUE INDEX
    FUX_Code_HelperCodeId
ON dbo.Code
    (HelperCodeId)
WHERE
    HelperCodeId IS NOT NULL ;

The filter (WHERE HelperCodeId IS NOT NULL) is needed because of the way SQL-Server treats nulls in unique constraints and unique indexes. Without the filter, SQL-Server would not allow more than one row with NULL in HelperCodeId.

An alternative design would be remove the HelperCodeId from Code and add a third table that will store the Code - HelperCode relationships. The relationship between the two entities seem to be Zero-or-One--to--Zero-or-One (both a Code can have no HelperCode and a HelperCode might be used by no Code):

CREATE TABLE [dbo].[Code]
(
    [Id] NVARCHAR(20) NOT NULL,
    [Level] TINYINT NOT NULL,
    [CommissioningFlag] TINYINT NOT NULL,
    [SentToRanger] BIT NOT NULL DEFAULT 0,
    [LastChange] NVARCHAR(50) NOT NULL,
    [UserName] NVARCHAR(50) NOT NULL,
    [Source] NVARCHAR(50) NOT NULL,
    [Reason] NVARCHAR(200) NULL,
    --
    -- removed:   [HelperCodeId] NVARCHAR(20) NULL,
    --
    CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ),
    CONSTRAINT [FK_Code_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level]),
) ;

HelperCode remains unchanged:

CREATE TABLE [dbo].[HelperCode]
(
    [HelperCodeId] NVARCHAR(20) NOT NULL,
    [Level] TINYINT NOT NULL,
    [CommissioningFlag] TINYINT NOT NULL,
    [LastChange] NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_HelperCode] PRIMARY KEY CLUSTERED
    (
        [HelperCodeId] ASC
    ),
    CONSTRAINT [FK_HelperCode_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level])
) ;

The additional table will have two UNIQUE contraints (or one primary and one unique) to ensure that every Code is related to (maximum) one HelperCode and each HelperCode is related to (maximum) one Code. Both columns would be NOT NULL:

CREATE TABLE [dbo].[Code_HelperCode]
(
    [CodeId] NVARCHAR(20) NOT NULL,
    [HelperCodeId] NVARCHAR(20) NOT NULL,
    CONSTRAINT [UQ_Code_HelperCode_CodeId]
       UNIQUE (CodeId),
    CONSTRAINT [UQ_Code_HelperCode_HelperCodeId]
       UNIQUE (HelperCodeId),
    CONSTRAINT [FK_HelperCode_Code]
       FOREIGN KEY ([CodeId])
        REFERENCES [dbo].[Code] ([Id]),
    CONSTRAINT [FK_Code_HelperCode]
       FOREIGN KEY ([HelperCodeId])
        REFERENCES [dbo].[HelperCode] ([HelperCodeId])
) ;


Extremely Slow StatMan query issue with #SP2010 farm

This is SQL Server updating the statistics on a table. It can be triggered automatically when about 20% of the data in your table changes, or on demand if you run jobs that update statistics.

You have a few ways to lessen the pain.

You can enable asynchronous statistics updates, which lets SQL Server update stats behind the scenes without blocking other queries. It'll still have a performance impact, though, as Kendra Little explains in The Secret IO Explosion.

You can manually update statistics proactively during maintenance windows. This doesn't stop the chances that 20% of your data will change, but it just reduces the likelihood.

You can disable automatic updating of statistics altogether. This is generally a pretty bad idea since your execution plans may be wildly incorrect, and it doesn't stop folks from manually updating the statistics either.


Automatically add core fields to a table when created

The feature you are asking for is called "DDL triggers".

And no, MySQL doesn't have that. So you can't do this in a trigger.


If you only need to know that a row is distinct, and don't need the actual contents of col3, then perhaps returning the hash of col3 would speed up the query?

You could even perhaps pre-compute the hash using a calculated column so that you aren't computing the hash on the fly.

If you do need the contents of col3, but have a lot of duplicates of col1+col2+col3, then it still may be beneficial to work with the hash to remove duplicates as a sub-query, then only return the col3 contents for the distinct rows.


SSAS Multidimentional Drillthrough with Many-to-Many Relationships

If your drillthrough command includes a dimension attribute from a many-to-many dimension then it behaves as described: showing a row zero to many times.

The easiest solution is to create a drillthrough action and mark that action as the default drillthrough action and specify the columns you want to include in the drillthrough results. If you don't include the many-to-many dimensions then it will stop skipping or duplicating rows.


In SQL Server, how do you create a default constraint for a column using the result or return value from a Stored procedure?

You can't use a SP in a default, but you can use a function. But presumably your SP code has to increment the value in the NextNumber table, so that won't work for you.

Your best bet for doing this within SQL is probably to have an INSERT trigger on MyTable which calls the SP and sets MyColumn.

However you'll have to carefully consider the behaviour of your SP code when multiple concurrent inserts are involved.
For example, you'll probably need to select the current value from the NextNumber table using an UPDLOCK hint to prevent another user reading and using the same value. (Note that the UPDLOCK hint will only prevent reads in this way if it's within a transaction [which triggers run within in by default], and if other selects on the NextNumber table use UPDLOCK as well.)


Deprecating SQL Server, need to redirect clients to new server

We are doing exactly this at the moment in our company - decommisioning servers and moving the databases to a new infrastructure. We use a DNS alias to point to the SQL instance - the only downside is that the SQL instance must be configured to use static ports. Once this is done you can connect to a SQL instance using the following address: servername,port - this way you do not need to know the instance name. Once you have configured your application with to connect with the DNS alias moving your databases is easy - just create a copy of the database and change the IP Address that the DNS Alias points to.

There are a couple of downsides to this approach; firstly, configuring SQL Server to use static ports requires a restart of the SQL Server instance; secondly, all servers that are to take part in the moving of databases must be configured to use the same port (otherwise you would need to reconfigure the client each time).

We primarily use this for DR to be fair (although it is making the migration to new infrastructure easier) and the approach is pretty robust. When failover occurs an automated job kicks off to redirect all of the DNS Aliases to point to the new server. There will be a slight window of failure within the application while the new addresses are replicated across the domain controllers but this tends to be fairly small.

I hope this helps you.


Fixing wildcard expansion resulting in too many terms error (DRG-51030)

The wildcard_maxterm is set to the maximum. Actually, the maximum in 10g is 15,000. I think the best approach is to limit the search term. The Oracle error action is to make the query narrower. Here is Oracle's documentation on wildcards (%_). The % means zero or more characters. The _ means match in a single position. And 'WIN' is a broad term for a contains search.

I think you should try a very narrow query and then gradually broaden the query until you find right combinations. Can you remove the % before and after and try a % only at the end? Try to increase the characters before or after WIN. Try multiple queries for more specific terms. Try a fixed number of _ characters instead of a %?


What you're trying to do is to pivot your data, which - if you're on Oracle 11g or higher - you can do like so:

SELECT team,
       buyer_name,
       style_no,
       item_no,
       receipt_date,
       hour1,
       hour2,
       hour3,
       ...
FROM   (SELECT wc.wc_name AS team,
               buy.name AS buyer_name,
               itm.style_no,
               itm.item_no,
               trunc(gr.receipt_date) receipt_date,
               to_char(dt, 'hh24') receipt_date_hr,
               jrd.qty
        FROM   INNER JOIN job j ON wc.wc_id = j.wc_id
               INNER JOIN Job_Receipt_Details jrd ON j.job_id = jrd.job_id
               INNER JOIN Grn_Receipt gr ON jrd.grn_no = gr.grn_no
               INNER JOIN item itm ON jrd.item_no = itm.item_no
               INNER JOIN "ORDER" ord ON itm.order_no = ord.order_no
               INNER JOIN buyer buy ON ord.buyer_id = buy.buyer_id
        WHERE  receipt_date >= trunc(SYSDATE)
        AND    receipt_date < trunc(SYSDATE) + 1)
PIVOT (SUM(qty) FOR receipt_date_hr IN ('00' AS hour1,
                                        '01' AS hour2,
                                        '02' AS hour3,
                                        ...));

Note that I have changed your outer query's where clause to where receipt_date >= trunc(sysdate) and receipt_date < trunc(sysdate) + 1 to enable the use of any indexes involving the receipt_date column.

If there are no indexes on that column which might be used, then you could further simply it to where trunc(receipt_date) = trunc(sysdate).


More tables or more columns (in my simple example)?

Typically user would have the fk

user:
ID (pk)
Name
StatusID (fk referencing status.ID)

status:
ID (pk)
Name

A user has a status - not a status has a user


Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

Assuming your table does not have any triggers on it, you should consider using pt-online-schema-change since it will allow you to ALTER the table without locking it.

It will still take a fair amount of time given the size of the table.

Also, with this method or with ALTER TABLE you will need to make sure you have 600 GB of extra disk space to support two copies of the table while it is being rebuilt.


Is there anything wrong with splitting a complicated view into many small ones?

Is there a down-side to splitting them into multiple smaller views and joining them by customer_id?

Yes, definitely. Each view has to scan the whole underlying table on its own, and then you add 20 joins after that. The index does not apply to the derived tables you are joining. The single SELECT can make do with a single scan over the table (or index), it should be substantially cheaper.

Proof: db<>fiddle here


Does varchar size matter in temporary tables?

Depending on how you are using your temp tables, you could run into a data truncation issue.

This example is a bit contrived, but it illustrates my point.
Example:

Your user table column is varchar(50).
Your temp table column is varchar(255).
You have a record with 45 characters in that column in your user table.
In your procedure, you concatenate ' - for the win' to the end of that column, prior to merging that temp table into your user table.

The temp table would gladly accept the new varchar value with a length of 59.  However, your user table could not.  Depending on how you handle this in your procedure, this could result in truncation or an error.

Unless you document and account for these issues, your procedure could perform in an unexpected manner.

Personally, I do not think there is an answer to this question that is correct 100% of the time.  It really depends on how you are using those temp tables.

Hope this helps


It depends...

Anyone can work from home if they have the proper work ethic.  If your very self motivated and task oriented then it will work fine.  It is important that you are able to show the people that you report to that you are producing top quality work.

As a manager it is harder for me to manage remote employees because I have to work harder to make a personal connection with them and ensure that we are working well together.  I have some great people that work for me, database professionals, that work from home hundreds of miles away.


If you are talking about the file system space and releasing it back to the Operating System, then you will have to manually do this operation.

First find out how much free space you have in each file:

use YourDatabase;
go

;with file_cte as
(
    select
        name,
        physical_name,
        size_mb =
            convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb =
            convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
    from sys.database_files
)
select
    name,
    physical_name,
    size_mb,
    space_used_mb,
    free_space_mb = size_mb - space_used_mb,
    space_used_percent =
        convert(decimal(5, 2), space_used_mb / size_mb * 100)
from file_cte;

Then if you are absolutely sure that you need to shrink a database file, you can use DBCC SHRINKFILE().

To shrink your file, an example would be like this:

dbcc shrinkfile(YourDataFileName, <target_size_mb>);

But... only shrink your database files if you absolutely must.  If your files are going to re-consume that same space in the future, it doesn't make sense to shrink your files.  A necessary read is Why you should not shrink your data files by Paul Randal.


Activity Monitor showing long duration for WAITFOR RECEIVE

This is a red herring. WAITFOR (RECEIVE...) is by definition supposed to ... wait for messages! Therefore 281 seconds elapsed simply means that for 281 seconds there was no message to receive. In this case the application is the Database Mail external sending process that is sitting idle waiting for messages to be enqueued (ie. waits for sp_send_db_mail to be called). There is nothing to 'resolve' here.


Add a primary key constraint to an existing unique column

The assumption you have is that a lock will occur when creating a new table. This is false if you use the correct query hints & have a good action plan. I recommend the following:

Create a New table with identical schema but with a primary key constraint added.
INSERT INTO NewTable SELECT * FROM OldTable (this will still take a while)
Use SSIS or write a good function to identify new rows in the old table whilst the insert into the newtable was occuring and write these rows to the new table. (Essentially perform another merge ontop)
Rename/Drop the old table and rename the new table to the old table name.

On the topic of locks. You have a few options as I see:

Specify NOLOCKS or READUNCOMITTED on your query. This has its own implications that are best read here.
Take a full table lock which you've already said is less desirable but would mean step 3 of the action plan is not required.
Take shared page locks (Default for select). This means that the individual pages when being read will lock and no updates or inserts will be allowed. It does mean though that other selects on this page can still occur during this lock.

It really depends on your specific need which of these three is your best COA. I suggest consulting with business to determine if an outage is really such a bad thing and if it is, consult with the developers to determine what lock level is most appropriate for the task.


Scalar function used in calculated column, what's the cleanest way to update the function?

This may be more work but it should get (approximately) what you are after:

1 - Create a new table that has just your PK from the main table and the updated function.

2 - Drop your old column

3 - Rename your old table to something like TableName_Base

4 - Create a VIEW that joins your main table to your lookup table, with the fields in the order that you want to see them.  Name the view whatever your original table name was.

This will potentially create some issues with INSERTS and UPDATES though since you'll be accessing via a view.  To deal with that you can either insert into the table directly, bypassing the view.


Create cyclic pointers in postgresql 9.5

Your idea would be a pain to enforce under concurrent load. Instead, just keep adding new rows (INSERT only). There are simple and fast queries to get the current row(s) for each cat.
Make sure, that updated_at is current. I added a column default and a trigger for that.
To rule out duplicate entries for the same cat and the same timestamp, add a UNIQUE constraint.
CREATE TABLE cat(
  id         serial PRIMARY KEY
, cat        text NOT NULL
, value      text
, updated_at timestamptz NOT NULL DEFAULT now()
, CONSTRAINT uni_cat_updated_at UNIQUE (cat, updated_at)
);

-- trigger func & trigger
CREATE FUNCTION public.trg_cat_updated()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.updated_at = now();
   RETURN NEW;
END
$func$;

CREATE TRIGGER cat_befupd
BEFORE UPDATE ON public.cat
FOR EACH ROW EXECUTE PROCEDURE public.trg_cat_updated();

Or, to be absolutely sure, make that:
BEFORE INSERT OR UPDATE ON public.cat

The default value normally takes care of inserts cheaply, but it can be overruled with explicitly inserting a value. The trigger overrules no matter what.
To get the "current" row for a given cat:
SELECT value
FROM   cat
WHERE  cat = 'cat1'
ORDER  BY updated_at DESC
LIMIT  1;

This is extremely fast while you have a matching index:
CREATE INDEX on cat (cat, updated_at DESC, value);

In modern Postgres versions you could use a unique covering index to replace index and UNIQUE constraint. See:

Does a query with a primary key and foreign keys run faster than a query with just primary keys??

To get the last three (live) rows, use the same query with LIMIT 3:
From time to time (as your db load and schedule permit/require) delete deprecated rows:
DELETE FROM cat c
USING (
   SELECT id, row_number() OVER (PARTITION BY cat ORDER BY updated_at DESC) AS rn
   FROM cat
   ) d
WHERE  d.rn > 3
AND    c.id = d.id;

db<>fiddle here


Creating SQL Azure login for single database

Connect to master database and do the following:

CREATE LOGIN YourNewUser WITH PASSWORD = '&lt;strong password here&gt;';

Connect directly to database that the new user should own:

CREATE USER YourNewUser FOR LOGIN YourNewUser;

EXEC sp_addrolemember 'db_owner', 'YourNewUser';


How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?

1) If you want the headings in one line, you have to use a UNION with a select from dual. Example:

select '"'||'col1'||'","'||'"col2"'||'","'||'"col3"'
  from dual
union
select '"'||col1||'","'||col2||'","'||col3||'"'
  from table;

2) This one is tricky. It helps to know what ascii character your line breaks actually are. Depending on how they were written, generally (but not always) they will be chr(10) or a combination of chr(10)chr(13). You can use the REPLACE function in your query to replace those with '', which should get rid of them.

3) If you want quotes, see my example #1 above.

HTH.


Prevent users creating constraints or indexes with no name

The simplest way to do this (though it makes me shudder a bit) is to use a database level DDL trigger with EVENTDATA. There is nothing specific here for constraints, so you'd have to use your imagination (depending on what you're attempting to actually accomplish) with the DDL Event groups, and some manual parsing. Here's an example to get you started.

CREATE DATABASE IndexRollback;
GO

USE Indexrollback;
GO

CREATE TRIGGER IndexNamingTrigger
ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX
AS
BEGIN

    -- this doesn't need to be here (can be inlined), just did it for readability
    DECLARE @ObjName sysname
    SELECT @ObjName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

    -- check our naming, if it doesn't start with IDX, throw it away and be super mean about it
    IF(LEFT(@ObjName, 3) <> 'IDX')
    BEGIN
        --log to a table or something
        PRINT 'Dont''t be daft, look at the naming convention spec!'
        ROLLBACK
    END
END
GO

CREATE TABLE Test
(
ID  INT NULL
)
GO

CREATE CLUSTERED INDEX IDX1 ON dbo.Test(ID)
GO
--success

CREATE NONCLUSTERED INDEX HAHAHAHAHA ON dbo.Test(ID)
GO
-- sad trombone


Index manteinance: does the order of processing make a difference?

Does it make any difference the order in which the indexes are being
  rebuild/reorganized?

As commented by @Dan Guzman it does not matter. But if your rebuilding and  free disk space is limited I suggest you start from small to bigger ones (among the one qualify based on whatever criteria you are using).  Reason explained here:

Disk Space Requirements for Index DDL Operations.

Very good tips for other considerations.

Should You Rebuild or Reorganize Indexes on Large Tables? (Dear SQL DBA Episode 19) by Kendra Little.

  Is it the same if I first reorganize the clustered index and then the
  nonclustered ones or if I do it the other way?

Yes it same because the clustered keys, which are the record locators in the NC indexes, aren’t changed because you rebuild the index.


Compress JSON String Stored in PostgreSQL, such as MessagePack?

Individual PostgreSQL fields over 2000 bytes are automatically compressed, for TOAST storage.  However, this compression is not very good, and is local to just that one PostgreSQL value, so can't compress out repetition that occurs between values (like the same keys being used repeatedly, but only once in each JSON structure).  You might want to look at ZSON for compression that uses the whole column for compression context (I've never use it myself).

  I am particularly interested in using JSON encoding/compression algorithms like MessagePack with zlib, but how can we use this when inserting the record into the PostgreSQL table?

You could always compress it on the client, then pass it to and from the database as bytea. Of course then you can't do JSON manipulation inside the database.  Also working with bytea is generally unpleasant.

If you can find an implementation of MessagePack in Perl or Python, you could use those pl languages to allow compression and decompression inside the database.


How can I reliably track stored procedure ( and functions ) use over time?

I would create a polling job and auditing table to track this information, much like I do currently with space usage in my environment.  This seems fairly straightforward and will not be as high impact as running a continuous trace.  While the frequency of your polling would depend largely on your procedure cache churn, even executing a snapshot every few minutes would be fairly lightweight.

Auditing Table

CREATE TABLE sproc_tracking
(sproc_name sysname
,last_execution_time datetime
,CONSTRAINT pk_sproc_tracking PRIMARY KEY CLUSTERED (sproc_name));

Auditing MERGE Statement (usable since we're talking SQL 2008)

MERGE INTO sproc_tracking
USING (SELECT p.name, max(ps.last_execution_time) last_time
    FROM sys.dm_exec_procedure_stats ps
    JOIN sys.procedures AS p ON (ps.object_id = p.object_id)
    GROUP BY p.name
) AS sproc_stats(name,last_execution_time)
ON (sproc_stats.name = sproc_tracking.sproc_name)
WHEN MATCHED THEN
    UPDATE SET last_execution_time=sproc_stats.last_execution_time
WHEN NOT MATCHED THEN
    INSERT (sproc_name,last_execution_time)
    VALUES (sproc_stats.name,sproc_stats.last_execution_time);

Once you create the table, I would place the auditing statement in a SQL Agent Job to execute periodically based on your cache churn.  Probably a good place to start, if your system is reasonably active, is every 15 minutes.

Gotchas

Since this is still based on procedure cache, any stored procedures that are created WITH RECOMPILE will not be gathered no matter what.
This process will need to execute/collect within the context of your specific database.  If you need multiple databases, you'll need to adapt the process to execute in each one across the whole server.  There are a variety of ways to do this.


Postgres Index a query with MAX and groupBy

Too bad
If you cannot change the query at all, that's too bad. You won't get a good solution.
If you had not table-qualified the table (run.frames_stat), you could create a materialized view (see below) with the same name in another schema (or just a temporary one) and adapt the search_path (optionally just in sessions where this is desirable) - for hugely superior performance.
Here's a recipe for such a technique:

How can I fake inet_client_addr() for unit tests in PostgreSQL?

@Joishi's idea with a RULE would be a measure of (desperate) last resort. But I would rather not go there. Too many pitfalls with unexpected behavior.
Better query / indexes
If you could change the query, you should try to emulate a loose index scan:

Optimize GROUP BY query to retrieve latest record per user

This is even more efficient when based on a separate table with one row per relevant run_id - let's call it run_tbl. Create it if you don't have it, yet!
Implemented with correlated subqueries:
SELECT run_id
    , (SELECT frame
       FROM   run.frames_stat
       WHERE  run_id = r.run_id
       ORDER  BY frame DESC NULLS LAST
       LIMIT  1) AS max_frame
    , (SELECT "time"
       FROM   run.frames_stat
       WHERE  run_id = r.run_id
       ORDER  BY "time" DESC NULLS LAST
       LIMIT  1) AS max_time
FROM   run_tbl r;

Create two multicolumn indexes with matching sort order for lightening performance:
CREATE index fun_frame_idx ON run.frames_stat (run_id, frame DESC NULLS LAST);
CREATE index fun_frame_idx ON run.frames_stat (run_id, "time" DESC NULLS LAST);

NULLS LAST is only necessary if there can be null values. But it won't hurt either way.

Unused index in range of dates query

With only 280 distinct run_id, this will be very fast.
MATERIALIZED VIEW
Or, based on these key pieces of information:

The "frames_stat" table has 42 million rows

rows=280  -- number of returned rows = disctinct run_id

The table is unchanging (no inserts/deletes)

Use a MATERIALIZED VIEW, it will be tiny (only 280 rows) and super fast.
You still need to change the query to base it on the MV instead of the table.
Aside: never use reserved words like time (in standard SQL) as identifier.


How do I know what my username/password is for SQL PLUS?

I have not given a username or password during the whole process.

Why not? It is clearly written on that site, right before the step where you try logging in with sqlplus.

  Execute docker exec <<container id>> /opt/oracle/setPassword.sh <<pwd>> to reset password for SYS user.


Based on your question I can think of four possible issues with statistics that could be causing the issues that you're experiencing.

1. Statistics are not automatically updated often enough.

In SQL Server 2012, statistics are updated only after 20% or more of the rows in the table have changed. That means that for a billion row table you will need to modify 200 M rows before a statistics update occurs. As the table grows larger your statistics updates will become more infrequent, so SQL Server can go for years without updating statistics on large tables.

TF 2371 changes the threshold so that statistics updates occur more often. In SQL Server 2016 this change has been made my default.

2. Queries in your workload are vulnerable to the ascending key problem.

Consider a table which has new data loaded daily and queries that filter on the latest day of data. Unless statistics update updated immediately after the data load the new data will not be present in any of the statistics histograms. That can result in very poor query performance due to low cardinality estimates.

The new CE in SQL Server 2014 makes improvements in this area. If you ask for data outside of the histogram range it may make a more optimistic guess and assume that there is data in the table but not in the histogram. In SQL Server 2012 you can address this problem, if you have it, by updating statistics more often or by enabling TF 4139. TF 4139 only works against columns with an index on them. SQL Server may run a very quick query against the index to get the highest or lowest value and will temporarily amend the histogram of the relevant statistics object. This can result in much better plans for some queries.

3. Your queries wait on statistics updates.

By default, if a query loads a stale statistics update it will update that statistics object before creating a query plan. On SQL Server 2012, sampled statistics update will run with MAXDOP 1. If kicked off against a large table the process may timeout while waiting for the statistics update to complete. After you update statistics against the tables the query performs better because it no longer has to wait for the statistics update.

If you're running into this problem this can be addressed by more proactive statistics maintenance with the NORECOMPUTE option. Alternatively, you can try to make the statistics update faster by Upgrading to SQL Server 2016. On SQL Server 2016 sampled statistics updates can run in parallel.

Another option is to turn on the AUTO_UPDATE_STATISTICS_ASYNC option. If a query plan encounters a stale statistics object it will queue that statistics object to be updated by a background job. This might sound bad and it is. The query may execute with stale statistics. This is the kind of feature that you want to turn on when you don't have a better choice, such as when working with large systems where auto statistics updates are too expensive or just don't help with plan shape enough. Jack Li blogged about a customer that was helped with this option here.

4. Your workload would benefit from manual statistics updates with a higher sample rate than the auto sample rate.

Some queries and workloads need more than the default sampled rate of statistics to achieve acceptable performance. This can be difficult to do on a large database but there are a few tricks and a few enhancements in later versions of SQL Server which will help.

If you know your data and workload very well you may be able to turn off automatic statistics updates. You can gather statistics that you need with FULLSCAN and update them when appropriate. This approach will require a lot of work and a lot of attention paid to the server.

If you have an existing maintenance process that rebuilds indexes (the wisdom of that is debated) note that rebuilding indexes automatically updates stats with FULLSCAN, so perhaps you can take advantage of that if you build a maintenance solution to update statistics.

Note that gathering sampled statistics may not be faster than fullscan statistics, especially if the histogram column is indexed. SQL Server can do fullscan statistics updates in parallel. It may also avoid a sort when doing a fullscan of an indexed column but will not avoid the sort when sampling the column. In fact, for large enough tables statistics updates against unindexed columns can fail if they fill up tempdb.

SQL Server 2014 introduced incremental statistics. Suppose that you have a partitioned table and lots of data is modified in just one partition. Previously, to update the statistics on the table you would have had to look at all of the partitions. With this new feature it is possible to just gather new statistics on the changed partition. SQL Server is able to roll up the statistics from the partitions into one table level object.

If you aren't able to upgrade you could consider converting some tables to partitioned views. Each table within the view will get its own statistics objects, so if you load data according to date you may only need to update statistics on the latest table in the view instead of all of the tables of the view.

Finally, as mentioned before, SQL Server 2016 can update sampled statistics in parallel:

  Starting with SQL Server 2016, sampling of data to build statistics is done in parallel, when using compatibility level 130, to improve the performance of statistics collection. The query optimizer will use parallel sample statistics, whenever a table size exceeds a certain threshold.


How to "hack-drop" a database snapshot after SQL Server upgrade has botched it

I was able to reproduce this in an Azure VM, and resolve by simply setting the primary database offline and dropping the snapshot, eg

ALTER DATABASE AdventureWorks2012 SET OFFLINE
DROP DATABASE AdventureWorks2012_SN001


Is there any way to have Unique Constraint and NOT have Unique Index?

Reasonable Answer: No.

You really should just let it create the unique index and let it do it's job.  Space is cheap.

If you make the column you want unique the clustered index key, then it won't cost you any additional disk space, but it may slow down access to the table if your queries don't go through the unique column.

Less Reasonable Answer: Yes.

But it burns CPU, increases disk I/O and slows down just about everything.

You put an AFTER trigger on the table and have it count the number of rows in the base table for the column you want to make unique.  If the count is greater than 0 then rollback the transaction.

Example (of how not to do it):

CREATE TABLE dbo.Test
    (
    TestID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , TestValue VARCHAR(100) NOT NULL
    );

GO

CREATE TRIGGER trg_TestInsert ON dbo.Test AFTER INSERT, UPDATE
AS
BEGIN

    DECLARE @DuplicateCount INT;
    SET @DuplicateCount = 0;

    WITH CTE_Count AS
        (
        SELECT TestValue, COUNT(*) AS TestValueCount
        FROM dbo.Test
        GROUP BY TestValue
        )
    SELECT @DuplicateCount = COUNT(TestValue)
    FROM CTE_Count
    WHERE TestValueCount >=2;

    IF @DuplicateCount >= 1
    BEGIN
        ROLLBACK;
    END

END;

GO

/** Test Insert Statement */
INSERT INTO dbo.Test (TestValue)
VALUES ('TestMe');

INSERT INTO dbo.Test (TestValue)
VALUES ('TestYou');

--HINT: This one will fail.
INSERT INTO dbo.Test (TestValue)
VALUES ('TestYou');

SELECT TestID, TestValue FROM dbo.Test;

--Cleanup.
DROP TABLE dbo.Test;


Limiting total number of records in a table with a nightly job

Thinking outside the box a little bit, but instead of a nightly job, what about a Table Trigger to handle it sooner so there's a much smaller batch to delete?
Basically you can have an after insert Trigger that checks the new size of the table, and deletes the amount of rows > 3 million. I'd imagine this would always be 1 row since the Trigger would fire on every INSERT. Deleting 1 row should be very quick, but depending on the existing busyness of the table, I'm not sure if the additional contention for deleting will help or hurt more than a larger batch job at night. You'd have to test both ways and compare.

Another trick you could use is create an Indexed View on top of the table that only brings back the latest 3 million records, and do your querying off the View instead of the Table. Then the Table you can prune and archive less frequently, in a routine maintenance window perhaps.
As far as determining the latest 3 million rows, you can store the max value of your identity column of your Table in another Table that only has 1 row to keep track of this. Then in your View you'd INNER JOIN to that Table ON AuditTable.IdentityColumn >= MaxAuditIdentityValueTable.Value - 3000000.
You can accomplish storing that max identity value a number of ways, one being an after insert Table Trigger as mentioned above. In this case, it should be an even more efficient Trigger because you're not locking your audit table when it fires (since you're not deleting from it), rather you're locking the MaxAuditIdentityValueTable instead, and since there's only 1 record to UPDATE in that table, it should be extremely fast. (By the way, please pick a better name than my example of MaxAuditIdentityValueTable, I suck at naming things. :)


In same server you just need to use alter table to do that.

ALTER TABLE `old_db.foo` RENAME `new_db.foo`;


name of subquery not accessible by where clause

Simiply use an Inner Join (your current query using a Scalar Subquery is similar to an Outer Join):

SELECT
  MOVIE.NAME,
  COUNT(*) AS RENTCOUNT
FROM MOVIE
JOIN MOVIERENTED
ON MOVIERENTED.MOVIEID=MOVIE.MOVIEID
GROUP BY MOVIE.NAME, MOVIE.MOVIEID
ORDER BY RENTCOUNT;


For what you want to do, I would recommend the following (which is pretty much what you were thinking).

1> Create history tables for the historic data you have - keep the schemas as similar as possible. Split up by some logical grouping (such as year/month) based on how they are going to be queried (say you need to report with in month/year as well as all). Do not worry about the table size of the splits unless they are getting into the TB size range (your dbms should handle it) just make sure that they are appropriately indexed for the queries that need to be run.  You should consider putting these onto a different disk to the active data if performance is an issue.

2> Create a routine to move data from the active table to the relevant historic table. Run this periodically. As a practice rebuild the indexes on the table that has had the data removed from it, and maybe update the table statistics. Easiest way to do this is to write a sql script.

3> Consider the reporting you want to do. If you want to only have to deal with 1 table when writing queries, create a view that joins the archived tables together. Create indexes on all the tables to suite the view. This way if you want all the data, select from the view. If you want data from a specific year/month, query that table. The view will look something like:

create view view_all_data as
select "Jan12" as month,a.* from data_Jan12 a
union
select "Feb12" as month,b.* from data_Feb12 b
....

I am assuming here that the system is not a highly used transactional system and that you have windows of low usage to run the analysis queries. If you need to maintain high levels of performance, you may like to do the above in a separate database (separate hardware) and port across the new data that you get from backups.


PostgreSQL selecting empty fields of integer type

This is not really database administration related, nor is it really about PostgreSQL, but as @foibs answered, you should have a look at IS NULL:

 SELECT fk_fc_id,
        di_timestamp,
        di_item_value
 FROM data_item
 WHERE fk_fc_id IS NULL


Three Node Cluster that Allows For Two Failures

If you want to be able to sustain a failover of two nodes within the failover cluster, then you'll need to ensure that you have five voters for quorum.

You currently have three nodes, and even if you were to add a disk witness, you'd still be at the same point:  whether you have three or four possible quorum votes, you'd still need to have three votes for quorum (you need to have more than half of the votes for quorum).

I've never heard the terminology of a "witness server" before, and I think that person is just trying to give a logical name to simply adding another node to your cluster.

In other words, if you now have a four node cluster with a disk witness, that'll be a total of five voters.  Five voters will allow you to sustain two nodes to fail (provided your disk witness continues to be functioning properly).

With that being said, you also need to answer a few questions for yourself:

Does you really need three separate instances in this cluster?  What is the driving force behind that?
Can one of your possible owning nodes for the FCI be able to sustain all three instances of SQL Server? (think about it, there could be a lot of serious contention there)
Do all of these instances need to be in the same cluster?

And I do want to echo what @AaronBertrand has said above in his comment on your question.  There is no such thing as an active / active [ / n [ / n ... ] ] cluster for SQL Server.  It's extremely misleading to think of it like that, and that terminology has traditionally caused a lot of confusion (not to mention, the basic problem of a wrong title for something).


Import lots of mysqldumps with missing CREATE DATABASE

This script creates all databases accordingly to the names of the files:

PW=your password here
for i in *.sql; do db=$(echo $i|sed s/.sql$//;);
  echo $db ...;
  mysql -u root -p$PW -e "create database $db";
  mysql $db -u root -p$PW < $i;
done


How can I know which tables "compose" a view?

To take some action when a database mirroring state change occurs, you have several options including WMI and Event Notifications (using the DATABASE_MIRRORING_STATE_CHANGE event):

Altering on Database Mirroring Events

If you are doing any sort of work with mirroring, I can recommend (MVP & MCM) Robert Davis' book:

Pro SQL Server 2008 Mirroring


Are multiple databases sharding for read on docker as a good idea?

The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer.

When you’re building an application for lots of clients, there’s two common ways to design the database(s):

Option A: Put all clients in the same database
Option 2: Build one database per client

Putting All the Clients in the Same Database

It’s simple: just add a Client table at the top of the schema, add a ClientUsers table to make sure people only see their own data, and away we go.

Benefits of this approach:

Easier schema management. When developers deploy a new version of the application, they only have to make schema changes in one database.  There’s no worries about different customers being out of sync or on the wrong version.

Easier performance tuning. We can check index usage and statistics in just one place, implement improvements easily, and see the effects immediately across all our clients.  With hundreds or thousands of databases, even the smallest change can be difficult to coordinate.  We can check our procedure cache contents and know for certain which queries or stored procedures are the most intensive across our entire application, whereas if we’re using separate databases per client, we may have a tougher time aggregating query use across different execution plans.

Easier to build an external API. If we need to grant access to our entire database for outsiders to build products, we can do that easier if all of the data is in a single database.  If the API has to deal with grouping data from multiple databases on multiple servers, it adds development and testing time.  (On the other hand, that “multiple servers” thing starts to hint at a restriction for the one-database-to-rule-them-all scenario: one database usually means all our load impacts just one database server.)

Easier high availability & disaster recovery. It’s really, really simple to manage database mirroring, log shipping, replication, and clustering if all we have to worry about is just one database.  We can build a heck of an infrastructure quickly.

Putting Each Client in its Own Database or Shard

You still need a client listing, but now it becomes a directory - for each client, you also track the shard it lives in. On startup, your app queries this table, and caches it in RAM. When it needs data for a client, it connects directly to that shard (database & server).

Benefits of this approach:

Easier single-client restores. Clients are unreliable meatbags.  (Except mine – they’re reliable meatbags.)  They have all kinds of “oops” moments where they want to retrieve all of their data back to a point in time, and that’s a huge pain in the rear if their data is intermingled with other client data in the same tables.  Restores in a single-client-database scenario are brain-dead easy: just restore the client’s database.  No one else is affected.

Easier data exports. Clients love getting their hands on their data.  They want the security of knowing they can get their data out anytime they want, avoiding the dreaded vendor lock-in scenario, and they want to do their own reporting.  With each client’s data isolated into their own database, we can simply give them a copy of their own database backup.  We don’t have to build data export APIs.

Easier multi-server scalability. When our application needs more power than we can get from a single server, we can divide up the databases between multiple servers.  We can also spread out the load geographically, putting servers in Asia or Europe to be closer to clients.

Easier per-client performance tuning. If some clients use different features or reports, we can build a specialized set of indexes or indexed views just for those clients without growing everyone’s data size.  Granted, there’s some risk here – by allowing schema differences between clients, we’ve just made our code deployments a little riskier and our performance management more difficult.

Easier security management. As long as we’ve properly locked down security with one user per database, we don’t have to worry about Client X accessing Client Y’s data.  However, if we just use a single login for everyone, then we haven’t really addressed this concern.

Easier maintenance windows.  In a global environment where customers are scattered around the globe, it’s easier to take customers offline for maintenance if we can do it in groups or zones.

Which one is right for you?

There’s no one right choice: you have to know your own company’s strengths and weaknesses.  Let’s take two of my clients as examples.

Company A excels at hardware performance tuning.  They’re really, really good at wringing the very last bit of performance out of hardware, and they don’t mind replacing their SQL Server hardware on a 12-18 month cycle.  (They refresh web servers every 4-6 months!)  Their Achilles’ heel is extreme compliance and security requirements.  They have incredible auditing needs, and it’s just easier for them to implement bulletproof controls on a single server, single database than it is to manage those requirements across thousands of databases on dozens of servers.  They chose one database, one server, many clients.

Company 2 excels at development practices.  Managing schema changes and code deployments across thousands of databases just isn’t an issue for them.  They have clients around the world, and they’re processing credit card transactions for those clients around the clock.  They need the ability to spread load geographically, and they don’t want to replace servers around the world every 12-18 months.  They chose one database for each client, and it’s paying off as they start to put SQL Servers in Asia and Europe for their offshore clients.


.mdf and .ldf files do not show the last modification date in the way you think it does.

The file-system date reflects the last time the instance started the database.  Modifications to data continue while the instance has the database open, without the instance actually updating the file-system dates for the files in use by the database.

Can you add the output of sp_helpdb 'dbname' for both instances to your question?  Also add output from:

SELECT d.name
    , mf.name
    , mf.type_desc
    , mf.size * 8192E0 / 1048576 AS FileSizeMB
    , mf.state_desc, mf.max_size * 8192E0 / 1048576 AS MaxFileSizeMB
    , mf.growth  * 8192E0 / 1048576 AS FileGrowthMB
FROM master.sys.databases d
    INNER JOIN master.sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = 'dbname';

The following query, when run from the database in question, will show the number of megabytes allocated and used:

SELECT sum(total_pages) * 8192E0 / 1048576 as TotalMB
    , sum(used_pages) * 8192E0 / 1048576 as UsedMB
FROM sys.allocation_units;


Using a Replication Subscription Database for Publication

Yes you can but not easy to maintain.  If your ever get out of sync and you have to reinitialize, resnap of the first set of publication and subscription; you will need to remove the publication property of the subscription. Otherwise you cannot do a full snapshot.


Delete rows in a table based on a table value parameter

Is there a reason this simple correlated delete statement won't do the trick?

DELETE A
FROM TABLE_A A
WHERE NOT EXISTS (
        SELECT *
        FROM TVP_A
        WHERE ID = A.ID
        )


How do I copy databases from Postgres 8.4 to Postgres 9.1?

It's failing when trying to run the old bin files because it's missing a dependency. You're going to need to install its dependencies, install 8.4 on the new server for this task, or spin up a VM with 8.4 installed, copy the files to the VM, do what it takes to start the 8.4 instance on the VM (which means that postgres will need to know where the default data directory is). From there, I'd do a pg_dump on the database(s) in question and then restore them to the new server. That would probably give you the cleanest environment.

So, here are what I see as your options:

Using pg_upgrade

On The New Server (pg_upgrade)

Install PostgreSQL 8.4 dependencies and hope that your copied binaries work. If they don't, then you may need to just remove these copied binaries and install 8.4 from apt.
Attempt to start the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl start -D /tmp/postgres.old/main/
If that works, stop the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl stop -D /tmp/postgres.old/main/
Now try your pg_upgrade script

Without Using pg_upgrade

Create a VM or use an old, decommed server and install postgresql 8.4
Initialize your server using initdb (or pg_createcluster)
Copy your backed up data over that which was just created
Start postgresql
Backup the data using pg_dump
Copy the dump file to the "new server"
Restore to a new database on the new server


"Role does not exist" error when granting usage on public schema, but role does exit

Your problem is that the role you are after has capital letters in its name.  Mixing cases is usually a bad idea in PostgreSQL, because you need to double-quote such names.

So your grant should look like

GRANT ALL ON SCHEMA public TO "MyRole";

I'd suggest renaming that role to "myrole", to avoid such problems later.


Registered Server list and groups disappeared from SQL Server 2012 Management Studio

The list you're talking about wouldn't be present in Object Explorer (unless you connected to all of them manually every time you started Management Studio). Your active connections will disappear from Object Explorer when you close down SSMS or reboot, so you can't persist them there (as convenient as that might be).

What you might be talking about, though, is Registered Servers, which you might have inadvertently closed before closing SSMS last (it should pop up again if it was active when you shut down SSMS or rebooted). If it's missing, no problem, you can restore this panel using View > Registered Servers or through the key combination Ctrl+Alt+G.

If your servers still don't show up there, it's possible that you lost your registered server configuration due to a roaming profile, logging in as a different user, or messing with SqlStudio.bin.


T-SQL query for date of last full backup, size and location

You need to query the list of databases first (it's in sys.databases), then join it to your query:

WITH LastBackUp AS
(
SELECT  bs.database_name,
        bs.backup_size,
        bs.backup_start_date,
        bmf.physical_device_name,
        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM  msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE   bs.[type] = 'D'
AND bs.is_copy_only = 0
)
SELECT
        sd.name AS [Database],
        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
        backup_start_date AS [Last Full DB Backup Date],
        physical_device_name AS [Backup File Location]
FROM sys.databases AS sd
LEFT JOIN LastBackUp AS lb
    ON sd.name = lb.database_name
    AND Position = 1
ORDER BY [Database];


Getting a list of active objects that use cursors but don't explicitly close/deallocate them

Rather than search all of your stored procedure text for those wildcards, you'd probably be better off looking for cursors that are open, and their associated text. That would likely make it easier to locate cursor names, stored procs, etc. written by forgetful developers.

To test, run this in one window:

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#commands') IS NOT NULL
   BEGIN
         DROP TABLE #commands;
   END;

CREATE TABLE #commands
(
  ID INT IDENTITY(1, 1)
         PRIMARY KEY CLUSTERED,
  Command NVARCHAR(2000)
);
DECLARE @CurrentCommand NVARCHAR(2000);

INSERT INTO #commands ( Command )
    SELECT 'SELECT 1';

DECLARE result_cursor CURSOR
FOR
        SELECT Command
            FROM #commands;

OPEN result_cursor;
FETCH NEXT FROM result_cursor INTO @CurrentCommand;
WHILE @@FETCH_STATUS = 0
      BEGIN

            EXEC (@CurrentCommand);

            FETCH NEXT FROM result_cursor INTO @CurrentCommand;
      END;

--CLOSE result_cursor;
--DEALLOCATE result_cursor;

Then in another window, run this:

SELECT dec.session_id, dec.cursor_id, dec.name, dec.properties, dec.creation_time, dec.is_open, dec.is_close_on_commit,
        dec.fetch_status, dec.worker_time, dec.reads, dec.writes, dec.dormant_duration, dest.text
    FROM sys.dm_exec_cursors (0) AS dec
    CROSS APPLY sys.dm_exec_sql_text(dec.sql_handle) AS dest
    WHERE dec.is_open = 1;

You should see the cursor opened and left hanging by the other session here, along with any other open cursors.

If you need to monitor for these (and you don't have a monitoring tool), you can use the dormant_duration column and poll that DMV with an agent job that will fire off an alert or email when an open cursor is dormant for a certain amount of time. That's in milliseconds.

Hope this helps!


best db and file system for high volume, high qps

Virtually any database server can meet your needs, whether it is MySQL, Microsoft SQL Server, Oracle, etc. Performance with all the rdbms' vary a lot based on the following criteria

I/O Performance of the disk (get good, fast, RAID'd disks over high throughput connections)
Performance and Amount of RAM (good and fast RAM, and lots of it)
File System Overhead (use a modern fs designed for databases. Ext4 works fine, but there's OCFS2 that is Oracle's file system, built for databases, or even Oracle's RAW storage where there isn't even a file system in the OS-sense... the database manages the disk entirely.)
Properly Indexed table structure (quick access to data depends upon your data being indexed correctly, where "correct" means most efficient for all your needs.)
Properly Optimized Queries... usually goes hand-in-hand with the correct indexes; the database can try to figure out the best optimization for your query, but it can only do so with correct indexes and statistics. Better yet, fine tune your queries so that the database doesn't have to do a lot of optimization on its own, and you'll be faster and happier for it.
I/O Performance of the network - if you are transferring a lot of data in or out, network performance will matter (otherwise the database has to wait when sending data across the wire).

A few things that really don't matter very much (as far as performance):

Your operating system. Windows, Mac OSX, Linux, and anything else that's modern will do just fine as the operating system layer. Most of your work is done in the database anyway, with occasional tasks needing done in the OS. Pick an OS that will simplify your life (and not complicate it), but modern databases are performant on most OSes.
Your database engine. Yes, there are critical differences between MySQL and Oracle and MS SQL, etc. Does it really matter? If there is a particular feature you must have, then by all means, go for it, but ultimately, all will be sufficient with the correctly configured hardware and tables.

Really, pick a database, an OS, a file system, and build your app. With good hardware and good table design (with indexes and optimization), things should go swimmingly.

All that said, here's what I'd pick (only because I'm most familiar with this type of configuration):

RHEL / OEL / Ubuntu (OS)
Oracle Standard One (or Standard, or EE, depending on your needs) (DB)
Ext4 Journaled File System (FS)
2 2xCore Processors (>2.5GHz)
8 GiB Memory (something around 1066 or faster MHz)
1 TiB HDD (SATA, with >=7200rpms)
At least gigabit ethernet

Swap out the OS, File system, and DB as desired. Your machine should scream and be just fine for a good number of databases, and is probably a bit overkill. Best thing, though, is that you can always add memory and disk space and upgrade your processors if you need more. If necessary, move to a new box, or add boxes (if your database supports it). (Even better: run your environment as a Virtual Machine/Cluster. Then moving to another physical machine is child's play.)


Excessive log throttling on Azure SQL on business critical tier

After a few weeks with the Azure product team we have great answers to these questions.

The main bottleneck in Azure for us is Log I/O.  Each logical processor has 6 MB/s throughput. So, on Gen4_8, we have 16 logical processors and get 96 MB/s log I/O throughput. On Gen5_8, there are 8 processors so the throughput is 48 MB/S.

The reason for these limits is that if they allowed higher log I/O throughput they wouldn't be able to guarantee that the log shipping job that runs every 5 minutes would be able to capture all the transactions that occurred.

There is also a major issue with using MAXDOP on Azure right now.  By adding MAXDOP 1 to the SELECT INTO statements and our parallel inserts, our performance improved tremendously.  Do NOT use MAXDOP 8 or higher on Gen4 or Gen5! I tested on Gen5_16 and MAXDOP 8 adds a 10 fold increase to run times.   On Gen4_8, our production pool, our parallel insert for loading 1 billion records goes from 40 minutes with MAXDOP 1 to 3.5 hours with MAXDOP 8 (yes, which means we aren't really doing a parallel insert).


Sharing PostgreSQL database on one dual-boot machine without duplication (cross-platform)

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa.

You can't. Just not supported, not possible. No can do. PostgreSQL does not attempt to make the data directory portable across architectures, operating systems, etc.

The only way you can do this is to run a Windows virtual machine inside Ubuntu when you want to access a Windows data directory, or a Linux virtual machine inside Windows when you want to access a Linux data directory.

You might want to run a VM image that's shared between both platforms, and have it contain the database - but be careful, Linux's NTFS driver isn't great for performance, and might not have all the wrinkles ironed out when it comes to concurrent I/O so I'd be a little leery running something like a VM image off it. FAT32 and exFAT are not crash-safe. So there aren't really any good choices for a file system both systems can share to run "real work" off.

So personally... I'd just have a small separate machine. Or I'd keep a dump of the DB and reload it when I rebooted.

(Additionally, if running PostgreSQL under Linux on Linux's NTFS file system driver works, that's a little surprising, and I certainly wouldn't rely on it for anything I cared about).


sql server database sharding - what to do with common data / non sharded data

Your question focused on this:

  However, my question is regarding the non sharded data which is
  common/universal. An example of data like this may be an Inventory
  table for instance or possibly an Employee table, user table etc .

When you're doing sharding, and you have data that all of the shards need to see, you have to classify that data with a few attributes:

Does it change frequently? In your examples, you listed Inventory, Employee, and User. Typically inventory changes very fast, but the Employees records only change periodically (say, a few hundred updates per day).

How much delay can each shard tolerate? Even though the Inventory may constantly be changing, you can typically tolerate a large amount of delay (minutes or even hours) on a table like that. If you're selling unique items with a very limited quantity that you can never restock (think original artworks), then you don't shard that data at all - you only query the original database. However, in most online stores, you're not selling out of every item every day, and you're going to restock things quickly anyway, so you don't really need up-to-the-millisecond counts of inventory. In fact, in most cases, you only need an In-Stock flag that's either 0 or 1, and a central process updates that flag. That way, you don't have to push every up/down bump of item count out to every shard. Employee or User data, on the other hand, may need to be pushed out to every shard nearly instantaneously.

Will you be joining from the sharded tables to the non-sharded ones? Ideally, the answer here is no - you should make two separate queries to get the data, and then join them on the app side. This gets a lot harder from an app perspective, but it gives you the capability to get the freshest data from each source.

Is this original data, or copied? Another way to think of this question: what do you need to back up, and how frequently? Typically in a high-volume sharding environment, you want the backups to be as fast and as small as possible. (After all, you need to protect each node, and you want all of the shards to fail over to DR at the same point in time - not have some shards with newer data than others.) This means the sharded data and the non-sharded data should be in completely separate databases - even if they're on the same server. I may need constant transaction log backups of my sharded (original) data, but I may not need to back up the non-sharded data at all. It's probably easier for me to just refresh my Employees or Users table from the single source of truth rather than back it up on every shard. If all of my data is in a single database, though, I lose that capability.

Now, about your concerns:

"Transactional issues...you will no longer be able to do this easily." Correct. In sharded scenarios, throw the concept of a transaction out the window. It gets worse, too - for the sharded data, you could have one shard up and online, and another shard down temporarily due to a cluster instance failover or restart. You need to plan for failure of any part of the system, at any time.

"Not possible to do cross database referential integrity." Correct. When you split a single table out across multiple servers, you're putting your big boy pants on and telling the database server that you're taking over for tough tasks like point-in-time backups, relationships between tables, and combining data from multiple sources. It's on you and your code now.

"Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards." Correct here as well. There's no easy button for this, but once you've built this into the app, you're able to scale like crazy. I'd argue that the easier way to do this is to split the app's connections by reads.

"increased database trips." - Yes, if you break the data into multiple servers, the app is going to have to reach out to the network more. The key is to implement caching as well so that some of this data can be stored in lower-cost, higher-throughput, lock-free systems. The fastest query is the one you never make.

I've also laid out more pros and cons to dividing up multi-tenant databases here, such as performance tuning on individual shards, different backup/recovery strategies per shard, and schema deployment challenges.


Subscriber Node down in transactional replication

First, you shouldn't use Replication for High-Availability or Disaster Recovery.  It was never designed for that, and you'll see below that is not a very good solution

Instead you should really use AlwaysOn Availibility Groups instead of Replication if you plan on using the solution for High-Availability or Disaster Recovery.

  i need to bring up the subscriber for read and write by disabling replication, will the transaction and commands in node 1 distribution database will be rolled back

No.  The old Publisher/Distributor will come back at the point-in-time it went down.

  or i will lose certain data?

You will have data in each database that isn't present in the other.  You can either discard the data in one or the other, or manually add changes from one to the other, perhaps using the tablediff utility.

  What happen to transaction marked for replication in publisher but not has been sent to distributor, will it be rolled back or i will lose certain data?

The publisher will come back online, and any transactions committed, but not distributed, will be present in the Publisher, but the old Distributor is no longer reading its log file for transactions.  Again the previous subscriber now has data not present in the old publisher, and you have to decide what to do.

The typical operational response here is to save a copy of the old publisher, and then to re-create and re-initialize replication using the data from the old subscriber.  You do this either by configuring the old subscriber as the new publisher, or by restoring a backup of the old subscriber over the old publisher and re-creating the old replication topology.


Following are the resources which I'm using to brush-up my database skills and to teach newbies...

Animated DataBase Courseware

From Database Design to Advanced Concepts

wofford-ecs.org

Database Design - Normalization

db4u

Normalization exercises

http://www.sql-ex.ru/

Tons of sql exercises

http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/intro.html

Database Design Tutorial


(How) can I return the most specific row on a join?

This makes some assumptions but it will do:

SELECT
    prd.id, prd.height, prd.fabric,
    MAX(pri.price) AS price
FROM
    products AS prd
  JOIN
    prices AS pri
      ON  (prd.height >= pri.min_ht OR pri.min_ht IS NULL)
      AND (prd.fabric =  pri.fabric OR pri.fabric IS NULL)
GROUP BY
    prd.id ;

The above assumes that the more details a pricing has, the higher the price is. Since that is not the case, a different, more general approach:

SELECT
    prd.id, prd.height, prd.fabric,
    pri.price
FROM
    products AS prd
  , LATERAL
    ( SELECT p.price
      FROM prices AS p
      WHERE (prd.height > p.min_ht OR p.min_ht IS NULL)
        AND (prd.fabric = p.fabric OR p.fabric IS NULL)
      ORDER BY
          ((p.min_ht IS NOT NULL)::int + (p.fabric IS NOT NULL)::int) DESC
      LIMIT 1
    ) AS pri ;

Tested at SQL-Fiddle


Why does sql server need to convert count(*) result into int before comparing it with an int variable?

The fact that you are comparing it against an integer variable is irrelevant.

The plan for COUNT always has an CONVERT_IMPLICIT(int,[ExprNNNN],0)) where ExprNNNN is the label for the expression representing the result of the COUNT.

My assumption has always been that the code for COUNT just ends up calling the same code as COUNT_BIG and the cast is necessary to convert the bigint result of that back down to int.

In fact COUNT_BIG(*) isn't even distinguished in the query plan from COUNT(*). Both show up as Scalar Operator(Count(*)).

COUNT_BIG(nullable_column) does get distinguished in the execution plan from COUNT(nullable_column)  but the latter still gets an implicit cast back down to int.

Some evidence that this is the case is below.

WITH
E1(N) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)                                       -- 1*10^1 or 10 rows
, E2(N) AS (SELECT 1 FROM E1 a, E1 b)   -- 1*10^2 or 100 rows
, E4(N) AS (SELECT 1 FROM E2 a, E2 b)   -- 1*10^4 or 10,000 rows
, E8(N) AS (SELECT 1 FROM E4 a, E4 b)   -- 1*10^8 or 100,000,000 rows
, E16(N) AS (SELECT 1 FROM E8 a, E8 b)  -- 1*10^16 or 10,000,000,000,000,000 rows
, T(N) AS (SELECT TOP (2150000000)
                  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E16)
SELECT COUNT(CASE WHEN N < 2150000000 THEN 1 END)
FROM T
OPTION (MAXDOP 1)

This takes about 7 minutes to run on my desktop and returns the following

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

Which indicates that the COUNT must have continued on after an int would have overflowed (at 2147483647) and the last row (2150000000) was processed by the COUNT operator leading to the message about NULL being returned.

By way of comparison replacing the COUNT expression with SUM(CASE WHEN N < 2150000000 THEN 1 END) returns

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

with no ANSI warning about NULL. From which I conclude the overflow happened in this case during the aggregation itself before row 2,150,000,000 was reached.


Getting null block_st.text when querying for blocks

If I understand you correctly you are looking to see who is blocking and why, not just gathering overall statistics.  (Both approaches have real value, of course.)

Since every event is transient, so it is not surprising that some values would not return what you expect.

We have been using the approach outlined by Tony Rogerson quite a few years ago that uses Event Notifications.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx

This approach will capture the state of blocking based on a timing trigger.  In the sample code Tony has configured to receive a notification when a block has lasted 10 seconds. (And every 10 seconds after until the block is removed.)

This approach will provide a capture of the XML describing the blocking state at the moment of the event. This includes the blocked and the blocking process.  And if none of the blocks are long enough to capture, then your event table will be very quiet.

You can monitor for shorter or longer blocks, depending on your need.  I generally run at 25 seconds, to cut out a lot of chatter from shorter blocking periods.

New software releases can be a good reason to monitor blocking more closely.


SQL Server database backup file is double final size until backup completes

This is a known issue and expected behavior (it is by design). Essentially, the backup reserves the total amount of space that it thinks it might need, then shrinks as the last step. To change the behavior you can experiment with trace flag 3042. From http://msdn.microsoft.com/en-us/library/bb964719.aspx#Allocation :

  For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.
  To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).


SQL Server + Reporting Services + SSIS + SSAS Licensing and Infrastructure

Check out the Microsoft SQL Server 2014 Licensing Guide (PDF).

Page 11 says:

  The software components of a single SQL Server 2014 license cannot be separated for use in more than one OSE. If these components are running on a server other than the main database server, then an additional license is required for each additional OSE in which they are installed. For example, if the SQL Server DB is deployed in one OSE and SQL Server RS is deployed in another, both OSEs must be fully licensed for SQL Server 2014 accordingly.

What that means is that if you run the database engine, SSIS, and SSRS on three separate servers, then you have to license all three.