How many B-tree indexes do I need to support lookups by any subset of n columns? First, some background ideas

Generally, an SQL database which supports multi-column B-tree indexes also supports doing a lookup by a subset of the columns in the index if and only if they're the first columns in the index. For example, if I have an index on columns (a, b, c, d) and would like to execute:

SELECT * FROM my_table
WHERE b = 7 AND a = 'foo';

then this will use the index and be fast, because the pair (a, b) is located at the start of the index and so the database can navigate the tree looking for index records starting with ('foo', 7, ... ). However, if I run

SELECT * FROM my_table
WHERE b = 7 AND c = 'bar';

then the index will not be used*, because matching records will be distributed all over the index depending upon the value they have in column a.

* (except perhaps by doing a full or partial scan of the index, as described in Evan's answer below - but since a full index scan still has the same time complexity as a full table scan, and a partial index scan potentially does too, this doesn't help much.)

The problem

I have a table with n columns and a potentially huge number of rows. I also have a front-end GUI that allows the user to filter by the exact value of an arbitrary combination of those columns and view a table of results. It is not acceptable for any query generated by this front end to cause a full table scan to get its results; every possible filter has to be supported by an index.

With n columns, what is the minimum number of B-tree indexes I need to create to ensure that every possible combination of columns is covered by some index?

Example with n=4

Suppose my table has 4 columns: a, b, c, and d. There are then 15 different combinations of columns my user might filter by:

a b c d
a b c
a b d
a c d
b c d
a b
a c
a d
b c
b d
c d
a
b
c
d

However, I can support lookups from any arbitrary subset of these 4 columns using only the following 6 B-tree indexes:

(a, b, c, d)
(b, c, d)
(c, d, a)
(d, b, a)
(a, c)
(a, d)

To illustrate this, below are the 15 possible subsets alongside the index that can be used to do a lookup by that subset:

a b c d   (a, b, c, d)
a b c     (a, b, c, d)
a b d     (d, b, a)
a c d     (c, d, a)
b c d     (b, c, d)
a b       (a, b, c, d)
a c       (a, c)
a d       (a, d)
b c       (b, c, d)
b d       (d, b, a)
c d       (c, d, a)
a         (a, b, c, d)
b         (b, c, d)
c         (c, d, a)
d         (d, b, a)

I'm unsure how to generalise this idea to tables with large numbers of columns, though, or how the number of indexes required scales with n. Hence my question: how many indexes are needed to extend this approach to n columns, and how can I determine what they are?

(I'm primarily interested in the specific theoretical problem of how to support these lookups using B-tree indexes - but I'm open to other solutions, if for example there's an index type that neatly solves this precise problem better than a bunch of B-tree indexes would.)

How many B-tree indexes do I need to support lookups by any subset of n columns

At least for Oracle (12.x) and Postgres the answer is: n

That is: one index for each column. Both DBMS are able to combine multiple indexes for a single query. In older Oracle versions you would need to use a single bitmap index on all columns. Oracle 12.1 can do a bitmap "scan" on the fly (don't know if that was already possible in 11.x - I don't have an 11.x installation to test).

Test setup:

create table idx_test (a int, b int, c int, d int, e int);
create index idx_a on idx_test(a);
create index idx_b on idx_test(b);
create index idx_c on idx_test(c);
create index idx_d on idx_test(d);

Now populate the table with million rows and random values for each column. I did this in Postgres using:

insert into idx_test (a,b,c,d,e)
select (random() * 10000 + 1)::int,
       (random() * 100000 + 1)::int,
       (random() * 1000000 + 1)::int,
       (random() * 100000 + 1)::int,
       (random() * 10000 + 1)::int
from generate_series(1,1000000);

Then copied the rows over to the Oracle server.

The following query

select *
from idx_test
where b = 42 or a = 24 or c = 100;

shows the following execution plan in Postgres:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on idx_test  (cost=6.25..166.36 rows=111 width=20) (actual time=0.032..0.244 rows=113 loops=1)
  Recheck Cond: ((b = 42) OR (a = 24) OR (c = 100))
  Heap Blocks: exact=113
  ->  BitmapOr  (cost=6.25..6.25 rows=111 width=0) (actual time=0.020..0.020 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_test_b_idx  (cost=0.00..1.96 rows=11 width=0) (actual time=0.009..0.009 rows=11 loops=1)
              Index Cond: (b = 42)
        ->  Bitmap Index Scan on idx_test_a_idx  (cost=0.00..2.27 rows=98 width=0) (actual time=0.007..0.007 rows=100 loops=1)
              Index Cond: (a = 24)
        ->  Bitmap Index Scan on idx_test_c_idx  (cost=0.00..1.93 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=1)
              Index Cond: (c = 100)
Planning time: 0.077 ms
Execution time: 0.270 ms

You can see that the corresponding index for each column was used. The execution plan for Oracle 12.1 is nearly identical:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2332290563

--------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     43 |  2795 |   740   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| IDX_TEST |     43 |  2795 |   740   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS       |          |        |       |            |
|   3 |    BITMAP OR                        |          |        |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  5 |      INDEX RANGE SCAN               | IDX_C    |        |       |     3   (0)|
|   6 |     BITMAP AND                      |          |        |       |            |
|   7 |      BITMAP CONVERSION FROM ROWIDS  |          |        |       |            |
|*  8 |       INDEX RANGE SCAN              | IDX_A    |        |       |     3   (0)|
|   9 |      BITMAP CONVERSION FROM ROWIDS  |          |        |       |            |
|* 10 |       INDEX RANGE SCAN              | IDX_B    |        |       |     3   (0)|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("C"=100)
   8 - access("A"=24)
  10 - access("B"=42)

A query with only two columns will only use two indexes:

select *
from idx_test
where a = 1001 and b = 45877;

Postgres:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on idx_test  (cost=4.48..5.99 rows=1 width=20) (actual time=0.118..0.118 rows=0 loops=1)
  Recheck Cond: ((b = 45877) AND (a = 1001))
  ->  BitmapAnd  (cost=4.48..4.48 rows=1 width=0) (actual time=0.117..0.117 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_test_b_idx  (cost=0.00..1.96 rows=11 width=0) (actual time=0.075..0.075 rows=11 loops=1)
              Index Cond: (b = 45877)
        ->  Bitmap Index Scan on idx_test_a_idx  (cost=0.00..2.27 rows=98 width=0) (actual time=0.038..0.038 rows=93 loops=1)
              Index Cond: (a = 1001)
Planning time: 0.150 ms
Execution time: 0.157 ms

And Oracle:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3618970768

--------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     78 |  5070 |    22   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| IDX_TEST |     78 |  5070 |    22   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS       |          |        |       |            |
|   3 |    BITMAP AND                       |          |        |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  5 |      INDEX RANGE SCAN               | IDX_A    |     43 |       |     3   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  7 |      INDEX RANGE SCAN               | IDX_B    |     43 |       |     3   (0)|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"=1001)
   7 - access("B"=45877)

The indexes are also used for an OR condition:

select *
from idx_test
where a = 1001 or b = 45877;

Postgres:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on idx_test  (cost=4.29..161.31 rows=109 width=20) (actual time=0.053..0.240 rows=104 loops=1)
  Recheck Cond: ((a = 1001) OR (b = 45877))
  Heap Blocks: exact=104
  ->  BitmapOr  (cost=4.29..4.29 rows=109 width=0) (actual time=0.038..0.038 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_test_a_idx  (cost=0.00..2.27 rows=98 width=0) (actual time=0.030..0.030 rows=93 loops=1)
              Index Cond: (a = 1001)
        ->  Bitmap Index Scan on idx_test_b_idx  (cost=0.00..1.96 rows=11 width=0) (actual time=0.007..0.007 rows=11 loops=1)
              Index Cond: (b = 45877)
Planning time: 0.111 ms
Execution time: 0.273 ms

Oracle:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2853963857

--------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     62 |  4030 |   879   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| IDX_TEST |     62 |  4030 |   879   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS       |          |        |       |            |
|   3 |    BITMAP OR                        |          |        |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  5 |      INDEX RANGE SCAN               | IDX_A    |        |       |     3   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  7 |      INDEX RANGE SCAN               | IDX_B    |        |       |     3   (0)|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"=1001)
   7 - access("B"=45877)

A quick test with SQL Server 2016 shows that it does essentially the same thing:

StmtText                                                                                | StmtId | NodeId | Parent | PhysicalOp   | LogicalOp  | Argument
-------------------------------------------------------------------------------------------------------------------------------------+--------+--------+--------+--------------+------------+------------------------------------------------------------------------------------------------------
select * from idx_test where b = 42 and a = 24                                                                                |      1 |      1 |      0 |              |            | 1
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))                                                                          |      1 |      2 |      1 | Nested Loops | Inner Join | OUTER REFERENCES:([Bmk1000])
       |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]), RESIDUAL:([Bmk1000] = [Bmk1000]))                                     |      1 |      4 |      2 | Hash Match   | Inner Join | HASH:([Bmk1000])=([Bmk1000]), RESIDUAL:([Bmk1000] = [Bmk1000])
       |    |--Index Seek(OBJECT:([TestDB].[dbo].[idx_test].[idx_b]), SEEK:([TestDB].[dbo].[idx_test].[b]=(42)) ORDERED FORWARD)     |      1 |      5 |      4 | Index Seek   | Index Seek | OBJECT:([TestDB].[dbo].[idx_test].[idx_b]), SEEK:([TestDB].[dbo].[idx_test].[b]=(42)) ORDERED FORWARD
       |    |--Index Seek(OBJECT:([TestDB].[dbo].[idx_test].[idx_a]), SEEK:([TestDB].[dbo].[idx_test].[a]=(24)) ORDERED FORWARD)     |      1 |      6 |      4 | Index Seek   | Index Seek | OBJECT:([TestDB].[dbo].[idx_test].[idx_a]), SEEK:([TestDB].[dbo].[idx_test].[a]=(24)) ORDERED FORWARD
       |--RID Lookup(OBJECT:([TestDB].[dbo].[idx_test]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)                          |      1 |     16 |      2 | RID Lookup   | RID Lookup | OBJECT:([TestDB].[dbo].[idx_test]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD

Is this the most efficient index access possible? Probably not, but it is most probably the best compromise between indexing overhead and query flexibility.


Does the spec permit column-count mismatch on INSERT? I'm just wondering if the spec permits excluding the column-list clause in an INSERT statement if you're inserting in fewer columns than the table provides. I see PostgreSQL does, and MySQL and SQL Server do not.

With PostgreSQL, (works)

# CREATE TABLE t (a int, b int);
CREATE TABLE
# INSERT INTO t VALUES (42);  -- (b is set to DEFAULT)
INSERT 0 1

With SQL Server, I get

Msg 213 Level 16 State 1 Line 1
Column name or number of supplied values does not match table definition.

With MariaDB, I get

Column count doesn't match value count at row 1

What's the right behavior if there is a column-count mismatch?

Question inspired by the response and comments to this question here

Draft documents can be found at:

http://www.wiscorp.com/sql20nn.zip

The part that is of interest for this question is:

7IWD2-02-Foundation-2011-12.pdf

At first I could not find any support for leaving out the column specification at all. However, in 14.11  the BNF looks like:

<insert statement> ::=
INSERT INTO <insertion target> <insert columns and source>
[...]
<insert columns and source> ::=
    <from subquery>
  | <from constructor>
  | <from default>

<from subquery> ::=
  [ <left paren> <insert column list> <right paren> ]
    [ <override clause> ]
    <query expression>

<from constructor> ::=
  [ <left paren> <insert column list> <right paren> ]
    [ <override clause> ]
    <contextually typed table value constructor>

<insert column list> ::=
    <column name list>

I.e. the "column name list" is contained within square-brackets so there may or may not be a column specification. However, in the text under Syntax Rules it mentions:

7) If the <insert column list> is omitted, then an
   <insert column list> that identifies all columns of T in the
   ascending sequence of their ordinal positions within T is implicit.

So, it seems that if the column list is omitted, all columns are assumed and that they should be interpreted from left to right. My guess is that PostgreSQL is too generous in this regard.

For clarity I think it is a good idea to always declare the column-list (except for ad-hoc situations). I can add Db2 (10.5) to the products that require a column-list for a partial tuple:

db2 "INSERT INTO t VALUES (42)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0117N  The number of values assigned is not the same as the number of specified or implied columns or variables.  SQLSTATE=42802


How can I reliably track stored procedure ( and functions ) use over time? I have a lot of stored procedures and functions in database, many are not used any more and I want to remove them. How can I find them reliably ?

I have seen answer that suggests looking into plan cache. That's great start but there is note that retention of these plans has many factors involved. What are those factors ? I would not like to delete procedure of function just because plan cache is missing ( for instance: does option recompile delete plan ? ).

I have also considered using profiler ( running it for 2 months does not sound like a good idea ) or perhaps even making some script to change all procedures to write when they are being used ( that would not help with functions though ).

If it is not possible to find them this way, is there another way? Do you know of any other tool, besides profiler ? ( Log analysis would be too slow as I would need to go over 2 months of data ).

I need this data to be reliable as possible.

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

Auditing Table

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

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

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

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

Gotchas

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


MySQL AlphaID Function While trying to optimize a few queries to not do with PHP something I could bring ready-to-use from database, I tried to convert two routines originally created in PHP to encode an integer in its AlphaID representation and then back, from string to integer.

Let's take a deep breath before this can be considered as a Stack Overflow question, I asked before posting

I tried to port them by myself but there were a few problems:

The original routine, in PHP, was able to encode correctly up to the maximum possible value fo a 32bits integer (2147483647), but for some reason my port stops earlier. I wasn't capable to debug further but in some moment after 2000000000 doesn't encode anymore.
Up to the point in which the function works, the encoding is always "one ahead', for example, encoding the integer 2, instead of returning the expected baaac is resulting in baaad, the equivalent of the integer 3 when encoded

I've got suggestions to use AES_ENCRYPT() and its counterpart to decrypt, but that's not the purpose because what I really need, as a requirement of the project, is to display something better characterized as a PRIMARY KEY in a textual representation of letters and number (only!)

I also had suggestions to use hashing functions and I tested MD5(), SHA1() and all combinations of SHA2() but all of them result in very long strings which would be difficult to display on web environment.

And, of course, being able to do such conversion straight in the database would allow me to change INT for BIGINT and have up to 9,223,372,036,854,775,807 records.

So, that said, is there a way to represent integer as AlphaID with MSQL? Or something similar then, I'm open to suggestions.

If not something built-in, and maybe this could be considered as off-topic, how could I fix the implementations I made?


Find current users DDL statement running, Oracle 11g I have created a database trigger (oracle 11.2.0.4 standard edition on windows 2008 R2) to intercept any drop or truncate statements, eg

drop table abc123;

and that will raise an exception to prevent any drop statements

As part of that trigger I would like to log the statement that is being called so I can a) see who is trying to do what that they shouldn't and b) allow certain drop and truncate commands to execute

