How to (optimally) get random sample of (a.id, b.id) pairs from two tables (a, b)? Let's assume I have to very simple tables

CREATE TABLE a(id integer PRIMARY KEY,
       t timestamp default now(),
       sensor_readings real[]);
CREATE TABLE b(id integer PRIMARY KEY,
       t timestamp default now(),
       sensor_readings real[]);

with some data on them

INSERT INTO a(id) SELECT generate_series(    1,   100);
INSERT INTO b(id) SELECT generate_series(10001, 10100);

In reality, table a might have about 100_000 rows, and table b about 50_000. In practive, also, the id sequence might have gaps (in the order of a few %). Thus, the cartesian product a x b has a cardinality of billions.

I want to take a random sample of 1_000 sorted pairs (a.id, b.id).
I can use something like the following query:

SELECT
    *
FROM
(
    SELECT
        *
    FROM
        (
        SELECT
            a.id AS a_id, b.id AS b_id
        FROM
            a CROSS JOIN b
        ORDER BY
            random()
        ) AS s0
    LIMIT
        1000
) AS s1
ORDER BY
    a_id, b_id ;

... but it would become extremely inefficient as soon as the number of rows on a or b grows (because of the growth of the CROSS JOIN).

Is there any way of doing something equivalent to this in an optimal manner? That is, is there a practical way of getting a random sample of rows from the a x b relation without actually having to instantiate it.

NOTE: There's no limitation on the fact that a.id or b.id can be repeated. Although the pair (a.id, b.id) cannot.

If I were trying to program this in an imperative language, I'd most probably would use a loop and be doing something like the following pseudo-code (and then, have it checked by a statistitian, to make sure I really take a sample where all the pairs have the same probability of being chosen):

start with a result set equal to {} (empty set)
while size of result set < 1000
    Pick the id value from a random row from table a -> rand_id_a
    Pick the id value from a random row from table b -> rand_id_b
    If (rand_id_a, rand_id_b) not in result set
        append (rand_id_a, rand_id_b) to result set
    end if
end while
sort result set and return it

Is there a way to achieve an equivalent result without resorting to loops?
If not, is there an efficient way to do it using plpgSQL? (or any other language)

The best solution depends on the exact definition of your setup. For the example setup it's trivial:

Serial integer columns without gaps.

SELECT DISTINCT
           1 + trunc(random() * 100)::int AS a_id
     , 10001 + trunc(random() * 100)::int AS b_id
FROM   generate_series(1, 1100) g  -- enough excess to make up for possible dupes
LIMIT  1000;  -- only take 1000

The only interesting question: how to fold dupes efficiently. The solution: let Postgres decide. Simply use DISTINCT.
We don't even need to involve the tables at all. Super fast.
Note that random() generates (per documentation):

random value in the range 0.0 <= x < 1.0

Hence 1 + trunc(random() * 100)::int to cover ID numbers between 1 and 100 exactly.
Actual setup?
You need be be more specific about your actual setup. Let's assume there is at least a payload column in each of your tables, not just ID columns.
CREATE TABLE a(a_id integer PRIMARY KEY, a text);
CREATE TABLE b(b_id integer PRIMARY KEY, b text);

INSERT INTO a(a_id, a)
SELECT g, 't' || g FROM generate_series(    1,   100) g;
INSERT INTO b(b_id, b)
SELECT g, 't' || g FROM generate_series(10001, 10100) g;

Query:
SELECT a.a_id, a.a, b.b_id, b.b
FROM  (
    SELECT DISTINCT
               1 + trunc(random() * 100)::int AS a_id  -- cover *whole* key space
         , 10001 + trunc(random() * 100)::int AS b_id  -- maybe add reserve for new rows
    FROM   generate_series(1, 1100) g
    LIMIT  1000
    ) ra
JOIN   a USING (a_id)
JOIN   b USING (b_id);

Truly random, very fast and almost independent of the actual table size.
All you need is indexes on a(a_id) and b(b_id). Or possibly multicolumn indexes to allow index-only scans.

The solution also works for a few gaps from skipped nextval() calls, as long as there are not much more gaps than islands, it's still very cheap to generate enough combinations to cover losses by gaps. (Much cheaper than working with a Cartesian product of big tables or sorting whole big tables with ORDER BY random() anyway.) Just be sure to generate enough combinations.
SELECT a.a_id, a.a, b.b_id, b.b
FROM  (
    SELECT DISTINCT
               1 + trunc(random() * 100)::int AS a_id
         , 10001 + trunc(random() * 100)::int AS b_id
    FROM   generate_series(1, 1100) g  -- enough to cover dupes *and* gaps
    ) ra
JOIN   a USING (a_id)
JOIN   b USING (b_id)
LIMIT  1000;  -- LIMIT moves to outer query to cover gaps

With more than a few gaps, start with a number of combinations that's enough 95 % of the time and add a recursive step to add more rows if you should fall short. There is a recipe for this solution (for a single table) in the related answer. Also more explanation and variations:

Best way to select random rows PostgreSQL


Auditing database administrator actions with SQL Server 2014 Standard We have a requirement to audit database administrator access to customer databases - at a minimum, knowing which customer database they were accessing (and ideally why). Without upgrading to SQL Enterprise or installing a proxy like GreenSQL are there any alternative options open to us?

I'm thinking for example some kind of privileged script that elevates (sudo style) your privileges to access a database, which logs this to the event log, and reduces them again later.

Is there any way to achieve this that would reliably auditable? Are there any other approaches we should consider?

If you only need to track database access, maybe collecting schema stability locks with extended events is enough.

I blogged this idea last week for a totally different reason  (tracking unused objects) but the script can be adapted to fit your needs. You can find it here.

Basically, it reads an extended events session using the streaming api and then saves summary information in a table at regular intervals. In your case you need the individual events and probably the sql text as well. It really depends what you're trying to achieve. Maybe a simple and plain file target is enough.


Trying to compare several similiar lists on one table to produce a concatenated result So imagine that four friends are planning on moving into a house together.  They put together a list of stuff that they all own.  Obviously, there is some overlap but there is also unique things among them.

Table LIST_OF_STUFF is a fairly simple table:

LIST_ID   ITEM
47        Baseball
47        Hat
47        Gloves
47        Sparkle Shoes
91        Baseball
91        Sparkle Shoes
91        Bubble Gum
91        Hat
91        Fish Tank
91        Book of Spiders
103       Silver Spoon
103       Fiddle
103       Trumpet
103       Paper Towels
103       Fireworks
103       Mosquito Netting
103       Cure for the Common Cold
295       Baseball
295       Fireworks
295       Trumpet
295       Bubble Gum
295       Hat
295       Gloves
295       Christmas Tree

This table contains lists unrelated to these four people (e.g. shopping lists, my favorite vacation spots, etc.) but the specific LIST_IDs I'm interested in are shown above.  I want to be able to make results similar to the following:

LIST_47          LIST_91          LIST_103...
Baseball         Baseball         (null)
Hat              Hat              (null)
(null)           Fish Tank        (null)
(null)           (null)           Silver Spoon

I initially thought a FULL OUTER JOIN on LIST_OF_STUFF would do the trick but it didn't return all the values on the other lists.  It would only be compared to LIST_47:

SELECT LIST_47.ITEM LIST_47_LIST
     , LIST_91.ITEM LIST_91_LIST
     , LIST_103.ITEM LIST_103_LIST
     ...
FROM LIST_OF_STUFF LIST_47
FULL OUTER JOIN LIST_OF_STUFF LIST_91.ITEM = LIST_47.ITEM AND LIST_91.LIST_ID = 91
FULL OUTER JOIN LIST_OF_STUFF LIST_103.ITEM = LIST_47.ITEM AND LIST_103.LIST_ID = 103
...
WHERE LIST_47.LIST_ID = 47

What is the best method to return such information?

JOIN is not that effective, because you need to access the table 4 times. With PIVOT you can do it with 1 table access.

create table list_of_stuff
(
  list_id number(5,0),
  item varchar2(40 char)
);

insert all
  into list_of_stuff values (47, 'Baseball')
  into list_of_stuff values (47, 'Hat')
  into list_of_stuff values (47, 'Gloves')
  into list_of_stuff values (47, 'Sparkle Shoes')
  into list_of_stuff values (91, 'Baseball')
  into list_of_stuff values (91, 'Sparkle Shoes')
  into list_of_stuff values (91, 'Bubble Gum')
  into list_of_stuff values (91, 'Hat')
  into list_of_stuff values (91, 'Fish Tank')
  into list_of_stuff values (91, 'Book of Spiders')
  into list_of_stuff values (103, 'Silver Spoon')
  into list_of_stuff values (103, 'Fiddle')
  into list_of_stuff values (103, 'Trumpet')
  into list_of_stuff values (103, 'Paper Towels')
  into list_of_stuff values (103, 'Fireworks')
  into list_of_stuff values (103, 'Mosquito Netting')
  into list_of_stuff values (103, 'Cure for the Common Cold')
  into list_of_stuff values (295, 'Baseball')
  into list_of_stuff values (295, 'Fireworks')
  into list_of_stuff values (295, 'Trumpet')
  into list_of_stuff values (295, 'Bubble Gum')
  into list_of_stuff values (295, 'Hat')
  into list_of_stuff values (295, 'Gloves')
  into list_of_stuff values (295, 'Christmas Tree')
select * from dual;

commit;

JOIN version:

SQL> set null "(null)"
SQL> r
  1  select *
  2  from
  3                  (select * from list_of_stuff where list_id = 47)  L1
  4  full outer join (select * from list_of_stuff where list_id = 91)  L2 using (item)
  5  full outer join (select * from list_of_stuff where list_id = 103) L3 using (item)
  6  full outer join (select * from list_of_stuff where list_id = 295) L4 using (item)
  7  order by item
  8*

ITEM                              LIST_ID    LIST_ID    LIST_ID    LIST_ID
------------------------------ ---------- ---------- ---------- ----------
Baseball                               47         91 (null)            295
Book of Spiders                (null)             91 (null)     (null)
Bubble Gum                     (null)             91 (null)            295
Christmas Tree                 (null)     (null)     (null)            295
Cure for the Common Cold       (null)     (null)            103 (null)
Fiddle                         (null)     (null)            103 (null)
Fireworks                      (null)     (null)            103        295
Fish Tank                      (null)             91 (null)     (null)
Gloves                                 47 (null)     (null)            295
Hat                                    47         91 (null)            295
Mosquito Netting               (null)     (null)            103 (null)
Paper Towels                   (null)     (null)            103 (null)
Silver Spoon                   (null)     (null)            103 (null)
Sparkle Shoes                          47         91 (null)     (null)
Trumpet                        (null)     (null)            103        295

