mysqlbackup failed with error when --use-tts option is used I just found an interesting behavior when using the mysqlbackup feature to create a backup of a database and i'm wondering if i missed any steps or maybe a config that i missed or created a typo somewhere.
When running the following command:

/path/to/mysql_enterprise_backup/bin/mysqlbackup --host=127.0.0.1
--user=$BACKUP_USER --password=$BACKUP_PASS --include-tables="^*.*$" --with-timestamp --use-tts --backup-dir=$BACKUP_DIR --compress backup-and-apply-log

I get an error at the end of the script:

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
86 87 88 89 90 91 92 93 94 95 96 97 98 99  210120 09:53:45 PCR1
INFO: We were able to parse ibbackup_logfile up to
lsn 31466657750. 210120 09:53:45 MAIN    INFO: Apply-log-compress operation completed successfully. 210120 09:53:45
MAIN    INFO: Full backup prepared for recovery successfully.
MAIN: Can't get stat of
'/path/to/backup/hotbackups/2021-01-20_09-51-53/datadir/ibdata1'
(Errcode: 2 - No such file or directory) 210120 09:53:45 MAIN ERROR:
Unable to get the size of file
/path/to/backup/hotbackups/2021-01-20_09-51-53/datadir/ibdata1. Error
code: 2, No such file or directory
mysqlbackup failed with errors!

As per the error, the script attempts to get the size of the ibdata1 file, but it can't find it and fails.
the only folders available in the datadir directory are my schemas and ibbackup_logfile, but not ibdata
Now this is where it get's interesting and also where i'm getting lost.
I'm using the --use-tts option when creating my backup (in order to be selective and restore in another environment) but looking at the doc for the use-tts option https://dev.mysql.com/doc/mysql-enterprise-backup/3.12/en/backup-partial-options.html#option_meb_use-tts and i quote:

The system tablespace is not backed up, saving disk space and I/O
resources

With this in mind, if i understand correctly, the --use-tts option will not backup and create a ibdata file but the command mysqlbackup will still check for that specific file even though the option to not create it has been specified in the command.
This is causing the backup to get the fail message.
My dilemma at this time is that the mysqlbackup script fails even though my backup seems to have been done correctly. (i'm assuming it was done correctly because of this log entry: 210120 09:53:45 MAIN INFO: Full backup prepared for recovery successfully.)
Has anyone noticed this before?

This is some error cause by bad usage of the tool. Make sure you are running a mysqlbackup version that is compatible with the server version of MySQL your databases are running. Note that upgrading only either one will not work, so for instance MySQL version 8 and older only works with MySQL Enterprise Backup 8 or older.
Check from the documentation.


Working with PostgreSQL information_schema and tables (with same names, in different schemas) whose names start with a number I'm needing to work with tables whose names start with numbers. For example, in the crs_1973 schema there is a table called 2015_04_21. I know that when the table name is made up of numbers, I must double quote it to work with it, for example, SELECT * FROM crs_1973."2015_04_21"; (this works for me). However, I want to extract information about this table from the PostgreSQL information_schema. Specifically, I need the data types of the columns in the table 2015_04_21,

I have tried this query with a table whose name starts with a letter and it produces the desired result:

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'some_table_name_that_starts_with_a_letter'
ORDER  BY ordinal_position;

However, when I run this query with the tables whose names begin with a number, it does not. For example:

WHERE  table_name = 'crs_1973.2015_04_21'

does not work. Nor does:

WHERE  table_name = 'crs_1973'.'2015_04_21'
WHERE  table_name = 'crs_1973'."2015_04_21"
WHERE  table_name = 'crs_1973'.'"2015_04_21"'
...

etc. How do I make this query work for a table name that starts with a number? Is this possible?

There are tables called "2015_04_21" in several different schemas so I need to pass a qualified table name to the query.

@ypercube's solution (in the comments above):

SELECT column_name, data_type
FROM   information_schema.columns
WHERE table_schema = 'crs_1973' AND
table_name = '2015_04_21'
ORDER  BY ordinal_position;

worked perfectly. I'm writing it up here so that I can accept the answer.


max allowed packet keep changing I have a very strange case in mysql 5.5.46. the max_allowed_packet keeps resetting to 1024

show variables like 'max_allowed_packet';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_allowed_packet | 1024  |
+--------------------+-------+

although it is set in my.cnf to 32M

[mysqld]
max_allowed_packet=32M

and even if i try to run set global max_allowed_packet=33554432; it shows success but does nothing

mysql> set global max_allowed_packet=33554432;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_allowed_packet';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_allowed_packet | 1024  |
+--------------------+-------+
1 row in set (0.00 sec)

when i restart mysql it becomes 32M for a day or two then it back to 1024!! what could be the reason?

Sounds like some code somewhere is performing SET @@max_allowed_packet=1024;.

You could turn on the "general log" to find out where it is coming from.


Find queries causing a lot of read IO I'm trying to find the cause of a higher amount of VolumeReadIOPs in our Aurora Postgres instance on AWS. This is what the past 6 weeks look like:

I found the cause of the largest period (02/27 to 03/12), which was a really bad query running almost constantly.

However, since 03/12, I'm seing a larger amount of reads than I did in the period before 02/27. But I can't find the source of this just by looking at what has changed in the code.

What kind of logging kan I enable to find the source of this?
Are there any other CloudWatch metrics that can help me figure this out?

Is your concern performance, or that you are charged per IO?

Activate "pg_stat_statements" and turn on "track_io_timing".  Then you can look in "blk_read_time" column to find queries that spend a lot of time reading data from disk.  I recommend to look at the time column, not the count column "shared_blks_read", because a lot of the "reads" will actually be fulfilled by the OS's filesystem cache, so the count doesn't give you an accurate picture of true disk IO.  Timing should give a more accurate picture of that.

pg_stat_statements doesn't give you info on when queries are being run, other than 'since the last pg_stat_statements_reset() call'.  If you want to able to line the queries up with your chart, then you would want to enable "auto_explain", again after turning "track_io_timing" on, and adopt the settings of:

auto_explain.log_min_duration = '50ms' ## need to tweak for your needs
auto_explain.log_analyze=on
auto_explain.log_timing=off  ## this may have high overhead, so turn off
auto_explain.log_buffers=on

And then go mine the data out of the server log file.  This does come with an overhead, so you might to turn it off again once you find the problem.  The amount of overhead depends on your hardware and kernel, on modern systems I find it low enough to be worth paying to leave it on all the time, just cranking up "log_min_duration" to the point few queries get logged when I am not actively tracking a problem down.


Select records and related entity count Given the following select that will count related entities for every record
SELECT MAIN.*, SUB.app_count
FROM my_table_view MAIN
     LEFT JOIN (SELECT MAIN2.p_key,
                       count(DISTINCT T3.app_id) AS app_count
                FROM my_table MAIN2
                         JOIN T1 ...
                         LEFT JOIN T2 ...
                         LEFT JOIN T3 ...
                GROUP BY MAIN2.p_key) SUB ON MAIN.p_key = SUB.p_key
WHERE ...

Trying to simplify it, came up with the following :
SELECT MAIN.p_key, count(DISTINCT T3.app_id) AS app_count -- select MAIN.* errors out - missing GROUP BY
FROM my_table_view MAIN
     LEFT JOIN T1 ON ...
     LEFT JOIN T2 ON ...
     LEFT JOIN T3 ON ...
WHERE ...
GROUP BY MAIN.p_key

How to select all data from the MAIN table without adding a bunch of GROUP BYs ?
Would also appreciate performance hints.
Edit
I think this is related
Postgres GROUP BY id works on table directly, but not on identical view

If p_key is the primary key of my_table, it should not be necessary to add the other columns, unless you want the statement to comply with the SQL standard.


Why would there be varying response times for the same query? Is there a reason why the same query executed a number of times have huge variance in response times? from 50% - 200% what the projected response time is?
They range from 6 seconds to 20 seconds even though it is the only active query in the database.
Context:

Database on Postgres 9.6 on AWS RDS (with provisioned IOPS)
Contains one table comprising five numeric columns, indexed on id, holding 200 million rows

The query:
SELECT col1, col2
  FROM calculations
 WHERE id > 0
   AND id < 100000;

The query's explain plan:
Bitmap Heap Scan on calculation  (cost=2419.37..310549.65 rows=99005 width=43)
  Recheck Cond: ((id > 0) AND (id <= 100000))
  ->  Bitmap Index Scan on calculation_pkey  (cost=0.00..2394.62 rows=99005 width=0)
        Index Cond: ((id > 0) AND (id <= 100000))

Is there any reasons why a simple query like this isn't more predictable in response time?
After doing a database dump to my own EC2 instance PostgreSQL server, and executing the above query continuously, I can see that the variance is quite small -- within +/- 10% of each other. So now thinking that we are not correctly configuring (or don't don't understand fully how the Provisioned IOPS works on RDS). Will look into this more.

After investigation of the historical load, we have found out that the provisioned IOPS we originally configured had been exhausted during the last set of load tests performed on the environment.

According to Amazon's documentation @http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html, after this point, Amazon does not guarantee consistency in execution times and the SLAs are no longer applicable.

We have confirmed that replicating the database onto a new instance of AWS RDS with same configuration yields consistent response times when executing the query multiple times.


Keep original column names in SSRS report designer 3.0? I have a query that returns over a hundred columns. The column names contain important underscores, >, < and Uppercase/lowercase characters. However, when creating the reports, SSRS designer (3.0) automatically added spaces and convert these special characters to spaces. It's really annoying. Is it possible to disable this SSRS feature?

How much work would it be to fix the headers using the following two methods?

Use a script to update the column header back to the original column name.
Transform the XML reporting rdl file.

Find and Replace one of the desired characters, such as an Underscore, in the dataset query SQL with a non-sense string, such
as "3145234jhasdf" ..... (minus the quotes).
In Report Builder, create a DataSet with the resulting data.
Create a Tablix with that DataSet.  The headers in the Tablix will now look odd.  As an example the header, "Column_One", will now look something like this: "Column3145234jhasdfOne".
Save the file on your desktop or other local directory.  It will be an RDL file.  Close Report Builder.
Open the resulting RDL file you saved in a text editor, such as Notepad++.  You will see XML code.
Find and Replace your non-sense string: "3145234jhasdf", with the character you want it to display your headers: "_".
Save the file. Close the text editor.
Open the file in Report Builder.  Your headers should look like you want it.

You can replace more than one type of character by choosing a different non-sense string for each.  For ease, keep the string alphanumeric with no spaces.


PHP MySQL peer to peer How do I achieve with MySQL to have peer to peer data shared between network? Let's say I have 5 different nodes of MySQL servers and would like the data to be shared peer to peer rather than master-slave or multi-master having all transactions data integrity in check.

If you can show steps or even guide to do this it would be really great.

Galera clustering does essentially what you describe.  Each "node" acts like a MySQL server for reading and writing.  Each node always contains the same data.

Under the covers is M-S / M-M, plus extra glue so that, on the surface, it does not feel like those replication technologies.

Galera is included in PXC and MariaDB, or you can install it on top of MySQL.  Competing products include Clustrix and InnoDB Cluster.

Caution:  3 nodes is recommended; 5 is getting rather large.  To maintain the peer-to-peer, each node needs to talk to each other nodes; the number of connections grows quadratically.

https://mariadb.com/kb/en/the-mariadb-library/galera-cluster/


I don't know mysql syntax, but it seems like the following would give you the same results.

SELECT b.id, b.language, b.word, b.state, b.domein, b.characteristic, a.lang1
FROM xwrh9_betty_dictionary_ids a
LEFT JOIN xwrh9_betty_dictionary_words b ON b.id=a.lang1 OR b.id=a.lang2
WHERE b.id<>19
GROUP BY b.id;


SQL function which returns true/false depending on success of insert query I want to write a SQL function which returns true if a certain INSERT inserted a row and false if no row was inserted. In this use case, we know that at most one row can be inserted.

I came up with the following solution:

create function test() returns boolean as
$$
    with
    dummy as
    (
        insert into    ...
        select         ...
        from           ...
        where          ... -- this will limit to at most one row
        returning      true
    )
    select exists(select true from dummy);
$$
language sql volatile;

This seems to work fine, but it is rather clumsy and verbose, in my opinion. Is there something better possible?

I first wanted to write the following:

create function test() returns boolean as
$$
    insert into    ...
    select         ...
    from           ...
    where          ...
    returning      true;
$$
language sql volatile;

The problem now is that not false but null is returned when no row was inserted.

One way with little noise in the code:

create function test()
  returns boolean as
$$
   WITH ins AS (
      insert into    ...
      select         ...
      from           ...
      where          ...
      returning      true
      )
   SELECT true FROM ins
   UNION ALL SELECT false;
$$
language sql volatile;

The function, being declared as returns boolean (and not returns setof boolean) returns only the first row. If no INSERT happens, the first (and only) row delivers false.

Or, a shorter version of your first variant:

...
   WITH ins AS (
      insert into    ...
      )
   SELECT EXISTS(TABLE ins)
...

See:

Is there a shortcut for SELECT * FROM?

Related (with alternatives):

How to return a value from a function if no value is found

Quoting the manual to clarify the behavior of SQL functions:

  SQL functions execute an arbitrary list of SQL statements, returning
  the result of the last query in the list. In the simple (non-set)
  case, the first row of the last query's result will be returned. (Bear
  in mind that “the first row” of a multirow result is not well-defined
  unless you use ORDER BY.) If the last query happens to return no rows
  at all, the null value will be returned.

