Sql. Calculating the size in KB of each row

If you already have some representative sample data, just run the following to find the average row size:

sp_spaceused 'Tablename'

Otherwise you can estimate its size without indexes with this function:

CREATE FUNCTION available_tablerowsize
(
-- Add the parameters for the function here
@tablename char(50)
)
RETURNS int
AS
BEGIN
-- variables to track fixed and variable column sizes
DECLARE @num_columns int
DECLARE @result int
DECLARE @num_fixed_columns int
DECLARE @fixed_data_size int
DECLARE @var_data_size int
DECLARE @num_var_columns int
DECLARE @max_var_size int
DECLARE @null_bitmap_size int
DECLARE @row_size int

-- Find the total number of columns
select @num_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype

-- Find the size occupied by fixed length columns (Note: not possible to exist outside the 8060 bytes limit)
select @num_fixed_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0

select @fixed_data_size = sum(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0

-- Find the size occupied by variable length columns within the 8060 page size limit

-- number of variable length columns
select @num_var_columns=count(*)
from syscolumns, systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- max size of all variable length columns
select @max_var_size =max(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- calculate variable length storage
begin
if @num_var_columns>0
set @var_data_size=2+(@num_var_columns*2)+@max_var_size
--set @var_data_size = @num_var_columns*24
else
set @var_data_size=0
end

-- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability.
select @null_bitmap_size = 2 + ((@num_columns+7)/8)

-- Calculate total rowsize
select @row_size = @fixed_data_size + @var_data_size + @null_bitmap_size + 4

-- Return the available bytes in the row available for expansion
select @result = 8060 - @row_size

RETURN @result

END
GO


Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assembly

Your GRANT statement is incorrect. You only granted the ability to set Assemblies to EXTERNAL_ACCESS, not to UNSAFE. UNSAFE is less restricted than EXTERNAL_ACCESS, and so granting UNSAFE ASSEMBLY to a Login includes – implicitly – the EXTERNAL ACCESS ASSEMBLY permission.

You need to use:

GRANT UNSAFE ASSEMBLY TO [clr_key1];

P.S. You need to check the two DLLs you are importing for why they need to be marked as UNSAFE. If it is due to storing values in static variables then that could result in unexpected results if two sessions execute this code at the same time.


10.2.6 is a really old version from 2017, and the first table reelase..

Consider upgrading to latest 10.2 version (e.g. 10.2.25).

You table probably isn't corrupt, just has the wrong definition. In 10.2.6 it should look like this. Look to see what you need to alter to make this your table definition.


how to filter the resulted table in dbeaver?

You can filter on multiple columns with DBeaver, either on tables or query results.

The menu is called "Custom filter" and is located at the top right of the query result:

From there you can specify custom filters as you need:


When do _WA_Sys_ statistics Get Updated?

SQL Server only automatically updates the statistics it uses. It may not be actively using the _WA_Sys statistics for cardinality estimation, though it may load them as part of the decision.

To see which statistics are being used for your query, add this to the end of your query, and look in the Messages tab:

OPTION(QUERYTRACEON 3604, QUERYTRACEON 2363);

If your database is in a compat level prior to 2014, you'll need these:

OPTION(QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204);

These won't tell you why it's not using certain statistics, though generally if there are statistics created with a higher sampling percentage on the same column, statistics with lower sampling percentages will be disfavored.

Keep in mind that statistics updates don't occur on modification, only when queries run that use them


What is the date function used to display month name from Date column in Teradata

The full name is returned by to_char(dateCol, 'Month')

'Month' -> 'October'
'MONTH' -> 'OCTOBER'
'month' -> 'october'
'Mon' -> 'Oct'
'MON' -> 'OCT'
'mon' -> 'oct'


Getting the only updated field using a postgreSQL trigger?

If you are using the latest versions of PostgreSQL, you can use the JSON(B) functions and operators to your advantage. Although this is not (yet) a full solution, check to see if it mimics what you're trying to achieve:

-- The 'new' and 'old' entries will simulate the 'old' and 'new'
-- values for a row that you can use in a trigger function
WITH
new(id, changed_column, integer_changed_column, not_changed,
   array_changed_column, changed_null) AS
(
    VALUES (12, text 'Value', 1234, text 'unchanged',
        array [1, 2], cast(null as text))
),
old(id, changed_column, integer_changed_column, not_changed,
    array_changed_column, changed_null) AS
(
    VALUES (12, text 'New value', 1235, text 'unchanged',
        array [1, 3], text 'not-null')
)

-- And we get a setof records with the changes
SELECT
    *
FROM
    (
    SELECT
        column_name,
        (row_to_json(new)->column_name #>> '{}') AS new_value,
        (row_to_json(old)->column_name #>> '{}') AS old_value
    FROM
        new, old, (
        SELECT
            json_object_keys(row_to_json(new)) AS column_name
        FROM
            new
        ) AS cc
    ) AS s0
WHERE
    new_value IS DISTINCT FROM old_value
ORDER BY
    column_name ;

The result that you'll get shows you all the updated columns (=fields). I have assumed that more than one can be updated at once:

 column_name             | old_value | new_value
 ------------------------+-----------+-----------
 array_changed_column    | [1,2]     | [1,3]
 changed_column          | Value     | New value
 changed_null            |           | not-null
 integer_changed_column  | 1234      | 1235

NOTE: all values are converted to text, because it is the type that all others can be converted to.


Script not allowed to create schema objects when generating tables despite db_owner role

Do I understand correctly: you scripted tables only, created the database manually and then ran the script to create the tables?

You have to either script the whole database and create the new database using that script (change the DB name if you'll execute it on the same instance) or at least include schemas in the list of database objects to script ("Choose objects" step of the wizard).


Why is the install of SQL Server 2012 in Windows Server 2012 taking over previously accessible hard drive's?

Oops... should mark this as answered.

The issue simply was specifying the root of the drive. Don't do that.

When specifying a folder in that drive, all is well.


A BTree index is arguably better than a Hash index.  A BTree can efficiently handle "ranges", whereas Hash is useless for such.  For "point queries" they are about equivalent.

As for MEMORY, the allure is the speed of access.  However, the caching of disk blocks makes any other Engine nearly as good in many production situations.

In a survey of over 1000 tables discussed on forums.mysql.com, only 6 were MEMORY.  I suggest that says that MEMORY is only very rarely the 'engine of choice'.

The lack of persistence makes MEMORY a non-starter.

As a "cache", Redis, memcached, Memory, etc, have appeal.  But InnoDB, for example, has a builtin cache, and it is usually inefficient to put a cache in front of a cache.

Perhaps you are thinking of some niche usage where MEMORY is well suited?


ola-hallengren index rebuild with multi thread and multiple days

For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

This is very common for VLDBs. You can be a bit smatter and follow Paul Randal's Quick list of VLDB maintenance best practices. You can rebuild indexes ONLINE in Enterprise edition.

In your case since you are using Ola's Index Optimization scripts, you are running into endless index defragmentation which you can solve by being analyzing CommandLog table

I have given few more thoughts on Is a weekly rebuild of indexes a good idea? covering maxdop option.

I would say, if your databases are extremely big (TB size), atleast do a reorg instead of rebuild and manually update stats. This will put you in a good reasonable state since a reorg will start from where it got killed.

For running in parallel threads, you can drive of commandLog table and use powershell runspace (be careful of not spawing many threads - see Notes on usage of using runspace).


How to convert primary keys into clustered primary keys for a great number of tables keeping referential identity

Your approach is correct and pretty much the only way, but I would consider using a tool like Red Gate to help me.

drop all FKs without scripting them
change the indexes (as above)
generate an "FK only" script using the compare tool to re-apply the FKs

The 3rd step saves writing something to script our your FK definitions, or fiddling with SSMS/SMO


How to combine date and time with a string value in column created by Case in SQL Query

I guess because it cant have a string value with a column formatted
  for Date and time?

You are correct. CASE Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. Data type precedence comes into play here.

You'd have to convert your column to a varchar.

DB FIDDLE

case
   when DateTimeColumn  is null then 'No Data'
   else convert(varchar (20),DateTimeColumn,120)
end

You can reference the CAST and CONVERT documentation for other date/time formats.


Help With MySQL Computed Columns/Auto Updating Field Based On Average

How about using a scheduled job to take a snap-shot of your users' weight stats and influencing factors into a different (could be a temp-) table, then using those to calculate the new weight (into a different column of that same table). And then update your user table with the complete set of calculated weight stats.

You'd avoid all kinds of conflicts (from updating with modified data depending on order of execution) and execution time would not be an issue either (though it should not be too bad anway, from what you described), since you could run this as a low priority background process.


They're integers.

Try:

select 1.0/2.0;

Or:

select CONVERT(NUMERIC,1)/CONVERT(NUMERIC,2);


How do I optimize large table so queries that target only recent data perform optimaly?

Time series should be clustered by time:

CREATE TABLE [dbo].[mydata](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [varchar](20) NOT NULL,   -- index1 UNIQUE NONCLUSTERED INDEX
[Data2] [varchar](20) NULL,
[Data3] [nvarchar](50) NOT NULL,
... bunch of DATA around 5kb
[Time_1] [datetime] NULL,    -- time created,
[Time_2] [datetime] NULL,    -- time finished ( usualy within few days ) -- index3 NONCLUSTERED INDEX
[Status] [int] NOT NULL,     -- active
[Modid] [timestamp] NOT NULL,
CONSTRAINT NONCLUSTERED PRIMARY KEY ([ID])
);

CREATE CLUSTERED INDEX cdxMyDataTime_1 on dbi.mydata (Time_1);

In time series data the time is almost always specified in queries, and usually as a range. With a clustered key based on the time range queries will scan only the relevant portion of the table.

The ID can continue to server the logical primary key role, but there is little benefit from having the table clustered by it since the ID is never used as a range. So off it goes into a non clustered constraint. Singleton lookups based on ID will need two reads, but who cares, is two fast reads.

If you cannot have the Time_1 as clustered key a frequent trick used is to retrieve the ID range for each day, eg. create table of days and min_ID/max_ID. Then use the ID range that covers the time range you're interested in to restrict the range of the scan on the table. This advantage of this approach is that it works for multiple time columns (you cannot cluster by Time_1 and Time_2...) and it is less invasive (can be tried out right away w/o modifying the table). But this approach is very invasive in the application query design, it requires discipline in remembering to use the ID ranges for the days desired. Note that since the IDs usually don't change, they can be cached in the app.

Simple indexes on Time_1 and Time_2 do not work because they hit the index tipping point. Covering index (with INCLUDE columns) on Time_1 and Time_2 explode the data size as often the included column required are ... all columns.


You can set up an Extended Event trace and capture event type 'sp_statement_completed'. Filter the trace by object_type='trigger'. You can also pass your trigger name to narrow down further. Following definition will give you a starting point. Adjust file name, location, size and max_file_size based on your need.

CREATE event session [CaptureTriggerExecution] ON server ADD event sqlserver.sp_statement_completed( WHERE (
  [package0].[equal_uint64]([object_type],(21076))
  AND
  [object_name]=N'''My Trigger'''
)
) ADD target package0.event_file(SET filename=N'CaptureTriggerExecution',
  max_file_size=(10)) WITH (max_memory=4096 kb,
event_retention_mode=allow_single_event_loss,
max_dispatch_latency=30 seconds,
max_event_size=0 kb,
memory_partition_mode=none,
track_causality=ON,
startup_state=OFF)
GO


ASM diskgroup drop from another ASM instance

Check the diskgroup status in both instances.  If it is showing mounted in both instances then:

dismount the diskgroup in all the instance except one.  For instance, if you have a 3 node RAC setup, then dismount the diskgroup in nodes 2 and 3.
Drop the diskgroup from the  1st node.


Optimize Table query causing Fatal error during execution with InnoDB table

Ok I feel really stupid now...
Problem had nothing to do with MySQL Server settings etc.
Problem was that in my service (that is communicating with MySQL) I'm using MySqlCommand Object (Service is coded using VB NET). This MySqlCommand object has a parameter CommandTimeout and its default value is 30. So that was the reason why executions longer than 30s caused timeout that throws fatal error..
So now I'm setting this parameter large enough in my VB NET code before executing command and everything is working as planned!


Trigger function using current row and current table name as variables

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this:

CREATE OR REPLACE FUNCTION trfn_tbl_log_any()
  RETURNS trigger AS
$func$
DECLARE
   _ct int;
BEGIN

IF NEW.timetype = 'start' THEN

   EXECUTE format($$
      SELECT floor(t.timeidx) + 1
      FROM   %s t
      WHERE  t.fnname = $1
      AND    t.timetype = 'start'
      ORDER  BY t.stmtserial DESC
      LIMIT  1$$
    , TG_RELID::regclass  -- concatenate *identifer* ..
      )
   USING NEW.fnname        -- .. but pass *value* in USING clause
   INTO  NEW.timeidx;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      NEW.timeidx := 1;
   END IF;
END IF;

RETURN NEW;
END
$func$  LANGUAGE plpgsql;

All of this should work in Postgres 9.1. But consider upgrading to a current version anyway (currently 9.4).

NEW is not visible inside EXECUTE. Use the USING clause to pass the value from the new row (NEW.fnname).
Use TG_RELID (or TG_TABLE_SCHEMA and TG_TABLE_NAME) to concatenate the table name like we worked out under your previous question:

PostgreSQL trigger function that selects from %current% table

Use format() and dollar-quoting to simplify the syntax of string-concatenation safely.
You can assign individual columns of the NEW row directly from the dynamic query.
Use GET DIAGNOSTICS _ct = ROW_COUNT; to check if rows were found. Per documentation:

  Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

Aside: the logic of the dynamic query is only correct for BEFORE trigger. An AFTER triggers would also see the newly inserted row.


On the face of it, this looks like a classic lookup deadlock.  The essential ingredients for this deadlock pattern are:

a SELECT query that uses a non-covering nonclustered index with a Key Lookup
an INSERT query that modifies the clustered index and then the nonclustered index

The SELECT accesses the nonclustered index first, then the clustered index.
The INSERT access the clustered index first, then the nonclustered index.  Accessing the same resources in a different order acquiring incompatible locks is a great way to 'achieve' a deadlock of course.

In this case, the SELECT query is:

...and the INSERT query is:

Notice the green highlighted non-clustered indexes maintenance.

We would need to see the serial version of the SELECT plan in case it is very different from the parallel version, but as Jonathan Kehayias notes in his guide to Handling Deadlocks, this particular deadlock pattern is very sensitive to timing and internal query execution implementation details.  This type of deadlock often comes and goes without an obvious external reason.

Given access to the system concerned, and suitable permissions, I am certain we could eventually work out exactly why the deadlock occurs with the parallel plan but not the serial (assuming the same general shape).  Potential lines of enquiry include checking for optimized nested loops and/or prefetching - both of which can internally escalate the isolation level to REPEATABLE READ for the duration of the statement.  It is also possible that some feature of parallel index seek range assignment contributes to the issue.  If the serial plan becomes available, I might spend some time looking into the details further, as it is potentially interesting.

The usual solution for this type of deadlocking is to make the index covering, though the number of columns in this case might make that impractical (and besides, we are not supposed to mess with such things on SharePoint, I am told).  Ultimately, the recommendation for serial-only plans when using SharePoint is there for a reason (though not necessarily a good one, when it comes right down to it).  If the change in cost threshold for parallelism fixes the issue for the moment, this is good.  Longer term, I would probably look to separate the workloads, perhaps using Resource Governor so that SharePoint internal queries get the desired MAXDOP 1 behaviour and the other application is able to use parallelism.

The question of exchanges appearing in the deadlock trace seems a red herring to me; simply a consequence of the independent threads owning resources which technically must appear in the tree.  I cannot see anything to suggest that the exchanges themselves are contributing directly to the deadlocking issue.


When you delete data like this, the files are not automatically rewritten to reflect the new size of the data (which is potentially a massive operation).  Instead, the free space in those files is marked as free and will be re-used when you insert new data.

As you have found, the only way to reclaim that space on disk at the moment is a repair - that is not "just compacting data".  In fact a repair rewrites your data files from scratch, essentially creating a brand new copy of them on another part of the disk before removing the old ones.  Depending on how large the current data set is, how much data is left, and how fast your disk is, this can take a long time.

If you removed all data in a database, a faster and more efficient method to remove the data is to simply drop the database, which would not have this issue.


Restore SQL Server DB encrypted by EKM - where's the asymmetric key?

If you're using an HSM, there would be no point to export the key in a situation like this. The main point of an HSM is to keep the keys safe, among various other things - so you really don't want to export the key.

First, I'm going to assume that on the server you want to restore this database to you've created the credential used to access the HSM and I'm going to assume you've allowed the addresses of the new server to talk to the HSM, and all of the other assumptions such as load the EKM DLL, etc. In that case, rather than specifying CREATION_DISPOSITION = CREATE_NEW you'll want to use CREATION_DISPOSITION = OPEN_EXISTING which is part of the CREATE ASYMMETRIC KEY T-SQL.


This KB article : How to configure SQL Server to listen on a specific port will clarify things for you :

Dynamic Port Allocation

If you configure an instance of SQL Server to use dynamic port allocation, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:

  TCPDynamicPorts = Blank

  TCPPort = 0

However, if you configure an instance of SQL Server to use dynamic port allocation, and you restart the instance of SQL Server, the registry values are set as follows:

  TCPDynamicPorts = Current port that is used

  TCPPort = Current port that is used

Static Port Allocation :

If you configure an instance of SQL Server to use a static port, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:

  TCPDynamicPorts = Last port that is used

  TCPPort = New static port to be used after the next restart; new static port that you set by using the Server Network Utility

However, if you configure an instance of SQL Server to use a static port, and you restart the instance of SQL Server, the registry values are set as follows:

  TCPDynamicPorts = Blank

  TCPPort = New static port that you set by using the Server Network Utility

for your second question -

Every time you start the named SQLServer, it uses the port that is allocated. In case if the port is used by another program, then SQL Server chooses another port at the time of restart i.e. The dynamic port is chosen at first startup, and will generally remain the same through future restarts (stored in the Registry) - but if its used by another program then SQL server will choose a new port. note: For Prod Servers, I use only static ports - security and ease of manageability reasons.

Note: More cools thing to find out :

Check if Dynamic port is being used or not using T-SQL :

SELECT NAME
    ,protocol_desc
    ,type_desc
    ,state_desc
    ,is_admin_endpoint
    ,port
    ,is_dynamic_port
    ,ip_address
FROM sys.tcp_endpoints

You can use  netstat -ano to check using cmdline.

Additionally, you can check what got cached earlier in the registry on the client side as to what port it is using to connect to sql server :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0\LastConnect


How to understand Composite Primary Key when it comes to storage (in Mysql)?

(This answer is focused on MySQL; in particular, the InnoDB Engine.  JCole's blog (which you linked to) is valid for InnoDB, but might be overly technical.  I'll try to give a lighter answer.)

Think of a composite key this way...  Concatenate the keys together, then the BTree structure is just as if it had a single-column (though larger) key.

The order of the columns specified in any composite index matters.  For PRIMARY KEY(a,b), the rows will be in this order:

a b
- -
1 1
1 2
1 5
1 7
2 1
2 2
2 3
2 8
3 2
...

This means, for example, that WHERE a=2 will find all the rows "clustered" together.  But WHERE b=2 will find them scattered.

"Clustering" is also used here to say that the data is right next to the PK in the same BTree.

The block structure (16KB blocks in InnoDB) is controlled by whatever happens.  That is, there is no obvious place in the pair (a,b) where one block ends and the next starts.

In InnoDB, the PRIMARY KEY is always clustered with the data.  As a side effect, there is virtually zero overhead for that one KEY.  All "secondary keys" are separate BTrees that have the PK in their leaf nodes.

In the above example, let's say (for conciseness) that there were only 3 rows per Block:

Block 0:  (1,1),(1,2),(1,5)
Block 1:  (1,7),(2,1),(2,2)
Block 2:  (2,3),(2,8),(3,2)

Note that the breaks in a are unrelated to the Block splits.  (Also, this example may be misleading -- Blocks rarely have as few as 3 rows; Blocks do not necessarily have the same number of rows each.)


what would be the right permission to allow everything else but overwriting or creating a database?

TL;DR

In order to restrict the DEVs from doing anything "wrong" you have to at least remove the sysadmin SQL Server role.

Let's start

You could assign your DEVs the db_owner database role for each individual database. However that role will permit a user to perform a BACKUP DATABASE ... or BACKUP LOG ... which you mentioned you don't want them to be able to perform.

Solutions

Beginning with SQL Server 2014 you can then restrict a SQL Server Login with the db_owner role further removing individual privileges/permissions. One example is to remove the backup privilege on the database and transaction log level by issuing the following commands on the database:

USE <database_name>
GO
DENY BACKUP DATABASE TO <database_principal>
GO
DENY BACKUP LOG TO <database_principal>

Reference: DENY Database Permissions (Transact-SQL) (Microsoft Docs)

There are three levels of permissions.

GRANT  : You are granted/granting a specific permission
REVOKE : A previously granted/denied permission is revoked (removed) again
DENY   : You are denied/denying a specific permission

Alternative

Now because DENY has precedence over GRANT you could use the alternative solution of creating a new database role and adding the <database_principals> to that role:

USE <database_name>
GO
CREATE ROLE [deny_backup]
GO
DENY BACKUP DATABASE TO [deny_backup]
GO
DENY BACKUP LOG TO [deny_backup]
GO
EXEC sp_addrolemember N'deny_backup', N'<database_principal>'
GO

Reference: Restrict Backup permission to user (Social MSDN)

Step by Step Guide

Because some people are sceptical about statements I have made in this post, I am adding the individual steps to show you how you can indeed DENY permissions from a SQL Login even though that login has the db_owner role.

Creating and Mapping SQL Login

Let's create a SQL Login and assign it the db_owner database role:

Deny Backup Database permissions

Now we switch to a higher privileged account like sa or a Windows Login (which would be you, the DBA asking this question) that has the sysadmin SQL Server role and DENY the right to backup database from the database principal hot2use:

In a perfect world you would also need to submit a DENY BACKUP LOG TO hot2use too.
Log in to server with hot2use and back up database

Seeing as hot2use has the db_owner database role for the Test database, we log in with hot2use...

...and try to back up the database:

Well that doesn't work and is what we expected. This is because the SQL Server login hot2use no longer has the privilege to back up the database even though the login still has the database role db_owner:
SQL Login wants to hack permissions

Seeing as the SQL Server login has the db_owner role and according to some people he can assign himself the permissions back, let's try that out:

(The test was conducted in a fresh database SQL Server Query window)
Summary

If you DENY permissions with a higher privileged account, then these privileges can not be GRANTed back from the lesser privileged account, even thought the SQL Login (e.g. hot2use) should have these privileges from the database role db_owner. This is something that changed in SQL Server 2014 (yes, the tests were conducted on a SQL Server 2014 instance).

Additional ressources

You can further restrict permissions according to the official overview which can be found here:

Permissions (Database Engine)

Further recommended reading:

Getting Started with Database Engine Permissions

Wait, restores...

Oh yes, we only denied the BACKUP DATABASE privilege didn't we? That has nothing to do with RESTORE DATABASE, or does it? Let's find out. I'll create a database backup with a higher privileged account/login:

Seems to work. Let's switch back to the hot2use SQL Server Query window and restore the backup:

As you can see, it isn't going to work, because the BACKUP DATABASE privilege also restricts the restore capability of that SQL Server login.

No backup privilege = No restore privilege.

How about DENY BACKUP DATABASE FROM SYSADMIN?

Well, since you asked. Let's try it out. I'll leave out the screen shots that show how I revoked the database role.

I assigned the SQL Login the sysadmin SQL Server role with:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [hot2use]
GO

...and then removed the permission to backup the database with the above mentioned DENY BACKUP DATABASE.. commands and then performed a database backup with:

backup database Test to disk = 'C:\temp\Test_Full_Backup_20171121.bak'

... which resulted in:

So you can't DENY permissions from a SQL Login with the sysadmin Server role, but you can DENY permissions if the SQL Login does not have the 'sysadmin' database role.

Minimal permissions required to backup / restore a database

If I assign the SQL Server login absolutely no SQL Server roles and assign only the db_backupoperator database role on a given database, then I can backup and restore the database. This is something that others believe the SQL login is unable to perform. The higher privileged account created the backup, so the lower privileged account (non-sysadmin) shouldn't be able to do a backup/restore.

After resetting all permissions of the SQL Server login hot2use to public on the server and db_backupoperator on the database level, I am presented with the following enumerated permissions:

According to others I shouldn't be able to perform a backup because I am missing a SQL Server role.

So I can in fact backup a database if I have the db_backupoperator role.

And again, if I DENY BACKUP DATABASE to the SQL Server login (on a SQL Server 2014 instance or greater), then I am no longer capable of backing up my database, even though I still have the db_backupoperator role on the database level:

If the SQL Login hot2use tries to REVOKE the previously denied permission then that will fail, with a similar message that I previously showed above.

Summary

Determing which permissions/privileges/roles you want to assign to your DEVs is going to be a tiring fight/issue, because they won't want to give up the  sysadmin SQL Server role. You will have to find out which SQL Server Roles and which Database Roles they will exactly require to do their job. The worst-case is that everything will stay the same. The best case is that you will be able to restrict the DEVs in their permissions/privileges.

You will at least have to remove the sysadmin SQL Server role from your DEVs in order to further restrict them from being able to do everything they want. But once that SQL Server role has been removed, you can assign them for example the db_owner role and DENY BACKUP DATABASE ... to disallow them from backing up the database or restoring it.

The developers can still do anything they want inside the database (includig a DROP DATABASE), but they cannot perform a backup or a restore. See the following enumerated permissions for the SQL Server login hot2use with the database role db_owner but having both DENY BACKUP DATABASE ... and DENY BACKUP LOG ...:

SQL Server Login with db_owner role but without backup permissions


How can I use a default value in a Select query in PostgreSQL?

SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3

or

SELECT case count(*) when 0 then 0 else MAX(post_id) end AS max_id
FROM my_table
WHERE org_id = 3;

if you want max(post_id) to be null when there is 1 row but post_id is null

dbfiddle


Any possible way to get a progress estimate for ALTER COLUMN TYPE in Postgres?

PostgreSQL rewrites the table:

postgres=# \d t;
                   Table "public.t"
 Column |            Type             |   Modifiers
--------+-----------------------------+---------------
 x      | integer                     |
 y      | text                        |
 z      | timestamp without time zone | default now()
Indexes:
    "idx_only_scan" btree (x, y, z)
postgres=# select relfilenode, relname from pg_class where relname='t';
 relfilenode | relname
-------------+---------
       16760 | t
(1 row)

postgres=# alter table t alter column x set data type smallint;
ALTER TABLE
postgres=# select relfilenode, relname from pg_class where relname='t';
 relfilenode | relname
-------------+---------
       16792 | t
(1 row)

There should be new database files in $PGDATA/base/"database_oid"  but I don't know if it is possible to get the new relation oid, to monitor the rewrite or to pause it:

$ ls -rtl  $PGDATA/base/13297/16792*
-rw-------. 1 postgres postgres 24576 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792_fsm
-rw-------. 1 postgres postgres  8192 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792_vm
-rw-------. 1 postgres postgres  8192 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792


Logging/profiling an unpredictably slow stored procedure

You can set tracing at the ADMIN level. Your timing is suspiciously similar to an issue I dealt with in April/May this year. The admin level trace file showed a 5 second gap when reading from a socket. (10:44:05 -> 10:44:11) NTTRD is the read from tcp socket call. I'd definitely work this issue with Oracle support, but if you see gaps like the one below then the pause is somewhere in your network stack or hardware (i.e outside Oracle). In our case Oracle support pointed us towards network switch logs etc, but the culprit turned out to be an issue in the Microsoft Windows TCP stack down to the new Receive Window Auto-Tuning feature (see http://support.microsoft.com/kb/983528 for example if on Server 2008). Obviously it would be pure fluke if your issue was the same as ours, but if you can see the gaps in the admin level trace file it's a fair bet that it will turn out to be a network issue.

[08-APR-2011 10:44:05:938] nsprecv: reading from transport...
[08-APR-2011 10:44:05:938] nttrd: entry
[08-APR-2011 10:44:11:095] nttrd: socket 2156 had bytes read=60
[08-APR-2011 10:44:11:095] nttrd: exit
[08-APR-2011 10:44:11:095] nsprecv: 60 bytes from transport
[08-APR-2011 10:44:11:095] nsprecv: tlen=60, plen=60, type=6


SQL Server maintenance plan not working after renaming administrator

I have solved the problem using SQL Server Management Studio (SSMS):

Expanding Server --> security
Expanding Logins
Right click on administrator account and simply rename it to the new name I have set before in Local Group Policy.
Maintenance plans works fine!


How to prevent query execution after exiting the \e editor in psql?

Don't add a semicolon (;) and you are fine. I quote the manual about the \e or \edit meta-command here:

The new contents of the query buffer are then re-parsed according to
the normal rules of psql, treating the whole buffer as a single line.
Any complete queries are immediately executed; that is, if the query
buffer contains or ends with a semicolon, everything up to that point
is executed and removed from the query buffer. Whatever remains in the
query buffer is redisplayed. Type semicolon or \g to send it, or
\r to cancel it by clearing the query buffer.

To be clear: only the last statement that is not terminated with a semicolon is not executed. To execute nothing at all, make sure there are no complete statements before the last statement. I.e. no semicolons at all.


The copydb command cannot be run against a database in which sharding has been enabled.

In this situation I would recommend using the mongodump and mongorestore tools to extract your collection data and to restore to a different collection / database / cluster. These tools can be run against mongos and will respect shard boundaries.


PostgreSQL corrupted after running pg_resetxlog

I guess it is too late now. If you use pg_resetxlog, you need to be extremely careful and, most importantly, know what you are doing.

Thanks to PostgreSQL's robustness, all you had to do in that case was to free space in the Barman server, for example by deleting the oldest backup in the catalogue. Then, once space was reclaimed, PostgreSQL could have resumed shipping WAL files and automatically recovered.

I know it is too late for you, but I am hoping that my reply will be able to help somebody in the future and prevent them from running pg_resetxlog.


Arithmetic overflow error converting float to data type numeric

I'm no expert in this, but it looks to me as if you're trying to use FLOAT. Have you tried using NUMERIC? Something like this maybe? Just throwing ideas out.

88  SQLNUMERIC 0   0   "," 89  PPL_2_BL    ""


Questioning a DB Consultant and their recommendations

If we continue growing as a company (as we wish to), this table of
  analytics could easily hit 10,000,000 rows in a few years, and just
  keep growing.

  This scares me.

10M rows isn't a matter to be worried about. Even 10B rows isn't a matter for a single host. The main criteria is the throughput of the DB/engine. To prevent hangups and outages you need to be able to ingest/return the data at the rate at least 3x faster than your actual (and potential) average load. 10x is better of course. This goal can be achieved in two ways. First is the DB/queries optimization. In most cases results are more than acceptable. But every engine has its limitation that can be overcomed by some kind of scaling. Another way is the clustering, segmentation, load balancing etc. Here the DBA should pass the problem to the system architect. This is a different set of competencies. Here is the good explanation how it works:
https://github.com/donnemartin/system-design-primer


Is there a way to figure out an executing query's plan handle by the query text?

Derived from https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan

DECLARE @SearchForSql AS NVARCHAR(MAX) = 'SQL TEXT'

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE Text LIKE CONCAT('%' ,@SearchForSql,'%')
    AND Text NOT LIKE '%-- Self Reference Marker --%'

Note: The "NOT LIKE" is there to ensure that you don't find the statement you are running, which is inherently self referencing.


Slave DB promoted as Master DB when Master DB is down. New data added on the new master while old master is down

As @jkavalik said with your current setup the only option you have is to recreate the master as the slave of the new master and do a switchback if necessary.

Keep in mind that you will have some data loss because you can have some unreplicated transactions on the failed master unless you enable semi-sync replication.

  Sidenote: you don't have to write your own scripts for
  moving/promoting slaves if you don't want to. There are plenty of open
  source solutions which can do this for you. For example you can give a
  try to orchestrator. I used this in production setup with over
  4000 servers.

  Jetpants is also a popular toolset developed by Tumblr which can also handle cloning and many other common tasks.

  But there are also other solutions if you are not satisfied with these.

Further down the road

If you want to have better HA and/or more automated way of operation you can look at the following solution. (this list is not complete only some pointers to give you a start)

Percona XtraDB cluster

With XtraDB cluster you can ensure consistency and have true HA with even under 100ms failover time (depending on your failover technology).

MySQL Fabric

It's Oracle's in house HA solution. Requires client libraries so make sure your application can handle it properly.

MySQL MMM

One of the old player on the MySQL HA market but worth to mention.

I hope this helps.


Your my.cnf file should have:

[mysqld]
init_connect="CALL audit.login_trigger()"

Or any other group name from running mysqld --help --verbose, there's a line like The following groups are read: mysqld server mysqld-10.1 mariadb mariadb-10.1 client-server galera, so any of those groups be can used instead of [mysqld].

SET GLOBAL ... is for setting mysqld system variables at runtime. The config file takes the shorter form.


I don't think it is possible to get your expected result set using a single Select-statement. IMHO you must create an empty copy of the result table and then use following single-row Insert to prevent any duplicate values. Repeat this until you get the expected n rows.

insert into result_table
select top 1 column1, column2,column3,column4
from base_table as t1
where Column2>= '2016-07-01'
  and not exists
  ( select *
    from result_table as t2
     where Column2>= '2016-07-01'
       and
        (  t1.column1 = t2.column1 -- unique data in each column
        or t1.column2 = t2.column2
        or t1.column3 = t2.column3
        or t1.column4 = t2.column4
        )
  )
 Order By Column2;


SQL Server database : moving from 2008 to 2005 - encrypted procedure issue

I ran into this problem a few years back myself. However because SQL Server doesn't actually "encrypt" the objects that you create (it obfuscates them) you can quite easily reverse the process of obfuscation to get the definition back.I use the following procedure to script out encrypted objects:

CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL)
AS
--Jon Gurgul 27/09/2010
--Adapted idea/code from shoeboy/joseph gama
SET NOCOUNT ON
IF EXISTS
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e
on (ec.[endpoint_id]=e.[endpoint_id])
WHERE e.[name]='Dedicated Admin Connection'
AND ec.[session_id] = @@SPID
)
BEGIN

DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX)
SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
SET @b='ALTER PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS '+REPLICATE('-', 8000)

    BEGIN TRANSACTION
        EXECUTE (@b)
        SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
    ROLLBACK TRANSACTION

SET @d = REPLICATE(N'A', (DATALENGTH(@a) /2 ))
SET @i=1
WHILE @i<=(DATALENGTH(@a)/2)
BEGIN
SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING('CREATE PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS ' + REPLICATE('-', 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1)))))
SET @i=@i+1
END