This returns a NULL where the item is not on the list, otherwise it returns the list_id. The actual value does not matter, just the fact that it is not null, so providing the desired output:

SQL> r
  1  select
  2    nvl2(L1.list_id, item, null) as LIST_47,
  3    nvl2(L2.list_id, item, null) as LIST_91,
  4    nvl2(L3.list_id, item, null) as LIST_103,
  5    nvl2(L4.list_id, item, null) as LIST_295
  6  from
  7                  (select * from list_of_stuff where list_id = 47)  L1
  8  full outer join (select * from list_of_stuff where list_id = 91)  L2 using (item)
  9  full outer join (select * from list_of_stuff where list_id = 103) L3 using (item)
 10  full outer join (select * from list_of_stuff where list_id = 295) L4 using (item)
 11  order by item
 12*

LIST_47         LIST_91         LIST_103                       LIST_295
--------------- --------------- ------------------------------ ---------------
Baseball        Baseball        (null)                         Baseball
(null)          Book of Spiders (null)                         (null)
(null)          Bubble Gum      (null)                         Bubble Gum
(null)          (null)          (null)                         Christmas Tree
(null)          (null)          Cure for the Common Cold       (null)
(null)          (null)          Fiddle                         (null)
(null)          (null)          Fireworks                      Fireworks
(null)          Fish Tank       (null)                         (null)
Gloves          (null)          (null)                         Gloves
Hat             Hat             (null)                         Hat
(null)          (null)          Mosquito Netting               (null)
(null)          (null)          Paper Towels                   (null)
(null)          (null)          Silver Spoon                   (null)
Sparkle Shoes   Sparkle Shoes   (null)                         (null)
(null)          (null)          Trumpet                        Trumpet

This requires 28 buffer gets on my demo table:

SQL> select * from table(dbms_xplan.display_cursor(format=>'iostats last'));
...

Plan hash value: 912304261

------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |     15 |00:00:00.01 |      28 |
|   1 |  SORT ORDER BY             |               |      1 |     21 |     15 |00:00:00.01 |      28 |
|   2 |   VIEW                     | VW_FOJ_0      |      1 |     21 |     15 |00:00:00.01 |      28 |
|*  3 |    HASH JOIN FULL OUTER    |               |      1 |     21 |     15 |00:00:00.01 |      28 |
|   4 |     VIEW                   |               |      1 |      7 |      7 |00:00:00.01 |       7 |
|*  5 |      TABLE ACCESS FULL     | LIST_OF_STUFF |      1 |      7 |      7 |00:00:00.01 |       7 |
|   6 |     VIEW                   | VW_FOJ_1      |      1 |     13 |     14 |00:00:00.01 |      21 |
|*  7 |      HASH JOIN FULL OUTER  |               |      1 |     13 |     14 |00:00:00.01 |      21 |
|   8 |       VIEW                 | VW_FOJ_2      |      1 |      6 |      7 |00:00:00.01 |      14 |
|*  9 |        HASH JOIN FULL OUTER|               |      1 |      6 |      7 |00:00:00.01 |      14 |
|  10 |         VIEW               |               |      1 |      4 |      4 |00:00:00.01 |       7 |
|* 11 |          TABLE ACCESS FULL | LIST_OF_STUFF |      1 |      4 |      4 |00:00:00.01 |       7 |
|  12 |         VIEW               |               |      1 |      6 |      6 |00:00:00.01 |       7 |
|* 13 |          TABLE ACCESS FULL | LIST_OF_STUFF |      1 |      6 |      6 |00:00:00.01 |       7 |
|  14 |       VIEW                 |               |      1 |      7 |      7 |00:00:00.01 |       7 |
|* 15 |        TABLE ACCESS FULL   | LIST_OF_STUFF |      1 |      7 |      7 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------------------------

Then I have this PIVOT query that puts the number 1 (or I could have written any other value in MIN(), or use another aggregate function) to a list column where the item in the row exists:

SQL> set null "(null)"
SQL> r
  1  select *
  2  from
  3  (
  4    select list_id, item
  5    from   list_of_stuff
  6    where  list_id in (47, 91, 103, 295)
  7  )
  8  pivot (min(1) as in_list for list_id in (47, 91, 103, 295))
  9  order by item
 10*

ITEM                           47_IN_LIST 91_IN_LIST 103_IN_LIST 295_IN_LIST
------------------------------ ---------- ---------- ----------- -----------
Baseball                                1          1 (null)                1
Book of Spiders                (null)              1 (null)      (null)
Bubble Gum                     (null)              1 (null)                1
Christmas Tree                 (null)     (null)     (null)                1
Cure for the Common Cold       (null)     (null)               1 (null)
Fiddle                         (null)     (null)               1 (null)
Fireworks                      (null)     (null)               1           1
Fish Tank                      (null)              1 (null)      (null)
Gloves                                  1 (null)     (null)                1
Hat                                     1          1 (null)                1
Mosquito Netting               (null)     (null)               1 (null)
Paper Towels                   (null)     (null)               1 (null)
Silver Spoon                   (null)     (null)               1 (null)
Sparkle Shoes                           1          1 (null)      (null)
Trumpet                        (null)     (null)               1           1

Similarly, after transforming it to the desired output:

SQL> r
  1  select
  2    nvl2("47_IN_LIST", item, null)  as list_47,
  3    nvl2("91_IN_LIST", item, null)  as list_91,
  4    nvl2("103_IN_LIST", item, null) as list_103,
  5    nvl2("295_IN_LIST", item, null) as list_295
  6  from
  7  (
  8    select *
  9    from
 10    (
 11      select list_id, item
 12      from   list_of_stuff
 13      where  list_id in (47, 91, 103, 295)
 14    )
 15    pivot (min(1) as in_list for list_id in (47, 91, 103, 295))
 16  )
 17  order by item
 18*

LIST_47         LIST_91         LIST_103                       LIST_295
--------------- --------------- ------------------------------ ---------------
Baseball        Baseball        (null)                         Baseball
(null)          Book of Spiders (null)                         (null)
(null)          Bubble Gum      (null)                         Bubble Gum
(null)          (null)          (null)                         Christmas Tree
(null)          (null)          Cure for the Common Cold       (null)
(null)          (null)          Fiddle                         (null)
(null)          (null)          Fireworks                      Fireworks
(null)          Fish Tank       (null)                         (null)
Gloves          (null)          (null)                         Gloves
Hat             Hat             (null)                         Hat
(null)          (null)          Mosquito Netting               (null)
(null)          (null)          Paper Towels                   (null)
(null)          (null)          Silver Spoon                   (null)
Sparkle Shoes   Sparkle Shoes   (null)                         (null)
(null)          (null)          Trumpet                        Trumpet

Much simpler, 1 table access, 7 buffer gets:

SQL> select * from table(dbms_xplan.display_cursor(format=>'iostats last'));

...

Plan hash value: 1664708432

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |     15 |00:00:00.01 |       7 |
|   1 |  SORT GROUP BY PIVOT|               |      1 |     24 |     15 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL | LIST_OF_STUFF |      1 |     24 |     24 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

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

   2 - filter(("LIST_ID"=47 OR "LIST_ID"=91 OR "LIST_ID"=103 OR "LIST_ID"=295))

Now if you don't insist on naming the columns LIST_47, LIST_91, ..., the JOIN version peforms worse, but it can be easily reused, by changing the 4 list_id parameters, you can even use bind variables there. The PIVOT performs better, but if you want to use different list_id values, you need to rewrite the query, because you can not dynamically provide the values for the pivot( ... in (***)). PIVOT needs to know the column names it generates at parse time.


Should a table have a foreign key to another table if it already has a foreign key to another table that has that foreign key? That was a hard title to digest even for me so I'll break it down.

I'm very new to SQL, and I just designed my first database. I also am not very good at JOINS which might be the answer to my problem. But to get back on track I currently have three tables of interest:

comments

   id    |      author       |     category    | date_submitted
---------+-------------------+-----------------+----------------

replies

   id    | date_submitted | comment_id
---------+----------------+------------

images

           image_url            | reply_id |                    original_id
--------------------------------+----------+---------------------------------------------------

So given this, the question came to mind when I was trying get a table with the columns comments.id and images.original_id, an operation that I would like to do often.

Currently to do this I can't directly JOIN comments and images because they have nothing in common, meaning that the middleman has to be the replies table. So I have to JOIN replies and images and then JOIN that with comments.

So my question is this. Would it be okay to add a foreign key column referencing comments to the images table so that I can directly JOIN the two tables instead of going images -> replies -> comments, or would it be bad practice to add a foreign key that I already have to access to through the replies table?

It is not a good practice for many reasons. The most important one, design principles, you should never mix entities just because it is easier for the programmer/dba to do queries. Entities should be as clear as they can and must exclude any other information not related to them.

For your scenario, to make it easier for future querying, you can create a view that does the join and instead query this view.

Regarding any benefit that could be possible while cutting a join, there are other costs maintaining the foreign keys, so there is not really a huge advantage either.

If you are in SQL Server, you can create an index for the view, which materializes the view (creates an underlying table with the data) and you will have performance as querying a single table.


While inserting to table date going as 000000 in mysql I have a table with column datatype as Date in mysql database.
I want to insert dates in 'dd/mm/yyyy' format so I used below query. When I use only the SELECT, the result set is showing correct. But when I use the INSERT, it is going as '000000' (the values inserted are all the '0000-00-00' date in abc).

The column I am selecting from (completion_date) is varchar datatype.

INSERT INTO abc
    (maturity_date) -- Datatype Date for maturity_date column
SELECT
    DATE_FORMAT(
        STR_TO_DATE(completion_date,    -- varchar
                    '%d/%m/%Y'),
        '%d-%m-%Y')
FROM XYZ ;

When if am using below script, data is inserted properly but format is 'yyyy/mm/dd'.

INSERT ...
SELECT STR_TO_DATE(completion_date , '%d-%m-%Y')
...

Is there any way to insert in 'dd/mm/yyyy' format with proper data?

example: '21-01-2017'

From the MySQL manual, DATE uses the YYYY-MM-DD format
https://dev.mysql.com/doc/refman/5.7/en/datetime.html

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