Bold emphasis mine.

Note: the above is a single query. And we don't need ORDER BY, since a plain UNION ALL without more operations in the outer query level simply appends the resulting row to the set in last position.


Disable indexes on Amazon RDS PostgreSQL instance If I correctly understand according to this advice: https://stackoverflow.com/questions/6146024/is-it-possible-to-temporarily-disable-an-index-in-postgres

it is possible to temporary disable indexes in all catalogue in order to increase performance of huge number of insert operations:

update pg_index
set indisready = false
<where ... >

But there is a problem: I run my instance on Amazon RDS and I have user granted access only to my particular schema. It is not granted to perform updates on pg_index table. Other user is rdsadmin which is internal Amazon's system user (according to this explanation: https://serverfault.com/questions/587695/what-is-the-rdsadmin-user-and-should-i-care ).

Are any other ways increase speed of huge number of inserts operation or disable indexes in this case?

As @pietrop already said: it's a good pratice drop de index and recreate it after the data load.

If you are loading a lots of data, use COPY[1] instead of INSERTs. Consider using PGLoader[2] on huge data load environments.

Hope it helps.

References:

https://www.postgresql.org/docs/current/static/sql-copy.html
http://pgloader.io


MySQL Multi Master Replication I have 4 servers running MySQL 5.7 in total , servers 1,2,3 each has single database on them For example:

Server 1 has the DB x_1
Server 2 has the DB x_2
Server 3 has the DB x_3

Server 4 has 3 databases in it:

x_1
x_2
x_3

What i want to achieve is master to master replication between the 3 servers and server 4 each to his own database:

Changes in x_1 in server 4 or server 1 will replicate both ways
Changes in x_2 in server 4 or server 2 will replicate both ways
.
.
.

Appreciate any guidance to the correct way to achieve this (Note: I already know about multi source replication single slave and multiple masters but i am not sure if it can be used here)

You have to build two replication setups:

from masters 1, 2 and 3 to the slave 4 in the multimaster config
from master 4 to slaves 1, 2 and 3 in the plain replication config

Caveats:

each instance of mysql should have an unique server_id to tell events originators
multimaster masters 1, 2 and 3 should use binlog_do_db directive to restrict the set of databases being replicated (until you want to populate ALL the DB's like mysql.* across the whole setup):
binlog_do_db = x_?  # on each server 1, 2 and 3
mysql 4 as a master needs a restriction for replicated databases too:
binlog_do_db = x_1,x_2,x_3
plain replication from the single master to three slaves means the shared master  binlog will contain the events from all the databases mentioned in the binlog_do_db directive. So be ready that relay files will be about three times bigger than required for a single DB.
also you have to restrict the replication on each slave to the proper DB:
replicate_do_db = x_? # on 1, 2 and 3
you have to turn on the binloging on all the servers
you have to CHANGE MASTER 's1'..., CHANGE MASTER 's2'... and CHANGE MASTER 's3'... on the server 4
you have to CHANGE MASTER 's4'... on each server 1, 2 and 3

Replication events are represented in the common object naming manner: originator.database.table

When event is replicated from, say, S1 to S4 it will be applied to the replica of x_1 on S4 as well as written to the binlog. Then that event will be replicated back to the S1. Each replication event is marked by its originator. S4 will see that certain event's originator is S1 and applies it to the corresponding database. When that event has been replicated back to the S1 it will see that he was an originator of the event and discard it. If certain event will be originated from S4 it will be applied by S1.

The whole setup isn't easy to maintain as far as each crash recovery will require the total reinitialization from the scratch. If you really want something like that you'd better look at the mariadb/galera.


Indexing a date field on MongoDB - best practice Hi We would like to index a date field on a potentially large collection.
Is indexing a date-time field a good practice? Or should we stick to a date field (e.g. 07/12/2017) indexing (both of the options are valid as candidates for our date field, of course the date time is more fit if there is no real impact on performance)

In MongoDB dates are stored as BSON Dates and this type is a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). This means that dates and date-time values are stored in the same format, so there is no difference in indexing on either of them.


A simple mnemonic:

The key (1NF - no data items repeated in more than one place within the row)
The whole key (2NF - nothing relates to just a part of a composite key)
Nothing but the key (3NF - nothing is related to a non-key attribute)

. . . So help me Codd.


What should I consider before I upgrade my SQL Server memory? I want to upgrade the memory in my SQL Server.  What should I check first?

Version: SQL Server 2012 64-bit Standard edition

There is not much to check if you have found out that SQL Server requires more memory.Is your OS on physical machine or Virtual if it is virtual adding memory would be easy.

You must know that SQL Server 2012 standrd edition supports maximum of 64G memory. After adding new memory you must change max server memory setting using sp_configure. Set max server memory to appropriate value. Leave enough memory for OS and SSIS, SSAS,SSRS if you are using these features.


Select strings with line breaks How can I select strings that have line breaks in them?

I can think of these possibilities when it comes to line breaks in strings:

create table test1 (
    id number(4,0),
    f1 varchar(25));

insert into test1(id,f1) values (1,'a');
insert into test1(id,f1) values (2,chr(10) || 'a');
insert into test1(id,f1) values (3,'a' || chr(10));
insert into test1(id,f1) values (4,'a' || chr(10) || 'a');
insert into test1(id,f1) values (5,'a' || chr(10) || chr(10) || 'a');
insert into test1(id,f1) values (6,chr(10));
commit;

chr(10) is the value for line breaks.

There might be more possibilities that I haven't thought of.

Don't try to ask the result of dump() if there is a newline character in the string.  Ask the string directly.

Example where clause

where f1 like '%' || chr(10) || '%'

or

where instr(f1,chr(10)) > 0

or (Per John A)

where regexp_like( f1, chr(10), 'n' )


Robust Mysql failover using free tools I am looking for a free solution to set up Mysql in an active/passive configuration, in order to provide high-availability. In particular, a Bugzilla installation, and MediaWiki needs to stay alive (which produce a dependency for MyISAM support).

I have two sites, where one site is the primary site, and one site is the backup site, with minimal latency between the two (<1ms). My plan is to set up replication between two Mysql installations, and use Mysql proxy or HAproxy at the webserver node as the failover mechanism. The goal is to allow for the primary site to go down, without affecting the backup site (users are located at both sites).

For replication, I was looking at semisyncronous replication - mainly since it appears to provide limited data loss on outage, and have a solution for network congestion.

My concerns are as follows:

Would the above constitute a robust solution?
What would happen if primary site is in a flapping state?
What would happen on network outage between the sites (split brain)?
Coping with data corruption (faulty hw)?

How can I set up my servers to provide full consistent database service for the web applications?

*Edit: Due to a dependency on MyISAM, I've found that the following solutions won't work for me:

Galera/Percona
DRDB

There are two tools that jump to mind, with the caveat being I haven't tried either, just have read about them. Percona's* XtraDB Cluster (based on Galera) and Percona Replication Manager (PRM).

PRM is based on a single-master with multiple slave nodes. It uses pacemaker to handle the failover. I can't answer how it handles your last two bullet points, but as to a flapping state, read about GitHub's experience. That experience and subsequent posting caused quite a commotion, questioning the validity of automatic failover. Here is one such post that links to some of the others.

The basic gist is, be careful with automatic failover. You might save yourself a lot of time restoring service by monitoring the state of the master and getting notification to do a manual failover.