SELECT @d [StoredProcedure]

END
ELSE
BEGIN
    PRINT 'Use a DAC Connection'
END

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The original article where this can be found is http://social.msdn.microsoft.com/Forums/en/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330

I hope this is of help to you.


If positive, sum all items. If negative, return each one

Try this:

SELECT   salesid, sum(num) as num
FROM     #BE
WHERE    num > 0
GROUP BY salesid
UNION ALL
SELECT   salesid, num
FROM     #BE
WHERE    num < 0;

If you want both the sum values in one row then you must create a maxValue (and minValue) function and use this as sum(maxValue(0, num)) and sum(minValue(0, num)). This is described in: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?


Cassandra nodetool repair scheduling and gc_grace_period

Found an answer to my own question on SO. There seems to be much more cassandra related questions then on DBA SE

https://stackoverflow.com/questions/32340429/what-does-cassandra-nodetool-repair-exactly-do

The data can become inconsistent whenever a write to a replica is not completed for whatever reason. This can happen if a node is down, if the node is up but the network connection is down, if a queue fills up and the write is dropped, disk failure, etc.

When inconsistent data is detected by comparing the merkle trees, the bad sections of data are repaired by streaming them from the nodes with the newer data. Streaming is a basic mechanism in Cassandra and is also used for bootstrapping empty nodes into the cluster.

