SQL logs filling up with "Setting database option Compatibility_Level" messages On several of my servers, I have "Setting database option Compatibility_Level" messages filling up the SQL log and application event log to the tune of twice every 5 seconds. This makes finding anything else in the SQL log nearly impossible.

I'm aware that this is a known bug in MS Reporting Services (marked "Closed as Won't Fix"). I'm looking for other ideas to resolve this. Using profiler, I found the code being run by the reporting server is:

DECLARE @currVer nvarchar(128), @currMajorVer nvarchar(32)
     , @idx int, @currMajorVerInt tinyint ;

SELECT @currVer = CONVERT(nvarchar(128), ServerProperty('ProductVersion')) ;
SET @idx = CHARINDEX('.', @currVer, 0) ;
SET @currMajorVer = SUBSTRING(@currVer, 1, @idx-1) ;
SET @currMajorVerInt = CONVERT(tinyint, @currMajorVer) ;

DECLARE @dbname sysname

IF @currMajorVerInt =  10
BEGIN
    SELECT @dbname=DB_NAME()
    EXEC sp_dbcmptlevel @dbname, 100 ;
END

Ideas?

The listed "workaround" suggested modifying the system stored procedure sp_dbcmptlevel, which although possible, is not recommended for a variety of good reasons.

Another thread on this issue suggested changing the system account used by the reporting services service. I'm not sure if that applies to our situation. Has anyone found that this resolves the issue for you?

Only other idea I had: is there any way to intercept and either block a query from being executed, or to alter the syntax of the query before it is executed? Like a kind of INSTEAD TRIGGER or something?

Only a couple of things come to mind.

Hack sp_dbcmptlevel and add a "IF suser_sname() = 'Your SSRS Account' RETURN" to the very top of it.
Add a DDL trigger to the database that simply rolls back any alter database statements.

There's no way to intercept the database call and prevent it from being sent to the database engine.


Why does an AS cube rebuild use so much TempDB? I have a 50GB SQL 2008 database (only 36 of that is data) with a 24GB Analysis Server cube that rebuilds nightly.

The cube rebuilds have been using more and more of the TempDB on the SQL server each night. Last night TempDB data files grew to about 100GB before using up all space on the TempDB drive.

I can add more space, but I'd feel better if I understood more about what is going on here:

How/why do cube rebuilds use TempDB space on the SQL server?
Is there any way to reduce the amount of TempDB that it uses, or force it to "release" tempdb between steps, or something?
Is there any way for me to estimate the amount of space it might continue to use as my data continues to grow? (based on the size of certain key tables in my database, or something?)

Edit: Only significant difference in this particular database that I can think of is that they use some very large, very complicated nested views for their final data set. If these complex views are the ones being used to build the cube, could that explain the high tempDB use?

While the cube builds, you can run Adam Machanic's sp_WhoIsActive diagnostic tool to see which queries are allocating space in TempDB.  I recorded an sp_WhoIsActive tutorial video to show how it works.  Include the @get_plans = 1 parameter when you call it, and you'll also get the execution plans.  That way you can see exactly what's using TempDB and why.


Rollback generate scripts During the development of applications users often want to add new modules, business rules etc. So I've often used SQL Server 2008 option to create scripts from the development DB to generate new tables that I will then execute on the production server before I publish the new version of the application.

So I'm wondering is there a way to put the whole generate script file in the transaction and rollback changes if there's an error returning everything to the previous state, no new tables added, data inserted etc.

If there is a reason that this isn't a recommended approach please explain.

In theory you could do something as like wrap the script execution in a transaction. You can execute the .sql batch files from an application library like dbutilssqlcmd or SMO's ServerConnection.ExecuteNonQuery which handles the GO batch separator or sqlcmd extensions in the script.

However in practice this is nearly impossible to do. Wrapping a script in a transaction may yield unexpected results because the locking changes. Certain DDL statements are not allowed inside a transaction. The script may contain sqlcmd extension directives like :connect which would mess up the entire transaction handling.

Another approach is to use migrations, which are coded in the application and handle both the roll forward and the roll back of the change. But to be effective the steps must be idempotent (yield the same result even if invoked multiple times) and this is very hard to achieve in practice.

The safest and also easiest approach is to rely on a backup taken prior to migration, which would give the ability to rollback to the backup if anything goes wrong during the deployment of V. next. This is very easy to implement and very efficient if database snapshots are used as backups.

Of course if there is a 24/7 availability requirement things change dramatically, but rolling out upgrades with no downtime is a subject much bigger that this post and solutions roll in the $millions, unlikely something you would fancy.


Get all possible parent ids only if has parent bigger than 0 Right now If I call get_parent_id(4) I will get all id parents. This means 3,2,1. Is not hard to guess that 1 has parent 0.

This is my problem. I want to output all ids unless the parent is 0. To do this I have one more query (check_for_parent_0) in this function.

Please suggest any solution so I can have only one query and not two in the next function. Any other advices for a better performance is welcomed.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_parent_id`(`id` INT) RETURNS varchar(1024) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE return_string VARCHAR(1024);
    DECLARE return_string_separator CHAR(1);
    DECLARE parent_id INT;
    DECLARE check_for_parent_0 INT;

    SET return_string = '';
    SET return_string_separator = '';
    SET parent_id = id;
    SET check_for_parent_0 = '';

    WHILE parent_id > 0 DO

        SET parent_id = (
            SELECT
                forum_parent_id
            FROM
                forums
            WHERE
                forum_id = parent_id
        );

        SET check_for_parent_0 = (
            SELECT forum_parent_id FROM forums WHERE forum_id = parent_id AND forum_parent_id != 0
        );

        IF (parent_id > 0 AND check_for_parent_0 > 0) THEN
            SET return_string = CONCAT(return_string, return_string_separator, parent_id);
            SET return_string_separator = ',';
        END IF;

    END WHILE;

    RETURN return_string;
END$$

I have a way to do it in one query, though it may not be faster than your current solution.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_parent_id`(`id` INT) RETURNS varchar(1024) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE return_string VARCHAR(1024);
    DECLARE return_string_separator CHAR(1);
    DECLARE parent_id INT;

    SET return_string = '';
    SET return_string_separator = '';
    SET parent_id = id;

    WHILE parent_id > 0 DO

        SET parent_id = (
            SELECT
                forum_parent_id
            FROM
                forums
            WHERE
                forum_id = parent_id and forum_parent_id not in (Select forum_id from forums where forum_parent_id=0)
        );

        IF (parent_id > 0) THEN
            SET return_string = CONCAT(return_string, return_string_separator, parent_id);
            SET return_string_separator = ',';
        END IF;

    END WHILE;

    RETURN return_string;
END$$


Auto increment primary key inside a number range I have following table in SQL Server 2012:

CREATE TABLE [dbo].[xxx](
    [K1] [int] NOT NULL,
    [K2] [int] NOT NULL,
    [F1] [int] NULL,
    [F2] [int] NULL,
    [F3] [int] NULL,
 CONSTRAINT [PK_xxx] PRIMARY KEY CLUSTERED
(
    [K1] ASC,
    [K2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

K1 and K2 are primary keys.

I want to achieve that K2 is automatically incremented inside the number range of K1.

An usual query should look like:

INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)

A trigger should now check the highest K2 inside the number range of K1 (select MAX(K2) from xxx where K1 = i.K1) , and add one to it.

I tried following trigger:

CREATE TRIGGER [dbo].[trg_xxx_INSERT] ON [dbo].[xxx] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON

insert into xxx
 (K1, K2, F1, F2, F3)
select
  i.K1, (select isnull(max(K2),0)+1 from xxx where K1 = i.K1), i.F1, i.F2, i.F3
from
  inserted i;
END

It does work well if an INSERT statement adds single rows.

truncate table xxx
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)

In this case, I will have following rows in the Table xxx:

10, 1
10, 2
10, 3

However, the trigger fails if I want to add more than 1 row:

truncate table xxx
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...), (10, -1, ...), (10, -1, ...)

I guess the subquery is only executed once and not for every i, and therefore I get a primary key violation.

How can I achive that the trigger also works with multiple inserts?

This example should get you what you need, or at least it worked in my testing.

Setup

CREATE TABLE dbo.TestInsert
    (
    TestID INT NOT NULL
    , TestSubID INT NOT NULL
    , TestValue VARCHAR(100) NOT NULL
    );

GO

CREATE TRIGGER dbo.trgTestInsert ON dbo.TestInsert INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.TestInsert
(TestID, TestSubID, TestValue)
SELECT I.TestID
    , NewSubID = ISNULL(T1.MaxID, 0) + ROW_NUMBER() OVER (PARTITION BY I.TestID ORDER BY I.TestID)
    , I.TestValue
FROM INSERTED AS I
    LEFT OUTER JOIN (
                    SELECT T.TestID
                        , MAX(T.TestSubID) AS MaxID
                    FROM TestInsert AS T
                    GROUP BY T.TestID
                    ) AS T1 ON T1.TestID = I.TestID;

END

GO

Testing

INSERT INTO dbo.TestInsert
(TestID, TestSubID, TestValue)
VALUES (10, NULL, 'test 1')
    ,(10, NULL, 'Test 2')
    ,(11, NULL, 'Test 3')

SELECT TestID, TestSubID, TestValue FROM dbo.TestInsert;


Select count without join I'm using MySQL. I have three tables, and in every table, I have a time column.

With a single query I want to count the number of rows that are between two given dates.

I have no need with a connection between the tables (although they are related), so join is not necessary.

Is it possible?

Failed attempt #1

SELECT
  COUNT(a.time),
  COUNT(b.time),
  COUNT(c.time)
FROM
  tbl1 AS a,
  tbl2 AS b,
  tbl3 AS c
WHERE
  a.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 20:46:55'
AND
  b.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 18:46:55'
AND
  c.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 18:46:55' ;

This would probably need to be a UNION query, as in:

SELECT 'a'
  , COUNT(a.time)
FROM
  tbl1 AS a
WHERE
  a.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 20:46:55'

UNION ALL

SELECT 'b'
  , COUNT(b.time)
FROM
  tbl2 AS b
WHERE
  b.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 20:46:55'

UNION ALL

SELECT 'c'
  , COUNT(c.time)
FROM
  tbl3 AS c
WHERE
  c.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 20:46:55'

The output would look like:

╔══════╦══════╗
║ col1 ║ col2 ║
╠══════╬══════╣
║ a    ║   10 ║
║ b    ║    3 ║
║ c    ║   14 ║
╚══════╩══════╝


Why 0 is equal to empty string? I need some help in finding why the following T-SQL statement returns 1 (true):

SELECT IIF( 0 = '', 1, 0)

I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is causing the behavior.