I've tried looking in v$sql but I don't think DDL statements are run this way, eg

    select /*ignore this*/ nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' username,
        ses.sid,
        ses.machine,
        replace(sqlt.sql_text,chr(10),'') as stmt,
        ltrim(to_char(floor(ses.last_call_et/3600), '09')) || ':'|| ltrim(to_char(floor(mod(ses.last_call_et, 3600)/60), '09')) || ':'|| ltrim(to_char(mod(ses.last_call_et, 60), '09')) as runtime
    from v$session ses
        join v$sql sql on (sql.address = ses.sql_address and sql.hash_value = ses.sql_hash_value and sql.sql_text not like 'select /*ignore this*/%')
        join v$sqltext_with_newlines sqlt on (sqlt.address = ses.sql_address and sqlt.hash_value = ses.sql_hash_value)
    where ses.username = user
    order by sql.hash_value, sqlt.piece

Is there another view I can get currently executing DDL statements from?

Thanks

You really need the statements? The drop / truncate commands don't have that many options. You can get this info from these functions in the trigger (that I think is enough for what you need):

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner

Ps: if you want to capture the statements, I would recommend you choose AUDIT feature available in the database, instead of using triggers.


Setting up AG listeners I am relatively knew to MSSQL so please bare with me.
My infrastructure is currently in AWS.
I have 3 servers running MSSQL. I have a primary replica and two secondary replicas. All three servers are in 1 AG group and there is 1 database being replicated.
Server 1 is in AZ A (Subnet A)
Server 2 is in AZ A (Subnet A)
Server 3 is in AZ B (Subnet B)
All are set for automatic sync failover. I am working on adding a listen for these 3 replicas.
As far as I can tell you can only add a static listener with the two different subnets.
So lets say I add an IP for Server 1 and Server 3. It wont let me add it for Server 2 because its 1 per subnet.
How would this work. Am i missing something here? Am I doing something wrong?

How would this work. Am i missing something here? Am I doing something
wrong?

You don't need another IP for Server 2 because it is in the same subnet as Server 1. Your listener requires 1 valid IP address for each subnet that a cluster node is in. In your case, you have 3 servers, but only 2 subnets so you only need 2 IP addresses for the listener.
Only one IP is ever 'active' for the listener so even if the AG fails over from Server 1 to Server 2, the IP address for the listener will stay the same, but Server 2 will begin listening on that IP.


Recover Mysql data from formated hard disk One of my machine got crashed last day and i ended up reinstalling windows on that machine. It had a mysql server in it. Is there a way to recover mysql data from that machine now . There are couple of tables which has some important data for which backup was not there.

any help would be greatly appreciated.

Before you do anything make sure nothing can overwrite data on the disk. stop all writes to the disk ASAP. Unmount, take it out of a server - whatever is applicable and feasible. Plug the disk in a 64bit Linux machine.

Then you need to take an image from this disk. I prefer dd for the job. It's available everywhere, stores the image as it is.

 dd if=/dev/sdb of=/path/on/sda/faulty_disk.img  conv=noerror

Where /dev/sdb is the disk with data and /path/on/sda/faulty_disk.img is a path on another disk, big enough to store the image. conv=noerror will ignore disk read errors if you get any.

If you prefer to store the image remotely - check out https://twindb.com/take-image-from-corrupted-hard-drive/ , it describes more options.

Then download and compile Undrop-for-InnoDB.
Then follow steps from tutorial:

You need to find InnoDB pages on the image. The stream_parsertool does it:

./stream_parser -f /path/on/sda/faulty_disk.img

The result of the tool will be a directory pages-faulty_disk.img that contains InnoDB pages sorted by page type and index_id. InnoDB stores tables in indexes. The table itself is stored in the PRIMARY index, secondary indexes are stored separately. To recover the data we need to fetch records from PRIMARY indexes. Now, InnoDB identifies indexes by index_id while we, humans, know table names. To find index_id for each table you will need to restore InnoDB dictionary first. You can check the blog post or script recover_dictionary.sh from the undrop-for-innodb toolkit.

Now, let's say you found index_id for each table and there is a respective index file in pages-faulty_disk.img/FIL_PAGE_INDEX/<index_id>.page.

To extract records from the index you need to know the table schema, because InnoDB doesn't store the schema in the index. I hope you have the schema either from old backups, or maybe from the installation script. Worst case you can try to restore the schema from InnoDB dictionary.

When you have the schema and index_id you can generate text dumps from the index.

./c_parser -6f pages-faulty_disk.img/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql

Obviously, sakila.actor is just an example.
Then review the text dump dumps/default/actor and you can load it with an SQL command stored in dumps/default/actor.sql.
The tool's page also refers to some other tutorials which may be useful. Data recovery is quite unpredictable so you have to get familiar with the tool and process.

Hope this helps.


Was I right with locking? Regarding the this question I want to ask

Comparing two queries:

(1)
DELETE dbA.dbo.tableA
FROM dbA.dbo.tableA a WITH(NOLOCK)
JOIN dbB.dbo.tableB b WITH(NOLOCK)
  ON
      b.colA = a.colA
  AND b.colB = a.colB

and

(2)
DELETE FROM dbA.dbo.tableA
WHERE EXISTS
(
  SELECT *
  FROM dbB.dbo.tableB b WITH(NOLOCK)
  where
      b.colA = dbA.dbo.tableA .colA
  AND b.colB = dbA.dbo.tableA .colB
)

it is clear, that the queries do the similar work, if we not consider concurrent writes which can lead to dirty reads.

BUT i have a doubt