The reason you need to run repair within gc grace seconds is so that tombstones will be sync'd to all nodes. If a node is missing a tombstone, then it won't drop that data during compaction. The nodes with the tombstone will drop the data during compaction, and then when they later run repair, the deleted data can be resurrected from the node that was missing the tombstone.

So it's sufficient that the tombstone will be delivered to other nodes before it expires on the original node, and that will complete when the repair runs on this node (as I think). I think because the nodetool takes snapshots before it runs, there is no chance that the tombstone will be gone in the middle of the repair and so will be lost.

It would be nice to test it though.


MyISAM
You should perform this as follows:
TRUNCATE TABLE mydb.myisam_mytable;
ALTER TABLE mydb.myisam_table DISABLE KEYS;
LOAD DATA INFILE ... INTO mydb.myisam_table ... ;
ALTER TABLE mydb.myisam_table ENABLE KEYS;
ANALYZE TABLE mydb.myisam_table;

The DISABLE KEYS will allow the LOAD DATA INFILE perform a linear building of the PRIMARY KEY and any UNIQUE indexes.
The ENABLE KEYS will perform a linear building of all non-unique (secondary) indexes.
Skip doing DISABLE KEYS and ENABLE KEYS if there are no secondary indexes.
ANALYZE TABLE compiles index statistics (the last step of an OPTIMIZE TABLE)
InnoDB
OPTIMIZE TABLE would essentially do a reload of the table a second time. That's like doing LOAD DATA INFILE twice.
In other words doing this:
LOAD DATA INFILE ... INTO mydb.innodb_table ... ;
OPTIMIZE TABLE mydb.innodb_table;

