Multi-expression indexes in PostgreSQL not using being used as expected I have a PostgreSQL 9.3 table with multiple expression indexes defined. When I use explain to see which indexes are being used for my query, I am surprised to see that PostgreSQL is not using the index which most closely-matches the conditions in my WHERE clause. It appears to be a problem specific to expression indexes. Here is a sqlfiddle link which demonstrates the problem:

http://sqlfiddle.com/#!15/580e0/1

CREATE TABLE Table1
    ("a" int, "b" int, "c" int, "d" int)
;
CREATE index a ON Table1 (a);
CREATE index ab ON Table1 (a,b);
CREATE index bplus1 ON Table1 ((b+1));
CREATE index abplus1 ON Table1 ((a+1),(b+1));

The basic multi-column index ab is being used properly:

 explain select * from Table1 where a = 1 AND b = 1

    |                                                       QUERY PLAN |
    |------------------------------------------------------------------|
    | Index Scan using ab on table1  (cost=0.13..8.15 rows=1 width=16) |
    |                                Index Cond: ((a = 1) AND (b = 1)) |

However the similar multi-column expression index abplus1 isn't being used:

explain select * from Table1 where (a+1) = 2 AND (b+1) = 2

|                                                           QUERY PLAN |
|----------------------------------------------------------------------|
| Index Scan using bplus1 on table1  (cost=0.13..8.16 rows=1 width=16) |
|                                            Index Cond: ((b + 1) = 2) |
|                                                Filter: ((a + 1) = 2) |

The strange thing is that the more-specific index does get used if the single-column expression indexes are removed. See this example http://sqlfiddle.com/#!15/f4fda/1:

explain select * from Table1 where (a+1) = 2 AND (b+1) = 2

|                                                            QUERY PLAN |
|-----------------------------------------------------------------------|
| Index Scan using abplus1 on table1  (cost=0.14..8.15 rows=1 width=16) |
|                         Index Cond: (((a + 1) = 2) AND ((b + 1) = 2)) |

It looks to me like there is a conflict between the single-expression and the multi-expression indexes. The surprising thing is that there doesn't appear to be any such conflict when using basic (non-expression) indexes.

Is there anything that I can do to work around this problem? I would like to keep both of my expression indexes, as each one is well-suited for particular queries.

It looks to me like there is a conflict between the single-expression and the multi-expression indexes.

All applicable indexes are candidates, if that's what you mean by "conflict". But that's the extent of it. Postgres estimates execution time based on table and index statistics in combination with cost settings, and the best estimate wins.

If Postgres expects one of your two conditions to be selective enough, it will likely take a matching single-column index and filter possible (few!) false positives in a FILTER step with the second condition.

However, that is certainly not the case for the data distribution you mention in the comment:

  20,000 rows. b=1 for 10,000, a=1 for 2. One row for a=1,b=1

@ypercube already demonstrated in his sqlfiddle that your test should use the multicolumn index. Even more so with the data distribution you describe.

If you provide the output of EXPLAIN (ANALYZE, BUFFERS) instead of just EXPLAIN, we might be able to see more. I would guess you didn't run ANALYZE after creating the expression index, which is required to update statistics.  autovacuum will kick in after some time, but not fast enough if you run your test immediately after creating the index (and never for temporary tables!).

ANALYZE Table1;

Come to think of it, we (that is: you & me) solved this problem before:

PostgreSQL partial index unused when created on a table with existing data

Also related:

Index that is not used, yet influences query

That aside, Postgres 9.3 is getting old. There have been 3 major release since. If you run into problems like this I suggest to test a current version and see if the problem is still there.


Where to place tempdb and logfiles I have fallowing configuration for my SQL 2014 server:

SSD RAID 1 - for data files
SSD RAID 1 - for tempdb? logfiles?
HDD (SAS 15K) RAID 1 - for tempdb? logfiles?

I noticed that sql uses tempdb quite heavly and I wonder how shall I place the logfiles and tempdb to optimize the throughput (the application is not very write intensive). Does it make sense to put logfiles and tempdb on the same SSD array or I should better use the HDD array for logfiles?

RAID 1 is less suitable for any of Data file, log file and tempdb file. Although RAID 1 provides redundancy by mirroring and also provides bit of a fault tolerance it is kind of outdated with arrival of RAID 5 and RAID10(or RAID1+0). Best is to keep data, log and tempdb on differnt physical drives which are configured either in RAID 5 or RAID10. RAID 10 is more preffered but is costly as compared to other RAID technologies.

If you have restriction with RAID 1 you can use it but then make sure you separate data,log and tempdb files on different physical drive to avoid I/O contention.

Above suggesstion were given because you asked about seggregating files. In many environment because I/O load is relatively less data and log files are kept on same drive, even tempdb is placed on same drive where other files are present. According to surrounding/env/load  we can seggregate files to get optimum performance.


Finding same values and updating rankings thank you for taking the time to look at this.  I have a simple table that I am trying to update based on the entries.

table name is TOPUSER and there us a field called tu_total and tu_rank, I would like to look through this data and see if there are tu_total that are the same and if so those records get the same tu_rank and the tu_rank.  the ranking starts out at 1 and goes up from there.  Also if a users have a 0 value in tu_total then they all would get the next number above the last user's tu_rank who actually has data in their tu_total.

Here is a sample of what it currently looks like:

So as you can see Mark and Bill have the same values of tu_total = 3

So it should look like this:

This will display the rank you requested

SET @prev = 0;
SET @rank = 0;
SELECT name,tu_total total,rnk rank
FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
FROM (SELECT name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AA) AA;

If TOPUSER has an id field, here is the update of the rank

SET @prev = 0;
SET @rank = 0;
UPDATE
(SELECT id,name,tu_total total,rnk rank
FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
FROM (SELECT id,name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AAA) AA) A
INNER JOIN TOPUSER B USING (id)
SET B.tu_rank = A.rank;

SAMPLE DATA

mysql> DROP DATABASE IF EXISTS nate;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE nate;
Query OK, 1 row affected (0.00 sec)

mysql> USE nate
Database changed
mysql> CREATE TABLE TOPUSER
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     tu_total INT NOT NULL,
    ->     tu_rank INT DEFAULT 1000,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO topuser (name,tu_total) VALUES
    -> ('Mark',3),('Bill',3),('Randy',1),('Sammy',0),('Max',0),('Jane',0);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TOPUSER;
+----+-------+----------+---------+
| id | name  | tu_total | tu_rank |
+----+-------+----------+---------+
|  1 | Mark  |        3 |    1000 |
|  2 | Bill  |        3 |    1000 |
|  3 | Randy |        1 |    1000 |
|  4 | Sammy |        0 |    1000 |
|  5 | Max   |        0 |    1000 |
|  6 | Jane  |        0 |    1000 |
+----+-------+----------+---------+
6 rows in set (0.00 sec)

mysql>

UPDATE EXECUTED

mysql> SET @prev = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @rank = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE
    -> (SELECT id,name,tu_total total,rnk rank
    -> FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
    -> FROM (SELECT id,name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AAA) AA) A
    -> INNER JOIN TOPUSER B USING (id)
    -> SET B.tu_rank = A.rank;
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> SELECT * FROM TOPUSER;
+----+-------+----------+---------+
| id | name  | tu_total | tu_rank |
+----+-------+----------+---------+
|  1 | Mark  |        3 |       1 |
|  2 | Bill  |        3 |       1 |
|  3 | Randy |        1 |       2 |
|  4 | Sammy |        0 |       3 |
|  5 | Max   |        0 |       3 |
|  6 | Jane  |        0 |       3 |
+----+-------+----------+---------+
6 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Logging and saving user activity Database:Oracle 11gR2
OS: Windows 2008 Server R2

I'm not trained DBA, just have to run things on an Oracle database temporarily, which is used to perform OLAP processes.

I need to automatize user activity logging (about 30 users) and saving this data (as a text file) if possible.

What I need to log:

SQL_TEXT,
PARSING_SCHEMA_NAME,
FIRST_LOAD_TIME,
DISK_READS,
ROWS_PROCESSED,
ELAPSED_TIME,
SERVICE,
MODULE,
IP_ADDRESS

It would be great to automatically save this logs on an hourly basis.

I've made a research about RMAN, but it seems complicated for me at this stage.

Thanks.

Oracle has pretty extensive auditing capabilities. This page is part of the Oracle 2 Day + security guide. It will run you through some of the capabilities, plus has a tutorial on setting up an audit trail.

Each hour you could query DBA_AUDIT_TRAIL and write the results out to a text file.


How to assign MongoDB the complete RAM of system? I am trying to run the MongoDB with the full capacity of my system RAM. But I am not getting a way to assign complete RAM of my system. PLease advise me what I can do in the cnfiguration, so that the RAM will take the complete RAM of my system.

The RAM capacity is 32 GB.
After system reserve 28 GB.
I want to assign the MongoDB with atleast 25 GB of RAM from my system.
Kindly, advise me.

You don't need to do anything. MongoD -process will use all available memory automatically. You may not "notice" it, because mongod -process don't eat all your memory, but in reality, all free memory is used to cache data files (data itself and indexes; weight is on indexes). That's why, available memory should be bigger than active data and indexes are.

If you use wiredTiger, data and indexes stays compressed in the memory too and not only on the data-store.


Unable to use mysql_upgrade: I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says-

mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`'`  FOR UPGRADE' at line 1 when executing 'CHECK TABLE ...  FOR UPGRADE'

I searched this problem and found that this problem is due to use of delimeter in table_name and If I alter the name of table then this can be solved.
But I don't know how to search those table which are not ready to upgrade ?

Given that the valid characters for unquoted identifier characters are documented here, you should be able to find non-confirming table names in information_schema using a regular expression match, with this query:

SELECT *
  FROM information_schema.tables
 WHERE table_name NOT RLIKE '^[0-9a-zA-Z$_]+$';


How to compare across rows in large table? I need some help with a complicated query. Here are the dataset structures I'm dealing with. This is a MySQL database.

*transactions_table*

zip  | date
-----------------
1234 | 2010-05-01
4567 | 2010-03-10
8901 | 2010-02-25

*stores_table*

brand_id | zip
---------------
1        | 1234
2        | 4567
3        | 8901
4        | 2222
5        | 6666

*manager_table*

brand_id | name
---------------
1        | jane smith
1        | joe smith
2        | ellen katz
3        | foo fuzz
4        | bar bazz

