Why does Postgres ORDER BY seem to halfway ignore leading underscores? I have an animal table with a name varchar(255), and I've added rows with the following values:

Piranha
__Starts With 2
Rhino
Starts With 1
0_Zebra
_Starts With 1
Antelope
_Starts With 1

When I run this query:

zoology=# SELECT name FROM animal ORDER BY name;
      name
-----------------
0_Zebra
Antelope
Piranha
Rhino
_Starts With 1
_Starts With 1
Starts With 1
__Starts With 2
(8 rows)

Notice how the rows are sorted in an order that implies the leading _ is used to place the _Starts With 1 rows before the Starts row, but the __ in the __Starts With 2 seems to ignore this fact, as if the 2 at the end is more important than the first two characters.

Why is this?

If I sort with Python, the result is:

In  [2]: for animal in sorted(animals):
   ....:     print animal
   ....:
0_Zebra
Antelope
Piranha
Rhino
Starts With 1
_Starts With 1
_Starts With 1
__Starts With 2

Furthermore, Python ordering suggests that underscores come after letters, which indicates that the Postgres's sorting of the first two _Starts rows before the Starts row is incorrect.

Note: I'm using Postgres 9.1.15

Here are my attempts at finding the collation:

zoology=# select datname, datcollate from pg_database;
  datname  | datcollate
-----------+-------------
 template0 | en_US.UTF-8
 postgres  | en_US.UTF-8
 template1 | en_US.UTF-8
 zoology   | en_US.UTF-8
(4 rows)

And:

zoology=# select table_schema,
    table_name,
    column_name,
    collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
    table_name,
    ordinal_position;
 table_schema | table_name | column_name | collation_name
--------------+------------+-------------+----------------
(0 rows)

As you haven't defined a different collation for your column in question, it uses the database-wide one, which is en_US.UTF8 - just like on my test box.  I observe the exact same behaviour, take it as a consolation :)

What we see is apparently a case of the variable collation elements.  Depending on the character and the collation, a number of different behaviours is possible.  Here the underscore (and the hyphen and some others, too) are used only for breaking ties - 'a' and '_a' are equivalent in the first round, then the tie between them is resolved by taking the underscore into account.

If you want to sort with ignoring the underscores (and hyphens, question marks and exclamation marks in my example), you can define an ordering on an expression:

SELECT *
FROM (VALUES ('a'),
             ('b1'),
             ('_a'),
             ('-a'),
             ('?a'),
             ('!a1'),
             ('a2')
     ) t (val)
ORDER BY translate(val, '_-?!', '');

In my experiments adding a new value to the list often changes the order between otherwise equal items, showing they are treated really equal.


I couldn't change root user password on mysql with --skip-grant-tables I am very new at mysql and have struggled to change root user password in mysql.
(I have never set root password but it asks me root password.)

The below is what I have done.

One thing I suspect is that flush privileges results 0 rows affected.

Could you give me a suggestion?

$ sudo service mysql stop
$ sudo mysqld_safe --skip-grant-tables &
[1] 5395
sangmin@ubuntu:~$ 170201 00:32:55 mysqld_safe Logging to syslog.
170201 00:32:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

$ ps -e | grep mysql
  5396 pts/6    00:00:00 mysqld_safe
  5545 pts/6    00:00:00 mysqld

$ mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.29-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set password=PASSWORD("root") where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> quit
Bye

$ sudo service mysql restart
[1]+  Done                    sudo mysqld_safe --skip-grant-tables
$ mysql -uroot -proot
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

First of all, when you start your server with the --skip-grant-tables it's recommended that you use also --skip-networking, so no one can connect from the outside.

Then, once you enter to mysql you should first do:

mysql> FLUSH PRIVILEGES;

And to chance your password you should use the ALTER USER instruction:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YourPass');

After that, disconnect again and restart your server, that should do it.


Can I optimize this MERGE statement? I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows.

In my development environment, the query takes just under 8 minutes. But in the production environment, it should take significantly less (much more powerful machine). However, I anticipate the query taking at least 2 minutes to run in production. I know that this query was causing timeouts for other developers in the development environment, which means it could easily cause timeouts for customers. Is there a safer and/or faster way to perform this query?

declare @dt datetime = cast(dateadd(month, -6, getdate()) as date);

merge ShoppingCart as TargetTable  -- 07:55 to complete in Dev
using
(
  select * from -- 04:55 to run select, resulting in 12,727,927 rows in Dev
  (
    select
      visitorid  -- int, not null, foreign key
      ,useripaddress  -- varchar(55), null
      ,row_number() over
      (partition by visitorid order by createdate desc) as [row]
    from VisitorSession (nolock)
    where UserIPAddress is not null
    and CreateDate > @dt   -- createdate is a datetime, not null
  ) as subTbl
  where subTbl.[row] = 1
) as SourceTable
on (TargetTable.VisitorID = SourceTable.VisitorID)  -- visitorid is not a primary key
when matched
  then update set
  TargetTable.UserIpAddress = SourceTable.UserIpAddress;

Personally, I don't like MERGE because there are many unresolved bugs:

This blog post (scroll down to "Other MERGE issues")
This answer by AlexKuznetsov
These posts on Paul White's blog
I published a cautionary tip about MERGE here, including references to several bugs that haven't been addressed.
And this list may not even be exhaustive - given the number of bugs discovered by this small set of users, I have to wonder how many haven't been discovered yet?

MERGE also gives a false sense of security in terms of optimistic concurrency and race conditions. See Dan Guzman's blog post for more details.

I'm not trying to be a fear-mongerer here. But I also find the syntax unintuitive and daunting. So I would only use it in cases where it's actually needed and I can prove that I'm not affected by any of the above issues. I don't know what I would ever possibly gain from using it for an operation that could only ever end in an UPDATE anyway.

So here is how I would do it instead, using syntax that is much more familiar to me:

;WITH s AS
(
  SELECT VisitorID, UserIpAddress FROM
  (
    SELECT
      VisitorID,
      UserIpAddress,
      rn = ROW_NUMBER() OVER (PARTITION BY VisitorID ORDER BY CreateDate DESC)
    FROM dbo.VisitorSession
    WHERE UserIpAddress IS NOT NULL
    AND CreateDate > @dt
  ) AS x
  WHERE rn = 1
)
UPDATE c
  SET c.UserIpAddress = s.UserIpAddress
  FROM dbo.ShoppingCart AS c
  INNER JOIN s
  ON c.VisitorID = s.VisitorID;

You can also break this operation into chunks to reduce the impact on the transaction log which may, in turn, reduce the overall duration. I blogged about this here.

Here is how I would handle that approach:

DECLARE
  @dt DATE = DATEADD(MONTH, -6, SYSDATETIME()),
  @rc INT = 1;

WHILE @rc > 0
BEGIN

  BEGIN TRANSACTION;

  ;WITH s AS
  (
    SELECT TOP (100000) VisitorID, UserIpAddress FROM
    (
      SELECT
        VisitorID,
        UserIpAddress,
        rn = ROW_NUMBER() OVER (PARTITION BY VisitorID ORDER BY CreateDate DESC)
      FROM dbo.VisitorSession AS s
      WHERE UserIpAddress IS NOT NULL
      AND CreateDate > @dt
      AND EXISTS
      (
        SELECT 1 FROM dbo.ShoppingCart AS c
          WHERE c.VisitorID = s.VisitorID
          AND (c.UserIpAddress <> s.UserIpAddress
          OR c.UserIpAddress IS NULL)
      )
    ) AS x
    WHERE rn = 1
  )
  UPDATE c
    SET c.UserIpAddress = s.UserIpAddress
    FROM dbo.ShoppingCart AS c
    INNER JOIN s
    ON c.VisitorID = s.VisitorID;

  SET @rc = @@ROWCOUNT;

  COMMIT TRANSACTION;
END

Of course, as the blog post illustrates, you can gain just about as much time by making sure your log is big enough to handle the entire transaction without having to grow - most of the delay is likely coming from many, many autogrow operations accommodating your large transaction. Sadly until you've done the operation once it can be very difficult to try and guess how much transaction log you'll need...


How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters? I am using MySQL version 5.1.49 and I have now enabled UTF8 character encoding. The default character-set for MySQL is latin1. How can I change it show UTF8 characters?

Even when I query a table using Workbench, I get 'NULL' in the name section which I want, should display multibyte characters.


SQL Server: Explaining to C-Level Executives the impact of using Primary Key of VARCHAR(50) in many tables throughout a database? Suppose a global, mid-sized company is considering a document control management solution that is backed by a SQL Server database and allows developers to create document form types using a form creation tool.  Each form type created using the tool is represented in the database by a table with a primary key of datatype VARCHAR(50).

What impact would the requirement to use VARCHAR(50) primary keys have on system performance vs. a system using a more conventional design approach, e.g., this?  Assume the core audience for the answer is mostly non-technical, i.e., C-Level Executives making a go or no-go decision on the solution.

Notes:

The document control management system needs to perform typical document control management system tasks for a global mid-sized company for many years (at least two decades).
This is a canned solution and the primary key data type cannot be altered.
The VARCHAR(50) data type maps to the company's serial number for products.  The serial number format is alphanumeric and can contain dashes.
The system is web based and uses SQL Server 2014 and the .NET Framework (C#) technology stack.
Questions researched before creating this question, all of which don't align with the technology stack and/or have answers too in the weeds for a non-technical audience:

VARCHAR primary key - MySQL
Should I replace my varchar primary key with an integer primary key?
Can I use VARCHAR as the PRIMARY KEY?
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

The document control management system needs to perform typical document control management system tasks...for many years (at least two decades).

The end of extended support for SQL Server 2014 Service Pack 3 is 7/9/2024.  That's 5 years from now.  So I think your first step should be to see if / when the vendor intends to get certified on a more modern version of SQL Server.  This is more important if you are subject to compliance rules that require software to recieve vendor support and security updates.

As far as other things to share with executive stakeholders, it's hard to make general statements, because you certainly won't be comparing systems that have the exact same schema with the exception of the primary key data type.

If you are comparing this product to a similar one that uses surrogate (integer) keys, assuming the varchar(50) primary keys are also clustered indexes (the default for SQL Server), issues you might raise would be:

You will likely need to pay for increased disk space for the database

the varchar(50) key will take up more space on disk than the integer key
this space increase is multiplied by any nonlclustered indexes on these tables, since the clustering key is included in them automatically
it's common to say "disk is cheap," but some very smart folks have disagreed with that assessment: Disk Is Cheap! ORLY?

Because of the above, you will also spend more on disk space for backups
You will likely need to pay for more RAM

since these tables and indexes are bigger on disk, they will be bigger in RAM too

Some reports and screens might be slower to load

index scans and seeks will have to consume more pages, and would take longer than they equivalent scan of an integer-keyed-table

Some reports and screens might be faster to load

since the text of this serial number would be stored in any table that references it as a foreign key, you might be able to avoid joins sometimes

Notice all of the hedge words above like "might" and "likely" - like I said, I don't think you can make a lot of useful general statements about this situation.


The same set of columns for two tables I have the following db schemas:

Comments table

and Posts table ( has FK to Comments table)  :

As you can see, there is a set of the same columns for both tables.
Is it bad design? If yes, how can I avoid it? Also I want to use Entity Framework to map this entities.
I tried to use inheritance and table-per-hierarchy approach , but unsucessfully (difficult to understand how to map self-referencing table)

Regards

Having identical column names across tables is a good thing and a common design practice. It makes writing queries easier because the naming standards are more consistent. When joining them in a query, you'll just have to qualify each identical column name with the table name or alias.
If you wanted to get all the post titles and any comments along with the date the comment was made, it would look like this:
SELECT p.Id, p.Title, c.CreatedOn, c.Content
FROM Posts AS p
LEFT JOIN Comments AS c ON c.PostId = p.Id

If you leave off the table name or alias from the CreatedOn column (the "c.", in this case), SQL Server can't tell which CreatedOn column you're asking it for, and you'll get an error like this:

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'CreatedOn'.

Technically, you can get away with not aliasing the unambiguous columns (like Title, in this example, because it doesn't exist in the Comments table), but it's better to alias all or none of the columns for readability's sake.
Is what you're doing with two tables a bad design? If my assumption is correct that there can be multiple comments per post, you're right to split them into separate tables. It's a true one-to-many relationship and those belong in separate tables. Entity Framework shouldn't have any trouble with this design.


Case-sensitive query - SQL Server 2008 How can I rewrite as Latest Developer a record which was created as LATEST DEVELOPER via SQL query?

UPDATE YourTable
SET YourColumn = 'Latest Developer'
WHERE YourColumn COLLATE LATIN1_GENERAL_CS_AS = 'LATEST DEVELOPER'


MySQL table replication on the same server I have one the same server (Linux) two databases: db1 and db2 and I want to replicate db1.table1 to db2.table1

I added the following lines to my.cnf and restarted mysql service

replicate-do-table = db2.table
replicate-rewrite-db  = database1->database2

but it's not working. Are those commands correct ? Do I need to set permissions on both databases ?

mysql version: 5.5.25a

Thanks in advance

MySQL Replication was designed to replicate data between two MySQL Instances, not two MySQL Databases within the same instance.

What you need to do is write triggers in db1.table1 to write the same changes to db2.table1.

See my old post How to set up triggers in MySQL for copying a field for some ideas.


Connectivity to SQL Server on Azure Error: 121 - The semaphore timeout period has expired I having trouble connecting to a SQL Server database on Azure. We've already added the IP address to the firewall exclusion list and the username/password work when connecting from other machines. We tried with both username/password and MFA.
What else could be causing the problem?

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error: 121)

try to connect choosing the proper user database here:

Maybe the user you are using is not able to connect to the master database.
Here is how database firewall rules work:
https://docs.microsoft.com/it-it/azure/azure-sql/database/firewall-configure


Microsoft SQL Server 2014 Nested From Query in Cross-Apply When selecting from a nested query in an OUTER APPLY statement the nested query seems to be evaluated only once in certain circumstances.

Bug reported to Azure Feedback Forum: https://feedback.azure.com/forums/908035-sql-server/suggestions/39428632-microsoft-sql-server-2014-incorrect-result-when-s

Is this the expected behavior or am I missing something in the documentation or is this a bug in SQL Server?

Also, is there any possibility to force evaluation of the nested query for every row?

Test Case 1

  Evaluates nested FROM query for every row in VALUES (imho expected
  behaviour)

SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
        FROM (
            SELECT
                15 AS id,
                v AS inner_v2
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |

Test Case 2

  It also evaluates nested FROM query for every row in VALUES (imho
  expected behaviour)

SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
        FROM (
            SELECT
                15 AS id,
                v AS inner_v2
            UNION ALL
            SELECT
                id AS id,
                TestCaseTemp2.v AS inner_v2
            FROM
                (VALUES (1337, 0)) AS TestCaseTemp2(id, v)
            WHERE TestCaseTemp2.v != 0
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply;

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |

Testcase 3

  Evaluates nested FROM query only once

CREATE TABLE TestCaseTemp
(
    id int,
    v int
);
INSERT INTO TestCaseTemp VALUES (1337, 0);

SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
        FROM (
            SELECT
                15 AS id,
                v AS inner_v2
            UNION ALL
            SELECT
                id AS id,
                TestCaseTemp.v AS inner_v2
            FROM
                TestCaseTemp
            WHERE TestCaseTemp.v != 0
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply;

DROP TABLE TestCaseTemp;

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |

is this a bug in SQL Server?

Yes, certainly, the 1 that is returned in all rows in your final result only exists in the first row of the outer input so shouldn't even be in scope for the subsequent rows. It looks like the same basic issue as looked at in detail by Paul White here.

I executed your final query in dbfiddle (SQL Server 2019) and pasted the plan here https://www.brentozar.com/pastetheplan/?id=Sy4sBB5lI  It looks like the sort executes 4 times (once for each outer row) but for some reason it rewinds rather than rebinds so doesn't call the child operators of the sort more than once. This is a bug as the reference to the correlated parameter of the outer join (Union1004) should cause a rebind when this has changed value. As a result the reference to Union1004 in Node 5 of the plan is never re-evaluated.

I see you have now reported it here https://feedback.azure.com/forums/908035-sql-server/suggestions/39428632-microsoft-sql-server-2014-incorrect-result-when-s

  Is there any possibility to force evaluation of the nested query for every row?

Adding the query hint OPTION (MERGE UNION) works for your example, I don't know if this will necessarily be sufficient to avoid the bug in all cases but from the linked Paul White answer it appears it should work. In the case of your example it works as the sort is pushed down lower in the plan so it only rewinds the TestCaseTemp rows, not the entire unioned result. You could also add appropriate indexing to remove the sort entirely.


Replicated timestamp column in different timezone I have the master running Mysql 5.5.38 on Ubuntu 14.04 LTS. The timezone in the master is UTC+5:30. The slave is in AWS RDS which runs in UTC. The slave is primarily used for running for SQL queries by staff.

To maintain their sanity, I setup the timezone hack. This also applies to the replication user.

One of our innodb tables records scheduled appointments. The relevant parts of the table are below:

`startDateTime` timestamp NOT NULL DEFAULT '2000-01-01 12:00:00',
`endDateTime` datetime DEFAULT NULL,
`createdOn` timestamp NULL DEFAULT NULL,

I am seeing the following time on the master when querying the table:

startDateTime - IST
endDateTime - IST
createdOn - IST

However on the slave:

startDateTime - UTC
endDateTime - IST
createdOn - IST

Running SELECT NOW(); on either servers shows current time in UTC+5:30. The bin_log format on both master and slave is STATEMENT

How do I ensure that the replicated column startDateTime is also in IST?

Update:

RDS is a hosted mysql with no access to my.cnf. Futher time_zone variable is restricted and hard coded to UTC

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values.

http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

Change your binlog format to ROW to eliminate the vagaries of statement-based replication and then change your DATETIME columns to TIMESTAMP (on the master, let the change replicate).

This solves the issues you're experiencing.

Although... it solves entirely the wrong problem, which is that it's typically better to store times properly in UTC and localize them as needed.  Of course, that's what TIMESTAMP columns essentially do -- they store datetimes as UTC after converting then from the current session time zone, and then they convert back when you read them.

  If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.


Different lock types on source table when I change method of inserting data? Why do I encounter IS(intent shared) lock on source table when I insert into target table using SSIS data flow task and S(shared) lock for same task when using T-SQL statement?

I am trying to insert around 10M rows. Concurrent inserts on the source happens all the time. So, according to lock compatibility matrix, SSIS data flow task does not block parallel inserts, but T-SQL insert does.

Why is that?

When you use SSIS, the target and source are separate operations. As far as SQL Server is concerned, it sees one connection performing a simple SELECT and another connection performing a bulk load or regular INSERT.

The simple SELECT can benefit from a locking optimization where row-level shared locks are skipped when safe to do so. This leaves only the intent-shared locks at the page level.

When the insert and select are combined in the same T-SQL statement, a different execution plan is produced, and the specific locking optimization is not applied.

If you wish to produce the same non-blocking behaviour on the source table when using INSERT...SELECT, you would need to use a row-versioning isolation level.

Read uncommitted isolation would also not take shared locks, but offers very few consistency guarantees, and might even throw an error when the data structures underneath the scan are modified (error 601).


Conditional Join - Specific Column Select I have tables like these:

Table Employee ( Emp_Id, Full_Name, ...)
Table Order ( Submitter_Id, Recipient_Id, ...)

Then say that I have conditional join like this:

select isnull(Emp.Full_Name, Emp2.Full_name) as Recipient  -- , other fields
from Order
    left outer join Employee Emp on Emp.Emp_Id = Order.Recipient_Id
    left outer join Employee Emp2 on Emp2.Emp_Id = Order.Submitter_Id
-- where clauses

Brief explaination: The query will return the name of recipient from specific order. An order may has recipient id being set, or using submitter id if the recipient is not set.

The condition: Both table Order and Employee has big amount of records inside, so joining them both is a costly operation. 80% - 90% of records in Order has recipient_id set, so joining to submitter_id can be useless operation. Using isnull as join condition can resulting in index scan (in my experience). Using subquery for column maybe help, but the cost can be high because the operation can be row-by-row.

Is there any conditional join for case like this?

Regarding your comment about the scan, that happens because using ISNULL() in the join manipulates the field, so it's no longer SARGable. It may be faster to split this up, rather than try to do all the work in a single query. For example:

DECLARE @Recipient_ID INT, @Submitter_ID INT

SELECT
@Recipient_ID = Order.Recipient_Id,
@Submitter_ID = Order.Submitter_Id
FROM Order
-- where clauses

IF @Recipient_ID IS NULL

SELECT Emp.Full_Name
FROM Employee Emp
WHERE Emp.Emp_Id = @Submitter_ID

ELSE

SELECT Emp.Full_Name
FROM Employee Emp
WHERE Emp.Emp_Id = @Recipient_ID

EDIT based on comment:
Since there can be many orders, what about this? It is SARGable so should index well, and you're not querying the table multiple times.

with cte as (
SELECT
COALESCE(Order.Recipient_Id, Order.Submitter_Id) AS EmployeeID
FROM Order
-- where clauses
)

SELECT Emp.Full_Name
    FROM Employee Emp
INNER JOIN cte on cte.EmployeID = Emp.Emp_Id


Database Properties Error - sys.spt_values missing but dbo.spt_values works? We have SQL2012 SP1 installed on several servers and have run into an issue on one instance trying to view the database properties in SSMS. (This happens with both SSMS 11.0.5058.0 and 11.0.3128.0.) I have crawled all over Google results looking for something that makes sense, but I'm baffled at the moment.

The issue is rooted with a view in the master database called dbo.spt_values.  This table is referenced in the error thrown by SSMS when trying to view the properties.

The recommended solution here is to run the u_tables.sql script with will recreate the necessary objects in the master database.  Opening and running this script produces an error stating:

Msg 208, Level 16, State 1, Procedure spt_values, Line 7
Invalid object name 'sys.spt_values'.

Most of the posts I have seen related to this error recommend completely rebuilding the master database at this point, then reattaching or restoring the user databases.  Before I went down this road, I wanted to see what the servers not experiencing this problem look like...  To further my befuddlement, I am not able to see sys.spt_values ON ANY INSTANCE OR ANY DATABASE.  However, the master.dbo.spt_values view does return rows when queried.  When I try to take the select statement out of the view and run it, I get the invalid object message again.

I have connected as my windowd users, which is sysadmin, as well as the SA login.  I have done this on each of the servers as well as a local instance of SQL 2012 SP2.  I have used SSMS installed with the original SP1 installation as well as an instance that had been upgraded to SP2.

I have also looked at synonyms, other views, functions, stored procedures, or any other objects I could parse or query and have no idea where I can find the sys.spt_values table.  Any help to get the properties box working as well as improve my understanding of the purpose of this table and where it lives would be monumentally helpful!

Resources I've already looked at:

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_21958187.html
http://answers.flyppdevportal.com/categories/azure/sqlazure.aspx?ID=92d22012-b829-4823-ab3a-7195fcf2dcb4
http://social.msdn.microsoft.com/Forums/en-US/3343a3a6-bd4a-48d6-b87f-2480ef8b597c/master-db?forum=sqldatabaseengine
http://web.archive.org/web/20150411043738/http://sqlserver2000.databases.aspfaq.com/why-do-i-get-errors-about-master-spt-values.html
http://blogs.msdn.com/b/suhde/archive/2009/04/05/invalid-object-name-master-dbo-spt-values-microsoft-sql-server-error-208.aspx

(I also followed links within the links...  Good thing I'm bald already)

Answer compiled from comments by the question author

Following a suggestion by Ben Thul to try connecting using the Dedicated Administrator Connection (DAC), that was it: I was able to connect over the DAC wtih SQLCMD and run

select * from sys.spt_values

From there I ran the u_Tables.sql script and it looks like we're good to go.

I'm not familiar everything that is different when using the DAC, but that connection allowed me to see the sys.spt_values object.  I had not been otherwise able to query against it.  Since the object was now visible over the DAC, I could execute the u_tables.sql rebuild script successfully.

It is still unclear what caused the original issue. I was contacted by a client regarding the error in our Test environment. The issue did not impact any other functionality besides the properties dialog box in SSMS.

Several sources said to run the u_tables.sql script to fix the issues. When I inspected the script, I tried to run just the sql within the view and kept getting the error about an invalid object.

This was true regardless of how I connected through SSMS. It also meant the u_tables.sql would not execute successfully.  The DAC connection allowed me to see the necessary objects.


DBeaver: connecting to a PostgreSQL database server through SSH: can't initialize tunnel, auth fail I am trying to use DBeaver to connect to a PostgreSQL database server through SSH:

can't initialize tunnel
cannot establish tunnel
com.jcraft.jsch.JSchException: auth fail

I am sure the SSH server is working fine. I manage  to connect to the same PostgreSQL database server through the same SSH server with pgAdmin 3 and RazorSQL. I have checked the IP, username and password, they are correct. I disabled all firewalls. What could be the issue?

My settings:

I use DBeaver 3.7.3 x64 portable version with Windows 7 SP1 x64 Ultimate.

The error log window does not log the error, so I don't have any more information there.

I tried DBweaver on Kubuntu and had the same issue. It looks like the issue is around the fact that the authenticity of the SSH server hadn't been verified beforehand, and DBweaver and unable to cascade the correct error message (e.g., Host key verification failed.) and instead misleadingly blames some authentication error.

I fixed the issue on Kubuntu as follows:

Disable the SSH Tunnel:

Run in the shell sudo apt-get install -y autossh
Add the shell command in your connection in DBeaver:

autossh -f -N -L 5432:localhost:5432 ssh_server_username@ssh_server_ip

At that point if you try to connect you'll have the same com.jcraft.jsch.JSchException: auth fail error message. Go to the shell and run the command:

ssh -f -N -L 5432:localhost:5432 ssh_server_username@ssh_server_ip

it will output:

The authenticity of host 'ssh_server_hostname (ssh_server_ip)' can't be established.
ECDSA key fingerprint is 29:13:b5:cb:99:21:b2:18:f1:11:aa:12:54:c2:88:9a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ssh_server_hostname,ssh_server_ip' (ECDSA) to the list of known hosts.
Password:

Go back to DBeaver, try to connect, that should work. I still don't know why I should use autossh instead of the SSH tunnel tab in DBeaver though.


Modelling a second level table relationship I have three tables in my Database:

Customers
----------
Id
Name

Addresses
----------
Id
Description
CustomerId

Orders
----------
Id
Description
CustomerId
DeliveryAddressId

I'd like to make sure that every Address referenced by DeliveryAddressId in the Orders table belongs to the Customer referenced by CustomerId.
How can I model this?

I recently answered a question just like this one here:

How to structure Tables with changing status without duplicating data

You have two options, create the foreign key from Orders using both the AddressId and CustomerId or remove the CustomerId entirely from the Orders table as you can obtain it through the relation anyway.


Is it ok to have huge page life expectancy? Recently I have buffer issue for my SSIS package.
When i started to monitor metrics in perfmon i noticed the PLE is nearly 500000.
Is it ok to have this huge PLE.

Page Life Expectancy is simply how long (in seconds) a page will remain idle in the buffer before being removed to make room for another page.

There's no active harm in a very large PLE. It's possible that you've got more memory going to SQL Server than is absolutely required, but that's still not necessarily a bad thing.


Best way to export excel table into MySQL db I'm trying to export an Excel table which contains about 400 records of HR stuff (e.g. name, surname, age, job, ecc.) into a MySQL db.
I'm actually using mysql for excel and started reading a db book, but I noticed many format issues such datetimes from trying to convert from %d/%m/%Y to %d-%m-%Y.

Best practice hints for db administration are well accepted!

For 1-time:

Save as text (csv) file.

CREATE TABLE ... with the columns approximating the Excel columns.

LOAD DATA LOCAL FILE ... into that table in MySQL.

(If you need 'automation', there may be better ways.)


MySQL - wait_timeout variable I've been trying to resolve an issue which I think may be helped by changing the wait_timeout variable in MySQL (5.1.67) on my CentOS machine.

So, I updated /etc/my.cnf with the value I want (180), and restarted MySQL, but now I'm surprised to find that I'm getting different values for wait_timeout depending on how I display it (see below):

mysql> show global variables like "%wait%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 180   |
+--------------------------+-------+
3 rows in set (0.00 sec)

mysql> show variables like "%wait%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800 |
+--------------------------+-------+
3 rows in set (0.00 sec)

I'm guessing that the second query is showing session variables, as opposed to global variables, but I don't understand why they would be different, after having exited my MySQL session, restarted MySQL, and then logged back in again. How come they're not the same? And, I guess, how to I ensure that the same value (180) is consistent across everything?

Any suggestions/help appreciated. Thanks.

The documentation states

  On thread startup, the session wait_timeout value is initialized from
  the global wait_timeout value or from the global interactive_timeout
  value, depending on the type of client (as defined by the
  CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also
  interactive_timeout.

Since you are connecting via the command line, rather than a script, it makes sense that the CLIENT_INTERACTIVE is being used as a connection option automatically. As such, the interactive_timeout value is being used, which defaults to 28800.

To make the session wait_timeout value be 180, update the interactive_timeout value to be 180.

As for why that is so, I can only speculate. However, as a programmer, I know it is more useful to only have to look up one value whenever checking to perform a certain action, rather than having to check multiple values, and make sure I'm always check all those values each time I want to check something.

More explicitly, in this case, every time the wait_timeout value is needed, it is best to just have to check that, rather than both wait_timeout and interactive_timeout. It helps keep code maintainable by having the session wait_timeout automatically be initialized with the appropriate value, and then have to only check that one value. To do otherwise invites mistakes, by potentially failing to check both values every time something needs to be done that involves this timeout.


Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4 I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4.  Is there a way to generate ALTER statements for every table and column that needs to change?

I'm upgrading MySQL hosted on Amazon RDS from MySQL 5.7 to 8.0.  The Pre-patch compatibility tool tells me:

  The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.

  More Information:
  https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

It then lists about 6,000 databases and columns that need to be updated:

mydb - schema's default character set: utf8
mydb.mytable.mycolumn - column's default character set: utf8

I'd like to be able to generate all the ALTER statements I'll need to run similar to how MySQL command querying all MyISAM database helped my convert all my tables from MyISAM to Innodb.

Here is a query what will generate all the necessary update statements.

It temporarily disables foreign key checks so that the update statements succeed.
It updates the default character set for each database that needs it.
It updates the default character set of each table:

If it doesn't have the right default character set.
If it contains text columns that are not the right character set.
The same ALTER TABLE ... CONVERT statement both updates the table default and all the columns within the table.

SELECT
    /* Disable foreign key checks temporily to be able to make these changes */
    'SET FOREIGN_KEY_CHECKS = 0;' AS alter_statement
UNION SELECT
    /* Alter the default character set of each database */
    CONCAT('ALTER DATABASE `', SCHEMA_NAME,'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement
FROM
    information_schema.SCHEMATA
WHERE
    DEFAULT_CHARACTER_SET_NAME!='utf8mb4' AND
    SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')
UNION SELECT
    /* Alter the default character set of each table .
      This also converts all text columns in the table,
      So there is no need to have a statement to alter each
      column individually */
    DISTINCT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement
FROM
(
    SELECT
        /* Find all tables with a text column that isn't utf8mb4 */
        TABLE_SCHEMA, TABLE_NAME
    FROM
        information_schema.COLUMNS
    WHERE
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys') AND
        CHARACTER_SET_NAME IS NOT NULL AND
        CHARACTER_SET_NAME!='utf8mb4'
    UNION SELECT
        /* Also find all tables that don't have the correct default character set */
        TABLE_SCHEMA, TABLE_NAME
    FROM
        information_schema.TABLES AS T
    JOIN
        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C ON C.collation_name = T.table_collation
    WHERE
        CHARACTER_SET_NAME!='utf8mb4' AND
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys')
) AS TABLE_UPDATES
UNION SELECT
    /* Re-enable forign key checks */
    'SET FOREIGN_KEY_CHECKS = 1;' AS alter_statement
;

When run, it should generate output like:

SET FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE `mydb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `mydb`.`mytable` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;

Sources:

MySQL command querying all MyISAM database -- For how to query the information_schema to create UPDATE statements.
How to change collation of database, table, column? - Stack Overflow -- For the update statements.
How to convert an entire MySQL database characterset and collation to UTF-8? - Stack Overflow -- For the comment about disabling foreign key checks to be able to make these changes.
How do I see what character set a MySQL database / table / column is? - Stack Overflow -- For how to join TABLES and COLLATION_CHARACTER_SET_APPLICABILITY


Adding new data source connection only contains sql server in dropdown in SSRS 2016 I just installed SQL Server 2016 Express and was going to add an ODBC connection to my SSRS instance. When I went to the dropdown to change the connection type from SQL Server to DB2, the only connection type available in the dropdown is SQL Server.

Anyone know how to fix this? Is this a limitation in the Express version?

It is a limitation that you are hitting for Reporting Services in SQL Server Express

  Report data sources must be SQL Server relational databases that run locally in SQL Server Express.


Can somebody explain BEGIN; and COMMIT; for me once and for all? Let's say I have this PHP code:

dbcall('BEGIN');
dbcall('CREATE SCHEMA "cool schema"');
dbcall('CREATE TABLE "cool schema"."cool table"');
dbcall('COMMIT');

Am I correct in thinking that this will first create a schema, and if the table cannot be created (for any reason, such as a temporary glitch or whatever), the schema that was just created is deleted again, so that the entire "state" of the database is reset to exactly how it was hen the BEGIN query was executed?

I've read the manual on transactions numerous times over the years, and asked people questions like this many times, but I've never got a clear, unambiguous answer, so I've never dared to actually use BEGIN and COMMIT queries anywhere in my application code.

This makes me feel as if my software isn't as "solid" as it could be, and I'm worried that temporary glitches could cause all kinds of issues because I oftentimes execute two queries which are very related, and where one doesn't make sense without the other, and those seem like perfect candidates for the BEGIN/COMMIT syntax.

And what if there is never a COMMIT query being made? Will it just continue piling on query after query and never "commit" them? And then throw them away when the script/session finishes? Will it COMMIT automatically if you make another BEGIN before a COMMIT?

If it has to "roll back" created records and other changes, what happens if other scripts have at the same time used the data in the database while it was working, which now no longer exists?

This truly boggles my poor mind. I cannot comprehend how this can be possible at all, from a logical perspective.

You are already using transactions, because every statement in PostgreSQL runs in a transaction. So no need to be afraid of transactions.

By default, PostgreSQL is running in autocommit mode, so every statement has its own transaction. To get a transaction that spans more than one statement, you explicitly start it with START TRANSACTION (or BEGIN).

Until a transaction is committed, its effects won't be visible outside the transaction itself. If you terminate the transaction with ROLLBACK, all its effects remain invisible. If you terminate the transaction with COMMIT, its effects suddenly become visible.

This works for normal data modifying transactions as well as for DDL statements. After all, DDL statements are mostly data modifications of the metadata tables (of course, there is more to it, like files being created or removed, which is deferred until the transaction commits).

If that seems too magical to you, dive into the depths of how it works under the hood. The documentation has little about that, but you can read the source, perhaps starting with src/backend/access/transam/README and src/include/access/htup_details.h. Blogs may be helpful too.

Start two sessions and play with it. Soon you will get the hang of this wonderful feature that makes a coder's life so much easier when applied sensibly.


Are multiple dbf files better than one? Is it better to distribute a large database over several dbf files than over a single one?

The number of files is irrelevant from a performance perspective.  The number of spindles those files are distributed over is, on the other hand, critically important to performance.  If you are using a reasonably modern SAN and the additional files would be created on the same mountpoint, there will be no meaningful performance difference since the data is being spread over the same number of physical disks.  If additional data files would be placed on different physical disks, there would likely be performance benefits to that.

There may be management benefits to having multiple smaller, consistently sized data files if that makes it easier to move files from one physical location to another.  If you have a bunch of 5 GB data files, for example, and you generally allocate 30 GB mountpoints on the SAN, it may be easier to have up to 6 data files per mountpoint so that if you need to move data files from one mountpoint to another, you can do so relatively easily.  If you've got 4 mountpoints, each with 4 data files, you can move all the data files off of one mountpoint and distribute them over the other 3 mountpoints if that ever became necessary (for example, if you started having performance problems on one mountpoint).  If you had one data file per mountpoint each of which was 20 GB, you couldn't easily move those data files around.  Additionally, if a single data file became corrupt, it would generally be quicker to restore a 5 GB file from backup rather than a 20 GB file.

Of course, the number of data files needs to be reasonable.  If you've got a 20 TB database, 5 GB data files would mean that you needed to manage 4000 separate files which would probably be excessive and would probably cause checkpoints to start to take longer than you'd like.


Heap size too large for data present A SQL Server 2008 R2 Standard Edition database has a staging table with no indexes, eight varchar(50) and one varchar(255) columns, and 162,676 rows. I'd expect that this table shouldn't be much over 100 MB in size, however it actually exceeds 7.5 GB.

What can cause this? I suspect that I can make the problem go away by adding a clustered index or recreating the table completely, but I want to understand what's going on that makes one staging table inflate to such ridiculous proportions. Here's what I've gathered as pertinent data so far:

sys.dm_db_index_physical_stats (DETAILED)
    fragment_count = 1056
    page_count = 964392
    forwarded_record_count = 0

sp_spaceused @objname = 'db.schema.table',@updateusage = 'TRUE'
    reserved = 7716888 KB
    data = 7715136 KB
    index_size = 24 KB
    unused = 1728 KB

sys.allocation_units
    total_pages = 964611
    used_pages = 964395
    data_pages = 964392

Can anyone tell me why this table is occupying so much space?

Chances are that a lot of data has been updated or deleted, or you have adjusted the size of some of your columns, or you have truncated / deleted the table and re-loaded many times. Some of these operations can lead to un-reclaimed space in some scenarios. I would try this first:

ALTER TABLE schema.table REBUILD;

Next I would consider adding a clustered index.


How can I run SQL Server Vulnerability Assessment from a SQL Job? I want to run SQL Server Vulnerability Assessment from a SQL Server Agent Job. Currently, I am attempting a job with a PowerShell script and am running a command like the one below.

Invoke-SqlVulnerabilityAssessmentScan -ServerInstance $(ESCAPE_DQUOTE(SRVR)) -Database AdventureWorks

I have confirmed that Invoke-SqlVulnerabilityAssessmentScan is available on the SQL Server (I can run it from the PowerShell command prompt there), but when I run my job, I receive an error stating that

  The term 'Invoke-SqlVulnerabilityAssessmentScan' is not recognized as the name of a cmdlet

After looking at this Microsoft article, I am wondering if SQL Agent only has a subset of PowerShell cmdlets that it can access.

How can I run the vulnerability assessment scan from a SQL Job?

You are getting that error because the module is not imported.

You have to use

import-module -name sqlserver  # or path to the module

see : Running PowerShell in a SQL Agent Job


Mixed mode auditing - 12c Oracle 12c database comes with default Unified auditing enabled even if we set it to FALSE which has ORA_SECURECONFIG policy enabled by default.

Apparently, the audit options for that particular policy captures many default options such as Alter and create statements.

My question is, by default for any new installation, mixed mode is enabled which means that the traditional auditing is enabled and also Unified auditing is enabled as well both capturing the same audit_options.

Why is it that way? What is the point of Mixed mode? When by default tradionally Oracle audits certain user actions, why should ORA_SECURECONFIG should also audit the same default user actions and write to AUDSYS schema?

And if I need to keep only Unified auditing, then how to disable traditional auditing? Should I change audit_trail from DB to NONE?

In Oracle 12c Mixed Mode is enabled by default which makes the use familiar with Unified Auditing prior to switching to full Unified Auditing. Mixed Mode allows to pre-12c audit functionality to co-exist with new Unified Auditing functionality.

Using Mixed Mode audit data can be found both in the traditional locations as well as in SYS.UNIFIED_AUDIT_TRAIL.  This is because the Unified Auditing Policy ORA_SECURECONFIG is enabled by default.

You can use the following commands to disable Unified Audit.

NOAUDIT POLICY ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

Unified Auditing is not included in the Oracle 12c kernel so you have enable to manually. But in Mixed Mode it is on though it is not included in the kernel.
You can check whether the Unified Auditing is enabled or not using the following query.

select VALUE from V$OPTION where PARAMETER='Unified Auditing';
If you wish to linked the Unified Auditing to kernel then use the following commands.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

If you dont set AUDIT_TRAIL=NONE  then both auditing mechanisms will run concurrently.

Reference

Link to Oracle Official Documentation.

Mixed Mode Auditing


Oracle Discoverer like app native for SQL Server? Can someone point me at some web based apps that work like Oracle Discoverer but native for SQL Server.  We already the SQL connector running to actually use disco, but was wondering what other options there were.  Not really interested in setting up connections inside of excel.

thanks.

--update 3/15/12

Oracle Discoverer is a set of BI tools that enable web based access to your data.  I'm looking specifically for the ad hoc data manipulation it does, as well as the admin tool set that exposes data, defines relationships, manaages data access, and defines calculated data items.

SQL Server 2005+ comes with an ad-hoc reporting tool called Report Builder, which does much the same sort of thing as Discoverer.  This will probably get you what you need.

SQL Server It also comes with a reporting tool called Reporting Services (SSRS), a SQL based tool that does the same sort of thing as Crystal Reports or Oracle Reports.  SSRS will consume a variety of data sources, including Oracle.  One key point to note about SSRS is that you can use it as a medium for publishing reports built with Report Builder.


Query to check where database growth was? I have a database that grew very significantly overnight and I'd like to find out why. I've restored yesterdays backup to another location. Is there a query I can run to query the size of tables or indexes so I can compare the two?

I profiled the query and this is the portion that returns the 'Disk Usage by Top Tables' results:

exec sp_executesql @stmt=N'begin try

            SELECT TOP 1000
            (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
            a3.name AS [schemaname],
            a2.name AS [tablename],
            a1.rows as row_count,
            (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
            a1.data * 8 AS data,
            (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
            (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
            FROM
            (SELECT
            ps.object_id,
            SUM (
            CASE
            WHEN (ps.index_id < 2) THEN row_count
            ELSE 0
            END
            ) AS [rows],
            SUM (ps.reserved_page_count) AS reserved,
            SUM (
            CASE
            WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
            SUM (ps.used_page_count) AS used
            FROM sys.dm_db_partition_stats ps
            GROUP BY ps.object_id) AS a1
            LEFT OUTER JOIN
            (SELECT
            it.parent_id,
            SUM(ps.reserved_page_count) AS reserved,
            SUM(ps.used_page_count) AS used
            FROM sys.dm_db_partition_stats ps
            INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
            WHERE it.internal_type IN (202,204)
            GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
            INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
            INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
            WHERE a2.type <> N''S'' and a2.type <> N''IT''
            end try
            begin catch
            select
            -100 as l1
            ,   1 as schemaname
            ,       ERROR_NUMBER() as tablename
            ,       ERROR_SEVERITY() as row_count
            ,       ERROR_STATE() as reserved
            ,       ERROR_MESSAGE() as data
            ,       1 as index_size
            ,       1 as unused
            end catch',@params=N''


SQL Server 2016 deprecation of DML keyword ROWGUIDCOL I just found out that the DML keyword ROWGUIDCOL is deprecated, replacement is $rowguid; and that DML IDENTITYCOL keyword is deprecated, new name is $identity.

I don't understand why a non-variable (no dollar sign symbol) DML can be replaced with what is essentially an alias, which if I understand it, will sometimes evaluate back to ROWGUIDCOL and sometimes to something else?. Can anyone explain what is going on here? What does this deprecation mean?

In AdventureWorks sample, for example, I don't think it means I should do this:

CREATE TABLE [Sales].[Customer]
(
    [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    ...
    [rowguid] [uniqueidentifier] $rowguid  NOT NULL,
    ...
);
GO

The above doesn't work obviously. So what is it saying is deprecated? Clearly not the keyword ROWGUIDCOL above?

I thought when something is deprecated that a new syntax must already exist and this would give me time to transition to a form that won't break in "SQL 2017+".

Origin:

Deprecated Database Engine Features in SQL Server 2016

I think you are misreading the deprecation notice. Specifically, you seem to be confusing DML (Data Modification Language) with DDL (Data Definition Language). What is being deprecated is the ability to use ROWGUIDCOL in INSERT / UPDATE / DELETE / MERGE statements:

-- DDL statement
CREATE TABLE #Test
(
  ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT (NEWID()) ROWGUIDCOL,
  SomeColumn INT NULL
);

INSERT INTO #Test ([SomeColumn]) VALUES (1), (100);

SELECT * FROM #Test;

DECLARE @RowID UNIQUEIDENTIFIER;
SELECT TOP 1 @RowID = tbl.[ID]
FROM   #Test tbl;

SELECT @RowID AS [RowToUpdate];

-- DML statement
UPDATE tmp
SET    tmp.[SomeColumn] += 2
FROM   #Test tmp
WHERE  ROWGUIDCOL = @RowID;

SELECT * FROM #Test;

If you run the above, only one row will be updated. This example shows that ROWGUIDCOL when used in a DML statement (not in a DDL statement) is dynamically replaced with the actual column marked as ROWGUIDCOL. And this is, in fact, why you are able (or required?) to mark a column as ROWGUIDCOL in a DDL statement: so that you (and other processes) can find it later :-).


Is there any disadvantages if i shift from myisam to innodb in mysql 5.7? We are using myisam primarily for full text on mysql 5.5 ,However as 5.6+ innodb supports full text i want to know any performance or other bottleneck ,Please suggest ,we have huge data set in millions and currently tables are mix of myisam and innodb.

Oracle's goal seems to be to get rid of MyISAM.  Version 5.7 has virtually all the improvements to eliminate the 'desire' to use MyISAM.  The next version, 8.0, is attempting to finish the task -- including getting rid of the MyISAM tables from the mysql database.  (cf "Data Dictionary").

The only thing that remains is disk space.  Any InnoDB table will continue to take 2x-3x the disk space of a comparable MyISAM table.  But disk space is cheap and SSDs are prevalent, so this is not a big deal.

I strongly encourage to make the shift.  See my article for some rare gotchas that might bite you in the conversion.  (Which reminds; I should update it to say more about the status of 8.0.)


Are page numbers of a book a good analogy for a clustered index? The title pretty much covers the question.
I think that the index of a book is a good analogy for a non-clustered index, as it demonstrates the extra storage and physical separation properties of a non-clustered index. I also think that the page numbers of a book represent well the physical ordering of data, similar to the structure of data with a clustered index.
However I'm a bit of a newbie to more advanced database theory. Does the analogy for page numbering make sense for a clustered index, or are there any properties of a clustered index where this analogy falls over?

Please note that the terminology of pages in the question refers to pages of a physical book, such as George Orwell's 1984, and not database pages.

Unfortunately I don't think there is a proper StackExchange for theoretical questions, but hopefully you gather good enough information here
Typically the "Phonebook" makes for a good analogy of how indexes work, not only because of the pages sorted by Names (i.e. the nodes of the B-Tree) but the sorting of the LastName, FirstName of the individual People within a specific page being representative of the leaves of the B-Tree. Brent Ozar has a good in-depth article on how indexing works that utilizes this analogy.


Fill percentage of total field with MS Access SQL I am trying to fill, with a SQL query, a field F with the percentage over the total of some field f, such that each record F(i) in field F is given by:

F(i) = f(i)/SUM_i(f(i))

Both fields f and F and located in a table MyTable.

I am however struggling to develop a query which manages to do this. My try was:

INSERT INTO MyTable (F)
SELECT f/MyField.fSum
FROM MyTable, (SELECT SUM(f) AS fSum FROM MyTable) AS MyField

But this code inserts new lines in table MyTable. I don't know how I can proceed from here, as my understanding is that the UPDATE statement is quite limited and wouldn't be useful for doing this.

Any ideas on how I could manage to do this in the simplest form possible?

I have just called the columns f and F here for illustration purposes, it is not how they are named in the actual DB.

You need UPDATE, not INSERT:

UPDATE MyTable
SET F = f / (SELECT SUM(f) AS fSum FROM MyTable) ;

or:

UPDATE
    MyTable AS my,
    (SELECT SUM(f) AS fSum FROM MyTable) AS total
SET my.F = my.f / total.fSum ;

As mendosi commented, having two columns with same name in different (upper/lower) case, like f and F is not recommended in any DBMS and (not sure) probably not even allowed in Access.

The above statements however do not work in version 2013. I think they worked in old (1997) version but some change in the Jet engine (3.5? 4.0?) made the queries throw error instead.

Using the DSUM() function instead, to do the summation, works:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable") ;


Error when executing "create pfile from spfile" I just installed Oracle database 12cR2 and am trying to startup a database.

I am trying to create a pfile from spfile using following query.

create pfile from spfile;

Then I get the following errors.

create pfile from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?=/dbs/spfile@.ora'
ORA-27077: too many files open

Can anybody tell me what's going on here or what I am doing wrong?

ORA-27077: too many files open

Based on that error I guess you have reached the limit of maximum open files.

You can check the soft and hard limits with ulimit -Sn and ulimit -Hn. Configure it in /etc/security/limits.conf or a seperate file under /etc/security/limits.d/. Check the list of actual open files with lsof -u oracle (if the user running the instance is oracle).


Approaches for deleting unnecessary records from tables We have a database which stores the temperatures of 20 thermometer every 7 seconds. We want to delete all records in the way that every minute holds just one tempereature instead of 8 which are older than 3 months. I was planning to do it as follows as discussed in that question here:

Select the (relatively few) surviving rows into a temporary table.
Truncate the table.
Re-insert the survivors.

But all devices are working without interruption and inserting values into the DB so I can not Truncate and rename the temp_tables since the system should be on all the time. Or can I? If not, do I have to do it by DELETE VACUUM?

By the way we delete all unnecessary records which are older than 3 months just once, afterwards we have to repeat this process every month for the 4.th month previous (I hope that part was clear). If we repeat this process at the beginning of 05.2013, we have to delete the unnecessary records of the month 01.2013. How can I automatize this process? Should I better write a .bat file for scheduled task or is there any better approaches?

I can suggest you another approach/solution. You can partition the table and delete partition older than 3 months just dropping them. This is fast and does not impact other inserts.
However seems that you need to keep at least one value; can you calculate values to be kept and insert them in another table?


Does ALTER TABLE - ADD COLUMN lock the table? I have a question about the ALTER TABLE ... ADD COLUMN DDL statement.

On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in the table (as verified via SELECT) before an ALTER TABLE ... ADD COLUMN on the table finishes.

Shouldn't any DML statement that performs a write be queued until the ALTER TABLE operation finishes?

I'm posting this question because I've been asked to perform some test to verify whether it is possible to run ALTER TABLE ... ADD COLUMN on a live Production database in business hours, on a heavily-used database, on tables with several million rows -- which I find very ill-advised. Even if ALTER TABLE does not place a lock on the table, it will have to wait until any connection is not using the table anymore (due to the connection placing a metadata lock), which may happen much much later.
EDIT: Apparently this evaluation was too pessimistic. I've been doing several tests with mysqlslap performing heavy operations on the table (INSERT, UPDATE, DELETE statements, and SELECT statements with LIKE to avoid using indexes) on 150 simulated concurrent connections while ALTER TABLE ... ADD COLUMN runs; profiling shows metadata locks but with short waiting times (1 sec each), and table alteration completes in around 30 minutes, compared to 10 minutes with no SQL statements running. While this is satisfying, on the other hand I'd like to know whether it is safe to assume that DDL statements are non-blocking.

(It is probably worth of note that there is an Instant ADD COLUMN feature on InnoDB, which allows instant addition of a column to the table (under specific constraints), but it is not available before v10.3.2.)

Yes, it locks the table. From the docs on MySQL 8,

  The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

And from the docs you linked, it's pretty explicit

  With instant ADD COLUMN, you can enjoy all the benefits of structured storage without the drawback of having to rebuild the table.

As you've stated, you're on 10.2. So it looks like adding a column will require rebuilding the whole table.

As to what happens when you can't receive a lock,

  I've noticed that INSERT statements complete and the rows are correctly inserted in the table (as verified via SELECT) before an ALTER TABLE ... ADD COLUMN on the table finishes.

Yes, that's generally what happens during a lock, but be aware that's not always what happens. Sometimes statements and transactions give up waiting. Sometimes backends and pools get reaped when they're stuck waiting. It's always safer to do this during downtime, to have timeouts, and to catch errors from libraries when the timeouts expire. So long as you're using transactions, things rollback if something is triggered and can't get it's lock before timeout -- all will be kosher.


Normalization for postal addresses in US (address, county, city, state, zipcode)? I've been trying to understand for the last few years which way is good for storing addresses. I've been getting "normalize all the way" but also "denormalize as much as you can" and I just cannot get my head on deciding what is good for my project.

Shortly, my project would involve lots of users (100k+) and all users will have 1-3 addresses stored (personal, business and billing). That means that I can have 100k+ * 3 records for addresses. Also, I will be doing a lot of look-ups by zipcodes (get users that have addresses registered into a zipcode).
I will only have U.S addresses.

I am happy with the user-to-address tables and their relationship for my project. However, the tables without relationships is what drives me nuts.

(My tables displayed in the image are like that just for me to get a better view on what I need and how do to it. I know there are a lot of redundant fields so please don't take them as they are.)

Does anyone has any tips on how should this be designed?
Does anyone has a link or something to a schema or a similar schema of what big companies use (UPS, USPS, etc)?

I deal with millions of existing international addresses.  The following design works for my project:

Address Table

AddressLine1 varchar(255)
AddressLine2 varchar(255)
City varchar(50)
PostalCode varchar(20)
State      varchar(50)
CountryID  int  (FK to Country table)

Avoid the temptation to normalize postal codes and states, unless you really do have a master list that is updated frequently.

Countries are easier to manage, so they belong in their own lookup table. Master lists can be found online readily.


sql server - Restoring Windows Database Backup File on a Linux Server Instance I need to work with a SQL server instance, however my workspace runs Linux and the database backup that was given to me was running on Windows.

I succesfully managed to install SQL Server on my Linux environment, and I'm using DBeaver to access it, the main problem I have is when I try to restore this .bkp file in my SQL Server instance.

I'm trying to restore the database with this SQL command I read in an answer in this site, however I need the Logical Data File Name and the Logical Data Log. So I ran the next SQL command.

RESTORE DATABASE FILELISTONLY
FROM DISK = '/var/tmp/(redacted).bkp';

I get SQL Error 5133

SQL Error [5133][S0001]: Directory lookup for the file "D:\SQL2012\DATA\(redacted).mdf" failed with the operating system error 2(The system cannot find the file specified.).

Is it possible to restore this file? If so what am I doing wrong?

Thanks in advance.

Depending on what build you are using .. if you are getting an error then it might be a bug that you need to report to Microsoft.

You can still give the undocumented - sp_restore_filelistonly a try but it is just a wrapper as Brent says in his answer :

EXEC sp_restore_filelistonly
    @device_type = 'DISK',
    @backup_path = '/var/tmp/(redacted).bkp';

If above does not work, then for sure open a bug report with Microsoft.

It works for me - I have tested (Microsoft SQL Server 2017 (RTM-CU4) (KB4056498) - 14.0.3022.28 (X64)   Feb  9 2018 19:39:09    Copyright (C) 2017 Microsoft Corporation    Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)) and for me it works using both (restore filelistonly and using undocumented sp)

restore filelistonly from disk = '/var/opt/mssql/backup/dbname_(FULL)_20170108_020026.bak'


How real-time apply works in logical standby oracle? I am a little bit confused about real-time apply services.
I've read a documentation about the services and I still have a question.
Does archived redo logs required for logical standby or not?
I've configure logical standby and there are 3 destination for db_recovery_file dest.
1st is local storage, 2nd transmits logs on standby and the 3d is for switchover.
But still I do not purely understand how it works.
Please, clarify or give some useful links.

Does archived redo logs required for logical standby or not?
  Yes, it does.

https://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ls.htm#g105412

The archived redo logs (archivelogs, commonly) contain SQL statements to apply the changes being made to the database, as well as SQL statements to undo the work if a rollback occurs later on the primary's activity.


Mirroring in SQL Server 2012 I've got newly installed Win 2008 R2 machines, in a workgroup, in the same subnet, and of course SQL Server 2012.

I allowed login with SQL Server authentication, thinking it may help with each workstation having a different user account database. I created identical logins for each of three machines. Two have the full SQL Server software and one has SQL Server Express. Then I tried to mirror two different databases, producing the same result.

The first, I just right clicked on database in the SQL Server Management Studio, made a new database, then confirmed the recovery model was set to full. Backed it up and the transaction log. Restored the two items to my other server. (To start I'm not using the Witness yet in hopes to keep it as simple as possible) When restoring the data and log, for both I am restoring it without recovery. Then when I try to mirror, I get one warning about Fully qualified domain names, click through it because my servers are not in a domain, and then when it tries to mirror and comes back with:

  An error occurred while starting mirroring.

  Additional Information:
  Alter failed for database 'test'. (Microsoft.SqlServer.Smo)
  An exception occured while executing a Transact-SQL statement or batch.
  (Microsoft.SqlServer.ConnectionInfo)
  The server network address "TCP://(SERVERNAME):5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, ErrorL 1418)

I have tried it with the AdventureWorks database too with the same result.

In Event Viewer I am seeing "Database Mirroring connection error 4. 'An error occurred while receiving data.' '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://(Mirrored SERVERNAME):5022'.

In the SQL Server log I am seeing

  Error 1443. Severity 16. State 2
  Database mirroring has been terminated for database 'test'. This is informational only.  No user action is required.

and

  Error 1474. Severity 16.  State 1.
  Database Mirroring connection error 4. 'An error occurred while receiving data.'  '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://(Mirrored SERVERNAME):5022'.

I have been doing searches on google trying to get my head around this, but I think because I am new to all this, I am having a hard time understanding where to go from here.

Sorry for the previous useless response - skipped over the fact that you are setting up a mirror without a domain. You will need to use certificates for this.

I found an article that might help you with that part. Check it out: http://mssqlnet.wordpress.com/2012/06/20/microsoft-sql-server-database-mirroring-in-a-workgroup-without-domain-with-certificate/


Sub queries or joins? Whats better in terms of SQL server efficiency; to use sub queries or joins?

I know uncorrelated are better than correlated sub-queries. But what about joins?

The SQL becomes more readable and understandable using joins

OUTER JOIN and check for NULLS

but is it worse or better for performance of the DB?

Taking your examples, these are normally different queries if c2 is a child of c1 (a.k.a many rows in c2 per row in c1). Which is the usual case unless you only have 1:1 relationships.

select c1 from t1 join t2 on c1 = c2

select c1 from t1 where c1 in (select c2 from t2)

The first one will give a row for each match
The second gives unique c1 values only.

To make them the same you'd need DISTINCT on the first one: which doesn't make it more readable.

Going further, the 2nd one can be expressed as

select c1 from t1
INTERSECT
select c2 from t2

select c1 from t1 where EXISTS (select * from t2 WHERE t1.c1 = t2.c2))

That is: IN, EXISTS and INTERSECT give the same results.

Now, we may as well cover the inverse case too since you asked about LEFT JOINs. Here we have NOT IN, NOT EXISTS, LEFT JOIN and EXCEPT.

Let's say we want rows from t1 with no rows in t2 where t2.c3 = 'foo'

select c1 from t1 left join t2 on t1.c1 = t2.c2 AND t2.c3 = 'foo'
WHERE t2.c2 IS NULL

select c1 from t1 where c1 NOT in (select c2 from t2 where t2.c3 = 'foo')

select c1 from t1
EXCEPT
select c2 from t2 where t2.c3 = 'foo'

select c1 from t1 where NOT EXISTS
        (select * from t2 WHERE t1.c1 = t2.c2 AND t2.c3 = 'foo')

In this case, only EXCEPT and NOT EXISTS are always correct. NOT IN will fail in t2.c2 is ever NULL. And you need DISTINCT in the LEFT JOIN.

Note also that the LEFT JOIN filter is in the ON clause. To remove this you'd need a derived table or a CTE.

I would also say that the subqueries these days don't matter too much given the sophistication of the query optimisers (unless abused of course)

Personally I almost always use EXISTS and NOT EXISTS (maybe INTERSECT or EXCEPT for clarity) so my code is consistent and works in every case. I don't have to worry about bollixing a LEFT JOIN into an INNER JOIN, or NULLs in an NOT IN.


How to fix below blocking problem on SQL server 2014 I am having a strange blocking problem which i am not able to understand.
During a slighter more volume due to some app pooling issues we see sleeping spids with open transactions, so those SP's tend to be lead blocker and blocks few delete statements.
Yes we are aware on sleeping SPID and working with app team to fix the issue, but i am not able to understand why on below
SP's which leave their session open say EXEC spleadblocker does not access any of the table underneath within its code i.e
it blocks a simple statement like below within an SP say spblocked
DELETE from dbo.table1 WHERE Col1=@col1
OPTION (QUERYTRACEON 9481)

spleadblocker does not have any thing to do with table "table1" and should not blocking therefore above statement
Waittype is LCK_M_X
and wait_resource is database.dbo.table1.PK_table1

table 1 has col 1  as PK /Clustered index created , which is of data
type bigint not null column

Why an SP with no correlation to this table will create blocking and is there a way i can avoid that PK scan from that delete statement to avoid being blocked?
UPDATE After more research below is what we found, say the very first SP which comes SP1leablocker for spid 66 and had its transaction open with status sleeping for about a min. This session block the delete statement above and yes they have table 1 in SP1leablocker code, so explains the blocking
Later, on above spid 66, multiple times we see different SP's running and creates a blocking chain for that delete statement of around 10 mins because that SPID 66 gets connection from various other SP's with status sleeping and open tran
Is there a way to avoid blocking on DELETE statement ?

As stated by a previous user the lock type indicates that your query tries to acquire an Exclusive lock on a resource.
Is your predicate (Col1=@col1) supported by in index ?
Some options for a query referencing a specific table to acquire locks on other tables can be: triggers, fks and fk cascading.
More info is required. See this post for more ideas.
Have you tried to capture the blocked process report ?
See this post for how to run a server side trace to capture the "Blocked Process Report Event" constantly in the background so that when your issue occurs you do not miss it and have the data needed for analysis


Pitfalls of creating a foreign key constraint on a system table? I have a number of tables which hold metadata about the tables and packages in the system. This is intended to be a resource for analysts and documentation so it is not used to create data.

One of the columns that appears in a few of these tables shows the user or "Owner" of the object.  The data should only contain user accounts in the database and it seems like a good practice to have a foreign key constraint from the metadata tables to  sys.user$.

However I have always approached SYS tables with caution and I was surprised to see that there is no primary key for SYS.USER$ only a unique index on the NAME column.

As far as I know I cannot create a foreign key on sys.all_users.  Or I could create a table as Select username, user_id from all_users  so the data entry is checked on insert but it all seems very clumsy.

Is there a better way to access a table that has a primary key and the names of all schemas?

I ended up adding a check constraint as a hard coded solution that won't mess with system files.  I still think there should be way to get a list of the schemas or "owners" that can be used as a foreign key constraint.

@Phil, why don't you post your comment as an answer?

I did not find an elegant way to solve this question but adding a check constraint on the "Owner" column of the metadata tables accomplished the objective of ensuring that only schema owners would be listed.  If a new schema owner is added then all the check constraints would have to be changed but that is not too onerous.

I tip my hat to Phil for confirming that backing away slowly from system tables is the best approach and wish he had posted this as an answer.


Combine intersecting ranges to produce new ranges based on the points of intersection I have a table with data like this:

ID  valid_from          valid_to             item_a    Item_b    Item_c
1   20.5.2016 15:28:02  21.5.2016 7:12:49    1         2          -
1   21.5.2016 7:12:50   17.6.2016 8:49:29    1         4          -
1   17.6.2016 8:49:30   31.12.2999           1         5          -
1   24.6.2016 11:23:13  31.12.2999           -         -          6

The last two rows have validity ranges that intersect. More specifically, one of the ranges is longer, so the period from 24.6.2016 11:23:13 to 31.12.2999 is covered by both ranges. I want that to be returned as a single row for all three items. And I want the longer range to be cut off at the beginning of the common subrange, i.e. at 24.6.2016 11:23:13.

So I would like the output to look like this:

ID  valid_from          valid_to             Item_a    Item_b    Item_c
1   20.5.2016 15:28:02  21.5.2016 7:12:49    1         2          -
1   21.5.2016 7:12:50   17.6.2016 8:49:29    1         4          -
1   17.6.2016 8:49:30   24.6.2016 11:23:13   1         5          -
1   24.6.2016 11:23:13  31.12.2999           1         5          6

How should I write my query to solve this problem?

My guess

select ID, valid_from
  , lead(valid_from,1,'31.12.2999') over (partition by id order by valid_from) as valid_to
  , case Item_a when '-' then lag(Item_a,1,'-') over (partition by id order by valid_from) else Item_a end as Item_a
from t


Get relation data on comma separated values I am an average in database operation but I never deal with a database having values in comma separated manner. As I want the result from both table.

I have one table "Form table"

+------+-----------------+
| id   | Fname           |
+------+-----------------+
| 1    | f_934_tarun1    |
| 2    | f_934_tarun2    |
| 3    | f_934_a1        |
+------+-----------------+

2nd table "user assigned form table"

+------+--------------------------------+
| u_id | Fname                          |
+------+--------------------------------+
| 1    | f_934_xyz,f_934_tarun1         |
| 2    | f_934_tarun2,f_934_fhh         |
| 3    | f_934_ttt,f_934_nhj            |
| 4    | f_934_a1,f_934_t5trt           |
+------+--------------------------------+

I want to get all user ids who has any of form listed in form table. So here the output will be:

+------+
| u_id |
+------+
| 1    |
| 2    |
| 4    |
+------+

Every help will be appreciated.

You can use MySQL INSTR to achieve this:

SELECT U.u_id
FROM Userassigned U
JOIN FormTable F ON INSTR(U.Fname, F.Fname) > 0;

SQL Fiddle for the same: http://sqlfiddle.com/#!9/6f27ce/1


MySQL: Soft Delete post likes I have implemented a like/unlike features for posts in one of my projects. The like table is normalized and has a column visible. The schema is as follows:

Column      Type       Null    Default  Links to
id          int(11)     No
user_id     int(11)     Yes     NULL    user  -> id
post_id     int(11)     Yes     NULL    posts -> id
visible     tinyint(1)  Yes     NULL
created     datetime    No
updated     datetime    No

What I'm doing currently is that when a user likes a post, I create a record and when he un-likes it, I just set the visible column to false. The count is calculated by post_id and visible = true. I did that to prevent excessive load on database when a user likes/unlikes a post.

My question is am I being paranoid in considering database performance so early (without much traffic) or is this among the best practices and I should continue with this?

If (post_id, user_id) is unique, then jettison the id and make that pair the PRIMARY KEY.

I specifically suggest that order because I assume this query is frequently used?

SELECT COUNT(*)
    FROM tbl
    WHERE post_id = ?
      AND visible;

By having the PK start with post_id, you get the added efficiency of "clustering", thereby drastically reducing the I/O when this table eventually becomes so large that it can no longer be cached.

I chose not to include visible in any way because (1) it confuses the "uniqueness" requirement of the PK, and (2) the row would have to 'move' when visible changes.  This would be a bunch of overhead.  (OK, maybe that action is rare enough not to care.)

See also my indexing cookbook.

If you want to discuss this further, please provide SHOW CREATE TABLE and the critical queries that hit the table.


create table with date name I am writing a simple procedure to backup a table-

CREATE PROCEDURE daily_backup()
BEGIN
   DECLARE given_date VARCHAR(25);
   SET given_date = now();

   CREATE TABLE given_date LIKE db1.table1;
   INSERT INTO given_date SELECT * FROM db1.table1;

END

But it creates a table with name given_date . I want to create a table with date as name.
How to do the same ?

Try This one

DROP PROCEDURE IF EXISTS daily_backup;

DELIMITER //

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

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

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

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

END //
DELIMITER ;
CALL daily_backup();

UPDATE

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

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

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

UPDATE by RolandoMySQLDBA

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

  The backup will be named db1.table1_YYYMMDD_HHMMSS

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


PostgreSQL - retrieve all IDs in a tree for a given subnode I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node.

The table is very simple, just an join table with a parent id and child id.
This is a representation of the tree I stored in my db.

In this example if I search for node 17 I need in return 14-17. If I search for 11 I need in return 1-6-5-4-8-11-12-7-2-10-3.

The order is not important. I only need the ID to avoid circularity when adding children to a node.

I created this query.
The ancestor part works fine, I retrieve all parent nodes, but for the descendants I have some trouble. I'm only able to retrieve some part of the tree.
For example, with node 11 I retrieve 4-10-6-11-7-8, so all right part of the tree is missing.

WITH RECURSIVE
-- starting node(s)
starting (parent, child) AS
(
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t
  WHERE t.child = :node or t.parent = :node
)
,
ancestors (parent, child) AS
(
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t
  WHERE t.parent IN (SELECT parent FROM starting)
  UNION ALL
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (parent, child) AS
(
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t
  WHERE t.parent IN (SELECT parent FROM starting) or t.child in (select child from starting)
  UNION ALL
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t JOIN ancestors AS a ON t.parent = a.child
)

table ancestors
union all
table descendants

UPDATE

I see that many examples included in the tree table also the root in form (root_id, null).

In my case I don't have this record.

For example, taking the smallest tree 14->17, in my table I have only one record
parent, child

14      17

A very primitive implementation:

It basically divides the problem into two subproblems:

First find all the ancestors of the node in question (including the node itself). If the node has no parents, then this would be just itself.
Then find the descendants of all those ancestors (including themselves). We may have several nodes in the ancestors result set, we may get duplicates here, so we use UNION (and not UNION ALL) to remove them.
Note that the query will work even if the input node is a root with has no children.
It will also work if the data set is not a forest of trees but an arbitrary  directed graph (where nodes can have more than one parent).

The query:

WITH RECURSIVE
ancestors (parent) AS
(
  SELECT :node                      -- start with the given node
  UNION ALL
  SELECT t.parent                   -- and find all its ancestors
  FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (customer) AS
(
  SELECT parent AS customer       -- now start with all the ancestors
  FROM ancestors
  UNION
  SELECT t.child                  -- and find all their descendants
  FROM public.customerincustomer AS t JOIN descendants AS d ON t.parent = d.customer
)
SELECT customer
FROM descendants ;


Column containing the word from another column I am trying to select records in a postgresql where the service column contains usernames.

Something like this but not 1 to 1:

select service, cash from services where service not in (select name from users);

and this but for all users:

select service, cash from services join users on service not like '%' || 'username' || '%' and user = id_users;

this show me all records from services:

 select service, cash from services join users on service not like '%' || name || '%' and user = id_users;

example:

    CREATE TABLE users (
    id_users SERIAL PRIMARY KEY,
    name character varying(50) NOT NULL
);

CREATE TABLE services (
    service text,
    cash real,
    usery integer REFERENCES users (id_users)
);

insert into users (name)
values ('user1'),('user2'),('user3'),('user4');
select * from users;

insert into  services (service, cash, usery)
values ('user1','123','1'),
       ('something user2 something','123','1'),
       ('something user3 something','123','3'),
       ('something something','123','1'),
       ('fhalsefh','123','4');
select * from services;

I expect:

service              |  cash  |  usery
---------------------------------------
something something  |   123  |    1
fhalsefh             |   123  |    4

From your sample data I guess something like:

select * from services x
where not exists (
   select 1 from users y
   where x.service like '%' || y.name || '%'
);

should do


SQL Server 2017: How does Interleaved Execution work? Interleaved Execution is part of a family of features in the 2017 query processor that consists of:

Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback

So how does Interleaved Execution work?

The Devil Is In The Table Variable

Interleaved execution is aimed at correct cardinality misestimated in Multi-Statement Table Valued Functions.

In prior versions of SQL Server, these functions would always produced rather shoddy estimates:

2014, 2016: 100 rows
2005 - 2012: 1 row

Needless to say, this could cause a lot of problems when joining to other tables.

While selecting data from a table variable does not inhibit parallelism on its own, the low row estimates would often contribute to low query costs, where parallelism wouldn't be considered.

With Interleaved Execution, cardinality estimation is paused, the subtree for the MSTVF is executed, and optimzation is resumed with a more accurate cardinality estimate.

How do I know if my MSTVF receives interleaved execution.

Like with Adaptive Joins, Interleaved Execution is noted in the query plan. Unlike Adaptive Joins, it is not noted in estimated plans, at least as of this writing.

The plan shape for a MSTVF with Interleaved Execution is a bit different from a typical plan that has a MSTVF in it.

You'll see the Table Valued Function operator at the top of the plan, and the scan of the Table Variable where the TVF operator would normally be in the graphical plan.

When hovering over the TVF operator, you'll see the attribute IsInterleavedExecuted set to True, as well as a an estimated number of rows that may very nearly reflect reality. Hurrah.

Are there any Extended Events to troubleshoot when Interleaved Execution doesn't occur?

Yes, a whole bunch:

Note that some of these are in the Debug channel, which isn't selected by default when searching for Events to Extend.

Does Interleaved Exection Require ColumnStore Indexes?

No, they'll work either way. Here's an example:

SELECT u.Id, mj.*
FROM   dbo.Users_cx AS u --ColumnStore
JOIN   dbo.MultiStatementTVF_Join(0) AS mj
ON mj.UserId = u.Id
WHERE  u.LastAccessDate >= '2016-12-01';

SELECT u.Id, mj.*
FROM   dbo.Users AS u --RowStore
JOIN   dbo.MultiStatementTVF_Join(0) AS mj
ON mj.UserId = u.Id
WHERE  u.LastAccessDate >= '2016-12-01';

These queries each join to different tables. One ColumnStore, one not. They both get Interleaved Execution plans.

When does Interleaved Execution work?

Right now, it only works with MSTVFs where the correlation is done outside of the function.

Here are a couple examples:

This function has no inner correlation, meaning there's no WHERE clause predicated on a table column and a passed in variable.

CREATE OR ALTER FUNCTION dbo.MultiStatementTVF_Join
(
    @h BIGINT
)
RETURNS @Out TABLE
(
    UserId INT,
    BadgeCount BIGINT
)
AS
    BEGIN
        INSERT INTO @Out ( UserId, BadgeCount )
        SELECT   b.UserId, COUNT_BIG(*) AS BadgeCount
        FROM     dbo.Badges AS b
        GROUP BY b.UserId
        HAVING   COUNT_BIG(*) > @h;
        RETURN;
    END;
GO

This function is the opposite, with a predicate on the UserId column with a passed in variable.

CREATE OR ALTER FUNCTION dbo.MultiStatementTVF_CrossApply
(
    @h BIGINT,
    @id INT
)
RETURNS @Out TABLE
(
    UserId INT,
    BadgeCount BIGINT
)
AS
    BEGIN
        INSERT INTO @Out ( UserId, BadgeCount )
        SELECT   b.UserId, COUNT_BIG(*) AS BadgeCount
        FROM     dbo.Badges AS b
        WHERE    b.UserId = @id
        GROUP BY b.UserId
        HAVING   COUNT_BIG(*) > @h;
        RETURN;
    END;
GO

It's a common misconception that CROSS APPLY won't work. The real limitation is noted previously. The inner-function correlation is the deal breaker.

SELECT u.Id, mj.*
FROM   dbo.Users AS u --RowStore
CROSS APPLY dbo.MultiStatementTVF_Join(0) AS mj
WHERE  mj.UserId = u.Id
       AND u.LastAccessDate >= '2016-12-01';

SELECT   TOP 1 u.Id, mj.*
FROM     dbo.Users AS u --RowStore
CROSS APPLY dbo.MultiStatementTVF_CrossApply(2147483647, u.Id) AS mj
WHERE    u.LastAccessDate >= '2016-12-01'
ORDER BY u.Id;


How to simplify repeating variables in where clause for multiple columns SQL Server I know there has to be a better way to evaluate a set of repeating variables against multiple columns. I've been searching for a while and still don't have a great solution. Is there a better way to do this?

SELECT *
FROM TABLE
WHERE
  (SUBSTRING(COLUMN1,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)
OR SUBSTRING(COLUMN2,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)
OR SUBSTRING(COLUMN3,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)
OR SUBSTRING(COLUMN4,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)
OR SUBSTRING(COLUMN5,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30))
...REPEATS 5 MORE TIMES

One good way to get reasonable performance from this type of query is to insert the 'val1', 'val2', ... values into a temporary table, then join against that temporary table.

We start with some sample data:

USE tempdb;
IF OBJECT_ID('dbo.Items') IS NOT NULL DROP TABLE dbo.Items;
CREATE TABLE dbo.Items
(
    ItemID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Items
        PRIMARY KEY CLUSTERED
    , col1 varchar(30) NOT NULL
    , col2 varchar(30) NOT NULL
    , col3 varchar(30) NOT NULL
    , col4 varchar(30) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.Items (Col1, Col2, Col3, Col4)
VALUES ('value0', 'value02', 'value03', 'value04')
    , ('value11', 'value21', 'value31', 'value41')
    , ('value5', 'value6', 'value7', 'value8');

To find desired rows, we insert the search terms into a temporary table, then join against it:

IF OBJECT_ID('tempdb..#SearchItems') IS NOT NULL DROP TABLE #SearchItems;
CREATE TABLE #SearchItems
(
    val varchar(30) NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO #SearchItems (val)
VALUES ('value2%')
    , ('value03%')
    , ('cow%');

SELECT i.*
FROM dbo.Items i
    INNER JOIN #SearchItems si
        ON (i.col1 LIKE si.val)
            OR (i.col2 LIKE si.val)
            OR (i.col3 LIKE si.val)
            OR (i.col4 LIKE si.val);

The output from the query above:

+--------+---------+---------+---------+---------+
| ItemID |  col1   |  col2   |  col3   |  col4   |
+--------+---------+---------+---------+---------+
|      1 | value0  | value02 | value03 | value04 |
|      2 | value11 | value21 | value31 | value41 |
+--------+---------+---------+---------+---------+


Load balancer versus connection pooling -- is there a difference? I am working on a project that is expected to serve millions of users shortly after launch. The database is postgres, and for now I am assuming at least two servers will be needed. A sys admin (who is clearly well versed in scalable systems) has suggested putting a load balancer in between the web servers and the database servers.

My question is with regard to the difference of load balancing versus connection pooling. To maintain performance, should I be looking at one or the other, or both?

With PostgreSQL you have two different areas that can do pooling, either at the app layer (i.e. jdbc built in pooling etc) or in an intermediate layer that sits between the app and the db(s), such as pgbouncer or pgpool.

If you do pooling in an intermediate layer, like pgbouncer or pgpool, then that layer can ALSO do load balancing of select queries.  Further when load balancing, you can do writes in one of two ways: you can either have a single write master that replicates via other means to your read slaves, using a tool like slony or the built in streaming replication that showed up in pg 9.0 and above, or you can have the load balancer do all the writing, so that incoming reads hit only one db, but writes hit each database to keep them all updated.

Or if you're adventerous, you can move the load balancing layer down another layer into postgresql itself using plproxy.  This is a pl language for pgsql designed to allow you to put one pg database on the front end that has no actual data in it, and that database then can run on top of multiple possibly redundant dbs for incredible throughput.  plpoxy is pretty complex to get set up and running but it is also quite scalable as well.  Note that your application has to be re-written to support it so it can't be tossed under any old app and just work.

http://slony.info/
http://wiki.postgresql.org/wiki/PL/Proxy
http://pgpool.projects.postgresql.org/