would mechanically behave like
LOAD DATA INFILE ... INTO mydb.innodb_table ... ;
ALTER TABLE mydb.innodb_table ENGINE=InnoDB;

No real benefit in the case of InnoDB. Just the LOAD DATA INFILE would be adequate.


Should you encrypt the system databases?

The answer is "NO".

When you attempt encrypt a system database, SQL Server complains:

  Cannot encrypt a system database.  Database encryption operations
  cannot be performed for 'master', 'model', 'tempdb', 'msdb' or
  'resource' databases.

However, it's important to realize that any successful encryption of a non-system database will cause TempDB to be encrypted automatically, to protect temporary objects. In fact, even if TDE is later removed from the user database, TempDB will remain encrypted.


Combine column from multiple rows into single row

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE
(
  id INT,
  row_num INT,
  customer_code VARCHAR(32),
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)


How to install keyring_aws plugin in MySQL?

Unfortunately, the keyring_aws plugin is only available on the Enterprise edition (not available on the community edition).

For community edition, the only encryption method available for tablespace encryption is the standard keyring (keyring_file.so)

To load the plugin, use the --early-plugin-load option to name the plugin library file that contains it. For example, on platforms where the plugin library file suffix is .so, use these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary):

[mysqld]
early-plugin-load=keyring_file.so

If you need the AWS plugin and can't pay for enterprise edition your best option could be MariaDB:
https://mariadb.com/kb/en/library/aws-key-management-encryption-plugin/


SQL server - parsing element of delemiated string

You can take a SUBSTRING of your string to ignore the first element, then use PARSENAME. Then, you don't have to use a function.

declare @var varchar(64) = 'test1:test2:test3:test4:test5'

select
    ThirdElement = parsename(replace(substring(@var,charindex(':',@var) + 1,len(@var)),':','.'),3)
    ,FourthElement = parsename(replace(substring(@var,charindex(':',@var) + 1,len(@var)),':','.'),2)


Double Transactional Replication (e.g. Two report servers)

Is it a good plan to use SQL Server Transactional Replication from
  server one to server two then another from server two to server three?
  Most of us DBAs here are advise against it.

I have done this before because that was the best design we could come with to meet business requirements.  Biggest issues with this design, if you are to make any changes to any publication/article you will first have to

Remove the subscription from server two to server three
Make changes in publication/article in server one
Reinitialize and resync between server one and server two
Once sync is completed, add back subscription from server two to server three
Reinitialize and resync between server two and server three (There is a way to avoid this step. See this question and answer)

  Are two subscriptions from the production server a big hit?

Depends on two things:

Location of the distribution database
Push vs Pull subscription

If you are using the same publication for all subscribers (two in your case) the workload on publisher database/server does not increase provided your distribution database is not in the same server as publisher databases. Log reader will read the publication database's transaction log once and add the replicated transactions in the distribution database. Distribution agent will read transactions from the distribution database and apply in the subscription databases.


Why is a max degree of parallelism 1 recommended and used for Sharepoint in SQL Server?

A busy SharePoint site is expected to have a lot of users which generate a huge volume of simple database requests.

