You are not showing the query you are using to obtain the results without diff. I'm assuming it is something like this:

SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),  -- or, if Value is an int, like this, perhaps:
                      -- AVG(CAST(Value AS decimal(10,2))
  Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
  DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
;

Also, you are not explaining what first and last mean. In this answer, it is assumed that first stands for earliest in the group (according to the Date value) and, similarly, last means latest in the group.

One way to throw in diff could be like this:

First, add two more aggregated columns, minDate and maxDate, to the original query:

SELECT
  min     = MIN(Value),
  max     = MAX(Value),
  avg     = AVG(Value),
  minDate = MIN(Date),
  maxDate = MAX(Date),
  Date    = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
  DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
;

Next, join the aggregated result set back to the original table on minDate and on maxDate (separately) to access the corresponding Values:

SELECT
  g.min,
  g.max,
  g.avg,
  diff = last.Value - first.Value,
  g.Date
FROM (
  SELECT
    min     = MIN(Value),
    max     = MAX(Value),
    avg     = AVG(Value),
    minDate = MIN(Date),
    maxDate = MAX(Date),
    Date    = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
  GROUP BY
    DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
) g
INNER JOIN atable first ON first.Date = g.minDate
INNER JOIN atable last  ON last .Date = g.maxDate
;

Note that the above assumes the Date values (at least those that happen to be first or last in their corresponding hours) to be unique, or you would get more than one row for some of the hours in the output.

An alternative, if you are on SQL Server 2005 or later version, could be to use window aggregate functions MIN() OVER (...) and MAX() OVER (...) to calculate Values corresponding to either minDate or maxDate, before aggregating all the results similarly to how you are probably doing it now. Here's what specifically I'm talking about:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
)
, firstlast AS (
  SELECT
    Value,
    Date,
    GroupDate,
    FirstValue = CASE Date WHEN MIN(Date) OVER (PARTITION BY GroupDate) THEN Value END,
    LastValue  = CASE Date WHEN MAX(Date) OVER (PARTITION BY GroupDate) THEN Value END
  FROM partitioned
)
SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),  -- or, again, if Value is an int, cast it as a decimal or float
  diff = MAX(LastValue) - MIN(FirstValue),
  Date = GroupDate
FROM firstlast
GROUP BY
  GroupDate
;

As you can see, the first common table expression (CTE) merely returns all rows and adds a calculated column GroupDate, the one subsequently used for grouping/partitioning. So it essentially just assigns a name to the grouping expression, and that is done for better readability/maintainability of the entire query, as the column is later referenced more than once. This is what the first CTE produces:

+-------+------------------+------------------+
| Value |       Date       |    GroupDate     |
+-------+------------------+------------------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |
+-------+------------------+------------------+

The second CTE adds two more columns to the above result. It uses window aggregate functions MIN() OVER ... and MAX() OVER ... to match against Date, and where the match takes place, the corresponding Value is returned in a separate column, either FirstValue or LastValue:

+-------+------------------+------------------+------------+-----------+
| Value |       Date       |    GroupDate     | FirstValue | LastValue |
+-------+------------------+------------------+------------+-----------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |         10 |      NULL |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |       NULL |        15 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |         17 |      NULL |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |       NULL |      NULL |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |       NULL |      NULL |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        30 |
+-------+------------------+------------------+------------+-----------+

At this point, everything is ready for the final aggregation. The min, max, and avg columns will be aggregated same as previously, and diff can now easily be obtained as the aggregated FirstValue subtracted from the aggregated LastValue. As you can see from the above result set, you can use various functions to get FirstValue and LastValue for the group: it could be MIN, MAX, SUM, AVG – any if these would do, because there's just one value in every group.

The main SELECT, however, as you can see, applies specifically MAX() over LastValue and MIN() over FirstValue. That is intentional. It's because this second suggestion doesn't really require Date to be unique, like the first one did, but, in case either minDate or maxDate does happen to have more than one associated Value, it would result in FirstValue or LastValue containing more than one value per group, something like this:

+-------+------------------+------------------+------------+-----------+
| Value |       Date       |    GroupDate     | FirstValue | LastValue |
+-------+------------------+------------------+------------+-----------+
|     9 | 10/10/2010 10:00 | 10/10/2010 10:00 |          9 |      NULL |
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |         10 |      NULL |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |       NULL |      NULL |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |       NULL |        15 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |         17 |      NULL |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |       NULL |      NULL |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |       NULL |      NULL |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        30 |
|    33 | 10/10/2010 11:45 | 10/10/2010 11:00 |       NULL |        33 |
+-------+------------------+------------------+------------+-----------+

I assumed that in this situation it would be more natural to take the difference between the greatest last value and the least first one. You, however, should know better what rule to apply here, so you'll just change the query accordingly.

You can test both solutions at SQL Fiddle:

#1
#2

Update

Starting from SQL Server 2012, you can also use the FIRST_VALUE and LAST_VALUE functions and substitute them for the CASE expressions in the firstlast CTE in my last query above, like this:

FirstValue = FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
LastValue  = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

In this case it will not matter whether you use MIN or MAX over FirstValue and LastValue later (in the main SELECT): each column will have exactly the same value (first or last Value accordingly) across all rows of the same GroupDate group, and so MIN() and MAX() would return identical results in each case.

In fact, you can get diff directly in the firstlast CTE and then, in the main query, just either aggregate it using MIN/MAX or add it to GROUP BY and reference it without aggregation, like this:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM atable
)
, firstlast AS (
  SELECT
    Value,
    Date,
    GroupDate,
    diff = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
         - FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  FROM partitioned
)
SELECT
  min  = MIN(Value),
  max  = MAX(Value),
  avg  = AVG(Value),
  diff,
  Date = GroupDate
FROM firstlast
GROUP BY
  GroupDate,
  diff
;

Moving one step further, you could obtain min, max and avg in firstlast as well, instead of the main query – using the counterpart window functions:

min  = MIN(Value) OVER (PARTITION BY GroupDate),
max  = MAX(Value) OVER (PARTITION BY GroupDate),
avg  = AVG(Value) OVER (PARTITION BY GroupDate),

With these three additional columns and the previous change, the firstlast CTE will be returning a row set like this for your example:

+-------+------------------+------------------+-----+-----+-------+------+
| Value |       Date       |    GroupDate     | min | max |  avg  | diff |
+-------+------------------+------------------+-----+-----+-------+------+
|    10 | 10/10/2010 10:00 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    11 | 10/10/2010 10:15 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    15 | 10/10/2010 10:30 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    15 | 10/10/2010 10:45 | 10/10/2010 10:00 |  10 |  15 | 12.75 |    5 |
|    17 | 10/10/2010 11:00 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    18 | 10/10/2010 11:15 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    22 | 10/10/2010 11:30 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
|    30 | 10/10/2010 11:45 | 10/10/2010 11:00 |  17 |  30 | 21.75 |   13 |
+-------+------------------+------------------+-----+-----+-------+------+

Note how GroupDate, min, max, avg and diff – the columns that you really need for the final set – are simply repeated across all rows that belong to the same group. That means that you can get rid of Value and Date, rename GroupDate to Date, slightly rearrange the columns, apply DISTINCT to the resulting set – and you have eliminated the last SELECT:

WITH partitioned AS (
  SELECT
    Value,
    Date,
    GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
  FROM
    atable
)
SELECT DISTINCT
  min  = MIN(Value) OVER (PARTITION BY GroupDate),
  max  = MAX(Value) OVER (PARTITION BY GroupDate),
  avg  = AVG(Value) OVER (PARTITION BY GroupDate),
  diff = LAST_VALUE(Value)  OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       - FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  Date = GroupDate
FROM
  partitioned
;

Finally, it is also possible to move the GroupDate calculation into the same scope where min, max etc. are calculated. You can use CROSS APPLY for that and thus avoid the need for nesting a query entirely – in other words, this way you can get rid of the partitioned CTE as well. The entire query would look like this:

SELECT DISTINCT
  min  = MIN(t.Value) OVER (PARTITION BY x.GroupDate),
  max  = MAX(t.Value) OVER (PARTITION BY x.GroupDate),
  avg  = AVG(t.Value) OVER (PARTITION BY x.GroupDate),
  diff = LAST_VALUE(t.Value)  OVER (PARTITION BY x.GroupDate ORDER BY t.Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       - FIRST_VALUE(t.Value) OVER (PARTITION BY x.GroupDate ORDER BY t.Date ASC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  Date = x.GroupDate
FROM
  atable AS t
  CROSS APPLY (SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)) AS x (GroupDate)
;

and return the same results. You can test it at SQL Fiddle too.


Best way to put checksum on all pages I have a SQL Server database with the PAGE_VERIFY option set to NONE. When I set this to CHECKSUM, only pages changed from that point onwards get their checksum calculated and stored on the page.

I'm looking for the best way to put checksums on at least all table and index data.

My initial idea would be to drop and recreate all clustered indexes. For tables that have no clustered index I would create one and then drop it again.

That should result in at least one write operation on each data and index page, but it's a bit heavy-handed. Anyone has a better idea?

The most comprehensive way in my view would be to encrypt/decrypt the database with TDE.
This will ensure that each and every page will change in memory and will be flushed to disk.

I've tried this with success on 'legacy' dbs that were originally created in SQL2000, after I discovered that several pages didn't have actual checksums on them (0x...200) if you look at the header with dbcc page.

If you were to try this, I would recommend testing it on a restored version of the live db, just in case you have undetected corruption that could be caught and stall the encryption process. There are flags to deal with it, but better play it safe.

Obviously you'll want to backup the certificate used by the encryption, so you are covered for any eventuality during the time the db is encrypted.

If anyone has a better idea for writing checksums on all pages, I'd love to hear it :-)


Get all descendants from 3 hierarchical tables I have the following tables within a database:

+-------------+   +-------------+-------------+   +---------------------------+
|  project1   |   |          project2         +   |         project3          |
+-------------+   +-------------+-------------+   +---------------------------+
| project1_id |   | project2_id | project1_id |   | project3_id | project2_id |
+-------------+   +-------------+-------------+   +---------------------------+
|    hnc1     |   |    hnc4     |     hnc1    |   |     hnc7    |    hnc4     |
|    hnc2     |   |    hnc5     |     hnc1    |   |     hnc8    |    hnc4     |
|    hnc3     |   |    hnc6     |     hnc3    |   |     hnc9    |    hnc6     |
+-------------+   +-------------+-------------+   +---------------------------+

I need to retrieve all descendants of a given ID from all three tables - including the ID itself if it is found in any table.

For example, if I query using the ID 'hnc4' it should get:

+-------------+
| descendants |
+-------------+
|    hnc7     |
|    hnc8     |
|    hnc4     |
+-------------+

If I query using the ID 'hnc1':

+-------------+
| descendants |
+-------------+
|    hnc4     |
|    hnc5     |
|    hnc7     |
|    hnc8     |
|    hnc1     |
+-------------+

The query must work even if the ID is only in the last table project3.

I was searching and found that a recursive CTE could be useful but I don't know how to make it with 3 tables involved.

Any guidance on how to get with the right results?

Identifiers cannot be parameterized, so I can't think of a way to traverse multiple tables with an rCTE.

Assuming UNIQUE or PRIMARY KEY constraints on each table, so we don't have to worry about duplicates.

I wrapped the query in a SQL function to simplify passing the ID:

CREATE OR REPLACE FUNCTION f_all_descendants_of(_id text)
  RETURNS TABLE (descendants text) AS
$func$
   WITH p2 AS (
      SELECT project2_id
      FROM   project2
      WHERE  project1_id = $1
         OR  project2_id = $1
      )

   SELECT project3_id AS descendants
   FROM   project3
   WHERE  project2_id IN (TABLE p2)
      OR  project3_id = $1

   UNION ALL TABLE p2

   UNION ALL
   SELECT project1_id
   FROM   project1
   WHERE  project1_id = $1;
$func$  LANGUAGE sql;

The SELECT on project2 goes into CTE p2, so we can reuse the result for the SELECT on project3. The same is not needed for project1 and project3.

Call:

SELECT * FROM f_all_descendants_of('hnc4');

Produces your desired result exactly.

SQL Fiddle.

If the tables are big, be sure to have the right indexes. Related:

Is a composite index also good for queries on the first field?


Help with modeling this table Thanks for reading my post...I'm new at setting up databases and am working on a simple practice app.

I have a table called PhoneNumbers, which hold a list of phone numbers (duh).  I have two other tables that relate to PhoneNumbers, Employee and Company.

Each Employee can have multiple phone numbers, each Company can have multiple phone numbers.

Since my app needs to be able to separate out the difference between Company phone numbers and employee phone numbers, and be able to list each number per employee and per company, I was wondering the best way to set up the PhoneNumbers table, keeping in mind that perhaps there will be more than two Number Types in the future.

One ideas I had was:

Create two fields in the Phone number table called NumberTypeID and OwnerID and create another table called NumberType.  NumberTypeID would hold the ID numbers of the Number Types created in the NumberType table.  OwnerID would hold ID of the Company or Employee it belongs to.

PhoneNumbers
 - ID
 - PhoneNumber
 - NumberTypeID
 - OwnerID

NumberType
 - ID
 - Name

I'm thinking the NumberTypeID column will help querying all numbers for all companies/employee easier. I also am thinking this makes it more flexible down the road for new number types, but perhaps is overly complex?  Maybe I don't need the NumberTypeID column at all and querying all numbers for all companies/employees is best with a more complex statement saved as a view? Or perhaps I'm missing something else all together?

Again very new at this, so any help would be greatly appreciated.

Thanks

One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.

create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)

create table employee
(
employee_id int PRIMARY KEY IDENTITY,  --(Primary Key)
employee_name varchar(100),
company_id int not null  --(Foreign Key)
)

create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10),   -- Home | Cell | Work
employee_id int not null  --(Foreign Key)
)

employee_id being your foreign key to your employees table.
If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this

select *
from phone as p
join employee as e on e.employee_id = p.employee_id
where e.company_id = 1

I hope this helps :)


ORDER BY optimization issue I have a query:

SELECT
  user_details.id , user_details.first_name , user_details.last_name,
  user_accounts.name account_name, jtl0.account_id account_id,
  user_details.title , user_details.job_function ,
  user_details.contact_data_status , user_details.assigned_user_id
FROM user_details
LEFT JOIN  user_accounts_user_details jtl0
  ON user_details.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN  user_accounts user_accounts
  ON user_accounts.id=jtl0.account_id AND user_accounts.deleted=0
where user_details.deleted=0
ORDER BY account_name ASC LIMIT 0,21;

Without the ORDER BY / LIMIT, the query returns immediately, but when I add ORDER BY account_name ASC to this takes 5 minutes. I have tried several combinations of indexes on user_accounts but have no success.

Here is the EXPLAIN result of the query:

+----+-------------+----------+--------+--------------------------------------------------------------+-----------------------+---------+-------------------------------+---------+----------------------------------------------+
| id | select_type | table    | type   | possible_keys                                                | key                   | key_len | ref                           | rows    | Extra                                        |
+----+-------------+----------+--------+--------------------------------------------------------------+-----------------------+---------+-------------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | user_details | ref    | idx_contacts_del_last,idx_cont_del_reports,idx_del_id_user   | idx_contacts_del_last | 2       | const                         | 1554822 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | jtl0     | ref    | idx_contid_del_accid                                         | idx_contid_del_accid  | 113     | salesify_db.contacts.id,const |       1 | Using index                                  |
|  1 | SIMPLE      | user_accounts | eq_ref | PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del,idx_oss_name | PRIMARY               | 108     | salesify_db.jtl0.account_id   |       1 |                                              |
+----+-------------+----------+--------+--------------------------------------------------------------+-----------------------+---------+-------------------------------+---------+----------------------------------------------+

No of records in user_accounts : around 3000000
No of records in user_details : around 3400000
No of records in user_accounts_user_details : around 2500000

Index in user_accounts

PRIMARY KEY (`id`),
  KEY `idx_accnt_id_del` (`id`,`deleted`),
  KEY `idx_accnt_name_del` (`name`,`deleted`),
  KEY `idx_accnt_assigned_del` (`deleted`,`assigned_user_id`),
  KEY `idx_accnt_parent_id` (`parent_id`),
  KEY `idx_oss_name` (`id`,`name`(15))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Index in user_details

