fn_trace_gettable Error SQL Server 2008 When I Execute
 SELECT * FROM FN_TRACE_GETTABLE(N'E:\Trace_Files\trace1.trc', 1)
It says "You do not have permission to run 'FN_TRACE_GETTABLE", What type of permission on server required to execute the above query ??

It Requires ALTER TRACE permission on the server.

Note :- This feature will be removed in a future version of Microsoft SQL Server (2014 above). Avoid using this feature in new development work.


sqlite - join between two tables I have two tables, same structure (filename, filesize, permissions), and possible different or missing values between the two..

I would like to display all the records with the following fields:

filename | filesizeTable1 | permissionsTable1| filesizeTable2 | permissionsTable2 | match

and I would like:

the same filename to be displayed only once
if filename is missing in one of the two tables, the columns filesize and permissions for that table would be empty
if filename exists on both tables, but one of the other fields is different the field "match" would display "1"
if filename exists on both tables, and the other fields are the same, the field "match" would display "0"

The step 1 is achievable with a join between the two tables (select filename from table1 union select filename from table2 order by filename;) but I'm stuck on how to proceed to have empty lines when a filename is missing in one of the two tables..

Solution (thanks Lennart):

select a.filename as filename
     , a.size as filesize1, a.permissions as permissions1
     , b.size as filesize2, b.permissions as permissions2
     , CASE
        WHEN a.filename IS NULL or b.filename IS NULL THEN 1
        WHEN a.size != b.size THEN 2
        WHEN a.permissions != b.permissions THEN 2
        ELSE 0
       END  as Match
from scanTableHost1 as a
LEFT JOIN scanTableHost2 as b
    ON a.filename = b.filename
UNION
select b.filename as filename
     , a.size as filesize1, a.permissions as permissions1
     , b.size as filesize2, b.permissions as permissions2
     , CASE
        WHEN a.filename IS NULL or b.filename IS NULL THEN 1
        WHEN a.size != b.size THEN 2
        WHEN a.permissions != b.permissions THEN 2
        ELSE 0
       END  as Match
from scanTableHost2 as b
LEFT JOIN scanTableHost1 as a
    ON a.filename = b.filename

I assume when you say JOIN you really mean a more general combination of the two tables. JOIN is an operator in SQL, so you should avoid using it when you are referring to a UNION. Anyhow, if I get it right you want a FULL OUTER JOIN between two tables:

select COALESCE(a.filename, b.filename) as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
from T1 as a
FULL JOIN T2 as b
    ON a.filename = b.filename

COALESCE is a function that return the first value from left to right that is not null.

However, from what I understand SQLite only supports LEFT JOIN so you have to rewrite the query using two LEFT JOINS and a UNION as:

select a.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
     , CASE WHEN b.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)
                      THEN 0 ELSE 1
                 END
       END
from T1 as a
LEFT JOIN T2 as b
    ON a.filename = b.filename
UNION
select b.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
     , CASE WHEN a.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)
                      THEN 0 ELSE 1
                 END
       END
from T2 as b
LEFT JOIN T1 as a
    ON a.filename = b.filename

Note that a and b changed place in the second leg of the union.


SQL 2014 Always On Availability Groups - Read Replica I'm not familiar with Always On Availability Groups, but our company is using it to provide a read-only replica for our main production database so that certain users can report against it and generate ad-hoc queries.

Since this is read-only, I know that no modifications can be made to this database. However, I was wondering if it's ever necessary to rebuild indexes or regenerate statistics on a read replica like this? Or, does this happen automatically when the the source database has indexes rebuilt or statistics regenerated?

No, you don't need to rebuild indexes or update statistics. You can't do any writes to a readable replica at all.


Kerberos Configuration Manager - Does it work with SQL Server 2016? I have just installed Kerberos Configuration Manager on a clustered SQL Server 2016 server and trying to connect using the app, it keeps failing.

I can connect using SSMS, but not with Kerberos Configuration Manager. I've tried leaving the fields empty and populating them, always fails.

Does anyone know if Kerberos Configuration Manager works with SQL Server 2016?

This currently is not available yet for SQL Server 2016 unfortunately. You will have to manually add your SPN's using

 setspn –S MSSQLSvc/<server name>:1433 domain\account

EDIT: The Kerberos Configuration manager is now available for SQL Server 2016
https://www.microsoft.com/en-us/download/details.aspx?id=39046


Syncing data from one table to another I want to sync data from one table to another(only from 1 column) to another table.

Table A (
   col1 varchar(50) DEFAULT NULL,
   date_in timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
)

Table B (
   col1 varchar(50) DEFAULT NULL,
)

My strategry is to sync data that changed in timeframes of 5 minutes .

I sync with accuracy of 1 sec. Now there can be a situation where I want to sync data since 2020-05-03 00:05:00 until now (2020-05-03 00:10:00) and I receive x rows, but there are a few rows which are inserted to the database with for example date 2020-05-03 00:09:59 but they are not committed at the time I execute a SELECT query, so I miss them.

Plan A

Add some grace time, for example 10 seconds to my SELECT query.

Plan B

Have another thread which runs once a day and sync the "missed rows".

Are this plans legit? it there a better way of acomplishing this?

Plan Sound Cool but Depending on Overload on the Server for every 5 minutes will be huge. not to mention an overkill.

Use Triggers on Bulk or Inserts on The Table.

A trigger in SQL is a procedural code that is automatically executed in response to certain events on a specified table. It is important to understand how these small codes make such a huge difference in database performance.

what you need is a DML Trigger on table on Each insert or delete.

here's a mention.
https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/


One column in a join needs to be unique I am new to SQL. I have a join which uses three lookup tables. It is a bit complicated so I will publish the code here.

Create View TEST As select
        BDP_INSTITUTION_NAME,
        BIC,
        BDP_COUNTRY_NAME,
        BDP_ISO_COUNTRY_CODE,
        BDP_CITY,
        BDP_NETWORK_CONNECTIVITY,
        BDP_SERVICE_CODES,
        BDP_ISTARGET,
        BCDB_NAME,
        BCDB_LAENDERKENNZEICHEN,
        BCDB_AKTIVMERKMALBANK,
        BCDB_AKTIVMERKMALLAND,
        BCDB_AKTIVMERKMALBANKLAND,
        BCDB_SWIFTKENNZEICHEN,
        COUNTRYCODE,
        ISBDP,
        ISBCDB,
        BCDB_ORT,
        RMA.CRSPDT AS RMA_CRSPDT,
        RMA.ISSR AS RMA_ISSR,
        RMA.TP AS RMA_TP,
        RMA.SVCNM AS RMA_SVCNM,
        RMA.RMASTS AS RMA_RMASTS
       from
(SELECT
        bdp.bic,
        bdp.institution_name AS bdp_institution_name,
        bdp.country_name AS bdp_country_name,
        bdp.iso_country_code AS bdp_iso_country_code,
        bdp.city AS bdp_city,
        bdp.network_connectivity AS bdp_network_connectivity,
        bdp.service_codes as bdp_service_codes,
        bdp.isTarget AS bdp_isTarget,
        bcdb.name as bcdb_name,
        bcdb.laenderKennzeichen as bcdb_laenderKennzeichen,
        bcdb.aktivMerkmalBank AS bcdb_aktivMerkmalBank,
        bcdb.aktivMerkmalLand AS bcdb_aktivMerkmalLand,
        bcdb.aktivMerkmalBankLand AS bcdb_aktivMerkmalBankLand,
        bcdb.swiftKennzeichen AS bcdb_swiftKennzeichen,
        CASE
            WHEN bcdb.laenderKennzeichen IS NOT NULL THEN bcdb.laenderKennzeichen
            ELSE bdp.iso_country_code
        END AS countryCode,
        CASE
            WHEN bdp.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbdp,
        CASE
            WHEN bcdb.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbcdb,
        bcdb.ort AS bcdb_ort
        FROM BDP bdp LEFT JOIN BCDB bcdb ON bdp.bic = bcdb.bic WHERE bdp.bic IS NOT NULL
    UNION ALL SELECT
        bcdb.bic,
        bdp.institution_name AS bdp_institution_name,
        bdp.country_name AS bdp_country_name,
        bdp.iso_country_code AS bdp_iso_country_code,
        bdp.city AS bdp_city,
        bdp.network_connectivity AS bdp_network_connectivity,
        bdp.service_codes as bdp_service_codes,
        bdp.isTarget AS bdp_isTarget,
        bcdb.name as bcdb_name,
        bcdb.laenderKennzeichen as bcdb_laenderKennzeichen,
        bcdb.aktivMerkmalBank AS bcdb_aktivMerkmalBank,
        bcdb.aktivMerkmalLand AS bcdb_aktivMerkmalLand,
        bcdb.aktivMerkmalBankLand AS bcdb_aktivMerkmalBankLand,
        bcdb.swiftKennzeichen AS bcdb_swiftKennzeichen,
        CASE
            WHEN bcdb.laenderKennzeichen IS NOT NULL THEN bcdb.laenderKennzeichen
            ELSE bdp.iso_country_code
        END AS countryCode,
        CASE
            WHEN bdp.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbdp,
        CASE
            WHEN bcdb.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbcdb,
        bcdb.ort AS bcdb_ort
    FROM BDP bdp RIGHT JOIN BCDB bcdb ON bdp.bic = bcdb.bic WHERE bdp.bic IS NULL)

  t1 left join RMA on (RMA.ISSR = substr(t1.BIC, 1,8) or RMA.CRSPDT = substr(t1.BIC, 1,8));

What I want is to have a unique BIC for each row in the 'TEST' view. The rest of the information is not important i.e. once I get a hit on a BIC it should not be repeated again, all the rest is not important what is important is if I get a BIC hit or not.

Through internet search I found out I cannot use DISTINCT in my case it does not help. What else can I do?

Something like this would work (in Oracle).

select c.* from (select b.*, row_number() over(partition by b.bic order by b.rnum) rnk from (select rownum rnum, a.* from test a) b) c where rnk = 1;


Do regular ALTERs weaken the performance? In a project, we need to frequently ALTER the tables to delete or add columns. I wonder if this weakens the database performance in long term. If it has a considerable negative impact, what is the approach to keep the database healthy and efficient?

I was thinking of the following approaches:

OPTIMIZE the tables
CREATE a new database with the final architecture, and INSERT the entire data

The database is mysql(5.5) with InnoDB.

It's an intuitively-sensible concern that you've raised because it would seem to make sense that frequent DDL operations might leave little bits of messiness behind, but my experience has been that this concern is unfounded with InnoDB.

Prior to MySQL 5.6, the vast majority of ALTER TABLE operations actually rebuild the entire table with the new structure, copy all the data, then swap in the new table and delete the old one... so, I see very little potential for frequent changes to have a negative impact down the road.

