SQLServer Inconsistency between changing values for columns vs variables

It is just how SQL Server works. More specifically, how its UPDATE statement works.

One SELECT statement with variables in your example is equivalent to three separate SET statements.

SET @b = @a + @c;
SET @a = 5;
SET @c = @b - @a;

The calculations here are sequential. The order in which you write these lines is important as in traditional programming language.

UPDATE statement is not sequential in this sense. It reads current values of all columns of a row into memory (internal variables behind the scene) and uses these cached values in calculations. These cached values are not changed. The final result is written back onto disk in one go, not column by column.

I think, the most vivid example would be an example how you can swap values of two columns with this statement:

update #CalcTest
set  b = a,
     a = b

In traditional procedural languages you have to use third variable to store the value:

temp = a;
a = b;
b = temp;

UPDATE in SQL Server has this variable implicitly. I don't know how other DBMSs implement UPDATE, but at least Postgres and Oracle seem to work in the same way as SQL Server. While MySQL not.

EDIT:

Before I mark this as the correct answer I will clarify and say that it's not so much how updates work but that it is just how columns work and, no, we can't change or control it.

My interpretation from this and other research elsewhere is this:

Columns are updated atomically in SqlServer. They get their new values all at the same time at the conclusion of the statement. Hence no partial updates or updates of one and not the other. All or nothing.
Variables (even in the same statement as columns) are not updated atomically. They get their new value part way through the statement one after another and their changes can feed into each other as they go. This is just like most programming languages.
Also, SqlServer will not guarantee the order in which variables are updated in a Select (and I suspect the same is true of Updates). This is probably due to the optimiser and how it schedules the cost of complex instructions like sub-selects. Simple stuff like that presented here has never (as far as I can see) been processed in anything other that the order it's written in. Just don't rely on that order.
Microsoft recommends using the Set command to set variable values where that statement does not refer to tables or views, i.e. like those presented here. However, you can't chain up variables in a Set like you can in a Select. Also, Microsoft say this and then go right ahead and continue using Select to set variables in their examples. :-)


Where is it best to store a reference of the user-selected default?

It doesn't matter.
As you have to get the message from the second table, you always have to inner join it , and add a where clause, to get the right one.
So the only thing would be size.
A boolean needs 1 byte.
Your default_user_address_id would be bigint which needs 8 Bytes, so as most people have only 1 address or maybe two, you would save space with Booleans.
So my vote would be for number two, as it would save size.


Is pre-sorting valuable when using table value constructor to insert multiple values into a table with clustered index?


How to query time specific performance in postgres?

I don't know of a canned tool to do this.  But you can easily create a table which has the same columns as pg_stat_statements and an extra timestamptz column, then have a scheduled task take a snapshot of pg_stat_statements into this table every 2 hours.  Then you can take the differences between adjacent snapshots of the same query.


MongoDB Replication initial sync fails with OplogStartMissing

How big is your oplog?

Could it be that the oplog is filling up and overwriting the operations since the sync started before the initial sync has finished?  If this is the case, the new secondary will never catch up. I had this error once before, but can’t recall the exact error now.

Check the oplog start / end points by using

rs.printReplicationInfo()

If this is the case, either increase the oplog size or pause the application (if possible).

I’ll dig through an old ticket and check the error I had with a sync failing.


How to use variable and drop in test script?

drop, along with create, alter, truncate etc is a DDL (Data definition Language) statement. DDL is not allowed in PL/SQL blocks. Your script is a PL/SQL block (it has begin and end, along with variable declarations).

The way to work around it is to use execute immediate:

declare
myTestId VARCHAR(32);

begin

select  '23423DSSF34SD234' into myTestId from dual;

--Here my drop for my_test_table1 and my_test_table2
execute immediate 'drop table my_test_table1';
execute immediate 'drop table my_test_table2';

--Here create table my_test_table1 and my_test_table2

--Here I have a bunch of inserts

--Here are some selects statements

end;

Be aware that executing DDL in a PL/SQL block in this fashion will implicitly perform a commit. Any errors (such as ORA-00942: table or view does not exist) will also abort the script execution, though there are ways around this. You could first query the ALL_TABLES data dictionary few to check if the table exists before attempting to drop it, for example.

Variable use is massive subject... If you have a specfic question about it, you'd be better off asking a new question on the site.

As far as creating temporary tables are concerned, this is different than creating a true temporary table, as frequently used in Sybase and SQL Server - don't worry about it, your approach is fine for test scripts.


best way to create multiple similar tables - CTAS vs INSERT

Use execute immediate to make creation of many very similar tables easier.

A little PL/SQL demo:

SQL>
declare
table_number number;
ctas_sql varchar(4000);
begin
  ctas_sql:='select sysdate as foo from dual';

  for table_number in 1..10
  loop
    execute immediate('create table foo'||table_number ||' as ('||ctas_sql||')');
  end loop;
end;
/
SQL>
PL/SQL procedure successfully completed.

SQL>
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
FOO1
FOO2
FOO3
FOO4
FOO5
FOO6
FOO7
FOO8
FOO9
FOO10

10 rows selected.

SQL>

The create table statement can contain anything you want - the above is just showing that the statement can be dynamic. So, in your case you'd have simple logic to alter the TEAM_ID and table name for each statement.

Bind variables will offer no gain unless you're executing the same single query multiple times (extremely simplified for the sake of this answer) - in this case you're executing many different queries that are performing large bulk inserts.

Creating tables with NOLOGGING is not advisable if this is a production system. Also be aware that not logging and /*+APPEND*/ can have backup/restore implications. This Ask Tom thread may be of particular interest.

To clarify, using CTAS is the way to go. You only have to parse the CTAS statement once to insert your millions of rows. If you use individual INSERT statements you have to parse each statement over & over again, which adds a massive overhead!


Mixed based replication between two databases on the same server

Have two instances of MySQL on that one server.  Each has its own data directory, its own port (3307 is typically used for the second one), each has its own my.cnf and logs.  But, since they share the RAM, you need to cut back many tunables relating to RAM.

Why?  What do you gain?  Certainly not HA.  You may be able to better use lots of Cores.  But you are doubling the "writes" on that machine (since the 'Slave' is repeating the writes).


Clustered indexed fields hash matching. Does it matter?

SQL Server is very good at deciding the best, most efficient execution plan for any given query.  Since you have no predicates, such as a WHERE D500M.PERSON_REF = 123456, SQL Server is most likely assuming an index scan is the quickest way to gather all the relevant details from each table.  If you add a predicate to the query, you will most likely see an index seek instead.  This is to be expected.

"Good, bad or irrelevant" is only something you can decide.  Is the performance acceptable for your needs?

You could try forcing the join type to see what happens if a loop join or a merge join were used instead.  Something like:

SELECT p.PERSON_REF, p.surname, p.first_forname, e.employee_number, ph.MAIN_FLAG
FROM D500M p
INNER LOOP JOIN D550M e ON e.PERSON_REF=p.PERSON_REF
INNER LOOP JOIN D580M ph ON ph.PERSON_REF=p.PERSON_REF

If you want a more conclusive answer, you should provide code for a minimally complete verifiable environment that matches your environment. i.e. provide us table definitions, the actual queries involved, and the actual query plans you're seeing.


As to why you do not get an index scan with SET enable_seqscan = off, you should be getting an index-only scan. Your situation can not yet be recreated with the data you've provided. This certainly works with PostgreSQL 10.4. I can't speak to your own use case, and there are a lot of reasons why you may not get an index scan in the real world. Ultimately, debugging a question along those lines will result in an answer that is simply "planner estimates" but will require a lot more data about your environment, configuration, and the plans with and without SET enable_seqscan = off.

Sample Data

BEGIN;
  CREATE TABLE foo ( x int NOT NULL UNIQUE );
  INSERT INTO foo (x) SELECT generate_series(1,1e6);
COMMIT;

ANALYZE foo; -- don't forget to analyze

With seq_scan

test=# EXPLAIN SELECT count(*) FROM foo;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=10633.55..10633.56 rows=1 width=8)
   ->  Gather  (cost=10633.33..10633.54 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=9633.33..9633.34 rows=1 width=8)
               ->  Parallel Seq Scan on foo  (cost=0.00..8591.67 rows=416667 width=0)
(5 rows)

Notice we're doing a "Parallel Seq Scan"

Without seq_scan

SET enable_seq_scan = off;

test=# EXPLAIN SELECT count(*) FROM foo;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=26616.97..26616.98 rows=1 width=8)
   ->  Gather  (cost=26616.76..26616.97 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=25616.76..25616.77 rows=1 width=8)
               ->  Parallel Index Only Scan using foo_x_key on foo  (cost=0.42..24575.09 rows=416667 width=0)
(5 rows)

Notice we're doing a "Parallel Index Only Scan"


Harden a public login to only let it retrieve a list of other logins, and do nothing else

"Most secure" is an ambiguous statement that needs more explanation. You need to think about what kind of attack vectors you are trying to mitigate.

For example, what if a malicious user trys to brute-force your built-in account? It's going to be locked out. This creates a denial of service scenario, as normal users cannot receive a list of valid accounts either. If you were trying to mitigate a DoS scenario by using hard-to-guess logon names, you just shoot your own foot.

Hard-coding a password in your application will make it insecure. First, it can be extracted from the application, no matter how you encrypt it. That's exactly the way Blu-Ray encryption was broken. Second, should you need to change the password, you need to ship new binaries to all the users which is somewhat a hassle.

How about creating a SSIS package that publishes a list of valid logins on a network share? This moves some security configuration from the database to your AD group membership maintenance.


SQL Server Configuration File and InstanceName parameter

Will /InstanceName="TestSQLServer" parameter override what we have in
the configuration file (/InstanceName = MSSQLSERVER (default
instance)), and install SQL Server as a named instance "TestSQLServer"
?

Yes, per the below excerpt from the configuration file documentation :

The configuration file overwrites the defaults in a package

Command-line values overwrite the values in the configuration file


Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

If you cannot use OPENROWSET, you could create a loopback linked server and use OPENQUERY or EXECUTE AT.

DECLARE @srv nvarchar(4000);
SET @srv = @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server     = N'LOOPBACK',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
@datasrc    = @srv;

-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'LOOPBACK',
@useself     = N'True',
@locallogin  = NULL,
@rmtuser     = NULL,
@rmtpassword = NULL;