If you had 8 cores and 200 users, you'd fill the queues parallelising just a few user's worth of workloads. By forcing each workload onto one core, you are ensuring a better "minimum standard" experience for everyone while reducing the time wasted due to context switching where it wasn't strictly necessary (as those queries were simple and would finish quickly enough on one core anyway).

That's my understanding of it.

Regarding the indexing, that's done by Sharepoint when it installs/upgrades and you don't need to touch it. As Shanky and Max posted, Sharepoint (and some other high-end Microsoft products like SCOM) manages maintenance on itself.

I still do weekly maintenance on top of that to keep it standard across my servers. It doesn't break anything.


Excluding Read only databases using Ola Hallengren script

It's available for backups.

Though it's a somewhat recent addition, so you may need to upgrade your scripts.

For index maintenance, database status is checked so commands don't actually run on databases that aren't fully accessible.

IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'
BEGIN

Hope this helps!


Strategy to find the unique bivariate polynomials

The number of polynomials is the number of distinct graph_id values.
(You probably have a separate table for graphs; in this case, the SELECT DISTINCT subqueries can be replaced with SELECT graph_id FROM graph.)

To count unique polynomials, we exclude any that are duplicates.

A polynomial is a duplicate if there exists any other polynomial with a smaller ID (the smallest ID would be the non-duplicate) and with the same coefficients.

Two polynomials have the same coefficients if there are not any differences in the possible x/y/coefficient combinations for both, i.e., for each row of one polynomial, the same row must exist for the other polynomial.
In other words, there must not exist any row that does not have a match for the other polynomial.

And now that we have the description in the language of set theory, we can translate it directly into SQL.
(I've used compound SELECTs for the innermost comparisons to avoid yet another level of negated subquery lookups.)

SELECT COUNT(*)
FROM (SELECT DISTINCT graph_id
      FROM tutte_polynomial) AS a
WHERE NOT EXISTS (SELECT 1
                  FROM (SELECT DISTINCT graph_id
                        FROM tutte_polynomial) AS b
                  WHERE b.graph_id < a.graph_id
                    AND NOT EXISTS (SELECT x_degree, y_degree, coeff
                                    FROM tutte_polynomial
                                    WHERE graph_id = a.graph_id
                                    EXCEPT
                                    SELECT x_degree, y_degree, coeff
                                    FROM tutte_polynomial
                                    WHERE graph_id = b.graph_id)
                    AND NOT EXISTS (SELECT x_degree, y_degree, coeff
                                    FROM tutte_polynomial
                                    WHERE graph_id = b.graph_id
                                    EXCEPT
                                    SELECT x_degree, y_degree, coeff
                                    FROM tutte_polynomial
                                    WHERE graph_id = a.graph_id))

If computing the count dynamically is too slow, you could try to create a temporary table that contains the data in a format that is easier to count:

CREATE TABLE polys_as_string AS
SELECT group_concat(data)
FROM (SELECT graph_id,
             x_degree || '|' || y_degree || '|' coeff AS data
      FROM tutte_polynomial
      ORDER BY graph_id, x_degree, y_degree)
GROUP BY graph_id;

CREATE INDEX polys_as_string_index on polys_as_string(data);

SELECT COUNT(DISTINCT data) FROM polys_as_string;


You can prepare a query with a VALUES expression:

SELECT a.*, b.*
FROM  (
   VALUES
      (id1a, id2a)
     ,(id1b, id2b)
     , ...
   )   t(id1, id2)
JOIN   tablea a ON a.id = t.id1
JOIN   tableb b ON b.id = t.id2;

Or you COPY the values from the text file to a temporary table and proceed from there:

CREATE TEMP TABLE t(id1 int, id2 int);

COPY t FROM '/path/to/file.csv';

SELECT a.*, b.*
FROM   t
JOIN   tablea a ON a.id = t.id1
JOIN   tableb b ON b.id = t.id2;


Why does the SHOW FULL COLUMNS command show the Type field in lower case?

I suggest it is sloppiness on the part of the implementor.

Keywords are not case sensitive.  A common convention, except by some SHOW commands, is to use uppercase.

Column names are not case sensitive.

Table and database names may be case sensitive, primarily dependent on the OS.


Half of a transaction completes, when the other half fails?

@@ERROR is reset after every statement. The error from your attempt to alter the first procedure is no longer detectable via @@ERROR after you successfully altered the second procedure. Here is an even simpler repro:

SELECT 1/0;
GO
SELECT @@ERROR; -- 8134

However if I put a successful statement in between:

SELECT 1/0;
GO
SELECT 1/1;
GO
SELECT @@ERROR; -- 0

In your case, you are doing this:

ALTER dbo.p1 ... -- fails
GO
-- @@ERROR here would be <> 0, but you're not checking it here!

ALTER dbo.p2 ... -- succeeds
GO
-- You're checking @@ERROR here, but success is 0

The only way your current logic would roll everything back is if the last ALTER failed. You need to check for @@ERROR after every ALTER. Here is an example.

USE tempdb;
GO
CREATE TABLE #x(err INT);
GO

BEGIN TRANSACTION;
GO

CREATE PROCEDURE dbo.foo -- fails
AS
  SELECT foo FROM sys.objects;
GO
IF @@ERROR <> 0
  INSERT #x(err) SELECT 1;
GO

CREATE PROCEDURE dbo.blat -- succeeds, but will get rolled back
AS
  SELECT 1;
GO
IF @@ERROR <> 0
  INSERT #x(err) SELECT 1;
GO

IF EXISTS (SELECT 1 FROM #x)
  ROLLBACK TRANSACTION;
ELSE
  COMMIT TRANSACTION;

DROP TABLE #x;


Microsoft SQL Server 2008+ TIMESTAMP problem

You cannot change default dateformat for all users without changing your language Which you do not want to do in your case.

You can create a new user with a default language which supports the dateformat you need will do the trick in this case. Then your application will need to use that login/user for connecting when doing the insert.

For example my test server and my login is set to following.

SELECT NAME,
       alias,
       dateformat
FROM   syslanguages
WHERE  langid = (SELECT value
                 FROM   master..sysconfigures
                 WHERE  comment = 'default language')

  name=us_english

  alias=English

  dateformat=mdy

With your example I get the same error if I try this.

INSERT INTO testDateTimes (id, ts) VALUES (2, '13.05.2017 12:34:56.789')

  Msg 242, Level 16, State 3, Line 15 The conversion of a varchar data
  type to a datetime data type resulted in an out-of-range value. The
  statement has been terminated.

Creating a new login with DEFAULT_LANGUAGE =British

USE [master]
GO
CREATE LOGIN Mary8 WITH PASSWORD = 'A2c3456$#',
CHECK_EXPIRATION = ON,
DEFAULT_LANGUAGE =British
GO
USE [test]
GO
CREATE USER [Mary8] FOR LOGIN [Mary8]
GO
USE [test]
GO
ALTER ROLE [db_owner] ADD MEMBER [Mary8]
GO

Now if I login with Mary8 and run same insert statement it will succeed.

SELECT SUSER_NAME(), USER_NAME();
GO
INSERT INTO testDateTimes (id, ts) VALUES (2, '13.05.2017 12:34:56.789')
GO

  (1 row(s) affected)

Reference I used.


I think this is what you are looking for:

test setup: http://rextester.com/SGKW56535

create table t (Item int, Kategoria char(2))
insert into t values
   (1111,'OD')
 , (2222,'AL')
 , (1234,'UT')
 , (1122,'OD')
 , (4321,'OD')
 , (5555,'AL')
 , (6666,'UT')
 , (65577,'AX');

query with row_number() partitioned by Kategoria:

select
    rn=row_number() over (
        partition by Kategoria
        order by Item
        )
    , Item
    , Kategoria
  from t;

results:

+----+-------+-----------+
| rn | Item  | Kategoria |
+----+-------+-----------+
|  1 |  2222 | AL        |
|  2 |  5555 | AL        |
|  1 | 65577 | AX        |
|  1 |  1111 | OD        |
|  2 |  1122 | OD        |
|  3 |  4321 | OD        |
|  1 |  1234 | UT        |
|  2 |  6666 | UT        |
+----+-------+-----------+

hard coded pivot example:

with cte as (
  select
    rn=row_number() over (
        partition by Kategoria
        order by Item
        )
    , Item
    , Kategoria
  from t
  )
  select rn, OD, AL, UT
  from cte
    pivot (max(Item) for Kategoria in (OD, AL, UT) ) p;

results:

+----+------+------+------+
| rn |  OD  |  AL  |  UT  |
+----+------+------+------+
|  1 | 1111 | 2222 | 1234 |
|  2 | 1122 | 5555 | 6666 |
|  3 | 4321 | NULL | NULL |
+----+------+------+------+

dynamic pivot example:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct
      ',' + quotename(Kategoria)
      from t
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'')
select  @sql = '
 select rn, ' + @cols + '
  from  (
    select
        Kategoria
      , Item
      , rn=row_number() over (
          partition by Kategoria
          order by     Item
          )
      from t
      ) as a
 pivot (max([Item]) for [Kategoria] in (' + @cols + ') ) p'
 select @sql
 exec(@sql);

generated sql:

 select rn, [AL],[AX],[OD],[UT]
  from  (
    select
        Kategoria
      , Item
      , rn=row_number() over (
          partition by Kategoria
          order by     Item
          )
      from t
      ) as a
 pivot (max([Item]) for [Kategoria] in ([AL],[AX],[OD],[UT]) ) p

dynamic sql result:

+----+------+-------+------+------+
| rn |  AL  |  AX   |  OD  |  UT  |
+----+------+-------+------+------+
|  1 | 2222 | 65577 | 1111 | 1234 |
|  2 | 5555 | NULL  | 1122 | 6666 |
|  3 | NULL | NULL  | 4321 | NULL |
+----+------+-------+------+------+


How can I generate all trailing substrings following a delimeter?


Msg 8905: DBCC CHECKALLOC Error Messages

I would suggest to take a backup and restore the database to a different server and then run DBCC CHECKDB ...repair_allow_data_loss. This way you will know if there is any data loss or not.

Refer to my answer for more details.

If you have a good backup that you can restore, I would prefer to go with that and export the data out of the corrup database. You can use redgate's data compare (not free, but I have used it and its a lifesaver).

Make sure you do diagnosis of your hardware and update driver firmware to latest version and are on the latest supported service pack for sql server 2008R2.


MultiSubnet Failover with RegisterAllProvidersIP 1

We are using the OLEDB flavor of SQLNCLI11 which does not support
  multisubnetfailover out of the box.

Microsoft has deprecated the native client, however, they have released a new OLE DB driver which supports all the native client features plus adds MultiSubnetFailover and TLS1.2 support. See the announcement here. It may be worth looking at upgrading OLE DB to take advantage.

  My understanding is that this driver is legacy and hence should not
  support failover with RegisterAllProvidersIP as 1 and default
  HostRecordTTL( 20 minutes). Although I am unable to failover when
  RegsiterAllProvidersIP is 0 and TTL is 20 minutes (I am able to when
  TTL is lowered to say, 2 minutes), the application successfully fails
  over with RegisterAllProvidersIP set as 1.

With the RegisterAllProvidersIP = 0 and TTL = 20 combo, you would need to wait up to twenty minutes post-failover for the new listener IP to be registered in DNS and picked up by your clients. It is still technically 'working' it's just very slow.

  According to the blog here,
  https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/
  It is recommended to set RegisterAllProvidersIP to 0 and HostRecordTTL
  to 2 minutes to achieve failover, but in my case I found that it is
  not needed as I am able to failover with RegisterAllProvidersIP set as
  1. Am I completely missing the point of recommended settings from MS blog or my multi subnet setup has any issues?

How are you testing to validate that failover works?
What is your connection timeout?

With RegisterAllProvidersIP set to 1, the native client will try and connect to each IP address returned by DNS serially. If the first IP returned by DNS is the correct one it connects, if not it takes ~20 seconds to timeout, then waits ~20 seconds then tries the next IP.

What this means is if your connection timeout is greater than ~40 seconds, its quite possible that you're seeing the behaviour in question but a high connection timeout value is masking it.

Either way, the true fix here is to start using MultiSubnetFailover to make the best use of the rapid failover capabilities provided by RegisterAllProvidersIP=1. If you don't want upgrade the OLE DB driver, you should set a low TTL for the listener DNS entry and set RegisterAllProvidersIP=0. It may work for you now with it set to 1, but conditions in your environment could change and cause this to suddenly stop working.


The Seek
When the LIKE specification is in a variable:
WHERE DESCRIPTOR_TEXT LIKE @ccode

...SQL Server needs to build a plan that will work whatever the variable contains each time the cached execution plan is executed. It would also like to be able to seek if the variable contents are compatible. The solution is to use a dynamic seek, as I explain in the following article:

Dynamic Seeks and Hidden Implicit Conversions

This can result in a much better plan if the variable contains something that can be transformed to an efficient seek, which usually means no leading wildcards. If there is a leading wildcard, the dynamic seek may turn out to be less efficient than a simple scan with a residual LIKE predicate. For example, a full scan may issue more, and larger, read-ahead reads.
To be clear, a dynamic seek can still be used if the variable contains a leading wildcard, but the start and end points of the seek will cover the whole structure.
The Scan
Making the leading-wildcard explicit in a string literal makes it clear that a seek cannot be used, so you get a scan:
WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The downside is that this plan cannot be reused for different string literals, which may cause excessive plan cache usage.
The combination
Using a variable with the recompile option:
WHERE DESCRIPTOR_TEXT LIKE @ccode
OPTION (RECOMPILE)

...means SQL Server can 'sniff' the value of the variable at runtime, embed the value it finds into the query, and produce a plan optimized for that specific value.
The resulting execution plan will not be cached, so there is no possibility of plan reuse, but this also means you will not suffer from plan cache bloat either. There will be a small overhead on each call for the fresh compilation, but this is often a trade-off that makes sense: If the variable contains some seekable, you'll get a seek plan; otherwise, a scan. More details in my article:

Parameter Sniffing, Embedding, and the RECOMPILE Options


Unless you are in a very old version of Postgres, you don't need the double join. You can get the same result by using a LATERAL join.
The duplicate results can be avoided in your method by adding a second condition besides the rec.id = rech2.record_id. With the LATERAL join method, the use of LIMIT  is avoiding it anyway. There can be only 1 row returned from the lateral subquery. We can add a second condition so the choice is deterministic (from the two or more rows with same timestamp):
SELECT rec.id, rec.name, rech.data AS last_history_data
FROM record AS rec
     LEFT OUTER JOIN LATERAL
     ( SELECT rech.data
       FROM record_history AS rech
       WHERE rec.id = rech.record_id
       ORDER BY rech.ts DESC
                -- ,rech.id DESC               -- optional
       LIMIT 1
     ) AS rech
     ON TRUE
ORDER BY rec.id DESC ;

Regarding how to do this with the original method (2 joins and IS NULL check), you could change the ON condition - assuming there is an id column in history table so that (id) or at least (ts, id) is unique:
LEFT OUTER JOIN record_history rech2
ON rec.id = rech2.record_id
   AND (rech2.ts > rech1.ts OR rech2.ts = rech1.ts AND rech2.id > rech1.id)

By the way, you could replace that second LEFT join and IS NULL check with a NOT EXISTS subquery with same results and possibly similar efficiency (or even with a NOT IN subquery although that needs extra care for nullable columns, not recommended).


Cannot generate reports from SQL Management Data Warehouse

Just ran into and resolved this issue today.  Select the View Logs context menu option in the Data Collection Sets--the data upload could have failed, which causes the reports to generate this issue.


Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

If you can't do it in one query, then put the results from the first query into a #temporary table.  Now do a second query to do the math.


If you use SQL Server 2008 R2 Standard Edition, you are limited to 64GB RAM per SQL-Server instance.

Memory Supported by the Editions of SQL Server


How to backup a MySQL database without slowing the application?

A) Take the backup from a replicating slave. Zero performance loss.