You can select dates out of your database and convert them to whatever format you want to display to your uses, but to store them you'll need to use the YYYY-MM-DD format that MySQL is expecting.


Incorrect Statistics EQ_ROWS Causing Parallel Table Scans and Deadlocks On one customer's system, one of our database's tables has about 123 million rows.  This table is a transaction table that is grouped by a batch key.  The clustered index is 4 columns: batch key, transaction version, shift number, transaction number.  One batch can have any number of transactions in it, but a query to count by batch shows the maximum number of transactions per batch is 764, and the count drops sharply from that peak.

Some of our queries that join to this transaction table are randomly getting bad query plans that choose to perform parallel index scans on the transaction table.  Many of these queries run in separate connections.  When this occurs, the parallel scans are causing constant deadlocking on this table.

The query plan shows a very large estimate for the number of rows in a batch-- about 20,000.  I already know the maximum per batch is 764, so I was puzzled where this estimate comes from.

I ran DBCC SHOW_STATISTICS ('MyTable', 'MyTableClusteredIndex').  The statistics histogram that came back looks weird, because the EQ_ROWS numbers are incorrect.  For example, here are the first 3 entries in the histogram.  The EQ_ROWS numbers are very different than the actual number of rows found with a query using the same key (shown in the right-most column):

RANGE_HI_KEY RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ActualRows
        58287          0        1                   0              1        121
        78045   664341.4 14512.91                1308       507.9888        134
        86487   569858.3 14512.91                1208       471.8449        118
        94282   574411.7  9070.57                 538       1067.721        128

Since this is a production server, I do not want to try to mess with the statistics during business hours.

We have an internal dev server that we use for performance testing.  The dev machine has 2 10-core processors on it and is using about 500 GB of memory.
On this server, the transaction table has about 1.1 billion rows.  The dev server shows a larger disparity between EQ_ROWS and the actual number of rows for each key.  For example, some EQ_ROWS numbers are over 60,000, but the actual number of rows for those keys are between 130 to 160 rows.

If I run a manual UPDATE STATISTICS MyTable on our dev server, the statistics do not change.  The EQ_ROWS are still the same incorrect numbers.

So far, I have found the same strange EQ_ROWS behavior on SQL 2014 SP 2 and SQL 2012 SP 3.

Our database servers run with the following trace flags enabled: 1222, 2312, 2453, 4199.  I verified that disabling these trace flags globally does not change the behavior when updating statistics.

I verified that the UPDATE STATISTICS MyTable is not running in parallel.  When it runs, its degree of parallelism is 1, so it does not look like parallelism is causing some kind of statistics estimation bug.

Aren't the EQ_ROWS values supposed to be the exact number of rows for that key value?  If so, why are they incorrect?  Does this look like a SQL Server bug?

(Note that I have ignored parameter sniffing, because this issue looks related to the statistics estimates.  The query plan does not look related to parameter sniffing to me except for randomly optimizing the query for one of these keys.)

As pointed out by sp_BlitzErik (thank you!), the problem here was the sampling rate was too low for the billion row transaction table.  The more rows sampled by the statistics update, the more accurate the estimate, but the longer it takes for SQL Server to generate the statistics.  There is a tradeoff between database maintenance time and statistics accuracy.