My issue is that I am joining some values and in the final row set I have values which are joined by 0 and '' values, which is not correct.

That is just documented behavior. I don't think anyone messed with the settings.

See data type precedence on MSDN.

  When an operator combines two expressions of different data types, the
  rules for data type precedence specify that the data type with the
  lower precedence is converted to the data type with the higher
  precedence.

As noted in the comments the empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date.

EDIT: I think your real problem is that your design is so that you have to join on fields of a different data type. The only way to get around this is to have a convert on your join clause which will hurt query performance. The main problem is probably with the schema design

EDIT:
There was a lot of discussion in the comments that have been moved to chat. However illogical it may seem, converting an empty string to other data types produces arbitrary values.

This code:

SELECT CONVERT(int, '')
SELECT CONVERT(float, '')
SELECT CONVERT(date, '')
SELECT CONVERT(datetime, '')

Produces this output:

0
0
1900-01-01
1900-01-01 00:00:00.000

You could expect then that this behavior is consistent between other preceding datatypes and expect that converting 0 to a date would produce the same arbitrary value but it doesn't.

SELECT CONVERT(date, 0)

Produces

  Explicit conversion from data type int to date is not allowed.

Because it's not a supported conversion

while

SELECT CONVERT(datetime, 0)

Returns

  January, 01 1900 00:00:00

So yes, it's weird and arbitrary, but actually documented and explainable.


How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT I am going through the execution plan of a tsql query.

There is a key LookUp (Clustered) in the plan. To avoid this Key LookUp, I tried to Include a column (Status_Ind) to The Index (The Index used in execution plan). But I realize that optimizer is using two Indexes, One the Primary Key index and the 2nd one, an index automatically created as a part of a Constraint (A UNIQUE NONCLUSTERED Index). I find no way to add an include part in this Index.

How do we add an INCLUDE in an index which is automatically created (through CONSTRAINTS)? I am really a beginner in Query optimization.

It is not possible to have an INCLUDE column for an index that enforces a constraint.  This has been brought up, but marked as "won't fix".

If the benefits of having a covering index are that great, consider revising the indexing strategy that you currently have on that table to maximize query performance.


Solving "Gaps and Islands" with row_number() and dense_rank()? How does one solve the islands part of gaps-and-islands with dense_rank() and row_number(). I've seen this now a few times and I'm wondering if someone could explain it,

Let's use something like this for example data (example uses PostgreSQL),

CREATE TABLE foo
AS
  SELECT x AS id, trunc(random()*3+1) AS x
  FROM generate_series(1,50)
    AS t(x);

Which should produce something like this.

 id | x
----+---
  1 | 3
  2 | 1
  3 | 3
  4 | 3
  5 | 3
  6 | 2
  7 | 3
  8 | 2
  9 | 1
 10 | 3
...

What we want is something like this...  (where z is some value that we can use to GROUP BY)

 id | x | grp
----+------
  1 | 3 | z
  2 | 1 | z
  3 | 3 | z
  4 | 3 | z
  5 | 3 | z
  6 | 2 | z
  7 | 3 | z
  8 | 2 | z
  9 | 1 | z
 10 | 3 | z
...

Such that we can do either

GROUP BY x,grp where (x,grp) is a unique island
GROUP BY grp where grp is a unique island.

That would look like this for (x,grp) in the above example.. (where z is some value that we can use to GROUP BY)

 id | x | grp
----+------
  1 | 3 | 1
  2 | 1 | 1
  3 | 3 | 2
  4 | 3 | 2
  5 | 3 | 2
  6 | 2 | z    -- Because we're grouping by an (x,grp)
  7 | 3 | z    -- Does not have to be `3` (or something unique)
  8 | 2 | z    -- z=1, is fine to use again if we
  9 | 1 | z    -- GROUP BY (x,grp)
 10 | 3 | z

Background

This has come up only a few times in answers in my research.

Using Row_Number to find consecutive row count by @Martin Smith
Getting each status change in a table by @Martin Smith
Grouping or Window by @ErikE

It also seems to explicitly confuse others though it has never been explained on this network,

Question 1 (never explained), solved and accepted with lag()
Question 2 (never explained), solved an accepted with lag()

So the trick here is a property of two equally incrementing series which produce a difference that can be used to identify islands {11,12,13} - {1,2,3} = {10,10,10}. This property isn't enough to identify islands in and of itself, but it's a crucial step that we can exploit to do so.

Background

Stepping aside from the problem. Let's check this out.. Here we

Define 1 group as 3 rows.
Create one group for each pair/row of offsets in xoffsets.

Here is some code.

SELECT x1, x2, x1-x2 AS difference
FROM (VALUES
  (2,42),
  (13,7),
  (42,2)
)
  AS xoffsets(o1,o2)
CROSS JOIN LATERAL (
  SELECT x+o1,x+o2  -- here we add the offsets above to x
  FROM generate_series(1,3) AS t(x)
) AS t(x1, x2)
ORDER BY x1, x2;

 x1 | x2 | difference
----+----+------------
  3 | 43 |        -40
  4 | 44 |        -40
  5 | 45 |        -40
 14 |  8 |          6
 15 |  9 |          6
 16 | 10 |          6
 43 |  3 |         40
 44 |  4 |         40
 45 |  5 |         40
(9 rows)

This looks pretty good, and grouping by the difference would be good enough in this example. You can see we have three groups starting at       (2,42), (13,7), and (42,2), corresponding to the groups in xoffsets.  This is essentially the problem in reverse. But we have one major issue because we're demonstrating this with static offsets. If the difference between any two offsets o1-o2 is the same we'll have a problem. Like this,

SELECT x1, x2, x1-x2 AS difference
FROM (VALUES
  (100,90),
  (90,80)
) AS xoffsets(o1,o2)
CROSS JOIN LATERAL (
  SELECT x+o1,x+o2  -- here we add the offsets above to x
  FROM generate_series(1,3) AS t(x)
) AS t(x1, x2)
ORDER BY x1, x2;

 x1  | x2 | difference
-----+----+------------
  91 | 81 |         10
  92 | 82 |         10
  93 | 83 |         10
 101 | 91 |         10
 102 | 92 |         10
 103 | 93 |         10

We'll we have to find a way define the second offset statically.

SELECT x1, x2, x1-x2 AS difference
FROM (VALUES
  (100,0),
  (90,0)
) AS xoffsets(o1,o2)
CROSS JOIN LATERAL (
  SELECT x+o1,x+o2  -- here we add the offsets above to x
  FROM generate_series(1,3) AS t(x)
) AS t(x1, x2)
ORDER BY x1, x2;

 x1  | x2 | difference
-----+----+------------
  91 |  1 |         90
  92 |  2 |         90
  93 |  3 |         90
 101 |  1 |        100
 102 |  2 |        100
 103 |  3 |        100
(6 rows)

And, again we're back on track to making groups for each pairs of offsets. This isn't quite what we're doing, but it's pretty close and hopefully it serves to help illustrate how the two sets can be subtracted to create islands.

Application

Now let's revisit the problem above with table foo. We sandwich the variables between two copies of x for display purposes only.

SELECT
  id,
  x,
  dense_rank() OVER (w1) AS lhs,
  dense_rank() OVER (w2) AS rhs,
  dense_rank() OVER (w1)
    - dense_rank() OVER (w2)
    AS diff,
  (
    x,
    dense_rank() OVER (w1)
      - dense_rank() OVER (w2)
  ) AS grp,
  x
FROM foo
WINDOW w1 AS (ORDER BY id),
  w2 AS (PARTITION BY x ORDER BY id)
ORDER BY id
LIMIT 10;

 id | x | lhs | rhs | diff |  grp  | x
----+---+-----+-----+------+-------+---
  1 | 2 |   1 |   1 |    0 | (2,0) | 2
  2 | 1 |   2 |   1 |    1 | (1,1) | 1
  3 | 2 |   3 |   2 |    1 | (2,1) | 2
  4 | 3 |   4 |   1 |    3 | (3,3) | 3
  5 | 3 |   5 |   2 |    3 | (3,3) | 3
  6 | 2 |   6 |   3 |    3 | (2,3) | 2
  7 | 3 |   7 |   3 |    4 | (3,4) | 3
  8 | 1 |   8 |   2 |    6 | (1,6) | 1
  9 | 3 |   9 |   4 |    5 | (3,5) | 3
 10 | 1 |  10 |   3 |    7 | (1,7) | 1
(10 rows)

You can see here all of the variables at play, in addition to x and id

The lhs is simple. We're just generating a unique sequential identifier with that (because we're feeding it a unique sequential identifier: id -- though never forget that ids are seldom gapless)
The rhs is slightly more complex. We partition by x and generate a sequential identifier with that amongst the different x values. Observe how rhs increments over the set each time it sees a row with a value that it has already seen. The property of rhs is how many times the value was seen.
The diff is the simple result of subtraction but it's not too useful to think of it like that. Think of more like subtracting a sequence than digits (though they're digits for any single row). We have a sequence increasing by one for the amount of times a value is seen. And, we have a sequence increasing by one for every distinct id (every time). Subtracting these two sequences will return the same number for repeating values, just like in our example above. (11,12,13) - (1,2,3) = (10,10,10). This is the same principle in the first section of this answer

diff doesn't independently mark the group by itself
diff forces all identical islands of x into a group (which may have false-positives, ex. the three cases where diff=3 above and their corresponding x values)

The group grp is a function of (x, diff). It serves as the grouping id, albeit in a slightly weird format. This serves to reduce false positives that would happen if we just grouped by diff.

Simple Unoptimized Query

So now we have our simple unoptimized query

SELECT x, diff, count(*)
FROM (
  SELECT
    id,
    x,
    dense_rank() OVER (ORDER BY id)
      - dense_rank() OVER (PARTITION BY x ORDER BY id)
    AS diff
  FROM foo
) AS t
GROUP BY x, diff;

Optimizations

On the matter of replacing dense_rank() with something else, like row_number(). @ypercube commented

  It also works with ROW_NUMBER() but I think it may give different results with different data. Depends if there are duplicate data in the table.

So let's review it, here is a query that shows

row_number() and dense_rank()
the respective differences computed.
over a result set that includes, ids, 1,2,3,4,5,6,7,8,6,7,8, and two different "islands" of x vals.

SQL Query,

SELECT
  id,
  x,
  dense_rank() OVER (w1) AS dr_w1,
  dense_rank() OVER (w2) AS dr_w2,
  (
    x,
    dense_rank() OVER (w1)
    - dense_rank() OVER (w2)
  ) AS dense_diffgrp,
  row_number() OVER (w1) AS rn_w1,
  row_number() OVER (w2) AS rn_w2,
  (
    x,
    row_number() OVER (w1)
    - row_number() OVER (w2)
  ) AS rn_diffgrp,
  x