I blogged about it some years ago.

Another option is using some kind of CLR trickery to interpret the result set definition and create a target table based on the type and length of the columns.

If you're not allowed to use OPENROWSET, I highly doubt you will be allowed to use CLR, though. In this case, a third option could be using sp_describe_first_result_set and code your table creation script in T-SQL. It requires SQL Server 2012 or newer.


listener does not currently know of SID given in connect descriptor in oracle 10g and solaris 10 env

There are a few problems.

 given the fact that you specified local_listener, you want your database instance to register to a non default listener for which the tnsalias should be LISTENER_OTRS
 the tnsalias LISTENER_OTRS is missing
 the tnsalias otrs is misformed (it has multiple addresses but not the ADDRESS_LIST parameter (in this case, the last piece will be ignored))

fix: remove the local_listener parameter and your instance will register perfectly fine on the listener that sits on port 1521 (the default port). The instance will register during startup or, if the listener is started later, once every few minutes or after issuing 'alter system register;'.

If you did this, lsnrctl status will show the instance name.
Also: don't forget that on *nix, the ORACLE_SID is case sensitive.

The otrs tnsalias could be used to connect to the instance, once it has registered itself to the listener. You can test this using 'tnsping otrs'


How to design DB for statistics service?

I'd recommend you to make a separate table for each attribute. Like so

table impressions
-channel_id
-date timestamp
-other attributes like ad_id

table clicks
-channel_id
-date timestamp
-ad_id

For every event you just make an insert, to show stats it is also very efficient - it is just index scan with count or aggregate function.
Also this approach scales well - you can easily shard such tables. Or replace it with nosql solution. For such a task i'd recommend HBase - this datamodel naturally translates in HBase schema, and HBase is very good at range scans.

UPD: I see you updated qustion, so i need to clarify.
Basic tables which still usefull to store Facts. Over them you can immediately make graphs and reports. If you concern about performance, people usually preaggregate data. So if your people need 5/10/30 minutes resolution, you aggregate into 5/10/30_minute_table for fast reporting.

This batch processing of old data takes time. If it doesn't fit your SLA, modern guys take CEP engines like twitter Storm to analyze and aggregate this table in real time. Or you can just buffer events for a minute, aggregate them and then store. I don't know how RoR fits this role and i don't know your requirements. But i think thats another story.


You will need to disable interactive mode in the package. The answer to this question on Stack Overflow should point you in the right direction and the documentation for this property on Tech Net can be found here.

Alternatively, try right-clicking on the solution, Properties -> Configuration Properties -> Debugging -> Interactive Mode. Set this to false.


[very good] Do BULK insertions instead of many single insertions -- Yes.  Inserting 100 rows in a single INSERT can run 10 times as fast.

[good] Use transactions -- Multiple single-row INSERTs save some by doing this.

[it depends] Increase innodb_log_file_size -- There are metrics in GLOBAL STATUS that would help determine the utility of this.  (Your 768M is probably big enough.)

[minor] char(64) -- Is it truly fixed length?  If not, use VARCHAR.  If it is just ascii, then say so.  If it is hex, then consider using UNHEX() and putting it into BINARY(32).  Shrinking the size of the table always helps, but usually by only a small amount.

[safety] max_connections=10000 -- Shrink to something reasonable.  If you ever hit 10000, the system would (1) really bog down, and (2) swap, which would be terrible for performance.

[?] innodb_buffer_pool_size=240G -- This implies that you have at least 300GB of RAM?  How big is (or will be) the table?

[RAID] 4 drives just Mirrored?  No striping?  Hardware (better) vs software controller?

[2 unique keys] Each row must check both before committing.  Is the CHAR(64) a hash of some kind -- hence "random"?