A simple `update statistics MyTable' was sampling 0.13% of the rows.  This generated a very poor estimate for the "EQ_ROWS" column that was usually hundreds of times worse than the actual row counts.  For example, some EQ_ROWS estimates were over 60,000 when the actual counts were about 150.  On the other hand, the default stats update completed in 25 seconds-- pretty fast for a billion rows.

A full scan generated perfect EQ_ROWS statistics.  However, the full scan required about 5 hours to complete.  That would not be acceptable for the customer's database maintenance window.

Below is the chart of samples vs. accuracy.  We're going to recommend the customer adjusts the sample size manually for the largest tables in the system.  I expect we'll go with a sample size of about 10%.  The Avg Stat Diff column is calculated as ABS(EQ_ROWS - ActualRowCount).

Sample Size     Avg Stat Diff Time to Create Stats
 =============== ============= ====================
 Default (0.13%)        42,755             00:00:25
 1%                      4,880             00:02:41
 5%                        940             00:13:47
 10%                       443             00:27:45
 20%                       209             00:56:28
 50%                        67             02:24:17
 100% (fullscan)             0             ~5 hours


Concatenate all values of the same XML element using XPath/XQuery I have an XML value like this:

<R>
  A
  B
  C
  ...
</R>

I want to concatenate all I values and return them as a single string: ABC....

Now I know that I can shred the XML, aggregate the results back as a nodeless XML, and apply .values('text()[1]', ...) to the result:

SELECT
  (
    SELECT
      n.n.value('text()[1]', 'varchar(50)') AS [text()]
    FROM
      @MyXml.nodes('/R/I') AS n (n)
    FOR XML
      PATH (''),
      TYPE
  ).value('text()[1]', 'varchar(50)')
;

However, I would like to do all that using XPath/XQuery methods only, something like this:

SELECT @MyXml. ? ( ? );

Is there such a way?

The reason I am looking for a solution in this direction is because my actual XML contains other elements too, for instance:

<R>
  A
  B
  C
  ...
  <J>X</J>
  <J>Y</J>
  <J>Z</J>
  ...
</R>

And I would like to be able to extract both the I values as a single string and the J values as a single string without having to use an unwieldy script for each.

This might work for you:

select @MyXml.value('/R[1]', 'varchar(50)')

It picks up all text() elements from the first R and below.

If you just want all text() you can do

select @MyXml.value('.', 'varchar(50)')

If you want the values for I and J separate do this instead.

select @MyXml.query('/R/I/text()').value('.', 'varchar(50)'),
       @MyXml.query('/R/J/text()').value('.', 'varchar(50)')


Should I run multiple DBCC Shrinkfile if files are in different disk arrays? We just deleted about 45% of the data in a database, and we want to reduce the size of the mdf files (now we have a "delete old data job", so it won't grow that much again in the near future).

My question is:

If I execute more than one DBCC Shrinkfile at the same time, and the files are on different array disks, will the total time of shinking be shorter?
Compared against executing DBCC Shrinkfile one file at a time.

This will be executed on a maintenance window, and the idea is to have this maintenance window as short as possible.

Regards

Your task is to reduce the size of mdf file, right?

Shrinking a database by dbcc shrinkdatabase is extremely slow. It is single threaded.

There is a faster way to reduce DB size:

Create new filegroup and allocate space for that filegroup as needed (determine how much space is actually used in the old files)
Move all the data to that filegroup and drop the old one

This solution can be multi threaded so it is going to be much faster


How do I have Oracle ASM scan for and repair uncorrectable ECC errors (bad sectors)? I'm running Oracle ASM (11gR2) with normal redundancy on SATA disks (WD Raptors). This is on a physical standby database, if it matters. Occasionally, disk sectors develop read errors (ECC failures). When that happens, that block is no longer mirrored, but ASM doesn't know this.

How can I have ASM read through the partitions it controls, and make sure that each block is readable?

Ideally, it'd also confirm that both sides of the mirror match, and re-write any sectors that the disk reports uncorrectable ECC errors for.

(I.e., I'd like the equivalent of mdraid's echo 'repair' > /sys/block/md0/md/sync_action or a hardware RAID controller's background consistency check.)

Update 2012-01-03: So far, the best I've found is to alter the diskgroup to drop the disk, wait for the rebalance to finish, use an OS utility to zero the disk, and then add the disk back. This of course doesn't compare the two sides of the mirror, but at least reallocates any bad sectors. It seems like there must be a better way. Its hard to believe ASM lacks such a standard RAID feature.

Use alter diskgroup mydiskgroup check all; or the remap command. But ASM should manage this for you; perhaps you need a higher redundancy setting on your JBODs,


How can I define a one to many constraint between columns of the same table? I'm creating an IDs table in PostgreSQL to represent the relationship between multiple foreign IDs - call them a, b and c.
I happen to know that the foreign IDs have one-to-many relationships between them:
a 1..* b
b 1..* c
I'd like to represent them as a single table (see below), but enforce the one-to-many invariant between a & b, & between b and c.
Is that possible?
Examples:
The following insertions would be illegal:

pk
a
b
c
reason

p15
a1
b11
c111
c111 already exists

p16
a1
b21
c213
b21 already exists for a2

p17
a2
b11
c214
b11 already exists for a1

Given the following legal table:

pk
a
b
c

p1
a1
b11
c111

p2
a1
b11
c112

p3
a1
b12
c121

p4
a1
b12
c122

p5
a2
b21
c211

p6
a2
b21
c212

p7
a2
b22
c221

p8
a2
b22
c222

p9
null
b31
c311

p10
a3
null
c312

p11
a3
b31
null

p12
null
null
c314

p13
null
b31
null

p14
a3
null
null

This has to be done by TRIGGER, because it is too complex for CHEKCconstraint
For updating you need a similar trigger/function

CREATE TABLE tabl1
    ("pk" varchar(5), "a" varchar(6), "b" varchar(6), "c" varchar(6))
;

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
    ('p1', 'a1', 'b11', 'c111'),
    ('p2', 'a1', 'b11', 'c112'),
    ('p3', 'a1', 'b12', 'c121'),
    ('p4', 'a1', 'b12', 'c122'),
    ('p5', 'a2', 'b21', 'c211'),
    ('p6', 'a2', 'b21', 'c212'),
    ('p7', 'a2', 'b22', 'c221'),
    ('p8', 'a2', 'b22', 'c222'),
    ('p9', NULL, 'b31', 'c311'),
    ('p10', 'a3', NULL, 'c312'),
    ('p11', 'a3', 'b31', NULL),
    ('p12', NULL, NULL, 'c314'),
    ('p13', NULL, 'b31', NULL),
    ('p14', 'a3', NULL, NULL)
;

CREATE OR REPLACE FUNCTION check_insert()
RETURNS TRIGGER AS $$
   BEGIN
   IF EXISTS(SELECT 1 FROM tabl1 WHERE "c" = NEW."c" AND "b" = NEW."b" ) then
       raise EXCEPTION 'The column %  already  with %',NEW."c",NEW."b";
   ELSIF EXISTS(SELECT 1 FROM tabl1 WHERE "a" <> NEW."a" AND "b" = NEW."b" ) then
           raise EXCEPTION 'The column %  already  has already a column',NEW."b";
   END IF;
   RETURN NEW;
   END;
$$ LANGUAGE plpgsql;

CREATE  TRIGGER trigger_name BEFORE
 INSERT
ON tabl1
FOR EACH ROW
EXECUTE PROCEDURE check_insert();

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
    ('p15', 'a1', 'b11', 'c111')

ERROR:  The column c111  already  with b11

CONTEXT:  PL/pgSQL function check_insert() line 4 at RAISE

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
('p16' , 'a1'   , 'b21'  , 'c213' )

ERROR:  The column b21  already  has already a column

CONTEXT:  PL/pgSQL function check_insert() line 6 at RAISE

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
('p17',   'a2',   'b11'   ,'c214')

ERROR:  The column b11  already  has already a column

CONTEXT:  PL/pgSQL function check_insert() line 6 at RAISE

SELECT * FROM tabl1

pk  | a    | b    | c
:-- | :--- | :--- | :---
p1  | a1   | b11  | c111
p2  | a1   | b11  | c112
p3  | a1   | b12  | c121
p4  | a1   | b12  | c122
p5  | a2   | b21  | c211
p6  | a2   | b21  | c212
p7  | a2   | b22  | c221
p8  | a2   | b22  | c222
p9  | null | b31  | c311
p10 | a3   | null | c312
p11 | a3   | b31  | null
p12 | null | null | c314
p13 | null | b31  | null
p14 | a3   | null | null

db<>fiddle here


When changing the default port number from 5432 to 5332, why do I get a unix domain socket error? I successfully followed the Postgres 11 installation instructions on Centos 7 found here. I am able to successfully create schemas / tables and insert data into the database over port 5432 with no problems.
Now I would like to change the default port to 5332 (or something else, doesn't matter), and I did the following:
 vi /var/lib/pgsql/11/data/postgresql.conf

Changed the line:
#port = 5432                            # (change requires restart)

to look like:
port = 5332                            # (change requires restart)

Then restarted postgres service by doing:
systemctl restart postgresql-11.service

Then I change to the postgres user:
su - postgres
psql

I'm getting the following error message:
sql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

So apparently there is another reference of port 5432 somewhere, but I am unable to find it.
FYI, if I undo the change I did above (in the file /var/lib/pgsql/11/data/postgresql.conf), then everything works perfectly again over default port 5432.
What else do I need to do to get postgres to run on a different port ?

You may have changed the port for the PostgreSQL database, but not the client.
Try this:

Update your PostgreSQL configuration to use port 5332 again
Test this by passing the port number when trying to connect:
psql -p 5332

If everything works, change the default port for the command-line client like this:
PGPORT=5332; export PGPORT

Now all calls to psql will act as if it has been invoked with the -p 5332 command-line option.


MySQL Backup with innobackupex error: Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374 Getting following error message:

Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
170414 19:01:33 Connecting to MySQL server host: localhost, user: XXXXXXXX, password: set, port: not set, socket: not set
Error: Unsupported server version: '5.7.17-0ubuntu0.16.04.2'. Please report a bug at https://bugs.launchpad.net/percona-xtrabackup

after running following command:

innobackupex --user=XXXXXXXX  --password=YYYYYYYYY --no-timestamp /data/backups/new_backup

should be fixed in version 2.4.7 and version 2.3.8 https://bugs.launchpad.net/percona-xtrabackup/+bug/1651978, if you can still reproduce using the latest version please update the bug report


Can Mysql Cluster AQL(Push-Down Join) be trigger with sharding table at the same time? From the jonas slide, I knew that the feature of "Push-Down Join" can make join 70x faster. the most important factor is to eliminate the round trip between API node and data nodes. where all data in the same node, it is not necessary to do inter-node communication. But what if a certain table is partitioned to some group of data nodes, how AQL eliminate the data shipping? can push-down work in sharding scene?

Pushdown basically means that some part of the job is "pushed down" to data nodes. So performance gain is because:

data nodes can filter and process data in much more efficient way than SQL nodes. They "know" more about the physical details of the data
we save on SQL node - data node traffic. And latency between those two nodes is relatively high because of the MySQL Cluster design

Inter-data-node latency is much much lower. So data shipping does not have such impact on performance. And all the tables (except some absolute corner cases) are sharded between data nodes. For any data fetch data nodes must find which node holds active part of the required row.

This latency still cannot be ignored. One can find that for the best possible performance Dolphin Interconnect is suggested. But:

inter-data-node latency is lower
for one data fetch to SQL nodes several communication packets must pass between data nodes. And when filtering is done by data nodes much less packets are needed between data nodes for one processed row.


Cannot delete publication When attempting to delete a publication, I am getting the error cannot drop publication 'XX' cannot drop article 'XX'.

The subscription has been deleted at the subscriber. Any ideas on how I can force the deletion of the publication so that it can be recreated? I can execute

exec sp_dropsubscription @publication = N'XX', @subscriber = N'all'

but this has no apparent effect.

sp_removedbreplication Database name works for me but sometimes when replication is really screwed up I have to remove all replication by using sp_removesrvreplication


Re-Write Query For Speed This query works, but it is extremely slow, and takes a lot of system resources to complete.  Is there a different way to write this table so that speed is #1 priority?

Below is the query and sample DDL

CREATE TABLE [dbo].[Costing](
    [Store] [varchar](45) NULL,
    [TotalCost] [float] NULL,
    [SaleID] [varchar](12) NULL,
    [SaleDate] [date] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[HOLD1](
    [Store] [varchar](45) NULL,
    [SaleID] [varchar](12) NULL,
    [TimesBilled] [int] NULL
) ON [PRIMARY]

Create Table MasterData
(
    [Store] [varchar](45) NOT NULL,
    TC1 float NOT NULL DEFAULT(0),
    TC2 float NOT NULL DEFAULT(0)
)
SET ANSI_PADDING OFF

INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 1', 5800, N'1234', CAST(0xA13C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 1', 50000, N'1123', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 2', 2925, N'3311', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 3', 4000, N'4411', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 3', 100000, N'2211', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 4', 1200, N'1211', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 4', 500, N'5511', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 5', 3500, N'1314', CAST(0x9F3C0B00 AS Date))
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 3', N'4411', 4)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 4', N'1211', 2)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 5', N'1314', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 2', N'3311', 2)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 4', N'5511', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 1', N'1234', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 3', N'2211', 5)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 1', N'1133', 3)
Insert Into MasterData (Store) VALUES
('Store 1'), ('Store 2'), ('Store 3'), ('Store 4'), ('Store 5')

This is the script:

SELECT [Store]
    ,[SaleID]
    ,TPC = SUM(COALESCE([TotalCost],0))
INTO HelperTable
FROM Costing
GROUP BY Store, SaleID

Alter Table HelperTable
ADD timesbilled int NOT NULL DEFAULT(0)

UPDATE x
SET TimesBilled = y.TimesBilled
FROM HelperTable x
INNER JOIN [Hold1] y
ON x.SaleID = y.SaleID

Alter Table HelperTable
ADD TC1 As case
                when TimesBilled > 1 then TPC/TimesBilled
                else TPC
            end

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN HelperTable y
ON x.Store = y.Store

Select * FROM MasterData

Even when creating a table via SELECT ... INTO, you can include empty columns to be populated later. This avoids the need to ALTER the table even once:

Select * FROM MasterData
SELECT [Store]
    ,[SaleID]
    ,TPC = SUM(COALESCE([TotalCost],0))
    ,timesbilled = CAST(NULL as int)
    ,TC1 = SUM(COALESCE([TotalCost],0))
INTO HelperTable
FROM Costing
GROUP BY Store, SaleID

UPDATE t
SET t.TimesBilled = h.TimesBilled
   ,t.TC1 = CASE WHEN h.TimesBilled > 1
              THEN t.TC1/h.TimesBilled
              ELSE t.TC1
            END
FROM HelperTable t
INNER JOIN [Hold1] h
ON t.SaleID = h.SaleID

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN HelperTable y
ON x.Store = y.Store

If HOLD1 is guaranteed to have 0 or 1 records for each SaleID, you can compress this even further, avoiding the need for the UPDATE - at which point, you can actually skip HelperTable, and put a subquery directly into the MasterDate UPDATE statement:

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN (SELECT c.[Store]
                  ,c.[SaleID]
                  ,TPC = SUM(COALESCE(c.[TotalCost],0))
                  ,TimesBilled = h.TimesBilled
                  ,TC1 = CASE WHEN h.TimesBilled > 1
                           THEN SUM(COALESCE(c.[TotalCost],0))/h.TimesBilled
                           ELSE SUM(COALESCE(c.[TotalCost],0))
                         END
              FROM Costing c
                     LEFT  JOIN [Hold1] h ON (c.SaleID = h.SaleID)
             GROUP BY c.Store, c.SaleID, h.TimesBilled
           ) y ON x.Store = y.Store

On my system, the original query took 13 ms to execute (I ran it twice, same both times); My version with HelperTable took 10 ms the first time I ran it, and 6 the second; and the version without HelperTable took 3 ms the first time, and 0 ms the second. (Noting first and second runs as the second run may be able to use a cached plan).


MySQL / MariaDB working only sometimes I have the following code in MariaDB that was working perfectly well yesterday. It calculates a rank and orders by the data by another column:
SET @c = (SELECT COUNT(*) FROM myTable);
SET @rownum = 0;
UPDATE myTable
SET rank = (100 / @c) * (@rownum:= 1 + @rownum)
ORDER BY anotherColumn DESC LIMIT 100000;

Today I executed it and the first time through it gave the wrong ranking values. I don't think the SET @rownum = 0 had worked. Now it seems to be working fine again.
Does anyone know why it would work perfectly yesterday and then stop working today and behave so strangely?
EDIT: I believe the @c is the variable that isn't updating when I add a new row. Does anyone know how to force it to recalculate @c based on the current data in the table?

MariaDB 5.2 is really old.  Upgrade.
MariaDB 10.2 (and MySQL 8.0) have RANK and other "windowing functions" to assist with your query.
Setting @variables inside SELECTs should not be trusted.  Rewrite the query to avoid them.
Updating everyone's rank all the time will be a performance drag when you have a lot of users.


Restore superuser role to postgres user I am looking for clear step by step instructions to restore superuser role to postgres user that comes at install. I had to perform the below instructions (which didn't work thus far), because in pgAdmin I created a new Login Role user and assigned superuser privileges. Then I checked off superuser privileges from postgres, only to find out my new superuser was no superuser at all.

Below instructions are also here:
Restoring the superuser account on the postgres server

1) Create a local user called 'postgres'

c:\windows\system32> net user username password /add

2) Assign write rights for this user on the data folder

right click on Postgresql 9.5 install folder at c:\Program files\Postgresql\9.5\
click properties
click security tab
click edit (computer as for administrative rights... ok)
select 'all users' and check box 'total control'
click apply and wait for computer to assign writing privileges

3) Stop the postgres service from services

open the services list on windows through task manager
right click on postgresql 9.5
stop the service

4) Open a command prompt as postgres user (e.g. runas /user:postgres cmd)

c:\windows\system32\runas /user:postgres cmd
enter postgres user password
wait for another cmd window open under postgres user

5) On postgres cmd window run postgres in single user mode

c:\windows\system32>cd c:\Program Files\PostgreSQL\9.5\bin
c:\Program Files\PostgreSQL\9.5\bin>postgres.exe --single -D c:\Program Files\PostgreSQL\9.5\data

At this point I get this error message:

  postgres não pode acessar o arquivo de configuração do servidor "c:/Program/postgresql.conf": No such file or directory

Translating the part from Portugues to English you get:

  postgres cannot access server's configuration file

Your problem is this line:

c:\Program Files\PostgreSQL\9.5\bin>postgres.exe --single -D c:\Program Files\PostgreSQL\9.5\data

If there are spaces in a command line parameter you need to quote like this

c:\Program Files\PostgreSQL\9.5\bin>postgres.exe --single -D "c:\Program Files\PostgreSQL\9.5\data"

This isn't really a problem with Postgres but just how command line works.


SSDT project with working linked server references gets SQL71562 warning I have a VS 2013 solution with two database projects.

One of them, let's call it the "worker" is supposed to get deployed to about 10 different production servers.

The other one, that is supposed to work as an "Controller" instance, is basically supposed to run different stored procedures on the "worker" database on the 10 production servers.

So to realize this, I have scripted 10 Linked Server objects within the "controller" and call the different SPs in the "worker" databases.

Here is a sample for the Linked Server scripts:

EXEC sp_addlinkedserver @server = N'PROD1', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'1.2.3.4\prod1'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'PROD1',@useself=N'False',@locallogin=NULL,@rmtuser=N'aaaaa',@rmtpassword='aaaaa'
GO
EXEC sp_serveroption @server=N'PROD1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC sp_serveroption @server=N'PROD1', @optname=N'rpc out', @optvalue=N'true'
GO

So far so good, this works and the Linked Servers are created on the controller SQL Server and I can use them.

BUT:
Within the SSDT project / solution, I get broken references whenever I use the Linked server objects. Sure, the schema is not aware of the linked server names, since they are only in a string constant in the above script.

Sample: (I translated the message on my own, no guarantee it's exactly right)

  Warning   14  SQL71562: "Procedure: [dbo].[SP1]" contains an unresolved rerference to object [PROD1].[db1].[dbo].[SP1]".

So my problem is that I have kind of a reference from the Controller database schema to the "worker" database schema, but I can't use / insert a database-reference, because I use several different references (10 production servers) to "access" the worker's schema...
I don't want to add 10 database references to my project with the "OtherServer" option (even if I could, I guess it won't allow to add 10 references to the same database project...)

I have a feeling that it should be possible to address the linked server objects in a correct manner in the solution, but I can't figure out how.

I tried Aaron's suggestion with Synonyms, but there I end in the same problem as soon as I use the linked server's name resulting in unknown objects "behind" the name.

I edited the question to make the problem more clear and hope anyone has an idea, because I want a clean solution without hundreds of WARNING messages.

According to the message shown in the Question, this is actually a warning ("warnung") and not an error. An error will stop the build process and prevent the project from being published to the server. Warnings merely show up in the "Error List" window, but don't prevent the build or publish processes from completing.

I created a Database project in Visual Studio 2013 and added a stored procedure that selected from a Linked Server. If the Linked Server name did not match a valid Linked Server on SQL Server, then there was a publication error as reported by SQL Server (not Visual Studio). Otherwise, everything worked as expected. But in both cases, my Linked Server reference in that Stored Procedure in Visual Studio was underlined in blue AND the following message appeared in the "Error List" window after the build finished:

Warning 2   SQL71562: Procedure: [dbo].[GetExpressDBs] has an unresolved reference to
            object [LStoSqlExpress2014].[master].[sys].[databases].

If you don't want to see these warnings, there are two things you can do:

You can simply not show them in the "Error List" window by clicking the button at the top of the "Error List" window that says "X of Y Warning(s)". If you do this, you won't see ANY warnings here (even non-Linked Server-related warnings), but your code will still show the blue underlines that will display the warning if you hover over them.
You can tell Visual Studio / SSDT to ignore just these specific warnings (but still show the others) by doing the following:

Go to Project Properties
Go to the "Build" tab
Enter the following into the "Suppress Transact-SQL warnings:" text field:
71562

Doing the steps noted above will prevent just this one specific warning from showing up in the "Error List" window as well as the blue underlines in the code using the Linked Servers.


LTREE extension does not exist after CREATE EXTENSION I am trying to create a trigger that uses ltree column.

CREATE EXTENSION IF NOT EXISTS ltree;

Running

SELECT * FROM pg_catalog.pg_extension

gives the following output:

extname     |extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
------------|--------|------------|--------------|----------|---------|------------|
plpgsql     |      10|          11|false         |1.0       |NULL     |NULL        |
ltree       |      10|      361964|true          |1.1       |NULL     |NULL        |
postgres_fdw|      10|      361970|true          |1.0       |NULL     |NULL        |

I cannot drop the extension because I get an error that there are dependencies. I was also able to create a table that has a column with data type ltree without an error.

When trying to create a trigger, I get the following error:

SQL Error [42704]: ERROR: type "ltree" does not exist
  Position: 252

This is a snippet of the trigger and the problem seems to be happeing when I declare a variable of type ltree:

CREATE OR REPLACE FUNCTION asset.update_asset_path()

RETURNS TRIGGER

AS $$
    DECLARE
        db_path_ltree ltree;
        single_asset INT;
        s3path TEXT = '';
        ltree_array TEXT[];
        v_asset_name TEXT;
        i_last_element INT;

What is confusing, is this is only happening in one environment for dev. Every other postgres server does not have this problem and is using the same code.

Any ideas why this is happening?

The extension ltree is installed in the schema with the object ID 361964. If you want to know the name of that schema, run

SELECT 361964::regnamespace;

It could be that that schema is not on the search_path when you execute the function. That would explain the error.

If you install an extension in a schema other than public, it is usually best to reference its objects with schema qualification, for example

DECLARE
    db_path_ltree extschema.ltree;


Architectural Help Needed - MySQL or NoSQL for a table with 54 million records We have a table with 54 million records. Here is the table structure.

CREATE TABLE `metaplay` (
  `track_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) DEFAULT NULL,
  `completed` int(11) DEFAULT NULL,
  `skipped` int(11) DEFAULT NULL,
  `created` int(11) DEFAULT NULL,
  `updated` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `created` (`created`),
  KEY `updated` (`updated`),
  KEY `skipped` (`skipped`),
  KEY `track_id` (`track_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

All these data are numeric. At this point, we have ~300 inserts and ~100 updates per minute. We also pull out daily, weekly and monthly track playing records from this table. Now my question is which of the followings will be better for architectural design and best performance. I would appreciate if you can highlight hardware details as well.

MySQL in SSD Cached Storage with 4GB Ram
MySQL in SSD with 4GB Ram
Any NoSQL Solution
Anything else?

Also do you suggest any mysql specific tuning tips for a table like this?

Ah, what is the problem?

  a table with 54 million records

A small table. Nice. I have one here with 8.5 billion rows.

  we have !300 inserts and !100 updates per minute

Yeah. Small. I know. I have one here with around 500 million inserts per day. That is 347222.2222222222 per minute.

All that running on stock hardware. Seriously. THough not totally as low end as what you suggest.

But your metric is totally off. 54 million was large 25 years ago. Today it is small, unless someone tries to run a database server on a mini virtual machine.

For example a non-server with 5gb memory - ouch.

Generally - if you need analysis and this is not document style data - stick to MySql, avoid NoSql databases and learn the relational theory so that you know what you do there.

SSD is great, but I am not sure 5gb memory is decent - come on, that is less than a decent workstation has.

For daily / weekly / mnothly aggregations I would make DAILY aggregates, then use those as basis for larger aggregations. A month is only max. 31 days - so most of hte work is done once per day (in an off time). I strongly would reconsider using ebay-level cheap used hardware, though - and that is what the specs look like.


Windows Server 2019 and SQL Server Backups issue? I have an issue with SQL Server that is really driving me nuts so I wanted to ask if anyone has any suggestions.
I have a program that does a daily backup of the SQL Server database. This has worked for two years.
Now, today, we had to upgrade our server and along with this update (now Windows Server 2019 Essentials), we upgraded to SQL Server 2019 Express.
Now, when I try to perform the backup, I get the error:

Cannot open backup device '\\SERVER\DIRECTORY\BACKUPDIRECTORY\Backup_Wed.BAK'. Operating system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.

I know that this error generally means that SQL Server doesn't have read / write access, but the issue I have is that it DOES.

This IS the SQL Server Instance, as you can see here:

AND, if I run a backup from within SQL Server Management Studio, this does succeed...
I also checked to make sure that the file and it's contents weren't set to "Read Only" and they're not.
Those are the only two causes I'm aware of and all I was able to find searching online over the past several hours.
There's plenty of room on the drive as well (As I said, it's brand new and there's little on it).
Has anyone else seen anything like this or know what else might cause this issue?  The old SQL Server version was the prior version, so I wasn't upgrading from some ancient release either.
And here's my SQL that has worked for several years.  I can't find anywhere where SQL Server 2019 changed the requirements here either:
BACKUP DATABASE THEDATABASE
TO DISK = '\\\\SERVER\\DIRECTORY\\BACKUPDIRECTORY\\Backup_Wed.BAK'
WITH INIT

I'm at a loss...  If SQL Server has access permissions on the directly, how can it NOT have permissions on the directory?
Also, just to clarify, this is a local directory. SQL Server is on the server and is attempting to backup to a directory ON the server.
Is this a bug in Windows Server 2019?
I really appreciate everyone's help and sincerely hope that someone knows a solution here. Thank you again.

this is a local directory. SQL Server is on the server and is attempting to backup to a directory ON the server

If SQL Server has access permissions on the directly, how can it NOT have permissions on the directory?

You're backing up to a share, not a directory.  When accessing a share the account must have permissions both on the share and the underlying directory.
Also it's possible that a Credential is used for NTLM auth to the share and it's attempting to authenticate using some other configured identity.
Change the backup to point directly to the local folder.


How can I handle cases where a group of rows have the same identifier in mysql? For example, in a log file, I have to collect events in a particular time and I have to store them in the database. How can I do that? The log file looks this:

Example:

Log file 1:

12/12/12 1:20:00.000    Start

12/12/12 1:20:00.000    Initialize

12/12/12 1:21:00.989    Stop

Log file 2:

12/12/12 1:20:00.000     Transmit

12/12/12 1:20:00.000     Hello

12/12/12 1:20:00.099     Receive

12/12/12 1:21:00.989     Bye

If I want the events during 12/12/12 1:20:00.000, I have more than one entry.How can I insert them into the database???

You need to create a separate key, unrelated to the timestamp, when inserting the rows into a table. MySQL has a handy auto increment feature for this purpose.


Effect on performance on retrieval of image column from a table with half a billion records on remote server I require to loop through different servers to collect data in a temporary table. The table accessed on these servers have about half a billion records. To retrieve the data from these tables, I filter out rows using a list of values for one of the columns. The query takes about 5-10 mins for each server. However, if I add image column to the select list, the query hangs.

I was wondering if it is acceptable to obtain image column in such a scenario ? Will it hit the performance ?

CREATE TABLE #TempPI(
RowId INT IDENTITY(1,1),
[id]  [char](22)  NOT NULL,
[name]  [varchar](128)  NOT NULL,
[time]  [varchar](50)  NOT NULL,
[url_id] [char](22) NOT NULL
--file IMAGE NULL)

CREATE TABLE #TempBrowser(
[browserid]  [char](22)  NOT NULL)

BULK INSERT #TempBrowser
FROM '\\Shared\BulkLoadDocs\Input.txt' WITH (ROWTERMINATOR = '\n')

INSERT INTO #TempPI
SELECT [id] , [name] , [time] , [url_id]
FROM Server.Database.DBO.table1 a WITH (NOLOCK)
WHERE  exists (Select * from #TempBrowser b  where a.ID=b.BrowserId)

If I use File column, the query hangs.

Regards,
Kanu

You are doing a local join over a huge table, which is tremendously slow. Adding a large makes it even slower, to the point that it times out. You need to redesign your whole logic so that you retrieve only the needed rows from the 1B table, ie. force the filtering to occur remotely. This is not a simple query rewrite, I think you really need to go back to the drawing board and review your whole ETL process, and perhaps use a tool that is actually designed to handle such process, like SSIS (as a side note, SSIS flows that involve a custom W3C common-log-format parser are all to common  and many had rolled their own from logparser.exe->csv->rolap cube... but I digress). While you're at it, also stop using deprecated types, like IMAGE, and use the appropriate VARBINARY(MAX).


Determine Oracle package body memory size We have recently added a lot of new packages to our system, and trying to determine incremental memory usage from a prior version of our product. I know I can get the sharable_mem value from v$db_object_cache.  However, that will not include all packages in our schema, as they may be aged out.

Is there any way to determine the memory size of a package that is not in the object cache?  And, I do not mean lines of source code (ALL_SOURCE), but size of whatever it is that oracle does under the hood to compile/parse the package.

Thanks!

Have a look at PL/SQL Program Limits

  In the shared memory pool, a package specification, ADT specification,
  standalone subprogram, or anonymous block is limited to 67108864
  (2^26) DIANA nodes which correspond to tokens such as identifiers,
  keywords, operators, and so on. This allows for ~6,000,000 lines of
  code unless you exceed limits imposed by the PL/SQL compiler, some of
  which are given in Table C-1.

I remember long time ago with Oracle 8i I "managed" once to get an PLS-00123: program too large error. At this time the limit was 64K DIANA nodes.

I think nowadays it is hardly possible to exceed the limit.


Package mariadb-server-5.5 is not configured yet I am having issues trying to upgrade my mariadb.....

when i do apt-get update && apt-get upgrade i get the following error

Setting up mariadb-server-5.5 (5.5.47+maria-1~trusty) ...
 * Stopping MariaDB database server mysqld                               [ OK ]
 * Starting MariaDB database server mysqld                               [fail]
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing package mariadb-server-5.5 (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mariadb-server:
 mariadb-server depends on mariadb-server-5.5 (= 5.5.47+maria-1~trusty); however                                                                                :
  Package mariadb-server-5.5 is not configured yet.

dpkg: error processing package mariadb-server (--configure):
 dependency problems - leaving unconfigured
No apport report written because the error message indicates its a followup erro                                                                                r from a previous failure.
                          Errors were encountered while processing:
 mariadb-server-5.5
 mariadb-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

It seems to be the result of a muddled setup between installing mariadb and mysql. Rebooting the machine and running apt-get again solved the issue for me.


Trying to import a large sql file using BigDump. Partial fail occuring Problem:

I am using the php script: BigDump to import a sql file that phpMyAdmin says has 9344926 rows.

However when I run it, BigDump calculates and inserts 4050623 rows

It should have Total: 9344926 rows

My SQL file looks like this:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `ree`
--

-- --------------------------------------------------------

--
-- Table structure for table `media`
--

CREATE TABLE IF NOT EXISTS `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `URL` varchar(255) DEFAULT NULL COMMENT 'URL',
  `MLSNUMBER` bigint(20) NOT NULL COMMENT 'MLSNUMBER',
  `FILETYPE` varchar(32) DEFAULT NULL COMMENT 'FILETYPE',
  `MIMETYPE` varchar(128) DEFAULT NULL COMMENT 'MIMETYPE',
  `CREATEDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'CREATEDATE',
  `MODIFYDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'MODIFYDATE',
  `POSITION` int(10) NOT NULL COMMENT 'POSITION',
  PRIMARY KEY (`id`),
  KEY `MODIFYDATE` (`MODIFYDATE`),
  KEY `MLSNUMBER` (`MLSNUMBER`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9540549 ;

--
-- Dumping data for table `media`
--

INSERT INTO `media` (`id`, `URL`, `MLSNUMBER`, `FILETYPE`, `MIMETYPE`, `CREATEDATE`, `MODIFYDATE`, `POSITION`) VALUES
(1, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/35A00/1C07B/35A0011C07B74E/8ECA936E29E4F7.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069136, 'PICTURE', 'image/jpeg', '2012-07-06 09:54:50', '2012-07-06 09:54:50', 7),
(2, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/35A00/1C07B/35A0011C07B74E/13B4A1CB2C6483.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069136, 'PICTURE', 'image/jpeg', '2012-07-06 09:54:52', '2012-07-06 09:54:52', 8),
(3, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/33019/DE74A/330196DE74A041/28DBA86D5DF4D9.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069138, 'PICTURE', 'image/jpeg', '2012-07-06 11:00:53', '2012-07-06 11:00:53', 6);
   ...
   ...
   ...

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Question:

Is the problem with the BigDump script or am I doing something wrong? Or is there a better way to go about my 9 million row import/export?

I finally found the actual problem! It had nothing to do with how I was importing it, it was my local server's export that failed halfway through!

After opening up VIM and going to the end of the file, I found: Fatal error: Maximum execution time of 300 seconds exceeded...

Ahhhh, the problem was with my local server's timeout, so it only exported a partial amount of the data.


The process cannot access the file because it is being used by another process 2 I have log shipping enabled on my database. I created a file, with let's say logical name 'AB' of 50 MB at location E:\Databases\AB.ndf. However, I later dropped this file and created another file with logical name 'AB_File' of 10 MB at same location E:\Databases\AB.ndf. Now the server where the backup was moved to is failing with error:

Error:

1) Could not apply log backup file 'some location of a .trn file' to secondary database

2)The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\Databases\AB.ndf'.