FROM (
  SELECT id,
  CASE WHEN id<4
    THEN 1
    ELSE 0
  END
  FROM
  (
    SELECT * FROM generate_series(1,8)
    UNION ALL
    SELECT * FROM generate_series(6,8)
  ) AS t(id)
) AS t(id,x)
WINDOW w1 AS (ORDER BY id),
  w2 AS (PARTITION BY x ORDER BY id)
ORDER BY id;

Result set, dr dense_rank, rn row_number

 id | x | dr_w1 | dr_w2 | dense_diffgrp | rn_w1 | rn_w2 | rn_diffgrp | x
----+---+-------+-------+---------------+-------+-------+------------+---
  1 | 1 |     1 |     1 | (1,0)         |     1 |     1 | (1,0)      | 1
  2 | 1 |     2 |     2 | (1,0)         |     2 |     2 | (1,0)      | 1
  3 | 1 |     3 |     3 | (1,0)         |     3 |     3 | (1,0)      | 1
  4 | 0 |     4 |     1 | (0,3)         |     4 |     1 | (0,3)      | 0
  5 | 0 |     5 |     2 | (0,3)         |     5 |     2 | (0,3)      | 0
  6 | 0 |     6 |     3 | (0,3)         |     7 |     3 | (0,4)      | 0
  6 | 0 |     6 |     3 | (0,3)         |     6 |     4 | (0,2)      | 0
  7 | 0 |     7 |     4 | (0,3)         |     8 |     6 | (0,2)      | 0
  7 | 0 |     7 |     4 | (0,3)         |     9 |     5 | (0,4)      | 0
  8 | 0 |     8 |     5 | (0,3)         |    10 |     8 | (0,2)      | 0
  8 | 0 |     8 |     5 | (0,3)         |    11 |     7 | (0,4)      | 0

You'll see here, that when the column you're ordering by has duplicates you can't use this method because you can't ensure an ordering for a query that has duplicate in the ORDER BY clause. It's very much just the effect of the ordering of the duplicates that throws off the difference: the relationship of the global incremeneting value to the incrementing value over the partition. However, when you have an unique id column, or a series of columns that define uniqueness, by all means use row_number() instead of dense_rank().


Finding extents of Oracle BIN$ segments I am looking for information on recyclebin extents.

When dropping a table its segments are renamed to 'BIN$...' system generated names and the extents are hidden.

Will those extents still stop me from shrinking a data file (or tablespace)? If so I would like to account for them in the script.

Currently I am using a query inspired by AskTom's maxshrink.sql:

select tbs.TABLESPACE_NAME, df.FILE_NAME,
    round(MAX(e.BLOCKS+e.BLOCK_ID+1)*tbs.BLOCK_SIZE/1024/1024,2) "MinMB",
    round(MAX(df.BYTES)/1024/1024,2) "FileMB"
  from DBA_TABLESPACES tbs
  LEFT JOIN DBA_DATA_FILES df on tbs.TABLESPACE_NAME = df.TABLESPACE_NAME
  LEFT JOIN DBA_EXTENTS e on df.FILE_ID = e.FILE_ID AND df.RELATIVE_FNO = e.RELATIVE_FNO
  -- WHERE
  --   tbs.TABLESPACE_NAME like 'MY%'
  GROUP BY df.FILE_ID, df.RELATIVE_FNO, tbs.TABLESPACE_NAME, tbs.BLOCK_SIZE, df.FILE_NAME
  ORDER BY 1,2;

Update: Initially my question also asked why I no longer see the 'BIN$' segments created by Flashback Drop Table anymore in 12.2 anymore. However that was an error on my side, my test tables had simply no segments to begin with.

Do you know since when those segments are hidden(?), and is there a way to actually find their extents?

You can find their details from *_SEGMENTS views/synonyms.

SQL> select bytes from user_segments where segment_name='BIN$XYbYSYopQFTgUzIUqMA4vA==$0';

     BYTES
----------
     65536

  Will those extents still stop me from shrinking a data file (or tablespace)?

Yes, they may stop you to shrink the data files. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependent objects are not moved, they are simply renamed with a prefix of BIN$.