You might, though, be well-advised to OPTIMIZE TABLE on tables where you've repeatedly inserted and deleted test data to clean up wasted disk space and rebuild the indexes, but unless your test data has been comparable in size to your production data, even this will disappear in the mix as the tables take on live data.


Is the size limit of a json field before or after compression in Postgres? The size limit of a json field is 1GB (source: this StackOverflow answer):

json is the same as a text datatype but with JSON validation. The text datatype's maximum size is 1GB.

I ran some experiments with inserting json of varying sizes into a table in Postgres.
Taking pg_sizeof of rows here seems to indicate that Postgres does compression of the json data.
My experiment:
create table json_size (n integer, j json);

For n with values of 1, 10, 100, 1000, 10 000, 100 000, 1 000 000.
with q as (select generate_series(1, n)::bigint as x)
insert into json_size (n, j)
select 1, coalesce(json_agg(to_json(q.*)), '[]'::json) from q;

select n, pg_column_size(j) as size from json_size;

Results
n       size
1       10
10      92
100     996
1000    3224
10000   33488
100000  335115
1000000 3351365

Is the size limit of 1GB before or after this compression is done?

The limit of 1GB on varlena data types like json is before compression.
Note that you will need a lot of RAM to read and write such values. It is usually a problem and bad design to store such large values in a database.


how to take backup of a child table with foreign key dependency in mysql using bash Parent table : p_id, t_id, name , salary
Child table : c_id, t_id , age
key : Child.t_id=Parent.t_id

While taking backup i would like to take pull only the matching records from Child table . How can i do it via bash script ?

Thanks in advance .

Assuming MySQL 5.x

mysqldump has a --where option that can be used to limit/select rows to be backed up.

There isn't an option to join two tables using --where but you can identify which t_id values you want to backup and then run two mysqldumps as follows:

mysqldump -u... -p... --where="t_id in (10,100,200,300,.....)" database_name parent_table > parent.sql

mysqldump -u... -p... --where="t_id in (10,100,200,300,.....)" database_name child_table > child.sql

Once you have the parent.sql and child.sql dump files, load them into the target database and you should be good to go.

Also, note that I am assuming t_id column is indexed on both tables otherwise it may take a long time to dump the rows if the two tables have large number of rows.


Using Two LEFT JOIN or use AND with single LEFT JOIN? Using two LEFT JOINs i.e.

SELECT <some columns>
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON <condition1>
LEFT JOIN Table2 AS t3 ON <condition2>

is this same as using AND in single LEFT JOIN? i.e.

SELECT <some columns>
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON <condition1>
AND <condition2>

Both are same or different(in general)?

They are different. In the first option you get 2 times Table2 into your query. Once as t2 and once as t3. Both have a different content and you must put them somehow back together. To me this is more an OR instead of an AND. In the second option you get only the Table2 rows that meet both criteria.

Suppose you have Table2 with the following content:

| Color | Size |
|-------|------|
| Red   |  S   |
| Blue  |  S   |
| Blue  |  XS  |

Suppose you want to have the rows that are Blue and S. In your first option you get all rows (t2 for example with all Blue and t3 with all S) and in your second option you only get row 2.


Ideas to improve query performance SELECT SQL_CALC_FOUND_ROWS pn_posts.id
FROM   pn_posts
       LEFT JOIN pn_term_relationships
              ON ( pn_posts.id = pn_term_relationships.object_id )
       INNER JOIN pn_postmeta
               ON ( pn_posts.id = pn_postmeta.post_id )
       INNER JOIN pn_postmeta AS mt1
               ON ( pn_posts.id = mt1.post_id )
       LEFT JOIN pn_wc_product_meta_lookup wc_product_meta_lookup
              ON pn_posts.id = wc_product_meta_lookup.product_id
WHERE  1 = 1
       AND ( pn_posts.id NOT IN (SELECT object_id
                                 FROM   pn_term_relationships
                                 WHERE  term_taxonomy_id IN ( 8 ))
             AND pn_term_relationships.term_taxonomy_id IN ( 21 ) )
       AND ( pn_postmeta.meta_key = '_manage_stock'
             AND mt1.meta_key = '_stock_status' )
       AND pn_posts.post_type = 'product'
       AND ( pn_posts.post_status = 'publish' )
GROUP  BY pn_posts.id
ORDER  BY pn_postmeta.meta_value ASC,
          Field (mt1.meta_value, 'instock', '3days', 'outofstock'),
          wc_product_meta_lookup.min_price ASC
LIMIT  48, 24;

A first step in improving the performance of WordPress is to change the "meta" schema as discussed here:

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

If that does not help enough, I will take a deeper look at your query.


What happens if agent job owner account is deactivated in Active directory? We have a number of jobs owned by a DBA.
The DBA is now leaving the company so their account will be deactivated in Active Directory.

What affect will this have on the agent jobs?

Let us say we are talking about Windows login which part of AD and was owner of a job. Below is more detailed answer with couple of scenarios.

1. The login was removed from AD but not from SQL Server( someone forgot to remove it) and the login is member of sysadmin fixed server role

In above scenario the job will "still run". The reason is since job was added as administrator in SQL Server there is entry of this owner with SID in sys.server_principal which SQL Server knows, and since the login was member of sysadmin role no "impersonization" will be done and hence job will succeed.

2. The login was removed from AD and from SQL Server.

In this scenario the job will fail which is pretty much obvious

3. The login was removed from AD but not from SQL Server( someone forgot to remove it) and the login is NOT member of sysadmin fixed server role.

In this case job will fail because since owner is not sysadmin impersonization will be done and SQL Server will look for this account in Windows AD, since the account was removed the job will fail.

For SQL Server admin login like SA, even if SA is disabled and is owner of some job when you run the job it would succeed.

Tibor Karaszi has more detailed explanation on this.


Why do case/iif return nullable datatypes? Consider the following sproc:

create or alter procedure TestSproc
as
begin

declare @ZeroBit bit = 0, @OneBit bit = 1

-- Table.Id is a nullable integer column
select iif(Table.Id is null, @ZeroBit, @OneBit) as ColumnNotNull

end

Once it's been compiled, running the following:

select name, system_type_name, is_nullable
  from sys.dm_exec_describe_first_result_set_for_object(object_id('TestSproc'), 0)

shows the ColumnNotNull column correctly typed as a bit, but that it is nullable.

The nullability does not make sense to me, since the arguments I passed to iif are non-null constants, so why is the resultant column nullable? And is it possible to get it to be non-nullable without wrapping the expression in isnull to get around this, which to my mind is - or rather, should be - unnecessary?

The documentation for iif makes no mention of nullability, merely stating that this function "Returns the data type with the highest precedence from the types in true_value and false_value".

First off, the column is_nullable returned from sys.dm_exec_describe_first_result_set_for_object has the following definition as per docs.Microsoft.com:

  Contains the value 1 if the column allows NULLs, 0 if the column does
  not allow NULLs, and 1 if it cannot be determined that the column
  allows NULLs.

This means that the engine could also be looking for a situation where it can't predict the nullability of the return column. (This is more for your information than anything else - in this case it doesn't appear to be happening)

Now when we simplify the stored procedure and eliminate the variables as such:

CREATE PROCEDURE TestProc
AS

SELECT IIF('hello' = 'hello','true','false')
GO

We can see the nullability goes away.

This is because the variables you are utilising in your procedure (@Zerobit and @Onebit) are NULLABLE by definition. The variables themselves are getting set at run-time, but the underlying metadata for the data type of the variable is still nullable.

There is also a possibility that the query optimiser needs to account for all scenarios, and it can't use the value of the variable at runtime for an all-use plan.

If we rewrite the query and do not set the value of the variables, we will see the same results (The column metadata for the return of the procedure is still NULLABLE):

CREATE PROCEDURE TestProc
AS

DECLARE @trueoutput VARCHAR(10)
DECLARE @falseoutput VARCHAR(10)

SELECT IIF('hello' = 'hello',@trueoutput,@falseoutput)
GO

My suggestion for the above case would be (as Mikael Eriksson says) to use constants - this is unless it's a cut down example and this isn't possible. The issue then would be the returned data type would always be nullable without being wrapped with ISNULL, CASE etc


Oracle High-water Mark: how relevant it is when using ASSM tablespace for tables? I have several questions:

1) If Oracle manages both High-water Mark and Low High-water Mark in ASSM tablespace then do we have to think about that when thinking about performance? I mean, does Oracle manages deleted blocks itself and does not scan those blocks during table full scan, or do we still have to shrink those tables (alter table mytable shrink space;) in order to clean those deleted blocks and increase performance?

2) If ASSM gives automatisation for this and other related stuff then no doubtfully there's a performance trade-offs when choosing ASSM instead of MSSM. What should be major criteria on choosing ASSM or MSSM?

UPDATE. I did a test on my laptop with mytable (ASSM). First, inserted 1.000.000 records. Query "select count(*) from mytable" was executing at average 0.05 s. Then did full DELETE. The same query was executing at average 0.03 s, so maybe Oracle was still reading deleted blocks. Only after TRUNCATE the query executed at average 0.001 s. Maybe ASSM takes time to shrink table space, or still I have manually to shrink it?

ASSM is not about reducing high water mark of the table. The main benefit of ASSM is it automates the task of freelist management in order to handle the concurrent DML on the table. Freelists are used to allocate new blocks when data is added to the table. You will be able to see the performance benefits of ASSM when multiple sessions try to populate the data concurrently into a table.
As for the issue of deleted blocks and its performance impact, the answer is it depends. ASSM or any other oracle feature does not automatically remove blocks from table when data is deleted. If you delete large amount of data from a big table and do not explicitly shrink the table and if your queries mostly do full table can, then you will not see any performance benefit by deleting the table data. Even if you decide to shrink the table to get immediate performance benefit, you will need to think about how quickly new data will be added to the table and the table will grow back, basically a cost-benefit analysis.
Again, a better explanation of this can be found in oracle documentation and in books like Expert Oracle Database Architecture authored by Tom Kyte.


Where can I find good literature for beginners? I am trying to find some database tutorials,books and similar, since I have started this course in college, and received some material, but it is really hard to manage through all the info on the net. I searched through the questions on the site but didn't find any. Thankfull for all your input.

It sounds like you are looking for something on an overview level rather than a practical level.  If you are interested in learning about Oracle databases from a practical level, there is no better place to start than the Oracle Concepts Guide.


Application with "configurable" columns : how to model this I'd like to do a status list. I have 1..n projects. Each project contains 0..n tasks, for which I'd like to report a status, next phase, priority, etc...
For each project, I'd like to setup which kind of column I want to use, for instance :