I don't know much about XtraDB Cluster, but from the documentation, below is a list of features. However, you require MyISAM support, which XtraDB Cluster/Galera does not handle.

  Synchronous replication. Transaction either commited on all nodes or none.
  Multi-master replication. You can write to any node.
  Parallel applying events on slave. Real “parallel replication”.
  Automatic node provisioning.
  Data consistency. No more unsynchronized slaves.

disclaimer: I linked a lot of Percona stuff, but I am not affiliated with them in any capacity.


Multiple Filters for Databases on SQL Server Server Level Audit I am attempting to create a Server Level Audit to track Database Level events in SQL Server 2014.  I know I can create them at the database level but I want the audits all databases at the server level if this is possible.

I need to filter the Server Level Audit for two databases and only for users who are not the two application users but my filter is not working correctly.  Either I am building the filter wrong or it is not possible to filter multiple database and multiple users.  My Google-Fu is failing me in finding examples with multiple filters:

Any assistance or ideas are appreciated.

CREATE SERVER AUDIT [PCIAudit]
TO FILE
(   FILEPATH = N'D:\SQLAuditLogs\'
    ,MAXSIZE = 2048 MB
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 0
    ,ON_FAILURE = CONTINUE
)
WHERE ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2' AND [server_principal_id] <> 277 OR [server_principal_id] <> 278 AND NOT [statement] like 'ALTER INDEX%REBUILD%' AND NOT [statement] like 'ALTER INDEX%REORGANIZE%')
GO

ALTER SERVER AUDIT [PCIAudit]
WITH (STATE = ON);
GO

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [DDLAudit]
FOR SERVER AUDIT [PCIAudit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
GO

Try this, which slightly modifies your WHERE clause to correctly follow the SQL Server operator precedence rules:

CREATE SERVER AUDIT [PCIAudit]
TO FILE
(   FILEPATH = N'D:\SQLAuditLogs\'
    , MAXSIZE = 2048 MB
    , RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 0
    , ON_FAILURE = CONTINUE
)
WHERE ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2')
    AND [server_principal_id] <> 277
    AND [server_principal_id] <> 278
    AND NOT [statement] LIKE 'ALTER INDEX%REBUILD%'
    AND NOT [statement] LIKE 'ALTER INDEX%REORGANIZE%';
GO

ALTER SERVER AUDIT [PCIAudit]
WITH (STATE = ON);
GO

Notice the round brackets ( and ) in this:  ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2') - they tell the where clause to allow either DataBase1 or Database2.  As you have it, the where clause is set to allow either DataBase1 or Database2+server_principal_id<>277.


Is it safe to use Strings instead of SqlStrings for CLR function input parameters? I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server), Solomon Rutzky mentions the following reasons to prefer the SQL data types for strings:

  A primary difference between native common language runtime (CLR) data
  types and SQL Server data types is that the former do not allow for
  NULL values, while the latter provide full NULL semantics.

  ...

  Streaming values in can be achieved via SqlChars for N[VAR]CHAR,
  SqlBytes for [VAR]BINARY, and SqlXml.CreateReader() for XML...

  ...

  When using SqlString (not string or even SqlChars) you can access the
  CompareInfo, CultureInfo, LCID, and SqlCompareOptions properties...

I know that my input will never be NULL, I don't need to stream the values in, and I'll never check the collation properties. Could my case be an exception where it's better to use String instead of SqlString? If I do go with that approach, is there anything in particular that I should watch out for?

If it matters, I'm using SQL Server's default collation. Here's part of my source code, with s1 being the input parameter:

fixed (char* chptr = s1)
{
    char* cp = (char*)current;

    for (int i = 0; i < s1.Length; i++)
    {
        cp[i] = chptr[i];
    }
}

Excellent question. As far as I am aware of, under those conditions (i.e. guaranteed no NULLs and no need for the extra functionality) there shouldn't be any specific concerns. This could be a situation similar to CURSORs where, if a generic rule is needed, it would be: "don't use cursors". But, the actual rule is: "only use cursors when/where appropriate". The problem is educating people on the technical details of cursors such that they can make that decision, which is those of us who know enough about such things ignore the generic rule and proceed to use them appropriately.
So, I advise folks to "always" use the Sql* types because it cuts down on confusion and errors. But, that is not to say that using string in your situation wouldn't be better. I say go for it, and if you ever run into a problem with string, it's easy enough to go back and change it to be SqlString.
With regards to collation and your statement of:

If it matters, I'm using SQL Server's default collation.

While it generally does not matter, it's also a bit unclear what you mean here given that there is no true default collation. Chances are that you are referring to the unfortunate default collation when installing SQL Server on an OS with a language setting of "US English" (i.e. LCID = 1033), which is SQL_Latin1_General_CP1_CI_AS. But then there are still three levels of collations that can all be different (Instance / Server, Database, and Column), and you might be meaning only one or even two of these levels.
The reason I mention all of this is that there are some non-obvious things going on here:

to some degree, none of those 3 levels that collations affect is relevant since the default culture of SQLCLR threads is the Language setting at the OS level (the LCID of the selected language). This impacts operations using String.Equals when using either of the two StringComparison.CurrentCulture* values, and operations using String.Compare when not specifying a culture.

to some degree, none of those 3 levels that collations affect is relevant since the = operator does an ordinal comparison (i.e. should be the same as using a _BIN2 collation). This is also how String.CompareOrdinal works, as well as String.Equals when not passing in StringComparison.CurrentCulture* or StringComparison.InvariantCulture* values.

