Date and time period query I would be very grateful if someone can help me here: in my database, I have prices recorded at a particular time in a day over years. So for each vendor or station, there can be several price data points within a day. The data is stored in a timestamp format so I can see which specific time within a specific day that the price was recorded.

My problem: I want to be able to select the price data for a period e.g. between 2014-01-01 and 2014-03-31 and at the same time select all the data within a specific time period e.g., between 08:00:00 and 20:00:00 for each day. I attempted it with this:

SELECT  station.id,
        DATE(item_price.date_time) as mydate,
        TIME(item_price.date_time) as mytime,
        item_price.price
    FROM  station
    INNER JOIN  item_price  ON station.id=item_price.station
    WHERE  date_time >='2014-01-01'
      AND  date_time < '2014-03-31'
      AND  date_time >= '08:00:00'
      AND  date_time <  '20:00:00'
    ORDER BY  item_price.date_time;

But, I'm unable to obtain the price data between '08:00:00' and '20:00:00' for each day in the specified period. Can someone please help me out?

Your WHERE clause has two parts that deal with date and time. The date part is OK but the time part isn't doing what you want. You need to extract only the time from the timestamp values, with TIME(date_time):

WHERE
          -- date part
      date_time >= '2014-01-01'
  AND date_time  < '2014-04-01'    -- minor correction
          -- time part
  AND TIME(date_time) >= '08:00:00'
  AND TIME(date_time)  < '20:00:00'

The whole query:

SELECT
    st.id,
    DATE(ip.date_time) AS mydate,
    TIME(ip.date_time) AS mytime,
    ip.price
FROM
    station AS s
    INNER JOIN item_price AS ip
    ON st.id = ip.station
WHERE
          -- date part
      ip.date_time >= '2014-01-01'
  AND ip.date_time  < '2014-04-01'    -- minor correction
          -- time part
  AND TIME(ip.date_time) >= '08:00:00'
  AND TIME(ip.date_time)  < '20:00:00'
ORDER BY
    ip.date_time ;


Date variable to filter result for all previous years I am querying a sales order table and need to add a variable so that the query always returns a customer list for all customers that have not purchased this year.  I don't want to hard code this years' date into WHERE or HAVING, but instead am interested to somehow have this dynamically pass the correct variable so that it also works in the future without changing the code. I appreciate any suggestions. Hope this is clear enough.

There are many ways to get the beginning of this year, I think the most intuitive is DATEFROMPARTS():

DECLARE @BeginningOfThisYear date = DATEFROMPARTS(YEAR(SYSUTCDATETIME()),1,1);

SELECT ... FROM dbo.Customers AS c
WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.Orders
    WHERE CustomerID = c.CustomerID
    AND OrderDate >= @BeginningOfThisYear
);

Less intuitive ways include:

DECLARE @BeginningOfThisYear date = DATEADD(DAY,
  1-DATEPART(DAYOFYEAR, SYSUTCDATETIME()),
  SYSUTCDATETIME()
);

And downright terrible ways include:

DECLARE @BeginningOfThisYear date = CONVERT(date,
  CONCAT(YEAR(SYSUTCDATETIME()),'0101'));


Append columns with values from has many reletionship I have two tables orders and items. Every Order has many Items.

orders
- id
- created_at
- paid_at

items
- id
- title
- amount
- quantity
- order_id

Let say that there are 3 kinds of items. Chair, table and door.

I would like to create a query that will produce table with following columns:

- order_id
- created_at
- paid_at
- item_id # for chair
- item_title
- item_quantity
- item_amount
- item_id # for table
- item_title
- item_quantity
- item_amount
- item_id # for door
- item_title
- item_quantity
- item_amount

If order has chair item, data in columns for chair will be displayed otherwise it will be empty.

Why am I doing this?

I would like to export this data to excel sheet.

There is limited amount of different items so there is no problem with milions of columns.

You can join to a single table multiple times. Just use different aliases.

And use LEFT [OUTER] JOIN to accommodate for the possibility of missing rows.

Assuming there can only be a single instance of chair, table and door attached to each order. You did not specify that, but your question would hardly make sense otherwise.

I introduce item_type in the table items to make this work. Consider the schema in the fiddle.

SELECT o.id AS order_id, o.created_at, o.paid_at
      ,c.id       AS chair_id
      ,c.title    AS chair_title
      ,c.quantity AS chair_quantity
      ,c.amount   AS chair_amount

      ,t.id       AS table_id
      ,t.title    AS itable_title
      ,t.quantity AS table_quantity
      ,t.amount   AS table_amount

      ,d.id       AS door_id
      ,d.title    AS door_title
      ,d.quantity AS door_quantity
      ,d.amount   AS door_amount
FROM   orders o
LEFT   JOIN items AS c ON c.id = o.id AND c.item_type = 'chair'
LEFT   JOIN items AS t ON t.id = o.id AND t.item_type = 'table'
LEFT   JOIN items AS d ON d.id = o.id AND d.item_type = 'door';

Use distinct column aliases to make this work.

-> SQLfiddle demo


Conversion of a varchar data type to a datetime data type resulted in an out-of-range value I am trying to run a simple query to get all rows created in November:

SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN '2014-11-01 00:00:00.000'
AND '2014-11-30 23:59:59.997';

SMSS returns:

  The conversion of a varchar data type to a datetime data type resulted
  in an out-of-range value.

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime:

Do I need to tell the server that 'Created' is datetime? If not, why am I getting this varchar message?

Edit: The value in the database was YYYY-MM-DD. Reply from @SqlZim below says that I need to use convert() to tell sql what format the date is in the db - and to replace the space character with the letter T:

select count(*)
from dbo.profile
where [created] between convert(datetime,'2014-11-01T00:00:00.000')
and convert(datetime,'2014-11-30T23:59:59.997');`

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*)
  from dbo.profile
  where [created] between convert(datetime,'2014-11-01T00:00:00.000')
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.


don't the users SYS and SYSTEM share the table dba_users? While being logged in as SYS, I created a new user. I ran

 select username from dba_users order by username;

and I was able to see the user that i just created;

Then, I logged as SYSTEM, I ran the same query, but the user was missing this time.  This leads me to the question: don't all users have the same instance of the table DBA_USERS? I thought that this was a system related table, and all users get the same instance (as long as they have the right privileges to access this table).

Local users created in a pluggable database are not visible in other containers through DBA_USERS. You can query all users from all open pluggable databases in the root container through CDB_USERS.

Overview of Commonality in a CDB

SQL> show con_name pdbs

CON_NAME
------------------------------
CDB$ROOT

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SAMPLE                         READ WRITE NO
         5 BENCHMARK                      READ WRITE NO

SQL>  conn sys/Oracle123@o7ca1-vip/SAMPLE as sysdba
Connected.
SQL> create user user_created_by_sys identified by 1;

User created.

SQL> conn system/Oracle123@o7ca1-vip/BENCHMARK
Connected.
SQL> create user user_created_by_system identified by 1;

User created.

SQL> select username from dba_users where username like 'USER_CREATED%';

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
USER_CREATED_BY_SYSTEM

SQL> conn sys/Oracle123@o7ca1-vip/SAMPLE as sysdba
Connected.
SQL> select username from dba_users where username like 'USER_CREATED%';

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
USER_CREATED_BY_SYS

SQL> conn / as sysdba
Connected.
SQL> select username from dba_users where username like 'USER_CREATED%';

no rows selected

SQL> select con_id, username from cdb_users where username like 'USER_CREATED%';

    CON_ID USERNAME
---------- --------------------------------------------------------------------------------------------------------------------------------
         3 USER_CREATED_BY_SYS
         5 USER_CREATED_BY_SYSTEM


Insert escaped double quotes into MySQL JSON field I am trying to insert a stringified JSON which looks like this:

'{"test": "string with \"escaped quotes\" does not work"}'

into a MySQL JSON field, but I always get the error:

  SQL Error (3140): Invalid JSON text: "Missing a comma or '}' after an object member."
  at position 24 in value (or column) '{"test": "string with "escaped quotes" does not work"}'.

Any idea what might be causing the issue?

The full INSERT statement is:

INSERT INTO test(json_test)
VALUES ('{"test": "string with \"escaped quotes\" does not work"}');

Let's create the table:

mysql> create table jsontest (id serial, value json);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table jsontest\G
*************************** 1. row ***************************
Table: jsontest
Create Table: CREATE TABLE `jsontest` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` json DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And insert some JSON data/objects:

mysql> INSERT INTO jsontest(value) VALUES (JSON_OBJECT('test', 'string with "escaped quotes" does not work'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO jsontest(value) VALUES ('{"test": "string with \\"escaped quotes\\" does not work"}');
Query OK, 1 row affected (0.01 sec)

mysql> select id, value->>"$.test" from jsontest;
+----+--------------------------------------------+
| id | value->>"$.test"                           |
+----+--------------------------------------------+
|  2 | string with "escaped quotes" does not work |
|  9 | string with "escaped quotes" does not work |
+----+--------------------------------------------+
2 rows in set (0.00 sec)

As you can see here you have to use the double backslash escape sequence because you don't want the SQL parser to perform the standard escape sequence processing but instead you want to pass the literal string containing the escape sequence down to the storage engine for the JSON data type processing.

I'll talk to the MySQL docs team about adding some examples and explanation for that here:

12.6 The JSON Data Type - Creating JSON Values


How to get list of dates between From Date and To Date in MySQL I have a table Emp_UnAvailablity_ID`, `PersonID`, `Is_UnAvailable`, `From_Date`, `To_Date` FROM `emp_unavailablity. I want to get list of all dates between From_Date and To_Date. I have tried this query SELECT date(From_Date) AS F, DATEDIFF(date(To_Date), date(From_Date)) AS D FROM emp_unavailablity its output is in the attached image

Here is my table data

I have tried some php code to get list of dates but not worked for me.

$connection = Yii::app()->db;
    $sql = "
        SELECT date(From_Date) AS F, DATEDIFF(date(To_Date), date(From_Date)) AS D FROM emp_unavailablity ";
    $command = $connection->createCommand($sql);
    $dataReader = $command->queryAll();

    $newValues = array();
    foreach($dataReader as $key => $value)
    {
        $newValues[$key] = $value;
        if($key == 'F')
        {
            for ($i=0; $i < $key['D']; $i++) {
                $xx = $key['F'] + $i;     //f + i
                $newValues = $xx;
            }
        }
    }
    print_r($newValues);

Please help me to solve this problem. Thanks

Based on the accepted answer on Stack Overflow here, you can adapt it to fit your requirements:

select *
from emp_unavailablity,
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) datelist from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) as v
where datelist>from_date and datelist <to_date;