[stumbling] If you have too many clients, they will just stumble over each other.  Your image show a rather long list.  (I don't have a good number for where InnoDB stops improving and starts stumbling.  But it sounds like you have too many.)

The first item will probably be the most productive.  But we can discuss the rest.


How would you normalize a database that has multiple products to a single transaction ID?

Might as well make my comment an answer so you can close it:

A Sales Order has many Sales Order Line Items. One table for Sales Orders, one for Sales Order Line Items with a foreign key to the former

create table sales_orders (
  id int primary key,
  order_date date,
  ...
);

create table order_items (
  order_id int references sales_orders(id),
  line_number smallint,
  unit_price decimal(19,4) not null,
  quantity smallint not null default 1,
  product_id int not null references products(id),
  ...

  primary key (order_id, line_number)
);


Multiple versions of PostGIS and PgRouting on CentOS 7

It won't work to install several versions of PostGIS in the same PostgreSQL software directory, since the resulting shared objects will have the same name. Using a different --prefix is not enough, since most of PostGIS is not installed there, but in the PostgreSQL software directory.

So you should make a copy of your PostgreSQL software directory and install the different PostGIS versions in different directories.

If you really need different versions of Proj, GDAL etc. (why?), things become difficult, and you'd have to use a different LD_LIBRARY_PATH for each instance.

But perhaps it is much simpler, since the recent versions of PostGIS are binary compatible and come with installation scripts for several versions.

So maybe you can do with just installing the most recent PostGIS version and using different versions in different databases:

CREATE EXTENSION postgis VERSION 2.4.0;


SQL Server 2017: How do Batch Mode Adaptive Joins work?

Batch Mode Adaptive Joins

For Batch Mode Adaptive Joins, the goal is to not pin join choice to a specific type at compile time.

When available, Adaptive Joins allow the optimizer to choose between Nested Loops Joins and Hash Joins based on row thresholds at run time.

At this time, Merge Joins are not considered. Pure speculation is that needing data to be sorted, or needing to inject a sort into the plan would add too much overhead when changing the course of a query.

When Do Batch Mode Adaptive Joins Occur?

At this time, Batch Mode query processing requires the presence of a ColumnStore index. They also require, well, a join, and an index that allows for the choice of a Nested Loops or Hash Join.

How do I know if my Join is Adaptive?

Query plans for adaptive joins are quite distinctive.

The Adaptive Join operator is new to SQL Server 2017, and currently has the following properties in actual execution plans.

Physical Operation: Adaptive Join
Actual Join Type: Will be Hash match or Nested Loops
Adaptive Threshold Rows: Signifies the tipping point when the join type will switch to Hash Match
Is Adaptive: True for Adaptive Joins
Estimated Join Type: Rather self-explanatory!

In an estimated or cached plan, there is rather less information:

Most notably, the Actual Join Type is missing.

What breaks Batch Mode Adaptive Joins?

To monitor this, there's an Extended Event session called adaptive_join_skipped, and it has the following reasons for skipping a Batch Mode Adaptive Join:

eajsrExchangeTypeNotSupported
eajsrHJorNLJNotFound
eajsrInvalidAdaptiveThreshold
eajsrMultiConsumerSpool
eajsrOuterCardMaxOne
eajsrOuterSideParallelMarked
eajsrUnMatchedOuter

Aside from those, Batch Mode Adaptive Joins may be skipped for other reasons. Take these two queries for example:

/*Selecting just integer data*/
SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

/*Selecting one string column from Users*/
SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

They're identical except that the second query selects the DisplayName column, which has a type of NVARCHAR(40).

The Batch Mode Adaptive Join is skipped for the second query, but no reason is logged to the XE session. It appears that string data remains the steadfast enemy of ColumnStore indexes.

There are other query patterns that fail to get Adaptive Joins, that also do not trigger events.

Some examples:

CROSS APPLY with a TOP
OUTER APPLY

eajsrExchangeTypeNotSupported

One thing that will trigger this event appears to be the presence of a Repartition Streams operator. In this query, the partitioning type is Hash Match. Special thanks to the Intergalactic Celestial Being masquerading as a humble blogging man known as Paul White for the bizarre query.

SELECT uc.Id, uc.Reputation, CONVERT(XML, CONVERT(NVARCHAR(10), p.Score)).value('(xml/text())[1]', 'INT') AS [Surprise!]
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2009-08-01'
OPTION ( USE HINT ( 'ENABLE_PARALLEL_PLAN_PREFERENCE' ));
GO

eajsrHJorNLJNotFound

No queries have triggered this XE yet.
What doesn't work:

Merge Join hint
Query patterns that rule out join type, for example Hash and Merge joins require at least one equality predicate. Writing a join on >= and <= does not trigger the event.

eajsrInvalidAdaptiveThreshold

This event can be triggered by various TOP, FAST N, and OFFSET/FETCH queries. Here are some examples:

SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
INNER JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2008-01-01'
OPTION ( FAST 1);
GO

SELECT TOP 1 uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
INNER JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2008-01-01';
GO

In some circumstances, they can also be triggered by paging-style queries:

WITH pages
AS ( SELECT TOP 100 uc.Id, ROW_NUMBER() OVER ( ORDER BY uc.Id ) AS n
     FROM   dbo.Users_cx AS uc ),
     rows
AS ( SELECT TOP 50 p.Id
     FROM   pages AS p
     WHERE  p.n > 50 )
SELECT u.Id, u.Reputation
FROM   pages AS p
JOIN   dbo.Users AS u
ON p.Id = u.Id;

eajsrMultiConsumerSpool

No known query patterns have triggered this event yet.

What hasn't triggered it so far:

Recursive CTEs
Grouping sets/Cube/Rollup
PIVOT and UNPIVOT
Windowing functions

eajsrOuterCardMaxOne

A couple different types of queries have triggered this event. A derived join with a TOP 1, and a join combined with a WHERE clause with an equality predicate on a unique column:

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   (SELECT TOP 1 p2.OwnerUserId, p2.Score FROM dbo.Posts AS p2 ORDER BY Id) AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2009-08-01';
GO

SELECT p.Id, p.ParentId, p.OwnerUserId
FROM dbo.Posts AS p
JOIN dbo.Users_cx AS uc
ON uc.Id = p.OwnerUserId
WHERE p.Id = 17333;
GO

eajsrOuterSideParallelMarked

One type of query that can trigger this event is a Recursive CTE.

WITH postparent AS
    (
    SELECT p.Id, p.ParentId, p.OwnerUserId
    FROM dbo.Posts_cx AS p
    WHERE p.Id = 17333

        UNION ALL

    SELECT p2.Id, p2.ParentId, p2.OwnerUserId
    FROM postparent pp
    JOIN dbo.Posts_cx AS p2
    ON pp.Id = p2.ParentId
    )
    SELECT pp.Id, pp.ParentId, pp.OwnerUserId, u.DisplayName
    FROM postparent pp
    JOIN dbo.Users AS u
    ON u.Id = pp.OwnerUserId
    ORDER BY pp.Id
    OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

The reason here appears to be that the recursive portion of the CTE, which causes a serial zone in the plan, disallows Batch Mode Adaptive Join choice.

eajsrUnMatchedOuter

This is the most common, and seems to occur when an index is used for the join that cannot support a seek. For instance, this query causes a Key Lookup:

SELECT uc.Id, uc.Reputation, p.Score, p.LastActivityDate
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20080101'
       AND uc.DisplayName = 'harrymc'
       AND p.Score > 1;

The resulting query chooses a Row Mode Nested Loops join to execute both the Key Lookup and the table joins, which triggers the event.

Another example is a query that skips the narrow nonclustered index in favor of the PK/CX. In this case, the PK/CX does not lead with OwnerUserId, so the only join choice is a Hash Join.

In both cases, the "unmatched outer" seems to indicate that the index chosen does not sufficiently cover our query.

SELECT uc.Id, uc.Reputation, p.*
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

Do Batch Mode Adaptive Joins work with multiple joins?

Yes, but as of this writing, there appears to be a limitation:

Joining from one ColumnStore index to multiple Row Store indexes will yield multiple Adaptive Joins, whereas a join between multiple ColumnStore indexes will not be Adaptive.

For example, these two queries

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
JOIN dbo.Comments AS c
ON c.PostId = p.Id
AND  c.UserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
JOIN dbo.Comments_cx AS c
ON c.PostId = p.Id
AND  c.UserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

The first query joins one ColumnStore index (on Users) to two Row Store indexes on Posts and Comments. This yields two Adaptive Join operators.

The second query joins two ColumnStore tables (Users and Comments) to one Row Store table (Posts), and yields one Adaptive Join.

Is there any overhead to Batch Mode Adaptive Joins?

Yes, all Batch Mode Adaptive Join plans receive a memory grant. This isn't always the case with Nested Loops join, unless they receive the Nested Loops prefetch optimization. The memory grant is to support a Hash Join should the plan require one based on row thresholds.


Delete millions of rows disabling Indexes

Depending on how interconnected this table is with others in your database, and assuming that the table only has data for 2013-present, the fastest solution might be:

Script out your original table, and create a new empty table with the same structure.
INSERT the data for 2016 and 2017 into the new table.
DROP the original table.
Rename the new table to the original table's name (see the docs for ALTER TABLE).

Deletes are generally enough slower than inserts that it's probably faster to copy out 25-30% of the records in the table than to delete 70-75% of them. However, of course, you need to have sufficient disk space to hold the duplicates of the data to be kept to be able to use this solution (as noted by Toby in the comments).

If you do this, you'll want to be absolutely certain that the new table ends up exactly like the original, including any indexes, triggers, etc. You might want to truncate the original, rename it, and keep it around for a while instead of deleting it, just to be sure there's nothing you've missed. Also, outside of any clustered index, you may want to add clustered indexes and triggers after you've inserted the 2016 and 2017 data. If triggers are involved at all, make sure that whatever you do leaves the rest of your data in a valid state.

If other tables reference your table in foreign key relationships (as suggested by Joe Obbish in the comments), then this becomes somewhat more complicated. I would recommend scripting out all the foreign keys that point to this table, removing them, and then recreating them after the new table has been renamed. See this link to an article by Aaron Bertrand for help with this.


MySQL: After inserting data into a table, the table remains empty

This may run faster:
 INSERT INTO diff_set_values(value)
    select DISTINCT value
    from diff_sets;

Either would run faster if diff_set_values had INDEX(value).  However, adding the index would take a long time.
Increase the timeout.


Copy+Pasting currency value from SSMS 2012 to excel does not complete or calculate new value

If I do a Paste Special, I get two options, Text and Unicode, and selecting Text seems to work properly, where as Unicode does not.
as per comment by @JonSeigel


Is it possible to query value of SQLNET.EXPIRE_TIME parameter?

No, it can't be queried. These values are stored in sqlnet.ora on either or both of the client and the server.

In either case, none of the contents of the network configuration files sqlnet.ora, tnsnames.ora, listener.ora etc (eg protocol.ora, snmp.ora, cman.ora) can be queried.

If you really need to read the contents of these files and can't solve this problem in some other way (programatically via some OS level program or even administratively), you could read the file via external tables or via a Java stored procedure.

I would prefer to solve this outside of the database.


Mongo is using 5-7% of CPU, so why is my computer so slow?

Use 'mongostat' to monitor the amount of inserts
Use 'iostat -x 1' to see how much of the disk limitation is used. 'top'/'htop' to see cpy/ram limitations. 'nethogs' for network limits.
If the disk limit isn't reached : Let your python script run from different locations (servers) at the same time, and see if you can increase the amount of inserts. The pymongo driver might have a limitation per connection also.

Or you can use the mongoclient as extra threads. But I found the mongo client even slower inserting than pymongo on my tests:

$ mongo testDB --eval "for( var i = 0; i < 50000; i++ ) { db.test.insert({x:i,y:'hi'}); }" &

$ mongo testDB --eval "for( var i = 0; i < 50000; i++ ) { db.test.insert({x:i,y:'hi'}); }" &

Python speed insert test:

import sys
import datetime

from pymongo import MongoClient
connection = MongoClient("mongodb://10.x.x.x", w=0)

db=connection.testing
test = db.test

tstart = datetime.datetime.now()
d = {'counter' : 0}
while d['counter'] < 1000000:
  d['counter'] += 1
  testjs = {"x":d['counter'], "y":"hi" }
  try:
    test.insert(testjs)
  except:
    print "Unexpected error:", sys.exc_info()[0]

tend = datetime.datetime.now()
tdiff = tend - tstart
print tdiff.total_seconds(), ' ',tdiff.microseconds

Todo extra in the python script:

connect to mongos with a sleep between the inserts to see what happens when a primary of a shard goes down
insert more than "hi" to test splitting of chunks
...


SELECT koalaName
FROM main
GROUP BY koalaName
HAVING SUM( fate IN ('Released', 'Relocated', 'Care') ) > 0
   AND SUM( fate = 'Found Dead') = 0


SQLAgent executing command on Mirror database

The easiest way to prevent the job from running on the mirror (remotely executes on the principal) is add as a first step a USE statement for YourDBName  e.g.

The Job Step will Fail on the Mirror and the Job History Log Entry will resemble the following:

The database "YourDBName" cannot be opened. It is acting as a mirror database. [SQLSTATE 42000] (Error 954).  The step failed.

I am using SQL server 2012 SP4 Build No 11.0.7469.6

I understand that initially the database jobs will run on the Principal Only, however after a Mirror Failover they also appear to execute on the Mirror also, but actually will be remotely executing on the Principal, even though this does not appear to make much sense.


Why can't you add an Identity to an existing colum in SQL Server?

So what is so special about an Identity column that this can't be
easily added in retrospect?

An identity does not insert values on a table by itself, it depends on an INSERT command and the old rows on your table would require an UPDATE. If you try to UPDATE an identity column, you get an error like this:

Msg 8102, Level 16, State 1, Line 8 Cannot update identity column
'ColumnName'.

Every feature has a purpose and limitations. The purpose of the identity is not for updating values.

Why is this not just a single int/bigint flag somewhere that is being
incremented every time you insert a new row?

In fact it is something like that. The CREATE TABLE (Transact-SQL) IDENTITY (Property) says:

SQL Server might cache identity values for performance reasons and
some of the assigned values can be lost during a database failure or
server restart.

So your IDENTITY column has something like a flag that asks SQL Server for one of those values when inserting a new row.
From the Remarks section of the IDENTITY doc you can see the feature has some restrictions and the reason is:

These restrictions are part of the design in order to improve
performance, and because they are acceptable in many common
situations.


For every row where Place1 is null, you are returning an empty string as item_sku. So without DISTINCT all such entries would be returned as

item_sku  Place1
--------  ------
          NULL
          NULL
          NULL
          NULL
          NULL
…         …

and DISTINCT condenses them to just one row. Do you need that row in the output? To me, it does not make much sense to keep it. I would get rid of it with a simple filter:

SELECT DISTINCT
  "k1 " & ProductID1 & " " & ProductID2 & " " & ProductID3 AS item_sku,
  Place1
FROM
  Tam_Liste
WHERE
  Place1 IS NOT NULL
;

Furthermore, I would also consider moving the concatenation bit to the front end and use the database only as a data source*. My final query, therefore, would look like this:

SELECT DISTINCT
  ProductID1,
  ProductID2,
  ProductID3,
  Place1
FROM
  Tam_Liste
WHERE
  Place1 IS NOT NULL
;

I am not 100% sure but it seems likely to me that the above query would require less temporary disk space than the original one.

*I realise that things may work differently with Access, as it can be used both as a back end and as a front end. Nevertheless, I would still keep this option in mind.


How to synchronize two MySql tables using command line

This can be achieved using a mysql replace into feature provided you have either primary key or unique key implemented on both table to eradicate duplicates. Replace into have similar syntax as of insert into as when key is present on table it first delete the old row and insert the coming one.
mysql>Replace into table2 select * from table1

Either have cron job or you can use mysql event scheduler to populate the data to other table according to your desired timings.
Hope it helps


How to configure Ola Hallengren backup script for encryption

In the documentation for Ola Hallengren's backup scripts, he explains these parameters:

@Encrypt Y/N - Y means encrypt the backup, N is the default
@EncryptionAlgorithm - sounds like you're looking for AES_256, which is a valid option that he supports
@ServerCertificate - so you can specify the one you want to use

At the bottom of the page, in the examples, there's an example to back up all user databases with compression, encryption, and a server certificate:

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'

So based on my extensive research and a good thirty seconds of reading, it would be:

EXECUTE dbo.DatabaseBackup @Databases = 'MyTestDB',
@Directory = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyTestDBBackupEncryptCert'


You link to a FAQ that says

  The Instant Client libraries occupy a virtual address space that is
  equal to the size of the files. However, only frequently used error
  messages from the libraries occupy physical memory. Under most cases,
  the physical memory load is a few kilobytes despite the larger
  reserved virtual address space

This isn't saying that there is a memory leak in the Instant Client.  It is saying that using the Instant Client involves adding a few kb of physical memory and a bit more virtual memory to your application's memory footprint.  That's normally not something that would impact performance.  If you are running in an environment where a few kb of space is an issue (for example, you're building an application that is supposed to run 10's of thousands of copies of itself on a single server), then you might be better off with a full client install.  Of course, the full client will undoubtedly load more than a few kb of shared libraries into memory, they just won't count as part of your application's footprint.


SQL Server database backup on Remote site

You can use Deltacopy http://www.aboutmyip.com/AboutMyXApp/DeltaCopy.jsp it's a rsync solution for Windows, it can be used to sync files or folder between servers.


Export all tables in database using SQLCMD

I looked into doing this years ago and looked into both BCP and SSIS. Neither one really provided for great flexibility. BCP needed format files to get the text-qualifiers, etc. But this was for a dynamic backup solution for a subset of tables to get archived nightly. SSIS seemed a bit more dynamic (though not fully), but couldn't do a few of the things that BCP could. I needed something that could dynamically accept changes to tables (include new tables, don't require removed tables) AND changes to columns (as I needed to do "SELECT * FROM {SchemaName}.{TableName};") AND dynamic text-qualification AND column headers, etc.