was I right with the following comment?

  note, that there will be placed a shared (upgradeable?) lock @ tableA from the very
  beginning of the statement (2), instead of two NOLOCK tables in join (1),
  which will place the first lock - UX lock only after there will be
  found the first row to delete

No.

At least in my testing both of them have identical execution plans and identical locking behaviour. Both place an IX lock on tableA and a schema stability lock on TableB straight away and then follow the same behaviour in doing the clustered index scan on TableA taking IU locks on the page, U locks on the key, then in the event of a match converting the page lock to IX then the key lock to X prior to deleting the row. This is exactly the same pattern as if the NOLOCK hint on tableA is removed entirely so it is pointless in this case.

My original results had some differences between the two queries in that the Join version has a series of apparently aquiring and immediately releasing schema stability locks on TableA that didn't show up in the EXISTS version. That seems to be related to whether the "Include Actual Execution Plan" option is enabled in SSMS. Today if I have this option enabled this series appears in the locking info for both of them so not sure why originally it only appeared in one. Maybe some timing issue.

You can see this locking information using TF1200 as below (with "Include Actual Execution Plan" option disabled).

Code

SET NOCOUNT ON;

CREATE TABLE tableA(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

CREATE TABLE tableB(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

SELECT OBJECT_ID('tableA') AS tableA,
       OBJECT_ID('tableB') AS tableB;

INSERT INTO tableA VALUES (0,0),(1,1),(2,2),(3,3);
INSERT INTO tableB VALUES (1,1),(2,2),(4,4),(5,5),(6,6),(7,7);

SELECT *, %%lockres%%  AS lockres
FROM tableA

DECLARE @JoinSQLNoLockTableA nvarchar(max) = N'
DELETE tableA
FROM tableA a WITH(NOLOCK)
JOIN tableB b WITH(NOLOCK)
  ON
      b.colA = a.colA
  AND b.colB = a.colB;'

DECLARE @ExistsSQL nvarchar(max) = N'
DELETE FROM tableA
WHERE EXISTS
(
  SELECT *
  FROM tableB b WITH(NOLOCK)
  where
      b.colA = tableA.colA
  AND b.colB = tableA.colB
);'

DECLARE @JoinSQLWithoutNoLockTableA nvarchar(max) = REPLACE(@JoinSQLNoLockTableA,'tableA a WITH(NOLOCK)', 'tableA a')

/*Run the commands first with the trace flag off so the locking
info is less full of irrelevant stuff about plan compilation */
EXEC (@JoinSQLNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@ExistsSQL);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@JoinSQLWithoutNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);

/*Run with TF1200 on*/

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLNoLockTableA - Start';
EXEC (@JoinSQLNoLockTableA);
PRINT '@JoinSQLNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@ExistsSQL - Start';
EXEC (@ExistsSQL);
PRINT '@ExistsSQL - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLWithoutNoLockTableA - Start';
EXEC (@JoinSQLWithoutNoLockTableA);
PRINT '@JoinSQLWithoutNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

DROP TABLE tableA,
           tableB;

ObjectIds

tableA      tableB
----------- -----------
308196148   372196376

LockRes

colA        colB        lockres
----------- ----------- ------------------------------
0           0           (00009620dd9a)
1           1           (02006d47cbee)
2           2           (040060eff172)
3           3           (06009b88e706)

Output (All three queries)

Process 52 acquiring Sch-S lock on OBJECT: 23:372196376:0  (class bit0 ref1) result: OK

Process 52 acquiring IX lock on OBJECT: 23:308196148:0  (class bit2000000 ref1) result: OK

Process 52 acquiring IU lock on PAGE: 23:1:14144 (class bit0 ref1) result: OK

Process 52 acquiring U lock on KEY: 23:72057594051231744 (00009620dd9a) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (00009620dd9a)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit0 ref1) result: OK

Process 52 acquiring IX lock on PAGE: 23:1:14144 (class bit2000000 ref0) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (02006d47cbee)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (040060eff172) (class bit0 ref1) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (040060eff172) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (040060eff172)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (06009b88e706) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (06009b88e706)

Process 52 releasing lock reference on PAGE: 23:1:14144

Process 52 releasing lock on OBJECT: 23:372196376:0


Galera Arbitrator system requirements compared with the actual Galera MariaDB node requirements Right now our setup is CloudFlare > 1x HAProxy (Dallas,TX), 3x App servers (Dallas,TX), 3x MariaDB nodes in a Galera multi-master cluster (Dallas,TX).
The App servers auto-scale horizontally to handle whatever traffic gets thrown at them. HAProxy and DB servers are scaled up when needed.
The App servers also maintain individual SQL read caches to ease the load on the DB.

Our main selling point is keeping our product online no matter what happens, as our solution caters to government emergency services, and since we're a start-up, our budget is really tight.

It was unexpected that our current cloud provider (which we cannot change in the following months) is experiencing DDOS for around 30minutes a month. For our situation not even one minute of downtime is acceptable.

So I am thinking of going with something like CloudFlare > 3x HAProxy (Dallas,TX; Freemont,CA; Newark,NJ) > 6x App servers (Dallas,TX; Freemont,CA; Newark,NJ) > 3 Galera Multi-master + 2 Galera Arbitrator (Dallas,TX; Freemont,CA; Newark,NJ; Atlanta,GA; San Francisco,SF).

This will increase our costs quite a bit, and that is why I want two Galera Arbitrators if I can skimp on costs while at the same time increase stability.

My main question is, what are the resource requirements for Galera Arbitrator, and would it be worth having three MariaDB nodes and two Galera Arbitrator nodes, instead of only three MariaDB nodes in the cluster?
Another way to put it is, is it worth using garbd on setups over three nodes in size?

Provided that route-wise our HAProxy servers will choose the App servers in the same region, which subsequently choose the DB server in the same region. The choices are based on a blend of connect time and server load.

Overall I'd say its best to widen the flow control and send/recv windows, and time-outs between nodes.

If you get down to one node, you can use set global wsrep_provider_options="pc.bootstrap=TRUE" to turn it into an active node without garbs. Lacks automation however is more durable overall.

The nature of the DoS might have an impact in the design. If the app server for the site is down is there sufficiently reduce load on the galera node? Or is the network throughput/latency compromised?

Recommending sticking with a 3 node cluster initially. Its a big step up from a one site.


Firebird: Access Denied When ever I try to create a database with SQI Tools for firebird I receive this error...

SQL> create database 'C:\Remake'
CON> user 'sysdba' password 'masterkey';
Statement failed, SQLSTATE = 08001
I/O error during "CreateFile (create)" operation for file "C:\REMAKE"
-Error while trying to create file
-Access is denied.
SQL>

I went to the C:\Program Files\Firebird\Firebird_3_0 directory in CMD by using the cd command and then typed isql. I have also tried this when running cmd as the Administrator. I have googled this issue many times and all I get back is responses for Linux to do with the terminal, can anyone help me with Windows?

I am a first time firebird user, can anyone help me with this?

By default you are not allowed - by Windows - to create files into the C:\ drive root, except when running as Administrator. Make sure you create a folder and create the database file there. Also note that it is customary (but not required) to give Firebird databases the extension .fdb.

Also make sure that C:\Remake does not already exist as a folder (directory), see also https://stackoverflow.com/questions/42280679/firebird-permission-denied


I have very bad news for you.
Unfortunately, that is the correct behavior.
The MySQL Query Optimizer tends to get sidetracked very easily when using a FULLTEXT index.
I also have very good news for you.
I wrote about this in StackOverflow on how to get around it.
You may have to nest your original query in a subquery and return the MATCH function as a column. Then, evaluate the MATCH column outside of the subquery.
Instead of your query
select * from seeds  WHERE  MATCH(text) AGAINST ("mount cameroon" IN BOOLEAN MODE) = 4;

you must refactor it into something like this:
SELECT B.* FROM
(
    SELECT id,MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE) score
    FROM seeds
    WHERE MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE)
) A
INNER JOIN seeds B USING (id)
WHERE A.score = 4;

Give it a Try !!!


SQL Server : How to make "Update Statistics" faster? We have a table with a 1 column Primary Key on and
                      an UNIQUE Non-Clustered Index with 16 columns.

This table has more than 20695780 Rows and on every day new records inserted between 30000 and 35000.

After insertion process, we have many other processes those refer this table.

On every day, before proceeding further, after insert we do an Update Statistics with 30 Percent Sample on this table.

After this when we do rest processes then we are not facing problem and everything goes smoothly.

Now the problem is if we do Update Statistics then it is taking more than 15 Minutes.

We need a solution: -
    To make this "Update Statistics" faster OR
    Can we perform a parallel Update Stats on this table with other processes OR
    Instead of "Update Statistics" can we go for REBUILD INDEXES (need to know it will update all Statistics or not)? OR
    Any other solution by which we can save this 15 minutes.

Please advice.

I would start by seeing if you have any statistics that aren't being used.  Unfortunately that isn't possible directly but you could start by looking at your indexes and seeing if any of them are worth getting rid of.  If you can eliminate some of your indexes and/or statistics that will improve your run time for the UPDATE STATISTICS.  You can find out which indexes are actually in use by looking at sys.dm_db_index_usage_stats.

Here are a couple of links I used for reference although you can do a quick google search and find dozens.

SQL SERVER – Identifying Statistics Used by Query
SQL SERVER – 2008 – Find If Index is Being Used in Database

Also as a general suggestion if you partition your table using a "partitioned view" so you have "old data" and "new data" then you can schedule your statistcs updates separetly.  For example let's say you partition your data by year.  You run the daily UPDATE STATISTICS on just the current years partition.  If there are no updates on previous years data then you don't have to worry about statistics updates on those partitions at all.  If there are updates on previous years data you can either

Let them update themselves using the auto update functionality or
Update them say once a week
And/or update them during an off hour time.


How to change "deployment target version" on SSIS 2019? I am experiencing the following errors when using the "Transfer SQL Server Objects" task in SSIS on Visual Studio 2019:

An exception occurred in SMO
Value cannot be null. Parameter name: server

These errors have previously been described in SSIS 2017 and could apparently be solved by changing the deployment target version to 2016 as described in these links:

https://social.msdn.microsoft.com/Forums/SECURITY/en-US/6096339d-766b-45ab-9e97-e8b49812aaf5/object-transfer-error-an-exception-occurred-in-smo-value-cannot-be-null-parameter-name-server?forum=ssdt

