What are the primary differences between NuoDB and MySQL?

NuoDB is a completely new database system written from the ground up to exploit distributed shared-nothing architectures.  NuoDB's "Emergent" architecture is the opposite of the monolithic approaches to SQL/ACID relational database systems that have dominated the last 3 decades.  In NuoDB it is the aggregate effect of many nodes that electively opt-in and contribute to the system that results in it being a SQL/ACID database.  It behaves like a flock of birds that fly in an organized fashion but without a central point of control or a single point of failure.

The product is not offered as a MySQL storage engine, partly because the MySQL storage engine API is a table-level API and does not allow the flexibility that a database system needs to do what NuoDB does.  You can see a good video overview of the product at http://www.nuodb.com/blog.

On availability and pricing:

The product is designed specifically for web scale applications, supports PHP, Ruby, Hibernate and the usual range of SQL tools/APIs, and runs either on your own hardware or on public clouds.

NuoDB is in late Beta (Beta 6 is due in the next few weeks) with about 250 customers. The product is due to ship in the Spring.

Pricing depends on whether you download it or run it on public clouds. For download it will be free forever for small installations, and will have a subscription fee as you scale out. On public clouds it will be based on the pay-per-use model of the cloud.

Disclaimer: I am the CEO of NuoDB Inc


No, if PostgreSQL writes logs, then it writes basically real-time.  For example, if you open a log file with less and press ShiftF, you will get the newly written rows flowing in.

That you don't see anything in the logs can be caused by a few things, but first be sure you have read through the relevant documentation and set up everything accordingly.  There and in postgresql.conf you can also check where the DB writes the logs and so on.  Be aware that some configuration options need server restart or DB reload to take effect.


How can I disable the notifications shown at bottom right corner in pgAdmin 4?

Changing the value in Query info notifier timeout to -1 it will disable it. This is documented in the official pgAdmin documentation in the Preferences Dialog help as:

  Use the Query info notifier timeout field to control the behaviour of the notifier that is displayed when query execution completes. A value of -1 will disable the notifier, and a value of 0 will display it until clicked. If a positive value above zero is specified, the notifier will be displayed for the specified number of seconds. The default is 5.


WHERE SYSDATE < to_date( LD.DEADLINE, 'Month DD, YYYY' );

would work assuming all your data is perfect and that all your session are configured to use English month names.  I would strongly recommend, however, that you fix the data model.  Storing strings that represent dates rather than actual dates is a terrible practice.  Inevitably, you end up with at least a handful of columns where the data is invalid which would cause this query to fail.  You could work around that by creating a function that attempts to convert the string to a date and catches the exception if there is an error and then use that function in your query, i.e.

CREATE OR REPLACE FUNCTION my_to_date( p_str IN VARCHAR2, p_format IN VARCHAR2 )
  RETURN date
IS
BEGIN
  RETURN to_date( p_str, p_format );
EXCEPTION
  WHEN others
  THEN
    RETURN NULL;
END;

and then

WHERE SYSDATE < my_to_date( LD.DEADLINE, 'Month DD, YYYY' );


How can I view special characters in SQL Server 2012 Management Studio grid view?

I found viewing the data as binary was the way to go for me:

SELECT @String as MyString, CAST(@String as varbinary(max)) MyString_In_Hexidecimal;

I found an ASCII table then helped decipher what was in the string.


Performance - Database queries for range of dates

I'd recommend you make a date column to store all this data then partition and index it. You'll then be able to query it for days and months as you would usually query the date.

Here's some information on partitions (in terms of oracle DB):
https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.html

It effectively means if you partition by month and you only need to query that month you will only select data from that partition instead of the whole table.


How do I preserve the leading zeros when I insert a number into this table?

0023 is not a number. It's a string. 23 is the number. SQL Server is able to recognize that those extra zeros are not needed to define the number, so it ignores them. If you want to dispaly a value as 0023 to the application, I'd suggest doing the formatting on the application side. That way the number stored in SQL Server is still a number if you want do addition, aggregation, or other calculations. If you really have to store it as '0023', you need to convert it to a character field; char, varchar, nvarchar, nchar.


How to fine tune a huge problematic Heap Table?

Based on those facts, 190M rows, 450GB, un-indexed heap, long-running queries, you should create a Clustered Columnstore index and store the data in compressed columnar format.
However, that might not be the optimal solution, as you still have a table with no key and no indexes, which is strange.


Why does database recovery take so long?

I had a large transaction fail due to the transaction log filling up yesterday, it couldn't be rolled back and SQL Server has restarted the specific DB to perform recovery.

This is not totally correct when a transaction starts in SQL Server it reserves space in transaction log in case the transaction has to rollback. From Transaction Log architecture BOL doc

  Rollback operations are also logged. Each transaction reserves space
  on the transaction log to make sure that enough log space exists to
  support a rollback that is caused by either an explicit rollback
  statement or if an error is encountered. The amount of space reserved
  depends on the operations performed in the transaction, but generally
  it is equal to the amount of space used to log each operation. This
  reserved space is freed when the transaction is completed

So I guess you can understand now.

  What I can't understand it why it's taking so long?

Query rollback operation is mostly single threaded while when same query executes it can use parallelism/multiple threads to perform same operation making it very fast as compared to the rollback. I suggest you read What Bob Dorr has to say about rollback taking long time. To get more insight about what occurs when rollback is in progress please read This article. Restarting SQL Server service is not going to help much, after restart the rollbak process had to start again. You have to wait and let the rollback process complete.

You can also use sys.dm_exec_requests to track your rollback.

select
session_id,
command,
status,
percent_complete
from sys.dm_exec_requests
where command IN ('killed/rollback','rollback','db_startup')

The other reason which Remus mentioned is too many VLF's. If you have to many VLF's recovery can take lot of time. Also other factor that can delay recovery is if SQL Server service account does not have Perform Volume maintenacce task right or instant file initialization. You can use this link to check if IFI is present or not.

EDIT:

From The output you posted

session_id  command status  percent_complete
35  DB STARTUP  background  86.06061

The startup process is 86 % complete. Just keep monitoring it, eventually it will complete. Also as stated rollback process can get blocked and can incur waits so keep monitoring that as well.

  2012 x64 Standard SP2

You have standard edition which cannot take advantage of fast recovery which is present in enterprise edition. With fast recovery database can come online after second phase of recovery(the redo phase) however it is alwasy not the case so please read the article. As an additional reading you can read When is fast recovery used. Standard edition limitation could be one more reason why it is taking database time to come online.

  One other thing, the rollback session (35) is itself blocking another system process from performing CHECKPOINT

Yes it can and you cannot do anything as you have no control over system process in terms you cannot kill it.


Does it makes sense to partition on a foreign key?

FOREIGN KEYS are not supported in PARTITIONed tables.  (With luck this might change in 5.8.)

If you don't have over a million rows, don't bother to PARTITION.

If you are not depending on FOREIGN KEY CONSTRAINTS, don't use them.  INDEXes will suffice.

If you would like to discuss these further, please provide SHOW CREATE TABLE (even if not finalized) and the main SELECTs.

Based on what you said, I would guess that you need a compound index:

INDEX(OtherID, NegativeTimestamp)

Edit

I see a redundant index on MarketID.  Also UNIQUE(ID) is redundant since the PRIMARY KEY is a UNIQUE key.

If the two Close columns have the same information, but one is negated, then the negative on can probably be removed.  Then use DESC in the ORDER BY.

The use of @variables in the WHERE may be poorly optimized; let's see EXPLAIN SELECT ....

I would replace all of the indexes with just 2:

PRIMARY KEY (`MarketID`, Close_Datetime, ID),
INDEX(ID),  -- This is sufficient for AUTO_INCREMENT (except for protecting from deliberate dup)
-- nonexistent field?  KEY `OpenTimestamp` (`Open_Datetime`),
-- unneeded?   KEY `CloseTimestamp` (`Close_Datetime`),
-- unneeded?   KEY `CloseNegativeTimestamp` (`Close_NegativeTimestamp`),
-- covered     KEY `MarketIDIndex` (`MarketID`),
-- replaced    KEY (`MarketID`,`Close_NegativeTimestamp`),

Also, I would shrink most of the fields to save space and improve cacheability.  BIGINT is not really needed?  MEDIUMINT would suffice for MarketID?  decimal(30,15) takes 14 bytes, mostly zeros.  DOUBLE (8 bytes) might work?

(I am assuming this is Stock Market data?)

If you choose to get rid of the FK and go with PARTITION, then I would need to see the other important SELECTs.

But, for that matter, do you need ID?  Can't (MarketID, CloseDate) be the unique PRIMARY KEY?  Note: It would be a 3-byte DATE, not a 5 or 8-byte DATETIME.


Monitoring decimal values on Perfmon using User Settables

Running this:

CREATE PROCEDURE dbo.set_user_counter1
(
    @newvalue DECIMAL(18,2)
)
AS
BEGIN
    DBCC setinstance (
        'SQLServer:User Settable'
        , 'Query'
        , 'User counter 1'
        , @newvalue
    );
END;
GO

EXEC dbo.set_user_counter1 12.5

results in:

  Msg 2560, Level 16, State 9, Procedure set_user_counter1, Line 7
  Parameter 4 is incorrect for this DBCC statement.

Running the following shows you the "help" entry for the DBCC setinstance:

DBCC TRACEON (2588); /* show DBCC Help */
DBCC TRACEON (2520); /* show undocumented command help */
DBCC HELP ('setinstance')

This returns:

  dbcc setinstance (objectname, countername, instancename, value)

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Unfortunately, that is no help determining if value can only be an int.

In fact, the MSDN page you linked states the following:

  A counter can monitor any single integer value


SQL Always on High Availabity Groups Multi Subnet

As mentioned in the docs here the issue is explained:

MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB
Issue: If your Availability Group or Failover Cluster Instance has a
listener name (known as the network name or Client Access Point in the
WSFC Cluster Manager) depending on multiple IP addresses from
different subnets, and you are using either ADO.NET with .NET
Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially 50% of
your client-connection requests to the availability group listener
will hit a connection timeout.