Project A : task ID, name, long description, next phase (date), ontime (boolean)
Project B : task ID, name, next phase (date), priority (limited set of values)

I also want to be able to configure the use of a field for a given project. In my example, I want to display next phase (a date) using a different format for instance. Some fields may be computed values (eg : late = true if today() > next phase()

I though of the following model :

one table for projects (with general metadata about the project)
one table with available column types
one link table between the project and the column types, with attributes on the configuration of the column (in my example, the date pattern)...(I'm no so happy about this part, as I would need to extend the table every time I add a new time with new configuration possibilities)
one task table, with a foreign key on projects, a foreign key on column types, and one column for the value

eg : the task on project B (12, "Write specifications", 12.12.12, "High") would be represented with 4 lines :
B, 1 (assume it's PK for ID column type), 12
B, 2 (assume it's PK for description column type), "Write specifications"
B, 3 (assume it's PK for date column type), 12.12.12
B, 4 (assume it's PK for priority column type), High

I'm not so convinced...do you have any suggestion ?
Maybe I should not model the column types in the DB ?

my biggest issue is to model the fact I have an undetermined number of attribute for a task in the project, and that 2 projects may have a different table size

What you're thinking about is either "row modelling" or "entity-attribute-value" (EAV) - See Wikipedia - either of which is a common subject for religious wars. I won't get too far into the pros vs. cons. If you read up on EAV and row modeling you'll see lots of discussion on that.

What I will say is that you should model all of the common predicates using properly normalized tables and rely on EAV (or row modelling) only for the customizable columns (if at all).  This is how many highly customizable packaged solutions deal with allowing users to add their own attributes to existing objects.  It isn't clean, easy or efficient, but it is probably the best compromise for allowing this particular type of customization when you have no way of reasonably predicting what the customized columns might be at design time.


Relation in 2NF with a transitive dependency? Tomorrow I have my final exam of Database Design but I have a doubt about the Second Normal Form (2NF).

The definition says a relation is in 2NF if no non-prime attribute is partially dependent on a key.

My doubt is about if a relation could be in 2NF is if has attributes that are fully dependent on a key but transitively.

For example: R (A,B,C,D) with AB key and FD ( AB->C,C->D)

In this case the non-prime attribute C is fully dependent on the key so it meets the condition but in the case of the attribute D I don´t know if it meets the rule. D is dependent on C and due to C is fully dependent on the key,does it mean that D is fully dependent on the key being this relation in 2NF?

The important thing about the 2NF is that in each (non trivial) dependency the determinant should not be a proper subset of a key. In the example, the determinant of AB->C is the full key, while the determinant of C->D is C, which is no part of any key. So the schema is obviously in 2NF.


Update a table with data from another table: truncated incorrect double value mysql I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. Nothing I have tried has worked. Any thoughts? I receive truncated incorrect double value mysql when I run the

Table1

  user_id|contact_email |contact_name
 =======================================
 123     |test@test.com |deft
 124     |test3@test.com|deft3

Table2 (User table)

 user_login|user_email     |display_name
 =======================================
 123       |test@test.com  |deft
 124       |test3@test.com |deft3

I have tried:

 update tbl1
 join   tbl2
 on     tbl1.user_id = tbl2.user_login
 set    tbl1.contact_email = tbl2.user_email;

And received truncated incorrect double value mysql

and when I try:

 update tbl1
 join   tbl2
 on     tbl1.user_id = tbl2.user_login
 set    tbl1.contact_email = tbl2.user_email;
 select * from tbl1;

I get 2 queries successfully executed. 0 record(s) were affected. There should be around 2000 records affected, so I know its not right.

This should do it.  Update your table inner join the second table based on ids.
Then we set the email from one table to the other where the ids are the same.  Hope it helps.

UPDATE tbl1
INNER JOIN tbl2 ON tbl2.user_login = tbl1.user_id
SET tbl1.contact_email = tbl2.user_email ;


How to get MSSQL Type definition from system tables? If I have a user table type (like CREATE TYPE ...), how do I get to it's definition in the system tables?

I know I can see it through SSMS through GUI, but I would like to have a query to retrieve it from system tables.

I don't see it through any of the modules:

SELECT
      *
FROM
      sys.all_sql_modules m
WHERE
      m.definition like 'CREATE TYPE %' --doesn't output any types definitions

You need to query sys.table_types:

SELECT
    SchemaName = SCHEMA_NAME(TYPE.schema_id),
    TypeName = TYPE.name,
    ColumnID = COL.column_id,
    [Column] = COL.name,
    DataType = ST.name,
    IsNullable = COL.Is_Nullable,
    Length = COL.max_length,
    Precision = COL.[precision],
    Scale = COL.scale,
    Collation = ST.collation
FROM
    sys.table_types TYPE
    INNER JOIN sys.columns COL ON TYPE.type_table_object_id = COL.object_id
    INNER JOIN sys.systypes AS ST ON ST.xtype = COL.system_type_id
WHERE
    TYPE.is_user_defined = 1
ORDER BY
    SchemaName,
    TypeName,
    ColumnID

Make sure you are connected to the proper database.

If you want a CREATE TYPE statement like the one from SSMS, you will have to build it from the results of the previous query. That or trace the SSMS's query to see how it builds it when you ask it from the GUI, but there's a chance it's very convoluted (although bulletproof).


Change compatibility Level on 80 to 100 We are about move all databases from a SQL server 2008r2 (Standard edition) to a 2016 standard edition server.

Many databases have its compatibility level set at 80, wich is not supported on the 2016 server. Our plan is to change all databases to level 100, run some tests, and only then, move the databases to the new server.

My questions are the following:

Should I expect differences in performance after the
compatibility level change?
What is the best way to measure the performance before and after?
Should I take special care when moving the databases the SQL
server 2016?

The major difference between sql server 2012 and below vs sql server 2014 and up is the new cardinality estimator (CE)

See my answer here as well.

My Story

When we migrated from sql server 2008R2 to 2014, we saw huge query regressions happening on our application. So we decided to enable TF 9481 but still have the latest compatiblity level.

Upgrading from sql server 2014 to 2016, I did not see any query regressions. So no Traceflag or older compatiblity mode required in sql server 2016.

In sql server 2016, there is database scope configuration option to govern the CE and optimizer fixes (equivalent of TF 4199)

  Should I expect differences in performance after the
  compatibility level change?

Its a big change for your application. SO test it properly. You can use Database Experimentation Assistant (New & in preview)

  What is the best way to measure the performance before and after?

You can use Query store (new in sql server 2016) to measure performance and find easily regressed queries interms of IO, CPU, etc.

  Should I take special care when moving the databases the SQL
  server 2016?

I have written lenghty answer for migrating from lower version of sql server to higher version of sql server.


How to change a script task version in SSIS? I added a script task to an SSIS project in VS2015. When I deployed to SQL Server 2016 I got an error message that the version 15.0 script is not supported.

Where does this version 15 come from? Reading other, similar questions on Stack Overflow I see that you can set the target version of the project to SQL Server 2012 - which I did (the eventual deployment target is SQL Server 2012).

I also tried deleting and recreating the script task. And in the information of the script it says it's using V10 of C#.

How can I resolve this?

  Script Task:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task ""ST_a1ad9dc5972c42b68c12a13155f10b6d"" uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.
         at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)"

I have also opened the project in SSDT 2012 and rebuilt with a different name. Same error. It seems that there must be a reference that wasn't deleted or something.

None of the solutions on this question (https://stackoverflow.com/questions/34893267/ssis-script-task-vs15-not-work-when-deploy-on-sql-server-2014) worked.

Looking at the XML in the package where the script is, I can easily find that task, and there is no reference to version 15 anywhere.

=========== EDIT

After copying the project to the machine that hosts the database, opening VS2015 and deploying from there, the package executes.

And then when going back to my machine and building there, it doesn't.

Is this a bug? Or am I doing something stupid by expecting the build to produce the same deployment wizard as using the wizard from VS...

I have SQL Server 2016 (13.0.4411.0), ssisdb has the schema version (13.0.1601.5).

I am using an integration services package created in visual studio 2015. The script component has the path: C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\VSTA14_IS_ST_CS_Template.vstax It won't let me execute the package through the integration services catalog (due to the message experienced by Zach). However it appears that it will let me execute it through the file system (using SQL Agent). Unsure if this is working, will update this once the package has completed.

I believe I have the same issue, and here is the work around that got over my issue.

Details first:

I have sql server 2016 (13.0.4411.0)
SSISDB has the schema version (13.0.1601.5)
I am using an integration services package created in visual studio
The script component (Which states it is 2015 C#) has the path:
C:\Program Files (x86)\Microsoft SQL
Server\130\DTS\Binn\VSTA14_IS_ST_CS_Template.vstax

If I deploy to the catalog through SSMS, it won't let me execute the package through the integration services catalog (due to the message experienced by Zach - version 15.0 not supported).

The workaround:

If I deploy the package through Visual Studio to the 'Integration Services Catalog' on the required instance, this message goes away and the project runs successfully. This is not ideal as we should be able to deploy through SSMS, but meant that the project can progress.


Why is SQL doing an index scan and seek with the same XML query structure? I've set up a test to benchmark xml performance in SQL server.
Test Setup

One Million rows of data
XML defined Column with primary key
A primary XML index
A secondary XML index on the path
XML data is similar in format but with variable tag names in each document

Table and Index Design
CREATE TABLE [dbo].[xml_Test]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [varchar](50) NULL,
    [JSON_Data] [varchar](max) NULL,
    [XML_Data] [xml] NULL,
    CONSTRAINT [PK_xml_Test] PRIMARY KEY CLUSTERED ([ID] ASC)
);

ALTER TABLE [dbo].[xml_Test] ADD  CONSTRAINT [DF_xml_Test_GUID]  DEFAULT (newid()) FOR [GUID];
ALTER TABLE [dbo].[xml_Test] ADD  CONSTRAINT [PK_xml_Test] PRIMARY KEY CLUSTERED ([ID] ASC);

CREATE PRIMARY XML INDEX [PK_xml] ON [dbo].[xml_Test]
(   [XML_Data]);

CREATE XML INDEX [IX_xml_Path] ON [dbo].[xml_Test]
(   [XML_Data])
USING XML INDEX [PK_xml] FOR PATH;

Sample XML Schema
<data>
  <id>3812</id>
  <guid>E3735046-1183-4A79-B8EE-806312B533D6",</guid>
  <firstName>John</firstName>
  <lastName>Doe</lastName>
  <tel>123-123-1234</tel>
  <city>Toronto</city>
  <prov>Ontario</prov>
  <Q.49.R.47>14325</Q.49.R.47>
  <Q.1>14326</Q.1>
  <Q.9>143257</Q.9>
  <Q.25>14328</Q.25>
  <Q.50>14329</Q.50>
  <Q.51>14330</Q.51>
  <Q.30>14331</Q.30>
  <Q.22>14332</Q.22>
  <Q.100>14333</Q.100>
  <Q.70.R.4>1</Q.70.R.4>
  <Q.43>14335</Q.43>
  <Q.3>14336</Q.3>
  <Q.84.R.21.L.19>1</Q.84.R.21.L.19>
  <done>1</done>
</data>

When I go to query the table with two different values, I get a very efficient seek for one result and a scan for the other that takes orders of magnitude longer to complete.
I'm unsure as to why the second query is not able to do a seek on the index.  I think it may have something to do with the element not being in the index to start with but even then I don't know why a scan would be necessary unless the index really didn't index all paths.
Execution Plans via Paste the Plan
Query A
select ID, 'Query A'
from xml_test
where XML_Data.exist('(/data/Q.70.R.4)[1]')=1

Query B
select id, 'Query B'
from xml_test
where XML_Data.exist('(/data/Q.61.R.15)[1]')=1

As you can see, Query A takes 5ms to execute compared to almost 15s for query B.
What are the reasons that the same query format but with different input would yield such an obviously inefficient execution plan?
Aside

I was also using this to test against JSON with identically formatted JSON data and my results show that the identical JSON query as XML Query A took 22,344 ms (22 seconds!) of cpu time (3,419 ms clock time) to run.
If the XML index can work reliably with a seek operation, it is clearly the more performant read option contrary to what I have researched and read online.
When querying over a large amount of data, I would not recommend JSON at all inside SQL server other than for blob storage or where you have a small dataset, which is to say, don't use it in production for searchable data that should be indexed.  There are other good services designed for that.

XML index in SQL Server is implemented as an internal table that is a persisted version of the node table that is much the same as the XML shredding functions produce.
One of the columns in the internal table is called hid and that column contains the value used in the seek for a path expression. The value is an ordpath value. When you create a path xml index you get a non clustered index with hid as the leading column.
The ordpath for a specific element name is generated when SQL Server inserts XML data to the table. To encode the path, the different ordpath values are concatenated.
Simplified the path X/Y/Z could be represented as 1.2.3 and Y/Z/Z as 2.3.3.
SQL Server has to have a way to generate the same ordpath value for the same element name. That could be done with some kind of dictionary or table that keeps track of all generated ordpath values for one XML index. I'm a bit fuzzy here because I have not found anything anywhere that says this is how it is done and I have not found where it is stored.
Inserting one row with this XML
<X>
  <Y>
    <Z />
  </Y>
</X>

will give you this content in the internal table with one extra column added by me to show the path expression encoded in hid
id      hid     pk1
0x              1
0x58    À€      1     Z
0x5AC0  Á€À€    1     Y/Z
0x5AD6  Â€Á€À€  1     X/Y/Z

You can look at the internal table using a DAC login.
What happens when you query for a path expression (and here I get a bit speculative again) is that the same dictionary/lookup table that is used to make sure the elements with the same name get the same ordpath value is used to find the hid to use for the index seek in the internal table. The path expression in a XQuery is always static so the hid value used for the query can be stored in the query plan and resused.
A query for a node that exists get a query with a seek on the hid where Î is the hid value to search for.
<SeekKeys>
  <Prefix ScanType="EQ">
    <RangeColumns>
      <ColumnReference Database="[yy]" Schema="[sys]" Table="[xml_index_nodes_658101385_256000]"
                       Alias="[NodeThatExist:1]" Column="hid" />
    </RangeColumns>
    <RangeExpressions>
      <ScalarOperator ScalarString="'Î'">
        <Const ConstValue="'Î'" />
      </ScalarOperator>
    </RangeExpressions>
  </Prefix>
</SeekKeys>

A query for a node that does not exist gives you a very different way of locating the rows.
select count(*)
from dbo.T
where T.X.exist('NodeDoesNotExist') = 1

The compute scalar uses some functions to calculate values that are checked against in the filter operator.

Some of the tests I did to get here can be found here. Note that you don't actually need a generated hid for a element in the index to get the seek plan, you just have to try to insert it.
As a side note, the rewriting of the query to use one way or the other to find the rows of a XML query is done before the query optimizer starts to do its job so if you get a seek or not also depends on cardinality on the hid column in the internal table.

Paul White mentioned something in chat about where the element names inserted are stored:

QNames are stored in sys.sysqnames. The XML algebrizer requires an
existing known QName to generate an optimized path transform. i.e. to
use a path index. Entries are added to sys.sysqnames in a system
transaction that does not feel user transactions


Regarding Always On Licensing (Secondary replica no license required) I have few questions regarding the licensing of SQL Server when it comes to Always On:

Under which scenario / settings we can say that secondary replica is truly passive and will not be considered for licensing:

"Readable Secondary" AG setting for all the replicas associated to Always On(including current Primary replica) should have setting either as “NO” or “Read-Intent Only” or it should be "NO" only?
There should not be any Standalone Database? (not even any third party backup tool database such as "LiteSpeedLocal").
Also the backup should not be configured to run on Secondary replica. Otherwise, the Secondary replica will not be truly passive. Is it true?

Any other things which we need to consider / look for in order to be 100% sure that the secondary replica will not require license?

"Readable Secondary" AG setting for all the replicas associated to
  Always On(including current Primary replica) should have setting
  either as “NO” or “Read-Intent Only” or it should be "NO" only?
  There should not be any Standalone Database? (not even any third party
  backup tool database such as "LiteSpeedLocal").
  Also the backup should not be configured to run on Secondary replica. Otherwise, the Secondary replica will not be truly passive. Is it true?

Must be set to "No" so that it is not readable. From the licensing data sheet:

  The secondary replica used for failover support does not need to be
  separately licensed for SQL Server as long as it is set to ‘not
  readable’. If it is readable or serving data, such as reports to
  clients running active SQL Server workloads, or performing any “work”
  such as additional backups from secondary servers, then it must be
  separately licensed for SQL Server.

A standalone database doesn't violate the passive license unless it is used for something, anything. No backups, no queries, no integrity checks, index or statistics maintenance. All of it would constitute production use and violate the terms. Easier to not have any standalone databases.
If you run backups against the secondary it will violate the passive licence terms and require a paid licence.

Further information can be found in the licensing guide and licensing data sheet. Note that this information is for SQL Server 2017. It hasn't varied too much since 2012, but best to validate with your specific version and run any concerns past your licensing reseller for official confirmation.


SQL Server ReadOnly Routing Connection String Can we route read-only connection to readable secondary replica without setting connection string (ApplicationIntent = ReadOnly)?

You would be required to set the Secondary Replica Copy as Readable, different from Read-Intent, and point the connection string to the actual instance where the replica resides.

This is not advisable, as it negates the HA usability of any Read-Intent replicas during failover.


Updating linked server with temp table join I am performing the following operation:

Update A set A.col1 = B.col1
From LinkedServer.LinkedDatabase.dbo.remoteTable A
JOIN #localTempTable B on B.column = A.primaryKeyColumn

The performance is terrible and the query takes what seems to be forever,
given that i can't just create a table to store the temp table data in, what can I do in SQL Server 2014 to improve this? I am considering running the query from the linked server instead.

current query

Update linked
   set linked.col1 = local.col1
  From LinkedServer.LinkedDatabase.dbo.remoteTable linked
  JOIN #localTempTable local
         on local.column = linked.primaryKeyColumn

It is typically better to perform the write operation locally
Here you are both reading from and writing to the linked server

My recommendation is if you are going to create #localTempTable then do so on LinkedServer.  Have a session on the LinkedServer and link to local to populate #localTempTable but #localTempTable is now on LinkedServer.  Flop the link.

insert into #linkedLocalTempTable
select ...
from Local.LinkedDatabase...

Please post the query to populate #localTempTable
If you use that table for other stuff then flopping stuff around may not be and option


Problem with BETWEEN and SUM I have a query below.

The SUM should be the SIX_AGO + FIVE_AGO + FOUR_AGO + etc.

But the SUM give all the times wrong numbers.

Is there anything I am doing wrong?

 SELECT
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS SUM,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 36 - diff) then 1 END) AS SIX_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 35  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 29 -  diff) then 1 END) AS FIVE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 28  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 22 -  diff) then 1 END) AS FOUR_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 21  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 15 -  diff) then 1 END) AS THREE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 14  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 8  -  diff) then 1 END) AS TWO_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 7   -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 1  -  diff) then 1 END) AS ONE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') -        diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS NO_AGO
FROM TEST