3) File 'AB_File' cannot be restored to 'E:\Databases\AB.ndf'. Use WITH MOVE to identify a valid location for the file.

I have tried running the following script:

RESTORE LOG [db_name] FROM
DISK =  'E:\Log_Shipping\\db_name_20160817070500.trn'
WITH MOVE 'AB_FILE'
TO 'E:\Databases\AB.ndf',
standby = N'E:\Databases\\db_name_20160818.tuf'

I don't seem to be able to fix this. Please help!

The file location on the backup server is the same as that on primary server. Also, the file-group was dropped and the file names are correct.

As you said the error

  The operating system returned the error '32(The process cannot access
  the file because it is being used by another process.)' while
  attempting 'RestoreContainer::ValidateTargetForCreation'

The reason for this is that the name of the database files of the databases you are trying to restore already exists.

This could be for two reasons, either you have used the same create scripts for the same database on all instances or you have already done a restore of the database you are trying to restore now to that instance.

To solve this issue do the following:

  In the restore page in SSMS, press the "options" tab.
  In the "Restore as" column change the name of the datafile(s) and log file to something unique.

Then hit the restore button and that should be it.

for your ref Here


Steps involved in configuring SQL Server and its components after server is renamed I have a cloned (duplicate) server with SQL Server on it and the server name had to obviously change once it was copied. This article describes two actions to perform:

using below code to manually rename SQL Server:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

some changes in SSRS.

My questions are:

are there other things I need to consider/change?
Despite following all of those items, I still get the following issue:
in SSRS Configuration Manager, under Scale-out Deployment, I still have a reference to the old server.
Isn't that article incomplete given it does not speak of SSAS, SSIS, and  Linked servers?

Assumptions:

SQL Server in question has just one instance (default)
There exists no mirroring, failover cluster, replication, etc. It's a simple case.

You've noted that your setup doesn't use mirroring, failover clustering, replication, etc, so it sounds like you've done everything you need to do inside SQL Server.  If you are changing the name of the this server as part of replacing an old decommissioned server, you'll need to ensure the logins from the old server are configured in the new server to ensure clients will be able to connect.

As for Reporting Services, aside from altering the Database settings, you'll also need to change the name of the Report Server URL in the <UrlString> node of the RsReportServer.config in %SystemRoot%\Program Files\Microsoft SQL Server\<SSRSInstanceName>\Reporting Services\ReportServer.  Ensure the UrlString points to the correct name of the server, then restart the SSRS service.

You'll need to change the Report Server Database to refer to the new name of the SQL Server machine:

It's not clear what the actual problem is regarding your scale-out deployment.  Perhaps a screenshot of the problem screen might help explain what you're seeing.