one instance where the SQL Server collation matters is when concatenating a SqlString input parameter with a string via +. In this case the + operator creates a new SqlString to contain the value of the string so that it can then concatenate the two SqlStrings. The problem is that the new SqlString is created with the current threads LCID (which is the Operating System's LCID), and then the + operator compares the two SqlStringss prior to concatenation (i.e. verifies that they are the "same type"). But, due to the SqlString input parameter having the LCID of the database (not instance or column) and the implicitly created SqlString having an LCID of the OS, the operation gets an exception stating that the "collations" do not match. Nice, eh?
This, however, should be a non-issue since nobody should ever use the SqlString value directly when wanting the string. Everyone should instead always use the Value property to get the string.

That being said, I am curious as to what test you did to determine that string was faster. I tested a simple UDF that accepts a single NVARCHAR(4000) input parameter, concatenates a short string and then returns the new value. One version of that UDF accepts and returns string, and the other version accepts and returns SqlString. Over 1 million iterations, the string version was about 200-300 milliseconds faster than the SqlString version, about 50% of the time, when comparing their fastest times (over all 1 million iterations, not per each). The other 50% of the time the performance gain was around 100 milliseconds, but could also be none.
Also, regarding your test code: is s1 always the direct input parameter, whether  it's string or SqlString? If yes, then you should also test creating a string locally and setting it s1.Value. Meaning:
string s2 = s1.Value; // when s1 is SqlString instead of string

fixed (char* chptr = s2)
{
    char* cp = (char*)current;

    for (int i = 0; i < s2.Length; i++)
    {
        cp[i] = chptr[i];
    }
}

Also, some other options to possibly test:

SqlString.GetUnicodeBytes method (returns byte[])
SqlChars.Value property (returns char[])


SSAS and SQL Server optimal settings I have 4 cubes running on the SSAS server. The CPU seems to peg and we had memory issues with the 4th cube during loads.

We have 4 CPUs with MAXDOP set at 2, cost threshold for parallelism = 70. We added server memory 68GB to 96GB, with max server memory from 33GB to 44GB.

I had max SQL Server memory higher, but looks like the OS and other processes were causing memory issues, so I lowered it to 44GB. Is there a great article to help with SSAS and optimal settings? I would like to adjust them so we don't throw un-needed CPUs and memory at it if possible.

There are several whitepapers on optimizing SSAS that you can find on James Serra's blog here - http://www.jamesserra.com/archive/2011/12/top-ssas-white-papers/

I would particularly look at:

The SSAS performance guide in the settings and configuration sections
https://technet.microsoft.com/en-us/library/dd542635(v=sql.100).aspx

and for memory management
http://sqlcat.com/technicalnotes/archive/2008/07/16/running-microsoft-sql-server-2008-analysis-services-on-windows-server-2008-vs-windows-server-2003-and-memory-preallocation-lessons-learned.aspx

I'm not sure what your cost limitations are, but if you're hosting SQL Server Service and the Analysis Service on the same box, I would advise against it if you have fairly large cubes.


Transactional Data Replication, SQL Server version compatibility I've been following the Microsoft Tutorial: Configure replication between two fully connected servers (transactional). Everything has gone "smooth", however the 'New Subscription Wizard' is stopping me from creating a subscriber due to compatibility issues between the publisher and subscriber.

Error Message

  The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication. (New Subscription Wizard)

Versions

The publisher and distributor are on:

  Microsoft SQL Server 2014 - 12.0.4100.1 (X64)   Apr 20 2015 17:29:27   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

The subscriber is on:

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Transactional Replication

The type of data replication I'm attempting to configure is Transactional and according to the Microsoft documentation for Replication Backward Compatibility, this should be working fine, but for some reason, the wizard is not playing ball.

Any help would be appreciated.

I found a way.

Turns out, if your subscriber is a higher SQL version than the publisher this error will always get produced. A way around this is to add the subscription on the Subscriber instance instead of on the Publisher.


Query to generate a new column containing the value of the "previous" column I have a MySQL table that has data similar to this:

id   #        current
--   -----    -------
1    item1    50
2    item2    60
3    item1    70
4    item2    40
5    item2    80
6    item2    33
7    item1    55

I need to analyze the data per each item and compare it to the previous value of the same item.

So how can I write query in order to retrieve the data like shown below?

id   #        current       prev
--   -----    -------       ----
1    item1    50            -
2    item2    60            -
3    item1    70            50
4    item2    40            60
5    item2    80            40
6    item2    33            80
7    item1    55            70

If no, then what is the most efficient way to achieve this?

The only thing that comes to my mind is query all and for loop on the results keeping array for previous value of each item. I am a little worried about performance as this is going to be a big table with a lot of rows and a lot of items. Any other option?

For version 5.x

SELECT id,
       CASE WHEN @item = item
            THEN @value
            ELSE @value:=NULL
       END prev,
       @item:=item,
       @value:=current
FROM tablename, (SELECT @item:='', @value:=0) variables
ORDER BY item, id

The query firstly processes all item1 in the order of id growth, and then processes all item2 (due to ORDER BY expression). CASE in prev's expression checks the moment when item1 finished and item2 started, and resets the variable to NULL (because first record for item have no previous record).

For version 8.x

SELECT id,
       item,
       current,
       LAG(current) OVER (PARTITION BY item
                          ORDER BY id) prev
FROM tablename

PS. Column name `#` replaced with `item`.

PPS. If you need in final sorting by `id` then wrap the query with proper ordering outer query (for 5+) or add ordering clause (for 8+).


Join table on one of the row I have a table, say, Person with fields

id  name
1    Jone
2    Jane

Got another table address

id   person_id   address_type
1    1           Permanent
2    1           Temporary

I want to select table Person to Address such that if a person has Temporary address then join it else join with Parmanent address.

I can do:

    select *
    from person p
    left join address at on at.person_id=p.id and at.type='Temporary'
    left join address al on al.person_id=p.id and at.type='Permanent'

But joining address table twice seems like overkill here. Can I just join address only once such that I get:

-> If Temporary is there, join with Temporary
-> If temporary is not there, join with permanent
-> If pernamenet is not there join address with all null fields.

Help?

This could be done using a left join to a derived table that picks the correct address:

select p.*, a.id as address_id, a.address_type
from person p
  left join (
     select ad.*,
            row_number() over (partition by ad.person_id
                               order by case when ad.address_type = 'Temporary' then 1 else 2 end) as rn
     from address ad
  ) a on a.person_id = p.id and a.rn = 1;

The case expression for the order by inside the window function could also be written as decode(ad.address_type, 'Permanent', 1, 2), but I prefer standard SQL over Oracle specific one if both are equivalent.

Another option - that might or might not be faster - is to use a lateral join with a fetch first clause.

select p.*, a.id as address_id, a.address_type
from person p
  left join lateral (
     select *
     from address ad
     where ad.person_id = p.id
     order by case when ad.address_type = 'Temporary' then 1 else 2 end
     fetch first 1 rows only
  ) a on 1 = 1;

The lateral left join returns one row for each person_id from the outer query (because of the fetch first 1 rows only)


ALTER TABLE on a large table with an indexed column I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about 90-120 minutes to complete, so I can really only do that on a Saturday or Sunday night to avoid affecting the users of the database. If possible I would like to do this modification prior to then.

The column is also indexed, which I presume will make the ALTER TABLE slower, because it has to rebuild the index after modifying the column length.

The web app is set up in a MySQL replication environment (26 slaves and one master). I recall once reading somewhere that one method is to first perform the ALTER TABLE on each slave (minimising impact on users), then do this on the Master, but won't that then try to replicate the ALTER TABLE command to the slaves?

So my question is: what is the best way for me to modify this table with minimum disruption to my users?

Edit: the table is InnoDB.

If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see. Suppose the table you want to change is called WorkingTable. You could perform the changes in stages like this:

#
#  Script 1
#  Alter table structure of a single column of a large table
#
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

You can perform this on all slaves. What about the master ??? How do you prevent this from replicating to the slaves. Simple: Don't send the SQL into the master's binary logs. Simply shut off binary logging in the session before doing the ALTER TABLE stuff:

#
#  Script 2
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#
SET SQL_LOG_BIN = 0;
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

But wait !!! What about any new data that comes in while processing these commands ??? Renaming the table in the beginning of the operation should do the trick. Let alter this code a little to prevent entering new data in that respect:

#
#  Script 3
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#  and preventing new data from entering into the old table
#
SET SQL_LOG_BIN = 0;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
CREATE TABLE WorkingTableNew LIKE WorkingTableOld;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

Script 1 can be executed on any slave that do not have binary logs
enabled
Script 2 can be executed on any slave that does have binary
logs enabled
Script 3 can be executed on a master or anywhere else

Give it a Try !!!


How to design sales model for escrow type payment process I need advice for best sales model for payment process. Basically my system is kind of market place. There is two kind of transaction.

Customer will pay full money to administrator.

Administrator will take some fee and release payment to seller.

So for the single order, we need to do two transaction. But Paypal offers Delayed Chained payment option to do in single transaction as below.

But I am not sure other major payment gateways are proving this option or not. So I want to design the database compatibly for both.

Note: I want to notify customer after paid at this time I don't know
who will be seller. After certain point of time administrator will
choose the seller and admin will release the pay to seller. Here I
want send notification for both seller and customer.

From above, I need to use unique order_id for both transaction. So should i split the order into two and store as two records or can I manage it in single order row. Advice me reliable and better design pattern for this two kind of situation.
What I tried !

Help me out!

I find with financial transactions you want to have a detailed audit trail of every movement of funds.  This means a write-once (no updates) table containing fund movements using a double entry accounting model.

In a simple version of this, you would have one table containing the list of payees and payers (your senders, administrators and receivers).  Then you would have another table (or better two) that contain the financial transactions.  The tables are roughly as follows:

PARTY: Name, address, bank account, other details about payers/payees.
TRANSACTION: Date, transaction ID, order reference, other general info.
TRANSACTION-DETAIL: FK to Transaction, FK to party, Amount.

When someone makes a simple payment you record one TRANSACTION and two TRANSACTION-DETAIL records.  The first record shows who paid and the second shows who was paid.  The sum of the amounts for the two details must be zero, that means you need to pick a convention for positive and negative Amounts such that one means giving money and the other means getting money.

If the transaction is more complicated, you just add more TRANSACTION-DETAIL records, for example instead of one person getting paid, you get two people being paid (administrator and seller for example).

While the sum of transaction detail amounts for any transaction is zero, the sum of amounts for any given PARTY may be non-zero, meaning you have some of their money in your system.  To clear these amounts, you need a PARTY representing any payment settlement organization or bank that you deal with.  These are the ultimate sources and sinks of funds and their balances don't need to be zero in the long run.

All of this may seem complicated, especially compared to what you were thinking of, but the advantage of it is that it allows you to keep a detailed record of exactly what happened and when it happened.  If you need to make an update, i.e. cancel and order or issue a credit note, just add more transactions and transaction details.  You can always go back and see exactly what went on.  This convention is how people have been handling and accounting for financial transactions for hundreds of years, so if nothing else, people will understand what you're doing.

Here is an example with some data.  In this example, Buyer X pays 100.00 for an order.  The Agency collects 10.00 and pays Seller 1 40.00 and Seller 2 50.00.  They buyer pays with PayPal and the Agency and both Sellers deposit their money in one of two banks.  Note that this example is quite simple insofar as the agency and the sellers clear out their accounts after just one order.  In a real-world scenario, payments for many orders might be batched up before settlement is made.

Note that for OP's scenario where determination of the seller may be delayed, the agency would need to have some kind of escrow account.  This would be an additional party, such as "Outstanding Order Payments".  In such a case the agency could take their fee right away and the balance would be placed in the escrow account.  When the sellers are determined later on, they would be paid out of the escrow account, rather than out of the buyer's account, as is shown in the example, above.

Also note that while each transaction is shown with two details above, it is also legitimate to have three or more details for one transaction if you happen to want to record it that way.  For example, all of the details in 8001, 8002 and 8003 could have been represented as a single transaction if you prefer.


How much storage space do indexes and keys take for an SQL Table? I have a table which is predominantly filled with NULLS and takes up 10 GB which was shocking since probably 90 to 95% of the table is NULLS. I copied this table over to a new database on the same SQL 2012 instance using Select * Into.... but the copied table takes up about 70 MB.

Checking them against each other, they seem to have the same data, as expected so it's not like data is missing.

Is the disparity in size a result of not generating a script to copy indexes, triggers, keys, etc?

Is there a way to optimize the table to reduce space?

Thank you,

One aspect which I don't think was addressed so far is if your source table is a heap table. SQL server is very bad at re-using space for heaps, and this is one (not the only) reason for not using heaps in the first place. You make the table a non-heap table by having a clustered index on the table.

I.e., this is most likely not at all about nulls, but rather about heaps (or possibly could also be non-deallocated LOB storage).


Only return recordset(s) from Stored Procedure if it has rows I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty.

I'd like to reduce overhead and only return those which have 1 or more rows.

My question is - How can I only return those recordsets which have rows?

The application simply expects 0 or more recordsets, and loops through each, and prints them out.

I know I can skip them in the application code, but I am trying to prevent them from being returned at all, if empty.

The procedure is as simple as this:

CREATE PROCEDURE bfsp_PROC_NM
AS

    BEGIN

        SELECT * FROM TABLE_1

        SELECT * FROM TABLE_2

        SELECT * FROM TABLE_3

        RETURN

    END
GO

In the actual procedure, some of the queries are expensive, so I don't want to have to test the query, then if it returns a row or more, execute it again... as it would be too expensive.

I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty.
I'd like to reduce overhead and only return those which have 1 or more rows.

That's a terrible idea.  How is the application going to know what resultset it is processing?  Stored procedures should have a fixed set of resultset shapes.
That being said, to do this load the results into temp tables, and then SELECT from those if they are non-empty.
eg
CREATE PROCEDURE bfsp_PROC_NM
AS

    BEGIN
        SELECT *
        into #result1
        FROM TABLE_1
        if @@rowcount > 0
          select * from #result1

        SELECT *
        into #result2
        FROM TABLE_2
        if @@rowcount > 0
          select * from #result2

        SELECT *
        into #result3
        FROM TABLE_3
        if @@rowcount > 0
          select * from #result3

    END
GO


Accidentally change database name (SQL Server 2008) It didn't happen to me YET. But I was thinking about this.

I was messing around with my training environment, and by mistake I clicked in the database name, and then I touched the letter A.

Let's assume that I hit enter. Now the database is called A, and I don't remember the original name. CTRL + Z doesn't work.

What to do in a case like this, in a production environment?

I know it could not happen, because the database isn't set as SINGLE USER. But if it happens. What to do? For argument's sake, let's say it's a database that nobody is using right now.

Right click on the database, go to files. You can see the original file names. This will help you to find the correct database name easily. The file names won't be changed on a rename.

You can also try to take a look at your fn_dblog. It's undocumented but you can see (and filter) for the latest actions.

SELECT *
FROM fn_dblog(NULL,NULL)


Grant access only to System databases Is it possible give an SA like permission minus any access to user databases?  I want people to be able to collect information about Sql Server from the system databases but I want to make sure the User databases cannot be touched.  What is the best way to approach that?

There are a couple of approaches you can take.  The better one is to grant VIEW SERVER STATE.  This gives you access to the DMOs that give you information about the server.

The other version (which I don't recommend) is to grant CONTROL SERVER.  This permission is very similar to SYSADMIN but you can do DENYs.  So you could DENY CONNECT on each of the user databases.  The down side here is that you are giving someone (for example) the ability to stop & start the SQL Services.

Unless you have a VERY good reason to go beyond VIEW SERVER STATE just go with that one.

You can also look at the list of server level roles and see what matches your needs.  Just remember that someone with securityadmin for example could create a login with sufficient permissions to affect the databases.


Literal does not match format string error in oracle I was given a SELECT statement that is running in a Crystal Report to investigate a data problem but when I try to run it against the database I get the error:

  ORA-01861: literal does not match format string
  01861. 00000 -  "literal does not match format string"
  *Cause:    Literals in the input must be the same length as literals in
             the format string (with the exception of leading whitespace).  If the
             "FX" modifier has been toggled on, the literal must match exactly,
             with no extra whitespace.
  *Action:   Correct the format string to match the literal.

I think it has to do with the Date comparisons but I am not sure what is wrong. I know nothing about oracle and since the error message does not specify the line the error is on I am only guessing at what might be the problem. Can anyone point me to what is causing this problem?

   SELECT ACTDET.GROSS_VOLUME,
          ACTDET.NET_VOLUME,
          ACTIVITY.SHIPPER_ID,
          ACTIVITY.ACTIVITY_START_DATE,
          SHIPPER.NAME_1,
          TANK.TANK_PRODUCT_CODE,
          PRODUCT.DESCRIPTION_2,
          SITE.NAME_1,
          SITE.NAME_2
   FROM   G3USER.ACTIVITY ACTIVITY,
          G3USER.ACTDET ACTDET,
          G3USER.SHIPPER SHIPPER,
          G3USER.TANK TANK,
          G3USER.SITE SITE,
          G3USER.PRODUCT PRODUCT
   WHERE ((ACTIVITY.TERMINAL_ID=ACTDET.TERMINAL_ID) AND (ACTIVITY.BOL_NUMBER=ACTDET.BOL_NUMBER))
   AND ((ACTIVITY.TERMINAL_ID=SHIPPER.TERMINAL_ID) AND (ACTIVITY.SHIPPER_ID=SHIPPER.SHIPPER_ID))
   AND ((ACTDET.TERMINAL_ID=TANK.TERMINAL_ID) AND (ACTDET.TANK_ID=TANK.TANK_ID))
   AND (ACTDET.TERMINAL_ID=SITE.TERMINAL_ID)
   AND ((TANK.TERMINAL_ID=PRODUCT.TERMINAL_ID AND (TANK.TANK_PRODUCT_CODE=PRODUCT.PRODUCT_CODE))
   AND (ACTIVITY.ACTIVITY_START_DATE >= '2014-02-05 00:00:00') AND (ACTIVITY.ACTIVITY_START_DATE < '2014-02-06 00:00:00'));

Oracle doesn't understand the literal date format strings, as they are not in the same format that your client is configured for. You need to use the TO_DATE function to tell Oracle the format of the date string you are presenting it with, like so:

AND (ACTIVITY.ACTIVITY_START_DATE >= TO_DATE('2014-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS'))
AND (ACTIVITY.ACTIVITY_START_DATE < TO_DATE('2014-02-06 00:00:00','YYYY-MM-DD HH24:MI:SS')));

You could also represent your dates using ANSI syntax (seeing as they have no time component):

AND (ACTIVITY.ACTIVITY_START_DATE >= DATE'2014-02-05')
AND (ACTIVITY.ACTIVITY_START_DATE < DATE'2014-02-06'));


