computed column as foreign key allow null

You cannot create the contraint on A.actual_id because it may contains NULLs and there is no PK or unique index.
I didn't touch B because you said you cannot change it.

  Here is what I did. It may work for you:

Create A_data where <> 0, Create A_null when = 0 and your Table B

Create Table dbo.A_data (
    full_id INTEGER NOT NULL PRIMARY KEY,
    actual_id  AS (CASE WHEN (full_id & 0x0F000000) <> 0 THEN full_id &  0x00FFFFFF ELSE NULL END) PERSISTED
)
Create Table dbo.A_null (full_id INTEGER NOT NULL PRIMARY KEY)
Create Table dbo.B(actual_id int not null)

I can now create a unique index on A_data and a contraints on B which only contains not null value

Create UNIQUE INDEX idx_A_data On dbo.A_data(actual_id)
Go
ALTER Table B WITH CHECK ADD CONSTRAINT fk_a_actual_id FOREIGN KEY(actual_id) REFERENCES A_data(actual_id)
Go

I then create a view A which look like your table A

Create View A with schemabinding as
    Select full_id, actual_id From dbo.A_data
    union all
    Select full_id, null From dbo.A_null

I finally create a instead of trigger on the view. It will give you the same insert possibilities than your old table A

Create Trigger triggerA on A Instead Of Insert
As
    Insert Into A_data(full_id)
    select full_id from inserted where (full_id & 0x0F000000) <> 0

    Insert Into A_null(full_id)
    select full_id from inserted where (full_id & 0x0F000000) = 0

You may also have to create Instead Of Update and Instead Of Delete triggers if required. It is better to keep them separated (1 trigger for each task: insert, delete, update)

  Test

     insert into A(full_id)values(0),(1),(2),(286331153)

0, 1 and 2 go to A_null because (full_id & 0x0F000000) = 0 and 286331153 goes to A_data

A contains:

286331153 / 1118481
0 / NULL
1 / NULL
2 / NULL


Database backup and restore incase Always Encrypted feature is enabled

I tested with "Window Certificate Store - Current User" and "Windows Certificate Store - Local machine (localhost) and concluded to.

Existence of any object related to Always Encrypted features (master key, column master key and the certificates used to create the master key) is limited to the database only.

So simple backup and restore process works fine.

Thanks!


Migrating PostgreSQL files to new server

Install the latest minor release of the same PostgreSQL major version - e.g. if you were using 9.3.1 before, install 9.3.9. Make sure it's the same architecture - i.e. if you were using a 32-bit PostgreSQL before you must install 32-bit PostgreSQL now.

Stop the service using the services control panel (services.msc).
Delete %PROGRAMFILES%\PostgreSQL\9.3\data.
Copy the old data directory to that location.
Get properties on the data directory and in the security tab recursively grant full control to NETWORKSERVICE.
Start the service using services.msc
immediately set up backups.


When a cube is "materialized", all combinations of aggregates over all dimensions are computed and stored.. the WITH CUBE operator does exactly this..

http://msdn.microsoft.com/en-us/library/ms175939%28v=sql.90%29.aspx


Distributed Availability Group with Manual Seeding

TL;DR:

It sounds like from the current description and comments that you may have added databases to the AG on the forward before joining the DAG. Instead, join the DAG first and then add the databases in the following order:

Create AG1

Add Databases to AG1

Create AG2 (no databases)
Create the DAG

Join AGs 1 & 2 to the DAG from AG1
Join AGs 1 & 2 to the DAG from AG2

Add databases to AG2
Profit?

Long(er) form answer

Assuming lots of things like...

Listeners/Cluster/Instances are already configured
There is already an AG...

on both the PRIMARY and the FORWARDER
that is not the same AG
and neither are already a member of another DAG
and the FORWARDER AG is empty and ready to seed

You have shared storage that is accessible from both replicas

... you can... The following scripts are in sqlcmd format.

Step 0. Disable log backups on |OLD_AG| (optional)

You can ignore this step if:

If you don't mind a potentially "moving target" LSN at the very end of the process or
You can complete the entire process inside the log backup window of all databases in the AG

Step 1. Create the DAG

CREATE once on the current primary and ALTER ... JOIN on the prospective forwarder. Execute as an appropriate service account so you don't end up with parts of the architecture owned by your user account.

On the current PRIMARY...

:connect |OLD_AG|.|DOMAIN|
execute as login = 'sa'

-- double check local replica is manual seeding first
alter availability group [|OLD_AG|]
    modify replica on '|THIS_REPLICA|'
    with (seeding_mode = manual);

create availability group [|DAG_X|]
    with (distributed)
    availability group on
        '|OLD_AG|' with (
            listener_url      = 'TCP://|OLD_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual
        ),
        '|NEW_AG|' with (
            listener_url      = 'TCP://|NEW_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual
        );
go

On the prospective FORWARDER...

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

alter availability group [|DAG_X|]
    join availability group on
        '|OLD_AG|' with (
            listener_url      = 'TCP://|OLD_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual
        ),
        '|NEW_AG|' with (
            listener_url      = 'TCP://|NEW_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual
        );
go

Step 2. Full Backups

Did you know you can append log backups to copy_only fulls? Neither did I until very recently! But by using copy_only here you can

protect your backup chain from any migration tomfoolery and
reduce the length of the restore chain at the end of the process.

exec as... is not strictly necessary for this step.

:connect |OLD_AG|.|DOMAIN|

backup database DB1 to disk = N'\\my.shared.storage\backups\DB1.bak'
    with copy_only, compression;
backup database DB2 to disk = N'\\my.shared.storage\backups\DB2.bak'
    with copy_only, compression;
go

Step 3. Restore

Once again, executing as an appropriate service account.

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

restore database DB1 from disk = N'\\my.shared.storage\backups\DB1.bak'
    with norecovery;
restore database DB2 from disk = N'\\my.shared.storage\backups\DB2.bak'
    with norecovery;
go

Step 3(b). Did you leave the log backups on?

Nbd, but append them now ¯\_(ツ)_/¯

Executing as an appropriate service account
with norecovery

Step 4. On the Forwarder, join DBs to new AG

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

alter database DB1 set hadr availability group = [|NEW_AG|];
alter database DB2 set hadr availability group = [|NEW_AG|];
go


OEM (Oracle Enterprise Manager or 'cloud control') is licensed under your Enterprise licence agreement. See:

Licensing of Oracle Enterprise Manager Cloud Control by Markus Flechtner
https://community.oracle.com/thread/4197719

So you need to install the OMS (Oracle Management Server) on one server, and an agent on each server that you want to to monitor databases or middleware like Weblogic.  You can use a single OEM installation to monitor your entire enterprise, irrespective of where the hosts are.

Sometimes, due to firewall complications, organizations choose to have more than one OEM installation for ease of use, and sometimes as a backup in case OEM goes down.

However, Oracle licensing is complex and all queries are best handled by your Oracle account manager.


Replication Server version 15.7.1 - Sybase ASE 15.5 to Oracle EE 12.1.0.2 (64 bit) replication failing

By default the repagent (in Sybase DB Y) will not replicate maintenance user transactions (ie, transactions replicated from Sybase DB X to Sybase DB Y).

To get the repagent (Sybase DB Y) to also replicate maintenance transactions (ie, transactions from Sybase DB X, once applied in Sybase DB Y, are then re-replicated to Oracle), you'll need to configure the Sybase DB Y repagent:

use <Sybase_DB_Y>
go
sp_config_rep_agent <Sybase_DB_Y>,'send maint xacts to replicate',true
go
sp_stop_rep_agent <Sybase_DB_Y>
go
-- wait for repagent to go down and then ...
sp_start_rep_agent <Sybase_DB_Y>
go


In simple recovery mode the log gets truncated automatically when checkpoint is issued.  Now when you run DBCC SQLPERF(LOGSPACE) and your log is more than 70% full then you can assume that your log is not getting truncated because a checkpoint is supposed to be issued when the log grows to 70% in simple recovery.

If you suspect that your log is not getting truncated in simple recovery you can use sys.databases, log_reuse_wait_desc columns to find out why the last log truncation did not work.

If you wanna dig a bit deeper, you can use dbcc loginfo and check the status column to see which virtual log records are active and in that way figure out if its getting truncated or not


You can see the role of this aggregate if no rows match the WHERE clause.

SELECT MAX(Revision)
FROM   dbo.TheOneders
WHERE  Id = 1
       AND 1 = 1 /*To avoid auto parameterisation*/
       AND Id%3 = 4  /*always false*/

In that case zero rows go into the aggregate but it still emits one as the correct semantics are to return NULL in this case.

This is a scalar aggregate as opposed to a vector one.