B) Use LVM to take a consistent snapshot. Copy the database files at your leisure.

C) Use Rsync over a remote connection to copy the data files, then do a final Rsync with the database briefly locked for a couple seconds.

As an aside, how are they taking a consistent backup with MyISAM tables without LOCKING the application for 8 hours straight? It doesn't seem possible.


Cannot open database “user_auth.mdf” requested by the login. The login failed. Login failed for user 'web_user'

If you created the database using SSMS, you should use the logical database name and not the file with the .mdf extension in your connection string.

Use

Server=XYZ;Database=user_auth;User ID=web_user;Password=web_user

for your connection string and let SQL Server worry about the file-related details.


mssql suddenly cannot access physical file

This is definitely interesting. Normally, Access is denied error is due to changed/missing permission on the file. Did you see any related errors in journalctl?
If it happens again, I'd raise a ticket with Microsoft.


Creating Conditional Rules for Column Values

Yes, you can create a table check constraint without going to triggers or stored procedures. This is valid for Sql Server 2008 or higher:

ALTER TABLE dbo.order_status_logs
   ADD
   CONSTRAINT CHK_EXPECTED_BY
   CHECK ((status_id <> 4 AND expected_by IS NOT NULL) OR status_id = 4);
GO


What might be the reason for licence compliance violation?

One possible cause might be that someone created a sample database using db2sampl. The sample database contains both of these artefacts:

]$ db2 -x "select tabschema, tabname, type, clustered
           from syscat.tables
           where type = 'S' or clustered = 'Y'"

DB2INST1    ADEFUSR    S    -
DB2INST1    EMPMDC     T    Y

It appears as if something in the instance is flipped during create of sample database, dropping the sample database does not help, the violation persists.

As @Chris Aldrich points out in the comment, db2licm -x does a reset of the license report:

DB2 Workgroup Server Edition   In compliance

Out of curiosity I created 1 MQT to see if the violation re-appears. So far it has not. It's now more than 12 hours since I created the MQT and I'm still in compliance.

I'm still puzzled by how the licence compliance mechanism works, but I'll add another question about that.


Retrieve title from rows with a maximum count in other columns

Depending on your exact requirements, something along these lines should work:
Subqueries to determine most common attributes
SELECT DISTINCT title
FROM   archived
WHERE  showurl = (
   SELECT showurl
   FROM   archived
   GROUP  BY 1
   ORDER  BY count(*) DESC, 1
   LIMIT  1
   )
AND    reference = (
   SELECT reference
   FROM   archived
   GROUP  BY 1
   ORDER  BY count(*) DESC, 1
   LIMIT  1
   );

I added DISTINCT to the outer SELECT to fold duplicate titles. It's undefined what you need exactly.
Shorter with window functions
A more modern / sophisticated / elegant approach would be with two window functions in the ORDER BY clause, allowing for a very terse query:
SELECT title
FROM   archived
ORDER  BY count(*) OVER (PARTITION BY showurl)   DESC
         ,count(*) OVER (PARTITION BY reference) DESC
LIMIT 1;

Not necessarily faster than the first solution, though.
Again, guessing what you might want exactly, I only retrieve a single winning title.
The trick here is to partition the rows by showurl and reference, run a count over each partition and order descending by that count. This way, you can determine a winner in a single query level.
This will not work, if there is no row with the most common showurl and the most common reference at the same time. Again, exact requirements would be needed ...
-> SQLfiddle demo.


Users complain that system runs slow when mysqldump is in progress

There is a known issue in Windows, that when you push a large file to another server all the memory ends up getting allocated to the System cache instead of the user processes.  You can look  in the Physical Memory (MB) section of task manager to see how much memory is allocated to the system cache.

This can be solved by backing up to a local disk, then having the remote machine pull that file.


You can test this variation. In theory, it would use an (id, value) index to find the min and max and would not have to count the distinct values at all:

SELECT id
FROM t
GROUP BY id
HAVING MIN(value) < MAX(value) ;


Monitoring Progress of Index Construction in PostgreSQL

According to Postgres Wiki's Index Maintenance page, you can find out the current state of all your indexes with:

SELECT
  t.tablename,
  indexname,
  c.reltuples AS num_rows,
  pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
  pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
  CASE WHEN indisunique THEN 'Y'
    ELSE 'N'
  END AS UNIQUE,
  idx_scan AS number_of_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_tables t
  LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
  LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
      JOIN pg_class c ON c.oid = x.indrelid
      JOIN pg_class ipg ON ipg.oid = x.indexrelid
      JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
  ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

The column num_rows indicates how many rows are covered by your index and index_size will grow as the index is being built.


SQL Server connection from iis website: "error: 26 - Error Locating Server/Instance Specified"