The question does not contain test data, and some details are not quite clear eg we don't know what the "diff" in the original query stands for.  Suppose we have the following test table (assuming that diff is just a constant value):

-- Oracle 12c
create table test
as
select
  sysdate - level as my_date_col
, 1 as diff
from dual
connect by level <= 400 ;

SQL> select * from test fetch first 10 rows only ;
MY_DATE_COL  DIFF
04-JUN-18    1
03-JUN-18    1
02-JUN-18    1
01-JUN-18    1
31-MAY-18    1
30-MAY-18    1
29-MAY-18    1
28-MAY-18    1
27-MAY-18    1
26-MAY-18    1

Original query

The counts for the "sections" (SIX_AGO, FIVE_AGO etc) do not add up to the expected "SUM".

 SELECT
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS SUM,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 36 - diff) then 1 END) AS SIX_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 35  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 29 -  diff) then 1 END) AS FIVE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 28  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 22 -  diff) then 1 END) AS FOUR_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 21  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 15 -  diff) then 1 END) AS THREE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 14  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 8  -  diff) then 1 END) AS TWO_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 7   -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 1  -  diff) then 1 END) AS ONE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') -        diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS NO_AGO
FROM TEST ;

-- result
SUM  SIX_AGO  FIVE_AGO  FOUR_AGO  THREE_AGO  TWO_AGO  ONE_AGO  NO_AGO
367  329      6         6         6          6        6        2

The problem seems to be that the nested functions used in BETWEEN ... AND ... lead to incorrect counts.  Thus, I suggest that we rewrite the query in a way that allows us to use integer values for the between conditions. Something like ...

select
  count( case when trunc( my_date_col - sysdate ) between -7   and -1  then 1 end ) section1
, count( case when trunc( my_date_col - sysdate ) between -14  and -8  then 1 end ) section2
, count( case when trunc( my_date_col - sysdate ) between -21  and -15 then 1 end ) section3
, count( case when trunc( my_date_col - sysdate ) between -28  and -22 then 1 end ) section4
, count( case when trunc( my_date_col - sysdate ) between -35  and -29 then 1 end ) section5
, count( case when trunc( my_date_col - sysdate ) between -365 and -36 then 1 end ) section6
, count( case when trunc( my_date_col - sysdate ) between -365 and -1  then 1 end ) overall
from test ;