And there are two main workarounds:

Workarounds: We recommend that you do one of the following tasks.
If do not have the permission to manipulate cluster resources, change
your connection timeout to 30 seconds (this value results in a
20-second TCP timeout period plus a 10-second buffer).
Pros: If a cross-subnet failover occurs, client recovery time is
short.
Cons: Half of the client connections will take more than 20 seconds
If you have the permission to manipulate cluster resources, the more
recommended approach is to set the network name of your availability
group listener to RegisterAllProvidersIP=0. For more information, see
"RegisterAllProvidersIP Setting" later in this section.
Pros: You do not need to increase your client-connection timeout
value.
Cons: If a cross-subnet failover occurs, the client recovery time
could be 15 minutes or longer, depending on your HostRecordTTL setting
and the setting of your cross-site DNS/AD replication schedule.

In my opinion half of the connections taking more than 20 seconds is far from ideal and I would look into changing the RegisterAllProvidersIP to 0 and HostRecordTTL settings according to your needs. Know that lowering this value too much will increase DNS lookup requests. You could also look into monitoring failovers here would help with connection issues after failover.


Firebird 2.5. Windows 8.1. Can't connect to any database! Why?

If you start Firebird as an application, then Firebird must either be located in a normal folder (not a system folder under UAC protection like Program Files), or you need to run it as Administrator.

The reason is that Firebird needs write access to several files in its own program folder, which doesn't work without privilege elevation.

Better yet: run it as a service. The service by default has sufficient rights to start Firebird.


SQLDeveloper: Unable to desc a table that exists

This must be because of MYTABLE being a synonym of a synonym, which is not supported on SQL Developer 4.1.3(must be a bug). Reference

I have three users user3, user2 and user1

SQL> conn user3/user3
Connected.
SQL> create table mytable(id number);

Table created.

SQL> grant select on mytable to user2 with grant option;

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create synonym mytable for user3.mytable;

Synonym created.

SQL> grant select on mytable to user1;

Grant succeeded.

SQL> conn user1/user1
Connected.

SQL>  create synonym mytable for user2.mytable;

Synonym created.

SQL> desc mytable;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> conn / as sysdba
Connected.
SQL> select table_name from dba_all_tables where table_name='MYTABLE';

TABLE_NAME
------------------------------
MYTABLE

SQL> select owner from dba_tables where table_name='MYTABLE';

OWNER
------------------------------
USER3

Now try to describe it on SQL Developer.

desc mytable;

ERROR: object MYTABLE does not exist


Insert command not showing schema details

That's fine because your dump should have the following:

SET search_path = kref, pg_catalog;

and then table definition followed by insert statements


accelerating a SQL quadruple self-join with a complex alternation in the WHERE clause

In case someone is interested, I answer my own question: since I reuse a large number of this filtered tables in several queries, I ended up precalculating them. This address both the readability and performance issues.


Microsoft connect ticket was resolved with wording changed:

Please refer to link below (changes have not been made on BOL yet, 1 dec)

https://connect.microsoft.com/SQLServer/feedback/details/706766/books-online-suggested-permission-to-create-spatial-index-incorrect


Calculating duration between two events in a MySQL database (timeseries)

Approach 1 (works in any version)

Add a sequence number to the table (or a copy of the columns of interest).
Do a "self join" ON a.seq = b.seq-1 to match up adjacent rows.
Filter down to the status changes: WHERE a.Value != b.Value

The table will contain, for example:

  3  13:44:13  off   4  13:47:27   on
 12  14:27:27   on  13  14:34:48  off

Assuming you want the timespan between pairs of rows in this table, again add a sequence number, and do the self join to extract and subtract the next to last column.

Approach 2 (plug for upgrading)

If you have MariaDB 10.2 or MySQL 8.0, then there are "Windowing functions" that make this task immensely simpler.  See LAG(); it obviates the need for the self joins and the sequencing.

In the first pass, look for LAG(Value) != Value while using ORDER BY DateTime.  In the second pass, diff the LAG of the time with the current time.

Approach 3 (possibly the best for 5.5 without windowing)

Since you are stuck in the nearly-antique 5.5, Consider using a Stored Procedure, not a VIEW.  It will involve two extra tables.  They cannot be CREATE TEMPORARY TABLE since you can't use a temp table twice for the self-join.

So, a better approach (without windowing) would be to use @variables to simulate LAG().

Approach 4 (somewhere between 1 and 3)

This involves a single pass of looking at consecutive rows.  If the first of a consecutive (based on datetime) pair of rows says ON, then calculate the "on time".  Then SUM the "on times".

Details (Maybe I will give you details, but first...)

Provide sample data (not images) ready to run (CREATEs and INSERTs).  Also provide expected output.  (I understand ON to OFF, but what about OFF to ON; and do you want the start time of the range; etc.)

A hiccup

Since you want the "on time" to stop at the top of the hour, extra code is needed to stop the summation, etc.  One artificial way to do this is to add 2 rows, one second apart at the top of each hour.  The first is OFF, the second (1 second later) is ON.  This will create a 1-second error in the results, but has no impact on the Approaches except for simplifying the "top of hour" requirement.


You have no problem with permissions (otherwise you'd got "access denied" or "file not found" errors), nither it's a lock timeout. This timeout is set up in your C++ program, if I'm not mistaken, 30 seconds is the default. SQL Server has no timeouts at all, you should change the property in your application, smth like SqlCommand.CommandTimeout


Lock a table for a CTAS upsert in Azure SQL DataWarehouse

There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.

Other approaches to this such as sp_get_applock are also unavailable.  Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.


If the column is not indexed then it does not matter how many times (or with how many values) it is used, a table scan seems inevitable (unless there is some other indexed condition).

But there is no need to scan multiple times. MySQL optimizer tries to find the most effective plan, if nothing better is possible, it iterates over the table and checks each row for all conditions at the same time.

If an index is available (and statistics say it is effective), then ORs (on the same column) and IN are treated the same way - a "range" scan on the index is executed, fetching only matching rows.


The index is only used when Postgres expects it to help performance - which is only the case if the filter is expected to be selective enough (~ 5 % or less of the rows qualify, percentage heavily depends on various details).

One problem with document types like jsonb: Postgres currently does not maintain statistics about value frequencies of embedded elements (still true in Postgres 10). So it has to base its decision whether or not to use an index on generic frequency estimations. Meaning, even if your particular filter data -> 'school_id' ->> 1 IS NOT NULL is very selective and using the index would pay, Postgres works with a generic average estimation and might miss the opportunity.

There are ways around this with expression or partial indexes, because Postgres collects separate statistics for index expressions. The best you could do for this particular query would be a partial index:

CREATE INDEX data_school_id ON events (event_id)  -- idx column largely irrelevant here
WHERE (data -> 'school_id' ->> 1) IS NOT NULL;

And VACUUM ANALYZE events; at least once (or wait until autovacuum kicks in).

Related:

Index that is not used, yet influences query
Inconsistent statistics on jsonb column with btree index


How to connect to pluggable database using ipc in Oracle XE 18c?

"if I use use ipc (sqlplus user/password@xepdb1) "

That's not IPC, that's tcp.  It tries to find the entry for 'xepdb1' in the client's tnsnames.ora file. Then it tries to construct a tcp packet from the information found there, and send that packet to the specified host and port, where - hopefully - there is a listener.  Your error means that it could not find the specified entry in the client's tnsnames.ora.


How to restore function using PG_RESTORE

The parenthesis is special to the shell.  You don't have to remove it, you have to quote/escape it to get it to pass through the shell.

pg_restore -d postgres --function='addconfiguration2(text)' -v /tmp/dump


If a replicaset needs to be added as shard in a sharded cluster, --shardsvr argument is mandatory when starting a mongod process.

mongod --shardsvr --replSet <replSetname>  --dbpath <path> --bind_ip localhost,<hostname(s)|ip address(es)>

If is it a config server, then --configsvr argument is mandatory

mongod --configsvr --replSet <replica set name> --dbpath <path> --bind_ip localhost,<hostname(s)|ip address(es)>

Refer the following link to deploy

Deploy a Sharded Cluster


DevExpress formats columns differently if they're all numbers

Force the GridView column format from the code behind:

using DevExpress.Utils;
// ...
GridColumn myCol = new GridColumn() { Caption = "TheColumn", Visible = true, FieldName = "ColumnName" };
colPayment.DisplayFormat.FormatType = FormatType.String; //Char, Varchar, or whatever option DE gives you to format the display
gridView1.Columns.Add(myCol)

If you're gonna add it from the code behind, if not access the Column and set the format.

gridView1.Columns["TheColumn"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.String;

It is not an SQL issue, the data is stored in the format that is supposed to be stored, the frontend is the one supposed to handle the display format in this case. If you create a view the same deal will happen because the grid will still try to guess the column type. Talk to the dev.


SSMS Restore Database Wizard questions while restoring to different destination

In my experience, when you change the name of the database during a restore SSMS will automatically update the file names. However, I always double check that it is the case just to be absolutely sure.


I think the main problem is in your table design (many-many redundant values which are prone to typos and other problems).  If you were referencing these values via foreign keys, the table rows would be much shorter - therefore the aggregation could be done on a much smaller dataset and the actual values be added via joins.  (The optimizer takes care of doing this well, you don't need additional magic.  The design change is your task, though :) - see also ypercube's comment above.)

Aside this, in your configuration the work_mem value seems to be very low.  If this is true or not depends on how many rows you get from this query - but as grouping and aggregating involves sorting and work_mem is the place where the data is being sorted, there is a chance that simply increasing this to a (much) bigger value would improve the performance a lot.

Furthermore, an index like

CREATE INDEX idx_emissions_totales_residentiel
ON emissions_totales (schema_source, numreg, annee_ref, type_source_n1, unites)
WHERE schema_source = 'residentiel';

will also help this particular query.  As edze suggested, going to PostgreSQL 9.2 and including 'emi' in the index (as the last column) could also help.


SQL server DAC connection to mssqlsystemresource database

I just managed to connect to my SQL Server using DAC, where the SQL Server was started in single user mode. I verified single user mode by looking in the errorlog file.

Using SSMS is not a reliable way to use the DAC or single user mode. SSMS has a tendency to open several connections, and you never know which one comes first - even for a single query window. I used SQLCMD. I managed to connect using SSMS single query window as well, but again, sometimes it works sometimes not depending on the orders for the (background) connections you happen to get.

Use SQLCMD.EXE instead of SSMS.


when were the sys-dm_os_wait_stats last cleared? sql-2005!

there is a wait type called SQLTRACE_BUFFER_FLUSH -> Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.

this article is very good:
Figuring Out When Wait Statistics Were Last Cleared - by Erin Stellato

I came out with the following script that seems to be working fine on my servers, including the sql2005 ones.

/* last time the wait cache was cleared - sql 2005*/
SELECT
[wait_type],
[wait_time_ms],
DATEADD(ss,-[wait_time_ms]/1000,getdate()) AS [Date/TimeCleared],
CASE
WHEN [wait_time_ms] < 1000 THEN CAST([wait_time_ms] AS VARCHAR(15)) + ' ms'
WHEN [wait_time_ms] between 1000 and 60000 THEN CAST(([wait_time_ms]/1000) AS VARCHAR(15)) + ' seconds'
WHEN [wait_time_ms] between 60001 and 3600000 THEN CAST(([wait_time_ms]/60000) AS VARCHAR(15)) + ' minutes'
WHEN [wait_time_ms] between 3600001 and 86400000 THEN CAST(([wait_time_ms]/3600000) AS VARCHAR(15)) + ' hours'
WHEN [wait_time_ms] > 86400000 THEN CAST(([wait_time_ms]/86400000) AS VARCHAR(15)) + ' days'
END [TimeSinceCleared]
FROM [sys].[dm_os_wait_stats]
WHERE [wait_type] = 'SQLTRACE_BUFFER_FLUSH'
  OR  [WAIT_TYPE] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP';

/* check SQL Server start time - 2008 and higher */
SELECT
[sqlserver_start_time]
FROM [sys].[dm_os_sys_info];

-- to clear the wait cache
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

this time I will leave it like this, because I in a bit of a rush to monitor stored procedures executions, but for the next time I will use other Several Methods to collect SQL Server Stored Procedure Execution History

and maybe also a bit of this one:
Converting SQL Trace to Extended Events in SQL Server 2012

hopefully soon we will migrate all of the sql server 2005


Find "n" consecutive free numbers from table

This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
)
SELECT
  id_set,
  number
FROM counted
WHERE cnt >= 3
;

Here's a SQL Fiddle demo* link for this query: http://sqlfiddle.com/#!1/a2633/1.

UPDATE

To return only one set, you could add in one more round of ranking:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
),
ranked AS (
  SELECT
    *,
    RANK() OVER (ORDER BY id_set, grp) AS rnk
  FROM counted
  WHERE cnt >= 3
)
SELECT
  id_set,
  number