Recursive CTE to get Grandparent, parent, children I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents.
Here's an example of how things can look in the tree:
900107
    --> 200237
            --> 100112
            --> 100113
    --> 200238
            --> 100114
            --> 100115
                --> 100120
                    --> 100001
                    --> 100002
--> 200239
--> 200501
--> 300112
--> 400987
--> 444444

In this example, 900107, 200237, 200238, 100120 are all parents where 900107 is going to be our grandparent.
Here's how it looks in the table:
ParentPart   Component
900107       200237
900107       200238
900107       200239
900107       200501
900107       300112
900107       400987
900107       444444
200237       100112
200237       100113
200238       100114
200238       100115
100120       100001
100120       100002

I have a recursive CTE that will get me all of the parents and children, but not the "grandkids" or "great grandkids" if they exist.
Here's my CTE:
CREATE TABLE #Hierarchy (
ParentPart NVARCHAR(6),
Component NVARCHAR(6))

INSERT INTO #Hierarchy (ParentPart, Component)
VALUES ('900107', '200237'),
       ('900107', '200238'),
       ('900107', '200239'),
       ('900107', '200501'),
       ('900107', '300112'),
       ('900107', '400987'),
       ('900107', '444444'),
       ('200237', '100112'),
       ('200237', '100113'),
       ('200238', '100114'),
       ('200238', '100115'),
       ('100115', '100120'),
       ('100120', '100001'),
       ('100120', '100002')

;WITH hier AS (
SELECT ParentPart AS MainPart, ParentPart, Component
  FROM #Hierarchy
UNION ALL
SELECT hier.MainPart, p.ParentPart, p.Component
  FROM hier
  JOIN #Hierarchy AS p
    ON p.Component = hier.ParentPart
)

SELECT ParentPart
     , Component
  FROM hier
ORDER BY ParentPart, Component

OPTION (MAXRECURSION 0)

This works for the most part....I can get this if I use one ParentPart. We have thousands of ParentParts and we'd need to know who the grandparent is. Ideally we'd want the output to look like this where the parent part is the grandparent and every child and grand child is in the component field
ParentPart    Component
900107        200237
900107        200238
900107        200239
900107        200501
900107        300112
900107        400987
900107        444444
900107        100120
900107        100112
900107        100113
900107        100114
900107        100115
900107        100001
900107        100002

What would be the best way to do this?

I believe this CTE can give you the desired result:
;WITH hier AS
(

    SELECT DISTINCT h1.ParentPart, h1.Component,
        h1.ParentPart AS GrandParent
    FROM #Hierarchy h1
        LEFT JOIN #Hierarchy h2 ON h1.ParentPart = h2.Component
    WHERE h2.Component IS NULL

    UNION ALL

    SELECT h1.ParentPart, h1.Component, h.GrandParent
    FROM #Hierarchy h1
        INNER JOIN hier h ON h.Component = h1.ParentPart

)
SELECT GrandParent, Component
FROM hier
ORDER BY GrandParent, Component;

Since you're only interested on the highest parent in each relationship chain, I used the trick of adding a third column on the anchor member containing what would be the great-great-grandparents. With that I can relate what value was the grandparent for each level of the chain no matter how many levels I descend.


MySQL table with identical name multiple times in same database I stumbled into a pretty nasty bug into our PROD environment
I was running some alter scripts and it appears it ended up duplicating the table for each column altered.
I've tested with MySQL Workbench, HeidSQL and DBBeaver they all display same duplicated results.
Bear in mind below examples it's just one table, but I have this issue with 4 different tables in the same Database

Any table that appears duplicated now it's also duplicated in the DB backup script.
If I click and drop one, all entries are gone.
When I run the create script, the table is multiplied for each column.
Per question below this the CREATE script, this script is multipled same in the dump script.
If I drop the table, all entries are gone. When run this script, they're all back
DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeNumber` varchar(45) DEFAULT NULL,
  `firstName` varchar(150) NOT NULL,
  `lastName` varchar(150) DEFAULT NULL,
  `email` varchar(150) NOT NULL,
  `phoneNumber` varchar(20) DEFAULT NULL,
  `address` varchar(250) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `merchantId` int(11) NOT NULL,
  `departmentId` int(11) NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date DEFAULT NULL,
  `contract` tinyint(4) DEFAULT '0',
  `countryId` int(11) NOT NULL,
  `createdDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedDate` datetime DEFAULT NULL,
  `isActive` tinyint(4) NOT NULL DEFAULT '0',
  `contractIssueDate` datetime DEFAULT NULL,
  `visaType` varchar(45) DEFAULT NULL,
  `visaStartDate` datetime DEFAULT NULL,
  `visaEndDate` datetime DEFAULT NULL,
  `knownAs` varchar(45) DEFAULT NULL,
  `dateOfBirth` datetime DEFAULT NULL,
  `ppsNumber` varchar(15) DEFAULT NULL,
  `passOrIdNumber` varchar(45) DEFAULT NULL,
  `nokName` varchar(100) DEFAULT NULL,
  `nokAddress` varchar(200) DEFAULT NULL,
  `nokRelationship` varchar(100) DEFAULT NULL,
  `nokPhoneNumber` varchar(20) DEFAULT NULL,
  `lastModifiedUserId` int(11) DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  `honorificTitleId` int(11) NOT NULL DEFAULT '0',
  `genderId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_country_idx` (`countryId`),
  KEY `fk_department_idx` (`departmentId`),
  KEY `fk_gender_idx` (`genderId`),
  KEY `fk_honorificTitle_idx` (`honorificTitleId`),
  KEY `fk_merchant_idx` (`merchantId`),
  CONSTRAINT `fk_country` FOREIGN KEY (`countryId`) REFERENCES `country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_dep` FOREIGN KEY (`departmentId`) REFERENCES `department` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gender` FOREIGN KEY (`genderId`) REFERENCES `gender` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_honorificTitle` FOREIGN KEY (`honorificTitleId`) REFERENCES `honorificTitle` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_merchant2` FOREIGN KEY (`merchantId`) REFERENCES `merchant` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

Any help would be greatly appreciated on how to prevent or resolve it.
Thank you

Resolved the problem through Azure Helpdesk
The problem was that the parameter dir_cache_fine_grained_enabled switched on during an Azure update.
This needed to be switched off and the DB server restarted. After restart things went back to normal
The parameter is not available to the public.


Extract data along a path (lat, long, altitude and time) I will be deploying PostGIS in the next few day. I've never done this before, nor have I ever worked with and geo-spacial data in the past.

I will have two datasets: weather data (A) and coordinate data (B).

(A) will have records containing a lat, long, altitude, time and a measurement value.
(B) will have records containing a lat, long, altitude and time.

I would like to pull out a bunch of records from (B), which together would represent a trajectory through space and time.