https://stackoverflow.com/questions/48145026/why-does-ssis-transfer-object-stage-return-smo-failure

So as directed in the linked pages, I opened the "Deployment Property Pages" window and looked in "Configuration Properties" for the "General" option, but it is not present, all that is present is "Configuration" and it does not have a "Deployment Target Version" option.

Is there any way I can change the Deployment Target Version (or any other ideas about how best to resolve this error) in SSIS 2019?

I've tried Google but can't find anything relevant!

Be sure to go to the Project properties, not the solution properties. See image below.


dev.mysql.com has 2 different installers to download, which do I need? If you go to http://dev.mysql.com/downloads/windows/installer/ there are 2 installers you can download. They both are named exactly the same and have the same version number. There is nothing on the download page to indicate how the 2 versions are different. Does anyone know?

Look up.

"Choosing the right file:

If you have an online connection while running the MySQL Installer, choose the mysql-installer-web-community file.
If you do NOT have an online connection while running the MySQL Installer, choose the mysql-installer-community file."


MySQL - Server collation utf8_unicode_ci vs table collation utf8_bin: compatibility and performance I'm not sure whether this belongs to SE DBA, but it sounds more logical ...

Our MySQL 5.5.32 on Ubuntu 12.04 64 uses utf8_unicode_ci for the Server collation and some tables, like phpBB3, uses utf8_bin for all of their tables. This has been since start.

I read utf8_bin is (at least in theory) faster since no conversion and/or normalization is done, but are these quite different UTF8 thingies fully compatible ? If they are, is there performance hit or improvement ? The tables I'm planning switching from utf8_unicode_ci to utf8_bin contain integers and dates, so not having to bother with much larger UTF8 should drop at least 1ms when dealing with these large ASCII-only tables.

Before I had all tables not already using UTF8 Unicode Ci or UTF8 Binary I was using ASCII Binary for these tables, but decided to switch to UTF8 Unicode Ci for full compatiblity with Server collation (and of course a bit of performance improvement).

By using "UTF8 thingies" I think that the problem here would be concepts. Let's review them:

Encoding (badly called character set in MySQL): utf-8 (utf8 and utf8mb4), utf-16 (utf16), 7-bit ASCII (ascii)... Those are the actual formats in which the characters are stored, transmitted or converted. They can use (in MySQL, from 1 byte to 4, and some are dynamic, while others are fixed in size)
Collation: these are the default algorithms that, when nothing is explicitly said, MySQL will use to compare 2 strings. the _bin one basically compare bit-to-bit, while the _ci and _cs will compare for a particular language, in a case-insensitive or case-sensitive way.

Now we have presented the concepts, let's apply them: First, you should use the most appropriate format for your application. If your application needs to store international names, you definitely want to use a multi-byte encoding. And given that even the most local business are globalised something like utf8 is practically a must. However, there are some cases in which has a lot of sense to use different encodings. Storing something like a car plate, may only require ascii, which not only is cheaper in size, but would prevent from other strange characters to get in. Other examples include Wikipedia, a high-traffic site that has to support perfectly tons of different languages. They decided (I suppose partly to overcome MySQL limitation, partly for efficiency) to store all the pages without encoding, in pure blobs.

Regarding efficiency: having the same encoding in all the tiers of your application is usually the best policy: you avoid unnecessary conversions back and forth. Smaller and fixed size encodings are supposed to be faster but never compromise functionality. Sometimes the bottleneck on encodings can be a slow conversion, sometimes on storing many zeros. Compression can always help with the last one, specially with text.

And now about collation: basically use the one you will need by default- it will affect the string comparisons and order of the sorts. Different collations do not change the bits for each row, but it (may) change the order between them (is 'a' before 'A' or are they equivalent?). Although you can force a different collation for particular queries manually:

mysql> SELECT mycolumn FROM mytable WHERE mycolumn LIKE 'a%' COLLATE utf8_bin;
mysql> SELECT mycolumn FROM mytable ORDER BY mycolumn COLLATE utf8_bin;

Why you may have seem many people saying that the _bin version may be faster? Well, because it only has to compare bits, and not have into account the particularities of each language. If you are not going to ever do comparisons in which a == A and even a == A == á == ä, use the binary collation. But in those cases, you probably do faster by not storing text in char/varchar/text, but in the binary equivalents: varbinary/binary/blob. Of course, if you want to get the advantages of storing characters and not bytes, like getting those comparisons done automatically done for you, use utf8_general_ci or utf8_unicode_ci, which will work for most languages well.

Given that most of your data is ASCII, the size in utf8 shouldn't have changed much. Check your performance- I have suffered some weird utf8 bugs, but usually this shouldn't be a common problem unless having to deal with lots of 4-byte utf-8 characters. Given that most of your data is ascii-like, and that cpu is usually not the bottleneck (and IO will probably won't change), you may not see any change, so use the combination that better fits your needs. But test it before and after to see the change.

Remember that there are several configurations on mysql: the character set for the connections, the one for the table storage and the one in which they are sent back to the client. You can check both the encoding and the collations of a particular session by doing:

mysql> show session variables like '%character\_set\_%';
mysql> show session variables like '%collation\_%';

Some of them are not that important, like the internal format of the identifiers.

Finally, remember that the configuration of the charset and collation for databases and tables are defaults, and that changing those don't modify the actual column encoding. Also, be careful when ALTERing the tables, as you block writes to that table.


select only one element from an priority ordered group with limits? This question searches for an answer in pure sql (no db-server specific code)

-edit-
Due comments/answers:
"pure sql" - SQL92/99 because this works on all servers. I do not only use this code on mysql, this was just the first one I tried. I have to support Microsoft SQL Server (2000!), h2, PostGRE SQL and MySQL Server but the code should work on others too (db server independent). I'm sorry if "pure sql" was not detailed enough. Next time I'll specify requirement of the revision of SQL (3/4, 92/99)

I have to be backwards compatible.
And this is why I did not specify a db server. This was on purpose.
-end-of-edit-

I try to get all values from a table with given limits.
The data i have for the query is: possible locales and a max_priority

this is the table to work with

SELECT * FROM i18n

.

id|featureName|locale     |priority|text
--+-----------+-----------+--------+----
 1| type      |         en|       1|father
 2| type      |         de|       2|Vater
 3| type      |      de_AT|       3|Papa
 4| type      | de_AT_Wien|       4|Oida
 5| firstName |         en|       1|first name
 6| firstName |         de|       2|Vorname
 7| lastName  |         en|       1|last name
 8| lastName  |         de|       2|Nachname
 9| lastName  |         fr|       2|nom de famille
10| firstName |         fr|       2|prénom

so for example i have en, de, de_AT and a max_priority of 3 (which is the priority of de_AT)
i expect this as result: (the order of these results are not important!)

id|featureName|locale     |priority|text
--+-----------+-----------+--------+----
 3| type      |      de_AT|       3|Papa
 6| firstName |         de|       2|Vorname
 8| lastName  |         de|       2|Nachname

i tried to solve this with an left outer join
this is the best result i was able to get:

SELECT DISTINCT nls1.*
FROM i18n nls1
LEFT OUTER JOIN i18n nls2
ON (
    nls1.featureName = nls2.featureName
    and nls1.priority > nls2.priority
)
WHERE nls2.priority<=3
AND (nls1.locale='en' OR nls1.locale='de' OR nls1.locale='de_AT')
GROUP BY featureName
ORDER BY featureName, priority DESC

but this selects those rows:

id|featureName|locale     |priority|text
--+-----------+-----------+--------+----
 2| type      |         de|       2|Vater
 6| firstName |         de|       2|Vorname
 8| lastName  |         de|       2|Nachname

another try was

SELECT nls2.*
FROM   i18n nls2
       JOIN (SELECT Max(priority) max_priority,
                    featurename,
                    priority
             FROM   i18n nls
             WHERE  ( locale = 'en'
                       OR locale = 'de'
                       OR locale = 'de_AT' )
                    AND priority <= 3
             GROUP  BY featurename) nls3
         ON nls3.priority = nls2.priority
            AND nls3.featurename = nls2.featurename

but this one only returns the en version...

id|featureName|locale     |priority|text
--+-----------+-----------+--------+----
 1| type      |         en|       1|father
 5| firstName |         en|       1|first name
 7| lastName  |         en|       1|last name

NOTE: i'll update the question depending on the answers.

The main problem seems that i need a group by an column but the group also needs a order by?

Without window functions, the query will be complicated. One way to solve the problem in MySQL:

SELECT
    n.*
FROM
    ( SELECT DISTINCT featureName
      FROM i18n
    ) AS d
  JOIN
    i18n AS n
  ON  n.id =
      ( SELECT i.id
        FROM i18n AS i
        WHERE i.featureName = d.featureName
          AND i.locale IN ('en', 'de', 'de_AT')
          AND i.priority <= 3
        ORDER BY i.priority DESC
        LIMIT 1
      )
ORDER BY
    n.featureName ;           -- or whatever you like, eg:  BY n.id

Also note that the above query will fail it is in SQL Server (which uses TOP, not LIMIT) and possibly fail in other DBMS as well.


What is the best practice for backing up a database in Simple mode? I am trying to create a SQL Job for backing up my database.  I want to have:

A weekly full backup
A daily differential backup
Something that will clean up backups that are older that 14 days

I started doing this with the Maintenance Plan, but decided managing it through jobs would be more flexible.

My questions are: how do I name the backup files?  Do I name them with a date naming convention and use the same convention to delete old files?  Is there an easier way?

If I'm over thinking this and should be using a Maintenance Plan, I would appreciate that answer as well.

I'd recommend Ola's scripts for SQL Server maintenance - backups, re-indexing, checkDB, house-keeping, everything! It takes care of naming conventions too! They are very clever, and award winning too!!!! :)

Personally, if your databases are small, I'd keep to a full daily backup schedule. I believe Microsoft recommend that as well - simplicity is best approach.

Ola's scripts:
http://ola.hallengren.com/


moving table to another tablespace in primary server oracle 11g What will be the effect on dataguard configuration when moving one table to another tablespace using the impdp remap_tablespace parameter?

In our production environment we have setup dataguard and I am curious that if move a table in primary what will be effect on dataguard or what steps should I do before the moving table.