My goal is to get all the transactions in stores that are managed by managers whose names do not match for a regex on the final word of the name column. The number of managers per store can be 1 or 2; it's inconsistent.

I can join all the data together, but then I can't figure out how to perform that last filter step. Here's what I have so far:

SELECT
manager.name, store.zip, txn.date
FROM
transactions_table txn
JOIN
stores_table store
  ON
  txn.zip = store.zip
JOIN
manager_table manager
  ON
  store.brand_id = manager.brand_id

Which gives me a derived table that looks like:

name       | zip  | date
------------------------------
jane smith | 1234 | 2010-05-01
joe smith  | 1234 | 2010-05-01
ellen katz | 4567 | 2010-03-10
foo fuzz   | 8901 | 2010-02-25

But I want to exclude the first two rows, because they represent a transaction at a store managed by people with names that match on their last name.

I want the dataset to look like:

name       | zip  | date
------------------------------
ellen katz | 4567 | 2010-03-10
foo fuzz   | 8901 | 2010-02-25

A couple notes:

I realize the domain this represents seems a little weird, but it's a simplified example for the sake of trying to nail down this query
scale is an issue. the real manager_table is about 38MM rows and the real stores_table is about 18MM, so I'd like to avoid a join on a derived table is possible

You could get the last word by first using reverse() to reverse it. So the last space becomes the first, so that the right one is taken even if there is more than one space in the name. Find the position of that space with locate(). With a substring() from the first character to the found position of the space minus one you'll get the last word reversed. Reverse this and have your last word.

reverse(substring(reverse(manager.name), 1, locate(' ', reverse(manager.name)) - 1))

For comparison we it actually doesn't matter if the word is reversed or not and if the space is still included. So we can simplify it to:

substring(reverse(manager.name), 1, locate(' ', reverse(manager.name)))

But we have to take care that at least one space is included as otherwise we'd do a substring(..., 1, 0) (locate() returns 0 if no match was found) which will result in the empty string. So two names without a space would match even if they were different, as the empty string is equal to the empty string. So we additionally need to check if:

locate(' ', manager.name) > 0

The order of the characters doesn't matter here, so we don't need to use reverse().

So you could use something like:

SELECT manager.name,
       store.zip,
       txn.date
       FROM transactions_table txn
            INNER JOIN stores_table store
                       ON txno.zip = store.zip
            INNER JOIN (SELECT *
                               FROM manager_table managero
                               WHERE NOT EXISTS (SELECT *
                                                        FROM manager_table manageri
                                                        WHERE AND locate(' ', managero.name) > 0
                                                              AND locate(' ', manageri.name) > 0
                                                              AND manageri.name <> managero.name
                                                              AND manageri.brand_id = managero.brand_id
                                                              AND substring(reverse(manageri.name), 1, locate(' ', reverse(manageri.name)) - 1) = substring(reverse(managero.name), 1, locate(' ', reverse(managero.name)) - 1)) manager
                       ON storeo.brand_id = managero.brand_id;

It will only join the set of managers, where no other manager with the same last word in name and the same brand_id exists. However note, that this fails to exclude managers without a space in their names, even if the names are equal. That is due to the fact, that you didn't reveal if there is any id column in manager_table. Only name seems to be unique. So there is no way to tell if the inner row is the same as the outer row without checking for the whole name. If there is and id column (e.g. named id) you can rewrite it to:

SELECT manager.name,
       store.zip,
       txn.date
       FROM transactions_table txn
            INNER JOIN stores_table store
                       ON txno.zip = store.zip
            INNER JOIN (SELECT *
                               FROM manager_table managero
                               WHERE NOT EXISTS (SELECT *
                                                        FROM manager_table manageri
                                                        WHERE manageri.id <> managero.id
                                                              AND (locate(' ', managero.name) > 0
                                                                   AND locate(' ', manageri.name) > 0
                                                                   AND manageri.brand_id = managero.brand_id
                                                                   AND substring(reverse(manageri.name), 1, locate(' ', reverse(manageri.name)) - 1) = substring(reverse(managero.name), 1, locate(' ', reverse(managero.name)) - 1)) manager
                                                                    OR manageri.name = managero.name)
                       ON storeo.brand_id = managero.brand_id;

That will also find duplicate managers with the same name, without a space in it.

Note: I assumed you're not already using MySQL 8.0. Version 8.0 introduced regexp_replace and regexp_substring providing possible easier alternatives to the string reversing.


I can't find time zone tables in sys database of MySQL For my Django application, I had to set the time zone to UTC, however I am not able to set it as I couldn't find any tables related to time zone in the system database.

How do I get those? the scripts available in here https://dev.mysql.com/downloads/timezones.html only relates to loading of data in the tables and not creating them.

MySQL Version: 5.7
OS: Windows

I used to face the same issue as you and I resolved it by following these quick steps.

Please follow below:

Environment:

MySQL Version: 5.7
OS: Windows 10 (64 bit)

Steps:

Download the package that contains the data files of pre-built time zone tables. You can download here https://dev.mysql.com/downloads/timezones.html

timezone_2018e_posix_sql.zip - POSIX standard for MySQL version 5.7

Extract the downloaded package, you will then have a file named timezone_posix.sql
Open a command line in the root directory of MySQL. Ex: On my
computer this is "C:\Program Files\MySQL\MySQL Server 5.7\bin"
Run the command mysql -u root -p to login to MySQL with root access and then enter password.
Run the command show databases; to view all database. You will see a database named mysql. You need to insert data into the mysql database

Run the command use mysql;

Finally, you can insert data by issuing the command source *path timezone_posix.sql*.

After inserting the timezone data, you can test by issuing the following query:

 SELECT CONVERT_TZ('2018-07-02 14:46:46','utc','America/Los_Angeles')

If the result is not null, you're successful!!!


bcp database migration I want one server to migrate the database to another server. On another server database already exists, then ist all the tables, but they are empty and I want to fill. For the filling, I want to use bcp - database migration. In command line, I wrote the following:

bcp [dbo].[aspnet_Profile] in "c:\SQLAzureMW\BCPData\dbo.aspnet_Profile.dat" -E -n -b 10000 -a 16384 -S servername.database.windows.net -U username@servername -P "password" -d databasename!

The name of the user and the server name passvord written correctly.

When I run I get this error:

error = Packet size change not supported by server, default used.

Then I fixed packet size to the size of a default. I removed the "-a".

bcp [dbo].[aspnet_Profile] in "c:\SQLAzureMW\BCPData\dbo.aspnet_Profile.dat" -E -n -b 10000 -S servername.database.windows.net -U username@servername -P "password" -d databasename!

Then I got the following error:

Who can tell what I should do, or is there a good way to move the database?
I also used the SQL Azure Migration Wizard,but it don't help me.

I found the reason that did not work, and really was not password correct because I used the password to these characters who divided password to pieces, and of that reason but it was not correct. I used to password ",", "$" and other characters.


How can I differentiate between a Multivalued Attribute and an Entity? While designing ER Diagrams, how can we differentiate between a Multivalued Attribute and an Entity?

How can we know that, what we are considering as an 'Entity', isn't actually an entity but a Multivalued attribute or vice-versa?

Ask yourself if the thing under consideration continues to have an existence if all the other things in your model go away. If so it's an entity, if not it's an attribute.  Does the thing you are considering have a way to distinguish it from all other items in its class i.e. a primary key? Then it's an entity.

The answer you come to may depend on the context in which you are working. For one computer application a particular value may be treated as an attribute. In another it may be an entity. This is not a contradiction; it is a natural outcome of simplifying the real world into a model and in doing so selecting the important parts and excluding the others.

If you intend to implement your model you should recall that first normal form requires that each attribute hold only one value.


MySQL "Limit" clause grabbing more results than requested I'm trying to add pagination to my points system (100 results per page) and came up with this :

SELECT A.username, A.points
FROM users A
INNER JOIN (
   SELECT points
   FROM users
   WHERE points <> 0
   ORDER BY points DESC
   LIMIT $start, $limit
)B
USING (points)

The username and points columns have indexes.

When running this query (LIMIT 100, 100), it returns this (in phpMyadmin) :

Showing rows 0 - 29 ( 124 total, Query took 0.0001 sec)

I'm asking for 100 rows, but it's returning 124. I've never seen this before.

Here is what EXPLAIN shows :

+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref      | rows | Extra                    |
+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL   | NULL    | NULL     |  100 |                          |
|  1 | PRIMARY     | A          | ref   | points        | points | 2       | B.points |    2 |                          |
|  2 | DERIVED     | users      | index | points        | points | 2       | NULL     |  200 | Using where; Using index |
+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+

EXPLAIN shows 100 rows as well, but when outputing the array, it's still returning 124 results.

Any ideas how I can fix this?

This is happening because the LIMIT clause is in a subquery.

To fix it, move it to the end:

SELECT A.username, A.points
FROM users A
INNER JOIN (
   SELECT points
   FROM users
   WHERE points <> 0
   ORDER BY points DESC
)B
USING (points)
LIMIT $start, $limit

I suspect you'll also want to move the ORDER BY.


How to remove SQL Server Error Logs I have six error logs plus the current.  They are taking up 10G of disk space on my hard drive on my VPS server.  Thats about 25% of the server space.  The largest file is 6G and growing.

How can I remove the error logs as I don't need them and reduce the size in which the archive files grow to before recycling.

Thanks,

Thats about 25% of the server space.

First and foremost, you really need to increase the size of your drive or move the error logs to a different drive. ~40GB for a C:\ drive might've been an okay default years ago, but you can quickly burn through that with misc files from applications - as you're seeing.

You can easily configure this via SQL Server Configuration Manager:

by modifying the directory for the -e parameter, which controls the error log destination.

  How can I remove the archive logs as I don't need them and reduce the
  size in which the archive files grow to before recycling.

You can of course just delete the files manually, but it sounds like you really want to reduce the number of logs used and/or reduce their size.

The steps contained in the above link detail how to do this in SQL Server Management Studio:

  In Object Explorer, expand the instance of SQL Server, expand
  Management, right-click SQL Server Logs, and then click Configure.
  In the Configure SQL Server Error Logs dialog box, choose from the
  following options.

  a. Log files count

  Limit the number of the error log files before they are recycled

  Check to limit the number of error logs created before they are
  recycled. A new error log is created each time an instance of SQL
  Server is started. SQL Server retains backups of the previous six
  logs, unless you check this option, and specify a different maximum
  number of error log files below.

  Maximum number of error log files

  Specify the maximum number of archived error log files created before
  they are recycled. The default is 6, not including the current one.
  This value determines the number of previous backup logs that SQL
  Server retains before recycling them.

  b. Log file size

  Maximum size for error log file in KB

  You can set the size amount of each file in KB. If you leave it at 0
  the log size is unlimited.


SQL Server - Cursors - Missing Afterthought - Safeguard against infinite looping in case of missing FETCH NEXT It was found that this code mistake caused an infinite loop on PROD:

DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
    SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    ...
    IF [condition]
    BEGIN
        ...
        FETCH NEXT FROM MyCursor INTO @BatchID
    END
END
CLOSE MyCursor
DEALLOCATE MyCursor

Assuming the need for a cursor is warranted, is there any way to safeguard against this mistake (besides more testing/code review)?

In other languages, we have FOREACH loops that manage progressing the iteration for us, or FOR loops that have an afterthought, so is there any equivalent in SQL Server that prevents sloppy mistakes like misplacing (or forgetting!) it?

I have never seen custom loops in SQL need to do anything fancy with the cursor, and WHILE loops have the same risk (along with cases like DELETE #WorkData WHERE ID = @BatchID when @BatchID is NULL), so how might this risk be mitigated programmatically/cleanly for the typical use case?

An approach like this is very unappealing:

DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
    SELECT BatchID = -1--dummy entry to always be skipped
    UNION ALL SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM MyCursor INTO @BatchID
    IF @@FETCH_STATUS = 0
    BEGIN
        ...
    END
END
CLOSE MyCursor
DEALLOCATE MyCursor

It seems to me that checking @@FETCH_STATUS twice in a row without any cursor position/control statements in-between could be an effective way for SQL Server to guess if there was such a mistake, as my experience with cursors has never seen two checks in a row intentionally (without nested cursors at least, but those still have control statements like OPEN and CLOSE between checks of @@FETCH_STATUS of the outer cursor).

P.S. The [condition] this time was equivalent to "not the DST transition day" (which was this Sunday). More time zone bugs!

To avoid problems like this I'm using different patter for fetch:

OPEN Cursor;

WHILE 1=1
BEGIN

    FETCH NEXT FROM Cursor INTO [...];
    IF @@FETCH_STATUS <> 0 BREAK;

    [...]

END;

CLOSE Cursor;
DEALLOCATE Cursor;

In this pattern you have FETCH exactly once and then you need to check fetch status once too.


Cassandra with Hadoop Cassandra uses Cassandra File System (CFS) and Hadoop uses Hadoop Distributed File System (HDFS)

I'm already using Pig over HDFS and i would like to install Cassandra.

Having additional servers for Cassandra is might inefficient to the fact that i already have Hadoop setup.

Can Cassandra use HDFS instead of CFS?

The Hadoop Support section of the Cassandra Wiki says:

  Cassandra's Hadoop support implements the same interface as HDFS to
  achieve input data locality (see cluster configuration for details on
  data locality and how to split your analytic and realtime read loads).

So the answer appears to be, "yes".


Oracle database not open and ORA-00600 error Whenever I try to open Oracle, I get following error :
    ORA-01109: database not open

So as suggested by various blog and forum, I tried the following series of commands

c:> sqlplus /nolog
sql> connect sys/manager as sysdba
sql> shutdown immediate
sql> startup

(and startup mount, startup force etc)

I get
    ORA-01589: must use RESETLOGS or NORESETLOGS options for database open

If I try :

SQL> alter database open noresetlogs;

I get error ORA-00600: internal error code error

And if I try resetlogs after recovery, I get the following option :

ORA-01152: file 1 was not restored from a sufficiently old backup

I do not have any important data on the database, I was just starting up for a dummy project. So I don't care much about data, my target is to just work with ORACLE with some new tables.

Any help would be much appreciated.

It looks like your database experienced an incomplete recovery. This is why it complains about open resetlogs. After an incomplete recovery you need resetlogs. To be able to get this database up and running again, you need to know what caused the current situation and some skills to get it out of there.

If the data is not important at all, just trash the database and create a new one. The actions needed for this depend a little on the platform where you are on.

the documentation looks like a good starting point.


Transaction Log autogrowth In my recent studies about Auto Growth, I took a look at one of our DB's at work and see the Transaction log has many Auto Growth events. My understanding is that the Auto Growth event is a blocking event and should be avoided. So my goal is to stop this recurring Auto Growth. Is this happening because the reserved Log space is too small and the transactions fill it up immediately triggering the Auto Growth? Also, In this DB, they are taking TLog backups every hour on the hour.
Here is a pic from the Disk Usage Report.
Thanks!

I think you'll find a good starting point here - http://blog.sqlxdetails.com/set-transaction-log-initial-size/

To summarize from the link:

Set your transaction log large enough so it will not grow during normal operation.  All update activity stops in the database when the logs grows, so you want to minimize log growths.
For in initial log size, you could roughly estimate the log size needed to rebuild your largest table in db (roughly, that is the size of that table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.
Log file growth should be changed from a percentage (as is the default) to a fixed MB size and monitored.
The link references the usage of BULK RECOVERY model to limit the size of the transaction log due to 'minimally logged' transactions like BULK INSERT and INDEX REBUILDS.  You can only switch to BULK RECOVERY from FULL RECOVERY, but you should be aware that you cannot restore to a point of time that encompasses a 'minimally logged' transaction because individual log (insert/update/delete) records are not generated during that process.  While the actual log file will not be large for a 'minimally logged' transaction, the log backup will still be large due to backing up the changed data extents that were affected during the 'minimally logged' transaction.
In FULL RECOVERY, the transaction log will continue to grow until a log backup occurs (assuming there aren't any open transactions left)
In SIMPLE RECOVERY, the transaction log is 'basically' available for reuse after a transaction commits work.
Generally speaking, shrinking the log is discouraged.  At some point, there was activity that caused it to grow to that size (index rebuild, etc.).  There may be situations where an out-of-the-normal issue occurred and caused the transaction to grow large than normally needed and in those cases you may need to shrink the log down to a more realistic size


Time taken by VACUUM FULL to reclaim space After receiving some insightful guidance in a previous post here, I'm going to run VACUUM FULL on 4 PostgreSQL 9.3.10 tables. Table sizes are:

1) links_publicreply: ~30M rows, 9 columns, 3 indexes (types: int, timestamp, char, bool)

2) links_reply: ~25M rows, 8 columns, 6 indexes (types: int, text, timestamp, char)

3) links_link: ~8M rows, 14 columns, 3 indexes (types: int, text, dbl precision, timestamp, char bool)

4) links_user_sessions: ~2M rows, 7 columns, 4 indexes (types: int, text, timestamp, inet)

This is my first attempt at reclaiming disk space. It's a busy server of a local social networking website. No time is actually "downtime". But the least busy is ~4:00 AM, so that the window I'll use.

Speaking from experience, can you guys form any opinion on how long VACUUM FULL would take for the 4 tables I pointed out? I'd like to put up a "under maintenance till xx:xx:xx" message on the website while it's happening. I know no one can be sure, but is this deterministic enough for you to form a ballpark opinion?

Secondly, just so that we're on the same page, the commands I'd be running on psql are simply VACUUM (FULL, VERBOSE, ANALYZE) link_publicreply; (and so on), correct? Don't want to screw it up.

It's a busy server of a local social networking website. No time is actually "downtime".

So VACUUM FULL is going to be a problem since it takes an exclusive lock on each table it processes. Consider the community tool pg_repack instead which achieves the same without exclusive locks.
Related:

VACUUM returning disk space to operating system

None of this affects the size of backups, since those do not include dead rows to begin with.


ERROR: operator does not exist: text[] ~~ text We have an easy syntax that allows us to look into an array for a single scalar,

SELECT 'foo' = ANY(ARRAY['foo', 'bar', 'baz']);

We can use the same method to match with LIKE

SELECT 'foobar' LIKE ANY(ARRAY['foo%', 'bar%', 'baz%'];

My question is what if you want to do it the other.

SELECT ANY(ARRAY['foobar', 'barbar', 'bazbar']) LIKE 'foo%'
ERROR:  syntax error at or near "ANY"
LINE 1: SELECT ANY(ARRAY['foobar', 'barbar', 'bazbar']) LIKE 'foo%';

I know that syntax doesn't work, but I have expected this to work.

# SELECT ARRAY['foobar', 'barbar', 'bazbar'] LIKE 'foo%';
ERROR:  operator does not exist: text[] ~~ unknown
LINE 1: SELECT ARRAY['foobar', 'barbar', 'bazbar'] LIKE 'foo%';
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I want to filter an array to see if an element exists. Is this possible without using unnest?

The Postgres manual suggests you might have a design issue:

  Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

You can make your operator more efficient — it's better if it stops checking after the first match:

CREATE OR REPLACE FUNCTION like_in_array( arr text[], pattern text )
RETURNS bool AS $$
  SELECT coalesce(( SELECT true
                    FROM unnest(arr) AS u(n)
                    WHERE n LIKE pattern
                    LIMIT 1),false);
$$ LANGUAGE sql
IMMUTABLE;

dbfiddle here


What's the difference between functions and stored procedures? I heard that the difference between functions and stored procedures in SQL Server is that functions are not stored in the database while stored procedures are.

Is that true?  If not, what are the differences between them?

In addition, I know that stored procedures are only complied once and then executed directly. What about functions?

A function attempts to return something, always, and has several restrictions - for example, you can not have any side effects, so you can't issue DML, call stored procedures, use dynamic SQL, call NEWID(), etc. You also cannot have error handling, transactions, or non-deterministic functions (e.g. GETDATE() in older versions, at least in SQL Server 2000 - though there are workarounds for this) defined inside a function.

There are several types of functions, mainly scalar and table-valued functions. Scalar functions can be called inline, e.g.

CREATE FUNCTION dbo.PrettyDate(@d DATETIME)
RETURNS CHAR(10)
AS
BEGIN
    RETURN(SELECT CONVERT(CHAR(10), @d, 120));
END
GO

DECLARE @Date DATETIME = CURRENT_TIMESTAMP;
SELECT dbo.PrettyDate(@Date);

Results:

----------
2012-03-14

Table-valued functions need to be called a little differently, since they act essentially like a parameterized view. For example:

CREATE FUNCTION dbo.DatesInRange
(
  @StartDate DATETIME,
  @EndDate   DATETIME
)
RETURNS TABLE
AS
  RETURN (SELECT [date] = DATEADD(DAY, n-1, @StartDate)
    FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.objects) AS x
      WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1
  );
GO

SELECT [date]
  FROM dbo.DatesInRange('20120101', '20120105')
  ORDER BY [date];

Results:

date
-----------------------
2012-01-01 00:00:00.000
2012-01-02 00:00:00.000
2012-01-03 00:00:00.000
2012-01-04 00:00:00.000
2012-01-05 00:00:00.000

A stored procedure does not necessarily return data, but it can return more than one resultset. It can be used to affect data (update/insert/delete) and other side effects, can contain dynamic SQL, can have transactions, can have error handling, do not have restrictions about non-deterministic functions, and can call other stored procedures.

EDIT

I am not quite sure what you mean by "function does not store in database." When you create either a stored procedure or a function, its definition is most certainly stored in the catalog view sys.sql_modules, and a reference to the module is created in sys.objects. There is a separate catalog view for stored procedures (sys.procedures) but there isn't an equivalent view for functions - you can still find those in sys.objects with types such as IF, FN and TF.

I will come back on the compile part of the question.


SSAS - Which way to order an attribute? Our SSAS cube has a date dimension, which for the DayName string member displays alphabetically instead chronologically. I added a DayOfWeek integer 1-7 to use for ordering. I have two options to accomplish this, not sure of the advantages/disadvantages of either.

Make DayName attribute visible, and use DayOfWeek as OrderBYAttribute
Make DayOfWeek attribute visible, and use DayName as NameColumn

The attributes are both functionally the same when setup like this. I'm leaning towards #2, since it's an int.

You have your terminology a bit wrong which makes it hard to tell what exactly you mean by your 2 options but you should use the OrderByAttribute (which is what I think you mean by option 1). The OrderByAttributeis meant to be used for exactly that, ordering the attributes by something else than their Key or Value.

If by option 2 you mean you create the attribute using the DayOfWeek column (essentially creating an attribute with a KeyColumn of DayOfWeek) and then set the NameColumn to DayName you would in this case essentially have the same end user experience but that is not what those attributes are meant to do.

The KeyAttribute should be used to define which members are unique and the NameColumn should be used to define what you want to be shown to the users.
Each of these properties have their own purpose and you should use them as such.

Abusing KeyColumn to define an order will get you in trouble when you will actually need that property to resolve issues with duplicates and so on.

Some excerpts from the documentation:

  KeyColumns:   Contains the column or columns that represent the key for
  the attribute, which is the column in the underlying relational table
  in the data source view to which the attribute is bound. The value of
  this column for each member is displayed to users unless a value is
  specified for the NameColumn property.

  NameColumn    Identifies the column that provides the name of the
  attribute that is displayed to users, instead of the value in the key
  column for the attribute. This column is used when the key column
  value for an attribute member is cryptic or not otherwise useful to
  the user, or when the key column is based on a composite key. The
  NameColumn property is not used in parent-child hierarchies; instead,
  the NameColumn property for child members is used as the member names
  in a parent-child hierarchy.

  OrderBy   Describes how to order the members that are contained in the
  attribute hierarchy. The default value is Name, which specifies that
  ordering of the attribute members is based on the value of the
  NameColumn property, if any. Otherwise, members are ordered by the
  value of the key column. The available options are as follows:


I/O errors backing up to an SMB Share from MSSQL Server 2014 I'm having some issues with SQL backups which are being written to an SMB share on a AWS Storage Gateway Appliance from an EC2 SQL Server. I'm consistently getting the same error in the SQL Event log for two SQL instances saying:

  BackupIoRequest::ReportIoError: Write failure on backup device
  'Path_To_Backup' Operating System Error 59 (An unexpected network
  error occurred)

This causes several DB backups to fail at exactly the same time to the second, resulting in an invalid backup file. This happens 2 hours into the backup of this 150-200GB database at around 1AM. The weird part is I have other servers backing up to this path which do not experience the failure. I've been working with AWS Support and they've been unable to find a related network or Storage Gateway Error. I have scoured the Windows/SQL Logs but can't find any correlated events. What errors I do have seem to be dead ends for my particular error.

Is there anything I can monitor in SQL server to collect more information about the backup failure than I'm getting from the default Windows/SQL Logs? Is it a valid backup strategy to backup to a share when you're writing ~600gb a night from many SQL instances?

Just following up on this. Backups have been running flawlessly since we made a change to the SMB timeout in the registry:

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\LanmanWorkstation\\Parameters]
"SessTimeout"=dword:00000258