Consider the following(it's just for demonstration purpose only) example.

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> create tablespace tbs1 datafile size 1M;

Tablespace created.

SQL> alter user jay quota unlimited on tbs1;

User altered.

SQL> create table t1(id number, col1 varchar2(100)) tablespace tbs1;

Table created.

SQL> insert into t1 select level, 'dsfldshfkjdhsfkdshfdklsjfhdkhfdks'||level
     from dual connect by level<=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
    983040

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.

SQL> select ts#, name from v$tablespace;

       TS# NAME
---------- ------------------------------
        10 TBS1

1 rows selected.

SQL> select bytes from v$datafile where ts#=10;

     BYTES
----------
   1048576

SQL> select file# from v$datafile where ts#=10;

     FILE#
----------
        55

SQL> conn jay@orclpdb
Enter password:
Connected.
SQL> drop table t1;

Table dropped.

SQL> select object_name from recyclebin;

OBJECT_NAME
--------------------------------------------------------------------------------
BIN$XYY3afBNKwPgUzIUqMAIkA==$0
BIN$XYYmdj0XKLjgUzIUqMB2iA==$0

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> alter database datafile 55 resize 512K;
alter database datafile 55 resize 512K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> conn jay@orclpdb
Enter password:
Connected.
SQL> purge recyclebin;

Recyclebin purged.

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> alter database datafile 55 resize 512K;

Database altered.

  Hm, on my 12.2 system I see no BIN$ segments in *_SEGMENTS views (with SYS as SYSDBA user). And I think they never showed up in *_EXTENTS, or?

There is no recycle bin for SYSDBA privilege.

  Each user can be thought of as having his own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns.
  What is Recycle Bin?


Alternatives for a LEFT OUTER JOIN query Problem

I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS.

SQL Query

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT nlsGuid = CASE
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid
    END
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

Table

CREATE TABLE tsysNLS
(
   nlsGuid           nvarchar(207)   NOT NULL,
   nlsLocale         nvarchar(5)     NOT NULL,
   nlsClazz          nvarchar(100)   NOT NULL,
   nlsAttribute      nvarchar(100)   NOT NULL,
   nlsDisplayName    nvarchar(255)   NOT NULL,
   nlsOldname        nvarchar(50),
   nlsDescription    nvarchar(255),
   nlsShapefilename  nvarchar(10)
);

Example data

INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.en', N'en', N'Contact', N'firstName', N'Name - First Name', N'conFirstName', N'Name - First Name', NULL);
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de', N'de', N'Contact', N'firstName', N'Vorname', N'conFirstName', NULL, N'ConNamVor');
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de_AT', N'de_AT', N'Contact', N'firstName', N'Vorname (AT)', N'conFirstName', NULL, N'ConNamVor');

You can rewrite the LEFT JOIN to a subquery, but it may still compile as an outer join query - I have no knowledge of those aspects of the H2 platform.

SELECT *
FROM tSysNls
WHERE nlsGuid IN (
    SELECT COALESCE(
           --- first option:
           (SELECT de_AT.nlsGuid
            FROM tSysNLS AS de_AT
            WHERE de_AT.nlsClazz=x.nlsClazz AND
                  de_AT.nlsAttribute=en.nlsAttribute AND
                  de_AT.nlsLocale='de_AT'),
           --- second option:
           (SELECT de.nlsGuid
            FROM tSysNLS AS de
            WHERE de.nlsClazz=x.nlsClazz AND
                  de.nlsAttribute=en.nlsAttribute AND
                  de.nlsLocale='de'),
           --- third option:
           en.nlsGuid)
    FROM tSysNLS AS en
    WHERE en.nlsLocale='en'
        AND en.nlsClazz='Contact'
        AND en.nlsAttribute='firstName');

Or you could build some type of temp table (or a regular table if H2 doesn't support temp tables), then populate it in three steps. Again, this will probably still compile using an anti-semijoin or some other type of outer join, but maybe it'll function as a workaround for your bug:

--- 1. de_AT, if there are any:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de_AT' AND nlsClazz='Contact' AND nlsAttribute='firstName';

--- 2. de for those that don't have de_AT:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

--- 3. en for the remaining records:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='en' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

SELECT * FROM #temptable;

But these are not pretty solutions.


Constraint on existing table - unique combination of values only when another column is null I have a pre-existing table structured similarly to this:

[Username]   [Gateway]   [Code]   [ActiveTo]   [OrgId]
=======================================================
user1        gateway      50        Null        100
user1        gateway      50        Null        101
user2        gateway2     51        Null        102
user3        gateway3     52       12/08/17     103

This table already has duplicate combinations of [Username], [Gateway], and [Code].

I want to add a constraint such that any new users added have a unique combination of [Username], [Gateway], and [Code] but that ignores two pre-existing duplicates such as the two user1 entries above (not ignoring new entries that match pre-existing ones).

I only want this to be applied when the duplicate's [ActiveTo] value is null (new entries have ActiveTo as null by default). So:

Applying the constraint to the table above would ignore the two user1 duplicates.
Adding a row [user2], [gateway2], [52], [Null] would be fine.
Adding a row [user2], [gateway2], [51], [Null] would then throw an error.
Then adding a row [user3], [gateway3], [52], [Null] would be fine.

Any ideas?

Blatantly copied from A-K's answer in the similar question (Can I add a unique constraint that ignores existing violations?) and adjusted for this one.

It assumes there is an ID column with a unique constraint:

ALTER TABLE dbo.Example
ADD IgnoreThisDuplicate integer NULL;
GO
UPDATE E
SET IgnoreThisDuplicate = ID
FROM dbo.Example AS E
WHERE EXISTS
(
    SELECT *
    FROM dbo.Example AS P1
    WHERE
        P1.Username = E.Username
        AND P1.Gateway = E.Gateway
        AND P1.Code = E.Code
        AND P1.ActiveTo IS NULL
        AND E.ActiveTo IS NULL
        AND P1.ID < E.ID
);
GO
-- This constraint is not trusted
ALTER TABLE dbo.Example
WITH NOCHECK
    ADD CONSTRAINT CHK_Example_NoNewDuplicates
    CHECK (IgnoreThisDuplicate IS NULL);
GO
-- Applying the constraint to the table above would ignore the two user1 duplicates.
GO
SELECT * FROM dbo.Example;
GO
CREATE UNIQUE INDEX
    UNQ_Example_UniqueNewUsernameGatewayCode
ON dbo.Example
    (Username, Gateway, Code, IgnoreThisDuplicate)
WHERE
    ActiveTo IS NULL;

Test it out: db<>fiddle demo


Check on whether a person is subscribed to another person I have a table of subscribers. It has a connection to the user table.

 id | user_id | follower_id |
-----------------------------
 1  |   46    |     71      |
 2  |   46    |     53      |
 3  |   46    |     67      |
 4  |   46    |     34      |
 5  |   89    |     71      |
 6  |   50    |     32      |
 7  |   89    |     53      |
 8  |   12    |     34      |

I am user with id 89. I display all subscribers of the user with ID 46

SELECT F.USER_ID, F.FOLLOWER_ID, U.USERNAME, U.NAME, U.LAST_LOGIN, (U.USER_ID IS NOT NULL) AS IS_FOLLOWER
FROM FOLLOWERS AS F
INNER JOIN USERS AS U ON (U.USER_ID = F.USER_ID)
WHERE F.USER_ID = '46'

I get the result

 id | user_id | follower_id | if_follower |
-------------------------------------------
 1  |   46    |     71      |    true     |
 2  |   46    |     53      |    true     |
 3  |   46    |     67      |    true     |
 4  |   46    |     34      |    true     |

My problem is how do I check that I am subscribed to users with indexes 52, 53, 67, 89?
What am I waiting for as a result

 id | user_id | follower_id | if_follower |
-------------------------------------------
 1  |   46    |     71      |    true     |
 2  |   46    |     53      |    true     |
 3  |   46    |     67      |    fale     |
 4  |   46    |     34      |    fale     |

That is, I received a list of the subscribers of the user with ID 46 and found that I subscribed to users with IDs 71 and 53

Test

SELECT F.USER_ID,
       F.FOLLOWER_ID,
       U.USERNAME,
       U.NAME,
       U.LAST_LOGIN,
       F2.FOLLOWER_ID IS NOT NULL AS IS_FOLLOWER
FROM FOLLOWERS AS F
INNER JOIN USERS AS U ON (U.USER_ID = F.USER_ID)
LEFT JOIN FOLLOWERS AS F2 ON F.FOLLOWER_ID = F2.FOLLOWER_ID
                         AND F2.USER_ID = 89
WHERE F.USER_ID = '46'


Does SQL/MED (ISO/IEC 9075-9) defines a writable foreign data wrapper interface I just can't find anything about it.

I don't have the paper or draft of the SQL:2011, and have almost only drafts from SQL:2008, 2006, 2003.

Does anyone know if "SQL/MED (ISO/IEC 9075-9)" defines a writable foreign data wrapper interface?

Yes, it does define a writable interface.


Java-Hibernate-Oracle: Can a fluctuating DB connection duplicate insert statement?   Is it possible that a fluctuating DB connection causes insert statement to execute multiple times?
Thanks

An interrupted client connection can lead up to a scenario where the insert statement executes multiple times. It is possible for the client to miss the commit message. This scenario is documented in the manual: "A crash occurring after a successful write to the log can prevent the success message from returning to the client. In this case the client cannot tell whether or not the transaction committed." If the client is designed to retry in such a scenario, then the insert statement will run twice.

Oracle 12c feature "Transaction Guard" is designed to protect against such a scenario. Transaction Guard guarantees "At-Most-Once Execution".

http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_trans_idemp_guard.htm#ADFNS8000


Blank (but not null) columns - PostgreSQL 9.2 Doing by:

select * from ja_customers where id = 8154501

I receive:

You can see that all the required fields are "blank" with some invisible character, so they're not null, so they're valid (technically)

Question:

How can I see which characters are in there?

You can use get_byte function to detect which character in specific offset:

SELECT *, get_byte(name_first,0) as firstChar,  get_byte(name_first,0) as secondChar FROM ja_customers WHERE id = 8154501


Is there a collation to sort the following strings in the following order 1,2,3,6,10,10A,10B,11? I have a database with a VARCHAR column that contains integers of varying length. I want to sort them so 10 comes after 9, not 1, and 70A comes after 70. I was able do this with PATINDEX(), a CTE, and CASE statements in the WHERE clause.

However, I was wondering if there was a collation where this would be unecessary.

No. Collation is about alphabetical sorting, depending on code page, accent, case, width, kana. Numbers characters (0-9) have none of there properties.

So 9 is always after 10B in any sort.

You have to split it up as you noted or sort like this:

ORDER BY
    RIGHT('                              ' + MyColumn, 30)

The length in the right determines how many spaces you have.

You could of course:

have 2 columns to make this unnecessary (and far quicker) and have a computed column to combine them
insist on leading zeros
right justify in a char (a stored version of my RIGHT above)

The latter 2 suggestions are like my RIGHT above and slightly different. Quicker to sort (no processing of the colukmn needed) but more storage required


How to call a windows program in mysql prompt in windows I want to call an OS level commands from mysql prompt within Windows command line tool.

In Unix* there is a system command available to execute OS commands within MySQL prompt.

Any way of dong the same in windows or even in workbench as well?

The objective is to invoke a OS command within a mysql prompt.

You cannot run OS commands in a MySQL CLI nor in MySQL Workbench. Why would you do that (except for trying to hack a machine via an SQL connection)? If you need to run an OS command, open a normal shell window (that still stands).

Update:
The system command only works on Unix like OSes (http://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html), e.g. Linux + Mac.

MySQL Workbench doesn't support most of these CLI commands however (use and delimiter are 2 that are supported).


What is the optimal way to upgrade production RDS instance? I have MySQL  small RDS instance as part of my production system and I want to upgrade it to medium instance with provided IOPS.

As old-school  DBA I'm aware about "add slave; promote to master; switch clients" method, but AWS promises to provide magic one-click upgrade path, i.e. "upgrade instance", "add  provided IOPS".

Tried this on test RDS instance, downtime is too long, IMHO: about 5 min for small->medium upgrade, and 30 min (!!!) for switching to provided IOPS.

Is this normal behavior?
Is there any way to run upgrade on production RDS w/o downtime?
Do you recommend "stop; create a snapshot;  restore from snapshot to bigger instance" way?

Upgrading an instance in RDS means RDS will be physically migrating the database to a new instance, likely on a different physical host, so downtime would not be avoidable.  Migrating to provisioned IOPS would likely mean your data would be migrated to a new EBS volume (and the server might be migrated to a new instance as well with this change, depending on whether, internally, machines capable of accessing EBS volumes with provisioned IOPS are physically segregated from machines that aren't, so that they can be on a different class of network hardware) so downtime would again be inevitable.
There appears to be a way to avoid this disruption: a Multi-AZ deployment, which creates an invisible and inaccessible (to you) replica in another availability zone within the region.

In the case of system upgrades like OS patching or DB Instance scaling, these operations are applied first on the standby, prior to the automatic failover. As a result, your availability impact is limited only to the time required for automatic failover to complete.
— http://aws.amazon.com/rds/multi-az/

That should provide a quick and seamless migration path, though I have not had occasion to test this capability.  "Modify" in the console appears to allow you to convert an instance to Multi-AZ.  Presumably, this would result in brief I/O freeze as the instance is cloned, so I of course would recommend testing all of this functionality before trying it.
Alternately, RDS supports an internal mechanism that should allow you to emulate the "add slave; promote to master; switch clients" operation, and this also should allow you to achieve a near-zero-downtime conversion:

Create an actual RDS read replica of your database with the desired instance class
Wait for the replica to come online and be synched with the master
Modify the replica's configuration to add Provisioned IOPS
Wait for the replica to come online and be synched with the master
Verify that both systems have identical data using 3rd party tools
Disconnect your application from the old master
Verify matching binlog coordinates on master and replica to assure that all application writes have replicated
Split the systems with "Promote Read Replica" on the new replica in RDS
Connect your application to the new master

http://aws.amazon.com/about-aws/whats-new/2012/10/11/amazon-rds-mysql-rr-promotion/


WiredTiger panic when attempting to rename .turtle leads to loss of all data We've seen this twice this week on separate servers (one staging, one production).

2017-10-19T12:50:37.525-0400 I ACCESS   [conn266] Successfully authenticated as principal ********* on admin
2017-10-19T13:00:42.782-0400 E STORAGE  [thread2] WiredTiger (-28817) [1508432442:782769][1520:8790690042448], file:WiredTiger.wt, WT_SESSION.checkpoint: c:\mongo\data\WiredTiger.turtle.set to c:\mongo\data\WiredTiger.turtle: file-rename: rename: Cannot create a file when that file already exists.
2017-10-19T13:00:42.784-0400 E STORAGE  [thread2] WiredTiger (-28817) [1508432442:784770][1520:8790690042448], checkpoint-server: checkpoint server error: Cannot create a file when that file already exists.
2017-10-19T13:00:42.785-0400 E STORAGE  [thread2] WiredTiger (-31804) [1508432442:785770][1520:8790690042448], checkpoint-server: the process must exit and restart: WT_PANIC: WiredTiger library panic
2017-10-19T13:00:42.785-0400 I -        [thread2] Fatal Assertion 28558
2017-10-19T13:00:42.785-0400 I -        [thread2]

***aborting after fassert() failure

2017-10-19T13:00:42.805-0400 I -        [conn259] Fatal Assertion 28559
2017-10-19T13:00:42.806-0400 I -        [conn259]

***aborting after fassert() failure

The result is that the admin database is wiped out.  To recover we need to disable authentication and recreate all users.  The original data files seem to contain data, but it's no longer visible from the shell commands.

Both servers have been running fine, independently for months.  They are running the same version of Mongo but very different versions of the software which interacts with it.

I suppose I have two questions:

1) What might cause this particular failure?
c:\mongo\data\WiredTiger.turtle apparently shouldn't exist at this point, but it does; what can cause this?  They have extensive security protocols including Bit9 and anti-virus solutions.  I suggested a scheduled defrag job (they're running SSDs so defrag should NEVER run, but hey...) but they can't find any evidence that a defrag happened on either server.

2) What might cause them to experience the same crash a day or two apart?
The only thing in common on the two systems is Mongo.  As an aside, we're going to suggest journaling and replication (their production environment is still in its test rollout, so the downtime and loss of data was acceptable this time).

What version you are using? This is known bug, what has been fixed at versions 3.2.13, 3.4.4, 3.5.6 forward.


Error during table creation : is not valid at this position expecting an identifier I keeping getting this error during table creation: Order is not valid at this position expecting an identifier. I look up on the Web but I can't figure out how to fix this. What that error means, and how to fix it?

Create Table Customer
(
CustomerID Int Primary Key,
CustomerName varchar(20) Not Null,
CustomerContactNo varchar (10)Not Null,
CustomerAddress varchar(50)Not Null,
CustomerEmail varchar(25)Not Null
)
ENGINE=INNODB;

Create Table Employee
(
EmployeeID TinyInt Primary Key,
EmployeeName varchar(20) Not Null,
EmployeeContactNo varchar(10)Not Null,
EmployeeEmail varchar(25)Not Null
)
ENGINE=INNODB;

Create Table Order
(
OrderNo Int Primary Key,
Foreign Key(CustomerID) REFERENCES Customer(CustomerID),
Foreign Key(EmployeeID) REFERENCES Employee(EmployeeID)
)ENGINE=INNODB;

You are using a reserved word "Order" as a table name.

This is possible, though still not a good idea, if you (always) embrace the name in back-ticks, i.e.: `Order`. It would probably be better to come up with a different table name, though.

By the way, your Order table has a few other issues as well. You need to add the CustomerID and EmployeeID columns before you can create foreign keys with them:

Create Table `Order` (
  OrderNo Int Primary Key,
  CustomerID Int,
  EmployeeID TinyInt,
  Foreign Key(CustomerID) REFERENCES Customer(CustomerID),
  Foreign Key(EmployeeID) REFERENCES Employee(EmployeeID)
) ENGINE=INNODB;

Also, are you sure tinyint is the data type you want for EmployeeID? It's got a very limited range, only [-128, 127].

Another good idea is to specify your ints (in all the tables) as unsigned, i.e.: CustomerID Int unsigned, since presumably you won't be using negative IDs.


SQL OLTP Filegroups - Lookup Tables According to resources, in Dimensional Warehousing, it is a good idea to place Dimension tables in a different filegroups from Fact Tables. In OLTP, is it a good idea to place Lookup tables in filegroups, from Transactional Tables. OLTP and Datawarehousing are different. (As Lookup tables are different from Dimensional Tables, Relational vs Kimball). Just wanted confirm this idea?

Microsoft FileGroups

SQL Saturday

•   "Put different tables used in the same join queries in different filegroups. This will improve performance, because of parallel disk I/O searching for joined data.

•   To maximize performance, create files or filegroups on different available disks as possible. Put objects that compete heavily for space in different filegroups.

•   Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups. This will improve performance, because of parallel I/O if the files are located on different physical disks."

In theory, absolutely. In practice, it depends and most likely not.

Data warehouses will be a lot more likely to do full scans and disk reads/writes due to the nature of a DW.  Optimizing a DW can be very important to get reports out in time, saving money in not buying expensive disks if not needed, and also to reduce wear/tear on the machines doing too many IOPS trying to get the same data.  They will often do a big (or a few) table scan(s) and get all the relevant data which pushes a lot of items out to disk.

A lot of OLTP can be ran out of memory in a relatively managed environment where bad ad hoc queries aren't causing memory buffer issues.  If for some terrible reason your OLTP environment is doing a lot of table scans, you see memory buffer pool pressure from table data, with high disk read/sec and disk write/sec metrics in perfmon, AND you're not able to fix the code, this would be helpful.

Note though with customization comes overhead.  The additional complexity have to be properly documented, perhaps automated, and maintained.  If you don't see any benefits to it then why introduce additional complexity?


How to connect to mysql on a server My uncle gave me access to his server so I could play around with mysql and php. He installed php and mysql on it created a user as well folder for me to play around in. I was wondering how to access mysql from my laptop. I am using filezilla to connect to the server, through ssh. Do I need a client version that connects to the server or anything like that?

There are several applications for mysql access, below are free ones.

mac os -> sequel pro www.sequelpro.com
windows -> sqlyog community https://code.google.com/p/sqlyog/downloads/list


Containment check tstzrange @> timestamptz not using btree or gist index Schema:

        Column        |           Type
----------------------+--------------------------
 id                   | integer
 event_id             | integer
 started_at           | timestamp with time zone
 ended_at             | timestamp with time zone
 created_at           | timestamp with time zone
    "event_seat_state_lookup_pkey" PRIMARY KEY, btree (id)
    "event_seating_lookup_created_at_idx" btree (created_at)
    "event_seating_lookup_created_at_idx2" gist (created_at)

Query:

SELECT id
FROM event_seating_lookup esl1
WHERE
  tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)') @> esl1.created_at;

