How do I get SSMS to use the relative path of the current script with :r in sqlcmd mode like SSDT does? If I have foo.sql and bar.sql in the same folder, foo.sql can reference bar.sql when run from SSDT in sqlcmd mode with :r ".\bar.sql". However, SSMS won't find it. Procmon shows SSMS is looking in %systemroot%\syswow64:

How do I tell SSMS to look in the folder that the current script is saved to without explicitly declaring the path?

Getting a relative path in SSMS is not that straight-forward since you are not executing the script; SSMS has loaded the script into memory and is executing its text. So the current directory/folder is the default process starting folder. You can see this by running the following in SQLCMD mode in SSMS:

!! PWD

However, I did find a kinda-sorta way to do this. I admit that this is not the super-most ideal way to do this, however, it currently seems to be the only way to get a true relative path (given that setting the path in a variable isn't really "relative" per se).

So what you can do is:

Execute a DOS command to find foo.sql and store the path to that file in a text file in a folder that you can get from SSMS, either because it is a hard-coded path or because it uses an environment variable that is available to both the DOS command and to SQLCMD mode in SSMS
When storing the path in that file, store it as a SQLCMD mode command that sets a variable to that path
Import that text file into SSMS using :r and it will set that variable to the desired path

!! CD C:\ & FOR /F %B IN ('DIR /B /A -HS /S foo.sql') DO ECHO :setvar mypath "%~dpB" > %TEMP%\relative_path.txt

:r $(TEMP)\relative_path.txt

:r $(mypath)\bar.sql

Notes:

The above method assumes only 1 file on the system is named foo.sql. If more than one file has that name, the last one found will be the path set in the relative_path.txt file
Doing the CD C:\ will start at the root of the C: drive. This is probably not the most efficient place to start. If you generally have your SQL files in an area such as C:\Users{YourLogin}\Documents\Visual Studio 2013\Projects, then just change the CD command to get closer to the destination, such as:

!! CD C:\Users\{YourLogin}\Documents\Visual Studio 2013 & FOR ...


Recover the disk space after deleting rows from table I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the production queries. Is there any other way to recover the space without affecting the production queries.
innodb_file_per_table is ON and there are about 15,221,827 rows in the table.
There are no constraints or foreign keys.

You will have to take the table offline to prevent any reads or writes that would get locked  and probably have to roll back. If the table is called mydb.mytable, here is what you must do:
USE mydb
DROP TABLE IF EXISTS mytablenew;
CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable RENAME mytableold;
INSERT INTO mytablenew SELECT * FROM mytableold;
ANALYZE TABLE mytablenew;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

Any queries attempting to read from or write to mytable will fail immediately rather than hang on a lock from OPTIMIZE TABLE.
Give it a Try !!!
UPDATE 2013-08-26 15:24 EDT
Look back at the code I suggested. Please note the line
ALTER TABLE mytable RENAME mytableold;

That line takes the table offline with respect to the application.
To get a good idea how long it should take to optimize run the following:
USE mydb
CREATE TABLE mytablenew LIKE mytable;
INSERT INTO mytablenew SELECT * FROM mytable;

However long this takes, is how long it will actually take to run.


Error wile debugging (signature buffer too small) I'm trying to debug a package in SQL Developer, but I keep getting error:

ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [pbjrms - signature buffer too small], [38], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [pbjrms - signature buffer too small]
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Closed Connection

Package compiled without errors. Anyone had this problem before?

Its a known bug, fixed in 9.2.0.5.
This is the note on Oracle Support that describes this error:

Bug 2829071 - PLS-801/OERI:[15419] when debugging PLSQL from JDWP debugger (Doc ID 2829071.8)


Multi-table sum of cost breakdown by pricing category Looking for help with a query to get the sum of the subtotals for each price category from a list of line items, by order. Scratching my head on the group by stuff.

There are 3 categories: setup, build, and finishing. I'd like to get a total for each category per order. Testing shows my sql foo is rusty and looking for guidance on how to roll this stuff up. I'm already querying a view to make this simpler.

http://sqlfiddle.com/#!9/acb02/3

CREATE TABLE `OrderLineItems` (
  `orderId` INT(11) NOT NULL,
  `dt` DATETIME,
  `buyerEmail` VARCHAR(512),
  `orgId` INT(11) NOT NULL,
  `description` VARCHAR(512),
  `application` VARCHAR(512),
  `category` VARCHAR(30),
  `quantity` INT(5),
  `price` DECIMAL(9,2),
  `subtotal` DECIMAL(19,2)
)

Looking for one row per order in the result, with the total for each price category. A total cost for the order would be bonus.

 Order Id | Setup Total | Build Total | Finishing Total | All Total
--------------------------------------------------------------------

Also, adding in the date, is it possible to get a grouping by month for the 3 categories?

 Month | Setup Tot | Build Tot | Finishing Tot | All Tot
---------------------------------------------------------
 1/16 |
 2/16 |

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query.

Doing a simple row-based group of your sample dataset:

MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2;
+---------+-----------+----------+---------------+
| orderId | category  | count(*) | sum(subtotal) |
+---------+-----------+----------+---------------+
|  323607 | BUILD     |        5 |       2515.00 |
|  323607 | FINISHING |        1 |         30.00 |
|  323607 | SETUP     |        2 |         95.00 |
|  323614 | BUILD     |        4 |       4614.00 |
|  323614 | SETUP     |        1 |         36.00 |
|  323636 | BUILD     |        2 |        840.00 |
|  323636 | FINISHING |        1 |        130.00 |
|  323636 | SETUP     |        7 |       1152.00 |
|  323649 | BUILD     |        1 |        240.00 |
|  323649 | FINISHING |        1 |         30.00 |
|  323649 | SETUP     |        4 |       1995.00 |
+---------+-----------+----------+---------------+
11 rows in set (0.00 sec)

You can pivot the subtotals to columns using the trick of using an IF statement in the select. This way you get the main part of your data with only one query:

SELECT orderId , count(*),
sum(if(category='BUILD', subtotal, 0)) as Build_Total,
sum(if(category='SETUP', subtotal, 0)) as Setup_Total,
sum(if(category='FINISHING', subtotal, 0)) as Finishing_Total,
sum(subtotal) as Total
from OrderLineItems
GROUP BY 1;

With output:

+---------+----------+-------------+-------------+-----------------+---------+
| orderId | count(*) | Build_Total | Setup_Total | Finishing_Total | Total   |
+---------+----------+-------------+-------------+-----------------+---------+
|  323607 |        8 |     2515.00 |       95.00 |           30.00 | 2640.00 |
|  323614 |        5 |     4614.00 |       36.00 |            0.00 | 4650.00 |
|  323636 |       10 |      840.00 |     1152.00 |          130.00 | 2122.00 |
|  323649 |        6 |      240.00 |     1995.00 |           30.00 | 2265.00 |
+---------+----------+-------------+-------------+-----------------+---------+
4 rows in set (0.00 sec)

You can use the same approach to get your date-based rollups.


SSIS Data Flow data access mode - what's the point of 'table or view' vs fast load? Using the SQL Server Business Intelligence Development Studio, I do a lot of flat file to OLE DB destination data flows to import data to my SQL Server tables.  Under "Data access mode" in in the OLE DB destination editor, it defaults to "table or view" rather than "table or view - fast load".  What is the difference; the only discernible difference I can perceive is that the fast load transfers the data much faster.

The OLE DB Destination Component's Data Access Modes comes in two flavors - fast and non-fast.

Fast, either "table or view - fast load" or "table or view name variable - fast load" means that data will be loaded in a set-based fashion.

Slow - either the "table or view" or "table or view name variable" will result in SSIS issuing singleton insert statements to the database. If you're loading 10, 100, maybe even 10000 rows, there's probably little appreciable performance difference between the two methods. However, at some point you're going to saturate your SQL Server instance with all these piddly little requests. Additionally, you're going to abuse the heck out of your transaction log.

Why would you ever want the non-fast methods? Bad data. If I sent in 10000 rows of data and the 9999th row had a date of 2015-02-29, you would have 10k atomic inserts and commits/rollbacks. If I was using the Fast method, that entire batch of 10k rows will either all save or none of them. And if you want to know which row(s) errored out, the lowest level of granularity you will have is 10k rows.

Now, there are approaches to getting as much data loaded as fast as possible and still handle dirty data. It's a cascading failure approach and it looks something like

The idea is that you find the right size to insert as much as possible in one shot but if you get bad data, you're going to try resaving the data in successively smaller batches to get to the bad rows. Here I started with a Maximum insert commit size (FastLoadMaxInsertCommit) of 10000. On the Error Row disposition, I change it to Redirect Row from Fail Component.

The next destination is the same as above but here I attempt a fast load and save it in batches of 100 rows. Again, test or make some pretense of coming up with a reasonable size. This will result in 100 batches of 100 rows sent because we know somewhere in there, there is at least one row that violated the integrity constraints for the table.

I then add a third component to the mix, this time I save in batches of 1. Or you can just change the table access mode away from the Fast Load version because it'll yield the same result. We will save each row individually and that will enable us to do "something" with the single bad row(s).

Finally, I have a failsafe destination. Maybe it's the "same" table as the intended destination but all the columns are declared as nvarchar(4000) NULL. Whatever ends up at that table needs to be researched and cleaned/discarded or whatever your bad data resolution process is. Others dump to a flat file but really, whatever makes sense for how you want to track bad data works.


Just how much is a query supposed to slow down the whole system? This is similar to my earlier question but more generic. I have a system with 16GB of memory, 16 cores, supposedly quality hard drives, RHEL 5, MySQL 5.0.x. Yes, I know, we're working on upgrading that. But this is what we have.

I just ran a few queries with large inserts and complicated joins that caused load to spike to 6, from where it usually is at 1.10. People started reporting that the website was becoming sluggish. These are all MyISAM table, but this is not about row vs table locking - the performance loss was on an entirely separate set of tables from what was being operated on. This is not about 'can't execute other queries', this is about 'everything else on the system slows down'.

I've been trying to google this and can find no other resource saying a query is supposed to spike server load like that. So I'm starting to wonder: Is it just me? If so, do you have any suggestions? And if not, do you have any mitigation suggestions?

Complicated joins can be a precursor to temp table usage. By default, tmp_table_size is set to whatever the value max_heap_table_size is (16M).
For any given query, once a tmp table reaches 16M in RAM, it performs the following migration

the query suspends operation
mysqld will migrate the entire 16M of tmp table data onto disk as a MyISAM table
the query continues until completion

You must also keep in mind where the disk-based tmp tables will land. By default, the folder where disk-based tmp tables will land in defined in tmpdir.

For Windows, this would be C:\TEMP or C:\TMP (environment variable TEMP or TMP)
For Linux, this would be /tmp

If the mysqld is competing for tmpdir usage with the OS, your should feel it rather quickly because the OS would start swapping to find the elbow room in needs in the system temp folder while the query in question might be trying to force a tmp table into the same OS tmp space.
You may want to look into one or more of the following

mounting /tmp to a RAM disk
mounting /tmp to another disk away fromthe root partition
setting tmp_table_size and max_heap_table_size to 2K (that's not a typo, I SAID 2K) to force tmp tables to goto tmpdir quickly, thus shortening tmp table memory-to-disk migration time
I wrote about this in StackOverflow

UPDATE 2012-03-13 15:11 EDT
Setting tmp_table_size and max_heap_table_size to 512M is dangerously high. In the event of bad queries (especially the Cross Join (aka Caretsian Join)), mysqld will spend time migrating a 512M MEMORY table to a disk-based MyISAM table before the query continues processing. You may need to lower that number significantly to force tmp tables into disk faster. This is imperative since tmp_table_size and max_heap_table_size are set per DB Connection.


Order of Dates in Oracle Database I have following table which records employee's attendance in a company.

    CREATE TABLE EMP_ATNDNC
    (
    WORK_DAY    DATE                         NOT NULL,
    EMP_ID      VARCHAR2(20)                 NOT NULL,
    STATUS      VARCHAR2(1)   DEFAULT '0'    NOT NULL,
    TMSTMP      TIMESTAMP,
    RCRD_VER    TIMESTAMP                    NOT NULL,

   CONSTRAINT  EMP_ATNDNC_PK PRIMARY KEY(WORK_DAY,EMP_ID),
   CONSTRAINT  EMP_ATNDNC_FK_DAY FOREIGN KEY(WORK_DAY) REFERENCES SCHOOL_OPEN_RCRD(WORK_DAY),
   CONSTRAINT  EMP_ATNDNC_FK_ID FOREIGN KEY(EMP_ID) REFERENCES EMP_RCRD(EMP_ID)
   );

As of now, it has two records:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM

If I run this query:

    SELECT * FROM EMP_ATNDNC WHERE WORK_DAY BETWEEN TO_DATE('24-AUG-16') AND TO_DATE('30-SEP-16');

I get only this record:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                          RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM     27-AUG-16 05.38.46.022000000 PM

Also If I run this query:

    SELECT * FROM EMP_ATNDNC ORDER BY WORK_DAY ASC;

I get this result:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM

Instead of:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM

Why is this happening?

And that is why we never rely on an unspecified date format (or the default in this case).

SQL> create table t(d date);

Table created.

SQL> insert into t values (date'2016-09-29');

1 row created.

SQL> insert into t values (date'2116-08-26');

1 row created.

SQL> select * from t where d BETWEEN TO_DATE('24-AUG-16') AND TO_DATE('30-SEP-16');

D
---------
29-SEP-16

SQL> select * from t order by d;

D
---------
29-SEP-16
26-AUG-16

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select * from t order by d;

D
-------------------
2016-09-29 00:00:00
2116-08-26 00:00:00

Do not use date format such as DD-MON-RR, as it does not show the century.

Also, do not use TO_DATE without specifying the date format explicitly.

Instead of TO_DATE('24-AUG-16'), use TO_DATE('24-AUG-16', 'DD-MON-RR'), but you still miss the century part. TO_DATE('24-AUG-2016', 'DD-MON-YYYY') is better, but I prefer using the date'2016-08-27 syntax that has a fixed format.


case statement when grouping? I have data that contains multiple games, each game would have several users picking it and they would all be grouped into a single week.

At the end of that week I evaluate the results and assign a score. In the list of games there is one bonus game and I want to get the margin from that game, but it doesn't work because I believe there is a grouping it always returns null.

my query is:

SELECT COUNT(game.game_id) AS correct_picks,
       sum(game.points) points,
       pick.user_id, game.bonus,
       user.user_id, user.username,
       user.avatar_date, user.avatar_width, user.avatar_height,
       user.gravatar, user.gender,
       CASE
         WHEN game.bonus = 1 THEN ABS(SUM((game.hteam_score - game.vteam_score) - (pick.hscoreary - pick.ascoreary)))
         ELSE NULL
       END AS margin
FROM      xf_nflj_pickem_game game
LEFT JOIN xf_nflj_pickem_pick as pick ON (pick.game_id = game.game_id)
LEFT JOIN xf_user             as user ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id)
        AND game.week_id = 1
        AND game.pool_id = 1
        AND pick.team_id <> 0
GROUP BY pick.user_id

as you can see I have the case statement. Now if there is just one game then this seems to work but when there are multiple games it just returns null.

Any thoughts on how I could achieve this in a single query?

Dan

Don't get me wrong. I suggest you to get rid of user information to make you query more readable and because user information is out of scope of your answer. IMHO is better to have more small queries with a single purpose that to have single big query that retrieves all information your application need, if there isn't a good reason to do so (typically performace). Small queries are more maintainable, more understandable (seeing then after years) ... you can add here your prefered boring virtue :-)

