Proper creation of a database with categories that have sub-categories Preface: I have for the most part never worked with databases, and am new. So my terminology may be incorrect at times.

I was wondering what the correct procedure would be for creating a database that has objects with multiple objects contained in them.

For a practical example, I was thinking if I were to make a database with separate meals, and also have the ingredients within the meals, how should I go about doing this? I was thinking it might look like this:

Meals  | Ingredient  | ~
------------------------
Chili  | Ground Beef | ~
Chili  | Beans       | ~
Chili  | Corn        | ~
Burger | Ground Beef | ~
Burger | Tomato      | ~
Burger | Lettuce     | ~
Burger | Bun         | ~

(the tilde represents extra columns, maybe calories, unit price, etc)

I was thinking this might be good, but I wouldn't be to sure how to group the meals. Would I have to make some query that looks through the meals column, finds similar names, then groups them together?

Not sure if there would be any better way of storing this type of information?

Meals and Ingredients are two separate entities joined like M:N. The most common storing structure consists from 3 tables - one table per each entity (2 tables) and join table which is referenced to first two tables. - Akina

You might find the following Q & A helpful: When to add another lookup table for the same field


Transfer of data from table to other database table have the same primary key I have a database with data, I want to copy the all tables with data to newly created database also with same existed auto incremented primary field. How I can do that process.

There are a couple of ways to do this.  It depends on your setup.  As per the previous question,  if you just need a copy of everything you can backup and restore the database onto a new instance or, on the same instance with a different name.

If you need to physically pipe data with identity columns, into a new database thats already setup, you could use the import export tool.  With that you can easily select just the tables you want data from, and the destination.  Its quick and easy for a one time load.  If you need to keep Identity columns, remember to select Column Mappings, and check the box to enable Identity insert per the article on Microsoft's website.

This is just one option to moving the data.  A quick google and you will find many other options, including BCP, Linked Servers, SSIS, etc.  It all depends on how often you want to do this load.  How often do you need to do this?


Unknown column 'actions_in_group' in 'field list' I keep getting: Unknown column 'actions_in_group' in 'field list'

Here's the SQLFiddle: http://sqlfiddle.com/#!9/4ddf0/19

SELECT *,
   SUM(actions_in_group) AS total_rows_in_group,
   GROUP_CONCAT(in_collection) AS complete_collection
   FROM (SELECT stream.*,
          COUNT(stream.id) AS actions_in_user_group,
          GROUP_CONCAT(stream.id) AS actions_in_user_collection
       FROM stream
       INNER JOIN follows
       ON stream.user_id = follows.following_user
       WHERE follows.user_id = '0'
       GROUP BY stream.user_id,
            date(stream.stream_date)
     ) As something
   GROUP BY object_id,
            date(stream.stream_date)
   ORDER BY stream.stream_date DESC;

The SUM should be on actions_in_user_group, not actions_in_group

Same goes with actions_in_user_collection

I also added groupby_stream_date to assist with the group by

SELECT *,
   SUM(actions_in_user_group) AS total_rows_in_group,
   GROUP_CONCAT(actions_in_user_collection) AS complete_collection
   FROM (SELECT stream.*,date(stream.stream_date) groupby_stream_date,
          COUNT(stream.id) AS actions_in_user_group,
          GROUP_CONCAT(stream.id) AS actions_in_user_collection
       FROM stream
       INNER JOIN follows
       ON stream.user_id = follows.following_user
       WHERE follows.user_id = '0'
       GROUP BY stream.user_id,
            date(stream.stream_date)
     ) As something
   GROUP BY object_id,
            groupby_stream_date
   ORDER BY groupby_stream_date DESC;


Use regex in postgres I want to use regexp_replace() to replace the string

  name1-01.name2-02.1

to

  name1.01-name2.02.1

How to use the regex here?

select regexp_replace('name1-01.name2-02.1','(.*)-(\d+)\.(.*)-(.*)','\1.\2-\3.\4');

Assuming:

Everything before the first '-' can contain anything but '-'.
There's always a number after the first '-'
'name2' doesn't contain a '-'

Just guessed your requirements, basically. Might be wise for you to read a regular expression tutorial.


How can I change TIMESTAMP to TIMESTAMP(3)? I have a column of TIMESTAMP, NOT NULL, DEFAULT CURRENT_TIMESTAMP.

I want to modify it as TIMESTAMP(3).

I tried

ALTER TABLE <table>
MODIFY COLUMN <column>
TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

And I got.

  Error Code: 1067. Invalid default value for '<column>'

What can I do with it?

I'm answering for my own question.

I found an answer solves the exact problem.

https://stackoverflow.com/a/23671577/330457

Shortly, I needed to specify the same type for DEFAULT.

ALTER TABLE <table>
MODIFY COLUMN <column>
TIMESTAMP(3)
NOT NULL
DEFAULT CURRENT_TIMESTAMP(3) -- See?


Database Engine vs. Database location I'm a new user of SQL Server 2008 R2.

We work in a Windows domain environment and I have access to multiple servers - by this I mean I have various drives mapped to various servers.

I have SQL Server 2008 R2 installed in my local machine and have been able to create a database on my local machine using Windows Authentication.

My question is:  Given that I have SQL Server in my local machine, can I create a database and store it in any other server I have access to?  Or does this other server need to have SQL Server installed as well?

Thank you in advance!

Installing SQL Server 2008 DB files on network shares really isn't fully supported properly until you get to SQL 2012.  I'm not sure how it'll work as I've never tried that in prod but you can do it.
http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/

To answer your security question though, no, the user authenticating against SQL Server does not need access to that file location, that file, or anything like that.  The SQL Server service account that manages that DB does need access to it though across the network.

Also, No, the remote storage server does not need SQL Server engine or client installed.


Show specific data from a partitioned table in SQL Server. Data Archiving using Table Partitions I have created partitions on a table i.e. tblUserDetails having the following record details based on the partitions:

ObjectName       PartitionNumber    RangeValue  RowCnt
tblUserDetails      1               01-01-2014  500
tblUserDetails      2               01-01-2015  347
tblUserDetails      3               01-01-2016  1948
tblUserDetails      4               01-01-2017  4077
tblUserDetails      5               01-01-2018  478

Now I want to hide the data of year 2014 i.e. display the data from year 2015 to 2018 and it should be available in the same table so that if the user wants to see the data it can be seen.

eg: Whenever I fire a query like like Select * from tblUserDetails. It should list all the records from year 2015 to 2018 and not the records of 2014.
But if user still wants to see the records of 2014 then the user will have to use some key like where or some thing explicitly to show all the records.

My requirement is to archive the data before a specific date. But still if user wants to see old records then on click of a button 'Show Old Records' every thing should be displayed.

I dont want to use the Swtich Command, because in that i will have to create another table.

I want to achieve this without making any changes in the existing code. And this is just a one table. I want to do this on many tables.

So, if there is any option to hide or disable a partition of a table then that will also be helpful.

If you want to archive it and do not want it to be returned in default queries, then remove it from the table and add the filegroup for this partition to another table tblUserDetailsArchive. Then any user could query

select * From tblUserDetailsArchive

or

select * From tblUserDetailsArchive where datecreated < '20140101'

to get the older rows if required.


How to get a parallel nested loop in postgres 12? I have a lateral join, and I would like each subselect to execute in parallel. According to the docs (https://www.postgresql.org/docs/12/parallel-plans.html), nested loops can be parallelized:

Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.
In a nested loop join, the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes.

However, I can't get a nested loop - or any join - to execute in parallel. Here's a (contrived) example:
=> create table foo as
  select round(random()*100) as pid, val
  from generate_series(1, 10000000) v(val);
=> set force_parallel_mode = on;
=> explain
  SELECT pid, sum
  FROM (
    select distinct pid
    from foo
  ) x
  JOIN LATERAL (
    select sum(val)
    from foo y
    where x.pid = y.pid
  ) y
    ON TRUE;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=358361.91..18288838.71 rows=101 width=16)
   ->  HashAggregate  (cost=179057.19..179058.20 rows=101 width=8)
         Group Key: foo.pid
         ->  Seq Scan on foo  (cost=0.00..154056.75 rows=10000175 width=8)
   ->  Aggregate  (cost=179304.72..179304.73 rows=1 width=8)
         ->  Seq Scan on foo y  (cost=0.00..179057.19 rows=99012 width=4)
               Filter: (foo.pid = pid)
 JIT:
   Functions: 12
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(10 rows)

I know that parallel plans are sometimes generated on this database, here is an example:
=> explain select pid, sum(val) from foo group by 1;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=92560.09..92577.55 rows=101 width=16)
   Group Key: pid
   ->  Gather Merge  (cost=92560.09..92574.52 rows=404 width=16)
         Workers Planned: 4
         ->  Sort  (cost=91560.03..91560.28 rows=101 width=16)
               Sort Key: pid
               ->  Partial HashAggregate  (cost=91555.66..91556.67 rows=101 width=16)
                     Group Key: pid
                     ->  Parallel Seq Scan on foo  (cost=0.00..79055.44 rows=2500044 width=12)
(9 rows)

postgres server version: 12.4
linux kernel version: 5.4

I think that lateral join (other than for function calls) just doesn't support parallel execution.  Not everything that conceivably could support it has actually been implemented yet (and probably never will be).
Also, the DISTINCT feature also doesn't support parallel execution currently.  But changing your example to get rid of that still does not show parallel execution.


Why does this query result in deadlock? I provide below the raw MySQL query and also the code in which I do that programatically. If two requests are being done at the same time results in the following error pattern:

  SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: update user_chats set updated_at = 2018-06-29 10:07:13 where id = 1)

If I execute the same query but without transaction block around it will work without error with many concurrent calls. Why ? (The transaction aquires lock, right ?)

Is there any way to solve this without locking the entire table ? (Want to try avoid table level locks)