What that works out to is 600 seconds or a 10 minute timeout for SMB sessions from the SQL Server, the default is 60 seconds. it needs to be saved in hex.

I found it in the article: CIFS and SMB Timeouts in Windows

It's the line with a heading of Client Session timeout.

We arrived at this solution after verifying in a packet capture that the session was being ended by the SQL Server sending an RST, ACK to end the session.


Clarification on signal wait percent I'm experiencing high Signal Wait percent while CPU utilisation is very low at one of our servers.
I have gone through numerous articles online about Signal Waits and understand that it is the time spent on the runnable queue however I'm still having difficult time to understand why CPU usage is staying so low (25%) while Signal Waits' percent is high (band of 75-90%.)
If a session is waiting for an available CPU to run it, why is CPU utilised so low?

Don't tune with percentages. Your server will always have 100% of its waits on something - but there just may not be much load. Your server is the classic example.

Say you only run 1 query per hour. All of the query's data is already cached in memory, and there's no one else competing for locks. That query has several parallel tasks in it, and they all need to get CPU time.

That query's just going to work for a while, then finish. If you looked at signal waits, it might seem like 90-100% of the time, WHEN IT WAS WAITING, it was waiting to get on CPU. But it just wasn't waiting that much.

Instead, look at wait time per core, per second (or per hour) as described here:

https://www.brentozar.com/archive/2015/03/how-to-measure-sql-server-workloads-wait-time-core-second/

(Disclaimer: I wrote that.) That way, you can see if the server's really doing any work at all. I bet in your server, you're only waiting a few minutes per hour - meaning, you just don't need to bother tuning that server overall. It doesn't mean every QUERY is fast - it just means you can't do server-level tuning, and you have to switch to query-level tuning. (You have to ask why the query is slow rather than asking why the server is slow.)


MariaDB - Changing explicit_defaults_to_timestamp I am trying to check from MariaDB docs online how I can turn ON explicit_defaults_to_timestamp global variable. I encountered this as part of Apache Airflow db setup.
Is there any solution to do this? I am running mysql as system service via systemctl.

open you my.conf/ini
find the section [mysqld]
and add explicit_defaults_for_timestamp = 1
sometimes you need instead
explicit_defaults_for_timestamp = ON
You can check after restarting mysql with
SHOW GLOBAL VARIABLES LIKE '%timestamp%'; if it is enabled
But this is a not standard option and could be removed at some point


Can I consolidate multiple MYSQL databases into one database via a script? I'm using MYSQL 5.7.19 on an ubuntu server.
Say I have 3 databases (db_1,db_2,db_3) and I create an empty database called all_db

Is there a way to copy my databases to all_db where db_1,db_2,and db_3 fall under all_db with the format of all_db.db_1_tablename1,all_db.db_2_tablename1, etc?

I found a solution to my problem.
I wrote a select statement from the information_schema database that pulls all of the databases and their tables. I then ran these commands via CLI.

select concat('alter table ', table_schema, '.', table_name, ' rename NEWDB.', table_schema, '_', table_name, ';') from tables

Thanks for the advice, help.
Hopefully this helps someone in the future.


Restore / Create a database with a data file (MDF) from backup the same but with two data files (MDF and NDF) I must restore/create a db (in a new instance) in one file(mdf) from a backup of the same but with two files of data (mdf and ndf)

How can I do it?

This is what I try... but it did not work

    RESTORE database mydb
    FROM  DISK='\\150.10.10.12\backup\mydb.bak'
    WITH  REPLACE, RECOVERY,
    MOVE 'Mydb_DATOS'       TO 'F:\MSSQL\Data\Mydb.mdf',
    MOVE 'Mydb_REGISTRO'    TO 'G:\MSSQL\LOG\Mydb_LOG.ldf'

then check the following

 RESTORE FILELISTONLY
 DISK='\\150.10.10.12\backup\mydb.bak'

You need to include the .NDF file in your restore script.

 RESTORE DATABASE mydb
    FROM  DISK='\\150.10.10.12\backup\mydb.bak'
    WITH
        REPLACE
      , RECOVERY
      , MOVE 'IMAGENES_Data' TO 'F:\MSSQL\Data\Mydb.mdf'
      , MOVE 'IMAGENES'      TO 'F:\MSSQL\Data\Mydb.ndf'
      , MOVE 'IMAGENES_Log'  TO 'G:\MSSQL\LOG\Mydb_LOG.ldf';


Fetch data if exist in distinct of another column I am trying to generate the query to fetch the data on the basis of company which have tax paid record in each year of :

Select *
From (
  Select
       CompanyName
     , TaxPaidOn
     , DATEPART(Year, TaxPaidOn) as 'TaxPaidYear'
     , OtherInfo
  From
  TestData
) As BaseTable
Where CompanyName IN (Distinct TaxPaidYear)

I know the above query is wrong. Will you please rectify it?

Sample data are as below:

Script :

CREATE TABLE TestData(
    CompanyName nvarchar(50) NOT NULL,
    TaxPaidOn date NOT NULL,
    OtherInfo nvarchar(max) NULL
)
GO
INSERT TestData (CompanyName, TaxPaidon, OtherInfo)
VALUES
 ('ABC', '2009-01-01', 'Information1')
,('ABD', '2009-01-01', 'Information2')
,('ABE', '2009-01-01', 'Information3')
,('ABF', '2009-01-01', 'Information4')
,('ABC', '2010-01-01', 'Information5')
,('ABE', '2010-01-01', 'Information6')
,('ABF', '2010-01-01', 'Information7')
,('ABC', '2011-01-01', 'Information8')
,('ABD', '2011-01-01', 'Information9')
,('ABE', '2011-01-01', 'Information10')

TestData Table :