Using the result set of a select query as the input of another select I need to make three select queries over three different tables, using the outputs of each select query, the catch is each one gives multiple results. Here is how I do it.

Select "Title", "Num" from "Table" where "Id" in (

    Select "Id" from "Table2" where "Id"    in (

        select distinct "Id" from "Table3" where foo-clause
           )

)

İt only gives me the result of one of the results. How can I make each one use multiple inputs?

You need to join the 3 tables together into one select rather then use the other two tables as part of a where clause. I have provided an example of joining all 3 together and having a condition for the third table.

SELECT a."Title", a."Num"
FROM   "Table" a,
       "Table2" b,
       "Table3" c
WHERE  a."Id" = b."Id"
AND    a."Id" = c."Id"
AND    c.foo-clause = foo-clause


Updating rows conditionally with a gapless ascending value A user has a table they would like to update.  Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less.  The rows that will not contain this ascending value also need to be updated.  Is there a way to do this with a single UPDATE statement?

Create sample data:

CREATE TABLE t1 AS (
   SELECT CHR(ASCII('a')+rownum-1) letter, rownum Number1, 0 Number2
   FROM dual connect by rownum<=7);

Update statement that assigns an ascending rownum, but it isn't gap-less:

UPDATE t1 SET Letter=’x’,
   Number2 = DECODE(letter,’a’,rownum,’d’,rownum,’e’,rownum,NULL);

Using a sequence returns the same results:

CREATE SEQUENCE s1;
UPDATE t1 SET letter=’x’,
   number2 = DECODE(letter,’a’,s1.nextval,’d’, s1.nextval,’e’, s1.nextval,NULL);

The data should look like this when complete:

L    NUMBER1    NUMBER2
- ---------- ----------
x          1          1
x          2
x          3
x          4          2
x          5          3
x          6
x          7

I'm not looking for a trick applicable only to my specific test case, but a general solution to the problem.

See this discussion on AskTom about the way expressions are evaluated. The decode function does perform short-circuit in most cases: right-side expressions are not evaluated if a condition on the left is evaluated to true. Sequence are special however, they are evaluated for all lines in all cases.

The easiest workaround is to use a function that calls the sequence:

SQL> CREATE SEQUENCE seq;

Sequence created

SQL> CREATE OR REPLACE FUNCTION f RETURN NUMBER
  2  AS
  3     l_result NUMBER;
  4  BEGIN
  5     SELECT seq.nextval INTO l_result FROM dual;
  6     RETURN l_result;
  7  END;
  8  /

Function created

SQL> UPDATE t1 SET letter='x',
  2     number2 = DECODE(letter,'a',f,'d', f,'e', f,NULL);

7 rows updated

SQL> select * from t1;

LETTER    NUMBER1    NUMBER2
------ ---------- ----------
x               1          1
x               2
x               3
x               4          2
x               5          3
x               6
x               7

7 rows selected

As you can see the function is evaluated only when the DECODE expression requires it.