PRIMARY KEY (`id`),
  KEY `idx_cont_last_first` (`last_name`,`first_name`,`deleted`),
  KEY `idx_contacts_del_last` (`deleted`,`last_name`),
  KEY `idx_cont_del_reports` (`deleted`,`reports_to_id`,`last_name`),
  KEY `idx_reports_to_id` (`reports_to_id`),
  KEY `idx_del_id_user` (`deleted`,`id`,`assigned_user_id`),
  KEY `idx_cont_assigned` (`assigned_user_id`),
  KEY `primary_address_idx` (`primary_address_country`),
  KEY `idx_oss_dt` (`date_entered`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Index in user_accounts_user_details

  PRIMARY KEY (`id`),
  KEY `idx_account_contact` (`account_id`,`contact_id`),
  KEY `idx_contid_del_accid` (`contact_id`,`deleted`,`account_id`)

Normally you want to throw away as many rows as you can, as quickly as you can.  That doesn't seem likely here, unfortunately.

All you have is 'deleted = 0' for each table, so MySQL will pick a table to start with.  In your explain, it picked user_details, resulting in 1.5M rows being used.

Do the other tables have fewer active rows?  If so, you'd want to start with that table instead.

Once it picks a table, it will join across.  If we're starting with user_details, you're off to user_accounts_user_details, so you'll want an index on user_details(deleted, id) so the ids can be found via the index.

Since you're coming into user_accounts_user_details with the contact_id, you'll want to start your index with that.  We know that deleted=0 there, too, so add that.  And, once again, you'll want the id for the next table in your index.  I think that means user_account_user_details(contact_id, deleted, account_id).

We'll take that account_id over to user_accounts, so we'll need an index there.  We still have deleted=0, and we're not continuing on to other tables, so try user_accounts(id, deleted).

Depending on the quantity and size of the columns you're SELECTing from each table, you can add them at the end of the indexes, which can keep MySQL from having to read the underlying row from disk.  In your case, it looks like you're SELECTing a good number of long columns, which I wouldn't add to the composite indexes described above.  I might be tempted to include user_accounts.name, since you use that in the ORDER BY clause.

I hope that helps.

If possible find some other way to reduce the number of rows more quickly.  Can you limit by date, or something else?  Starting with 10 records in the first table would be so much better than starting with 1M.

Good luck.


Query: How to join on different tables depending on the filled column? I'm pretty new to the field of SQL, but already solved a lot of problems, so until now i am familiar with the different possibilites of JOIN(s) (explicit and implicit syntax), group by, distinct and so on.

But the query i am working on right now is crushing my head and i'm not sure how to approach that one:

The data i got is about roads and junctions, one road segment is defined from one junction up until the next one.

Segment Table:

| ID | start_junc | end_junc | ... |
| 1  | 1518       | 3645     | ... |
| 2  | 3645       | 769      | ... |

But there are different types of junctions, so the start_junc and end_junc FK are for a table looking like this:

| ID   | type        | junc_type_A | junc_type_B | junc_type_C |
| 769  | junc_type_C | <NULL>      | <NULL>      | 63          |
| 1518 | junc_type_B | <NULL>      | 17          | <NULL>      |
| 3645 | junc_type_A | 42          | <NULL>      | <NULL>      |
| ...  | junc_type_B | <NULL>      | 16          | <NULL>      |
| ...  | junc_type_B | <NULL>      | 13          | <NULL>      |

So for every ID there is exactly one junction type, all other junction types are NULL value and additional the the type of it is written in the column 'type'. Finally there is a table for each junction type with the ID and attributes for the junction like name,...

junc_type_A
| ID | name | ... |
| 42 | abc  | ... |
| 43 | def  | ... |
| .. | ...  | ... |

junc_type_B
| ID | name | ... |
| 13 | jkl  | ... |
| 16 | mno  | ... |
| 17 | pqr  | ... |

and of course for junc_type_C which i will omit.

What is needed is a query that additional outputs the start and end junctions name to the segments table:

| ID | start_junc | end_junc | ... | start_junc_name | end_junc_name |
| 1  | 1518       | 3645     | ... | pqr             | abc           |

So, doing it manually it seems like an easy piece... from the start_junc ID join the next table on that ID, look which column is not null, join that table with the name of the column... But i have no idea how to approach this in SQL. Hope it is possible without writing a procedure.

And one additional question: How can this kind of relation (for the junction type table where there is a column for every type of junction table and all other values are NULL) be called, so that i have an idea how to search for that in the future?

Let's say that your tables are called (in order of you mentioning them):

Segment,
FK,
JC_type_A,
JC_type_B,
etc

The main problem that I can see is that you don't know which junc_type will be for which junc.
So you will have to left join back to their respective tables and select the one that is not null in your select statement.

Something along the lines of the following should help to be a template for you.

SELECT  s.Id,
        s.start_junc,
        s.end_junc,
        -- Coalesce works here as there are more than 2 potential NULL values
        --  and only 1 of them will be NOT NULL as you have said.
        start_junc_name = COALESCE(ja_start.name, jb_start.name, jc_start.name),
        end_junc_name   = COALESCE(ja_end.name, jb_end.name, jc_end.name),
FROM    #Segment AS s
JOIN    #FK AS f_start
ON      s.start_junc = f_start.Id
JOIN    #FK AS f_end
ON      s.end_junc = f_end.Id
-- get junc_a start if exists, else return null...
LEFT
JOIN    #JC_type_A AS ja_start
ON      f_start.junc_type_A = ja_start.Id
-- get junc_a end if exists, else return null...
LEFT
JOIN    #JC_type_a AS ja_end
ON      f_end.junc_type_A = ja_end.Id
--     [...continue for rest of tables...]


Why we need auxiliary instance in TSPITR Need help!

I've read Database Backup and Recovery User's Guide 11g but I can't uderstand why we need auxiliary instance to perform tspitr.
 We can do complete recovery of tablespace within same instance, why it's impossible for tspitr?

Here Hemant K Chitale says: "You cannot recover a tablespace to an SCN that is lower than the rest of the database.". Can anyone help me to uderstand why?

In this message is shown that when we turn tablespace offline, its SCN becomes lower than SCN of other tablespaces after a while, and when we turn it online "it will not apply redo it will just adopt current SCN".

So, why database cannot open recovered until time datafile adopting it's SCN to current? It'so due to technical aspects of OracleDB or there is another reason?

TL;DR to be able to restore data+undo without overwriting the entire current undo.

When you perform RMAN online backup, some transactions are in the middle of processing, they did change something but not yet performed their COMMIT. Transactional model of work ("atomicity") requires those transactions to be rolled back (completely undone) on recovery. Oracle engine does this automatically. Oracle engine does not allow onlining tablespace if this cannot be done, to protect user from seeing possibly inconsistent data. So do transactions save somehow the "consistent" data before they change it? Yes, in the undo tablespace (or a "rollback segment"). This is why, recovering a tablespace, you cannot "forget" it had undo, you need to recover undo from the same point in time (same SCN).

To sum up, Oracle engine needs to have undo data synchronized with from the same point in time as the main data.

Undo is, usually, put into a single tablespace despite it is being a mix of pieces of "consistent" information from many different data tablespaces. This is why you TSPITR to an auxiliary instance - to be able to restore data+undo without overwriting the entire current undo.

Once auxiliary is online, the auxiliary's data has been made consistent and auxiliary's undo is no longer needed.


MySQL 8.0.22 Trigger won't run unless definer is logged in I have the following tigger that used to work before upgrading to MySQL 8 from MySQL 5.  Now the trigger will only run if the definer is logged in via phpmyadmin or some other database admin tool. The trigger is setup as timing After and event Insert.
 CREATE TRIGGER `countupdate` AFTER INSERT ON `status`
 FOR EACH ROW BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`);
       IF @COUNT > 0 THEN
        IF NEW.`status` = 'UP'
        THEN
        UPDATE `updowncount`
        SET `upcount` = `upcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        ELSE
        UPDATE `updowncount`
        SET `downcount` = `downcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        END IF;
    END IF;
 END

I have tried deleting the trigger then creating it again and setting the definer to root@%. I also tried definer root@localhost.
Running on Ubuntu 20.04.1
UPDATE:
Also it appears that I have to go back in and re-save the trigger or it won't trigger.
Log file during an insert that did not update data based on the tigger:
2020-12-14T02:06:09.569622Z   794 Query INSERT INTO status (systemname, status, lastchecked) VALUES ('Linuxdb2', 'Up', '2020-12-13 20:06:09.569394')
2020-12-14T02:06:09.569808Z   794 Query SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`)
2020-12-14T02:06:09.570132Z   794 Query SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`)
2020-12-14T02:06:09.574412Z   794 Query COMMIT

When status table has a new row inserted two tiggers run. The one above that has stopped working and one to add the system to the updowncount table. If the systemname doesn't exist in updowncount table.  This is a before for insert trigger.
 CREATE TRIGGER `countsystemadd` BEFORE INSERT ON `status`
 FOR EACH ROW BEGIN
       SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`);
       IF @COUNT=0 THEN
        INSERT INTO `updowncount` (upcount, downcount, systemname, `lastupdate`) VALUES (0, 0, NEW.`systemname`, NOW());
       END IF;
END

Thirteen new rows are all added with in seconds of each other by different python scripts.
Status table:
CREATE TABLE `status` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `systemname` char(40) DEFAULT NULL,
  `status` char(40) DEFAULT NULL,
  `lastchecked` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

Updowncount table:
CREATE TABLE `updowncount` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `upcount` int DEFAULT NULL,
  `downcount` int DEFAULT NULL,
  `systemname` text,
  `lastupdate` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

UPDATE:
I removed the countsystemadd trigger and things started working. But once the data in the status table gets over 1,000 records the it stops working. This what shows up in the log.
2021-01-08T15:00:01.829948Z  1348 Query INSERT INTO status (systemname, status, lastchecked) VALUES ('msmccam', 'Up', '2021-01-08 09:00:01.829712')
2021-01-08T15:00:01.830210Z  1348 Query SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`)
2021-01-08T15:00:01.831031Z  1348 Query COMMIT

Everything is fine, with  your triggers, as long as you don't have also a Trgger on updowncount

CREATE TABLE `status` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `systemname` char(40) DEFAULT NULL,
  `status` char(40) DEFAULT NULL,
  `lastchecked` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

CREATE TABLE `updowncount` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `upcount` int DEFAULT NULL,
  `downcount` int DEFAULT NULL,
  `systemname` text,
  `lastupdate` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

 CREATE TRIGGER `countsystemadd` BEFORE INSERT ON `status`
 FOR EACH ROW BEGIN
       SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`);
       IF @COUNT=0 THEN
        INSERT INTO `updowncount` (upcount, downcount, systemname, `lastupdate`) VALUES (0, 0, NEW.`systemname`, NOW());
       END IF;