If you have configured Data Guard properly, this will not cause any problem. And by 'properly', I mean FORCE LOGGING is enabled. Data Pump can use direct path load (in which case there is minimal redo  generated), but there are several conditions that will prevent it:

Situations in Which Direct Path Load Is Not Used

Anyway, to check FORCE LOGGING:

select force_logging from v$database;

If the result is NO, you can enable it with:

alter database force logging;

Note that, this should be done on the primary and all standby databases as well (just in case a role transition happens and a standby becomes primary).


Lab test database modeling help I have a database design problem which I need some help with.

I am developing a database to store some engineering lab test results. But the main problem I am running into is that each Test is completely different from each other in terms of inputs and results. For example, Test1 measures Measurement1 at three different Frequencies. Test2 measures Measurement2 at five different Temperatures.

If I am running multple Tests on sample, what is the best way to go about it?

I can create independent tables for each Test and address this in the interface code.
I can have a normalized database with Test table and Results table. Store the columns required for all the Tests in there, eventhough some will be empty for  a particular Test.

Any help on this is much appreciated. Thanks.

Neither of your approaches is good.

You have Tests that produce Results for some Measurement that is done with some Method. The Method may applies to multiple Test. (see sample Data below)

So this is my approach

Test(**TestID**, Name, Description, ...)
Measurement(**MeasurementID**, TestID[FK], Description)
Result(**Measurement[FK]**, **ResultNr**, ResultValue, Time)

Sample Data:

Test
0 | Rat in the Maze | Our rat John in the large Maze testing him find the exit
1 | Water on the oven | Boiling Water on our nuclear oven

Measurement
0 | 0 | Distance to Exit in cm
0 | 1 | Aggressiveness of the rat in Units of Aggressiveness
1 | 1 | Temperature of the water in K
2 | 1 | Energy currently consumed by nuclear oven in W
3 | 1 | Amount of water evaporated

Result
0 | 0 | 10 | 0s
0 | 1 | 12 | 2s
0 | 2 | 7  | 6s
...

Ok however this still relies on your use case. Maybe you want to keep some other table for input circumstances and map them to your results as a FK.

But note how it differs from your approach:

Number of tables independet from number of Tests, Results, Mesuret Values
No Empty rows and expandable for more Measured Values.

You can still add more information (Columns) to the Measurment table to keep track of the Device used to measure, the accuracy or whatever.


MySQL FOREIGN KEY constraint is incorrectly formed I have the following table definition:

CREATE TABLE `async_task` (
  `idasync_task` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idasync_type` int(10) unsigned NOT NULL,
  `priority` tinyint(3) NOT NULL,
  `status` enum('todo','doing','failed') NOT NULL DEFAULT 'todo',
  `iduser` int(11) NOT NULL,
  `date_added` datetime NOT NULL,
  PRIMARY KEY (`idasync_task`),
  KEY `priority_id` (`priority`,`idasync_task`),
  KEY `status_type` (`status`,`idasync_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

I've added a column to the my notification table which I want to point to async_task:

ALTER TABLE `notification` ADD COLUMN `async_task_id` BIGINT(20)

And when I add the following foreign key:

ALTER TABLE `notification` ADD CONSTRAINT `fk_notification_async_task`
    FOREIGN KEY (`async_task_id`) REFERENCES `async_task`(`idasync_task`);

I get:

ERROR 1005 (HY000): Can't create table `my_database`.`#sql-182_2d`
(errno: 150 "Foreign key constraint is incorrectly formed")

I've looked elsewhere but only find the errors as being:

The table you're referencing is not created (not the case)
The table you're referencing is not InnoDB (not the case, both notification and async_task are InnoDB)
You're not referencing the entire primary key (not the case, the only primary key is the ID column).

What else could it be?

Both the referencing and referenced columns must be of the same type (and same in this case includes the unsigned attribute).

You didn't define notification.async_task_id as unsigned, so it was created with the (default) signed. Fix that and the foreign key will raise no errors.


Best way to solve a lot of queries stuck because of no index Environment

PostgreSQL 9.4
16 GB RAM  8 Cores
max_iops: 8000

What problem I encountered
Last night our production environment database encountered a large amount of traffic, whcih caused the cpu load to approach 100% and the number of active connections approached more than 600. Most of the connections are querying the same table, and stuck there.
Then I found this table has 800,000 rows and does not have any indexes.

What I have tried
I created a index on this table without concurrently. A few minutes later, there were no more stuck queries.
What I want
Although I have solved this problem, but I think this approach is risky! Because the database server is already busy, the ddl operations may be the last straw to kill the camel. Is there any  safer and more sufficient way to solve this problem?

found this table has 800,000 rows and does not have any indexes

Is there any safer and more sufficient way to solve this problem?

Only really re-emptive work:

Proper design work up-front so there are no large tables with no indexes, or not common queries that are not well-supported by the existing indexes. This might be out of your hands if you don't work directly with the development team for the application, but as a DBA you could monitor your databases for potentially worrying structures (i.e. a table with no keys/indexes, regular slow running queries if you log those, ...). Recheck after application updates, in case a structure migration has failed silently and left indexes undefined that the developers have added.

Load testing on the application to make sure no such issues are likely (you often can't rule them out entirely, but you can certainly minimise the risk). Again, this may be out of your hands.

but I think this approach is risky

A long index build, especially if done as an offline operation so could hold up all other access to the table, could be problematic, but when this happens adding the index is the only solution. Perhaps go for an online index build if possible - that will take longer but won't completely block part of the application while it does the job.
Also, if you have a dev copy of the system, test the new indexing changes there before applying to production to avoid making a change that will take ages to apply and have little of the benefit you are needing.


Populating one foreign key based on entry of another foreign key in Access 2016 I currently have three tables as shown below:

The user enters Certificates via a form. I want CertCounty to populate based on selection of TownshipID. I've attempted the following Data Macro:

But, it is giving me this error:

I'm pretty sure my expression for the Value is incorrect as it was a stab in the dark and I had little confidence it was correct. I'm also open to other solutions, i.e. VBA, just not sure what to do as I'm still learning how to work with databases and, in particular, Access.

Just because [TownshipID] is populated (i.e. not null) and because there is a relationship defined between the two tables, these facts do not mean that the table [Townships] is open and positioned at any particular record.  You must specifically direct Access to open the table and look for the related record.  You do that in the Before Change Data Macro by using the LookupRecord action.

Look Up a Record In: [Townships]
  Where Condition: Townships.ID = Certificates.TownshipID

Within that action's group is where you place the SetField action.

Other comments:

Data Macros are best for validating data.  They also have their place in keeping tables in sync as you are doing, but these conditions should be rare, especially if your tables are properly normalized.

It appears that Certificates.TownshipID is optional and can be null.  I assume that means the county can be specified independently for the certificate when there is no township.  Correct?  If so, your technique is probably justified.  Otherwise, there would be no need to also store the county with the certificate since it could always be retrieved via the Township relationship.

Since you already have a form where Certificate data is entered, you could also place relevant code in the form's module, for example in the TownshipID_AfterUpdate() event handler (or similar method) to retrieve and update the county information.


Why does my procedure run slowly until I update statistics? I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the destination DB, and then the transform processes are run. On Tuesday (26 Nov) for no known reason, the procedure which 'shreds' the staging data for providers into the relational tables went from running approx 9 per second to one every 5½ minutes. No parts of the database, ELT process (not a typo!), database server etc were changed.

With around 800 records every night (and up to several thousand) this single step of the process has gone from taking just over a minute to potentially taking just over 3 days (this doesn't happen in practise of course because we notice and stop the long running job). This happens even if I manually run the code which is within the procedure rather than letting it run as part of SSIS, and the same happens if I run through the code for just one record rather than letting the loop go through.

By sheer chance I happened to discover that if I ran the following code against the provider staging table and the provider destination relational table, suddenly my ELT process runs as normal again and zips through everything:

update statistics tblProviderStaging with fullscan
update statistics tblProvider with fullscan

My question (sorry for the journey to get here) is why would I have to run this manually every morning? I can't even run it before the ELT as it makes no difference - only affects the process if I run it while the process is slowly churning through. Presently I am running this at 7am every morning (SSIS kicks off at 6:30). Auto Update Statistics and Auto Create Statistics are both set to 'True'.

I have never, in my limited experience, come across a scenario where I have to manually run this every morning? And the fact that I can't just run it either but have to run it while the ELT is still running? We have tried running the SSIS package from a different server, against a different destination database server (restored the DB to another DBS), have checked all indexes, I/O performance is running as normal, there is no undue server load happening while this runs (it was running about 18% capacity and nothing to speak of on any of the performance metrics).

The staging table in question has just over 1.2 million rows and uses up just over 1.5GB of space (including index size). The destination table in question has 430k rows and uses around 140 MB of space (including index size). This has not changed significantly.

If there is any more information I need to provide please let me know but I'm shooting into the dark here so I don't know what is helpful and what is just in the way. If anyone has any ideas of places and things to check to see why I am having to do this please shout!

Please note we have run checkDB which has not shown any issues in the DB.

SQL Server uses a cost based formula for determining how to satisfy queries.

"It'll cost 10 random reads in a billion row table vs 1000 sequential reads. I think I can satisfy the needs faster by doing the 1000 reads since they'll all in order" and decision made off it goes. Unfortunately, when the engine opens the data file to find those 1000 sequential rows, the data has been updated and instead of living in there, there's simply a pointer saying "sorry we had to move, try this address in the next state" And you now have 1000 forwarding rows to contend with. The database engine can't go back and say let's try re-evaluating this costing metric because those 10 reads sound a lot better.

That's probably what is happening (simplified) with your data. The statistics on your data distribution are all out of whack after your ELT processing which is typical-you've just moved a pantsload of data so what was true before may not be as true now. Depending on usage, I will have an explicit call to a reindexing job (thank you Ola) after ETL is complete so that now that all my data is up to date, I need to get all the metadata the server uses up to date as well.


Why DB2 cannot allow creation of database with keyword "BACKUP" in its name? I'm using DB2 Express C v 10.5

I'm trying to create databases with names "BACKUP", "BACKUPTEST", "BACKUP_TEST" and even "TEST_BACKUP".

In all above cases DB2 throws error as below:

SQL1001N "BACKUPTEST" is not a valid database name. SQLSTATE=2E000

I tried with following command, it didn't work.

> db2 create database BACKUP

or

> db2 create database "BACKUP"

This shows an error as:

  "SQL1001N  "BACKUP_TEST" is not a valid database name.  SQLSTATE=2E000"

There's no restriction on the specific name, other than it must be 8 characters or less.  So BACKUP works just fine as a database name:

$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL10055" with level
identifier "0606010E".
Informational tokens are "DB2 v10.5.0.5", "s141128", "IP23633", and Fix Pack
"5".
Product is installed at "/opt/ibm/db2/V10.5.5".

$ db2 "create database backup"
DB20000I  The CREATE DATABASE command completed successfully.


In case of inserted a fixed value into autoincrement how to automatically skip into the next sequence value without causing insert to have an error? In my postgresql 9.2 database I created the following table:

CREATE TABLE dummy(id SERIAL PRIMARY KEY,text TEXT);

And I inserted a record via:

INSERT INTO dummy(text) VALUES ("Hello Word");

Then I inserted a record like that:

INSERT INTO dummy(id,text) VALUES (2,'SAYONARA Word');

Afterwards when I tried to insert a record like that:

INSERT INTO dummy(text) VALUES ('<3 Word');

I get the following error:

  ERROR:  duplicate key value violates unique constraint "dummy_pkey"
  DETAIL:  Key (id)=(2) already exists.

But when I reinsert the value:

INSERT INTO dummy(text) VALUES ('<3 Word');

No error is thrown.

So as I see the potgresql autoincrements a value for id via an internal counter regardless whether the value is sucessfully inserted or not. Also the error has occured because postgresql does not "punches holes" on exising counter id.

So how I can tell postgresql if the autoincrement value exists just to skip into the next one and avoid using the existent autoincrement value. (By existing I mean values that have already set as primary key).

You can use the following statement to synchronize the sequence with the current max value of the ID column:

select setval(pg_get_serial_sequence('dummy', 'id'), (select max(id) from dummy));

Online example: https://rextester.com/GMK2630

You can run this manually after you have inserted rows with explicitly supplied values.

Note that this can still result in a wrong sequence value if this is done concurrently from multiple transactions because a transaction does not see the values inserted (but not committed) by other transactions.

But I would strong recommend to simply let the default do its job, adjusting the sequence like that is more a hack, rather than good coding practice.


Reportviwer matrix show all months/weeks of year I'm new here, and i have a situation.
I need to alter one report that i created a few time ago, that shows the planning of maintenance. But not all months/weeks have plan defined, and i want to show all months/weeks even in that situation.
The table where i go get the data, have a column Month and onother named Week, and the matrix column group is on Month, and the second group is on Week.

I show you a image of what i have done, and the result.

And here is the query:

SELECT [dbo].[Equip_PIMP].[ID_Equip_PIMP]
          ,[dbo].[Contrato].[ID_Contrato] AS [Contrato_Nr]
          ,[dbo].[Cliente].[Nome] AS [NomeCliente]
          ,[dbo].[Instituicao].[Nome] AS [NomeInstituicao]
          ,[dbo].[Servico].[Nome] AS [NomeServico]
          ,[dbo].[Equip_Marca].[Nome] AS [NomeMarca]
          ,[dbo].[Modelos].[Nome] AS [NomeModelo]
          ,CASE WHEN @DIA_OU_X = 1 THEN CAST([dbo].[Equip_PIMP].[Dia] AS VARCHAR(2))
                WHEN @DIA_OU_X = 0 THEN 'X'
           END AS [VALOR_CEL]
          ,[dbo].[Equip_PIMP].[Data_Plano]
          ,[dbo].[Equip_PIMP].[Data_Execucao]
          ,[dbo].[Rotina].[Nome] AS [NomeRotina]
          ,[dbo].[Rotina].[Tipo] AS [TipoRotina]
          ,[dbo].[Equip_Categoria].[Nome] AS [NomeCategoria]
          ,CASE [dbo].[Equip_PIMP].[Mes]
                WHEN 1 THEN 'JANEIRO'
                WHEN 2 THEN 'FEVEREIRO'
                WHEN 3 THEN 'MARÇO'
                WHEN 4 THEN 'ABRIL'
                WHEN 5 THEN 'MAIO'
                WHEN 6 THEN 'JUNHO'
                WHEN 7 THEN 'JULHO'
                WHEN 8 THEN 'AGOSTO'
                WHEN 9 THEN 'SETEMBRO'
                WHEN 10 THEN 'OUTUBRO'
                WHEN 11 THEN 'NOVEMBRO'
                WHEN 12 THEN 'DEZEMBRO'
           END AS [Mes_Nome] -- This is the MONTHNAME
          ,[dbo].[Equipamento].[ID_Equipamento]
          ,[dbo].[Equipamento].[Nome]
          ,[dbo].[Equipamento].[Marca]
          ,[dbo].[Equipamento].[Modelo]
          ,[dbo].[Equipamento].[Categoria]
          ,[dbo].[Equipamento].[Num_Serie]
          ,[dbo].[Equipamento].[Num_Inventario]
          ,[dbo].[Equipamento].[Num_Equipamento]
          ,[dbo].[Equip_PIMP].[Dia]
          ,[dbo].[Equip_PIMP].[Semana] -- This is the WEEKNUMBER
          ,[dbo].[Equip_PIMP].[Mes] -- This is the MONTHNUMBER
          ,[dbo].[Equip_PIMP].[Trimestre]
          ,[dbo].[Equip_PIMP].[Semestre]
          ,[dbo].[Equip_PIMP].[Ano]
FROM ...

At a high level:

Create a temporary table (a subquery or CTE could be used as well, it's up to you) with the week numbers you want to show, and their corresponding month numbers.

Then modify your query as follows:

SELECT b.ID_Equip_PIMP]
      ,b.[Contrato_Nr]
      ,b.[NomeCliente]
      ,b.[NomeInstituicao]
      ,b.[NomeServico]
      ,b.[NomeMarca]
      ,b.[NomeModelo]
      ,b.[VALOR_CEL]
      ,b.[Data_Plano]
      ,b.[Data_Execucao]
      ,b.[NomeRotina]
      ,b.[TipoRotina]
      ,b.[NomeCategoria]
      ,CASE a.[Mes]
            WHEN 1 THEN 'JANEIRO'
            WHEN 2 THEN 'FEVEREIRO'
            WHEN 3 THEN 'MARÇO'
            WHEN 4 THEN 'ABRIL'
            WHEN 5 THEN 'MAIO'
            WHEN 6 THEN 'JUNHO'
            WHEN 7 THEN 'JULHO'
            WHEN 8 THEN 'AGOSTO'
            WHEN 9 THEN 'SETEMBRO'
            WHEN 10 THEN 'OUTUBRO'
            WHEN 11 THEN 'NOVEMBRO'
            WHEN 12 THEN 'DEZEMBRO'
       END AS [Mes_Nome]
      ,b.[ID_Equipamento]
      ,b.[Nome]
      ,b.[Marca]
      ,b.[Modelo]
      ,b.[Categoria]
      ,b.[Num_Serie]
      ,b.[Num_Inventario]
      ,b.[Num_Equipamento]
      ,b.[Dia]
      ,a.[Semana]
      ,a.[Mes]
      ,b.[Trimestre]
      ,b.[Semestre]
      ,b.[Ano]
  FROM #temp_table a
         LEFT OUTER JOIN (
                          /*  Place entire current query here, except ORDER BY  */
                         ) b ON (a.Mes = b.Mes AND a.Semana = b.Semana)
 ORDER BY /* place current query's ORDER BY here */
;

The temp table will ensure that you have at least one row for every week; however, if there's no actual data for that week, all the related values except the week and month will be NULL.

You may have to make adjustments on the reporting side, depending on how that handles NULL values.


Performing projection first and then selection? Consider a simple query:

Select username,usertype from Table1 Where usertype='Manager'

Most probably the DBMS will be performing selection first, and then projection
i.e. first pulling out rows form Table1 having usertype='Manager'

Is there a case where performing projects first, and then selection would be more
optimal or better in any means?

Not with row-oriented storage, where the column values are stored together per rows, which is not ideal for starting with projection.

Lets say Table1 has 30 columns, but you need data only from 2 (username, usertype). If username and usertype columns are not indexed, the DBMS has to read the full table with all rows (thus read all columns). In this case the only order to do this, is selection, then projection.

If username and usertype are part of a composite index, the DBMS will decide to get the data from this index instead of reading the whole table itself. The index will have most likely less columns than the table, but it is still not the projection you are looking for. Indexes are still organized for row-oriented storage, first selection happens based on usertype='Manager', then the projection.

However, there are some other DBMSs, that use column-oriented storage. Column-oriented DBMS. In these systems, whole columns are stored after each other, which makes it really easy to start with the projection. If you need only 2 columns from 30, the database can just simply read that 2 columns, and do the selection.This kind of system performs better, when running low selectivity queries, that want to access most, or all the rows from a table. Overall performance depends on the kind of workload.


How to monitor INSERT failures? An application INSERTS a parent record then child objects. Both these procedure call different stored procedures. Parent can have many child objects.  Typically both parent and child would be inserted in milliseconds.

I am finding the parent record is created ok but the child objects arent. The inserts are not bound by a transaction , they are also done individually  - no batch inserting.

This problem manifests itself as a parent with no child rows or a parent with a single child row or a parent with no end row (or i guess missing child in the middle but this would be hard/impossible to identify).

To the end user they see the parent with no end/closing row generally i.e the first CHILD that arrives creates a new PARENT object based on its status.

I am using SQL profiler to examine/alert on deadlocks. There are a few but fall far short of the missing child objects.

I have reduced SQL cpu pressure , was bouncing off 100% , not rarely gets near 100%.

Using SPOTLIGHT i can monitor lock/waits but see nothing unusual , infact look better than normal. LATCHES are relatively low too.

Today our application logs are not showing any timeouts but they have appears in small quantities in the past particular when sql server cpu at 100% or during out of hours maintenance.

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

I monitor disk read/write speed. Not problems.

  QUESTIONS

1)In regards to the TIMEOUT which timeout value should be amended on the connection string to potentially counter this error above ?

2)Regards failure to INSERT , can you suggest any processes i should be monitoring to quantify the problem ?

Thank you , I hope the above is clear enough.
(SQL 2005 Standard)
(Parent table 200m records)
(Child table 1bn records)

Just to extend on Max Vermon's comment...

INSERT failures should be monitored at the data access layer.

An exception is raised by SQL with information regarding the error for failed INSERTs. If you are accessing data through a Stored Procedure (SP) you can handle the error with the SP using the BEGIN TRY...END TRY...BEGIN CATCH...END CATCH exception handling blocks.

You can easily write code to store the exception information into a generic table.

You can use exception handling in other programming environments such as C#, VB.NET etc...

Here is a good example of handling exceptions in T-SQL: http://www.codeproject.com/Articles/38211/Exception-Handling-in-SQL-Server


How to sort the result based on a certain column when using GROUP BY in sql statement I am new to this, so please be patient...

I have a table "tFruit".
The fields inside tFruit are "ID", "Buyer", "Seller", "Time", and "FruitName".
"ID" is the key field, the other 4 fiends has non-unique data.

When I use this SQL statement I will get all sellers sorted by Time. But an individual seller appears multiple times because they sold fruits not only once.

SELECT Seller
FROM tFruit
WHERE Buyer = 'email@address.com'
ORDER BY Time DESC

What I want is to get the list of seller, non-duplicate, sorted by Time. Thus I use this statement:

SELECT Seller
FROM tFruit
WHERE Buyer = 'email@address.com'
GROUP BY Seller
ORDER BY Time DESC

Now there is no duplicate Seller, but they are not sorted by Time.

Any ideas how to do this?

Thanks!

Try adding the Time field to your select list, as in:

SELECT Seller, Max(Time) AS MostRecentTime
FROM tFruit
WHERE Buyer = 'email@address.com'
GROUP BY Seller
ORDER BY Max(Time) DESC;

Max(Time) as MostRecentTime will display the most recent Time for the given Seller.  You don't really need this in the SELECT clause, you really only need MAX(Time) in the ORDER BY clause.  I've added it to the SELECT clause so you can see the results are sorted by Time in descending order.


seeking a confirmation -- SQL Server backup performance Possible Duplicate:
  Is SQL Server Backup single or multi-threaded?

Does SQL Server uses a separate writer thread for each backup device (backup file) or separate thread is used only when you stripe your backup across multiple backup volume (drive letter, LUN or mount point)?

for e.g. do we get three write thread here or

backup database dbName to DISK = 'D:\file1.bak',
                      DISK = 'D:\file2.bak',
                      DISK = 'D:\file3.bak'

OR  the following speeds up the backup because you are writing to different volume

backup database dbName to DISK = 'D:\file1.bak',
                      DISK = 'E:\file2.bak',
                      DISK = 'F:\file3.bak'

In above each drive letter is a separate IO path. I understand IO throughput is also very important for backup speed.

Yes, I tested this with a 2TB database.  The backup time dropped from 5 hours to roughly 4 hours 15 minutes after breaking it up into 4 files and putting them in the same drive.  Another advantage is you could back them up to separate drives if your drives IOPS is your bottleneck.

Also, I believe Paul Randall goes through this in his "Microsoft Certified Master Backup and Restore internals" modules.

/edit:
Furthermore, I've been testing something similar with RoboCopy's /MT command (Multi-Threading) and we get similar performance gains doing multi threaded copies of backup files.  During our next backup, I'll test to see if I see multiple SPIDs as well.


SQL Server 2014 Not recognizing 64 bit drivers for Office I have a SQL Server 2014 virtual machine. It is a 64 bit version of SQL Server or a 64 bit Windows Server. The SQL Server software was installed before I was hired by the Sysadmin. He choose to have the 32 bit drivers for Access (Office) installed. We’ve talked about it, he claims he did it because we are using a 32 bit version of Office 2010.

Anyway, I uninstalled the AccessRuntime_x32 and installed AccessRuntime_x64. I’ve downloaded both the 2010 and 2013 versions off the Microsoft Website. I’ve restarted SQL Server, I’ve even rebooted the whole server. Yet I still can’t export out more than 64,000 rows of data for Excel nor can I choose a new version of Microsoft Excel past 2007. What am I missing?

When attempting to reproduce your issue, I received the following error:

  the 'microsoft.ace.oledb.12.0' provider is not registered on the local
  machine

I downloaded the 2007 Office System Driver:  Data Connectivity Components
to resolve the error.  When I choose to export data to Excel and choose a file name of .xls, the export will error at 65,536 rows.  If I choose to export data to Excel and select a file name of .xlsx, the export will complete without error.  This is because in Excel 2010 there is a 1048576 row limit per sheet, and in Excel 97 there is a 65536 row limit per sheet.


SQL Server: Are there any performance benefits from indexing a table <8kb in size? If a table is <8kb in size in SQL Server then presumably it is all stored on a single page. Thus - aside from enforcing a unique constraint - is there any performance benefit that could be obtained by applying an index?

My assumption is that in order to read such a table, SQL Server has to read the single page it is stored on into memory. So any indexing on an <8kb table is pointless from a performance point of view as any index would take up an extra page by itself and then you'd have to read two pages (unless the index included required columns) to get the data.

So unless there's something in the indexing which would either improve the handling of the data in ways I don't understand, or it helps the query optimiser, my assumption is indexing such a small table is pointless.

Would appreciate your expert thoughts!

(This is a purely theoretical question to help me ensure I understand some database concepts properly... thanks!)

Yes.

One potential benefit is that the slot array is ordered by index key order so matching row(s) on the page can be found by performing a binary search (or sometimes linear interpolation) rather than reading all rows on the page.

SQL Server, Seeks, and Binary Search contains much more details about this.


SSAS - what could be a good starting point for memory settings? I have received the following alert regarding memory settings of SSAS:

Alert: SSAS 2014: Invalid TotalMemoryLimit Configuration Source:
MSSQLSERVER Path: SASBI01Alert time: 9/15/2015 5:38:50 PM
Alert description: The configured TotalMemoryLimit for the SSAS instance (307.1728515625 GB) exceeds the configured threshold required for necessary operating system functions and may lead to system instability or failure.
Total memory on the server: 0 GB Total memory
usage: 0 GB Memory used by non-SSAS processes: -3.89069747924805 GB
Memory used by SSAS: 3.89069747924805 GB SSAS Memory Limit Low:
249.57795715332 GB (0%) SSAS Memory Limit High: 307.1728515625 GB (0%)

What could be a good starting point for those memory settings in SSAS? Please see the image below.

This server has ONLY SSAS running on it.

Depending on how much RAM is on your server, below is a good starting point (given you have SSAS ONLY running on the server) :
click to enlarge:

Refer to :SSAS Memory Configurations for Common Architectures and Analysis Services Memory Limits

SSAS uses memory limit settings to determine how it allocates and manages its internal memory.  Memory\LowMemoryLimit defaults to 65% of the total available physical memory on the machine (75% on AS2005), and Memory\TotalMemoryLimit (also sometimes called the High Memory Limit) defaults to 80%.  This is the total amount of memory that the SSAS process itself (msmdsrv.exe) can consume.
Once memory usage hits the Low limit, memory cleaner threads will kick in and start moving data out of memory in a relatively non-aggressive fashion.  If memory hits the Total limit, the cleaner goes into crisis mode… it spawns additional threads and gets much more aggressive about memory cleanup, and this can dramatically impact performance.


Postgres create an insert return rule I am failing to create a rule which will return the insert's data.

The table is:

CREATE TABLE foo(a int, b text);

My attempt for the rule is:

CREATE RULE return_data AS ON INSERT TO foo DO RETURN *;

RETURN isn't a valid command in a rule definition; you can only use SELECT, INSERT, UPDATE, DELETE, and NOTIFY.

You can create an ALSO rule to return the value of the new row, something like this:

> CREATE RULE return_data AS ON INSERT TO FOO DO ALSO SELECT NEW.*;
CREATE RULE

> INSERT INTO foo VALUES (1, 'test');
 a |  b
---+------
 1 | test
(1 row)

INSERT 0 1

Note that you can do something identical by just adding the RETURNING clause to the INSERT statement:

> INSERT INTO foo VALUES (2, 'test 123') RETURNING *;
 a |    b
---+----------
 2 | test 123
(1 row)

INSERT 0 1


MERGE a UNION query results down to one row per KEY value This is a lengthy query, it polls one LIVE database for certain sales information, then a UNION runs the same exact query on the ARCHIVE database for the same information.  The results show each store's sales from the two queries on two lines each, one for each DB polled.

The ideal result would merge the results down to ONE line per store, something I'm currently doing in Excel instead.

The QUERY:

DECLARE @FROM DATETIME, @TO DATETIME

    SET @FROM = '12-30-2013 03:00:00'
    SET @TO   = '12-28-2014 03:00:00'

SELECT d.DivisionName, (SUM(PaymentAmount) - SUM(TipAmount))[CC w/o Tips], SUM(TipAmount) [Tips], SUM(PaymentAmount) [Receipts]
  FROM LFLive.POS.OrderPayments  op WITH(NOLOCK)
  JOIN LFLive.POS.Orders          o WITH(NOLOCK) ON o.OrderId=op.OrderId
  JOIN LFLive.POS.Payments        p WITH(NOLOCK) ON p.PaymentId=op.PaymentId
  JOIN LFLive.Directory.Divisions d WITH(NOLOCK) ON d.DivisionId=o.DivisionId
 WHERE op.IsVoided=0 AND op.IsDeleted=0 AND o.IsVoided=0 AND o.IsClosed=1
   AND o.OrderTypeId = 2 AND p.PaymentTypeId = 6
   AND o.DateClosed BETWEEN @FROM AND @TO
 GROUP BY d.DivisionName

 UNION

SELECT d.DivisionName, (SUM(PaymentAmount) - SUM(TipAmount))[CC w/o Tips], SUM(TipAmount) [Tips], SUM(PaymentAmount) [Receipts]
  FROM LFArchive.POS.OrderPayments op WITH(NOLOCK)
  JOIN LFArchive.POS.Orders         o WITH(NOLOCK) ON o.OrderId=op.OrderId
  JOIN LFLive.POS.Payments                 p WITH(NOLOCK) ON P.PaymentId=op.PaymentId
  JOIN LFLive.Directory.Divisions          d WITH(NOLOCK) ON d.DivisionId=o.DivisionId
 WHERE op.IsVoided=0 AND op.IsDeleted=0 AND o.IsVoided=0 AND o.IsClosed=1
   AND o.OrderTypeId = 2 AND p.PaymentTypeId = 6
   AND o.DateClosed BETWEEN @FROM AND @TO
 GROUP BY d.DivisionName
 ORDER BY d.DivisionName

Notice the LFArchive in two lines of the second half? That's the only difference.

The RESULTS:

DivisionName    CC w/o Tips     Tips            Receipts
------------    -----------     ----            ---------
Store1          96794.67        18875.97        115670.64
Store1         342488.77        67588.71        410077.48
Store2          90863.25        20353.98        111217.23
Store2         315051.32        69489.22        384540.54
Store3          82561.83        15213.77        97775.60
Store3         302811.03        54493.71        357304.74

What I'd rather have is ONE line per store...  never tried to merge down a UNION results output so not sure exactly how to ask the question.

DESIRED RESULT:

DivisionName    CC w/o Tips     Tips            Receipts
------------    -----------     ----            ---------
Store1          439283.44       86464.68        525748.12
Store2          405914.57       89843.20        495757.77
Store3          385372.86       69707.48        455080.34

It seems you just need to use a derived table or common table expression with a new grouping level:

DECLARE
    @FROM datetime = CONVERT(datetime, '12-30-2013 03:00:00', 101),
    @TO datetime = CONVERT(datetime, '12-28-2014 03:00:00', 101);

WITH Combined AS
(
    SELECT
        DivisionName = d.DivisionName,
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)),
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFLive.POS.OrderPayments AS op
    JOIN LFLive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId
    JOIN LFLive.Directory.Divisions AS d
        ON d.DivisionId=o.DivisionId
    WHERE
        op.IsVoided=0
        AND op.IsDeleted=0
        AND o.IsVoided=0
        AND o.IsClosed=1
        AND o.OrderTypeId = 2
        AND p.PaymentTypeId = 6
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY
        d.DivisionName

    UNION ALL -- Not UNION!

    SELECT
        DivisionName = d.DivisionName,
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)),
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFArchive.POS.OrderPayments AS op
    JOIN LFArchive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId
    JOIN LFLive.Directory.Divisions AS d
        ON d.DivisionId=o.DivisionId
    WHERE
        op.IsVoided=0
        AND op.IsDeleted=0
        AND o.IsVoided=0
        AND o.IsClosed=1
        AND o.OrderTypeId = 2
        AND p.PaymentTypeId = 6
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY
        d.DivisionName
)
SELECT
    C.DivisionName,
    [CC w/o Tips] = SUM(C.[CC w/o Tips]),
    [Tips] = SUM(C.Tips),
    [Receipts] = SUM(C.Receipts)