You can work a pure SQL solution in most cases. If we assume that the table has a PK (I'll take number1), this self-join SQL solution would work:

SQL> UPDATE t1 t1_out
  2     SET letter  = 'x',
  3         number2 = CASE WHEN letter IN ('a', 'd', 'e') --
  4                        THEN (SELECT COUNT(*)
  5                                FROM t1 t1_in
  6                               WHERE letter IN ('a', 'd', 'e')
  7                                 AND t1_in.number1 <= t1_out.number1)
  8                   END;

7 rows updated

SQL> select * from t1;

LETTER    NUMBER1    NUMBER2
------ ---------- ----------
x               1          1
x               2
x               3
x               4          2
x               5          3
x               6
x               7

7 rows selected

You have probably tried an analytics solution. It is possible to write a query that will return the appropriate result set but update and analytics don't work very well together (analytics tend to produce non-updatable views).


Survey Database - Respondent Table (1NF or 3NF - Pros and Cons) Building out a Survey ERD ODS layer.  Now i'm debating on the design for my Respondent Table.  Not sure if its best to have it as 1NF or 3NF.  FOr a quick back story I get a survey extract with respondent, response, and questions data.  This data is loaded as his in a staging area.  From there I am moving the data from staging to ODS than to DW.  But wanted to get some feedback and pros and cons on my respondent table.

Now here is what i'm thinking for my design for the 1NF respondent table and 3NF respondent table.

- **1NF Respondent Table**
 - RESPONDENT_ID PK
 - RESPONDENT_SERIAL_ID
 - RESPONDENT_AGE_CODE
 - RESPONDENT_AGE_DESC
 - RESPONDENT_GENDER_CODE
 - RESPONDENT_GENDER_DESC
 - RESPONDENT_STATELIVEIN_CODE
 - RESPONDENT_STATELIVEIN_DESC
 - RESPONDENT_REGION_CODE
 - RESPONDENT_REGION_DESC
 - CREATE_DATE
 - UPDATE_DATE
 - CHECK_SUM - MD5
 - CURRENT_IND

And 58 other attributes that define a respondent such as zipcode, political party, employment and etc.

 - **3NF Respondent Table**
 - RESPONDENT_ID PK
 - RESPONDENT_SERIAL_ID
 - RESPONDENT_TYPE -- Example (AGE, STATELIVEIN, ZIPCODE, POTPARTY and
   etc)
 - RESPONDENT_CODE
 - RESPONDENT_DESC
 - CREATE_DATE
 - UPDATE_DATE
 - CHECK_SUM - MD5
 - CURRENT_IND

Now my question what are some of the pros and cons on both design of each table and lastly which would you go with and if there are any improvements you see please let me know.

Your question really is not about normalization.  Instead it is about specialization vs. generalization with respect to design.  Let me give some background to show why this is the case.

Background

A table is a relational (R-Table) table, and thus normalized (meaning in 1NF by definition) if in its design a discipline is followed that ensures:

Distinct, un-ordered rows
Uniquely named, un-ordered columns
Each column contains a single value from the corresponding domain

Normalization, more formally called Projection-Join Normalization, is a scientific process in which one can remove redundancies in R-tables due specifically to join dependencies which are not implied by the candidate keys. The join dependencies are exploited by taking projections based on them to create two or more tables from the original table which removes the redundancy. It is important to note that normalization cannot remove all redundancy. Instead, it can remove only redundancies caused by join dependencies not being implied by the primary key.

The first 3 normal forms and Boyce-Codd Normal Form (BCNF) deal with redundancies due to functional dependencies not implied by the candidate keys.  While most designers talk about normalization to the third normal form, what they really mean is normalization to BCNF as that is the normal form with respect to the functional dependencies.

Functional Dependencies

In order to determine if either table is in any normal form, 1NF or 3NF, (and thus an R-Table), we must clearly state the functional dependencies.  A mistake most designers make is to assume the functional dependencies based upon what makes sense to them.  In simple examples this isn't usually a problem, but in complex real world scenarios it is deadly.  For your example let us assume the following functional dependencies for the first table:

RESPONDENT_ID -> RESPONDENT_SERIAL_ID, RESPONDENT_AGE_CODE, RESPONDENT_GENDER_CODE, RESPONDENT_STATELIVEIN_CODE,
RESPONDENT_REGION_CODE, CREATE_DATE, UPDATE_DATE, CHECK_SUM - MD5,
CURRENT_IND, 58 other attributes that define a respondent such as zip code,
political party, employment and etc.
RESPONDENT_AGE_CODE -> RESPONDENT_AGE_DESC
RESPONDENT_GENDER_CODE -> RESPONDENT_GENDER_DESC
RESPONDENT_STATELIVEIN_CODE -> RESPONDENT_STATELIVEIN_DESC
RESPONDENT_REGION_CODE -> RESPONDENT_REGION_DESC

Aside:  One thing I will gloss over in the interest of space but that is extremely important is truly understanding all of the candidate keys.  In this example we have RESPONDENT_ID as the PK.  Are there other data elements that uniquely identify a respondent?  Just designating a surrogate key upon which we declare all the other now "non-key" columns functionally dependent does not change the functional dependencies of those non-key columns on some other column or set of columns which also form a candidate key.

Another Aside: There is a CURRENT_IND column.  That makes the PK of RESPONDENT_ID very suspicious.  If this means what it usually means, then the table could potentially have multiple rows per RESPONDENT_ID, many of which are not current, and one of which is.  Then the real candidate key is something like RESPONDENT_ID plus CREATE_DATE.  This makes the example too complex to analyze though so I will ignore it.  But in reality this is a real problem that implies you really have two types of things here - current respondents and former respondents.  This gets into temporal concerns which are complicated to address.

Let us assume the following functional dependencies for the second table:

RESPONDENT_ID -> RESPONDENT_SERIAL_ID, 58 other attributes that
define a respondent such as zip code, political party, employment and etc.
RESPONDENT_ID, RESPONDENT_TYPE -> RESPONDENT_CODE, CREATE_DATE,
UPDATE_DATE, CHECK_SUM - MD5, CURRENT_IND
RESPONDENT_TYPE, RESPONDENT_CODE -> RESPONDENT_DESC

Evaluating The First Respondent Table

Given the assumed functional dependencies, the first table is indeed in 1NF, and thus an R-Table, as it meets the rules laid out in the background.  However, realize that you really must look at the data to really determine if those rules are being met.  For example, we may assume that each column contains a single value, but upon inspection of the data find that some column has been populated with multiple values separated by commas or pipes.  An even more insidious violation of normalization may be using the order of rows or columns to encode additional meaning, for example the arrival order of survey responses.  For this example let us assume none of this to be the case and the table is normalized and thus an R-Table.

The R-Table is also in 2NF as there are no partial key dependencies.  The T-Table is clearly not in 3NF, as there are transitive dependencies.  Each of the 4 DESC columns functionally dependent on the CODE columns, which in turn functionally depend on the RESPONDENT_ID column.

Evaluating The Second Respondent Table

Given the assume functional dependencies, the second table is not in 3NF.  It is not even normalized (in 1NF) and thus not an R-Table.  The RESPONDENT_ID is claimed to be the PK but there is also a composite candidate key on the RESPONDENT_ID plus the RESPONDENT_TYPE.  This is because there must now be multiple rows per respondent, each with a different type and that type's corresponding code.  The 3NF violation with a transitive dependency between the RESPONDENT_ID and RESPONDENT_TYPE functionally determining RESPONDENT_CODE and then RESPONDENT_TYPE and RESPONDENT_CODE functionally determining RESPONDENT_DESC is still present.

Conclusion

Many designers often assume that taking a specific data element, such as a categorical domain for age, and breaking it into two data elements which are a name value pair is performing "normalization."  It is not.  It is instead choosing a more generalized vs. more specific design.  Normalization is a scientific process with clear rules.  An R-Table in BCNF clearly has less redundancy that one on 2NF.  The BCNF R-Table is clearly better with respect to avoiding redundancy.  Deciding between specialization vs. generalization however is not a scientific process.  So how to know which is better?  Well it depends on the situation.  The advantage to generalization is that it is more flexible for future considerations only in that the data structure doesn't have to change when more data points are added, but comes at a cost of more complexity.  The advantage to specialization is it is simpler and enables the DBMS to constraint the domain of each specific column, but it does require additional columns to be added when new data points are added.  Personally I prefer to do things like Einstein said "as simple as possible but no simpler."

With respect to the two tables you've presented, I prefer the first design (over a corrected second design) but again not because it is more or less normalized.  It is simply more clear what is being represented.  The only change from a normalization perspective I would make would be to place each CODE --> DESC combination into its on R-Table so as to achieve full normalization.  If you want to be sure to avoid the anomalies associated with R-Tables that are less than fully normalized you must ensure full normalization.

References

A good primer on normalization (and on data management fundamentals in general) is Fabian Pascal's Practical Database Foundation Series.  Paper number one describes business modeling (where dependencies are determined) and paper number two which describes normalization. Chris Date's Database Design and Relational Theory provides an in-depth look at normalization along with orthogonality with respect to reducing redundancy in logical database designs.


How to optimize MySQL query with more than 2 inner joins select id
from customer_details
where store_client_id = 2
  and id not in ( select customer_detail_id
                  from  orders
                  where store_client_id = 2
                    and total_spent > 100
                  group by customer_detail_id )
    or id in ( select tcd.id
               from property_details as pd
                  , customer_details as tcd
               where pd.store_client_id = 2
                 and pd.customer_detail_id = tcd.customer_id
                 and pd.property_key = 'Accepts Marketing'
                 and pd.property_value = 'no' )
    and id IN ( select customer_detail_id
                from orders
                where store_client_id = 2
                group by customer_detail_id
                having count(customer_detail_id) > 0 )
    or id in ( select tor.customer_detail_id
               from ordered_products as top
                  , orders as tor
               where tor.id = top.order_id
                 and tor.store_client_id = 2
               group by tor.customer_detail_id
               having sum(top.price) = 1 )`

I have this MySQL query with inner joins so when it runs in MySQL server it slows down. What is the issue I can't find?

But after 4-5 minutes it returns 15,000 records. These records is not an issue may be.

In some tutorial suggest to use Inner join, Left join,... But I don't know how to convert this query in Join clause. Any help will be appreciated. Thanks in advance.

I find it rather difficult to understand your query, so let's break it apart:

select id
from customer_details
where store_client_id = 2
  and id not in ( select customer_detail_id
                  from  orders
                  where store_client_id = 2
                    and total_spent > 100
                  group by customer_detail_id )
    or id in ( select tcd.id
               from property_details as pd
                  , customer_details as tcd
               where pd.store_client_id = 2
                 and pd.customer_detail_id = tcd.customer_id
                 and pd.property_key = 'Accepts Marketing'
                 and pd.property_value = 'no' )
    and id IN ( select customer_detail_id
                from orders
                where store_client_id = 2
                group by customer_detail_id
                having count(customer_detail_id) > 0 )
    or id in ( select tor.customer_detail_id
               from ordered_products as top
                  , orders as tor
               where tor.id = top.order_id
                 and tor.store_client_id = 2
               group by tor.customer_detail_id
               having sum(top.price) = 1 )

This can be translated into:

select id
from customer_details
where store_client_id = 2
  and id not in ( select customer_detail_id
                  from  orders
                  where store_client_id = 2
                    and total_spent > 100
                  group by customer_detail_id )

union

select id
from customer_details
where id in ( select tcd.id
              from property_details as pd
                 , customer_details as tcd
              where pd.store_client_id = 2
                and pd.customer_detail_id = tcd.customer_id
                and pd.property_key = 'Accepts Marketing'
                and pd.property_value = 'no' )
  and id IN ( select customer_detail_id
              from orders
              where store_client_id = 2
              group by customer_detail_id
              having count(customer_detail_id) > 0 )

union

select id
from customer_details
where id in ( select tor.customer_detail_id
              from ordered_products as top
                 , orders as tor
              where tor.id = top.order_id
                and tor.store_client_id = 2
              group by tor.customer_detail_id
              having sum(top.price) = 1 )

Now, the implicit joins should for readability be transformed into explicit ones:

select id
from customer_details
where store_client_id = 2
  and id not in ( select customer_detail_id
                  from  orders
                  where store_client_id = 2
                    and total_spent > 100
                  group by customer_detail_id )

union

select id
from customer_details
where id in ( select tcd.id
              from property_details as pd
              join  customer_details as tcd
                on pd.customer_detail_id = tcd.customer_id
              where pd.store_client_id = 2
                and pd.property_key = 'Accepts Marketing'
                and pd.property_value = 'no' )
  and id IN ( select customer_detail_id
              from orders
              where store_client_id = 2
              group by customer_detail_id
              having count(customer_detail_id) > 0 )

union

select id
from customer_details
where id in ( select tor.customer_detail_id
              from ordered_products as top
              join orders as tor
                  on tor.id = top.order_id
              where tor.store_client_id = 2
              group by tor.customer_detail_id
              having sum(top.price) = 1 )

IN is often a slow predicate, I would try replacing it with [NOT] EXISTS. PITFALL: IN and EXISTS behaves different when nulls are involved, be careful so that you don't change the semantics of the query:

select id
from customer_details as x
where store_client_id = 2
  and not exists (
         select 1
         from  orders
         where store_client_id = 2
           and total_spent > 100
           and customer_detail_id = x.id )

union

[...]


Incorrect string value: '\\xD0\\xA2\\xD0\\x9E\\xD0\\x9E...' for column Username in MySQL DataGrip specific I know that such error can be caused by incorrect character set applied to some table, but in my case it's a bit different:
I have some procedure with select, create temporary table and cursor...
The actual problem is that if I do DROP/CREATE procedure from JetBrain's DataGrip and CALL it - error appears. But in case I do DROP/CREATE of SAME procedure without any modification from other software, for example I've used DBForge - CALL works just as expected! What can cause such behavior? I've used DataGrip for years now and this is the first time after which I start to worry about recreating procedures on production database.
Additional information: the error is caused by FETCH of Username column in cursor loop body. In case of commenting that part - everything works. So there is a problem FETCHING Username to my local variable DECLARE Username VARCHAR(300);. Sizes of variables are same.

A bit strange case, but I'll leave the answer we've came up to ourselves:

Fixed after I've used NVARCHAR (or VARCHAR CHARACTER SET UTF8;), not VARCHAR for variable declaration and for temporary table's creation scheme. The problem appeared ONLY when we used temp table in the script. For regular tables our VARCHAR with default DB collation/charset worked OK.
I still don't know why DBForge ignored it and calling procedure
created in it caused no problems (but there really were problems: "?"
instead of UTF8 characters).

UPDATE: added issue on DataGrips tracker, because I think that failing to execute the procedure is not the correct behavior in such case: https://youtrack.jetbrains.com/issue/DBE-11789


how to capture the data using DMV's in sql server 2008 r2? Hi i am working on sql server 2008 r2,
i have a situation where i wanted to capture the DMV's data into a table, i would like to know when DMV's tables get refreshed or data flushed out. because as soon as the run going on at the production environment the DMV's data changes. i wanted to capture each data that comes in the DMV's can somebody please help me to capture this data i need this in production to look on CPU,Reads,writes for the data or the query that are running by the testing team.
Thanks! in advanced

The clue is in the name, DMVs are Dynamic Management Views. The data they return is dynamic and will be the latest values available to the system, so they will be constantly updating themselves. So the best you could do is to sample them at a set interval and then try to extrapolate the intermdiate values if you really need them

Some of the DMVs do capture cumulative statistics (sys.dm_exec_query_stats for example, captures cumulative stats about queries). But they don't offer the sort of access you might be looking for at the individual query level.

Your other option may be to use Extended Events ( http://msdn.microsoft.com/en-us/library/bb630354%28v=sql.105%29.aspx ) to capture the queries that the devs are submitting. Pinal Dave provides a nice example of capturing queries - http://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/ - his is based on capturing long running queries, but you could easily reduce the time critreia for capture, or remove it completely in favour of a login match or applictaion match as required.


Lazy Writes Performance Issues My SQL Server sometimes reports a high amount of Lazy Writes.
e.g. "Lazy Writes per second is 119 Writes/sec"

What should I be checking to find out why this is high?
What are potential causes?

The lazy writer process is closely related to checkpoints, so I'll start with that first

Best SQL Server performance is achieved when pages are read from the buffer. To provide enough free space in the buffer, pages are moved from the buffer to disk. These pages are usually moved at a check point, which can be:

automatic (occurs automatically to meet the recovery interval request)
indirect (occurs automatically to meet the database target recovery time)
manual (occurs when the CHECKPOINT command is executed)
internal (occurs along with some server-level operations, such as backup creation)

At a checkpoint, all dirty pages are flushed to disk and the page in the buffer cache is marked for overwriting

  “For performance reasons, the Database Engine performs modifications
  to database pages in memory—in the buffer cache—and does not write
  these pages to disk after every change. Rather, the Database Engine
  periodically issues a checkpoint on each database. A checkpoint writes
  the current in-memory modified pages (known as dirty pages) and
  transaction log information from memory to disk and, also, records
  information about the transaction log.”

Database Checkpoints (SQL Server)

The lazy writer process periodically checks the available free space in the buffer cache between two checkpoints. If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache

If SQL Server needs more memory and the buffer cache size is below the value set as the Maximum server memory parameter for the SQL Server instance, the lazy writer will take more memory

If SQL Server is under memory pressure, the lazy writer will be busy trying to free enough internal memory pages and will be flushing the pages extensively. The intensive lazy writer activity affects other resources by causing additional physical disk I/O activity and using more CPU resources

To be sure that the server is under memory pressure, check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure. Check the Free List Stalls/sec value as well. If above 2, consider adding memory to the server