If you have a static set of tables, then SSIS, using a ForEach container, would be one way to do this (again, assuming the desire is to stay away from BCP due to requiring multiple calls).

Using PowerShell would probably also be an option that would allow for more flexibility than SSIS.

However, if you need something fully dynamic and/or need to stick with a single connection  per each DB (all tables in that DB) or even a single connection per server (all tables for all databases), then the only way I have ever found to handle this is the one that I created. It is a SQLCLR stored procedure called DB_BulkExport which has parameters for @Query and @FilePath that allow for easily cycling through a list of all databases, then per each one cycling through a list of tables, and building both the query and filename from those pieces. You could write a fairly simple stored proc that uses a cursor to get the Database and Table names and call this proc, hence being a single call to SQLCMD.EXE to call that one proc. Or it can be scheduled via SQL Agent without needing to enable xp_cmdshell.

This procedure -- DB_BulkExport -- is part of the SQL# library (which I am the creator of), though it is only available in the Full (i.e. paid for) version, not in the Free version. I generally do not mention stuff here that is not available in the Free version, but as I said, I am not aware of any other existing means of automating this type of process. Of course, anyone is free to write this themselves, either as a SQLCLR proc as I have done, or as a stand-alone .NET app, or using PowerShell. It is more than a few lines of code in C# and I suspect it is also not overly simplistic in PowerShell either (but I haven't tried and don't have enough experience with PowerShell to really know).

EDIT:
Regarding how I have used the above mentioned stored proc in at least one instance: It was used to export data nightly, for long-term archival, the oldest day from a large table that was itself a common, short-term archival of data from 20 source servers.

It would extract maybe 10 million rows for a given date
The table held 150 - 450 million rows (fluctuation due to several factors)
It would grab all records for the oldest date and export them to a delimited file
1 day was 5 million rows (avg)
Each row was 1240 bytes (avg; 2 URLs plus a few other text fields, etc)
The file was 3 GB - 6 GB depending on the day
It exported to a local temp folder so that it didn't need to go across the network.
The archival process then:

used the File_GZip function -- which can handle zip64 (i.e. source files over 4 GB) -- to compress the text file down to 1 GB - 2 GB (no, it didn't take much memory; buffer is 8k)
used File_Copy to copy the .gz file to a networked archive folder
used File_Delete to remove the local file(s)
If the above steps completed successfully, the date of the data that was just archived was placed into a queue table for another SQL Agent job to pick up and incrementally (in small batches as the table was constantly being filled from the 20 source servers) delete the records from the archive table.

So to amend the description above of the archival process: it would extract the oldest date that was not in this queue table.
Once all of the records had been deleted the date being deleted would be removed from the queue table.

Ideally there would be a local drive that would not have pagefile, mdf, or ldf files on it that could be used for the extraction and compression. For the long-term archival process noted above, that server was a dedicated archive that just collected data for a few tables from the 20 source servers. Hence the only querying of the data 99% of the time was the SELECT * FROM ArchiveTable to do the extract; the other 1% of the time it was research.


Database stuck in restoring mode during log shipping

This is expected behavior for a log shipping job set to norecovery.  The logs are being restored with norecovery, which means that more logs can be restored on top, which is what you want.  No other transactions can occur on the target database;  it's a copy of the original.  If something goes wrong with the original, you bring the target online with Restore YourDB with recovery.

I've never used log shipping with any options other than norecovery, but there's also a standby option that will allow the target database to be used read-only (but the users will all be kicked off during the next restore). If this doesn't suit your requirements, maybe you need to look at something other than log shipping.


Prevent users from using power pivot excel connections to a database

You can do that using LOGON TRIGGER.

something like below :

--Prevent acces from Excel
CREATE TRIGGER tr_stop_excel_users
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END

Refer to my answer here as it has the script to achieve it as well. You have the flexibility of customizing the script to check for Application, Users, etc and depending on what you want to allow or not, the table that governs the script can be modified.


If those other users only need to Select from that table neither your nor the other session must add a LOCKING modifier.

A Select applies a READ lock by default which doesn't prevent other Selects, only INSERT/UPDATD/DELETE & DDL is blocked.

If you want other sessions to be able to modify rows you should use LOCK ROW ACCESS which allows dirty reads, i.e. a 2nd session might modify data while you're reading.

Teradata supports two out of the four Standard SQL Transaction isolation levels, the least and the most restrictive:

Read Uncommited -> ACCESS LOCK
Read Commited
Repeatable Read
Serializable -> READ LOCK

Teradata 15.10 implements a new feature named Load Isolation which adds a kind of Read Commited.


Cluster is causing my query to run SLOWER then before

Chat Recap
According to the Plan, the CBO decided to not take advantage of the data being in a CLUSTER.


should parent entity always have a primary key?

How can you identify each record if you don't have primary key. What if there is person having the same name and use it for future data. You must have primary key for table Person.


How do I determine when scaling is necessary or helpful?

If things are slowing down as you add more data then it's most likely a lack of indexes. If you have a table being queried without indexes then the server will need to do a full table scan (eg. read the entire table).
To understand why it's so much slower read up about Big O Notation. Full table scans are O(n) and will be very slow for large data sets. Index scans would be O(log(n)). For comparison with n=1M rows the linear operation would need to read 1M rows vs about 20 for the O(log(n)) one[1].
If you know all the SQL that is being executed in your app you can jump to the EXPLAIN part of this answer.
If you don't know what SQL is causing the slowness then you need statistics to figure it out. There's no info in your question about the tech stack you're running on but if you're using an ORM framework then you may be able to enable query stats there.
You can also look at enabling the MySQL Slow Query Log. Once enabled your MySQL server will keep track of SQL commands that execute for longer than a predefined amount of time (the default is 0 seconds, eg. everything).
Once you've identified the SQL that is slow you should run an EXPLAIN on it to see the query plan. Based on that you can add additional indexes to the relevant tables that are being accessed via full table scans.
If after adding indexes things are still slow due to excessive load then you can consider scaling to a larger server or a clustered setup. Until you've optimized your SQL though it's pointless as the same slow operations would be equally slow on a clustered setup (you just may be able to support more of them concurrently).

[1]: These numbers aren't entirely correct. The real stat to look at is the number of disk I/Os involved. For a full table scan the number of disk I/Os is is the number of rows divided by the number that can fit in a disk block (usually 4KB) so it would be less than 1M I/Os though still O(n) as it would grow linearly with the number of rows. Also, most database servers use B-Trees with a high fan out (~100 vs 2 for binary trees) for indexes so the number of disk I/O's would be significantly less (4-5 levels of the tree is common). Again it's still O(log(n)) though the log is base 100 instead of 2.


How to retire a physical standby in Oracle 11.2

The unique names of the databases in a Data Guard configuration are listed in the log_archive_config parameter in the following form:

log_archive_config='DG_CONFIG=(PDB10,PDB10_STBY)'

You can remove the old one and add the new one as:

alter system set log_archive_config='DG_CONFIG=(PDB10,PDB10SBY)';

After this, you can set log_archive_dest_N to the new standby.


Reaching a Microsoft SQL Server 2017 cluster from another device

Assuming the MSSQL cluster is setup on windows, it will have a virtual IP address which is different than the IP addresses assigned to each node. This virtual IP address will always point to the active node. The application can connect to the SQL server using this virtual IP address.


MySQL join to show room booking availability on different days per slot

Finally got a hook of it by using MAX (CASE WHEN.. END) instead of IF

SELECT tbl_days.id,tbl_days.date_day,
       MAX(CASE WHEN tbl_bookings.slot_id =1 THEN 'Booked' ELSE 'Available' END) '07:00 to 08:00',
       MAX(CASE WHEN tbl_bookings.slot_id =2 THEN 'Booked' ELSE 'Available' END) '08:00 to 09:00',
       MAX(CASE WHEN tbl_bookings.slot_id =3 THEN 'Booked' ELSE 'Available' END) '09:00 to 10:00',
       MAX(CASE WHEN tbl_bookings.slot_id =4 THEN 'Booked' ELSE 'Available' END) '10:00 to 11:00'
FROM tbl_days
LEFT JOIN tbl_bookings
     ON tbl_bookings.day_date = tbl_days.id
LEFT JOIN tbl_slots
     ON tbl_slots.id = tbl_bookings.slot_id
LEFT JOIN tbl_rooms
     ON tbl_rooms.id = tbl_bookings.room_id
GROUP BY tbl_days.id;


Is @@ROWCOUNT preserved by EXECUTE or not?

I propose a different test for you to understand that doc statement:

CREATE PROCEDURE TestRowCount
AS
BEGIN
    SELECT TOP (1) name FROM sys.databases;
    SELECT TOP (6) name FROM sys.databases;
END

After you create this procedure, run this:

EXECUTE TestRowCount;
SELECT @@ROWCOUNT AS Previous;

The Previous result is 6. With that result it's easier to understand that  EXECUTE statements preserve the previous @@ROWCOUNT means the previous statement that was called by the EXECUTE.

In your example the EXECUTE called an statement that affected 2 rows, so it returned 2 instead of 3 (as you expected).


If you already have SQL Server 2014 RTM installed

You can't do any slipstreaming - service packs don't support that, only initial installs.
(note: you cannot vote for the suggestion linked directly above as it was never migrated to the newer "Feedback" system; the link merely takes you to an archived version of the request that used to exist)

You can't apply an SP1 CU without first applying SP1.

Cumulative updates are cumulative - if you eventually want to get to SP1 CU2, you can ignore SP1 CU1 altogether.

So, in that case, you would have to download SP1, install it, and then apply Cumulative Update #2.

If SQL Server 2014 is not already installed
You can slipstream in this scenario, so that the system starts for the first time running at SP1 CU2, and no further installs are necessary. Download SP1 and SP1 CU2, extract them to a single folder, and point setup at them, e.g.:
setup.exe /Action=Install /UpdateSource=C:\UpdateFolder

If you are using versions older than 2012
All of the above still applies, however in 2008 and 2008 R2, a single /UpdateSource is not supported; you want to use the following instead, with /PCUSource for the extracted service pack files, and /CUSource for the extracted cumulative update files:
setup.exe /Action=Install /PCUSource=C:\SP1Folder /CUSource=C:\CU2Folder

Other info
Peter Saddow documented the slipstream process here, long ago, but this did not deal with SP + CU scenarios:

Creating a merged (slipstreamed) drop containing SQL Server 2008 RTM + Service Pack 1

I talk about it a bit more here (and the advice for 2012 will stand for 2014, and from what I've heard so far, 2016 as well):

SQL Server 2012 Service Pack 1 & Cumulative Update 1

For slipstreaming to work, you will have to extract the CU file you get from the hotfix download server (in this case 486657_intl_x64_zip.exe), and make sure that the update source folder has the extracted files.


Is using dbms_utility.format_error_stack to filter errors in the exception block a bad idea?

I would suggest two small modifications:

re-raise the exception in the WHEN OTHERS exception handler. Not raising the exception will make the calling application think that the procedure did succeed. Or better yet don't use WHEN OTHERS: there's no point in catching an exception if you do nothing but re-raise it. Let the natural exception bubble up.
add an ELSE clause in the first exception handler just to be sure that you don't silently ignore a new type of error that doesn't get catched by the two IF conditions.

Aside from that, I don't see anything wrong with using dbms_utility.format_error_stack in an exception clause (in fact you can't use it anywhere else).

EXCEPTION
   WHEN external_table THEN
      IF dbms_utility.format_error_stack LIKE '%KUP-04040%' THEN
         RAISE_APPLICATION_ERROR(-20000, 'Missing CSV file!');
      ELSIF dbms_utility.format_error_stack LIKE '%ORA-30653%' THEN
         raise_application_error(-20001, 'Invalid UTI report, check for missing fields!');
      ELSE
         raise_application_error(-20002,
                                 'An exception occurred: ' || SQLERRM,
                                 TRUE);
      END IF;
   -- WHEN OTHERS THEN -- No point really
   --    raise_application_error(-20003,
   --                            'An unknown exception occurred: ' || SQLERRM,
   --                            TRUE);
END;


There are two possible ways
1.  Automatically create scripts for all SQL Server Jobs

I. Open Object Details

II. Select All Jobs (Shift + Mouse Click) -> Right Click -> Script Job as


How to name a constraint which was unnamed during the table creation?

When working with Oracle, you can find the constraint names (and other details about constraints) by querying USER_CONSTRAINTS.

Example (Oracle 12c)

-- table with PK and NN constraints
create table test (
  id number
    generated always as identity start with 2000
    primary key
, description varchar2( 100 ) not null
);

-- populate the table (optional)
insert into test ( description )
select 'description_' || to_char( level )
from dual
connect by level <= 5 ;

select * from test ;
ID    DESCRIPTION
2000  description_1
2001  description_2
2002  description_3
2003  description_4
2004  description_5

Find constraints

select table_name, constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'TEST' ;  -- table_name: use upper case!

TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE  SEARCH_CONDITION
TEST        SYS_C0021091     P                NULL
TEST        SYS_C0021089     C                "ID" IS NOT NULL
TEST        SYS_C0021090     C                "DESCRIPTION" IS NOT NULL

Rename the constraints

alter table test
rename constraint SYS_C0021091 to test_pk ;

alter table test
rename constraint SYS_C0021089 to id_nn ;

alter table test
rename  constraint SYS_C0021090 to description_nn ;

Do the constraints have new names now? Yes.

select table_name, constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'TEST' ;

TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE  SEARCH_CONDITION
TEST        TEST_PK          P                NULL
TEST        ID_NN            C                "ID" IS NOT NULL
TEST        DESCRIPTION_NN   C                "DESCRIPTION" IS NOT NULL

Data still intact? Yes.

SQL> select * from test;
ID    DESCRIPTION
2000  description_1
2001  description_2
2002  description_3
2003  description_4
2004  description_5


It should be more of a case support with idera. Generally these are good repuatated products in market which have a better documentation than we can explain here. I can share this based on my experience with sentry support where had similar if not exact issue. E.g if you are using idera to collect actual execution plan or events for tracking queries and their metrics comes with additional cost.

Please open a case with idera and go through their documentation for further troubleshooting.


A couple of suggestions:

Use legal, lower-case, unquoted identifiers to save yourself a lot of confusion. end (and, to a lesser degree start) are reserved words.

Your id column seems to be numeric type like integer, not text.

Since you don't need a time component in start_day and end_day, the appropriate data type is date, not timestamp.

Why concatenate id and the year for a new "ID"? Add a second column year if you need it for a PK. Or don't add an additional column at all. It can cheaply be extracted from the new start_day on the fly. Generally, don't store data redundantly if you can avoid it.

Typically, timestamp ranges use an inclusive lower bound and an exclusive upper bound. Since timestamps can have fractional digits (up to 6 in Postgres) that is much cleaner. Your input 2003-12-31 23:59:59 would fail for 2003-12-31 23:59:59.123.

So, your table could look like this:
CREATE TABLE ad (
  id        int PRIMARY KEY
, start_day date NOT NULL    -- *inclusive* lower bound
, end_day   date NOT NULL    -- *exclusive* upper bound
CHECK (end_day > start_day)  -- enforce legal input
);

Proper test values:
INSERT INTO ad(id, start_day, end_day)
VALUES
  (1, '2003-06-07', '2004-10-01')   -- span 2 years (your example)
, (2, '2003-06-07', '2003-06-08')   -- 1 day in same year
, (3, '2003-06-07', '2003-10-01')   -- span 1 year
, (4, '2003-06-07', '2006-10-01');  -- span many years

Solution
Use generate_series() in a LATERAL join, based on start and end day, truncated to the year with date_trunc(). This produces one row per year with the new start date. Add a year and you have the new end date. Except for first and last row per id, where you substitute the proper start / end with GREATEST and LEAST respectively. Voilá.
-- CREATE TABLE ad_year AS
SELECT ad.id
     , extract('year' FROM y)::int AS year
     , GREATEST(y                    , ad.start_day) AS start_day
     , LEAST   (y + interval '1 year', ad.end_day)   AS end_day
FROM   ad
     , generate_series(date_trunc('year', start_day::timestamp)  -- cast to ts here!
                     , date_trunc('year', end_day::timestamp)
                     , interval '1 year') y;

Note that date_trunc() returns timestamptz for timestamptz input and timestamp for timestamp input. For date input it defaults to timestamptz. Since you seem to be ignoring time zones, cast the date to timestamp explicitly (start_day::timestamp).
Result:
 id | year |      start_day      |       end_day
----+------+---------------------+---------------------
  1 | 2003 | 2003-06-07 00:00:00 | 2004-01-01 00:00:00
  1 | 2004 | 2004-01-01 00:00:00 | 2004-10-01 00:00:00
  2 | 2003 | 2003-06-07 00:00:00 | 2003-06-08 00:00:00
  3 | 2003 | 2003-06-07 00:00:00 | 2003-10-01 00:00:00
  4 | 2003 | 2003-06-07 00:00:00 | 2004-01-01 00:00:00
  4 | 2004 | 2004-01-01 00:00:00 | 2005-01-01 00:00:00
  4 | 2005 | 2005-01-01 00:00:00 | 2006-01-01 00:00:00
  4 | 2006 | 2006-01-01 00:00:00 | 2006-10-01 00:00:00

If you create a new table from the result, I suggest (id, year) as primary key.
Aside: This is not an operator: ||'_'|| (nor a cute little face). It's  2 concatenation operators || and a string literal '_'.


SQL Server - Add signature to stored procedure by certificate does not work

You need to copy the certificate from master to your user database. For example:

-- Copy master certificate to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'your encryption password',
              'your decryption password')

--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE your_certificate
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''your encryption password'',
          ENCRYPTION BY PASSWORD = ''your decryption password'')'

EXEC YourUserDatabase.sys.sp_executesql @sql;

EDIT

If the certificate is encrypted with the database master key instead of a password, omit the decryption password from CERTPRIVATEKEY (to decrypt with the source database DBMK) and omit the ENCRYPTION BY PASSWORD clause of CREATE CERTIFICATE (to encrypt with the target database DBMK). The password in this case is used only to encrypt the private key in transit.

-- Copy cert to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key  varbinary(MAX) = CERTENCODED(@cert_id),
        @private_key varbinary(MAX) =
           CERTPRIVATEKEY(@cert_id,
              'your encryption password');

--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;

--create certificate in target database encrypted with DBMK
DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE your_certificate
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1)
        + ', DECRYPTION BY PASSWORD = ''your encryption password'');'
PRINT @sql

EXEC YourUserDatabase.sys.sp_executesql @sql;
GO

I'll add that for only cross-database permissions, one doesn't need to create a login from the certificate. Create a certificate in a user database, copy the cert to other databases as needed, create a user from the certificate in each database and grant permissions to the cert user, and finally sign the proc with the certificate.

A server level principal is needed only when server-level permissions must be granted.


Error during MySQL encryption, Code: 3185. "Can't find master key from keyring (...)"

What is your MySQL Version?

I find the following 'solution' and i will try upgrade mine 5.7.20 to 5.7.22:

  Posted by developer:

  Fixed in 5.7.22, 8.0.4, 9.0.0.

  Cosmetic change to
  error message. No changelog entry needed.

Maybe it will solve your problem.


Exclude rows with unique values in one column from grouped mysql query