I would then like to, for each record extracted from (B), query (A) for the measurement value which corresponds to the lat, long, altitude and time from (B)... give or take some distance (eg, within 10 seconds or within 1km radius from the record from (B)).

An example would be I have a GPS device taped around my head and I am flying through the clouds. As I fly, I record my lat, long, altitude and time of measurement. When I get home, I want to look at the weather data that I have available and show what the temperature was at lat_x, long_x, altitude_x and time_x. I want to do this for each measurement that I made while flying so that I can work out what temperatures I experienced during my flight. However, since I know my measurements may not be 100% accurate, I'd like to give a bit of tolerance to the time of my measurement, as well as to the location (imagine I have a donut with radius r around my center).

How can I achieve this? What functions should I look to use?

Happy to clarify further if needed.

UPDATE

As requested, some example data & expected output.

altitude is in feet.
temp in deg c

Weather data (A):

CREATE TEMP TABLE weather AS
SELECT lat, lon, alt, time::timestamp, temp
FROM ( VALUES
  ( 23, 7  , 30000, '2017-01-01 00:01:00', -4  ),
  ( 35, 13 , 33000, '2017-01-01 00:01:30', -9  ),
  ( 45, 19 , 36000, '2017-01-01 00:02:00', -11 ),
  ( 53, 30 , 40000, '2017-01-01 00:02:30', -15 )
) AS t(lat, lon, alt, time, temp);

Flight path (B):

CREATE TEMP TABLE flightpath AS
SELECT id, lat, lon, alt, time::timestamp
FROM ( VALUES
  ( 1, 7 , 9 , 29000, '2017-01-01 00:01:05' ),
  ( 1, 35, 14, 34000, '2017-01-01 00:01:31' ),
  ( 1, 44, 19, 37000, '2017-01-01 00:01:59' ),
  ( 1, 55, 31, 40100, '2017-01-01 00:02:28' ),
  ( 2, 20, 9 , 60000, '2017-01-01 00:14:05' )
) AS t(id, lat, lon, alt, time);

In the above example, I would expect to extract all records from (B) where id = 1. I'd then want to go over each record in (A) and extract records within some range according to lat, lon, altitude & time. So, for example, I would like a tolerance of +-3 for lat, lon, altitude and time... I would then expect to see a new table:

id | lat | lon | altitude (feet) | time                | temperature (c)
------------------------------------------------------------------------
1  | 7   | 9   | 29000           | 2017-01-01 00:01:05 | no measurement
1  | 35  | 14  | 34000           | 2017-01-01 00:01:31 | -9
1  | 44  | 19  | 37000           | 2017-01-01 00:01:59 | -11
1  | 55  | 31  | 40100           | 2017-01-01 00:02:28 | -15

First, to make sense of this we convert to use geography points, rather than lat-long. I assume this is in WGS84. We do this because measuring a difference in lat/long doesn't make any sense. As you approach the poles the differences between 3 degrees of lat/long will be nothing compared to the equator.

BEGIN;
  ALTER TABLE flightpath ADD COLUMN geog geography;
  UPDATE flightpath SET geog =
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;
  ALTER TABLE flightpath DROP COLUMN lon, DROP COLUMN lat;
COMMIT;

BEGIN;
  ALTER TABLE weather ADD COLUMN geog geography;
  UPDATE weather SET geog =
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;
  ALTER TABLE weather DROP COLUMN lon, DROP COLUMN lat;
COMMIT;

Now you can use ST_DWithin and datetime math.

SELECT
  id,
  f.alt,
  f.time,
  coalesce(w.temp::text, 'no temperature') AS temp
FROM flightpath AS f
LEFT OUTER JOIN weather AS w
  ON (
    (
      w.time - f.time < '3 minutes'::interval      -- diff in time
      AND  f.time - w.time < '3 minutes'::interval
    )
    AND abs(w.alt - f.alt) <= 1000                 -- diff in alt
    AND ST_DWithin(w.geog, f.geog, 1000000)        -- diff in distance (meters)
  )
WHERE id = 1
ORDER BY f.alt;

That outputs something like this..

 id |  alt  |        time         |      temp
----+-------+---------------------+----------------
  1 | 29000 | 2017-01-01 00:01:05 | no temperature
  1 | 34000 | 2017-01-01 00:01:31 | -9
  1 | 37000 | 2017-01-01 00:01:59 | -11
  1 | 40100 | 2017-01-01 00:02:28 | -15


Oracle - Query to join fact table to bridge table and show bridge group rows in one row? I have a data warehouse with a One-To-Many relationship:

"Fact" table contains: Product_Group_ID
"Product_Bridge" table contains: Product_Group_ID, Product_ID
"Product" table contains: Product_ID, Product_Name, other product attributes..

I've inserted the data into the relevant tables, but how can I query the database and retrieve all products from a product group in the one row result? I've never used a bridge table before and could not find any decent/helpful examples online for how to actually query between the bridge/fact/dimensions.

i.e. the desired result would look like:

product_group_ID product_ID product_name product_ID_1 product_name1 product_ID_n product_name_n

Any suggestions would be greatly appreciated.

Based on your example output you can use LISTAGG. The basic query that you can add whatever other criteria to is;

SELECT DISTINCT (p.product_group_id ||' '|| LISTAGG(c.product_id||'  '||c.product_name, ' ')
                                   WITHIN GROUP(ORDER BY p.product_group_id, c.product_id)
                                   OVER (PARTITION BY p.product_group_id))
  FROM product c
       INNER JOIN
       product_bride p ON c.product_id = p.product_id

If you need more attributes from the product then you will need to concatenate them in the LISTAGG. I haven't included a join to the Fact table but that can easily be included if you need something from it.


Check if value exists in postgres text column I have a direct_selector column

        direct_selector
----------------------------------------------
 a:2:{i:0;s:7:"transit";i:1;s:9:"departure";}
 a:1:{i:0;s:9:"departure";}
 a:1:{i:0;s:7:"arrival";}

select *
from disc_agent
where direct_selector = ANY('{"departure"}');

This query gives me nothing
When I try

select *
from disc_agent
where '"departure"' = ANY(direct_selector);

It gives me an error

  ERROR: op ANY/ALL (array) requires array on right side

So what I should do to check if value exists?

That is not an "array column". The contents of a real array (or even JSON) would be displayed in a different way. I guess that's just defined as text or varchar.

It's not a valid syntax for an array so you can't use any of Postgres' array functions and it's not valid JSON either so you can't use any of Postgres JSON functions. As this is plain text, the only thing you can use is LIKE or regex comparisons.

Something like:

where direct_selector LIKE '%"departure"%';
-- or
WHERE direct_selector LIKE '%"departure"%' AND direct_selector LIKE '%"transit"%'


How to send parameters to BCP inside dynamic sql using sp_executesql We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a parameter. Is there any way to send that parameter value all the way in? When running this, the error message is:
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@RId".

Query:

DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';

DECLARE @SQL nvarchar(500);

SET @SQL
    = N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = @RId" queryout @Path -t, -T -N -S SERVERNAME\INSTANCENAME''';

EXEC sp_executesql @SQL,
                   N'@RId nVARCHAR(500), @Path VARCHAR(500)',
                   @REPORTID,
                   @Path;

I think you'll have to externalize those parameters when generating the dynamic SQL.  Something like:

DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';

DECLARE @SQL nvarchar(500);

SET @SQL
    = N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''''' + @REPORTID + '" queryout ' + @Path + ' -t, -T -N -S SERVERNAME\INSTANCENAME''';
PRINT @SQL
--EXEC sp_executesql @SQL

The PRINT statement reveals the generated dynamic SQL

master.dbo.xp_cmdshell 'BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''SomeID_Resend2019-02-20 11:38:24.040" queryout \\server\folder\_FileName.csv -t, -T -N -S SERVERNAME\INSTANCENAME'


Optimizing removal of duplicate rows from recursive CTE query I need to write a recursive SQL query to fetch multiple projects together with their lineage from the following table:

CREATE TABLE #Project
(
Name VARCHAR(10),
ParentName VARCHAR(10)
)

INSERT INTO #Project(Name, ParentName) VALUES ('P', NULL);
INSERT INTO #Project(Name, ParentName) VALUES ('C1', 'P');
INSERT INTO #Project(Name, ParentName) VALUES ('C2', 'P');
SELECT Name, ParentName FROM #Project

(this is just a toy example, so please ignore table design issues and the lack of indexing)

So, given multiple project names, I need to fetch a table that contains the projects as well as all their ancestors, because the result table will have to support lineage queries for a given Project name

In order to do this, I came up with the following recursive CTE query:

WITH Lineage (Name, ParentName)
AS
(
SELECT        Name, ParentName
FROM            #Project WHERE Name = 'C1' OR Name = 'C2'
UNION ALL
SELECT        #Project.Name, #Project.ParentName
FROM            #Project INNER JOIN Lineage ON #Project.Name = Lineage.ParentName
)
SELECT Name, ParentName FROM Lineage

And, here is the result of the query:

Name | ParentName
C1   | P
C2   | P
P    | NULL
P    | NULL

As expected, this returns projects C1 and C2, as well as their parents, which will be duplicated for each child, unfortunately.

In order to get rid of the duplicated parents, I added DISTINCT to the final SELECT:

SELECT DISTINCT Name, ParentName FROM Lineage

Which returns:

Name | ParentName
P    | NULL
C1   | P
C2   | P

Even though this produces the desired result, I am concerned about the performance of this query. The Lineage CTE will surely contain a lot of extra rows if I let it return these duplicates, which I remove at a later step in the query.

Is there any way to rewrite the CTE such that it removes these duplicates directly?

UPDATE: Just in case my example wasn't clear enough, I need to somehow specify the project names for which to fetch the lineages.

How about this? Kind of a hack - but row_number should be quicker than distinct.

;WITH Lineage AS
(
SELECT
    Name
   ,CASE WHEN ROW_NUMBER() OVER (PARTITION BY ParentName ORDER BY ParentName) > 1
         THEN NULL
         ELSE ParentName END AS ParentNameToJoin
   ,ParentName AS ParentNameToDisplay
FROM
    #Project
WHERE
    Name in ('C1','C2')
UNION ALL
SELECT
    p.Name
   ,CASE WHEN ROW_NUMBER() OVER (PARTITION BY p.ParentName ORDER BY p.ParentName) > 1
         THEN NULL
         ELSE ParentName END AS ParentNameToJoin
   ,p.ParentName
FROM
    #Project as p
INNER JOIN
    Lineage ON p.Name = Lineage.ParentNameToJoin
)
SELECT
    Name
   ,ParentNameToDisplay