FROM ranked
WHERE rnk = 1
;

Here's a demo for this one too: http://sqlfiddle.com/#!1/a2633/2.

If you ever need to make it one set per id_set, change the RANK() call like this:

RANK() OVER (PARTITION BY id_set ORDER BY grp) AS rnk

Additionally, you could make the query return the smallest matching set (i.e. first try to return the first set of exactly three consecutive numbers if it exists, otherwise four, five etc.), like this:

RANK() OVER (ORDER BY cnt, id_set, grp) AS rnk

or like this (one per id_set):

RANK() OVER (PARTITION BY id_set ORDER BY cnt, grp) AS rnk

* The SQL Fiddle demos linked in this answer use the 9.1.8 instance as the 9.2.1 one doesn't appear to be working at the moment.


Concurrent Update On Primary Key Column Making Deadlock

Without the deadlock trace, we can only guess based on symptoms.
Long-term blocking and deadlocks suggest the singleton row update query is performing a full scan rather than locating the row by the PK index. This will happen if the primary key parameter data type is different than the referenced column and results in a non-sargable expression that can't use the PK index efficiently.
Attention to detail is important in declaring parameters in app code or stored procedure to ensure the parameter definition matches the referenced column type. A common cause of mismatched types (not necessarily yours) in ADO.NET is the AddWithValue method, which infers the SQL type based on the supplied value. Strings in .NET are Unicode so the resultant parameter type of a string value is nvarchar. Comparing an nvarchar parameter with a legacy SQL collation varchar column precludes using the primary key index to locate the row, resulting in a full scan that leads to blocking and deadlocks on on active system.


MySQL: How to match data with regular expression string

CREATE TABLE patterns (pattern VARCHAR(16));
INSERT INTO patterns VALUES ('ABC4*'), ('RDTU22*');
SELECT * FROM patterns;

| pattern |
| :------ |
| ABC4*   |
| RDTU22* |

CREATE TABLE values_to_check (val VARCHAR(255));
INSERT INTO values_to_check VALUES ('ABC41'), ('ABC49'), ('RDTU225'), ('RDTU229');
SELECT * FROM values_to_check;

| val     |
| :------ |
| ABC41   |
| ABC49   |
| RDTU225 |
| RDTU229 |

SELECT values_to_check.val, patterns.pattern
FROM values_to_check
JOIN patterns ON values_to_check.val LIKE REPLACE(patterns.pattern, '*', '%');

val     | pattern
:------ | :------
ABC41   | ABC4*
ABC49   | ABC4*
RDTU225 | RDTU22*
RDTU229 | RDTU22*

SELECT values_to_check.val, patterns.pattern,
       values_to_check.val LIKE REPLACE(patterns.pattern, '*', '%') does_they_match
FROM values_to_check
JOIN patterns;

val     | pattern | does_they_match
:------ | :------ | --------------:
ABC41   | ABC4*   |               1
ABC41   | RDTU22* |               0
ABC49   | ABC4*   |               1
ABC49   | RDTU22* |               0
RDTU225 | ABC4*   |               0
RDTU225 | RDTU22* |               1
RDTU229 | ABC4*   |               0
RDTU229 | RDTU22* |               1

db<>fiddle here
PS. REPLACE() converts the pattern to the format clear for LIKE (replaces the asterisk with the percent sign).


Can you implement DR in cloud using Async AG, logshipping with an on-premise FCI solution?

What are my options for building DR in cloud if I implement FCI for HA on-premise.

There are a ton. Whatever you could do now, you can do "in the cloud". Think of the cloud as an extension of your current datacenter (being a very basic analogy).

  The goal is to have a Hybrid on-premise HA + DR in cloud solution.

Different solutions are going to cost different amounts per month, so some maybe be more or less expensive than others - choosing the minimum needed infrastructure to meet the SLA's is often the best choice.

Outside of the other technical requirements (let's just keep it SQL Server at this point), you already have an FCI on site and setup. Depending on the version of SQL Server, you can do Availability Groups if you need that level of DR or BC. There is also Log Shipping, Transactional Replication, Backups (not even to an instance, just to a storage area), depending on the database feature usage there is also a bacpac which could be imported to IaaS or PaaS solutions.

  Can I configure Async AG/logshipping in Azure with an on-premise FCI?

Yes, but it'll require some extra infrastructure setup - still easily doable. Setting up an AG, though, would cost more than log shipping for the additional usage of components (at least in Azure - I work for Microsoft) and require much less setup. Things to think about.


All actions denied on MS SQL Server on Go Daddy Hosting

You can't use sysadmin (sa) if you register shared hosting. You need to create your database via their control panel, then you can manage your database via your CP. Or some providers allow to connect remotely. For more information, just contact your hosting provider. Good luck


Choose two columns for same purpose or it should be two different columns?

If there are only 3 states for EmiAmount (and barring other complications that may need to be added) I would be inclined to use the EmiAmount (NULL, 0, >0) to define the state.

Why add a flag (which is duplicate information) if the data itself easily describes the state that you need to know?

I greatly prefer avoiding adding duplicate data which must itself be maintained. And a single source of the status (the actual data) is all that you need.

Of course, there may be complications unmentioned that could complicate matters.


Change Data Capture and the __$update_mask binary

And the moral of the story is... test, try other things, think big, then small, always assume there is a better way.

As scientifically interesting as my last answer was.  I decided to try one other approach.  I remembered I could do concat with the XML PATH('') trick.  Since I knew how to get the ordinal of each changed column from the captured_column list from the previous answer I thought it would be worth testing if the MS bit function would work better that way for what we needed.

SELECT __$update_mask ,
        ( SELECT    CC.column_name + ','
          FROM      cdc.captured_columns CC
                    INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
          WHERE     capture_instance = 'dbo_OurTableName'
                    AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
                                              PD.__$update_mask) = 1
        FOR
          XML PATH('')
        ) AS changedcolumns
FROM    cdc.dbo_MyTableName PD

It's way cleaner than (though not as fun as) all that CLR, returns the approach back to native SQL code only.  And, drum roll.... returns the same results in less than a second.  Since the production data is 100 times bigger every second counts.

I'm leaving the other answer up for scientific purposes - but for now, this is our correct answer.


It's not possible with DEFAULT. Docs says:

  Restrictions on Default Column Values
  Default column values are
  subject to the following restrictions:

  A DEFAULT expression cannot contain references to PL/SQL functions or
  to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date
  constants that are not fully specified.

  The expression can be of any form except a scalar subquery expression.

So DEFAULT (SELECT country_if FROM country where default_country =1 and rownum=1), and even  DEFAULT (SELECT 1 FROM DUAL) fail because it cannot contain subquery .   You cannot work it around by defining your own function either.
The only way is to create row level before insert trigger .


These two queries may help you.  The first will list all of the tables and indexes on those tables in your database.  If the table does not appear in the list is does not have any indexes defined on it.  These queries assume SQL Server version 2005 or newer.

SELECT
    IndexName = QUOTENAME(I.name),
    TableName =
        QUOTENAME(SCHEMA_NAME(T.[schema_id])) +
        N'.' + QUOTENAME(T.name),
    IsPrimaryKey = I.is_primary_key