I attempt to outline an answer because I did not understand exactly what you want to achieve.

I suppose there is only a bonus game for week and poll so the following query always returns a number:

select (game.hteam_score - game.vteam_score)
from xf_nflj_pickem_game as game
where game.bonus = 1 and game.week_id = 1 and game.pool_id = 1;

So I suppose you want to sum the difference from previous number and pick.hscoreary - pick.ascoreary. I have rewritten your query:

SELECT pick.user_id,
       count(game.game_id) as correct_picks,
       sum(game.points)    as points,
       -- game.bonus,      -- cannot aggregate on this field
       SUM(
         CASE
         WHEN pick.hscoreary is not NULL
         THEN ABS( (select (game.hteam_score - game.vteam_score)
                    from xf_nflj_pickem_game as game
                    where game.bonus = 1 and game.week_id = 1 and game.pool_id = 1)
                    - (pick.hscoreary - pick.ascoreary)
                 )
         ELSE 0
         END)
       as margin

FROM      xf_nflj_pickem_game as game
LEFT JOIN xf_nflj_pickem_pick as pick ON game.game_id = pick.game_id
WHERE       game.winner = pick.team_id
       AND game.week_id = 1
       AND game.pool_id = 1
       AND pick.team_id <> 0
GROUP BY pick.user_id;

I don't know if the previous query is correct. But you can try :-)

You can retrieve information about user from the result set of previous query:

SELECT user.username FROM xf_user where user_id IN(...)


Indexed but still slow during query I have a table that stores REST requests and responses:

CREATE TABLE ws_call_logs
(
   id                 BIGINT         NOT NULL,
   ws_method_id       BIGINT         NOT NULL,
   device_id          BIGINT,
   session_id         BIGINT,
   corr_id            BIGINT,
   request            MEDIUMBLOB,
   response           MEDIUMBLOB,
   http_status_code   INT UNSIGNED   NOT NULL,
   processing_status  CHAR(1)        NOT NULL,
   created_timestamp  DATETIME       NOT NULL
)

I created 4 indexes representing the 4 queries I usually execute:

CREATE INDEX ix_corr_id_timstamp ON ws_call_logs (corr_id, created_timestamp);
CREATE INDEX ix_corr_method_timstamp ON ws_call_logs(corr_id, ws_method_id, created_timestamp);
CREATE INDEX ix_corr_id_status_timstamp ON ws_call_logs (corr_id, http_status_code, created_timestamp);
CREATE INDEX ix_corr_method_status_timstamp ON ws_call_logs (corr_id, ws_method_id, http_status_code, created_timestamp);

Here is the query:

SELECT *
FROM
   ws_call_logs
WHERE
   (corr_id is not null
      AND http_status_code > 200
      AND created_timestamp >= '2015-11-01 23:00:00'
      AND created_timestamp <= '2015-11-30 23:00:00'
   )
ORDER BY
   created_timestamp
DESC LIMIT 25;

I am expecting that it should be fast because it should use the 3rd index that I have created. But it seems it is not and it takes around 1 minute before it returns.

Can anybody spot what is the problem? Or any tips how to debug?

And any other tips to optimise the queries will be appreciated.

first what You need, check explain plan of query:

EXPLAIN SELECT *
FROM
   ws_call_logs
WHERE
   (corr_id is not null
      AND http_status_code > 200
      AND created_timestamp >= '2015-11-01 23:00:00'
      AND created_timestamp <= '2015-11-30 23:00:00'
   )
ORDER BY
   created_timestamp
DESC LIMIT 25;

It return You information about which indexes from present MySQL will use when run query

Than You will check cardinality of the indexes

SHOW INDEX FROM ws_call_logs

it give You idea - which index better to use in this case

without information about data, general ideas:
 - index for created_timestamp - good candidate

You can or create index separate for created_timestamp only, or create index for 3 columns:
core_id, hit_status_code, created_timestamp - columns in index must be in same order as used in query

and last SELECT * not give ideas about data size, even You request 25 records, but before server must sort records by DESC


Where does an execution plan come from? Is there a way I can determine if a plan was just generated for a particular query, or was found in the plan cache?

SQL Server 2012 has an indicator in the plan itself, RetrievedFromCache, which can be either "true" or "false".

This appears to be the property you are asking about.

This is a sample (the last line shows the property):

<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1"
StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
StatementSubTreeCost="0.0508992" StatementText="SELECT COUNT(*)
&#xD;&#xA;FROM sys.tables" StatementType="SELECT"
QueryHash="0x9A4B63A948B30EA0" QueryPlanHash="0xF357CAE882D5B15D"
RetrievedFromCache="true">

Unfortunately, I do not see anything similar in a plan generated by SQL Server 2008 R2.

In SQL Server 2008 R2, you can use the sys.dm_exec_query_stats system DMV to inspect the creation_time column for plans that have the same query_hash value.  The query hash can be obtained from the header of the plan XML (see the example above).  This query will return rows regarding the plan mentioned above:

SELECT *
FROM sys.dm_exec_query_stats qs
WHERE qs.query_hash = 0x9A4B63A948B30EA0;