FROM Combined AS C
GROUP BY
    C.DivisionName
ORDER BY
    C.DivisionName;

I replaced the UNION with UNION ALL because you don't want duplicate removal.


Concatenation, with separators, where any combo of input fields might be null I have three columns, CITY, STATE, COUNTRY. One, two, or all columns may be NULL.

With the following logic, a single string is made using the SQL below. It is messy, and I am wondering if there is a simpler way to achieve the output?  I've considered CONCAT() but I don't think I can use that and still get the separators in there.

if both CITY and STATE have a value, use that, separated by ', '. Exclude COUNTRY.
if only one of CITY or STATE exists, AND Country exists, use the one of CITY/STATE which exists, plus the separator, and COUNTRY.
if neither CITY or STATE exist, use country only with no separator
if only one of the three exists, use it, as is, with no separator
If none exist, output should be NULL (not '')

I will add that if a CITY and/or STATE value exists, then COUNTRY will never be NULL.

Here is the code so far. It is messy but I believe it meets the output requirements listed above.

I am working on SQL Server 2008.

CASE WHEN
    CASE WHEN ISNULL(M.CITY, '') = '' THEN  '' ELSE ISNULL(M.CITY, '') + ', ' END + CASE WHEN ISNULL(M.PROVINCE, '') = '' THEN  '' ELSE ISNULL(M.PROVINCE, '') END

     = '' THEN CASE WHEN COUNTRY IS NOT NULL THEN COUNTRY ELSE NULL END ELSE

     CASE WHEN ISNULL(M.CITY, '') = '' THEN  '' ELSE ISNULL(M.CITY, '') + ', ' END + CASE WHEN ISNULL(M.PROVINCE, '') = '' THEN  '' ELSE ISNULL(M.PROVINCE, '') END