FROM
    lineage


SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server's databases. I have a script to do this, and it has been working quite well when the local server is 2008 R2 or 2017. But I just installed 2019 and the same script puts the same value in every row of the table, rather than a different value for each row. It is as though the function is executed only once, then that value is used in the UPDATE statement. Perhaps it is some kind of optimization 2019 is doing? Here is a snippet of the script.
UPDATE Guest SET GuestFirstName=Utility.dbo.RANDWORD() WHERE GuestFirstName IS NOT NULL;
RANDWORD grabs a random word from a table containing approximately 100,000 English words.

I suspect this is because of a new SQL 2019 feature, scalar UDF inlining:

With this new feature, scalar UDFs are automatically transformed into scalar expressions or scalar subqueries that are substituted in the calling query in place of the UDF operator. These expressions and subqueries are then optimized. As a result, the query plan will no longer have a user-defined function operator, but its effects will be observed in the plan, like views or inline TVFs.

This can be a performance benefit, but in your case it appears to be processing the function only once, then applying the same result to every row.
The bottom of the linked article describes several ways to disable scalar UDF inlining.
You can disable it database by database:
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Or force this option query-by-query, using a hint:
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Or you can disable it by altering the UDF itself:
WITH INLINE = OFF


How to use mongoexport command with ssl? I am trying to export collection data from (SSL MongoDB 4.x), for this I am running below command on linux terminal.
Earlier I used mongoexport command in Linux terminal without SSL which works properly for (MongoDB without SSL).

sudo mongoexport --verbose=1 --host X.X.X.X:27018 --ssl --sslCAFile "certificate.pem"  -u username -p password --authenticationDatabase XYZ_Database --collection XYZ_Collection --out Output.csv

Error

2020-01-28T15:15:03.266+0000    could not connect to server: server selection error: server selection timeout
current topology: Type: Single
Servers:
Addr: X.X.X.X:27018, Type: Unknown, State: Connected, Average RTT: 0, Last error: connection(X.X.X.X:27018[-121]) connection is closed

You are using the --sslCAFile argument, this is to specify the Certificate Authority file to be used against the server's certificate.

I think you might need to use --sslPEMKeyFile instead of --sslCAFile.
Better yet, you might need to specify them both if you are using self-signed certificates with a custom Certificate Authority.


Is there anything wrong with splitting a complicated view into many small ones? I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values such as probability_of_buying_premium_membership. I have around 20 such columns of varying complexity (both in terms of lines-of-code and also computational complexity), ranging from a single line of SQL to several dozen. Right now they are all in one monster view.

Is there a down-side to splitting them into multiple smaller views and joining them by customer_id?

Ie, break it down into views called
customer_life_time_value, customer_purchases_per_week and so on, and then recreate the monster view by joining 20 views? It seems like there shouldn't be a performance hit because of the joining, as it's over a indexed primary key. Many of the columns/views will perform similar calculations (purchases_per_week and purchases_per_quarter would look very similar), but it seems like the DB should be smart enough to share computation if I am selecting from the joined view.

I am using Postgres, but interested for answers in general.

Is there a down-side to splitting them into multiple smaller views and joining them by customer_id?

Yes, definitely. Each view has to scan the whole underlying table on its own, and then you add 20 joins after that. The index does not apply to the derived tables you are joining. The single SELECT can make do with a single scan over the table (or index), it should be substantially cheaper.

Proof: db<>fiddle here


SQL server AlwaysOn alternate option for readintent=true I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly"
Now my question is , if some application don't have have these newest drivers and I(DBA) want to block this application's read only connections to primary and want the readonly requests of this application always routes to a AlwaysOn secondary replica then how this can achieved without "applicationIntent= readonly" option? Does anyone have any handy script to achieve this?

I(DBA) want to block this application's read only connections to primary and want the readonly requests of this application always routes to a AlwaysOn secondary replica then how this can achieved without "applicationIntent= readonly" option?

The simple solution is to configure the application to connect directly to the host hosting the secondary replica.  The downside is that if the secondary is offline, the application won't be able to connect.
Alternatively, if your SQL Instances are standalone instances configured to listen on all IPs (the default), you can add an additional Client Access Point (ie a Network Name and an IP Address), to the cluster in a new Application (Cluster Resource Group), and configure the preferred owners of the group, and/or the possible owners of the Client Access Point to control which node the the new Client Access Point run on.  The application would be configured to connect to the Network Name, which could fail over to some node if the preferred owner node is offline.


Unable to remove additional tempdb file In our production DB, we only have 1 tempdb file and it had ballooned to 180GB.

I ran:

Alter Database tempdb
modify file (name='tempdev', size=30gb);

to resize the primary file, and then ran additional:

ALTER DATABASE tempdb
ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData\tempdb2.ndf', Size=30GB);

to add additional tempdb files. I ran into a drive space error,
as the primary did not shrink to 30gb and the operation was halted mid-way.

I am now left with one additional tempdb file that is 30GB while the primary is 180GB and I am unable to run get rid of it.

I have tried running

DBCC SHRINKFILE('tempdev2', EMPTYFILE)

but I get an error:

  Could not locate file 'tempdev2' for database 'tempdb' in sys.database_files.
  The file either does not exist, or was dropped.

I feel this error is because the operation was not able to complete successfully. (Do I have corruption?)

And when I try to run

ALTER DATABASE tempdb
REMOVE FILE tempdev2;

I get the following error:

  The file "M:\SQLData\tempdb2.ndf" has been modified in the system catalog. The new path will be used the next time the database is started.
  Msg 5042, Level 16, State 1, Line 35
  The file 'tempdev2' cannot be removed because it is not empty.

The sys.database_files looks like this

Results from tempdb.sys.all_objects

Sys.database_files

Edit:
I was able to resolve the 'file not found' filename corruption that i was receiving when trying to run the EMPTYFILE Shrink. I used the File ID instead of the logical name.

DBCC SHRINKFILE(3,EMPTYFILE); -- File ID instead of logical name.

Another method I found that worked was renaming the file.

ALTER DATABASE tempdb MODIFY FILE (NAME =tempdev2, NEWNAME =tempdev3);

So your current situation is a full drive where you have two TempDB's that you want to modify the old one's size and the newly created one is having difficulties being removed?

  There are limitations for use of the DBCC SHRINKDATABASE command on
  the tempdb database. The target size for data and log files cannot be
  smaller than the size that is specified when the database was created
  or smaller than the last size that was explicitly set by using a
  file-size-changing operation such as ALTER DATABASE that uses the
  MODIFY FILE option or the command. Another limitation of BCC
  SHRINKDATABASE is the calculation of the target_percentage parameter
  and its dependency on the current space that is used.

Try running:

dbcc shrinkdatabase (tempddev, '70')

This should set your file to ~54GB which should be a safe size considering I am assuming your size or model was 30GB. Adjust the % accordingly.

Microsoft - How to shrink tempdb

Now for the second file, give this a try:

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev2', EMPTYFILE)
GO

USE [tempdb]
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

If the issue persists, you'll have to restart SQL Server to remove the file as TempDB is in use.

Microsoft Forum - File in use

SQL Authority - Shrink and remove Tempdb file

One last note, you typed this:

  ALTER DATABASE tempdb
  ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData**tempdb3**.ndf', Size=30GB);

Your screenshot shows tempdb2, make sure your accurate with all the names and double check them. Just in case there is a mistype and that is causing your headaches.

There are some cases where running

DBCC FREEPROCCACHE

can help alleviate the pain and allow you to shrink the file.

Ozar - When TempDB won't shrink.

If you are still having issues, see what is out in your tempdb:

select * from tempdb.sys.all_objects
where is_ms_shipped = 0;

This will let you see your size of the tempdb files.

USE [tempdb]
SELECT
   [name]
   ,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2))                                 AS [Size]
   ,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2))            AS [Used]
   ,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],'SpaceUsed'))/128.,2))      AS [Unused]
FROM [sys].[database_files]

From your post we see that you have files used in TempDB and there is space free. SQLSunday by Daniel Hutmacher has a good link on shrinking your DB with additional methods that were similar to Ozar, I would recommend to give this a try as well with caution.

There is a post by Mike Good - SQL Central on shrinking your TempDB by adjusting the snapshot level of TempDB with a rollback period to kill any connections that may have lingered on your db.


SQLCLR User-Defined Type (UDT) SCH-M blocking I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# SQLCLR UDT’s.  I’ve been on the Internet for an extended period of time researching my problem without success on a solution.

Also, this "might" be a linked server issue as we see linked server queries accessing the UDT seem to block queries (sch-m running on the remote server).

Back story:

Back in 2006, we began porting some of our DB2 processes down to SQL Server 2005 (we’ve upgraded to SQL 2008 since then, but still need to use our UDT for a lot of current processes). Under DB2, we had made extensive use of the DB2 TIMESTAMP data type -- CCYY-MM-DD.HH.MM.SS.mmmmmm -- which has precision down to the microsecond. Because SQL Server, at that time, did not have DATETIME2, we were forced to create our own C# SQLCLR UDT to emulate the DB2 TIMESTAMP. The UDT was created as VARBINARY with properties that allowed us to reference things like:

            Column.Timestamp        (CCYY-MM-DD.HH.MM.SS.mmmmm)
            Column.Date                     (CCYY-MM-DD)
            Column.Microsecond     (mmmmmm)
            Etc.

When multiple processes access the UDT with just the column name (without referencing a specific property), we don’t seems to have problems.

However, when we have multiple processes accessing the UDT and reference one of the properties (.Timestamp, etc.), we encounter Sch-M locks and other processes also accessing that UDT are forced to wait.

I’ll admit that I have limited knowledge on all things CLR and UDT.

I simply do not understand why we’re getting Schema Modification locks while merely selecting the column and specifying one of the underlying properties.

I’d appreciate any suggestions you might have.

Thanks,