Using activity over last few days to label user as "active" Here's an example dataset.
with activity_cte (day, user_id, act1, act2) as (
    values
        ('2020-01-01'::date, 1, 0, 1),
        ('2020-01-01'::date, 3, 1, 0),
        ('2020-01-02'::date, 1, 3, 2),
        ('2020-01-02'::date, 2, 0, 2),
        ('2020-01-02'::date, 5, 0, 1),
        ('2020-01-03'::date, 1, 1, 2),
        ('2020-01-03'::date, 5, 1, 1),
        ('2020-01-04'::date, 2, 1, 1),
        ('2020-01-04'::date, 5, 4, 0)
)
select * from activity_cte;

In this I'm tracking user activity counts.  There are two activities I'm tracking here: "act1" and "act2", and I'm just summing the number of times the user engages in that activity over time.  For now, if a user doesn't engage in either activity on a given day they're just not showing up for that day (though this could be changed if needed).  So for example, user 2 engages in activity 2 twice on Jan 2 and in both activities once each on Jan 4.
What I'd like to do is calculate the number of "active" users each day which I'll define as users who have engaged in at least one of the activities since the day before (really it'll be something like a week before, but I don't want to have to write a ton of rows in this example set here).  So here's what I'd like returned.
2020-01-01 2
2020-01-02 4
2020-01-03 3
2020-01-04 3
This seems like something I'd need to use a window function for.  Maybe I'm overthinking it, but I'm having trouble coming up with the actual approach to generate these numbers.

That may not be the most elegant solution, but it works:
with activity_cte (day, user_id, act1, act2) as (
    values
        ('2020-01-01'::date, 1, 0, 1),
        ('2020-01-01'::date, 3, 1, 0),
        ('2020-01-02'::date, 1, 3, 2),
        ('2020-01-02'::date, 2, 0, 2),
        ('2020-01-02'::date, 5, 0, 1),
        ('2020-01-03'::date, 1, 1, 2),
        ('2020-01-03'::date, 5, 1, 1),
        ('2020-01-04'::date, 2, 1, 1),
        ('2020-01-04'::date, 5, 4, 0)
),
day_users AS (
    SELECT DISTINCT
           day,
           array_agg(user_id)
               OVER (ORDER BY day
                     RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW) AS users
    FROM activity_cte
)
SELECT day_users.day,
       count(DISTINCT user_id)
FROM day_users
    CROSS JOIN LATERAL unnest(users) AS u(user_id)
GROUP BY day_users.day;

    day     | count
------------+-------
 2020-01-01 |     2
 2020-01-02 |     4
 2020-01-03 |     3
 2020-01-04 |     3
(4 rows)


DB2 - how to safely blow away database on linux? I'm using (I believe) 10.1.2, on CentOS 6.5 (for sure).

The underlying problem I have is that the database upgrade package prepared in house isn't working for me, in at least the sense that the database schema is not changing as it is supposed to. Of course, no-one external can help me with that.

What I'd like to do is remove the existing database as completely as possible (preferably to the level of deleting/archiving files that might have records of it), so that there's no way the upgrade package can be reading old schema data. What's the safest way to do that?

I've tried doing a drop database in the db2 commandline as db2inst1, with no luck:

db2 => connect to xydb

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = XYDB

db2 => drop database
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"DATABASE".  Expected tokens may include:  "<identifier>".  SQLSTATE=42601
db2 => drop database xydb
SQL1035N  The operation failed because the specified database cannot be
connected to in the mode requested.  SQLSTATE=57019
db2 =>

Any help much appreciated.

You cannot drop a database if someone is connected to it. You would need to force everyone off the database first (and to do that you need SYSCTRL authority for the instance, and I'm gonna guess DBADM at the database level if you don't have instance level authority).

db2 connect to xydb
--the next command forces off the users asynchronously
db2 quiesce database immediate force connections
--but you need to unquiesce to drop
db2 unquiesce database
db2 connect reset
-- hope you don't have a connection in between
-- also make sure the database isn't activated....
db2 deactivate db xydb
-- do this as a safeguard cause there is no going back once you drop it.....
db2 backup db xydb to '<path you provide>' compress
db2 drop db xydb

or if you want to go instance level and lock down the instance.....

db2 quiesce instance <instance name> restricted access immediate force connections
-- make sure the db isn't activated
db2 deactivate db xydb
-- do this as a safeguard cause there is no going back once you drop it......
db2 backup db xydb to '<path you provide>' compress
db2 drop db xydb
-- wait a few seconds
db2 unquiesce instance <instance name>

Then don't forget to backup logs and archived logs and then you can clean those out as well.

Now you can attempt to create a new database. However, I'm guessing you need to worry about migrating your data? Are you attempting to migrate to a new fix pack or version of DB2? Are you attempting to upgrade your schema? You can always test out the "upgrade" on another server first. I guess I'd like more information on your "upgrade" and I can tweak this answer to hopefully respond to that part rather than just giving you hints on how to drop a database.


Should I be concerned about excessive Com_change_db commands? I installed MONyog recently to check for problems on a MySQL server that I admin. Most of the information was useful and helpful, but there's one warning I am not sure is worth fixing.

It reports that Com_change_db commands are excessive (currently 9+ million or 58 per second), or unusually high. The connections to the database are strictly Windows .NET applications and they use Connector/Net as an API.

I typically connect such that I specify which database to use when creating a MySqlConnection object. I discovered in a test that doing this causes the Com_change_db to increment by one when the connection is established.

I can "fix" it by not specifying a database, and instead qualifying the database within the query every time. (e.g. db.table.col instead of table.col)

Is it worth going through the entire code base of the application to make this change? Is MONyog concerned with something that doesn't matter?

MONyog
In MySQL 5.6, 341 different status values are being recorded.
If all values are retrieved with:
SHOW GLOBAL STATUS;

Then, you are retrieving all 341 values. Com_change_db is just coming along for the ride.
If each value is retrieved individually, such as with
SHOW GLOBAL STATUS LIKE 'Com_change_db';

this should not be a problem since you can set MONyog's interval for collecting status values.
In either scenario, MONyog is a non-factor in your case.
Source Code
You may want to leave your code alone since you are only generating a Com_change_db 58 times per minute (about 1 per second). Just to be hypothetical, if each Com_change_db took up 2K in transmission over the DB Connection through the network at a rate of 1 per second:
86400 sec X 2K = 168.75 MB per day
That's not a lot to worry about in a day.
Therefore, changing your code just to lower the Com_change_db count would be the only gain you would make. There would be no discernable performance improvement.
Epilogue
I would be more concerned with status variables that count temp table creation or all the handler counts as that would be earmarks for bad queries or large temp tables do to key distribution or setting for temp tables that are too small.
UPDATE 2013-09-26 19:38 EDT
OK my mistake. You said 58 per second, not per minute.
Let me redo my hypothetical example
86400 sec X 2K X 58 =

9.558 GB per day
407.8 MB per hour
6.7968 MB per minute

This shows that could reduce the amount of network traffic by cutting out Com_change_db requests. If you have a good network with 10Gig E, this may not be an issue.
Summary
Think of the tradeoff you were proposing: If you are qualifying all tables with the database name prepended, then you are transmitting more bytes in the SQL text and giving the Query Parser more to work with anyway.
Another way to approach it would be to have a different DB Connection connecting to a separate database. That way, one DB Connection would not have to switch databases and run up the Com_change_db counter and add to the network traffic. The tradeoff would be to have multiple DB Connections per client (requiring more memory).
In other words, something has to bite the bullet in the end.
Again, I suggest leaving everything alone because all the alternatives require major code changes without knowing if the enhancements were worth it in the end.


Connect Oracle database using AD account via OID We have configured Oracle Internet Directory. We have integrated OID with AD. We have also registered Oracle database with OID. And we are now trying to configure authentication to the database with AD users.

Any suggestions, what additional steps should be done to connect to the Oracle database using AD accounts.

We have read many documentations about OID but have not found any certain step by step instructions how to configure it.

We are newbies with this product.

Waiting your suggestion..

Sounds like what you looking for is an implementation EUS (Enterprise User Security).

As of now, EUS will only work with OID and OUD (Oracle Unified Directory). This is because of it's requirement of the LDAP protocol, which AD can not provide. However, there is a way to configure an OUD Proxy, connected to a Active Directory (ultimately making AD more LDAP compliant), and the implement EUS.

The other option is to synchronize user accounts on AD to users on OID (after you configured OID for EUS with your database). However, this can be troublesome with AD's strict protection with the passwords.

Hope this helps!


Schema for multi-account database I'm building an app which will have accounts for organisations leading to organisation.myapp.com subdomains for their account access.

I'm putting together the schema for it and I have a few questions.

This is a simplified overview.

An organisation has users
An organisation has clients
A client has contacts
A client has projects
A project has an estimate

Some initial thoughts

The clients table and the users tables will have organisation_id as a
foreign key.
Contacts and Projects will have client_id as a foreign key
Projects will have estimate_id as a foreign key

However, is there an argument that organisation_id should also be a foreign key in Estimates, Contacts and Projects?.

For example, to make sure that only members of the relevant organisation can view Estimates I need to perform a query each time to make sure that the authorised user belongs to the organisation that owns the estimate. I can do this via a join but I wonder if this is unnecessary overhead. On the other hand, repeating organisation_id across pretty much every table seems to go against denormalization.

Is there a best practice I should be following here?

If it's of any importance this is for a MySQL DB with all the tables running InnoDB.

Thanks.

OK - so in your first year, all going well, you might be looking at 50GB for 1k organisations? That's certainly not "pie in the sky". What I'm going to propose is based on my own experiences and other people's opinions may vary depending on theirs. I worked for a company that had fewer organisations but more data, however I think that what worked there may very well apply to your use case.

We used Oracle at the time, and the company, being scrupulous about licences, didn't want to run loads of Oracle servers with attendant costs. So, every client had their own schema (or in MySQL terms, database) on the same server. That is what I am proposing for your situation.

The advantage this has is that if you have to take one client down, you won't affect the others because their data will be independent of each other. Neither will you have to worry about one client seeing another client's data. Furthermore, given that this is a new project, I imagine that you'll want a good deal of flexibility until the app is "bedded down".

This arrangement also gives you the possibility of running different clients on different versions - say your early adapter types on a test release and your laggards on another. You might want to to test on Freemium type clients, but keep your paying customers happy with the stable version?

You can also consolidate some of your data in a central reference schema (i.e. lookup tables - names of countries/US states/post codes...) and grant select on those to all users of the other schemas. You could do something like what's shown here.