END
+
CASE
    WHEN (M.CITY IS NULL AND M.PROVINCE IS NOT NULL) OR (M.CITY IS NOT NULL AND M.PROVINCE IS NULL) THEN
        CASE WHEN PROVINCE IS NOT NULL AND CITY IS NULL THEN ', ' ELSE '' END + COUNTRY
    ELSE ''
END
LOCATION

Does this do what you need?

select coalesce(city + ', ' + state,
                city + ', ' + country,
                state + ', ' + country,
                country,
                state,
                city) as location

If you are 100% sure that the following holds, too (if there are constraints that make it certain):

  I will add that if a CITY and/or STATE value exists, then COUNTRY will never be NULL.

then the last 2 parameters can be omitted as well:

select coalesce(city + ', ' + state,
                city + ', ' + country,
                state + ', ' + country,
                country)


Time addition based on previous column partition by different object I asked a question on
Time addition based on the previous row.

Now I have another one related to that question. Appreciate if someone could give me some help. I use the following code to create the data my need.

create temporary table test(object varchar(10),
                            id integer,
                            realtime datetime,
                            realminute float(15));

insert into test(object, id, realtime, realminute) values
("A","1","2018-12-12 03:03:03","96"),
("B","1","2018-12-12 05:24:01","15");

insert into test(object, id, realminute) values
("A","2","15"),
("A","3","10"),
("A","4","30"),
("A","5","30"),
("A","6","15"),
("B","2","15"),
("B","3","15"),
("B","4","15"),
("B","5","15"),
("B","6","15");

Then I have this.

object|id|          realtime                      | realminute
     A|1|           2018-12-12 03:03:03           |  96
     A|2|                                         |  15
     A|3|                                         |  10
     A|4|                                         |  30
     A|5|                                         |  30
     A|6|                                         |  15
     B|1|           2018-12-12 05:24:01           |  15
     B|2|                                         |  15
     B|3|                                         |  15
     B|4|                                         |  15
     B|5|                                         |  15
     B|6|                                         |  15

For object A, 2018-12-12 03:03:03 plus 96 minutes would be 2018-12-12 04:39:03. This is the realtime for object A in the second column. For object B, 2018-12-12 05:24:01 plus 15 minutes would be 2018-12-12 05:39:01. This is the realtime for object B in the second column. The following table is what I need.

object|id|          realtime  | realminute
     A|1| 2018-12-12 03:03:03 |  96
     A|2| 2018-12-12 04:39:03 |  15
     A|3| 2018-12-12 04:54:03 |  10
     A|4| 2018-12-12 05:04:03 |  30
     A|5| 2018-12-12 05:34:03 |  30
     A|6| 2018-12-12 06:04:03 |  15
     B|1| 2018-12-12 05:24:01 |  15
     B|2| 2018-12-12 05:39:01 |  15
     B|3| 2018-12-12 05:54:01 |  15
     B|4| 2018-12-12 06:09:01 |  15
     B|5| 2018-12-12 06:24:01 |  15
     B|6| 2018-12-12 06:39:01 |  15

How could I get this result?


Grant CREATE SCHEMA (only) to users Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth).  The public space is where I place all my default course material (tables, views, etc), most of which can be accessed by anyone interested.

What I would like is for:

each student (any authenticated user) to be able to create their own schema and use that as the scope where they can create tables and views
students to not be able to create tables/views/objects in public
to apply these settings to all all users; or more specifically, not have to target specific user accounts when assigning permissions

Thought process:

REVOKE ALL ON DATABASE class FROM PUBLIC
GRANT CREATE ON DATABASE class TO PUBLIC  -- can it be CREATE SCHEMA ?
-- being able to create on database makes it seem like they can create in public
-- can permissions be revoked from public?

As user:

psql -h <host> class

-- desire: permitted
CREATE SCHEMA <current_user>;
GRANT USAGE ON <current_user> TO teacher;
CREATE TABLE <current_user>.<table name> ...;
SELECT * FROM public.<table name>;
SELECT * FROM <current_user>.<table name>;

-- desire: not permitted
CREATE TABLE public.<table name> ...;

I'm not sure those are the proper permissions.  I was also curious if I could create a roll and use the role to administer permissions.  The tricky bit is I would like any authenticated user to be part of that role, which I don't think you can add public to a role, so I don't know how that would work.

I haven't thought this through thoroughly, but appreciate any feedback, concerns, and answers offered.  Of course, I'm available to answer questions for clarification -- being concise while still offering comprehensive details is a challenge.

Postgres is quite good at doing such things, this is one reason I like it.

You can create an arbitrarily complex role hierarchy, but for our purposes a simple one will be enough:

CREATE ROLE student;

REVOKE CREATE ON SCHEMA public FROM public;  -- the second public is a pseudorole
                                             -- every role (user, group) is a member of
REVOKE CREATE ON DATABASE class FROM public; -- disallows schema creation, see below

GRANT USAGE ON SCHEMA public TO student;

At this point, student is a role that cannot log in.  We will use it as the privilege collector for the actual students, which you will have to create like this:

CREATE USER jsmith IN ROLE student;  -- will be able to log in,
                                     -- and a member of the student role

You can put the latter two statements into a stored function/procedure (which will include some dynamic SQL), or a script in your second favourite language.

This way your students will be able to create a schema for themselves (the name can be anything), and as they own that schema, they can also create objects in it.  If you want to restrict them to a schema named after them, do the following after the CREATE USER:

CREATE SCHEMA jsmith AUTHORIZATION jsmith;

To allow access for the teacher to all student schemas and objects, the easiest solution is:

GRANT student TO teacher;

What remains now is to allow students to access the existing and future tables in the public schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO student;
ALTER DEFAULT PRIVILEGES IN SCHEMA public FOR ROLE teacher
      GRANT SELECT ON TABLES TO student;

The last command assumes the tables will be created by the user/role teacher.