-- result
SECTION1  SECTION2  SECTION3  SECTION4  SECTION5  SECTION6  OVERALL
7         7         7         7         7         330       365


Database Log Reuse Wait shows AVAILABILITY_REPLICA but database is no longer part of an availability group My database log is growing out of control, I recently removed the database from an availability group by when I look at the log reuse wait description it shows "AVAILABILITY_REPLICA"

I have tried to set the database to simple mode and then shrink the log file, I have tried full backups, log backups (in full mode after a full backup). But the status will not change and I cannot shrink the log file.

Any suggestions?

Take your database offline then back online. This should clear the AVAILABILITY_REPLICA wait, then you can shrink whatever you need to shrink.


Percona: Backup preparation doesn't work ("cannot open ./xtrabackup_logfile") I've created a MySQL backup with PerconaXtrabackup tool via (thanks to this guide):

innobackupex --user=backupmysqluser --password=xyz --compress --stream=xbstream ./ > /var/backups/db/firstBackup.db

It succeeds creating the hot backup. Now I want to test the restoration of it, so then, first, I decompress it:

cd /var/backups/db
mkdir decompressed
xbstream -x -C decompressed

Now I try to prepare it:

innobackupex --apply-log /var/backups/db/decompressed/

And the result is this error:

160719 20:06:28 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.3 based on MySQL server 5.7.11 Linux (x86_64) (revision id: 6a46905)
xtrabackup: cd to /var/backups/db/decompressed
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
  xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.
xtrabackup: Error: xtrabackup_init_temp_log() failed.

It's strange because in the decompressed folder there's no xtrabackup_logfile, but there is a file named xtrabackup_logfile.qp instead.

What did I do wrong?

You have not decompressed the Xtrabackup files if they still have the .qp extension.

Try using this inside the current folder:

sudo innobackeupex --decompress --parallel=4

where 4 stands for the number of threads you would like to use.

Also, for clean-up afterwards:

sudo find ${DEST_DIR}/ -name "*.qp" -delete


How can I insert without changing the identity seed? I will add the records in table A to table B. The tables are the same. However, table B is an actively transactional table. When doing the operation, the record in the tableB will continue to be added row.

tableA
Id
1
2
...
1000000
1500000000
1500000001
1500000002
1500000003

tableB

Id
1000001
1000002
...
80768928

Some of the new records I will add are higher than identity seed, the seed will update to 1500000004. Is there a way to do this without updating this value?

  tableB Identity Seed : 80768928

I use these commands when doing the operation:

SET IDENTITY_INSERT tableB ON

INSERT INTO dbo.tableB (...Columns...)
SELECT ...Columns... FROM dbo.tableA

SET IDENTITY_INSERT tableB OFf

You could put the old seed into a variable and then reseed the table after the insert.

DECLARE @Seed INT = IDENT_CURRENT( 'tableB' )

SET IDENTITY_INSERT tableB ON

INSERT INTO dbo.tableB (...Columns...)
SELECT ...Columns... FROM dbo.tableA

SET IDENTITY_INSERT tableB OFF

DBCC CHECKIDENT ('tableB', RESEED, @Seed)

This is assuming that no other inserts are going to take place while you do your insert.


Partition Function with only one value, what does this effect? I am currently looking at a database that has been setup by the default installation routine of AppFabric. Looking at the creation scripts I encountered this partition function:

CREATE PARTITION FUNCTION [MSAppServerPF_ASWcfEventsTable](datetime2(7)) AS
RANGE RIGHT FOR VALUES (N'0001-01-01T00:00:00.000')
GO

And it is being applied to a table that way:

CREATE PARTITION SCHEME [MSAppServerPS_ASWcfEventsTable] AS
PARTITION [MSAppServerPF_ASWcfEventsTable] TO ([PRIMARY], [PRIMARY])
GO

I wonder what this specific partition style effects in. I was under the impression that you store boundary values into the partition function by which the data will be partitioned.
Unfortunately, looking for this on the web I could not find a similar example using just one DATETIME2 value in the partition function definition.

Can someone shed some light on this for me?

You are correct in your thinking of the boundary values.  Basically what your partition function dictates, is that anything greater than or equal to (range right) 0001-01-01T00:00:00.000 will be on the second partition, and anything less than that will be on the first partition.

The peculiar thing about the partition function is that there will never be any value less than 0001-01-01T00:00:00.000, due to the datetime2 date range.  As far as I can see, this would mean that the first partition will never have data, and the second partition will have all of the data (provided there is no split going forward).

One thing this strategy could be used for is partition switching and sliding window operations.  For more information on exactly what that is you can see it on this blog article.  But as for this isolated partitioning structure as a standalone solution, I'm not convinced that there's anything gained here.


Failed to start MySQL Community Server. (start-limit-hit) Since i made an update last Friday (2019-01-25), on "Linux Mint 18.3 Sylvia", the mysql-server has broken.

My first solution approach was as following:

sudo apt purge mysql*
sudo apt autoremove
sudo update-grub
sudo apt update
sudo apt dist-upgrade
sudo apt install mysql-server
sudo ufw allow mysql

And I get the following error messages:

user@computer ~ $ systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: start-limit-hit) since Mon 2019-01-28 10:29:06 CET; 4min 56s ago
  Process: 21680 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)

Jan 28 10:29:06 computer systemd[1]: Failed to start MySQL Community Server.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Unit entered failed state.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Failed with result 'exit-code'.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jan 28 10:29:06 computer systemd[1]: Stopped MySQL Community Server.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Start request repeated too quickly.
Jan 28 10:29:06 computer systemd[1]: Failed to start MySQL Community Server.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Unit entered failed state.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Failed with result 'start-limit-hit'.

user@computer ~ $ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

According to this this side there should exist the following 3 config files after installing mysql-server:

ls /etc/my.cnf             # missing
ls /etc/mysql/my.cnf       # exists
ls /var/lib/mysql/my.cnf   # missing

However only one of them exits in my file-system and because i was curious about them, i looked on my notebook and there also only the second file exists and mysql works fine.

I am currently a bit stuck because the sites i found by doing research were all very specific and none of their solution approaches did either work or was not appropriate, because of similar error messages but in a different context.

Any help is appreciated, thanks!

EDIT:

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

$ ls /var/log/mysql/
error.log       error.log.2.gz  error.log.4.gz  error.log.6.gz
error.log.1.gz  error.log.3.gz  error.log.5.gz

