How to get information about the sequence behind the new Identity Column in Postgres 10? (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)

It appears you use the same functions as before:

https://www.postgresql.org/docs/10/static/functions-info.html

  pg_get_serial_sequence returns the name of the sequence associated
  with a column, or NULL if no sequence is associated with the column.
  If the column is an identity column, the associated sequence is the
  sequence internally created for the identity column. For columns
  created using one of the serial types (serial, smallserial,
  bigserial), it is the sequence created for that serial column
  definition. In the latter case, this association can be modified or
  removed with ALTER SEQUENCE OWNED BY. (The function probably should
  have been called pg_get_owned_sequence; its current name reflects the
  fact that it has typically been used with serial or bigserial
  columns.) The first input parameter is a table name with optional
  schema, and the second parameter is a column name. Because the first
  parameter is potentially a schema and table, it is not treated as a
  double-quoted identifier, meaning it is lower cased by default, while
  the second parameter, being just a column name, is treated as
  double-quoted and has its case preserved. The function returns a value
  suitably formatted for passing to sequence functions (see Section
  9.16). A typical use is in reading the current value of a sequence for an identity or serial column, for example:

  SELECT currval(pg_get_serial_sequence('sometable', 'id'));


Convert SQL Server 2012 SSIS package to 2008

Yes,  you'll need to recreate the package in 2008. 2005/2008 packages can be upgraded to 2012, but downgrading doesn't work.

There is a codeplex project/utility that can move 2008 packages to 2005 by changing version numbers, and making small changes to the XML. Unfortunately this approach doesn't work with 2012, as the package format went through some big changes.


MySQL (or MariaDB) multi-master without MMM

The biggest caveat with only 2 machines in a cluster is the possibility of "split-brain", in which the nodes lose communication with one another, and each assumes that they are now the Master, possibly getting out of sync with the other.  This risk is mitigated if you can absolutely ensure that writes will not go to the 2 Masters separately while they are not in communication.  It is generally advisable to have at least 3 (and always preferably an odd number of nodes) to prevent these types of situations.

I did something similar to what you are proposing (with 3 nodes, not just two) as a MariaDB/Galera Cluster.  In actuality, it is a Multi-Master setup, in which a write to any server will be propagated to the others, but in practice I treat it as a Master-Slave with automatic failover.

For Context, I will describe how I do my Master-Slave automatic failover using HAProxy:

I use HAProxy on my application servers and set up two ports, one that my application treats as the Master for writes, and one that the application treats as the Slave for reads.  In my HAProxy, the Master port is set to use only the Master, but fail to the Slaves in a particular order.  If the Master goes down, HAProxy detects it and redirects writes to the next server, which becomes the de facto Master.

My HAProxy Slave port balances between the Slave nodes (you could include the Master, too) using one of several different balancing algorithms.  If one of Slaves goes down, HAProxy continues balancing with whatever is available until the Slave comes back up.

If the Master goes down, HAProxy re-directs and the application just works.  When the Master comes back up, it syncs with the other machines before accepting connections.  When it begins accepting connections, then HAProxy starts sending writes to it, and it becomes the Master again, and all is right with the world.

In your situation, you can do something similar, using HAProxy as your connection/failover tool.  You do not have to split out your reads and writes, but I do so as to take advantage of the extra machines.  I do like the MariaDB/Galera solution.  I would hesitate only having 2 nodes.  It feels dangerous.


Planning and Optimizing the table structure InnoDB MySql

Recommendation