Yet another advantage is that when you grow, all you'd have to do is fire up another machine and split your customer base between the two machines (and so on up to ...). Although, I hope you'll have split long before you arrive where this chap did in terms of schema numbers! :-)

The only major downside of this approach as far as I can see is that it will complicate scripting - but then that should be no problem to a man of your calibre (10K on Stackoverflow :-) ). Just to make sure I wasn't committing some appalling IT faux pas, I used your friend and mine, Mr. Google and was pleasantly surprised to come up with these links (1 - note the point about standards, 2 & 3 - see the "Shared schema" part of the accepted answer). There are dissenting voices, but on the whole, they appear to show that I'm not completely delerious! Furthermore, as I said, this is based on my own personal experiences. You may well, of course, choose to consolidate when your app is up, and running stably.

Two final points.

I believe that I've given my take on this part of your question

  For example, to make sure that only members of the relevant
  organisation can view Estimates I need to perform a query each time to
  make sure that the authorised user belongs to the organisation that
  owns the estimate. I can do this via a join but I wonder if this is
  unnecessary overhead.

but I haven't answered the bit as to which tables should have which foreign keys. This is virtually (again, IMHO) impossible to answer for the following reason. I've started many projects with all sorts of ideas but then when you start throwing data at it, all sorts of new issues arise - it's the classic "no plan survives contact with the enemy". Experiment, play around, test. If you come up with particular issues, then post back here, but it would be rash to give advice at this point.

Finally, and this is again (totally) IMHO. If I were starting a project such as this from scratch, I would, without hesitation, choose PostgreSQL over MySQL. How MySQL came to dominate the Open Source database world is a different question. MySQL doesn't have check constraints. It doesn't fully support set operators. It doesn't have CTEs (Common Table Expressions). It doesn't support windowing functions. Just my 0.2c...


DBCC TRACEON with the global flag In the question Need Help Troubleshooting Sql Server 2005 Deadlock Scenario there was a suggestion of using DBCC TRACEON (1204, -1) to trace deadlocks globally.

When reading about this command in BOL it states that it should only be used  while users or applications are not concurrently running statements on the system. Does it mean that we must be in singleuser-mode when enabling this trace-flag? And in addition, why is it required and is it important to follow the advice? (Just seems a bit hard to follow in a production system that is always running.)

This applies more to other trace flags (eg 610 for minimal logging) that affect the optimiser and important behaviours/optimisations

You should be OK with the deadlock trace flags 1204, 1205 and 1222


INDEX Creation Advice Oracle I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id.

The attributes date, and entity_id would make a unique pair.

Data is made into range partitions on the date field across several tablespaces.  However, the script to create the index is a normal CREATE INDEX script which does not mention any tablespace or partition name.

I would like to create a combined index on both columns as the queries could use both columns, however the index data file exceeds disk space availability.  I am not sure of the choice of index type.

I do not want to touch the existing index.

create table interval_date
(
  person_id   NUMBER(5) NOT NULL,
  last_name   VARCHAR2(30),
  dob         DATE
)
partition by range (dob)
INTERVAL(numtoyminterval(1,'MONTH'))
STORE in (uwdata)
(
  partition p1 values LESS THAN (to_date('2008-03-15','YYYY-MM-DD'))
)

Whether you use a local index or a global index will have (practically) no impact on the amount of space required for the index.  If you are running out of space to create the index, you'll need to allocate additional disk space to the tablespace (or tablespace(s) if you create a local index where the index partitions are stored in different tablespaces).  If you are building an index that involves the column you are partitioning on, you would almost always want to create a local index-- there is no benefit to creating a global index in this case.

Assuming that the dob column stores dates of birth and that the time component of that column is always set to midnight, you can probably reduce the space required for the index by making the dob column the leading column of the index and by using index compression.  In your case, you probably want to specify COMPRESS 1 since you would only want to compress the dob column, not the person_id column assuming that person_id has many fewer repeated values.


SQL Server combine Update and Delete Is it possible to combine both update and delete in a single SQL statement? I would like to execute something like this:

update a
set a.value = a.value + b.value
from Table1 a
join Table2 b on a.ID = b.ID

Also, rows, which has value higher than some constant number, should be deleted.

You can't combine them in one statement but you can wrap them into one transaction.

BEGIN TRANSACTION
  update a
  set a.value = a.value + b.value
  from Table1 a
  join Table2 b on a.ID = b.ID;

  DELETE FROM Table1 WHERE Table1.Value > @Constant;
COMMIT TRANSACTION ;


msdb system table fragmentation I have a Index Maintenance job for msdb database to rebuild those fragmented index in msdb database.

But I still found some backup related table in msdb database (e.g. backupmediafamily, backupset, backupfile) has high fragmantation.

After further checked found out it was skipped by the Ola Index maintenance command.

My question is how I can make ola defragment the index rather than I write another set of index maintenance just for msdb ?

The root cause is those tables in msdb  (e.g. backupmediafamily, backupset, backupfile)
are MS Shipped Object. by default, Ola skip those MS Shipped object for rebuild/reorganize.

MS Shipped Object means the objects that are created by internal SQL Server components.

Solution: Add 1 more parameter @MSShippedObjects = 'Y' to enable the Ola command to get those MS Shipped Object to defragment.


Recommendations for building a big search engine I am creating a image search engine that would probably have millions of images tagged with keywords. Basically it would be something like Google Image search.

So I want to ask for any tips on designing the database of the application.

What kind of database system to use? How would the images table look like to store that huge quantity? How do I create the relation between the keywords and the image to be able to do a very quick search?

The programming will be done using a PHP Framework.

Thank you,
Alex.

you might consider to use an indexing framework like Apache Lucene as well. If not, I would recommend to model an inverted index like database. An inverted index uses keywords as key identifier for a set of images.

Inverted Index Table:

keyword image_ids
"sun" : [1,5,6]

Image Table

image_id  size    ...
   1     200x300  ...


What permissions does db_owner not have? (In SQL Server 2008) Can you list the permissions that db_owner cannot do.

Permissions of Fixed Database Roles: db_owner: has CONTROL permission with GRANT option in the database and VIEW ANY DATABASE permission in the server. To understand what that entails, read all of the following:

Permissions
Principals
Securables
Permission Hierarchy


How do I find a job that sends a specific email? I had a job sending an irrelevant email to a group of users.
The title of the email was Staging -> [AUPAIR] arrivalDate trigger issue.
When I query the jobs table in the msdb database using the title above, I got no answer:
USE [msdb]
GO
SELECT    j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON    js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON    s.srvid = j.originating_server_id
WHERE    js.command LIKE N'%Staging -> [AUPAIR] arrivalDate trigger issue%'

Only when I alter my query as below, could I find the bandit job:
USE [msdb]
GO
SELECT    j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON    js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON    s.srvid = j.originating_server_id
--WHERE    js.command LIKE N'Staging -> [AUPAIR] arrivalDate trigger issue%'
WHERE    js.command LIKE N'%Staging -> %'

This is what was the command on the jobstep:
if exists (
select count(1) as total
,year(modify_date) as [Year]
,month(modify_date) as [Month]
from repl_aupair r
where r.arrivaldate is not null
and junocore_applicationid not in(
select applicationid from junocore.dbo.replicationUpdateControl
where arrivaldatechanged = 1 )
and modify_Date >='2017-01-26 00:38:46.197'
group by year(modify_date),month(modify_date)
having year(modify_date) >= 2017
--order by [Year] desc
)
begin
EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'DBA',
            @recipients = 'my_emails@mycompany.com',
            @body = 'APIA_Repl_Sub.dbo.repl_aupair arrivalDate not null and replicationUpdateControl arrivalDateChanged still 0',
            @subject = 'Staging -> [AUPAIR] arrivalDate trigger issue ';
end

Why did my first query not work?
Conclusion:
I modified my script and the following two versions work!
After the answer by EzLo:
USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE js.command  LIKE '%Staging -> \[AUPAIR] arrivalDate trigger issue%' ESCAPE '\'

USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE js.command  LIKE '%Staging -> [[]AUPAIR] arrivalDate trigger issue%'

The problem with your first query is the use of unescaped brackets with the LIKE operator.

When using the LIKE operator, there are a few special characters such as brackets that require escaping if you want to look for them. Characters enclosed in brackets will tell the SQL engine to look for a match between any character listed inside (same as regular expressions).

DECLARE @string VARCHAR(100) = 'ABC'

SELECT 'match!' WHERE @string LIKE '%[CX]%' -- Sting must have a C or an X
-- Result: match!

In your case, using [AUPAIR] inside the like will only match 1 letter (either A, U, P, I or R), and not the whole word.

DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -> [AUPAIR] arrivalDate trigger issue%'
-- Result: (none)

This will however match with the following strings (just 1 letter!):

'Staging -> A arrivalDate trigger issue'
'Staging -> U arrivalDate trigger issue'
'Staging -> P arrivalDate trigger issue'
'Staging -> I arrivalDate trigger issue'
'Staging -> R arrivalDate trigger issue'

To correctly search for brackets, you can use 2 solutions:

Declare a custom ESCAPE character.

DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -> \[AUPAIR] arrivalDate trigger issue%' ESCAPE '\'
-- Result: match!

Enclose the opening bracket with brackets.

DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -> [[]AUPAIR] arrivalDate trigger issue%'
-- Result: match!