You can use COUNT(DISTINCT xxx) in this way:

select distinct user_id, project_id
from   t
where  project_id in (select   project_id
                      from     t
                      group by project_id
                      having   count(distinct user_id) > 1);

user_id | project_id
------: | ---------:
   6342 |       1003
   6318 |       1003

db<>fiddle here


Does ISOLATION LEVEL READ UNCOMMITTED affect Deletes?

I am failry clear on what ISOLATION LEVEL READ UNCOMMITTED does for reads, but I am not sure about deletes.

It does nothing.  You can't modify the database without proper locking.  That would produce not just the failures and nonsense results that dirty reads produces, but would corrupt the database.


Postgres how to get column data types for a view

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'my_view';


Different MySQL Datafile Sizes After Restoration

This makes all the sense in the world to me.

InnoDB creates data pages and index pages that are 16K each. If rows of data are being inserted, updated, deleted, committed, and rolled back, you are going to have FRAGMENTATION !!!

There are two cases where you can have internal fragmentation:

A single row could be written in multiple data pages because certain column values make a row too big to fit in the data page.
Having a TEXT column with 32K of data in it.

In those two cases, a single row spanning multiple data pages would have to be chained like a linked list. The internally generated list of data pages would always have to be navigated when the row is read.

Giving credit where credit is due, PostgreSQL implemented a very brilliant mechanism called TOAST (The Oversized-Attribute Storage Technique) to keep oversized data outside of tables to stem the tide of this kind of internal fragmentation.

Have used mysqldump to make a file with CREATE TABLE statements, followed by lots of INSERTs, you get a fresh table with no unused space along with contiguous data and index pages when loading the mysqldump into a new server.

For my explanantions, let's assume you have an InnoDB table in the CUSTODIA database called userinfo

If you would like to compress a table, you have three(3) options

Option 1

OPTIMIZE TABLE CUSTODIA.userinfo;

Option 2

ALTER TABLE CUSTODIA.userinfo ENGINE=InnoDB;

Option 3

CREATE TABLE CUSTODIA.userinfo2 LIKE CUSTODIA.userinfo;
INSERT INTO CUSTODIA.userinfo2 SELECT * FROM CUSTODIA.userinfo;
DROP TABLE CUSTODIA.userinfo;
ALTER TABLE CUSTODIA.userinfo2 RENAME CUSTODIA.userinfo;

CAVEAT : Option 3 is no good on a table with constraints. Option 3 is perfect for MyISAM.

Now for your questions:

Question 1. Why is there this difference between original and restored database size?

As explained above

Question 2. Is it safe to assume that restored database is OK, although this difference in size?

If you want to make absolutely sure that the data on both servers are identical, simply run this command on both DB servers:

CHECKSUM TABLE CUSTODIA.userinfo;

Hopefully, the checksum value is identical for the same table on both servers. If you have dozens, even hundreds, of tables, you may have to script it.

Question 3 : How does MySQL calculate data_length? Is it an estimate?

You are using the correct method in summing up the data_length and index_length. Based on my explanation of fragmentation, it is an estimate.

Question 4. Can I safely reduce production's ibdata file size to 3.6GiB with no down-time?

GOOD NEWS !!! You absolutely can compress it !!! In fact, how would like to compress it to a fraction of that number ??? Follow these two links because I addressed this issue in StackOverflow and ServerFault.

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400

BAD NEWS !!! Sorry, but you will have a 3-5 minute window of downtime for rebuilding ib_logfile0 and ib_logfile1 as well shrinking ibdata1 once and for all. It's well worth it since it will be a one-time operation.


SQL Server automatic transaction log truncation

No physical truncation actually occurs, the size of the file would not get smaller.

Parts of the logfile (virtual logfile/vlf) are merely flagged as 'the content is allowed to be overwritten'. If such an event exists, and I know of none, there would be no practical duration.

If you are referring to 'auto-shrink', I would highly recommend you read this post 'Auto-shrink – turn it OFF!' by Paul Randal.


Documenting SSRS and SSIS jobs that are the Job Activity Monitor

This simple query on MSDB should get you started:

SELECT s2.subsystem AS [TypeOfJobStep]-- when subsystem = 'SSIS' then 'SSIS job step'
     , s2.command AS [JobStepCommand]
      , s.name AS [JobName]
FROM dbo.sysjobs AS s
JOIN dbo.sysjobsteps AS s2 ON s2.job_id = s.job_id

You can identify SSRS subscriptions because their name will be alternating alphanumeric strings with dashes. I think a SUBSTRING function could determine that.

SSIS job steps should show subsystem = 'SSIS', depending on how you execute your SSIS jobs.

As for identifying which tables your SSRS jobs hit, that will require you to document your SSRS reports. SSRS reports can access data through a stored procedure, or through an embedded query within the report. Once you determine which they are doing, you'll need to determine a way to link them to the jobs you previously queried.

This is the hard part, because SSRS reports can use multiple data sources and those data sources don't have to be on the same SQL server as your scheduled jobs. I don't know if this part is feasible.


MySQL InnoDB page_cleaner settings might not be optimal

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances

Check innodb_buffer_pool_instances with:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_instances'

You can only set innodb_page_cleaners as high as innodb_buffer_pool_instances. If you want innodb_page_cleaners=4 then you also need innodb_buffer_pool_instances=4.


How to set up indexes for dynamic filtering queries in Oracle

Taking into consideration all combinations of predicates can be used

One option would be a bitmap index on the non ID, non TIMESTAMP column.
One index per column.
Beware of its it's downsides.

serialization
possible slower DML

Luckily, the newer versions of Oracle allow you to experiment with indexing strategies.


Does MySQL have a limitation of one index per query?

There are multiple questions here.

To answer the title: MySQL can utilize more than one index per query. This is called index-merge.

However, it does not always happen that index-merge optimization is used.

"Normal" indexes are B/B+ Trees. Such trees provide with linear sorting. You can index x, and the index sorts x in ascending order. You can do the same for y. You can even index both (x, y) but you still get linear sorting -- not two dimensional one.

To get back to your other question, regarding the query

x < xmax and x > xmin and y < ymax and y > ymin

Let's assume then that we have an index on x and an index on y. The problem begins with the fact that these indexes do not share resources, nor information, nor at any way point to one another.

It's very easy to figure out all rows where x < xmax and x > xmin, using the index on x. But once you have these, you can't just take those rows to the y index and ask of it to "return rows from withing these rows I'm giving you and for which y < ymax and y > ymin. The reason is that the index onycan quickly lookup usingy` values. It does not know how to quickly lookup row #3984987.

The way index-merge works is by getting row ids from the x index, getting row ids from the y index (meanwhile the x rows are stored in a temporary buffer), and then intersect these. Note that intersection has nothing to do with indexes. It is a brute force operation (hopefully, achieved in linear time thanks to the fact that the two lists of rows are already sorted).

Now given such a query as yours, MySQL asks itself: what would be cheaper? Use just one index on x, then row by row filter those rows where y < ymax and y > ymin applies? Or perhaps use an index-merge, create a temporary buffer, work out a brute-force intersection?

The fact is that MySQL rarely chooses the index-merge optimization. I've been looking at many queries and their execution plans. Very rare. Perhaps there's someone there for whom this works on regular basis.

But looking at the X:Y coordinates problem, it's easy to see why this wouldn't work: there's just too many matching values for the x-range alone, and just too many values for the y-range alone for this to be a good plan.


High CPU usage on Azure SQL Server ~1.6m rows

I would say the issue is the # of executions. When u take the time per execution for two first rows, its about 0.8 seconds per row. But the select is executed so often It eats up cpu by comparision.
In adition, what is the datatype of @param?


Oracle: terrible execution times from virtual application server

This is almost certainly a networking issue (as the execution on a different machine is way faster) so I doubt you'll be able to go much further with any Oracle tools. I've never had any needs to tweak out-of-the-box network parameters to get good performance.

What I suggest is you use a standard network benchmarking tool, e.g. iPerf. It needs to be cross-platform since you have a mixed Linux/Windows environment. Keep in mind that the issue could be due to either low bandwidth or high latency. Run the same test from the other client from which you're getting good SQL Plus results, compare and if it's massively different send the evidence to your networking admin guys.

By the way, if you still need more advice I suggest you post to https://serverfault.com/ as you'll likely to reach more experienced network admins there.


Database design: same products in inventory but with different cost

First, you must ask the Controller (or CFO) what the appropriate costing model is for inventory of this sort - LIFO, FIFO, Average, or some less common pattern. This is very important as if you use a different calculation from that published in Financial Reports of the corporation, you will be engaged in fraud of the shareholders and be in violation of Sorbanes-Oxley.

If the financial records are mailed to the shareholders, you might be in violation of RICO also.

All of the methods I listed above are appropriate in various industries, at various times - you cannot make an a priori decision on one being better than the others.

One absolute principle of GAAP is that when any change is made to the traditional accounting policies of a corporation, a number of prior years financial statements must be restated according to the new calculation in the current year's financial statements. This can only be done as authorized by the Controller and/or CFO, and possibly the corporate auditors as well.

Never, ever program an accounting calculation of any sort without verifying the technique with an appropriate corporate authority. There are many aspects of GAAP (Generally Accepted Accounting Principles) that are non-intuitive to those without an accounting background.

Update:

Note that even when inventory items have serial numbers, and thus the actual cost may be known, it is not necessarily appropriate accounting practice to use the actual cost instead of a calculated cost based on all equivalent items in inventory. That is to say: The cost of selling a unit of product XYZ is a calculated accounting value that may not be the actual purchase price of the particular unit sold, even when that is known.

I repeat: You must discuss this with your Controller and/or CFO.


It depends on the nature of the ALTER. A lot of times this is in fact implemented by creating a new column, copying the data across and marking the old column as dropped. Example:

CREATE TABLE T1(X CHAR(10));

INSERT INTO T1 VALUES (REPLICATE('X',10));

ALTER TABLE T1 ALTER COLUMN X CHAR(12);

And then looking at the row in SQL Server internals viewer you can clearly see this

You can also look at the results of the query under Inspecting the physical table structure

  the main question is what is happening during a column alter to double
  the size of a table?

There are several possible things that can contribute.

In the above case the row size was doubled with a single ALTER COLUMN so that would easily explain the overall table size doubling.