When you use SQL CLR objects across databases, SQL Server will automatically convert them if certain conditions are met (https://technet.microsoft.com/en-us/library/ms178069(v=sql.105).aspx).

In my case, this automatic conversion was taking out the SCH-M locks. I fixed the problem by manually converting the contents of my SQL CLR type to binary, and passing it to the second db as varbinary(max).  I then deserialized back to my CLR type in the second db.


copying Data from Big Data Table I have a table with around 400 GB data I want to copy some data to some other temporary database to make some more space. Can you please give me a efficient way to do that?

My DBMS is SQL server 2014 standard edition.

You want to use a minimally logged inserts. If your temp/scratch database is on the same instance you can simply do.

SELECT * INTO DATABASE.SCHEMA.TargetTable FROM SourceTable;

The above statement create the table in the target database and bulk insert the data.

If you want to copy the data to a different instance you are best off using SSIS.


Conditional table reference I have a table tree:

+------+-------+
| id   | name  |
+------+-------+
| 0    | tree1 |
| 1    | tree2 |
| 2    | tree3 |
| 3    | tree4 |
+------+-------+

A table pen:

+------+------+
| id   | name |
+------+------+
| 0    | pen1 |
| 1    | pen2 |
| 2    | pen3 |
| 3    | pen4 |
+------+------+

And a third table task that "attaches" a task to either a tree or a pen:

+------+------+-------+
| type | id   | name  |
+------+------+-------+
| 0    | 1    | foo   |
| 0    | 2    | bar   |
| 1    | 1    | fee   |
| 1    | 2    | beer  |
+------+------+-------+

When type is 0, it means id references a tree. When type is 1, it references a pen (and so on with many different tables).

How can I do this and ensure referential integrity?

Creating a table task adding a refer id field for each referred table, in this way:

type : I prefer something as 'tree', 'pen'. Use an enum for this: ENUM('tree', 'pen');
tree_id : int (it can be NULL), that refers to tree table;
pen_id : int (it can be NULL), that refers to pen table;
name : varchar;

Some test data:

+------+---------+--------+-------+
| type | tree_id | pen_id | name  |
+------+---------+--------+-------+
| tree | 1       | NULL   | foo   |
| tree | 2       | NULL   | bar   |
| pen  | NULL    | 1      | fee   |
| pen  | NULL    | 2      | beer  |
+------+---------+--------+-------+


Increased query_cache_size, Queries slowed drastically on increased traffic I increased our servers query_cache_size to 2gb, hoping for performance benefits. After sometime the query performance would drastically decrease. Once I dug in deeper, I found the queries in State "Waiting for query cache lock ". Lowering the query cache to 512M brought the performance back to normal. Why would this happen, and why would lowering it increase performance ?

That happens because all requests to the QC are serialized. The larger QC is the more time it takes to find and invalidate entries.


Script to Add a Job to a SQL Server DB I want to add a job to my DB to make a back up weekly but i want to do it from an script, i've serched and with what i found i made this:

CREATE SCHEMA job
go
CREATE PROC BACKUPS @dir varchar(max)
AS BEGIN
    DECLARE @comando nvarchar(max) = N'BACKUP DATABASE STAZIONE TO DISK = '''+@dir+'-'+convert(char(8),getdate(),104)+'.bak'' WITH  COMPRESSION,  ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert), STATS=1'
    EXEC job.sp_add_job @job_name = @job ;--Add a job
    EXEC job.sp_add_jobstep @job_name = @job, @step_name = N'Backing',  @subsystem = N'TSQL', @command = @comando   --Schedule the job at a specified date and time
    EXEC job.sp_add_jobschedule @job_name = @job, @name = N'DB_BACKUP', @freq_type=8, @freq_interval =1, @active_start_date = @startdate,   @active_start_time = @starttime -- Add the job to the SQL Server Server will run weekly
    EXEC job.sp_add_jobserver @job_name =  @job, @server_name = @servername
END
GO

But when executin i get this:

  The 'BACKUPS' module depends on the missing object 'job.sp_add_job'. Even so, the module will be created; however, you can not run successfully until the object exists.

How can i create a job for my DB ?

Instead of writing your own solution, I would highly recommend to use Ola Hallengren's SQL Server Backup Solution.

Refer to : Bad habits to kick : avoiding the schema prefix by Aaron Bertrand.

Below should work for you (I have not tested it):

CREATE SCHEMA job
go
CREATE PROC job.BACKUPS @dir varchar(max)
AS BEGIN
    DECLARE @comando nvarchar(max) = N'BACKUP DATABASE STAZIONE TO DISK = '''+@dir+'-'+convert(char(8),getdate(),104)+'.bak'' WITH  COMPRESSION,  ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert), STATS=1'
    EXEC msdb.dbo.sp_add_job @job_name = @job ;--Add a job
    EXEC msdb.dbo.sp_add_jobstep @job_name = @job, @step_name = N'Backing',  @subsystem = N'TSQL', @command = @comando   --Schedule the job at a specified date and time
    EXEC msdb.dbo.sp_add_jobschedule @job_name = @job, @name = N'DB_BACKUP', @freq_type=8, @freq_interval =1, @active_start_date = @startdate,   @active_start_time = @starttime -- Add the job to the SQL Server Server will run weekly
    EXEC msdb.dbo.sp_add_jobserver @job_name =  @job, @server_name = @servername
END
GO


Transform a number in symbols in a MySql SELECT I want to know if is possible to transform a numeric return (INT column) in a sequence of symbols direct in a MySQL SELECT.

My table is like below:

| ID        | category |
|-----------|----------|
| product_1 | 5        |
| product_2 | 4        |
| product_3 | 3        |

I want a SQL SELECT like SELECT ID, some_mysql_fancy_func(category,'*') AS symbol_category FROM MyTable that could returns the following:

| ID        | symbol_category |
|-----------|-----------------|
| product_1 | *****           |
| product_2 | ****            |
| product_3 | ***             |

Is it possible?

I can do it programmatically, but this is not my intention, I want some built-in (or even custom) function that can achieve this result direct in the SELECT statement.

SELECT ID,
       REPEAT('*', IFNULL(category, 0)) AS SymbolCategory
    FROM ...

This assumes you want NULL to be equivalent to 0.


Google Big Query Invalid time zone: +0000 Getting the error

Query Failed
Error: Invalid time zone: +0000

Is there a way to ignore time zones or default everything to UCT
This is my where condition

WHERE TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%E6S%z", PARSE_TIMESTAMP("%Y%m%d", date )),"UCT") = TIMESTAMP('2018-03-05 00:00',"UCT")

date column is string in this format 20180307

Community wiki answer originally left by the question asker as a comment:

The fix was removing the timestamp formatting from FORMAT_TIMESTAMP

TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%d", PARSE_TIMESTAMP("%Y%m%d", ga.date )))


Database to track employee performance This is going to be very basic as I'm a complete newbie when it comes to database design.

I've been tasked with setting up a way to track employee data over a number of months and potentially years. I need to keep track of their Performance, Quality and Attendance on a daily basis but I'm unsure on how to setup my tables so that I don't run into troubles down the line.

At the moment I made a very basic database in Access 2010. I have 2 tables, one holding Employee details (FirstName, LastName, Email, Manager) and one holding Manager details (FirstName, LastName, Email). I've successfully created a relationship between the manager field in the Employee table and the Manager table but I'm at a loss on how to implement the tracking of the actual stats.

How should I go about creating table(s) from now? Should I make a table each for Performance, Quality and Attendance with the primary key as the date? Or should there be a table for each employee? How should I relate the data to each employee and each day so that it doesn't become complicated?

Final goal is to use vb.net to create an application for generating reports if it matters. I don't have a problem with that step but I want to make sure I have a solid foundation first.

Any help appreciated, thanks.

I suggest you read up about database normalization. Here is a good starting point: Third normal form

Definitely do not create one table per employee as management of that database structure will soon become too cumbersome and your application will need to be modified every time a new employee is hired.

I do not know what your business rules are, but assuming that Performance, Quality and Attendance all store different types of data (i.e., different column types) then they should be in separate tables. I could not guess at what primary key you should use without knowing what types of data are stored in those tables.

Can you provide more information?


sql server: trigger to update in sum after update I have 3 tables:

Artist = {artistname, startdate, members, genre}
Musician = {msin, firstname, lastname, birthdate}
Plays = {artistname, msin, share}

Key:

Artist: artistname
Musician: msin
Play: artistname (foreign key ref Artist), msin (foreign key ref Musician)

I need to create trigger on Plays:  The members attribute of the Artist table should always equal the number of musicians who are members of the artist. Any insertion, deletion, update in Plays triggers changes of members attribute in Musician.

So I did following:

create trigger memberAdd
on Plays
after insert
as
if exists ( select * from Plays p )
begin
    update Artist set members = members + 1 where artistname = (select artistname from inserted)
end
else
begin
    update Artist set members = 1 where artistname = (select artistname from inserted)
end

create trigger memberDel
on Plays
after delete
as
begin
    update Artist set members = members - 1 where artistname = (select artistname from deleted)
end

The problem is update. I can use deleted/inserted table to trigger changes. but for updating existing the data which table should I use? How can I the find the artistname?

create trigger memberUpdate
on Plays
after update
as
begin
    update Artist
    set members = (select sum(msin)
                    from Plays p
                    group by artistname
                    having artistname = (select artistname from ????))
end

I'd like suggest another approach.

Instead of adding or deleting one record, you can use the same trigger for both insert and delete, -(Do you really need to count records after update?)-, and then you can count all records and set members field.

Just keep in mind that you can insert or delete more than one record, you should consider an INNER JOIN with inserted and deleted tables.

CREATE TABLE Artists (artistName nvarchar(200), members int);
CREATE TABLE Plays   (artistName nvarchar(200), share int);

INSERT INTO Artists VALUES ('Eric', 0), ('Luca', 0);
GO

CREATE TRIGGER trgPlays
ON Plays
AFTER insert, delete, update
AS

    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        UPDATE     Art
        SET        members = (SELECT count(*) FROM Plays WHERE artistName = i.artistName)
        FROM       inserted i
        INNER JOIN Artists Art
        ON         Art.artistName = i.artistName
    END
    ELSE IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        UPDATE     Art
        SET        members = (SELECT count(*) FROM Plays WHERE artistName = d.artistName)
        FROM       deleted d
        INNER JOIN Artists Art
        ON         Art.artistName = d.artistName
    END
GO

SELECT artistName, members
FROM   Artists
GO

artistName | members
:--------- | ------:
Eric       |       0
Luca       |       0

INSERT INTO Plays VALUES ('Eric', 1), ('Eric', 2), ('Luca', 1);
INSERT INTO Plays VALUES ('Eric', 3);
DELETE FROM Plays WHERE artistName = 'Eric' AND share=2;
GO

SELECT artistName, members
FROM   Artists
GO

artistName | members
:--------- | ------:
Eric       |       2
Luca       |       1

dbfiddle here