Please note that there is no need to escape the closing bracket since this one will only be interpreted as a LIKE special character as long as there is an opening one before it (which won't be if you escaped it).


Postgres COPY, is it possible to load multiple files into the same table? Is it possible to execute several \copy commands in parallel against the same table? I know something similar exists with pg_restore, but that splits the work up amongst tables.

I'm working with files that range from 40-100M rows. Despite dropping indices and creating them later, it still takes a considerable amount of time.

You have two questions here,

Is it possible to load multiple files into the same table?
On the benefit of it, with concerns about time "in parallel against the same table" .. "Despite dropping indices and creating them later, it still takes a considerable amount of time."

Yes you can load up multiple files. Functionally, it doesn't matter how many times you run the COPY command -- it'll work more than once.

Normally, there isn't much an advantage to split up the copy command if the target is the same table, or even on the same tablespace. One background-writer process can easily saturate a disk. The only time this would matter is if you had something that was computationally intensive. Moreover, there are some reasons to not break up copys. For instance, if you create and load a table in the same transaction you have less WAL,

  COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration only applies when wal_level is minimal as all commands must write WAL otherwise.

If you need faster writes, check out the Non-Durable Settings


Why the mysql MSI Installer is more huge than wamp installer packages size? In http://dev.mysql.com/downloads/windows/installer/5.6.html, you can get Windows (x86, 32-bit), MSI Installer  whose size is 252.6M .
In http://sourceforge.net/projects/wampserver/files/, you can get wampserver installer packages whose size is only  39.8M.
I am confused that wampserver contain apache\mysql\php ,why 1+1+1<1?

MySQL Installer provides an easy to use, wizard-based installation experience for all your MySQL software needs. Included in the product are the latest versions of:

MySQL Server
MySQL Connectors
MySQL Workbench and sample models
Sample Databases
MySQL for Excel
MySQL Notifier
MySQL for Visual Studio
Documentation

While WAMP comes only with MySQL-Server (no client tools etc), not 100% sure but did you see if the wamp-setup is offline installer only?


Storing multiple contact addresses for a person Simplified diagram for reference.

My database stores information about people in the following way. There is a  Person table containing common personal information fields; name, date of birth, gender, etc.

The contact information for each person is kept in the ContactInformation table with a 1:1 relationship.

Finally, there are then individual tables for each specific type of person in the organisation we store data on with a 1:1 relation to Person

I have a bit of a problem with the Student table. Basically my client now wants to store both a home address and term time address for a student. But I'm not sure how to approach this.

I see three possible options/solutions:

Junction Table - Create a junction table (e.g. ContactAddressMapping) between Person and ContactInformation.
New Table - Create a TermTimeAddress table with a 1:1 relation to Student
New Fields - Add extra fields to the Student table to hold the term time address.

Now here are the main "problems" I see with each option:

A junction table would allow multiple addresses to be stored for a person, but we'd only ever need to do this for students (for now). There would also be no way to determine which address is the home/term time address without using some kind of boolean field which seems like pointless information in that table since it's only unique to students.

Creating a new table separates the term time address from the main address but these feels like duplication since I'm basically creating a specific type of address with the same fields, minus things like phone numbers and email addresses.

Adding the fields to the Student seems like I'm cluttering the table with information that should be elsewhere. The table should only be for information about that student such as their school, the area of study and the start and end date of their placement.

Which of these would be the most appropriate option, or is there another solution that I haven't seen yet?

I wouldn't have the ContactInformation linked 1-to-1 to Person anyway; storing several addresses like that in ContactInformation violates normal form. Instead I'd have ContactInformation with its own PK, an FK for PersonID back to the Person table, and store as many Contacts as necessary for each Person.

CREATE TABLE ContactInformation (
    ContactID int IDENTITY(1,1) PRIMARY KEY,
    PersonID int FOREIGN KEY REFERENCES Person(id),
    ContactInfo nvarchar(250),
    ContactType char(2)
)

Populate the ContactInformation table with the PersonID, then an email address/phone number/mailing address/twitter handle/etc and mark it with a ContactType. This could be 'MA' for mailing address, 'EM' for email, "TW' for Twitter, 'TT' for Term Time as in your question.

Triggers could validate any inserts to make sure contacts don't have a TT (term time) address unless they're a student.

Hope that helps you think outside the box. It may be too hard to make these changes at this point, if you're already deep in the application.

Edit:
I didn't feel I properly addressed your direct question. If I were you and making deeper changes isn't possible or feasible at this point, I would add TermTime address to the Students table.

It belongs there because 1) it only applies to Students, so having it in the Person or Contact tables (as they exist now) is less than ideal, and 2) Each student should have one so it's already part of their student record and 3) this can be done with the least disruption to the existing schema.


MAXDOP recommendation We would like to implement best practice on our SQL Server instances. All of our instances still run with the default setting of MAXDOP = 0.