Company Name    TaxPaidOn    OtherInformation
==============================================
ABC             2009-01-01   Information1
ABD             2009-01-01   Information2
ABE             2009-01-01   Information3
ABF             2009-01-01   Information4
ABC             2010-01-01   Information5
ABE             2010-01-01   Information6
ABF             2010-01-01   Information7
ABC             2011-01-01   Information8
ABD             2011-01-01   Information9
ABE             2011-01-01   Information10

Expected Result :

Company Name    TaxPaidOn    OtherInformation
==============================================
ABC             2009-01-01   Information1
ABE             2009-01-01   Information3
ABC             2010-01-01   Information5
ABE             2010-01-01   Information6
ABC             2011-01-01   Information8
ABE             2011-01-01   Information10

I could achieve the result you expected with this query:

;WITH CTE (CompanyName, Total)
AS
(
    SELECT companyName, COUNT(1)
    FROM TestData
    GROUP BY CompanyName
)
SELECT TD.CompanyName, TD.TaxPaidOn, TD.OtherInfo
FROM CTE
    INNER JOIN TestData AS TD
    ON CTE.CompanyName = TD.CompanyName
WHERE Total = (SELECT MAX(Total) FROM CTE)
ORDER BY TD.TaxPaidOn;

It doesn't have a DISTINCT though.


DISABLE SQLSERVER VSS backups I have some automated backups that i want to disable. but i do not know how to do it. Apparently these are done via Volume Shadow copy Service  .

Can anyone tell how to get rid of these backups ?

You will need to do a trace or Extended Event session to determine what application is attempting the backups. Be sure to include the client hostname and application name, those two generally help indicate what is taking the backup. The client hostname will most always show the local machine name, which if that is the case look at what is installed on the server.

Some examples of 3rd products that will use VSS:

Symantec NetBackup SQL Agent
Symantec Backup Exec ( I believe does this now as well)
VMWare/Hyper-V (both products have options to along with snapshot of server to also do database)


How do I design a Dimension and Fact Table from a relational database? I'm new to using SQL Server 2008 R2 SSAS.  I have the following tables in my database, that I wish to model using SQL Server Analysis Services:

Table1

ServiceTypeID    | ServiceTypeName
---------------------------------------
   001           |    FLM
   002           |    PM
   004           |    SLM
   005           |    CL
   999           |    OTH

Table 2

BankCode   | BankName
------------------------------------------
BAY        | Bank Of Ayudhya
Brinks     | Brinks rama3
ESB        | Easy Buy
ISB        | Islamic Bank
SCB        | Siam Commercial Bank
SCBT       | Standard Chartered Bank
TCRB       | Thai Credit Retail Bank

Table3 (main table)

TransactionNo            | ServicedDate            | BankCode | ServicedTypeId
------------------------------------------------------------------------------
ATMBK00000000000104212   | 2011-06-09 00:00:00.000 | BAY      |  001
ATMBK00000000000104648   | 2011-06-14 00:00:00.000 | BAY      |  001
ATMBK00000000000105504   | 2011-06-24 00:00:00.000 | BAY      |  001
ATMBK00000000000107397   | 2011-07-14 00:00:00.000 | BAY      |  001
ATMBK00000000000111500   | 2011-08-23 00:00:00.000 | BAY      |  001
ATMBK00000000000115016   | 2011-09-23 00:00:00.000 | BAY      |  001
ATMBK00000000000123117   | 2011-12-14 00:00:00.000 | BAY      |  001

I need a to design a fact table and dimensions to get a report of BankName as a column and ServiceTypeName as row, categorized by month.  I've used the DISTINCT COUNT function on TransactionNo to get the total number of different services provided to each bank. I also don't have a time dimension, which I need.

Can anyone point me in the right direction?

First, I suggest you read about star schema first. Not understanding the concepts could lead to wrong results.

You may use the transaction as your fact table as in the diagram below.

Don't use DISTINCT, instead you want to use:

SELECT ... FROM fact GROUP BY dimension data (time, transaction and bank)
WHERE condition to restrict dimension data and join with fact.


How to design a versioned database with PostgreSQL? I am planning to create a back-end aka an API. The database will be PostgreSQL. I would like to discuss the following scenario:

How can I have multiple versions of the same database? I would like to be able to serve data from an older database structure (v.1) to older clients while serving a newer database structure (v.2) to newer clients. I would like to maintain multiple database versions since I would like to avoid to write workarounds for each version in the surrounding API.

Please leave you architectural thoughts.

Use liquibase (you can write 99% of it in sql now, not xml)
Store liquibase schema in VCS (like git)
branch different versions in git
loop and apply branches to either different database schemas (tenant per schema. you could set schema at top of liquibase file) or loop and apply to different databases (tenant per database, would need to change liquibase db per loop)

see http://msdn.microsoft.com/en-us/library/aa479086.aspx for architectural discussion (applies equally to postgres). shared schema does not apply here.


How to time SPARQL queries on an Oracle12c database? I have an Oracle 12c instance setup with an Apache Fuseki endpoint sitting on top of it. I am able to query the database using the Fuseki endpoint. I want to be able to get the execution time of some queries without the network overhead. What is the best way to go about this?

EDIT: I also have access to the server but do not have DBA permissions. I can only query the data.

If you have access to the server, use SQLPlus running on the server to invoke your query; that will eliminate the network delay.  To get somewhat precise timings, invoke these SQL*Plus commands first:

set timing on
set autotrace on statistics

Invoke the SQL a few times; the first time will likely cache the data and subsequent runs will reflect that.  Then do the same queries via your client.

If you had DBA access you could turn on tracing for a session and see exactly how much time is used for each part of the query.


Extend iso currency data with user currency I have a simple currency table with iso 4217 data:
currencies
----------
currency_code char(3) pk
name          nvarchar

And I want users to be able to add their own currencies (like cryptocurrencies) in a table like:
users_currencies
----------------
user_id int     pk (fk to users table)
code    char(3) pk
composite primary key (user_id, code)

So that they can use to price a product:
users_products
--------------
user_id       int pk
product_id    int pk
currency_code char(3) (fk to users_currencies)
price         decimal

Then I would copy the currencies table into users_currencies when creating new users.
Is this a good design? What are other possibilities?
I would like to avoid this kind of copy if possible.

You shouldn't be copying data from the currencies table into the user_currencies table, otherwise that defeats the purpose of having a currencies table.
If users are creating new currencies (besides the ISO currencies) then you could just have a single currencies table with the currency_code (primary key), name, and possibly an isStandard BIT field that you insert into. Then your user_currencies table will just store the mapping of currencies each user has created.
Then to get all standard codes and the ones a user has created you just need to join the two tables together like so:
SELECT currencies.[name]
FROM currencies
INNER JOIN users_currencies
    ON currencies.currency_code = user_currencies.code -- Gets only the user created currency codes
    OR currencies.isStandard = 1 -- Gets all the ISO currency codes
WHERE users_currencies.user_id = 'SomeUserId'

You can even create a View to save the above query minus the WHERE clause, and adding in the user_id field to the SELECT list, so you can later filter by a specific user.


Are key lookups from non-clustered indexes always slower than a second query that does the lookup? I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries.

The first with the non-clustered index inserting only the key field into a temp table (so no key lookup is performed) and the second using that temp table to join back to the original table to filter it down on the key and then select the fields I need.

I'm typically querying tables with hundreds of millions to tens of billions of rows when I notice this. I'm not sure if it can be related to the fact that I'm eliminating the key lookup when the table is first loaded into memory and instead I'm inserting the key into a temp table so that the subsequent field lookup query occurs between two tables already in memory?

The difference in time I'll see is usually significant too, e.g. on the order of minutes.

I can think of a couple of cases where this approach might be beneficial.