I know that a lock is acquired for inserting/updating/deleting tables in MySql with InnoDB but still do not understand why the deadlock happens here and how to solve it in the most efficient way.

    START TRANSACTION;

    insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`)
        values (1, 2, 'dfasfdfk);
    update `user_chats`
        set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

    COMMIT;

Above is the raw query, but I do it in PHP Laravel Query Builder as follows:

    /**
     * @param UserChatMessageEntity $message
     * @return int
     * @throws \Exception
     */
    public function insertChatMessage(UserChatMessageEntity $message) : int
    {
        $this->db->beginTransaction();
        try
        {
            $id = $this->db->table('user_chat_messages')->insertGetId([
                    'user_chat_id' => $message->getUserChatId(),
                    'from_user_id' => $message->getFromUserId(),
                    'content' => $message->getContent()
                ]
            );

            //TODO results in lock error if many messages are sent same time
            $this->db->table('user_chats')
                ->where('id', $message->getUserChatId())
                ->update(['updated_at' => date('Y-m-d H:i:s')]);

            $this->db->commit();
            return $id;
        }
        catch (\Exception $e)
        {
            $this->db->rollBack();
            throw  $e;
        }
    }

DDL for the tables:

CREATE TABLE user_chat_messages
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_chat_id INT(10) unsigned NOT NULL,
    from_user_id INT(10) unsigned NOT NULL,
    content VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id),
    CONSTRAINT user_chat_messages_from_user_id_foreign FOREIGN KEY (from_user_id) REFERENCES users (id)
);
CREATE INDEX user_chat_messages_from_user_id_index ON user_chat_messages (from_user_id);
CREATE INDEX user_chat_messages_user_chat_id_index ON user_chat_messages (user_chat_id);

CREATE TABLE user_chats
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

The FOREIGN KEY user_chat_messages_user_chat_id_foreign is the cause of your deadlock, in this situation.

Fortunately, this is easy to reproduce given the information you've provided.

Setup

CREATE DATABASE dba210949;
USE dba210949;

CREATE TABLE user_chats
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE user_chat_messages
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_chat_id INT(10) unsigned NOT NULL,
    from_user_id INT(10) unsigned NOT NULL,
    content VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id)
);

insert into user_chats (id,updated_at) values (1,NOW());

Note that I removed the user_chat_messages_from_user_id_foreign foreign key as it references the users table, which we don't have in our example. It is not important for reproducing the problem.

Reproducing the deadlock

Connection 1

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

Connection 2

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

Connection 1

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

At this point, Connection 1 is waiting.

Connection 2

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

Here, Connection 2 throws a deadlock

  ERROR 1213 (40001): Deadlock found when trying to get lock; try
  restarting transaction

Retrying without the foreign key

Let's repeat the same steps, but with the following table structures. The only difference this time around is the removal of the user_chat_messages_user_chat_id_foreign foreign key.

CREATE DATABASE dba210949;
USE dba210949;

CREATE TABLE user_chats
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE user_chat_messages
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_chat_id INT(10) unsigned NOT NULL,
    from_user_id INT(10) unsigned NOT NULL,
    content VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

insert into user_chats (id,updated_at) values (1,NOW());

Reproducing the same steps as before

Connection 1

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

Connection 2

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

Connection 1

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

At this point, Connection 1 executes, instead of waiting like before.

Connection 2

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

Connection 2 now is the one waiting now, but it has not deadlocked.

Connection 1

commit;

Connection 2 now stops waiting and executes its command.

Connection 2

commit;

Done, with no deadlock.

Why?

Let's look at the output of SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-07-04 10:38:31 0x7fad84161700
*** (1) TRANSACTION:
TRANSACTION 42061, ACTIVE 55 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140383222380288, query id 81 localhost root updating
update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42061 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000a44b; asc      K;;
 2: len 7; hex b90000012d0110; asc     -  ;;
 3: len 4; hex 5b3ca335; asc [< 5;;
 4: len 4; hex 5b3ca335; asc [< 5;;

*** (2) TRANSACTION:
TRANSACTION 42062, ACTIVE 46 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140383222109952, query id 82 localhost root updating
update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42062 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000a44b; asc      K;;
 2: len 7; hex b90000012d0110; asc     -  ;;
 3: len 4; hex 5b3ca335; asc [< 5;;
 4: len 4; hex 5b3ca335; asc [< 5;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42062 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000a44b; asc      K;;
 2: len 7; hex b90000012d0110; asc     -  ;;
 3: len 4; hex 5b3ca335; asc [< 5;;
 4: len 4; hex 5b3ca335; asc [< 5;;

*** WE ROLL BACK TRANSACTION (2)

You can see that transaction 1 has a lock_mode X on the PRIMARY key of user_chats, while transaction 2 has lock_mode S, and is waiting for lock_mode X. That is a result of it obtaining a shared lock first (from our INSERT statement), and then an exclusive lock (from our UPDATE).

So, what is happening is Connection 1 grabs the shared lock first, and then Connection 2 grabs a shared lock on the same record. That's fine, for now, as they are both shared locks.

Connection 1 then tries to upgrade to an exclusive lock to perform the UPDATE, only to find that connection 2 has a lock already. Shared and exclusive locks don't mix well, as you can probably deduce by their name. That is why it waits after the UPDATE command on Connection 1.

Then Connection 2 tries to UPDATE, which requires an exclusive lock, and InnoDB goes "whelp, I'm never going to be able to fix this situation on my own", and declares a deadlock. It kills off Connection 2, releases the shared lock that Connection 2 was holding, and allows Connection 1 to complete normally.

Solution(s)

At this point, you are probably ready to stop with the yap yap yap and want a solution. Here are the my suggestions, in order of my personal preference.

1. Avoid the update altogether

Don't bother with the updated_at column in the user_chats table at all. Instead, add a composite index on user_chat_messages for the columns (user_chat_id,created_at).

ALTER TABLE user_chat_messages
ADD INDEX `latest_message_for_user_chat` (`user_chat_id`,`created_at`)

Then, you can obtain the most recent updated time with the following query.

SELECT MAX(created_at) AS created_at FROM user_chat_messages WHERE user_chat_id = 1

This query will execute extremely quickly due to the index, and doesn't require you to store the latest updated_at time in the user_chats table as well. This helps avoid data duplication, which is why it is my preferred solution.

Make sure to dynamically set the id to the $message->getUserChatId() value, and not hard coded to 1, as in my example.

This is essentially what Rick James is suggesting.

2. Lock the tables to serialize requests

SELECT id FROM user_chats WHERE id=1 FOR UPDATE

Add this SELECT ... FOR UPDATE to the start of your transaction, and it will serialize your requests. As before, make sure to dynamically set the id to the $message->getUserChatId() value, and not hard coded to 1, as in my example.

This is what Gerard H. Pille is suggesting.

3. Drop the foreign key

Sometimes, it is just easier to remove the source of the deadlock. Just drop the user_chat_messages_user_chat_id_foreign foreign key, and problem solved.

I don't particularly like this solution in general, as I love data integrity (which the foreign key provides), but sometimes you need to make trade offs.

4. Retry the command after deadlock

This is the recommended solution for deadlocks in general. Just catch the error, and retry the entire request. However, it is easiest to implement if you prepared for it from the start, and updating legacy code may be difficult. Given the fact that there are easier solutions (like 1 and 2 above) is why this is my least recommended solution for your situation.


Assembly 'system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog I am trying to deploy a SQL CLR function using the HTTPUtility.UrlDecode method of System.Web but I can't get it to deploy.  Error received:

  .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1
  Assembly 'system.web, version=4.0.0.0, culture=neutral,
  publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.

The function (as part of SSDT project):

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
    public static SqlString udf_UrlDecode(SqlString encodedXML)
    {
        string decodedXML;

        decodedXML = HttpUtility.UrlDecode(encodedXML.ToString());

        return new SqlString(decodedXML);
    }
}

It's in relation to this thread.  I am SQL Server 2014 with VS2012 SSDT and Database Project.  Have tried with other Target Frameworks, eg 3, 3.5, 4 and 4.5.

I have also tried CREATE ASSEMBLY with System.Web, but then have to add other assemblies, eg Microsoft.Build, System.Xaml until they also fail.  I see System.Web is not on the list of Supported Libraries so any ideas?

You can either use Uri.UnescapeDataString (in System), in which case you will also need to do a Replace('+', ' ') on the string before passing it to Uri.UnescapeDataString, or if you would rather not bother with it, this function is available in the Free version of SQL# (which I am the author of).

Importing System.Web is probably more work than it's worth. And in fact, it can be risky.  There is a good reason that System.Web is not in the "Supported Libraries" list that you linked to in the question: it is not guaranteed to work! You might run into situations, especially when dealing with non-US ASCII character sets, that do not behave as expected, and Microsoft will not fix it. So, unless you absolutely have to, you should be careful about adding non-supported DLLs. The DLLs in the "Supported" list have been fully tested and verified to work with SQL Server collations and any other environmental issues that are different between standard CLR running in Windows and the CLR running inside of SQL Server.

Here are some additional resources from Microsoft regarding several pitfalls to incorporating unsupported .NET Framework libraries:

Support policy for untested .NET Framework assemblies in the SQL Server CLR-hosted environment
Error message when you execute a CLR routine or use an assembly in SQL Server: "Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)"

A few notes about your code:

Please do not use .NET types for parameters, input or output. Hence, change string encodedXML to be SqlString encodedXML.
This function is deterministic so should be marked as such, else you will suffer a performance hit. Add IsDeterministic = true to the SqlFunction attribute.


What is the optimum method of finding the per-item earliest date from a clustered index I have a SQL Server 2012 table which contains columns like this:

ID int NOT NULL,
EventDate datetime NOT NULL,
... 32 other columns...

where the table has about half a billion rows across a range of about 10000 distinct ID values.

The table has a unique clustered index like this:

CREATE UNIQUE CLUSTERED INDEX [MyIndex] ON [dbo].[MyTable] (
    [ID] ASC,
    [EventDate] ASC
)

I need to find the earliest per-ID EventDate, which I can obtain using a query such as:

SELECT ID, min(EventDate) FROM [dbo].[MyTable] GROUP BY ID

However this query takes just under 2 minutes to complete.

I can't share the specifics (query plans etc.) of the problem I'm looking at due to NDA constraints, but I can advise that I'm seeing a clustered index scan, so it's checking all the rows in the table. Given that the data is organised in EventDate sequence I'd expect that the retrieval could be much quicker, but I'm not sure quite how. Any other ID specific range query responds in a few milliseconds and the table has recently been rebuilt and reindexed, so I don't think there are any statistic updates which would help.

Can anyone suggest a better method of determining the minimum per-ID EventDate value which avoids scanning the entire clustered index?

I do have a table with the (10 thousand) distinct id values.

This is related to "Index Skip Scan" optimization (see the Connect item below, from 2011). Unfortunately it has been closed as "Won't Fix".

Some related enhancements are already there but only for partitioned tables: Query Processing Enhancements on Partitioned Tables and Indexes.

Various workarounds exist though:

Workaround / solution 1: CROSS APPLY to subquery with TOP 1

If there is a table with the (10K) distinct ID values, we can use it to do a CROSS APPLY.

-- if you don't have a table already
CREATE TABLE MyTableIDs
  ( ID int NOT NULL,
    PRIMARY KEY (id)
  ) ;

-- we only do this once
INSERT INTO MyTableIDs (ID)
SELECT ID
FROM MyTable
GROUP BY ID ;

Then the query will do (10K) seeks into the index:

SELECT  i.ID, a.EventDate
FROM    MyTableIDs AS i
    CROSS APPLY
        ( SELECT TOP (1) t.EventDate
          FROM     MyTable AS t
          WHERE    t.ID = i.ID
          ORDER BY EventDate
        ) AS a ;

Workaround / solution 2: Implement "Skip Scan" with Recursive CTE

Another option is a recursive CTE to implement the skip scan. Here is a demo script, posted by @PaulWhite on SSC in October 2010, demonstrating how much faster an rCTE can be: Calculating interest query

In your case, it would be something like this:

WITH    RecursiveCTE
AS      (
        SELECT  a.*
        FROM    ( SELECT TOP (1)  ID, EventDate
                  FROM     MyTable
                  ORDER BY ID, EventDATE
                ) AS a
        UNION   ALL
        SELECT  r.ID, r.EventDate
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE ;)
                SELECT  t.ID, t.EventDate,
                        rn = ROW_NUMBER() OVER (ORDER BY t.EventDate)
                FROM    MyTable AS t
                JOIN    RecursiveCTE AS r
                        ON  r.ID < t.ID
                ) AS r
        WHERE   r.rn = 1
        )
SELECT  *
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0) ;

Workaround / solution 3: Partitioned Table

Available if you have Enterprise edition. More details about this possibility in the linked article: Query Processing Enhancements on Partitioned Tables and Indexes

The main disadvantage is that the skip scan will work only if ID is the partitioning column.

Workaround / solution 4: Additional Index

Adding an NCI index on (ID, EventDate) allows an index scan on the much smaller index. See @Daniel Hutmacher's answer for an explanation.

It's still a scan though and not (many) seeks so I'm not sure if it will scale as good as options 1 and 2. Of course everything depends on details (column sizes, number of distinct IDs vs number of repeated values, etc).

Workaround / solution (no, not working) 5: Indexed View

Nice idea, if it worked. Unfortunately a view cannot be indexed if it has GROUP BY and MIN/MAX, see restrictions of Indexed Views.

I wonder why, since it is allowed (and be indexed) if it has GROUP BY and COUNT_BIG(). There may be a Connect item about it, too!

Workaround / solution 6: "Do It Yourself" Indexed View

Implement an equivalent to an indexed view yourself. You could for example, add a MinEventDate column in MyTableIDs (the table with the distinct ID values, see option 1) and add triggers in MyTable that update this column accordingly. Then your query would be a simple SELECT from MyTableIDs.

Links to related web pages

Connect item: Implement Index Skip Scan
Implemented feature: Query Processing Enhancements on Partitioned Tables and Indexes
Indexed Views and restrictions: Create Indexed Views


Poor cardinality estimate disqualifies INSERT from minimal logging? Why is the second INSERT statement ~5x slower than the first?

From the amount of log data generated, I think that the second is not qualifying for minimal logging. However, the documentation in the Data Loading Performance Guide indicates that both inserts should be able to be minimally logged. So if minimal logging is the key performance difference, why is it that the second query does not qualify for minimal logging? What can be done to improve the situation?

Query #1: Inserting 5MM rows using INSERT...WITH (TABLOCK)

Consider the following query, which inserts 5MM rows into a heap. This query executes in 1 second and generates 64MB of transaction log data as reported by sys.dm_tran_database_transactions.

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbers
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

Query #2: Inserting the same data, but SQL underestimates the # of rows

Now consider this very similar query, which operates on exactly the same data but happens to draw from a table (or complex SELECT statement with many joins in my actual production case) where the cardinality estimate is too low. This query executes in 5.5 seconds and generates 461MB of transaction log data.

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that produces 5MM rows but SQL estimates just 1000 rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

Full script

See this Pastebin for a full set of scripts to generate the test data and execute either of these scenarios. Note that you must use a database that is in the SIMPLE recovery model.

Business context

We are semi-frequently moving around millions of rows of data, and it's important to have these operations be as efficient as possible, both in terms of the execution time and the disk I/O load. We had initially been under the impression that creating a heap table and using INSERT...WITH (TABLOCK) was a good way to do this, but have now become less confident given that we observed the situation demonstrated above in an actual production scenario (albeit with more complex queries, not the simplified version here).

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.

Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.


Mongodb: how to create multiple indexes on same field? For example, I created this index:

db.test.createIndex({field1:1}, {partialFilterExpression:{field2:{$gt:10}}})

Index created successfully. Then I want to create second index:

db.test.createIndex({field1:1}, {partialFilterExpression:{field2:{$lte:10}}}

But above will give error  IndexOptionsConflict, but no error details, only dumps the index object.

I have tested to create such indexes on PostgreSQL and it works. How can I do this in MongoDB?

Sadly, a restriction of MongoDB:

Restrictions
In MongoDB, you cannot create multiple versions of an index that
differ only in the options. As such, you cannot create multiple
partial indexes that differ only by the filter expression.

Right now, you can only do this:
db.test.createIndex({field1:1}, {partialFilterExpression:{field2:{$gt:10,$lte:10}}})

Or use composite indexes with a placeholder field:
db.test.createIndex({field1:1, never_use_this_field1: 1}, {partialFilterExpression:{field2:{$gt:10}}})
db.test.createIndex({field1:1, never_use_this_field2: 1}, {partialFilterExpression:{field2:{$lte:10}}}


Will different tables for different Users help in performence I have this Posts table. Somehow Read/Write ratio is 50:50. And we have to write very frequently.

Now suppose I am going to update 1000 post 500 users each, every minute. I think this will overcrowd the post table. So I am thing to have n different table for Posts like Posts_a, Posts_b, Posts_c.. and so on.

And I will have a posts_table field in the users meta data.

Now I want to know if that is a proper solution.

You only need 2 tables:

Users (user_id (PK), username, password, email, created_at)
Posts (post_id (PK), user_id (IX), subject, body, created_at)

Updates to either table would be done using the PK so they will be very fast. A simple JOIN can be used to retrieve posts of a particular user based on username, or a simple SELECT if the user_id is already cached.

Later on, any new features would be another table, referencing data in the aboves. Let's say you wanted to add a feature that tracked 'Post Likes'. Create a table 'post_likes' (post_id (PK), user_id (PK), created_at). Each time a user likes someone else's post, insert to this table.

Or allowed people to 'subscribe' to a particular user. 'user_subscribe' (subscribee_id (PK), subscriber_id (PK)). When user #14 makes a new post,

SELECT
  u.email
FROM user u
LEFT JOIN user_subscribe us ON u.user_id = us.subscribee_id
WHERE
  u.user_id = 14

then email all those people of the new post.

Until your "posts" table reaches the ~500-700GB range, you should be fine with this. After that, we can discuss proper sharding.


Using distinct in comma seperated list Can someone Explain why i get only last row ?

where as when i remove DISTINCT i can able to achieve full rows.

DECLARE @liststr VARCHAR(MAX) = ''
;WITH COMMA (name)
AS
(
        SELECT 'a'
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT DISTINCT @liststr = @liststr+',' + name FROM comma
SELECT @liststr as Result

In the original, unedited question you use UNION instead of UNION ALL in your CTE so the DISTINCT is unnecessary. Consider the following query:

DECLARE @liststr VARCHAR(MAX) = '';
WITH COMMA (name)
AS
(
        SELECT 'a'
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT @liststr = @liststr+',' + name FROM (SELECT DISTINCT name FROM comma) AS comma;
SELECT @liststr as Result;

Here is the query plan for the above:

The sort occurs before the string concatenation, which is what you want. The result is ",a,b,d".

Now consider this query:

DECLARE @liststr VARCHAR(MAX) = '';
WITH COMMA (name)
AS
(
        SELECT 'a'
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT DISTINCT @liststr = @liststr+',' + name FROM comma;
SELECT @liststr as Result;

Here is the query plan for the above:

The sort occurs after the string concatenation, which is not what you want. The result is ",d".

I don't recommend using either query. I don't know what we are seeing here is defined behavior or if you'll consistently get the result that you want from the first query. It's possible that you'll end up with a query that works now but is broken by a future update of SQL Server. If it's important for you to get consistent results for this query you may want to consider a different method of string concatenation.


Storing two integers as a decimal What are the downsides to storing two integers as a decimal?

I am storing asset details in tables, each asset type has its own table (each asset is very different) and using another table to define the asset tables, so each asset table has an integer id and each asset also has an integer id.

I have two different scenarios where this could be handy:

have an "audit" table that stores information like: this user did this to that item
someone is assigned to work on this asset of this type.
I was thinking of storing it like assetType.assetID so asset type 5 and id 99 would be decimal 5.99

I would very rarely need to select based on 5.99, I would just query the record that stores the 5.99, then split it and use a function to go to table 5 record 99.

I can't tie the assetID to a specific table; assetType is the id of an entry in a table referencing the asset tables (defines things like table name, primary key column and the like) so it already seems like I wouldn't be able to use foreign key constraints either way.

There are a lot of asset tables, like asset_tmv and asset_backflow. An asset is assigned a type by what table it is in, as the data to be stored for each asset varies greatly.

I realise I could achieve this using 2 integer fields. What I am wondering is: What would the downsides be?

Note: In order to expose the advantages of handling each discrete data point via its dedicated (logical-level) column, this answer is based exclusively on the following excerpt:

  …someone is assigned to work on this asset of this type. I was thinking of storing it like assetType.assetID so asset type 5 and id 99 would be decimal 5.99…

In order to analyze the scenario in full (every entity type, property and association of relevance), a description of all the informational requirements of the particular business domain you are working on is indispensable; in other words, one has to know the applicable business rules, which at the time of this edit, have not yet been provided.

If you want to supply all the information required, with all the relevant details involved, just let me know and I would be glad to share my take on the scenario as a whole.

You clarified via comments that your intention is building a relational database, so retaining two distinct pieces of information in the same column would imply multiple downsides, e.g.:

The logical level of abstraction of the database under consideration would not be representing the corresponding conceptual schema with the required precision.
Logical-level constraints (value validation) would be much harder (or impossible) to set up declaratively (one has to resort to archaic, sub-optimal, procedural methods to protect data integrity).
Data manipulation at the logical level becomes needlessly cumbersome.
The work that the database management system has to perform at the physical level in order to optimize or even support the logical operations becomes superfluously more complex.
Database usage, in general, becomes unnecessarily more difficult since the database structure and constraints would be puzzling for DBAs, application programmers and, more importantly, end users (in certain environments, there are power users who access databases without application program assistance).

The Asset example

Let us use the scenario about the Asset entity type as an example. So, starting the analysis from the conceptual level, one can say that:

An Asset is primarily distinguished by its Id
An Asset is alternatively distinguished by exactly-one Name
An Asset is classified by exactly-one Type.
A Type classifies zero-one-or-more Assets

Therefore, the informational requirement of that particular business domain entails keeping track of two distinct data points, i.e., Asset.Id and Asset.Type, each of which carries an exact meaning; hence, at this stage it is very clear that each of them:

entails a specific set of constraints; and
has the potential of undergoing manipulation individually.

Subsequently one can represent said conceptual elements by way of the following logical-level SQL-DDL layout:

CREATE TABLE asset_type (
    asset_type_id INT      NOT NULL,
    name          CHAR(30) NOT NULL,
    --
    CONSTRAINT asset_type_PK PRIMARY KEY (asset_type_id),
    CONSTRAINT asset_type_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE asset (
    asset_id      INT      NOT NULL,
    name          CHAR(30) NOT NULL,
    asset_type_id INT      NOT NULL,
    --
    CONSTRAINT asset_PK PRIMARY KEY               (asset_id),
    CONSTRAINT asset_AK UNIQUE                    (name), -- ALTERNATE KEY.
    CONSTRAINT asset_to_asset_type_FK FOREIGN KEY (asset_type_id)
        REFERENCES asset_type (asset_type_id)
);

As demonstrated, each datum would be kept in its dedicated column, and with this arrangement:

One can constrain asset.asset_id declaratively as the PRIMARY KEY of the asset table, which prevents the possibility of accepting duplicated values in such a column. The database management system programmers have worked on optimizing the related physical-level processes carried out “under the hood” for years.
Being declared as a PRIMARY KEY, the asset.asset_id column can now be “used as a target” from one ore more FOREIGN KEY constraints, in case it is necessary.
It is very easy to guarantee referential integrity by way of declaring a FOREIGN KEY constraint on the asset.asset_type_id column, making a reference to asset_type.asset_type_id.
You do not have to resort to functions accessing column value sub-parts (as in the case of the non-atomic combined column) in data manipulation operations, be it INSERTs, SELECTs, UPDATEs, DELETEs or combinations thereof. Functions would (1) increase processing time at the physical level and (2) would reduce code readability.
The (a) logical layout reflects the (b) conceptual schema with precision, which aids all the interested parties when interpreting data structure and result sets. Writing the queries themselves is much simpler because each column stands for a clear and individual piece of information.
At the physical level, having a separate asset.asset_type_id column permits fixing one (or more) single- or multi-column index(es) for this table, which can speed up execution of logical queries that, e.g., include the column as a condition in the WHERE clause.

Thus, in view of all of the above, my main point is: Each data point of interest should be represented at the logical level by its corresponding —properly typed and properly constrained— individual column.

I recommend re-analyzing thoroughly the scenario on your own, starting at (i) the conceptual level and then proceeding with (ii) the corresponding logical design, since that method facilitates obtaining an exact mapping and you will be able to avoid uncalled-for hacks straightforwardly.

Audit trail

You mention in the question that you are interested in enabling some kind of audit trail for the database at issue so, in light of that, I would suggest following an approach like the one I expose in this answer and in this other answer (both about temporal capabilities) with the pertinent adaptations, of course.


SQL Server Replication using Static IP I'm trying to setup transaction replication using a static IP.

I have 2 servers: one has a static IP but other one does not. In this case can I do replication. I am able to connect to the static IP SQL Server from the other server. I also created an alias for the static IP.

I set most of it up, but at the end I got an error on the subscriber:

And if I click on Start then this error appears:

This means this job is already runing. You might want to stop it and start it again, if you really need to re-run it.

Instead:

for a basic quick verification

I check the log reader on the publication, see if the log reader agent is reading the transactions and passing them to the distributor.

second thing

I go to the publication database and find out who is the distributor

sp_helpdistributor

then I connect to the distributor you got above
and run the following query to find out what is going on.

--Bocss Distribution
-- distribution_BOCSS is the name of my distributor database -make sure you change this to your distributor DB
--insert into tempdb.dbo.DistributorCheck
SELECT
(CASE
    WHEN mdh.runstatus =  '1' THEN 'Start - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '4' THEN 'Idle - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '5' THEN 'Retry - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '6' THEN 'Fail - '+cast(mdh.runstatus as varchar)
    ELSE CAST(mdh.runstatus AS VARCHAR)
END) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
right(left(mda.name,LEN(mda.name)-(len(mda.id)+1)), LEN(left(mda.name,LEN(mda.name)-(len(mda.id)+1)))-(10+len(mda.publisher_db)+(case when mda.publisher_db='ALL' then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],
CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
und.UndelivCmdsInDistDB [UndistCom],
mdh.comments [Comments],
'select * from distribution_BOCSS.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
(CASE
    WHEN mda.subscription_type =  '0' THEN 'Push'
    WHEN mda.subscription_type =  '1' THEN 'Pull'
    WHEN mda.subscription_type =  '2' THEN 'Anonymous'
    ELSE CAST(mda.subscription_type AS VARCHAR)
END) [SUB Type],

mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB],
mda.name [Pub - DB - Publication - SUB - AgentID]
FROM distribution_BOCSS.dbo.MSdistribution_agents mda
LEFT JOIN distribution_BOCSS.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN
    (SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
    FROM distribution_BOCSS.dbo.MSrepl_commands t (NOLOCK)
    JOIN distribution_BOCSS.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
    JOIN
        (SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
        FROM distribution_BOCSS.dbo.MSdistribution_history hist (NOLOCK)
        JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
        FROM distribution_BOCSS.dbo.MSdistribution_history (NOLOCK)
        GROUP BY agent_id) AS h
        ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
        GROUP BY hist.agent_id, h.maxseq
        ) AS MaxAgentValue
    ON MaxAgentValue.agent_id = s.agent_id
    GROUP BY s.agent_id, MaxAgentValue.[time]
    ) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.

hope this helps


Where can I find the SQL-89 standard? This question is different but similar to this request looking for SQL-86.

SQL 1989 is almost 30 years old. Is this spec freely available anywhere. The standard does not have a Wikipage or the like?

The spec goes by

ANSI X3.135-1989 Database Language - SQL With Integrity Enhancements
ISO 9075-1989
SQL-1989

The need for it came up while researching the question, Does the SQL-1992 standard restrict naming identifiers to 18 characters?

This one looks like the document you are after (although French, not English, and not free)
https://iteh.it/catalog/standards/iso/7c7fa915-f99f-4ce9-8aa4-0e896d89ed84/iso-iec-9075-1989
Or this one seems to have English and Russian versions:
https://gostperevod.com/st-iso-iec-9075-1989.html


CREATE DATABASE vs. CREATE ANY DATABASE permissions In Microsoft SQL Server, what is the difference between the CREATE DATABASE and CREATE ANY DATABASE permissions?

I am unable to find an authoritative answer. The best I can infer is that either (a) CREATE ANY implies I can create the database to be owned by another user, whereas with CREATE I can not, or (b) the original permission in the Sybase/early SQL Server days was CREATE ANY and CREATE is aliased for ANSI conformance.

I believe I have found the authoritative answer, helped greatly by the table provided in this article: http://msdn.microsoft.com/en-us/library/ms178569.aspx. It would seem CREATE DATABASE is indeed the ANSI SQL compliant permission, while CREATE ANY DATABASE is an MSSQL proprietary server permission. However, the two are not the same. In fact, there is a rather important distinction between the two, and I found this out by trying it.

They are both permissions, but CREATE DATABASE only consults the security context of the currently used database, while CREATE ANY DATABASE can lookup the list of logins on the SQL Server. This appears to be implemented because the general expectation for an ANSI SQL server is that permissions are conferred in some main system database, with users in that database being granted permissions. Then the default behavior of new databases is to consult master to see what permissions it ought to inherit. (SQL 101, I know, but it’s really exemplified here.)

So, when you grant CREATE DATABASE, you must actually have a user in master to grant this permission to. If you try this, it works (create a user in master, grant it create database, then you can make databases). However, by granting create any database (or granting the role dbcreator), you do not need to be a literal user in master.

To further illustrate the difference between the two permissions, observe the differing messages returned by granting a user CREATE DATABASE on master vs. another database, then rinse and repeat for the permission CREATE ANY DATABASE.

use master; GRANT CREATE DATABASE to TestUser

  Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'TestUser',
  because it does not exist or you do not have permission.

This error happens because the user TestUser is not yet in the master database. This is a database-level permission – it has no context to go outside the selected database and look for logins or users. Note that if you create the user in master first, this command succeeds, and you can create databases just as if CREATE ANY DATABASE was granted to you.

use master; GRANT CREATE ANY DATABASE to TestUser

  Command(s) completed successfully.

This succeeds because, as a proprietary Microsoft SQL Server permission, CREATE ANY DATABASE can consult the list of logins for the SQL instance, not just the users in the currently used database.

use test; GRANT CREATE DATABASE to TestUser

  Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'TestUser',
  because it does not exist or you do not have permission.

This demonstrates the consistency of my explanation for the first example. Note the below query and its resultant error message. You will not receive this error message if you first create the user in this database (see the last query).

use test; GRANT CREATE ANY DATABASE to TestUser

  Msg 4621, Level 16, State 10, Line 1 Permissions at the server scope
  can only be granted when the current database is master

As shown in the second query, CREATE ANY DATABASE consults the list of logins, where I had already created this login name. So while SQL Server acknowledged the existence of the user, it still errors out because I am attempting to grant a server-level permission somewhere other than master, which is not allowed in MSSQL.

use test; create user TestUser; grant create database to TestUser

  CREATE DATABASE permission can only be granted in the master database.
  Msg 0, Level 11, State 0, Line 0 A severe error occurred on the
  current command.  The results, if any, should be discarded.

Now that there is a user for CREATE DATABASE to apply to, I am given the generic error message that you cannot give server-level permissions anywhere but master, because that is where SQL Server stores these permissions.

Well, this was fun.


In SQL Server 2008R2 Will currently deployed applications still work if I turn on Mirroring without changing connection strings? I've googled a great deal, and can't come up with an answer to this.

In SQL Server 2008R2 will currently deployed applications (Asp.Net, .Net Smart Client) still work (with a connection string pointing to a single DB server) if I turn on Mirroring without redeploying the apps?

The original apps are deployed and only expecting one server.

I understand the client apps without the "Failover Partner" section of the connection string won't know to use failover partner in the event of a primary failover event, however I don't want to break currently deployed apps. Rolling out new apps that are failover-aware will happen at one point, but would rather know the implications of setting up mirroring first.

Apologies if this should be on ServerFault as it pertains to .Net Application Access. Would ask in the Meta if my deadline allowed me the time.

Yes, it'll be just fine and you'll fail over just fine, provided that the server that was the single server is online.  If that server is totally down then without the connection string change the apps won't know how to find the other instance.

In short yes it'll work fine until you can get the connection strings updated.


Does the order of columns in a covering index matter? I have the following covering indices:

INDEX (col1, col3); -- index 1
INDEX (col1, col2, col3); -- index 2

because I would like to support the following type of queries:

1)

  SELECT col3
    FROM my_table
   WHERE col1 = ... AND
         col2 = ...
ORDER BY col3

2)

  SELECT col3
    FROM my_table
   WHERE col1 = ...
ORDER BY col3

I am unfamiliar with how a covering index works.  Is index 1 redundant? Or does a covering index requires that the columns be side by side?

"Covering" is the wrong term to start with.  I'll get back to that in a minute.

The optimal index for your queries can be summarized:

The first columns in the index must be all the = columns in the WHERE, in -any_ order.
The last columns in the index must be the ORDER BY columns in the same order, and either be all ASC or all DESC.  (MySQL 8.0 has an exception here.)

Your index 1 is necessary and sufficient for query 2.
Your index 2 is necessary and sufficient for query 1.
Anything different would be sub-optimal.

Note that they would also be "covering" in that all the columns anywhere in the SELECT are found in the respective indexes.

Index 2 "covers" both queries, but it is not very good for Query 2.

Devise the optimal index for each important query.
Eliminate redundancy.  Example: Given INDEX(a), INDEX(a,b), toss the former.
Then think about adding some columns on the end to make an index "covering".

For those tips, plus more, see http://mysql.rjweb.org/doc.php/index_cookbook_mysql

This is "covering", but not efficient because col99 in first in the index, but not used for filtering or sorting:

INDEX(col99, col1, col2, col3)

It might be used, but only because it is covering.  It can't be used for filtering or sorting.

This is "covering" and might be used to avoid sorting because the ORDER BY column(s) are first:

INDEX(col3, col2, col1)

Query 1 would be just as happy with this regardless of the cardinality of the individual columns:

INDEX(col2, col1, col3)

Back to the title question:

  Does the order of columns in a covering index matter?

Answer:

For the sake of "covering":  No.
For speeding up the query:  It depends.  And that is why I recommend starting with other 'rules' for building a good index.

An analogy

A table is an unordered set of things.  Analog:  a textbook.
An index is an ordered list, referencing those things.  Analog:  the index in the back of the textbook.
When everything you need to answer a question is sitting in the index (and you don't need to leaf back into the body of the textbook), then the index is "covering".

A B+Tree has two important properties:

It is quick to jump into the middle at any specific word.
It is fast to scan forward from that word.


Books on optimizing and refactoring SQL stored procedures Can you please recommend resources to write efficient store procedures, refactor and optimize them?

EDIT: - jcolebrand ♦

The intent of the answers to this post may be books or non-transient blogs. Please try not to refer to resources that have a tendency to disappear.

This question is being allowed as being intentful for long term purposes, but is not a typical Stack Exchange network question. Please do not use this question as merit why another question should be permissible to be asked. Reference to it may be made as a request for evaluation on a case-by-case basis.

The best two books here are both from Itzik Ben Gan - Inside SQL Server T-SQL Querying and Inside SQL Server T-SQL Programming.

Read the Querying one first. Covers all the important information on how queries are processed, thinking in terms of sets, etc. Covers all aspects of querying. The second book goes into programming constructs when working with T-SQL. Amazing books.


MongoDb: acquireWaitCount and timeAcquiringMicros is missing from system.profile output I enabled profiling in a mongodb database since sometimes a query is fast while sometimes the exact same query takes a very long time, despite the execution plan being a IXSCAN. I suspect it is due to locking issues but in the database profiler output acquireWaitCount, timeAcquiringMicros and deadlockCount are missing. How can I find these values and how can I find the query that holds those locks? I am using mongodb 4.2
Here is the output from a database profiler query:
{
  "op": "query",
  "ns": "db.Caratula-221",
  "command": {
    "find": "Caratula-221",
    "filter": {
      "EmpresaId": "33707830439",
      "Carpeta": "VALOR CRITERIO"
    },
    "limit": 1,
    "readConcern": {
      "level": "linearizable"
    },
    "$db": "db",
    "lsid": {
      "id": "a4c0937f-a77c-40c4-ae4a-f14fc8c67042"
    },
    "$clusterTime": {
      "clusterTime": "Timestamp(1598361984, 1)",
      "signature": {
        "hash": "BinData(0,\"AAAAAAAAAAAAAAAAAAAAAAAAAAA=\")",
        "keyId": 0
      }
    }
  },
  "keysExamined": 1,
  "docsExamined": 1,
  "cursorExhausted": true,
  "numYield": 0,
  "nreturned": 1,
  "queryHash": "D282DB09",
  "planCacheKey": "8921BB9F",
  "locks": {
    "ParallelBatchWriterMode": {
      "acquireCount": {
        "r": 2
      }
    },
    "ReplicationStateTransition": {
      "acquireCount": {
        "w": 3
      }
    },
    "Global": {
      "acquireCount": {
        "r": 2,
        "w": 1
      }
    },
    "Database": {
      "acquireCount": {
        "r": 1,
        "w": 1
      }
    },
    "Collection": {
      "acquireCount": {
        "r": 1
      }
    },
    "Mutex": {
      "acquireCount": {
        "r": 1
      }
    },
    "oplog": {
      "acquireCount": {
        "w": 1
      }
    }
  },
  "flowControl": {
    "acquireCount": 1
  },
  "storage": {},
  "responseLength": 2492,
  "protocol": "op_msg",
  "millis": 8383,
  "planSummary": "IXSCAN { EmpresaId: 1, Carpeta: 1 }",
  "ts": "2020-08-25T13:26:32.526Z",
  "client": "192.168.2.78",
  "allUsers": [
    {
      "user": "dbadmin",
      "db": "admin"
    }
  ],
  "user": "dbadmin@admin"
}

When those fields are not present in the profiling output, it means their value was 0.
That is, if the query never had to wait to acquire a lock, the acquireWaitCount field will not be present in the profiling data.
The profiling data out contains "storage": {} which could mean 2 things:

all necessary documents and indexes were already in cache so the query didn't need to request anything from storage
there was a timeout attempting to retrieve the storage data

In the second case, there should be a log entry to that effect in the mongod log.
Another possibility is too many simultaneous read operations.  The storage engine has an upper limit on the number of concurrent reads it can handle, when that limit is exceeded, operations queue waiting for a turn in the storage engine.  This is not the same as locking, and is not reported in the profiling data.  You can see this occurring in the output of the serverStatus command


Recursive Relationship I have an application, which contains two Models Question and Option

Each Question has many Options
Each Option may have many Questions (and this Question may have many sub Options) so on and so forth.

Please help in how to achieve this relationship in database.
Thank You.

I would handle it like a normal many-to-many relationship, creating a table that holds question_id and option_id. Then, since you can have sub options, i would add another field containing the primary key of this table:

CREATE TABLE [question_option_rel] (
    [id] int IDENTITY (0,1) PRIMARY KEY,
    [id_question] INT FOREIGN KEY REFERENCES [question]([id]),
    [id_option] INT FOREIGN KEY REFERENCES [option]([id]),
    [parent_option] INT FOREIGN KEY REFERENCES [question_option_rel]([id])
)


SQL Server 2008 - Adding a column and a filtered index within a single transaction? This is more of a general question. Why can you not add a filtered index within the same transaction scope as adding the new column in the first place? The solution is of course simple, just make two transactions where you manually cancel the changes in made in the first one, should it return an error.

But all the same, I'm interested in the technical reasoning behind this phenomena, especially considering I've done this before. Have I activated some interesting overzealous checking on SSMS? Example code below, and thanks!

--CREATE TABLE TEST_TABLE (ID INT IDENTITY(1,1) PRIMARY KEY)

BEGIN TRY
    BEGIN TRAN

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;

You can do it within the same transaction it just needs to be pushed down into a child scope so compiled separately.

BEGIN TRY
    BEGIN TRAN

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            EXEC('CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL')
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;

You can also do it within the same scope and in the same transaction if the table does not yet exist.

DROP TABLE TEST_TABLE

GO

BEGIN TRY
    BEGIN TRAN

        CREATE TABLE TEST_TABLE (ID INT IDENTITY(1,1) PRIMARY KEY)

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;

In this second case the reference to the column is subject to deferred compilation as the table does not yet exist when SQL Server compiles the batch. See Deferred Name Resolution and Compilation in BOL

  Deferred name resolution can only be used when you reference
  nonexistent table objects. All other objects must exist at the time
  the stored procedure is created. For example, when you reference an
  existing table in a stored procedure you cannot list nonexistent
  columns for that table.


Concurrent Transactions vs Simultaneous Transactions Chapter 6 of David Kroenke's Database Concepts: Database Administration

Is my understanding correct that in a database, simultaneous transactions don't really happen. Instead, it is a concurrent transaction that is happening fast that users think it is a simultaneous transaction?

The reason I ask is because I was thrown off by the question on my assignment asking how many CPUs are required to perform a simultaneous transaction. I answered only one cpu is required since it is really a concurrent transaction that is happening and not a simultaneous one?

Thoughts, anyone?

EDIT: ~ jcolebrand --

@gbn and @Mark,

I appreciate your input, however, I absolutely have no idea about what those terms are because this course I am currently taking is an introductory course to database management and my question was only discussed in the book in 1 paragraph and did not elaborate any further. I might be able to understand your responses in time, but now, i cannot just yet. Thanks though!

Your assignment question is poor and the expected answer probably incorrect. Feel free to send the lecturer our way to discuss!
From the book:

Explain the difference between concurrent transactions and
simultaneous transactions. How many CPUs are required for simultaneous
transactions?

I guess the expected differentiation is:

Concurrent transactions = multiple transactions active at the same time.
Simultaneous transactions = multiple transactions executing at the same time.

Naff question in my opinion.

Is my understanding correct that in a database, simultaneous
transactions don't really happen

Transactions do of course run concurrently and simultaneously. The question is whether or not simultaneous commits can occur, which is what @gbn is addressing in his answer.
@gbn is correct that two simultaneous commits cannot occur if you consider the transaction to be committed at the point the WAL entry is hardened to disk. From a crash recovery perspective, the transaction is definitely committed at this point. In the event of a failure occurring, this transaction would be rolled forward (redo), not rolled back (undo). As two writes to disk cannot occur at the same time, we could argue that two simultaneous commits cannot occur.
However, you could also argue that a transaction is committed at the point the RDBMS is notified by the IO subsystem that the WAL write has completed successfully AND has released any locks it was holding for the duration of the transaction. Therefore, in a multi-cpu environment simultaneous commits can and will occur.
The mechanics of how this can occur for SQL Server is explained in Performance Tuning Waits and Queues, section titled 'Execution Model (simplified)'. Again for the SQL Server folk, some background reading on the WRITELOG wait type is useful to understand the concepts.


Import data tier application in Managed instance I am using SSMS 18.0 Preview 6 and wanted to import the .bacpac file we have locally stored and accessible. However, seems the function is not supported in the GUI . Any updates/suggestions are welcome.

We will use the sqlpackage.exe, hoever wanted to know if GUI supports this.

Try using SqlPackage instead:

sqlpackage.exe /Action:Import /tsn:MyServer /tdn:worldwide /tu:MyUserName /tp:MySecretPwd /sf:c:\sql\WideWorldImporters-Standard.bacpac

The command will import the bacpac file to Azure. The parameters used are the following:

/Action is used to indicate if we are going to import, export, publish, and extract data.

/tsn is used to define the Target Server Name.

/tdn is the name of the new database to be created.

/tu is the Target User. It is the Server Admin Login.

/tp is the Target Password. It is the password defined.

/sf is the source file.


MySql autoincrement counter resetting after server restart I need a way to keep auto-increment counter between mysql service restart.

As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last autoincrement value?

This is a big problem for me. I'm using envers to keep entities audits. I get as many errors as many "last rows" I delete.

Suppose I'm starting insert data into an empty table. Suppose to insert 10 rows. Then suppose to delete the last 8. In my table I will have as result 2 entities, with id 1 and 2 respectively. In the audit table I will have all 10 entities, with id from 1 to 10: entities with id from 3 to 10 will have 2 action: create action and delete action.

auto-increment counter is now setted to 11 in main table. Restarting mysql service auto-increment counter goes to 3. So if I insert a new entity, it will be saved with id 3. But in audit table there is already an entity with id = 3. That entity is already marked as created and deleted. It result in an assertion failure during update /delete action because envers cannot handle this inconsistent state.

Thanks

MySQL fix

After 14.5 years this is fixed in MySQL version 8.0

  [6 Oct 16:40] Daniel Price Posted by developer: Fixed in 8.0.0 by WL#6204.

WL#6204: InnoDB persistent max value for autoinc columns

MariaDB fix

Also this was MariaDB 10.2.4 released on July 12, 2017

MDEV-6076 Persistent AUTO_INCREMENT for InnoDB.

I found out this change was finally added on the 2ndquadrant blog,

Why PostgreSQL is better than MySQL


How to migrate old Oracle ASM disks We have EMC CLARiiON disks that are near their end of support, so we need to move Oracle ASM disks to a new sets disks.  We are trying to minimize the database down time, so I am trying to find ways to use ASM mirroring or disk clone method to move current databases on ASM old disks to new ones.

If our databases were on the file system, we could use the disk mirror/clone to copy the data files on the file system to new disks.  We could then open the database on new disks with ease.  But Oracle ASM disks are different than file systems.

Any suggestions or references will be very much appreciated.

Why not just create luns on the new storage, with same size as on old storage and add them to the existing disk groups. Next drop the old disks from the disk group and when the re-balance is over, your system is migrated to the new storage, without any downtime.

Works like a charm. ASM is way smarter than a filesystem.


Is there a way to speed up an ALTER statement in SQL Server I have an existing table with millions of rows in it where I have a column which is the SMALLMONEY data type and I want to ALTER it to be MONEY. To make things a bit more complicated it has an index on it which which INCLUDE (s) that column so I need to remove that prior to the ALTER and then add it in again after the ALTER.

I have been running through my test deployment and each of these operations (index removal, table alter and index addition) takes about 4 - 6 minutes each.

I really need to come up with some way to do this faster.

This table is constantly being inserted into and so the shorter the duration the better.

Any pointers would be appreciated.

I am using SQL Server 2008 Enterprise.

You're using Enterprise edition, so you should be able to use online indexing. Add the WITH ONLINE = ON option to your index DDL statement. The operation will still take just as long (possibly longer), but SQL Server will do some magic to keep the table accessible throughout. Here's some documentation with assorted restrictions, but they mostly only come into play if you've got LOB columns in the table:

http://msdn.microsoft.com/en-us/library/ms190981.aspx

This won't help you with altering the column data type, of course. If it's constantly being inserted, but not necessarily updated or read (and you can live with such operations being unavailable during the transition), you can rename the original table, create a new one with the desired structure and permissions, move all the data over, then recreate the indexes on the new table. This may be slightly more complicated if you have an identity column and want to preserve values, but it's still doable.


Is there a way to show a string instead if numeric column has specific value? I have a float column called "weight". It's default is -1, showing the object wasn't weighted. Is it possible to show string "unweighted" instead of any negative number in the field?

As it is internally stored:

 ________________________
| object_id  |  weight   |
|____________|___________|
|     1      |   12.4    |
|     2      |    7.5    |
|     3      |   -1      |
|____________|___________|

And now how the table should appear to the user:

 ________________________
| object_id  |  weight   |
|____________|___________|
|     1      |   12.4    |
|     2      |    7.5    |
|     3      |unweighted |
|____________|___________|

The table with the combined float/text column can be a view; still, I have no idea how to do it. Is it possible, or is doing the translation (negative number -> 'unweighted') on client side my only option?

As you want to mix numeric and string values in the same column you need to convert the numbers to strings as well.

select object_id,
       case
         when weight < 0 then 'unweighted'
         else weight::text
       end as weight
from the_table;

As that column is no longer a number, you can not sort or compare it properly any more. If you want to put that into a view, I suggest you create an additional column for this purpose:

select object_id,
       weight,
       case
         when weight < 0 then 'unweighted'
         else weight::text
       end as display_weight
from the_table;

However it would be cleaner if you don't use "magic numbers" to mark the absence of data. Precisely for this reason the NULL value was created in SQL (although using NULL won't solve the problem of mixing numeric and string values in the same column).


multi-column index has 1 unique column vs single column let assume that I have 2 columns

comment_id | unique
shop_name  | duplicated

which case is batter ?

1- making multi-column index using these 2 columns

2- making single column index using comment_id

which one is batter based on your experience?

I would suggest both,

Making comment_id a PRIMARY KEY: which is both UNIQUE and NOT NULL
If shop_name is frequently a part of the query, making shop_name it's own index.


Calculate Growth rate I'm trying to calculate purchase/profit growth rate in different time periods, This is the query i've come up with (View in SQLFiddle):
Schema:
CREATE TABLE `profit_log` (
  `market_id` smallint(6) NOT NULL,
  `total_purchase` bigint(20) NOT NULL,
  `total_profit` bigint(20) NOT NULL,
  `company_profit` bigint(20) NOT NULL,
  `date` date NOT NULL,
  `tid` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Query:
SELECT a.`market_id`,
    SUM(a.`total_purchase`) as acc_purchase,
    SUM(a.`total_profit`) as acc_profit,
    SUM(a.`company_profit`) as acc_company_profit,
    ((b.`total_purchase` - c.`total_purchase`)/ c.`total_purchase`) * 100 as growth,
    ((d.`total_purchase` - e.`total_purchase`)/ e.`total_purchase`) * 100 as growth_week,
    ((f.`total_purchase` - g.`total_purchase`)/ g.`total_purchase`) * 100 as growth_month,
    ((d.`total_profit` - e.`total_profit`)/ e.`total_profit`) * 100 as growth_week_profit,
    ((f.`total_profit` - g.`total_profit`)/ g.`total_profit`) * 100 as growth_month_profit
FROM `profit_log` as a
JOIN `profit_log` as b on a.`market_id` = b.`market_id`
    AND b.`date` = '2016-05-05'
JOIN `profit_log` as c on a.`market_id` = c.`market_id`
    AND c.`date` = '2016-05-01'
JOIN `profit_log` as d on a.`market_id` = d.`market_id`
    AND d.`date` >= ('2016-05-05' - INTERVAL 7 DAY) AND d.`date` < ('2016-05-05' + INTERVAL 1 DAY)
JOIN `profit_log` as e on a.`market_id` = e.`market_id`
    AND e.`date` >= ('2016-05-05' - INTERVAL 14 DAY) AND e.`date` < ('2016-05-05' - INTERVAL 7 DAY)
JOIN `profit_log` as f on a.`market_id` = f.`market_id`
    AND f.`date` >= ('2016-05-05' - INTERVAL 1 MONTH) and f.`date` < ('2016-05-05' + INTERVAL 1 DAY)
JOIN `profit_log` as g on a.`market_id` = g.`market_id`
    AND g.`date` >= ('2016-05-05' - INTERVAL 2 MONTH) AND g.`date` < ('2016-05-05' - INTERVAL 1 MONTH)
WHERE a.`date` >= '2016-05-01'
AND a.`date` < ('2016-05-05' + INTERVAL 1 DAY)
AND a.`market_id` IN (0,20)
GROUP BY a.`market_id`

The Strange thing is after all this JOINs, SUMs (i.e SUM(a.total_purchase)) return unbelievably large numbers, it seems that values are accumulated over all this joins.
Can you please explain why is that happening (maybe i don't understand JOIN behavior), and help me to rewrite this query the proper way (6 self joins doesn't look right).
Thanks.

Explanation

First all the JOINing is done.  This inflates the number of rows.  Then you do a GROUP BY and SUM.  Hence, the "unbelievably large numbers".

Solution 1

A solution is usually to do the GROUP BY and SUM over just one table.  If that can't be done, then find the ids for that one table, dedup them (via DISTINCT or GROUP BY), then do the grouping and summing.

Give this a try...

Rip out all mention of tables other than a.  See if the grouping and summing works correctly.
Make that a subquery and put it as a 'table' in with the rest of the tables:

SELECT ...
    FROM ( SELECT ... SUM... FROM profit_log WHERE ... GROUP BY ... ) AS a
    JOIN b ... -- as you have the rest

Solution 2

Another approach is to use subqueries for the other stuff...

SELECT a.`market_id`,
    SUM(a.`total_purchase`) as acc_purchase,
    SUM(a.`total_profit`) as acc_profit,
    SUM(a.`company_profit`) as acc_company_profit,
    ( SELECT ((b.`total_purchase` - c.`total_purchase`)/ c.`total_purchase`)
        FROM `profit_log` as b on a.`market_id` = b.`market_id`
    ) * 100 as growth,
    ...
FROM `profit_log` as a
WHERE a.`date` >= '2016-05-01'
  AND a.`date` < ('2016-05-05' + INTERVAL 1 DAY)
  AND a.`market_id` IN (0,20)
GROUP BY a.`market_id`

Need indexes

InnoDB tables really need a PRIMARY KEY.

Also you need this; it should increase performance significantly:  INDEX(market_id, date)


Different tables with identical fields My question is for designing something in MSSQL server.

I work at an engineering company that designs embedded systems. Basically, we have 3 different hardware components in a product: one is for communication on a network (GPIB, Ethernet, Modbus...), one is for measurement/control functions, and the third is for miscellaneous stuff (like diagnostics and logging). Each piece of hardware has multiple firmware versions available. Each firmware has a set of registers used for configuration and a set of defaults.

I want to create a database to track the firmware, hardware, default configs, and register maps. I can create three separate tables for firmware, like commHardware, controlHardware, miscHardware, and then create tables that relate to these (i.e., commFirmware with a foreign key to commHardware and commDefaults with a FK to commFirmware).

Or... I can structure my tables so that there's one table for hardware, one for firmware, one for register maps, one for default configs, and one for hardware role (comm/misc/control).

The representation will be the same no matter which option I choose, so I don't have to shoehorn one design to fit the other. Right now, I'm leaning toward option 1, for two reasons.

If I confuse something, it might not work - if I try to download comm firmware 01 onto a control board, it'll be blatantly screwed up. I don't think it is physically possible to do so - the processors are different.
If I confuse something, it might actually work... for a while. Downloading the miscellaneous registers onto a communications board can be done, but it'll  screw up communications ("Oh, you wanted to suppress overflow alarms? Too bad, here's a new MAC Address.")

In writing this, I've almost convinced myself, but I want to get a second opinion. Is the first approach superior in a way I'm missing? I'm looking for problems that could cause:

Obvious performance issues
Data integrity problems
Maintenance nightmares
Software development headaches
Angry future coworkers visiting my house with hatchets, torches, and/or pitchforks in hand.

I think you want to go with your first option.  Here's why:

You should design your database with a primary goal of maintaining data integrity.  Subtle data corruptions can be harder to debug than source code logic errors.  This is because corrupted data may only cause a detectable problem when a series of conditions occur at runtime.  Often you can't repeat these reliably.

The best way to focus on data integrity is to create a properly structured database.  If your data is going to be edited at any time, then you're best to start with a design that's in Third Normal Form (3NF) at least.  In general, you should have a table for each distinct type of thing that you have data about.

Sometimes, you may have two or more different types of things with similar attributes (data items).  This puts you in the position of deciding where you make your trade off.  Combining the facts about your things into one table means fewer tables.  Fewer tables will mean less code as long as the different types of things are and continue to be similar.  If you need to start moving in the direction of each type of thing having its own distinct set of attributes, then your code will become more complex and hard to maintain, with lots of branching logic.

It's important to remember that two different tables having the same set of attributes might be coincidental to your system or it might be consequential to your system.  Database structure design is not optimized in the same way as code reuse.  Cramming logically distinct but structurally similar data into the same table is not a good practice.  If you have two or more semantically distinct tables with very similar or even identical structure and your programmer's spidey-senses are tingling too much, then satisfy your urge for code reuse by subclassing in your code, but leave your apples and oranges in their own tables, rather than cramming them into a "fruit" table.

In a case where your two (or more) types of things are different from one another, then having one table for each type of thing is probably an excellent place to start.  This will keep your logic simple and your data clean, which makes for a high quality system.


Postgres function return table with extra column Newbie to Postgres here..

I have a Postgres / plpgsql function that will return a table. I want to return everything from a query plus a logical value that I create and return along with it.

However, after searching around I can't seem to figure out how I can return this data along with a logical value that I generate on the fly within the query?

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(email_address character varying)
RETURNS TABLE(credential_id integer, user_id integer, password_hash character varying, password_salt character varying, created_at timestamp without time zone, last_updated_at timestamp without time zone, logical_value_return SMALLINT)
 LANGUAGE plpgsql
AS $function$

DECLARE
sproc_logical_value_return SMALLINT;

BEGIN

-- also want to return logical_value_return along with the query below
-- ex: sproc_logical_value_return = (2)::INT2; how do I add this as a column logical_value_return along with the query below?

  RETURN QUERY
    SELECT
      myapp_users_credentials.credential_id,
      myapp_users_credentials.user_id,
      myapp_users_credentials.password_hash,
      myapp_users_credentials.password_salt,
      myapp_users_credentials.created_at,
      myapp_users_credentials.last_updated_at
    FROM
      myapp_users_credentials
    JOIN myapp_contacts_assoc ON
      myapp_contacts_assoc.user_id = myapp_users_credentials.user_id AND
      myapp_users_credentials.expired_at IS NULL
    JOIN myapp_contacts ON
      myapp_contacts.contact_id = myapp_contacts_assoc.contact_id AND
      myapp_users_credentials.expired_at IS NULL
    WHERE
      myapp_contacts.value = $1 AND
      myapp_contacts.type = 1 AND
      myapp_contacts.is_primary = 1
    LIMIT 1;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Credentials not found';
  END IF;

END
$function$

Just add the variable to the SELECT list:

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(email_address varchar)
  RETURNS TABLE (credential_id integer
               , user_id integer
               , password_hash varchar
               , password_salt varchar
               , created_at timestamp
               , last_updated_at timestamp
               , logical_value_return smallint) AS
$func$
DECLARE
   sproc_logical_value_return SMALLINT := 2;  -- you can assign at declaration time
BEGIN
   RETURN QUERY
      SELECT uc.credential_id,
             uc.user_id,
             uc.password_hash,
             uc.password_salt,
             uc.created_at,
             uc.last_updated_at
             sproc_logical_value_return  -- just put it in the SELECT list
      FROM   myapp_users_credentials uc
      JOIN   myapp_contacts_assoc    ca ON ca.user_id = uc.user_id      AND uc.expired_at IS NULL
      JOIN   myapp_contacts          c  ON c.contact_id = ca.contact_id AND uc.expired_at IS NULL
      WHERE  c.value = $1
      AND    c.type  = 1
      AND    c.is_primary = 1
      LIMIT  1;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Credentials not found';
   END IF;
END
$func$  LANGUAGE plpgsql ROWS 1;

I simplified with table aliases.

Since your function returns a single row by definition, the value is also returned only once.

You might use RETURNS RECORD combined with OUT parameters instead of RETURNS TABLE since you only return exactly 1 row anyway.

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(
                 IN  email_address varchar
               , OUT credential_id integer
               , OUT user_id integer
               , OUT password_hash varchar
               , OUT password_salt varchar
               , OUT created_at timestamp
               , OUT last_updated_at timestamp
               , OUT logical_value_return smallint) AS
$func$
BEGIN

   logical_value_return := 2;  -- assign separately or with SELECT list

   SELECT uc.credential_id, uc.user_id, uc.password_hash, uc.password_salt, uc.created_at, uc.last_updated_at
   INTO      credential_id,    user_id,    password_hash,    password_salt,    created_at,    last_updated_at
   FROM   myapp_users_credentials uc
   JOIN   myapp_contacts_assoc    ca ON ca.user_id = uc.user_id      AND uc.expired_at IS NULL
   JOIN   myapp_contacts          c  ON c.contact_id = ca.contact_id AND uc.expired_at IS NULL
   WHERE  c.value = $1
   AND    c.type = 1
   AND    c.is_primary = 1
   LIMIT  1;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Credentials not found';
   END IF;

END
$func$  LANGUAGE plpgsql;

RETURNS RECORD is redundant noise which can be omitted in this case.

The subtle difference: You could still return the logical constant, even when no row is found. Other columns would be NULL in this case. Details:

Return multiple fields as a record in PostgreSQL with PL/pgSQL
Declaring the tuple structure of a record in PL/pgSQL


MySQL Using Group By, Need to add fields where answer is reliant on another fields data I've asked a the same question before, but didn't get an answer and I believe I made the question too confusing to understand.

BEFORE CODE:

SELECT
inventory.WHSE AS `Warehouse`,
inventory.`CODE` AS `Item No.`,
inventory.INV_DESCRIPTION AS Description,
inventory.ONHAND AS `On Hand`,
pricing.BVRTLPRICE01 AS `SF Warehouse`,
pricing.BVRTLPRICE02 AS `LA Warehouse`,
pricing.BVRTLPRICE03 AS `HP Warehouse`,
pricing.BVRTLPRICE04 AS `FOB China/Indonesia`,
pricing.BVRTLPRICE05 AS ECOMM,
inventory.CUBE,
inventory.WEIGHT,
inventory.DIMENSION,
inventory.PROD AS `Status`
FROM
inventory
INNER JOIN pricing ON inventory.`CODE` = pricing.BVSPECPRICEPARTNO
WHERE
inventory.WHSE IN (00, 10, 20, 50, 70) AND
inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY
inventory.`CODE`,
inventory.WHSE ASC
ORDER BY
`Item No.` ASC,
inventory.WHSE ASC,
inventory.PROD

SQL before and after http://www.glgonline.com/pics/SQL.png

Can someone please help me figure this out? Thanks

Your question is not clear enough, but the following query would provide you with the results you specify as "AFTER :)"

SELECT  inventory.`CODE` AS `Item No.`,
        inventory.INV_DESCRIPTION AS Description,
        SUM(CASE WHEN inventory.WHSE = '00' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (LA)`,
        SUM(CASE WHEN inventory.WHSE = '10' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (SF)`,
        SUM(CASE WHEN inventory.WHSE = '20' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (HP)`,
        SUM(CASE WHEN inventory.WHSE = '50' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (CHINA)`,
        SUM(CASE WHEN inventory.WHSE = '70' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (INDO)`
FROM inventory
WHERE inventory.WHSE IN ('00', '10', '20', '50', '70') AND
      inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY inventory.`CODE`, inventory.INV_DESCRIPTION
ORDER BY 1


Postgres to Postgres DBLink with AWS/RDS I am trying to use dblink functionality for Postgres 9.5.2 DB to Postgres 9.52 on AWS/RDS.

On the server:

CREATE SERVER myremotedb FOREIGN DATA WRAPPER
    dblink_fdw OPTIONS (host 'myremotedb.123456.eu-west-1.rds.amazonaws.com', port '5432', dbname 'my_db');

CREATE USER MAPPING FOR foouser SERVER myremotedb OPTIONS (user 'foouser', password '1234');

GRANT USAGE ON FOREIGN SERVER myremotedb TO foouser;

On the client:

SELECT dblink_connect('myremotedb', 'host=myremotedb.123456.eu-west-1.rds.amazonaws.com , port=5432 , user=foouser , password=1234 , dbname=my_db');

select * from dblink('myremotedb', 'SELECT * FROM my_data') AS T(id integer, name text);

but what I get is:

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

Any idea how can I start a connection?

You need to configure the remote server so that it demands a password when logging on as foouser to mwu.  Right now it is using one of the other authentication methods.


How to execute this procedure in PL/SQL? I have this table in the below format:

Persnbr | Userfieldcd | Value
01      | Port | Funds
01      | Vip1 | Systems
02      | Port | Bank
02      | Vip1 | Authority

This is how I want it:

Persnbr | Port  | Vip1
01      | Funds | Systems
02      | Bank  | Authority

As I dont know the all the fields in the userfieldcd column, I am trying to dynamically pivot the table. So I am using this procedure but I dont know how to call it in PL/SQL developer. I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
sql_query varchar2(1000) := 'select persnbr ';

begin
    for x in (select distinct userfieldcd from persuserfield order by 1)
    loop
        sql_query := sql_query ||
            ' , min(case when userfieldcd = '''||x.userfieldcd||''' then value else null end) as '||x.userfieldcd;

            dbms_output.put_line(sql_query);
    end loop;

    sql_query := sql_query || ' from persuserfield group by persnbr order by persnbr';
    dbms_output.put_line(sql_query);

    open p_cursor for sql_query;
end;
/

When I call the procedure using:

VARIABLE x REFCURSOR
BEGIN
       dynamic_pivot_po(:x)
    END
    /

it gives me

  ORA-00900: Invalid SQL statement.

You are missing a semicolon:

VARIABLE x REFCURSOR
BEGIN
   dynamic_pivot_po(:x); -- here
END; -- and here
/


MySQL Replication Query Imagine I have three servers.
x.x.x.31 - master
x.x.x.32 - slave
x.x.x.33 - slave

If I run a simple update in master. What does the slave servers done. run the update command or copy the updated data from master?

It depends on the replication configuration. The two Replication Formats are Statement-based Replication and Row-Based Replication.

The default is Statement-Based logging.

If the Statement-Based Replication is used, the master writes SQL statements to the binary log and the SQL statements are executed on the slave.

In the case of Row-Based Replcation, the master writes events to the binary log that indicate how individual tables rows are changed. These events are then copied to the slave.

Another format is Mixed-Based Replication. MySQL replication can also be configured to use a mix of both Statement and Row-Based logging. Statement-based log is used by default, but depending on certain statements, the log is automatically changed to row-based.

Refer to binlog_format, to configure the logging format and for binary log setting more information.


Which type of repair level is “DBCC CHECKDB (databasename, repair)”? The MSDN's article about the command "DBCC CHECKDB" explains three ways to perform a database repair in the syntax section:

- REPAIR_ALLOW_DATA_LOSS
- REPAIR_FAST
- REPAIR_REBUILD

But I found the following statement when I was looking for how to repair a suspect (I put in this mode doing these steps) database and I can't understand which one of the three modes is it:

  DBCC CHECKDB (databaseName, repair)

I executed the statement and it works fine. I'm confused because there isn't any reference to the "repair" parameter alone without finish the word with "_allow_data_loss", "_fast" or "_rebuild".

If I try to run "DBCC CHECKDB (databaseName, repair_rebuild)" on my database in emergency status, I receive a error message saying I can't execute that level of repair while the database is in emergency status. So, I discard that "repair" alone is "repair_rebuild".

That statement is the third line in the script that I found:

ALTER DATABASE databaseName SET EMERGENCY;
ALTER DATABASE databaseName SET SINGLE_USER;
DBCC CHECKDB (databaseName, repair) ALL_ERRORMSGS
ALTER DATABASE databaseName SET MULTI_USER;
ALTER DATABASE databaseName SET ONLINE;

If I read the output of the third statement execution I see information about the process but nothing related on the level used in the repair.

Thanks in advance,

Running this on a sufficiently corrupted database results in the following error message:

Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.

This leads me to estimate that REPAIR is simply the short form of REPAIR_ALLOW_DATA_LOSS.  In the very least, I would treat it as the same when it comes to expectations around recoverability of the database in question.  In other words, this should be considered the operation of last resort, only to be tried when all else fails.  Make sure you make a physical copy of the database before running this process.