Your "logically equivalent" query is not equivalent. Adding GROUP BY Id would make it a vector aggregate and then the correct behaviour would be to return no rows.

See Fun with Scalar and Vector Aggregates for more about this.


The question might be better stated as:

  "What is the advantage of over-specifying the maximum length of a variable-length column?"

In general, there is little advantage, and several disadvantages as the various linked answers point out.  Aside from any other concerns, consider that SQL Server is not open-source: there are many 'magic numbers' and heuristics applied based on the information provided to the system.  Without source code access, we could never be entirely sure what the impact of this practice might be.

In some cases, where the average length of a column is significantly higher than the 50% assumed by SQL Server when calculating sort/hash memory grants, you may see a performance improvement by over-specifying the maximum length.  This is a dubious workaround, and should probably only be applied by an explicit CAST or CONVERT (with comments!) rather than changing the base column definition.  Sometimes, it will be preferable to rewrite the query to sort keys instead of whole rows anyway.

Where the maximum row size might exceed the in-row limit (even if no rows actually do), deleting rows can cause unexpected page-splitting if a trigger is present.  Updates may also lead to fragmentation via the same mechanism.

SQL Server does a pretty good job in most cases where it is provided with good, accurate information from metadata.  Compromising this principle for 'convenience' seems unwise to me.  A sensible approach is to choose a maximum length value that is reasonable according to the actual data to be stored, and any foreseeable changes.


Table OIDs are assigned quite early in table creation, and are definitely present by the time the CREATE TABLE finishes (before xact commit), since the row is visible in pg_class by then.

The new pg_class row (and its oid) are not visible to concurrent transactions until commit, though.

It's not clear why you care, though. It shouldn't matter.


5.5.14 and 5.6.3 and 5.7 have innodb_file_format=Barracuda as an option.

Prior to 5.7.7, do these:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;

In 5.7.7 and after, most of that is defaulted as you need.

DYNAMIC is the format you need -- it leaves only 22 bytes in the main part of the row (which is limited to about 8KB), and puts the rest in overflow blocks.  This applies to VARCHAR, TEXT, VARBINARY and BLOB.  22*156 is well under 8K.  "When the row is too long, InnoDB chooses the longest columns for off-page storage."  That is, some of the shorter columns for a given row may still reside 'on-page'.

I think the "22" is a 2-byte length, plus a 20-byte "pointer" to the data.  That is, none of the data is stored 'on-page', and the 768 is not relevant.

Another possibility (but I don't recommend it) is to increase the default page size from 16KB to 32KB, thereby increasing the max from about 8K to about 16K.  (There is a 64KB page size, but the limit is still 16KB for each row.)

Also, COMPRESSED can be used instead of DYNAMIC, but again, I don't recommend it, especially before 5.7.  ("Compressed = Dynamic plus compression.")

Splitting the table ("vertical partitioning") is a good idea.

MyISAM is a bad idea.

Checking to see if you really need 1000 is a good idea.


at least one member of a list within another list when querying over a join

SELECT DISTINCT
   a.id,
   a.etc
FROM years y
JOIN activitymapper am
   ON (y.targetyearid = am.targetyearid)
JOIN activity a
   ON (a.id = am.activityid)
WHERE y.targetyearname IN ('Year1', 'Year2')

Should do it. We're going from years to activity through activitymapper, selecting each activity only once (DISTINCT).


Passing parameters in MySql procedure with prepare execute

Modify your SP for to look at the SQL text built:
fiddle
It is clear that you forget to wrap string literal value with single quotes. You must use
SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
 FROM ( SELECT current_Price as price
        FROM UNION_SALES
        WHERE location = ''', loc,'''
          and current_Price >5000
        ORDER BY current_Price ASC LIMIT ?) as prices;');

location can be used for SQL injection. You could fix it either by using a bound parameter (preferred), or by using QUOTE(location).


ORDER BY ASC is 300x faster than ORDER BY DESC

I think the problem is two-fold (or 3-fold):

missing composite index on both columns filtered: (x, inserted_at)
uneven distribution
old statistics

   Filter: (a.x = 'some_x_val'::uuid)
   Rows Removed by Filter: 2745418

The unusual distribution means that the single index scan has to do a lot of work to remove the rows that don't match the 2nd filter (by the other (x) column). The index scan for ORDER BY ASC is just lucky and finds the 20 rows a bit more quickly, only by luck. It could be the other way around with other values in the parameters of inserted_at and x

So the best way to solve this is to add this composite index. You could follow the advice on the linked question and answer by Erwin, to update statistics on the table but this may help with the issue or not. It's not certain.


FILEPROPERTY not working over linked server to Azure SQL Database

Please note that logs are excluded for the purposes of determining database size in Azure SQL Database, that is the reason you receive NULL as result. You should add the following WHERE clause to your query.

WHERE type_desc = 'ROWS';

For example, the following query will work and won't return nulls.

SELECT
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)
AS DatabaseSizeInBytes,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024 /1024
AS DatabaseSizeInMB,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024/1024/1024
AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';


In what direction SQL Server WHERE clause conditions are checked?

I don't think there are any guarantees for evaluation order. The optimizer will try to find the most efficient way to execute the query using available information and each invocation of the query may change depending on what the optimizer thinks is the best plan right now.

If you MUST exclude some rows before evaluating other rows, I think you'll have to materialize the first result set (possibly in a temp table) and then run additional queries against that materialized result set.

To get around your CASTing error, you could use TRY_CAST

DROP TABLE IF EXISTS Table1
GO
CREATE TABLE Table1 (
    RcodeID INT
    ,RCode VARCHAR(6)
    );

INSERT INTO Table1 (RcodeID,RCode)
VALUES (1,'342'),(2,'232,23'),(3,'323xs');

DECLARE @FROM INT = 300
DECLARE @TO INT = 400

SELECT RCodeID AS 'ListItemID'
    ,RCode AS 'ListItemName'
FROM table1
WHERE RCode NOT LIKE '%,%'
    AND ISNUMERIC(RCode) = 1
    AND TRY_CAST(RCode as INT) >= CAST(@From AS INT)
    AND TRY_CAST(RCode as INT) <= CAST(@To AS INT)
ORDER BY ListItemName;

| ListItemID | ListItemName |
|------------|--------------|
| 1          | 342          |


To answer your questions:

Year number is part of a (three column) composite primary key.  There will be one record per employee + year + type of leave (vacation/sick/...)  Year number probably means the calendar year stored as an integer.
der_leave_cumulative_days_taken means the total of leave actually taken, which would be derived by summing the days between Employee_Leave_Taken.leave_from_date and Employee_Leave_Taken.leave_to_date where those dates fall within the year given by Employee_Annual_Leave.year_number for the same employee_id and leave_type_code.
der_leave_days_remaining is next to pointless because it would be Employee_Annual_Leave.leave_days_allowed less der_leave_cumulative_days_taken.  A real scenario would be that an employee is allowed three weeks vacation (15 days) and has taken two weeks so far (der_leave_cumulative_days_taken=10 days) and so they have 5 days remaining.  The point of keeping these precalculated in this way is that you can refer easily to these values in a query or report.  Notice the date_updated field.  This implies that there is a regular (perhaps scheduled) batch job that calculates these derived values periodically, or perhaps they are recalculated when changes are made to either leave allowed or leave taken.


PostgreSQL's record type returning null?

... then the inserted row is returned.

It's safe to assume there are multiple fields in that returned row. If at least one additional field is NULL, then the row value is neither NULL nor NOT NULL. The manual:

If the expression is row-valued, then IS NULL is true when
the row expression itself is null or when all the row's fields are
null, while IS NOT NULL is true when the row expression itself is
non-null and all the row's fields are non-null. Because of this
behavior, IS NULL and IS NOT NULL do not always return inverse
results for row-valued expressions; in particular, a row-valued
expression that contains both null and non-null fields will return
false for both tests.

Bold emphasis mine.
db<>fiddle here
Related:

NOT NULL constraint over a set of columns
(x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL


Can I use impdp FULL=Y for an Oracle database charset conversion?

On export the documentation states:

A full export does not, by default, export system schemas that contain Oracle-managed data
and metadata. Examples of system schemas that are not exported by default include
SYS, ORDSYS, and MDSYS.

If you want to be absolutely sure I would switch to a schema-mode import instead. You can still use the full export. That way you specify what schemas you want to import.

Regards,
Daniel


How to merge multiple rows into one row with SQL?

This is a classic case of a pivot operation in SQL. The method working in, probably, most (if not all) SQL products, including SQL Server, is conditional aggregation:

SELECT
  UserID,
  Year,
  M1  = SUM(CASE Mth WHEN  1 THEN Value END),
  M2  = SUM(CASE Mth WHEN  2 THEN Value END),
  M3  = SUM(CASE Mth WHEN  3 THEN Value END),
  M4  = SUM(CASE Mth WHEN  4 THEN Value END),
  M5  = SUM(CASE Mth WHEN  5 THEN Value END),
  M6  = SUM(CASE Mth WHEN  6 THEN Value END),
  M7  = SUM(CASE Mth WHEN  7 THEN Value END),
  M8  = SUM(CASE Mth WHEN  8 THEN Value END),
  M9  = SUM(CASE Mth WHEN  9 THEN Value END),
  M10 = SUM(CASE Mth WHEN 10 THEN Value END),
  M11 = SUM(CASE Mth WHEN 11 THEN Value END),
  M12 = SUM(CASE Mth WHEN 12 THEN Value END)
FROM
  dbo.YourTable
GROUP BY
  UserID,
  Year
;

SQL Server also supports the PIVOT operator (since the 2005 version). I can see two distinct approaches using PIVOT here. One is probably more straightforward:

SELECT
  UserID,
  Year,
  M1  =  [1],
  M2  =  [2],
  M3  =  [3],
  M4  =  [4],
  M5  =  [5],
  M6  =  [6],
  M7  =  [7],
  M8  =  [8],
  M9  =  [9],
  M10 = [10],
  M11 = [11],
  M12 = [12]
FROM
  dbo.YourTable
PIVOT
  (
    SUM(Value)
    FOR Mth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
  ) AS p
;

Note that the above method assumes your table has no other columns apart from those shown in your example (UserID, Year, Mth, Value). If there are other columns, replace dbo.YourTable with a derived table pulling only those four columns:

  ...
FROM
  (
    SELECT
      UserID,
      Year,
      Mth,
      Value
    FROM
      dbo.YourTable
  ) AS s
PIVOT
  ...

The other, slightly less obvious, option would be to use a derived table to construct the final Mn columns and thus avoid the renaming in the main SELECT:

SELECT
  UserID,
  Year,
  M1,
  M2,
  M3,
  M4,
  M5,
  M6,
  M7,
  M8,
  M9,
  M10,
  M11,
  M12
FROM
  (
    SELECT
      UserID,
      Year,
      Mth = 'M' + CAST(Mth AS varchar(11)),
      Value
    FROM
      dbo.YourTable
  ) AS s
PIVOT
  (
    SUM(Value)
    FOR Mth IN (M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12)
  )
  UserID,
  Year
;

In this case you could also use just * in the main SELECT instead of the explicit column list.


Try setting the "Collation Compatible" to True on the Linked Server definition.

If you set it to False, it will pull the entire table down and do the compare on the receiving end. Consider the scenario where the local server is case insensitive, and the remote server is case sensitive, the results will differ based on where the compare is done.


Why is vacuum at a critical level in PostgreSQL when I'm running autovacuum?

check_postgres isn't very sophisticated software. It just checks if a particular table was vacuumed in the last 2 days (or as configured). For tables with no updates or deletes it is perfectly normal to not be vacuumed for extended periods of time. You can either ignore this or configure check_postgres to ignore this particular table.

I don't think this functionality of check_postgres is of much use. Maybe for some particular tables. Definitely not so much for system tables like pg_catalog.pg_shdepend.


Function in same Session become increasingly slow when run multiple times concurrently

Since the problem are bad generic plans (symptom: execution becomes slower from the sixth execution on) and you are using PostgreSQL v12, the solution is simple:
ALTER FUNCTION xy SET plan_cache_mode = force_custom_plan;


If the Fixed Assets and Consumables have some identical columns, they can be in one table. Then create a category like table.


Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name

Ah-ha!! I figured out what I did wrong. In my first attempts, I was doing all the work from the existing primary (SQL2014OnSite), and when I removed it from the AG, I must have done it in such a way that it blew away the AG. This time, after failing over to the new primary node (SQL2016OnSite), I made the change on this instance by right-clicking the AG, choosing Properties, and in that window, highlighted SQL2014OnSite, and clicked the Remove button. It has now been running in the new configuration, which is SQL2016OnSite as primary and SQL2016OffSite as secondary, for about 15 minutes after removing SQL2014OnSite from the AG, and...so far, so good. I think that was my error: performing the "after-migration" work from the 2014 instance instead of the 2016 instance.
Still, if anyone has any insights or recommendations for a better way to do this, by all means, please suggest away.


Benefits of using backtick (`) in MySQL queries?

They are called quoted identifiers and they tell the parser to handle the text between them as a literal string. They are useful for when you have a column or table that contains a keyword or space. For instance the following would not work:

CREATE TABLE my table (id INT);

But the following would:

CREATE TABLE `my table` (id INT);

Also, the following would get an error, because COUNT is a reserved keyword:

SELECT count FROM some_table

But the following would be parsed correctly:

SELECT `count` FROM some_table

I hope this helps you.


Filling in missing dates with values from the month before


Transactional Replication: When to delete the initial snapshot

You dont have to manually cleanup the old/static snapshot files, the distribution cleanup agent will purge the old folders for you.

A bit of under the hood story of what and how snapshot files gets generated :

Basically, the snapshot agent creates snapshots for the subscribers to initialize. The snapshot folder will contain various types of files like -

.bcp => actual data files - either using native or character mode
.idx => Index creation statements
.sch => schema and table creation scripts
.dri => foreign and check constraints

These files are always kept in \\unc\folder with a timestamp value for the folder name. The folder name is the publication name.

So, everytime a snapshot is generated, a new timestamp value is used for the folder name and then the snapshot files are written to that folder.

Also, if you see that your distribution agent is not cleaning up the files, you can delete the older snapshot folders. But I would still troubleshoot - why distribution agent is not cleaning it up !!


A Google of "open source subscription software rent a bike" gives a number of interesting results, including this one. You can take and adapt ideas from some of these programmes - the beauty of Open Source.

Just a couple of thoughts about the ERD. Why include colour - that can readily change - and is it really relevant? I mean, how many people care about the colour of a hire bike? Here in Dublin, they are the same colour and have a uniform appearance - I think that this "branding" contributed in some way to the success of the scheme. You also have size - again, here in Dublin, a similar scheme uses 1 size (adjustable).

All of your bicycle attributes (top left of ERD) should be in 1 table with PK Bicycle_ID - model, make, size and colour follow logically from the Bicycle_ID.

Also, one thing that struck me was this GPS stuff - are you sure that there aren't EU Data Protection issues with this? Has it been looked at by a lawyer competent in EU DP law? Finally, another thing about GPS - maybe you should have a GPS coordinate for the Terminals? People regularly now navigate using phones with GPS - they'll be able to find the nearest terminal more easily this way.


table design to track significant events associated with an item

Normalize always unless you have a very good reason not to. It should not be flipping a coin.

Neither of your proposed designs seems optimal to me for a variable / unknown number of events, since when "that number may increase or decrease," in both cases, you have to change the schema and the code.

I also don't believe that you have to base your core table design around the format of the input files. This is why we have staging tables, ETL processes, SSIS, C#, etc. There are many ways to transform data from flat files into a format that is more appropriate for a relational database. Don't design your database because of what your flat files look like.

So you could have staging tables that look like the input files, but then you would load the data into real tables that look perhaps like this:

CREATE TABLE dbo.Events -- lookup table describing events
(
  EventID INT PRIMARY KEY,
  Description VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.OrderEventLog -- actual event data
(
  EventID INT FOREIGN KEY REFERENCES dbo.Events(EventID),
  OrderID INT FOREIGN KEY REFERENCES dbo.Orders(OrderID), -- guess
  ExpectedDate DATETIME2(7),
  ActualDate DATETIME2(7)
);

Now when you have a new type of event, you just insert it into the dbo.Events table, and then you can insert related events into dbo.OrderEventLog without having to change the schema of either table, and without having to change the interface to your stored procedures or bulk inserts into the staging tables either. This also keeps the history table (and all of its indexes) nice and narrow, and allows you to build indexes that favor searches for specific types of events.

You can always flatten the data later - which is typically what you do when you get to a point where you are building a data warehouse around the data.


Data differences

With a low amount of records to seek on, the optimizer is able to use the SIX_T_pathXQUERY index:

and filter on moretext with a seek predicate:

An interesting part here is that it executes the key lookup to get the path_1_id values that are not null.

Since that is a filter definition on the nonclustered xml index...

...while not being present in the index itself.

As a result, for the optimizer to consider using the index it knows that it has to complete these steps:

Filter on the XML value with the secondary index on the internal
table
Match these returned values with the SIX_T clustered index on the
internal table and filter on path_1_id is not null since path_1_id is
not included in the secondary index
Match these values with the actual table, dbo.T to return the ID to count on

Tipping points

When the expected rows to be returned are higher, it favours using the selective clustered XML Index, to be able to run a merge join & no key lookup instead:

with a residual predicate:

To filter on the xml column and path_1_id

Comparing the plans

You could (but you shouldn't) use the USE PLAN hint to force the plan with the seek on the XML column and see what would happen if we where to search on these values.

With  the execution time =

   CPU time = 218 ms,  elapsed time = 215 ms.

And the execution time for the scan + merge join plan:

   CPU time = 62 ms,  elapsed time = 58 ms.

In short, I believe that the scan with residual predicate + merge join choice was the right choice by the optimizer.

No way around it

While I might be wrong, I don't think there is a way to improve the count query with regular XML Indexes.
We also cannot change these internal tables or even query them:

SELECT * FROM
[sys].[xml_sxi_table_1463676262_256000];

It even gives a missing index hint on the merge join query plan, which you cannot create:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [sys].[xml_sxi_table_1463676262_256000] ([pathXQUERY_1_value])
INCLUDE ([path_1_id])

To improve the queries, you would have to look into non-xml index solutions.

Edit

  How do i use USE PLAN hint? You have example code for this query?

I would advise against this, but for testing purposes it would be fine.

Step 1: You would have to get the actual execution plan of the query and get
    the xml:

If you don't have the query with the low estimates, enter a value that does not exist like:

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/SomeText[. eq "bbb"]') = 1

Step 2: Replace all 's with ''s in the execution plan xml, we will need this later.

Step 3: Paste the plan between OPTION( USE PLAN '')

SELECT count(*)
FROM dbo.T as T
WHERE T.XMLDoc.exist('/SomeText[. eq "MoreText"]') = 1
OPTION(USE PLAN
'');

Step 4: I had to change utf-16 to utf-8 to get the use plan hint to work

From:

OPTION(USE PLAN
'<?xml version="1.0" encoding="utf-16"?>

To:

OPTION(USE PLAN
'<?xml version="1.0" encoding="utf-8"?>

Step 5: Run the query.

My query now looks like this:

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/SomeText[. eq "MoreText"]') = 1
OPTION(USE PLAN
'<?xml version="1.0" encoding="utf-8"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.481" Build="14.0.3223.3" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.00986014" StatementText="select count(*)&#xD;&#xA;from dbo.T as T&#xD;&#xA;where T.XMLDoc.exist(''/SomeText[. eq &quot;bbb&quot;]'') = 1" StatementType="SELECT" QueryHash="0x412154B6AD55BBFC" QueryPlanHash="0x280B174BF20902E3" RetrievedFromCache="true" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="394" CompileCPU="301" CompileMemory="648">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419404" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="3655432" />
            <QueryTimeStats CpuTime="0" ElapsedTime="0" />
            <RelOp AvgRowSize="11" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00986014">
              <OutputList>
                <ColumnReference Column="Expr1017" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1017" />
                    <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1020],0)">
                      <Convert DataType="int" Style="0" Implicit="true">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1020" />
                          </Identifier>
                        </ScalarOperator>
                      </Convert>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="11" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.00986014">
                  <OutputList>
                    <ColumnReference Column="Expr1020" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                  </RunTimeInformation>
                  <StreamAggregate>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1020" />
                        <ScalarOperator ScalarString="Count(*)">
                          <Aggregate AggType="countstar" Distinct="false" />
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.00985904">
                      <OutputList />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                      </RunTimeInformation>
                      <StreamAggregate>
                        <DefinedValues />
                        <GroupBy>
                          <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                        </GroupBy>
                        <RelOp AvgRowSize="11" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985804">
                          <OutputList>
                            <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                          </RunTimeInformation>
                          <NestedLoops Optimized="false">
                            <OuterReferences>
                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                            </OuterReferences>
                            <RelOp AvgRowSize="16" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
                              <OutputList>
                                <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                              </RunTimeInformation>
                              <NestedLoops Optimized="false">
                                <OuterReferences>
                                  <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                  <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                </OuterReferences>
                                <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="150002">
                                  <OutputList>
                                    <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                    <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Index="[SIX_T_pathXQUERY]" Filtered="true" Alias="[SomeText:1]" IndexKind="SecondarySelectiveXML" Storage="RowStore" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pathXQUERY_1_value" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="N''bbb''">
                                                <Const ConstValue="N''bbb''" />
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                                <RelOp AvgRowSize="461" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="150002">
                                  <OutputList />
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                  </RunTimeInformation>
                                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues />
                                    <Object Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Index="[SIX_T]" Alias="[SomeText:1]" TableReferenceId="-1" IndexKind="SelectiveXML" Storage="RowStore" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[pk1] as [SomeText:1].[pk1]">
                                                <Identifier>
                                                  <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[row_id] as [SomeText:1].[row_id]">
                                                <Identifier>
                                                  <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="row_id" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                    <Predicate>
                                      <ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[path_1_id] as [SomeText:1].[path_1_id] IS NOT NULL">
                                        <Logical Operation="IS NOT NULL">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="path_1_id" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Logical>
                                      </ScalarOperator>
                                    </Predicate>
                                  </IndexScan>
                                </RelOp>
                              </NestedLoops>
                            </RelOp>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="200002">
                              <OutputList>
                                <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[adventureworks]" Schema="[dbo]" Table="[T]" Index="[PK__T__3214EC27EF7043C5]" Alias="[T]" IndexKind="Clustered" Storage="RowStore" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[adventureworks]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="ID" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="[adventureworks].[sys].[xml_sxi_table_1463676262_256000].[pk1] as [SomeText:1].[pk1]">
                                            <Identifier>
                                              <ColumnReference Database="[adventureworks]" Schema="[sys]" Table="[xml_sxi_table_1463676262_256000]" Alias="[SomeText:1]" Column="pk1" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                              </IndexScan>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                      </StreamAggregate>
                    </RelOp>
                  </StreamAggregate>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>');


If you have MYSQL installed and running on a Windows Server, you may want to look into BitLocker with TPM encryption plus PIN boot password. Even if the host OS is Windows but the CentOS is a VM, then this would suffice.

TrueCrypt
VeraCrypt

There's data-at-rest encryption so when it's at rest on a server disk partition not being accessed by some query, then using OS level full disk encryption such as BitLocker with TPM plus PIN boot password is fine per many regulations such as HITECH, HIPAA, etc.

Data-in-motion and Access Controls of who or what applications you allow to access the data is a different story but I'd look into something of that nature or equivalent for data-at-rest encryption specifically.


Is InnoDB Engine up to speed against Memory Engine?

It is ironic I answered a question earlier about InnoDB vs MEMORY storage engines.

There is something very weird about the MEMORY Storage Engine you must consider.

MEMORY tables perform full table locks each time there is an INSERT, UPDATE, and DELETE.

MEMORY tables still trigger a little disk I/O because the .frm file of the MEMORY table is a disk file that must be referenced with each query as far the table's existence goes and query parsing thereafter.

The default index type for a MEMORY table is the HASH index not BTREE. If you forget to declare USING BTREE, all range searches become table scans. HASH indexes are poor candidates for indexes to fulfill range queries. The query you have in the question body would quickly be victimized by this.

Even if you create indexing in the MEMORY table with the USING BTREE clause, BTREE indexes in RAM grow at a pace of O(log n) so expect disk I/O again for checking the index definition in the .frm file plus O(log n) running time on page access.

Another crazy thing to think about when using the MEMORY storage engine is this : If you try to join a MEMORY table and an InnoDB table, the resulting lock behavior defaults to the worst one, which in this case is full table locking.

CAVEAT

Others have answered question like this back in March 2011

David Splllett's answer to What are reasons *not* to use the MEMORY storage engine in MySQL?
Morgan Tocker's answer to What are reasons *not* to use the MEMORY storage engine in MySQL?

Here is one on why an all memory Database is good or bad : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?


Query gets slow when bind peeking is turned on

Bind peeking is done in stages. First the query is be flagged as "bind sensitive" based on histograms, and then it may become "bind aware". The IS_BIND_SENSITIVE and IS_BIND_AWARE columns in V$SQL will show the state of the query. If IS_BIND_AWARE is not on, the query is not adapting.

This post helps to explains the process.


The multi-part identifier "xxx" could not be bound

Reformatting your code shows the problem. All I have changed is whitespace and added a comment:

HAVING
    (
        SELECT COUNT(*) FROM
        (
            SELECT *
            FROM dbo.ContractDailyRoomAllocation da2 -- <-- da2 defined here
            WHERE da2.ContractId = DA.ContractId
            AND da2.RoomTypeId = DA.RoomTypeId
            AND da2.Date >= @FromDate AND da2.Date < @ToDate
            AND da2.IsSold = 0
            AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)
        ) T
    ) = @StayDates
AND
    (
        (
            @AllowOnRequestBookings = 'False' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) >= 0

        )
    )
AND
    (
        (
            @AllowOnRequestBookings = 'True' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) > 0
        )
    )

As can now easily be seen, da2 is an alias defined in the subquery in the first ANDd term, and so is not available in other terms.

It may be that you want to move the second and third ANDd terms here into the subquery, or it might be more complicated than that.


Query: How to join on different tables depending on the filled column?

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...]


When a database backup is restored it almost immediately allocates the space needed for the database files.  So a 300 GB database will quickly appear in the file system at full size, but the file will be essentially empty to start with.

Once the space is allocated, the restore process goes on to read the backup file restoring data at the speed sustainable by the network and the servers. Once the backup is complete, restoring all the backup files, handling roll backs and roll forwards, then the database will be available.

A few years ago we had a very big database with the backup files stores remotely that was going to take 12 hours to restore over the network.  So, in that case we cancelled the restore.  Then we copied the files locally (about 1 hour) and restored quite quickly after that.

Note that if you database backups are compressed, the backup file will restore faster since there is less network traffic.


SQL Server Core - Installed SQL Server features

One way of doing that would be to query the registry location containing all currently installed programs and then filter out all that do not contain SQL SERVER in the name.

$array = @()

$computername="MachineName"

#Define the variable to hold the location of Currently Installed Programs

$UninstallKey="SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall"

#Create an instance of the Registry Object and open the HKLM base key

$reg=[microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine',$computername)

#Drill down into the Uninstall key using the OpenSubKey Method

$regkey=$reg.OpenSubKey($UninstallKey)

#Retrieve an array of string that contain all the subkey names

$subkeys=$regkey.GetSubKeyNames()

#Open each Subkey and use GetValue Method to return the required values for each

foreach($key in $subkeys){

    $thisKey=$UninstallKey+"\\"+$key

    $thisSubKey=$reg.OpenSubKey($thisKey)

    $obj = New-Object PSObject

    $obj | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $computername

    $obj | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $($thisSubKey.GetValue("DisplayName"))

    $obj | Add-Member -MemberType NoteProperty -Name "DisplayVersion" -Value $($thisSubKey.GetValue("DisplayVersion"))

    $obj | Add-Member -MemberType NoteProperty -Name "InstallLocation" -Value $($thisSubKey.GetValue("InstallLocation"))

    $obj | Add-Member -MemberType NoteProperty -Name "Publisher" -Value $($thisSubKey.GetValue("Publisher"))

    $array += $obj

}

$array | Where-Object { $_.DisplayName -like "*SQL SERVER*" } | select ComputerName, DisplayName, DisplayVersion, Publisher | ft -auto

This script was taken from the Hey, Scripting Guy! Blog and I modified to include -like "*SQL SERVER*" and also removed the loop through multiple computers. There may be an easier way. I hope this helps.


While there's no messing with Mikael Eriksson when it comes to XML (he is, after all, my spiritual XML guide), here's an answer specific to your question.

You just have to change the way you're handling the XML a little bit. I also changed the XML a touch to rule out false positives searching for A and B in the node names to text 1 and text 2.

DECLARE @xmltable TABLE (xmlvalue XML)
INSERT @xmltable ( xmlvalue )
    VALUES ( '<root>text 1text 2</root>' )

DECLARE @searchstring VARCHAR(100) = 'A'

SELECT x.*,
xa.c.value('.', 'VARCHAR(100)') AS [weird]
FROM @xmltable AS x
CROSS APPLY x.xmlvalue.nodes('/root/*[local-name()=sql:variable("@searchstring")]') xa (c)

This returns text 1.

DECLARE @xmltable TABLE (xmlvalue XML)
INSERT @xmltable ( xmlvalue )
    VALUES ( '<root>text 1text 2</root>' )

    DECLARE @searchstring VARCHAR(100) = 'B'

    SELECT x.*,
    xa.c.value('.', 'VARCHAR(100)') AS [weird]
    FROM @xmltable AS x
    CROSS APPLY x.xmlvalue.nodes('/root/*[local-name()=sql:variable("@searchstring")]') xa (c)

This returns text 2.


Database placement on abstract storage structure

What I would do is to benchmark the performance before and after the changes. There should be a performance gain after tempdb is moved to another drive. Use DMVs like sys.dm_io_virtual_file_stats to see the read and write wait times for the DB files.

Use the perfmon physical disk counters: Avg. Disk sec/Read, Avg. Disk sec/Write, Disk Reads/sec, Disk Writes/sec. The change in these metrics will let you know if the disks are physically separate or are just the same disks which are logically separated.

Want a third party disk benchmarking tool for your disks, so that the SAN admins cannot accuse SQL server of its results? Use SQLIO. Although there is SQL in the name, this tool does not require SQL server to run and is not related to SQL server.

This is a good question, and I look forward to see the other answers.


In MySQL, how to execute two different SELECT statements depending on a condition?

SELECT *
FROM ( full text of query 1 )
WHERE (condition 1)
UNION ALL
SELECT *
FROM ( full text of query 2 )
WHERE (condition 2)
  AND NOT (condition 1)

Neither condition must give NULL (due to NULL in field/variable values) in NOT.

Outer SELECTs for each separate subquery in UNION may be excess if separate queries are not complex.

For the code shown in the question it will be

SET @input1=123;
SET @input2='foobar';

SELECT @result := col12
FROM   table1
WHERE  col11 = @input1
  AND  @input1 > 0

UNION ALL

SELECT @result := col21
FROM   table2
WHERE  col22=@input2
  AND  @input2 != ''
  AND  COALESCE(@input1, -1) <= 0; /* prevent ejecting by NULL */


Overcome LIKE character length limitation

It does not appear that this can be solved in pure T-SQL since neither CHARINDEX nor PATINDEX allow for using more than 8000 bytes in the "to search for" string (i.e. max of 8000 VARCHAR or 4000 NVARCHAR characters). This can be see in the following tests:

SELECT 1 WHERE CHARINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                         N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

SELECT 1 WHERE PATINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                        N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

Both of those queries return the following error:

  Msg 8152, Level 16, State 10, Line xxxxx
  String or binary data would be truncated.

And, reducing the 7000 in either of those queries down to 3999 gets rid of the error. A value of 4000 in both cases will also error (due to the extra N'Z' character at the beginning).

HOWEVER, this can be accomplished using SQLCLR. It is fairly simple to create a scalar function that accepts two input parameters of type NVARCHAR(MAX).

The following example illustrates this ability using the Free version of the SQL# SQLCLR library (which I created, but String_Contains is again available in the Free version :-).

SETUP

-- DROP TABLE #ContainsData;
CREATE TABLE #ContainsData
(
  ContainsDataID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Col1 NVARCHAR(MAX) NOT NULL
);

INSERT INTO #ContainsData ([Col1])
VALUES (N'Q' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15000)),
       (N'W' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 20000)),
       (N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 70000));

-- verify the lengths being over 8000
SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp;

TESTS

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15100)) = 1;
-- IDs returned: 2 and 3

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 26100)) = 1;
-- IDs returned: 3

Please keep in mind that String_Contains is using an everything-sensitive (case, accent, Kana, and width) comparison.


Does NTILE work over floating point numbers?

The NTILE  function breaks an input set down into N equal sized groups.  To determine how many rows belong in each group, SQL Server must first determine the total number of rows in the input set.

If the NTILE function includes a PARTITION BY clause, SQL Server must compute the number of rows in each partition separately.  Once we know the number of rows in each partition, we can write the NTILE function as

NTILE(N) := (N * (ROW_NUMBER() - 1) / COUNT(*)) + 1

where COUNT(*) is the number of rows in each partition.

Taking above into consideration, below will explain :

select *, (3*(RowNumber-1)/Cnt)+1 AS MyNTile
from
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Cash ORDER BY cash) AS RowNumber,
        COUNT(*) OVER (PARTITION BY Cash ) AS Cnt,
        NTILE(3) OVER (PARTITION BY Cash ORDER BY Cash) AS NTile,
        NTILE(3) OVER (ORDER BY Cash) AS WholeTableNtile

    FROM Test
) T

Refer to : Ranking Functions: RANK, DENSE_RANK, and NTILE


SQL Master Data Services database has huge leaf member staging table - can I purge old records?

In short: The official answer is to use the new proc mdm.udpEntityStagingBatchTableCleanup to clean up entity staging tables, but in practice, DELETE or TRUNCATE should work just fine (or might be necessary), as long as you understand your batch detail.

I found reference to a SQL 2012 patch that adds 3 stored procedures to MDS, including the one I need:

--Cleanup entity-based staging table
EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

See also this article for scheduled cleanup options utilizing the same procs.

For my purpose, though, I was still concerned about blowing up the tran log with the 300+ million rows that had to be deleted, so I looked in detail at the inner workings of mdm.udpEntityStagingBatchTableCleanup. This stored proc does the following:

Populates a temp table with all completed batches that match your deletion criteria (for the given @Model_ID, older than @CleanupOlderThanDate).
Builds dynamic DELETE statements for the corresponding Leaf, Consolidated, and Relationship tables for each DISTINCT Entity_ID in the temp table.
It executes each of those DELETE statements for each entity table, filtered via a join to the temp table.
It then deletes from mdm.tblStgBatch itself, again filtered by join to the temp table.

There is no attempt to limit the amount of rows deleted at once, and in my case,  the 300+ million rows are all for a single Entity_ID, which would likely blow up the transaction log.

The good news, though, is that if I intend to remove all Entity_IDs and all dates anyway (which I do), then that's functionally equivalent to a TRUNCATE,
 as long as I am careful to not attempt it right in the middle of an active batch.

Finally, while I couldn't find any official MS sources that explicitly recommended truncating these tables, I did find several articles where DBAs do a routine TRUNCATE at either the beginning or the end of their import process:

"Step 1:  Truncates each of the 3 staging tables."
"If you want to empty the staging table, you can either delete all records, or click Clear Batches in Integration Management."
TRUNCATE TABLE stg.TableName_Leaf

So my plan is to do an initial TRUNCATE, and then schedule mdm.udpEntityStagingBatchTableCleanup to run on a periodic basis.


Triggers make an auditing mechanism, that satisfies your criteria.

they are present in the ISO/ANSI standard (ISO 9075)
they can be used to track row-level changes
they can save data to tables, hence can be queried using SQL

Only thing I do not understand in your question is "database agnostic" - what does it mean, precisely?


Attach AdventureWorks Data to Existing Database (No Admin Role)

You know what's really funny? This is exactly the kind of thing SSIS is designed to do.

You're probably going to run SSIS on your own desktop or laptop as you learn. SSIS is part of SQL Server Developer Edition, so you can install that for free on your laptop. It also comes with the SQL Server engine, obviously.

So attach the AdventureWorks db to your local SQL Server Developer Edition, and then use SSIS to move the contents of the local tables you want over to the server run by your IT department.

Congratulations, you have your first ETL project! Bad news though: you're also the client. You're probably a terrible boss.

If you want to learn something easier, start by using SSIS to import the contents of a spreadsheet or a text file into that target SQL Server instead.


Integrating MongoDB metrices with Cloud watch

As per @AndrewAslinger blog here The Amazon CloudWatch is a useful tool for monitoring your compute resources, services, and applications running in AWS. Amazon automatically publishes metrics for you when you use many different AWS Services.

As aws documentation here Amazon CloudWatch is basically a metrics repository. An AWS service—such as Amazon EC2—puts metrics into the repository, and you retrieve statistics based on those metrics. If you put your own custom metrics into the repository, you can retrieve statistics on these metrics as well.

You can configure alarm actions to stop, start, or terminate an Amazon EC2 instance when certain criteria are met. In addition, you can create alarms that initiate Amazon EC2 Auto Scaling and Amazon Simple Notification Service (Amazon SNS) actions on your behalf. For more information about creating CloudWatch alarms, see Alarms.

For further your ref here, here and here


You posted a bug on this at Microsoft Connect:

SQL Server 2016 SQLServer:Resource Pool Stats:CPU usage target % Always Returns Zero - by HunterX3

Given that Microsoft didn't respond there either, it's probably a bug. Open a support case with Microsoft, and if it turns out to be a bug, they refund your support fee.


How to run OPTIMIZE TABLE on a 3-node cluster?

If there is a need to run OPTIMIZE TABLE in PXC, you could achieve this by denying its recording into binary logs into one of three(3) ways:

METHOD #1

SET sql_log_bin = 0;
OPTIMIZE TABLE mydb.mytable;
SET sql_log_bin = 1;

METHOD #2

OPTIMIZE NO_WRITE_TO_BINLOG TABLE mydb.mytable;

METHOD #3

OPTIMIZE LOCAL TABLE mydb.mytable;

SUGGESTION

Run the one of the above OPTIMIZE TABLE scenarios on one PXC node at a time while redirecting reads and writes from the cluster. If the data is huge, remove the PXC node from the cluster, run OPTIMIZE TABLE at will, and add the PXC back into the cluster.


I recommend either of the following:

Michell Ufford's index defragmentation script
Ola Hallengren's index and statistics maintenance script

Either of these can accomplish the task of maintaining indexes without requiring you to rebuild the scripts when indexes are added or removed.

One of the biggest problems I've seen with using the maintenance plan is the fact that it appears to iterate through every single index and place the index rebuild into a single script. Then the script is compiled and finally run.

Now imagine that you have a 150 databases each with over 1000 indexes. This becomes a maintenance plan that takes 8 hours to run.


Bitmask Flags with Lookup Tables Clarification

I partially agree with Aaron's comment - in the most general case for storing 21 unrelated pieces of information, you'd probably use 21 bit columns.  As a general solution, it may well be your best solution.  If you had multiple bitmask-ed varchar columns, that would translate to a row with possibly over a hundred bit flags. FYI, 21 bits get stored as 3 bytes when you don't define them as NULLable, removing the necessity for space in the NULL bitmap. Since you have multiple bitmask columns, you'd end up with every 8 bits mashed into a byte.

What SQL Server ends up doing with your multi-column queries is eventually a bunch of bitmasking routines (yes! SQL Server uses bitmasks, so they the concept per se can't be all bad!) but for average use cases, it makes life easier for you.

If we had more information about what types of queries you run, we may be able to better advise, because ultimately the use cases dictate the design.

If you persist with the COMPUTED column, I would persist and index it if you haven't already.  It helps some queries, such as

exact matches

WHERE computedInt = POWER(2, 6) -- bit position 7
AND matching on 15th bit and OR matching on 2 other bits (10th and 7th)

WHERE computedInt >= Power(2,14) AND computedInt < Power(2,15)
  AND computedInt & (Power(2,9) + Power(2,6)) > 0

But these are probably exotic samples and yet also real live in some cases.  It's certainly not too much worse than 21 individual bit columns, for which yes your statements could be easier to write, but remember that SQL Server has mashed them for storage into 3 bytes and will be doing the bit-unmasking anyway!  You would have thought if bit-masking were all bad (without exception) then SQL Server wouldn't be doing it, right?

EDIT

Re the scenario of

  Four flags, HasHouse,HasCar,HasCat,HasDog, 0000 is has none, 1111 is has all.

it is more efficient and logically expedient to test all 4 bits at once and do a single integer based operation, e.g.

WHERE computedInt & (POWER(2,10)+POWER(2,5)+POWER(2,3)+POWER(2,1)) = 0 -- has none
WHERE computedInt & (POWER(2,10)+POWER(2,5)+POWER(2,3)+POWER(2,1)) > 0 -- has one or more

Hypothetically, if this were your most exercised query on the table, you might even group the four columns into another computed column and index it separately, making the bitmask unnecessary (just test the resultant int with =0 and >0).  You might even go further and just precompute the answer... horses for courses.


In my experience, no it will not drop that 1 time cpu heavy query

It may have an insignificant compile time, but the execution is not insignificant

But if you are interested in a very accurate measurement of total cpu utilization that includes every request, you should not use a Query Store Capture Mode of 'Auto', then it should be 'All'


Visual Studio DAC with Schemabound View containing hierarchyid::GetRoot() - Two part name needed?

The answer is to add sys. in front of the hierarchyId:

sys.hierarchyid::GetRoot()


Is it possible to create a view in one database from another database in oracle when databases are different machine?

Yes, by using database links. For example, in db2:

create database link db1_link connect to myuser identified by mypassword using 'db1';
create view myview as select * from mytable@db1link;
select * from myview;

Where using 'db1' refers to a TNS entry, but it could be replaced with the actual TNS descriptor.


Where should one put indexes in a time dimension table?

You likely won't run into the issues of write problems, as I assume this would be something created once (or once per year), and then not touched.

But using an index will likely be a hinderance if you're searching by week ...  The problem is, if the index is used, it might scan that first, and then grab each record out of the table individually, which when you're pulling out more than about 5-20% of the records, it's typically faster to do a full table scan, and then drop the records you don't care about.

I don't know of any major RDBMSes that don't optimize for this when it's well-distributed data.  If it's not well distributed (eg, one of the values in a column occurs 95% of the time, but there's also other possible values), you may have to compute histograms on the table and not use a placeholder for the value when searching, so that the query optimizer has the value being searched for when generating the execution plan.

I'd likely not index day of week.  I'd check my database's documentation to see what their tradeoff is for indexed reads vs. full table scans to see if I'd index the day of the month or month of the year.  I'd likely index DOY/day of year if present (which sounds like it's your unique index, anyway)


MySQL : LEFT JOIN not fetching required data

Storing a string of comma-separated values in a column is not an ideal design; ideally you would have a table of keywords and a 2-column map table with a row for every (E_HEADER.id,keyword_id).

However, given your current structure, the FIND_IN_SET() function should work, although this will not be the best possible performance, since a function in the where clause that takes a column as its argument will generally prevent the use of indexes for evaluating that condition -- but given your structure, you don't have an alternative.

FIND_IN_SET() returns the ordinal position of the first argument within the comma-separated string that is the second argument, or in this case "something true" if a match is found; no match returns 0 ("false") and if either argument is null, the function returns NULL.

Also, it's difficult to say without a better understanding of your structure exactly what you want the query to do, but the derived table seems unnecessary.

SELECT Q.question_id, Q.school_id
  FROM Q_MASTER Q
  LEFT JOIN E_HEADER X on X.school_id = Q.school_id AND X.low_q = 1 AND X.school_id = 6
 WHERE NOT FIND_IN_SET(Q.question_id, X.keyword);

This would select every question_id and school_id from Q_MASTER where there exists a row in E_HEADER with a matching school_id and low_q is equal to 1 and school_id is equal to 6 and where the value of Q.question_id is not found in the comma-separated string X.keyword.

Based on my interpretation of the original query, only Q_MASTER records with school_id = 6 will be returned in spite of the left join, because the join requires school_id to be identical, and will project a NULL for X.keyword otherwise; FIND_IN_SET() returns NULL if either argument is null, and the expression "NOT (NULL)" returns evaluates to NULL which will be considered untrue by the WHERE clause.  The query will perform much better as an inner JOIN than a LEFT [outer] JOIN because fewer rows will need to be considered, but the ultimately correct approach requires a valid logical expression of what you actually need the server to do.


Returning only the row with maximum value from related rows

You can use window functions:

select id, reservationID swapNumber, oldRoom, newRoom
from (
    select id, reservationID swapNumber, oldRoom, newRoom
         , row_number() over (partition by reservationId
                              order by swapNumber desc) as rn
    from @RoomExchange
) as T
where rn = 1;

By reversing the sort order we can pick the first one for each partition.


oracle 11g Grant on dbms_lock not persisting

From Oracle documentation-

  When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.

SQL> conn system
Password:

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
DBA

SQL> show user;
USER is "SYSTEM"

SQL> grant execute on dbms_lock to jay;
grant execute on dbms_lock to jay
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

The error has occurred because the SYSTEM user has no 'EXECUTE' privilege with GRANT option on DBMS_LOCK.

SQL>conn / as sysdba

SQL> select * from user_tab_privs where grantee='SYSTEM' and table_NAME='DBMS_LOCK';

no rows selected

Also, the DBA role has no 'EXECUTE' object privilege on DBMS_LOCK package.

SQL>  select * from role_tab_privs where role='DBA' and table_name='DBMS_LOCK';

no rows selected

In order to grant execute privilege, you need to connect as SYSDBA which connects to SYS schema, owner of DBMS_LOCK, or grant 'EXECUTE' privilege with GRANT option to the user performing the grant.

[oracle@myserver ~]$ sqlplus system as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 2 14:45:27 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show user;
USER is "SYS"

SQL> grant execute on dbms_lock to jay;

Grant succeeded.

  When logged in from sqlplus (as SYSDBA), don't the grants assigned to a given user persist even after we exit sqlplus?

Yes, they persist.

  Is there no way we can log in as SYSDBA via JDBC since when I tried it, it didn't allow me.

Here is a sample connection string which works fine for me.

Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@host:port/SID","sys as sysdba","password");


How do I delete unique rows from a table with duplicate keys?

By using the ROW_NUMBER() ranking function, you could achieve that.  Here's an example below.  The top two queries are just for data verification.  I'm a firm believer that you should see what you will be deleting (and what will remain) before actually deleting data.

(Verification) Rows that will be deleted

;with cte as
(
    select
        *,
        row_num =
            row_number() over (partition by road order by ID)
    from dbo.YourTable
)
select *
from cte
where row_num > 1;

(Verification) Rows that will not be deleted

;with cte as
(
    select
        *,
        row_num =
            row_number() over (partition by road order by ID)
    from dbo.YourTable
)
select *
from cte
where row_num = 1;

Data Delete

Note: the below query, when modified to fit your environment will actually delete data.  Therefore, you should ensure that you really want to delete the data, and also have a backup of the data/database prior to performing any data modification.

;with cte as
(
    select
        *,
        row_num =
            row_number() over (partition by road order by ID)
    from dbo.YourTable
)
delete from cte
where row_num > 1;


After doing a lot more investigation this is what I have found.

It fixed the issue (significant performance gain and WRITELOG has an average wait time of 0.0126 which was initially 14.681)

Apparently the issue was with the Number of Virtual Log files in my physical log file.

There is a job scheduled to rebuild indexes every night, the job creates 36GB of logs, and until few weeks ago someone had add a job to shrink log file on weekly basis. Log file was being shrunk to 500MB.

Since it is a very busy server the log file would grow in size and it was set to auto grow by 3 percent. Each time it grew it added more and more VLFs. As a result my 35.5GB log file had 1600 VLFs.

To resolve the issue I did the following:

-- To get the number of VLFs in a physical Log file.
DECLARE @count INT, @DBname SYSNAME;
------------------------------------------------------------
SET @DBname = N'TEST_DB'
------------------------------------------------------------

DBCC LOGINFO(@DBname)
     SET @count = @@ROWCOUNT;

SELECT @DBname AS DatabaseName , @count Total_VLFs;
GO

The above query returned the databasename and number of VLFs (1600)

To reduce the number of VLFs this is what I did:

USE TEST_DB;
GO

DBCC SHRINKFILE (TEST_DB_log, 0, TRUNCATEONLY)
GO

DBCC SHRINKFILE (TEST_DB_log  , 0)
GO

ALTER DATABASE TEST_DB
  MODIFY FILE ( NAME       = TEST_DB_log
              , SIZE       = 36864MB
              , FILEGROWTH = 4096MB)
GO

This reduced the number of VLFs from 1600 to 20. The end-users has seen a sudden improvement in the application performance.

Now one thing I am not sure about is, if 20 VLFs is the right number of VLFs for a 36GB log file. But it seems to have a good impact on the performance of the database.


The below query uses the  sys.dm_os_performance_counters DMV to capture the performance counters of SQL Server. You can change the WAITFOR DELAY options and the Numeric suffix after the second GO command to modify the time interval and the number of times you want the code to be executed.

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('Tempdb..#PerformanceMonitor') IS NOT NULL
DROP TABLE #PerformanceMonitor;

CREATE TABLE #PerformanceMonitor
(
[object_name]   NVARCHAR(255) NOT NULL,
[counter_name]  NVARCHAR(255) NOT NULL,
[instance_name] NVARCHAR(255) NULL,
[cntr_value]    BIGINT NOT NULL,
[cntr_type]     INT NOT NULL,
[LoadedOn]      DATETIME NOT NULL DEFAULT GETDATE(),
)
GO
INSERT INTO #PerformanceMonitor
([object_name],[counter_name],[instance_name],[cntr_value],[cntr_type])
(SELECT object_name,counter_name,instance_name,cntr_value,cntr_type
 FROM sys.dm_os_performance_counters)

WAITFOR DELAY '00:00:10' --Change the interval here
GO 10 --Change the number of times the data will be captured here
SELECT * FROM #PerformanceMonitor

Once you have enough data and you have narrowed down the counters which are relevant, you can decide upon the best time to run your queries. And also as mentioned in the answer by @Yaroslav ,it is very important to keep in mind what type of query you want to run and decide accordingly.
And the most important thing, be careful with production data!!!


Is adding the ‘tbl’ prefix to table names really a problem?

I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it.

In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the month is complete, then the books are sealed and they can't modify values.

It's amazing how much financial data a financial services firm generates... Something we didn't realize with our test data set was the volume of data was going to make their month end procedures untenable. It took an increasingly long time to delete out the "current month's data" prior to replacing it with the new trial run.

We had to do something to make it faster for processing without breaking the uncatalogued list of "who knows what" that all depends on the MonthlyAllocation table. I decided to play magician and whip the tablecloth out from underneath them. I went old-school and used a Partitioned View. The data already had  an IsComplete flag so I made two tables - each with contrary check constraints: MonthlyAllocationComplete, MonthlyAllocationInComplete

I then created the partitioned view with the same name as the original table: MonthlyAllocation. No process was any the wiser about the physical change we made to the database. No reports broke, none of the analysts with direct access reported any issues with that "table" before or after.

Cool story bro, but where you going?

What if they had a naming convention there, tbl_MonthlyAllocation? Now what? Do we spend lots of man hours going through every ETL, every report, every ad-hoc spreadsheet in the organization and updating them to use vw_MonthlyAllocation? And then of course all those changes go through the Change Board and that's always a quick and painless process.

You boss might ask: What's the reward to the company for all that work again?

The other option becomes we leave this view named as tbl_ and not spend all that time testing, updating and deploying code. Which becomes an amusing anecdote you explain to all the new hires, and those with short attention spans, that have to work with the database as to why you are inconsistent with the naming of objects

Or you don't double encode objects with redundant metadata. The database will happily tell you what is a table, what is a view, what is a table valued function, etc.

Naming conventions are good, just don't paint yourself into a corner with them.


Choosing A PostgreSQL Authentication Method For A Large Course

Sounds like no fuss to me:

Create an OS account for each user, preferably with simple ssh key based authentication;
in pg_hba.conf use peer for local connections;
Optionally also use ident for host connections from 127.0.0.1 and run identd on the server. Not strictly required.
Set a password on their role for use with md5 authentication, then use md5 authentication for connections from allowed remote hosts with the hostssl method.

That way they'll be able to connect over SSL directly with a password, and locally (after ssh'ing in) with no password prompt.

What you describe:

  My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running.

sounds a bit like remote ident. This exists and works, but it's completely and utterly insecure. It was written for the days when "my personal computer" would elicit gales of laughter and nobody but nobody had root. These days anyone can set up a laptop (or phone) to claim to be any user they want over ident, so it's completely useless except on the local loopback where you control it.

It does sound like Kerberos is closer to what you want, but honestly it's going to be way more messing around than seems worthwhile. I'd just use peer for local connections and md5 for remote ones.


Yes. This is a common scenario when implementing 0..1 relationships.


How to map single-products to categories in my scenario?

For simplification, add a "variant" even for products without variants.

Is there any more info on categories other than cat_name?  If not, then I suggest it is "over-normalization" to even have the Categories table.  Simply put cat_name in Product_to_Categories_Mapping.

Product_to_Categories_Mapping needs

PRIMARY KEY(product_id, category),  -- the _pair_ is unique
INDEX(category, product_id)         -- to go the other direction


`ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend

FACTS

You said you are using ext4. File size limit is 16TB. Thus, Sample.ibd should not be full.

You said your innodb_data_file_path is ibdata1:10M:autoextend. Thus, the ibdata1 file itself has no cap to its size except from the OS.

Why is this message coming up at all? Notice the message is "The table ... is full", not "The disk ... is full". This table full condition is from a logical standpoint. Think about InnoDB. What interactions are going on ?

My guess is InnoDB is attempting to load 93GB of data as a single transaction. Where would the Table is Full message emanate from? I would look at the ibdata1, not in terms its physical size (which you already ruled out), but in terms of what transaction limits are being reached.

What is inside ibdata1 when innodb_file_per_table is enabled and you load new data into MySQL?

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

My suspicions tell me that the Undo Logs and/or Redo Logs are to blame.

What are these logs? According to the Book

Chapter 10 : "Storage Engines" Page 203 Paragraphs 3,4 say the following:

  The InnoDB engine keeps two types of logs: an undo log and a redo log. The purpose of an undo log is to roll back transactions, as well as to display the older versions of the data for queries running in the transaction isolation level that requires it. The code that handles the undo log can be found in storage/innobase/buf/log/log0log.c.

  The purpose of the redo log is to store the information to be used in crash recovery. It permits the recovery process to re-execute the transactions that may or may not have completed before the crash. After re-executing those transactions, the database is brought to a consistent state. The code dealing with the redo log can be found in storage/innobase/log/log0recv.c.

ANALYSIS

There are 1023 Undo Logs inside ibdata1 (See Rollback Segments and Undo Space). Since the undo logs keep copies of data as they appeared before the reload, all 1023 Undo Logs have reached its limit. From another perspective, all 1023 Undo Logs may be dedicated to the one transaction that loads the Sample table.

BUT WAIT...

You are probably saying "I am loading an empty Sample table". How are Undo Logs involved? Before the Sample table was loaded with 93GB of data, it was empty. Representing every  row that did not exist must take up some housecleaning space in the Undo Logs. Filling up 1023 Undo Logs seems trivial given the amount of data pouring into ibdata1. I am not the first person to suspect this:

From the MySQL 4.1 Documentation, note Posted by Chris Calender on September 4 2009 4:25pm:

  Note that in 5.0 (pre-5.0.85) and in 5.1 (pre-5.1.38), you could receive the "table is full" error for an InnoDB table if InnoDB runs out of undo slots (bug #18828).

Here is the bug report for MySQL 5.0 : http://bugs.mysql.com/bug.php?id=18828

SUGGESTIONS

When you create the mysqldump of the Sample table, please use --no-autocommit

mysqldump --no-autocommit ... mydb Sample > Sample.sql

This will put an explicit COMMIT; after every INSERT. Then, reload the table.

If this does not work (you are not going to like this), do this

mysqldump --no-autocommit --skip-extended-insert ... mydb Sample > Sample.sql

This will make each INSERT have just one row. The mysqldump will be much larger (10+ times bigger) and could take 10 to 100 times longer to reload.

In either case, this will spare the Undo Logs from being inundated.

Give it a Try !!!

UPDATE 2013-06-03 13:05 EDT

ADDITIONAL SUGGESTION

If the InnoDB system table (a.k.a ibdata1) strikes a filesize limit and Undo Logs cannot be used, you could just add another system tablespace (ibdata2).

I just encountered this situation just two days ago. I updated my old post with what I did: See Database Design - Creating Multiple databases to avoid the headache of limit on table size

In essence, you have to change innodb_data_file_path to accommodate a new system tablespace file. Let me explain how:

SCENARIO

On disk (ext3), my client's server had the following:

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  2 00:15 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  2 00:15 ibdata2

The setting was

innodb_data_file_path=ibdata1:346M;ibdata2:500M:autoextend:max:10240000M

Note that ibdata2 grew to 2196875759616 which is 2145386484M.

I had to embed the filesize of ibdata2 into innodb_data_file_path and add ibdata3

innodb_data_file_path=ibdata1:346M;ibdata2:2196875759616;ibdata3:10M:autoextend

When I restarted mysqld, it worked:

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  3 17:02 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  3 17:02 ibdata2
-rw-rw---- 1 s-em7-mysql s-em7-mysql   32315015168 Jun  3 17:02 ibdata3

In 40 hours, ibdata3 grew to 31G. MySQL was once again working.


No SQL Server does not hold a latch. The storage engine keeps track of the current index scan position using a "cookie". The cookie is revalidated if another process takes a latch (of a type that means the page might have been changed) on the same page since the cookie was acquired.

If the cookie (scan position) is no longer valid, the b-tree structure is traversed (using key + uniquifier information stored in the cookie) to reacquire the correct position, and the cookie updated.

In case it is of interest, here is a stack capture when the saved position is validated:

And another one just after cookie validation has failed due to a concurrent incompatible latch. The engine is repositioning the scan position using a seek to find the correct new position:

There are performance counters related to cookie activity, for example Scan Point Revalidations/sec and Used page/leaf page cookie.

There is no public documentation that describes exactly how this all works. The details above are based on observations, subsequently confirmed in conversations with Paul Randal and Bob Ward.


MongoDB only three of four shards being used

There are a few confusing pieces here, first you list this piece of sh.status():

{  "_id" : "test",  "partitioned" : false,  "primary" : "d" }
{  "_id" : "test_collection",  "partitioned" : false,  "primary" : "d" }

That means that there is a test database, a test_collection database, not collections.  It also indicates that neither of them are sharded - that is what "partitioned" : false means.

You then show some output from test_cluster.test_cluster_collection.stats() but that is not one of the databases from the sh.status command.  It is a collection from the test_cluster database, and there is no indication of how that is sharded, though it would be listed in sh.status().  Basically the two pieces of information have nothing to do with each other.

Your for loop will also be inserting into the test_sharded_collection as a sub-collection of test_cluster.  In other words, the full namespace you are inserting into is test_cluster.test_cluster.test_sharded_collection, and I suspect that is not sharded which is why you are likely not seeing the chunks increase.  What I suspect you intended to do was insert into test_cluster.test_sharded_collection.  Try this for your inserts instead:

use test_cluster;
for( var i=0; i < 20000; i++ ){db.test_sharded_collection.insert( { x:i,y:3,z:"test                       test" } )};

The full output of sh.status(), and specifically the section about the test_sharded_collection would help me confirm my suspicions.