FROM sys.indexes AS I
INNER JOIN sys.tables AS T
    ON I.[object_id] = T.[object_id]
WHERE
    I.type_desc <> N'HEAP'
ORDER BY
    TableName ASC,
    IndexName ASC;

The second query will report for each table the identity column, if any on each table in your database.

SELECT
    TableName =
        QUOTENAME(SCHEMA_NAME(T.[schema_id])) +
        N'.' + QUOTENAME(T.name),
    IdentityColumn = COALESCE(QUOTENAME(C.name), N'No identity column')
FROM sys.tables AS T
LEFT OUTER JOIN sys.columns AS C
    ON T.[object_id] = C.[object_id]
    AND C.is_identity = 1
ORDER BY
    TableName ASC;

To limit the queries to a specific table add a WHERE clause similar to:

WHERE T.name = N'NAME-OF-TABLE'


To use or not use ISNULL(NULLIF(Field1, ''))?

The primary difference between your examples is that the latter does not replace NULL with your default value.

With regard to performance, you will generally need to try very hard to find a measurable difference (in the order of a few seconds over millions of rows). Search for performance measures for isnull, coalesce, and case--you'll find lots of blog posts about them.

The common voice I've heard is that you should use the structure that feels the most readable for your team, and any time you get the feeling that it might not be as performant as it could be, test it. Run it both ways, and compare the time it takes to complete, and compare the execution plans.


There are a lot of different ways to do this.

I don't usually recommend inserting into a #temp table, since any tempdb load or autogrowth may impact the results, and I definitely don't recommend using a @table variable, since modifications to those are forced serial (no parallel plan can be used), which may change actual query times.

Variable Assignment

You can declare a variable and assign your columns to it, like this:

DECLARE @Start datetime
DECLARE @End datetime
DECLARE @blob_eater SQL_VARIANT;

SELECT @StartTimeWA=GETDATE()

SELECT
       @blob_eater = [id]
      ,@blob_eater = [database_id]
      ,@blob_eater = [proc_name]
      ,@blob_eater = [exec_t] from
  [DB].[dbo].[STAT]

SELECT @End=GETDATE()

SELECT DATEDIFF(MS,@Start,@End) AS [Duration]

Though doing this may prevent some parameter embedding optimizations. See Parameter Sniffing, Embedding, and the RECOMPILE Options under "An Embedding Restriction".

Note that this method may trigger plan warnings about implicit conversions, but they're not the kind you have to worry about. See this Q&A for background: What Triggers This Warning: Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan Choice.

In SSMS

You can change the settings to discard query results.

SQL Query Stress

SQL Query Stress is an open source tool that allows you to run queries against a SQL Server to simulate load. No query results are returned to the application when they're run.

You can read some instructions on it here.

ostress (RML Utilities)

ostress is a similar tool, published by Microsoft, which also doesn't return results to the client, unless you choose to do it.

I've written some about it here.

Plan Explorer

SentryOne's Plan Explorer is a free alternative to view SQL Server execution plans and deadlocks with.

You can also use it as a client to query SQL Serve to some degree:

This will also discard results.

Hope this helps!


You have these entities to convert in tables.

I suggest you build the fallowing tables in excel and see if all is there or something missing.

Stations : has name , and id .

Routes : connect stations and defines the bus time from one to another station.

Bus_and_start: when will every buss start and what route. Do you have a list of buses , like buss_long1 , bus_short1 , bus_extralarge4 ? Then split this table in 2 , the bus table and the start table. This would be good if a bus breaks or if you monitor real time if is to slow . Real_time monitoring will bring a list of challenges, not sure if this is part of your project.

To see for a station the next hour bus , you take all the routes that stop in that station and join with all bus_and_start and the route time to the bus start time and here is the list. If you need help , define the tables and I will help with the SQL statement.


Possible Double-Hop issue. SSIS User changing to anonymous when making a remote query

Either configure the directory to allow Kerberos Constrained Delegation to work or configure a SQL Agent job to run your package with a proxy (or the perhaps just Agent Service Account), and kick off the job with sp_start_job from your applciation.


Oracle RAC Failover functionality clarification

Just from Oracle side there is nothing to worry about. You could even do this operation during normal business hours.

BUT! most Applications are not cluster ready. They cannot deal with this situation and will crash. All services are still available since they are migrated to the second node. But the session does not get migrated that's why the client will loose the connection. An application which supports RAC would then reopen the connection and just continue to work.

I would plan a downtime for this operation.

For the future you could bond two network ports for the public interface in failover mode. With that setup you could do the operation without downtime. Note that bonding in load balance mode does not work so well -- at least this is my experience.


Why is an entire index that is infrequently used stored in the buffer pool?

Don't blindly rely on the sys.dm_db_index_usage_stats as it does not tell you the entire story.

Refer to Joe Sack's

Exceptions – what sys.dm_db_index_usage_stats doesn’t tell you
Exceptions–what sys.dm_db_index_usage_stats doesn’t tell you (Part II)

  Why would this infrequently used index be taking up so much space in the buffer pool?

In simple terms, SQL Server tends to rely on Buffer Pool to do its normal operations to avoid expensive disk I/O's. If you have enough RAM and have set up MAX Memory appropriately, then all the queries that are ran across your database will CACHE the objects that are required by your queries.

  Doesn't the high number of user_updates compared to the low number of scans and seeks make this a candidate for removal due to maintenance overhead?

It can be the case where the index from your perspective is not useful, but there might be queries that require that index or part of that Index.

I agree that caching such a huge index and if it is really not useful - is a waste of Buffer Pool and you might run into performance issues like more I/O's or excessive plan recompilations if the plan cache is too constrained.

  Should a field with such low cardinality be indexed in the first place?

Not really as maintaining an index - it costs you for modifications like insert, update and deletes as well as disk space. Also, low  cardinality indexes might be ignored by the Query Optimizer as it wont help generate an efficient query plan - its not just selective enough.Try to look into Filtered indexes.

  Is the only way to clear this from the buffer pool to drop and recreate? Clearing the entire cache or buffer pool is obviously not possible on a production box.

Unfortunately, DBCC DROPCLEANBUFFERS does not have any parameters for specific database or objects.

Best is to rebuild the index.


getIndexes is not a function on mongo shell

db.stats() is a function call so db.stats is a function reference which has no function called getIndexes().

To get them db.getCollection("stats").getIndexes()

https://docs.mongodb.com/manual/reference/method/db.stats/


MySQL 5.6.27: How to get latest pairs for different points in time in a single query?

SET @date:='2018-01-03';

select * from ( select ta.*,
                       if(@typex=ta.group_id,
                          @rownum:=@rownum+1,
                          @rownum:=1+least(0,@typex:=ta.group_id)) rown
                from stats_kpi ta, (select @rownum:=1, @typex:='_') zz
                where ts_week_start <= timestamp(@date)
                order by group_id, ts_week_start DESC
              ) yy
where rown < 3

fiddle


How can I better model the relationships in this project?

I am still learning myself so you might not agree with everything I say.

In my business we have 'projects' that last for a certain amount of time and have staff assigned to them.

I would give your Employees a 'default role' eg Manager, Technician whatever. Then when they are added onto a project this role will be used by default in a drop-down menu unless you choose otherwise. This role may affect their pay rate for each project also.

If you really wanted to impress them you could make an application in Visual Basic for creating and updating the tables, you can do this for free I believe.


Get data from database and put it back to a new datasheet

This returns the results you you specified, but I'm not sure it returns the results you want.  It was built on Oracle, so you may need to modify the syntax slightly and of course the table names.

CREATE TABLE T1 (ID Number(3), A Number(3), B Number(3), C Number(3), D Number(3));
CREATE TABLE T2 (ID Number(3), A Number(3), B Number(3), C Number(3));
CREATE TABLE T3 (ID Number(3), A Number(3), B Number(3), C Number(3), D Number(3));

INSERT INTO T1 values (1, 1, 5,  9, 13);
INSERT INTO T1 values (2, 2, 6, 10, 14);
INSERT INTO T1 values (3, 3, 7, 11, 15);
INSERT INTO T1 values (4, 4, 8, 12, 16);

INSERT INTO T2 (
   SELECT T1B.ID, T1A.A, T1A.B, T1A.C FROM T1 T1A
   CROSS JOIN T1 T1B
   WHERE T1A.ID=1
);

INSERT INTO T3 (
   SELECT T1B.ID, T1A.A, T1A.B, T1A.C, (SELECT T1C.C FROM T1 T1C WHERE T1C.ID=2)
   FROM T1 T1A
   CROSS JOIN T1 T1B
   WHERE T1A.ID=1
);

SELECT * FROM T2;
SELECT * FROM T3;


Executing local scripts against remote server?

Obligatory security disclaimer: You shouldn't be ssh-ing into a remote machine as root, neither should you be connecting to MySQL as root.  But I digress.  At least the question implies that the remote MySQL server isn't directly accessible from the Internet.

It's a little bit difficult to be sure whether you're doing this incorrectly or you simply made an error in typing the question.

  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).

You need a capital 'P', not a lowercase 'p', before the port number on the 2nd line... otherwise, you're probably connecting to your local mysql server on port 3306, not the remote one tunneled back to port 12341.

Assuming for a moment that the ssh tunnel is actually being set up correctly, the correct syntax would be:

$ mysql -h 127.0.0.1 -P 12341 -u root -p

That is, connect to the MySQL server listening at 127.0.0.1 on port 12341 with username 'root' and a password which will be prompted for.  Since your local port 12341 is tunneled to the far-end machine, and the destination address in the tunnel is 127.0.0.1, you will be known to the far-end machine as 'root@localhost' where "localhost" means the far end machine -- not your machine.  It's "that" localhost, not "this" localhost.

Now, let's go back and look at the tunnel.  The correct form would be this, assuming ssh version 2:

$ ssh -N -L 127.0.0.1:12341:127.0.0.1:3306 root@remoteserver &