Sometimes you can end up with an execution plan which does a load of lookups for rows that are then ultimately filtered out downstream (I've noticed this especially with pagination queries). If you only store the rows post filtering there will be fewer "manual lookups" to resolve. This scenario can generally be addressed with a self join and does not need intermediate materialisation.
Lookups always use nested loops. The "manual lookup" might use a different join type - the cardinality estimates for rows to lookup will be spot on when materialised and may differ from the original estimates encouraging this.

For the case where neither of the above apply (and you are just materialising into a temp table and getting the same number of nested loops lookups as you would have got without this step and not benefiting from improved cardinality estimates) I would expect this to generally be slower than the original query without the intermediate step (as on the face of it you are doing the same work with some additional overhead added) but haven't tested this.


AUTO_UPDATE_STATISTICS_ASYNC ON? I have been advised that setting the AUTO_UPDATE_STATISTICS_ASYNC ON may be a good idea.  I have been reading a couple of ideas and still not sure as this is a non default setting.

Any further advice?

This is a big "It Depends..." but generally speaking you don't want to have statistics updating asynchronously for most workloads because it can mean that you generate a less-than ideal execution plan based on the existing out-of-date statistics.

If you have a scenario where the auto update stats execution takes an excessive amount of time and causes timeouts, you might consider going the route of letting stats update asynchronously so that it doesn't create that problem.  The few seconds it typically takes to auto update statistics can easily result in minutes of difference in execution times from a better execution plan from the new statistics.


what oracle data dictionary view can be used to decribe types defined in a plsql package? What oracle data dictionary view can be used to describe types / subtypes defined in a plsql package?

If all of the types uses are used explicitly, meaning now using execute immediate, then you can look at dba_dependencies to see what types a PL/SQL stored procedure uses.


Should I align my SQL Server file and growth sizes with the block sizes on my SAN? It was brought to my attention that our (Hitachi) SAN handles data in 42MB blocks. It is tiered storage so each block is evaluated when the director makes decisions as to when it should promote/demote storage to faster/slower storage. In the absence of having the political weight to "pin" particular LUNs to particular tiers I am trying to do all I can to set up my SQL Server (2008 R2 Enterprise) instance for success.

As such, I was wondering if there would be any benefit/detriment to changing my file sizes and file growth sizes to multiples of 42? An example database would be SIZE=57886MB with a FILEGROWTH=5124MB.

I see the potential for extra reads (since (42*1024)/8 = 8601.6, but (42*1024)/64 = 672) but I'm not sure I'm fully grasping all of the concepts here.

The 42MB unit (Hitachi calls this a page too, how convinient). is the basic allocation unit at which space is allocated to a LUN. So with Dynamic provisioning, a LUN will always grow in 42MB steps. These, Hitachi 42MB pages are taken from different Array groups with in a pool.

So whenever more space is needed for the LUN a new 42MB Hitachi page is allocated. When you write your first 8KB SQL Data page to a file, a 42MB Hitachi page will be allocated. Subsequent writes of SQL data pages go to this 42MB area, until it's full.

Now, what is important to understand is that a 42MB page goes to one parity raid group. If you have small RAID groups, for example : RAID-1+0: 2D+2P (2+2) (4 disks) and your data access is mostly sequential, this could be a problem when you have one file.

Lets say you have 4 RAID10 groups each having 4 disks. You just happen to have only 1 important table with 1 clustered index that is 168MB in size.

When you start inserting into this table, the first 42MB alloc page will be placed on one of the RAID groups, once this 42MB is full, the second alloc page will be allocated on another RAID group, most likely all 4 42MB alloc page will be devided across the 4 RAID GROUPS. (we are excluding tiering for simplicity at this moment).

What's important to understand is that anything you do sequentially in the order of the clustered key, will only have 4 disks at any given moment to handle the IO. (2 disks if you are writing..) even though the whole table is spread across 16 disks..

If you would want to be using all 16 disks. you would have to create 4 files (on 4 seperate LUNS) in a filegroup and create the table on that. At least you now know that SQL is "striping the data across 4 42 MB alloc pages"

This can be most problematic  with the transaction log file. It's only 1 file and sequential. The only "tuning" you can do here is make the RAID groups used in that pool as big as possible. Contradictionary a concatinated array group RAID5 14+2 OR RAID5 28+4 might be the fastest even though it's RAID5 as opposed to RAID 10, since as far as I know the biggest RAID group in RAID 10 that can be created is 4+4 is 8 disks.

Please be aware this becomes far less important if you are running a server with multiple databases with multiple LUNS all sharing the same pool, or even more generic multiple servers all having LUNS in the same pool. SInce obviously the general load would then be evenly distributed across all the raid groups.

Regarding tiering, I wouldn't be to worried about it from a performance objective. (if you are not allowed to pin..) Heavy 42MB alloc pages are moved to a high performance tier, so you could have the situation where one table is spread across 3 different tiers. Apperently you have parts of your table that are IO intensive and parts that are not...

worry about the combination small raid groups AND single files (actually single LUNs) AND  (high incremental inserts OR heavy table localized updates/deletes OR heavy seqeuential reads)

hope this helps a bit..

further reading:

Hitachi Tiering design guide

Hitachi SQL Server best practise

Blog post about 42 alloc unit


Postgres Nested WHEN Aggregate Function In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by "SettlementPointPrice" the count() function works correctly bucketing prices into individual tranches as I would expect. However, this creates hundreds of rows. I am looking for the ability (sub-query?) to aggregate the sum of count()'s in each bucket into one single row. What is the best way to manage this in SQL?

I am using a statement like this (full SQL below) for each bucket/tranche:

CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 0 AND 10
     THEN count(*) ELSE 0 END AS "DA $0 - $10",

When I group by "SettlementPointPrice" (two prices in this example) to confirm the data counts the buckets correctly. As the table below shows.

Raw data from individual two days:

Row | "SettlementPointPrice" | 0-10 | 11-20 | 21-30
1   | 18                     | 0    | 1     | 0
2   | 22                     | 0    | 0     | 1

However, I am unable to get the aggregated summation by grouping them all together. I assume this is a sub-query?

I would like the result to be as such:

Row | 0-10 | 11-20 | 21-30
1   | 0    | 1     | 1

Full SQL code:

SELECT
  "DA-A"."SettlementPointPrice",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 0 AND 10
       THEN count(*) ELSE 0 END AS "DA $0 - $10",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 11 AND 20
       THEN COUNT(*) ELSE 0 END AS "DA $11 - $20",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 21 AND 30
       THEN COUNT(*) ELSE 0 END AS "DA $21 - $30"
FROM
  public.da "DA-A",
  public.rt_aggregate "RT-A"
WHERE
  "RT-A"."DeliveryDate" = "DA-A"."DeliveryDate" AND
  "RT-A"."SettlementPointName" = "DA-A"."SettlementPointName" AND
  "DA-A"."SettlementPointName" = 'John' AND
  "DA-A"."DeliveryDate" >= '2015-02-01' AND
  "DA-A"."DeliveryDate" <= '2015-02-20' AND
 ("RT-A"."DeliveryHour" = 14) and
  date_part('hour', "DA-A"."DeliveryHour") = "RT-A"."DeliveryHour"
GROUP BY
  "DA-A"."SettlementPointPrice",
  "DA-A"."SettlementPointName"

After some processing this boiled down to:

While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to:

SELECT count(                                     d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10
     , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" < 20.5 OR NULL) AS da_11_20
     , count(d."SettlementPointPrice" >= 20.5 AND d."SettlementPointPrice" < 30.5 OR NULL) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."SettlementPointName" = 'John'
AND    d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour";

About the counting technique:

For absolute performance, is SUM faster or COUNT?

Or better, yet, use the new aggregate filter technique in pg 9.4:

SELECT d."SettlementPointName"
     , count(*) FILTER (WHERE d."SettlementPointPrice" <  10.5) AS da_00_10
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 10.5
                        AND   d."SettlementPointPrice" <  20.5) AS da_11_20
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 20.5
                        AND   d."SettlementPointPrice" <  30.5) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour"
GROUP  BY 1;

This time, selecting all names and returning one row per name like you asked in the comment.

Details for FILTER:

Return counts for multiple ranges in a single SELECT statement


Compatibility level 100 on SQL Server 2008 R2 I'm upgrading my databases to compatibility level 100 on SQL Server 2008 R2.
My next step is to check if my stored procedures, functions, views, triggers are still working properly. Running each of these is not an option.

How should I check?

Your question is too wide I am not sure a clear answer can be given but I would try. As such sitting from here I cannot tell you what could break after chaning compatibility level. Only you can find it out by running each and evry procedures,functions, views and triggers  you have.

Microsoft has documented breaking changes in SQl server 2008 R2 before changing the compatibility level you must go through this article.

You must also read Change database compatibility level BOL document to see what could get affected after changing compatibility level. Please read complete article most of the things are documented.

Did you ran upgrade advisor before you migrated database to 2008 R2 ? that should as well have pointed out breaking changes.

The good thing with compatibility level is it can be immediately changed to previous value if you face issue. So you also have got hit and try method to check out


is possible to use graphql with couchdb? I work well with graphql environment, and I need to use couchdb, but his api with uris like rest api, it's for me a little old way to retrive data. Is possible to use graphql with Couchdb ?  or there are plans in the future ?

I think GraphQL is a tool meant to smooth the way for you to write the custom API for your own app. Very few databases have a commonly published API as yet.

Some people have started playing with GraphQL for couchdb and pouchdb - see these repos on Github

https://github.com/chentsulin/awesome-graphql
https://github.com/esnet/couchdb-graphql-test
https://github.com/robertkowalski/couchdb-graphql
https://github.com/MikeBild/graphql-pouch


Possible to CREATE EVENT NOTIFICATION with a different owner? We are in the process of removing a previous dba login and he owns all the endpoints and event notification objects. Endpoints were easy to change; Event notification objects not so much.

I found this thread about changing the owner of an event notification object (you have to drop and recreate). I don't want to go through this process again if I can avoid it. I doubt it's possible, but outside of logging in as another user, can you create an event notification that runs as sa, etc.?

I thought that ALTER AUTHORIZATION might offer a way to transfer ownership, but no, according to the docs (emphasis mine):

  Ownership of the following entities cannot be transferred: <many things> and event notifications.

So, script them, drop them, and re-create them under a more generic account that is not tied to an employee.

This is similar to things like the ANSI_NULLS setting for tables (which I dealt with recently re: persisted computed columns).


Login Failed for User I have written code in Visual Studio.NET and used data from SQL Server 2008. After debugging, it says:

Login failed for user 'sunny'.

When I try to set the user as something else, it keeps on reporting that error. I start sql with windows authentication and the username there is: sunny-lptp\sunny.

I do not have any sql authentication user/pass on sqlserver express 2008.

I tried changing the authentication in server/security tab. I changed it to both windows and sql. but it didn't fix the error.

What should I do?

Here is the connection string in web.config:

<configuration>
    <appSettings>
        <add
            key="SchoolsDBConnectionString"
            value="server=sunny-lptp\sqlexpress;database=SchoolsDB;">
        </add>
    </appSettings>

And the code:

scn = new sqlConnection(
    ConfigurationManager.AppSettings["SchoolsDBConnectionString"].ToString());

Append on Trusted_Connection=True to the end of your connection string in your config file:

server=sunny-lptp\sqlexpress;database=SchoolsDB;Trusted_Connection=True

Great resource for connection strings


Can a Couchbase application work with no network connection (like CouchDB)? I'm building an application where users running android tablets will have to be able to write to a database (running on their local device) with NO network connection and sync using peer-to-peer replication (through a USB connection or possibly bluetooth). I'm working in West Africa, where internet is basically nonexistent.

I know that CouchDB will work for this use case, but it seems like it's difficult to get CouchDB running on Android (compared to Couchbase). Will Couchbase work for this project?

Thanks!
- avi

PM from Couchbase here. We've actually been working on an embedded NoSQL database for mobile dubbed "Couchbase Lite" (in parallel with SQL and SQLite) that plays exactly into the scenario you are describing. The idea is to provide your application with a JSON-based database that can:

Opportunistically sync to a backend cluster as needed
Utilize our built-in P2P support to replicate data between devices without going through a central point
Store data locally in its original JSON format for full-offline access to your data