Some of the mystery is solved - The reason that some sites could connect and some couldn't is that there was an additional connection string stored in the machine.config file that two of the sites were using.  The site that worked when a port number was specified did not use this connection string, so worked.  The other sites didn't work when port number was specified because I hadn't added the port number to this connection string in machine.config.  Adding the port number to that connection string as well as the ones stored in web.config allowed all the sites to work.

Additionally, the reason that iisreset had an effect is that once IIS knows the port number that SQL is using, it seems to 'remember' the port number, and so it no longer has to rely on using port 1434.  Once I did an iisreset it 'forgot' what port number was used, so had to make a request on port 1434, which for some reason doesn't work.

So my question has gone from a very confusing 'works sometimes' situation to a basic 'cannot communicate on port 1434' situation.


If you really need to look at all the records, you should take the strain off of MySQLWorkbench. It already has enough on its plate micromanaging MySQL.
SUGGESTION #1 : Use MySQLWorkbench from Another Workstation
Install MySQLWorkbench on another server that is not running MySQL. Remotely connect to MySQL from the other Workstation. By divorcing MySQLWorkbench from MySQL, MySQLWorkbench can focus on what does best, micromanage the data and display at the user's whim. MySQL can focus on doing key caching, query caching, InnoDB, DB Connections, read/writing threading, and all other memory-consuming tasks. This will end the tug-of-war for RAM by both products. Even the OS will reap more of the spoils of war for RAM.
SUGGESTION #2 : Create a Text File and View it in some Editor
Install mysql on Another Workstation. Do not run the server on it, but use the mysql client program. To get data from the table mydb.mytable, from the second server, run this:
C:\> set DESTFILE=C:\subfolderofyourchoice\Display.txt
C:\> mysql -hIPOfMySQLServer -uroot -p --table -Ae"SELECT * FROM mydb.mytable" > %DESTFILE%

Once the file is created , use a text editor to display the file. I suggest the following:

Notepad
vi (yes I said, vi for Windows, I use it today)
Microsoft Word

Give it a Try !!!


Why did full backup file size grow after data movement for Availability Group replica was suspended?