The -N option stops ssh from spawning a remote shell, which you don't need, and which may cause problems when you try to send it to the background with &.

Your environment might be different, but if I omit the -N, my tunnel doesn't work, and I see the following:

[1]+  Stopped                 ssh -L ...

After the -L, the first mention of 127.0.0.1 means "only listen on 127.0.0.1 of my local machine" as opposed to listening on all ipv4 interfaces.  If you don't do this, then every IP address on your machine is exposing the remote MySQL server to anybody who connects on your port 12341.


Oracle Materialized View - Is it Safe to Use Only ROWID as Record Identifier?

ROWIDs are good to use in a transient sense, but they're a poor choice for long-term storage.

"You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later." Source: https://docs.oracle.com/database/121/SQLRF/pseudocolumns008.htm#SQLRF00254

You may need to import and export this database when you upgrade the solution or when you refresh a nonproduction environment. If you do decide to use the ROWID anyway, then a complete refresh of the materialized view would be prudent whenever you copy or move data.


As for how often, I would say that depends entirely on your situation. e.g. How much data do you need to access, how much disk space do you have, how far back will you need to go.

I have a similar process here for similar data, which I partition by month, and then at the start of each month I simply have a scheduled Event that runs and drops the oldest Partition, and creates a new one. This is quick and simple, and keeps the disk space relatively constant. Depending on your current table structure this may or may not be so easy to do.

If you wanted to keep the data, you could simply add a new partititon to the top at the start of the month, creating one huge table that remains searchable, but is held on the system as individual files. If you are using MySQL 5.6 you can even select by partition (a good index also helps).

see How to split a MySQL table file? for a similar discussion.

The other option, if you want to keep things accessible, (which I also do with another system) is to simple run a scheduled event at the start of the month to create a new empty table (CREATE TABLE tablename LIKE..., and rename the old one e.g. tablename_201505. I also mainstain another table to keep track of which months data is in each table for easy refenrence by our applications.


you can use double/float for both exposure and aperture because they carry floating points and int/smallint for focal length. you can use varchar for all this but if you need to do any calculation on these that's not good and when you sort you can't get correct out put you wanted because sever treat these values as strings (if use varchar). in my case i always use numeric data types for numbers.


Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place

You cannot (or, you can, but with pain & risk).

For the ib_logfile[01] - you needn't worry: these are cyclic files, so your info will eventually be overwritten (and if you want to rush it, why don't you invoke a gazillion UPDATE foo SET bar=1 WHERE id=1 on some dummy foo table?

But the ibdata file cannot just be dropped.
Nevertheless you should (no guarantees held) be able to overwrite the file at those particular positions where you find your sensitive data. If you can identify all of them - overwrite them with spaces/whatever (say these are credit card numbers -- overwrite with "0"s).

I wrote on how you can use a replicate to convert all your tables to MyISAM (Assuming no foreign keys), restart, throw away your ibdata file, convert all back to innodb, get on with your life. It's a lengthy process as well. See: Getting rid of huge ibdata file, no dump required


Performance issue with composite primary key or not on Transactional Replication with AlwaysOn Subscribers?

Performance not only depends on the query, but also the kind of data you store in the table.

Let's zoom out and talk about how you choose a clustering key for a table. Generally, you want the clustering keys to be:

Static - non-changing
Unique
Narrow - as few fields as possible, with as small datatypes as practical
Ever-increasing - which gives you a hotspot at the end of the table, which would sound bad but is usually good in small (sub-terabyte) database designs

Here, you've already got a single field that satisfies all of those requests: CLIENT_ID, which you defined as an identity field. You could just start by using that field alone - adding SERVER_ID doesn't really buy you anything.

However, I'm a little suspicious of the table design because SERVER_ID is only a CHAR(1). That gives you a limited number of servers to work with per CLIENT_ID. I don't know what you're trying to model, but generally an ID wouldn't be a single character.


Oracle RMAN - MAXPIECESIZE ignored for Level 0 backups

Try removing the MAXPIECESIZE from the configuration of the channel, and use SECTION SIZE 10G on the backup command.  The following is working for me:

run {
        backup section size 10g incremental level 0  as compressed backupset archivelog all database;
        delete noprompt obsolete;
}

You may need to set paralellism to > 1:

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;

If that does not solve the problem, please post the output of show all;


Steps involved in configuring SQL Server and its components after server is renamed

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

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

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

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


Does an order by clause have a negative effect on performance?

An ORDER BY clause will always add a sort operation if there is no usable index to retrieve the data in order.  For larger sets of data the data being sorted may be written to disk adding additional processing.  For smaller result sets where indexes are being used, there may be no requirement for a sort step.

If sorting is required, the database will have to retrieve all the sort key data and sort it before presenting results.  This can delay the availability of the first rows.  If you only want the first few rows of a large set, this can generate a lot of extra work for the database.

Than being said, I have rarely seen an ORDER BY clause a significant performance impact.  I recall one case where a developer attempted to improve performance by retrieving an unsorted result set and sorting it in the client, but it did not work well.

I avoid the ORDER BY clause unless there is a requirement for data to appear in a given order.  If there is a requirement to present data in a given order, I use the ORDER BY clause.  If there is a question about performance, I test the query against a production database before testing alternatives.


You can snap sys.dm_exec_connections which will give you the net IO metrics for any given connection. The associated sys.dm_exec_sessions will identify the connection client (host, application). Since you need aggregated history and the view gives moment in time snapshot you will have to do the usual transformations to convert moment-in-time to aggregate history.


Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results in another table oracle

We know that it is best when a single SQL statement can be written for solving a problem.  However, your situation requires a SQL statement that would be very unwieldy and difficult to debug and test.  One of your comments* contains the following facts:

  User can enter anything between 0 to 1000 characters (Data is read
  from the excel, and end user do not want any restriction on the
  excel). But on our application, we have to show the errors of the
  excel.

(* see: https://stackoverflow.com/questions/48839473/oracle-plsql-validate-a-row-and-insert-each-cell-value-of-row-as-a-new-row-with )

My suggestions are: {1} Create a table ("checks") that contains all necessary conditions (checks) including error messages, comments etc. {2} Using PL/SQL and BULK COLLECT to read all rows from this table, and subsequently generate dynamic SQL statements that perform batches of tests (checks) and write their results to a log table (your "table B").

{1}

create table checks (
  condition varchar2(256)
, colname varchar2(256)
, errmessage varchar2(256) default 'no error'
);

begin
insert into checks ( condition, colname ) values ( 'LENGTH( column_1 ) <= 7', 'column_1') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_2 ) <= 75', 'column_2') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_3 ) <= 17', 'column_3') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_4 ) <= 17', 'column_4') ;
-- etc
-- etc
-- etc
insert into checks ( condition, colname, errmessage )
values ( 'LENGTH( column_38 ) > 44', 'column_38', 'invalid length' ) ;
insert into checks ( condition, colname, errmessage )
values ( 'LENGTH( column_39 ) > 44', 'column_39', 'invalid length' ) ;
insert into checks ( condition, colname, errmessage )
values ( 'LENGTH( column_40 ) > 44', 'column_40', 'invalid length' ) ;
end;
/

{2}

declare
  type checks_t is table of checks%rowtype ;
  tchecks checks_t ;
  sqlstring   varchar2(1000) := '' ;
