Change Data Capture vs trigger for logging changes

Given the choice between those two things, here's why I'd choose triggers:

Triggers can make related table changes synchronously (like if you need to update reporting tables or check referential integrity)
Triggers let you store the data in the format that works for you, whereas CDC puts it in its own CDC schema, in tables that may not work for your reports
Triggers can have logic that checks the kind of update or the source of the update, and only log things you want to log
Triggers can easily be temporarily disabled for things like bulk loads, or if the business is facing huge load demands (like Black Friday sales)
Triggers let you put more business logic in, like if you want to put some kinds of history in one table and other kinds of history in another
Triggers don't directly add storage overhead (whereas CDC adds at least 34 bytes per row)


Updating a table from a temporary table where clause error in MySql Community

SQL queries always explicitly reference their tables by name, apart from any references to the columns in the tables. Since your query doesn't include a reference to the 'a' table explicitly as a table, the engine has no idea what a is. I'm a SQL Server guy, but based on some quick Googling of "MySQL update join", looks like the statement should be:

UPDATE residential_summary
         INNER JOIN a ON residential_summary.meter = a.meter
SET residential_summary.load_factor = a.load_factor;

The UPDATE ... INNER JOIN ... tells the engine that this query will be working with the residential_summary and a tables. The INNER JOIN ties residential_summary and a together based on the values of meter in both tables, so that only residential_summary rows that have a matching row in a will be updated.


Using a varchar(255) as a 'one-size fits all' type is a bad idea, but that is another story, and not what you asked.

As @ypercube said, you have 2 options:

a) Create an additional index on user_id

ALTER TABLE `test` ADD INDEX `user_id` (`user_id`);

b) Reorder your PRIMARY key so it starts by user_id

ALTER TABLE `test` DROP PRIMARY KEY,
ADD PRIMARY KEY (`user_id`, `tweetid`,`screen_name`,`date`);

This second option will take half the disk and memory space, but it may affect negatively queries filtering, for example, by tweetid only. You can read more about left-most prefix indexes and why your query was doing a full table scan here.


I haven't tested this extensively but I think it can do what you need. Here I assume that ID_NO is a customer identifier, TD is the date of the order, and AMT_PUR represents the amount ordered. You want all of the rows for when a customer has ordered 10 or more of anything over a 30 day date range starting with that day.

Here is a little bit of test data:

CREATE TABLE DB1 (
ID_NO VARCHAR(10) NULL,
TD DATETIME NULL,
AMT_PUR INTEGER NULL
);

BEGIN TRANSACTION;
INSERT INTO DB1 VALUES ('A', '01/01/2016', 3);
INSERT INTO DB1 VALUES ('A', '01/05/2016', 1);
INSERT INTO DB1 VALUES ('A', '01/09/2016', 2);
INSERT INTO DB1 VALUES ('A', '02/01/2016', 7);
INSERT INTO DB1 VALUES ('A', '02/02/2016', 2);
INSERT INTO DB1 VALUES ('A', '03/01/2016', 2);
INSERT INTO DB1 VALUES ('A', '03/04/2016', 1);
INSERT INTO DB1 VALUES ('B', '02/01/2016', 3);
INSERT INTO DB1 VALUES ('B', '02/28/2016', 6);
INSERT INTO DB1 VALUES ('B', '03/05/2016', 3);
INSERT INTO DB1 VALUES ('B', '03/28/2016', 6);
COMMIT TRANSACTION;

Suppose that you just looped over each ID_NO in reverse TD order and kept track of the running sum of AMT_PUR. You couldn't just keep the rows with a running sum that's greater or equal to 10 because some of the rows previously added to the total may not be within 30 days of the current row. However, if there was a way to remove rows too far away from the current row then you could just use the running sum and you could do your calculation with a single pass over the data.

The idea behind this approach is to UNION ALL together two copies of the data. The second copy will have the AMT_PUR value reversed and will have 31 days subtracted from the original value. With the data in that format you can perform the calculation with just one pass. We can loop over the data this way using SUM() with the OVER() clause.

SELECT
ID_NO
, TD
, REVERSE_RUNNING_SUM
FROM
(
    SELECT
      ID_NO
    , TD
    , SUM(AMT_PUR) OVER (PARTITION BY ID_NO ORDER BY TD DESC, VALID_FLAG ASC) REVERSE_RUNNING_SUM
    , VALID_FLAG
    FROM
    (
        SELECT ID_NO
        , TD
        , AMT_PUR
        , 1 VALID_FLAG
        FROM db1

        UNION ALL

        SELECT ID_NO
        , DATEADD(DAY, -31, TD) TD
        , -1 * AMT_PUR
        , -1 VALID_FLAG
        FROM db1
    ) t
) tt
WHERE tt.VALID_FLAG = 1
AND tt.REVERSE_RUNNING_SUM >= 10;

Minor update: It would probably be more efficient to CROSS JOIN db1 to a two row table instead of using UNION ALL, but I'm going to keep the UNION ALL approach in my code because I think that's easier to understand.


As dezso said in the comment, the problem was that CREATE can't be part of a subquery. And phpPgAdmin transform the query before executing it so it had put the CREATE as a subquery.

So the solution was to execute the CREATE query in PgAdmin for example.


charindex thrown off by extended characters

Apparently it seems related to a collation or encoding issue.

I'd check database and column collations to ensure both are the same.

Server collation

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

Database collation

SELECT
    name,
    collation_name
FROM
    sys.databases
WHERE
    name = 'DBName';

Column collation

SELECT
    name,
    collation_name
FROM
    sys.columns
WHERE
    name = N'ColumnName';

Quoted from MS-Docs Collation precedende

  Functions and Collation

  THE CAST, CONVERT, and COLLATE functions are collation sensitive for
  char, varchar, and text data types. If the input and output of the
  CAST and CONVERT functions are character strings, the output string
  has the collation label of the input string. If the input is not a
  character string, the output string is Coercible-default and assigned
  the collation of the current database for the connection, or the
  database that contains the user-defined function, stored procedure, or
  trigger in which the CAST or CONVERT is referenced.

  For the built-in functions that return a string but do not take a
  string input, the result string is Coercible-default and is assigned
  either the collation of the current database, or the collation of the
  database that contains the user-defined function, stored procedure, or
  trigger in which the function is referenced.

  The following functions are collation-sensitive and their output
  strings have the collation label of the input string:

  CHARINDEX, REPLACE, DIFFERENCE, REVERSE, ISNUMERIC, RIGHT, LEFT, SOUNDEX, LEN, STUFF, LOWER, SUBSTRING, PATINDEX, UPPER

Let me suggest to have a look at this or this answers of Solomon Rutzky about UTF-8 and UTF-16 encoding values for VARCHAR and NVARCHAR.

By now you can try by CASTING it to VARCHAR(255) before use CHARINDEX.

I've tried to reproduce it in one of my servers in this way:

DECLARE @t VARBINARY(MAX) = 0x6600640070002E000103650074006900;
DECLARE @vt VARCHAR(MAX);

SELECT CAST(@t AS nvarchar(MAX));

---------
fdp.́eti

SET @vt =  CAST(CAST(@t AS nvarchar(MAX)) AS VARCHAR(MAX))

SELECT REVERSE(LEFT(REVERSE(@vt), CHARINDEX(N'.', REVERSE(@vt))-1));

-------
´eti

Note it returns the accent as a single character.


How to create multiple tables based on existing tables in postgreSQL?

Most of these procedural things can be done using a PL/pgSQL DO Block. So you don't have to get into another language. Assuming your main tables have a naming pattern like 'mt_%', you could do something like the following.

do
$create_hist_tables$
declare
  main_table text;
begin
for main_table in
  select
    'public.' || quote_ident(table_name)
  from
    information_schema.tables
  where
    table_name like 'mt_%'
    and table_schema = 'public'
loop
  execute 'CREATE TABLE ' || main_table || '_hist ("action" text, "user" text, like ' || main_table || ' including all)';
end loop;
end
$create_hist_tables$
;

Building a long query using concatenation will not be neat for maintainability. So you could replace the single create query with a series of create and alter commands within the loop.

I have tried this on PG 11.4.


I unfortunately don't know much about postgres on AWS, my company is just starting to move that way, but when a vacuum runs here it does create disk io that could potentially slow down a query.  It could be that the locks are on resource contention more as opposed to Postgres Locks.


DBA's - What did you do that helped you gain the most understanding in your role?

If you're referring to Microsoft SQL Server then my path directly applies, otherwise you can derive the same generalizations and apply them to the database system you're learning about. The following four things were the major contributors to how I grew in my DBA knowledge:

Brent Ozar - Specifically I was lucky enough to stumble on his podcast Office Hours and would listen to all the old ones everyday until I was caught up and then I'd participate in asking questions in the new live sessions. (You can find the podcast on any modern podcast system.) This helped introduce new key terms and system features in my brain, even if I didn't fully understand them at the time. Just hearing the vocabulary used over and over was super helpful in itself.

RTM (Reading The Manual) - After I got hooked on Brent Ozar, and learned a lot of new key terms, I'd read the Microsoft documentation a few times over on that feature to try to understand its purpose and how it's used. Some days I'd scan page after page in the documentation looking for more features I wasn't aware of. I was hungry to have at least heard of every feature SQL Server had to offer.

Reputable Articles and Blogs - Aside from all the content on Brent Ozar's website, there's a ton of reputable sites out there with awesome information on everything SQL Server under the sun. To name just a few of the many, SQLPerformance, RedGate's Simple Talk, Erik Darling Data, Kendra Little, Pinal Dave's SQL Authority, anything written by Aaron Bertrand on the multitude of places he contributes to (including SQLPerformance), among many many others out there. I initially stumbled on a lot of these from Googling issues I ran into with SQL Server as they came up, or when trying to further my knowledge from the Microsoft docs.

DBA.StackExchange - This site has always been greatly helpful for me, especially when I was in my early phases of learning and got stuck. I learned a lot from the many questions I asked on here, and resultantly continue to further my expertise as I share that knowledge to help others on here or as I am forced to research more into the answers I provide to others.