Having read several pages on the recommended settings for this (including Microsoft Support and Erik Darling's posts on brentozar.com), we wanted to check that we have understood these recommendations correctly in terms of our instances. All of our instances are running on virtual machines, and many have only one socket and only a handful of virtual processors.

Example 1:

1 x socket 2.30 GHz
6 x virtual processors
Running 1 x SQL Server instance (2016 SP1 CU8, Web Edition 64-bit)

Change MAXDOP from 0 to 6, or from 0 to 3?

Since this instance only has a single socket anyway, a change from 0 to 6 will surely make no difference? Are we likely to gain anything from changing this setting?

Example 2:

2 x socket 2.30 GHz
6 x virtual processors per socket
Running 8 x (don't ask!) SQL Server instances (2012 SP4, Standard Edition 64-bit)

Change MAXDOP from 0 to 6? Are we likely to gain anything from changing this setting?

Change MAXDOP from 0 to 6? Are we likely to gain anything from
  changing this setting?

The short answer is: it depends on your workload and the cpu usage of your queries.

  Example 1:

What is your CPU usage, are you seeing spikes on certain queries, are these queries bottlenecking other queries?

If the answer is yes

consider changing your maxdop to 2 or 4. Also, check your cost threshold for parallellism, if this is at the default of 5, consider setting this higher, but as always, monitoring your workload before and after is key.

  Example 2:

This one is a bit tricky, since one instance could hog all the resources on your machine.

An important factor in this example is determining which instances are the most critical to always run their queries without possible bottlenecking of the other  five instances.

Monitor workload before and after, but leaving it at 0 for all instances and having big queries running at 2 or more instances will cause bottlenecks.

I would consider again setting it to 2 for less important instances and 4 for more important instances to start with, and change this again based on the results. (Here also consider the cost threshold for parallellism setting).


Sequential joining of tables in order Table albums has (among other field) field id.

Table photos has column id, field album which is a foreign key referring to album id and some other fields (which are irrelevant for the question I ask).

So in an album there is several photos (photos in an album are ordered by id of a photo).

Now I introduce one more "level of indirection": Bunches. There may be several albums in one bunch.

For this I add fields bunch and seq INT UNSIGNED into the table albums. bunch is the ID of the bunch where the album belongs and seq is the number of the album in the bunch.

Now the problem:

Let it is given a bunch ID.

I want to make a SELECT query which selects all photos from albums belonging to the given bunch, ordered first by seqs of the albums and then by IDs of photos in the album.

I'm not sure why bunches wouldn't be its own table (I think that would make this problem easier), but I'll run with it as is. If I'm understanding the problem correctly, assuming you wanted bunch 1, something like this should do the trick:

SELECT p.image
FROM photos p
  JOIN album a
    ON p.album = a.id
WHERE a.bunch = 1
ORDER BY a.id, a.seq, p.id


SUM rows FROM a table only if one row have some match criteria with the rest of the rows Sorry if the title is a little bit odd, but I am struggling even with explaining myself on this.

I am trying to create a soccer stats database. Right now I have a 'boxscore' table like this:

id_boxscore  id_player  id_team  goals  fouls
-----------  ---------  -------  -----  -----
1            1          1        1      3
1            2          1        3      5
1            3          2        2      8
2            4          5        0      6
2            4          5        0      6
3            2          1        5      9
3            3          1        11     4

So, for some advance data calculations, I need to know the accumulated totals from the team from games where the player participated. The good query should give me something like this if, for example, I am selecting accumulated stats from id_player = 1:

id_player  accGoals   accFouls
---------  ---------  --------
1          4          8

Right now my query is looking like this:

SELECT SUM(b.goals) FROM boxscore as b, (SELECT b.id_boxscore from boxscore as b
WHERE b.id_player = 1) AS c WHERE b.id_boxscore=c.id_boxscore

But is giving me the accumulated stats from games where the player with id 1 has played (both from his team and from the opponent):

id_player  accGoals   accFouls
---------  ---------  --------
1          6          22

Any ideas? I am sure this is really simple but I am in a dead-end right now.

Thanks in advance!

Is this close to what you expect?

SELECT a.id_player, SUM(b.goals) as accGoals, SUM(b.fouls) as accFouls
FROM boxscore as a
JOIN boxscore as b
    USING(id_team, id_boxscore)
WHERE a.id_player = 1
GROUP BY a.id_player
;

I.e. sum all goals, fouls from boxscore, where id_team and id_boxscore is the same as player 1


What should one do if the auto increment primary key exceeds the maximum? I have a primary key for a table with the following structure,

**Field**          **Type**
  nid     int(10)unsigned

I know the maximum limit of int(10) is 4294967295. But What happens if it exceeds the maximum? Should i have to change to bigint ?
if so what is the best practice is it to use int or big int in primary key?

Since you need to go to BIGINT, prepare for Disk Space Growth.

INT is 4 bytes
BIGINT is 8 bytes

For every table that uses INT and you want to switch to BIGINT, you must forecast how much additional space to expect.

For example, for figure out how much space mydb.mytable will increase when you shift all INT(10) columns to BIGINT, run this query

SELECT CONCAT('SELECT COUNT(1)*',int_column_count*4,
' SizeIncreaseInBytes FROM ',table_schema,'.',table_name,';') INTO @SQLStmt
FROM (SELECT table_schema,table_name,
COUNT(1) int_column_count from
(
    SELECT table_schema,table_name,column_type
    FROM information_schema.columns
    where table_schema = 'mydb'
    AND table_name = 'mytable'
    AND column_type = 'int(10)'
) AA group by table_schema,table_name) A;
SELECT @SQLStmt;
PREPARE s FROM @SQLStmt;
EXECUTE s; DEALLOCATE PREPARE s;

Let's use the table dev_oxygen.bplv_reg

mysql> show create table dev_oxygen.bplv_reg\G
*************************** 1. row ***************************
       Table: bplv_reg
Create Table: CREATE TABLE `bplv_reg` (
  `id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(40) DEFAULT NULL,
  `lastname` varchar(40) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `zipcode` varchar(11) DEFAULT NULL,
  `email` varchar(200) NOT NULL,
  `phone` bigint(20) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `newsletter` smallint(6) NOT NULL DEFAULT '0',
  `ip_address` varchar(20) DEFAULT NULL,
  `fb_user_id` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Notice the table has age is int(10)

Here is the table count:

mysql> SELECT COUNT(1) FROM dev_oxygen.bplv_reg;
+----------+
| COUNT(1) |
+----------+
|     2038 |
+----------+
1 row in set (0.00 sec)

mysql>

Now let's compute the number of bytes the table will have to grow:

mysql> SELECT CONCAT('SELECT COUNT(1)*',int_column_count*4,
    -> ' SizeIncreaseInBytes FROM ',table_schema,'.',table_name,';') INTO @SQLStmt
    -> FROM (SELECT table_schema,table_name,
    -> COUNT(1) int_column_count from
    -> (
    ->     SELECT table_schema,table_name,column_type
    ->     FROM information_schema.columns
    ->     where table_schema = 'dev_oxygen'
    ->     AND table_name = 'bplv_reg'
    ->     AND column_type = 'int(10)'
    -> ) AA group by table_schema,table_name) A;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @SQLStmt;
+-----------------------------------------------------------------+
| @SQLStmt                                                        |
+-----------------------------------------------------------------+
| SELECT COUNT(1)*4 SizeIncreaseInBytes FROM dev_oxygen.bplv_reg; |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @SQLStmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s; DEALLOCATE PREPARE s;
+--------------------+
| SizeIncreaseInBytes|
+--------------------+
|               8152 |
+--------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

This number is just the size increase (in bytes) for the age column.

What about the id? It's not int(10) but int(11). Could you forecast for int(11), Sure, just replace int(10) in the query with int(11).

What about the primary key and what if a primay key has multiple int columns defined as int(11)?

Let's pick a different table called oxygen.history;

mysql> show create table oxygen.history\G
*************************** 1. row ***************************
       Table: history
Create Table: CREATE TABLE `history` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `nid` int(11) NOT NULL DEFAULT '0',
  `timestamp` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`nid`),
  KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select count(1) from oxygen.history;
+----------+
| count(1) |
+----------+
|      564 |
+----------+
1 row in set (0.00 sec)

mysql>

This table has two columns in the primary key, both columns are int(11)

mysql> select CONCAT('SELECT COUNT(1)*',int_column_count*4,
    -> ' IndexSizeIncreaseInBytes FROM ',table_schema,'.',table_name,';')
    -> INTO @SQLStmt FROM
    -> (SELECT AA.table_schema,AA.table_name,
    -> COUNT(1) int_column_count FROM
    -> (
    ->     select table_schema,table_name,column_name
    ->     from information_schema.columns
    ->     where table_schema ='oxygen'
    ->     and table_name='history'
    ->     and column_type = 'int(11)'
    -> ) AA INNER JOIN
    -> (
    ->     select table_schema,table_name,column_name
    ->     FROM information_schema.STATISTICS
    ->     WHERE INDEX_NAME='PRIMARY'
    -> ) BB
    -> USING (table_schema,table_name,column_name)
    -> GROUP BY AA.table_schema,AA.table_name) A;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @SQLStmt;
+-----------------------------------------------------------------+
| @SQLStmt                                                        |
+-----------------------------------------------------------------+
| SELECT COUNT(1)*8 IndexSizeIncreaseInBytes FROM oxygen.history; |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @SQLStmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s; DEALLOCATE PREPARE s;
+--------------------------+
| IndexSizeIncreaseInBytes |
+--------------------------+
|                     4512 |
+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Based on how much increase is forecasted for the primary key, you can now increase these

MyISAM Key Cache (if you are forecasting for a MyISAM table, sized by key_buffer_size)
For InnoDB, you combine the two queries above to measure data and index size increases to size up the InnoDB Buffer Pool (size by innodb_buffer_pool_size).


How can you insert data into a ARRAY column on a Snowflake table using SQL? I am having difficultly finding documentation on how to insert data into an ARRAY column type using SQL on a Snowflake table.

Snowflake Documentation: https://docs.snowflake.net/manuals/sql-reference/data-types-semistructured.html#array

// example table
CREATE OR REPLACE TABLE array_test_table (
  id number,
  ids array
);

// This does not work
INSERT INTO array_test_table (id, ids)
VALUES (1, '[1,2,3]' );

I have tried using using postgres's syntax like ones described here: https://stackoverflow.com/questions/33335338/inserting-array-values

I originally asked this question here: Snowflake Community

It looks like you need to apply a function to parse the array. As of 9/13/2018 functions are not allowed in value lists see Is there a literal syntax for arrays

One way around this is to combine INSERT INTO and SELECT statement. Example:

CREATE OR REPLACE TABLE array_test_table (
  id number,
  ids array
);

INSERT INTO array_test_table (id, ids)
SELECT $1, PARSE_JSON($2)
  FROM VALUES (1, '[1,2,3]' );


Oracle Database Security - Audit Trail for specific user and specific module application I want to enable audit trail for specific user, but only for sessions when this user connect to database using one specific module applications.

For ex. I want to enable audit trail for user test1 only when user test1 is connected to database using TOAD, but not when the user test1 is connected to database using Golden6.

Is this type of audit possible?

Thanks,

You can do this with Unified Auditing, which is available starting with version 12.1.

Example:

create user u1 identified by u1 quota unlimited on users;
grant create session, create table to u1;
grant audit_viewer to u1;

Now create a Unified Audit policy, that audits all actions when the user is U1, and module is not Golden6:

create audit policy mypolicy1
actions all
when 'sys_context(''userenv'', ''current_user'') = ''U1'' and sys_context(''userenv'', ''module'') != ''Golden6'''
evaluate per statement
;

SQL> audit policy mypolicy1;

Audit succeeded.

Then test it:

SQL> conn u1/u1
Connected.

SQL> select sys_context('userenv', 'current_user') as current_user, sys_context('userenv', 'module') as module from dual;

CURRENT_USER         MODULE
-------------------- --------------------
U1                   SQL*Plus

SQL> create table t1 (c1 number);

Table created.

SQL> exec dbms_application_info.set_module('Golden6', null);

PL/SQL procedure successfully completed.

SQL> create table t2 (c1 number);

Table created.

SQL> exec dbms_application_info.set_module('SQL*Plus', null);

PL/SQL procedure successfully completed.

SQL> create table t3 (c1 number);

Table created.

Now check the contents of the audit trail:

SQL> select event_timestamp, DBUSERNAME, ACTION_NAME, sql_text, RETURN_CODE from unified_audit_trail where dbusername = 'U1' and sql_text like 'create%' order by event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          SQL_TEXT                       RETURN_CODE
------------------------------ ---------- -------------------- ------------------------------ -----------
17-APR-18 02.04.29.381770 PM   U1         CREATE TABLE         create table t1 (c1 number)              0
17-APR-18 02.04.59.233856 PM   U1         CREATE TABLE         create table t3 (c1 number)              0

As you can see, the creation of table t2 statement, that was issued with the module set to Golden6, was not audited.


10.3.9-MariaDB can not query equal operator with special character I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]\=-`

I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

When I try to created DB in MySQL 8.0.13, this query work well and return 1 row

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';

But in 10.3.9-MariaDB, the query

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';

can not return any result.

Is this an MariaDB issue or I missed some configuration?

According to the documentation about sql_mode NO_BACKSLASH_ESCAPES:

  Disables using the backslash character \ as an escape character within
  strings, making it equivalent to an ordinary character.

Your string has a double backslash in it. In the NO_BACKSLASH_ESCAPES sql_mode this will be interpreted as a double backslash whereas in the default sql_mode it's interpreted as a single backslash.

Below are some tests with MariaDB 10.3.13 using the following table:

CREATE TABLE taginfo1 (id int unsigned PRIMARY KEY, TagName varchar(200));

Default sql_mode

INSERT INTO taginfo1 (id, TagName) VALUES (1, '~!@#$%^&*()_+|}{":?><./'';[]\\=-`');
SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
+----+---------------------------------+
| id | TagName                         |
+----+---------------------------------+
|  1 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |
+----+---------------------------------+

Note that one backslash has disappeared.

sql_mode with NO_BACKSLASH_ESCAPES

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
Empty set (0.000 sec)

Let's remove one backslash from the WHERE clause:

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';
+----+---------------------------------+
| id | TagName                         |
+----+---------------------------------+
|  1 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |
+----+---------------------------------+

Then let's insert a second row while we're in NO_BACKSLASH_ESCAPES mode:

INSERT INTO taginfo1 (id, TagName) VALUES (2, '~!@#$%^&*()_+|}{":?><./'';[]\\=-`');

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
+----+----------------------------------+
| id | TagName                          |
+----+----------------------------------+
|  2 | ~!@#$%^&*()_+|}{":?><./';[]\\=-` |
+----+----------------------------------+

As far as I can tell, this all works as per the documentation. If you really get different results in 10.3.9, then presumably there was a bug that's been fixed.


Updating statistics on 18 TB database I am looking here for experts' advice on how to manage updating stats for very large database approx 18 TB.

We have recently started facing performance issues and believe it is due to old stats.

Actually we have a job which runs exec sp_update stats and update with default sample rate, in our case 1.2 %. So we have to manually update stats and see some improvement.

I am sure scheduling FULL SCAN would be a challenge. As per my knowledge I am comparing rows with rows sampled. For example, on one table with size 400 GB and over 100M rows I can see sampled rows is about 2 to 4M. The large tables are partitioned.

We are on SQL Server 2012 Enterprise Edition. Trace flag 2371 is not enabled.

Please suggest how I can make use of updating stats in a much better way for such a large DB and how to play with that sample rate?

Based on your question I can think of four possible issues with statistics that could be causing the issues that you're experiencing.

1. Statistics are not automatically updated often enough.

In SQL Server 2012, statistics are updated only after 20% or more of the rows in the table have changed. That means that for a billion row table you will need to modify 200 M rows before a statistics update occurs. As the table grows larger your statistics updates will become more infrequent, so SQL Server can go for years without updating statistics on large tables.

TF 2371 changes the threshold so that statistics updates occur more often. In SQL Server 2016 this change has been made my default.

2. Queries in your workload are vulnerable to the ascending key problem.

Consider a table which has new data loaded daily and queries that filter on the latest day of data. Unless statistics update updated immediately after the data load the new data will not be present in any of the statistics histograms. That can result in very poor query performance due to low cardinality estimates.

The new CE in SQL Server 2014 makes improvements in this area. If you ask for data outside of the histogram range it may make a more optimistic guess and assume that there is data in the table but not in the histogram. In SQL Server 2012 you can address this problem, if you have it, by updating statistics more often or by enabling TF 4139. TF 4139 only works against columns with an index on them. SQL Server may run a very quick query against the index to get the highest or lowest value and will temporarily amend the histogram of the relevant statistics object. This can result in much better plans for some queries.

3. Your queries wait on statistics updates.

By default, if a query loads a stale statistics update it will update that statistics object before creating a query plan. On SQL Server 2012, sampled statistics update will run with MAXDOP 1. If kicked off against a large table the process may timeout while waiting for the statistics update to complete. After you update statistics against the tables the query performs better because it no longer has to wait for the statistics update.

If you're running into this problem this can be addressed by more proactive statistics maintenance with the NORECOMPUTE option. Alternatively, you can try to make the statistics update faster by Upgrading to SQL Server 2016. On SQL Server 2016 sampled statistics updates can run in parallel.

Another option is to turn on the AUTO_UPDATE_STATISTICS_ASYNC option. If a query plan encounters a stale statistics object it will queue that statistics object to be updated by a background job. This might sound bad and it is. The query may execute with stale statistics. This is the kind of feature that you want to turn on when you don't have a better choice, such as when working with large systems where auto statistics updates are too expensive or just don't help with plan shape enough. Jack Li blogged about a customer that was helped with this option here.

4. Your workload would benefit from manual statistics updates with a higher sample rate than the auto sample rate.

Some queries and workloads need more than the default sampled rate of statistics to achieve acceptable performance. This can be difficult to do on a large database but there are a few tricks and a few enhancements in later versions of SQL Server which will help.

If you know your data and workload very well you may be able to turn off automatic statistics updates. You can gather statistics that you need with FULLSCAN and update them when appropriate. This approach will require a lot of work and a lot of attention paid to the server.

If you have an existing maintenance process that rebuilds indexes (the wisdom of that is debated) note that rebuilding indexes automatically updates stats with FULLSCAN, so perhaps you can take advantage of that if you build a maintenance solution to update statistics.

Note that gathering sampled statistics may not be faster than fullscan statistics, especially if the histogram column is indexed. SQL Server can do fullscan statistics updates in parallel. It may also avoid a sort when doing a fullscan of an indexed column but will not avoid the sort when sampling the column. In fact, for large enough tables statistics updates against unindexed columns can fail if they fill up tempdb.

SQL Server 2014 introduced incremental statistics. Suppose that you have a partitioned table and lots of data is modified in just one partition. Previously, to update the statistics on the table you would have had to look at all of the partitions. With this new feature it is possible to just gather new statistics on the changed partition. SQL Server is able to roll up the statistics from the partitions into one table level object.

If you aren't able to upgrade you could consider converting some tables to partitioned views. Each table within the view will get its own statistics objects, so if you load data according to date you may only need to update statistics on the latest table in the view instead of all of the tables of the view.

Finally, as mentioned before, SQL Server 2016 can update sampled statistics in parallel:

  Starting with SQL Server 2016, sampling of data to build statistics is done in parallel, when using compatibility level 130, to improve the performance of statistics collection. The query optimizer will use parallel sample statistics, whenever a table size exceeds a certain threshold.


Optimizing query on a partitioned table Tried to find an answer for this, but couldn't. So bear with me and apologies if this has been answered elsewhere. It may be just me not being able to find it.

Here's the setup: SQL 2008 R2 EE. Table partitioned on column1, integer (YYYYMMDD). Clustered index and a couple of NC indexes, all aligned, using the same partition function.

Facts: table has close to 300 million records. Query would return around 2-3% of the data (around 6million records).

Here's the query:

SELECT column1, column2, column3 FROM table1 WHERE column1 = 20131120

I'm expecting this query to use the clustered index (seek or scan) as all data is there and there'll be no need for additional I/O. But instead of using the clustered index, Query Optimizer decided to use a NC index defined on one column: column5 (not included in any way in the query above). No included columns. Stats all updated yesterday, full sample.

My logical explanation is that it uses the partitioning column to force partition elimination and for this it does not need necessarily the clustered index, any index is good as it uses the same partition function.

Am I way off with my expectations or is it something I'm missing ?

Update

Here's the DDL. As you can see, the second index is indeed narrower, but only covers the column1 (as the parititioning column is included in each partitioned index even if not explicitly part of the key), but that may explain why it is being chosen. I had to anonymize it first, but kept the actual column positions in place in the code.

CREATE TABLE [dbo].[table1](
[c1] [bigint] NOT NULL,
[c2] [int] NOT NULL,
[c3] [smallint] NOT NULL,
[c4] [int] NOT NULL,
[c5] [int] NOT NULL,
[c6] [int] NOT NULL,
[c7] [int] NOT NULL,
[column1] [int] NOT NULL,
[c8] [int] NOT NULL,
[column3] [int] NOT NULL,
[c9] [int] NOT NULL,
[c10] [int] NOT NULL,
[c11] [int] NOT NULL,
[column2] [bigint] NOT NULL,
[column5] [int] NOT NULL,
[c13] [bigint] NOT NULL,
[c14] [datetime] NOT NULL,
[c15] [datetime] NOT NULL,
[c16] [datetime] NULL,
[c17] [datetime] NULL,
[c18] [tinyint] NOT NULL,
[c19] [tinyint] NOT NULL,
[c20] [tinyint] NOT NULL,
[c21] [tinyint] NOT NULL,
[c22] [tinyint] NOT NULL,
[c23] [int] NOT NULL,
[c24] [int] NOT NULL,
[c25] [int] NOT NULL,
[c26] [int] NOT NULL,
[c27] [int] NOT NULL,
[c28] [int] NOT NULL,
[c29] [bigint] NOT NULL,
[c30] [int] NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(   [column1] ASC,
[column2] ASC,
[column3] ASC)
WITH ...
(   PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON)
)
ON [partition_scheme]([column1])
GO

CREATE NONCLUSTERED INDEX [IX_table1_column5] ON [dbo].[table1]
(
[column5] ASC
)
WITH (  PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON
)
ON [partition_scheme]([column1])
GO

If you have a composite clustered index on (at least) (column1, column2, column3), then a non-clustered index on column5 is covering for ( column1, column2, column3, column5 ) and would be narrower than the clustered index, especially on a wide fact.  So that might be why.

Can you post the DDL and indexes for your table please?

I'm just going to reproduce that section from "Query Tuning and Optimization", CH3 here as hopefully it will help clear up what looks like a misunderstanding you have about covering indexes:

CREATE TABLE T_heap (a int, b int, c int, d int, e int, f int)
CREATE INDEX T_heap_a ON T_heap (a)
CREATE INDEX T_heap_bc ON T_heap (b, c)
CREATE INDEX T_heap_d ON T_heap (d) INCLUDE (e)
CREATE UNIQUE INDEX T_heap_f ON T_heap (f)
CREATE TABLE T_clu (a int, b int, c int, d int, e int, f int)
CREATE UNIQUE CLUSTERED INDEX T_clu_a ON T_clu (a)
CREATE INDEX T_clu_b ON T_clu (b)
CREATE INDEX T_clu_ac ON T_clu (a, c)
CREATE INDEX T_clu_d ON T_clu (d) INCLUDE (e)
CREATE UNIQUE INDEX T_clu_f ON T_clu (f)

Hopefully that makes senses!


How to update collections and restore while doing mongorestore? is there any way that i can update the collection and restore  the same from dump created by mongodump cmd .
when we do mongorestore , it directly restores from the dump , but my requireemnet is to update some data in the dump and then restore .

Thanks in advance .

Sorry, not possible. If you could update dump, you must have possibility to update indexes too...


Difficulty in moving a SQL Server database I'm trying to upgrade from SQL Server 2008 R2 Express to Developer.

My plan is to remove the Express edition, then install the Developer edition. I have one database I need to bring over to the new installation.

To prep, I made physical copies of the data and log files to the desktop, as well as performed a full backup to the desktop. I then detached the database in Management Studio.

To get a restore under my belt before removing Express, I went to physically move the .mdf and log files in windows explorer after detaching, but I don't see the files.

I thought perhaps there was no need, I could restore from the backup (again, still with Express), I get the error that there is insufficient space. And interestingly, I don't see the backup file on the desktop. I am sure I specified it carefully. I tried plan B, to attach the .mdf file that I copied to the desktop, and I get

  CREATE FILE encountered operating system error 2 (The system cannot
  find the file specified).

Any help here would be huge. Thanks.

If you wanted to make life easier, according to the Version and Edition Upgrades page, there exists an edition upgrade for SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer.  You could just perform an edition upgrade and call it a day.

Alternatively, if you wanted to keep with your original plan of installing a new instance and migrating the database...

If you can't find files on your system, try installing Everything.  It is an extremely fast indexing application that will run in your notification area.

Once you find the backup file, try running the following command to see if SQL Server can access it:

RESTORE HEADERONLY FROM DISK = 'drive:\path\to\mybackup.bak'

or

RESTORE FILELISTONLY FROM DISK = 'drive:\path\to\mybackup.bak'

If that does not work, then try moving the file to a location that the SQL Server service account has access to.  If you don't plan on accessing any network resources with SQL Server, it would be easiest to run SQL Server as local system on your workstation.

Once your Developer instance can see the .bak file, you can either use the Restore Database task in SSMS (Right-click Databases in Object Explorer and select Restore Database) or you can script it.  Just remember in either option you should change the destination path to the data and log files so they reside in the new default data path of the SQL Developer instance (or some other directory on your workstation).  By default, it'll try putting them back in the original location when the backup was taken.

Here is an example restore script you could modify (use the RESTORE FILELISTONLY details for the logical file names):

RESTORE DATABASE [MyDB] FROM DISK = 'drive:\path\to\mybackup.bak' WITH MOVE 'logicalDataFilename' TO 'drive:\path\to\physicalDataFilename.mdf', MOVE 'logicalLogFilename' TO 'drive:\path\to\physicalLogFilename.ldf'


ALTER TABLE WITH VALUES FROM SELECT I have a table1 with fluctuating number of columns. (colname1,colname2,colname3...colnamen)

I want to :

ALTER TABLE table2 CHANGE COLUMN `columns` `columns` ENUM('colname1','colname2','colname3' .... '') ...

each time the table1 gets changed in the structure

I know how to get the column names in a table with:

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema=

But how can I now get them in a string into the ALTER TABLE ?

@nbk had given what you want - atleast what you made us to understand.

But...

Think of re-designing your whole job. Do not go by your fluctuating number of columns. There are better ways. json is one. Transactional database is what is been used in lot of projects. Those are the right methods to store / retrieve than having variable number of columns.


Mysql Create table shows ERROR 1005 errno 150 I am trying to make a junction table, but I getting a ERROR 1005 errno 150

CREATE TABLE 61furiousFistPokemon(
cardNumber int NOT NULL PRIMARY KEY,
pokemonName VARCHAR(12),
type VARCHAR(10),
stage VARCHAR(10),
evolvesFrom VARCHAR(12),
HP INT,
retreatCost INT,
weakness VARCHAR(10),
weaknessAmount VARCHAR(5),
resistance VARCHAR(5),
resistanceAmount VARCHAR(5)
);

CREATE TABLE cardTags (
tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
tagName VARCHAR(60) NOT NULL
);

CREATE TABLE 61furiousFistPokemonTags (
tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
pokemonCardNumber int,
tagName VARCHAR(60),
FOREIGN KEY (pokemonCardNumber) REFERENCES 61furiousFistPokemon(cardNumber),
FOREIGN KEY (tagName) REFERENCES cardTags(tagName)
);

Any help would be greatly appreciated.

tagName column in table cardTags  is not defined as primary key and you are declaring foreign key FOREIGN KEY (tagName) REFERENCES cardTags(tagName).


How to implement a multi-user account facility in a database I'm modelling a database schema that has the requirement of including a multi-user account facility for premium customers:

  A multiple account enables multiple users to login to the same account
  in order to do the job according to their permission level. This means
  that it is possible to maintain a single account for your entire
  marketing team. The account owner can add as many users as he/she
  wants and limit what permissions each of them has. You can choose role
  for each user: account manager, marketing executive, marketing
  assistant, etc… or create a customized permission.

At the moment I have one users table that stores the information of a generic user with the username and password etc. How would one implement a multi-user account facility? Could an extra field be added to the users table? Would a separate table be required? I would like to use the principle of keeping this simple where possible.

You should have an accounts table and a users table, with either a one-to-many or a many-to-many relationship between the two.

As an example of the many-to-many relationship:

CREATE TABLE Accounts
(
    AccountID int NOT NULL
    , AccountName varchar(30) NOT NULL
);

CREATE TABLE Users
(
    UserID int NOT NULL
    , UserName varchar(30) NOT NULL
);

CREATE TABLE AccountsUsers
(
    AccountID int NOT NULL
        FOREIGN KEY
        REFERENCES Accounts(AccountID)
    , UserID int NOT NULL
        FOREIGN KEY
        REFERENCES Users(UserID)
    , UserLevel int NOT NULL
);

The UserLevel represents the type of account access; account manager, marketing executive, marketing assistant, etc.

The one-to-many relationship would look like:

CREATE TABLE Accounts
(
    AccountID int NOT NULL
        CONSTRAINT PK_Accounts
        PRIMARY KEY CLUSTERED
    , AccountName varchar(30) NOT NULL
);

CREATE TABLE UserLevels
(
    UserLevelID int NOT NULL
        CONSTRAINT PK_UserLevels
        PRIMARY KEY CLUSTERED
    , UserLevelDesc varchar(30) NOT NULL
);

CREATE TABLE Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , UserName varchar(30) NOT NULL
    , AccountID int NOT NULL
        CONSTRAINT FK_Users_AccountID
        FOREIGN KEY
        REFERENCES Accounts(AccountID)
    , UserLevelID int NOT NULL
        CONSTRAINT FK_Users_UserLevelID
        FOREIGN KEY
        REFERENCES dbo.UserLevels(UserLevelID)
);

Insert some sample data:

INSERT INTO dbo.Accounts (AccountID, AccountName)
VALUES (1, 'Microsoft');

INSERT INTO dbo.UserLevels (UserLevelID, UserLevelDesc)
VALUES (1, 'User')
    , (2, 'Manager')
    , (3, 'Owner');

INSERT INTO dbo.Users (UserID, UserName, AccountID, UserLevelID)
VALUES (1, 'Bill Gates', 1, 3)
    , (2, 'Sataya Nadella', 1, 2);

Query combining the three tables:

SELECT a.AccountName
    , u.UserName
    , ul.UserLevelDesc
FROM dbo.Accounts a
    INNER JOIN dbo.Users u ON a.AccountID = u.AccountID
    INNER JOIN dbo.UserLevels ul ON u.UserLevelID = ul.UserLevelID

Results:

╔═════════════╦════════════════╦═══════════════╗
║ AccountName ║    UserName    ║ UserLevelDesc ║
╠═════════════╬════════════════╬═══════════════╣
║ Microsoft   ║ Bill Gates     ║ Owner         ║
║ Microsoft   ║ Sataya Nadella ║ Manager       ║
╚═════════════╩════════════════╩═══════════════╝

Cleanup:

IF OBJECT_ID(N'dbo.UserLevels', N'U') IS NOT NULL
DROP TABLE dbo.UserLevels;
IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL
DROP TABLE dbo.Users;
IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
DROP TABLE dbo.Accounts;


Is it possible to set a timeout for optimization in PostgreSQL? In PostgreSQL is there a timeout when the optimizer stops the optimization process and returns the least expensive plan it has found so far? Is it possible to change its value?

No, and no, though it does have some heuristics (like join_collapse_limit) to limit how long it tries for, and it also has things like GEQO to switch to different optimisation strategies for big joins under some circumstances.

There's no optimisation timeout as such.

(It'd help if you'd explain the kinds of queries you're running and why they're taking so long to plan.)


How to attach a sql db with blobs using a shared folder? I use the following query for attaching a sql db:

CREATE DATABASE [myDb] ON
 (FILENAME=N'C:\myDb\myDb.mdf'),
 (FILENAME=N'C:\myDb\myDb_log.ldf'),
 (FILENAME=N'C:\myDb\FILESTORE')
FOR ATTACH

In general it works well. I have a bit complicated case. I have to use a shared folder for attaching my db. I create a shared folder using the following command:

net share myShare=C:\myDb

Attaching a db from shared folder works well until the db doesn't have blobs.
If db has blobs sql returns the following error:

The path '\\localhost\myShare\FILESTORE' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.
Msg 5135, Level 16, State 2, Line 2
The path '\\localhost\myShare\FILESTORE' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.
Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "\\localhost\myShare\FILESTORE". Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

The sort of workaround for this problem is creating a symbolic link for blob folder.

mklink /D "C:\slink_myDb" "\\localhost\myShare"

and then

CREATE DATABASE [myDb] ON
 (FILENAME=N'\\localhost\myShare\myDb.mdf.mdf'),
 (FILENAME=N'\\localhost\myShare\myDb_log.ldf'),
 (FILENAME=N'C:\slink_myDb\FILESTORE')
FOR ATTACH

It works with almost all sql servers but it looks like since sql server 2016 it was disabled. sql server 2016 returns this:

Msg 5120, Level 16, State 106, Line 9
Unable to open the physical file "C:\slink_myDb\FILESTORE". Operating system error -1073741766: "0xc000003a(failed to retrieve text for this error. Reason: 317)".
Msg 5105, Level 16, State 14, Line 9
A file activation error occurred. The physical file name 'C:\slink_myDb\FILESTORE' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 9
Could not open new database 'myDb'. CREATE DATABASE is aborted.

How to attach a sql db with blobs via unc paths ?
(without copying; db files are local, sql server is remote)

ps
All needed permission ntfs and sharing are granted.
I've tried fsutil

fsutil behavior set SymlinkEvaluation L2L:1 R2R:1 L2R:1 R2L:1

Apparently it is not supported to use "localhost"

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-smb-fileshare-as-a-storage-option?view=sql-server-2017

You may need to use another server instead of localhost.


Select pairs (a,b) excluding (b,a) with same value For a given structure like this:

+----+----------+
| ID |   Name   |
+----+----------+
| a  | ZZZZZZZZ |
| b  | YYYYYYYY |
| c  | WWWWWWWW |
| d  | ZZZZZZZZ |
| e  | YYYYYYYY |
| f  | WWWWWWWW |
+----+----------+

Get the list of pairs (ID, ID2) with same Name, excluding all records where (ID2, ID) has the corresponding (ID, ID2).

+----+----------+-----+
| ID |   Name   | ID2 |
+----+----------+-----+
| a  | ZZZZZZZZ |  d  |
| b  | YYYYYYYY |  e  |
| c  | WWWWWWWW |  f  |
+----+----------+-----+

I've found this solution, but I wonder if there is a more "academic" solution.

;WITH Pair AS
(
SELECT
    CASE WHEN p1.Id > p2.Id THEN p2.Id ELSE p1.Id END as ID,
    CASE WHEN p1.Id > p2.Id THEN p1.Id ELSE p2.Id END as ID2,
    p1.Name
FROM Pairs p1
     LEFT JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id <> p2.Id
)
SELECT DISTINCT ID, ID2, Name
FROM Pair;

Rextester

CREATE TABLE Pairs(Id varchar(5), Name varchar(20));
INSERT INTO Pairs VALUES
('a','ZZZZZZZZ'),
('b','YYYYYYYY'),
('c','WWWWWWWW'),
('d','ZZZZZZZZ'),
('e','YYYYYYYY'),
('f','WWWWWWWW');

SELECT
    p1.Id,p2.Id,p1.Name
FROM Pairs p1
     JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id < p2.Id


SSIS VS17 Unsupported: This version of visual studio is unable to open the following projects dtproj I get this error when trying to open VS17 With SSIS Project written on VS15:

These actions solved my problem:

Install the latest version of SSDT
Open Visual Studio 2017: Go to: Tools > Extensions And Updates > Search: Microsoft Integration Services Projects > Click: Enable
Restart Visual Studio
In SSIS Project On Solution: Right Click > Reload Project


What is the equivalent of SELECT  in Flux Query Language? What would be equivalent flux query for SELECT address, name FROM addresses ? (I am referring to FluxQL, the new query language developed by InfluxData)

I didn't find a clear answer to this in the limited Flux Documentation present. Flux documentation says that filter() function is the equivalent of both SELECT and WHERE clauses, but all examples given are equivalent of WHERE clauses, nothing on SELECT.

Edit: An answer posted below gives the equivalent query in InfluxQL, which is not what I am asking for. Yeah I know, FluxQL, InfluxQL, it can get confusing.

These are the documentation for FluxQL for better reference:

https://docs.influxdata.com/flux/v0.50/introduction/getting-started

https://v2.docs.influxdata.com/v2.0/query-data/get-started/

See Writing and Exploring Data as part of the Getting Started documentation.

> SELECT "address", "name" FROM "addresses"
name: addresses
---------
time                              address            name
2020-03-09T07:51:07.580664347Z    123 fake street    Billy Bob

>