Explain analyze:

Table with <100k rows.

 Seq Scan on event_seating_lookup esl1  (cost=0.00..1550.30 rows=148 width=4) (actual time=0.013..19.956 rows=29103 loops=1)
   Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @> created_at)
   Buffers: shared hit=809
 Planning Time: 0.110 ms
 Execution Time: 21.942 ms

Table with 1M+ rows:

Seq Scan on event_seating_lookup esl1  (cost=10000000000.00..10000042152.75 rows=5832 width=4) (actual time=0.009..621.895 rows=1166413 loops=1)
  Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @> created_at)
  Buffers: shared hit=12995
Planning Time: 0.092 ms
Execution Time: 697.927 ms

I have tried:

VACUUM FULL event_seating_lookup;
VACUUM event_seating_lookup;
VACUUM ANALYZE event_seating_lookup;
SET enable_seqscan = OFF;

Problem:

event_seating_lookup_created_at_idx or event_seating_lookup_created_at_idx2 indexes are not being used.

Notes:

PostgreSQL 11.1.
btree_gist extension is installed.
I have tried equivalent setup with created_at timestamp without time zone and using tsrange; same result.
I understand that rewriting the query with >=, < checks would make it use the btree index. The question is what is the reason the index is not used with the tstzrange containment operator and if there is a way to make it work.

The question is what is the reason the index is not used with the tstzrange containment operator and if there is a way to make it work.

The reason is quite trivial. B-tree indexes do no support the containment operator @>. Neither for range types like tstzrange nor for any other type (including array types).

The manual:

  ... a btree operator class must provide five comparison operators, <, <=,
  =, >= and >.

And GiST indexes do not support <, <=, > =, >= and >. See these chapters of the manual:

Built-in Operator Classes for GiST indexes
Behavior of B-Tree Operator Classes

In Postgres indexes are bound to operators (which are implemented for certain types), not data types alone or functions or anything else. Related:

Optimizing queries on a range of timestamps (two columns)
Can PostgreSQL index array columns?

The GiST index event_seating_lookup_created_at_idx2 you have is pointless. It is created on the timestamptz column created_at. Such a GiST index would be useful for a range type (the opposite direction of logic).

Creating a GiST index on a timestamptz column is only possible since you installed the additional btree_gist extension to allow such useless indexes. (There are useful applications for multicolumn indexes or exclusion constraints ...) In stock Postgres you'd get an error:

  ERROR: data type timestamp with time zone has no default operator class for access method "gist"

So while it's logically valid and technically possible to use a btree index (or a GiST index) for the query, the case is not implemented: no index support for timestamptz <@ tstzrange (where timestamptz would be the indexed expression!). It can be solved with <, <=, >, >= more efficiently. So I guess no developer felt (or will feel) the need to implement it.

Function to rewrite expression with favorable operators

Your implementation makes sense to me. It does what you want and due to function inlining makes use of a plain btree index on the timestamp column - event_seating_lookup_created_at_idx in your example. For calls with a constant range (like in a single function call) I suggest this modified version:

CREATE OR REPLACE FUNCTION in_range(timestamptz, tstzrange)
  RETURNS boolean AS
$func$
SELECT CASE WHEN lower_inc($2) THEN $1 >= lower($2) ELSE $1 > lower($2) END
   AND CASE WHEN upper_inc($2) THEN $1 <= upper($2) ELSE $1 < upper($2) END
$func$  LANGUAGE sql IMMUTABLE;

Declare it IMMUTABLE, because it actually is. Not to help function inlining (can even prevent it if the declaration is false) but for other gains. Related:

Can declaring function volatility IMMUTABLE harm performance?
PostgreSQL Inline Function Behavior

It can be inlined and uses the index just like your version. The difference: this one suppresses a redundant index condition for exclusive bounds. Fortunately, your consideration in this regard is slightly off target:

  The reason for first matching on the $1 >= lower($2) AND $1 <= upper($2)
  condition and then checking the upper_inc and lower_inc
  constraints is to benefit from range scan first and then filter the result.

Postgres 11 (at least) is even smarter than that. I see no Filter step for your version. For default [) bounds (like in your example), I get this query plan (line breaks in conditions added by me):

  ->  Index Only Scan using foo_idx on foo (actual rows=5206 loops=1)
        Index Cond: ((datetime >= '2018-09-05 22:00:00+00'::timestamp with time zone)
                 AND (datetime <= '2018-09-05 22:30:00+00'::timestamp with time zone)
                 AND (datetime < '2018-09-05 22:30:00+00'::timestamp with time zone))

An actual Filter step could add a more substantial cost for corner cases with many hits on an excluded bound. Those would be fetched from the index and then discarded. Quite relevant for time ranges where values often end up on bounds - like timestamps on the full hour.

The actual difference is see is minor, but why not take it?

  ->  Index Only Scan using foo_idx on foo (actual rows=5206 loops=1)
        Index Cond: ((datetime >= '2018-09-05 22:00:00+00'::timestamp with time zone)
                 AND (datetime < '2018-09-05 22:30:00+00'::timestamp with time zone))


Why are tiny Innodb row updates causing so much disk IO? This is a followup question to my earlier How can I determine which Innodb table is being written?

Using the accepted answer from the above-referenced question I was able to determine which (two) of a hundred Innodb tables were being constantly updated on my server. Using some trial and error, I was able to determine which specific rows in those tables were being updated.

The rows are essentially counters that are updating and updates are happening about 1/s (I ran a query that counted 54 updates in 60 seconds). The rows are tiny with 4 columns: BIGINT, VARCHAR, LONGTEXT, VARCHAR. The value in the LONGTEXT column of the row in question is just a 10-character string and that is the column that is being updated.

I ran a query that calculated the change in Innodb_data_written over 60 seconds and ran it several times. It ranged from 4.5 to 5.5 MB over 60 seconds. When I turned off the process that is responsible for the 1-character row updates, the change in Innodb_data_written over 60 seconds went down to .03 MB, so it definitely seems to be these tiny row updates that are responsible for the ~ 5MB per minute of Innodb disk IO.

The innodb_buffer_pool_size is ample and the Innodb buffer use percentage is around 30%.

So, the question is, how are a couple of 1/s row updates that are only changing 1 character in a tiny row generating ~ 5MB per minute of Innodb disk IO?

Any UPDATE to a row requires the following (sooner or later):

Read the 16KB block containing the row (unless cached in the buffer pool)
Write the block back to disk (or cache it a long time)
The "doublebuffer" is written once per transaction
Do one write to the InnoDB log for transactional integrity.  (The
details here depend on various settings and whether you wrap things
in BEGIN..COMMIT.)
Since you say the buffer_pool is big enough (apparently for the entire table and any secondary indexes?), the first two items will not tally many IOPs.

But that was not your question.

The actual I/O (pages) related to the buffer_pool is (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed)

You will probably find this very low because of good caching:
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests


Should I always make an attribute id that's an primary key? I was thinking, sometimes several columns in a table uniquely specify the whole row.
In that case, is there a need to create a special column for a primary key?
Is that good for something?

There are two schools of thought about this and some of it is database dependent.

In general, on PostgreSQL, I define natural primary keys wherever I can (and make these composite keys where appropriate) and I (usually) add a secondary, surrogate id key which is an integer and is used as a surrogate for the primary key in joins.  I see this as a good way to navigate the positives and negatives of natural primary keys.  Note that if I was on MySQL, I would probably set the id as a primary key just because of how InnoDB works regarding indexes and primary keys.

Benefits of Natural Keys

A natural key is a piece of collected data which identifies a row in a relation.  Occasionally there are no natural keys but this is rare.  Natural keys can be a single column or they can be a composite key of several columns.  There are several important benefits to natural keys.

Defining natural keys is necessary for normalization.  Defining them in your DDL makes normalization mistakes more obvious.  This leads to clearer database design.
Forcing you to think about natural keys keeps data clear and avoids missing unique constraints.

Drawbacks of Composite Keys

Composite keys however pose performance issues when used in JOIN conditions.  Heuristic planners have a harder time estimating numbers of rows returned by multi-column keys because the co-incidence rate is not necessarily known between key columns, and these may not be well distributed and this can throw planner estimates off.

Additionally this leads to wider join criteria which in turn leads to more memory used in joins.  Additionally, and for obvious reasons, it takes significantly more computing power to calculate a multi-column join than a single column one.

Finally, natural keys are often "discovered" rather than defined.  Changing a composite key as a join condition is a really error-prone hassle I am quite happy to live without.

Where a key is text, definitional, and the most commonly used piece of information in a table, I will often use it as the join key, but it is quite rare that all of these are met.

Database Implementation Variables

Consider two different databases, PostgreSQL which uses heap tables with external indexes (so primary key indexes are not privileged over unique indexes, and tables can be scanned in physical order), and InnoDB which uses tree structures in the table, and contains the row, essentially, in the primary key btree index.  With MySQL's InnoDB, you must traverse the designated primary key on every query, even if other indexes are used, and physical-order scans are not supported.

In the first, case, performance is not adversely affected by using a non-pkey index, but in the second case it is and so you really want to have your primary key be the most frequently used unique index.


PostgreSQL: DBLink weird permission/connection error The goal is to (1) create a connection, (2) return a view of another database, and (3) close the same connection all to be stored in a view (no stored procedure or embedded password).  I came up with the following code, which uses a trust account and seems to work, until filtering on a boolean field.  Searching for false (field=false) works, but searching for true (field=true) or IS NOT NULL prompts for the password.

It's really odd that the data is being returned w/o needing the password, but when filtering, that is when the password is required.

PostgreSQL: v8.4:

There are two databases: db_remote and db_local
There are two accounts:

trusted_user (set up as trust in pg_hba.conf to both databases)
md5_user (set up as md5 in pg_hba.conf to both databases)

db_remote has a view (v_sessions), which contains two fields: a text field and a boolean field, which is created with a CASE statement.

From within db_local (as md5_user):

-- Creates okay:
CREATE VIEW v_sessions AS
SELECT * FROM (
     select  '1' query_type,'' as username, false as is_logged_in
     from    dblink_connect_u('connection', 'host=development dbname=db_remote user=trusted_user')
     union
     select  '2' query_type, username, is_logged_in
     from    dblink('connection', 'select username, is_logged_in from v_sessions') as v_session(username text, is_logged_in boolean)
     union
     select  '3' query_type,'',false
     from    dblink_disconnect('connection')
) v_sessions
WHERE query_type=2;

-- Calling the view with filter:
SELECT * FROM v_sessions WHERE is_logged_in;

What works:

removing the AND is_logged_in
filtering on a text field AND username = 'some value' !!
filtering on the boolean field for a false (AND is_logged_in = false) !!

What doesn't work:

filtering on the boolean field for a true value: AND is_logged_in IS NOT NULL, AND is_logged_in, or AND is_logged_in = true, gives the following error message:

  ERROR:  password is required
  DETAIL:  Non-superusers must provide a password in the connection string.

Filtering inside the second query works, but it doesn't help when wanting to store this in a view.

I suppose I'm looking for suggestions, but really trying to understand what is going on eg. why searching for false works and true does not - I first thought the connection was preemptively closing, but it's not.

Here is what I think is happening.  I don't think it is a bug.

You have a view.  That view is somewhat complex but it is a dynamically rewritten query.
The planner is pretty smart.  It will ignore UNION clauses where the search criteria will not produce an output result.
You are not executing what you think you are.  (EXPLAIN ANALYSE should show you this)

My recommendations:

Wrap the whole result set in a plpgsql function.  Wrap that function in the view.  OR
Use a WITH clause.  I am less sure about this one.  I think the with clause might result in running the whole thing consistently but not 100% sure, and it is not guaranteed in future versions.  Therefore wrapping in a function is probably better.

I think what the planner is seeing is:

"Oh we don't need to execute the first and third portions of the view because they are not in the output!  Let's skip and only run the second portion!"  This is a feature, not a bug (and it is what allows table partitioning to be useful).


Why we use low numbers for concurrent in MySQL benchmarks or HTTP_LOAD? I've seen so many times that we use numbers like 5,6 or 10 at most. What is this concurrency? Doesn't it refer to number of simultaneous users in bench?

EDIT
I'm not talking about any specific benchmarking tools, I mean in general. In benchmarks for databases OR for apache servers we use low concurrent numbers. why is that the case?

It really depends on what you are modeling. For mysql there are a few different definitions for simultaneous users.

Number of database connections, including idles that only take up memory.
Number of running queries.
Number of queries active in the database engine. This is typically ignored.

With a typical web application, you may have a hundred connections open to the database, but only a handful actually running queries at any single time. You can view this number by running SHOW STATUS LIKE 'Threads_running';. Perhaps run it in a loop and see how it changes. Benchmarking the inactive connections is typically a waste as they just use some memory and not much cpu. So if your application only is running x number of concurrent queries at a time, might as well only benchmark x, even if it's a small number.


Execution plan vs STATISTICS IO order SQL Server graphical execution plans read right to left and top to bottom. Is there a meaningful order to the output generated by SET STATISTICS IO ON?

The following query:

SET STATISTICS IO ON;

SELECT  *
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
        JOIN Production.Product AS p ON sod.ProductID = p.ProductID;

Generates this plan:

And this STATISTICS IO output:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 3, read-ahead reads 1277, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 1, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 1, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, I reiterate: what gives? Is there a meaningful ordering to STATISTICS IO output or is some arbitrary order used?

My initial playing around with various queries suggested no pattern at all but on paying closer attention it appears to be predictable for serial plans. I ended up at KB314648 which @AustinZellner mentions:

  Each SQL Server connection has an associated process status structure
  (PSS) that maintains connection-specific state information. Each
  unique server process ID (SPID) in the sysprocesses system table
  represents a different PSS, and the information in the sysprocesses
  virtual table is a "view" into this status information.

And the section relevant to your question:

  If STATISTICS IO is enabled for a connection, SQL Server allocates an
  array during query execution to track IO information on a per-table
  basis. As SQL Server processes the query, it records each logical
  request for a page in the appropriate table's entry in this array,
  along with whether that logical IO request resulted in a physical IO.
  SQL Server returns the information, at the end of the query, in error
  message 3615.

The observed behaviour suggests that entries are made to the array in the order that IO is generated, essentially the result of GetNext() on a physical operator. The last entry in the statistics output is the first table that resulted in an IO being recorded, the first entry is the last table. I’d speculate that the order for parallel plans is not predictable (or less so) as there is no guarantee as to which parallel task will be scheduled first.


How to avoid primary key violation while doing inserts I have a code which does insert into destination table from source, but at times we are ending up with deadlocks.

Due to deadlock , I have to rerun this code again.

while i do re-run , I end up with Primary key violation. Hence I want to add a check to insert only those rows which doesn't exist.

Can some help me with Tsql for this please

    declare @batchSize int = 1000
    INSERT INTO [dbo].[Destinationtable]
       ([PacketId]
       ,[DeviceId]
       ,[PacketDate]
       ,[PacketNumber]
       ,[Processed]
       ,[CreateDate]
       ,[NewReadingId])
 SELECT top (@batchSize)
       [PacketId]
      ,[DeviceId]
      ,[PacketDate]
      ,[PacketNumber]
      ,[Processed]
      ,[CreateDate]
      ,newid()
  FROM [dbo].[SourceTable]
  where Processed is null
  order by CreateDate

Error message when i run after deadlock

Violation of PRIMARY KEY constraint 'PK_packetid'. Cannot insert duplicate key in object 'dbo.destinationtable'. The duplicate key value is (1234567).

use not exists

declare @batchSize int = 1000
INSERT INTO [dbo].[Destinationtable]
   ([PacketId]
   ,[DeviceId]
   ,[PacketDate]
   ,[PacketNumber]
   ,[Processed]
   ,[CreateDate]
   ,[NewReadingId])
SELECT top (@batchSize)
   [PacketId]
  ,[DeviceId]
  ,[PacketDate]
  ,[PacketNumber]
  ,[Processed]
  ,[CreateDate]
  ,newid()
FROM [dbo].[SourceTable]
where Processed is null
  and not exists (select 1 from [Destinationtable]
                  where [PacketId] = [SourceTable].[PacketId])
order by CreateDate


What is the best way to stage data in SSIS? Using the SSIS visual designer, my goal is to load data from a CSV into a table of database X. To do this I've opted to 'stage' the data from the CSV and then select from that staged resource into the live tables (A single CSV will get split into may tables).

I can see two methods of achieving this 'staging' environment. Which of these would be considered best practice? (or what alternatives are recommended)?

As far as I know, either I can insert from a CSV into a TVP (Table Valued Parameter) and select from there (which I think is an in-memory resource), or I can insert into a static table, select from there, and then delete from the static table.

To me it seems that the static table would be slower but better in that it would allow for much larger CSV source files and not place a toll on system memory. Particularly if using a cloud box, the memory of a system might be limited. But then again it's unlikely I would ever get a CSV larger than a few hundred MB, so perhaps that doesn't matter.

I think it is better to pass values as a Table Valued Parameter (TVP).

TVPs can efficiently pass large amounts of data.

This is efficient because the client API internally bulk inserts the rows into tempdb before executing the procedure. And you don't need to load all the data into memory on the client.

You can also take a look at this helpful article to learn more on TVP performance:

Maximizing Performance with Table-Valued Parameters