Using SQLite ODBC without installing a driver

ODBC as just an interface between applications and drivers.
It is impossible to create an ODBC connection without using some installed driver.


sqlite database table with 1000's of columns

Despite of the database you are using, normally you don't add thousands of fields to a table. Your second model is more 'normalized' ( and it's easy to index ), and should work better ( I'm not an expert, anyway ). My opinion is based on normal limits of databases, that are not meant to work that way, so you're going to hit some inner limit of the engine, or misusing the engine as it was designed for.

You can explore functions and datatypes of more modern versions of mysql, MariaDB and others, that have defined types for points and similar.


Unfortunately you cannot do this with Extended events unless the data from XE can be converted into the format that DTA uses or can be stored in the tables generated by DTA.

Not recommended though but Trace is is still available, so can be used if you want.

You can even use DMV's for finding missing indexes, etc.

Also worth mentioning Aaron's post : Don't just blindly create those "missing" indexes!, since you are using DTA for analysis.


Lookup multiple ids in seperate table with 2 subquerys

You're permitted to do multiple joins in a single statement. You can do this even when joining on the same table by using aliases.

I created a very quick example which is similar to your case. I just used ids instead of a time, and I stored the whole name as a single field; you'll just have to concatenate the first and last name on your own ;)

Schema and Data

CREATE TABLE prtimesheet (
  id numeric,
  submitter_id numeric,
  approver_id numeric
);

 CREATE TABLE srm_resources (
   person_id numeric,
   name text
 );

 INSERT INTO prtimesheet VALUES(1, 1000, 1001), (2,1000,1002), (3,1002, 1001), (4,1001,1002);

 INSERT INTO srm_resources VALUES (1000,'Jack Ryan'), (1001, 'Bart Mancuso'), (1002, 'James Greer');

Multiple joins on same table using aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources AS sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources AS app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

This should fix you right up!

Here is a SQL Fiddle for you to play around with if you like.

Edit: And as suggested...

The Oracle version: Schema and Data

CREATE TABLE prtimesheet (
  id number,
  submitter_id number,
  approver_id numeric
 );

  CREATE TABLE srm_resources (
     person_id number,
     name varchar(25)
  );

  INSERT ALL
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(1, 1000, 1001)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(2,1000,1002)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(3,1002, 1001)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(4,1001,1002)
  SELECT * FROM DUAL;

 INSERT  ALL
 INTO srm_resources(person_id, name) VALUES(1000,'Jack Ryan')
 INTO srm_resources(person_id, name) VALUES(1001, 'Bart Mancuso')
 INTO srm_resources(person_id, name) VALUES(1002, 'James Greer')
 SELECT * FROM DUAL;

The Oracle version: Joins and aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

Here is the Oracle SQL Fiddle!


why didn`t the first query fail?

Because this is valid SQL.

First of all consider that it is valid to run a SELECT against the table that doesn't reference any columns from that table.

SELECT DISTINCT 'foo'
FROM   product_template
WHERE  type = 'import';

The above will return a single row result of foo if any rows exist that match the WHERE clause.

Secondly consider that it is valid to reference a column from the outer table in a sub query (this is required for correlated sub queries to work).

In the event that the column name is not qualified with a table reference then it will be resolved in the inner scope if possible and outer scopes if not. In your case product_template has no such column so it was resolved as belonging to product_product.

As a best practice be explicit about the tables the columns belong to. Had the subquery been written as below it would have failed to compile and alerted you of the error.

IN (SELECT DISTINCT pt.product_tmpl_id
    FROM   product_template pt
    WHERE  pt.type = 'import');

  what exactly did it do?

This depends. If FROM product_template WHERE  type = 'import'; returns zero rows then you were lucky and this is equivalent to

update product_product
set (write_date, default_code) = (LOCALTIMESTAMP, 'update')
where product_tmpl_id = null; /*Never true*/

Probably you weren't lucky though and it did return at least one row. In this case you ran the equivalent of the following

update product_product
set (write_date, default_code) = (LOCALTIMESTAMP, 'update')
where product_tmpl_id = product_tmpl_id;

which is equivalent to

update product_product
set (write_date, default_code) = (LOCALTIMESTAMP, 'update')
where product_tmpl_id IS NOT NULL;

  why took it so long to run?

I imagine as firstly it updated all rows in the table.

I'm not sure what the execution plans for this would be like in Postgres too.

In the worst case it might have been selecting all rows matching the where from product_template, passing in the correlated parameter, then performing DISTINCT on the result for each row in the outer product_product table.


SQL Server : differential backup not updating

The extension of a backup file is just to have a notation and easy identification. The script you are using is for Log backup and not for Differential backup.

Differential backup backs up the extents that had changes since the last full backup. Below is the t-sql for that :

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL
GO

The backup that you are taking is log backup and in addition to letting you restore the backed-up transactions, a log backup truncates the log to remove the backed up log records from the log file. This is very important to have a point in time recovery and has to be scheduled to run frequently.

Please go through below link to understand how backups work :

https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx


Why the length of JOBHISTORY field is 172 here?

Varray stores the total length of the varray and length of each element, so yes, it needs some extra bytes.

I am not aware of any public documentation on this topic, and I do not know the complete internal representation, so below is a simple example.

create or replace type jobs_done as varray(5) of varchar2(30);
/
create table emp_job_his (empno number(6), jobhistory jobs_done);
insert into emp_job_his values (1, jobs_done(
  'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',
  'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB',
  'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC',
  'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD',
  'EEEEEEEEEEEEEEEEEEEEEEEEEEEEEE')
);
commit;
alter system checkpoint;

select
  dbms_rowid.rowid_relative_fno(rowid) as fno,
  dbms_rowid.rowid_block_number(rowid) as block_number
from emp_job_his;

       FNO BLOCK_NUMBER
---------- ------------
         4          134

alter system dump datafile 4 block 134;

The relevant part from the dump:

col  1: [162]
 88 01 a2 01 01 00 05 1e 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 1e 42 42 42 42 42 42 42 42 42 42 42
 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 1e 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 1e 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44
 44 44 44 44 44 44 1e 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45
 45 45 45 45 45 45 45 45 45 45 45 45

The 3rd byte, a2, is the length of the column in bytes = 162 (col 1: [162]).

The 7th byte, 05, is the number of elements in the array  = 5.

Starting after that, each element is stored as length + data. Length is 1e = 30, and data is 30 times the letter A = 0x41, and so on.


Will Ola's default IndexOptimizer job update my stats?

By default, the script does not perform statistics maintenance. The documentation for the script's parameters can be found here.

Value   Description
================================
ALL     Update index and column statistics.
INDEX   Update index statistics.
COLUMNS Update column statistics.
NULL    Do not perform statistics maintenance. This is the default.

So if you let the main script create your maintenance jobs for you, and you wanted to update index and column statistics, then you'd need to edit the IndexOptimize job and add , @UpdateStatistics='ALL' into the procedure call.


To me this sounds like a clear case of using Lock Pages in Memory and setting a reasonable max and min server memory.


SQL Server to Oracle export using Microsoft OLEDB Provider for Oracle

I would try installing the Oracle Data Access Components (ODAC).

You will be able to download them from this link.
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html


Oracle ram usage higher than memory_max_target

Those are 3 seperate instances.

You checked and listed the memory parameters from only 1 instance.

We do not know which instance that is, and we do not know anything about the other 2.

Each instance has its own memory parameters.

None of the instances use more memory than the value listed above.

I see no problem here.


You need to use JOIN by ID instead of the UNION.
Using JOIN there is no need of the sub-query.
Also ( before SELECT is not required after the INSERT INTO.

So the working query is:

INSERT INTO Company (Year, Company, Revenue)
SELECT FR.Year, FR.Company, CF.Revenue
FROM Fortune500 FR
JOIN Comp_Financials CF ON CF.ID = FR.ID;

Added More explanation in this post, why you need to avoid UNION, and use JOIN for your requirement.


Mount mdf file to sql server desktop edition

I suspect this is not a SQL Server 2000 database you are attempting to attach to SQL Server 2000. That appears to be a common cause of the error as indicated here also.

Can you attach this to SQL Server 2005 or 2008 Express and see what error message you get? Do you have access to the underlying database that your files are from?

I imagine if you attach this to SQL Server 2005 or 2008 you will be able to attach it unless there are other impediments to attach going on that that approach will reveal.


Can't start postgres server, SHMMAX too small

This question has been asked many times. Try a search. Or Google.

This chapter of the manual deals with it comprehensively.


How do I copy a view from one server to another?

Change the script so that you use the correct database before running it or remove the use statement completely either manually or by not scripting it out to begin with.


Why is LIKE more than 4x faster than MATCH...AGAINST on a FULLTEXT index in MySQL?

I think the problem may stem from the presence of the FULLTEXT index itself.

Every time there is a query involving a FULLTEXT index, the MySQL Query Optimizer tends to whack the query into a full table scan. I have seen this over the years. I also wrote an earlier post about this most trifling behavior in FULLTEXT indexes.

You may need to do two things:

refactor the query so that the FULLTEXT index does not throw the MySQL Query Optimizer into a state of confusion
Add an additional index that will properly support the refactored query

REFACTOR THE QUERY

Here is your original query

SELECT post_id
FROM phpbb_posts
WHERE topic_id = 144017
AND post_id != 155352
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')

You will need to refactor the query like this:

SELECT subqueryA.post_id
FROM
(
    SELECT post_id FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) subqueryA
INNER JOIN
(
    SELECT post_id FROM phpbb_posts
    WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')
) subqueryB
USING (post_id);

CREATE A NEW INDEX

You will need an index to support subqueryA. You already have an index on topic_id. You need to replace it as follows:

ALTER TABLE phpbb_posts ADD INDEX topic_post_ndx (topic_id,post_id);
ALTER TABLE phpbb_posts DROP INDEX topic_id;

Give it a Try !!!

UPDATE 2012-03-19 13:08 EDT

Try this one first

SELECT post_id FROM
(
    SELECT * FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) A;

If this runs fast and return a small number of rows, then try this nested subquery:

SELECT post_id FROM
(
    SELECT * FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) A
WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar');

UPDATE 2012-03-19 13:11 EDT

Compare the running time of this:

SELECT count(*) FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

with this

SELECT count(*) FROM phpbb_posts WHERE 1 = 1;

If there running time is the same, then the MATCH clause is being executed on every row. As I mentioend earlier, using FULLTEXT indexes tends to nullify any benefits attempted  and contributed by the MySQL Query Optimizer.


Would that work? SQL version only.

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CPT4, CPT4MOD, ITEMCHARGE ORDER BY EFF_DATE DESC) AS RowNo
    , *
FROM #table
) AS T
WHERE T.RowNo = 1

You can check it here:
http://data.stackexchange.com/stackoverflow/query/260027/delting-duplicate-rows-with-condition


The answer is evolving.  More "cost" analysis went into 5.7, still more into 8.0.  There is more to go, such as "histograms" instead of a single number for cardinality.

In the case of joins, MySQL has always pursued all permutations of ordering for the tables.  However, it has an aggressive pruning strategy that keeps it from actually doing "all".  (Key-value (EAV) stores defeat this, but there is a tunable to help.)

The main statistic used is an estimate of the cardinality for each index.  This is found by 'randomly' probing the BTree for the index.  These probes and the retention of the data was significantly improved in 5.6.

Generally, when you are joining multiple tables, the WHERE clause has a lot of sway in saying which table to start with.  If there is no WHERE, then the "smallest" table is picked as the first.  (This is sometimes not the best.)

There are a few tricks to guide the Optimizer.  These are generally a bad idea.  What helps today may hurt tomorrow.

STRAIGHT_JOIN -- do the joins in the order given.
USE/IGNORE INDEX -- give preference to/against specified index(es).
FORCE INDEX -- If at all possible, use this index.

And 8.0 has more.

And there are tricks.  A 'derived' table is allow to ignore ORDER BY.  But you can trick it into doing the sort by adding a LIMIT with a huge value.


Are these things not to be set in my.cnf on the slave?

No, You only can set those values during execution time (using a mysql client tool)

  if I have to restart the slave mysql for whatever reason one day, how will the slave know where to pick up from? Do I have to go through the process of re-importing the DB to know the position again?

MySQL stores replication variables in files located by default in the data directory (Usually theses file names contain "relay" and "master" words) and it updates those file with every transaction, so no worries, if you need to restart your slave, the server already knows how to catch up the master.


Automatic Failover without data replication

Judging by your comment, it sounds like you are looking to make the move to AlwaysOn Availability Groups.  The way that availability groups work is that you choose which databases are added to the availability group and are therefore part of the high availability implementation.

So in other words, if you want Database1 and Database2 to be highly available with automatic failover, then you configure a partner to have synchronous commit and be an auto failover partner, and when you create your availability group you add those two databases to the availability group.  But if you don't want Database3 to be part of that topology, then you simply don't do anything with that database regarding the availability group.


SQL Server 2012 Express SP1 install still not 11.1

The full Express version (with SP1 included) is for installing a new instance, not for applying a service pack to existing instances. Sounds like perhaps you now have a second instance of SQL Express (no idea without seeing your configuration manager or services applet).

At this point you should download Service Pack 2 (not 1) to apply to existing instances, regardless of edition:

Download SQL Server 2012 Service Pack 2

(And perhaps clean up the extra instance, if in fact you did install a second instance inadvertently.)


Trouble with surname "Null" and LOAD DATA INFILE

How can I prevent the string 'NULL' from being interpreted as a NULL value in the context of a SET clause of a LOAD DATA INFILE command? (Assigning to a nullable varchar column.)

Provide a FIELDS ENCLOSED BY from the docs

  If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

Essentially if you set FIELDS ENCLOSED BY '"' then you can provide surname "Null", as "Null", and nulls as null (no quotes).


This can be done in a few minutes with storage snapshots/cloning. If you can't do that, just go for RMAN duplicate. Both method can be scripted and scheduled to run automatically.


Is there an equivalent of "OPTION(RECOMPILE)" or "WITH RECOMPILE" for an entire connection?

Execution plans do not last for the duration of a connection: they are shared across all connections.

It has to be specified per query because any plan cache/reuse issues affect only that plan.

What are you trying to do please, and why do you think it would help?

Edit, after comment

The plan must exist in cache to be used (and reused). When it's cached, it uses memory. Recompiling would use the same memory and use extra CPU etc to recompile.

You said "tons of commands" and "thousands of databases": this is your problem. And probably no "dbo." etc to help plan reuse

Thoughts:

buy bigger server + more RAM
migrate to 64 bit with as much RAM as possible
split databases onto different servers
forced parameterisation
check quality of queries


Running different instances of SQL Server from different networks on one box

If you can access the instances at the IP layer then you will always be able to connect using either SQL Authentication (if enabled) and even Windows Authentication using the runas /netonly trick. Say you are in the domain 'foo' and you want to connect to an instance in the domain 'bar', and there is no trust relationship between 'foo' and 'bar'. Since the instance in the 'bar' domain can only authenticate an user in the 'bar' domain you would normally have to be running as 'bar\user' to connect to this instance, but of course your host in 'foo' will not let you run as 'bar\user'. runas /netonly to the rescue: start your client process (eg. SSMS) using the /netonly option:

runas /netonly bar\user "c:\program files\...\ssms.exe"

When launched this way (you'll be prompted for bar\user password) the SSMS will authenticate to any resource over the network as 'bar\user' and you'll succeed to connect to the 'bar' based SQL Server instance. This trick works with local accounts as well, so instead of bar\user you can use <machinename>\Administrator to appear to that machine as the local administrator.

If the destination server is unreachable at the IP layer then you have no option, you must make it reachable, either by VPN, or by DirectAccess, even a netcat bridge can work with the /netonly trick.


What we have done in a similar scenario (using Aurora RDS for master and slaves) is to use an intermediate small EC2 instance with a standard MySQL acting as a filter. This instance is a relay-slave (secondary master) between our main master and final slaves with a configuration similar to this.

We came to this solution dued to the lack of SUPER privilege and replication configuration options in Aurora RDS: You can not set sql_log_bin or define replication filters in RDS.

The problem here is with DDL statements, they are replicated to slaves although you have replication filters by db or tables in the slave. So a possible workaround is:

Create an auxiliary schema in master.
Set a db filter in mysql.cnf of secondary master, so it only adds to its binlog changes in desired schema:
binlog-do-db=<myschema>
If you need to add or modify columns of one of your tables in main master, first set the auxiliary schema as default:
use auxiliary-schema;
Modify the table indirectly, like
alter table <myschema>.<table> ...

This way, DDL statements are applied in secondary master, but not stored in its binlog and therefore not send to the slaves.


How does SQL Server handle logins when the user is a part of multiple groups with various privileges?

For normal roles, permissions for all role memberships are cumulative and DENY takes precedence over GRANT.  However, sysadmin is a special case.  SQL Server permissions are not checked for sysadmin role members so members of that role have full permissions to all databases on the instance.


because where rating.stars = 5 rating.ratingdate is not null.

Hence your select part gets no data to insert.


Server Logon Trigger fails after deleting many records in "Logon-Table" -SQL Error 2801

From a logging perspective, if you intend to delete more than 50% of the rows from a table, you should use the following methodology:

Set the transaction isolation level to serializable
Begin a transaction
Create a new table, named the same as the original table, with a _x
suffix
Copy the results you want to keep from the original table to the _x
one
Drop the original table
Rename the _x table back to the original table
Commit the transaction

Also, the PK_ID column that you described should be the PK and clustred index for the table, otherwise its just a heap.

Also, in terms of logging server logins, perhaps you'd have better luck using an Audit or extended event session?

The "The definition of object ‘my_proc’ has changed since it was compiled" error that you described should be resolvable by calling sp_recompile.


Difference in Size of SQL Server 2008 R2 backup on Local disk and on Remote Network Share

You say you are "using SQLDATABACKUPANDFTP Software" if this software is from http://sqlbackupandftp.com/ then it says it "Zips, Encrypts and FTPs the database backups" which would explain much of the difference.

I'm afraid your screenshots didn't appear so I can't comment on the other difference.


Autoincrement vs composite primary key for a big table on innodb

There is nothing wrong with a composite key. However, you have to take into account how InnoDB stores data.

Quoting the above linked documentation:

  The data in each InnoDB table is divided into pages. The pages that make up each table are arranged in a tree data structure called a B-tree index. Table data and secondary indexes both use this type of structure. The B-tree index that represents an entire table is known as the clustered index, which is organized according to the primary key columns. The nodes of the index data structure contain the values of all the columns in that row (for the clustered index) or the index columns and the primary key columns (for secondary indexes).

That is, InnoDB will store the data according to your PRIMARY KEY. If the data you're inserting has an increasing PK, page fragmentation does not occur. That will happen always with an AUTO_INCREMENT. If you're inserting the data in chronological order (i.e. gDateTime is always monotonically increasing), changing the order of the columns that make out your PK to:

PRIMARY KEY (`gDateTime`, `alarmTypeID`, `vehicleID`)

... will have the same advantages, with regard to not having to "fit a new row in the middle of others" (which means, the B-tree isn't fragmented for every insert).

However: if you reference this table from other (related) tables, you have to store, in the referencing table always the PK (gDateTime, alarmTypeID, vehicleID). This means you're saving every time 7 or 8 bytes of storage. The composite PK would use 2 + 1 + 8 = 11 bytes of information (probably it uses 12 bytes due to alignment); whereas an INT UNSIGNED AUTO_INCREMENT, you'll use only 4 bytes in the referencing table. You're limited to a 2^32 different values for your PK. If you need more than 2^32 values, you'll need BIGINT AUTO_INCREMENT, that gives you 2^64 (and I haven't found yet a practical case where this isn't big enough).

Whether this makes sense or not, depends a lot on your particular scenario.


Oracle extract slow function call from a WHERE clause

The second form of your query will work if you make your mark your function as deterministic, meaning that for a given set of input values, it will always return the same result.
With that set, Oracle will only run the conversion once for each parameters in the where clause rather than for every row.

With this:

CREATE OR REPLACE FUNCTION TO_MINUTE_D (DATE_IN IN DATE)
RETURN NUMBER DETERMINISTIC AS
BEGIN
  /* Minute 0 = 12/30/1899 12:00am */
  RETURN
    (TRUNC(DATE_IN, 'DD') - TO_DATE('12/30/1899', 'MM/DD/YYYY')) * 1440 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'HH24'))  * 60 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'MI'));
END TO_MINUTE_D;
/

On a table filled with a large bunch of dummy rows (increasing ints), I get the following timings consistently:

SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD'));

no rows selected

Elapsed: 00:00:12.69

versus deterministic-annotated function:

SQL> SELECT * FROM MY_TABLE
     WHERE START_MINUTE < TO_MINUTE_D(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
     AND STOP_MINUTE > TO_MINUTE_D(TO_DATE('2013-01-01', 'YYYY-MM-DD'));

no rows selected

Elapsed: 00:00:00.07

You should get very close to what you have with the values plugged in directly, and indexes on those columns can be used as if you'd plugged in literals.

(Putting the conversion function on the start|stop_minute columns isn't a good idea in general as you've discovered, unless you have a function-based index on those that matches exactly.)


Do Availability Groups support Active/Passive?

If you are talking about AlwaysON Availability Group in that case as long as secondary replica(s) are used strictly for Disaster Recovery or StandBy database then you do not need to license those replica in terms of SQL Server Licensing.

Few weeks ago, I had similar question related to AlwaysON availability group and here is the official answer I got from our MS Licensing Rep.

"When the secondary server does any work at all, like taking requests or running backups off of it, or any of the readable features of AlwaysOn are enabled then it must be fully licensed.

If its sole purpose of replica is for fail-over and none of the readable features of AlwaysOnare enabled, then the fail-over right provided below will cover that server from a licensing standpoint and a second license is not required."

In terms AlwaysON FCSI (failover clustering) licensing stays the same. You only license active node. If you use log shipping for DR you do not license your DR server.

If you are in North America region, you can call Microsoft 1-800-426-9400, Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time)   to confirm this.


SQL Server commands to clear caches before running a performance comparison

Personally, for a common query the 2nd and subsequent executions matter more.

Are you testing disk IO or query performance?

Assuming your query runs often and is critical, then you want to measure that under real life conditions. And you don't want to clear prod server caches each time...

If you want, you can:

DBCC DROPCLEANBUFFERS clears clean (unmodified) pages from the buffer pool
 Precede that with a CHECKPOINT to flush any dirty pages to disk first
DBCC FLUSHPROCINDB clears execution plans for that database

Also see (on DBA.SE)

Solving high page load time using SQL Profiler


Can I create index on a table in a MySQL database while in use

Note that if your table is using the InnoDB Plugin storage engine (which I highly recommend), if it's a secondary index (almost surely the case), you can still read from the table (i.e. it's non-blocking to SELECT statements).


I think that you can rewrite that query to use a FULL OUTER JOIN:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        FULL OUTER JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
            AND ( ordIdent.Identifier = '29584'
                    OR ord.ClientOrderId = '29584')
WHERE ordIdent.Identifier IS NOT NULL AND ord.OrderId IS NOT NULL;

But why would you want to? You'll end up reading all rows from both tables with that syntax which causes the performance problem that you're trying to avoid. UNION or UNION ALL is the right way to go here. For filters like you have you can still use UNION ALL like so:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ord.ClientOrderId = '01193504'

UNION

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ordIdent.Identifier = '01193504' AND (ord.ClientOrderId
 IS NULL OR ord.ClientOrderId <> '01193504')

Sometimes there's a slight performance benefit in doing so. It depends on your data.


Is "Read only routing" the only way to force the connection with ApplicationIntent=ReadOnly to connect secondary replica?

Is there any other way to let one application such as SAS to connect to the listener and the SQL AG will point it to the secondary?

Unfortunately no, not within SQL Server.

You must specify ApplicationIntent=ReadOnly in the connection string in order for the connection to be eligible for read-only routing, as is documented:

  Requirements and Recommendations for Client Connection-Strings

  For a client application to use read-only routing, its connection string must satisfy the following requirements:

  ...
  Set the application intent attribute/property to readonly.
  ...


Would it be worthwhile to use the PostGIS extension for custom planets?

To be able to compute the distance on a sphere, you need the geography type, i.e., you need PostGIS.

For your fictional planets, you need to add custom projections so that PostGIS knows about them. The PostGIS documentation says:

  Although the PostGIS spatial_ref_sys table contains over 3000 of the more commonly used spatial reference system definitions that can be handled by the proj library, it does not contain all known to man and you can define your own custom projection if you are familiar with proj4 constructs.

And if you are not familiar with proj4 constructs, you can do it anyway by taking some existing definition and modifying it. Go to spatialreference.org, and look at the projections defined by the International Astronomical Union, e.g., IAU2000:19900 for longitude/latitude on Mercury. There is an option to show the INSERT command for PostGIS:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 919900, 'iau2000', 19900, '+proj=longlat +a=2439700 +b=2439700 +no_defs ',
         'GEOGCS["Mercury 2000",DATUM["D_Mercury_2000",SPHEROID["Mercury_2000_IAU_IAG",2439700.0,0.0]],PRIMEM["Greenwich",0],UNIT["Decimal_Degree",0.0174532925199433]]');

919900 is the SRID. User-defined PostGIS SRID values must be between 910000 and 998999; just select any unique number in this range.
iau2000 is the name of the authority that defines this projection. You are not the IAU, so replace it with something like The John O.
19900 is a unique ID for the projection, defined by the authority. For simplicity, just use the same number as the SRID.
2439700 (3×) is the radius of Mercury, in meters. Replace this with your planet's radius.
Mercury 2000 is the name of the projection (here: the planet's name, and the year when it was defined). Also replace D_Mercury_2000 and Mercury_2000_IAU_IAG with some other names.
Greenwich is the name of the prime meridian. I do not think that the Greenwich Royal Observatory exists either on Mercury or on your planet, but these names are not actually used anyway. You need a value different from 0 only if there are different prime meridians.
0.0174532925199433 is the number of degrees per radian, and does not change.

For example, a projection for B-612 would look like this:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 912345, 'Antoine de Saint-Exupéry', 912345,
         '+proj=longlat +a=1.75 +b=1.75 +no_defs',
         'GEOGCS["B-612",DATUM["B-612",SPHEROID["B-612",1.75,0.0]],PRIMEM["The Rose",0],UNIT["Decimal_Degree",0.0174532925199433]]');

This can then be used in computations:

CREATE TABLE points_on_b612(name text, point geography(point, 912345));
INSERT INTO points_on_b612 VALUES ('north pole', ST_SetSRID(ST_MakePoint(0, 90), 912345));
INSERT INTO points_on_b612 VALUES ('south pole', ST_SetSRID(ST_MakePoint(0, -90), 912345));

SELECT a.name, b.name, ST_Distance(a.point, b.point)
FROM points_on_b612 a JOIN points_on_b612 b ON a.name < b.name;

    name    |    name    | st_distance
------------+------------+-------------
 north pole | south pole |  5.49778714


Migrating data from Firebird to CSV on OSX

You can concatenate output to CSV, like select email || ',' || firstname from account with column header turned off using set heading


MYSQL: How to select all attributes of all duplicate entries?

There are a few way of doing this and depends on the size of your initial table.

Option 1:

/** This gives you all the surnames that are duplicated **/
Create Temporary Table tmpData
(surname CHAR(100) NOT NULL,
counter INT NOT NULL DEFAULT 1,
Primary Key (surname));
Insert Into tmpData
Select surname, count(1) as recs
From (jasenet)
Group BY 1
Having recs > 1;

/** This gives you all the SINS that are duplicated **/
Create Temporary Table tmpData2
(sin CHAR(100) NOT NULL,
counter INT NOT NULL DEFAULT 1,
Primary Key (surname));
Insert Into tmpData
Select sin, count(1) as recs
From (jasenet)
Group BY 1
Having recs > 1;

Select DISTINCT A.*
From jasenet A, tmpData B, tmpData2 C
Where A.surname = B.surname
Or A.sin = C.sin;

Option 2 (Horribly slow -- never do)

Select A.userid, B.userid,
    IF(A.surname = B.surname, 1, 0) as sameSurname,
    IF(A.sin = B.sin, 1, 0) as sameSIN
From (jasenet A, jasenet B)
Where A.userid != B.userid;

Option 3 -- Probably what you're looking for

Select A.surname, group_concat(A.userid) as users
From (jasenet A)
Group By 1
Having COUNT(A.surname) > 1;

Select A.sin, group_concat(A.userid) as users
From (jasenet A)
Group By 1
Having COUNT(A.sin) > 1;

So I gave a few differences but there are a few things I should mention. You are using GROUP BY without an aggregate function which in general means you can have non-deterministic results. Also it's just not how you use group by. What I tell my developers is when using group by it's as if you're building a new table with X fields as the PRIMARY KEY of that table.

Do not use #2 as it has to basically do 1 full scan for every userid, I just wanted to give it to you so you can see that you can double link a table (as long as it's not a temporary table).

Option 1 Is similar to #3 but uses temp tables which are extremely fast and I figured you can then use the temp tables to do your linking on another table. Just remember, temporary tables are gone once you CLOSE the session.

Let me know if this is not what you were looking for


This seems to give you what you want.

--demo setup
DROP TABLE IF EXISTS DocumentTable;
go
CREATE TABLE DocumentTable
    (DOC_ID int, RENDITION_ID int, RENDITION_TYPE varchar(11))
;

INSERT INTO DocumentTable
    (DOC_ID, RENDITION_ID, RENDITION_TYPE)
VALUES
    (001, 001, 'Interactive'),
    (001, 002, 'Web PDF'),
    (002, 003, 'Interactive'),
    (003, 004, 'Interactive'),
    (003, 005, 'Print PDF'),
    (004, 006, 'Interactive'),
    (005, 007, 'Interactive'),
    (005, 008, 'Web PDF')
;

--solution
SELECT *
FROM DocumentTable a
WHERE RENDITION_TYPE = 'Interactive'
    AND NOT EXISTS (
        SELECT *
        FROM DocumentTable
        WHERE DOC_ID = a.doc_id
            AND RENDITION_TYPE <> 'Interactive'
        )

| DOC_ID | RENDITION_ID | RENDITION_TYPE |
|--------|--------------|----------------|
| 2      | 3            | Interactive    |
| 4      | 6            | Interactive    |


JOIN 3 Tables without Union ALL nor nested queries with multiple IDs

I will answer the question as asked but I expect that it won't solve your actual problem. You want to translate the query that you have in the other post into a query that can be turned into an indexed view. The biggest problem that I see is that your data model requires that data from multiple rows of the "fact" table be combined together into single rows. Such a requirement fights against the indexed view functionality. Many of the T-SQL restrictions almost seem designed around stopping that type of operation (along with others):

The most important entries in that list for your problem are the prohibitions on MAX, MIN, self-joins, subqueries, APPLY, and UNPIVOT. All of those can be used to rewrite your UNION ALL query but none of them are allowed in indexed views. There is a way to rewrite it but you'll just get stuck further down the line. You started with the following query:

SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  f.Description,
  NULL Disclaimer,
  Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DescriptionID
JOIN Status s ON s.StatusID = f.StatusID
UNION ALL
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  NULL Description,
  f.Description Disclaimer,
Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DisclaimerID
JOIN Status s ON s.StatusID = f.StatusID;

There is at least one way to rewrite that query in a way that's a valid indexed view definition. First create a dummy table with just two rows:

CREATE TABLE DIM_NUMBERS (NUM INT NOT NULL);
INSERT INTO DIM_NUMBERS VALUES (1), (2);

You can then define your view like this:

CREATE VIEW dbo.X_VIEW
WITH SCHEMABINDING
AS
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  CAST(CASE WHEN d.NUM = 1 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) [Description],
  CAST(CASE WHEN d.NUM = 2 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) Disclaimer,
  Main.Other
FROM dbo.Main
CROSS JOIN dbo.DIM_NUMBERS d
JOIN dbo.Fact f
  ON (f.FactID = Main.DescriptionID AND d.NUM = 1) OR (f.FactID = Main.DisclaimerID AND d.NUM = 2)
JOIN dbo.Status s ON s.StatusID = f.StatusID;

CREATE UNIQUE CLUSTERED INDEX IDX_X_VIEW
    ON dbo.X_VIEW  (ID, [Order], LanguageID, ClientID, [Description], Disclaimer, Other);

I have rewritten your query not to use UNION ALL but I did not include the GROUP BY because MIN and MAX are not allowed in indexed views. There aren't any aggregate functions that will help you here because you have an NVARCHAR column. If you had a numeric column then you could probably pull this off with a SUM(COALESCE(COLUMN_NAME, 0)) column but you can't apply the SUM aggregate to NVARCHAR.

Instead of trying to shoehorn a query into an indexed view definition I would try to think of other solutions. Could you just store the results in a table instead? Depending on the length of the description column the space difference between that and an indexed view might not be that large. Can you change the data model in some way that helps?


Mysterious missing transactional replication tokens

This type of behavior is usually caused by an uninitialized subscription. Make sure the snapshot agent ran successfully and the distribution agent is running too.

Check out level 10 of my Stairway to SQL Server Replication for more help on troubleshooting replication.


The best way to log access to SSRS reports

SSRS has Execution Logs for this. http://msdn.microsoft.com/en-us/library/ms155836%28v=sql.100%29.aspx


The PostgreSQL 9.3 Documentation has outlined how to alter default permissions. Follow this link to learn more!

Here is an excerpt that demonstrates how one would change the default permissions for a GROUP:

  ALTER DEFAULT PRIVILEGES
      [ FOR { ROLE | USER } target_role [, ...] ]
      [ IN SCHEMA schema_name [, ...] ]
      abbreviated_grant_or_revoke

  where abbreviated_grant_or_revoke is:

  GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
      [, ...] | ALL [ PRIVILEGES ] }
      ON TABLES
      TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

You may also benefit from learning more about ROLES (follow link to documentation here). That would make it easier if you ever needed to re-assign permissions to other people to.

  It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

A good place to do some practice/tutorial work on permissions, groups, roles, etc. is a site called Tutorials Point. They've got examples that will help you work through setting up permissions.


Import Oracle .dmp with error 1659, unable to allocate MINEXTENTS beyond 3 in tablespace SYSTEM

01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
//          for the segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with smaller value for MINEXTENTS, NEXT or
//          PCTINCREASE

As stated above you have two options, either increase the size of your SYSTEM tablespace, which might be easier or first extract the table DDL(CONTENT=METADATA_ONLY) from the dump file and alter the DDL script to change MINEXTENT to lower value and run the script to create tables then import the data(CONTENT=DATA_ONLY).

From comment...

  I'm afraid I don't. I do have this query in my clipboard history: ALTER TABLESPACE ADD DATAFILE but don't actually know how to use it or why is the SYSTEM schema being used here when I created the IMPORT_MAN user/schema

The SYSTEM mentioned in your error is a tablespace, not a schema. SYSTEM tablespace is used to store metadata of system and user objects such as tables under SYS schema. So, while importing the tables you need to have space on SYSTEM tablespace as well as in user's default tablespace where it stores the rows. To add datafiles, refer to this link ALTER TABLESPACE


Audit Plugin

MySQL Enterprise Audit is a new feature for security and compliance. It's not included in the Open Source / Community edition.

  As of MySQL 5.6.10, MySQL Enterprise Edition includes MySQL Enterprise
  Audit, implemented using a server plugin named audit_log. MySQL
  Enterprise Audit uses the open MySQL Audit API to enable standard,
  policy-based monitoring and logging of connection and query activity
  executed on specific MySQL servers. Designed to meet the Oracle audit
  specification, MySQL Enterprise Audit provides an out of box, easy to
  use auditing and compliance solution for applications that are
  governed by both internal and external regulatory guidelines.

have a look at the manual for more details.

By the way, MariaDB has its own implementation Audit Plugin. You can specify what exactly you wish to log.

CONNECT
QUERY
QUERY_DDL
QUERY_DML
QUERY_DCL

General Query Log

The general log is a quite old feature for the DBA and developer (to troubleshoot issues). The general log has a big performance impact and should be only enabled during short time slots.

  The general query log is a general record of what mysqld is doing. The
  server writes information to this log when clients connect or
  disconnect, and it logs each SQL statement received from clients. The
  general query log can be very useful when you suspect an error in a
  client and want to know exactly what the client sent to mysqld.

more details here.


You can define event triggers on many DDL events, but there is no event trigger for CREATE DATABASE.  This means you will have to include the access rights in the database definition.  From this point, there are two ways: one is again event triggers, the other is setting default privileges for the different object types you have.  I prefer the latter in any case, for being much cleaner (objects and privileges are defined the same way, that is easy to grasp for developers and other humans, too).

For an example how it could work, see one of my older answers, and there the ALTER DEFAULT PRIVILEGES bits.  It's about tables, but you can easily generalize it to other object types.


What privileges does the SQL Azure Migration Wizard need to copy a SQL Azure database?

From
Copying Databases in SQL Azure

Permissions

In order to copy a database in SQL Azure, your login requires the following permissions:

On both servers: The login must have the same login name and password on both SQL Azure servers.

On the destination server: The login must be a member of the server-level dbmanager role. Note: the server-level principal of your SQL Azure server is not a member of the dbmanager role, but automatically has the same permissions. For more information about managing logins in SQL Azure, see Managing Databases and Logins in SQL Azure.

On the source server: The login must be the DBO of the source database. Only the login that created the source database, the DBO, can copy that database to another database on the same server or on a different server.


Understanding many-to-many ternary relationship sets

In a diagram that included that, I would call that a simple foreign key constraint. It is likely this diagram is first draft\ over-simplification of how player classes are related to parties.

If I was pushed for a stronger answer, I would have it as a many-to-one (multiple of each class can be in one party) This means that a party can have one or multiple of any class of person, but each person can only be part of one party.

The confusion for this really comes from the fact that a class is not an object in itself, it is a characteristic of a person. If you think of the class as a characteristic of a person, now you have three people in the party. The logic behind calling the party complete would be to check if, of the people in it, there is at least one of each that has the characteristic of mage, fighter, and healer. To go one step further in this, a person's party is also a characteristic of theirs. This would allow multiple people to be assigned a party, but each person can only be assigned ONE party.


group by clause without aggregate function

This:

SELECT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700')
GROUP BY users.id

Finds all users who have a timesheet submitted on or before the given date. It's equivalent to:

SELECT DISTINCT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700');

or:

SELECT  users.*
FROM users
WHERE EXISTS (
    SELECT 1
    FROM timesheets
    WHERE timesheets.user_id = users.id
    AND timesheets.submitted_at <= '2010-07-06 15:27:05.117700'
);

It works because users.id is the primary key, so all other fields of users are functionally dependent on it. PostgreSQL knows that you don't have to use an aggregate to guarantee a single unambiguous result for each field in a row because there can only be one candidate users.name or whatever for any given users.id row.

(Older PostgreSQL versions didn't know how to identify functional dependencies of the primary key and and would throw an ERROR about needing to use an aggregate or include the field in the GROUP BY here).


Improving query to generate a million empty rows

To generate the 1 million rows for @tempT you could use a cartesian product of the master..spt_values table, like such:

declare @tempT table (number int)

declare @maxno int
select  @maxno = 1000000

insert into @tempT
select top(@maxno) row_number() over(order by v1.number)
from   master..spt_values v1,
       master..spt_values v2

select min(number),max(number),count(*) from @tempT

(No column name) (No column name) (No column name)
---------------- ---------------- ----------------
               1          1000000          1000000

Takes about 2 seconds to run this dbfiddle

NOTE: master..spt_values should have at least a few thousand records so a 2-way cartesian product should be sufficient to generate 1 million rows; if you need to go a lot higher you could provide an additional ,master..sp_values v3.

You could likely push this cartesian product into your insert receptdata / select query thus eliminating the requirement for the @tempT table variable, ymmv ...

The next (much bigger) performance issue is likely the 1 million not exists() operations.

Without knowing a bit more about the relationship between @recId and receiptdata.ffid it's kinda hard to address this query, eg, do you really need to test all 1 million @tempT rows against receiptdata?

Also, do you have an index on receiptdata the optimizer can use for the not exists() operations?


Storing many small - and a few large - strings in an RDBMS

You failed to mention your DBMS but for Oracle and PostgreSQL you should simply put everything into a single CLOB column.

The small values will automatically be stored "inline" and the large ones will be store out of line anyway. So the DBMS applies the logic of "switching" that you imply with your two column approach anyway.

I would be surprised if other DBMS aren't doing the same thing.


With partitioning you have a couple of additional options.

Switch older partitions to a separate table in the same database.  Query is still not transparent, but advantage of is primarily that a partition switch is a metadata-only operation, only requiring a brief schema lock and not generating a significant amount of log writes.

Partition and leave the older rows in the partitioned table, perhaps setting an 'archived' status column.  The older partitions can be compressed differently, and if there is some column in that differentiates the archived from non-archived rows and that column is universally used as a filter by the OLTP application, then you can create filtered indexes that differentially index the archived and non-archived rows.

And on SQL 2017 you have the simple option to make the table a Temporal Table.  You would continue to DELETE older rows, but SQL Server will copy the deleted rows to the history table, manage the retention of archived rows, and provide AS OF views across the main and history table.
BTW Table and Index partitioning is available in every edition of SQL Server 2016 SP1+.


getting the error message from another trigger in sql server

Based on the problem description, I tried to create a similar scenario including the solution. Hope this will help you.

Here I created tables and triggers.

Trigger trgEvents inserting the EventId into tbl_Position table if the EventId does not exists.

In the trigger trgPosition created on tbl_Position, I'm just validating whether column PositionId in tbl_Position is having a numeric value.

DROP TABLE IF EXISTS tbl_Events
GO
CREATE TABLE tbl_Events
(
EventId int PRIMARY KEY,
EventDescription varchar(512)
);
DROP TABLE IF EXISTS tbl_Position
GO
CREATE TABLE tbl_Position
(
EventId int REFERENCES tbl_Events(EventId),
PositionId varchar(8)
);
GO

DROP TRIGGER IF EXISTS trEvents
GO
CREATE TRIGGER trEvents
ON tbl_Events
FOR INSERT
AS
BEGIN
BEGIN TRY
IF EXISTS (select 'Exists ?' from tbl_Position P join inserted i on P.EventId=i.EventId)
    BEGIN
    UPDATE P SET PositionId=i.EventId+100 from tbl_Position P join inserted i on P.EventId=i.EventId;
    END
ELSE
    BEGIN
    INSERT INTO tbl_Position
    SELECt EventId,'NA' FROM inserted;
    END
END TRY
BEGIN CATCH
ROLLBACK;

DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 10);
END CATCH
END
GO

DROP TRIGGER IF EXISTS trPosition
GO
CREATE TRIGGER trPosition
ON tbl_Position
FOR UPDATE,INSERT
AS
BEGIN
IF (SELECT isnumeric(PositionId) from inserted)=1
    BEGIN
    Print 'PositionId has Numeric value'
    END
ELSE
    BEGIN
    RAISERROR ('PositionId has Non-Numeric value', 16 ,10)
    ROLLBACK;
    END
END

After I insert bellow execute the INSERT statement below.

INSERT INTO tbl_Events(EventId,EventDescription)
VALUES(1,'Dance compatition in New Delhi')

It fails and complete transaction gets rolled back.

(0 rows affected)
Msg 50000, Level 16, State 10, Procedure trEvents, Line 21 [Batch Start Line 62]
PositionId has Non-Numeric value
Msg 3609, Level 16, State 1, Line 63
The transaction ended in the trigger. The batch has been aborted.

Thanks!


CTE query doesn't print the right structure?

You need to include the parentID and the childIDin the path. So instead of

RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)

you should write

RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6) + RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10)

(That is in two places in your code)

Used this way you can provide the siblingOrder where desired. If present it will override the child order. If not present the children will be ordered by their childID.

SQL Fiddle without siblingOrder

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.MyTable
    ([childID] int, [parentID] int, [NAME] varchar(3), [siblingOrder] int)
;

INSERT INTO dbo.MyTable
    ([childID], [parentID], [NAME], [siblingOrder])
VALUES
    (1, 1, 'a', 0),
    (212, 1, 'ab', 0),
    (112, 1, 'ac', 0),
    (412, 1, 'ad', 0),
    (-912, 112, 'aca', 0)
;

Query 1:

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('0000000000' + CAST(parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(childID AS VARCHAR(10)), 10)  AS VARCHAR(1024))  AS PATH
   FROM   dbo.MyTable
   WHERE   childID = parentID
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10) AS VARCHAR(1024) )
    FROM   dbo.MyTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

Results:

|                                                                             PATH | DEPTH | CHILDID | PARENTID | SIBLINGORDER |    COLUMN_5 |
----------------------------------------------------------------------------------------------------------------------------------------------
|                                                       00000000010000000000000001 |     0 |       1 |        1 |            0 |           a |
|                            00000000010000000000000001.00000000010000000000000112 |     1 |     112 |        1 |            0 |      ----ac |
| 00000000010000000000000001.00000000010000000000000112.0000000112000000000000-912 |     2 |    -912 |      112 |            0 | --------aca |
|                            00000000010000000000000001.00000000010000000000000212 |     1 |     212 |        1 |            0 |      ----ab |
|                            00000000010000000000000001.00000000010000000000000412 |     1 |     412 |        1 |            0 |      ----ad |

SQL Fiddle with siblingOrder

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.MyTable
    ([childID] int, [parentID] int, [NAME] varchar(3), [siblingOrder] int)
;

INSERT INTO dbo.MyTable
    ([childID], [parentID], [NAME], [siblingOrder])
VALUES
    (1, 1, 'a', 0),
    (212, 1, 'ab', 1),
    (112, 1, 'ac', 3),
    (412, 1, 'ad', 2),
    (-912, 112, 'aca', 0)
;

Query 1:

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('0000000000' + CAST(parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(childID AS VARCHAR(10)), 10)  AS VARCHAR(1024))  AS PATH
   FROM   dbo.MyTable
   WHERE   childID = parentID
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10) AS VARCHAR(1024) )
    FROM   dbo.MyTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

Results:

|                                                                             PATH | DEPTH | CHILDID | PARENTID | SIBLINGORDER |    COLUMN_5 |
----------------------------------------------------------------------------------------------------------------------------------------------
|                                                       00000000010000000000000001 |     0 |       1 |        1 |            0 |           a |
|                            00000000010000000000000001.00000000010000010000000212 |     1 |     212 |        1 |            1 |      ----ab |
|                            00000000010000000000000001.00000000010000020000000412 |     1 |     412 |        1 |            2 |      ----ad |
|                            00000000010000000000000001.00000000010000030000000112 |     1 |     112 |        1 |            3 |      ----ac |
| 00000000010000000000000001.00000000010000030000000112.0000000112000000000000-912 |     2 |    -912 |      112 |            0 | --------aca |


Doubts on Setting up Master-Master replication (Percona)

Set up slave

You can set it up from the nightly backups. That's actually a common practice to provision slaves from backups to also have them tested. You can go back as far as you have binlogs stored on the master (expire_log_days variable).

The impact of having a slave starting from older backup is that it has to fetch the logs from the master but that shouldn't be a heavy operation. Unless your master is already on the edge you shouldn't really notice it. The other impact is that it will take longer for the server to catchup which is of course expected.

Switchover

Switchover can have some impact if you use auto-increment IDs and your passive master is not fully caught up with replication. Best is to make the old active read-only for a second or two while checking the passive for master position. Once there is no change you can move change it to be the active and make it writeable.

Depending how you point to the active master (DNS switch for example) it might take some time during which time both masters will accept writes and the old active being read-only will report errors.

I hope this helped.


Why does sp_whoisactive show locks on databases, but not tables?

It means someone has a connection open in that database. You can reproduce it by:

USE NOG_APP;
GO
BEGIN TRAN;
GO

And then checking sessions from another window:

sp_WhoIsActive @get_locks = 1;

If you think that's your biggest problem, though, it's probably time to check in with users and ask them about their biggest database complaints. I think you're probably barking up the wrong tree. I can only wish my biggest problem would be open sleeping transactions with nothing locked....


How to prevent or notice automatic connection retry in SQL Server and OLE DB

You can check if the temporary object exists using this:

IF OBJECT_ID('tempdb..#sometablename') IS NULL
    CREATE TABLE #sometablename ...

Another thing you could do is check the connection time for the current connection:

SELECT connect_time
FROM sys.dm_exec_connections
WHERE session_id = @@SPID

However, I'm having a hard time believing that the client disconnects without throwing errors. I would try to catch those errors first.


sql query to compare column name to column data

The question is somewhat unclear, but I'll assume that you are trying to get the values of one particular column.  And looking at the column names of a particular table, you want a list of all columns whose name is the same as the returned value. Then something like below might be a good start.

SELECT
    T.ColumnName, -- will show the value of the column
    C.Object_name([object_id]), --will show the name of the table
    C.name -- will show the name of the column that matched the column value
FROM
    SchemaName.TableName T
INNER JOIN
    Sys.Columns C
ON
    T.ColumnName= C.name
WHERE
    [object_id] = OBJECT_ID('SchemaName.TableName')

If you want to compare the value of the column to the column names in a different table or any tables, you simply need to change or delete the WHERE clause.


Performance Order By large table with Join

This is a classic performance problem. You are ordering on the columns of one table and are filtering on the columns of another.

SQL Server can either scan Data and filter out rows by seeking into the other table (the current plan). Or it can access Permission to determine the Data rows that pass the filter, and then sort this set. Both plans require a lot of data to be touched because the filter is too selective for plan 1 and to little selective for plan 2.

There is no index that is able to cover both the sort and the filter because the columns belong to different tables.

Consider creating an indexed view that contains all the columns used here. This will result in near-perfect performance because you can now index all required columns.

Or, denormalize your data model. Store permission information directly in the Data table.


Party Relationship Model in Practice - Insertions

How about another answer?  Get rid of the Party table entirely.  I'm a passionate hater of the Party model as it causes vastly more problems than it solves.

However, since it's unlikely you're in the position to do such a thing:

  I can do two consecutive queries: One into the parties table, then one
  into a sub table.

There's nothing per-se wrong with this as long as you do your transaction control in your app.  It's not what I would suggest though.

  This answer mentioned moving columns into the super table and using a
  trigger after insert on the super table to insert into the sub table.

This is nasty from a model perspective.  If you go this route you have attributes that apply only to organizations intermingled with attributes that are only for users.  It becomes quite a mess and you just 'have to know' what is right and wrong.  It is a significant impediment to clarity.

  I can create a stored procedure in order to perform multiple queries
  in one transaction.

This is your best option.  It maintains atomicity while at the same time encapsulating and decoupling the data access from your app.  Do this.

UPDATED:

To clarify my point about the Party model.  The following are my major beefs with the approach:

There is no such thing as Party.  That is, there is no natural key that identifies a 'Party' in real life.  As such, it is a meaningless abstraction that leads to ...
Models inevitably base everything off Party.  Foreign keys always back to Party rather than the 'subclasses' (that aren't really subclasses).  The perception is that this makes matters simpler because it avoids n:n relationships along with intersection tables and so forth.  However, it ends up completely hiding what the model actually is.  Some things are related ONLY to User and some things are related ONLY to Organization.  Nothing is ever related to a 'Party', in reality.  So it is a physical 'optimization' that results in extreme logical confusion. It becomes completely impossible to determine the model by simply reading it.  You just simply have to know that certain things are allowed and certain things aren't.  Inevitably this ends up in model confusion, code mistakes/complexity, report inaccuracies, etc.  It also leads to other evils like not taking keys seriously.


How to use the covariance and variance in PostgreSQL

There is an answer on https://stackoverflow.com/questions/3015846/calculating-correlation-coefficient-using-postgresql that is relevant for you.

Corr(y,x) is pretty easy. Say you have a table of people with columns height and weight. You wanted to know if those were correlated.

select corr(height,weight) from people;


When is it worth it to use Triggers and stored procedures/functions?

Triggers are problematic because they tend to get forgotten about.  I only ever use triggers for auditing purposes such as updating a "last updated datetime" column.  Stored procedures and functions can be used at any time since they are called directly by processes wishing to modify data -- as opposed to triggers that are fired as the result of data being changed.

If you are building queues to do some of the work, I suggest you also use queues to create your posts as well.


Temp DB is growing continuously in SQL server

If you do a quick search on this same site, you will find lot of similar questions. Basically, some process is taking to long and is making your temdb grow until you run out of space. According to Brent Ozar "Tempdb is like a public toilette", everyone (all kind of processes) use it and usually to do filthy things.

What you should do is locate what is making your tempdb grow, search that root cause and solve it. If you don't do that, you will still deal with continues issues like this one. You can, as a temporal solution, run a shrink on the tempdb to free some space. Please please avoid having an automated job to do shrinks.

You have several options to find the cause, running a SQL Server Profiler trace to track down Tempdb activity. Also you can use sp_whoisactive to log activity on the server while whatever causes the issue is running, so you can check that log afterward and see possible queries or processes that cause it. Kendra Little has a full blog entry on how to do this.

For some specific code samples, look here and here. Those are just some samples, as commented on first line, this a common issue, so you should find tons of useful answers here on Stackexchange site.


MariaDB Galera stop working without network connectivity

Galera can't do that, unfortunately, although you can configure it to handle shorter network outages. (See Galera's config tips for WAN replication.) Nodes have to be connected for Galera's global ordering to work so that it can merge writes from multiple nodes. See e.g. here

Maybe what your application could do instead if a node loses network and becomes unavailable (which could be detected by the application or a DB proxy) is to automatically fail over to a different cluster node. However, that still requires network connectivity to this other node.

So if your architecture needs to be tolerant against network losses between nodes, then you may have to look for a different solution.


Timestamping each row doesn't seem to make a lot of sense to me. I would just keep a log of the events that are interesting. Such a log could be reusable for other events too. E.g. (sorry this is SQL Server dialect, you may have to adjust slightly):

CREATE TABLE dbo.EventLog
(
  EventDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  [Description] NVARCHAR(2000)
);

Whenever you reload the table, also call a stored procedure that does this:

INSERT dbo.EventLog([Description])
  SELECT N'Re-populated table x';

If you need to know the date of the last refresh, you can say:

SELECT MAX(EventDate) FROM dbo.EventLog
  WHERE [Description] = N'Re-populated table x';

If all you ever care about is the last time, then before the insert above, you could just:

DELETE dbo.EventLog
  WHERE [Description] = N'Re-populated table x';


You can use the following code to get the date for any given day in the current week:

DECLARE @DesiredDay INT = 0;
DECLARE @OutputDate DATE;
SET DATEFIRST 1; /* Monday is day 1 */
SET @OutputDate = DATEADD(d, @DesiredDay - DATEPART(DW, GETDATE()), GETDATE());
SELECT @OutputDate;

I'm setting @DesiredDay to 0 since you requested Sunday in your question.  For Monday, set this to 1, for Tuesday set it to 2, etc.


Basically Loopback linked servers are mainly useful when testing an application that uses distributed queries on a single server network.

Note that they cannot be used in a distributed transactions.

In terms of performance, there will be performance impact when you do selects or joins using LOOPBACK server as all the data has to go across wire.

For Linked Server - Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

Bottom line is linked servers are evil in terms of performance. Unless you really need them, dont / avoid using them.

For e.g -- Consider a basic example of selecting some data from a table

-- taken from : Aaron's article : "Best approaches for running totals – updated for SQL Server 2012"
-- http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals
CREATE TABLE dbo.SpeedingTickets
(
    [Date]      DATE NOT NULL,
    TicketCount INT
);
GO

ALTER TABLE dbo.SpeedingTickets ADD CONSTRAINT pk PRIMARY KEY CLUSTERED ([Date]);
GO

;WITH x(d,h) AS
(
    SELECT TOP (250)
        ROW_NUMBER() OVER (ORDER BY [object_id]),
        CONVERT(INT, RIGHT([object_id], 2))
    FROM sys.all_objects
    ORDER BY [object_id]
)
INSERT dbo.SpeedingTickets([Date], TicketCount)
SELECT TOP (10000)
    d = DATEADD(DAY, x2.d + ((x.d-1)*250), '19831231'),
    x2.h
FROM x CROSS JOIN x AS x2
ORDER BY d;
GO

SELECT [Date], TicketCount
    FROM dbo.SpeedingTickets
    ORDER BY [Date];
GO

--- select data as normally you would do

select [Date], TicketCount from test_kin.dbo.SpeedingTickets

Execution plan will have a clustered index scan

-- select data using loopback server (highly inefficient). Look at the CPU & Operator cost (though this is a simple example, but test out on a good data set with multiple joins, etc - you will clearly understand the difference - as to how expensive it is to use Linked (in this case loopback server))

Read up on :

Which one is more efficient: select from linked server or insert into linked server?
How to create an autonomous transaction in SQL Server 2008
CSS article - If you use linked server queries, you need to read this….
Bad database practices: abusing linked servers


is there any way to collect tables related to certain part in a folder inside my DB?? ..so i have group of folders each one contain tables of certain part of my application

The direct answer to that within SSMS would be no.

However, there are two ways you can go about this:

Use schemas to contain logically connected and functional objects
Use a BroadToSpecific naming convention to alphabetically group objects (I.e. Person, PersonAddress, PersonAddressArchive, etc.  These will appear alphabetically grouped)


Main thing to watch altering tables is the index length will increase for utf8 indexed columns which might put them over limit. Adjust URL for your mysql version as some differences apply. These index length exceeded errors will show up immediately on ALTER TABLE so this will be a final bit of the migration.

On validation, generally won't be a problem. Try this on a few tables to get confidence in the idea confidence on smaller tables:

   CREATE TABLE tbl_new LIKE tbl
   ALTER TABLE tbl_new ..  {change to utf8mb4 fields}
   INSERT INTO tbl_new SELECT * FROM tbl
   RENAME TABLE tbl_new TO tbl, tbl TO tbl_old

A SELECT ... tbl JOIN tbl_old ON tbl.pk = tbl_old.pk WHERE tbl.utf_field != tbl_old.utf_field can be used to validate values.

Application behavior can be checked, and you can RENAME back if their are problems. (taking to account updates).


How do I move tables and indexes owned by user to new tablespaces in Oracle 19C?

You can also use simple ALTER TABLE/INDEX command
for aTab in (select table_name from dba_tables where owner = 'SDE') loop
   execute immediate 'ALTER TABLE SDE.' ||aTab.table_name|| ' MOVE TABLESPACE SDEBUS_DT';
end loop;

for aInd in (select index_name from dba_indexes where owner = 'SDE') loop
   execute immediate 'ALTER INDEX SDE.' ||aInd.index_name|| ' REBUILD TABLESPACE SDEBUS_IX';
end loop;

But DBMS_REDEFINITION.REDEF_TABLE is more versatile than ALTER TABLE/INDEX, e.g. when user has partitioned tables.


WCS8 Solr Preprocess failed : Error SQLCODE=-601, SQLSTATE=42710, SQLERRMC=WCSSTGAP.XI_CATENTRY_PRICE_0

The -601 happens because table WCSSTGAP.XI_CATENTRY_PRICE_0 already exists , so it cannot be created again while it exists.

Either drop that table manually and retry, or check back in the log file(s) to find if a previous DROP TABLE WCSSTGAP.XI_CATENTRY_PRICE_0  did fail (and then solve that problem first). You will need to find root cause.


How to copy data from one database to another database efficiently?

What about using one of the existing ETL tools? Talend ETL or Pentaho Data Integration are two examples (performance-wise PDI is better).
You can design an ETL job then schedule to run it as you like.


Charindex, Getting specific data from a string

I use charIndex and patIndex to resolve it.

CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3
    THEN RIGHT(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1),3)
    ELSE NULL END
    AS NewNo,
CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3
    THEN SUBSTRING(Descr, PatIndex('%, [0-9][0-9]% Stage%',Descr)+2,3)
    ELSE NULL END
    AS NewNo2

With the output:

NewNo NewNo2
----- ------
 98   98
111   111
125   125
134   134

Some explanation on it:
I used to CharIndex(' Stage', Descr)to find the position when starts ' Stage'.
Then I use SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1) to cut the text so that your number will be in the right part. (like this :Playhouse, Virginia Series, 98).Then you can use different technique (right,another charIndex,reverse) to obtain the Number.


PWDENCRYPT is kind of deprecated, so no, you shouldn't be using it now, and you certainly shouldn't expect it to work indefinitely in any fashion - deterministic or not. From the documentation:

  PWDENCRYPT is an older function and might not be supported in a future release of SQL Server. Use HASHBYTES instead. HASHBYTES provides more hashing algorithms.

I don't know that anybody can answer your second question. How do you define "random enough"?

Also, just to be clear, NEWID() and RAND() are disallowed in functions not because they are non-deterministic, but rather because they are considered "side effecting" - even though there is nothing you or I could assume about how these functions affect anything, other than incrementing some internal random number sequence generator.

And there are easy workarounds to that, anyway - e.g. create a view and have the function reference the view, pass NEWID() into the function, etc.

In addition to PWDENCRYPT(), other functions that are also obviously not deterministic (such as GETDATE(), SYSUTCDATETIME(), CURRENT_TIMESTAMP, and others) are allowed. Example:

CREATE OR ALTER FUNCTION dbo.what()
RETURNS int
AS
BEGIN
  RETURN (SELECT @@DBTS + @@CPU_BUSY + @@CONNECTIONS
    + DATEDIFF(SECOND, GETDATE(), SYSUTCDATETIME()));
END
GO

SELECT dbo.what();

The function will be marked as non-deterministic (which you can confirm with OBJECTPROPERTY()), which can limit how the UDF is in turn used, but it isn't the case that "non-deterministic functions are not allowed inside user-defined functions." Even though that's what a lot of people have written, it isn't strictly true.

In fact, RAND() is documented to be deterministic when a seed is provided; RAND(5); is demonstrably deterministic. But it still isn't allowed in a function (this returns the same side-effecting error):

CREATE FUNCTION dbo.who()
RETURNS decimal(20,19)
AS
BEGIN
  RETURN (SELECT RAND(5));
END
GO


UPDATE to linked server (Pervasive Database) is failing using OPENQUERY

This issue is caused by upgrading SQL Server from SQL2014 to SQL2016 or later.  The Microsoft OLE DB Provider for ODBC Drivers has changed.  It now has more ISO and other ODBC features support.  This is a breaking change for some ODBC connections.

This driver (MSDASQL) is used to wrap an ODBC connection on the server, in this case, to the pervasive database and allow it to act as a linked server.  The wrapper allows both OPEN Query functionality as well as standard linked query calls.

The issue with the change is that the new wrapper is sending extra data that is not being handled if you are using ODBC driver versions 11 or below for query activity.  It can not handle the additional information and will return an error on execution.  It will be able to connect, however.

Version 12 of the pervasive ODBC driver will work for query only, but Open Query will not work. You will continue to get "unexpected catastrophic failure" on open query updates.

I recommend you upgrade to version 13.3.  This release from Actian will support both selects and updates and will solve the Open Query error. I do not know if you will need  support from Actian for the licensing of the driver.


Can MySQL use an index to calculate SUM?

Please note that Extra: Using Index doesn't mean that an index is used- you know that because of the columns type:eq_ref and key:PRIMARY. Using index in reality means that you are using the Covering index optimisation to access data.

In your case, you are using and index for all 3 table accesses. If you wanted also the Covering index optimisation, you could create an index on (document.id, document.size), but unless that table is very large and this query is very frequent, you would not see a big difference (in cases where you are disk bound). The index on (document.size), effectively, contains the primary key, but it cannot be used, because it is hidden after the size column. In order to be used for filtering, it must in the first place. Also, in MySQL/Percona 5.5, the hidden primary key can only be used for certain sorting and covering index operations, not for filtering. This last limitation is solved in MySQL/Percona 5.6.

You biggest problem is that your query has to read 50k rows and add them (an index won't solve that). With the above tricks you can make the calculations mostly in memory (assuming a big enough buffer pool), but you still have to sum every single value. You could maintain a duplicated customer_id on document -if that makes sense (that is denormalizing, too)- to avoid 2 table accesses.

But your initial idea of denormalizing, maintaining the value precalculated -can be maintained with a trigger-, or using any other kind of cache -query cache, with all its problems, external cache like memcache, etc.- is probably the way to go if you cannot handle such latencies.


Is there a more efficient way of using a match score in MySQL full text search?

Actually the operation is not performed twice. See the manual:

  The following example is more complex. The query returns the relevance
  values and it also sorts the rows in order of decreasing relevance. To
  achieve this result, specify MATCH() twice: once in the SELECT list
  and once in the WHERE clause. This causes no additional overhead,
  because the MySQL optimizer notices that the two MATCH() calls are
  identical and invokes the full-text search code only once.

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

Update

Notice that you do not need to sort explicitly though:

  When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first.

If you do it anyway, the optimizer will not notice that it is unnecessary and will sort again using filesort, which would add overhead.


Transaction Log Shipping across multiple SQL Server versions

I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server.

Let me tell you transaction log shipping is nothing but trasfer of log records from one server to another its quite light and you can create your own 'logshipping' by creating backup,copy and restore jobs. What you are trying to do is one way possible may be using TSQL. You are making SQL Server(SS) 2005 as primary and SS2012/2008R2 as secondary this can be achived but think of a scenario where 2005 database server fails due to any reason and you bring SS2012/2008R2 online all is fine now BUT would you be able to bring SS 2005 online with updated data from SS2012/2008R2, you cannot because higher version backups cannot be restored on lower version database. So this would lead to data loss which I guess you dont want. So I would advise you not to go for this scenario.

I would say upgrade your SQL Server 2005 to 2008 R2 or SQL Server 2012 this is the best thing to do. In all cases of High availability version mismatch can cause you problem. Having said that if this is immediately not possible, as per RTO,RPO, schedule a proper backup of SQL Server 2005 database. Of course configuring logshipping with 2005 as secondary is also good.

EDIT: After User asked below

  I want to avoid large backup and restore cycles which will be left to weekends and overnight if I can't achieve this.

Well in such case you have differential backups a very nice way to lower down RTO. This will surely decrease your total time taken to restore all backups after disaster. The restore sequence would be full backup followed by LATEST differential backup followed by all log backups in sequence taken after latest differential backup. Suppose you do daily full backup at 12 AM diff backup every 4 hr and every 1 hr log backup. Lets say database faied at 3:50 PM. You would restore

1.Full backup at 12 AM.

2.Differential backup at 12 PM

3.Log backup taken after 12 PM differential backup ( 1PM, 2PM and 3PM log backups) I dont think it would be big deal.

This is minimum you have to do Logshipping is not a solution here. You can create your own script to automate the restore process.

There are file system backups which can also do this job, they take file system backup of mdf anf ldf file and after disaster you just need to attach those mdf and ldf files but poiint in time recovery is not possible with file system backups


You should first attempt executing:

ALTER DATABASE [{db_name}] COLLATE {new_collation_name};

If you get errors, are they something simple to remove, then change the collation, then re-create, such as a small number of functions? If so, then do that.

If the errors related to items that cannot be easily changed, or are far too many things to change, then the next easiest way is the undocumented sqlservr.exe -q method. That does a simple meta-data change of the collation_id of each relevant column (relevant = column that can be changed; some system columns cannot be changed). This can lead to data corruption in VARCHAR data if the code page changes, but in your case the code page is not changing. I have documented what this command does here:

Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

If the instance has several databases on it that will not be changing, and/or if the instance-level collation itself will not be changing, then you can:

Create a temporary instance of the same version of SQL Server (be sure to specify a collation that is not the collation of the target instance where this database will ultimately be restored to).
Restore this one database to that temporary instance
Use the sqlservr.exe -q method to update this temporary instance to the instance being used by the target instance.
Backup / Detach this database from the temporary instance.
Restore / Attach this database into the target instance.
Assuming everything works as desired, stop and uninstall the temporary instance.


Query intermittently fails with "The multi-part identifier could not be bound"

If it's just one row, how about:

UPDATE LocalServer.DB.dbo.table
SET [ChangeStatus] =
(
  SELECT [ChangeStatus]
  FROM RemoteServer.DB.dbo.table
)
WHERE LocationID = '1';

Now you've eliminated the join and any aliases that have to be bound. Also why are you referencing both with 4-part names? If the local server is really the same instance, why not reference the local as DB.dbo.table instead of LocalServer.DB.dbo.table? If you take out the four-part name on the outer table, you can also do this for multi-row updates. It's not as pretty to do the aliasing this way, though:

UPDATE DB.dbo.table
SET [ChangeStatus] =
(
  SELECT [ChangeStatus]
  FROM RemoteServer.DB.dbo.table
  WHERE key_col = DB.dbo.table.key_col
)
WHERE LocationID = '1';

And as we discussed offline, if it's multiple rows and multiple columns, you should use something like this:

UPDATE t
SET [ChangeStatus] = r.[ChangeStatus],
    [OtherColumn]  = r.[OtherColumn]
FROM DB.dbo.table AS t
INNER JOIN RemoteServer.DB.dbo.table AS r
  ON t.key_col = r.key_col
WHERE t.LocationID = '1';

I believe that proper aliasing and eliminating the 4-part name should cause less work for the relevant parts of the execution process.