Some initial reading can be found here (http://www.couchbase.com/mobile#lite) but feel free to get in touch with me if you'd like to discuss more in-depth.


WARNING, this violates some basic database design principles. Like @a_horse_with_no_name notes, you want separate values (user names, user IDs) in separate rows / columns, not aggregated in a single comma-separated field. IMHO this is the most important reason you're not succeeding in writing this query on your own; otherwise, some simple JOINs would suffice.

But, if you insist, it can be done. Here's an example, using the STRING_SPLIT function from SQL Server 2016 to split comma separated strings and this trick to join them again:

SELECT file_name, LEFT(ca.value, LEN(ca.value) - 1), assign_users_id
FROM files AS extern
CROSS APPLY (
  SELECT user_name + ','
    FROM files AS intern
    CROSS APPLY STRING_SPLIT(assign_users_id, ',')
    INNER JOIN users
      ON users.user_id = value
    WHERE intern.file_name = extern.file_name
    FOR XML PATH('')
) ca(value)
GROUP BY file_name, ca.value, assign_users_id

SQLFiddle


Two-column foreign key constraint only when third column is NOT NULL Given the following tables:

CREATE TABLE verified_name (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  UNIQUE (name, email)
);

CREATE TABLE address (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  verified_name_id INTEGER NULL REFERENCES verified_name(id)
);

How can I add an additional constraint that when address.verified_name_id is not NULL, the name and email fields on the address must match the those on the referenced verified_name?

I've tried adding the following to address:

FOREIGN KEY (name, email) REFERENCES verified_name(name, email)

...but that constraint is being applied even when verified_name_id is NULL.

I'm looking for something similar to the partial index syntax with a clause like WHERE verified_name_id IS NOT NULL, but simply appending a clause like that to the FOREIGN KEY constraint doesn't work.

Current undesirable solution:

I can add the following constraints to verified_name:

UNIQUE (name, email),
UNIQUE (id, name, email)

And the following constraint to address:

FOREIGN KEY (verified_name_id, name, email) REFERENCES verified_name(id, name, email)

...but that creates an extra constraint on verified_name that I'd prefer not to have (it's a valid logical constraint, but it's also superfluous and there are minor performance implications).

Proper solution

The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this:

CREATE TABLE name (
  name_id          SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  verified         BOOLEAN NOT NULL DEFAULT FALSE,
  UNIQUE (name, email)
);

CREATE TABLE address (
  address_id       SERIAL PRIMARY KEY,
  name_id          INT REFERENCES name(name_id)
  ...
);

If not-yet verified names should be allowed to break the UNIQUE constraint, you can replace it with a partial UNIQUE INDEX (much like you had in mind):

CREATE UNIQUE INDEX name_verified_idx ON name(name, email) WHERE verified;

Work with what you have

While stuck with your unfortunate design, the solution you already found yourself fits your requirements perfectly. A FOREIGN KEY with the default MATCH SIMPLE behavior matches the

  partial index syntax with a clause like WHERE verified_name_id IS NOT NULL.

Quoting the manual:

  MATCH SIMPLE allows any of the foreign key columns to be null; if
  any of them are null, the row is not required to have a match in the
  referenced table.

A (somewhat less reliable and more expensive) alternative would be a trigger on INSERT / UPDATE in address and trigger(s) on INSERT / UPDATE / DELETE in verified_name.


VLDB - SQL SERVER versus VFoxPro I must create an DBF for an application which will collect data from 5 millions sensors on every minute, so I will have 5 millions records every minute.

I was thinking to use SQL server because I will have 7,2 billions records every day. But when I tested the writing speed on my PC (Dual Core E6300, @ Gb RAM, HDD 500Gb SATA) I found that Visual Foxpro 9 is registering 1 million records 100 times faster than SQL Server 2008.

SQL Server, 2.55 minutes:

declare @i int=1
while @i<1000001
begin
 insert into dbo.sensor (sensorid, datetime, value) values (1,getdate(),@i)
 set @i=@i+1
end

VFP9, 2 seconds:

? DATETIME ()
FOR  i=1 TO 1 000 000 STEP 1
 INSERT INTO Table1 (sensorid, dtime, value, status) VALUES (i, date(), i, "S")
ENDFOR
? DATETIME()

I mention that there was no Index, and the table will be read only. What is wrong?

A good place to start would be looking at the SQL CAT team's whitepaper on data loading performance for SQL Server 2008.

The procedural loops in SQL Server are very slow, which would be a large part of the difference in timings.  It would be best to generate files to be bulk loaded in the databases to measure the performance differences.  Also the size of the database will very quickly require the use of multiple database files to spread the load which I'm not sure that FoxPro has any builtin support.


SUM of DATALENGTHs not matching table size from sys.allocation_units I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken?

SELECT
SUM(DATALENGTH(Field1)) +
SUM(DATALENGTH(Field2)) +
SUM(DATALENGTH(Field3)) TotalSizeInBytes
FROM SomeTable
WHERE X, Y, and Z are true

I used this query below (that I got from online to get table sizes, clustered indexes only so it doesn't include NC indexes) to get the size of a particular table in my database. For billing purposes (we charge our departments by the amount of space they use) I need to figure out how much space each department used in this table. I have a query that identifies each group within the table. I just need to figure out how much space each group is taking up.

Space per row may swing wildly due to VARCHAR(MAX) fields in the table, so I can't just take an average size * the ratio of rows for a department. When I use the DATALENGTH() approach described above I only get 85% of the total space used in the query below. Thoughts?

SELECT
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
(SUM(a.total_pages) * 8)/1024 AS TotalSpaceMB,
(SUM(a.used_pages) * 8)/1024 AS UsedSpaceMB,
((SUM(a.total_pages) - SUM(a.used_pages)) * 8)/1024 AS UnusedSpaceMB
FROM
    sys.tables t with (nolock)
INNER JOIN
    sys.schemas s with (nolock) ON s.schema_id = t.schema_id
INNER JOIN
    sys.indexes i with (nolock) ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p with (nolock) ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a with (nolock) ON p.partition_id = a.container_id
WHERE
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND i.type_desc = 'Clustered'
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB desc

It has been suggested that I create a filtered index for each department or partition the table, so I can directly query the space used per index. Filtered indexes could be created programmatically (and dropped again during a maintenance window or when I need to perform the periodic billing), instead of using the space all the time (partitions would be better in this respect).

I like that suggestion and would typically do that. But to be honest I use the "each dept" as an example to explain why I need this, but to be honest, that is not really why. Due to confidentiality reasons I can't explain the exact reason why I need this data, but it's analogous to different departments.

Regarding the nonclustered indexes on this table: If I can get the sizes of the NC indexes, that would be great. However, the NC indexes account for <1% the size of the clustered index, so we are ok not including those. However, how would we include the NC indexes anyway? I can't even get an accurate size for the Clustered index :)

                          Please note that the following info is not intended to be a comprehensive description of how data pages are laid out, such that one can calculate the number of bytes used per any set of rows, as that is very complicated.

Data is not the only thing taking up space on an 8k data page:

There is reserved space. You are only allowed to use 8060 of the 8192 bytes (that's 132 bytes that were never yours in the first place):

Page header: This is exactly 96 bytes.
Slot array: this is 2 bytes per row and indicates the offset of where each row starts on the page. The size of this array is not limited to the remaining 36 bytes (132 - 96 = 36), else you would be effectively limited to only putting 18 rows max on a data page. This means that each row is 2 bytes larger than you think it is. This value is not included in the "record size" as reported by DBCC PAGE, which is why it is kept separate here instead of being included in the per-row info below.
Per-Row meta-data (including, but not limited to):

The size varies depending on the table definition (i.e. number of columns, variable-length or fixed-length, etc). Info taken from @PaulWhite's and @Aaron's comments that can be found in the discussion related to this answer and testing.
Row-header: 4 bytes, 2 of them denoting the record type, and the other two being an offset to the NULL Bitmap
Number of columns: 2 bytes
NULL Bitmap: which columns are currently NULL. 1 byte per each set of 8 columns. And for all columns, even the NOT NULL ones. Hence, minimum 1 byte.
Variable-length column offset array: 4 bytes minimum. 2 bytes to hold the number of variable-length columns, and then 2 bytes per each variable-length column to hold the offset to where it starts.
Versioning Info: 14 bytes (this will be present if your database is set to either ALLOW_SNAPSHOT_ISOLATION ON or READ_COMMITTED_SNAPSHOT ON).

Please see the following Question and Answer for more details on this: Slot Array and Total Page Size
Please see the following blog post from Paul Randall which has several interesting details on how the data pages are laid out: Poking about with DBCC PAGE (Part 1 of ?)

LOB pointers for data that is not stored in row. So that would account for DATALENGTH + pointer_size. But these are not of a standard size. Please see the following blog post for details on this complex topic: What is the Size of the LOB Pointer for (MAX) Types Like Varchar, Varbinary, Etc?. Between that linked post and some additional testing I have done, the (default) rules should be as follows:

Legacy / deprecated LOB types that nobody should be using anymore as of SQL Server 2005 (TEXT, NTEXT, and IMAGE):

By default, always store their data on LOB pages and always use a 16-byte pointer to LOB storage.
IF sp_tableoption was used to set the text in row option, then:

if there is space on the page to store the value, and value is not greater than the max in-row size (configurable range of 24 - 7000 bytes with a default of 256), then it will be stored in-row,
else it will be a 16-byte pointer.

For the newer LOB types introduced in SQL Server 2005 (VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)):

By default:

If the value is not greater than 8000 bytes, and there is room on the page, then it will be stored in-row.
Inline Root — for data between 8001 and 40,000 (really 42,000) bytes, space permitting, there will be 1 to 5 pointers (24 - 72 bytes) IN ROW that point directly to the LOB page(s). 24 bytes for the initial 8k LOB page, and 12 bytes per each additional 8k page for up to four more 8k pages.
TEXT_TREE — for data over 42,000 bytes, or if the 1 to 5 pointers can't fit in-row, then there will be just a 24 byte pointer to the starting page of a list of pointers to the LOB pages (i.e. the "text_tree" page).

IF sp_tableoption was used to set the large value types out of row option, then always use a 16-byte pointer to LOB storage.

I said "default" rules because I did not test in-row values against the impact of certain features such as Data Compression, Column-level Encryption, Transparent Data Encryption, Always Encrypted, etc.

LOB overflow pages: If a value is 10k, then that will require 1 full 8k page of overflow, and then part of a 2nd page. If no other data can take up the remaining space (or is even allowed to, I am unsure of that rule), then you have approx 6kb of "wasted" space on that 2nd LOB overflow datapage.
Unused space: An 8k data page is just that: 8192 bytes. It does not vary in size. The data and meta-data placed on it, however, does not always fit nicely into all 8192 bytes. And rows cannot be split onto multiple data pages. So if you have 100 bytes remaining but no row (or no row that would fit in that location, depending on several factors) can fit there, the data page is still taking up 8192 bytes, and your 2nd query is only counting the number of data pages. You can find this value in two places (just keep in mind that some portion of this value is some amount of that reserved space):

DBCC PAGE( db_name, file_id, page_id ) WITH TABLERESULTS; Look for ParentObject = "PAGE HEADER:" and Field = "m_freeCnt". The Value field is the number of unused bytes.
SELECT buff.free_space_in_bytes FROM sys.dm_os_buffer_descriptors buff WHERE buff.[database_id] = DB_ID(N'db_name') AND buff.[page_id] = page_id; This is the same value as reported by "m_freeCnt". This is easier than DBCC since it can get many pages, but also requires that the pages have been read into the buffer pool in the first place.

Space reserved by FILLFACTOR < 100. Newly created pages do not respect the FILLFACTOR setting, but doing a REBUILD will reserve that space on each data page. The idea behind the reserved space is that it will be used by non-sequential inserts and/or updates that expand the size of rows on the page already, due to variable length columns being updated with slightly more data (but not enough to cause a page-split). But you could easily reserve space on data pages that would naturally never get new rows and never have the existing rows updated, or at least not updated in a way that would increase the size of the row.
Page-Splits (fragmentation): Needing to add a row to a location that has no room for the row will cause a page split. In this case, approx 50% of the existing data is moved to a new page and the new row is added to one of the 2 pages. But you now have a bit more free space that is not accounted for by DATALENGTH calculations.
Rows marked for deletion. When you delete rows, they are not always immediately removed from the data page. If they cannot be removed immediately, they are "marked for death" (Steven Segal reference) and will be physically removed later by the ghost cleanup process (I believe that is the name). However, these might not be relevant to this particular Question.
Ghost pages? Not sure if that is the proper term, but sometimes data pages do not get removed until a REBUILD of the Clustered Index is done. That would also account for more pages than DATALENGTH would add up to. This generally should not happen, but I have run into it once, several years ago.
SPARSE columns: Sparse columns save space (mostly for fixed-length datatypes) in tables where a large % of the rows are NULL for one or more columns. The SPARSE option makes the NULL value type up 0 bytes (instead of the normal fixed-length amount, such as 4 bytes for an INT), but, non-NULL values each take up an additional 4 bytes for fixed-length types and a variable amount for variable-length types. The issue here is that DATALENGTH does not include the extra 4 bytes for non-NULL values in a SPARSE column, so those 4 bytes need to be added back in. You can check to see if there are any SPARSE columns via:

SELECT OBJECT_SCHEMA_NAME(sc.[object_id]) AS [SchemaName],
       OBJECT_NAME(sc.[object_id]) AS [TableName],
       sc.name AS [ColumnName]
FROM   sys.columns sc
WHERE  sc.is_sparse = 1;

And then for each SPARSE column, update the original query to use:

SUM(DATALENGTH(FieldN) + 4)

Please note that the calculation above to add in a standard 4 bytes is a bit simplistic as it only works for fixed-length types. AND, there is additional meta-data per row (from what I can tell so far) that reduces the space available for data, simply by having at least one SPARSE column. For more details, please see the MSDN page for Use Sparse Columns.
Index and other (e.g. IAM, PFS, GAM, SGAM, etc) pages: these are not "data" pages in terms of user data. These will inflate the total size of the table. If using SQL Server 2012 or newer, you can use the sys.dm_db_database_page_allocations Dynamic Management Function (DMF) to see the page types (earlier versions of SQL Server can use DBCC IND(0, N'dbo.table_name', 0);):

SELECT *
FROM   sys.dm_db_database_page_allocations(
               DB_ID(),
               OBJECT_ID(N'dbo.table_name'),
               1,
               NULL,
               N'DETAILED'
              )
WHERE  page_type = 1; -- DATA_PAGE

Neither the DBCC IND nor sys.dm_db_database_page_allocations (with that WHERE clause) will report any Index pages, and only the DBCC IND will report at least one IAM page.
DATA_COMPRESSION: If you have ROW or PAGE Compression enabled on the Clustered Index or Heap, then you can forget about most of what has been mentioned so far. The 96 byte Page Header, 2 bytes-per-row Slot Array, and 14 bytes-per-row Versioning Info are still there, but the physical representation of the data becomes highly complex (much more so than what has already been mentioned when Compression is not being used). For example, with Row Compression, SQL Server attempts to use the smallest possible container to fit each column, per each row. So if you have a BIGINT column which would otherwise (assuming SPARSE is also not enabled) always take up 8 bytes, if the value is between -128 and 127 (i.e. signed 8-bit integer) then it will use just 1 byte, and if the value could fit into a SMALLINT, it will only take up 2 bytes. Integer types that are either NULL or 0 take up no space and are simply indicated as being NULL or "empty" (i.e. 0) in an array mapping out the columns. And there are many, many other rules. Have Unicode data (NCHAR, NVARCHAR(1 - 4000), but not NVARCHAR(MAX), even if stored in-row)? Unicode Compression was added in SQL Server 2008 R2, but there is no way to predict the outcome of the "compressed" value in all situations without doing the actual compression given the complexity of the rules.

So really, your second query, while more accurate in terms of total physical space taken up on disk, is only really accurate upon doing a REBUILD of the Clustered Index. And after that, you still need to account for any FILLFACTOR setting below 100. And even then there are always page headers, and often enough some amount of "wasted" space that is simply not fillable due to being too small to fit any row in this table, or at least the row that logically should go in that slot.

Regarding the accuracy of the 2nd query in determining "data usage", it seems most fair to back-out the Page Header bytes since they are not data usage: they are cost-of-business overhead. If there is 1 row on a data page and that row is just a TINYINT, then that 1 byte still required that the data page existed and hence the 96 bytes of the header. Should that 1 department get charged for the entire data page? If that data page is then filled up by Department #2, would they evenly split that "overhead" cost or pay proportionally? Seems easiest to just back it out. In which case, using a value of 8 to multiply against number of pages is too high. How about:

-- 8192 byte data page - 96 byte header = 8096 (approx) usable bytes.
SELECT 8060.0 / 1024 -- 7.906250

Hence, use something like:

(SUM(a.total_pages) * 7.91) / 1024 AS [TotalSpaceMB]

for all calculations against "number_of_pages" columns.

AND, considering that using DATALENGTH per each field cannot return the per-row meta-data, that should be added to your per-table query where you get the DATALENGTH per each field, filtering on each "department":

Record Type and offset to NULL Bitmap: 4 bytes
Column Count: 2 bytes
Slot Array: 2 bytes (not included in "record size" but still need to account for)
NULL Bitmap: 1 byte per every 8 columns (for all columns)
Row Versioning: 14 bytes (if database has either ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT set to ON)
Variable-length column Offset Array: 0 bytes if all columns are fixed-length. If any columns are variable-length, then 2 bytes, plus 2 bytes per each of only the variable-length columns.
LOB pointers: this part is very imprecise since there won't be a pointer if the value is NULL, and if the value fits on the row then it can be much smaller or much larger than the pointer, and if the value is stored off-row, then the size of the pointer might depend on how much data there is. However, since we just want an estimate (i.e. "swag"), it seems like 24 bytes is a good value to use (well, as good as any other ;-). This is per-each MAX field.

Hence, use something like:

In general (row header + number of columns + slot array + NULL bitmap):

([RowCount] * (( 4 + 2 + 2 + (1 + (({NumColumns} - 1) / 8) ))

In general (auto-detect if "version info" is present):

+ (SELECT CASE WHEN snapshot_isolation_state = 1 OR is_read_committed_snapshot_on = 1
                 THEN 14 ELSE 0 END FROM sys.databases WHERE [database_id] = DB_ID())

IF there are any variable-length columns, then add:

+ 2 + (2 * {NumVariableLengthColumns})

IF there are any MAX / LOB columns, then add:

+ (24 * {NumLobColumns})

In general:

)) AS [MetaDataBytes]

This isn't exact, and again will not work if you have Row or Page Compression enabled on the Heap or Clustered Index, but should definitely get you closer.

UPDATE Regarding the 15% Difference Mystery

We (myself included) were so focused on thinking about how data pages are laid out and how DATALENGTH might account for things that we didn't spend a lot of time reviewing the 2nd query. I ran that query against a single table and then compared those values to what was being reported by sys.dm_db_database_page_allocations and they were not the same values for the number of pages. On a hunch, I removed the aggregate functions and GROUP BY, and replaced the SELECT list with a.*, '---' AS [---], p.*. And then it became clear: people must be careful where on these murky interwebs they get their info and scripts from ;-). The 2nd query posted in the Question is not exactly correct, especially for this particular Question.

Minor problem: outside of it not making much sense to GROUP BY rows (and not have that column in an aggregate function), the JOIN between sys.allocation_units and sys.partitions isn't technically correct. There are 3 types of Allocation Units, and one of them should JOIN to a different field. Quite often partition_id and hobt_id are the same, so there might never be a problem, but sometimes those two fields do have different values.
Major problem: the query uses the used_pages field. That field covers all types of pages: Data, Index, IAM, etc, tc. There is another, more appropriate field to use when concerned with only the actual data: data_pages.

I adapted the 2nd query in the Question with the above items in mind, and using the data page size that backs out the page header. I also removed two JOINs that were unnecessary: sys.schemas (replaced with call to SCHEMA_NAME()), and sys.indexes (the Clustered Index is always index_id = 1 and we have index_id in sys.partitions).

SELECT  SCHEMA_NAME(st.[schema_id]) AS [SchemaName],
        st.[name] AS [TableName],
        SUM(sp.[rows]) AS [RowCount],
        (SUM(sau.[total_pages]) * 8.0) / 1024 AS [TotalSpaceMB],
        (SUM(CASE sau.[type]
           WHEN 1 THEN sau.[data_pages]
           ELSE (sau.[used_pages] - 1) -- back out the IAM page
         END) * 7.91) / 1024 AS [TotalActualDataMB]
FROM        sys.tables st
INNER JOIN  sys.partitions sp
        ON  sp.[object_id] = st.[object_id]
INNER JOIN  sys.allocation_units sau
        ON  (   sau.[type] = 1
            AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA
        OR  (   sau.[type] = 2
            AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA
        OR  (   sau.[type] = 3
            AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA
WHERE       st.is_ms_shipped = 0
--AND         sp.[object_id] = OBJECT_ID(N'dbo.table_name')
AND         sp.[index_id] < 2 -- 1 = Clustered Index; 0 = Heap
GROUP BY    SCHEMA_NAME(st.[schema_id]), st.[name]
ORDER BY    [TotalSpaceMB] DESC;