$ systemctl show mysql.service
Type=simple
Restart=on-failure
NotifyAccess=none
RestartUSec=100ms
TimeoutStartUSec=10min
TimeoutStopUSec=10min
RuntimeMaxUSec=infinity
WatchdogUSec=0
WatchdogTimestampMonotonic=0
FailureAction=none
PermissionsStartOnly=yes
RootDirectoryStartOnly=no
RemainAfterExit=no
GuessMainPID=yes
MainPID=0
ControlPID=0
FileDescriptorStoreMax=0
NFileDescriptorStore=0
StatusErrno=0
Result=start-limit-hit
ExecMainStartTimestampMonotonic=0
ExecMainExitTimestampMonotonic=0
ExecMainPID=0
ExecMainCode=0
ExecMainStatus=0
ExecStartPre={ path=/usr/share/mysql/mysql-systemd-start ; argv[]=/usr/share/mysql/mysql-systemd-start pre ; ignore_errors=no
ExecStart={ path=/usr/sbin/mysqld ; argv[]=/usr/sbin/mysqld ; ignore_errors=no ; start_time=[n/a] ; stop_time=[n/a] ; pid=0 ;
ExecStartPost={ path=/usr/share/mysql/mysql-systemd-start ; argv[]=/usr/share/mysql/mysql-systemd-start post ; ignore_errors=
Slice=system.slice
MemoryCurrent=18446744073709551615
CPUUsageNSec=18446744073709551615
TasksCurrent=18446744073709551615
Delegate=no
CPUAccounting=no

I got after running
sudo mysqld --initialize

  [ERROR] --initialize specified but the data directory has files in it. Aborting.

for me there was a single file in /var/lib/mysql/ that you could just remove

then I got further, but another error stopped me about apparmor where I followed this, which got me further still but am still stuck:

>

  Resolution
  Add permissions for the objects reported in the lines that start with 'name=' in the output to the file /etc/apparmor.d/usr.sbin.mysqld.

  In this example, it is required to add r permissions to /proc/*/status and /sys/devices/system/node/. The paths may be different depending on the error messages.

  Connect the server via SSH.
  Open the file /etc/apparmor.d/usr.sbin.mysqld in a text editor (for example, vi editor) and add the lines below at the end of the /usr/sbin/mysqld section at the end of the file:

/usr/sbin/mysqld {
...
/proc/*/status r,
/sys/devices/system/node/ r,
/sys/devices/system/node/node*/meminfo r,
/sys/devices/system/node/*/* r,
/sys/devices/system/node/* r,
...
}

  Reload AppArmor configuration for MySQL service:

  apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

  Start MySQL server:

  service mysql start

  Note: In case MySQL fails to start again, check the output of journalctl -xe for new error messages.


SQL Server 2012 Execution Plan I was reading through Kendra Little's post on www.brentozar.com

http://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/

In the post, Kendra mentioned that she is using SQL Server 2012. Kendra has displayed a execution plan mouse over pop up as shown below, with more information like Memory Grants.
.

I am also using SQL Server 2012 (Microsoft SQL Server 2012 - 11.0.5058.0 (X64)) .
But I am getting a different information as below.

Anybody have any idea what am my missing ?

I believe Kendra is using a front end application called SQL Sentry Plan Explorer.  It's a really great tool and gives a little bit more verbose info about the execution plan.  Here's an example of a query I'm working on right now with Plan Explorer:

It's also free-ninety-nine! (the paid version gives you some more bells and whistles but the free version is fantastic for basic execution plan review).  It's one of my favorite tools in my DBA toolbelt, for sure.

Link to product: http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view


Always On Failover Clustering vs Always On Availability Groups I have some concerns over my understanding of SQL Server Always On. Kindly can you correct me (conceptually) where needed:

Always On Clustering and Always On Availability Groups are 2 separate concepts. Clustering is a HA solution and AG is a DR solution. Is the Always On Clustering same as Windows server clustering?
To create an Always On cluster - we have to launch the installer and choose the "New SQL Server failover cluster installation", then on every new node we need to launch the installer and choose "Add Node". The cluster (with many node) operates as a single unit in terms of IP address so applications point to only one IP and if there is instance crash, then the failover is handled by the Clustering technology and no application IP changes are needed. Also, the users/logins are in sync and will continue to work in case of failover. This does not protect against disk failure as all nodes share same disk.
Assuming we have an SQL Server instance already (say server A), then to create Always On Clustering for this we need to follow the same steps as above (create a new failover cluster installation) and then add Server A as a node. Right?
Assuming we have a fresh SQL Server instance (with no HA/DR) and are planning to configure only AG, then we first need to ensure that the "Failover clustering" windows feature is enabled on each participating node. Then right click on the SQL Server service and enable "Always on Availability Groups". Then on the server instance we create the AG and configure databases into the group(s). This ensures availability at database level, however if there is failover then logins wont work any more (orphaned). There is no shared disk and hence disk crash of primary server wont cause any issues for the secondary server database. Also, the client applications will be pointing to the Listener IP and the Listener will ensure that the appropriate working server is utilized by the applications. Right?
In above scenario, "Failover clustering" windows feature is enabled so is clustering a pre-requisite for AG? Is the concept of clustering in Point 2 and Point 4 the same.
If I wish to configure both HA/DR using Always On (AG) and Failover Clustering, then is best practice to follow Point 2 and then point 4 or the opposite way? Also, should we use both the virtual cluster name and Listener or either one if sufficient?
What is difference between "SQL Server failover cluster installation" and Windows clustering?

To start with there is nothing called as Always On or Always ON availability groups it is simply called as Availability groups.

  Always On Clustering and Always On Availability Groups are 2 separate concepts. Clustering is a HA solution and AG is a DR solution. Is the Always On Clustering same as Windows server clustering?

Yes you are correct. Always on clustering means you have created availability groups on database(s) which is part of failover cluster instance (FCI) and availability groups simply means two standalone nodes which are part of WSFC or not are configured to form AG, there is no FCI here.

  To create an Always On cluster - we have to launch the installer and choose the "New SQL server failover cluster installation", then on every new node we need to launch the installer and choose "Add Node"

That is basically creating an SQL Server failover cluster instance. Yes after you have created an FCI you can go ahead and add the databases residing on FCI to an AG. This is what you are calling as Always On cluster and this is what official MS doc calls it.

For point 3 please refer to above two quotes. Again note the difference between AG and AG residing on FCI.

  Assuming we have a fresh SQL instance (with no HA/DR) and are planning to configure only AG, then we first need to ensure that the "Failover clustering" windows feature is enabled on each participating node.

If you are planning to configure ONLY AG you do not need failover cluster instance. AG can be configured with 2 or more nodes which are part of WSFC or not (starting from SQL Server 2017 we have clusterless AG). Remember an FCI will always require some kind of shared storage, OTOH AG does not requires any kind of shared storage.

  In above scenario, "Failover clustering" windows feature is enabled so is clustering a pre-requisite for AG? Is the concept of clustering in Point 2 and Point 4 the same.

WSFC is prerequisite for AG but starting from SQL Server 2017 you can have AG without WSFC but that is not a real AG. WSFC is prerequisite for FCI though.

EDIT:

  If I wish to configure both HA/DR using Always On (AG) and Failover Clustering, then is best practice to follow Point 2 and then point 4 or the opposite way? Also, should we use both the virtual cluster name and Listener or either one if sufficient?

You need to first configure WSFC then install FCI like you mentioned in point 2 and point 4 then create database and then configure availability groups. Virtual cluster name is for FCI while Listener is for AG. You cannot configure cluster without virtual cluster name you have to have one, but you can have AG without listener. The only work of listener is to direct connections to primary replica which can be on any of the node.

  What is difference between "SQL server failover cluster installation" and windows clustering?

Windows server failover cluster(WSFC) is windows server feature on which whole "clustering" technology is based while FCI is at SQL Server level which leverages WSFC for failovers to provide AH to SQL Server instance.


utl_raw.bit_and attaches extra 0 after and operation in oracle sql For a given binary number, say length longer than 128, I want to check whether a particular bit is on or off (say 30th bit) I am using utl_raw.bit_and function to do and between 2 binary numbers. I expect the input binary number to be longer than 128 and I can't use oracle bitand function then. When I do a bit_and, I see extra 0 appended to the result. I am not sure how it got in there when the length of both the input are the same. Here is my sample sql

SELECT
A,
LENGTH(A) LENGTH_A,
B,
LENGTH(B) LENGTH_B,
UTL_RAW.BIT_AND(TRIM(A),TRIM(B)) AND_AB,
LENGTH(UTL_RAW.BIT_AND(TRIM(A), TRIM(B))) LENGTH_AND_AB FROM (
SELECT
    TRIM(RPAD('0', LENGTH('1111111111111100000000000000011') - 30    ,'0')) || '1' ||TRIM(RPAD('0', 29, '0')) A,
'1111111111111100000000000000011' B FROM DUAL);

This is the result of the operation

A                                 LENGTH_A  B                               LENGTH_B  AND_AB                           LENGTH_AND_B
0100000000000000000000000000000   31        1111111111111100000000000000011 31        00100000000000000000000000000000 32

That is not handled as binary, but hexadecimal.

select
          length( '0100000000000000000000000000000') as l_a_in_chars,
  utl_raw.length( '0100000000000000000000000000000') as l_a_in_bytes,
          length( '1111111111111100000000000000011') as l_b_in_chars,
  utl_raw.length( '1111111111111100000000000000011') as l_b_n_bytes,
          length('00100000000000000000000000000000') as l_ab_in_chars,
  utl_raw.length('00100000000000000000000000000000') as l_ab_in_bytes
from dual;

L_A_IN_CHARS L_A_IN_BYTES L_B_IN_CHARS L_B_N_BYTES L_AB_IN_CHARS L_AB_IN_BYTES
------------ ------------ ------------ ----------- ------------- -------------
          31           16           31          16            32            16

It is not the length in characters that matters, but the length in bytes, and that was the same the whole time. You can not have hexadecimal with odd number of characters. It is not the output that has an extra 0 attached, but your input, on the automatic conversion from hex to raw:

select hextoraw('0100000000000000000000000000000') from dual;

HEXTORAW('0100000000000000000000
--------------------------------
00100000000000000000000000000000

Your input does not fit into RAW(15), requires RAW(16) where the required 0-s are added as required, and there is no such thing as RAW(15.5):

SQL> select cast ('0100000000000000000000000000000' as raw(16)) from dual;

CAST('01000000000000000000000000
--------------------------------
00100000000000000000000000000000

SQL> select cast ('0100000000000000000000000000000' as raw(15)) from dual;
select cast ('0100000000000000000000000000000' as raw(15)) from dual
             *
ERROR at line 1:
ORA-25137: Data value out of range

Since you seem to store your numbers as strings, you can quite easily check the position of 1 "bit":

SQL> select substr('1111111111111100000000000000011', -30, 1) from dual;

S
-
1


Corruption in mariadb binlog I have an installation with mariadb version 10.0.21.

I need to backup the binlog file every 5 minutes to TSM, but as the binlog is being written every second, i am afraid of the file getting corrupted when copied.
Is it possible that this happens?
If the binlog gets corrupted (the end of it), will it corrupt the entire file or i can discard the last lines?
Any other solution proposed? We already have replication with master/slave, but for security and audit reasons, we need to keep a copy of the binlog.

Regards

If You work under linux, You can use Persona Backup - https://www.percona.com/software/mysql-database/percona-xtrabackup for HOT bin-log backup.


What differences are there with SQL Server 2017's packaging of `Binn` on Linux and Windows? Knowing that the Binn is packaged differently on Windows and Linux, I'm wondering what differences there are between the two? Do they come with different utilities? On Linux Binn is distributed in a sfp archive and Windows it's just a directory.

All tests done with a full install on Windows (except Machine Learning, and PolyBase) and a full install on Linux. So for starters on Windows you'll find these,

112K fdhost.exe
244K sqlceip.exe
2.7M DQSInstaller.exe
 32K DatabaseMail.exe
 40K BackupToUrl.exe
476K sqlservr.exe
 56K StretchCodeGen.exe
 60K fdlauncher.exe
 88K xpadsi.exe

While on Linux you'll find

1.1M launchpad.exe
120K bcp.exe
120K reg.exe
140K SqlDumper.exe
 16K calchash.exe
200K mtlogreader.exe
208K rep.exe
232K DTAEngine.exe
232K ldfreader.exe
244K sqlceip.exe
244K sqlcmd.exe
 28K rgs.exe
 32K DatabaseMail.exe
 36K PalProcessLauncher.exe
476K sqlservr.exe
 48K useraccounttool.exe
 72K XdbPackageLauncher.exe
 76K OSQL.exe

A quick overview,

sqlservr.exe is the actual database
sqlceip.exe is the spyware -- both installs get that of course

  Sql Server Telemetry Client

calchash.exe this is Microsofts copy of sha1sum.exe takes a filename.

  Microsoft Trust Calculate hash utility (US/Canada Only, Not for Export)

OSQL.exe was deprecated and removed on the Windows Install
bcp.exe is the Bulk Copy Program but only the Linux version has -G Azure Active Directory Authentication
rep.exe - think grep on XML.

  Rep  -  a fast, file-oriented search and replace program.

DTAEngine.exe seems to be a component of SMSS that is available on Linux without using SMSS.
reg.exe and rgs.exe both seem to be some kind of registry modifier. I assume reg.exe is the system, and rgs.exe is the hive file.
Though pe32+ XdbPackageLauncher.exe just crashes when I run them in Windows 10.
PalProcessLauncher.exe, and launchpad.exe are likely specific to the Drawbridge abstraction.
ldfreader.exe seems interesting and potentially useful,

LDF Reader version 14.0.3008.27 - Dump Microsoft SQL Server Transaction Log

Usage: \\VBOXSVR\mssql\lib\sqlservr\Content\binn\ldfreader.exe <file> [options]

Options:

        -minVlf <int>           Min VLF to dump. Skips all prior VLFs. (short form: -m)
        -maxVlf <int>           Max VLF to dump. Skips all following VLFs. (short form: -M)
        -verbosity <int>        Verbosity level. Default is 1. (short form: -v)
        -summary                Summary mode. Only prints overall structure of LDF without individual records. (short form: -s)
        -lopFilter <string>     Dump only records with these LOP. (short form: -l)
        -force                  Force dumping in presence of corruption. (short form: -f)
        -dumpPageLogs           Dump Page Log Records? (short form: -p)
        -instructionFile        Treat file as instruction file. (short form: -i)
        -targetruntime <int>    The target run time in seconds (see below). (short form: -t)
        -certInfo <string>      Certificate information file for encrypted logs. (short form: -c)
        -vlfSize <int>          Fixed VLF Size in MB for Socrates Log (short form: -z)

<file> has a different meaning depending on whether -instructionFile is specified
if -instructionFile is specified, <file> is a file containing vlf information
extracted at dump time, containing fileID;offset;size followed by a
separator line and then mappings: fileID;full log file path

The -t parameter is an indication of how long to run the tool
when used without -i it is treated as a hard timeout on log iteration
when used with -i it is used as a guide for hueristics, a hard timeout of
five times the requested value will be used

launchpad.exe

Invalid arguments. The supported arguments are:
        -launcher:          Define the launcher dll's full path.
        -logPath:           Define the launchpad's base log path.
        -satelliteDllPath:  Define the sql satellite dll path for the satellites.
        -workingDir:        Define the launchpad and satellite process base working directory.
        -cleanupLog:        Whether to cleanup the log directory after every execution [0|1].
        -cleanupWorkingDir: Whether to cleanup the working directory after every execution [0|1].
        -pipeName:          Define the launchpad's name pipe's name.
        -timeout:           Define the default timeout in ms.
        -SqlInstanceName:   Define the SqlInstanceName as in MSSQLSERVER or blank for default or an instance name.
        -logFilesCount -    Tells the max number of launchpad error log file to keep across launchpad restarts.
Example:
Launchpad.exe  -launcher commonlauncher.dll -logPath C:\sqlServerBinaries\log -satelliteDllPath C:\sqlServerBinaries\binn\sqlsatellite.dll      -workingDir C:\sqlServerBinaries\data\ExtensibilityData -cleanupLog 0 -cleanupWorkingDir 0 -pipeName sqlsatellitelaunch -timeout 600000 -SqlInstanceName MSSQLSERVER

mtlogreader.exe, some utility for Hekaton logs

MtLog Reader - Dump Hekaton MtLog files

Usage: \\VBOXSVR\mssql\lib\sqlservr\Content\binn\mtlogreader.exe <file> [options]

Options:

        -verbose <int>                 Verbosity (0-4) Default 1. (short form: -v)
        -buffio <bool>                 Buffer stdout (true/false) Default true (short form: -b)
        -keySecret1 <string>           The first secret key blob used for crypto keys (short form: -k1)
        -keySecret2 <string>           The Second secret key blob used for crypto keys (short form: -k2)
        -instructionFile               Treat file as instructionfile. (short form: -i)
        -targetruntimeinseconds <int>  The target run time in seconds (only valid with -i). (short form: -t)
        -skipchksum                    Skip checksum validation. (short form: -sc)
        -maxbsn <uint64>               Max BSN to dump. Default 0 (dump whole file). (short form: -m)

<file> has a different meaning depending on whether -instructionFile is specified
if -instructionFile is specified, <file> is a file containing checkpoint file
information extracted at dump time, containing the root file name followed by a
list of container directories (one per line).
 the -t parameter is an indication of how long to run the tool
when used with -i it is used to limit the processing of checkpoint files

useraccounttool.exe, I believe it's the use R account tool, as in the statistical language for Machine Learning that Microsoft brought

useraccounttool <command> [options]
Available Commands:
  /install                - Create the credentials required for R on SQL Server.
  /uninstall              - Cleanup the credentials required for R on SQL Server
Available Options:
  /instance:<name>        - [Optional (default: MSSQLSERVER)] The instance to use
  /sqlbinnpath:<path>     - [Required] The path to the sql binn directory.
  /userpoolsize:<size>    - [Optional (default: 20)] The number of user accounts to create.
[useraccounttool] <12/15/2017 5:23:26 PM> #3: Program exited with exit code 1


Better way to select a matching subset of rows? DB Fiddle link. I have a many to many table like this:

CREATE TABLE house_to_cats (
  id SERIAL PRIMARY KEY,
  house_id INTEGER,
  cat_id INTEGER
);

-- house with cats 1 and 2: too small
INSERT INTO house_to_cats (house_id, cat_id) VALUES (1, 1), (1, 2);
-- house with cats 1 2 3 4: too big
INSERT INTO house_to_cats (house_id, cat_id) VALUES (2, 1), (2, 2), (2, 3), (2, 4);
-- house with cats 1 2 3: just right
INSERT INTO house_to_cats (house_id, cat_id) VALUES (3, 1), (3, 2), (3, 3);

I need a query that takes an arbitrary list of cats and returns a matching house if exists. I came up with this:

SELECT
    house_id
FROM (
    SELECT
          house_id
        , ARRAY_AGG(cat_id) as cat_id_agg
    FROM house_to_cats
    JOIN (
        SELECT DISTINCT
              house_id
        FROM house_to_cats
        JOIN (SELECT * FROM UNNEST(ARRAY[1, 2, 3]) cat_id) inn USING (cat_id)
    ) filter USING (house_id)
    GROUP BY house_id
) agg
WHERE cat_id_agg <@ ARRAY[1, 2, 3]
  AND cat_id_agg @> ARRAY[1, 2, 3];

Is there a better way to do this?

The idea behind my query: in filter, get the house_id which have at least one of our cat in them. In agg, create cat_id_agg arrays for all of those house_ids. And in the outermost query filter out groups that don't match our set.

If I understood you correctly, your query can be simplified to:

select house_id
from house_to_cats
group by house_id
having array_agg(cat_id order by cat_id) = array[1,2,3]

Note the order by in the array_agg() call - the array [3,2,1] is not equal the array [1,2,3]. To avoid incorrect results due to the aggregation being done in a different order, the aggregated array has to contain the values in the same order as the comparison value.

Online example: https://rextester.com/IKGHWL59301


Why is SQL Server 2008 R2 Management Studio making me login again? I login to SQL Server 2008 management studio each morning, it works all day just fine. When I come in the next morning and try to run a query, it asks me to login again.

Where is the setting defined which controls this "login timeout"?

Possible simple reasons:

Connection is broken between your PC and SQL Server (for example some Switch can be powered-down, re-started etc)
Connection can be closed from SQL-Server side if your admins make some kind of maintenance and simply kill connections, or if your Server is running in Virtualized envronment, it's instance could be "paused" to make a snapshot.

list of reasons can be really big

you can start analysing using simple tool: when you go home, just run:

ping -t NAMEOFYOURSERVER >c:\temp\log.txt

this will start permanent ping and when you will come next day - just Ctrl-Break and look into the file if there are Time-Outs. This way you will know if at least IP connection was alive.


comparing left join and outer apply doing the same thing I have an update trigger on a table that that insert rows in another database/table

the query look like

insert into otherdb.table
select manyfield
from inserted
  left join (select id,
                    timestamp,
                    row_number() over (partition by id order by timestamp desc) as rownum
             from sometable) as t1
      on inserted.id = t1.id and
         rownum = 1

sometable have over 7 millions rows and have proper indexing

while doing execution plan i can see that if there is 1 updated row, that windowing fuction (proper name used?) retrieve the whole table do to the rows numbering and get executed once, it is using index scan

same(it will retrieve once the 7 millions rows) if inserted have 20 rows or inserted have 50,000 rows

now the usage of that trigger is mostly 1 row update (we do have some odd case where it will be 20-100 rows and even thousands)

I refactored this query to look like

insert into otherdb.table
select manyfield
from inserted
  outer apply join (select top 1 id,
                    timestamp
                    from sometable
                    where inserted.id = sometable.id
                    order by timestamp desc) as t1

same result at the end but the execution plan change.

now it is using index seek and it will return 1 row per inserted row

when i do a huge update of 50,000 rows, the execution plan will tell me number of execution 50,000 instead of one when doing the left join version.

to resume everything;

left join: 1 big operation being reused by all inserted row

outer apply: 1 small operation being executed once per inserted row

my question is, at this point i don't know enough about execution plan to decide which kind of join i should keep, left join or outer apply?

this is being used by 500-1000 users at the same time and we have timeout error maybe related to this trigger, we currently use the left join query

EDIT

result of some actual execution plan, select on 55017 rows

left join:

estimated subtree cost: ~78
memory grant: ~156k
estimated number of rows: ~57k

outer apply:

estimated subtree cost: ~99
memory grant: ~163k
estimated number of rows: ~55k

hybrid(solution of Paparazzi but using innner join, for left join see above):

estimated subtree cost: ~87
memory grant: ~170k
estimated number of rows: ~56k

result of some actual execution plan, update on 55017 rows (trigger)

left join:

estimated subtree cost: ~401
memory grant: ~455k
estimated number of rows: ~225k

outer apply:

estimated subtree cost: ~136
memory grant: ~153k
estimated number of rows: ~52k

hybrid(solution of Paparazzi but using innner join, for left join see above):

estimated subtree cost: ~126
memory grant: ~156k
estimated number of rows: ~53k

try this - you might get the best of both

select manyfield
from  ( select manyfield
             , row_number() over (partition by t1.id order by t1.timestamp desc) as rownum
          from inserted
          left join sometable t1
                 on inserted.id = t1.id
      ) tt
where rownum = 1 or t1.id is null


Dynamically choosing column for WHERE clause I've got a table that holds columns named "Day1" through to "Day31".

Is there a way I can dynamically choose the correct column to put in a WHERE clause?

I can get the current day, that's no problem, but I don't know where to go from there.

Use DATEPART DAY to retrieve today's day, then build a simple dynamic SQL concatenating that day's value to the column name.

IF OBJECT_ID('tempdb..#PivotedInfo') IS NOT NULL
    DROP TABLE #PivotedInfo

CREATE TABLE #PivotedInfo (
    Day1 INT,
    Day2 INT,
    Day3 INT,
    Day4 INT)

DECLARE @DayToday INT = DATEPART(DAY, GETDATE())

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        P.[Day' + CONVERT(VARCHAR(2), @DayToday) + ']
    FROM
        #PivotedInfo AS P '

PRINT (@DynamicSQL)

/*
    SELECT
        P.[Day3]
    FROM
        #PivotedInfo AS P
*/

EXEC (@DynamicSQL)

If you need a general column name as result, you can add an alias:

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        DailyValue = P.[Day' + CONVERT(VARCHAR(2), @DayToday) + ']
    FROM
        #PivotedInfo AS P '

PRINT (@DynamicSQL)

/*
    SELECT
        DailyValue = P.[Day3]
    FROM
        #PivotedInfo AS P
*/

You can avoid using dynamic SQL if you unpivot your columns and then filter where the unpivot column name matches your wanted day. It would be more readable but more expensive operation.

INSERT INTO #PivotedInfo (Day1, Day2, Day3, Day4)
VALUES (5, 10, 15, 20)

SELECT
    P.*
FROM
    #PivotedInfo AS I
    UNPIVOT (
        DailyValue FOR Day IN  ([Day1], [Day2], [Day3], [Day4]) -- All day list here
    ) AS P
WHERE
    P.Day = 'Day' + CONVERT(VARCHAR(2), DAY(GETDATE()))


write SQL query that takes its own output as input In a table that displays how trades are related to each other, starting from one trade, I want to find all other trades that relate to the first trade:

713613  PostPTETradeIDs 718478
713613  PrePTETradeId   711314
718478  PostPTETradeIDs 739522
718478  PrePTETradeId   713613

In this example, trade 718478 was created by splitting 713613 (row 1); and 739522 was created from 718478 (row 3), etc. (a PTE splits a trade into two).

select trade_id, keyword_name, keyword_value
from trade_keyword
where (keyword_name like 'LinkedTo' or keyword_name like 'PrePTETradeId' or keyword_name like 'PostPTETradeIDs' )
    and trade_id in('718478', '739522','713613')
order by trade_id;

My goal is to identify trades that belong together and come from one another.

Currently, using the above query, I must fill all the TRADE_IDs manually, i.e. start with 713613, run the query, see that 718478 came from it, then run the query again searching for 718478, too, to find that 739522 came from it, etc.

I would like to make this recursive, such that it will, in one query, given some starting TRADE_ID(s), find all TRADE_IDs that are related to these via any path of any of these relations.

In other words, if column 1 is X and column 3 is f(X), then I want at once a list of all X, f(X), f(f(X)), f(f(f(X))), etc.

Preferably, there would be another column that lists the distance in steps upwards or downwards, where the PostPTETradeIDs relation is upward and the PrePTETradeID is downward (the LinkedTo relationship does not change the counter):

713613 is distance 0,
718478 is distance 1,
739522 is distance 2.

I am using Oracle SQL. Is this issue better adressed via a subquery, and if so, how can it be made recursive; or some other functionality?

You can use a subquery with the same table in both the primary and sub queries, join on trade_id = keyword_value

Depending on how many levels a trade could have it might get complex, but this should get you going in the right direction.


optimizing MySQL for traffic analytics system background :

I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as analytics data.
currently I'm using MySQL and have a table to store click info with this schema:

visit_id (int)
ip (int)
date (datetime)
country
browser
device
os
referrer (varchar)
url_id (int)  //as foreign key to the shortened URL

and for now , just the url_id field has index

The system should represent click analytics in the time periods the user wants, for example past hour, past 24 hours , the past month , ...

for example to generate graphs for past month , I do following queries:

SELECT all DAY(date) AS period, COUNT( * )
                        FROM (

                        SELECT *
                        FROM visits
                        WHERE url_id =  '$url_id'
                        ) AS URL
                        WHERE DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH )
                        GROUP BY DAY( DATE )

//another query to display clicker browsers in this period
//another query to display clicker countries in this period
// ...

issues:

for a shortened link with about 500,000 clicks , it takes about 3-4 seconds to calculate just the first query , so for total queries about 10-12 seconds which is terrible.
lots of memory and CPU is needed to run such queries

questions :

1- how to improve and optimize the structure , so the analytics of high
   traffic links will be shown in less than 1 second(like bitly and similar web apps) and with less usage
   of CPU and RAM ? should I make an index on the fields date,
   country, browser, device, os, referrer ? if yes , how to do
   that for the field date because I should group clicks some times by
   DAY(date), sometimes by HOUR(date), sometimes by MINUTE(date)
   and ...

2- is MySQL suitable for this application? assume at maximum my application should handle 100 million links and 10 billion clicks on them totally. Should I consider switching to an NoSQL solution for example?

3- if MySQL is ok , is my database design and table structure proper and well designed for my application needs? or you have better recommendations and suggestions?

UPDATE: I made an index on column referrer but it didn't help at all and also damaged the performance and I think that's because of the low cardinality of this column (also others) and the big resulting index size related to the RAM of my server.

I think making index on these columns would not help to solve my problem, my idea is about one of these:

1- if using MySQL, maybe generating statistics using background processing for high traffic links is better instead of calculating lively at the user request.

2- using some caching solution like memcached to help MySQL with high traffic links.

3- using a NoSQL such as MongoDB and solutions like Map-Reduce which I am poorly familiar with and haven't used ever.

what do you think?

You can simplify your query to something along these lines. I expect MySQL will generate a simpler execution plan.

SELECT date(date) period, count(*) clicks
FROM visits
WHERE url_id = 3
  AND DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH )
GROUP BY period;

If returning results on high-traffic links in one second is a hard requirement, you might need to upgrade your hardware.

Expect to need an index on each column used in a WHERE clause. You might benefit from some multicolumn indexes; {url_id, date} is a candidate.

Test at scale, if that's at all possible. (It's usually possible, although it might take some time.) Use EXPLAIN to see what MySQL is doing with your queries.