SQL 2000 Maintenance Scripts Hi I've stumbled into the administration of a Legacy SQL 2000 server that has had next to no maintenance for the past 8 years (and is very slow).  As I have only been doing DBA / PT for the past 2 years (I'm primarily a Developer) I don't have allot of experience on 2000.  Can anyone point me in the direction of some maintenance, tuning scripts.  Just figured I'd ask first before I reinvent the wheel for something I will rarely use.

Thanks Guys / Girls !

Only recently waved goodbye to the last SQL2K server I looked after, so have a few scripts in the toolbox still. You could also root around in the SSC scripts archive.

Here's an old index maintenance script by Kimberly Tripp:

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- UPDATE: AUGUST 25, 2004
-- RE: Fix for DESC clustered indexes. There are no errors for DESC in
-- nonclustered indexes. Thanks!
--
-- For more scripts and sample code, check out
--  http://www.SQLSkills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Execute this whole script to create the sp_RebuildIndexes stored procedure in Master.
-- Best Viewed with Courier New 12pt. and Tabs saved as 4 spaces not 8. (Tools, Options, Editor)

-- To use the sp_RebuildIndexes procedure once created use:
-- sp_RebuildIndexes
--      To Rebuild All Indexes on All Tables for all that have a Scan Density < 100%
-- sp_RebuildIndexes @ScanDensity = 80
--      To Rebuild All Indexes on All Tables with a Scan Density of < 80%
-- sp_RebuildIndexes 'Authors'
--      To Rebuild All Indexes on the authors table - for a Scan Density of < 100%
-- sp_RebuildIndexes 'Authors', 80
--      To Rebuild All Indexes on the authors table - for a Scan Density of < 80%
-- Object Name and ScanDensity are both optional parameters.
-- ScanDensity must be a whole number between 1 and 100.

USE master
go
IF OBJECTPROPERTY(object_id('sp_RebuildClusteredIndex'), 'IsProcedure') = 1
    DROP PROCEDURE sp_RebuildClusteredIndex
go

IF OBJECTPROPERTY(object_id('sp_RebuildIndexes'), 'IsProcedure') = 1
    DROP PROCEDURE sp_RebuildIndexes
go

CREATE PROCEDURE sp_RebuildClusteredIndex
(
    @TableName      sysname     = NULL,
    @IndexName      sysname     = NULL
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- NOTE: If your clustered index is NOT unique then rebuilding the clustered
-- index will cause the non-clustered indexes to be rebuilt. If the nonclustered
-- indexes were fragmented then this series of scripts will build them again.
-- This is something this script DOES NOT look for...because you should not create
-- clustered indexes on a non-unique column or composite set of columns.
IF @TableName IS NOT NULL
    BEGIN
        IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
                AND OBJECTPROPERTY(object_id(@TableName), 'IsView') = 0)
            BEGIN
                RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
                RETURN
            END
        ELSE
            BEGIN
                IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
                    BEGIN
                        RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
                        RETURN
                    END
            END
    END