Basically we build an inline view with a list of all dates for the next few thousand years, and use that as a list to select from.

Sample SQL Fiddle here.


Using postgres to select a filtered array I'm looking to select data from a table that contains an ID, and an array of values. But in the query results, i want to filter the array and remove values that overlap with a white list.

For illustrative purposes, the following query shows how the data would be formatted with a whitelist of values in a single column, and then a table i want to select from... that contains a unique ID, and the array of keywords.

with whitelist as (
    select distinct unnest(array['a','b','c','f','h']) as keywords
), raw_with_keyword_array as (
    select * from  (
        values
            ('1234', array['a','c','e','h','q']),
            ('1235', array['a','d','e','f'])
    ) as t (id, keyword_array)
)
select...

The expected results i'm looking for is two rows from the raw_with_keyword_array table, but filtered arrays not including the values from the whitelist.

I figure i probably need to select the data from the main table, and then do some kind of recursive sub query to filter out the whitelisted values, and put the results back into a new array?

Any suggestions would be appreciated!

E.g.

1234    {e, q}

1235    {d, e}

Not sure if this is the most efficient way to do it, but this works.

You do have to unnest all elements from keyword_array to be able to filter the unwanted out:

with whitelist (kw) as (
    values ('a'),('b'),('c'),('f'),('h')
), raw_with_keyword_array (id, keyword_array) as (
  values
      ('1234', array['a','c','e','h','q']),
      ('1235', array['a','d','e','f'])
)
select id,
       array(select kw
             from unnest(ra.keyword_array) as t(kw)
             except
             select kw
             from whitelist)
from raw_with_keyword_array ra;

Instead of except you could also use a where not exists:

....
select id,
       array(select kw
             from unnest(ra.keyword_array) as t(kw)
             where not exists (select *
                               from whitelist wl
                               where wl.kw = t.kw)
             )
from raw_with_keyword_array ra;

I don't think it makes a big difference though.

Online example


Enable TDE When Publishing DACPAC In the database properties in Visual Studio there is a checkbox for encryption. I'm not sure if I check this if it will enforce encryption when doing a DACPAC deploy. What I would really like to do is enable encryption per environment. Ideally I would add a setting to my dacpac publish profile that determines whether or not encryption will be enabled on the database when publishing. Currently TDE gets disabled every time I do a publish.

This setting will only let you 'respect' the encrypted database setting.  It will not generate scripts for you to run to enable encryption on the target deployment server.  If you uncheck that, it will then disable encryption on your source database scripts that you are generating for the DACPAC.

Since TDE requires several keys to fully function, you might need to configure that outside of the DACPAC in order to set encryption as on/off per database/environment.  You could utilize this as part of your greater rollout effort (using orchestration like Jenkins/Octopus/maybe TFS), doing it as part of a powershell script, and so fourth.

From:
https://social.msdn.microsoft.com/Forums/en-US/df7d6f7f-f912-48ae-b553-0cede0eedce1/ssdt-and-tde?forum=ssdt

"When this checkbox is checked, no encryption changes are made to the target database*. That is, databases are neither encrypted or decrypted. I don't see that it performs any function at all, but perhaps it will be utilized in a future version.

When this checkbox is unchecked, encrypted database are decrypted, and no changes are made to unencrypted databases. No warning or status message is output to the messages window when running the script. This is the coe that is generated:

IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ENCRYPTION OFF
            WITH ROLLBACK IMMEDIATE;
    END
"


Why is GETUTCDATE earlier than SYSDATETIMEOFFSET? Alternatively, how did Microsoft make time travel possible?

Consider this code:

DECLARE @Offset datetimeoffset = sysdatetimeoffset();
DECLARE @UTC datetime = getUTCdate();
DECLARE @UTCFromOffset datetime = CONVERT(datetime,SWITCHOFFSET(@Offset,0));
SELECT
    Offset = @Offset,
    UTC = @UTC,
    UTCFromOffset = @UTCFromOffset,
    TimeTravelPossible = CASE WHEN @UTC < @UTCFromOffset THEN 1 ELSE 0 END;