You don't have to query browsers, countries, and everything else all at one time. When I was running web development, I rarely looked at countries--they weren't relevant to the niche I was working in. Also consider other asynchronous UI technologies.

PostgreSQL has a better optimizer. It might work better than MySQL. Test it.


How to add a check constraint in sql azure data warehouse? I'd like to add a check constraint to a table in azure data warehouse.

I have the below script:

alter table [FactTbl_Test] add constraint Ck_Test_id check ([Test_Id] != 'Unknown')

This causes the below error:

  Parse error at line: 2, column: 74: Incorrect syntax near 'check'.

Edit:

Also appears to fail with the same error if the constraint is created inside the table declaration.

This and constraints in general are currently unsupported in Azure SQL Data Warehouse. See https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview#unsupported-table-features for full list.

If the constraint is not enforced at the source or if you have multiple sources, you can implement during the ETL or ELT process. For small datasets, ETL would probably work better. With large datasets, you're likely to get better performance via ELT-L. As in extract then load to a temp table (heap is fast), perform the check/cleansing then load to the final table.


Mysql 8 - % CPU Wait Is it normal in Mysql 8 to have a high percentage of CPU wait?
(see image)

A CPU core being in an "IO wait" state inthe OS means that there are tasks that could be assigned for that core to work on, but they are all waiting for an IO operation to complete.
In your case displayed there, you have only one task that really busy and that is mysql, and it is spending most of its time waiting for IO operations to complete. This is not unusual. If you run cat /dev/sda > /dev/null you'll see the IO wait count jump up, more so if you run that a couple of times concurrently.
It may indicate that ere are things you could optimise to speed up your application(s) considerably:

if the delay is in making updates, then getting faster drives
if the delay is due to queries performing scans of large tables or indexes, then by improving the queries or your index choices
if the delay is mostly due to read IO and there isn't much scanning going on then your common working set may be larger than can fit in memory and adding/allocating more RAM may be helpful
if the delay is a mix or read/write or mostly random access read, then improving IO latency would be useful (the data rates displayed there are not high given the drive is 100% busy, so I assume you have a traditional drive and there is significant random access happening, so upgrading to SSD may help a lot)

Of course if everything is responding with acceptable speed this may be normal for your app and system and you might not need to alter anything yet (though it is always worth taking a look at index use and so forth, and proactively monitoring CPU & IO metrics, so you can pre-emptively spot and perhaps fix future problems that will happen as your use scales up before they actually become significant).


Why is GROUP BY clause missing rows Can't understand a strange behavior of GROUP BY clause:
SELECT id, SUM(num) as sum
FROM (
    SELECT 1 AS id, 2 AS num UNION
    SELECT 1, 3) AS a
GROUP BY id

results in:
id  | sum |
----|-----|
1   | 5   |

but
SELECT id, SUM(num) as sum
FROM (
    SELECT 1 AS id, 2 AS num UNION
    SELECT 1, 2 /*!*/) AS a
GROUP BY id

results in:
id  | sum |
----|-----|
1   | 2   |

UNION excludes duplicate rows.
You want to use UNION ALL in this example:
 SELECT id, SUM(num) as sum
 FROM (
     SELECT 1 AS id, 2 AS num UNION ALL
     SELECT 1, 2 /*!*/) AS a
 GROUP BY id

Results:
id          sum
----------- -----------
1           4


Database-wide trigger I'm new to DBA and I'm attempting to create a trigger that will update the a specified column in one table whenever an insert is made into every table across the database.

I understand it is possible to implement many triggers to solve this problem however it seems to me that it would be much more maintainable if it were to be a single trigger activating at a database wide level.

If having a single trigger is possible, is there then also reason I should not have one trigger to manage all of the tables?

Below is the current way in which I would implement the triggers at a table by table basis:

CREATE TRIGGER [dac].[T_UpdateModelTime] ON [dac].[SubSpeciality]
 AFTER INSERT
 AS

 DECLARE @Table_Model_ID int

 SELECT @Table_Model_ID = Model_ID FROM SubSpeciality

 UPDATE ModelVersion SET DateLastEdited = GETDATE() WHERE ID = @Table_Model_ID;

GO

Additional details

The data is inserted into the tables through stored procedures.

The updated column requires time sensitivity within seconds.

Can I add a single trigger that will fire for every UPDATE, INSERT or DELETE on every table in a database?

No. As per the documentation each TRIGGER must resolve down to columns updated on a single table.

There is no magic solution to your problem, but there are several generic approaches that are generally used.

Utilise Stored Procedures to update a single table holding the records of the last update time of all other tables - this would need to be written into every Stored Procedure that runs an INSERT, UPDATE or DELETE.
Change Data Capture or Change Tracking could also be used if you set up some SQL Agent Jobs to check for updates on a regular basis. Pinal Dave has a great guide to using CDC for this here
Table-level DML triggers - as per your own post. This is likely to have a significant performance overhead and is likely a really bad idea.


Copy a SQL Server 2000 database to another box without interrupting the backup chain I have a small-but-important MS SQL Server 2000 database that I'd like to copy to a development box. I understand that the usual way to do this is to make a copy of a full backup file, and restore that backup onto the dev box.

The twist here is that I've recently inherited this database and its backups are managed by a third-party tool (Groundworks). As far as I can tell, I don't have access to the backup files themselves, but only to the Groundworks interface which doesn't appear to expose them. The database has full, differential, and transaction log backups, so I can't just make a backup on my own from Management Studio, since I understand I'll invalidate the backup chain until the next full scheduled backup.

If this were SQL Server 2005, I'd get around this with a copy-only backup, but that feature doesn't seem to be there in SQL Server 2000.

Is there an approach I'm missing? The Groundworks manual doesn't seem to cover this scenario and I haven't had any luck online, but I'm rather new to DBA work and have the feeling I've overlooked something.

Taking a full backup will invalidate the subsequent differential backups, but it won't break the log chain. So you'd restore with your last full backup taken by the tool - and then all the subsequent log backups. Not ideal, but....

Can you take your full backup just before the tool takes its full backup?