Even in the case that the row itself doesn't double in size the fact that the row becomes wider can cause page splits and increase the level of internal fragmentation. This is the case in the article you linked. It goes from having pages 99.8% full to 55% full. Every leaf page needed to be split as there was not sufficient room to accommodate the wider rows so that doubles the number of leaf pages.

Also sometimes previous changes that were implemented as metadata only are deferred and will be written to the row now it is being updated anyway. An example of such a change would be ALTER TABLE T1 ADD Y CHAR(12) NULL this is generally a metadata only change at time of execution and the physical row won't be updated to reflect the additional 12 bytes for this column until next time it needs to be written to.


Why does this SQL work on one version of MySql-Server but not another?

The datatype DATETIME used to be only yyyy-mm-dd hh:mm:ss.  But, beginning with 5.6.4(?), fractional seconds were allowed.  DATETIME(6) allows for microseconds:  yyyy-mm-dd hh:mm:ss.ffffff.

If you want that CREATE to work on both versions, remove the (6).  Notice how the error message pointed exactly at that.


Store Function Value into temporary table

Build a table with every day for the next few years:

 CREATE TABLE foo (
     dy DATE NOT NULL,   -- every day
     bow DATE NOT NULL,  -- date (yyyy/mm/dd) of the Beginning Of Week
     eow DATE NOT NULL,
     PRIMARY KEY(dy)
 ) ENGINE=InnoDB;

Then, instead of having a function, JOIN to that table to fetch the bow and/or eow needed.

Meanwhile, DATE_FORMAT(some_date, '%a') yields Sun, Mon, etc.  And WEEKDAY(some_date) gives you 0 for Monday, etc.  These might simplify some of your code.


Concern on Active Directory Users on Azure SQL Server

I just tested this out in my Azure tenant.

Created a new Azure AD user
Added the test user to my Azure SQL DB.
Logged in as the test user and verified access.
Deleted the test user from Azure AD.
Confirmed: Azure SQL does not automatically remove the user from the DB.

Moreover this user retained access to the db for quite a while after the user record was deleted...  I did a db rename to disconnect the users from the db and then  tried to log in again and was denied.

You might be better off managing access to the database using Azure AD groups instead of adding individual users.


Does the 'quality' of query optimization differ by database provider?

A question asking if something is "better" or "worse" is subject to personal opinions, so take this with a grain of salt. Asking about the behaviour of the query optimizer is usually extremely broad as the behave differently in complex situations compared to extremely simple queries as the one in your question.

For Postgres a CTE is (as of Postgres 11) a so called "optimization fence". That means Postgres will optimize the CTE independently of the overall query. There are cases where this results in a "better" (=faster) plan, and then there are cases where this will result in a "bad" (=slower) plan (note that recently the discussion around this design decision started again, so maybe this will change in future versions after Postgres 11).

The example in your question is an example where this approach results in a "bad" plan as the condition of the outer query is not pushed into the CTE.

SQL Server optimizes the whole query including the CTE, so for your example the execution plan in SQL Server would qualify as "better.

But I have also seen the opposite in Postgres: when moving parts of a (non-trivial) query into a CTE the resulting plan was better because operations where not pushed down which resulted in better estimates and a much "better" (=faster) execution plans. As I hardly use SQL Server, I can't tell if there are similar situations there.


HierarchyID--multiple root values in one table

The dummy root is probably the right track, except you don't actually have to create a dummy root to make multiple roots.  Your table also appears to be simultaneously an adjacency pair as well, so you've got a couple of immediate options on how you want to go using the table.  Both should probably be tested for suitability, as I have a sneaky feeling that depending on read-a-lot or write-a-lot usage, your mileage from either case may vary.

Since the needs of both your developers and fellow DBAs must be considered, most of the other options are probably out of the question ( though there are loads and loads ).  This SQL Fiddle illustrates a couple of ways to leverage the materialized paths inherit in your structure, first through leveraging a multiple-root HIERARCHYID, then through adjacency pair traversal through recursive CTEs.

To implement multiple roots, the only real "trick" to it is to preserve the sub-sequence on the root level.  Instead of simply calling NULL descendants during root node creation, like so,

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( NULL, NULL ), '1' );

each root node can be created using the root as the base level ( though the first one obviously doesn't matter ):

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
            SELECT  MAX( IDOrg )
            FROM    dbo.FailCode
            WHERE   IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ),  '1' );

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
            SELECT  MAX( IDOrg )
            FROM    dbo.FailCode
            WHERE   IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ),  '2' );

If you're in more of an UPDATE situtation, you can create the multiple root nodes off the primary key, if you like:

SELECT  CAST( '/' + CONVERT( VARCHAR( 16 ), ID ) + '/' AS HIERARCHYID ).ToString()
FROM    dbo.FailCode
WHERE   Name IN ( '1', '2' );

Children can then be added to each root node as necessary, business as usual stuff from there.

On the adjacency pairs side of your structure, either of the above inserts would do the trick - the ParentID for any root node just has to be NULL.  Again, at this point, adding children is pretty evident.

Either way you go about it, the hierarchy capabilities are largely equivalent and either solution can be effective and efficient ( though the adjacency list model will certainly appear to be a little more involved! ).

HIERARCHYID

SELECT  Org = IDOrg.ToString(), ID, Name
FROM    dbo.FailCode
ORDER BY ID;

Adjacency List

;WITH cte_AdjacencyList AS (
    SELECT  Lvl = ROW_NUMBER() OVER (
                ORDER BY ID ),
            Org = CAST( '/' + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
                ORDER BY ID ) ) + '/' AS VARCHAR( 32 ) ),
            ParentID, ID, Name
    FROM    dbo.FailCode
    WHERE   ParentID IS NULL
    UNION ALL
    SELECT  Lvl = ROW_NUMBER() OVER (
                ORDER BY fc.ID ),
            Org = CAST ( al.Org + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
                ORDER BY fc.ID ) ) + '/' AS VARCHAR( 32 ) ),
            fc.ParentID, fc.ID, fc.Name
    FROM    dbo.FailCode fc
    INNER JOIN cte_AdjacencyList al
        ON  fc.ParentID = al.ID )
SELECT  Org, ID, Name
FROM    cte_AdjacencyList
ORDER BY ID;


Setup failed for Microsoft Management Studio

I ran into the exact situation where I uninstall various version of SQL and related tools and freshly reinstall 2016.

This resolve my problem: Setup failed when installing SQL Server Management Studio 2016. I do not understand why it works in my first installation but only after uninstalling and at my second reinstallation the registry points to (x86) instead - if someone can help explain this solution.


I found my answer from the book "Database system concepts". Here's what that means:
Suppose C is a composite attribute of an entity type, which has 3 attributes : A1, A2, A3. Suppose, only A3 is multivalued.
Now say the domain of these 3 attributes contain 5, 4, 3 elements respectively.

The domain of Multi-valued attributes is a set of subsets of values
from the basic domain.

This means, the multi-valued attribute's (A3 in our case) domain will be the Power set of the basic domain set.
So, in our case, the power set of A3 will contain 2^3 = 8 elements. So, the composite attribute C will contain 5x4x8 = 160 elements.
In case A3 was not multi-valued, then the domain of C would contain 5x4x3 = 60 elements.


Your problem boils down to access control.

The first defense I'd propose is to simply deny access to the untrusted users. If they can't get into the database, they can't query the database and get at the sensitive data.

If they must be allowed to access the database server, you can look at either explicitly granting them read permission to the tables they must access to perform their job tasks. Alternatively, you can leave their current permissions alone and simply revoke their ability to query tables or columns within those tables with sensitive data.

The reasons I'd start with this approach is that there is overhead to encryption. There is key management as well as the CPU cost of encrypting/decrypting the data. As I understand it, there's also going to be a performance cost of looking up data as the encrypted data if it's in an index, the encrypted value will be indexed and not the source value. The net result is that 2013-06-11 and 2013-06-12 could have been stored in contiguous disk locations but once they're encrypted they could be on opposite ends of the disk and your simple range query which used to perform well now sucks hind teat.

All of that said, Steve Jones had a good presentation on the Encryption Primer. I used some of that content plus this article on encryption in the DB but not in the cube to get our encryption stuff up and running.

As others have pointed out in the comments and other answers, despite having Encryption as part of its name, TDE, isn't going to protect the data in the database itself. The purpose of TDE is to protect your backups from unauthorized access. It has nothing to do with encrypted packets flowing across the network or automagically creating PCI compliance. It simply ensures that if our offsite backup tapes are lost, stolen or simply snooped, people will not be able to use them without the key.

The other point to access control that I failed to mention is physical access. If the concern is the junior admin accessing data they shouldn't, then in addition to denying them access to the database, don't overlook basics of preventing them from accessing the machine itself---either through remote desktop or physically logging onto the machine. Once they are able to get onto the box, there's nothing preventing them from restarting SQL Server into single user mode and removing your access controls.


Function to convert first names to a specific format

Quick and dirty, but I'm assuming you'll be putting this into a more formalizing procedure for spitting out names.

DECLARE @Firstname nvarchar(50)
DECLARE @Lastname nvarchar(50)

SET @Firstname =  'Marc-Oliver'
SET @Lastname = 'Saint-Onge'

SELECT @FirstName, @Lastname

IF CHARINDEX('-', @FirstName) <> 0
    SELECT SUBSTRING(@Firstname, 1, 1) + '.' +
    SUBSTRING(@Firstname, CHARINDEX('-', @Firstname), 2) + '. ' + @Lastname
ELSE
    SELECT @Firstname + ' ' + @Lastname

Which produces

Marc-Oliver     Saint-Onge
--------------------------------------------------------
M.-O. Saint-Onge

Edit:

I had never really messed around with pattern matching like this before, so it's been interesting. I believe this should work for hyphens, periods, or spaces in the first name. I tested with 'Marc Oliver', 'Marc.Oliver' and 'Marc-Oliver' all of which returned 'M.-O.' for the first name.

IF @Firstname LIKE '%[-. ]%'
    SELECT SUBSTRING(@Firstname, 1, 1) + '.-' +
    SUBSTRING(@Firstname, PATINDEX('%[-. ]%', @Firstname) + 1, 1) + '. ' + @Lastname
ELSE
    SELECT @Firstname + ' ' + @Lastname


Are cross-database queries expensive in SQL Server?