begin
  select condition, colname, errmessage
  bulk collect into tchecks
  from checks ;

  for i in tchecks.first .. tchecks.last
  loop
    sqlstring := 'insert into b ( column_name, column_value, column_errorKey ) '
              || 'select '
              || ' ( select ''' || tchecks( i ).colname || ''' from dual ), '
              || 'substr( '
              ||  tchecks( i ).colname || ', 1, 256 ) ,'
              || ' case '
              || ' when ' || tchecks( i ).condition
              || ' then ''' || tchecks( i ).errmessage
              || ''' else ''condition evaluates to FALSE'' end '
              || ' from A' ;
    execute immediate sqlstring ;
    sqlstring := '' ;
  end loop;
end;
/

There is quite a lot of example code in the dbfiddle.  However,  the first 5 sections are just about test data for "table A", which is supposed to resemble data stored in the spreadsheet (40 columns x 16 rows).  With this setup, table B will end up having 1280 rows after the PL/SQL anonymous block has been executed (80 checks are run on each row of table A).

Being a bit wary about the performance of the script, I've done some testing on a smallish VM (Oracle 12c).
Results as follows:

-- table A with 50 rows -> 4000 generated INSERTs into B (80 checks per row)
-- PL/SQL procedure successfully completed.
Elapsed: 00:00:00.050

-- table A with 234256 rows -> 18740480 generated INSERTs into B
-- PL/SQL procedure successfully completed.
Elapsed: 00:02:18.305

SQL> select count(*) from B;
COUNT(*)
18740480

Elapsed: 00:00:25.945

Due to limitations of Oracle 11g (no identity columns) and dbfiddle (could not create a sequence), there are no ID columns or PK constraints anywhere in this example.  Also, the error messages (in the checks table) can be a bit misleading, but all that can be "easily" adjusted .


If you are looking for a locking mechanism, You have several options.
For example you may create a new table where you track who is editing and what is being edited. This new table will have an unique key with the fields of "record_edited".

When an administrator starts to edit a hotel detail, you insert a row into this table, and if the result says duplicate entry, then there is somebody already having that record modifying, and tell the admin to wait a little.

If this query returns without error, you then allow the admin to proceed.

When the admin finished with the update on the hotel details, you then remove the "locking record" from the table, and update the hotel details in the database.

Example table for the locking mechanism:

CREATE TABLE `mod_lock` (
 `who_editing` int(11) NOT NULL,
 `record_edited` int(11) NOT NULL,
 UNIQUE KEY `record_edited` (`record_edited`)
)

This method will allow multiple hotel details being edited, while "table lock" will not permit changes to the whole table for the hotel details until the admin finishes with one update.


I would say to try to use a different storage engine like InnoDB.

It sounds like you were using MyISAM which isn't crash safe. If you want to continue using MyISAM and it crashes in the future, you can fix it with an external program called myisamchk.

http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html

 myisamchk --force --key_buffer_size=512M --sort_buffer_size=64M --read_buffer_size=8M --write_buffer_size=8M  /path/to/datadir/*/*.MYI


Postgres 9.2 under FreeBSD 8.4 - How do I find the databases & tables?

Don't mess with the data files in the filesystem it's a sure way to get your database corrupted.

You can achieve what you want, by creating a tablespace and then move the table to the new tablespace:

create tablespace big_one LOCATION '/path/to/other/spindle';
alter table map_data set tablespace big_one;
commit;

Btw: MySQL isn't "more straightforward" - it's just different. And I could list several things that are much more "straightforward" in Postgres than in MySQL


MySQL: columns with mixed character encodings, and finding columns which have multibyte data

The data size is not 4x.  English text, even in utf8mb4, takes only one byte per character.  Trademark(etc) symbols are multi-byte.  Then ones you mentioned are only 2 bytes.  Emoji and some Chinese is where 4 bytes become necessary.

Don't create indexes on large columns.  Don't create indexes until you have the queries -- derive the optimal indexes from the queries.

It is perfectly fine (in MySQL, at least) to have one column be latin1 and another be utf8mb4 (etc).  And both of those can be in the same index.

I suggest making a couple of passes over the data.  First bring everything in with utf8mb4, no indexes, wide columns (eg TEXT).  Then analyze what you got -- SELECT MAX(CHAR_LENGTH(col2)), ...; test for non-latin1, etc.  For the second pass, re-do the schema to be closer to max len, etc.


A pivot transposes rows to columns based on a discriminator column. Your example data doesn't show a discriminator column, and the data also seems to indicate that this could lead to a variable number of columns (depending on the attendance per date), which is not possible in Firebird. Instead it looks like you want to aggregate those dates in a single column.

Assuming that is what you want, you can use LIST combined by a group by on the date part of the timestamp, for example:

select surname, name, list("WHEN", ' ')
from users
left join attendant on users.id = attendant.userid
group by surname, name, cast("WHEN" as date)

Be aware: LIST does not guarantee an order. You may need to use an intermediate order by on "WHEN" in a sub-query as a work around, but that trick does not always work (especially as in this case we're grouping on a derivative of "WHEN").


myisam_recover_options is not a dynamic variable. It is among the startup options for MyISAM.

If you have set this option in my.cnf, mysqld must be restarted

Please restart mysqld with

# service mysql restart

or if you have Windows

C:\> net stop mysql
C:\> net start mysql

You should try switching your table to InnoDB and make your table crash-safe.


Diff in idx_tup_read and idx_tup_fetch in postgres pg_stat_all_indexes

idx_tup_read is increased whenever an index entry is read.
idx_tup_fetch is increased whenever an index scan fetches a table entry.
If your index-only scan increases idx_tup_fetch, it is not truly index-only. PostgreSQL had to fetch the table rows anyway just to check if they are visible or not, an information that is not stored in the index.
You can see that as "heap tuples fetched" in EXPLAIN (ANALYZE) output.
The solution is to VACUUM the table so that the visibility map gets updated.


How to handle temporal datatype columns when doing upgrade from 5.5 to 5.6

Without the upgrade, you cannot use fractions of a second.

Old data structure was 4 bytes for TIMESTAMP, 8 bytes for DATETIME.
New structure is 5 bytes for each, depending on how many decimal places of time you pick.
If you later allow for fractional seconds, the space is up to 8 bytes.


Try add each trace flag separately.

Some trace flags are now build in, in SQL Server 2016 like TF 1117, TF 1236 only applies to SQL 2012 and 2014 without SP1, TF 4199 is now default as is TF 8024.

For the complete list of trace flags go to this url: https://msdn.microsoft.com/en-us/library/ms188396.aspx


How to model a car (year, make, model, trim)?

The first model is more accurate for your business needs. I would go a little bit further and include an index (unique) that includes NAME and YEAR.
If you keep the model as it is; for example, the model doesn't prevent you to have this data:

TRIM:
id     model_id     name     year
==================================
1      1            SL       2013
2      1            SL       2013
3      1            SL       2013

To avoid this situation create one index in TRIM with this:

CREATE UNIQUE INDEX trim_idx ON trim (model_id ASC,name ASC,year ASC);

And do a similar action for MODEL

CREATE UNIQUE INDEX model_idx ON model (make_id ASC,name ASC);


MySQL id not saving in order (Laravel in production)

I think the auto_increment on your tables is set to 10 instead of 1.


DELETE IGNORE on Tables with Foreign Keys Can Break Replication

I have read it here: https://www.percona.com/blog/2012/02/02/stop-delete-ignore-on-tables-with-foreign-keys-can-break-replication/

The details of how DELETE IGNORE can be unsafe for replication is shown in that blog.

The solution to work around the issues to do either one of:

Don't use DELETE IGNORE
Use Row-Based Replication by setting binlog-format=ROW


SQL Server logins "un-sync" when Availability Group fails over

I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.

If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN... on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.

IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.


If you have the original SQL statements used to define the tables, you can create new empty tables. That will give you the .frm files you need.
Then you can try to attach the .ibd files you have using importable tablespaces. But those .ibd files probably don't have the required import files. There are blogs about how to binary-edit tablespace files to modify the tablespace id so you can attach it. This is not a solution for a novice.
Honestly, you're better off restoring from a recent backup.
If you didn't make a recent backup, then this will be an unfortunate learning experience about the importance of making backups.


Connection problems with ManagePartition executable

You are not using the

 /Server:<string>                   /S Server name, default = (local)

  Failed to connect to server (local). ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

From the readme page :

ManagePartition
    /Command:<string>       short form /C command, default = CreateStagingFull
    /Server:<string>                   /S Server name, default = (local)
    /User:<string>                     /U User name, default = null
    /Password:<string>                 /P Password, default = null
    /Integrated[+|-]                   /E Integrated Security, default +
    /Database:<string>                 /d Database name
    /Schema:<string>                   /s Schema name – use quotes as delimiter if needed
    /PartitionTable:<string>           /t Partition table name - use quotes as delimiter if needed
    /PartitionNumber:<int>             /p Partition Number
    /PartitionRangeValue:<string>      /v Value string to input to partition function to specify partition number
    /StagingTable:<string>             /A Staging Table Name, default = null
          /ScriptOption:<char>               /O Scripting Option -- i à Include Script, o à Script Only (no execute)
          /ScriptFile:<string>               /f path and name of file for generated TSQL -- if excluded, script any output to stdout
    /Keep[+|-]                         /K Keep Staging Table if ClearPartition, default +

<Command> = ClearPartition | CreateStagingFull | CreateStagingNoindex | CreateStagingClusteredIndex | IndexStaging


Date format on the output of cmdshell dir C:\ /TC

You can get the date format of dd/mm/yy by using wmic command. This command allows you to get current date without getting affected by regional settings.

**@echo off

    SETLOCAL EnableDelayedExpansion

    for /f "skip=1 tokens=1-6 delims= " %%a in ('wmic path Win32_LocalTime Get Day^,Hour^,Minute^,Month^,Second^,Year /Format:table') do (
        IF NOT "%%~f"=="" (
            set /a FormattedDate=10000 * %%f + 100 * %%d + %%a
            set /a FormattedTime=%%b * 10000 + %%c * 100 + %%e
        )
    )

    for /f %%f in ("D:\Test*.zip") DO set filename=%%~nf
move "D:\Test*.zip" "D:\%FormattedDate%%FormattedTime%-%filename%.zip"**

You can save it as date.bat and run this batch file by executing the following in command prompt:

C:>date.bat


Going out of disk space in the course of querying a postgres database

Of course, there are actually many ways for a SELECT to take disk space. Just look at the docs for work_mem

  work_mem (integer)
  Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to four megabytes (4MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.

Even with a high work_mem a SELECT can chew away disk space. You can for instance, have a function required that writes to disk directly.

You may be interested in mitigating this by using temp_file_limit

  temp_file_limit (integer)
  Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. A transaction attempting to exceed this limit will be canceled. The value is specified in kilobytes, and -1 (the default) means no limit. Only superusers can change this setting.


Sequence number shows a lower value after dump file import

If you run export command against live database without parameters like flashback_scn or flashback_time which ensures consistency during the export, then this can be an expected behavior because these objects are exported in different time and one can become inconsistent with another.

For example, if the sequence object is exported before the table and the application has inserted new records using the sequence and exported later then table and sequence can be out of sync.

Stopping application before starting the export process or using expdpparameters like flashback_time(this may not export all the transactions made after the time specified in this parameter unless you stop the application) can prevent this situation. Or you can adjust the sequence value after the import by recreating it.


Multi-insert batch executed remotely 5x slower than locally

So there's only one batch (SqlBatchCompleted) involved here, as I understand. Assuming that:
My guess is that the difference is because of (the lack of) the network stack.
The client network library default to try Shared Memory first. This doesn't "dip down" in the network stack. This is likely the one you end up with from the local SSMS.
But Shared Memory will of course not work from the remote machine. The next tried is TCP, which is likely the one you end up with from the remote SSMS (unless you either re-configured that client or re-configured SQL server - both pretty unlikely).
So you compare using a netlib which uses a shared memory area to one that travels the code path of the network stack in both directions and also has bandwidth and latency because of the network in between.
Things you can play with includes:

Force the TCP netlib when running locally (option in SSMS client connect dialog).
Play with various network packet sizes (also available in SSMS connection dialog).
Talk to your network people and try with jumbo frames and such things.


In what data type should I store an email address in database?

I've always used VARCHAR(320). Here's why. The standard dictates the following limitations:

64 characters for the "local part" (username).
1 character for the @ symbol.
255 characters for the domain name.

Now, some folks will say you need to support more than that. Some folks will also say that you need to support Unicode for domain names (meaning you have to switch to NVARCHAR). While the standard may change in the meantime (it's been a while since I've had skin in the game), I am quite confident that at this time most servers in the world will not accept Unicode e-mail addresses, and I am sure many servers will have issues creating and/or accepting addresses with > 320 characters.

That said, you can prepare for the worst now, if you like (and if you are using Data Compression in SQL Server 2008 R2 or better, you will benefit from Unicode compression, meaning you only pay the 2 byte penalty for characters that actually need it). This way you can make your column as wide as you want, and you can let people stuff any too-long junk in there that they want - they won't receive an e-mail if they give you junk just like they won't receive an e-mail if the insert fails. The problem is if you let invalid junk in, you have to deal with it. And no matter what size you make it - if someone will try to stuff 400 characters into a 320-character column, someone will try to stuff 1025 characters into a 1024-character column. There is no reason any sensible person should have an e-mail address > 320 characters unless they are using it to explicitly test system boundaries.

But stop asking for opinions on this - and stop looking at other implementations for guidance (it just so happens in this case that the ones you referenced did not bother to do their own homework and just picked numbers out of their, well, you know). You have direct access to the standard - make sure you consult the most current version, support that as a minimum, and stay on top of the standard so you can adapt to changes in specs.

EDIT thanks to @ypercube for the ping in chat.

As an aside, perhaps you don't want to dump the whole address into a single column in the first place. Normalization might suggest that you don't want to store @hotmail.com 15 million times when a much skinnier FK int would work just fine and not have the additional overhead of variable length columns. You could also normalize the username, as john.smith@hotmail.com and john.smith@gmail.com share a common username - they don't know each other but your database doesn't care about that.

I talked about some of this here:

http://www.mssqltips.com/sqlservertip/2657/storing-email-addresses-more-efficiently-in-sql-server/

http://www.mssqltips.com/sqlservertip/2671/storing-email-addresses-more-efficiently-in-sql-server--part-2/

This introduces challenges however to the 254-character limit above, since there doesn't seem to be consensus about what happens when a valid 255-character domain is combined with a valid 1-character localpart. This should be accepted by most servers around the world but seem to violate this 254-character limit. So do you create a Domains table that has an artificially lower restriction on length for e-mail addresses, when the domain could be re-used as a valid 255-character URL?


Search for rows with coordinates within a Polyline

When Google says Polyline they're referring specifically to the Encoded Polyline Algorithm Format. That's a rendering format though. Essentially, you're rendering a point (with longitude, and latitude) as an ASCII string for easy API-transfer. That's not useful to you.

In GIS terms, what you need to do is take the Polyline from Google and construct a GIS LINESTRING from it. A GIS LINESTRING is a series of sequential points. For completeness in parlance, a line segment is a LINESTRING between just two points. A LINESTRING includes one or more line segements.

How do you get the Polyline to LINESTRING? There are many ways. For PostgreSQL with PostGIS that's easy because you have ST_LineFromEncodedPolyline which will do it. In MySQL you'll either have to do this in the app, in your case PHP, or write a very complex function (like ST_LineFromEncodedPolyline in PostGIS) to do it.

Personalized advice: in your case I would convert from PolyLine to WKB in PHP and then bring it into the database with ST_GeomFromWKB

Either way, after you have the LINESTRING you'll have to

Buffer the LINESTRING to create a POLYGON with ST_Buffer
Use MBRCoveredBy(linestring|polygon, pt)

Then in the end, it'll look something like this,

SELECT MBRCoveredBy(
  ST_Buffer( ST_GeomFromWKB( ? ) , DISTANCE_IN_METERS ),
  t.pt
)
FROM TABLE as t;

Where ? is the WKB output from PHP.


SQL Server 2016, temporal tables and compressed indexes

This is default behaviour for temporal tables, the history tables are PAGE compressed by default. This is done out of space & performance considerations, compression will reduce the amount of data that has to be processed, both on disk & memory. This will have a trade-off in extra CPU cycles as SQL Server has to decompress the data. So unless you are under CPU pressure, compression is usually a good thing as your disk IO & memory usage improve.

More information on temporal tables: Temporal Table Considerations and Limitations

For what sp_BlitzIndex page says about it:

  Abnormal psychology findings aren’t necessarily problems– just look at
  this as a big FYI. Some implementations of indexes may mean that the
  database can only be restored in a certain edition of SQL Server, or
  they may change performance of some operations. Which apply to you?
  Check out details on your finding below.

So as stated above, this does not mean it is a problem. For the reasons described above.


SQL Server connectivity without specifying tcp/port

Just to clarify your question... you wish to connect to the instance on either server without the need to specify the Port Number?

In that case, did you check if the instance name is added into your SQL configuration manager?

Open SQL Configuration Manager
Expand "SQL Native Client Configuration"
Select "Aliases"
See that if the instance that you wish to connect exists, if no, create the alias for the instance

Hope I do answer your question...


Database design for ~ 2.7B rows per year / 26.000 data points every five minutes

I work for an IoT company and have recently implemented something similar (device sensor data readings) using a Kinesis stream, Spot Instances and DynamoDB.

The Spot Instances were a cost saving step to replace the Lambdas, which were processing the stream data and bulk inserting to DynamoDB, they were costing too much.  We changed the Lambdas to EC2s and then to Spots to save cash.

I would suggest trying the Lambdas to get data from SQS and put it in a DB, but for scale and cheapness please look at DynamoDB for storage. The downside of DynamoDB is that you have to know your query paths before you build the table but as you are using an API you will probably know what those are.


How can I index a text[] field in postgresql?

You probably can use the citext module to query things case-insensitively.  But I would rather just impose discipline in the system to only store lower case values, and only query lowercase values. Either one would let you get rid of the
ILIKE, which will then let you use a suitable GIN index on the array values.

This does create an annoying syntax issue, as there is no built-in operator to ask if an array contains a scalar, you have to ask if an array contains another array which just happens to hold one element.

create index on people (first_name);
create index on people using GIN (alternate_names);
select * from people where lower('Balzaretti')=first_name or
    ARRAY[lower('Balzaretti')] && alternate_names;

If you don't want to specify the name twice (and avoid the BitmapOr on execution), you could combine first_name into the alternate_names list, either by changing your code to just do that, or by using an expression index:

create index on people using GIN ((alternate_names || ARRAY[first_name]));
select * from people where
    ARRAY[lower('Balzaretti')] && (alternate_names || ARRAY[first_name]);


Home sensors database - optimising model

Quick Note
You probably want to ensure that a measurement of 760 mm Mg is not accidentally identified as a Temperature measurement.  As such, you really want to keep the two values in different columns (with appropriate CHECK constraints).
Now What?

Temperature IS A measurement
Humidity IS A measurement
Pressure IS A measurement

There are a few ways to build "IS A" relationships in a database.
The method I am recommending has a one "master" measurement table and a "specific" table for each type of measurement.  You'll need to decide if you can group some measurements together; in which case, they'll go into the same "specifics" table.
-- pseudo code.  Actual would be RDBMS specific

create table measurements_meta (
  measurement_id  int primary key, -- "Specifics" tables get the same ID.  therefore, the same column name.
  sensor_id       int not null references sensors(id), -- I dropped the unnecessary TBL_ prefix
  room_id         int not null references rooms(id),
  time_of_measure datetime not null default now()
);

create table measurements_temp (
  -- this enforces the 1:0 or 1 relationship
  measurement_id int primary key references measurements_meta( measurement_id ),
  temperature    number not null check ( temperature between -40 and 150), -- F
);

create view temperature
as
select a.*, b.temperature
from measurements_meta a
  join measurements_temp b on a.measurement_id=b.measurement_id;

-- RDBMS Specific
create trigger temperature_tr
instead of insert or update or delete
on temperature
before each row
-- fill in code here to do the actual DML

grant select, insert, update, delete on temperature to my_app_user_role;

In this design, the data Model remains hidden from the application.  The application will View the data through the VIEW. Control of the data (DML) is done through simple CRUD operations on the VIEW or through APIs (stored PROCEDURES).  TL;DR - I suggest you use the MVC design pattern.
If you can take multiple (simultaneous) readings (eg 2 thermometers for 1 device), the individual readings would go into a child table under that "specifics" table.  The "specifics" table would record the average.  You would need to use an API (instead of CRUD on the VIEW) which can take in the required number of reading in one call (eg an array of temperatures).


How do I determine why my stored procedure is getting deadlocked?

I think "deadlocked" is the wrong term in this case.  Your query was getting blocked.  Deadlocked means that it is blocked by another process that is in turn blocked, waiting for your original process to complete.  Since these two processes will never complete, one of them is chosen to the victim of a deadlock, and it is terminated, allowing the other one to continue.


IN subselects vs INNER JOIN (performance)

The best thing to do would be to test them in a non-production environment with similar workload and look at the Execution plans for both.  This is an advanced topic and I don't pretend to know everything about it but you might see noticeable execution time differences from the get-go in bottom right of SSMS.  If not, looking at the plans, pay attention to 'scans' and 'key lookups' as those should be avoided whenever possible.  What you generally want to see is a small number of seeks, which means it's going into the table with purpose and knows what it's looking for.  Scans mean it's going row by row through the entire table so much slower.  If one does a seek versus a scan, go with the seek!

You can also use commands like:

set statistics time ON;
set statistics IO ON;
GO;

And then run the queries individually to see the number of reads/writes as well as seeks and scans if you are uncomfortable with the execution plan approach.  Here is a good link as an introduction and if you aren't familiar with Brent Ozar, I'd highly suggest checking out his free scripts and he's blogged a ton about perform tuning.


In general terms, a clause is just a logical chunk of a SQL statement - and, usually, is a chunk that is (theoretically) optional.

I'm a SQL Server guy, so I'll be thinking in those terms.

SELECT GETDATE() is a valid SQL Server SELECT statement.  It'll return the current date and time.

If I add a FROM clause:

SELECT GETDATE()
  FROM sys.objects

I'll get back n rows (where n is the number of rows in the sys.objects table), each of which will have the same (unnamed) column, showing the current date and time.

You can add a WHERE clause to limit the number of rows you bring back; a GROUP BY clause to either deduplicate your data, or to allow you to put aggregate values in the SELECT list; a HAVING clause (usually to eliminate rows based on aggregate data); an ORDER BY clause to sort the data - but, none of those have to be there.

You'll note I said "SELECT list" when referring to the list of columns returned. I've never heard this referred to as a clause - presumably, because it does have to be there.

An operator, on the other hand is used to combine or compare two complete items. Just like the + operator lets you combine numbers (1 + 2) or strings ([firstname] + ' ' + [lastname]), the UNION, EXCEPT, DIFFERENCE, and INTERSECT operators let you combine two SQL statements.

I'm probably oversimplifying, but thinking in these terms should at least head you in the right direction.


MongoDB Ops Manager - CPU - No Data Available

Ref:

https://docs.cloudmanager.mongodb.com/tutorial/configure-monitoring-munin-node/

To chart the hardware statistics collected with Munin, Cloud Manager supports the following munin-node plugins:

cpu plugin, which creates the cputime chart.
iostat plugin, which creates the iostat chart.
iostat_ios plugin, which creates the iotime chart.

  You must install the munin-node package on all of the host systems
  that you wish to monitor. Ensure that the Monitoring Agent can connect
  to the munin-node process on port 4949 of the monitored host to
  collect data.


Combining data from multiple sources in one column

It seems you want a UNION:

SELECT id AS aid,  cid,  somedata
FROM a

UNION -- ALL

SELECT a.id,  c.id,  a.somedata
FROM a JOIN c ON a.bid = c.bid ;

It's not clear if you want a UNION DISTINCT or UNION ALL. If it's the first, you could combine the subqueries into one, using OR. That may produce different plan though, which may be less efficient:

SELECT a.id AS aid,  c.id AS cid,  a.somedata
FROM a JOIN c ON a.bid = c.bid
              OR a.cid = c.id ;


SQL Server Query with literals runs instantly, using variables takes minutes

The root cause of this is simple. The query optimizer makes row estimates to determine how it's going to resolve the query. The row estimates are based on the statistics for the columns involved. When you give the optimizer literals, it uses exact values to look at the statistics for row estimates. In this case, those estimates result in a plan that gives you the seek, meaning it's found that for the specific value there are only a few matching rows and it uses the index to find those rows.

When you give the optimizer a variable, it can't know what the value within the variable is. So, instead of looking at the statistics for a specific value and making the row estimates based on that, it uses an average based on the overall selectivity of the statistics. In this case, an average suggests to the optimizer that lots of rows are likely to match so a scan is the more efficient mechanism for retrieval.

First thing I'd try is an update to the statistics. It's possible that they're out of date and the row estimates with a variable are just inaccurate. For the best possible statistics, use the FULL SCAN option. That will take longer and use more resources.

Next, possibly changing the query to a stored procedure and using parameters instead of local variables could result in better plans. This is because parameter values are known at compile time through a process called parameter sniffing (sorry, I didn't name it). You may see more consistent behavior because of parameter sniffing (although, sometimes you can see inconsistent behavior because of parameter sniffing).

Next, yes recompile could help. It may be that your data is just too wildly skewed for the row estimates to ever be consistently right. Recompiling every time you execute will mean that a plan unique for the values passed will be generated. Further, recompiling means that the variable values are now known and the sniffing process is applied.

It's also possible that the query just needs some restructuring. Lots of ORs like that with long lists of filter criteria sounds like a catch-all query. Recompile can help there, but you also might be better off restructuring to either nested procedures (73 of them is a lot though) or using dynamic T-SQL to build individual queries instead of long, lists of OR statements.

The last thing you have is that you're on SQL Server 2017. You can enable Query Store and use plan forcing to make it use the plan that performs better.


Updating system tables directly is unsupported and unsafe. The structure of them has changed since 2000 so the code you have won't work against them anyway.

It is sometimes possible to use ALTER TABLE ... SWITCH to do this as a metadata only change however.

A full worked example below.

/*Create table with option off*/
SET ANSI_NULLS OFF;

CREATE TABLE dbo.YourTable (X INT)

/*Add some data*/
INSERT INTO dbo.YourTable VALUES (1),(2),(3)

/*Confirm the bit is set to 0*/
SELECT uses_ansi_nulls, *
FROM   sys.tables
WHERE  object_id = object_id('dbo.YourTable')

GO

BEGIN TRY
    BEGIN TRANSACTION;
    /*Create new table with identical structure but option on*/
    SET ANSI_NULLS ON;
    CREATE TABLE dbo.YourTableNew (X INT)

    /*Metadata only switch*/
    ALTER TABLE dbo.YourTable  SWITCH TO dbo.YourTableNew;

    DROP TABLE dbo.YourTable;

    EXECUTE sp_rename N'dbo.YourTableNew', N'YourTable','OBJECT';

    /*Confirm the bit is set to 1*/
    SELECT uses_ansi_nulls, *
    FROM   sys.tables
    WHERE  object_id = object_id('dbo.YourTable')

    /*Data still there!*/
    SELECT *
    FROM dbo.YourTable

    COMMIT TRANSACTION;
END TRY

BEGIN CATCH
    IF XACT_STATE() <> 0
      ROLLBACK TRANSACTION;

    PRINT ERROR_MESSAGE();
END CATCH;

One caveat however is that this does not always work. Adding a check constraint with definition CHECK (X <> NULL) causes it to fail with

  ALTER TABLE SWITCH statement failed. Table 'YourTableNew' has a column
  level check constraint 'CK_YourTableNew_X__37A5467C' on column 'X'
  that is not loadable for semantic validation.

This makes sense as the semantics of the constraint do change with the setting.

If you encounter this you could consider temporarily removing the problematic constraints etc and adding them back after the switch or just biting the bullet and recreating the table in a more conventional way.


In the commandline tool, use forwardslash, not backslash, for path delimiters:

source /home/user/Desktop/test.sql;


I decided to look at the sys.sysprocesses table and found that FT_CRAWL (looking in the CMD field) was running on the blocking spid.

I guess a restored database starts crawling right away? Anyways, I shut off the Full Text Daemon and the blocking ceased. We finished the upgrade and then turned the Daemon back on and all was well in the world.

I have emailed Adam Mechanic to see if the CMD field is used in his sp_WhoIsActive, as I think that would be very beneficial in this type of situation.


Table not getting created under dbo schema

The schema that will be used when schema is omitted will be the default schema of the database user.  Therefore, to have dbo schema have the created table without specifying, you'd have to set that database user's default schema to dbo.

Server-wide setting?  I don't think so.  But you could write a query to convert all database users in a particular database to their default schema changed to dbo if that's what you so choose.


How do I determine if pgAgent is installed on a Postgres instance?

From Database setup:

  pgAgent stores its configuration in the ‘postgres’ database in your
  cluster

And it creates there a schema named pgagent with a handful of tables prefixed by pga_

It's probably good enough to connect to the postgres database and interpret the result of:

select 1  from information_schema.schemata  where schema_name='pgagent';

Due to how information_schema.schemata is restricted, the above should be run as postgres user (otherwise consider querying directly pg_namespace as the non-standard alternative).


is it possible to generate create index sql in MySQL 5.7

You can use SHOW INDEXES FROM table_name; to script the list of indexes on that table.


Can SQL Server be configured to backup to a network share through a separate NIC?

Yes, see the Microsoft whitepaper A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabyte Database Over the Network (DOC). On page 15, they explain how to separate out the backup targets onto multiple subnets and volumes.


SSIS Data Flow Task Violates Unique Constraint But Linked Server Insert Succeeds

I had the O.P. check for duplicates in the destination table. The following query was used:

SELECT ITEM_GROUP, ITEM, COUNT(*)
FROM Lawson.dbo.ITEMMAST
GROUP BY ITEM_GROUP, ITEM
HAVING COUNT(*) > 1;

The result was:

  0x2020202020202020202020202020202020202020202020202020202020202020

The row starting with 7 "nul" characters (i.e. CHAR(0) or 0x00, see image #2 in the question) was replaced with a row of all spaces.

The problem is that the first character is 0x00, which is the null terminator for strings. Pulling that value into SSIS (i.e. .NET) probably viewed it as a simple empty string. And inserting an empty string into a CHAR() column (i.e. a blank-padded datatype) will naturally leave you with all spaces. Hence your duplicate row.

At this point, I would say that it's simply bad data, unless you feel a need to preserve an invalid string (no string should ever have anything after a 0x00, nor should it really even have the 0x00, but if it does, it would be the last character).

So, you can correct that row at the source, but it can't be an empty string since that would get the same PK violation that you are running into now. Is there a default dummy value?

Or, if that row is truly invalid / not used, then I would remove it, OR maybe find out how that row got there in the first place. Did someone manually enter it, did the app create it? Was it a botched import? You might take this opportunity to add a CHECK constraint to ensure that no row has the 0x00 character in it in the future (this would help identify the culprit in the future). Then delete the row from QA and move on... :)

I suppose you can always try to exclude that row if need be, but it would be best to prevent it from ever existing. It will never appear correctly when selected. And there might be other negative impacts downstream in the future. How much time have you (and I ;) spent on finding / fixing this? Do you really want to do it again, or have someone else hunt this down? If that row could never show up in prod, then it should be removed from QA.


Why does Management Studio not automatically create FK column in the parent table?

The comments on the question explain some of the perils of using Management Studio to make schema changes, so I won't get into that.

What I want to show is how to accomplish what you're asking using T-SQL:

ALTER TABLE [dbo].[MyTable]
    ADD NewColumn int NOT NULL
        CONSTRAINT FK_MyTable_MyOtherTable
            FOREIGN KEY REFERENCES [dbo].[MyOtherTable](Id);

The syntax is extremely powerful as you can create multiple constraints (DEFAULT, PRIMARY KEY, etc.) on the same column using this syntax (even named constraints as I demonstrated above). The best part is that it's an atomic operation, so if any part of it fails, the whole thing rolls back automatically.

See the ALTER TABLE documentation on MSDN for more information.


Couchbase does not track the created date in the meta-data of an object. You would have to add this to your application to add to each document.