I'm going to hazard a guess here -
Given that the fault that led to the replica being suspended is an acknowledged bug in SQL Server, my guess is that as part of the bug, the primary was somehow continually re-trying to send synchroisation updates to the secondary. Those could not be hardened on the secondary's log so the primary retained the transaction information and the continual re-tries slowly increased the amount of log being used for this transaction.
Further, all other transactions on the primary were not being synced to the secondary, and so were persisted to the primary's log and the log backups on the primary were backing up this information.
Lastly, the fact the primary "held on" to the secondary in this way (with the thread that didn't die) is simply also a part of the bug, or is an additional bug that was exposed when the secondary failed to apply the logs. EDIT: It looks like this is a second known issue. In other words, SQL Server should have "let go" of the secondary once the secondary was suspended, but did not, and that is also a bug.
As to the question of what might have been lost had the primary failed during this extended period - my best guess is that the loss would have been limited to information that was tying up the log and that information related to keeping the secondary in sync. If the primary wasn't persisting the changes until receiving confirmation from the secondary that the secondary had hardened that log, then whatever changes were being carried out by the transaction being synced, would have been lost - but all other transaction would have been persisted to the primary (only) and subsequently backed up during the log backups (and then nightly full backups).
Certainly open to feedback on this answer, as it is a "best guess" for the scenario.


Database design for job board based on neighbouring cities

There should be one user table. (user_id, username, etc)

There should be one city table. (city_id, city_name, etc)

There should be one ref_user_city table. (ref_id, user_id, city_id, etc)

There should be one Job table. Job can have only one city where job can be done. (job_id, city_id)

Users can select multiple cities at the time of profile creation or update. Which will be stored in ref_user_city. These cities are where the user lives or user can provide job service.

Now you can easily find which user can do the job in a specific city.

Select user_id from ref_user_city where city_id = 1;

now users who can do a job for city_id 1 you can get data of those users from user table.

Let's say if you want to get jobs for user_id = 1, user 1 lives in city 1 & can provide job service in city 2,3 & 4.

Select job_id from job where city_id in (1,2,3,4);


Why is this simple query taking more than 1 second?

I'm not an expert on MySQL, but it looks to me like it's doing a table scan by secid_date, which isn't even in the query at all. And scanning 17M rows is not what you want to be doing unless you really have to.

Check your indexes

mysql> SHOW INDEX FROM financial_key_ratios\G

I would make sure that you have something like

+-----------------------+------------+------------+--------------+---------------+ ...
| Table                 | Non_unique | Key_name   | Seq_in_index | Column_name   | ...
+-----------------------+------------+------------+--------------+---------------+ ...
| financial_key_ratios  |          1 | ...        |            1 | SecurityId    | ...

and if possible

| financial_key_ratios  |          1 | ...        |            2 | DateModified  | ...

If you aren't indexing on at least SecurityId, you can add it with

CREATE INDEX IdxSecurityId ON financial_key_ratios (SecurityId)

You can take it a step further and include DateModified

CREATE INDEX IdxSecurityId_DateModified ON financial_key_ratios (SecurityId, DateModified)


In the version of the query with FORCESEEK, the majority of the query cost is in the key lookup. You can eliminate this key lookup by creating a nonclustered index on GridRunId and MancoId.

Have a look at the estimated cost figure in the SELECT (root) node of your plans. The optimizer will chose the plan with the lowest estimated cost. By adding a covering nonclustered index, you eliminate the need for the key lookup. This should make the estimated cost of the index seek less than the index scan. You might also want to run the query with OPTION (RECOMPILE) to make sure it uses a fresh execution plan. The index could look something like this:

CREATE NONCLUSTERED INDEX [IX_GridRunId_MancoId] ON [dbo].[Documents]
(
    [GridRunId] ASC,
    [MancoId] ASC
)
WITH
(
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY];

The optimizer would ordinarily cost the scan higher than the seek, but the effect of the TOP means the optimizer computes costs for the desired number of rows (a 'row goal') rather than for the whole set (it estimates that a partial scan will locate the 15 desired rows more efficiently than the seek + lookup combination).


Test

UPDATE coupons c, accounts a
SET c.start = a.vipExpires,
    c.end = DATE_ADD(a.vipExpires, INTERVAL c.days DAY),
    a.vipExpires = c.end
WHERE a.id = 1 AND c.id = 1;


What is different between SQL-Server versions 2008R2 and 2012

A few things you'll want to watch out for:

Changes to memory manager mean some changes to some dynamic management views - https://sqlblog.org/2011/01/08/sql-server-v-next-denali-changes-to-performance-counters
fn_virtualfilestats no longer takes -1 for parameters - https://sqlblog.org/2011/06/28/sql-server-v-next-denali-breaking-change-to-fn_virtualfilestats
you may no longer be able to identify system databases simply by DB_ID between 1and 4 - https://sqlblog.org/2011/07/08/sql-server-v-next-denali-breaking-change-to-system-databases-database_id-db_id
Temp tables now get negative object IDs, so make sure you don't have code that checks for object id > 0 - https://sqlblog.org/2010/11/09/sql-server-v-next-denali-breaking-change-to-temp-tables
sys.dm_exec_sessions has a new column, authenticating_database_id

For an official list (it doesn't include all of the above items, and there may be others that aren't coming to mind):

http://technet.microsoft.com/en-us/library/ms143179(v=SQL.110).aspx

You may also want to check the discontinued features page:

http://technet.microsoft.com/en-us/library/ms144262

And also run the Upgrade Advisor against your instance, this is exactly what it's for:

http://technet.microsoft.com/en-us/library/ms144256.aspx


Using start and end event logs to create a table/view containing spans between the times of each log

Assuming all columns NOT NULL. And 'left' is never earlier than the associated 'joined'.
Simple case
If users can join teams only once (which would be enforced by a UNIQUE constraint on ("user", team), ideally), then the solution is a simple GROUP BY and works in Redshift as well as in most any RDBMS:
SELECT "user", team
     , min(CASE WHEN event = 'joined' THEN timestamp END) AS joined
     , max(CASE WHEN event = 'left'   THEN timestamp END) AS "left"
FROM   event
GROUP  BY "user", team
ORDER  BY "user", joined NULLS FIRST;

Note the NULLS FIRST clause. Seems like you want to sort an open start with joined IS NULL first. Redshift supports that, too.
Apart from that it's the most basic form of a crosstab / pivot query.
Not so simple
Judging from your column names and example data it might not be so simple. If users can join teams more than once (non-overlapping), you have to do more. You wouldn't want to merge multiple team memberships into one row like in this related answer:

Postgres: Use result of group by as column names

Instead you have to pair up adjacent 'joined' and 'left' rows somehow. There are many ways ...
Postgres 9.4+
For modern Postgres I like this best:
SELECT "user", team
     , min(timestamp) FILTER (WHERE event = 'joined') AS joined
     , max(timestamp) FILTER (WHERE event = 'left'  ) AS "left"
FROM  (
   SELECT *, count(*) FILTER (WHERE event = 'joined')
                      OVER (PARTITION BY "user", team ORDER BY timestamp) AS ct
   FROM   event
   ) sub
GROUP  BY "user", team, ct
ORDER  BY "user", joined NULLS FIRST;
Using the aggregate FILTER clause in a window function as well as in an aggregate function. Related (with links to alternatives):

Return counts for multiple ranges in a single SELECT statement

This way we count how many times the same user joined the same team, so we can group adjacent rows. Works for a missing 'joined' at the begin or a missing 'left' at the end as well.
Redshift
... does not support the new FILTER clause. We can substitute with a plain old CASE:
SELECT "user", team
     , min(CASE WHEN event = 'joined' THEN timestamp END) AS joined
     , max(CASE WHEN event = 'left'   THEN timestamp END) AS "left"
FROM  (
   SELECT *, count(CASE WHEN event = 'joined' THEN 1 END)
                      OVER (PARTITION BY "user", team ORDER BY timestamp, event) AS ct
   FROM   event
   ) sub
GROUP  BY "user", team, ct
ORDER  BY "user", joined NULLS FIRST;

SQL Fiddle.

Aside: you shouldn't use reserved words as identifiers, even if it's permitted by Redshift (or Postgres).


Postgresql - Streaming replication - STONITH

well, you cannot switch back easily.
what you can do is to use the old backup method:

SELECT pg_start_backup('whatever');
rsync the data dir
SELECT pg_stop_backup();

this is the best way as not all the data has to be transported over the network.
i definitely recommend this replacement for pg_basebackup because it is the only RELIABLE way to get things back in order (nobody knows for sure what happens before the crash).
sure, there might be other tricks out there but i would strongly vote for this method.


making a where clause optional in a sql statement

You may add the WHERE clause in the LEFT JOIN so not joining rows are still in the result set.

SELECT `performers`.`hash`, `performers`.`alias`,
  `performers`.`date_updated`, `performers`.`status`,
  IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`,
  `images`.`image_hash_file`
FROM `performers`
LEFT JOIN `images` ON `images`.`asset_id` = `performers`.`id` AND `images`.`asset_type` = 'performer'
ORDER BY `alias` ASC
LIMIT 12`


How to run stored procedure in another database on the same server using a different login?

Assuming ua is associated with the login la and ub is associated with lb.

To make this work you could grant impersonation of lb to la. Then you can create a wrapper of b.dbo.someproc in a like this:

CREATE PROCEDURE dbo.b_someproc
AS
BEGIN
  EXECUTE AS LOGIN='lb';
  EXEC b.dbo.someproc;
  REVERT;
END;

However, impersonation is a very broad permission that you probably do not want to grant. To get around it you can either add user ub to database a or user ua to database b.

If you add ub to a you can write a wrapper like this:

CREATE PROCEDURE dbo.b_someproc
WITH EXECUTE AS 'ub'
AS
BEGIN
  EXEC b.dbo.someproc;
END;

By far the easiest way however is to ad ua to b and just allow it to call the procedure directly.


Look at mongodump and mongorestore here in this tutorial in the documentation for backup and restore tools


How do I check my sp_Blitz/sp_WhoIsActive versions across multiple servers/databases?

For sp_Blitz, there's an enhancement request filed at Github to add outputs for version, and if you'd like to influence how the work is done (or contribute code), you're welcome to leave comments over there.

For sp_WhoIsActive, the author Adam Machanic is considering adding an output to the Messages tab each time it runs.

Until then, Oreo's suggestion above of checking the code is the best one. We won't break that intentionally - we haven't changed those strings in years, and don't intend to start, heh.


Maximum possible memory usage: 23.6G (785% of installed RAM)

I wish the creator of that tool would toss that line.  It unnecessarily scares people.  And the number it gives is both too small and too large.
But, in your case, it there is a real danger.  If you had just 3 queries running that were nasty enough, they could be asking for 2.4GB -- more than your RAM!!  This is because tmp_table_size and max_heap_table_size are set to 800M.  Change them to, say, 20M.  Queries will still work, but they may spill to disk more often.  More importantly, you want swap or crash.
Fragmented tables -- bogus.  Virtually all tables are fragmented.  OPTIMIZE TABLE is almost never worth doing.
But...
key_buffer_size of 1.5G in 2GB of RAM is really bad. 300M is about right if you are using only MyISAM.  The key_buffer is for MyISAM indexes.  You need to leave room for data, which is cached by the Operating System.
Since the Query cache hit rate is so low and the size is dangerously high, you are better off turning it off and using the RAM for other stuff.  query_cache_type = OFF and query_cache_size = 0.
Since you have a "tiny" amount if RAM (2GB), you should probably lower max_connections. Look at the STATUS value of Max_used_connections to see the high water mark since last restart.
Edit

Upgrade to a 64-bit MySQL
Configure key_buffer_size and innodb_buffer_pool_size in my.cnf to the amount of RAM you have.  Guidelines:  http://mysql.rjweb.org/doc.php/memory
See how things are going; come back to us for more help (and/or abuse) if needed.


If you have sp_WhoIsActive (great tool, can be downloaded here: http://whoisactive.com/) you can log the results to a table and then email out if the threshold you set for the duration is tripped.

example:

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---- Load WhoIsActive To A Temp Table

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

CREATE TABLE #wia ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)

EXEC sp_WhoIsActive
    @format_output = 1,
    @destination_table = '#wia'

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---- Find Long Running Queries

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

SELECT
[dd hh:mm:ss.mss],
session_id,
CAST(sql_text AS VARCHAR(MAX)) AS sql_text,
login_name,
wait_info,
host_name,
database_name,
program_name,
start_time
INTO #Temp
FROM
#wia

WHERE
start_time < DATEADD(HOUR,-8,GETDATE() ) -- CHANGE THIS TO WHATEVER YOU WANT

DECLARE @CNT INT = (SELECT COUNT(*) FROM #Temp )

IF @CNT > 0
BEGIN
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [dd hh:mm:ss.mss] AS 'td','',session_id AS 'td','', [sql_text] AS 'td','',
[login_name] AS 'td','',[wait_info] AS 'td','','',[host_name] AS 'td','',
[database_name] AS 'td','', [program_name] AS 'td','',start_time AS 'td'
FROM  #Temp
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='
Long Running Queries

 dd hh:mm:ss.mss 	  session_id 	  sql_text 	  login_name
 wait_info 	  host_name 	  database_name
 program_name 	  start_time

'

SET @body = @body + @xml +'
'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXXXX', -- replace with your profile
@body = @body,
@importance = 'HIGH',
@body_format ='HTML',
@recipients = 'XXXXX', -- replace with your email address
@subject = 'Longing Running Queries On <SERVER_NAME>' ;

DROP TABLE #Temp

END


Apache Cassandra INSERT-only permissions

No, there is no such way right now because it's how the Cassandra works.

INSERTS in Cassandra are really upserts, so if you insert data with the same primary key, then they are modified, like you do with UPDATE.  And similarly, if you insert null values, then you're deleting the data.


After Exporting Database should be empty

Use pt-archiver tool. It can delete data that it moves. Alternatively use drop & create after the select into outfile.


SSIS Data Flow data access mode - what's the point of 'table or view' vs fast load?

The OLE DB Destination Component's Data Access Modes comes in two flavors - fast and non-fast.

Fast, either "table or view - fast load" or "table or view name variable - fast load" means that data will be loaded in a set-based fashion.

Slow - either the "table or view" or "table or view name variable" will result in SSIS issuing singleton insert statements to the database. If you're loading 10, 100, maybe even 10000 rows, there's probably little appreciable performance difference between the two methods. However, at some point you're going to saturate your SQL Server instance with all these piddly little requests. Additionally, you're going to abuse the heck out of your transaction log.

Why would you ever want the non-fast methods? Bad data. If I sent in 10000 rows of data and the 9999th row had a date of 2015-02-29, you would have 10k atomic inserts and commits/rollbacks. If I was using the Fast method, that entire batch of 10k rows will either all save or none of them. And if you want to know which row(s) errored out, the lowest level of granularity you will have is 10k rows.

Now, there are approaches to getting as much data loaded as fast as possible and still handle dirty data. It's a cascading failure approach and it looks something like

The idea is that you find the right size to insert as much as possible in one shot but if you get bad data, you're going to try resaving the data in successively smaller batches to get to the bad rows. Here I started with a Maximum insert commit size (FastLoadMaxInsertCommit) of 10000. On the Error Row disposition, I change it to Redirect Row from Fail Component.

The next destination is the same as above but here I attempt a fast load and save it in batches of 100 rows. Again, test or make some pretense of coming up with a reasonable size. This will result in 100 batches of 100 rows sent because we know somewhere in there, there is at least one row that violated the integrity constraints for the table.

I then add a third component to the mix, this time I save in batches of 1. Or you can just change the table access mode away from the Fast Load version because it'll yield the same result. We will save each row individually and that will enable us to do "something" with the single bad row(s).

Finally, I have a failsafe destination. Maybe it's the "same" table as the intended destination but all the columns are declared as nvarchar(4000) NULL. Whatever ends up at that table needs to be researched and cleaned/discarded or whatever your bad data resolution process is. Others dump to a flat file but really, whatever makes sense for how you want to track bad data works.


migration of oracle xe 10 to xe 11 fails with wrong paths used in the import

I found out what the problem is, in my XE 10 additional tablespaces were created. When the import into 11 discovers those tablespaces do not exist yet, it tries to recreate them with the statement with which they were created in the first place, in XE 10. That explains where the wrong paths are coming from.
I just copy & pasted the create statements from the log, changed the paths, executed them, repeated the import and now it works.
A bit strange that the documentation doesn't mention this possible hiccup.


Upgrading hardware for MySQL. If we get SSDs, are CPUs likely to become a bottleneck

I'm a proud owner of a Samsung 850 Pro for my desktop machine. It is a great disk, but it is not server-grade, and it is far from the PCI Flashcards sold by Virident and FusionIO (to put examples of some known brands). On the specialized hardware, a recent version of MySQL is almost a must, and some configuration tuning is needed to get most of them (change buffer, innodb checksums, transaction logs, doublewrite area, etc.).

Check some of the vendor benchmarks (Oracle MySQL, MariaDB, Percona), as they usually boast its capabilities on those kind of devices.

However, a generalization cannot be reached- most of the problems are load-dependent. To give you an extreme example: if your queries all look like this:

SELECT AES_DECRYPT(*, key) FROM my_table;

Your CPU is probably going to be your bottleneck, while if you only insert new rows, then most of the delay will probably due to the disk latency. You should check if with the current hardware, the CPU load is high, that will probably increase with the new storage- but you may still hit other problems, like concurrency issues due to several threads trying to update the same row at the same time.

  assuming the current bottleneck is I/O and we replace the disks with SSDs, is it possible/likely for the E5-2620V2 CPUs to become the bottleneck

It is possible, but just with a bad configuration (what I mentioned before), assuming recent versions of MySQL (5.6, 5.7). Remember that SSDs are still way slower than the 1st cache level. You should probably ask again when the hardware is installed and meanwhile read sources like:

http://www.percona.com/resources/technical-presentations/ssdflash-modern-open-source-databases-percona-mysql-webinar (generalities on ssds for databases)
http://dimitrik.free.fr/blog/archives/2013/10/mysql-performance-the-road-to-500k-qps-with-mysql-57.html (check the configuration used)
http://www.percona.com/blog/2013/10/03/inexpensive-ssds-database-workloads/ (optimizing MySQL for SSDs)
http://www.percona.com/live/mysql-conference-2013/sessions/you-know-ssd%E2%80%99s-make-mysql-fast-do-you-know-why
http://www.percona.com/live/mysql-conference-2012/sessions/transitioning-ssds-high-io-environment.