IF @IndexName IS NOT NULL
    BEGIN
        IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') = 0
            BEGIN
                RAISERROR('Index: %s exists but is a Clustered Index. This procedure only accepts valid table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
                RETURN
            END
        ELSE
            BEGIN
                IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') IS NULL
                    BEGIN
                        SELECT @TableName, @IndexName, INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered')
                        RAISERROR('There is no index with name:%s on this table. Please check the table name and index name as well as location (which database?). This procedure only accepts existing table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
                        RETURN
                    END
            END
    END

-- So now we have a valid table, a valid CLUSTERED index and we're ready to rebuild.
    -- Here's a quick overview of what this code will do:
        -- Get the Column List and Index Defintion (Use the output from sp_helpindex)
        -- Figure out if it's UNIQUE - to specify in CREATE INDEX statement
        -- Build and Execute the CREATE INDEX command through dynamic string execution

DECLARE @ExecStr        nvarchar(4000) -- more than enough even if 16 cols of 128 chars,
                                       -- Tablename of 128 and Indexname of 128...
                                       -- but if this is the case you have other problems :).
        , @ColList      nvarchar(3000)
        , @Unique       nvarchar(7)    -- Will be either '' or 'Unique ' and added to CR Index String
        , @FillFactor   nvarchar(100)

CREATE TABLE #IndexInfo
(
    IndexName   sysname,
    IndexDesc   varchar(210),
    IndexKeys   nvarchar(2126)
)

INSERT INTO #IndexInfo
    EXEC sp_helpindex @TableName

SELECT @ColList = IndexKeys
         , @Unique = CASE
                        WHEN IndexDesc LIKE 'clustered, unique%'
                            THEN 'Unique '
                        ELSE ''
                    END --CASE Expression
         , @FillFactor = ', FILLFACTOR = ' + NULLIF(convert(nvarchar(3),
                        (SELECT OrigFillFactor
                            FROM sysindexes
                            WHERE id = object_id(@TableName)
                                AND Name = @IndexName)), 0)
FROM #IndexInfo
WHERE IndexName = @IndexName

SELECT @ExecStr = 'CREATE ' + @Unique + 'CLUSTERED INDEX '
                        + QUOTENAME(@IndexName, ']') + ' ON '
                        + QUOTENAME(@TableName, ']') + '(' + @collist
                        + ') WITH DROP_EXISTING ' + ISNULL(@FillFactor, '')

-- Added Aug 25, 04: FIX FOR DESC Indexes.
SELECT @ExecStr = REPLACE(@ExecStr, '(-)', ' DESC')

-- For testing the String
-- SELECT @ExecStr

-- Create the Clustered Index
EXEC(@ExecStr)
go

CREATE PROCEDURE sp_RebuildIndexes
(
    @TableName      sysname     = NULL,
    @ScanDensity    tinyint     = 100
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
--
-- This procedure will get the Fragmentation information
-- for all tables and indexes within the database.
-- Programmatically it will then walk the list rebuilding all
-- indexes that have a scan density less than the value
-- passed in - by default any less than 100% contiguous.
--
-- Use this script as a starting point. Modify it for your
-- options, ideas, etc. - and then schedule it to run regularly.
--
-- NOTE - This gathers density information for all tables
-- and all indexes. This might be time consuming on large
-- databases.
--
-- DISCLAIMER - Execute at your own risk. TEST THIS FIRST.
SET NOCOUNT ON

IF @ScanDensity IS NULL
    SET @ScanDensity = 100

IF @ScanDensity NOT BETWEEN 1 AND 100
    BEGIN
        RAISERROR('Value supplied:%i is not valid. @ScanDensity is a percentage. Please supply a value for Scan Density between 1 and 100.', 16, 1, @ScanDensity)
        RETURN
    END
IF @TableName IS NOT NULL
    BEGIN
        IF OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
            BEGIN
                RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
                RETURN
            END
        ELSE
            BEGIN
                IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
                    BEGIN
                        RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
                        RETURN
                    END
            END
    END

-- Otherwise the Object Exists and it is a table so we'll continue from here.
-- First thing to do is create a temp location for the data returned from DBCC SHOWCONTIG

CREATE TABLE #ShowContigOutput
(
    ObjectName          sysname,
    ObjectId                int,
    IndexName               sysname,
    IndexId                 tinyint,
    [Level]                 tinyint,
    Pages                   int,
    [Rows]                  bigint,
    MinimumRecordSize       smallint,
    MaximumRecordSize   smallint,
    AverageRecordSize       smallint,
    ForwardedRecords        bigint,
    Extents                 int,
    ExtentSwitches          numeric(10,2),
    AverageFreeBytes        numeric(10,2),
    AveragePageDensity  numeric(10,2),
    ScanDensity         numeric(10,2),
    BestCount               int,
    ActualCount         int,
    LogicalFragmentation    numeric(10,2),
    ExtentFragmentation numeric(10,2)
)

IF @TableName IS NOT NULL -- then we only need the showcontig output for that table
    INSERT #ShowContigOutput
        EXEC('DBCC SHOWCONTIG (' + @TableName + ') WITH FAST, ALL_INDEXES, TABLERESULTS')
ELSE -- All Tables, All Indexes Will be processed.
    INSERT #ShowContigOutput
        EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS')
PRINT N' '

-- Quick test to see if everything is getting here correctly
-- SELECT * FROM #ShowContigOutput

-- Walk the showcontig output table skipping all replication tables as well as all tables necessary for
-- the UI. This is also where you can list large tables that you don't want to rebuild all at one time.
-- NOTE: If you take out a large table from rebuilding this script may have already checked density
-- meaning that the expense in terms of time may have been expensive.
-- Also, you should use a different procedure to rebuild a large table specifically.
-- Even when you pass in the tablename it will be avoided here if MANUALLY added to the
-- list by you.
-- Test, Test, Test!

DECLARE @ObjectName             sysname,
            @IndexName          sysname,
            @QObjectName            nvarchar(258),
            @QIndexName         nvarchar(258),
            @IndexID                tinyint,
            @ActualScanDensity  numeric(10,2),
            @InformationalOutput    nvarchar(4000),
            @StartTime              datetime,
            @EndTime                datetime

DECLARE TableIndexList CURSOR FAST_FORWARD FOR
    SELECT ObjectName, IndexName, IndexID, ScanDensity
    FROM #ShowContigOutput AS sc
        JOIN sysobjects AS so ON sc.ObjectID = so.id
    WHERE sc.ScanDensity < @ScanDensity
        AND (OBJECTPROPERTY(sc.ObjectID, 'IsUserTable') = 1
                OR OBJECTPROPERTY(sc.ObjectID, 'IsView') = 1)
        AND so.STATUS > 0
        AND sc.IndexID BETWEEN 1 AND 250
        AND sc.ObjectName NOT IN ('dtproperties')
            -- Here you can list large tables you do not WANT rebuilt.
    ORDER BY sc.ObjectName, sc.IndexID

OPEN TableIndexList

FETCH NEXT FROM TableIndexList
    INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity

WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        SELECT @QObjectName = QUOTENAME(@ObjectName, ']')
        SELECT @QIndexName = QUOTENAME(@IndexName, ']')
        SELECT @InformationalOutput = N'Processing Table: ' + RTRIM(UPPER(@QObjectName))
                                            + N' Rebuilding Index: ' + RTRIM(UPPER(@QIndexName))
        PRINT @InformationalOutput
        IF @IndexID = 1
        BEGIN
            SELECT @StartTime = getdate()
            EXEC sp_RebuildClusteredIndex @ObjectName, @IndexName
            SELECT @EndTime = getdate()
            SELECT @InformationalOutput = N'Total Time to process = ' + convert(nvarchar, datediff(ms, @StartTime, @EndTime)) + N' ms'
            PRINT @InformationalOutput
        END
        ELSE
        BEGIN
            SELECT @StartTime = getdate()
            EXEC('DBCC DBREINDEX(' + @QObjectName + ', ' + @QIndexName + ') WITH NO_INFOMSGS')
            SELECT @EndTime = getdate()
            SELECT @InformationalOutput = N'Total Time to process = ' + convert(nvarchar, datediff(ms, @StartTime, @EndTime)) + N' ms'
            PRINT @InformationalOutput
        END
        PRINT N' '
        FETCH NEXT FROM TableIndexList
            INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity
    END
END
PRINT N' '
SELECT @InformationalOutput = N'***** All Indexes have been rebuilt.  ***** '
PRINT @InformationalOutput
DEALLOCATE TableIndexList
go

Wait stats, the old way (not sure where this originated from):

use master
go

if ((object_id('sp_waitstats') is not null) and (objectproperty(object_id('sp_waitstats'), 'IsProcedure') = 1))
    drop proc [dbo].[sp_waitstats]
go

create proc [dbo].[sp_waitstats] @orderby nvarchar(10) = N'total'
as

set nocount on

if (lower(@orderby) not in ('total', 'resource', 'signal'))
begin
    raiserror('Error: incorrect @orderby value, use ''total'', ''resource'', ''signal''', 10, 1) with nowait
    return
end

declare @requests   bigint,
        @totalwait  numeric(20, 5),
        @totalres   numeric(20, 5),
        @totalsig   numeric(20, 5),
        @endtime    datetime,
        @begintime  datetime

create table [#waitstats]
(
    [wait type]         varchar(80) not null,
    [requests]          bigint not null,
    [wait time]         numeric(20, 5) not null,
    [signal wait time]  numeric(20, 5) not null,
    [now]               datetime not null default getdate()
)

insert into [#waitstats]([wait type], [requests], [wait time], [signal wait time])
    exec ('dbcc sqlperf(waitstats) with tableresults, no_infomsgs')

select  @begintime = min([now]),
        @endtime   = max([now])
from    [#waitstats]
where   [wait type] = 'Total'

--- subtract waitfor, sleep, and resource_queue from Total
select  @requests  = sum([requests]),
        @totalwait = sum([wait time]),
        @totalres  = sum(([wait time] - [signal wait time])),
        @totalsig  = sum([signal wait time])
from    [#waitstats]
where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '***total***')
and     [now] = @endtime

-- insert adjusted totals, rank by percentage descending
delete  [#waitstats]
where   [wait type] = '***total***'
and     [now] = @endtime

insert into #waitstats values('***total***', @requests, @totalwait, @totalsig, @endtime)

if (@orderby = N'total')
begin

    select  [requests],
            [wait type],
            [total wait time]       = [wait time],
            [resource wait time]    = [wait time] - [signal wait time],
            [signal wait time],
            [%total wait time]      = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
            [%resource wait time]   = case @totalres  when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
            [%signal wait time]     = case @totalsig  when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
            [avg total wait time]   = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
            [avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres  / [requests] as numeric(20, 5)) end,
            [avg signal wait time]  = case [requests] when 0 then 0 else cast(100 * @totalsig  / [requests] as numeric(20, 5)) end
    from    [#waitstats]
    where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
    and     [now] = @endtime
    order by [%total wait time] desc

end

if (@orderby = N'resource')
begin

    select  [requests],
            [wait type],
            [total wait time]       = [wait time],
            [resource wait time]    = [wait time] - [signal wait time],
            [signal wait time],
            [%total wait time]      = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
            [%resource wait time]   = case @totalres  when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
            [%signal wait time]     = case @totalsig  when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
            [avg total wait time]   = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
            [avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres  / [requests] as numeric(20, 5)) end,
            [avg signal wait time]  = case [requests] when 0 then 0 else cast(100 * @totalsig  / [requests] as numeric(20, 5)) end
    from    [#waitstats]
    where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
    and     [now] = @endtime
    order by [%resource wait time] desc

end

if (@orderby = N'signal')
begin

    select  [requests],
            [wait type],
            [total wait time]       = [wait time],
            [resource wait time]    = [wait time] - [signal wait time],
            [signal wait time],
            [%total wait time]      = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
            [%resource wait time]   = case @totalres  when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
            [%signal wait time]     = case @totalsig  when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
            [avg total wait time]   = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
            [avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres  / [requests] as numeric(20, 5)) end,
            [avg signal wait time]  = case [requests] when 0 then 0 else cast(100 * @totalsig  / [requests] as numeric(20, 5)) end
    from    [#waitstats]
    where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
    and     [now] = @endtime
    order by [%signal wait time] desc

end

drop table [#waitstats]
go

exec sp_waitstats
exec sp_waitstats @orderby = 'total'
exec sp_waitstats @orderby = 'resource'
exec sp_waitstats @orderby = 'signal'
go


About "having" in SQL I have the problem:

  Find all the customers that have an order that was handled by employee number 129 and count all the orders they made.

What I tried:

select c.id, c.fname, count(*)
from customer c inner join sales_order so
on c.id = so.cust_id
group by c.id, c.fname
having so.sales_rep = 129;

But it isn't correct and the answer is:

select c.id, c.fname, count(*)
from customer c inner join sales_order so
where c.id = so.cust_id and c.id in (
select s.cust_id
from sales_order s
where s.sales_rep = 129)
group by c.id, c.fname;

Isn't the keyword having in SQL like a condition that runs on each group? Then why won't all the groups that have so.sales_rep = 129 come up?

These are the outputs: https://pastebin.com/J9LD1L9L

To answer this, I did the following. I used PostgreSQL 9.6 on db-fiddle (a very useful site for putting up code to help ask good questions!). All other servers should act the same way (unless you're using MySQL without ONLY_FULL_GROUP_BY when you can get, ahem..., unpredictable results. You should never do this!). p.s. SQLite actually models itself on PostgreSQL, so any results from one are normally applicable to the other!

CREATEed my TABLEs:

CREATE TABLE customer
(
  id SERIAL,
  fname VARCHAR (20)
);

CREATE TABLE sales_order
(
  order_id SERIAL,
  cust_id INTEGER,  -- put in FK stuff yourself
  sales_rep INTEGER
);

Input data:

INSERT INTO customer (fname) VALUES ('cust_1'), ('cust_2'), ('cust_3');
INSERT INTO sales_order (cust_id, sales_rep)
VALUES
(1, 111), (1, 121), (1, 129), (2, 111), (2, 121), (3, 111), (3, 129), (3, 129);

Running your original query (see db-fiddle here), gives the error

Query Error: error: column "so.sales_rep" must appear in the GROUP BY clause or be used in an aggregate function.

If you modify your query slightly, as shown below (db-fiddle)

select c.id, c.fname, count(*)
from customer c inner join sales_order so
on c.id = so.cust_id
group by c.id, c.fname, so.sales_rep  -- Note addition of so.sales_rep!
having so.sales_rep = 129;

you get the correct answer

id  fname   count
3   cust_3  2
1   cust_1  1

I personally would find it more helpful to include the sales rep's name (or join it to a sales_rep table to include his/her name (db-fiddle).

The HAVING clause is looking for so.sales_rep in its processing, but can't find it in your original - adding it in the GROUP BY gives the HAVING the information it needs to only produce results for sales_rep no. 129!


How the sys user gets the sysdba privilege? I am using oracle 12c on Linux. Using the enterprise manager database express I am looking at the list of privileges and roles for the sys user but I haven’t found the sysdba privilege assigned there.

Is the sys user member of an oracle role or Linux group with sysdba privilege? What role or group?

From the 12c docs:

  The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user SYS without connecting as SYSDBA or SYSOPER.

And you cannot revoke it:

SQL> revoke sysdba from sys;
revoke sysdba from sys
*
ERROR at line 1:
ORA-01998: REVOKE failed: user SYS always has SYSOPER
and SYSDBA

More information from the docs:

  These administrative privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself. Methods for authenticating database administrators with these privileges include operating system (OS) authentication, password files, and strong authentication with a directory-based authentication service.

  These privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion.

and:

  When you grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to a user, that user's name and privilege information are added to the database password file. A user's name remains in the password file only as long as that user has at least one of these privileges. If you revoke all of these privileges, Oracle Database removes the user from the password file.


Surrogate keys as Foreign Keys I have a question about Surrogate keys, say i have two tables(in sql server):

Table A: Student_ID, Name, School, Section
Table B: Book_ID,Student_ID, IssueDate, BookName

If Student_ID is a clustered  surrogate key and Name is unclustered index. How would one manually enter data into Table B without knowing the Student_ID. I know i might have to do a lookup in table A to find the IDs but that would not be efficient. Is there any way to make this efficient? I am trying to think how surrogate keys essentially work in terms of efficiency as a foreign key when considering inserts.

You could just add the name of the student in Table B instead of Student_ID but that would not be normalized and you would be duplicating data, also if you don´t have constraints it may be possible for you to add students that are not registered in Table A. Name column for sure will use more bytes than Student_ID. Lookup will be efficient since you´re using Student_ID as clustered index in Table A. If you make queries filtering by that column your search would be fast, SQL Engine will use an index seek operation in the execution plan and even if the table is small SQL will put the entire table in cache (RAM) so it won´t have to go and read from disk. For a transactional app your design is fine, for a data-warehouse you may use a denormalized design where duplicating data is allowed.


Lost (and found?) on 4NF I'm so extremely lost on 4NF it's not even funny.  I've been reading on it for literally days and, except for about 15 minutes ago, I wasn't sure that I had even broken the surface.

I think I might have just had the light bulb moment, but given the difficulty I had on this, I'd appreciate some confirmation. These are my notes:

Am I even close on this?

In your notes you say:

  4NF describes multivalued dependencies, wherein a key value has multiple attributes dependent upon it, but those attribute have, at best, only a trivial relationship to each other.

This is not the definition of 4NF, since multivalued dependencies are not present when “a key value has multiple attributes dependent upon it”, but when those attributes have multiple values depending from some other attribute.

A typical example is when you have an employee which has the attribute child with multiple values and, in the same relation, an attribute skill which has multiple values, so that you have two  Multivalued Functional Dependency (MVD), Employee →→ Child, Employee →→ Skill. Instead, in your relation you have only one MVD, Title, Developer, Publisher, ReleaseDate, Price, ESRB →→ Platform, and for this reason the MVD is called a trivial MVD.

So, the redundancy in your example is not due to the fact that the relation is not in 4NF, rather that it is not in BCNF, neither in 3NF. Assuming that there is the functional dependency Title → Developer, Publisher, ReleaseDate, Price, ESRB, your relation in neither in BCNF, nor in 3NF. So, a decomposition of your schema in 3NF would be:

R1(Title, Developer, Publisher, ReleaseDate, Price, ESRB)
R2(Title, Platform)

where the first relation has key Title and the only Functional Dependency Title → Developer, Publisher, ReleaseDate, Price, ESRB, and the second one has key Title, Platform.