Why is GETUTCDATE earlier than SYSDATETIMEOFFSET?

The issue is a combination of datatype granularity / accuracy and source of the values.

First, DATETIME is only accurate / granular to every 3 milliseconds. Hence, converting from a more precise datatype such as DATETIMEOFFSET or DATETIME2 won't just round up or down to the nearest millisecond, it could be 2 milliseconds different.

Second, the documentation seems to imply a difference in where the values come from. The SYS* functions use the high-precision FileTime functions.

SYSDATETIMEOFFSET documentation states:

  SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API.

while the GETUTCDATE documentation states:

  This value is derived from the operating system of the computer on which the instance of SQL Server is running.

Then, in the About Time documentation, a chart shows the following two (of several) types:

System Time = "Year, month, day, hour, second, and millisecond, taken from the internal hardware clock."
File Time = "The number of 100-nanosecond intervals since January 1, 1601."

Additional clues are in the .NET documentation for the StopWatch class (emphasis in bold italics mine):

Stopwatch Class

  The Stopwatch measures elapsed time by counting timer ticks in the underlying timer mechanism. If the installed hardware and operating system support a high-resolution performance counter, then the Stopwatch class uses that counter to measure elapsed time. Otherwise, the Stopwatch class uses the system timer to measure elapsed time.

Stopwatch.IsHighResolution Field

  The timer used by the Stopwatch class depends on the system hardware and operating system. IsHighResolution is true if the Stopwatch timer is based on a high-resolution performance counter. Otherwise, IsHighResolution is false, which indicates that the Stopwatch timer is based on the system timer.

Hence, there are different "types" of times that have both different precisions and different sources.

But, even if that is a very loose logic, testing both types of functions as sources for the DATETIME value proves it. The following adaptation of the query from the question shows this behavior:

DECLARE @Offset DATETIMEOFFSET = SYSDATETIMEOFFSET(),
        @UTC2 DATETIME = SYSUTCDATETIME(),
        @UTC DATETIME = GETUTCDATE();
DECLARE @UTCFromOffset DATETIME = CONVERT(DATETIME, SWITCHOFFSET(@Offset, 0));
SELECT
    Offset = @Offset,
    UTC2 = @UTC2,
    UTC = @UTC,
    UTCFromOffset = @UTCFromOffset,
    TimeTravelPossible = CASE WHEN @UTC < @UTCFromOffset THEN 1 ELSE 0 END,
    TimeTravelPossible2 = CASE WHEN @UTC2 < @UTCFromOffset THEN 1 ELSE 0 END;

Returns:

Offset                 2017-06-07 17:50:49.6729691 -04:00
UTC2                   2017-06-07 21:50:49.673
UTC                    2017-06-07 21:50:49.670
UTCFromOffset          2017-06-07 21:50:49.673
TimeTravelPossible     1
TimeTravelPossible2    0

As you can see in the above results, UTC and UTC2 are both DATETIME datatypes. @UTC2 is set via SYSUTCDATETIME() and is set after @Offset (also taken from a SYS* function), but prior to @UTC which is set via GETUTCDATE(). Yet, @UTC2 appears to come before @UTC. The OFFSET part of this is completely unrelated to anything.

HOWEVER, to be fair, this still isn't proof in a strict sense. @MartinSmith traced the GETUTCDATE() call and found the following:

I see three interesting things in this call stack:

Is starts with a call to GetSystemTime() which returns a value that is only precise down to the milliseconds.
It uses DateFromParts (i.e. no formula to convert, just use the individual pieces).
There is a call to QueryPerformanceCounter towards the bottom. This is a high-resolution difference counter that could be being used as a means of "correction". I have seen some suggestion of using one type to adjust the other over time as long intervals slowly get out of sync (see How to get timestamp of tick precision in .NET / C#? on S.O.). This does not show up when calling SYSDATETIMEOFFSET.

There is much good info here regarding the different types of time, different sources, drift, etc: Acquiring high-resolution time stamps.

Really, it is not appropriate to compare values of different precisions. For example, if you have 2017-06-07 12:01:58.8770011 and 2017-06-07 12:01:58.877, then how do you know that the one with less precision is greater than, less than, or equal to the value with more precision? Comparing them assumes that the less precise one is actually 2017-06-07 12:01:58.8770000, but who knows if that is true or not? The real time could have been either 2017-06-07 12:01:58.8770005 or 2017-06-07 12:01:58.8770111.

However, if you have DATETIME datatypes, then you should use the SYS* functions as the source as they are more accurate, even if you lose some precision as the value is forced into a less precise type. And along those lines, it seems to make more sense to use SYSUTCDATETIME() rather than call SYSDATETIMEOFFSET() only to adjust it via SWITCHOFFSET(@Offset, 0).


Not getting distinct results using case query

You have multiple rows in prod5219 for store store1, and each row has a unique set of values for concept and menusize.

Run this to check that out:

select storenumbers.*
    , prod5219.concept
    , prod5219.menusize
from storenumbers
    left join prod5219 on storenumbers.storenumber = [Prod5219].store

Simply adding distinct to the select clause doesn't magically remove "extra" rows from the output.  distinct simply returns rows that are unique, across all columns returned by the query.


ORA-22285 Error while trying to register an xsd schema on oracle database

Unless specified otherwise, directory objects names are stored as uppercase strings, and you need to reference them like that in such cases:

SQL> !ls -l /home/oracle/hello.txt
-rw-r--r--. 1 oracle oinstall 6 Jan  9 14:05 /home/oracle/hello.txt

SQL> create or replace directory hello as '/home/oracle';

Directory created.

declare
  v_file bfile;
  ret integer;
begin
  v_file := bfilename('hello', 'hello.txt');
  ret := dbms_lob.fileexists(v_file);
end;
  8  /
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEEXISTS operation
ORA-06512: at "SYS.DBMS_LOB", line 803
ORA-06512: at line 6

declare
  v_file bfile;
  ret integer;
begin
  v_file := bfilename('HELLO', 'hello.txt');
  ret := dbms_lob.fileexists(v_file);
end;
  8  /

PL/SQL procedure successfully completed.

SQL>


How to add rows/columns to the table in runtime in SSRS 2008

I don't know how your report table is currently being designed, but I would think the report table list all the rows from the query results (with predefined number of columns, in your case, 2 columns). As long as the query is correctly developed to return all required rows e.g. all enrolled courses, the report table designing part should be fairly straight forward.


How could one monitor MySQL replication?

Once I have asked the similar question there was ultimate Answer. Please have a look at it.

Then you will be clear with the

Seconds_Behind_Master

The Seconds_Behind_Master is not a good Parameter for monitoring the replication.

Try to use mk-heartbeat monitoring script. I use that and it's really reliable.

For documentation of mk-heartbeat, click here.

UPDATE

Try MONyog Web based application which has separate Replication tab and pretty easy to use, with lots of features

This will let you know Status of all slaves and Master on Replication tab for example: Replication topology, Master detail, Slave detail in a tabular format. Also, using Monitors page, you can get alert if seconds_behind_master lags behind certain threshold value.

You can also use the MySQL Community Replication Monitor

The community driven MySQL replication monitoring tools is a collection of scripts in SQL and other languages, to achieve the same result that others can get with expensive applications.


Oracle save mandarin characters with we8iso8859p1

Changing the NLS_LANG value does not affect any data in the database. However setting character set to WE8ISO8859P1 will not work.

When you set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 then you tell the database "The client uses character set WE8ISO8859P1." Thus any Mandarin characters will not be accepted by the database because they are not supported by WE8ISO8859P1.

Use AL32UTF8 or any other character set which supports Mandarin, for example ZHS32GB18030

Apart from that, Java does not use the NLS_LANG setting at all, see Database JDBC Developer's Guide - Globalization Support:

  Starting from Oracle Database 10g, the NLS_LANG variable is no longer
  part of the JDBC globalization mechanism. The JDBC driver does not
  check NLS environment. So, setting it has no effect.

Please have a look at this document how to setup Unicode in Java environment.

Of course, the database NCHAR Character Set has to support Mandarin. Usually this is set to default AL16UTF16, so I assume that's not the issue here.


Performance impact of waiting on a service broker message

You are blocking a worker in SQL Server and workers are limited, subject to max worker threads. This means there should not be thousands of requests blocked in WAITFOR(RECEIVE...) or you'll starve the server of workers.

But the first question that comes to mind is Why no leverage Service Broker Activation? This way you wouldn't be waiting all the time but only when activated because there are messages to receive.


Using dynamic column names in PostgreSQL

RETURN QUERY EXECUTE was introduced with Postgres 8.4.
Your version is just too old and unsupported by now. Upgrade to a more recent version.

Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front.

Returning anonymous records without a column definition list only works for a single record. Else you have to provide a column definition list in the call. Details under his question on SO:
Return multiple fields as a record in PostgreSQL with PL/pgSQL

There are limited ways around this with with polymorphic types. Advanced stuff:
Refactor a PL/pgSQL function to return the output of various SELECT queries

BTW, your function would look like this in modern PL/pgSQL:

CREATE OR REPLACE FUNCTION get_policy_name (
  _id        int,
  _lang      text,
  _def_value text
) RETURNS TABLE (col text) AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format('SELECT COALESCE(%I, col, $1)
           FROM my_table WHERE id = $2'
         , 'col_' || _lang)
   USING _def_value, _id;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT col AS col_fr FROM get_policy_name (1, 'fr', 'foo');

Here, I am simply using a column alias in the call to achieve what you want. Much easier than dynamic column names ...


Here is your original query:
SELECT
    user_actions.*,
    users.twitter_username,
    users.email
FROM
    `user_actions`
    INNER JOIN users
    ON (user_actions.user_id=users.id)
    ORDER BY timestamp
    DESC LIMIT 0, 30
;

First thing I notice is that you are joining two whole tables. Since you only need twitter_username and email from the users table, you should only join from users using three columns : id, twitter_username and email.
Second thing is the LIMIT clause. It is being executed after the join. You should execute it before the join. In your case, you are requesting for the 30 most recent user actions. If you can guarantee that only 30 rows are retreived from user_actions, the join should operate much faster.
If you read the answer from @DTest, his first two bulletpoints already tell you what's wrong the query because of the actions mysql will take in gathering data from each table. The key is to understand what the temp tables will look like while the query is being processed and where the data will reside (memory or disk).
What you need to do is refactor the query to fool the MySQL Query Optimizer. Force the query to produce smaller temp tables. In most cases, config changes in my.cnf should make a dramamtic difference. In other cases, such as this one, refactoring the query may be sufficient.
Here is my proposed change to your query that should work faster:
SELECT
    ua.*,
    u.twitter_username,
    u.email
FROM
    (SELECT * FROM `user_actions`
    ORDER BY timestamp DESC LIMIT 30) ua
    LEFT JOIN
    (SELECT id,twitter_username,email FROM `users`) u
    ON (ua.user_id=u.id)
;

Here are reasons for refactoring the query:
REASON #1
If you look at inline table ua, I retrieve only 30 rows using LIMIT. This will happen no matter how big the user_actions table gets. It is already ordered because the ORDER BY timestamp DESC happens before the LIMIT.
REASON #2
If you look inline table u, it has id,twitter_username,email. The id is needed to implement the join.
REASON #3
I use LEFT JOIN instead of INNER JOIN for two(2) reasons:

Preserve the order of the query based on ua
Display all user actions in case the user_id in the ua no longer exists in the users tables.

Doing these things will force the temp tables to be smaller. Nevertheless, you will still need implement bulletpoint #3 from @DTest's answer to preempt having temp tables land on disk.


How to know which record caused an error

SQL itself doesn't provide any way to do this, an INSERT will always entirely succeed or entirely fail (with one caveat, see #7 below).

So you'll need to do one of the following:

Update/cleanse the data in the source table in advance
Filter the rows to insert, something like WHERE LEN(longfield)<30 will only try to insert rows short enough for the destination field
Properly transform it on the fly: LEFT(longfield,30) will chop off any text that is too long, or use one of the new SQL 2012 functions like TRY_CONVERT, as suggested by Jonathan Fite in the comments.
Insert it one row at a time, using a TRY/CATCH block, and either fix the problem, or log the bad row for action later on
Insert one row at a time, but instead of taking action, just ignore the error on each row, and compare the two tables at the very end.

This assumes you have some key column you can join:

 SELECT TEMP1.*
 FROM TEMP1
 LEFT OUTER JOIN FINAL
 ON TEMP1.Rowid = FINAL.Rowid
 WHERE Final.Rowid IS NULL

SSIS provides a number of tools and options for data transformation and error catching if you're doing large-scale migrations. You can pipe the error rows into a second table, for example.
This won't help you with data conversion errors, but there is one specific kind of error you can "skip and continue": if the error is produced by a unique constraint (a special kind of index that prevents you from entering duplicate data into a table, based on whatever field or set of fields you specify).

This particular constraint can be created with a parameter of IGNORE_DUP_KEY = ON. When this is set to ON only the rows that violate the constraint will fail, the remaining rows will be properly inserted. When it is set to OFF the entire batch will fail.


How do I call a collection of tables with normalized (generic) information

These are often referred to as code tables.  It is common that the primary key will be a short code which can be used as a foreign key in tables which use the data.  Theses tables may contain additional information as required.


I looking for a solution to design database for property real estate

Depends what do you want to optimize for.

For smallest space consumption the easiest way is to create a field where you store the features as bits. For example:

Cuisine - 1
Internet - 2
TV - 4

Then having internet and TV would be 110 = 6. But this is going to be hard to search for and requires some magic to do bitwise operations to extract the features.

A middle ground would be to use SET and store the features which the real estate has aka. values that are set to yes. The downside is that each time you need a new feature it requires schema change and

  A SET column can have a maximum of 64 distinct members. A table can
  have no more than 255 unique element list definitions among its ENUM
  and SET columns considered as a group. For more information on this
  limit, see Section C.10.5, “Limits Imposed by .frm File Structure”.

(from http://dev.mysql.com/doc/refman/5.7/en/set.html)

The most flexible and probably the best performing option for search is to have a feature table where you store all the possible features. And there's a table to store what property has what feature. The downside of this approach is space. Assuming your using InnoDB every row will have a 13 bytes overhead + the two columns (property_id, feature_id) ~ 6 bytes. So you can expect something around 20 bytes * number of properties * avg number of features per properties. (To compare the first option will need number of unique features bits * number of properties)

create table feature (
    id smallint unsigned PRIMARY KEY AUTO_INCREMENT,
    name varchar(64) NOT NULL,
    UNIQUE KEY (name)
);

create table property_feature (
    property_id int unsigned NOT NULL,
    feature_id smallint unsigned NOT NULL,
    PRIMARY KEY (property_id, feature_id)
);

As per your example assuming 100000 properties the theoretical calculations would be:

30 bits * 100000 = 366kb
20 bytes * 100000 * 18 = 35MB (still not something to worry about though)

Note: in reality you would probably store the 30 bits in an unsinged int so it will take 4 bytes. So on disk it would consume around 390kb.


Based on the documentation (here) one must have Control permission on the database to issue a CREATE EVENT SESSION and the same permission should be required for the ALTER EVENT SESSION.


Error publishing SQLCLR C# Function using Visual Studio 2017

I've updated target databases:

 sp_configure 'clr enabled', 1;

Just to be clear, "clr enabled", and sp_configure in general, are instance-level, not database-level configuration.

  I've followed this advice on MSSQLTips article

Yikes. Well, that article on MSSQLTips is more of a problem than the error that you are getting. It would be best if you forgot everything you saw in that article and never looked at it again. It is full of bad advice:

SQLCLR not required for sending email in SQL Server Express (I have tested this and it does work). HOWEVER, it does not work in SQL Server Express LocalDB :-(
You should use SqlString instead of String for input parameter types (and for return types)
Deploying to msdb? Maybe it's just me, but I would never deploy code to msdb (and I would only add code to master if it is physically required, such as when marking a proc as either a start up proc or as a system proc).
UNSAFE permission set? Why? For email? The assembly should be EXTERNAL_ACCESS. Code (assemblies, logins, users, or anything else) shouldn't be given more permissions than necessary.
Setting TRUSTWORTHY ON for msdb is two problems in one:

Setting TRUSTWORTHY ON is lazy. Sure, it's fine for doing proof-of-concepts / quick tests, but it is not a good long-term / production-level option. It's a huge security risk. Instead, Module Signing should be used. Here is a guide I wrote for how to accomplish this, and do so within Visual Studio:
SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1
TRUSTWORTHY is already ON for msdb. At least it is ON by default.

The @body input parameter should be NVARCHAR(MAX), not NVARCHAR(4000). This is, after all, HTML email (i.e. myMessage.IsBodyHtml = True)
No, absolutely do not store your plain-text email password in the .NET code. DLLs / Assemblies are not encrypted. ALL strings are stored as-is at the end of the EXE / DLL / Assembly. It takes almost no effort to display the contents of an assembly, and if you follow this advice, your SMTP server, login, and password will be stored for anyone to see. Either pass those in from a variable, or do a simple query to select from a table. You might even be able to get them from the Windows Registry.

What database are you actually publishing to? Assuming that you are publishing to another database that is not msdb, then you, at least simplistically, in this moment to move forward, need to set TRUSTWORTHY ON for the DB that you are publishing to. But again, enabling TRUSTWORTHY is just for testing (please see: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining).

Now, if you are executing the ALTER DATABASE... manually and then still get the error upon publishing, then most likely you have the "deploy database properties" option selected (in Visual Studio: go to "Project Properties" | "Debug" tab | "Deployment Options" area towards the bottom). TRUSTWORTHY is one of those database properties. So, when "deploy database properties" is checked, then every deployment will set the options to match what is defined in the project. And by default, Trustworthy is not enabled.

Your options are:

If you are deploying database properties:

To enable Trustworthy in Visual Studio (for deployments): go to "Project Properties" | "Project Settings" tab | "Database Settings..." button | "Miscellaneous" tab. Check the box for "Trustworthy" and then click the "OK" button.
If you are not deploying database properties:

Enable trustworthy in the database where you are deploying the assembly (enabling TRUSTWORTHY in msdb won't help)
Once you have this working correctly in development, handle security properly before pushing to production:

SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1

For learning more about SQLCLR in general, please visit: SQLCLR Info. Be sure to check out the "Stairway to SQLCLR" link, which is a series I am writing on this topic on SQL Server Central.


How to identify fragmentation level of the table data itself not the table indexes, and then defrag

Tables in SQL Server can be either organised with a clustered index or have no CI in which case they are a heap.

You need to look at sys.dm_db_index_physical_stats. Despite the name this also does analysis of heaps too (though logical fragmentation does not apply to these, pages cannot be out of logical order as there is no "correct" ordering in a heap).

For tables with a CI the clustered index has an index_id of 1. The leaf level of the CI is the table. For a heap this is given an index_id of 0.


Force NOW to return new timestamps without committing the transaction

I think that that is questionable practice. Any way in which your tests differ from the productive environment increases the danger of testing the wrong thing.
Anyway, you could not use now(), but a different function, say mytimestamp(). On your test system, the function is defined as
CREATE FUNCTION mytimestamp() RETURNS timestamp with time zone
   LANGUAGE sql VOLATILE AS
'SELECT clock_timestamp()';

In the production database, you use
CREATE FUNCTION mytimestamp() RETURNS timestamp with time zone
   LANGUAGE sql VOLATILE AS
'SELECT current_timestamp';

Then you get what you want.  The only difference is that current_timestamp (or now(), which is the same thing) is STABLE, not VOLATILE, which may make queries behave differently (or not, if the function is inlined). But that's exactly the kind of thing that I warned you about in the beginning.


Unexpected result when joining a table in a year range

Let me suggest another approach by adding a Years table.

create table p (id int, criterium decimal(18,2), init_year int, end_year int);

create table years(cyear int);

insert into years values(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020);

insert into p values (1, 93.0, 2018, 2020), (2, 50.0, 2015, 2017), (3, 74.5, 2010, 2012);

Now you can expand dates between initial and ending date, then simply sum year values that match user dates.

select id,
       cyear,
       (criterium / ((end_year - init_year) + 1.0)) as va
from   p
join   years
on     cyear >= init_year
and    cyear <= end_year
order by id;

id | cyear |        va
-: | ----: | --------:
 1 |  2018 | 31.000000
 1 |  2019 | 31.000000
 1 |  2020 | 31.000000
 2 |  2017 | 16.666667
 2 |  2015 | 16.666667
 2 |  2016 | 16.666667
 3 |  2010 | 24.833333
 3 |  2011 | 24.833333
 3 |  2012 | 24.833333

db<>fiddle here

select sum((criterium / ((end_year - init_year) + 1.0))) as total
from   p
join   years
on     cyear >= init_year
and    cyear <= end_year
where  cyear >= 2016 and cyear <= 2018;

|     total |
| --------: |
| 64.333333 |

db<>fiddle here


Access 2013: Merging the same table-field with different data

create a view likes this:

    select * from table1
    union
    select * from table2
    union
    ...
    select * from table6


It appears this has already been answered here.

The answer references this article.

From the answer:

  From the article:

use strict;
use DBI;
use XML::Generator::DBI;
use XML::Handler::YAWriter;

my $dbh = DBI->connect ("DBI:mysql:test",
                       "testuser", "testpass",
                       { RaiseError => 1, PrintError => 0});
my $out = XML::Handler::YAWriter->new (AsFile => "-");
my $gen = XML::Generator::DBI->new (
                               Handler => $out,
                               dbh => $dbh
                           );
$gen->execute ("SELECT name, category FROM animal");
$dbh->disconnect ();


Simple query not returning blank records

Try:

SELECT ASCII(CodeNum)
FROM tableA

To see the ascii number of the character in that field.


When you say:

  On the source machine, I executed all the required steps to enable my
  Test database as well as backing up the certificate and private key.
  No issues here.

We don't really know the steps you took.

Here is a query you can run to see if the database is encrypted on the target server.  Look at the EncryptionState column.

SELECT
    db_name(database_id) as DatabaseName,
    Case
        when encryption_state=0 then 'No Database Encryption'
        when encryption_state=1 then 'Unencrypted'
        when encryption_state=2 then 'Encryption In Progress'
        when encryption_state=3 then 'Encrypted'
        when encryption_state=4 then 'Key Change In Progress'
        when encryption_state=5 then 'Decryption In Progress'
        when encryption_state=6 then 'Protection Changes In Progress'
    end as EncryptionState,
    percent_complete
FROM sys.dm_database_encryption_keys


Multi-master + slaves replication in MySQL

PROBLEM

Looking at the diagram and seeing the red line, I immediately thought of one thing that's missing.

The root of the problem is that both masters require log-slave-updates in my.cnf. Why ?

When a master receives a binlog event from its relay logs, it cannot replicate to any other Slave if it cannot record a binlog event into its local binary logs. For a MySQL Instance to be both Master and Slave, it has to be allowed to record Slave-based binlog events into its Binary Logs so other Slaves can read the same event.

SOLUTION

All you need to do is add this to each Master's my.cnf

[mysqld]
log-slave-updates

and restart mysqld.

Give it a Try !!!

CAVEAT #1

I have discussed log-slave-updates in this situation before

Oct 09, 2013 : Multi-master and multi slave
Jan 31, 2013 : Master to Slave to Slave Configuration in MySQL
Feb 06, 2012 : Converting 3 Master + 1 Slave to 4 Master - Any advice?
Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines

CAVEAT #2

You need to adjust the diagram for another reason. The blue arrows need to be reversed. Why ?

In a diagram, any DB Server can have multiple arrows pointing out, but can only have one arrow coming in. This is due to a Slave only having one Master.
MySQL Replication can only run CHANGE MASTER TO from one Master.

UPDATE 2014-03-07 12:12 EST

In your last comment, you said

  What about updates between masters M1 & M2. M1 and M2 are both slaves to each other. If M1 sends update to M2. Will M2 send the same update back to M1? Will it go on forever ?

Yes, M2 will send the same update back to M1.

However, the update will not get executed again on M1. Why ?

Each binlog event and each relay log event includes the server-id of the event. This allows MySQL Replication to work as follows for a Master that is also a Slave:

Server sees relay log event
Server examines server-id of relay log event
If server-id of relay log event is the same as my server-id, do not execute
If server-id of relay log event is not the same as my server-id

Execute relay log event
If log-slave-updates is enabled, record relay log event in local binlogs

I have discussed server-id issues like this before

Jul 06, 2012 :  Screwed up replication by sharing server ids
Dec 27, 2011 : Mysql thinks Master & Slave have the same server-id
Dec 07, 2011 : Master and Slave having same id's

As long as M1, M2, S1, S2 all have unique server-ids, you should have no issues.


Check on whether a person is subscribed to another person

Test

SELECT F.USER_ID,
       F.FOLLOWER_ID,
       U.USERNAME,
       U.NAME,
       U.LAST_LOGIN,
       F2.FOLLOWER_ID IS NOT NULL AS IS_FOLLOWER
FROM FOLLOWERS AS F
INNER JOIN USERS AS U ON (U.USER_ID = F.USER_ID)
LEFT JOIN FOLLOWERS AS F2 ON F.FOLLOWER_ID = F2.FOLLOWER_ID
                         AND F2.USER_ID = 89
WHERE F.USER_ID = '46'


Adding logging statements at start and end of all stored procedures

I would advise against programmatically modifying all the stored procedures in a database like that.  Trying to ensure no edge-case causes a modification to be made erroneously will prove quite difficult.

Instead of modifying all the stored procedures, why not use Extended Events to capture the information you're interested in.  This will

Not impact performance nearly as much
Allow you to enable/disable monitoring as required, without modifying any stored procedures.
Not affect execution plans for the stored procedures.

SQLTips has a good example of several ways to capture stored procedure execution history without modifying the stored procedures.


How to select students that failed at least at all courses that student with id = 1 failed?

Here is one way of getting your desired results

SELECT *
FROM   STUDENT S
WHERE  NOT EXISTS (SELECT g1.course_id
                   FROM   GRADE g1
                   WHERE  g1.student_id = 1
                          AND g1.grade <= 4
                   MINUS
                   SELECT g2.course_id
                   FROM   GRADE g2
                   WHERE  g2.student_id = S.student_id
                          AND g2.grade <= 4)

For each student it checks with MINUS whether there are any courses that student_id = 1 has failed that are not in the set of failed courses for the student under focus.

If there are no such courses then the NOT EXISTS evaluates to true and the student is returned.

student_id = 1 will be returned as they have obviously failed at least as many courses as themselves so you may want to filter that out.


Best Practice for Passing and Working With Parameters Security/Performance

He seems confused. Using the same value from a local variable should be no different to using it from an input parameter. Is your colleague getting the wrong end of the stick with regard to using parametrised queries (sometimes called prepared statements) instead of full ad-hoc SQL?

For example:

resultSet = dbconnector.getRS('EXEC sp_foo_GET \''+inputVariable+'\');

could be an injection route and moving the value between variables inside the procedure will not affect it at all as the injected code will not be seen by the procedure. If inputVariable were to have the value xxx'; WAITFOR DELAY '00:10:00'-- the SQL sent to the server would be

EXEC sp_foo_GET 'xxx'; WAITFOR DELAY '00:10:00'--'

Your procedure would run with he input parameter set to xxx and would not know of the extra code at all, it is run after the procedure call has completed.

With a prepared statement, assuming your DB access library properly supports them and isn't doing ad-hoc silliness under the hood, this would not happen:

resultSet = dbconnector.getRSWithParams('EXEC sp_foo_GET ?', [inputVariable]);

Your procedure would be called with the value xxx'; WAITFOR DELAY '00:10:00'-- rather than that extra code being seen as something to execute.

Of course the technique might be part of a larger pattern he wants you to follow the purpose of which will become clear as you do more advanced things yet which actually does nothing useful (other than getting you into the habit of following the pattern) at this stage, but I suspect he is simply misunderstanding and therefore adding unnecessary work. Could you ask him to explain in more detail? Perhaps he could provide you with an example call that he thinks the pattern will protect against?

After Comment Re. Performance

As per your comment, while I doubt there is any security reason for this pattern the new explanation that it could make a performance difference is more valid though you might find in most cases it makes no difference and in  some it reduces rather than increases performance so benchmarking rather than blindly using the pattern is recommended.

The issue comes from query plan caching and how input parameters & literals are handled differently to local variables. I'll not go into it into massive detail here as we are straying away from the original question (search in the MS documentation and the web generally around the phrases "parameter sniffing", "query plan cache", and "OPTIMIZE FOR, then ask a new question if you need more clarity after reading a few things) but as a starter-for-ten: in the example you give above what you are doing with the local variable is effectively the same as declaring the procedure with OPTION (OPTIMIZE FOR (@parAcct UNKNOWN)). The affect of this, as with any optimisation hint, can vary a lot depending on the queries being run in the procedure and the balance of the data touched by them.

The difference between using input parameters, literals and local variables with respect to cached plans can be significant when debugging performance issues (it can mean the performance profile you see when debugging/testing is quite different to that seen in production if you are not aware of the issue). http://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/ covers the issue from this particular context.


I'm confused about High Availability/Always On

AGs are a lot more like a mix of mirroring and clustering than log shipping.

Clustering would let you have multiple instances that fail over from one node to another node, but the nodes and disks are all absolutely distinct and only one instance runs at a time (though you can then load up other instances on the same set of nodes and make it more complicated if you wanted to).

Mirroring copies over the logs in real time (not quite log shipping but that's where the similarity comes from; it's "shipping" logs) and lets you automatically fail over if you have a witness. However, you have to specify the primary and secondary names in the connection string in order for it to work, and you can't go past those initial two nodes.

AGs have the best of all worlds. It's mirroring under the covers so it's live, but it lets you read from the secondaries (and the tech behind that is far superior than any home-grown stuff).

AGs also skip the shortcomings of mirroring because you can have multiple nodes, and you don't specify any of them in the connection string! You get a listener name and use THAT in the connection string, clustering fails over that listener name between nodes as required (while sending off those read only connections to the secondaries). Later on you can add/remove/replace nodes without modifying your connection strings at all.


If you use use_remote_estimate be sure to run ANALYZE the foreign table  (I see estimations pretty close with the returned, you'd probably did it). Also, the pushdown improvements are not available in <9.5 version. I also assume that you have the same table structure on the remote server either (including indexes). If a bitmap is needed due to the low cardinality, it won't use the index due to the limitations on the pushdown mechanism. You may want to reduce the amount of returned rows to force a BTREE index scan (timestamp ranges). Unfortunately, there is no clean way to avoid the SeqScan on the remote server if the filter returns +10% of the rows of the table (may vary this percentage if the planner considers that scanning the whole table is cheaper than seek reads). If you are using SSD, you will probably find useful to tweak random_page_cost).

You can use CTE to isolate the GROUP BY behavior:

WITH atable AS (
    SELECT "IncidentTypeCode"
    FROM PUBLIC ."IntterraNearRealTimeUnitReflexes300s"
    WHERE
       ("IncidentDateTime"
              BETWEEN '2016-05-01 00:00:00'::TIMESTAMP WITHOUT TIME ZONE
                  AND '2016-05-02 00:00:00'::TIMESTAMP WITHOUT TIME ZONE)
)
SELECT atable."IncidentTypeCode", COUNT(atable.IncidentTypeCode)
FROM atable
GROUP BY atable."IncidentTypeCode"
ORDER BY atable."IncidentTypeCode";


Store massive values data from remote controllers

3.65B rows a year is a lot.  How many year's data will you keep?

PARTITIONing?  Don't do that until you have a reason.  One reason is that you want to delete data that is over a year old.  In which case, use a rolling RANGE of partitions based on month or week.

Another thing to consider -- will you need the details after some period of time?  Or can you simply summarize the data, then throw away the raw data.  This will potentially save a lot of space.

One text field -- it depends on what queries you will be doing.  If you want the "average temperature" for last week, a single text field would make it 'impossible' in SQL.

What do the queries look like?  Probably they want summary info.  No amount of indexing will salvage the performance of a huge table.  You must do incremental summarization.

If controller 123 has data scattered over 3.65B rows, it will take hours, maybe days to fetch all the info for that one controller.  Think again whether that is really the query you want to run.  And think about the other queries.

You must present all the queries up front in order to design this table optimally.


Is there a better ticketing system to check PostgreSQL issues?

There is no alternative.

Many people would like to have a bug tracker, and there are regularly spirited attempts to introduce one. They usually fail because of lack of buy-in by the community.

One of the problems is that it must be possible to continue using e-mail as an interface.
The other problem is that PostgreSQL does not want to become dependent on third-party tools.

PostgreSQL has been here for over twenty years, and we want to stay around for more. Tools come and go, e-mail will remain.

The archives offer a pretty good search functionality.

For example, if you want to search for previous discussions about bug trackers, this search on the Hackers list will bring many results.


Drivers enable specific communication methods between applications.  Some communication methods are very generic (such as OLEDB) and work with a variety of applications.  Others are specific and provide interaction only between one application type.

SQL Server ships with a few drivers which are usually enough to enable it to communicate with other databases as required.  Additional drivers give additional capabilities.  For example, in my environment I have installed Oracle drivers so that some of our SQL Servers can communicate directly with an Oracle database server.

For your second question about where the drivers need to be installed, that depends on where the work is taking place.  If you are writing an application to run on your local client machine to work with Excel spreadsheets then you need the Excel driver on your local client, because that is where the work is taking place.

But I do not recommend having SQL Server work with Excel documents.  You should write something that will extract the data from excel (using the excel drivers) and then push that data to SQL Server (using ODBC or OLEDB drivers).  The same process could work in reverse.


What are possible ways to migrate SQL Server DB to PostgreSQL DB?

You can try to use Foreign Data Wrapper and tds_fdw

Or you can try pgloader.

Both work perfectly, are easy to set up and are packaged on any serious Linux distributions.


Is possible to bypass the 32 bit limitation in MongoDB?

You could configure sharding  because 2Gb limit only applies to individual mongodb processes.
Pls refer the documentation
sharded-clusters


OBSERVATION #1
You have innodb_thread_concurrency set to 8. In MySQL 5.5, it is 0 by default. Setting innodb_thread_concurrency to 0 allows InnoDB to decide the best number of concurrency tickets to handle at the same time. I discussed way back on May 26, 2011 : About single threaded versus multithreaded databases performance
OBSERVATION #2
You have innodb_log_file_size set to 179M (187695104). If you have already set this value based on Proper tuning for 30GB InnoDB table on server with 48GB RAM, then 179M is set for your environment. If you did not change this and there are lots of data to change with the INSERT ... ON DUPLICATE KEY UPDATE, then you should consider raising the log file size to 1G. Please follow the steps from my post How to safely change MySQL innodb variable 'innodb_log_file_size'?
OBSERVATION #3
Since MySQL 5.5's InnoDB Storage Engine can access multiple CPUs and multiple core, you need to set innodb_buffer_pool_instances to a number higher than 1. I just discussed this a week ago in another post : Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?.
Essentially, you need to find out how many physical cores there are (numactl --hardware), and set innodb_buffer_pool_instances to that number.
Give it a Try !!!


Permissions required to alter a database file

You need to GRANT ALTER DATABASE permission to the user.

GRANT ALTER ON DATABASE:: joneslocker2 TO username

Below script will help you to find what permissions are assigned at the database level :

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
  JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

Check my answer here for an example on why you are getting the error.

Refer to : Database Engine Permission Basics


How Can I generate a data dictionary which contains the data types of a Json file.

You can not get all the types from a csv or json file.

Lets check the following example:

asd;3534;0674;slfo

What are the types? The third column with the value 0674 contains a string or an integer, decimal, float or what? Has that leading 0 any meaning? If so, it should be a string/character data.

The case is similar with the JSON files: Dates are stored as strings in those files (for example), you can not determine what its type. There should be values in a sample data which seems to be valid dates, but what is those values are some kind of category identifiers or generated identifiers (2014-01-24 could be for example 'marketing campaign' (id = 01) in the year 2014 for the partner with id of 24).

Conclusion: you can get a prediction from sample data, but you have to determine the real type manually (or at least revise the predictions). You have to 'add' the business meaning to the determination process -> you have to know what does that data mean to determine the data type required to store that data.

Also please note, that the RFC of the CSV files is not determines the "string identifier" character. It could or could not wrap the string with '"' or any other characters.


Optimizing WHERE condition for TIMESTAMP field in MySQL SELECT statement

For the specific query:

select sum(diff_ms)
from writetest_table
where time_on > '2015-07-13 15:11:56' ;     -- use single quotes, not double

an index on (time_on, diff_ms) would be the best option. So, if the query runs often enough or its efficiency is crucial to your application, add this index:

ALTER TABLE writetest_table
  ADD INDEX time_on__diff_ms__ix      -- pick a name for the index
    (time_on, diff_ms) ;

(Not related to the question)
  And really, change the table's engine to InnoDB. It's 2015 and MyISAM's funeral was some years ago.
(/rant)


Associating on the ID of an association table

Just to get this part out of the way, what is the intention of keeping the historical data? If it is for the app to allow users to interact with on some level, then ok, as that is still a transactional need. Else, if it is merely for reporting purposes, that could be tracked entirely in a separate server / database / schema as you are really talking about a Slowly Changing Dimension (SCD). If it only exists for reporting then no need to complicate your transactional model with anything but the current info.

That being said, I will assume the data is needed for transactional purposes, in which case there is no question (in my mind, at least :) that Option 2 (i.e. relating the Price info with the Supplier and Product relationship table) is the only way to go. If you relate the Price properties back to their respective sources then you allow for invalid combinations of Suppliers and Products that don't exist in the SuppliersAndProducts table. And while performance and maintainability are very important considerations when designing, they are secondary to data integrity as that is the primary responsibility of the database.

Some notes:

It is better to use the table name + "id" rather than the generic "id". This will make writing queries a lot easier as the field name will be the same between tables. Queries in general will be more readable.
While I also like naming tables as plural (it just sounds better), it does make things easier to use the singular. This way the "id" field of the table includes the table name without sounding odd, such as "SuppliersID" (as opposed to "SupplierID"). If you ever have to code automated processes against the tables, it is much easier to assume that the ID field is simply "{TableName}ID", else you might need to hit the database to do a look up. And for new people being trained it is just easier to know that any table will have a standard ID field name, which makes writing queries faster. This is why I no longer use plural table names :). So, for example, SuppliersAndProduct would be SupplierAndProduct or maybe even SupplierXProduct.
If it weren't for the historical values then SupplierAndProduct wouldn't need an auto-incrementing ID as the combination of SupplierID and ProductID would be the composite PK.
But since we do have that history we can do two things with that relationship table:

PK is the SupplierAndProductID field
Unique Index (representing an "Alternate Key") on the combination of the SupplierAndProductID, SupplierID, and ProductID fields

For the SupplierAndProductPrice (or SupplierXProductPrice) table we would have (just in terms of the Key fields here; no changes to the "price" and date, etc fields):

- supplier_and_product_price_id (autoincrement) -- I will stop PascalCasing now ;-)
- supplier_id (FK to supplier_and_product; could also FK to supplier)
- product_id (FK to supplier_and_product; could also FK to product)
- supplier_and_product_id (FK to supplier_and_product)

In this model, since the supplier_and_product_id field lacks any real meaning, we bring along the other two fields and the combination of all 3 will relate back to the Unique Index on supplier_and_product. I generally try to avoid FKs to Unique Indexes/Constraints but this is one situation where it makes sense to do it.

Putting all 3 fields together in a Unique Index in order to reference the FK to it allows for bringing over the two needed fields (supplier_id and product_id) while guaranteeing that the combination of those two is always a valid combination (as it is enforced by the FK).
I would recommend against combining these into a single table as it duplicates relationship info of Product and Supplier when the Price changes during the from and to dates of the Product-to-Supplier relationship. But, that is just for the transactional side. On the reporting side it is definitely a good idea to combine them as suggested by @JonofAllTrades.
Regarding the need for the extra WHERE condition to check the date against the from and to dates in supplier_and_product: this is not a bad idea, but is technically just a safe guard, assuming that the app doesn't allow a given Price entry to have dates that fall outside of the time-frame that the Product is being supplied by that Supplier. But if the dates are somewhat validate against such oddities, then you can now get away with the simplified query of Option 1 (since the product_id and supplier_id fields are there) while having the integrity of Option 2 (since those fields also FK back to the supplier_and_product relationship table). Which means you really should FK the product_id and supplier_id fields in the Price table back to both the relationship table and their respective parent tables since it will help with JOINs between Price and the two parent tables.


It turns out that the problem was to do with the index that was being selected as part of the query plan.

The query was accepting a range as part of its set of parameters. For reasons I won't go into now we were passing the range start and end date to be the same value.

When we were making the call directly we were hard coding these parameters and Oracle was deducing that they represented a single date, whereas when passing them via code the range was passed as bind variables.

This meant that Oracle selected to scan across all partitions in one case, and against a specific partition in the other.

We ultimately fixed the query by passing in just a single date parameter, since we realised we didn't need a range!

A rather peculiar situation admittedly!


Syntax error when trying to add columns to table in DBeaver (postgresql)

According to Postgres docs the correct syntax is:

ALTER TABLE <your_table>
    ADD COLUMN <column name> <column type>,
    ADD COLUMN <column name> <column type>,
    ADD COLUMN <column name> <column type>;

Have a look at Postgres tutorials on PostgreSQL ADD COLUMN: Add One Or More Columns To a Table.

I've set up a minimal example that reproduces your error:

CREATE TABLE newtable (id int);

ALTER TABLE newtable
    ADD COLUMNS f1 float8, f2 int;

  ERROR:  syntax error at or near "float8"
  LINE 2:     ADD COLUMNS f1 float8, f2 int;                          ^

ALTER TABLE newtable
    ADD COLUMN f1 float8, f2 int, f3 text;

  ERROR:  syntax error at or near "f2"
  LINE 2:     ADD COLUMN f1 float8, f2 int, f3 text;

Using the correct syntax:

ALTER TABLE newtable
    ADD COLUMN f1 float8,
    ADD COLUMN f2 int,
    ADD COLUMN f3 text;
✓

db<>fiddle here


MySQL get a lock to update certain rows and prevent other sessions to read that row

Unless you set the transaction level to read uncommitted, a transaction will see the unmodified row. The second session only has to acknowledge the existence of the first session when both sessions want to lock that row, otherwise they will act as if the other transaction doesn't exist (as that session could rollback at any time) - which is the whole point of using transactions.

In MySQL, you can get a locking read by either explicitly using SELECT .. FOR UPDATE or SELECT .. LOCK IN SHARE MODE, or by using the "serializable"-isolation level for the second transaction, which implicitly adds the LOCK IN SHARE MODE for you to every select in that transaction:

  This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

So if you can force the second transaction to use that transaction level and to not enable autocommit, you can lock that row for this session; but keep in mind that this mode can slow down concurrent queries, as every read locks, so you might want to use this mode only when you need it.

This behaviour complies with the requirements of the sql standard. The exact implementation is a design choice, and other databases will do this differently. E.g. the MSSQL Server will not read your modified, uncommitted row (unless the isolation level is read uncommitted).


Mysql - Select next x records even if reaching rowset end

Let me try for a pure SQL solution:

SELECT id
FROM
   (  ( SELECT *, 0 AS chck
        FROM tableX
        WHERE id > @givenID
        ORDER BY id
        LIMIT 3
      )
    UNION ALL
      ( SELECT *, 1
        FROM tableX
        WHERE id <= @givenID      -- just in case of a small table (< 3 rows)
        ORDER BY id
        LIMIT 3
      )
   ) AS t
ORDER BY chck, id
LIMIT 3 ;

Test at SQLfiddle.

The query is basically an improvement on Jehad's answer. It doesn't use a variable (@rank) and the two internal LIMITs will result in improved efficiency on a large table.

Assuming there is an index on id, the two subqueries will use the index and only need to fetch 3 ids and 3 rows each from the table. The external table (t) after the UNION will have a maximum of 6 rows to sort.


Spike and time out errors every x th minute

This is to help others as this was a very tricky solution to find:

We found below to be the problem where all the available core schedulers were not visible online

i.e we had SQL server enterprise edition because of which all 128 cores were not used and instead visible online were 40 only.

After upgrading to enterprise: core license , we were able to use all schedulers and viola!

Hope this helps as sometimes licensing could be tricky and we might skip


How do I grant a SELECT permission to an individual user who's part of a large group that is explicitly denied?

DENY overrides GRANT, and an inherited DENY even overrides a direct GRANT.  And a DENY at the schema or database level overrides a GRANT at the object level.

So you won't be able to directly override the DENY.

You can create a view, synonym, or stored procedure owned by the same user as SuperSecretData and grant them access to that.

  is there a better way to GRANT SELECT to almost all entities on my SQL server to "Domain Users" except for a couple of tables that I'd only want some specific users to have SELECT permissions on?

Yes.  Never use the built-in roles, and always use schema-level grants.  Then you can create separate schemas for additional security.  Eg

grant select on schema::dbo to ...

instead of a built-in role

alter role db_datareader add . . .

or a database-level grant

grant select to ...


ALTER TABLE online by keeping data as BLOBs

That's not what this is saying. Few systems can change a schema without taking the system offline -- even modern databases typically require exclusive table locks and table-rewriting to remove a column. This presents a problem in some workloads. Think about a contact book where you can add a field, and a delete a field. If each of those operations changed the schema of the data by issuing an ALTER TABLE, you'd have absolutely chaos. That's not how schema is supposed to work.

So you have two traditional options,

Schemaless data,
Entity–attribute–value model (which is frequently both schema-less and type-less, and slower.)

Schemaless Data

Storing the column as a serialized blob may sound bad, but that's only because DBAs are usually not capable of that level of engineering and there could be dragons there. It's just out of their pay grade to implement binary types. That's exactly what happens though with advanced types under the hood. Take for instance, in PostgreSQL,

JSONB
hstore

These types are in fact binary blobs under the hood. The database just provides operators and stringification for them. In PostgreSQL they're TOASTABLE binary types.

Before JSONB, and before hstore, we used to achieve the same thing with Storable::nfreeze. This is what the author is calling extreme demoralization. You serialize the version of the object (metadata), and the object itself and dump the whole thing into the database.

DBA's write in a declarative language (SQL) and model and query relational data. That's entirely different than the skillset required to create something like JSONB. We're a different community from Stackoverflow because we have a different skill set. How many databases have hstore, or jsonb -- or even SQL Arrays? These are hard problems. Getting it right is a niche skill for a DBA.


Does partitioning help if you don't query on the partition value?

Partitioning is not typically a performance enhancer.  Normally, partitioning is employed to enable efficient data management.  For instance, you can employ partitioning to easily swap old rows out of a table into an archive table, one partition at a time.
Querying against a partitioned table where your query does not include the partitioning key forces SQL Server to look at the entire table.  This is less efficient than simply scanning or seeking into a non-partitioned table because each partition is essentially a table, necessitating a UNION ALL style query against all the partitions.  This is not particularly obvious when looking at a query plan for a partitioned table, although you can see it if you look carefully at the pop-up properties for an index or table seek or scan:

To make this more obvious, lets set up a simple test rig in tempdb, with a date-based partitioning key:
USE tempdb;
GO
IF OBJECT_ID(N'dbo.Tab', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Tab;
    DROP PARTITION SCHEME PartScheme;
    DROP PARTITION FUNCTION PartFun;
END
GO

CREATE PARTITION FUNCTION PartFun (datetime)
AS RANGE LEFT
FOR VALUES (
      N'2012-01-01T00:00:00'
    , N'2012-04-01T00:00:00'
    , N'2012-07-01T00:00:00'
    , N'2012-10-01T00:00:00'
    , N'2013-01-01T00:00:00'
    , N'2013-04-01T00:00:00'
    , N'2013-07-01T00:00:00'
    , N'2013-10-01T00:00:00'
    , N'2014-01-01T00:00:00'
    , N'2014-04-01T00:00:00'
    , N'2014-07-01T00:00:00'
    , N'2014-10-01T00:00:00'
);

CREATE PARTITION SCHEME PartScheme
AS PARTITION PartFun
ALL TO ([PRIMARY]);

IF OBJECT_ID(N'dbo.Tab', N'U') IS NOT NULL
DROP TABLE dbo.Tab;
CREATE TABLE dbo.Tab
(
    TabID int NOT NULL
    , CreateDate datetime NOT NULL
    , Data1 varchar(100) NOT NULL
    , Data2 varchar(10) NOT NULL
    , Data3 varchar(1000) NOT NULL
    , CONSTRAINT PK_Tab
        PRIMARY KEY CLUSTERED
        (CreateDate, TabID)
) ON [PartScheme](CreateDate);

This will populate the table with 1,000,000 rows with randomly generated data evenly spread over all partitions:
;WITH Ten AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, Million AS
(
    SELECT Num = (t6.Num * POWER(10, 5))
        + (t5.Num * POWER(10, 4))
        + (t4.Num * POWER(10, 3))
        + (t3.Num * POWER(10, 2))
        + (t2.Num * POWER(10, 1))
        + (t1.Num)
    FROM Ten t1
        CROSS JOIN Ten t2
        CROSS JOIN Ten t3
        CROSS JOIN Ten t4
        CROSS JOIN Ten t5
        CROSS JOIN Ten t6
)
INSERT INTO dbo.Tab (TabID, CreateDate, Data1, Data2, Data3)
SELECT m.Num, DATEADD(DAY, m.Num % 1000, N'2012-01-01T00:00:00')
    , CONVERT(varchar(100), CRYPT_GEN_RANDOM(100))
    , CONVERT(varchar(10), CRYPT_GEN_RANDOM(10))
    , CONVERT(varchar(1000), CRYPT_GEN_RANDOM(1000))
FROM Million m;

Let's go ahead and create a non-clustered index using our partitioning scheme:
CREATE NONCLUSTERED INDEX IX_Tab
ON dbo.Tab(Data1)
ON [PartScheme](CreateDate);

Now, let's look at three "typical" queries you might execute (the first query just gets an actual value out of the table, which is then used in the WHERE clause for the three queries):
DECLARE @d varchar(100);
SELECT TOP(1) @d = t.Data1
FROM dbo.Tab t
WHERE t.CreateDate > N'2013-08-24T00:00:00';

SELECT Data1
FROM dbo.Tab
WHERE Tab.Data1 = @d;

SELECT Data1
FROM dbo.Tab
WHERE Tab.Data1 = @d
    AND tab.CreateDate >= N'2013-08-23T00:00:00'

SELECT Data1
FROM dbo.Tab
WHERE Tab.Data1 = @d
    AND tab.CreateDate >= N'2013-08-23T00:00:00'
    AND tab.CreateDate <= N'2013-08-29T00:00:00';

Here is a quick screenshot of the execution plans so you can see the difference adding the partitioning key to the WHERE clause can make:

The "query costs relative to the batch" numbers are instructive here.  The first query consumes 50% of the batch cost since it performed a scan of all 13 partitions.  It does that since it has no idea where in the table the particular value of Data1 exists.  The second query consumes only 23% by virtue of the "Start Date" parameter, which can eliminate half the partitions.  Finally, the third query only consumes 4% since it only needs to look at a single partition, because we included a "start date" and an "end date".
The "statistics" for the 3 queries are:

Table 'Tab'. Scan count 13, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tab'. Scan count 6, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tab'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What if we add one of the other columns to these queries:
DECLARE @d varchar(100);
SELECT TOP(1) @d = t.Data1
FROM dbo.Tab t
WHERE t.CreateDate > N'2013-08-24T00:00:00';

SELECT Data1
    , Data2
FROM dbo.Tab
WHERE Tab.Data1 = @d;

SELECT Data1
    , Data2
FROM dbo.Tab
WHERE Tab.Data1 = @d
    AND tab.CreateDate >= N'2013-08-23T00:00:00'

SELECT Data1
    , Data2
FROM dbo.Tab
WHERE Tab.Data1 = @d
    AND tab.CreateDate >= N'2013-08-23T00:00:00'
    AND tab.CreateDate <= N'2013-08-29T00:00:00';

The execution plans:

The statistics for those:

Table 'Tab'. Scan count 13, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tab'. Scan count 6, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tab'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, lets just do away with the partitioning, and re-run all those tests:
IF OBJECT_ID(N'dbo.TabU', N'U') IS NOT NULL
DROP TABLE dbo.TabU;
CREATE TABLE dbo.TabU
(
    TabID int NOT NULL
    , CreateDate datetime NOT NULL
    , Data1 varchar(100) NOT NULL
    , Data2 varchar(10) NOT NULL
    , Data3 varchar(1000) NOT NULL
    , CONSTRAINT PK_TabU
        PRIMARY KEY CLUSTERED
        (CreateDate, TabID)
) ON [PRIMARY];

;WITH Ten AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, Million AS
(
    SELECT Num = (t6.Num * POWER(10, 5))
        + (t5.Num * POWER(10, 4))
        + (t4.Num * POWER(10, 3))
        + (t3.Num * POWER(10, 2))
        + (t2.Num * POWER(10, 1))
        + (t1.Num)
    FROM Ten t1
        CROSS JOIN Ten t2
        CROSS JOIN Ten t3
        CROSS JOIN Ten t4
        CROSS JOIN Ten t5
        CROSS JOIN Ten t6
)
INSERT INTO dbo.TabU (TabID, CreateDate, Data1, Data2, Data3)
SELECT m.Num, DATEADD(DAY, m.Num % 1000, N'2012-01-01T00:00:00')
    , CONVERT(varchar(100), CRYPT_GEN_RANDOM(100))
    , CONVERT(varchar(10), CRYPT_GEN_RANDOM(10))
    , CONVERT(varchar(1000), CRYPT_GEN_RANDOM(1000))
FROM Million m;

CREATE NONCLUSTERED INDEX IX_TabU
ON dbo.TabU(Data1)
ON [PRIMARY];

Now, the first set of queries:
DECLARE @d varchar(100);
SELECT TOP(1) @d = t.Data1
FROM dbo.TabU t
WHERE t.CreateDate > N'2013-08-24T00:00:00';

SELECT Data1
FROM dbo.TabU
WHERE TabU.Data1 = @d;

SELECT Data1
FROM dbo.TabU
WHERE TabU.Data1 = @d
    AND TabU.CreateDate >= N'2013-08-23T00:00:00'

SELECT Data1
FROM dbo.TabU
WHERE TabU.Data1 = @d
    AND TabU.CreateDate >= N'2013-08-23T00:00:00'
    AND TabU.CreateDate <= N'2013-08-29T00:00:00';

And the plans:

And the stats:

Table 'TabU'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabU'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabU'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

All three queries now have the same "cost", which is substantially lower than the partitioned version.
The queries with Data1 and Data2 columns:
DECLARE @d varchar(100);
SELECT TOP(1) @d = t.Data1
FROM dbo.TabU t
WHERE t.CreateDate > N'2013-08-24T00:00:00';

SELECT Data1
    , Data2
FROM dbo.TabU
WHERE TabU.Data1 = @d;

SELECT Data1
    , Data2
FROM dbo.TabU
WHERE TabU.Data1 = @d
    AND TabU.CreateDate >= N'2013-08-23T00:00:00'

SELECT Data1
    , Data2
FROM dbo.TabU
WHERE TabU.Data1 = @d
    AND TabU.CreateDate >= N'2013-08-23T00:00:00'
    AND TabU.CreateDate <= N'2013-08-29T00:00:00';

and the stats:

Table 'TabU'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabU'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabU'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Clearly, if you can't do the queries the correct way, by including the partitioning key, then you are going to see worse performance with partitioning than without.


Understanding disk requirement to make fail over cluster

That all depends on which configuration you're seeking. As @EricHiggins noted, a traditional cluster will have shared resources, including disk, network name(s) and IP(s), and the service itself. SQL Server is a cluster-aware application and will interact with the Windows cluster service. When a failure (or manual failover) is detected by the cluster service, the SQL Server service is stopped (taken offline) and the instance resources (name, IP, disk, and other cluster resources) are migrated/transferred to the another node in the cluster. When the resources are fully transferred, the SQL Server service is started and the instance is available.

With SQL Server 2012 Enterprise Edition, the concept of Availability Groups was introduced to the system. Availability Groups are a shared-nothing environment that uses failover clustering for network naming and listening, but uses mirroring to protect at the database level.

There are many more details to go into to draw out the distinction between (and the benefits of) each of these solutions. However, to directly answer your question, if you're going to cluster using failover clustering then the TempDB LUN would be shared. If you're going with Availability Groups, then you'll double your storage, but be able to protect at the database level (instead of the instance level).

I'd need more information about "appears as one" statement - to what, or whom, does it "appear as one?"


MYSQL ways to represent '1970-1-1 0:0:0'


DB2 Alter column type and index state RBDP vs. "needs a rebuild"

The issue here is that you're reading documentation for Db2 on z/OS, but you're running tests on Db2 for Linux, UNIX & Windows.

When you perform this particular alter on Db2 for Linux, UNIX and Windows, it will succeed, but the table will be placed immediately into reorg pending state, which you can see in SYSIBMADM.ADMINTABINFO (see the REORG_PENDING column).  INDEXES_REQUIRE_REBUILD will still show as 'N', even though performing a REORG on the table will rebuild the index.


Oracle 12c - Create materialized view results in ORA-00942

Use the CREATE MATERIALIZED VIEW statement to create a materialized view. A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views.
  More

You cannot create Materialized View from Synonym. I don't know why would you like to that but we create materialized view as your working example.


How can I find out who is trying to connect to my MongoDB?

The IP address will be logged by default.

For example, the following log entry you shared shows activity for the connection with an ID of conn7881820:

2017-05-30T17:39:24.528+0000 I NETWORK  [conn7881820] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections

Prior to this log line, there will be a log line that shows the establishment of this connection. The log line should look something similar to the following:

2017-05-30T17:39:24.528+0000 I NETWORK [initandlisten] connection accepted from SOME_IP_ADDRESS:PORT #7881820 (SOME_# connections now open)

The number following the IP address will match the connection ID (without the conn portion) of the log lines in question, (i.e. #7881820)

Note: The log line I fabricated above is using the same timestamp of the log messages you provided; in reality it should be earlier in time.


Unable to shrink log file due to snapshot replication

If you are sure that everything is replicated to all subscribers and you really need to shrink it right now, you can try this.

Execute the following snippet inside your replication database. It will mark all replications as successful and your replication queue will be empty. Be sure that this is really the case! Otherwise you may loose some elements which need to be replicated.

Script:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,
     @numtrans = 0, @time = 0, @reset = 1

Take a backup and tail the log. It should now be empty.


SQL 2014 Availability Group Cluster or Listener

Turns out that yes the cname should be pointed at the listener IP.


It's not possible, however you can easily generate the needed commands.

Use SQL*Plus and spool this to a file:

select 'create synonym scoot.'||table_name||' for kitty.'||table_name||';'
from all_tables
where owner='KITTY';

... then run it in SQL*Plus.


Getting ORA-32042 even though i can create the view in SQL Developer

Interesting. It works without the column alias in the mview definition.

with
  bar as (select * from dual),
  foo(lvl) as
  (
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

       LVL
----------
         1
         2
         3
         4
         5

Create mview fails with column alias in definition:

create materialized view mv1(lvl) as
with
  bar as (select * from dual),
  foo(lvl) as
  (
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

Error report -
ORA-12018: following error encountered during code generation for "BP"."MV1"
ORA-32042: recursive WITH clause must reference itself directly in one of the UNION ALL branches
12018. 0000 -  "following error encountered during code generation for \"%s\".\"%s\""
*Cause:    The refresh operations for the indicated materialized view could
           not be regenerated due to errors.
*Action:   Correct the problem indicated in the following error messages and
           repeat the operation.

But it works without the column alias:

create materialized view mv1 as
with
  bar as (select * from dual),
  foo(lvl) as
  (
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

Materialized view MV1 created.

I see no reason for your version not working, it may be a bug, but I could not find anything related to this on the support site of Oracle.

Above output is from version 18.4.


Need to filter dimension where two attributes are the same

I got it!

Basically the filter expression was correct, but I needed to Crossjoin the set what I wanted to filter against...

FILTER (
  CROSSJOIN([Student].[Major].[Major], [Student].[Minor].[Minor]),
  [Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)

I know I answered this quick but I've spent the best part of 10 hours on this today :P


Get row values only if a particular column's value does not exist

SELECT Studentnumber, name, DepartmentName
    FROM Student AS s
    WHERE NOT EXISTS (
            SELECT 1
                FROM Grade_Report
                WHERE Studentnumber = s.Studentnumber
                  AND Grade = 'A'
                     )


Security permission to only read SSAS Cube with Excel

In order for a specific role to have permissions over a cube data you have to specifically grant cube permissions to that role.
That can be done using the Cube tab in the role properties page: in SSAS

-> Databases -> Your db -> Roles -> your specific role -> Cubes tab.

You can assign specific permissions for:

Access - None, Read, or ReadWrite
LocalCube/DrillthroughAccess - None, Drillthrough/Drillthrough and Local Cube
Process

Same for dimensions and their data and specific cell data.
You can find further info about cube permissions in the following articles:

Permissions and Access Rights (Analysis Services - Multidimensional Data)
Configuring permissions for SQL Server Analysis Services
Security with Analysis Services - Overview


sp_stop_job reports Success, but the jobs are still running

You should keep in mind that aborting a job does not always mean the job is aborted as expected. It could be that the transactions needed to be rolled back and this may also take a while depending on the amount of data and the performance.


Best solution to keep a big table synched in 2 DB

If I had to manage this, I'd look at what, if anything, modifies the data in the tables outside of the ETL process.

If nothing else besides your ETL modifies the data, I would simply update the ETL process to insert the finished data at both locations (and likewise carry out whatever index maintenance you're doing in both places).

If something else updates this data, but only one one server, then transactional replication is probably the most lightweight way to get the data to the secondary server. Even if the data isn't being modified outside of the ETL, then this wouldn't be a terrible alternative to modifying the ETL process to update two targets. It sounds like a relatively small percentage of data that's being inserted daily.

If the data is being modified on both servers, then you'll probably want to consider merge replication. The simplicity of this will largely depend on if the table has any identity columns.


Update Postgres on server that already has version included with system

Is it true that Postgres can happily run multiple versions of itself
  on the same server...under same OS instance?

I've run 8.3, 8.4, 9.0, and 9.1 at the same time on a single Windows server. You just need each PostgreSQL server listening on a different port. Choosing the port is part of the installation process.

I'm far to cynical to believe that PostgreSQL will never overwrite an existing data directory, but in my experience (none of it on OS/X), each version installs its own data directory.


How do I move all tables from one Postgres schema to another

Use PL/pgSQL in a DO statement:

DO
$$DECLARE
   p_table regclass;
BEGIN
   SET LOCAL search_path='xyz';
   FOR p_table IN
      SELECT oid FROM pg_class
      WHERE relnamespace = 'xyz'::regnamespace
        AND relkind = 'r'
   LOOP
      EXECUTE format('ALTER TABLE %s SET SCHEMA public', p_table);
   END LOOP;
END;$$;


Row by row subtraction with single input number

Fundamentally, this is a running total problem: for each row you are getting the running total of qty and subtracting a fixed value from it. More specifically, this is about how you want to use the running total. In this case you want to display a new qty value based on the result of the previous calculation. And there are essentially three cases here:

The result of the subtraction is negative.
The result of the subtraction exceeds the current row's qty.
The result is a non-negative value not exceeding qty.

The first case means that the supplied fixed value is more than necessary to exhaust the current running total – in other words, it exhausts all qty values from the beginning till the current row (including the current row). Therefore, we substitute a 0 for qty.

In the second case we just keep the qty, because the running total now exceeds the fixed value so much that the difference is greater than the current qty – so the latter stays intact.

The last case is when the fixed value either exactly covers all the qty values so far or is slightly less than the total but not much less: up to the amount of the current qty. In this case we show the difference between the running total and the fixed value.

The SQL for the above is this:

SELECT
  id,
  CASE
    WHEN balance < 0   THEN 0
    WHEN balance > qty THEN qty
    ELSE balance
  END AS qty
FROM
  (
    SELECT
      id,
      qty,
      SUM(qty) OVER (ORDER BY id ASC) - @fixedSum AS balance
    FROM
      a
  ) AS derived
;

where @fixedSum is the fixed value you want to subtract from the running total.

The central part of the solution is SUM(qty) OVER (ORDER BY id ASC) – the window aggregate function to get the running total of qty.

I am calculating the running totals here in a nested SELECT because the result of the subtraction needs to be referenced several times in a CASE expression, and you cannot reference a calculated column on the same level, you need to use nesting for that. The nesting can be in the form of either a derived table or a common table expression (CTE). The above solution uses a derived table.

There is a way to avoid nesting, though, and do all the calculations in a single SELECT using the functions LEAST and GREATEST:

SELECT
  id,
  qty,
  LEAST(
    qty,
    GREATEST(
      SUM(qty) OVER (ORDER BY id ASC) - @fixedSum,
      0
    )
  ) AS balance
FROM
  a

This method is much less straightforward than the one using CASE, though. Performance-wise, it would likely work the same.

The method works like this. First, you choose the greater value between the subtraction result and 0 using the GREATEST function. So, in terms of the cases listed above, if we have Case 1, GREATEST will yield 0. A non-zero result would mean we have one of the other cases.

The obtained value is then compared to the current qty and this time the lesser one of the two is chosen using the LEAST function. If we obtained 0 previously, LEAST will keep it. If we had the subtraction result and it is now found to be greater than qty, the LEAST function will give us qty (meaning this is Case 3). And if the difference happens to be less than qty, that is what LEAST will yield (Case 2).


How to prevent SQL Log from being too full

A couple of things you can do.

Your database is probably in full recovery mode. This is why the log file does not get re-used. Now if you DON'T need transaction log backups you can set the recovery mode to simple. Your log file will now re-use itself in simple mode. If you DO need transactional log backup i.e. point in time recovery then you will need to actually DO transaction log backups to re-use the log file.

Please be aware that this setting has a MAJOR impact on how you can perform disaster recovery and you need to make sure that the setting matches your disaster recovery needs. If you database is so busy then transactional backups are probably better than simple recovery mode.

Without transaction log backups you will NOT be able to do point in time recovery. Thus you will lose A LOT OF DATA in the event of a disaster.

Go read about transaction logs and recovery modes here: http://msdn.microsoft.com/en-us/library/ms189275.aspx

Now there is something else you need to keep in mind. Virtual Log File fragmentation. It is going to be something very important to understand and master. Kimberly L Tripp has a very very  usefull blog entry about this. You can read all about this very helpful topic here: http://www.sqlskills.com/blogs/kimberly/post/transaction-log-vlfs-too-many-or-too-few.aspx

Keep in mind 50-100 transactions a second is not a lot. Also a BIG transaction log file is not always a bad one. There is more to log files than just logging transactions. Go read about it. It is a topic worth mastering.


The definition of object '[sp_name]' has changed since it was compiled on fail-over to mirror database

Which SQL Edition are you using? Sometimes the engine couldn't refer the metadata catalog resulting in such error. This was identified as a bug. But you've workaround to resolve this issue. If You could recompile the stored procedure the next time it runs using sp_recompile.

The details of compile and re-compile is discussed below.

Recompile a Stored Procedure

Update Stored Proc in SSMS and executing a separate application which calls the procedure. You need to end the session in which you are editing the procedure. The changes are only visible in the editing session until they are committed.

Check the link below:

The definition of object 'name' has changed since it was compiled


Where/how does innodb store tables in files?

The setting is innodb_file_per_table, and it's default 0 (ie. use one big file) up to MySQL 5.1, and then in either 5.5 or 5.6 the default changed. Changing this value will not affect tables which already exist.

Even with innodb_file_per_table set to ON you still need the shared tablespace file (ibdata1 file by default).

There is no one recommended setting, it depends on your databases. Some advantages:
- It's easier to reclaim space from the file (you only need to reimport one table)
- It can be faster on ext* filesystems as they use inode level locking, and so splitting the accesses over multiple files will be alleviate this a little

Some disadvantages:
- It takes more space, as reclaimed space can only be used in the same table
- It takes longer to recover from an unclean restart
- It can be a big problem if you have a large number of tables (as you can hit an open files limit)
- I might be wrong with this one, but if the temporary tables aren't in the system tablespace a drop table can take a long time

What problem are you trying to solve?


postgres 9.1: both md5 and ident on the same connection type?

Only if you can differentiate by user or database. You can't have both ident and md5 enabled for the same (user,database,connection-origin) tuple.

I usually use unix sockets with ident for my admin and casual use. Many apps only work over tcp, so I use tcp with md5 for them. For apps that can use a unix socket I specify their database as md5 and just use a password when I connect to it.

It'd be really nice if Pg supported fallback auth where you could try several protocols or where the client could pick, but right now AFAIK it doesn't.


MS SQL Express - Find when a Query was run

Here is an example of creating a delete trigger to capture the results of the delete
--demo setup
DROP TABLE IF EXISTS TestDelete;
DROP TABLE IF EXISTS TestDeleteSave;

--create a TestDelete table and a TestDeleteSave
--the TestDeleteSave table will hold the contents of deleted rows
--from TestDelete when the trigger is fired

CREATE TABLE TestDelete (Id int, Description varchar(50));
CREATE TABLE TestDeleteSave (Id int, Description varchar(50));
go

--insert sample row into TestDelete
INSERT INTO TestDelete (
    Id
    ,Description
    )
VALUES (
    1
    ,'Test Description'
    )
go

--create a delete trigger on the TestDelete table.
--the 'deleted' virtual table will contain the rows that were deleted
--during a transaction
CREATE TRIGGER [dbo].[TestDelete_Delete] ON [dbo].[TestDelete]
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

INSERT INTO TestDeleteSave
SELECT *
FROM deleted

END;
GO

ALTER TABLE [dbo].[TestDelete] ENABLE TRIGGER [TestDelete_Delete]
GO

--now, delete a row from the TestDelete table which will fire the delete trigger
delete from TestDelete where id = 1

--now, select affected rows from the TestDeleteSave table
select * from TestDeleteSave


How to write validation trigger which works with all isolation levels

You can solve this for any isolation level by taking an exclusive lock on a row in a parent table - which is much cheaper and less intrusive than locking the whole table like you do currently.
If you don't have one already (which is a typical case), just create a simple table with some kind of UNIQUE constraint, a PK serves nicely:
CREATE TABLE parent (val integer PRIMARY KEY);  -- match data type

Achieve the lock with an UPSERT statement (as first statement in the transaction!) which either inserts a row in the parent table (holding an exclusive lock automatically) or takes a lock on an existing row. This way, only a single transaction at a time can write to rows with the same NEW.val in the child table. And all such rows are visible to the current transaction (even in REPEATABLE READ) because it started with the lock.
You do not even need a foreign key constraint between child and parent (though it won't hurt). You can just start with an empty parent table.
Waring: not reliable as trigger in REPEATABLE READ mode! See below.
CREATE OR REPLACE FUNCTION verify()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO parent AS p (val)    -- insert parent
   VALUES (NEW.val)
   ON     CONFLICT (val) DO UPDATE  -- if already there ...
   SET    val = NEW.val
   WHERE  FALSE;                    -- never executed, just locks the row

   IF (SELECT count(*) >= 5 FROM child c WHERE c.val = NEW.val) THEN
      RAISE EXCEPTION '% already present 5 times', NEW.val USING ERRCODE = 'integrity_constraint_violation';
   ELSE
      RETURN NEW;                   -- proceed with INSERT / UPDATE
   END IF;
END
$func$  LANGUAGE plpgsql;

The manual:

all rows will be locked when the ON CONFLICT DO UPDATE action is taken.

When inserting / updating multiple rows, make sure the rows are in consistent order to avoid deadlocks.
On second thought, while cheaper than your current trigger function, this can still fail because a trigger function is not necessarily the first statement in the transaction. Concurrent transactions may have committed between transaction start and the time the lock is taken. And any such rows in the child table remain invisible in REPEATABLE READ mode, making the count fail.
You would have to encapsulate each write on one or more rows with the same val in a separate transaction, starting with the UPSERT to take the lock first.
Related:

How to perform conditional insert based on row count?
How to use RETURNING with ON CONFLICT in PostgreSQL?
Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Alternative: add a number column and a UNIQUE constraint
Untested, no more time ...
ALTER TABLE child
   ADD COLUMN val_nr int NOT NULL
 , ADD CONSTRAINT child_max5_per_val CHECK (val_nr BETWEEN 1 AND 5)
 , ADD CONSTRAINT child_val_nr UNIQUE (val, val_vr);

These constraints enforce your rules in all transaction isolation modes. But you need to assign a val_nr between 1 and 5 for every row. After taking the lock, you can look up existing val_nr and fill gaps, which can still fail in REPEATABLE READ mode, but not silently. You'd get a duplicate key violation error.


No, it is basically a privilege that allows an account to modify how the database operates, but it's separate from INSERT, ALTER, etc.
From the documentation:

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.
To create or alter stored functions if binary logging is enabled, you may also need the SUPER privilege, as described in Section 17.7, “Binary Logging of Stored Programs”.


Is Service Pack 2 completely installed on Passive Node of SQL Server 2012 Instance?

The logs led us to review our Cluster services:

There was an error to lookup cluster resources. Error: There was a failure to call cluster code from a provider. Exception message: Generic failure . Status code: 4104. Description: .
HResult : 0x86d80014
FacilityCode : 1752 (6d8)
ErrorCode : 20 (0014)
Data:
    errorMessage = There was a failure to call cluster code from a provider. Exception message: Generic failure . Status code: 4104. Description: .

We determined that the replication agent service was the cause of the incomplete installation.

We removed the service from Cluster Resources and re-applied SP2 without success. We stopped the service on the node and re-applied SP2 without success.

Error: Failed to run patch request for instance: MSSQLSERVER (exit code: -2032664552)

We uninstalled the service from the server nodes, and then successfully "re-applied" SP2.

Follow-up actions:

We'll be contacting the replication agent vendor to discuss why this
was required.
Review the Microsoft document "Patching SQL Server Failover Cluster Instances with Cluster-Aware Updating (CAU)", available here, which I discovered today.


Most likely because the expression 1/10/1971 is interpreted as a numeric expression, i.e. 1 divided by 10 divided by 1971 which is 0 (with integer division rounding values down). Telling Access that this is a date can be done using quotes (not really recommended) or by using a date function, like this:

IIf([DOB]<DateSerial(1971, 10, 1), "Mature", "Not mature")

The above DateSerial() VBA function will return October 1, 1971. The parameters are (Year, Month, Day).

Also, the following will work (thanks, @ypercube!)

IIf([DOB]<#1971-10-01#, "Mature", "Not mature")


Is there a way to use a SQL Server 2000 database in SSIS 2012?

Apparently, the SQL Server Native Client 11.0 (the one that ships with SQL Server 2012) does not support SQL Server 2000. All you have to do is change the data source to use a .Net Provider (SqlClient Data Provider) instead of the Native OLE DB provider. The .Net Provider probably doesn't perform quite as well as the Native OLE DB but it was definitely a lot less work that having to build a bunch of new ETLs to stage the data and a lot less risky too.


You can do this directly through SQL Server. You can schedule the backups through SQL Server Agent and just call the backup command through the job. The job will allow you to schedule it as you please.

To make this even a bit easier you can create a Maintenance Plan directly in SQL Server and specify backing up the database.

There are a few options here, and no need to look outside of SQL Server for this functionality.

Here's an article on how to do this through a maintenance plan: http://weblogs.asp.net/sreejukg/archive/2010/01/20/scheduling-automated-backup-using-sql-server-2008.aspx


Generating maintenance database from maintenance tables currently in master and msdb

The following code will inspect the master and msdb databases for all linked-servers on the current instance, reporting non-Microsoft-shipped tables:

IF OBJECT_ID(N'tempdb..#obj_list', N'U') IS NOT NULL
DROP TABLE #obj_list;
CREATE TABLE #obj_list
(
    ServerName sysname NOT NULL
    , TableName sysname NOT NULL
);
DECLARE @server sysname;
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT s.name
FROM sys.servers s
WHERE s.name <> @@SERVERNAME;

OPEN cur;
FETCH NEXT FROM cur INTO @server;
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = '
SELECT ServerName = ''' + QUOTENAME(@server) + N'''
    , TableName = N''master.'' + s.name + N''.'' + t.name
FROM ' + QUOTENAME(@server) + N'.master.sys.tables t
    INNER JOIN ' + QUOTENAME(@server) + N'.master.sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
UNION ALL
SELECT ServerName = ''' + QUOTENAME(@server) + N'''
    , N''msdb.'' + s.name + N''.'' + t.name
FROM ' + QUOTENAME(@server) + N'.msdb.sys.tables t
    INNER JOIN ' + QUOTENAME(@server) + N'.msdb.sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
ORDER BY TableName;';

    INSERT INTO #obj_list (ServerName, TableName)
    EXEC sys.sp_executesql @cmd;
    FETCH NEXT FROM cur INTO @server;
END
CLOSE cur;
DEALLOCATE cur;

SELECT *
FROM #obj_list ol
ORDER BY ol.ServerName
    , ol.TableName;

Your code has two problems;

You cannot use a variable in OPENQUERY like this:

SELECT * FROM OPENQUERY(@ServerName, '

That would need to be:

SELECT * FROM OPENQUERY([Server\Instance], '

Clearly, that won't work for your case since you're trying to do this dynamically.  To make that work, you'd need to do dynamic SQL inside dynamic SQL.
This line contains an embedded reference to the @ServerName variable, but that variable is not visible inside the dynamic T-SQL... That line would need to be:

select *, ''''SELECT TOP 1 * INTO [''''' + @ServerName + '''''_-_'''' + Databasename +''''_-_'''' + TableName +''''] FROM [''''' + @ServerName + '''''].[''''+ Databasename + ''''].dbo.['''' + TableName + '''']''''


The rules that SQL Server uses to derive the type of literals are complex, with some odd behaviours that are maintained these days for backward compatibility. For example, the derived type may depend on whether simple parameterization is applied. There are other (undocumented) considerations as well.

With those caveats out of the way, the situation you face is one of the simpler ones. The untyped literals are typed as integer when they will fit in that data type. The data type of the result is as specified in * (Multiply) (Transact-SQL):

  Returns the data type of the argument with the higher precedence.
  For more information, see Data Type Precedence (Transact-SQL).

The two data types are both integer (with obviously the same precedence), so the result is also typed as integer. When the result does not fit, an error may be thrown (depending on settings).

When one of the literals is explicitly typed using CAST or CONVERT as (for example) bigint (which has a higher precedence than integer) the result type will be bigint, and no error occurs.

The two larger constants in the question are typed as numeric(24,0) and numeric(12,0) respectively. The result of multiplying them is typed as numeric(37,0) in accordance with the rules for e1 * e2 shown in Precision, Scale, and Length (Transact-SQL):

Result precision: p1 + p2 + 1 = 24 + 12 + 1 = 37
Result scale: s1 + s2 = 0 + 0 = 0

Result type: numeric (37,0)


Stored Procedures under Source Control, best practice

There are tools out there, such as this from Redgate, but I have always found that best is to save as SQL files, perhaps even in a Database Project (SSDT?) in your solution.

Along with this, I suggest the following guidelines:

Always assume the SVN version as the "current" / "latest"
Ensure that every script you run has an appropriate "if exists then drop" at the start
Remember to script your permissions, if any

You can initially create these SQL files by scripting directly from SSMS, and you can set SSMS to script all your "drop" and "create" as well as your permissions.


Use xQuery to extract attribute values that also exist in SQL variable

XML in SQL Server is good at shredding and getting values so for performance it is probably almost best to focus on extracting the data you want and then do the logic in SQL instead.

Put the values you are looking for in a table of some kind and filter your extracted values against that table instead. If your input is a string you could use a string split function of your choice to get them into a table.

declare @T table(X xml);
insert into @T(X) values('<ns0:message xmlns:ns0=''xxx:testing''>
  <ns0:field name=''AAA''>...</ns0:field>
  <ns0:field name=''BBB''>...</ns0:field>
  <ns0:field name=''VVV''>...</ns0:field>
  <ns0:field name=''CAR''>...</ns0:field>
</ns0:message>');

declare @V table(name varchar(10));
insert into @V(name) values('AAA'),('BBB'),('CAR'),('XYZ');

with xmlnamespaces(default 'xxx:testing')
select T.name
from (
     select F.X.value('@name', 'varchar(10)') as name
     from @T as T
       cross apply T.X.nodes('/message/field') as F(X)
     ) as T
where exists (
             select *
             from @V as V
             where V.name = T.name
             );

If you are really looking for a XQuery solution you could use contains

declare @T table(X xml);
insert into @T(X) values('<ns0:message xmlns:ns0=''xxx:testing''>
  <ns0:field name=''AAA''>...</ns0:field>
  <ns0:field name=''BBB''>...</ns0:field>
  <ns0:field name=''VVV''>...</ns0:field>
  <ns0:field name=''CAR''>...</ns0:field>
</ns0:message>');

declare @S varchar(100) = ',AAA,BBB,CAR,XYZ,';

with xmlnamespaces(default 'xxx:testing')
select T.X.query('data(/message/field/@name[contains(sql:variable("@S"), concat(",", ., ","))])')
from @T as T;