END

 CREATE TRIGGER `countupdate` AFTER INSERT ON `status`
 FOR EACH ROW BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`);
       IF @COUNT > 0 THEN
        IF NEW.`status` = 'UP'
        THEN
        UPDATE `updowncount`
        SET `upcount` = `upcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        ELSE
        UPDATE `updowncount`
        SET `downcount` = `downcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        END IF;
    END IF;
 END

INSERT INTO status (`systemname`,`status`) VALUES  ('SystemA','UP')

SELECT * FROM updowncount

id | upcount | downcount | systemname | lastupdate
-: | ------: | --------: | :--------- | :------------------
18 |       1 |         0 | SystemA    | 2020-12-15 19:18:43

db<>fiddle here


Hinting to Redshift how to distribute a join? If I am joining between two tables, A and B, with multiple equality constraints, is there any way that I can hint to Redshift which it should use for distributing the join? For example, in:

FROM A
JOIN B
  ON  B.id = A.b_id
  AND B.x = some_expression_of(A.x)

In my situation, I know that all the data is already distributed by this X expression, so that if Redshift distributes by it, there will not be any actual network activity.

Edit: Here's a more representative version of the join:

FROM order_products --distribution key: user_id
FULL OUTER JOIN order_payments --distribution key: user_id
   ON FALSE
LEFT JOIN orders --distribution key: user_id
   ON  orders.id = COALESCE(order_products.order_id, order_payments.order_id)
   AND orders.user_id = COALESCE(order_products.user_id, order_payments.user_id)

What I'd like is to just be able to tell Redshift to please distribute the last join based on user_id (which Redshift might categorize as a  DS_DIST_BOTH, but should actually result in no network redistribution)

You cannot use hints in Redshift like Oracle. You can do things like set the distribution keys. If X is always used in that fashion, it may make sense to incorporate that into your DDL.

See here:
http://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html


Is it bad to have M:M relationships defined where 1:M is being used? In general, would it be a bad idea to design a database with M:M relationships where ever things have a relationship, instead of using, say, 1:M instead?

My reason for doing this is because later on if I decide that I no longer want a 1:M relationship but want a M:M instead, I don't have to modify the database at all; all I would have to do is change my business logic to handle "many" instead of just "one".

For instance, say I have these tables:

user
image

A user should only have one profile image, however I handle the relationship with a table like this:

user_profile_image

That table would have user_id and image_id foreign keys. The alternative would be to embed a image_id foreign key in the user table.

In my business logic, I explicitly only allow one profile image to be set for a user. But as I stated before, my theory is that later on if I want to allow multiple profile images for a user, the database doesn't need a single change and I can just update my business logic to handle many instead of one.

Is this a good idea, or is it frowned upon? Please explain? I have been doing this all throughout my app, so I don't have a single 1:M relationship defined in the schema layer, though my business logic has basically 80% 1:M relationships.

It would work - but it would be relying on the application logic to maintain relational integrity. You probably want to limit the relationship to 1:1 initially at the database level - which can be done without sacrificing flexibility.

Normally, the following relationships can be handled in the following ways:

1:1 - data can be maintained in the same table
1:many - "many" data is in a separate table, and uses the primary key from the "1" table to link the many back to the one
many:many - a "bridge" table is used, with the primary keys from both of the "many" tables, so each row from table A can be tied to many rows from table B, and each row from table B can be tied to many rows form table A.

You can "downgrade" from 1:many to 1:1, or from many:many to 1:many or to 1:1, by using unique constraints.

Let's take your example: You've got user, image, and user_profile_image. Currently, you want to have a 1:1 relationship - each user has (at most) one profile image, and each profile image is tied to (at most) one user.

Let's assume that user_id is the primary key of user, and image_id is the primary key of image. In user_profile_image, as you note, those fields are foreign keys, linking each row to a specific user and image.

The table would look something like this:

CREATE TABLE user_profile_image
     ( upi_id int AUTO_INCREMENT PRIMARY KEY -- or whatever works in your DBMS
      ,user_id int
      ,image_id int
      ,CONSTRAINT FK_user_profile_image__user
         FOREIGN KEY (user_id) REFERENCES user (user_id)
      ,CONSTRAINT FK_user_profile_image__image
         FOREIGN KEY (image_id) REFERENCES image (image_id)
     )

Therefore, for a many-to-many relationship, there could be many rows with the same user_id, connecting that user to many different images, and many rows with the same image_id, tying that image to many different users.

If we want to change this many-to-many set-up, restricting it so it only allows a 1:1 relationship, we change our table, adding unique constraints to both of our foreign keys:

CREATE TABLE user_profile_image
     ( upi_id int AUTO_INCREMENT PRIMARY KEY -- or whatever works in your DBMS
      ,user_id int
      ,image_id int
      ,CONSTRAINT FK_user_profile_image__user
         FOREIGN KEY (user_id) REFERENCES user (user_id)
      ,CONSTRAINT UNIQUE INDEX UX_user_profile_image__user_id (user_id)
      ,CONSTRAINT FK_user_profile_image__image
         FOREIGN KEY (image_id) REFERENCES image (image_id)
      ,CONSTRAINT UNIQUE INDEX UX_user_profile_image__iamge_id (image_id)
     )

(Actual syntax may vary for your DBMS)

With these constraints in place, each user_id can only appear in one user_profile_image row, as can each image_id - a 1:1 relationship. So, each user can have only one image, and each image can be tied to only one user.

Note that you must have both UNIQUE constraints for this to work. I you had one UNIQUE constraint that covered both user_id and image_id, then users could have multiple image and images could be tied to multiple users. Such a constraint would only prevent the same user/image relationship from appearing twice in the table. That can be a useful constraint to have, admittedly; but it won't do what you want.

Now, if you get to a point where you want a user to be able to have multiple profile images (a 1:many relationship), you would drop the UNIQUE constraint on the user_id. At that point, user_id could appear in multiple user_profile_image rows, but each image could still only be tied to one user.

If, on the other hand, you still want each user to have one profile image, but you find that 90% of your users are using the same three profile images, you may want to keep the UNIQUE constraint on user_id, and remove the UNIQUE constraint on image_id. Then, each user would still have one image - but you could just keep one copy of each of those 3 images, and tie all the users who want one to one instance of the image, saving a fair amount of space. you've flipped which table is the one and which is the many, but this is still a 1:many relationship.

Finally, if you want users to be able to have multiple images, and images to be used by more than one user (a true many:many relationship), drop both the user_id and the image_id UNIQUE constraints.

Note: I would only do things this way if you genuinely see a possibility that the relationship could change. Someone looking at the basic table layout might assume that the relationship is many-to-many (since you have a bridging table), and miss the UNIQUE constraints, leading them to write code that won't work. It's even possible that someone will assume the UNIQUE constraints are a mistake created by someone in the past, and remove them without understanding the full implications.

Also, you do add somewhat to the overhead required to store your DB on disk, having additional indexes that you wouldn't need if you used the "normal" construct for a 1:1 or 1:many relationship. Also, queries will be a little bit more expensive. In most cases, this wouldn't be a big deal; but, if you're dealing with billions of data rows, it may be worth consideration.

I wouldn't say it's actively wrong to do things this way, but it can be over-engineering your solution, planning for eventualities that never appear. Like I said above - unless you can see a real likelihood that you will need to "upgrade" a 1:1 or 1:many relationship in the future, I'd stick with the simple solutions.


How do I specify a timeout in sqlite3 from the command-line? I have a couple basic scripts that kick out a little information while my sqlite3 DB is being populated, but about half the time the command instantly fails:

$ sqlite3 outgoing.db "select * from edges where worker is not null;"
Error: database is locked
$ sqlite3 outgoing.db "select * from edges where worker is not null;"
Error: database is locked
$ sqlite3 outgoing.db "select * from edges where worker is not null;"
1014->9000|1014|9000||-1.0|2
1014->9001|1014|9001||-1.0|2
...

If I add .timeout 1; to the beginning of the command I just get a syntax error; how do I pass sqlite the special . parameters non-interactively via the command-line?

You can do this by using an init file.

init.sql (note that the timeout value is in milliseconds - 1 is rather short):

.timeout 1000

At the prompt:

$ sqlite3 -init init.sql outgoing.db "select * from edges where worker is not null;"
Loading resources from init.sql
# 1 second pause
Error: database is locked

With some shells (on Linux at least, not very portable), you can avoid the need for a proper file with process substitution if that's a problem:

$ sqlite3 -init <(echo .timeout 1000) your.db "your sql;"

The extra output line ("Loading resources from ...") doesn't get printed if the output is redirected to a file or pipe, or to the .output file if you specified one in your init file.


How do tablespaces differ in DB2 from databases in MySQL or SQL Server? In DB2, it seems there is a strong separation between what a database is and what a tablespace is. Where as in MySQL and MS SQL Server tablespace and database seemed to be used synonymously.

What are the differences, if any?

In DB2, a tablespace is the object that serves as a layer of abstraction between tables (and indexes) and disk.  It is similar to a file group in MS SQL Server.

In DB2, a database is roughly equivalent to a SQL Server instance – a database contains multiple tablespaces, transaction logs, one or more bufferpools and other memory areas.

A "database" in SQL server is much closer to a schema in DB2.


Data Scrub - Duplicate Records I wanted to delete duplicate records in my database, but it's a little more in depth than just one field.

Status  ID
P   150013511822029
P   150022555544009
P   150022551512029
X   150014520920007
R   150022994940022
P   150023488655015

If status = R, the record needs to be removed.
If the status = X (this is a reversal), both the X record and the corresponding P record (with the same ID) need to be removed.

Can someone please help out a newbie? Thank you!

You did not specify your platform but it should be relatively easy to do that:

DELETE FROM mytable
 WHERE Status = 'R'
    OR Status = 'X'
    OR
    (
       Status = 'P'
       AND ID IN (SELECT ID FROM mytable WHERE Status = 'X')
    );

Here are some links you may find helpful:

DELETE

WHERE

IN

AND, OR, NOT


Insert multiple values into stored procedure I have 2 System Stored Procedures.

1) exec spGetObsoleteUpdatesToCleanup

2) exec spDeleteUpdate @localUpdateID=000000

The first proc retrieves a column called "localUpdateID" with 30 000 rows.
In the second proc you have specify the retrieved row from the first proc at "@localUpdateID=000000"

Copying each one of the 30 000 rows and pasting it into the second proc will take forever.

I have tried:

1) exec spDeleteUpdate @localUpdateID = exec spGetObsoleteUpdatesToCleanup

2) exec spDeleteUpdate @localUpdateID IN ('00000', '00001')

You need to update your first stored procedure so it returns data using an OUT parameter.

You can create a temporary table variable that you will use to temporarily store the output IDs of your first stored procedure and then send them to the second.

It should end up looking something like this:

declare @OutputIdList table(localUpdateId int)
exec spGetObsoleteUpdatesToCleanup @OutputIdList=@OutputIDs OUTPUT
exec spDeleteUpdate select localUpdateId  from @OutputIdList

Microsoft has a blog post that explains how to give OUT parameters to stored procedures.

Here's some copy-pasted data from it.

    CREATE PROCEDURE Myproc
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc


Lost databases in SQL Server 2012 Had three databases connected to SQL server 2012, unfortunately, now I can't see any of them under the Databases part of the tree in SQL Server Management Studio. The files (.mdf) are still on the disk, but when I try to reconnect them, I get this error once I click on the mdf file,

Failed to retreive data for this request (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
  The server principal "IESVE\user" is not able to access the database  "model" under the current security context (Microsoft SQL Server, Error: 916)

I thought this might be a problem with the management studio, but when I try to connect via ADO, I get this error,
[DBNETLIB][ConnectionOpen(Invalid Instance())] Invalid Connection

All of this worked before and the only thing I can think of is a recent windows update.

If anyone's got any ideas on how to fix/diagnose this, I would be very grateful,
Thanks
James

Edit; Running as administrator gets me a bit closer to reattaching the databases, but now I have this error  . I'm wondering if my SQL server version has been downgraded, judging by that error message.

Thanks,
James

It looks like MS has seen this already.  Have you tried running SSMS through
Right Click - Run As Admin?

http://connect.microsoft.com/SQLServer/feedback/details/198005/not-able-to-access-the-database-model-under-the-current-security-context-microsoft-sql-server-error-916

Also are you logging in as a sysadmin to SQL Server? Do you have create DB rights?

Edit:
Just saw your original post updated.  It looks like you are restoring to the wrong version for sure.  Consider yourself lucky if that is the issue!!

Do a SELECT @@SERVERNAME.  What does it return?  Is that the right instance?  Probably not (hopefully).  Try your restore script only after SELECT @@SERVERNAME returns the right info.  Also you might want to use OSQL where you have to state the server name and creds before connecting, that way you know you're at the right one instance.


How do I add an existing shared data source and dataset to my SSDT/Visual Studio SSRS Project? I am trying to add a shared data source and data set to an SSRS project in SSDT/Visual Studio, also called Report Designer.  There are plenty of ways to add this into a Report Builder. I am looking on how to do it in Report Designer.

They are published to http://local//report/dataset.

Thank you in advance for your help.

Here is an explanation retrieved from various MSDN and TechNet articles.

Prerequisites are

  To create a shared data source or modify its properties, you must have Manage data sources permissions on the report server. If the report server runs in native mode, you can use Report Manager to create and configure the shared data source. If the report server runs in SharePoint integrated mode, you can use the application pages on a SharePoint site. For any report server regardless of its mode, you can create a shared data source in Report Designer and then publish it to a target server.

To import an existing data source in Report Designer

In Solution Explorer, right-click the Shared Data Sources folder in the report server project, and then click Add Existing Item. The Add Existing Item dialog box opens.
Navigate to an existing Report Definition Shared data source (rds) file and then click Open.
Click OK.

Adding Report Data

In Report Builder or Report Designer, you can add data in the following ways.

Add embedded datasets based on shared data sources.
Add embedded datasets based on embedded data sources.

A detailed explanation can be found here: Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)

There are some limitation in Report Designer:

  In Report Designer, you can create shared datasets as part of a report project, and control whether to deploy them to a report server. You cannot browse to a report server and select a shared dataset to add to your report.

List of references:

Create, Modify, and Delete Shared Data Sources (SSRS)
Add and Verify a Data Connection (Report Builder and SSRS)
Report Datasets (SSRS)
Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)


Restore partial data from single file of SQL Server Full Strip Backup I don't know if I ask for something possible, but here is my problem:

We had a disaster (virus) in our SQL Server Database and we lost all the information and backups.

The only backup remaining is a single file of a Full Backup in strip mode (the one that splits a single backup in multiple files).

Logically, when I try to restore the database using that file I get the error:

  The media set has 2 media families but only 1 are provided. All
  members must be provided

Is there any way to recover some partial information from that piece of the backup? either using SQL or third party software.

Even if I just manage to get some information of a single table it would be more than none.

There is no way to recover a transactionally-consistent database from only half of the backup media set.

The problem is that, while a full backup contains the data file, there is also a portion of the transaction log, at the end of the backup file, to ensure the restored database is transactionally consistent.

Even if you did manage to recreate portions of the MDF file from this backup, you would still be missing the transaction log that rolls back in-flight transactions and rolls forward completed transactions that took place after the backup started.

Making matters worse, if the striped backup is written out as interleaved files (which I believe it is), you'd have one out of every two 8KB data pages, and a corrupted log at the end. There's no functional way of establishing any consistency with this kind of recovery.


MS Access fail to recognize correct field width I am trying to import a very large text file (200 fields +) into MS Access.

The text file is not comma separated. I am not sure if it can be called fixed length, since each field has it own length.

MS Access fail to recognize correct filed width. For example, correct field 1 start from 1 to 6 but MS Access believe it starts from 1 to 46. Am I going to manually correct this problem?

I plan to import to MS Access first and then import to SQL server. Since it does not correctly recognize right width in SQL server wizard

Update:

Sample data. The break line is added by me manually.

Looking at your image the data looks like it is fixed width. You have a few options. You could:

Manually edit the columns in access. (fixed width).
Import the data into excel and use the "text to columns" feature to split the fields. Then import to sql.
Edit the file with a text editor like notepad++ and insert a delimiter between columns.
Import the data into SQL into a single column and then use TSQL to parse/substring the data into different fields. It's not a nice job but its doable.

If the data is a mix of fixed width and and delimited you can use a combination of the steps above.

If you have to repeat or automate this process, I recommend you go back to the source of this data and ask them to re-export with a delimiter, grant query access so you can import data directly, or get them to export directly to a database table for you.


How to detect DBCC ShrinkDatabase completion percentage? Recently we have archived some of data from one of very large production database and need to shrink all data files to reacquire disk space.

Problem is that its taking too much time and we are unable to find out how much work is done by DBCC ShrinkDatabase so we can estimate remaining execution time.

Is there any quick way to get completion percentage of DBCC ShrinkDatabase task ?

Well, you shouldn't ever be using DBCC SHRINKDATABASE, IMHO - if you need to shrink files at all, you should think twice, maybe even three times, and even in the odd case where it really is warranted (hint: this should be rare), you should target each file individually using DBCC SHRINKFILE. Please read every single word on this page.

Anyway, you can see the progress using:

SELECT percent_complete, estimated_completion_time
  FROM sys.dm_exec_requests
  WHERE session_id = <spid running the shrink>;

Documentation for sys.dm_exec_requests.


Return "parent" rows where only one "child" record exists in one-to-many relationship Having a one-to-many relation, I am trying to get records in the accounts table where only one record exists in the accounts_prop table.

I have the following query, but it does not do the desired. The query returns records with more records in the accounts_prop table.

select *
from `accounts`
where exists (
    select *
    from `accounts_prop`
    where `accounts`.`account_id` = `accounts_prop`.`account_id`
      and `von` >= '2017-08-25'
    having count(account_id) = 1
) limit 3

Setup sample data:

create table accounts (account_id int, von datetime);
insert into  accounts (account_id, von) values (1,'2017-08-01'), (2,'2017-09-01'), (3,'2017-10-01');

create table accounts_prop (account_id int, propname varchar(30));
insert into  accounts_prop (account_id, propname) values (1,'pname1'), (2,'pname1'), (2,'pname2'), (3,'pname1');

Sample data joined together:

select a.account_id,
       a.von,
       ap.propname
from   accounts a
join   accounts_prop ap
on     a.account_id = ap.account_id
order by 1,2,3;

account_id | von                 | propname
---------- | ------------------- | --------
         1 | 2017-08-01 00:00:00 | pname1
         2 | 2017-09-01 00:00:00 | pname1
         2 | 2017-09-01 00:00:00 | pname2
         3 | 2017-10-01 00:00:00 | pname1

Proposed query:

select a.*
from   accounts a
where  a.von >= '2017-08-25'
and    (select count(*)
        from   accounts_prop ap
        where  ap.account_id = a.account_id) = 1
order by 1,2;

account_id | von
---------- | -------------------
         3 | 2017-10-01 00:00:00

Here's a dbfiddle for the above.


Remote backup to folder path in domain A simple question,

We have just done a P2V and are re-testing the environment and moved from SQL 2005 to SQL 2008R2. I am taking this chance to redo my back up plans. I have set up a remote folder on the domain and am having trouble accessing it. I have permissions on the folder of SERVICE plus AD administrators, and my SQL instance is running the Local Network service built in account.

This is not working I am trying to follow the question Here but not sure how to use this proxy in a maintenance plan / sql job. I am trying to avoid setting my SQL service accounts to administrator. Can this be done?

You may proceed with something like creating a proxy account and then assigning the same to SQL Agent job:

Assuming you have Maintenance plan created, which will have its corresponding job created out there on Job activity. Create a SQL login which later you need to give the required roles to execute the SQL agent jobs and assign this login as job owner of the Main'ce plan job
Before you create a proxy account you will need to create a credential as explained in How to: Create a Credential
Now for the SQL login created earlier assign the roles out of below as per you're security standards:

SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole

Once login has been created with sufficient roles as above you can
create proxy account as explained How to: Create a Proxy. In the ‘Principals’ option, select the SQL login which was created in above step.
Now the final step would be to run this proxy account in the job step
properties of the maintenance plan. You can select the proxy created from dropdown under the section which says "Run As".
Finally test the proxy by executing the job.


Multiple COUNT fields, GROUP BY I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries?

I'm aware of WITH ROLLUP but can't produce the right query.

This gives the right result:

SELECT A.mid, B.pid, A.count AS mid_count, B.count as pid_count
  FROM (Select mid , COUNT(*) AS count FROM test GROUP BY mid) AS A,
  (Select mid, pid, COUNT(*) AS count FROM test GROUP BY mid, pid) AS B
  WHERE A.mid = B.mid ORDER BY mid_count DESC

This is the expected output

+-----+-----+-----------+-----------+
| mid | pid | mid_count | pid_count |
+-----+-----+-----------+-----------+
|   3 |  12 |         5 |         2 |
|   3 |  14 |         5 |         1 |
|   3 |  15 |         5 |         2 |
|   4 |  12 |         2 |         1 |
|   4 |  17 |         2 |         1 |
+-----+-----+-----------+-----------+

I've created a fiddle at: http://sqlfiddle.com/#!9/e3ebe1/12

I removed one of the subqueries from yours but fundamentally as mysql doesn't support count over partition etc, it will need more than one pass through the table:

Select test.mid, test.pid, A.cnt as midCount, count(*) as pidCount
from test join
(Select mid, count(*) as cnt from test group by mid) A
on test.mid =  A.mid
Group by mid, pid


how can i get an accurate count based on max date when joining 3 tables when one of the join fields is many to 1? So, I have 3 tables that I am attempting to get counts for based on a groupid, and a task code.  There are a few issues I am having as some of the relationships are many to one, which I think is somehow inflating my counts.  I will list my 3 tables with the pertinent attributes.
task_table contains:
task_code - would like to get the counts of each one in a group id, would like to use the latest instance basedon event date.
sol_id -used to join to worktable; many sol_id to one m_id is possible
edate -need to use to get one record
cur_id - where cur_id = 1 in the where clause
worktable contains:
sol_id - used to join to task_table
m_id - used to join to grouptable
grouptable contains:
m_id
groupid- used to group the task_code to get count
I'd like the end result to look like:

group_id
task_count
task

5555
45
A

5555
4
N

5624
67
A

5624
23
O

5624
42
X

I have been attempting to run a number of queries, but the counts I am getting back do not look correct.  I am concerned that it is somehow returning more than one instance of the m_id somehow?  Here is the query in question:
select  c.groupid, count(c.groupid) group_count, a.task_code from task_table a
join worktable b
on a.sol_id = b.sol_id
join grouptable c
on b.m_id= c.m_id
where   a.cur_id = 1 and a.task_code is not null
group by c.groupid, a.task_code;

If I add 'edate = (select max(edate) from task_table)' in the where clause, it returns an empty table.
I am unsure how to incorporate edate to get only the newest record that fits the criteria in the where clause.  The reason I think I want to use this is because there could be more than one sol_id that is associated with a m_id, so i'd just like to include only the newest record with a cur_id in the count. Thank you for your time.
sample data
task_table

task_code
sol_id
edate
cur_id

A
23
6/7/09
1

A
24
6/4/09
1

A
23
6/10/09
0

B
45
6/2/09
1

B
42
6/3/09
1

C
34
10/8/10
0

C
83
9/10/09
1

work table

sol_id
m_id

23
1234

24
1234

45
1832

42
1343

83
7623

group table

m_id
group_id

1234
A76

1832
Y23

1343
A76

7623
Y23

looking at these tables, the result should look like the following

group_id
task_count
task

A76
2
A

Y23
1
C

( A76 should only count sol_id 23 and 42) ( Y23 should only count sol_id 83)

mmm based on your sample date your query is not wrong. but your expected results are wrong.
select
    a.task_code,a.sol_id,a.cur_id
    ,b.m_id
    ,c.groupid
FROM
    task_table AS a
    inner join worktable AS b on a.sol_id = b.sol_id
    inner join grouptable AS c on b.m_id= c.m_id
    where
        a.cur_id = 1 and a.task_code is not null
ORDER BY c.groupid, b.sol_id;

select
    c.groupid
    ,count(c.groupid) AS group_count
    ,a.task_code
from task_table AS a
join worktable AS b on a.sol_id = b.sol_id
join grouptable AS c on b.m_id= c.m_id
where
    a.cur_id = 1 and a.task_code is not null
group by
    c.groupid, a.task_code
;

the group_id A76 is linked to the work table with sol_id 23,24 and 42; 23 and 24 are task_code A. 42 is task_code B.
Y23 has two m_id that connect sol_id 45 and 83 to task code A and B.
So the second output in the image is as you expect but has 2 more rows related to the task B.
Maybe the sample date is wrong because a can't see a wrong result to fix.


Default value of a column based on other column in MySQL I have a mytable table in my MySQL database.

It contains the following columns:

id (INT) PRIMARY KEY - AUTO INCREMENT - DEFAULT NONE
name (VARCHAR (220)) - DEFAULT NULL
code (VARCHAR (220)) - DEFAULT NULL

Is it possible that if I insert something in this table I want the code column to autogenerate its value based on the columns id and name.

If I tried to run this example query:

INSERT INTO mytable(name) VALUES ('myname');

I want to get the following result:

+----+--------+-------+
| id | name   | code  |
+----+--------+-------+
| 1  | myname | my1   |
+----+--------+-------+

The code column default value must be the first 2 characters of myname which is my plus the id column.

I can actually do this using PHP code but I think there must be a way using MySQL only.

If the code field values are altered never the solution is trivial - look on the fiddle provided in Sam's comment. The only point I'd recommend to specify VIRTUAL attribute for calculated field explicitely.

If the code field values specified in a question are default only, and they can be altered from the default value freely either when inserting or by data updating, I'd recommend to make this field NULLable and use NULL value as a mark that the value is default one and must be calculated in a query. In all places where you need the value of this field you will use the below expression instead of direct field's value selection:

SELECT  ...,
        COALESCE(code, CONCAT(SUBSTRING(name,1,2),id)) code,
        ...


"Folder" structure of Postgres database missing in DBeaver I guess I did a faulty database restoration or the dump did not have this data and the "folder like" structure was lost. This is a capture of Dbeaver showing what I mean with "foler like" structure. I can easily access the functions, tables, schemas, etc. stored in the database definition. I'm not so worried about the loss of the original structure as it did not have much, but I do want to recreate it step by step when I need it. For example, now I made a function and would like to see it under a "Functions" folder, how can I do that?

Also, what should I have included in the pg_dump generation script to also backup this structure?

Thanks.

In the database navigator on the left, right-click on your database connection, and select "Advanced" rather than "Simple":


Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL.
In our application, we are planning to upload customer’s data using a Excel sheet and these data will be displayed on custom build interactive dashboard.

I have few questions before I start working on this application

1) For the mass data upload from excel, which db is suitable MySQL or PostgreSQL?

2) At some point is it possible that multiple customers can start data upload at same time ?. To deal with such scenario is it advisable to create a separate db for each customer or to use a single db for application(single tenancy vs multi-tenancy). Please tell me pros and cons if any for each scenario?

3) How it would affect my dashboard performance, if I go with multiple databases design and I join tables across multiple databases?

Thanks

For a data warehousing/analytics type application (DW/OLAP), I would go with PostgreSQL. It has set operators, windowing functions (also known as analytic functions) and common table expressions. You will most likely have to implement some or all of these in your own code with MySQL with the attendant possibility of bugs. MySQL is more suited to read-heavy OLTP type applications, whereas PostgreSQL is better (IMHO) for OLAP work.

With respect to "multi-tenancy" - why not use the same database for all and simply identify different customers by a customer_id - or (depending on the number of customers) have separate tables for each customer and non-customer specific data in other tables? How many customers do you have and how much data do you expect to be storing/analysing  in the next 10 years? Modern RDBMSs can store large amounts of data and retrieve quickly (with suitable disk configuration), but I would favour PostgreSQL for large databases.

Sharding might be interesting if you only occasionally query across customers and most of your analysis is done per customer - you could set up a table (or even schema) per customer (on different disks). Again, knowledge of data volumes would help here.

Furthermore, with respect to multi-tenancy, see my question to jynus about querying across servers - or even different schemas within the same server.

I take jynus' point about "holy wars" - this post is just my own opinion about your particular question - were I to be asked about a different scenario, I might well recommend MySQL.

[EDIT]

What, exactly, is "my dashboard performance"?


Migrating Oracle packages to SqlServer I'm moving database from Oracle to SQLServer (2012 if that matters). The process of migrating tables/views is more or less straightforward and automated. Triggers are also not an issue since I don't have too many of them.

However, I wonder what is the best practice for moving Oracle packages. As far as I know, SQLServer doesn't have anything similar. My idea is to map each package to SQLServer schema (thus, for instance, I'll have 'package1' , 'package2', etc schemas in SQLServer, each of them has no tables, just functions/procedures/types). I believe it will preserve original purpose of packages and at the same time reduce number of changes required on application side.

Does it make sense or I'm on a wrong way?

Thank you.

There is a similar Stack Overflow Post  on the subject. Your idea of mapping Oracle packages to schemas in SQL Server is the approach deemed most directly analogous.


Transactional Replication snapshot locking Publisher db from beginning I have recently upgraded my remote distributor to 2008r2. I am getting complete system locks when attempting to generate a new snapshot in the day when we have added an article to the publication (we could do this in the past with very little issues).

I have checked sp_Helppublication and we are using synchronization method 3 (concurrent), so no locks should be held until the very end.

I have been trying to following this link as I am only adding an additional table with 26 rows but don't want to reinitialize and have the whole snapshot delivered.

http://deepakrangarajan.blogspot.co.uk/2009/01/sql-2005-transaction-replication-adding.html

I attempted to run this code

EXEC sp_changepublication
@publication = 'statics',
@property = N'immediate_sync',
@value = 'false'
GO

but this is also locking up the system completely. I guess I will have to run in the middle of the night but thought that you should be able to run this as just a config change to the publication.

Also when I try the last step in the link to add it to the subscription

EXEC sp_addsubscription
@publication = 'statics',
@article = 'dbo.SupplierCorporatePayAccountLink_tbl',
@subscriber = 'PEARLJAM',
@destination_db = 'MLT-Replicated',
@reserved='Internal'

I get the following error message

  Msg 14058, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 331
  Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.

After the snapshot, how can I just have the new article added to the subscriber?

To get this to work I scheduled switching off the 'immediate sync' to very low traffic period

EXEC sp_changepublication
@publication = 'statics',
@property = 'Immediate_sync',
@value = 'false'
GO

I then ran a snapshot which only took a fraction of the time at 6 mins.

I then had to drop the current subscription for that article only

    EXEC sp_dropsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR'

and rerun the snapshot. Then i could add the subscription back into the publication

    EXEC sp_addsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR',
    @destination_db = 'MLT-Replicated',
    @reserved='Internal'

but it would not allow this without the fairly obscure setting

@reserved ='Internal'

or I got an error message saying I needed to subscribe to all articles.... replication then automatically sync'd the new table. I'm a happy man as this has been causing me some long nights!


Show the result of a set difference between tables Q and R Assuming that we have two tables:

Table Q
D E
a 1
b 2
g 1

Table R
D E
f 1
b 2
h 3

Show the result of a set difference between tables Q and R. My answer is

D E
a 1
g 1

But my classmates said the instructor's answer is

D E
a 1
g 1
f 1
h 3

So it looks like symmetric difference rather than the real "difference", which answer is right? My classmates said that the question use the word "Between" so the answer should include two tables, but I do not think so.

Here is what you looking at (Set Difference)

Q - R
Set Q Minus Set R

In MySQL

SELECT Q.*
FROM Q LEFT JOIN R USING (D, E)
WHERE R.E IS NULL;

Here is what the classmates are saying (Symmetric Difference)

(Q - R) U (R - Q) or (Set Q Minus Set R) Union (Set R Minus Set Q)
Also (Set Q Union Set R) Minus (Set Q Intersection Set R)

In MySQL

SELECT A.* FROM
(SELECT Q.* FROM Q LEFT JOIN R
USING (D, E) WHERE R.E IS NULL) A
UNION
SELECT B.* FROM
(SELECT R.* FROM R LEFT JOIN Q
USING (D, E) WHERE Q.E IS NULL) B;

Please go ask Your Teacher !!!


Can I create a user defined table type and use it in the same transaction? When I execute the following (in management studio, GO will separate the commands into batches)

use tempdb

begin tran
go

CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
go

declare @myPK dbo.IntIntSet;
go

rollback

I get a deadlock error message. My process has deadlocked with itself. I've seen this behaviour in 2008, 2008R2 and 2012.

Is there a way to use my newly created type inside the same transaction it was created?

This has been reported no less than four times (but all traces have been removed from the WayBack Machine since Connect was murdered). This one was closed as fixed:

connect.microsoft.com/SQLServer/feedback/details/365876/

But that wasn't true. (Also look at the workarounds section - the workaround I suggested is not always going to be acceptable.)
This one was closed as by design / won't fix:

connect.microsoft.com/SQLServer/feedback/details/581193/

These two are newer and still active:

connect.microsoft.com/SQLServer/feedback/details/800919/ (now closed as Won't Fix)
connect.microsoft.com/SQLServer/feedback/details/804365/ (now closed as By Design)

Until Microsoft can be convinced otherwise, you're going to have to find a workaround - just have all the types deployed before running your test, or break it up into multiple tests.
I will try to get confirmation from my contacts about what Umachandar meant by fixed in the earliest item, because obviously that conflicts with later statements.
UPDATE #1 (of, hopefully, exactly 2)
The original bug (that was closed as fixed) involved alias types, but not of type TABLE. It was reported against SQL Server 2005, which obviously didn't have table types and TVPs. It seems UC reported that the bug with non-table alias types was fixed based on how they handle internal transactions, but it did not cover a similar scenario later introduced with table types. I am still waiting on confirmation of whether that original bug should have ever been closed as fixed; I have suggested that all four be closed as by design. This is partly because it is kind of how I expected it to work, and partly because I get the sense from UC that "fixing" it to work in a different way is extremely complex, could break backward compatibility, and would be helpful in a very limited number of use cases. Nothing against you or your use case, but outside of test scenarios I'm not inclined to believe there is much value in this actually working.
UPDATE #2
I've blogged about this issue:
http://www.sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock


Error on DDL statement never invokes CATCH When I try to close the SS Management Studio window below is the error

I understand that DDL statements are implicit COMMIT and the below WARNING message is because BEGIN TRAN never COMMIT or ROLLBACK. Alter DDL statement aborted and never looped through CATCH block.
 Unable to understand why the control is not passed to CATCH block after the error (this does happen with any other DML statement)
Can you please explain why the statements in the CATCH never executed for this DDL error?

Why isn't it caught? Because TRY / CATCH doesn't catch all types of errors. But you can get it to catch the non-system-critical stuff by wrapping it in an EXEC(), such as:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY

The error in the ALTER will fail out to the EXEC, which in turn will return to the TRY / CATCH block reporting a simple, catchable error.

For more detail, the MSDN page for TRY...CATCH states:

  The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  Compile errors, such as syntax errors, that prevent a batch from running.
  Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

That second bullet point, about object name resolution errors, is what is causing the ALTER statement to fail and not be eligible to get handled by the CATCH block.

EDIT:
Regarding your statement about "DDL statements are implicit COMMIT": I am not sure what you mean by this exactly, but all (well, nearly all) queries are transactions by themselves. Meaning, single-query batches have no use for explicit BEGIN TRAN / COMMIT / ROLLBACK. If the statement fails it automatically rolls back, and if it succeeds then it automatically commits. You would only use the BEGIN TRAN / COMMIT / ROLLBACK construct if you want to conditionally rollback for some reason. It is still a good idea to use the TRY...CATCH structure to properly handle errors, but you gain nothing (at least with the code posted in the question) with the explicit transaction handling. There is absolutely no difference between the posted code and the following:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY
BEGIN CATCH
   SELECT 'I''m in Catch';
END CATCH;


Is a composite key correct here? I have two tables, Lpn and Uld that will relate on one column, that is, UldNo.

To give some background on the issue

An Lpn is a label that is assigned to one or more pieces of freight in a warehouse. A Uld is a pallet that the freight is built on so that it can go on an airplane. Every Uld has a unique identifier on it, consisting of a 3 letter type, 5 digit serial number, and 2 letter suffix that indicates the owner of the pallet. A Uld can come through a warehouse multiple times, and each time would have different Lpns (freight) on it.

To ask about the database

An Lpn row will only relate to one Uld row, and a Uld row may relate to multiple Lpn rows. The issue comes along is that UldNo cannot be the sole PRIMARY KEY (PK) for Uld because a Uld may be reused later (with different Lpns), so I created a PK of ID. If I try to create the relationship, I get an error because UldNo is not a PK. From everything I've learned, composite keys should be avoided. Is it correct to use a composite key in this situation? And if so, do I need to create the ID column in the Lpn table to create the relationship?

The following assumptions are correct:

On each flight, multiple pallets are transported.
Each pallet contains multiple LPNs (labels)
Pallets get reused.
LPNs (labels) are NOT reused.

The DBMS I'm using is SQL Server 2016 Express.

Maybe the following approach would be of help (the parent tables Uld and Flight should be created first; test server running PostgreSQL 9.3)

create table Uld ( uldno varchar(10) primary key);

create table flight (flightid varchar(32) primary key);

create table LPN (
  id integer primary key
, uldno varchar(10) references Uld(uldno)
, flight varchar(32) references Flight(flightid)
, unique (uldno, flight)
);

Test data:

insert into Flight values ('A'),('B'),('C'),('D');

insert into Uld values ('AAA12345ef'),('BBB12345gh')
, ('CCC12345ij'),('DDD12345kl');

insert into LPN values
(1, 'AAA12345ef' ,'A'),
(2, 'AAA12345ef' ,'B'),
(3, 'AAA12345ef' ,'C'),
(4, 'BBB12345gh' ,'A'),
(5, 'BBB12345gh' ,'D'),
(6, 'BBB12345gh' ,'C'),
(7, 'CCC12345ij' ,'A'),
(8, 'CCC12345ij' ,'B'),
(9, 'CCC12345ij' ,'C'),
(10, 'DDD12345kl' ,'A');

We get (something like):

+------------+
| table: Uld |
+------------+
| uldno [PK] |
+------------+
|AAA12345ef  |
|BBB12345gh  |
|CCC12345ij  |
|DDD12345kl  |
+------------+

+-------------------------------+
| table: LPN                    |
+-----------------+-------------+
| id | uldno [FK] | flight [FK] |
+-----------------+-------------+
|1   | AAA12345ef |A            |
|2   | AAA12345ef |B            |
|3   | AAA12345ef |C            |
|4   | BBB12345gh |A            |
|5   | BBB12345gh |D            |
|6   | BBB12345gh |C            |
|7   | CCC12345ij |A            |
|8   | CCC12345ij |B            |
|9   | CCC12345ij |C            |
|10  | DDD12345kl |A            |
+-----------------+-------------+

+---------------+
| table: Flight |
+---------------+
| flightid [PK] |
+---------------+
|A              |
|B              |
|C              |
|D              |
+---------------+

Testing:

-- eg which Ulds are on flight A?
select uldno
from LPN
where flight = 'A';

The data types for LPN.id and Flight.flightid should be adjusted as per requirements.  This layout should also work if there are several LPNs per uldno (if they have the same flightid in the LPN table).
It may aid your understanding of "keys" if you (also) regard them as "constraints" (~ rules that define certain limitations see eg https://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT322).


PostGIS vs. SQL Server for GIS data So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue with this, we are a 95% SQL Server and 5% Oracle shop. Our current internal GIS runs off SQL Server and I have yet to hear any complaints.

I know SQL Server has a lot of improved spatial / geometric capabilities as of 2012, but are there any killer features in PostGIS that are worth breaking into the new platform for? I've tried to research it but can't find anything truly in depth or that isn't completely bias.

I want to give them the best tools to get their work done, but also have to weigh the fact that I'll be learning Postgres/GIS from the start and that's an entire journey in and of itself.

I've worked with both Postgres and SQL Server. I found Postgres to be superior in GIS functionality. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. For example, maybe a 2 week time period to install and learn some specific functionality that is currently in use. If you find that you are stuck or lack functionality within that time period, then you know it's not for you. It's a investment in research that broadens your view and helps you realize you may have been missing something that you were unaware of before, or simply confirm your current course is the right now.

As far as the database goes, I found Postgres to have a shorter, and more shallow, learning curve. The documentation is just incredible. SQL Server does have quite a bit of documentation, but I find a lot of it hard to read, with not enough examples and tutorials.

PostGIS vs SQL Server Spatial is similar to the above regarding documentation, but PostGIS beats the pants off SQL Server Spatial in functionality. For example, Google Maps, and to a lesser degree Bing Maps, has recently added full geoJSON support to their maps API. Well, PostGIS can easily return a geoJSON result directly from a database query using ST_AsGeoJSON(). This geoJSON result can then be passed directly to whatever can understand geoJSON. SQL Server requires you to use additional library and processing, or use ogr2ogr. In addition PostGIS has over 300 functions available for data conversion into and out of the database, compared to SQL Server which has around 70-100.


Sync & Async Synchronisation on SQL Server 2012 Is it possible to set up async synchronisation (high-performance) on MS SQL Server 2012 globally, but somehow force synchronous replication for one table between nodes?

Nodes are in different subnets/continents.

Thank you in advance.

Is it possible to set up async synchronisation (high-performance) on MS SQL Server 2012 globally

Yes, with AlwaysOn Availability Groups having commit mode set to asynchronous commit for a replica.  That'll be by-database, so you'll have to group the databases into the AG that you want to see that happen with.

  but somehow force synchronous replication for one table between nodes

It really depends on your definition of "synchronous", but in the truest of definitions then no, you can't have synchronous commit for individual tables.  It'd have to be by database/availability group.


Additional info on some rows in the table I am trying to design an MS Access 2013 database to keep track of an inventory of items.

All items have certain common properties, like name, date they were stored, location of the physical item, etc.

The items fit into broad categories. The category is reflected in the item name.

Most items have a standardized name, made up of three letters and a positive integer. The letters indicate the category, the number is a serial number within that category. For example, PEN6' is item #6 in the "pens" category,LTR6` is item 6 in the "letters" category.