@Offset is set before @UTC, yet it sometimes has a later value. (I've tried this on SQL Server 2008 R2 and SQL Server 2016. You have to run it a few times to catch the suspect occurrences.)

This does not appear to be simply a matter of rounding or lack of precision. (In fact, I think the rounding is what "fixes" the issue occasionally.) The values for a sample run are as follows:

Offset

2017-06-07 12:01:58.8801139 -05:00

UTC

2017-06-07 17:01:58.877

UTC From Offset:

2017-06-07 17:01:58.880

So datetime precision allows the .880 as a valid value.

Even Microsoft's GETUTCDATE examples show the SYS* values being later than the older methods, despite being SELECTed earlier:

SELECT 'SYSDATETIME()      ', SYSDATETIME();
SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();
SELECT 'SYSUTCDATETIME()   ', SYSUTCDATETIME();
SELECT 'CURRENT_TIMESTAMP  ', CURRENT_TIMESTAMP;
SELECT 'GETDATE()          ', GETDATE();
SELECT 'GETUTCDATE()       ', GETUTCDATE();
/* Returned:
SYSDATETIME()            2007-05-03 18:34:11.9351421
SYSDATETIMEOFFSET()      2007-05-03 18:34:11.9351421 -07:00
SYSUTCDATETIME()         2007-05-04 01:34:11.9351421
CURRENT_TIMESTAMP        2007-05-03 18:34:11.933
GETDATE()                2007-05-03 18:34:11.933
GETUTCDATE()             2007-05-04 01:34:11.933
*/

I presume this is because they come from different underlying system information. Can anyone confirm and provide details?

Microsoft's SYSDATETIMEOFFSET documentation says "SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API" (thanks srutzky), but their GETUTCDATE documentation is much less specific, saying only that the "value is derived from the operating system of the computer on which the instance of SQL Server is running".

(This isn't entirely academic. I ran into a minor issue caused by this. I was upgrading some procedures to use SYSDATETIMEOFFSET instead of GETUTCDATE, in hopes for greater precision in the future, but I started to get odd ordering because other procedures were still using GETUTCDATE and occasionally "jumping ahead" of my converted procedures in the logs.)

The issue is a combination of datatype granularity / accuracy and source of the values.

First, DATETIME is only accurate / granular to every 3 milliseconds. Hence, converting from a more precise datatype such as DATETIMEOFFSET or DATETIME2 won't just round up or down to the nearest millisecond, it could be 2 milliseconds different.

Second, the documentation seems to imply a difference in where the values come from. The SYS* functions use the high-precision FileTime functions.

SYSDATETIMEOFFSET documentation states:

  SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API.

while the GETUTCDATE documentation states:

  This value is derived from the operating system of the computer on which the instance of SQL Server is running.

Then, in the About Time documentation, a chart shows the following two (of several) types:

System Time = "Year, month, day, hour, second, and millisecond, taken from the internal hardware clock."
File Time = "The number of 100-nanosecond intervals since January 1, 1601."

Additional clues are in the .NET documentation for the StopWatch class (emphasis in bold italics mine):

Stopwatch Class

  The Stopwatch measures elapsed time by counting timer ticks in the underlying timer mechanism. If the installed hardware and operating system support a high-resolution performance counter, then the Stopwatch class uses that counter to measure elapsed time. Otherwise, the Stopwatch class uses the system timer to measure elapsed time.

Stopwatch.IsHighResolution Field

  The timer used by the Stopwatch class depends on the system hardware and operating system. IsHighResolution is true if the Stopwatch timer is based on a high-resolution performance counter. Otherwise, IsHighResolution is false, which indicates that the Stopwatch timer is based on the system timer.

Hence, there are different "types" of times that have both different precisions and different sources.

But, even if that is a very loose logic, testing both types of functions as sources for the DATETIME value proves it. The following adaptation of the query from the question shows this behavior:

DECLARE @Offset DATETIMEOFFSET = SYSDATETIMEOFFSET(),
        @UTC2 DATETIME = SYSUTCDATETIME(),
        @UTC DATETIME = GETUTCDATE();
DECLARE @UTCFromOffset DATETIME = CONVERT(DATETIME, SWITCHOFFSET(@Offset, 0));
SELECT
    Offset = @Offset,
    UTC2 = @UTC2,
    UTC = @UTC,
    UTCFromOffset = @UTCFromOffset,
    TimeTravelPossible = CASE WHEN @UTC < @UTCFromOffset THEN 1 ELSE 0 END,
    TimeTravelPossible2 = CASE WHEN @UTC2 < @UTCFromOffset THEN 1 ELSE 0 END;

Returns:

Offset                 2017-06-07 17:50:49.6729691 -04:00
UTC2                   2017-06-07 21:50:49.673
UTC                    2017-06-07 21:50:49.670
UTCFromOffset          2017-06-07 21:50:49.673
TimeTravelPossible     1
TimeTravelPossible2    0

As you can see in the above results, UTC and UTC2 are both DATETIME datatypes. @UTC2 is set via SYSUTCDATETIME() and is set after @Offset (also taken from a SYS* function), but prior to @UTC which is set via GETUTCDATE(). Yet, @UTC2 appears to come before @UTC. The OFFSET part of this is completely unrelated to anything.

HOWEVER, to be fair, this still isn't proof in a strict sense. @MartinSmith traced the GETUTCDATE() call and found the following:

I see three interesting things in this call stack:

Is starts with a call to GetSystemTime() which returns a value that is only precise down to the milliseconds.
It uses DateFromParts (i.e. no formula to convert, just use the individual pieces).
There is a call to QueryPerformanceCounter towards the bottom. This is a high-resolution difference counter that could be being used as a means of "correction". I have seen some suggestion of using one type to adjust the other over time as long intervals slowly get out of sync (see How to get timestamp of tick precision in .NET / C#? on S.O.). This does not show up when calling SYSDATETIMEOFFSET.

There is much good info here regarding the different types of time, different sources, drift, etc: Acquiring high-resolution time stamps.

Really, it is not appropriate to compare values of different precisions. For example, if you have 2017-06-07 12:01:58.8770011 and 2017-06-07 12:01:58.877, then how do you know that the one with less precision is greater than, less than, or equal to the value with more precision? Comparing them assumes that the less precise one is actually 2017-06-07 12:01:58.8770000, but who knows if that is true or not? The real time could have been either 2017-06-07 12:01:58.8770005 or 2017-06-07 12:01:58.8770111.

However, if you have DATETIME datatypes, then you should use the SYS* functions as the source as they are more accurate, even if you lose some precision as the value is forced into a less precise type. And along those lines, it seems to make more sense to use SYSUTCDATETIME() rather than call SYSDATETIMEOFFSET() only to adjust it via SWITCHOFFSET(@Offset, 0).


Oracle 11gR2 + RedHat 6.5 cannot start sqlplus I cannot start sqlplus
I've read everything I found, tried everything and still I am getting "command not found"

I have a user oracle, I did export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

.bash_profile is:

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
umask 022
# User specific environment and startup programs

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME

PATH=$PATH:$HOME/bin

export PATH

You need to set LD_LIBRARY_PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

and to add $ORACLE_HOME/bin (sqlplus path is inside) in your path

PATH=:$ORACLE_HOME/bin:$PATH


SQL Server Cumulative Updates - not visible in Windows Update I just installed all possible Windows updates on WS2008R2/SQL2012 instance. When I run

SELECT @@VERSION

I get

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

According to

  http://sqlserverbuilds.blogspot.com/

and Microsoft websites, there are 4 Cumulative Updates available for SQL Server 2012 SP2 and the newest build is 11.00.9000.

Why didn't Windows Update get Cumulative Updates too? Should I download and install them manually?

Windows Update will only tell you about service packs for SQL Server, and only when you've enabled the "update other Microsoft products too" option (sorry, the wording has changed across versions, and I don't recall all of the variations).

SQL Server service packs are recommended primarily because the RTM branch (and earlier service pack branches) are ultimately retired and placed out of support. They are also supposedly more reliable and better tested than cumulative updates. I don't know that they should be provided via Windows Update, though, because that just makes it possible for a service pack to be deployed automatically. I don't like that idea at all - and I don't get a warm and fuzzy about how often I have to disable the "force feed me updates" setting and change it to a more appropriate "download them and tell me about them" option.

Cumulative updates, on the other hand, are recommended by some of us (Glenn Berry and I are vocal proponents of keeping up with CUs, because generally we've found them to be just as solid as service packs - though recently there have been a few chinks in that armor). But the message from Microsoft is not so clear. While they do put the updates out and publicize them, each one of them has this text (this is taken from 2012 SP2 CU4):

  We recommend that you apply The most recent update release.

The link points to KB 2983249, which gives you links to all of the cumulative updates for that branch. But then, in the CU article, the very next piece of text is:

  This cumulative package is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems.

This disclaimer is probably the reason you won't see cumulative updates being pushed through Windows Update. I know they've been working on bolstering the regression testing and therefore the stability that you can expect from cumulative updates, but until that disclaimer disappears, you're going to continue to have to seek out cumulative updates on your own. I try to keep people updated on all updates (service packs, cumulative updates, and out-of-band on-demand hotfixes) for SQL Server 2012 and SQL Server 2014, with some qualitative advice thrown in where appropriate:

Latest Builds of SQL Server 2012
Latest Builds of SQL Server 2014

From those two posts, you can probably guess that if people had received the most recent cumulative updates automatically, through Windows Update, they would be plenty upset, and justifiably so. I'm all for deploying cumulative updates but this rash of problems has made me adjust my timeline for doing so - like add a month, maybe, so that other people can shake these issues out and I stop being the guinea pig.


Statement based updates to a server using row based logging I have two MySQL servers. Server A is doing statement based logging. Server B is doing row based logging. B is replicating from A.

Does B change the statement based SQL to row based SQL, execute the row based SQL and then log this row based SQL in its binary log, or does it execute the statement based SQL and then translates it to row based SQL before logging it?

The logs will be applied in the format which they are consumed, so on Server B statement based execution is applied.

Whether or not Server B even produces binary logs depends on whether or not binary logging is enabled on Server B. https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_bin
It is not required if the server is only to be a slave.

Even then, Server B will only produce binary logs related to the updates it receives from Server A if log_slave_updates is set
https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_log-slave-updates

The resulting log format from the slave updates depends only on what binlog_format is set as on the slave https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_binlog-format

Keep in mind that Server A(statement format) to Server B(row/mixed format)  will work fine, but Server A(row/mixed format) to Server B (statement format) will fail. See this question for further explanation
https://dba.stackexchange.com/a/159905/129765

To answer your question tersely, Server B executes the statement based SQL and then translates it to row based SQL which it outputs to the binary logs.


Select,From,Join,Where within 1 query MySQL Wondering if its possible to use WHERE within such a big query string?
I cannot seem to achieve what I want once I add Where into the query.

customer
+----+------+------+
| id | type | name |
+----+------+------+
| 1  | boy  | dave |
| 2  | girl | lisa |
+------------------+

order
+----+--------+
| id | status |
+----+--------+
| 1  |  done  |
| 2  |  done  |
+-------------+

SELECT `customer`.*,order.status
FROM `customer`
JOIN `order` on customer.id = order.id
WHERE customer.type = boy

I assumed this would return result

'id' = 1
'type' = boy
'name' = dave
'status' = done

But instead I get errors.

If I do not include Where, I will get result

'id' = 1
'type' = boy
'name' = dave
'status' = done

'id' = 2
'type' = girl
'name' = lisa
'status' = done

Thanks for any help in advance.

from the error you've described, I am pretty sure it´s a mere missing single quotes around the word "boy" in your query. That's why removing the where clause it works.

SELECT `customer`.*,order.status
FROM `customer`
JOIN `order` on customer.id = order.id
WHERE customer.type = 'boy'


Does DBCC TRACEON() invalidate cached execution plans? In Microsoft SQL Server, does DBCC TRACEON() invalidate cached execution plans, same as SP_Configure invalidates all stored procedure plans ?

sp_configure doesn't invalidate all stored procedure plans. sp_configure used to configure certain values which do cause plans to be invalidated. For example on my test system with nothing else running, and filtering just for cached procedure plans:

--Purposely empty all buffers and cache. DO NOT RUN ON PROD!!!!!!
dbcc dropcleanbuffers;
--Just run something to put something in the cache
exec sp_who;
exec sp_who2;
exec sp_configure;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 14
EXEC sp_configure 'media retention', '3';
reconfigure with override;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 14

--This assumes maxdop is at the default of 1
EXEC sp_configure 'max degree of parallelism', '1';
reconfigure with override;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 0

Which is expected as it's only the last sp_configure usage that would invalidate an execution plans (other config value changes will do this as well).

DBCC may work the same way for some Trace flags, but having run through the same test using all of the ones I usually use I can't find any that do.

If you have a particular trace flag you want to use, and a test system, then this should be easy to test using something like this:

select count(1) from sys.dm_exec_cached_plans
--this number of cached objects before turning on trace flag
dbcc traceon (3604,-1)
select count(1) from sys.dm_exec_cached_plans
--If this number is lower, then something's been removed!


Hash join spills to disk even though there is plenty of memory granted for the query We are running SQL Server 2014 Enterprise Sp2 with 1.5TB of memory and 64 cores. There is close to 17 GB memory granted to this single threaded query, max memory used is 2.2gb, even though the hash join has plenty of memory available it seems like it's not using it and spilling to disk. Any idea why? Thank you in advance.

Same question posted in SQLPerformance. The Query plan and images are posted there

I want to understand the Memory fractions. It shows the hash join has 52.48% of the memory grant (total 17GB+) which is close to 9GB. But the maximum memory used of the plan is a lot less which is 2.2+GB and Hash join is the only spill in this query. Is there something I am missing about the memory fractions, are they not accurate?

The 17GB memory grant is across the entire query. That 17GB isn't given in its entirety to a single operator. Each operator gets a fraction of the overall grant, and that amount is not dynamically reallocated during query execution.

If you go into the query's XML and search for MemoryFractions (or hover your mouse over operators in Plan Explorer), you'll see more information about the fractions assigned to each operator.

It shows the input side of the join gets 52.48%, but not the output side. If you have more memory required for the data coming out of the operator, you'll spill to disk.

To avoid the spill, tune indexes, tune the query, use the new min memory grant hint in 2016, etc.


How to define a one-to-many relationship without primary keys? I am new to relational databases, I am not sure if I am asking this question correctly so any feedback is appreciated. I have seen a few questions about storing one-to-many relationships, but none that I have found seem to have data in quite this format.

I am more familiar with a document based approach, so I'll present my example data here as JSON and would appreciated guidance on how this would be implemented in a relational database.

For example if I have data about school classes and want to store where a student sits, in Mongo DB I would do something like the following:

[{'_id': 'asdf',
  'name': 'English 101',
  'seats': {'john': '1a',
            'bill': '1b',
            'suzie': '1c',
            'julie': '2a'},
  '_id': 'qwer',
  'name': 'Biology 205',
  'seats': {'julie': '1a',
            'bill': '1b',
            'john': '1c',
            'suzie': '2a'}
 }
]

In a relational database I am less certain. I would imagine I would have a table where the primary key is equivalent to the '_id' field a column named 'name' and one named 'seats'? However I don't know what would go in the seats field (I thought about using a JSON string, thought there must be a better way).

Does this mean that I need a new table for each unique class that contains the seat information? Or is there another way to store this data in a relational database?

Here's the design that comes to mind:

4 tables: student, class, seat_class, class_student_seat.
Each table has an ID column which is an autoincrementing integer.
Typically the ID is the PK.
Some tables have compound primary keys (i.e., multiple columns make the PK).

This is approximately a third-normal form (3NF) design; from a relational database perspective, it is modular an efficient (minimizes repeating data):

students
  student_id, (PK)
  student_first_name,
  student_last_name

class
  class_id,
  class_name ('biology 111', etc.)

seat_class
  seat_id,  (PK)
  class_id, (PK)
  seat_name ('1a', '1b', etc.)

class_student_seat
  class_id,  (PK)
  student_id,(PK)
  seat_id    (PK)


Is it okay to use MySQL Workbench with MariaDB? Workbench 8 gives me a warning when connecting to MariaDB, but it appears to work fine. Since MariaDB is based on MySQL, would it be a problem to keep using it?

This warning is shown for any other but the supported MySQL versions (at the time being these are 5.6, 5.7 and 8.0). The application uses language features and other resources that may or may not work with other servers. It's actually not tested, which is the main reason why this warning comes up.

MariaDB might be based on MySQL, but over time it more and more diverts from its root and it's very likely that there is an increasing number of incompatibilities.


Single-column junction table - does it make sense? I have inherited a database schema, which looks similar to the one below:

CREATE TABLE Products (
    ProductID int not null PRIMARY KEY,
    StoreGroupID int not null,
    -- product properties...
)

CREATE TABLE Stores (
    StoreID int not null PRIMARY KEY,
    StoreGroupID int not null,
    -- store properties...
)

The idea is that there's a 1-* correspondence between a product and a group of stores (a product is always carried by a single group of stores, and a group of stores can carry multiple products).

However, the current database does not define any sort of "group of stores" entity - the StoreGroupID is instead assigned by the business logic code from a sequence, completely arbitrarily and with no foreign key constraint.

Does it make sense to create a StoreGroup table, even if the only column it could carry would be the StoreGroupID? Or is there another way to model such a relationship?

In my mind I would separate the logical and physical design. Logically there is a entity type called StoreGroup. Physically, this entity type is not implemented as a table. This is OK. Often the physical design does not include all aspect of the logical design due to time constraints, priorities, performance or other reasons. Foreign key constraints, and the corresponding run-time checks, is a typical example of this. You've discovered another.

There is a cost, too. As you say, foreign keys cannot be enforced in the DB. Perhaps your application has other mechanisms to ensure consistency? As is so often the case there is a trade-off and the designer must make a judgement call.


Why is ADD CONSTRAINT ... NOT VALID taking a long time? I added NOT NULL constraints to some columns like this, expecting the command to be very fast because of NOT VALID. However, it took about 60-100 seconds, and seemed to lock the table for the whole time (based on other things failing during that time). This is on a table with about 8,600,000 rows. Why did this happen? Would it have been better to do 4 commands instead of 1?

Postgres 12

ALTER TABLE my_table
  ADD CONSTRAINT my_table_column1_not_null CHECK (column1 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column2_not_null CHECK (column2 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column3_not_null CHECK (column3 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column4_not_null CHECK (column4 IS NOT NULL) NOT VALID;

The ALTER TABLE needs an "access exclusive" lock on the table.  While it will only hold that lock for microseconds once it does gets it, it still might block indefinitely while waiting to acquire it if any other session is already holding any lock on the table.  And once it does block then other sessions, even ones wanting a weaker lock, will block behind it.  (PostgreSQL does not allow queue jumping, so a strong lock which is waiting to get granted will block incoming weak locks, even if those incoming locks would be compatible with the lock already being held.)

If you want the ALTER TABLE to either succeed or fail nearly instantly, set lock_timeout to a low value.  Trying to run them as separate statements would make the situation worse, not better.


Multi level hierarchy relationship My table design is

users (id, username..., parent_id)

Data is

id    username      parent_id
-------------------------------
1  |  admin       | null
2  |  reseller 1  | 1
3  |  client 1    | 1
4  |  reseller 2  | 1
5  |  reseller 3  | 2
6  |  reseller 4  | 2
7  |  client 2    | 5
8  |  client 3    | 6

I want to get all descendants of id 1

I studied Adjacency List, Nested List & Closure table design, but came to conclusion that Session based Adjacency List can be better in my situation.

I found an example at https://stackoverflow.com/questions/8104187/hierarchical-queries-in-mysql

SELECT  @id :=
        (
        SELECT  senderid
        FROM    mytable
        WHERE   receiverid = @id
        ) AS person
FROM    (
        SELECT  @id := 5
        ) vars
STRAIGHT_JOIN
        mytable
WHERE   @id IS NOT NULL

I tried to modify this query to get my result like this

SELECT  group_concat(@id :=
        (
        SELECT  id
        FROM    users
        WHERE   parent_id = @id
        )) AS u
FROM    (
        SELECT  @id := 1
        ) vars
STRAIGHT_JOIN
        users
WHERE   @id IS NOT NULL

But it is not working. Here is the SQLFiddle

Need help in making the query work.

The query in the linked answer is a "hack" as the OP there clearly describes in a comment and can stop working any time with a MySQL upgrade as the exact behaviour of session variables is not documented.

Aside from that, the question there was about finding all ancestors, not all descendants. In adjacency lists, a node has maximum one parent but possibly many children. So this query/hack will not work in your case.

The Adjacency List model is the simplest of all hierarchy models in SQL. The other 3 (Nested Sets, Evaluated Path, Closure Table) are more complicated because - in a way - store redundant information. In exchange, some queries are far more easier to write. In any of the other 3 models, the solution would be really simple and wouldn't need a recursive query.

So, it seems you have two options:

either evaluate your decision to use the Adjacency List model and use a different one (or a combination of models)
write a procedure / function that solves the issue. @RolandoDBA has an old answer on this site, that provides several procedures that solve this (and related) problems. I'm not sure how efficient they are but they surely work:
Find highest level of a hierarchical field: with vs without CTEs.

You'll find more useful information in the links:

Trees and Other Hierarchies in MySQL, in Get It Done With MySQL 5&6, Chapter 20. Copyright © Peter Brawley and Arthur Fuller 2011.
EXPLAIN EXTENDED: Hierarchical queries in MySQL, by Quassnoi (yes, it's the same one that posted the query you tried.) In his blog, he has several hierarchical queries that can find ancestors, descendants, etc.
Models for hierarchical data, for MySQL, by Bill Karwin. A slideshare describing the pros and cons of various hierarchical models in MYSQL. Slightly skewed in favour or the Closure Table.


`Encrypt Connection` causes an invalid certificate error even though server-side encryption is forced I have a SQL Server set up to force encrypted connections from all client. It has a standard SSL certificate, using Let's Encrypt as the CA. Browsing to the server via a web browser verifies that the SSL certificate is valid and current.

However, if one checks Encrypt Connection setting in SSMS, one gets an error about invalid certificate.

TITLE: Connect to Server
------------------------------

Cannot connect to <redacted>

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476

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

The certificate chain was issued by an authority that is not trusted

------------------------------
BUTTONS:

OK
------------------------------

One can then remove the Encrypt Connection, connect anyway, which the connection is then successful. The dm_exec_connection will still show that connection as encrypted nonetheless.

As noted in the comments, Let's Encrypt was recently added to trusted list by Windows, so it might be the older OS not trusting Let's Encrypted certificate. However, that in itself does not explain why clients apparently are able to connect and get encrypted connections by default. If the client explicitly specify Encrypt Connection, then it becomes an issue. But why would it be? In both cases, the Trust Server Certificate is not enabled, so I would have expected the behavior to be the same.

Who's pulling who? What's really going on?

Bonus: If anyone can show a way to determine which certificate is in use for SSL connection that would be appreciated; the server has only one active SSL certificate but I have no way of verifying that it's the one that SQL Server is actually using to identify itself. For all I know, it might be waving around a expired certificate, which may explain why the error.

This did not come up in my original search but I think this related question has the answer, in particular:

  One difference is that if you use self-signed certificate and the client is the one request encryption (with “Encrypt connection” option checked), then it will attempt to perform server validation on the certificate to verify the identity of the server machine so that it will be failed for a self-signed certificate since it hasn’t been signed by a trust root authority. For more information, please refer to http://blogs.msdn.com/b/dataaccess/archive/2005/08/05/448401.aspx.

Note that this is at variance with what is implied in the official documentation, suggesting that the certificate always will be checked. Evidently that is not the case. Thus, a client connecting to a SQL Server instance that has Force Encryption will be encrypted but without checking the identity of the server's certificate, unless the client explicitly sets the encryption form client-side.


Exporting schema data (tables, permisions) into another schema on same database I want to take data from one schema schema1 and put it into schema2 this should be quite easy to do but i have searched through the oracle documentation and can't find a way to do it.

Use expdp and impdp, in combination with the remap_schema option.

For example...

Firstly, make sure the target user/schema is already created. It may also temporarily need the IMP_FULL_DATABASE role, but I haven't tested this.

Create a directory object to hold the dump files and logs (make sure this exists on your filesystem):

create directory DUMPDIR as '/path/to/your/dump/directory';
grant read, write on directory DUMPDIR to system;

Export the data, specifying the schema you wish to export ( FROMUSER in this case):

expdp fromuser/frompass@yourdb schemas=FROMUSER directory=DUMPDIR dumpfile=fromuser.dmp logfile=expdp.log

Import with impdp:

impdp touser/topass@yourdb directory=DUMPDIR dumpfile=fromuser.dmp logfile=impdp.log remap_schema=fromuser:touser

remap_schema=fromuser:touser does the magic!

You can also do this without creating an interim dumpfile by using the network_link= parameter. See the Oracle documentation for more information.


Copying all columns of a row from table into another? I would like to keep the history of a table using SQL queries without triggers. The simplest way to do that is to fire a SELECT and INSERT into the history table like below:

INSERT INTO history (field1, field2)
SELECT
  field1,
  field2
FROM
  current
WHERE
  field1 = condition

However is it possible to do this without hard-coding the columns i.e a select * equivalent?

You can - you need to drop the column aliases from the INSERT list as below

CREATE TABLE SourceTest (id int, characters nvarchar(100))

INSERT INTO SourceTest (id, characters)
VALUES
(1,'hello'),(2,'goodbye'),(3,'test')

CREATE TABLE TargetTest (id int, characters nvarchar(100));

INSERT INTO TargetTest
SELECT * FROM SourceTest

SELECT * FROM TargetTest

DBFiddle

NOTE: The columns of both tables must be in the same order, and if the schema of the source table is modified the schema of the target table must be amended also.

DISCLAIMER: Using SELECT * in production code is seen as an anti-pattern - changes to column order or table structure can cause errors. I would recommend NOT using this method.


Meltdown Patching can be turned on as needed Experts,

Need to know if anyone has witnessed or found such settings while patching SQL servers for Meltdown patches.

Our sys admins informed that OS patches for Meltdown can be turned ON/OFF bases on registry setting and can be enabled or disabled.

Just looking if someone know a similar way available for SQL server patches? Means you can turn it active when needed?

No, there is no publicly known way to do this for the SQL Server Meltdown/Spectre patches.

Microsoft might have a trace flag to do it, but if so, they haven't shared it publicly as of yet.


Opening an Oracle database with a corrupted redo log I have a database that is not open. And I have a redo log that is corrupted.

What would be the best way for me to open the database? This is a testing db and I am not concerned with data loss - but I need to get the database open and working again.

Some additional notes:
Trying to switch logs tells me that the log is required for recovery
Attempting to recover fails
Trying to open gives the error msg that the log is corrupted

Additional clarification:
I do need some of the data in the db; but I don't need any of the recent data (about a year worth of data). There is some configuration data which I do want.
I don't know the structure of the db, and I have no script to re-create it.

What you are looking for is to clear the redo log.

ALTER DATABASE CLEAR LOGFILE GROUP #;


SQL Query return a column per hour / day / week / month betwen 2 dates What I am trying to achieve (Table View)

 Part No   01/02/15  02/02/15  03/02/15  04/02/15  05/02/15  06/02/15
 PU1234        24       25        15         22       27        21
 PU5678        25       22        19         21       24        26

Table of Data

 A   B  C   D           E         Part Name     Date & Time
FX01 FN50   X82 OP.70   Packing   PU4238TZA     2015-02-27 03:43:55.000
FX01 FN50   X82 OP.70   Packing   PU4238TZA     2015-02-27 03:43:56.000
FX01 FN50   X82 OP.70   Packing   PU4238TZA     2015-02-27 03:43:57.000
FX01 FN50   X82 OP.70   Packing   PU4238TZA     2015-02-27 03:43:58.000

I want the code to be generic so I can manipulate it to achieve all of the below

Quantity per part number per hour (24 hour period) stored procedure 1
Quantity per part number per day (between two dates) stored procedure 2
Quantity per part number per week (between two dates) stored procedure 3
Quantity per part number per Month (between two dates) stored procedure 4

The code below works (to a fashion) but populates the table with all zero's

`;with cte as(
select
    [CAB Part No.], cast([Date & Time] as date) UtcDay, DATEPART(hour, [Date & Time]) UtcHour, count(*) as Counts
from  [dbo].[SFDLOG]
where [Date & Time] between '2015-02-01' and '2015-02-02'
group by
    cast([Date & Time] as date), DATEPART(hour,[Date & Time]),[CAB Part No.]
)
select dateadd(hour, utchour, cast(utcday as datetime)) as [SFD Hour],
Counts
into #TempDates
from cte

select *
from
(
select [CAB Part No.],[SFD Hour],
    convert(CHAR(10),[SFD Hour], 120) PivotDate
from #tempDates
left join [SFDLOG]
    on [SFD Hour] between '2015-02-01' and '2015-02-02'
 ) x
 pivot
 (
count([SFD Hour])
for PivotDate in ([2015-02-01 00:00:00.000], [[2015-02-01 01:00:00.000], 2015-02-01 02:00:00.000],
 ) p;
 drop table #tempdates`


TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the database to match the application without needing to know what login it eventually maps up to. This is because I want to hide the logins from live and test environments from them and have the DBAs create these logins in each environment as necessary.

If I go into the project and create a new user i.e. [MyApplication] the default script suggests the following template:

CREATE USER [MyApplication]
    WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = dbo
GO

Which is great, as I can create a user which is mapped up to no login, and then when it's deployed I can run:

ALTER USER [MyApplication] WITH LOGIN=[MyApplicationLogin], Name=[MyApplication]

which is fine as long as the user is to be mapped to a SQL login. If the login is to be mapped out to a windows login, SQL throws an error when we try to remap:

Msg 33016, Level 16, State 1, Line 1
The user cannot be remapped to a login. Remapping can only be done for users that were
mapped to Windows or SQL logins.

So the question is, what is the best way to set this up in the project so that the developers can create a user which can be named whatever they like within the database users and then how would we re-map this to a windows auth login?

thanks in advance!

The idea of CREATE USER ... WITHOUT LOGIN isn't so you can map it later with ALTER USER ... WITH LOGIN (SQL Server simply refuses, as youhave found).

Instead, the idea is for application developers to change the security context before issuing SQL statements, by first doing EXECUTE AS USER = '<user>' WITHOUT REVERT to shed permissions.

This kind of gets you halfway to where you want to be.  You ultimately still need to map some SQL Server logins to some SQL Database Users, but you reduce your need to know in advance what they will be, because you declare access-control permissions against the 'WITHOUT LOGIN' user.

See:

User without login purpose
SQL SERVER – Importance of User Without Login – T-SQL Demo Script


Restore database excluding FILESTREAM data Context
We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB.

Out of these, approximately 8.5 GB is in a single table BinaryContent. As the name suggests, this is a table where we store simple files, of any kind, directly in the table as a BLOB. Recently we've been testing the possibility to move all these files out of the database to the file system using FILESTREAM.

We did the necessary modifications to our database without any problems, and our system is still working fine after the migration. The BinaryContent table looks roughly like this:

CREATE TABLE [dbo].[BinaryContent](
    [BinaryContentID] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](50) NOT NULL,
    [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL
) ON [PRIMARY] FILESTREAM_ON [FileStreamContentFG]
ALTER TABLE [dbo].[BinaryContent] ADD [FileContentBinary] [varbinary](max) FILESTREAM  NULL
ALTER TABLE [dbo].[BinaryContent] ADD  CONSTRAINT [DFBinaryContentRowGUID]  DEFAULT (newsequentialid()) FOR [BinaryContentRowGUID]

With everything residing in the PRIMARY file group, except the field FileBinaryContent which is in a separate file group FileStreamContentFG.

Scenario
From a developer's point of view, we would often like a fresh copy of the database from our production environment, to be able to work the the latest data. In those cases, we are rarely interested in the files stored in BinaryContent (now using FILESTREAM).

We have this almost working as we'd like. We back up the database, without the file stream like this:

BACKUP DATABASE FileStreamDB
FILEGROUP = 'PRIMARY'
TO DISK = 'c:\backup\FileStreamDB_WithoutFS.bak' WITH INIT

And restore it like this:

RESTORE DATABASE FileStreamDB
FROM DISK = 'c:\backup\FileStreamDB_WithoutFS.bak'

This seems to be working OK, and our system works as long as we avoid the parts that use the FileBinaryContent field. We can for instance run the following query without a problem:

SELECT TOP 10 [BinaryContentID],[FileName],[BinaryContentRowGUID]
--,[FileContentBinary]
FROM [dbo].[BinaryContent]

Naturally, if I un-comment the line above, including FileContentBinary in the query, I get an error:

  Large object (LOB) data for table "dbo.BinaryContent" resides on an
  offline filegroup ("FileStreamContentFG") that cannot be accessed.

Our system handles files where the content is set to null, so what I would like to do is something like this:

UPDATE [dbo].[BinaryContent]
SET [FileContentBinary] = null

But this of course gives me the same error as above. At this point I'm stuck.

Question
Is there any way I can restore the database without having to also restore everything from the FileStreamContentFG file group? Either by updating the values to null as I'm trying above, or default to to null when the file is missing or something?

Or am I perhaps approaching the problem in the wrong way?

I'm a developer by nature and does not have much knowledge as a DBA, so do excuse me if I'm overlooking some trivial thing here.

What you're trying to do would leave the database in a (transactionally) inconsistent state, hence it isn't possible.

The Partial Database Availability whitepaper is a useful reference guide and includes an example of how to check whether a particular table or file is online. If your data access were through stored procedures, you could relatively easily incorporate that check.

One alternative (but somewhat hacky) approach that might be worth a look in your scenario would be to hide the table and replace it with a view.

-- NB: SQLCMD script
:ON ERROR EXIT
:setvar DatabaseName "TestRename"
:setvar FilePath "D:\MSSQL\I3\Data\"

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SET NOCOUNT ON;
GO

USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'$(DatabaseName)')
  DROP DATABASE $(DatabaseName)
GO

CREATE DATABASE $(DatabaseName)
ON PRIMARY
  (
  NAME = N' $(DatabaseName)'
  , FILENAME = N'$(FilePath)$(DatabaseName).mdf'
  , SIZE = 5MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  )
, FILEGROUP [FG1] DEFAULT
  (
  NAME = N' $(DatabaseName)_FG1_File1'
  , FILENAME = N'$(FilePath)$(DatabaseName)_FG1_File1.ndf'
  , SIZE = 1MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  )
, FILEGROUP [FG2] CONTAINS FILESTREAM
  (
  NAME = N'$(DatabaseName)_FG2'
  , FILENAME = N'$(FilePath)Filestream'
  )
LOG ON
  (
  NAME = N'$(DatabaseName)_log'
  , FILENAME = N'$(FilePath)$(DatabaseName)_log.ldf'
  , SIZE = 1MB
  , MAXSIZE = UNLIMITED
  , FILEGROWTH = 1MB
  )
GO

USE $(DatabaseName);
GO

CREATE TABLE [dbo].[BinaryContent](
    [BinaryContentID] [int] IDENTITY(1,1) NOT NULL
    , [FileName] [varchar](50) NOT NULL
    , [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL UNIQUE DEFAULT (NEWSEQUENTIALID()) NOT NULL
  , [FileContentBinary] VARBINARY(max) FILESTREAM  NULL
) ON [PRIMARY] FILESTREAM_ON [FG2]
GO

-- Insert test rows
INSERT
  dbo.BinaryContent
  (
  [FileName]
  , [FileContentBinary]
  )
VALUES
  (
  CAST(NEWID() AS VARCHAR(36))
  , CAST(REPLICATE(NEWID(), 100) AS VARBINARY)
  );
GO 100

USE master;
GO

-- Take FILESTREAM filegroup offline
ALTER DATABASE $(DatabaseName)
MODIFY FILE (NAME = '$(DatabaseName)_FG2', OFFLINE)
GO

USE $(DatabaseName);
GO

-- Rename table to make way for view
EXEC sp_rename 'dbo.BinaryContent', 'BinaryContentTable', 'OBJECT';
GO

-- Create view to return content from table but with NULL FileContentBinary
CREATE VIEW dbo.BinaryContent
AS

SELECT
  [BinaryContentID]
    , [FileName]
    , [BinaryContentRowGUID]
  , [FileContentBinary] = NULL
FROM
  [dbo].[BinaryContentTable];
GO

-- Check results as expected
SELECT TOP 10
  *
FROM
  dbo.BinaryContent;
GO


CLR Procedure with XML parameter returns Data at the root level is invalid. Line 1, position 1 I have following CLR stored procedure, which is throwing error on this command

xmlDoc.LoadXml(inputXml);

code

    public static int spGetTaxOfficeXML(SqlXml _inputXml)
    {
        // this procedure rename Row elements name with NodeName attribute value
        string inputXml = _inputXml.ToString();
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.LoadXml(inputXml);

// procedure Logic

        SqlContext.Pipe.Send(inputXml);
        return 0;
    }

it works well, when the XML text is inside variable inside visual studio as a text. But when i upload code to the SQL server as CLR, and try to execute it from SQL Management Studio:

DECLARE @XML XML
SET @XML = '<NodeA><NodeB></NodeB><NodeC AttributeX=""><Row NodeName="RowA" AttributeA="" AttributeB="abcd" AttributeC="efgh" /><Row NodeName="RowB" AttributeA="wxyz" /><Row NodeName="RowC" AttributeB="qwer" AttributeC="tyui" /><Row NodeName="RowD" AttributeA="stuv" AttributeB="erty" AttributeC="fghj" /></NodeC></NodeA>'

EXEC dbo.spGetTaxOfficeXML @XML

then this error is throwed:

Msg 6522, Level 16, State 1, Procedure spGetTaxOfficeXML, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "spGetTaxOfficeXML":
System.Xml.XmlException: Data at the root level is invalid. Line 1, position 1.
System.Xml.XmlException:
   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace()
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.LoadXml(String xml)
   at StoredProcedures.spGetTaxOfficeXML(String inputXml)

I tried to solve the error with following code, because i though the utf8 byte could cause the error, but it did not help.

// if byte mark exception happens
    string _byteOrderMarkUtf8 = Encoding.UTF8.GetString(Encoding.UTF8.GetPreamble());
    if (inputXml.StartsWith(_byteOrderMarkUtf8))
    {
        inputXml = inputXml.Remove(0, _byteOrderMarkUtf8.Length);
    }

What is wrong here ?

The problem is that you made a bad assumption about what value is returned by the SqlXml.ToString() method, and then in testing in Visual Studio, you injected a value but did not check to see what ToString() was actually returning (which is: "System.Data.SqlTypes.SqlXml"). Though you probably would have seen this had you placed the SqlContext.Pipe.Send(inputXml); line above the xmlDoc.LoadXml(inputXml); line ;-).

When working with the Sql* types, you want to almost always access them via the Value property, which they all have. The Value property does return the string version of the XML as you were wanting to get. However, this is less than ideal since you will be converted the XML to a string, only to convert that back to XML again when you call XmlDocument.LoadXml().

A better way is to use the SqlXml.CreateReader() method which returns an XmlReader, and that can be used with the XmlDocument.Load() method.

I reworked your test code (below) to be an example showing all 3 of these variations:

using System.Data.SqlTypes;
using System.Xml;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static int spGetTaxOfficeXML(SqlXml InputXml)
    {
        // this procedure rename Row elements name with NodeName attribute value
        XmlDocument _XmlDoc = new XmlDocument();
        _XmlDoc.Load(InputXml.CreateReader());

        // procedure Logic

        SqlContext.Pipe.Send("Incorrect:\n" + InputXml.ToString());

        SqlContext.Pipe.Send("\nBetter, but not an XmlDocument:\n" + InputXml.Value);

        SqlContext.Pipe.Send("\nCorrect:\n" + _XmlDoc.OuterXml);

        return 0;
    }
}

Running the code above with the original test SQL returns the following in the "Messages" tab:

Incorrect:
System.Data.SqlTypes.SqlXml

Better, but not an XmlDocument:
<NodeA><NodeB /><NodeC AttributeX=""><Row NodeName="RowA" AttributeA="" AttributeB="abcd" AttributeC="efgh" /><Row NodeName="RowB" AttributeA="wxyz" /><Row NodeName="RowC" AttributeB="qwer" AttributeC="tyui" /><Row NodeName="RowD" AttributeA="stuv" AttributeB="erty" AttributeC="fghj" /></NodeC></NodeA>

Correct:
<NodeA><NodeB /><NodeC AttributeX=""><Row NodeName="RowA" AttributeA="" AttributeB="abcd" AttributeC="efgh" /><Row NodeName="RowB" AttributeA="wxyz" /><Row NodeName="RowC" AttributeB="qwer" AttributeC="tyui" /><Row NodeName="RowD" AttributeA="stuv" AttributeB="erty" AttributeC="fghj" /></NodeC></NodeA>

P.S.  You don't need to worry about UTF-8, especially for NCHAR / NVARCHAR / XML data generated from within SQL Server, since SQL Server is UTF-16 LE.


Re-import packages in Oracle I'm aware of options to re-import data to tables using table_exists_action and can give options like skip,append or replace for the data.

But is there an option to re-import packages (re-create) into the database if they are already existing without throwing up an error like

" Package PKG_1 already exists"

I basically have packages which have some changes in them and re-import to another environment where there packages are already existing.

According to https://community.oracle.com/thread/959980 and https://community.oracle.com/thread/838380 the only way to do this is to use the SQLFILE parameter to impdp to create a script. Then replace CREATE PACKAGE with CREATE OR REPLACE PACKAGE and then execute the script.


Oracle query performance when filtering results I am currently working on a query for a front end application, which is experiencing weird performance issues when filters are applied on large sets of data. Here is a link to an SQLFiddle of the simplified query.

The query is executing in about 1-2 seconds when fetching all rows. (limited only by rownum) A similar performance can be observed when filtering by amounts above a certain threshold (e.g. total > 1000). However, when filtering below a threshold (e.g. available < 1000) the query is taking forever to calculate the results.

As a general note all related table columns are indexed.

Is there perhaps a better way to perform the calculations shown in the example?

SELECT ident_account,ident_instrument,
        nvl(available, 0) + nvl(reserved, 0) + nvl(restrctd,0) AS total,
        nvl(available, 0) AS available,nvl(reserved, 0) AS reserved,nvl(restrctd, 0) AS restrctd,
        validfrom, minvaliduntil as validuntil
   FROM (
         SELECT h.ident_account, h.ident_instrument, h.validfrom, h.validuntil,
                rank() over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc) as rnk,
                sum(case when IDENT_BALANCEGROUP = 1
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as available,
                sum(case when IDENT_BALANCEGROUP = 2
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as reserved,
                sum(case when IDENT_BALANCEGROUP = 3
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as restrctd,
                min(validuntil) over(partition by h.ident_account, h.ident_instrument order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as minvaliduntil,
                h.amount,b.ident_balancegroup
           FROM holdings h INNER JOIN balancetype b ON h.ident_balancetype = b.ident_balancetype
        )
  WHERE rnk = 1;


SQL Queries Slow in the Upgraded environment I setup and configured an new SQL database server with SQL Server 2016 Standard Edition.
This is going to replace our existing SQL database server which is 2008 R2 database server.
I restored recent backup form the current database server(SQL Server 2008 R2) and pointed the application to the new database server.
But when i run queries from the application,the queries are very slow.Even those queries which used to run fast current database server is very slow in the new server.
New database server hardware specs are much better then the current database server.

This is the plan for one of the query which is taking almost 3 minutes to execute.

This plan is from current server(SQL Server 2008 R2) and it takes 10 seconds only for the same query.

I have been trying out what is causing this slow performance.
Steps i tried.

I changed the compatibility of the database from 2008(100) to 2016(130) after restore.
Tried to find changes in the execution plan.
Saw Implicit conversion warning in the execution plan,but the column mentioned in that message is not found in the query.

FYI,the sql queries are generating from the application using linq and not stored as a stored procedure.

See the attached screenshot when i run EXEC sp_BlitzFirst @SinceStartup = 1;

Rebuilt Indexes and Updated statistics,but not much help as of now.
I have noticed a SORT operation with cost 95% ,but i already have an index for that SORT filed,so i don't think that SORT is an issue.Correct me if i am wrong.

I have noticed that in the actual execution plan,for most of my operation Estimated Number of execution and Actual number of execution is having big difference.What i should make of that?

Queries in need of query and index tuning are often those that are most vulnerable to performance regression after a SQL Server upgrade. That may be the best long term solution. It won't hurt to update stats to make sure the optimizer has the best information for informed choices.

If you identify issues where the legacy cardinality estimator performs significantly better after tuning, I suggest the most granular approach to address performance regressions. With a limited number of specific queries, use the FORCE_LEGACY_CARDINALITY_ESTIMATION hint. For a large number of queries within specific database(s), use ALTER DATABASE SCOPED CONFIGURATION. In the case where you experience CE problems with most or all databases, turn on trace flag 9481 at the instance level.

You might first try the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint or  QUERY_OPTIMIZER_HOTFIXES database scoped configuration so that patches made the CE available in the SP/CUs you've installed are used. These are not used by default due to an overabundance of caution.


Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment.

I have a web based form with multiple fields including a field that is hidden on the form that writes the CURRENT_TIMESTAMP to the database when the form is submitted.

The challenge I'm facing is that I want to create reports from these forms (sorting by month, week, or year) and order them by the timestamp but based on what I've seen here, sorting is not possible.

Is there an alternative I can use to achieve the results I want?

I have no idea why you would want to ORDER BY CURRENT_TIMESTAMP. If the table has a column where you stored CURRENT_TIMESTAMP when the row was created, you just need to say:

ORDER BY that_column_name;

Or if you want newest first:

ORDER BY that_column_name DESC;

... in other words, forget how the column was populated.

Now, if the comments are accurate, and you're actually not concerned so much about ordering but rather about filtering, then perhaps what you want is:

-- create a DATE variable based on today:

DECLARE @s DATE = CURRENT_TIMESTAMP;

-- convert it to the first of the month
-- (you can do this in the step above,
-- just separating it for clarity):

SET @s = DATEADD(DAY, 1-DAY(@s), @s);

-- use >= and < in a WHERE clause, and order by that column
-- (however this is just a coincidence, order by and filtering
-- are completely unrelated in this case:

SELECT [?] FROM dbo.[?]
  WHERE [datetime_column] >= @s
  AND [datetime_column] < DATEADD(MONTH, 1, @s)
  ORDER BY [datetime_column];

Here is why you don't want to use BETWEEN, in case anyone sends you that way:

https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

Here is why you want to use built-in date functions and a native DATE type and not convert to a string to trim time from a datetime variable:

http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime
http://www.sqlperformance.com/2012/10/t-sql-queries/trim-time

And here is some general information about bad practices in date/range queries:

https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries
https://www.sentryone.com/blog/aaronbertrand/backtobasics-dating-responsibly
https://www.mssqltips.com/sqlservertip/5206/sql-server-datetime-best-practices/


Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date? If I regularly backup my whole server where a SQL instance and database lives, and restore it to a different machine, can I use the database backups that have occurred (on the original server) after that full server snapshot was generated, to bring the newly restored server up to date?

Which kinds of database backups would I need available to be able to do so?
I'm guessing if I have a full backup of the database that is current, that would be sufficient, but if my last full backup was as old (or even older) than the server snapshot, could I use that full backup + any differential, transaction log, etc backups that came after that full backup to bring my server snapshot on the newly restored server back up to date?

Example (for simplicity let's assume each of these backups are completed instantly):

Server 1 has a full server point in time snapshot taken at 5:30 PM every day.

Database A on Server 1 undergoes nightly full backups at 11:30 PM every day.

Database A also has routine differential backups taken every hour throughout the entire day.

Database A also has transaction log backups taken every 15 minutes throughout the entire day.

Scenario 1:

Server 1 crashes at 11:50 PM.

Server 2 is restored from Server 1's full server point in time snapshot backup from 5:30 PM earlier that day.

Can we use the full backup (from Server 1) of Database A from 11:30 PM (20 minutes before the crash) to restore the database to as it was at 11:30 PM and then use the 11:45 PM transaction log backup to bring the database to as recent as possible (on Server 2)?

Scenario 2:

Server 1 crashes at 11:20 PM.

Server 2 is restored from Server 1's full server point in time snapshot backup from 5:30 PM earlier that day.

Can we use the full backup of Database A from the night before at 11:30 PM (since we didn't get to take one today yet before the crash) and then all the hourly differential backups between 11:30 PM the night before until 11 PM the night of and then the 11:15 transaction log backup to bring the database to as recent as possible (on Server 2)?

Is there a more efficient way of restoring in either scenario above and/or a better way processes on types of and frequency of database backups?

can I use the database backups that have occurred (on the original server) after that full server snapshot was generated, to bring the newly restored server up to date?

No.  You cannot manually switch an ONLINE database to RESTORING and apply log backups, and you cannot attach a database and leave it in the RESTORING state.

This would be a useful feature, and it's been requested before.  See eg here.

  Which kinds of database backups would I need available to be able to do so?

Any full backup, optionally a differential backup, and then the log backups from that point forward.  Note that it doesn't have to be the latest full backup, as long as you have all the log backups going back to that full.  Full and  differential backups never break the log chain.


Is it possible to migrate Sharepoint 2016 and all it's databases from SQL SERVER2012 to AZURE? we are in a process to migrate our website to azure.

we have those tons of sharepoint databases :

Is it possible to migrate all user databases + sharepoint to azure?

I could migrate some user databases to azure with no problem in the past ( bkp as data tier, and etc and etc ) but I've never done this with sharepoint databases.

I could found some questions saying it's not possible.

https://sharepoint.stackexchange.com/questions/241759/sharepoint-2013-on-azure-with-on-premise-sql-server
It says "ou cannot move to a Azure SQL Database".

https://sharepoint.stackexchange.com/questions/217477/sharepoint-migration-of-db-from-one-server-to-another-server
This one is from on premises to on premises.

https://docs.microsoft.com/pt-br/archive/blogs/sambetts/hosting-sharepoint-content-databases-in-sql-azure
"Important edit: this is currently not officially supported for SharePoint Server, even for Azure-hosted farms."

and then with all those questions and answers, I dont know how to proceeed.

Is it possible? Yes.

Should you? Documentation says...

  Important edit: this is currently not officially supported for
  SharePoint Server, even for Azure-hosted farms. This is likely due to
  the 1-millisecond SharePoint -> SQL supportability requirement which
  Azure SQL Database doesn't & won't guarantee. At the time of writing
  there's no plans to do so either, so please note that despite the fact
  this configuration by all accounts does work, this article is purely
  academic.

No.


Can sub-selects change in one single query in a read committed transaction? I am querying the same table multiple times in a single query in a transaction with isolation level set to READ COMMITTED (which is the default in PostgreSQL). Examples are:

BEGIN;

WITH
first AS (SELECT * FROM table),
second AS (SELECT * FROM table)
SELECT ...;

END;

or

BEGIN;

SELECT * FROM table
UNION ALL
SELECT * FROM table;

END;

Is it possible that the first sub-SELECT yields other results than the second sub-SELECT? I understand that SELECT queries might yield different results when executed separately (due to the characteristics of a READ COMMITTED transaction), but I am wondering what might happen if they are embedded in the same query.

I am using PostgreSQL 9.5.

No, that's not possible.

A single query (and even a query with many sub-queries is considered a single query) sees a consistent state of the database as it was when that query started.

It's not even necessary to use explicit transaction control as you did.


Correct data model for users with clients I have an application where users are granted access. Part of the system allows users to have their own 'clients'.

I have a users table

********************************
 users
********************************
 PK user_id    int
    username   string
    password   string(salted)
********************************

and clients table

********************************
 clients
********************************
 PK client_id  int
 FK user_id    int
    username   string
    password   string(salted)
********************************

What i want is that each user should be able to have a client with the same name. The case could be that two individuals choose to go with a different user, but by chance choose the same username. Therefore, there can be multiple entries for a username in the clients table, but against different user_ids.

The problem I have is that although the username field is not unique in the clients table, it should be unique for each user_id i.e. a user cannot have two or more clients with the same username.

How would I model this?

You can add a unique index across the user_id and username in the Clients table this will ensure that the username can only occur once per user_id. Though you should be aware that if the user_id column is nullable it will also apply to clients that do not have a user_id assigned to them.

EDIT

Using a composite primary key that consists of the client_id, user_id and the username will not meet your requirements. This is because the addition of the client_id makes the grouping unique. Take the following rows as an example:

client_id user_id username
1         1       John Doe
2         1       John Doe

If you had a composite primary key of all 3 columns the above would be allowed - however, if you just added a unique index across the last 2 columns the above would be invalid - which is your intended result.

You could add a composite primary key across the last two columns but this is not a good design as it adds additional complexity when attempting to reference the client. A better design would be to keep the client_id as the primary and add a unique index across the last 2 columns.


MySQL SUM with multiple GROUP BY on same table What is the best way to produce several sum results based on different group by conditions on the same table in MySQL?

I believe I'm doing a bad select, but I can't figure out a better way.

Imagine a have a orders table where I track the client that made the order, it's value and some qualifiers for the order (the types). I want to sum the total orders for a given client grouped by a combination of the order types:

delimiter $$

CREATE TABLE `orders` (
  `idorder` int(11) NOT NULL,
  `value` double DEFAULT NULL,
  `idclient` int(11) DEFAULT NULL,
  `type1` int(11) DEFAULT NULL,
  `type2` int(11) DEFAULT NULL,
  `type3` bit(1) DEFAULT NULL,
  PRIMARY KEY (`idorder`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

My select:

SELECT T0.idclient, T1.condition1, T2.condition2, T3.condition3 FROM
(SELECT 1 AS idclient) AS T0
LEFT JOIN
(SELECT idclient, SUM(value) condition1 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 1 AND type3 = 0) AS T1
ON T0.idclient = T1.idclient
LEFT JOIN
(SELECT idclient, SUM(value) condition2 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 1 AND type3 = 1) AS T2
ON T0.idclient = T2.idclient
LEFT JOIN
(SELECT idclient, SUM(value) condition3 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 0 AND type3 = 0) AS T3
ON T0.idclient = T3.idclient;

Am I missing something?

Output:

+----------+------------+------------+------------+
| idclient | condition1 | condition2 | condition3 |
+----------+------------+------------+------------+
|        1 |         10 |         20 |         30 |
+----------+------------+------------+------------+
1 row in set (0.00 sec)

Data:

INSERT INTO `orders` VALUES (1,5,1,1,1,'\0'),(2,5,1,1,1,'\0'),(3,20,1,1,1,''),(4,5,1,1,0,'\0'),(5,25,1,1,0,'\0');

I'm more of a SQL Server guy, but here is a stab to get you motivated. You could use the CASE expression to help SUM() orders by certain order types.

For example:

SELECT
    idclient AS [Client ID]
   ,COUNT(value) AS [Total Orders]
   ,SUM(value) AS [Total Order Value]
   ,SUM( CASE WHEN type1 = 'some type' THEN value ELSE 0 END) AS [Order Combo 1]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' THEN value ELSE 0 END) [Order Combo 2]
   ,SUM( CASE WHEN type1 = 'some type' AND type2 = 'another type' AND type3 = 'another type' THEN value ELSE 0 END) [Order Combo 3]
FROM 'test'.'orders'
GROUP BY idclient

This will group the orders on [idclient] and will SUM([value]) the instances where certain order records meet the following conditions or combinations you specified.

So, if a customer has an order where [type1] was Apples and [type2] was Oranges, you can SUM([value]) that was 1 under a new column called something like [Apple/Orange Orders].

The result can also include additional combinations for the same record in multiple fields/columns. If that is a problem, just move the CASE to the GROUP BY to break each combination into a separate record in the the result set.

Hope this helps!


A database trigger is an alternative for? I had the following quiz question and could not answer it myself because of a little confusion.

Please answer this and tell me why?

  A database trigger is an alternative for?

  A. Stored procedure
  B. Primary key for implementing referential integrity
  C. Foreign key for implementing referential integrity
  D. All of the above

I don't expect the writer of the question meant it this way, but words mean things and the question specifies a "database trigger", therefore there is no correct answer.

  A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event. (PL/SQL Language Reference - DATABASE Triggers)

Unlike Row and Statement triggers, Database triggers fire for things like
AFTER STARTUP ON DATABASE, AFTER SERVERERROR ON DATABASE and AFTER LOGON ON DATABASE.


Design of an application log database We are generating logs like following example(this is a table no actual pipes):

2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms
2014-06-10 09:00:08.245 | Channel2 | Operation5 | Function10 | 22ms
2014-06-10 09:00:22.005 | Channel1 | Operation3 | Function15 | 48ms

Think about this with at least 25-30 columns of time-series application log data. Every row is a transaction. So I aggregate the same ones and get the sum(also average of the durations). After the aggregation, my unique clustured key is all of the columns except the metrics like transaction duration or sum.

In example between 2014-06-10 09:00:00 - 2014-06-10 09:01:00 our example will be:

2014-06-10 09:00:00 | Channel1 | Operation3 | Function15 | 2 | 31,5ms
2014-06-10 09:00:00 | Channel2 | Operation5 | Function10 | 1 | 22ms

Is there a better way to do this? Processing this data also costs me a lot while showing it to users for monitoring and analyzing purposes.

UPDATE-1

I think this question need more clarification. The raw log data is like in the first example. I'm running an ETL agent that gets it as per minute interval and aggregates to an another table like in the second example.

The second table has a primary key of all columns except metric columns(Count,ResponseTime). Because in the end 2014-06-10 09:00:00 | Channel1 | Operation3 | Function15 is the only thing that gives me uniqueness.

When the user wants to analyze it. He/She chooses values from every column which I named "Dimensions". He/She wants to see "Function15 transactions which are on Channel1" or "Operation5 response times on Channel1" and so on. I'm storing the data like this to achieve these requests from the user.

Regards

I did the following

CREATE TABLE L(
Time_Series_TS TIMESTAMP,
Channel VARCHAR(10),
Operation VARCHAR(10),
Function VARCHAR(10),
Duration INT);

Then

INSERT INTO L VALUES('2014-06-10 09:00:03.457', 'Channel1', 'Operation3', 'Function15', 15);
INSERT INTO L VALUES('2014-06-10 09:00:08.245', 'Channel2', 'Operation5', 'Function10', 22);
INSERT INTO L VALUES('2014-06-10 09:00:22.005', 'Channel1', 'Operation3', 'Function15', 48);
INSERT INTO L VALUES('2014-06-10 09:01:03.457', 'Channel2', 'Operation3', 'Function15', 296);
INSERT INTO L VALUES('2014-06-10 09:01:08.245', 'Channel2', 'Operation5', 'Function10', 225);
INSERT INTO L VALUES('2014-06-10 09:01:22.005', 'Channel1', 'Operation3', 'Function15', 7);
INSERT INTO L VALUES('2014-06-10 09:01:16.245', 'Channel2', 'Operation5', 'Function10', 10);
INSERT INTO L VALUES('2014-06-10 09:01:47.005', 'Channel1', 'Operation3', 'Function15', 20);

I added a few records to your sample for checking. Then ran this query

SELECT MINUTE(Time_Series_TS) AS Minute, Channel, Operation, Function,
COUNT(*) AS "Count/min", SUM(Duration) AS Duration
FROM L
GROUP BY Minute, Channel, Operation, Function
ORDER By Minute, Channel, Operation, Function;

Which gave

+--------+----------+------------+------------+-----------+----------+
| Minute | Channel  | Operation  | Function   | Count/min | Duration |
+--------+----------+------------+------------+-----------+----------+
|      0 | Channel1 | Operation3 | Function15 |         2 |       63 |
|      0 | Channel2 | Operation5 | Function10 |         1 |       22 |
|      1 | Channel1 | Operation3 | Function15 |         2 |       27 |
|      1 | Channel2 | Operation3 | Function15 |         1 |      296 |
|      1 | Channel2 | Operation5 | Function10 |         2 |      235 |
+--------+----------+------------+------------+-----------+----------+

Which appears to be the result you want (note 63 as the 1st duration as per my earlier comment). Is this the result you wanted? You can then use HOUR() and DAYOFMONTH() and even YEAR() to aggregate over these also with this query.

For performance, I did create an index

CREATE INDEX L_Index ON L(Channel, Operation, Function) using BTREE;

and explained the query before and after creating it, but there was no difference. This is hardly a surprise, since the optimizer probably said that there's no point in using one for such a small table. Obviously, I can't test
with your data, but there are a couple of points. If you are performing this operation over a large number of records with a large no. of fields, you may run into issues and if you create many indexes, your insert performance will decrease. Is it possible for you to categorise your data in some way to reduce the number of fields - i.e. split your big table into ones with a smaller number of fields? Check out different scenarios, test and see what happens with your data, your queries, your application and your hardware.

[EDIT]

For something more human readable, you might like to try something like

SELECT TIME(FROM_UNIXTIME(UNIX_TIMESTAMP(Time_Series_TS) - MOD(UNIX_TIMESTAMP(Time_Series_TS), 60))) AS Minute,
..
..

for your first field.

[EDIT - Response to UPDATE-1]

OK - so in my schema, you are indexing by (Minute, Channel, Operation, Function)?
See here for the docco on composite indexes in MySQL. If your queries have a predominatly left-right orientation, i.e you [always | usually] query Channel first and then Operation, then Function, you could try an index on Minute + (the usual three). If it's fairly arbitrary, then you could try using 6 indexes, but this will hit insert performance. How much, I can't say, but if this is a DW type app which performs the analysis, you can batch the inserts and only occasionally take the hit for that. You'll have to do a few tests with realistic data and EXPLAIN your queries - with realistic sample data, as I said earlier, the Optimiser with just a few records ignores indexes because the table is too small. Interestingly, on the MySQL man page given above, there's a hashing strategy which looks interesting - take MD5 hashes of CONCAT(Your_Column_List_Here). One other thing that I can suggest is that instead of using the

SELECT TIME(FROM_UNIXTIME(UNIX_TIMESTAMP(Time_Series_TS) - MOD(UNIX_TIMESTAMP(Time_Series_TS), 60))) AS Minute,...

Just remove the TIME() function and then you'll be storing INTs which appears to be better than indexes on DATETIMES - see here for a benchmark. Also as previously mentioned, you should remove your data from Production and perform the  OLAP/DW on another machine. You could also test out the InfiniDB solution that I suggested. It's drop-in compatible with MySQL (no learning curve). Then there are all the NoSQL solutions - we could be here all day :-). Take a look at a few scenarios, evaluate and test and then choose what best fits your budget and requirements. Forgot: Make your OLAP/DW system read only for performing queries - no transactional overhead! Make the OLAP/DW tables MyISAM? This last one is controversial - again, test and see.


Slow performance on averaging query I am fairly new to DBMS and backend management, and was hoping to get some advice on the following issue.

We have a Postgres database with the following tables: members and activity_scores. The activity_scores belong to each member, and are written to every 10 minutes. Here is an example of one of the rows of the activity_scores table (indexed on member_id):

 id | member_id | score |         created_at         |         updated_at
----+-----------+-------+----------------------------+----------------------------
  1 |         8 |    73 | 2016-11-04 05:32:55.564235 | 2016-11-04 05:32:55.564235
  2 |        10 |    20 | 2016-11-04 05:22:55.564235 | 2016-11-04 05:22:55.564235

Size of activity_scores data per member could be 10,000's to millions.

The query we are trying to run essentially takes scores for a member, and runs the average grouped by the date:

SELECT TO_CHAR((activity_scores.created_at AT TIME ZONE ? AT TIME ZONE members.zone),
         ?) AS date,
         AVG(score) AS average_score
FROM "activity_scores"
INNER JOIN "members"
    ON "members"."id" = "activity_scores"."member_id"
WHERE "activity_scores"."member_id" = $1
GROUP BY  date

An example result:

    date    |     average_score
------------+------------------------
 2016/10/15 | 52.00000000000000000000
 2016/10/29 | 60.25000000000000000000
 2016/09/05 | 70.05000000000000000000

These values are then used for graphing.

Unfortunately, the above query takes a long time (sometimes up to a few minutes), and causes the entire server (hosted on Heroku) to timeout.

There isn't really a need for us to have the data saved in 10 minute increments for months. Therefore, I've thought about making a separate table to just store the daily averages which we can poll directly from for graphing purposes. However this is somewhat of a duplicated data (which allegedly is a no-no in the DBM sense).

Would you guys have any suggestions on how to handle something like this?

Thank you!

If you are only inserting into the table and old rows are not deleted or updated and you don't insert rows for old dates, I don't see any harm at all from materializing the query results in another table. Every day, you could be aggregating the previous day's results for each member. Index properly the new table and efficiency should be good.

These are not duplicated data, they are summary results. Since efficiency of calculating them on the spot is not good enough, you are justified to pre-calculate and store
         them in a summary table.

Only if the members.zone changes, you would have to recalculate the results for that member.


Parent -> Child -> Grandchild with a foreign key back to Parent, Bad Practice? Basically the foreign key on options is there to make it so that if you want to know what product an option belongs to you don't have to go through variants.

A variant is like Color, Size Etc.. An option is like Medium, Large, Red Blue.

Is this a bad design choice?
Is it sometimes okay to do this?

The more I think about it, it seems like it's a bad idea just for the sheer fact that blue, when related to the product has no meaning without also being tied to the variant. This option loses any true meaning without the variant so the shortcut is never really worth taking?

You'll have your reasons for going this route, where it seems you're duplicating information (since id_Products is implied from id_Variants).

However, unless what you want to achieve is having Options with an optional product OR variant (i.e. a CHECK constraint ensuring one and only one of these IDs is not null), you must also ensure that you don't get into an invalid id_Variants + id_Products combination in Options in order to ensure you don't break referential integrity. For this, you need a UNIQUE key on Variants(Id, id_Products) and a FOREIGN KEY on Options(id_Variants, id_Products) REFERENCES Variants(Id, id_Products).