"all the records belonging to one of the categories" --> Build a many:many mapping between records and categories.  To have that table optimal, follow the tips here .  Be sure to use a 2-byte SMALLINT UNSIGNED for the category_id.  (You should not trust that you won't go beyond 255.)

Then the query is something like

SELECT r.*
    FROM RecordsCategories AS rc
    JOIN Records AS r  USING(record_id)
    WHERE rc.category_id = 123;

This approach will do an index scan of a portion of rc to find the record_ids, then randomly reach into r via the PK.  Yes, there will be "hundreds of millions of rows" in rc, but it will scan only a small part of it, because all the entries for category_id=123 will be 'clustered' together (either in the data or the secondary index).

Critique of alternatives

If you have a column with a commalist of categories, then every row will need to be fetched.  That is a full table scan ("tens of millions of rows" = a few gigabytes) of Records.  (BTW, don't use LIKE %123%, use FIND_IN_SET() -- not faster, but less messy to handle edge cases.)

"a separate table for each category" -- This is a very common question on this (and other) forum.  The answer is "NEVER!".

"separate column for each of the categories" -- Gag me with a spoon!  The table will be fatter and, again, you will need a full table scan.

"200+ indexes" -- The hard limit is 64.  The practical limit is more like 5.

Building on Akina's and Acidon's comments:  With 8.0, you can use TINYBLOB or BINARY(..) and set bits.  This is about as clumsy as having multiple SETs.  Before 8.0, bitwise ORs (etc) were limited to 64 bits; now BLOBs work.  SET/BLOB/BINARY/BIGINT save the most space.  But your entire dataset is only tens of GB, so I don't see 'size' as a problem.  Even the many:many table I recommend will be only several extra GB.


Like with any other parameter that can be defined for every session, the setting in postgresql.conf is the default in the absence of an explicit setting for the session.


Return all except some fields using findOne() in mongodb for node.js

I think you need to use project(). See the example here - Projections


Joining 2 tables based on Dynamic criteria

Joining (or the expression in SELECT) looks similar to:

FROM #A
FULL OUTER JOIN #PS ON #A.ACCOUNT_NUMBER = #PS.ACCOUNT_CODE__C
                   AND (    (#A.ID = #PS.I AND INSTR(#A.Id_Type, 'I') > 0)
                         OR (#A.ID = #PS.T AND INSTR(#A.Id_Type, 'T') > 0)
                         OR (#A.ID = #PS.C AND INSTR(#A.Id_Type, 'C') > 0) )

The rest is trivial.


Rebuild a mysql slave adding innodb_file_per_table without locking the master

really all could be shorter:

Stop mysql on Slave, change settings, Start MySQL on Slave
Login to MySQL; Stop slave; exit from mysql client
make dump from Master direct from Slave:

  mysqldump --host=Master_IP --port=Master_port -u
  user_with_enought_rights -p --single-transaction --all-databases
  --master-data=1 > dump.sql

restore dump on Slave

  mysql -u root -p < dump.sql

start Slave

It will not lock master (in case of InnoDB tables) and automatically adjust Slave to Master position


Here is one idea:

select start_date, stop_date
     , max(case min15 when 0 then sample_value end) as part1
     , max(case min15 when 15 then sample_value end) as part2
     , max(case min15 when 30 then sample_value end) as part3
     , max(case min15 when 45 then sample_value end) as part4
from (
     select start_date, stop_date, sample_value, min15
          , row_number() over (partition by min15
                               order by sample_date desc) as rn
     from (
         select h.*, trunc(to_char (sample_date,'MI') / 15) * 15 as min15
         from heats h
     ) T
) U
where rn = 1
group by start_date, stop_date;

In the innermost select, an attribute min15 is calculated. It is the 15 min period to which sample_date belongs

select h.*
     , trunc(to_char (sample_date,'MI') / 15) * 15 as min15
from heats h

At the next level, we enumerate the rows within each 15 min period, starting with the last one:

     select start_date, stop_date, sample_value, min15
          , row_number() over (partition by min15
                               order by sample_date desc) as rn
     from (
         ...
     )

At the outermost level, we filter out all but the last in each 15 min period (where rn = 1)

We also pivot out the last values to an individual column. Since we get null for all but one, we aggregate to a single row with max:

select start_date, stop_date
     , max(case min15 when 0 then sample_value end) as part1
     , max(case min15 when 15 then sample_value end) as part2
     , max(case min15 when 30 then sample_value end) as part3
     , max(case min15 when 45 then sample_value end) as part4
from (
    ...
)
where rn = 1
group by start_date, stop_date;

I don't normally use Oracle, so I googled for how to determine which 15 min period a row belongs to. There are probably more elegant ways to do it, but it should give you a start.

Fiddle


SQL Server Cluster Service Online Pending Status

The default restart settings in WSFC are such that a faulting resource is terminated and then retried once before being declared offline.

In this case: the cluster discovers the fault, calls for terminate and then attempts to re-online.  Storage Foundation for Windows detects that the Disk Group is not present and returns a status of Pending.  This creates a deadlock that lasts until either RHS.exe terminates (through hang protection); or the online pending timeout is reached.

So, recommend to change Pending timeout and deadlock timeout as per below link.

To change deadlock timeout(Values are in milliseconds)

(Get-ClusterResource “Resource Name”).DeadlockTimeout = 300000

To change pending timeout follow below steps:

In the Failover Cluster Management snap-in, if the cluster you want to configure is not displayed, in the console tree, right-click Failover Cluster Management, click Manage a Cluster, and select or specify the cluster you want.
If the console tree is collapsed, expand the tree under the cluster that you want to configure.
Expand Services and Applications.
Click the clustered service or application that you want to configure the pending timeout for.
In the center pane, right-click the resource for the service or application, click Properties, and then click the Policies tab.
Under Pending timeout, specify the length of time, in minutes and seconds, that the resource can take to change states between Online and Offline before the Cluster service puts the resource in the Failed state.

The default timeout value is 3 minutes. Change as per requirement.


Drop and restore PostgresSQL database without losing settings

If you are concerned about your logfile and the postgresql.conf, just do a file system backup of them before running initdb.

If your database did suffer from a harddisk corrupted then it is probably advisable to run initdb to make sure everything (including the system tables) are re-created properly.

As the configuration files are just plain text (as are the logfiles) you can simply copy the backup over into the new data directory after running initdb.

(Note: I don't know what pg_dropcluster does. It is not a standard Postgres tool)


selecting all row of max value of a column

Your query will get the max value of I_COMDTY_ID with ANY combination of the other fields from the rows that satisfies your WHERE condition.

A better way to do that:

SELECT
    t3.C_COMDTY_SHORT_DESC,
    t3.C_HS_CODE,
    t3.I_COMDTY_ID,
    I_COMDTY_ID
FROM
    ADM_COMMODITY_MASTER t3
WHERE
    t3.C_HS_CODE = '2203.00.00'
ORDER BY
    I_COMDTY_ID DESC
LIMIT 1;


Build replication from backup not publication source

If I understand you question correctly you could use a schema compare tool to generate a script that would be used to insert the extra SPs and triggers into the new datawarehouse DB.

Redgate have a tool like this in their DBA bundle and I think data and schema compare are now functionalities of visual studio.


script insert SQL Server 2005 from SQL Server 2014 Express

You can change the client date format when you do the INSERT.

SET DATEFORMAT ymd

Run this as the first line in the script that inserts the data, and this will tell the client to interpret the date correctly as YEAR-MONTH-DATE as it is formatted in the script.


Safe and secure implementation of logon trigger in SQL Server 2014 Express edition?

You can try to do this...
CREATE TRIGGER CSharpOnly ON ALL SERVER FOR LOGON
AS
BEGIN
     IF APP_NAME() NOT IN (N'Your C# app_name() according to SQL Server')
     BEGIN
          RAISERROR('Please use sea sharp.', 16, 1);
          ROLLBACK;
     END
END

...but, since application name and other connection properties are easily spoofed, it is very easy for your users to bypass them - they can just tell SSMS, for example, that it should present itself as the same APP_NAME(). So, it all depends on your definition of "in a reliable manner."
When things go south
In order to fix a bad trigger, you can bypass logon triggers entirely by using the Dedicated Administrator Connection (DAC) to connect and modify / disable the trigger. You can also keep your initial connection established when you create / enable the trigger - existing connections won't go through the trigger again unless they get disconnected.


Installing SQL Server TDE on a Windows Cluster

Several versions of SQL Server do not let you upgrade a Standard CLUSTERED installation of SQL Server to Enterprise.  Are you sure you can do this in your environment?  To clarify: If you are already clustered, upgrading to enterprise is not supported in many scenarios.  Secondly, TDE is a enterprise edition option.  You're not going to get to failover and test it without being on enterprise, but maybe 2016 enabled it for STD edition, I haven't heard of that though.  Have you tested this solution yet with your team?

At the bottom of the MS supported upgrade scenario's it reads in the :

Supported Version and Edition Upgrades

"** Changing the edition of a SQL Server 2016 failover cluster is limited. The following scenarios are not supported for SQL Server 2016 failover clusters:
SQL Server 2016 Enterprise to SQL Server 2016 Developer, Standard, or Enterprise Evaluation.
SQL Server 2016 Developer to SQL Server 2016 Standard or Enterprise Evaluation.
SQL Server 2016 Standard to SQL Server 2016 Enterprise Evaluation.
SQL Server 2016 Enterprise Evaluation to SQL Server 2016 Standard."

The documentation makes it look like you can until you get to the end and read that tiny snippet.

As far as TDE goes, it depends on how you do it.  In my implementation, the master key is encrypted by the Windows DPAPl (I think that's the right acryonym) service, but then the generated key is in the Master DB that gets moved between nodes as you stated on the database instance level.


practical effect of not validating FKs in Postgres?

It is conceivable that the planner could use valid foreign key constraints to optimize query plans.  I don't think that it does that today, though. I know people had been working on such things, but I don't think anything has been accepted into released code, or is likely to be in the near future.


1) Add an index will slow inserts.

2) It will speed up inserts

3) Inserts don't use indexes

4) It will speed up writes but w=0 risk consistency and j=0 risk durability

5) You can't insert data to secondaries, insert/update/delete applies only to primary

If you care for insert speed you should use hash based sharding which evenly distribute writes among shards and increase write throughput. (http://docs.mongodb.org/manual/tutorial/shard-collection-with-a-hashed-shard-key/)


You are trying to connect to the database as your user but that use doesn't exists. The best way to execute administrative commands on PostgreSQL is to issue them as the "postgres" user. Change user with:

sudo su -  postgres

The user postgres exists and has administrative privileges (it is a superuser) so can connect the database and create new users (just do a createuser after the sudo).


Why is database ownership by groups not allowed? Which user should own a database?

Honestly, sa or a disabled SQL Server account with absolutely minimum permissions is the best choice.  As to why?  Well, I could write out three or four paragraphs on that, or I can share my favorite article on What account should own the databases and why. A very thorough explanation covering all the bases. Highly recommended.
Here's a core excerpt: (Edited to add this 1/9/2020)

All right, well how about sa? It can already do anything it wants so it’s not like we are granting additional permissions, it can’t quit or be fired, and no one should be using it anyway. Heck, you probably have sa disabled, right? You know what? That’s actually perfectly reasonable! In fact sa is a really common id to use as the database owner.
The only risk is if the database becomes TRUSTWORTHY. Then you can create stored procedures that can act as sa. It’s honestly not a huge risk even then since you have to have impersonate permissions to create a procedure with the EXECUTE AS clause. Meaning that in order to create that stored procedure you have been explicitly granted the ability to impersonate a member of the db_owner role (please, please, please don’t do this) or be a member of db_owner yourself. That does mean that someone with db_owner can become a sysadmin but again, fairly minor risk. TRUSTWORTHY isn’t that common, and hopefully, if you are using it you understand the risks and are avoiding dangerous permissions.
But, a risk is a risk. So if we are feeling particularly paranoid and/or have a very sensitive system the easiest solution is to create a SQL login (with a stupidly complex password since no one is ever going to log in as it), disable it, and then use that as the owner.


Subscription Initialization throws Error "The option "INLINE=ON" is not valid for this function" which is incorrect

Refer this article The only solution worked for me was drop and recreate the UDF after upgrading to SQL 2019.
The inline_Type column from Sys.sql_modules will be 1 after your upgrade to SQL 2019.
After you drop and recreate the UDF, the Inline_type will be 0 and the replication initialization works fine.


How to create user that can access to "master" database

Recommendations

User objects shouldn't be stored in the master database as explained in the following articles.

Tables in the Master Database (BrentOzar.com)

  The master database is a system database – it’s not really for you to poke around in and store stuff.  We usually design different backup & recovery processes for the master database as opposed to user databases.

The master database is run in Simple Recovery model and hence cannot be restored to a certain point-in-time if the database becomes corrupt.

Tables in the SQL Server “master” database, will they cause problems? (ServerFault.com)

  I would recommend doing the move to a dedicated database. Here are a couple of reasons why:

  If you ever want to migrate this database to a different instance of SQL Server you'll need to do this anyway. You can't migrate master.
  The master database is not meant for user data. It's meant to be the central point for data and code needed to keep the instance functional. Adding things in master muddies the waters there.
  Adding things to master requires changing security in master unnecessarily.

master Database (Microsoft TechNet)

  When you work with the master database, consider the following recommendations:

  Always have a current backup of the master database available.
  Back up the master database as soon as possible after the following operations:

  Creating, modifying, or dropping any database
  Changing server or database configuration values
  Modifying or adding logon accounts

  Do not create user objects in master. Otherwise, master must be backed up more frequently.
  Do not set the TRUSTWORTHY option to ON for the master database.

Instead

Consider creating a user database (e.g My_App) for your application.

CREATE DATABASE [My_App]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'My_App', FILENAME = N'E:\DATA\My_App.mdf' , SIZE = 131072KB , FILEGROWTH = 16384KB )
 LOG ON
( NAME = N'My_App_log', FILENAME = N'F:\LOGS\My_App_log.ldf' , SIZE = 65536KB , FILEGROWTH = 16384KB )
GO

Then create an administrative user (e.g. My_App_DBO) for your database:

USE [master]
GO
CREATE LOGIN [My_App_DBO] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [My_App]
GO
CREATE USER [My_App_DBO] FOR LOGIN [My_App_DBO]
GO
USE [My_App]
GO
ALTER USER [My_App_DBO] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [My_App]
GO
ALTER ROLE [db_owner] ADD MEMBER [My_App_DBO]
GO

This user will have the db_owner role on your new database and can perform DDL and DML statements against your new database.

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database.

Now you can create your table in your new My_App database with the SQL Server Login My_App_DBO.

When you have finished configuring your database consider adding a new user that doesn't have the db_owner role, to reduce the liability of unwanted DDL statements.

Create a new user (e.g. My_App_USER) with only db_datareader and db_datawriter permissions.

USE [master]
GO
CREATE LOGIN [My_App_USER] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [My_App]
GO
CREATE USER [My_App_USER] FOR LOGIN [My_App_USER]
GO
USE [My_App]
GO
ALTER USER [My_App_USER] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [My_App]
GO
ALTER ROLE [db_datareader] ADD MEMBER [My_App_USER]
GO
USE [My_App]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [My_App_USER]
GO

A SQL Server login with the db_datawriter role can:

  Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

A SQL Server login with the db_datareader role can:

  Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.

And then go from there.

Instead of using SQL Server logins you can assign Windows Domain users as SQL Logins.
You could also vary the access levels on each user object you create for each individual login.

This is just a small starting point and should give you a general idea on how to keep things out of the master database.

And always ensure you are on your database when running scripts to create objects:

USE My_App

Good luck on your journey.


Postgres list constraints that use a stored procedure

It looks like that there is already a view called information_schema.check_constraint_routine_usage that provide this information.

The view originally only shows routines owned by the current role but you can find the original query by typing \d+ information_schema.check_constraint_routine_usage and tweak it.


As the documentation states:

  There are two commonly used internal WAL functions: LogInsert and
  LogFlush. LogInsert is used to place a new record into the WAL buffers
  in shared memory. If there is no space for the new record, LogInsert
  will have to write (move to kernel cache) a few filled WAL buffers.
  This is undesirable because LogInsert is used on every database low
  level modification (for example, row insertion) at a time when an
  exclusive lock is held on affected data pages, so the operation needs
  to be as fast as possible. What is worse, writing WAL buffers might
  also force the creation of a new log segment, which takes even more
  time. Normally, WAL buffers should be written and flushed by a
  LogFlush request, which is made, for the most part, at transaction
  commit time to ensure that transaction records are flushed to
  permanent storage. On systems with high log output, LogFlush requests
  might not occur often enough to prevent LogInsert from having to do
  writes. On such systems one should increase the number of WAL buffers
  by modifying the configuration parameter wal_buffers.

Which means for me that in such a situation the system will need more time to complete the COPY because of the writes.


Reduce query execution time in frequently updated tables

With 3GB of RAM, you should probably keep innodb_buffer_pool_size at no more than 1G.

The Query cache is of minimal use, and it gets slow when you increase its size.  Recommend no more than 32M for query_cache_size.

There is such a thing as "over-normalization"; please provide details.

What indexes do you have?  Do you understand when to use a 'composite' index?

Don't use memcached -- that's just putting a cache in front of a cache.

Replication may be useful if you have lots of users contending for MySQL.  It can speed up a query only if that is a lot of contention during the query.  I don't hear that is the issue.

Please provide the query.


When you create job using maintenance plan, a job is automatically created in SQL Server Agent under Jobs. So now go to SQL Server Agent, expand jobs, and look for your job. Right click on it and select Script job as... Drop and recreate to... New query editor window.

Edit:

You can export the maintenance plans as well. You need to connect to Integration Services of the instance. Expand the stored packages then MSDB then expand Maintenance plans. Right click on MP and select a export package. Then you can move the package to another SQL Server.


mysql high cpu usage (no have slow queries)

Somebody is trying to hack your server using bots (all these log entries are login attempts). To be honest I see the same atempts in my logs all the time. To check if this is the source of your server overload (I am not sure...) try to restrict network connections to mySQL: uncomment this line in your mysqld.cnf
bind-address    = 127.0.0.1

Then restart your mySQL and check the load.


Very large Primary Key index with few rows

A clustered index in MSSQL causes sequential data rows to be stored together in the data file, including when data in a row is updated.  You don't have to have one, but, unless you make it different, a primary key will be created with a clustered index.  As we've already said, really you're changing your entire table into the clustered index.  Basically a tree whose leaves are the data rows.  You can also have a clustered index which isn't the primary key.

A nonclustered index is a separate copy of specified table column data, organised in a different key order.  It includes the clustered index key, if there is a clustered index, to link back to the table row.  This makes it economical to use a small size clustered index key.  GUID isn't ideal, but there's worse.  Data can be fetched from a nonclustered index without touching the stored table at all.  Of course, when table data is updated, the server has the job of automatically updating the data in a nonclustered index as well.


How to automatically fix Orphaned Users with PowerShell and SQLCMD

The "(lots of dashes -----------------)" you talk about are a result of the output from sqlcmd.  Instead of using SELECT in your script, you likely want to use PRINT with a cursor.  SELECT will generate a resultset that sqlcmd will print to the screen along with header info.  The dashes are part of this header info.

The T-SQL won't work for logins/users that have spaces in their names, or other special characters such as dashes, etc.  To make it work correctly for those users/logins, you need to wrap the name in square brackets, using QUOTENAME().

Something like this:

$dbname = "test"
$InstanceTarget = "CP708-D377\MV2012"

$FixOrphansSQL=@"
SET NOCOUNT ON;
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT FixUsersCmd = 'ALTER USER ' + QUOTENAME(dp.name) + '
    WITH LOGIN = ' + QUOTENAME(sp.name) + ';'
FROM sys.database_principals dp
    INNER JOIN sys.server_principals sp ON dp.name = sp.name AND dp.sid <> sp.sid
    AND dp.type_desc = 'SQL_USER';
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
"@
$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname
echo $RepairOrphansSQL
if ($RepairOrphansSQL.Length -gt 0) {
    sqlcmd -S $InstanceTarget -Q $RepairOrphansSQL -d $dbname
}

The above code checks if $RepairOrphansSQL actually contains any commands before running it; if the variable is empty, sqlcmd is not called.


The benefits of ALGORITHM=INPLACE in ALTER TABLE queries

A 10-row alter will be so fast that the "Algorithm" won't make a noticeable difference.  You probably won't see a diff for 1000 rows.
Some history:
Originally ALTER had exactly one way of being performed under the hood.  This made the code easy, but the performance not as good as it could be.

Create a new table like the existing table
make the modifications to that empty table
INSERT ... SELECT ... to copy all the data over
Juggle table names and drop the old data.

Beginning with 5.6, there was a concerted effort to optimize specific cases -- mostly to avoid the full table copy.  This led to a variety of syntaxes and options.  Confusing.
Fortunately, if you ask for an algorithm that is not applicable to the action in question, it will spit at you.  Simply change the algorithm and try again.
I think, without proof, that ALTER TABLE will use the best Algorithm available for the use case in hand.  And the Algorithm options are there in case something goes wrong and you need to force a different algorithm.
So why have ALGORITHM?  I think it is like a lot of VARIABLES -- if something goes wrong, the end-user has the ability to "fix" the problem by turning off something.  This does come into play in various evaluation optimzations.  A visible case is FORCE INDEX.
Alas, the documentation of ALGORITHM is wimpy.


How can I keep the user permission on a table that is dropped and recreated?

When you drop and re-create the table, it becomes a new object. If the principal is granted access to that specific object, all security related to it will be dropped as well.

If you truncate the table or delete from it, the security will be preserved. You'll need to slightly adjust your bulk import process.

Alternatively, you could run the import with elevated permissions, and have that process execute a GRANT SELECT for that principal. But I would recommend against that.


Just JOIN on a subquery:

SELECT *
FROM MyTable T
INNER JOIN (SELECT OrderID, MAX(ProductID) as ProductID
            FROM MyTable
            GROUP BY OrderID) x
  ON x.orderid = t.orderid
  AND x.productid = t.productid


Copy all data to slave before mySQL replication connect

Q1. Yes, not only "should" you copy the data from the master, you have to.

Replication in MySQL relies on the two servers beginning with an identical set of data, after which, every query that modifies data on the master makes the same changes to the data on the slave.

There are two ways to do this, either by stopping the master completely (shutting down mysqld) and copying the data files... or using mysqldump, as explained in the documentation:

http://dev.mysql.com/doc/refman/5.6/en/replication-howto-existingdata.html

Q2.

  when disaster strikes on master, it switch to slave

Well, not exactly.  "It" (the master) or "it" (replication) doesn't do anything when there's a failure on the master.  You, or your application, must choose if and when to abandon a failed master and start writing to the slave, instead of writing to the master.  At this point, the slave becomes master and is the only server in the system, because the data on the old master is now stale and obsolete, and must be resynchronized.

  What kind of disaster could happen?

The fact that you ask this question suggests that you are not thinking of the kinds of disasters that replication actually protects you from: hard drive failure, memory corruption, cpu failure, cooling fan failure, power supply failure ... as well as utility power outage, fire, flood, earthquake (assuming the slave server is in a different data center) ... and a pretty-much endless list of the things that can go wrong in or around a server.

Your question suggests that you were thinking along the lines of things that might happen to your data in properly-functioning server... such accidental or malicious altering or deleting or rows, or dropping of tables or databases.  Replication doesn't protect against these things, because they'll replicate perfectly.

There was, however, a new feature introduced in MySQL 5.6, "Time Delayed Replication," which allows the slave to lag a fixed period of time behind the master, to give you time to recover from such a change to your data.  The slave still "discovers" the changes to the master in near real time, but does not actually apply the changes to its data until the configured amount of time has passed from the time they were originally logged on the master.

http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html


Mongodb update conditional set operator ($set only when it is insert )

It will work for conditional $set update operation

http://docs.mongodb.org/manual/reference/operator/update/setOnInsert/

db.products.update(
  { _id: 1 },
  {
    $set: { item: "apple" },
    $setOnInsert: { defaultQty: 100 }
  },
  {upsert: true }
)

Here setOnInsert will set defaultQty only when a new record is inserted.
Both $set and $setOnInsert can be used together.


Import multiple CSV fields into one MySQL field

The following LOAD DATA sentence loads the CSV file, ignoring the first line and inserting into the desired fields, but changing the format of the Date and Time columns into a proper datetime type:

mysql> LOAD DATA INFILE '/tmp/alarms.csv'
       INTO TABLE alarms
       COLUMNS TERMINATED BY ','
       IGNORE 1 LINES
       (@Date, @Time, ALARM_01, PT001, TEMP_AMB)
       SET DateTime = STR_TO_DATE(CONCAT(@Date, ' ', @Time), '%d/%m/%Y %H:%i:%s');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM alarms;
+----+---------------------+----------+--------+----------+
| id | DateTime            | ALARM_01 | PT001  | TEMP_AMB |
+----+---------------------+----------+--------+----------+
|  1 | 2014-08-14 14:23:21 |        0 | 130.00 |    20.00 |
|  2 | 2014-08-14 14:23:25 |      101 | 128.09 |    20.10 |
+----+---------------------+----------+--------+----------+
2 rows in set (0.00 sec)

Your fields and datatypes may vary slightly, with no effect on the import query.


How do I access a user's application username when that application connects to the DB?

Although I noted a potential duplicate question in my comments above, Microsoft has introduced new features to make the passing of (essentially "session") information from a client application to the database easier, with the introduction of new features in SQL Server 2016.

As noted before, CONTEXT_INFO was the solution prior to 2016 - but it is fraught with difficulties.

From 2016 onward, SESSION_CONTEXT has been introduced.

Rather than try and summarise this myself, there is an excellent article describing both methods, their shortcomings and providing SQL examples on their usage. I'm sure there's ample information there to work through a solution to this problem.


Percona MySQL 5.5.33 not reading my.cnf?

Ultimately the problem was SELinux on CentOS, and the answer was to disable SELinux! I couldn't find any errors in the SELinux log related to MySQL so this took quite some time to track down.


Postgres permission issues for new views

DEFAULT PRIVILEGES only apply to objects that the user creates. I didn't have DEFAULT PRIVILEGES setup for rormigration so users didn't get rights to objects created by rormigration.

I reviewed DEFAULT PRIVILEGES and then ran the missing grants while logged in as rormigration.

SELECT *
FROM pg_default_acl acl
JOIN pg_namespace ns ON ns.oid = acl.defaclnamespace;


Duplicate record inserted - why trigger is not preventing it?

If you suspect this to be as a result of 2 transactions occurring nearly simultaneously, then it would appear to perhaps be them overlapping and the first not having committed before the second queries the RPTs table.

This will be down to your Transaction Isolation Levels for the queries. Top validate this you could include SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in the Stored Procedure to force the second to wait until the first commits before operating.


How to ensure only a single row with a certain value

You can use a partial unique index:

create unique index on rollouts (device_id)
where is_active;


Using COUNT in triggers before insert into table

I suggest avoiding TRIGGERs.

BEGIN;
SELECT COUNT(*) ... FOR UPDATE;
if there is still room
    INSERT ... to add student to class
else
    give error
COMMIT;

That is, write application code perform the logic.  (TRIGGERs and FOREIGN KEYs have limited capabilities.)

Put it into a transaction to prevent another student from sneaking in while you are checking availability.  Note also the FOR UPDATE.

The above transaction could be put in a STORED PROCEDURE, but I suggest you do it in application code, rather than also taking on the potential challenges of SPs.


Results not returning rows where second parameter should match null

Put t3.store_number LIKE :store_number in your JOIN ON criteria rather than the WHERE criteria.

Being in WHERE means the the store_number of NULL won't match the criteria however being in a LEFT JOIN, t3.* are padded in the result when the JOIN criteria isn't met.


Mysql database replication (failover solution)

Facebook's DBA YOSHINORI MATSUNOBU has developed MHA :

  A primary objective of MHA is automating master failover and slave promotion within short (usually 10-30 seconds) downtime, without suffering from replication consistency problems, without spending money for lots of new servers, without performance penalty, without complexity (easy-to-install), and without changing existing deployments.

The project link : http://code.google.com/p/mysql-master-ha/

Max.


You can generate an SQL script with sqlplus and run that.

spool /tmp/get_the_ed.sql
select 'select ''' || owner || ''', ID from ' || owner
       || '.' || table_name || ' where jn_osuser = ''Ed Spencer'';'
  from all_tab_columns where column_name = 'JN_OSUSER';
spool off
start /tmp/get_the_ed

You need to replace "ID" by the primary key of those tables.  If that isn't always the same, you can go for rowid.


Diagnosing the error in a Postgresql to Redshift migration through psycopg2

This is because you are querying a postgresql replica that doesn't allow queries to run for very long.  Your only solution is to ask the database administrator to increase the query limit on that replica, or hit production instead.


Why is a Full Database Backup (as opposed to a level 0 backup) required in Oracle after switching to ARCHIVELOG mode?

You cannot use backups from before the switch to ARCHIVELOG mode to restore and recover the database to a point in time after the switch.

Looking at this quote it makes sense. If you enable archivelog on a database where it was not enabled, then any backup that you have before archive log was enabled can't be used to do a point in time recover, since you have no archive logs to rolll forward from the backup. You just have the backups and can restore to whatever warm backups that you have.

If you take a database that has archive log enabled and disable archive log, then going forward you can't do point in time recover. You can only restore to whatever backups that you have. But any warm backup that you have from when the database was in archive log can be restored and rolled as far forward as you have archive logs.

As far as I know both a warm full backup and incremental level 0 backup are full backups and you should be able to use either to do a full point in time restore provided that the databases were in archive log mode during the backup. Probably the biggest difference between a full backup and an incremental level 0 backup is that the incremental level 0 backup will update the block change tracking file, but I'm not sure that a full hot backup will do that.

But if you want to know for certain take a database that is not in archive log mode, put it in archive log mode, do an incremental level 0 backup, drop the database and restore it. You will know for certain then.


I can't select directly from a table I created

Found the answer! Took me less than I expected. The script that created the table quoted the table name, so the only way to find the table in a select statement was quoting the name.

Same problem posted here:

What exactly do quotation marks around the table name do? (Stack Overflow)


Help me understand and resolve MySQL deadlock - "lock mode AUTO-INC waiting"

The schema for n0_table looks like it is straight from Wordpress?  It has multiple inefficiencies.  Revamp it to make queries faster and either eliminate or decrease the frequency of the original problem.

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


Migrating Azure SQL database to Azure sql Managed instance

The only way to go with this to get the bacpac file for your Azure SQL Database. Then import this bacpac file into your Managed Instance. This can be achieved through :

SSMS
Export\import using sqlpackage.exe
Via portal which appears to be a bit slow as the Azure SQL Database Import/Export service provides a limited number of compute virtual machines (VMs) per region to process import and export operations
SSDT
bcp

This link explains each method : Export bacpac

Once done. Import the bacpac file into your managed instance using SSMS,sqlpackage or portal.

This will walk you through the process : Import bacpac


Finding fuctional dependencies and candidate keys

Content looks like a free-form text field - that generally makes it a horrible choice for a primary key column. Hotel_ID, Author, and Date are probably reasonable (especially if Date is "date of stay at hotel", rather than "date of posting of review").

I would generally expect that an updated review would replace the original. If you want to retain older version, I would include both "date of stay" and "date of posting" (and both could be part of the key); this would allow the same person to have a review for each stay (as ratings for a stay last month could remain valid, even if a review of a later stay had different ratings).

It would be likely that you would want a Hotel table, and an Author table (to hold information about those entities that wouldn't change from review to review (hotel info, author's account info (assuming some sort of login process).

NOTE: In practice, I would probably want to treat Hotel_ID, Author, and the two date fields as a functional key (must be unique), but use an auto-incrementing integer as the actual, literal primary key (saves space on indexing and in any tables that might need to reference it, easier to use in queries, etc.)


Calculate Difference in Overlapping Time in PostgreSQL / SSRS

Working with this setup:

CREATE TABLE tbl (
   tbl_id     serial PRIMARY KEY
,  user_id    text NOT NULL
,  start_time timestamp NOT NULL  -- if values could be NULL ...
,  end_time   timestamp NOT NULL  -- ... it would be more complicated
);

Total duration without redundancy

Your numbers indicate you just want the total duration without counting same ranges of time multiple times. Not trivial, but can be done with pure SQL rather efficiently:

SELECT sum(duration) AS total_duration_without_redundancy
FROM  (
   SELECT island, max(end_time) - min(start_time) AS duration
   FROM  (
      SELECT start_time, end_time
           , count(gap) OVER (ORDER BY rn) AS island
      FROM  (
         SELECT start_time, end_time
              , (start_time > max(end_time) OVER w) OR NULL AS gap
              , row_number() OVER w AS rn
         FROM   tbl
         -- WHERE  user_id = 'User1'  -- just for a given user?
         WINDOW w AS (ORDER BY start_time, end_time DESC  -- longest range 1st
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
         ) sub1
      ) sub2
   GROUP  BY island
   ) sub3;

Explanation:

Beginning at subquery sub1, first order rows by start_time. While being at it, I actually use ORDER BY start_time, end_time DESC hoping to help performance a bit. But that's unimportant.
The custom frame definition (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) allows to compute the maximum end_time for the previous row with the window aggregate function max() with . If the current start_time is later, we have a gap.
In sub2 count the gaps to form islands. (All rows with the same count of gaps live on the same island (can be aggregated to one gap-less range).
The rest is simple, in sub3 compute the size of each island (duration), and get the total in the outer SELECT.

Related:

Check if contiguous date interval exists

Only ranges without overlap

non-overlapping time entries like you wrote, can be interpreted in various ways. Excluding all ranges that overlap (consider only ranges of time that are covered exactly once) is hard to solve with pure SQL.

The special difficulty is that the "difference" of two ranges can produce two disjoint ranges if the one is a sub-range of the other, like the Postgres manual states for range functions:

  The union and difference operators will fail if the resulting range
  would need to contain two disjoint sub-ranges, as such a range cannot
  be represented.

I would therefore solve the problem procedurally. Iterate though rows ordered by start_time And compare each range to the remaining hopeful range from the last round.

This plpgsql function sums all non-overlapping time ranges (or parts of ranges):

CREATE OR REPLACE FUNCTION f_sum_no_overlap(OUT nol interval) AS
$func$
DECLARE
   a1 timestamp;  -- start and ...
   z1 timestamp;  -- ... end of current row
   a0 timestamp;  -- remaining candidate range ...
   z0 timestamp;  -- ... from previous row
   _id int;
BEGIN
nol := '0:0';     -- init!

FOR _id, a1, z1 IN
   SELECT tbl_id, start_time, end_time
   FROM   tbl
-- WHERE  user_id = 'User1'  -- or just for a given user?
   ORDER  BY start_time, end_time DESC  -- better take longest range first
LOOP
   IF    a1 >= z0 THEN               -- range starts later
   --       a0------z0
   --                  a1--z1
      nol := nol + (z0 - a0);
      a0 := a1;
      z0 := z1;

   ELSIF a1 >= a0 AND z1 >= z0 THEN  -- range overlaps
   --       a0------z0
   --            a1--z1
      nol := nol + (a1 - a0);
      a0 := z0;
      z0 := z1;

   ELSIF a1 >= a0 THEN               -- AND z1 < z0 - follows logically: range included
   --       a0-------z0
   --        a1--z1
      nol := nol + (a1 - a0);
      a0 := z1;  -- z0 unchaged

   ELSIF z1 <= a0 THEN               -- ignore redundant rest
   --       a0-------z0
   -- a1--z1
      -- do nothing

   ELSIF z1 <= z0 THEN               -- shrink remaining rest
   --       a0-------z0
   --     a1--z1
      a0 := z1;  -- z0 unchaged
   ELSIF z1 > z0 THEN                -- new remaining rest
   --       a0-------z0
   --     a1------------z1
      a0 := z0;
      z0 := z1; -- z0 unchaged

   ELSE                              -- only for 1st iteration: a0 & z0 are NULL
      a0 := a1;  z0 := z1;
   END IF;

   RAISE NOTICE '_id: %, nol: %, a0: %, z0:%', _id, nol, a0, z0;  -- to debug
END LOOP;

nol := nol + (z0 - a0);  -- rest from last row

END
$func$  LANGUAGE plpgsql;

SQL Fiddle
db<>fiddle here

Should be fast, too, because a single scan of the table (or index) is enough.

I added some basic ASCII art to visualize IF cases. That should help to understand.


Skipping VM backups for high workload SQL Server?

One option is to set the disks that contain the data and log files of the user databases to independent disks, so the VM snapshot backups will not include those disks.  As long as your database backup strategy is sound, you don't need to include the data and log files in the VM backup.  This may not be possible, of course, if you have applications and database files installed on the same disk.

One helpful thing here is to have your master, model, and msdb on one of the disks that is included in the VM backup, so that when the VM backup is restored you won't have to restore the system databases, which is a bit of a pain.  I scripted a process to move system databases--can be found at Script to move SQL Server System Database Files.

If this approach is used, restoring the server will take some work as you'll have to restore the VM backup, then create the disks and directories for the database files, then restore databases, etc.

A side benefit of this approach is that if you have really large volumes (multi-terrabyte), a VM snapshot of all of the disks will typically pause the server for several seconds, so this method will effectively eliminate that "stunning" effect.  If you are using clusters/availability groups, the pause can cause availability groups to go offline for a bit.

One reason why you don't want to stop backing up the VM is that if you build a new server or restore the OS from an old backup, it will require a lot of time to get all of the Windows Updates installed, and this could amount to a significant amount of downtime.  If you have a VM template (or whatever deployment method that is in use) that is kept updated this of course wouldn't be an issue.


Is it a good way to use different schemas for storing the data and provide data access?

With properly set privileges this can be a good solution.  You can define your stored procedures WITH SECURITY DEFINER, where the role with which you create them has the necessary access privileges to the tables.  Then you have an api_caller role which has only function execution rights for the procedures in the API schema.


SQL Server : create dynamically queries to select all related data in DB based on entry table and ID

It's not entirely clear what the wanted output is but based on the comments that you want a "lightweight db export" and that the solution needs to be dynamic and "to be able to apply it to different dbs and different schemata", I'll try to make an outline of a solution:

Find all FKs in the db in question.
Copy all tables to a new db - but without the FKs and no data.
Copy the "starting rows" from the db to the copy-db. It doesn't have to be 1 row from 1 table but any number of rows from any number of tables.
For each FK, create two statements (one for each direction of the FK) that finds rows in the db that are related to the rows in the copy-db and copies them (counting the copied rows) if they haven't been already copied. This is the dynamic part.
The normal direction of the FK constraints will be strictly needed (if you want a consistent database as a result) but the reverse direction can be decided by the user performing this procedure (see note below*).
Iterate over all these statements.
Repeat the iteration until it produces 0 copying (no rows copied at all, in any step).
If needed, add the FK constraints in the copy-db. This will be an extra check, that the solution worked and created a usable database.
Export the copy-db with a backup or any other method available.

*
Explanation:
  Lets say they choose a few rows from Orders. The FKs to Customers and Stores are necessary to be followed (and the related rows copied over). For OrderDetails you need the related Products. But the reverse FK routes are not all needed. You'll probably want to have for each customer all the Orders, for each order all the OrderDetails and for each product all the ProductDetails.
  But for Products, you'll probably not want all the related OrderDetails.
  Because if you do (follow all FKs in both directions), you'll start with a few or a single customer, find all their orders and the related details, then the products ordered, then all the other orders for these products, then all the other customers for these products, then all the other products that these customers ordered, etc. You'll end up, in many cases, with almost the entire database.


How do triggers affect last_insert_id()?

From mysql documentation

  The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:
    ....
    For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements do not see a changed value. (Before MySQL 5.0.12, the value is not restored and following statements do see a changed value.)

Thus whatever happens inside the trigger[s] on A does not affect value returned by  LAST_INSERT_ID() executed outside of trigger's body. Withing the trigger body LAST_INSERT_ID will reflect inserts you made to another table. To get last id for B you need to store the value of LAST_INSERT_ID() somewhere(new table, or a new column in A), for instance,

delimiter /

CREATE trigger biA before insert on A
for each row

begin
 insert into B(name) values('aaa');
 set new.b_id := (select last_insert_id());
end;
/


LEFT JOIN based on content of a field in matched rows

By moving c.contacttype='email' from WHERE to ON, the LEFT JOIN properties that if it doesn't match a criteria a NULL row appears behaviour occurs:

select u.firstname,u.lastname,u.title,c.contactdetails
from users u
left join contact c
  on u.id=c.userid and c.contacttype='email'


Expanding AlwaysOn Availability Groups with Replication Subscribers

Can I add multiple publishers to a subscriber(which is part of
  availability group as a primary server)?

Yes, this is the multiple publishers model and is supported.

  In which node can I configure distributer?

The distribution database is currently not supported for use in AlwaysOn Availability Groups. The only supported method for transparent distributor failover is AlwaysOn Failover Cluster Instances.

  How do I configure the distributer?

You can configure it through SSMS or T-SQL.


Creating additional indexes on a table with a columnstore clustered index

You can create additional nonclustered indexes on tables with clustered columnstore indexes, the following script shows this...

CREATE TABLE dbo.T2 (C1 INT NOT NULL, C2 INT, C3 INT);
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON dbo.T2;
GO
CREATE NONCLUSTERED INDEX IX ON dbo.T2 (C2);
GO

I have attempted the same thing using SSMS 16.5.3. It is possible that there is a bug in the version you are using (latest is at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms ).

Consider creating the script using TSQL rather than the GUI to see if you get the same result.


DateTime conversion fails when the datetime has seconds

Well, February doesn't have 30 days, so you can't naively just add 1 to the month number and expect to end up with a valid date. (Not to mention leap years, Dec-to-Jan wraparounds that should advance the year, etc.)
I think you'll find it much easier to convert to a date first, then use DATEADD. Then, if you like, convert back to a string format:
DECLARE @origstring VARCHAR(40) = '30/1/2020 20:34:16'
DECLARE @origdate DATETIME, @newdate DATETIME

SET @origdate = CONVERT(DATETIME, @origstring, 103)
SET @newdate = DATEADD(MONTH, 1, @origdate)

SELECT FORMAT(@newdate, N'MM/d/yyyy HH:mm:ss')

Your original string '30/1/2020 20:34:16 6 300' doesn't appear to be a valid date, the problem is the  6 300 at the end. If that's in your source data, just trim that off when you convert:
SET @origdate = CONVERT(DATETIME, LEFT(@origstring, 18), 103)


SQL query to list software by major versions

try this: I removed a few fields and changed the group by

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
-- remove these fields
--,swc.MajorVersion
--,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid

WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
--change the group by
--GROUP BY
--arp.DisplayName, arp.Publisher, swc.MajorVersion

-- to this
group by arp.DisplayName

order by
arp.DisplayName asc


There's a number of different ways to do this, but if you only need it to be a monthly period starting on a given date, I would use a variable for the day of the month to start on and just calculate a new column with that month. Something like:

DECLARE @DayStart TINYINT

WITH cteMonth AS
(
SELECT DateColumn,
CASE
WHEN @DayStart < DAY(DateColumn) THEN CASE WHEN MONTH(DateColumn) = 1 THEN CAST(YEAR(DateColumn) - 1 AS VARCHAR(4)) + '/12' ELSE CAST(YEAR(DateColumn) AS VARCHAR(4)) + '/' + CAST(MONTH(DateColumn) - 1 AS VARCHAR(2)) END
ELSE CAST(YEAR(DateColumn) AS VARCHAR(4)) + '/' + CAST(MONTH(DateColumn) AS VARCHAR(2))
END AS DateMonth
FROM SomeTable
)
SELECT DateMonth, [aggregate columns]
FROM cteMonth
GROUP BY DateMonth

Apologies for this being T-SQL syntax (I'm not a PL/SQL guy), but in case you need help on declaring variables:
http://www.postgresqltutorial.com/plpgsql-variables/


Considerations for performance comparison with a high fragmented heap?

Since the three table copies are brandnew, so there is no fragmentation in place. For a fair comparison I also re-builded all indexes of the original table.

A more realistic test would be to try recreate the fragmentation that would have resulted from the table being in each design from the start. This way you are comparing the result of the designs as they would look after real world use rather than after a fresh rebuild.

If your application keeps a full audit trail for that data then you could perhaps rebuild each copy by replying that audit. Otherwise you might need to make up some heuristic (for instance if the data includes a creation or last modified date inserting the rows into each copy in that order).

One thing to note when doing this is to do each insert individually rather than a block copy - when you insert or update many rows at once it is bright enough to bunch the index updates to reduce page splits which it can't do with the individual inserts that would result from real application access. The following will illustrate this (using the amount of space allocated as an indication of free page space fragmentation due to page splits caused by the randomness of UUID ordering):

SET NOCOUNT ON
CREATE DATABASE TestFragUUID
GO
USE TestFragUUID
CREATE TABLE IndividualInserts (ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, AnotherID UNIQUEIDENTIFIER UNIQUE)
CREATE TABLE SingleLargeInsert (ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, AnotherID UNIQUEIDENTIFIER UNIQUE)
GO
INSERT IndividualInserts SELECT NEWID(), NEWID()
GO 100000
WITH CTen AS (SELECT TOP 10 name FROM sys.objects)
INSERT SingleLargeInsert
SELECT NEWID(), NEWID()
FROM   CTen unit
CROSS JOIN cTen ten
CROSS JOIN cTen hun
CROSS JOIN cTen tho
CROSS JOIN cTen tth
GO
EXEC sp_spaceUsed 'IndividualInserts'
EXEC sp_spaceUsed 'SingleLargeInsert'
GO
USE master
DROP DATABASE TestFragUUID


How to test permissions on an empty database?

The question could use some clarification.  SQL Server security is the same whether or not the table is empty.  You have server roles, database roles, and explicit permissions.  You are asking 'how can I test execute permissions on a database that has no objects to execute'.

Well the answer is you have nothing to test if the DB is empty, that should be obvious. create objects then test it, and then remove the objects.  Test it on a dev box.  If you're worried that you'll forget to remove all objects, backup the DB, then do your tests, and restore it.  If you like, restore the DB as a different name, do all of your testing on there, then drop the newly created test DB.  I'm not sure what you're asking.

Final answer remains that you cannot test permissions on objects if you have no objects to test.

I will provide a script or two at the end to help you with these steps.

Step 1: Look for users with SA rights.

What you would look for first would be SA equivelent users.  Any SA user will have full access to every single object, and if they don't, they can grant it to themselves.

Step 2: Look for users with the DB_Owner role
Users granted the DB_Owner role will be able to perform any task in the database.  You could DENY them access to certain features but they can just GRANT themselves access to it again.

Step 3: Audit what users are in the database
You will want to look at the 'security' settings of each user in the database.  This could get very in depth as you can look for indvl permissions on each object and this would be required if you were taking over a database.  In your case, there are no objects and you get to create them yourself, thus we don't have any object level permissions to go through.  You will want to check what roles each user is in.  DataReader will be able to read anything in the DB unless explicitly blocked.  DataWriter will be able to write anything.  Execute role lets them execute sprocs.

Step 4: Continue to monitor after objects are created
Now comes the hard part.  What happens when you go live and a sysadmin grants themselves rights to the machine/db?  What if you have the option to have start up stored procs execute?  If you're on 2008R2 and below, the NTAUTHORITY user can always be used as a backdoor.  This is where real security auditing and security policies are needed but luckily it's out of scope of this question.

Check out:
http://www.mssqltips.com/sqlservertip/1881/sql-server-security-audit-report/

His script will cover almost everything you're looking for.

Also this one is very good too, but test it out first:
http://gallery.technet.microsoft.com/scriptcenter/SQL-SERVER-SECURITY-AUDIT-eb5b0fd5/view/Discussions

If you want to do some research on your own, search for "SQL Server Auditing" or "Object Level Auditing SQL Server".

Does this info help?  Do you have any follow up questions?

Follow up edit to address permissions under the current user context:

In that case you'll want to use this:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

USE DBName;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

The first one will tell you your permissions on the server, the 2nd one will tell you your permissions on the database.

Click here for more easy to read info.


Memory utilization at 98%, why not 100%?

Everything requires memory including the OS just like SQL Server. The Operating System can remove memory from SQL Server, unless you happen to have locked pages in memory permissions for SQL Server. However, even then the OS will always win to keep itself running, so if it needs memory it will force SQL Server to give it up.

As well, just because SQL Server pulled up to 98% memory does not necessarily mean it is using it all. You would have to dig into memory usage of SQL Server itself to know what it is doing with it all. It holds it until the OS request it back, forcefully or in a nice way.

I will also note, you should not have max memory set "well beyond the server's current memory" capacity. SQL Server thinking it can grab more memory than there is really is can cause performance issues. Max memory is just the buffer pool limit, this does not include other areas of SQL Server processes that grab memory. So it is best to keep the max memory lower so the OS and other processes for SQL Server have some.

To dig into into performance, use Perfmon, DVMs, your monitoring tool (if it includes SQL Server monitoring).

Specifying Max memory: I base it on the OS but you also have to consider what else is running on the server. Are you running other components of SQL Server (SSIS, SSAS, SSRS)? Do you have any agents for monitoring or security purposes? An example, base value for me with Window Server 2012 R2 is leaving the OS 6GB of RAM and everything else goes to SQL Server.

See Monitor memory usage in the product documentation.


removing specific Key and value from Hstore in postgres

Seems that you should use the = (equality) operator instead of @>(containment).

It will filter only the exact rows and you could use them in a delta statment.


Benefit of using WITH (TABLOCK, XLOCK) when inserting one line in a table?

Exclusive Access or Increased Performance

From what I've seen, this "could be" someone's attempt to make sure that absolutely no one else enters identical rows, that you're inserting, into the table.

Or if it's mass inserts, it can save time and have minimal logging.

https://drillchina.wordpress.com/2012/12/18/use-tablock-to-boost-your-insert-into-select-performance/


My suggestion about archiving:

Create archive_tablespace (if you want you can separate hardware on archive)
Create tables. For example we want to archive table posts.

create table  posts_all ( LIKE public.posts)  ;
create table  posts_archive () inherits  ( public.posts_all)  ;
alter table  public.posts  inherits ( public.posts_all ) ;

After that we will have 2 new tables: public.posts_all (with the same columns like in posts) to query all posts (archive and production) and public.posts_archive to query all archive posts. Public.posts will inherits from posts_all.
Inserts should go in a old way (to table public.posts) unless you will write triggers on posts_all to redirect inserts to posts table. If you have partitioning it will be more complicated. With working application and before old data migration you don't have to change anything in application code to work with this approach.
Create schema archive for logical separation. My suggestion will be to separate archive data by some time period (year or month) if possible (archive_2005).
Create archive tables in archive_year schema

create table archive_2005.posts (
  check(record_date >= '2005-01-01 00:00:00'::timestamp
    and record_date <  '2006-01-01 00:00:00'::timestamp)
) inherits (posts_archive) tablespace archive_tablesapce;

After that you will have new table posts in schema archive_2005 and postgresql planer will know that data there is only in designed time period. If you query by another time period postgresql will not search in this table.
Create functions/procedures/triggers to move data to archive tables.
Do archive once for a time period (year here) and vacuum old table or do it automatically by triggers (heavier on autovacuum). There are many advantages and disadvantages in both techniques.

If implemented:

Can query archive (select * from posts_archive), all (select * from posts_all) and production (select * from public.posts) data separately
Can do dump archive schemas separately and drop cascade on them in easy way.
pg_dump -s archive_2005 datase_name
drop schema archive_2005 cascade; --be careful because it removes all related tables
Old data separated physically by tablespace and logically by schema.
Quite complicated structure to manage archiving process
Can create different indexes on production and archive tables to optimize queries to both (smaller and specialized indexes = faster queries and less space required)
If you have partitioned tables (by year or month) archive process will be just to move whole table to archive_tablespace or just alter it to inherit from posts_archive (I didn't test this)
If you don't want to access old (archived) data you don't have to change anything in application.

This is general technique and you should adapt it to your needs.
Any suggestions to improve this?

Further reading: PostgreSQL inheritance, partitioning


MySQL does not have a datatype that will receive a local time and timezone.  You will need to do one of these:

Store both fields and worry about interpreting it later.
Use application code to convert the time to UTC based on the given TZ.  Then store it in a TIMESTAMP while having the system time set to UTC.

When you store 2019-03-31 01:00 into a DATETIME, 2019-03-31 01:00 is the only thing you will ever get from that column.

When you store something in a TIMESTAMP column, it is converted according to MySQL's tz setting, then stored as UTC.  When retrieving, it will be reconverted.  If nothing has changed, you will get the same value back.

Between 2am and 3am is tricky because that hour repeats once a year.  Beware.

If the flight leaves China at 2019-03-31 01:00 China time, but your system is set for Dublin time, neither DATETIME, nor TIMESTAMP will be useful.

Best to convert to/from UTC in your app, then store UTC in the database.


Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

It's the name of a schema.
In other words, a schema-qualified function name. Just like you can schema-qualify tables or views or types or even operators (you just don't have to, normally).

Also learn about the schema search_path.


How to change the default output format for boolean values

There is probably something that I don't understand about your question but given a table with a boolean attribute like:

create table T ( b boolean not null );

You can only insert True, False. Under what circumstances would you need a CASE expression?

Edit:

reading your comments above, I guess you are interested in a textual representation of t/f. Not sure if it fits your needs but you can cast it to text:

select b::text from T

If you want it to be TRUE/FALSE I guess you will have to:

select upper(b::text) from T

Perhaps a function would be cleaner?

CREATE FUNCTION showbool(boolean) RETURNS text
    AS $$ SELECT UPPER(CAST($1 AS text)) $$
    LANGUAGE SQL;

select showbool(b) from T


MySQL - A Count with INNER JOINS - that multiplies the result by 8

Perhaps you want something like this?

SELECT tu.*
FROM tests t
JOIN testusers tu ON (tu.TestNumber = t.TestNumber)
WHERE t.TestComplete = 0 AND  -- below is your tricky bit
      (ResignedTimestamp='1970-01-01 00:00:01' OR
       NOT EXISTS (SELECT 1 FROM testusers WHERE TestNumber = tu.TestNumber AND UserSlot = tu.UserSlot AND UserId <> tu.UserId))
ORDER BY tu.UserId


Option to tell Toad to never split a file which contains multiple objects?

In Toad 10.6, it's in View => Toad Options => Editor => Open/Save => Opening files: Never split files


Sql server service wont start after disabling TLS 1.0 and SSL 3.0 on windows

The issue still remains if TLS 1.0 and SSL 3.0 are disabled.  At the moment I don't see any way around this and maybe Microsoft needs to look into this for the future as TLS 1.0 is likely to be phased out over time.

The reason I had TLS 1.0 disabled was to mitigate the BEAST attack, as I found in some reading last night this was the wrong way to do this.  To properly disable the BEAST attack on a server one should elevate a specific RC4 cipher so it is the one used with TLS 1.0.  Unfortunately this raised another about the fact that the RC4 cipher is also vulnerable but that is another discussion.

I realize that I have not found an answer to the question. But my issue has been solve by keeping TLS 1.0 enabled in the registry.


Is it normal to take days to compile PostgreSQL from source code?

How long it takes to compile will depend on a big number of factors, mostly the hardware (or resources of your virtual machine) on which you're compiling, plus your OS and environment settings, plus what else is your computer doing while you compile.

To have a reference of times, you can check PostgreSQL BuildFarm.

For instance, for a certain system called gull, which runs Debian 8.3, the gull PostgreSQL BuildFarm for date 2017-07-06 shows a Total run time
01:05:48.

Your system might actually be an order of magnitude slower and take 12 hours. It seems a bit "overkill", but possible. Although I don't know the specifics of compiling PostgreSQL for Debian (specially, how parallel it can be made), I'm sure that more memory and more (and faster) cores will decrease compilation time. It is also very likely that, at this phase of development, some settings to ease debugging might be different from releases on production, and (maybe) take longer to compile.

As for packages already compiled, check

Search PostgreSQL at debian.org
Package postgresql-10 (10~beta1-1)


I cannot grasp the definition of serializable

Which "same result" do we have in the example? Which reordering can we have to produce the same result?

The opening quote is an incorrect statement of serializable isolation.

The definition of the serializable isolation level in the SQL-92 standard contains the following text (emphasis mine):

  A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

The example given is consistent with that definition.

The text above is taken from my article The Serializable Isolation Level


Whether you use a local index or a global index will have (practically) no impact on the amount of space required for the index.  If you are running out of space to create the index, you'll need to allocate additional disk space to the tablespace (or tablespace(s) if you create a local index where the index partitions are stored in different tablespaces).  If you are building an index that involves the column you are partitioning on, you would almost always want to create a local index-- there is no benefit to creating a global index in this case.

Assuming that the dob column stores dates of birth and that the time component of that column is always set to midnight, you can probably reduce the space required for the index by making the dob column the leading column of the index and by using index compression.  In your case, you probably want to specify COMPRESS 1 since you would only want to compress the dob column, not the person_id column assuming that person_id has many fewer repeated values.


This is documented in sys.sysprocesses (Transact-SQL):

You can find more information and possible actions to take in the duplicate question:

Dealing with Negative Session ID Denials in SQL Server
How It Works: Orphan DTC Transaction (Session/SPID = -2)
Orphaned MSDTC transactions

In some cases, after proper investigation, it may be appropriate to configure SQL Server to presume that any in-doubt distributed transactions have aborted, using sp_configure.


Query the query store DMVs for the query hash, then use that query hash in an Extended Events session to capture the pertinent details.

This should get you there:

SELECT qsq.query_hash
    , qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%some-pertinent-SQL%';

In the query above, I have LIKE '%some-pertinent-SQL%' - this limits the results of the query to match the target your looking for - you need to replace the some-pertinent-SQL with a unique piece of your target query.

Use the value from the query_hash column returned above in the Extended Events session definition:

CREATE EVENT SESSION [t] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name
    , sqlserver.client_hostname
    , sqlserver.nt_username
    , sqlserver.plan_handle
    , sqlserver.query_hash)
    WHERE ([sqlserver].[query_hash]=(0x00000000))
    )
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF);
GO

Replace the 0x00000000 with the actual query hash value.

Start the Extended Events session with:

ALTER EVENT SESSION [t] ON SERVER
STATE = START;

Right-click the Extended Events session in Object Explorer to see details about the client hostname, username, etc., for queries as they happen.

To test this, I executed the following code on my SQL Server 2016 test instance:

CREATE DATABASE QueryStoreTest
ON PRIMARY
(
      NAME = QueryStoreTest_primary
    , FILENAME = 'C:\temp\QueryStoreTest_primary.mdf'
    , SIZE = 100MB
    , FILEGROWTH = 100MB
    , MAXSIZE = 1000MB
)
LOG ON
(
      NAME = QueryStoreTest_log
    , FILENAME = 'C:\temp\QueryStoreTest_log.ldf'
    , SIZE = 100MB
    , FILEGROWTH = 100MB
    , MAXSIZE = 1000MB
);
GO
ALTER DATABASE QueryStoreTest
SET QUERY_STORE = ON;

Here, I'll create a couple of objects in the QueryStoreTest database, and a test query:

USE QueryStoreTest;
CREATE TABLE dbo.qst
(
    id int NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.qst2
(
    id int NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

SELECT *
/*  test query store query  */
FROM dbo.qst
    LEFT MERGE JOIN dbo.qst2 on qst.id = qst2.id;

Here, I'll get the query hash from the query store:

SELECT qsq.query_hash
    , qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%test query store query%';

Results:

╔════════════════════╦════════════════════════════════════════════════════════╗
║     query_hash     ║                     query_sql_text                     ║
╠════════════════════╬════════════════════════════════════════════════════════╣
║ 0x3A0100223AD74766 ║ SELECT *  /*  test query store query  */  FROM dbo.qst ║
║                    ║  LEFT MERGE JOIN dbo.qst2 on qst.id = qst2.id          ║
╚════════════════════╩════════════════════════════════════════════════════════╝

Now, I'll create the Extended Events Session:

CREATE EVENT SESSION [t] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (
          sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.nt_username
        , sqlserver.plan_handle
        , sqlserver.query_hash
    )
    WHERE ([sqlserver].[query_hash]=(0x3A0100223AD74766))
)
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

And start the session:

ALTER EVENT SESSION [t] ON SERVER
STATE = START;

Now, if I run the test query and look at the live data from the session, I see:

Cleanup:

USE master;
GO
ALTER EVENT SESSION [t] ON SERVER
STATE = STOP;
GO
DROP EVENT SESSION [t] ON SERVER;
GO
ALTER DATABASE QueryStoreTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE QueryStoreTest;


I did some more research and found a couple of useful articles with best practices regarding table partitioning and full-text searching on large tables. As I haven't received any answers to this question, I thought I'd post what I've found here for future reference.

I'm still reading those, but for what I've already read, I've found both of them useful and relevant to this particular situation:

SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned:

  The observations in this paper are based on tests run in the SQL
  Server Customer Lab for a customer who needed to scale up full-text
  search to a much greater potential volume. The paper describes the
  customer scenario, provides an overview of SQL Server 2005 full-text
  concepts that bear on the results, and offers lessons learned and
  recommendations for using full-text queries on large catalogs.

Partitioned Tables and Indexes in SQL Server 2005

  Summary: Table-based partitioning features in SQL Server 2005 provide
  flexibility and performance to simplify the creation and maintenance
  of partitioned tables. Trace the progression of capabilities from
  logically and manually partitioning tables to the latest partitioning
  features, and find out why, when, and how to design, implement, and
  maintain partitioned tables using SQL Server 2005. (41 printed pages)


Central stored procedure to execute in calling database context

One way would be to make a system procedure in master and then create a wrapper in your maintenance database. Note that this will only work for one database at a time.

First, in master:

USE [master];
GO
CREATE PROCEDURE dbo.sp_GetFragStats -- sp_prefix required
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(),
    TableName       = t.name,
    IndexName       = i.name,
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
    -- shouldn't s.partition_number be part of the output as well?
  FROM sys.tables AS t
  INNER JOIN sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID, i.index_id)
    AND t.name = COALESCE(@tableName, t.name)
  CROSS APPLY
    sys.dm_db_index_physical_stats(DB_ID(), t.[object_id],
      i.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  -- probably also want to filter on minimum page count too
  -- do you really care about a table that has 100 pages?
  ORDER BY
    DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO
-- needs to be marked as a system object:
EXEC sp_MS_MarkSystemObject N'dbo.sp_GetFragStats';
GO

Now, in your maintenance database, create a wrapper that uses dynamic SQL to set the context correctly:

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,      -- can't really be NULL, right?
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + ';
    EXEC dbo.sp_GetFragStats @tableName, @indexID, @partNumber, @Mode;';

  EXEC sp_executesql
    @sql,
    N'@tableName NVARCHAR(128),@indexID INT,@partNumber INT,@Mode NVARCHAR(20)',
    @tableName, @indexID, @partNumber, @Mode;
END
GO

(The reason the database name can't really be NULL is because you can't join to things like sys.objects and sys.indexes since they exist independently in each database. So perhaps have a different procedure if you want instance-wide information.)

Now you can call this for any other database, e.g.

EXEC YourMaintenanceDatabase.dbo.GetFragStats
  @DatabaseName = N'AdventureWorks2012',
  @TableName    = N'SalesOrderHeader';

And you can always create a synonym in each database so you don't even have to reference the name of the maintenance database:

USE SomeOtherDatabase;`enter code here`
GO
CREATE SYNONYM dbo.GetFragStats FOR YourMaintenanceDatabase.dbo.GetFragStats;

Another way would be to use dynamic SQL, however this, too, will only work for one database at a time:

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'SELECT
    DatabaseName    = @DatabaseName,
    TableName       = t.name,
    IndexName       = i.name,
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
  FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables AS t
  INNER JOIN ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID, i.index_id)
    AND t.name = COALESCE(@tableName, t.name)
  CROSS APPLY
    ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_physical_stats(
        DB_ID(@DatabaseName), t.[object_id], i.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  ORDER BY
    DatabaseName, TableName, IndexName, PercentFragment DESC;';

  EXEC sp_executesql @sql,
    N'@DatabaseName SYSNAME, @tableName NVARCHAR(128), @indexID INT,
      @partNumber INT, @Mode NVARCHAR(20)',
    @DatabaseName, @tableName, @indexID, @partNumber, @Mode;
END
GO

Yet another way would be to create a view (or table-valued function) to union the table and index names of all your databases, however you'd have to hard-code the database names into the view, and maintain them as you add/remove databases that you want to allow to be included in this query. This would, unlike the others, allow you to retrieve stats for multiple databases at once.

First, the view:

CREATE VIEW dbo.CertainTablesAndIndexes
AS
  SELECT
    db = N'AdventureWorks2012',
    t.[object_id],
    [table] = t.name,
    i.index_id,
    [index] = i.name
  FROM AdventureWorks2012.sys.tables AS t
  INNER JOIN AdventureWorks2012.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  UNION ALL

  SELECT
    db = N'database2',
    t.[object_id],
    [table] = t.name,
    i.index_id,
    [index] = i.name
  FROM database2.sys.tables AS t
  INNER JOIN database2.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  -- ... UNION ALL ...
  ;
GO

Then the procedure:

CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName NVARCHAR(128) = NULL,
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(s.database_id),
    TableName       = v.[table],
    IndexName       = v.[index],
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
  FROM dbo.CertainTablesAndIndexes AS v
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(v.db), v.[object_id], v.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
    AND v.index_id = COALESCE(@indexID, v.index_id)
    AND v.[table] = COALESCE(@tableName, v.[table])
    AND v.db = COALESCE(@DatabaseName, v.db)
  ORDER BY
    DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO


SQL Delete duplicate rows based on column

To remove duplicate rows, use a CTE and partition using the data you want to check for duplicates to assign a row_number against. Then remove any where the row_number > 1 (removing multiple duplicates)

    --REMOVE DUPLICATES
    WITH CTE AS
    (SELECT ROW_NUMBER() OVER
(PARTITION BY column_tocheck_duplication_against ORDER BY ( SELECT 0)) RN
    FROM TableName)
    DELETE FROM CTE WHERE RN > 1


Changing MASTER_CONNECT_RETRY - Anything pit falls to keep in mind?

The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by the --master-connect-retry option (or the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement) and reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400. A value of 0 means “infinite”; the slave attempts to connect forever.

Slave-net-timeout --> The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the CHANGE MASTER TO statement or --master-connect-retry option and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

So in conclusion you have to follow these steps in all slaves:

stop slave;
CHANGE MASTER TO MASTER_HOST = 'master_host',
MASTER_USER = 'user',
MASTER_PASSWORD = 'password',
MASTER_PORT = port,
MASTER_LOG_FILE = 'filename',
MASTER_CONNECT_RETRY=50;
start slave;


Use NVL().

 SELECT NVL(SUM(CANTITATE),0)
             FROM ANEXA2A_TABEL2_ROWS
            WHERE TIP_ACTIUNE = 'COLECTATE'
                  AND TIP_AMB = 'STICLA'
                  AND COD LIKE '%*';

That'll make the SUM() function return 0 if it would ordinarily return NULL.


How to check if list of values in one column match list of values in another column (aggregations)

For b) you can use intersection to find out what tuples that are in both resultsets, and then aggregate on top of that:

with rs1 (key, value) as ( values (1,1),(1,2),(1,3),(2,5),(2,7),(3,4))
   , rs2 (key, value) as ( values (1,1),(1,2),(1,3),(2,5),(2,7),(4,6))
select key, array_agg(value)
from (
    select key, value from rs1
    intersect
    select key, value from rs2
) t
group by key;

1   {3,2,1}
2   {7,5}

If you are dealing with bags instead of sets, you can use intersect all to preserve duplicates:

with rs1 (key, value) as ( values (1,1),(1,2),(1,1),(4,6))
   , rs2 (key, value) as ( values (1,1),(1,2),(1,1),(5,7))
select key, array_agg(value)
from (
    select key, value from rs1
    intersect all
    select key, value from rs2
) t
group by key;

1   {2,1,1}

a) does not really make sense, since there is no order to take into concideration. We can create one by adding an ordering number n for each key

with rs1 (key, n, value) as ( values (1,1,1),(1,2,2),(1,3,3),(2,1,5),(2,2,7),(3,1,4))
   , rs2 (key, n, value) as ( values (1,1,1),(1,3,2),(1,2,3),(2,2,5),(2,1,7),(4,1,6))
select key, array_agg(value)
from (
    select key, n, value from rs1
    intersect
    select key, n, value from rs2
) t
group by key;

1   {1}

Another possible interpreation is that n is a total ordering (i.e. not within each key). The same solution can be used to deal with that.


Database mirroring is more of a high availability solution, not a disaster recovery one. What makes you think synchronous commit will lead to high latency in your environment? Are the principal and mirror server geographically separate?

Asynchronous commit will lead to potential data loss in the event that you need to do a forced service failover, therefore you would need to ensure that your SLA guidelines are congruent with that. BOL reference on Force Service in a Database Mirroring Session:

  Forcing service may involve some data loss. Therefore, force service only if you are willing to risk losing some data in order to restore service to the database immediately.

And equally important, database mirroring is marked for deprecation:

  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

Is there a reason why you don't want to utilize AlwaysOn AG? See BOL for an overview of AlwaysOn Availability Groups.

AlwaysOn AG has all of the capabilities of database mirroring with many more advancements.


How can I track the last modified user of a file in an SQL Server 2012 FileTable

Turn on auditing at the file system level. The file system APIs should then capture this information to the Security Event log.


MySQL INSERTs waiting for UPDATEs (MyISAM)?

What is the lock level for a MyISAM table ? Table Level Locking
This is by design : (See http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html)
I wrote about before : Benefits of table level locking
You may want to take a shot at INSERT DELAYED. Note what page 410 bulletpoint 5 of "MySQL 5.0 Certification Study Guide" says on INSERT DELAYED:

DELAYED may be used with INSERT (and REPLACE). The server buffers the rows in memory and inserts them when the table is not being used. Delayed inserts increase efficiency because they are done in batches rather than individually. While inserting the rows, the server checks periodically to see whether other requests to use the table have arrived. If so, the server suspends insertion of delayed rows until the table becomes free again. Using DELAYED allows the client to proceed immediately after issuing the INSERT statementrather than waiting until it completes.

There are risks to using INSERT DELAYED. Page 411 says

If you use DELAYED, keep the following points in mind:

Delayed rows tends to held for a longer time on a very busy server than on a lightly loaded one.
If a crash occurs while the server is buffering delayed rows in memory, those rows are lost.

Looking over the whole section of the book, HIGH_PRIORITY is only applied to SELECT queries.
For more information, "MySQL 5.0 Certification Study Guide" pages 408-412 Section 29.2.
CAVEAT : When it comes to MySQL Replication, INSERT DELAYED executed on a Master is treated as INSERT on a Slave. If a master crashes, missing rows are logged in the binary logs of the Master. It would be very possible that the missing rows on the Master replicated successfully to the Slave.
UPDATE 2012-08-20 11:18 EDT
Please note what page 410 bulletpoint 3 of "MySQL 5.0 Certification Study Guide" says on LOW_PRIORITY:

The LOW_PRIORITY modifier may be applied to statements that update tables (INSERT, DELETE, REPLACE, or UPDATE). A low-priority write request waits not only until all current readers have finished, but for any pending read requests that arrive while the write request itself is waiting. That is, it waits until there are no pending read requests at all. It is therefore possible for a low-priority write request never to be performed, if read requests keep arriving while the write request is waiting.

Given this description, low-priority UPDATEs definitely take a backseat to INSERTs that have normal priority. Notwithstanding, when a SELECT occurs on a MyISAM table, all write requests take a backseat regardless of priority. If the read traffic is high enough, the precedence of INSERT over UPDATE LOW_PRIOIRTY would be negligible, nominal at best.
UPDATE 2012-08-20 11:36 EDT
If the read traffic is hindering your INSERT over UPDATE LOW_PRIOIRTY situation, I would have to recommend switching your MyISAM table to InnoDB. In fact, InnoDB can be tuned for performing heavy INSERTs. Please see my posts about tuning InnoDB for this purpose:

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?
Jan 18, 2012 : Insert-heavy InnoDB table won't use all my CPU
Jul 04, 2012 : Capabilities of InnoDB INSERT Performance
Aug 17, 2012 : When to switch from MyISAM to InnoDB?


databasename_indx.ndf file not referenced but can't delete

The information about files has somehow got out of synch between the master database and the CallAccounting database.

So I suppose there are two possibilities.

The file information was erroneously removed from the system tables in the user database.
The file row should have been removed from the master database but for some reason wasn't (or maybe master was restored from backup at some point which brought back in information about a previously deleted file).

The second one seems much more likely as I'm pretty sure that in the first case you would be encountering a bunch of other errors when queries tried to access data in that file.

First run DBCC CHECKDB and see if that tells you anything useful.

If it doesn't you could use DBCC IND to dump out information about all relevant objects to check that nothing appears to be using that file.

SET NOCOUNT ON;

CREATE TABLE #DBCCIND   (
     PageFID         TINYINT,
     PagePID         INT,
     IAMFID          TINYINT,
     IAMPID          INT,
     ObjectID        INT,
     IndexID         TINYINT,
     PageType        TINYINT,
     IndexLevel      TINYINT,
     NextPageFID     TINYINT,
     NextPagePID     INT,
     PrevPageFID     TINYINT,
     PrevPagePID     INT
  );

DECLARE @id varchar(10)

DECLARE @C1 AS CURSOR;
SET @C1 = CURSOR FAST_FORWARD FOR SELECT DISTINCT id
FROM   sysindexes
OPEN @C1;
FETCH NEXT FROM @C1 INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #DBCCIND
    EXEC('DBCC IND (0, ' + @id + ', -1) WITH NO_INFOMSGS')
  FETCH NEXT FROM @C1 INTO @id ;
END

SELECT IAMFID AS FileId
FROM #DBCCIND
UNION
SELECT NextPageFID
FROM #DBCCIND
UNION
SELECT PrevPageFID
FROM #DBCCIND

DROP TABLE #DBCCIND

If you do see FileID of 3 returned then something is definitely using it.

If you don't I'd probably try attaching (copies of) just the mdf and ldf to another instance and test to see it all works correctly (with CHECKDB and selecting all rows from all tables).

If all of this works without error then it is possibly OK to backup everything then go ahead and delete that row referencing the file from master..sysaltfiles (will need ad hoc updates to system catalogs enabled).

I, of course, accept no liability if this all goes horribly wrong however!


SELECT query on unique number field returns no rows

The problem was solved when I changed the MegaHerz field's data type in the golang struct definition from a float64 to a decimal.Decimal.

type Frequency struct {
    ID          uint
    MegaHerz    decimal.Decimal `gorm:"unique;not null;type:numeric(7,3)"`
    Packets     []Packet
}

You do however need to include a third party library for the decimal.Decimal fixed point type.

import "github.com/shopspring/decimal"


How to know DB size using backup file without restoring it

Yes, there is, though you got to use RESTORE command anyway. Instead of RESTORE DATABASE, use RESTORE FILELISTONLY to get a detailed view of files in the backup. The Size column tells the file size in bytes.

RESTORE FILELISTONLY FROM DISK = N'v:\MyBackup.bak'


Wal segment still exists on master, but logs on slave and master say it's been removed

It is as dezso alluded to in the comment.
The reason you get the error is because the WAL has already been archived.

You can configure how many segments to store on the master with the wal_keep_segments configuration. You should set it to at least the amount of WAL's created during the base backup (and some extra as buffer).

Since the standby doesn't connect to the replication slot until it is ready to use it (which is after the base backup is finished), it means that the slot has not been activated yet and therefore is not storing the old WAL's.


Selecting Records In SQL Server From Joined Table Where Timestamp Occurs Between Begin/End Range

You spelled out your solution:

  "I wish to join to Table2 to retreive Table2.UserStatus and
  Table2.UserStatusSubgroup where Table1.EventTimestamp occurs between
  Table2.StatusBeginTimestamp and Table2.StatusEndTimestamp"

    SELECT T1.*, T2.UserStatus, T2.UserStatusSubgroup
    FROM @Table1 AS T1
        INNER JOIN -- or LEFT OUTER JOIN to retain the rows with null userid
        @Table2 AS T2
        ON T1.UserID = T2.UserID
    WHERE /*(T1.UserID IS NULL) OR -- to retain rows with null userid  */
          T1.EventTimestamp BETWEEN T2.StatusBeginTimestamp AND T2.StatusEndTimestamp