There is a small number of items which have very non-standard names like `Bob's letter to his friend'. Their category cannot be easily determined by the name, but they still belong to some definite category (recorded elsewhere).

There are some kinds of data that are only relevant for some categories. For example, "From" and "To" need to be stored for all LTR, but not for PENs. "Color" needs to be stored for pens, but is irrelevant for letters.

How can I store this additional info? I came up with two possibilities, but both have issues I couldn't resolve.

Solution #1: I make separate table for each category: An LTR table, a PEN table, etc. Each table has whatever fields are desired for that table. But when adding a new item, how do I check that the name is unique within the whole database? Because of how I chose my name format, most of the time this is already guaranteed, but for every non-standard name I would have to do a database-wide query to make sure it doesn't already exist.

Solution #2: I make a single table for all items. For each category, I project this database into a new table showing only the category I want (how?). The new table then has the particular fields of that category. When rows of this projected table are edited, the change is propagated back to the original table (if relevant, e.g. for date). When a row is added to the projected table, the corresponding row is also added to the original table (How?).

It sounds like your categories are a case of the subtypes pattern.  Accordingly, I've added the tag subtypes to your question.  There are two design patterns that you may want to look into.

The first one is called "Single Table Inheritance".  This design involves collecting all the subtypes (or, if you prefer, subclasses) into one table, and leaving values missing (NULL) in places where they do not pertain.  Views can be used to project this table onto subclass virtual tables.  You generally need a "category" field to make the projections.  It's simple, but it doesn't address all the problems.

The second one is called "Class Table inheritance".  This design involves creating one table for the superclass and one table for each subclass.  Attributes are placed in the appropriate table.  Often, the PK for the subclass table is a copy of the PK for the superclass table.  This enforces the one-to-one nature of the IS-A relationship, and provides for easy joins. Views can be used to join each subclass table with the relevant subset of the superclass table.

Your solutions #2 and #1 are somewhat like these two design patterns.

If you want to look up more detail on the web,  I suggest searching for Martin Fowler's presentation of these two techniques.


Help with removing duplicate reversed pairs in relational algebra I have a listing of data that contains duplicate reversed pairs and I need to remove them.. Call them, name1 and name2. Where I have Tom and Mike, and Mike and Tom, such that this single pair is being counted twice.

 name1 | name2
-------|-------
 Tom   | Mike
 Pete  | Jenny
 Bill  | Jenny
 Joe   | Mary
 Mike  | Tom
 Jenny | Pete
 Jenny | Bill
 Mary  | Joe
 Linda | Jenny

The List was a product of an initial match of student with guidance counselor,  then the product of the student/counselor and counselor table, which resulted in a longer list and was able to reduce to the pairs above. But, now can't get rid of the duplicates.

While I might have made a mistake in combing the tables that made the pairing, I am stuck with that table for now.. This is a listing of student paired with guidance counselors.

Is there a way to de-dup the list, or do I need to start over?

Do you need to return an existing combination, e.g. if there's only Tom,Mike do you need to return exactly this or is Mike,Tom also ok?

-- order of columns doesn't matter
SELECT DISTINCT
   CASE WHEN name1 > name2 THEN name2 ELSE name1 END as name1,
   CASE WHEN name1 < name2 THEN name2 ELSE name1 END as name2
FROM tab;

-- order of columns is maintained
SELECT DISTINCT name1,name2 -- DISTINCT might not be needed
FROM tab AS t1
WHERE NOT EXISTS(
  SELECT * FROM tab AS t2
  WHERE t1.name1 = t2.name2
    AND t1.name2 = t2.name1
    AND t1.name1 > t2.name1)
;

See fiddle

If you want to delete those rows you might us the 2nd logic, the actual syntax depends on your DBMS:

DELETE --change to SELECT * to see which rows will be deleted
FROM tab AS t1
WHERE EXISTS(
  SELECT * FROM tab AS t2
  WHERE t1.name1 = t2.name2
    AND t1.name2 = t2.name1
    AND t1.name1 > t2.name1)
;


Restore or reset Oracle database (No Flashback features) I am running

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Seem it not Enterprise edition, so I not able to use flashback features

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

SQL> SELECT flashback_on, log_mode FROM v$database;
FLASHBACK_ON |LOG_MODE
-------------|-------------
NO           |NOARCHIVELOG

As you see my database are running with NOARCHIVELOG mode and my filesystem is ext3

I had to do CRUD test with my system + another vendor. and after that I want to revert back  to the state of "restore point" (before testing). In short word, I don't want to keep test data inside my database.

There is discussion here, but this is using flashback features. But how about for my case?

Is there any solution suitable for me?

If takeing a rman backup, remember that is has to be an offline backup. Expdp/impdp is an option only if your data is not too big.
You could do filesystem/VM snapshoting depending on your environment.

If your data is of any value to you, please activate the archivelog mode and take consistent backups of your database. The database does it‘s best to safeguard your data please don‘t sabotage the.effort by not takeing backups.


Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server I just found that on the SQL Server 2012 MSDN documentation for binary and varbinary types, they mention the following at the end of the article:

Conversions between any data type and the binary data types are not
guaranteed to be the same between versions of SQL Server.

I noticed that this does not exist in the previous versions of the article for 2005, 2008, and 2008 R2. I searched around online but didn't find any exact explanations that satisfied my question of "WHY?". (Which is the question I have here.)
The project I am working on needs to store data as varbinary(max), but based on this little note, I am afraid if I need to move the binary data and then convert it on another SQL Server version that it will not match the original data converted to binary, so answering this question more in depth would be a great help.
Thanks!

Microsoft reserves the right to change the binary representation of the build-in data types like DATETIME. So if you do an

INSERT dbo.table(binaryColumn)VALUES(CAST(GETDATE() AS VARBINARY(8)));

and then you upgrade SQL Server or even do an SP apply

SELECT CAST(binaryColumn AS DATETIME) FROM dbo.table;

might get you a different date then the one originally stored.

However, if you store you own binary data that was generated outside of SQL Server (e.g. a pdf file), that data will not be affected by such a change.

This by the way is not new. Microsoft always said that they might change the binary representation at any time. They might just have made it more obvious in the new BOL version.

That all being said, it is still very unlikely that Microsoft is going to change the binary representation of say an integer. However, something like a geospatial, which is implemented as a CLR type, is very likely to change between versions.

As per Paul's comment: SQL Server does indeed guarantee that you can convert a build-in data type to binary and back within the same statement. To extend that to the same session when storing the intermediate value in tempdb however I would already consider risky - a very small risk, but a risk. Storing the intermediate value in a real table to pick it back up at a later time is a definite no-no.