Sorry, I don't have enough reputation to comment on the question, but from my experience if the client application raises a transaction for a query which uses cross database joins then it would promote the transaction to distributed and have the overhead of a DTC transaction.

The DTC overhead in this case could be viewed as a negative to performance. Generally the difference would be negligible although Microsoft describe DTC transactions as such:

  Distributed transactions typically consume significant system resources

  Transaction Promotion

...which would suggest a performance degradation if your server cannot offer the resources it requires.

Just to clarify, the article above describes local transactions being promoted when remote systems are introduced, but I have seen this become the case for transactions on the same server when using cross database queries.

As Thomas Stringer points out in his comment, there will be extra overhead in authentication although I think as this will be SID-driven there will be minimal overhead there unless you have to use separate credentials to access the other database.

If there were difference in database settings which caused additional overhead in the join that could impact larger than the previous suggestions - for example database collation. Database collation could manifest as a functional difference, not just a performance difference.

I think Aaron has the strongest argument for performance with the optimizer not having the advantage of using relationships for cross-database queries whereas self-contained within a database you could use relationships to your advantage.


Let's try this query:
SELECT TOP 1000 A.id
FROM  sys.partitions AS P
   CROSS APPLY ( SELECT TOP 1000 T1.id
          FROM dbo.table AS T1
          WHERE $PARTITION.PF1(T1.date) = P.partition_number
           AND T1.id > @certain_id
           ORDER BY T1.id ) AS A
  WHERE P.object_id = OBJECT_ID('dbo.table')
  AND P.index_id = INDEXPROPERTY( OBJECTID('dbo.table'), 'idx_c1', 'INDEXID')
    ORDER BY A.id;

from here: https://support.microsoft.com/en-in/help/2965553/decreased-performance-for-sql-server-when-you-run-a-top-max-or-min-agg


Identity proceeds as normal, but with a leading 10...?

This is a known behaviour difference in SQL Server 2012 because of a change in the way IDENTITY is implemented.

See related Connect Item for some discussion and possible workarounds (if this is a real issue for you)


Write one or more triggers to manage the grade attribute of new Highschoolers

As i mentioned in the comment.

For your purpose you can use a BEFORE  INSERT trigger

DELIMITER $$
CREATE TRIGGER `gradeTrigger1`
BEFORE INSERT
ON highschooler  FOR EACH ROW
BEGIN
    CASE  WHEN  new.grade < 9 OR new.grade > 12  THEN
        SET new.grade = NULL;
    WHEN  new.grade IS NULL THEN
        SET new.grade = 9;
    ELSE
        SET new.grade = NEW.grade;
    END CASE;
END$$
DELIMITER ;


Get row with max date from a joined table

A solution is to do an extra LEFT OUTER JOIN with 'newer pages', and filtering those with no newer pages:

SELECT d.*, p1.*
  FROM dossiers AS d
  INNER JOIN pages AS p1
    ON d.id = p1.dossiers_id
  LEFT OUTER JOIN pages AS p2
    ON d.id = p2.dossiers_id
   AND (p2.date > p1.date OR (p2.date = p1.date AND p2.hour > p1.hour))
  WHERE p2.id IS NULL

SQLFiddle


MySQL Generated columns on non-deterministic function

Don't convert the column's value, convert your criterium:

WHERE Last_Used_Time > from_unixtime(1540874005)


Backing up the transaction log will not shrink the physical file.  It will only mark transactions as inactive and potentially mark virtual log file(s) for reuse.  What you're looking to do is actually shrink the file system file, and this can be done through DBCC SHRINKFILE.

Note: shrinking the transaction log, or any other database file for that matter, should not be a routine database task.  Proper sizing will mitigate the necessity for tasks like this.


We finally found the problem!! This database had a Standby Read-Only database but We broked the replication, removed all Standby parameters but We forgot to remove the  replication slot.
Yestarday we tried to remove the replication slot but We couldn't  because the database was not in standby mode:

zabbix_db=# select pg_drop_replication_slot('standby2');
ERROR:  replication slots can only be used if wal_level >= archive

We had to

Shutdown the database,
Change wal_level to archive Startup
Run again the pg_drop_replication (now it works)
Shudown
Disable wal_level
Startup

And.. the autovacuum comes back!!

postgres 19381 19089  1 13:07 ?        00:09:59 postgres: autovacuum worker process   zabbix_db
postgres 19411 19089  2 13:07 ?        00:11:20 postgres: autovacuum worker process   zabbix_db
postgres 19430 19089  1 13:07 ?        00:09:51 postgres: autovacuum worker process   zabbix_db


SQL Server - Windows Group Authentication

You were correct in creating the Windows domain security group.  Add Windows domain accounts to that security group as required.  You were correct in creating a SQL login for the Windows domain group.  SQL will allow authentication by any member of that Windows domain group.  Nothing further is required as far as SQL login creation.  Set permissions as needed within SQL Server.

Have you tested your configuration?  What problems are you encountering?

  By using Windows Authentication, Windows groups can be created at the domain level, and a login can be created on SQL Server for the entire group. Managing access from at the domain level can simplify account administration.

Books Online


Spatialite: use of virtual spatial index for distances

I know it is late but I recently stumbled accross the same problem. It is not working in your example because the search_frame parameter act as a filter and is equivalent to the function RTreeIntersect.

Here since you use a point it will only look for point wich intersect togetether (when using their bounding box).
The idea is to use a bouding box centered on the pp1 point, with side the length of the desired distance.

SELECT pp1.name AS "PopulatedPlace #1",
  GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)",
  pp2.name AS "PopulatedPlace #2"
FROM populated_places AS pp1,
populated_places AS pp2
WHERE GeodesicLength(
  MakeLine(pp1.geometry, pp2.geometry)) < 1000.0
  AND pp1.id <> pp2.id
AND pp2.ROWID IN (
  SELECT ROWID
  FROM SpatialIndex
  WHERE
   f_table_name ='populated_places'
   AND search_frame = ST_Expand(pp1.geometry, 500.0)
ORDER BY 2;

To do that we simply use the function ST_Expand which return the bounding box envelopping the geometry (here it is the point) and expand it uniformly.

So we filter the search as follow : only pp2 point inside the square centered on pp1 with 1000 meter side length will be considered.


How can I best combine "get categories" and "has user access"?

If you have the user id upfront, say id 12, then your query is

SELECT u.name username,GROUP_CONCAT('[',fc.name,']') categories
FROM (SELECT * FROM users WHERE id=12) u
INNER JOIN roles_users            ru  ON u.id = ru.user_id
INNER JOIN forum_categories_roles fcr ON ru.rol_id = fcr.rol_id
INNER JOIN forum_categories       fc  ON fcr.category_id = fc.id
GROUP BY u.name;

If you want to see all the categories for every user, then here is that query:

SELECT u.name username,GROUP_CONCAT('[',fc.name,']') categories
FROM users u
INNER JOIN roles_users            ru  ON u.id = ru.user_id
INNER JOIN forum_categories_roles fcr ON ru.rol_id = fcr.rol_id
INNER JOIN forum_categories       fc  ON fcr.category_id = fc.id
GROUP BY u.name;

Both of these queries return a comma-separated list of categories

I would recommend you also add an additional unique index

CREATE TABLE forum_categories_roles (
  category_id INT(4) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (category_id, role_id) ,
  UNIQUE KEY (role_id,category_id)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Give it a Try !!!


Assuming you can trust the last octet will always be a number:

ORDER BY CONVERT(INT, PARSENAME(Step_Id, 1));


Count Most Recent Browser Used By All Users

Not your exact requirements (instead of ignoring data from users inactive the last year, the following queries ignore all data older than a year) but you can get ideas and change the conditions to more complex if needed:

Count all Login and browser Type combinations, up to a year ago.

; WITH cte AS
( SELECT    *
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
)
SELECT      Type,
            Count = COUNT(DISTINCT Login)
FROM        Browser
GROUP BY    Type ;

Count only most recent browser Type per Login, up to a year ago.

; WITH cte AS
( SELECT    Type,
            Login,
            Rn = ROW_NUMBER() OVER (PARTITION BY Login
                              ORDER BY Timestamp DESC)
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
)
SELECT      Type,
            Count = COUNT(*)
FROM        cte
WHERE       Rn = 1
GROUP BY    Type ;

Count up to 5 recent browser Type per Login,  and up to a year ago.

; WITH cte AS
( SELECT    Type,
            Login,
            Rn = ROW_NUMBER() OVER (PARTITION BY Login
                                    ORDER BY MAX(Timestamp) DESC)
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
  GROUP BY  Login, Type
)
SELECT      Type,
            Count = COUNT(*)
FROM        cte
WHERE       Rn <= 5
GROUP BY    Type ;

Tests at SQL-Fiddle


Table variables have the scope of the batch or stored procedure they are run in. There is no need to drop the table variable at the end of a batch, however you will not be able to reference the table variable in consecutive batches.

If you execute the SQL as one block, without the exec, then your SELECT will be able to 'see' the table variable and select the data from it.

declare @T1 as table
(
    col1 int not null
);

insert into @T1(col1) values (10);

select col1 from @T1; -- returns 10

go

The exec encapsulates a T-SQL batch, so in your example it is in a completely separate batch from the table declaration and the insert statement and therefore the table variable is not in scope.

Exec does not terminate a batch though, the table variable will still be in scope after the exec is run.

declare @T1 as table
(
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

select col1 from @T1; -- returns 10

go

However, the GO command signals the end of a batch, so placing a GO between the insert and select will also mean you are executing 2 separate batches. You would then also get the error

declare @T1 as table
(
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

GO

select col1 from @T1; -- errors

GO


System.OutOfMemoryException in client app

The error you are seeing is being generated at the client end. Typically this error occurs when the client runs out of memory while trying to display or consume the result set.  Try running a smaller subset of queries, and ensure the client is properly disposing objects as they are no longer required.


Disk performance causing SQL Server Command Timeout?

Command timeout simply means that your queries don't return in the 30 seconds allocated by default. What is the size of your database and how much RAM you have?

The high disk usage is most likely a symptom, not a cause, and is caused by table scans, ie. missing indexes. A quick look at the execution plan of your timing out queries will reveal if this guess is correct or not (See Displaying Execution Plans by Using SQL Server Profiler Event Classes.

As a general advice I suggest you approach this as a performance investigation and deploy the well tested Waits and Queues methodology.