Where did new database 'BackupLocDb_###" come from?

The database is created when you use the GUI to add databases to the secondary servers, and select the option Full database and log backup, you may safely delete it.

  In the Specify a shared network location accessible by all replicas: field, specify a backup share to which all of the server instance that host replicas have read-write access. For more information, see Prerequisites, earlier in this topic. In the validation step, the wizard will perform a test to make sure the provided network location is valid, the test will create a database on the primary replica named "BackupLocDb_" followed by a Guid and perform backup to the provided network location, then restore it on the secondary replicas. It is safe to delete this database along with its backup history and backup file in case the wizard failed to delete them. Source


Explain output difference between this inner and natural join

Oracle database does not understand ANSI join syntax internally (except FULL OUTER JOIN), it rewrites such queries to its own, old join syntax. If you enable optimizer trace, for example:

alter session set events '10053 trace name context forever, level 1';

And run the queries, in the generated trace file, you can view the final form of the query that is executed.

select id, planet from t1 ta natural join t1 tb

is transformed into (BP is my schema):

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TB"."ID" "ID","TB"."PLANET" "PLANET" FROM "BP"."T1" "TA","BP"."T1" "TB"
WHERE "TA"."PLANET"="TB"."PLANET" AND "TA"."ID"="TB"."ID"

And

select id, planet from t1 natural join t1

is transformed into:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."PLANET" "PLANET" FROM "BP"."T1" "T1","BP"."T1" "T1"
WHERE "T1"."PLANET"="T1"."PLANET" AND "T1"."ID"="T1"."ID"

And this is the problem, "T1"."PLANET"="T1"."PLANET" AND "T1"."ID"="T1"."ID".

But don't ask me why, it is just one of those "unexplainable" things in Oracle.

I have seen a case, where the transformation resulted in a similarly ambigiuous final query, and there was an actual error message in the trace file, still, the database returned wrong results and no error to the client.


Restrict update on certain columns. Only allow stored procedure to update those columns

SQL Server allows column-level permissions. Just for example:

GRANT UPDATE ON dbo.Person (FirstName, LastName) TO SampleRole;
DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole;


Enabling Fragmentation Manager in SQL Sentry

It depends on which elements of the Fragmentation Management component you enable. There are three different levels of capability that can be enabled;

Collect Index usage stats & Buffer utilization capture
Collect Index Fragmentation details
Enable Index defragmentation capability

As Erik said, if you are collecting fragmentation data then that will have an overhead because if the way that the sys.dm_db_index_physical_stats() DMF works.

Rather than go into more details on the config options and other capabilities in more depth here. Please feel free to open a support ticket with us by emailing Support@SentryOne.com or contact me directly on Jmartin@SentryOne.com.

John Q. Martin
Product Manager
SentryOne


Record scan speed, column orientated databases and SSDs

Don't know enough about SSDs to comment, but I have studied columnar stores and how traditional disk technology affect/are affected by this model.

It's perfectly true that records in "OldSQL" (Google Michael Stonebraker and OldSQL) are not adjacent - this is entirely by design - the RDBMS hands placement of files over to the OS - hence fragmentation. (I'm guessing here, but I don't see why SSDs wouldn't suffer (maybe to a lesser degree) from the same effects as traditional HDDs).

What with DELETEs and subsequent INSERTs, your data for a given table could be scattered all over a disk. Columnar stores are meant to mitigate this problem - BUT by imposing restrictions on updating &c. There are complex ways in which they can retain bits of space for reorganisations, but it gets tricky fairly quickly.

The argument is that if you are summing over, say, sales, it would easier to trip along the disk and pick up the million (or whatever) sales in one pass rather than going to each record and picking out the sale value and adding. (Again, I can't see why a straight scan wouldn't be more efficient on SSDs - no idea of benchmarks in this area).

This also makes compression easier, since if datatypes are the same, more efficient algorithms can be used as opposed to compressing records with varying datatypes - which in turn makes the data smaller and quicker to read.

Take a look at Infinidb (now defunct) and Infobright (Community Edition). Vertica is a columnar store - kicked off by Stonebraker and sold subsequently to HP.

It's an interesting concept for the OLAP world and one which IMHO has validity in that space. I'm interested for the performance of statistical analysis which often requires an OLAP approach.


You can use SUM or COUNT functions to do this, with a case expression:

select
   TenNV
   ,SUM(count_x) as count_x
   ,SUM(count_L) as count_l
from
(
select
  TenNV
  ,Case when MaLC = 'x' then 1 else 0 end as count_x
  ,Case when MaLC = 'L' then 1 else 0 end as count_L
from vDemo
) A
group by TenNV

or with COUNT:

select
   TenNV
   ,COUNT(count_x) as count_x
   ,COUNT(count_L) as count_l
from
(
select
  TenNV
  ,Case when MaLC = 'x' then MaLC else NULL end as count_x
  ,Case when MaLC = 'L' then MaLC else NULL end as count_L
from vDemo
) A
group by TenNV

output of this:

TenNV   count_x count_l
Cam     5       0
Ngo     17      0

dbfiddle here


MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

I have written some posts in the StackExchnage

Tuning MySQL for InnoDB and MyISAM
How to keep InnoDB Diskspace under control
Another Viewpoint on MySQL Diskspace Managament
Viewpoint on InnoDB Optimization
InnoDB Fine Tuning

Please read these for the guidance you need.
Now, for more pressing issues: You mentioned that you have 400MB of data, 1GB with indexes. That kind of scares me that your indexes are 50% bigger than the data. However, since all your data is InnoDB and you are satisfied with the current query performance, your settings are more than adequate, espscially the 16384MB of innodb_buffer_pool_size. That's 16GB. You are all set there. But wait !!! Your innodb_log_file_size is 128M ? Way too small given the 16GB buffer pool. You should resize the ib_logfile files (set the innodb_log_file_size to 2047M).
You might be experiencing load on a per-thread basis. Try setting your connection buffers (join_buffer_size ,sort_buffer_size, read_buffer_size, read_rnd_buffer_size)
From Me : Why does MySQL say I'm out of memory?
From @DTest : How do you calculate mysql max_connections variable?
Give it a Try !!!


Change Location of VALIDATE RECOVERY FILES work area

Oracle Support closed bug 14226856 as "not a bug" and said it will be fixed in 12c.


MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?

Let's compare them
PARTITION SIZE
If you have the following:

100 million rows in a table
BTREE indexing
Each Page in the BTREE holds 1024 keys

What would the metrics look like?
Since LOG(100000000)/LOG(2) = 26.575424759099, a BTREE index with 1024 keys per page treenode would have a tree height of only 3 (CEILING(LOG(100000000)/LOG(1024))). With only three pages nodes, a binary search for the needed key in each accessed treenode would result in a pruning and isolating of about 30 keys.
NUMBER OF PARTITIONS
If you have the following:

100 million rows in a table
BTREE indexing
Each Page in the BTREE holds 1024 keys
You create 1024 parititions

The numbers would be slightly different.
Each partition should have about 97656 rows. What would the metrics become now?
Since LOG(97656)/LOG(2) = 16.575421065795, a BTREE index with 1024 keys per page treenode would have a tree height of only 2 (CEILING(LOG(97656)/LOG(1024))). With only two pages nodes, a binary search for the needed key in each accessed treenode would result in a pruning and isolating of about 20 keys.
CONCLUSION
Spreading out the keys just removes one tree level but essentially creates 1024 indexes. The queries won't know the difference. The search time would probably be nominal at best in favor of partitions. However, make sure all the data is active. Otheriwse, you may be hitting just a few partitions, while other partitions with rarely-accessed data just takes up space and are never accessed frequently enough to justify the partitioning. You may have different performance metrics to worry about that are more blatent (such as internal defragmentation in XFS, ext3 vs ext4, etc.) You also need to worry about which storage engine you are using because:

InnoDB indexing would be a little messier in comparison to MyISAM because of having to manage a clustered index
InnoDB does double writing of data in ibdata1 as well as the current log file (ib_logfile0 or ib_logfile1)


Whether or not to create separate tables for different product types?

If this were my design decision, I would probably go with more of an 'Option C' (modified option a).
First, why not 'Option B':
For one thing, I like the clarity that each product has it's own table affords. If it's all one big table with a field to determine the type, the relation isn't as clear.
For another, the indexing strategy would always require that type field to be listed. Since it's only 4 types, the index cardinality is extremely low (SELECT * FROM product_table WHERE type='X' is basically doing a full table scan anyway)
Option C

Create a parent table that holds only the columns that all types share
Create each product type as it's own table with their individual columns, with one extra: A link to the parent table
Create each 'link' table: Product_Option, Model_option, etc with links to the respective keys.
For those with reciprocal links (MODEL_OPTION, OPTION_MODEL) go ahead and create those tables as well. This will add clarity in your joins for anyone looking at it.

The downside is the complexity of making sure to avoid orphans when things are updated/deleted, and initially designing the queries that use these tables.


Update specific records of table based on subquery's output (sql server)

From your examples, the statement below should do what you want to do.  It's not fast as it involves casting ID to a string and manipulating it, but I think this is the only thing that will work for you.

UPDATE TEST
SET ID = CAST(LEFT(CAST(ID AS VARCHAR(20)), LEN(CAST(ID AS VARCHAR(20)) - 1)) + '1' + RIGHT(CAST(ID AS VARCHAR(20)), 1) AS INT)

If ID is a string to begin with then you can remove the CAST(ID AS VARCHAR(20)) statement and replace it with just ID.

UPDATE TEST
SET ID = CAST(LEFT(ID, LEN(ID) - 1)) + '1' + RIGHT(ID, 1) AS INT)


Will subquery be executed first here with full scan?

PLAN

Your understanding of the Plan seems correct.

Common Misunderstanding

People often forget that SQL is a declarative language, not a procedural language.

You should never assume sub-queries run in a certain order.


Sql job fails when connection string is updated with port number,where as it succeeds without port number

You should not include the instance name when specifying the port, so instead of

Source : sat\sql2012,5000
destination:tak3\war2012,55750

You should use

Source : sat,5000
destination:tak3,55750

** Note: following queries about this, I can find no documentation to support my answer. Consider it to be my personal experience only.

There is some detailed information on connection string attributes at the following URL,
Using Connection String Keywords With SQL Native Client
which defines the syntax for server addressing as:

[protocol:]Address[,port |\pipe\pipename] for an "Address"

or

Server= [protocol:]Server[,port] for a "Server"


How to organize database for translations, performance?

Generally, it's better practice to encapsulate the data as you did in your second approach.

This is because...

  When a fully normalized database structure is extended to allow it to accommodate new types of data, the pre-existing aspects of the database structure can remain largely or entirely unchanged. As a result, applications interacting with the database are minimally affected.

By following this tenant of normalizing, it's far easier to add, update, or remove rows to a table than it is to alter the table by adding/removing columns. Languages may change over time, but the table ideally should stay the same and not need to be in flux to accommodate changes in the data.


This can be caused by the asm_diskgroups parameter. On older releases (like 11g), this was a typical problem.  This parameter was automatically updated, except when a diskgroup was dropped, then its name was not removed from the parameter values.

You can check this if you have access to MOS:

Dropped Diskgroup Still Existing in ASM spfile (Doc ID 2241177.1)

BUG:19146721 - DROP DISKGROUP DOES NOT REMOVE THE ASM_DISKGROUP PARAMETER IN RAC

This parameter should not be set manually (unless you fix the incorrect value) and should be left empty. Below is only for demonstration.

Set asm_diskgroups for this ASM instance to include a diskgroup that does not exist:

SQL> show parameter diskgroups

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string
SQL> show spparameter diskgroups

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        asm_diskgroups                string
SQL> alter system set asm_diskgroups='DOES_NOT_EXIST' scope=spfile sid='+ASM4';

System altered.

Stop + start GI on this node:

# /u01/app/19.0.0/grid/bin/crsctl stop crs
# /u01/app/19.0.0/grid/bin/crsctl start crs

Check the parameter again after ASM was started:

SQL> show parameter diskgroups

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DOES_NOT_EXIST
SQL> show spparameter diskgroups

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
+ASM4    asm_diskgroups                string      DOES_NOT_EXIST

And this is from the alert log of this ASM instance:

2020-04-28T18:59:58.994866+02:00
SUCCESS: diskgroup DATA was mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DOES_NOT_EXIST" cannot be mounted
ORA-15040: diskgroup is incomplete


Here is a little something that you can use to find your cycles.

declare @T table
(
  ID int,
  Parent_ID int
)

insert into @T values
(1, 3),(2, 1),(3, 2),    -- This is a cycle
(4, 4),                  -- This is a cycle
(5, null),(6, 5),(7, 6)  -- This is not a cycle

;with C as
(
  select T.ID,
         T.Parent_ID,
         cast(',' + cast(ID as varchar(10)) + ',' as varchar(max)) as Path,
         0 Cycle
  from @T as T
  union all
  select T.ID,
         T.Parent_ID,
         C.Path + cast(T.ID as varchar(10)) + ',',
         case when C.Path like '%,'+cast(T.ID as varchar(10))+',%'
           then 1
           else 0
         end
  from @T as T
    inner join C
      on T.Parent_ID = C.ID
  where C.Cycle = 0
)
select *
from C
where Cycle = 1

Result:

ID          Parent_ID   Path       Cycle
----------- ----------- ---------- -----------
4           4           ,4,4,      1
3           2           ,3,1,2,3,  1
2           1           ,2,3,1,2,  1
1           3           ,1,2,3,1,  1


DR - Moving databases from Active/Active cluster to Standalone server with LUN sync

I'd stay away from the scripting and detach/attach methods. I've done two migrations in the past nine months and was convinced that a better method would be to backup and restore the databases. That way you have a last-known good configuration point-in-time restore.

So here's my thought process.

Restart the source instance in single-user mode
Backup your databases WITH COPY_ONLY, COMPRESSION
Stop the old instance. Take it offline. Pull the network cables, if you must.
Restart the new instance in single-user mode
Restore master
Change the location of tempdb (see below)
Restore model and msdb
Restart the instance.
Restore your user databases.

Please note that you will need to do some cleanup on the instance names since you're moving from what I assume will be a named instance from your Multi-Instance Cluster to what I assume will be the default instance on a dedicated piece of hardware.

I may have misunderstood what you meant by "replicated." Do you mean that you're going to provision LUNs of the same size (you've already mentioned letters) on your SAN that you attach to new hardware? In this case, you're not describing a DR scenario at all. You're simply describing the provisioning of hardware for your new SQL instance. Disaster recovery would be how you plan to recover from the failure of these instances.

Naturally you know that you're moving away from a High Availability solution. I hope you're not relying on your SAN to do both HA and DR.


This simple mysql query is taking 2-3 seconds

Lots of simplification and speedup:
SELECT e.*
    FROM ( SELECT DISTINCT event_id
             FROM events_tags
             WHERE tag_id = 617 ) AS et
    JOIN events  AS e  ON e.id = et.event_id
ORDER BY ???  -- else get random 50??
LIMIT 50;

events_tags smells like a many:many mapping table.  It can possibly be improved by following these tips.


Worth Splitting MySQL Server Into 2 MySQL Servers To Increase Performance

Your RDS is coping with your write load but if you want more scalability, separating the read and write load is a good idea: they have different work patterns and probably have conflicting performance tuning needs.

Once you have decided to split then you are free to choose a different product to handle the incoming raw data for the daily write load.  Why not choose a storage medium that is more optimised for writes?  Consider sending the daily the writes to a DynamoDB dynamodb table.
As your cron jobs are already written then you know how you will query the raw data so this can help you structure your DynamoDB table.  You can autoscale dynamoDB to cope with changing or bursty loads.

You can also create a TTL on the raw data so that as time passes and the summary data is more relevant the raw data is deleted, or even send the older raw data to S3 for cheaper storage once it is no longer 'hot'.


Failed to start mongo.service on ubuntu 16.04 after update from 4.0 to 4.2

Interesting.. It should not be possible...  From the source:
    cpp_name: oldOplogDeleteFromPoint
￼                type: timestamp
￼                optional: true # This field only exists on 3.4 upgrade
￼                description: "The timestamp of the first oplog entry in a batch when we are writing
￼                              oplog entries to the oplog after which the oplog may be inconsistent.
￼                              This field only exists on 3.4 upgrade."

And this problem only exists on unclean upgrade from <=3.4 version. It is removed from source code at version 3.8 forward.
Yes, I know, this answer doesn't solve the problem, but... It looks like that your upgrade process was not "by the book", sorry.


The mess was because I was in middle of a crash recovery. It is good now. No ghosts,no hackers.


Oracle Temp Table Space - Alias clean up ORA-01119

Resolved by removing Alias

ASMCMD> rmalias '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf'


How do I close log files that are open in OracleServiceORCL after inputting data into Oracle?

Well, its Oracle on Windows, such issues are not uncommon there.
Similar, but not exactly your issue:
Bug 30191227 - HANDLE LEAK ON EXTERNAL TABLE LOG FILE IN WINDOWS PLATFORM
No fix exists yet for it, not even in 19c. Suggested workaround:
WORKAROUND:
-----------
Manually release the file handle using Windows process explorer.

Of course database restart works as well (Oracle on Windows runs as a single process).
Process Explorer


Entity Framework 6: How can I add new column to existing table and at the same time update view (add column) in same migration file

I have tried your method and it's working on my computer. Have you checked the column name having wrong spelling?


How to Know if I have A Restricted or Full Access to Table

Basically, are there ways of knowing if I am viewing a full table or
  just a view derived from it?

The only thing you can see if you have restricted permissions is the type of object:

select type_desc
from sys.objects
where name = 'yourObject'

If you have only permissions on a subset of columns, you'll never figure it out using metadata (system tables/views):

  The visibility of metadata is limited to securables that a user either
  owns or on which the user has been granted some permission.

Related link:
Metadata Visibility Configuration


How to insert and or update a variable number of records and prevent concurrency issues

If your goal is to ensure that at the end of procedure dbo.data contains only records you inserted in the body of procedure, then you move DELETE to the very end (I assume you have primary key in data table, data_id:

--DELETE FROM dbo.data WHERE user = @user  -- do it at the end
DECLARE @captainsTable TABLE (captainId INT);
DECLARE @newData TABLE (data_id INT);
INSERT INTO @captainsTable (captainId)
SELECT DISTINCT TOP 3 item FROM [platform].[SimpleSplit](@captains, ',')

--store inserted rows into table
INSERT INTO dbo.data
( user ,
  player ,
  iscaptain
)
OUTPUT inserted.data_id INTO @newData
SELECT @user
     , item -- this is what is returned from the SimpleSplit
     , CASE WHEN [item] in (select captainId from @captainsTable) THEN 1
         ELSE 0
       END
  FROM [platform].[SimpleSplit](@captains, ',');
--delete all except recently inserted
DELETE b FROM dbo.data b WHERE user = @user AND NOT EXISTS
(SELECT NULL FROM @newData a WHERE a.data_id = b.data_id);

Also, it makes sense to begin transaction (or create a savepoint if the sp executed withing the context of existing transaction) in the beginning of procedure , and commit at the end.


Teradata : Disadvantage of Collect stats

The space consumption for statistics on Teradata is not significant enough to qualify as a disadvantage. For example, the statistics for a single column is retained in a 16KB VARBYTE column on DBC.TVFields

The rule of thumb has always been 10% change in the data which you have statistics collected or if they are stale. Unfortunately, stale has never really been clearly defined. Teradata 14.10 will introduce a more automated mechanism for maintaining statistics to help reduce the cost (CPU and IO) associated with the collecting stats using a homegrown maintenance schedule. This enhancement will be supported through Viewpoint.

Teradata 14 also introduced some changes with statistics that have to be taken into consideration from previous releases. Carrie Ballinger has done a good job of capturing these changes in her articles on the Teradata Developer Exchange found here and here.

Your stats maintenance schedule will be driven by the size of your environment, your ETL schedule, and manner in which your ETL maintains the target tables. We have multiple streams that maintain the same set of large target tables. As such we have moved the stats maintenance for these target tables to an external process instead of within each ETL stream.


What is the most effective way to manage a community table with one customizable flag?

No answers for months so here is the solutions I've come up with on my own.

I don't know if this is the best but if anyone else has this question it's worth a thought.

The common table can be stored in the common db as desired. Then each local database (which will always use a subset of the 130K possible values) can either store just the enabled values in their local copy of the table. Or store just the IDs of the enabled items. A disable would effective be a delete and an enable would be a create of either an ID or of the entire row.


There are a number of reasons not to use MongoDB as a pure key-value store, and there are some reasons to consider it. Mongo is optimized as a document store - it indexes all the fields in a document, and has rich primitives for JSON objects and hierarchies. You can use it as a key-value store, but the single-threaded nature means you won't be getting good performance out of your hardware. Storing simple blobs removes a number of the benefits of Mongo. Mongo has algorithms where it splits data chunks as you insert, which can create lag. Monogo's system for re-partitioning is cumbersome, as well. The benefit of a key-value system is it should be really simple and really fast, so you can scale up and keep server and management costs down.

Other systems are more tuned for key-value use. You mention Redis, one of the best key-value stores, but the repartitioning/clustering in Redis is still alpha-level, and there is a requirement of DRAM. Some people build their own shard layers and partitioning layers on Redis - this is very common among some of the larger Chinese social networks.

Cassandra is sometimes used as a key-value store. This isn't the best use of Cassandra, as Cassandra's "super column families" provide rich indexing. Cassandra isn't as fast as databases written in C like Redis and Mongo, but does have strong clustering capabilities.

One store you should strongly consider in this area is Aerospike. Aerospike has very flexible cluster management - adding a single node by just bringing it up - as well as support for both DRAM and SSD/Flash - and easy replication for HA. It's in use at very high levels of scale by advertising platform companies who need huge key value stores. Aerospike has a free version that supports node sizes to 200G.

CoucheBase (was MemBase) is another system to look at for key-value use. It provides some clustering primitives, and is focused more around in-memory use.


News Database Design for a Desktop Application

Based on how dynamic this sounds like I would suggest looking at a NoSQL data structure. I do not use them so much myself, but looking into something like MongoDB might be worth it. Good luck on your application!


Views do not store any data, they are no more than saved queries. Subsequently, the question "how many rows are there in the view" is equivalent to "how may rows does the query return", to answer which you by definition have to execute the query.

When querying a view, the optimizer will determine the query cost by looking up statistics of the view's underlying tables.


How to create a Windows user suitable for installing db2 server with extended security?

Problem solved by setting

 Security options/User Account Control: run all administrators in admin approval mode = Disabled

in Local Security Policy


Connecting to sql azure from ssms throw exception 'sys.configurations'

there can be couple of issues -

Firewall : You will need to add your local IP range to the allowed SQL Azure Firewall rules. Refer to : How to: Configure the Server-Level Firewall Settings (Windows Azure SQL Database)

You are using older/unsupported version of SSMS. If yes, then upgrade to the latest version (e.g SSMS 2008R2 - build 10.50.** Refer : SQL Azure July 2011 Service Release

You might be hitting - Invalid object name ‘sys.configurations’. (Microsoft SQL Server, Error: 208)


Facebook generates a Post record when I rename a group's name or just an ActivityItem?

While I can't comment how Facebook works internally, in terms of relational design, a normalized schema would have these two actions in separate tables.

This would be done because the other bits of information associated with each action would be very different.

Changing a group's name would probably involve inserting a history record with the old name, and updating the current record with the new name. Notifications could have other things associated, such as comments and likes.


From a DBA perspective, the key difference between OLAP and OLTP is the tuning method you apply to the queries. The read/write ratio doesn't really tell you anything useful.

I have a little "magic quadrant" that I use to illustrate the difference (in your case, consider BI/DW and ETL the same as OLAP):

Basically, if you must touch a lot of data to produce query results, you are OLAP. Converse if you can get away with touching very little data to produce return values, you are OLTP.

Obviously, if you have poor indexing strategies, then OLTP can end up looking like OLAP - simply because you end up doing table scans all the time. A good way to make that distinction is to look at how much data MUST be touched by the top 10 most important queries in the system - no matter how good your indexing strategy is.

Once you have settled on what the majority of your workload is, you can start applying workload specific tuning techniques. Here are some of the workload specific tricks you might consider (always tailor to your specific workload)

OLAP:

Prefer hash joins
Optimise table scan speed (typically by laying out data for sequential I/O)
Aggregate view are often better than indexes
Column store indexes are better than B-trees
Sequential I/O
Aggressively de-normalise
Use high MAXDOP
Optimise network for bandwidth

OLTP:

Prefer loop joins
Optimise for index seek (there should always be an indexed path to the data)
Rarely aggregate
B-tree indexes
Random I/O
Keep database in 3NF
Use MAXDOP 1
Optimise network for latency


Yes, this means it succeeded.  Normally (when the client settings are like that), you get back an echo of the command executed, like CLUSTER in this case.

In newer PostgreSQL versions, CLUSTER and VACUUM FULL do mostly the same (in fact, to my knowledge, they share the same code), the only difference being the ordering by the former.  What you see are typical output of the different VACUUM commands, describing that there were no stale rows in your table.

These can be either old versions of some existing rows, replaced by an UPDATE or deleted ones, which in both cases hadn't been removed by a previous VACUUM operation.  These are marked as 'removable', as opposed to some which may still be referenced by some open transactions.  When the transactions are closed, these will also be removable.


BigInt or Varchar for a large number I won't ever sort / calculate on

varchar adds overhead:

length of the string needs stored (extra 2 bytes IIRC in MySQL) per field and in the index
requires more processing for collation on comparison

Saying that, what does the Facebook API describe the type as? Use the "native" one of course


MySQL trigger/check on INSERT based on multiple factors (location, date and time)

With some help, the solution was extremely simple...
The trigger needs to validate the staff id as well. Therefore, the final trigger would look like:

begin
  if exists (select * from shift
             where shift_start <= new.shift_end
             and shift_end >= new.shift_start
             and staff_id = new.staff_id) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Shifts are overlapping';
  end if;
end


Controlling number of idle sessions in postgresql 11.5

You have to make a distinction between idle and idle in transaction. The database parameter idle_in_transaction_session_timeout limits the duration of the latter state, but there is nothing in PostgreSQL that will terminate idle connections. The reason is that they are not a problem, and in the case of connection pooling they are even something desirable.
What is odd about your question is that idle_in_transaction_session_timeout will never terminate a query that is active – being executed while the client waits for the result. The parameter for that is statement_timeout.
If you are running foul of max_connections because your application leaves connections open in idle state, you have a connection leak on the application side that you have to fix there.
If the connections are idle in transaction for a long time, that is also an application problem and should be fixed there. idle_in_transaction_session_timeout is just a brutal way to ascertain the database's health in the face of a buggy application that cannot be fixed.


SSAS -> New cube contains two fact tables. How do I relate these so I can use measures from both at the same time?

You're focusing on facts and forgetting about dimensions.

Years of service is a measurable attribute of a Driver, so, you should have a Driver dimension associated with the FactDeliveryDriver fact.

driverSid is a surrogate key for the Driver dimension, and not for the fact table. For the fact table it's a lookup column.

Also, deliveries are (probably) made by a Driver, so you should have the Driver dimension associated with the FactDeliveries fact as well.

There should also exist a Delivery dimension, that is associated with the FactDeliveries.

A simple schema should be something like:

Driver(sk_driver, name)
Delivery(sk_delivery, address, date_delivered)
FactDeliveryDriver(fk_driver, val_years_of_service)
FactDeliveries(fk_driver, fk_delivery, val_miles)

Since the Driver dimension is common to both fact tables, you can use the Driver dimension to relate measures from both of them.


SQL Server 2016 Availability Groups Named Pipes?

From the documentation on Listeners, Client Connectivity, Application Failover

  An availability group listener consists of a Domain Name System (DNS)
  listener name, listener port designation, and one or more IP
  addresses. Only the TCP protocol is supported by availability group
  listener. The DNS name of the listener must also be unique in the
  domain and in NetBIOS.

So only TCP/IP is supported.


Is EXECUTE AS OWNER in msdb a security risk?

In and of itself, no, you are probably not adding a security risk.  As long as no one but a sysadmin can alter the procedure and as long as the ONLY thing the procedure can do is start a job I don't see a problem.

The risks come in with the fact that MSDB is (and is supposed to be) marked as TRUSTWORTHY.  That means that by using EXECUTE AS OWNER you are able to impersonate the SERVER level permissions.  In this case since it's sa that means you could create a procedure to grant another user sysadmin.

That does mean you do want to limit permissions to MSDB though.


Performance and profiling on SELECT * FROM [table] ISNULL([column], '') = '' VS EXIST (SELECT * FROM [table] WHERE [condition])

Well, the IF (SELECT * FROM [table] WHERE ISNULL([column], '')) = '' test isn't really a working test, so it is possible that you made whatever code you are working with too generic when posting here. HOWEVER, in a very general sense, the IF EXISTS should nearly always be better because it is designed to stop processing its inner query upon the first row being returned to it. The first condition you posted does not have that built-in efficiency and would process all rows in the inner query. Of course, sometimes the Query Optimizer can re-write your code to be an IF EXISTS if it recognizes that your non-IF EXISTS query would logically be the same, but I am not sure if that would happen in this specific case. I believe I have seen that the Query Optimizer does rewrite IF ( (SELECT COUNT(*) FROM [table]) > 0 ) as an IF EXISTS.

Regarding how to test the efficiency of each, I would use a base query against the same table so that I could use the same column(s) for filtering (to hopefully make use of the same indexes that the real queries would use) and repeat that query, once for each of the two conditions you are testing for. Then, wrap each in SET STATISTICS TIME, IO [ON | OFF]; as follows:

SET STATISTICS IO, TIME ON;
IF (SELECT * FROM dbo.Table WHERE ISNULL()... )
BEGIN
  PRINT 'First';
END;
SET STATISTICS IO, TIME OFF;

PRINT '-----------';

SET STATISTICS IO, TIME ON;
IF (EXISTS(SELECT * FROM dbo.Table WHERE condition))
BEGIN
  PRINT 'Second';
END;
SET STATISTICS IO, TIME OFF;

Please read the MSDN page for the EXISTS operator for more info regarding how to use it in WHERE conditions.


Have a look at remarks on MS Docs about IS NULL:

  Remarks
  To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=).
  Comparison operators return UNKNOWN when either or both arguments are NULL.

And at SET ANSI_NULLS MS Docs:

  SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

Let me add some other records to your table:

INSERT INTO Test_Table
VALUES (NULL, 'A'),(NULL,'B'),(NULL,'C'),(NULL,'D'),(1,'E'),(2,'F');

Now, your query:

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id;

Returns:

id | pName
-: | :----
 1 | E
 2 | F

And the same for your second query:

Declare @ID INT = NULL

SET ANSI_NULLS OFF
SELECT CASE WHEN @ID > 0 THEN @ID ELSE id END AS Statements, *
FROM   Test_Table
WHERE  ID = CASE WHEN @ID > 0 THEN @ID ELSE ID END
GO

Statements | id | pName
---------: | -: | :----
         1 |  1 | E
         2 |  2 | F

Null indicates lack of value and you must explicitly check for NULL values using IS NULL:

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id IS NULL;

dbfiddle here


Trying to combine one row from one table with mulitple rows from another table but not sure if I'm doing it right?

It's not the only way to do it, but the other solutions won't perform any better.  You could create a function and use that for the values of your column, which might make your query easier to read.  But it won't perform better and depending on your scale, it may perform quite a bit worse.


How to choose the last recorded added without sorting

The generic way to access a table in order without a sort is a clustered index, which physically stores the rows in that order. However a table can only have one clustered index. From the documentation:

  PRIMARY KEY constraints create
  clustered indexes automatically if no
  clustered index already exists on the
  table and a nonclustered index is not
  specified when you create the PRIMARY
  KEY constraint.

So if this is your main access pattern for this table, then perhaps making the PK unique nonclustered and (DTime desc, UserID) a composite clustered index is the way to go. But be careful not to optimize for one access pattern at the expense of any others (e.g. is there another part of your app that wants fast lookups by RecordID, you would want to make sure that wasn't suboptimal now).


Insert rows in a table, via a row-level trigger, activated after insertion in that same table

To perform what you are looking for based on this example:

INSERT INTO synonym_bucket VALUES (23,37);

SUGGESTION #1

DROP TABLE IF EXISTS synonym_bucket;
CREATE TABLE synonym_bucket LIKE synonyms;
ALTER TABLE synonym_bucket ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER MakeTwoSynonyms
AFTER INSERT ON synonym_bucket
FOR EACH ROW
BEGIN
    IF new.w1 <> new.w2 THEN
        INSERT IGNORE INTO synonyms VALUES (new.w1, new.w2),(new.w2, new.w1);
    END IF;
END $$
DELIMITER ;

Using synonym_bucket takes up just 2K (table definition plus trigger)

Bottom Line: For a table's before and after trigger, you cannot perform additional DML to the same table. However, you can do DML from a trigger to another table. This is why I made a BLACKHOLE table. It takes up to 2K (table definition and the trigger code). You use it to write to a separate table. In this case, synonym_bucket does the double INSERT into synonyms.

SUGGESTION #2

Do the the double INSERT yourself

INSERT IGNORE INTO synonyms VALUES (23,37),(37,23);

No trigger required

GIVE IT A TRY !!!


Changing autogrow settings with database mirroring

I'm addressing the issue using Policy Based Management. I created a policy on the Data File facet that states all files must have @GrowthType = KB.

I also filed a connect item:
https://connect.microsoft.com/SQLServer/feedback/details/796354/autogrow-is-percent-flag-set-on-principal-does-not-persist-to-mirror

UPDATE: This is fixed in SQL Server 2008 R2 SP2 CU8 and SP1 CU13.


Why are Stored Procedure schema changes not being replicated when using ”initialize with backup”?

Probably not the issue, but double check this:

On the publication, go to properties
Go to Subscription Options
make sure Replicate schema changes is set to true.


You can discover all of these events that are in the current event log cycle using sp_readerrorlog:

EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'OFFLINE';

You can cycle through values of @p1 if you don't find it in the current event log. By default you should be able to read the current and prior 6 error log files, so use 0-6 as the arguments there to go back as far as possible (on my system I could not get 0/NULL to aggregate across all log files; YMMV).

Will return something like this:

LogDate        ProcessInfo Text
-------------  ----------- ---------------------------------------------------------
yyyy-mm-dd...  spid72      Setting database option OFFLINE to ON for database 'foo'.

There's a chance, of course, that the error log gets populated enough that the event(s) happened before the current set of error logs. In that case, you are out of luck. To keep a longer running history in the future, you can change the number of error logs that are kept. In Object Explorer, expand Management, right-click SQL Server Logs, and choose Configure. There you can change error log file recycling settings, including keeping up to the previous 99 files. Also see this answer.

Note that sp_readerrorlog is undocumented and unsupported, though many people have written about it. In the end, the error log files are just plain text files, so you could write your own PowerShell, CLR etc. that just parses the files and returns the same information. You can determine where the error log files are for this instance using:

SELECT SERVERPROPERTY('ErrorLogFileName');

The files will be named ERRORLOG, ERRORLOG.1, ERRORLOG.2, etc. You can go and open the files in a basic text editor to see the structure, though I would be cautious about opening the current file in use (ERRORLOG).


You are testing for NULL, yet you are not letting it be NULL -- either by having a DEFAULT or NOT NULL.

So change to

due DATETIME NULL,

And, for less confusion between "timestamps" and "datetimes", use

SET NEW.due = COALESCE(NEW.due, NOW() + INTERVAL 7 DAY);


Select every n-th row, don't pull the entire table

For test data in events

id  txtcol
--  ------
 1  event0
 2  event1
 4  event2
 5  event3
 6  event4
 8  event5
 9  event6

Retrieve the primary key values in ascending order

SELECT id FROM events ORDER BY id

Wrap that in a query to assign a zero-based rank

set @row:=-1;
SELECT @row:=@row+1 AS rownum, id
FROM
    (
        SELECT id FROM events ORDER BY id
    ) AS sorted

Wrap that in a query to select the first row and every third row thereafter

set @row:=-1;
SELECT id
FROM
    (
        SELECT @row:=@row+1 AS rownum, id
        FROM
            (
                SELECT id FROM events ORDER BY id
            ) AS sorted
    ) as ranked
WHERE rownum % 3 = 0

Finally, wrap that in a query to retrieve the other columns

set @row:=-1;
SELECT events.*
FROM
    events
    INNER JOIN
    (
        SELECT id
        FROM
            (
                SELECT @row:=@row+1 AS rownum, id
                FROM
                    (
                        SELECT id FROM events ORDER BY id
                    ) AS sorted
            ) as ranked
        WHERE rownum % 3 = 0
    ) AS subset
        ON subset.id = events.id

returning

id  txtcol
--  ------
 1  event0
 5  event3
 9  event6


This is the stuff of holy wars. There is no right answer. Further these are not "rules" of the sort that can be (easily) enforced in a CI tool chain. They are a style guide. Enforcement will be by code review and consensus, so make sure that all (or enough) of those affected agree, can live with the decision, and can justify them to the next generation and the unbelievers who will inevitably call foul.

Ask yourself why you want these rules. For me it is to impose a consistency which makes code easy to read. Very soon the eye gets used to reading a style and comprehension improves. When every query and table follows a unique style one starts from scratch on every reading. Further, rules remove the cognitive load of deciding how objects should be named. That decision's been made; done; move on to the important stuff. Right or wrong you never have to spend another second on this time-consuming low-value activity. And finally, a good style can uncover and make clear semantics within the structure without cruft.

By these measures I think your suggestions are fine.

There are a few suggestions I'd make, though. Forcing every table to have a numeric surrogate key may be unnecessary. I've touched on this here.

Extend the conventions to queries. There can be a lot of variation in select lists (leading or trailing commas?), joins (single line or clause per line?), sub queries, indentation. The team needs to think about this once and once only. A consistent layout can help make clear errors like missing join conditions.

I've seen the WHO column convention used many times. It's almost useless. What special significance does the original creator have over the most recent updater? Why is it important to know who updated the row a second ago but not who updated it three seconds ago?

If we saw a PERSONS table with columns FIRST_MAILING_ADDRESS and MOST_RECENT_MAILING_ADDRESS we, as data modelers, would have conniptions. The same applies to these WHO tables. Normalise them into their own table. If it's important to know how specific columns' values change over time these columns should be in the normalised table, too. Generally this pattern is know as temporal tables.


SQL Reporting Services - T2Embed.dll missing

SQL Server Reporting Services specifically is not supported on Windows Server Core 2008 R2 SP1 and higher.

You've probably stumbled upon one of the reasons why it's not supported. (I.e., it flat out doesn't work.)

Besides hacking around, which may or may not actually fix the issue, I think your only recourse is to install SSRS on a full version of Windows Server, and try again.


What structure is used for storing CONTEXT, CTXCAT and CTXRULE Oracle text indexes?

The documentation covers this thoroughly.

See this link for more detail.


Shutdown mode fast for stopping PostgresSQL standby server

A WAL receiver is neither an active client, nor an online backup. So a so-called "smart" shutdown executed on the master will not wait indefinitely for the WAL receiver to go away on its own.  Rather, it will actively disconnect the WAL receiver.  This is pretty much the last thing it does--it waits for the shutdown checkpoint to finish, and streams the WAL containing that checkpoint record over to the replica, and then disconnects it.

If you are concerned about "If the server is in hot standby, recovery and streaming replication will be terminated once all clients have disconnected", that is describing what happens when smart shutdown is initiated against the replica, not against the master.  The master is never "in hot standby", that is a status that only a replica can be in.


Recreating a Postgres table with all cascading relations transferred

There is no easy way to work around the problem. When you drop columns that other things are referencing, like views or foreign keys, you must manually inform all areas of the schema that have dependencies on that column.

There is no shortcut that I know of around this. No one really makes a habit of reordering tables. Most of the requests are for cosmetic reasons, however if you're really doing it for a valid reason (like table packing) then I wish you the best of luck.

It's been a feature request for a while, and it comes up frequently.


Get few ip postgresql slave from pg_stat_replication using non-superuser

You need a view not just function, e.g.

CREATE OR replace FUNCTION public.pg_stat_repl() returns setof
  pg_catalog.pg_stat_replication
AS
  $$begin
    RETURN query
      (
       SELECT *
       FROM pg_catalog.pg_stat_replication);
  end$$ LANGUAGE plpgsql security definer;

CREATE VIEW public.pg_stat_repl AS SELECT * FROM public.pg_stat_repl();

GRANT SELECT ON public.pg_stat_repl TO non-superuser;

Then you can as non-superuser

SELECT * FROM public.pg_stat_repl;


You can use the GROUP_CONCAT aggregate function to produce a comma-delimited list of values:

SET @facilities = (SELECT GROUP_CONCAT(facility_id) FROM facility);

The result will be just a string value, however. If you use it like this:

SELECT * FROM request WHERE facility_id IN (@facilities);

you cannot expect to get a match on every ID in the @facilities list. This is because @facilities is not expanded into a list and instead is treated as a single item of the IN list. The commas inside it will be seen as just characters inside a string value, not as syntactic delimiters.

In order to use @facilities the way you want, you will have to build and execute a dynamic query around the value of @facilities. It could be something like this:

@stmt = CONCAT('SELECT * FROM request WHERE facility_id IN (', @facilities, ')');
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This way the query will not contain a reference to @facilities – instead, it will have the contents of @facilities as its integral part, and the commas will thus be treated as part of the syntax.

Each query that you want to use the @facilities list in would have to be executed in the above manner. I think you will agree it is not very convenient. There are other considerations to keep in mind as well. When you use a dynamic query and concatenate the contents of a variable into the dynamic query as described, you may be open to SQL injection attacks. Also, with a large number of items in an IN list the performance may degrade.

I would suggest that instead of a variable you use a temporary table to store the IDs:

CREATE TEMPORARY TABLE tmp_facilities AS SELECT facility_id FROM facility;

A table is much more flexible in that you are not tied to just one syntactic structure. Although you could still use the temporary table in an IN predicate, as in your example:

SELECT
  *
FROM
  request
WHERE
  facility_id IN (
    SELECT facility_id FROM tmp_facility
  )
;

you could also use it in an EXISTS subquery:

SELECT
  *
FROM
  request AS r
WHERE
  EXISTS (
    SELECT * FROM tmp_facility AS f WHERE f.facility_id = r.facility_id
  )

;

or in a filtering join:

SELECT
  r.*
FROM
  request AS r
  INNER JOIN tmp_facilities AS f ON f.facility_id = r.facility_id
;

Having many options how to write your queries may be beneficial when you need to tune them for performance.


Is it ok to create additional column for helping other column to be a PK?

If I understood you correctly, you have two sorts of entities to manage:

Players (human beings that participate in your game)
Characters (fictional entities that the players use/manipulate in your game)

Once you look at your problem like that, you could model it easily like this:

players table, with a player_id as PK and information about your real users
characters table, with a character_id as PK and information about those game entities
A relation table to map characters to their owners.

(From a practical point of view, the relation table might be extraneous - you could store the owner as a column in the characters table .)

This allows you to use identity columns or sequences for both player and character IDs, and you don't have to worry about tricky triggers for inserts.


What are the performance benefits of segregating database objects into different tablespaces?

Minimal performance benefit, the only benefit in this regard is from reduced fragmentation. But Oracle and most filesystems handle this much better these days than in the past; Oracle handles this better with "automatic segment space management" and the Oracle "filesystem" ASM, though you don't need to use the latter.

It's more a manageability choice these days; personally I wouldn't bother with separate tablespaces, but I do often create one per application. This allows you to use a transportable tablespace export if you so desire.


Deadlock Detection | Should the node T4 point to T1 and T2?

T4 is only requesting B, which is held by T2. The line represents the dependency on the resource from a requestor to a holder; the graph does not model a "queue" of waiters, that's a different problem.
To help understand the graph draw the 4 processes T1-T4. Each time a resource is granted to a process, put the letter next to it. When you realize a process needs a resource held by another process draw a line from the waiter to the holder.
So in order:

T1 is granted A
T2 is granted B exclusively, T1 requests B. Draw a line from T1 to T2.
T3 is granted C
T2 requests an exclusive lock on C which is incompatible with the shared lock held by T3. Draw a line from T2 to T3.
T4 requests requests an exclusive lock on B which is held by T2. Draw a line from T4 to T2.

This is not a deadlock, btw.
If this helped you, please don't forget to mark this as solved; thank you.


SQL Server choose at random from giving data

You mean how to pick a random row from the data? This is one way to do it:

declare @data table (
  value int
)

insert into @data values (16), (17), (18), (22), (24)

select top 1 value from @data order by newid()


Check if postgresql database exists (case insensitive way)

You can use:

SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower('dbname');

... however, database names are case sensitive, so attempting to connect to the DB name won't actually work unless you match the case correctly. This won't use any indexes on dbname, but it's rather unlikely that you'd have enough databases for this to matter.

Folks don't realize that because PostgreSQL case-folds unquoted identifiers to lower-case, so most of the time it looks case-insensitive for clients. In fact, "DatabaseName" and "databasename" are different things.


Yes.  Create (database) MASTER KEY first, then  BACKUP, DROP, and CREATE the certificate (importing from earlier BACKUP), but this time WITHOUT a password.  This will encrypt the certificate's private key for the certificate using the database master key, which will automatically open the private key as needed.

Realize by doing this, you are exposing your data (via the private key for the cert) to anyone that has access to the database and sysadmin to the server.  In reality, all you have done is kept the data-at-rest encrypted on disk.  Anyone with access to your backups and the service master key can decrypt your data.

Make sure you follow best practices for keys (and certificates) in SQL and backup the database master key.

You can view the private key encryption type by looking at the pvt_key_encryption_type_desc column in sys.certificates database dmv.


Transferring large amount (84 million rows) of data efficiently

I would add that, however you decide to approach this, you'll need to batch these transactions. I've had very good luck with the linked article lately, and I appreciate the way it takes advantage of indexes as opposed to most batched solutions I see.

Even minimally logged, those are big transactions, and you could be spend a lot of time dealing with the ramifications of abnormal log growth (VLFs, truncating, right-sizing, etc.).

Thanks


What structure should be used to store data with multiple category identifiers?

If a particular place can belong to multiple categories, you need to implement many-to-many relationship between places and categories. Usually it's done with link table:

Category(category_id(PK), category_name);
Place(place_id (PK),place_name,phone,email);
Place_Category(place_id (FK to place),category_id(FK to category), PK(place_id,category_id));

You may also want to mark a particular category as a 'primary' category for a place. There are two approaches , 1st is to have primary_category_id in Place, 2nd is to add a field to Place_Category , say relation_type ...


Rearrange a string without using loop syntax

On SQL Server vNext you can take advantage of the TRANSLATE function to solve this. That version isn't generally available to the public yet but it might be useful one day or to inspire an answer from someone else.

SELECT
REPLACE(TRANSLATE(@ID, '0123456789', SPACE(10)), ' ', '')
+ REPLACE(TRANSLATE(@ID, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', SPACE(52)), ' ', '');

db fiddle link for your three examples in the question.

On SQL Server 2012 you could solve your problem by applying the REPLACE function 62 times but there may be a more elegant solution:

DECLARE @T table
(
    ID varchar(10) NOT NULL
);

INSERT @T
    (ID)
VALUES
    ('a1b2c3'),
    ('ac2b31'),
    ('db13a2');

SELECT
    -- Remove numerics
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        T.ID,
        '0', ''), '1', ''), '2', ''), '3', ''), '4', ''),
        '5', ''), '6', ''), '7', ''), '8', ''), '9', '')
    +
    -- Remove alphas
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        T.ID,
        'a', ''), 'b', ''), 'c', ''), 'd', ''), 'e', ''), 'f', ''), 'g', ''),
        'h', ''), 'i', ''), 'j', ''), 'k', ''), 'l', ''), 'm', ''), 'n', ''),
        'o', ''), 'p', ''), 'q', ''), 'r', ''), 's', ''), 't', ''), 'u', ''),
        'v', ''), 'w', ''), 'x', ''), 'y', ''), 'z', ''),
        'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''),
        'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', ''), 'N', ''),
        'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', ''), 'T', ''), 'U', ''),
        'V', ''), 'W', ''), 'X', ''), 'Y', ''), 'Z', '')
FROM @T AS T;


Routing data from one SQL Server to another

Yes, I can actually think of three ways to accomplish this (well, 4 if you include the Update section ;-). All three methods use an INSTEAD OF INSERT Trigger on the AuditLog table.

For all three methods, first create the AuditLog table on the remote instance (this should be obvious, but stated for completeness).

Option #1

Set up a Linked Server to the server that you want the data to go to.
Create a Stored Procedure on the remote server that:

accepts an NVARCHAR(MAX) input parameter
converts the input parameter to XML stored in a local variable
inserts into the AuditLog table from a SELECT statement that parses the XML variable using FROM @XmlVariable.nodes()

Create the INSTEAD OF INSERT Trigger on the local instance in which it does something along the lines of:

DECLARE @RowsToSend NVARCHAR(MAX);
SET @RowsToSend = (
  SELECT *
  FROM   INSERTED
  FOR XML RAW
);
EXEC [LinkedServerName].[DatabaseName].[SchemaName].[StoredProcedureName]
          @RowsToSend;

The reason for using the Stored Procedure instead of a straight INSERT statement is that DML statements over Linked Servers have definite performance issues (I don't remember the exact cause(s), but I recall there being a problem with them reusing cached plans or something related to execution plans).

Option #2

Create a SQLCLR INSTEAD OF INSERT Trigger
Create a SqlConnection using "Context Connection = true;"
Create a SqlCommand with a CommandText of SELECT * FROM Inserted;
Create a SqlDataReader via SqlCommand.ExecuteReader();
Use SqlBulkCopy (String, SqlBulkCopyOptions) with a regular / external connection string to the remote instance
Call SqlBulkCopy.WriteToServer(SqlDataReader)
Since this makes a connection to a remote instance, the Assembly needs WITH PERMISSION_SET = EXTERNAL_ACCESS

Please do not set the database to TRUSTWORTHY ON in order to use EXTERNAL_ACCESS. Instead:
Sign the Assembly / DLL
Create an Asymmetric Key in the [master] database from the DLL
Create a Login from that Asymmetric Key
Grant the new Login the EXTERNAL ACCESS ASSEMBLY permission

Option #3

Set up Service Broker to send messages from the local Instance to the remote Instance
Package up the rows in a similar manner to how it was done in Option 1:

DECLARE @RowsToSend NVARCHAR(MAX);
SET @RowsToSend = (
  SELECT *
  FROM   INSERTED
  FOR XML RAW
);

SEND ... @RowsToSend;

See SQL Server Service Broker for details.
This option requires a little more setup than the other two options, but is better in terms of making the data transfer asynchronous (hence this is better if there is a high volume of inserts of logging data and/or there is latency on the transfer with the other two options as that would slow down the operations generating the log data).

-- UPDATE Uno --

Option #4

A variation of Option #1 that I have used before (but somehow forgot about until reading @datagod's answer) is to move the data over in batches all throughout the day.
Create the same Stored Procedure on the remote Instance that receives an NVARCHAR(MAX) input parameter, converts that to XML, and then inserts that via INSERT INTO dbo.AuditLog SELECT c.value('@field1', 'type'), ... FROM @XmlVariable.nodes('/row') t(c);
Instead of creating an INSTEAD OF INSERT Trigger, create a Stored Procedure on the local instance that transfers the data along the lines of:

SET XACT_ABORT ON;

BEGIN TRY

  BEGIN TRAN;

  -- create temp queue table dynamically to adjust for schema changes
  SELECT * INTO #TempRows FROM @b WHERE 1 = 0;

  -- delete rows first to ensure they don't get transferred again
  DELETE tmp
  OUTPUT DELETED.*
  INTO   #TempRows
  FROM dbo.AuditLog tmp;

  DECLARE @RowsToSend NVARCHAR(MAX);
  SET @RowsToSend = (
    SELECT *
    FROM   #TempRows
    FOR XML RAW
  );

  EXEC [LinkedServerName].[DatabaseName].[SchemaName].[StoredProcedureName]
          @RowsToSend;

  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF (XACT_STATE() <> 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW;
END CATCH;

Create a SQL Agent Job to schedule the Stored Procedure above to run every few minutes
This has the asynchronous benefit of the Service Broker approach, but with a little less setup.
With regards to performance: I have used this method for years to move several million rows per day. If you need to move 20 million rows, or more, per day then maybe replacing the XML packaging here with the SqlBulkCopy via SQLCLR approach of Option 2 would be good to try because it would allow you to pass the local data to the remote Instance as a TVP. But otherwise this approach holds up quite well, especially if you stick with the attribute-based XML as I have shown here, which parses faster than element-based XML.

-- UPDATE Dos --

Regarding the ability of FOR XML RAW to handle all possible datatypes and data, I did the following testing:

First, create a test temp table that has columns and data that could potentially be an issue for converting to a text-based format, especially one that has certain characters reserved and hence need to be escaped if present in the data so as to not cause errors.

-- DROP TABLE #BadValues;
CREATE TABLE #BadValues (Col1 NVARCHAR(15), Col2 GEOMETRY, Col3 XML,
                         Col4 SQL_VARIANT, Col5 VARBINARY(MAX));
INSERT INTO #BadValues (Col1, Col2, Col3, Col4, Col5)
  VALUES (N'" =  & < ', 'LINESTRING(11 10, 20 20)', '<test/>', 23, 0x00125DFFFF);

If anyone is wondering where I got the  symbol from, it is a supplementary character that is part of UTF-16 but not in the base UCS-2 code points / characters. I created by doing the following:

SELECT NCHAR(150150) -- returns "" in a DB with a collation ending in "_SC", else NULL

Now, look at what it returns normally:

SELECT *
FROM   #BadValues;

-- " =  & <
-- 0x0000000001140000000000002640000000000000244000000000000034400000000000003440
-- <test />
-- 23
-- 0x00125DFFFF

Great. Now let's throw in the FOR XML RAW clause and see what happens:

SELECT *
FROM   #BadValues
FOR XML RAW;

/*
Msg 6865, Level 16, State 1, Line 1
FOR XML does not support CLR types - cast CLR types explicitly into one of the
supported types in FOR XML queries.
*/

Ok. So we need to convert the GEOMETRY field to something usable. All CLR types (whether custom UDTs or supplied by Microsoft) should have a .ToString() function, so let's try that:

SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
FROM   #BadValues
FOR XML RAW;

/*
Msg 6829, Level 16, State 1, Line 1
FOR XML EXPLICIT and RAW modes currently do not support addressing binary data as
URLs in column 'Col5'. Remove the column, or use the BINARY BASE64 mode, or create
the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.
*/

Well, we got passed the CLR-type error, only to get an error on the VARBINARY field. But it suggests that we use "BINARY BASE64" mode, so let's try that:

SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
FROM   #BadValues
FOR XML RAW, BINARY BASE64;

/*
<row Col1="&quot; =  &amp; &lt; " Col2stringified="LINESTRING (11 10, 20 20)" Col4="23"
     Col5="ABJd//8=">
  <Col3>
    <test />
  </Col3>
</row>
*/

Not so bad. This just means that if you have a CLR type -- GEOMETRY, GEOGRAPHY, HIERARCHYID, or a custom UDT -- then you cannot use SELECT * when packaging up the records in the Inserted pseudo-table. In those cases you need to explicitly list out the columns so that you can apply .ToString() to any fields that are of a CLR type.

Please note that this is only when using the XML method of transporting the data. If you did this in SQLCLR using the SqlBulkCopy class (as noted above in a couple of places), then that might transfer directly (though I haven't tried it yet).

-- UPDATE Dos punto Uno --

Continuing the test from the prior Update Dos above, the following (using the same temp table and test row) shows the process of converting the initial table to XML and then to NVARCHAR(MAX), then pass that to a stored procedure that will convert it back to XML and then SELECT from that using the .nodes() function.

DECLARE @RowsToSend NVARCHAR(MAX);

SET @RowsToSend =
(
  SELECT Col1, Col2.ToString() AS [Col2stringified], Col3, Col4, Col5
  FROM   #BadValues
  FOR XML RAW, BINARY BASE64
);

SELECT @RowsToSend;
--=====--   here would be the call to the remote procedure, which would do the following:
DECLARE @RowsToInsert XML;

SET @RowsToInsert = CONVERT(XML, @RowsToSend);

SELECT @RowsToInsert;

SELECT c.value(N'@Col1', N'NVARCHAR(15)') AS [Col1],
       CONVERT(GEOMETRY, c.value(N'@Col2stringified', N'VARCHAR(MAX)')) AS [Col2],
       c.query(N'(./Col3/*)') AS [Col3], -- use .query() to get sub-element
       c.value(N'@Col4', N'INT') AS [Col4],
       c.value(N'@Col5', N'VARBINARY(MAX)') AS [Col5]
FROM   @RowsToInsert.nodes(N'/row') t(c);


Create a unique constraint on a column based on distinct value from another column in the same table

Seems like this should do it:

CREATE UNIQUE INDEX [UNQ_locationName_companyId]
ON dbo.[locations]([locationName],[companyId]);


SQL Server - Linked Server - Using OPENROWSET with windows integrated security

Declare variable to get current instance name and pass value to OpenROWSet.

Enable Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

OPENROWSET using windows authentication to fetch data

DECLARE @InstanceName VARCHAR(200),
    @sql NVARCHAR(MAX)
SELECT  @InstanceName = ( SELECT @@servername  )

SELECT  @sql = 'select a.* from openrowset(''SQLNCLI'', ''Server='
        + @InstanceName
        + ';Trusted_Connection=yes;'', ''select * from Databasename.dbo.TableName'') as a'

EXEC sp_executeSQL @sql


Truncate Table With Partitions - aligned/ non-aligned indexes

I put this query together for you that should show you what you want.  You can add additional where clauses to trim it further if you need to.

;WITH CTE_PartCount AS
    (
    SELECT P.object_id
        , P.index_id
        , COUNT(P.partition_number) AS PartitionCount
    FROM sys.partitions AS P
    GROUP BY P.object_id
        , P.index_id
    )
, CTE_Objects AS
    (
    SELECT O.object_id
        , O.name AS ObjectName
        , S.name AS SchemaName
        , I.index_id
        , I.name AS IndexName
        , I.type_desc AS IndexType
    FROM sys.objects AS O
        INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
        INNER JOIN sys.indexes AS I ON I.object_id = O.object_id
    WHERE O.is_ms_shipped = 0
        AND O.type_desc = 'USER_TABLE'
    )
, CTE_Summary AS
    (
    SELECT O.object_id
        , O.ObjectName
        , O.SchemaName
        , O.IndexName
        , O.IndexType
        , PC.PartitionCount
        , TablePartCount = FIRST_VALUE(PC.PartitionCount) OVER (PARTITION BY O.object_id ORDER BY O.object_id, O.index_id)
    FROM CTE_Objects AS O
        LEFT OUTER JOIN CTE_PartCount AS PC ON PC.object_id = O.object_id AND PC.index_id = O.index_id
    )
SELECT S.object_id
    , S.SchemaName
    , S.ObjectName
    , S.IndexName
    , S.IndexType
    , S.PartitionCount
    , IsPartitioned = CASE WHEN S.TablePartCount <> 1 THEN 'YES' ELSE 'NO' END
    , IsAligned = CASE WHEN S.TablePartCount = S.PartitionCount THEN 'ALIGNED' ELSE '' END
FROM CTE_Summary AS S


MS SQL Server IP address change considerations

If you are just changing IP address ONLY, then there are less things to worry.

Check

Change the IP address and restart SQL Server.
If your applications are referencing IP address instead of HOST, then they need to change their web.config files to reflect the new IP address.
Linked servers referencing IP address needs to be changed as well.
If any user workstations are connecting using IP, then they need to flush DNS using ipconfig /flushdns or better just restart their workstations.
If the server is part of Failover cluster then follow this steps as well.

If you want to be proactive and find out the applications connecting to your sql server instance, then use this server side trace and then plan accordingly for downtime.

Note: make sure you assign a static IP address and port to sql server.


MariaDB: granting access to a DB before creating the DB

I think I have the answer: it works.

I leave it here just in case someone else run into the same doubt.

Here is an example:

D:\xampp\mysql\bin>mysql.exe -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.13-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER foo2@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT, UPDATE, DELETE ON  DBtest.* TO foo2@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
| localhost | pma  |                                           |
| localhost | foo2 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> CREATE DATABASE DBtest CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use DBtest;
Database changed
MariaDB [DBtest]>  CREATE TABLE t1 (a int, b int);
Query OK, 0 rows affected (0.23 sec)

MariaDB [DBtest]> quit;
Bye

D:\xampp\mysql\bin>mysql.exe -ufoo2 -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.13-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use DBtest;
Database changed
MariaDB [DBtest]> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

MariaDB [DBtest]> select * from t1;
Empty set (0.00 sec)

MariaDB [DBtest]> INSERT INTO t1 (a, b) VALUES (23, 23);
ERROR 1142 (42000): INSERT command denied to user 'foo2'@'localhost' for table '
t1'
MariaDB [DBtest]>

The user can work in the specified DB, with the granted rights, even if it was created (and granted its rights) before the DB was created.


Poor Performance When Calling Query From Another DB on same Server

The issue was the Compatibility Level as suggested by @Kin

One database was 120, the other 100.

I updated to ensure both are 120 and now queries are quick on both databases!

https://docs.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database

Thanks all for the help!


From where does the MySQL Query Optimizer read index statistics?

The direct answer for this would be

information_schema.statistics

mysql> desc information_schema.statistics;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

You could SELECT from that table with

SELECT * FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='mytable';

or see the statistics by doing

SHOW INDEXES FROM mydb.mytable;

Please keep in mind that this table is not always accurate in a write-heavy environment. Periodically you will have to run ANALYZE TABLE against all MyISAM tables that are updated frequently.  Otherwise, the MySQL Query Optimizer, which relies on information_schema.statistics, can sometimes make bad choices when developing EXPLAIN plans for queries. Index statistics must be as up-to-date as possible.

ANALYZE TABLE has ABSOLUTELY NO EFFECT against InnoDB tables. All index statistics for InnoDB are computed on demand by means of dives into the BTREE pages. Therefore, when you run SHOW INDEXES FROM against an InnoDB table, the cardinalities displayed are always approximations.

UPDATE 2011-06-21 12:17 EDT

For clarification of ANALYZE TABLE, let me rephrase. Running ANALYZE TABLE on InnoDB tables is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that conclusion as well.


How are limits in mysql on size of columns etc calculated

The 61 is probably arbitrary.  If you have a 61-way JOIN, you have an awful schema.
The row size limit is not quite right.  What page is that on?  It is ignoring that MEDIUM/LONGTEXT/BLOB fields are not included in that limit.  (But, for that matter, I don't know where 64K-1 applies.)  In InnoDB, there is an "about 8K" limit excluding TEXT, etc that can be pushed to off-block storage.
The InnoDB limit was raised to 1017 recently.  The 4096 limit smells like a 12-bit fields in the Handler (Engine-agnostic) code.

I like limits, too.  Here are some more.


Use SSMS to Create Database Diagrams but not Database Objects

AFAIK - there is no way to do it in SSMS, use other tools for that, such as Microsoft Visio, ERWin etc. - there are a lot of them.

You may create a dummy database and draw you diagrams there, thinking of underlying creating objects as about a part of diagram itself. 8-)


SQL Server instance using only a few hundred MB. Well below the minimum server memory setting

The memory counters in Task Manager or Resource Monitor are not a good way to determine SQL Server's memory usage. If SQL Server is using locked pages, these do not show in the working set or private bytes.
There are many ways to determine if SQL Server is using locked pages. You can look for a message like the following in the error log:
Using locked pages in the memory manager
Or, check the sys.dm_os_process_memory DMV locked_page_allocations_kb column. For example, on a local test instance using locked pages I see this:

Whereas Resource Monitor shows:

Note this is different from the sys.dm_os_sys_memory DMV you mention in the question comments.
There is all sorts of other useful memory usage information in the DMVs, including high-or-low virtual or physical memory indications. These are all described in Books Online.
It seems probable that someone has granted the SeLockMemoryPrivilege to the SQL Server start-up account recently, or the account has been changed to LocalSystem, which has this privilege by default.
Generally speaking, locking pages in memory is a good thing.


Count 3 hours overtime with in a given date time window

Based on @Akina s comment, I created two sets one for the IN actions and another for the OUT, which I joined them and subtract the time spent inside the premises. Therefore I can calculate the total working hours for each employee in any particular day.

I came up with the following query.

SET @row_number = 0;
SET @row_number1 = 0;

select SEC_TO_TIME( SUM( TIME_TO_SEC(abb.overtimes) ) ) overtimes, employee_id
from
    (select TIMEDIFF(ab.working_time,'08:00:00') overtimes, ab.employee_id from
        (select SEC_TO_TIME( SUM(aaaa.timespent) ) working_time, accessDate, aaaa.employee_id
            from
            (select aaa.OUTtime as OUTtime, aaa.INtime, TIME_TO_SEC( TIMEDIFF(OUTtime, INtime) ) as timespent, DATE(OUTtime) as accessDate, aaa.level_id, aaa.employee_id
             from
                 (select aa1.TIMESTAMP as OUTtime, aa2.TIMESTAMP as INtime, aa1.level_id, aa1.employee_id
                  from (select
                            (@row_number:=@row_number + 1) row_num,
                            a1.TIMESTAMP, a1.employee_id, a1.level_id
                        from access_action a1
                        where a1.ACTION = 'OUT'
                        order by employee_id asc, TIMESTAMP desc) aa1
                           INNER JOIN
                       (select
                            (@row_number1:=@row_number1 + 1) row_num,
                            a2.TIMESTAMP, a2.employee_id, a2.level_id
                        from access_action a2
                        where a2.ACTION = 'IN'
                        order by employee_id asc, TIMESTAMP desc) aa2
                       ON aa1.row_num = aa2.row_num) aaa) aaaa
        group by accessDate, aaaa.employee_id
        having accessDate >= '2017-05-26' and accessDate <= '2017-05-30'
        AND working_time > '08:00:00') ab) abb
group by employee_id
having overtimes > '03:00:00';

Performing the query in the data the result is the following.

overtimes   employee_id
03:50:00    4


"A severe error occurred" - Login Failed (but only occasionally)

Just in case this helps, I'm posting some code that might help you work out what account that sid represents:

USE tempdb;
GO
-- to translate SID in binary format to AD format
CREATE FUNCTION [dbo].[fn_SIDToString]
(
  @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN

  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)

  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT

  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY,
            REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END
GO

SELECT dbo.fn_SIDToString(sp.sid), sp.*
FROM sys.server_principals sp
WHERE dbo.fn_SIDToString(sp.sid) = 'S-1-9-3-3788657366-1166700905-846186909-3339902698';

Thanks goes to @James for pointing out that the sid might be in master.sys.server_principals and to @AaronBertrand for pointing me to a great piece of code that will translate SQL Server binary sids to the Windows format.

N.B.: I put this function in tempdb to avoid creating it in a permanent place.  You may want to put it somewhere else for future use, since tempdb gets recreated automatically upon SQL Server startup, and thus the function here will not survive service restarts or machine reboots, etc.


Sql Server pivot string results into columns

You need to add another column so that the aggregate function you have for the pivot operation has something to bite into.  Try the below.
DROP TABLE IF EXISTS #imgText

create table #imgText (
rowindex int,
imgName varchar(50)
)

insert into #imgText (rowindex,imgName) values (0,'dog')
insert into #imgText (rowindex,imgName) values (1,'plant')
insert into #imgText (rowindex,imgName) values (0,'cat')
insert into #imgText (rowindex,imgName) values (1,'tree')
insert into #imgText (rowindex,imgName) values (0,'mouse')

;WITH CTE_Raw AS
    (
    SELECT rowindex
        , imgName
        , GRP = ROW_NUMBER() OVER (PARTITION BY rowindex ORDER BY rowindex)
    FROM #imgText
    )
SELECT [0] AS index0
    , [1] AS index1
FROM CTE_Raw AS T
    PIVOT (MAX(imgName) FOR rowindex IN ([0], [1])) AS pvt


"Log file for database is full" running an INSERT statement

Did you try to run this select? I guess it outputs a LOT of records. For example if M table contains 1000 records it outputs 1000 * 1.3M = 1.3 BILLION records (excepts a few with M.MemberNo=D.MemberNo). So in this case increasing a LOG file has no sense. Try to run this query:

INSERT INTO SCRATCHDB..members_temp
SELECT * FROM PROD..Member (NOLOCK)
WHERE OutletId IN ('87')
AND DATECREATED >= '06 Nov 2012'
AND DATECREATED <= GETDATE()
AND MemberNo NOT IN (select MemberNo from SCRATCHDB..members_temp (NOLOCK));


Linked Server: An error occurred during decryption

Update: July 2020
As of July 2020, we now replicate the service master key after a host replacement. Previously, during a host replacement linked servers with passwords would get the error “An error occurred during decryption”. This is now resolved.
https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/

If a host replacement happens, RDS SQL Server does not restore the service master key. This means that linked servers with passwords will get the error “An error occurred during decryption” when you try to use the linked server. Unfortunately, there is no current notification of a host replacement. The closest you can come is to monitor reboots and failovers via SNS events. You can read more about subscribing to those events on Using Amazon RDS Event Notification. When a reboot or failover happens, you need to manually re-add the linked server login passwords. This is a temporary workaround until a future update to RDS SQL Server addresses this by restoring the service master key after a host replacement.


Is there a way to throttle index creation?

ionice -p PID ... on Linux can in theory change the I/O scheduling class and priority of a running process. In that case you can start the index build, find it's PID and "ionice it". Have the commands to find the PID and ionice ready to run in separate windows and the impact should be very short lived.


Unable to access OEM 11g grid control after upgrading to Firefox ver 39.0

Well, I have finally figured it out. I clicked on

Opened Help Menu (?)
Troubleshooting Information
Refresh Firefox

This would reset the default Firefox configuration. You will lose all your add-ons. Beware!

Next, enter following in browser URL:

about:config

Change the following settings
security.tls.version.fallback-limit=1

Restart firefox.

Viola! OEM loads flawlessly.


How to determine if SQL Server Reporting Services database is being used?

ExecutionLog databases seems to be the way to go:

SELECT COUNT(*) AS RptExecutions
FROM     ReportServer.dbo.ExecutionLog
        ,ReportServer.dbo.ExecutionLog2
        ,ReportServer.dbo.ExecutionLog3


PostgreSQL's MVCC is used over-writing mechanism,  which is roughly mentioned to this document , so all updated rows are inserted into the table. Thus, you cannot get free space.  On the contrary, the table size is temporarily increased.

(Even though autovacuum feature works  very efficiently,) I think you have to do VACUUM FULL in order to certainly remove dead rows(tuples) and dead pages after updating. That way (do UPDATE SET NULL and VACUUM FULL), the size of the table will be small.


Why is MySQL Workbench is reporting different number of writes per second at the same time on the same database?

Perhaps that figure is a snapshot with a tight granularity, reading the number of writes once then again after some portion of a second, and multiplying up to get a "per second" value. In that case I would expect the figures to differ every time unless you somehow request an update of both at exactly the same fraction of a second.

If the figure is an average taken over a larger timeframe (multiple seconds instead of one or a fraction there-of) then the figures would be closer to each other.

You can see a similar effect looking at CPU use on any unix-a-like system by running two copies of a top variant in separate terminals. The same for disk and network IO with iotop and iftop or equivalents. They will almost always show slightly different figures as they poll for updated data at slightly different times, especially if you turn the update frequency as fast as you can read.


In general SELECT * is bad, but like most rules that are exceptions.

Not really, there'll be a minor performance hit but the simplified logic and compatibility more than make up for this.
I believe the SELECT * gets replaced with a list of all fields in the table which the DB engine has to look up before it can run the SQL, this is where the performance hit comes from. However, I've never seen an noticeable performance decrease and in my opinion the simplified maintenance and compatibility make up for this.

There are a couple of things to consider though:

Always use a WHERE clause (unless you REALLY want the entire table), this is a different issue to the SELECT * issue.
If you don't control the schema, listing columns will keep network traffic as low and predictable as possible if someone adds a varbinary(max) field to the table for storing images/PDF file or whatever.

This seems to be one of those "rules" that have come about to stop people reading an entire 400+ column tables into memory.
In my experience, as long as the table is fairly small (in terms of columns) and you really do need all the data, let the highly-optimized DB take the hit while you concentrate on maintainability and compatibility and other benefits to yourself and your users.


Problems connecting to Windows-based Postgresql

Here are the steps of troubleshooting I would recommend:

From the local machine try the following.  If they work you know the issue is not PostgreSQL.  If they don't work then you know your issue is local to the machine.

psql -h localhost -p 5433
psql -h [external ip] -p 5433
If the latter fails, try temporarily disabling all firewalls and trying again

Now if that works the next step is to troubleshoot the network.  Traceroute may be helpful here.  Also try connecting to any other open ports using other software and make sure that works.  From there, you may be able to figure out exactly where the packets are being dropped.


stoplist can't be replicated but there is one ugly way to do it. Words in Stop lists are stored in sys.fulltext_stopwords . Using linked server you can query this table and compare it on secondary server . If there are any changes then run alter command on secondary to add\remove those changes from stoplist.

ALTER FULLTEXT STOPLIST YourStopLIstName ADD 'en' LANGUAGE 'Spanish';

Can be setup as recurring job. Not a decent method but should work fine.


SELECT *
FROM   crosstab(
       'SELECT ad_id, d.name, d.valueofname
        FROM   users
        LEFT   JOIN users_details d USING (ad_id)
        ORDER  BY 1'

    , $$VALUES ('name'), ('age'), ('birthday'), ('job')$$
   ) AS ct (ad_id int, name text, age text, birthday text, job text);

The LEFT JOIN to keep users without any data in the result. If that never occurs or is not desirable, remove the table users from the query completely - it has no other use here.

Detailed explanation for crosstab():

PostgreSQL Crosstab Query

The major advantage over alternative solutions is top performance, which seems to be your prime objective.


You want to be able to query sys.columns in each database. To do this, try:

select 'union all select ''' + name + ''' as dbname, s.name + ''.'' + o.name + ''.'' + c.name collate database_default as colname
        from ' + quotename(name) + '.sys.columns as c
        join ' + quotename(name) + '.sys.objects as o
        on o.object_id = c.object_id
        join ' + quotename(name) + '.sys.schemas as s
        on s.schema_id = o.schema_id
        where c.name like ''%name%''  collate database_default '
from sys.databases;

Then copy the column you get back into a new window, strip the leading "union all", and run it. :)


Run a report by specifying which server to execute the query on

This actually wasn't nearly as hard as I initially thought, but I am putting this up here so that others can find this.

The first step is to create a data source that connects to a server that already has the stored procedure/s on it.  You can't skip this step, otherwise you won't be able to easily build your tables, matrixes, etc. because Visual Studios won't know what fields you're working with.

The next step is to lay out report after adding all the necessary data sets and tables, etc.

After the report is roughly how you want it, create a parameter named @Server.  In the properties of the datasource's connection string, click the equation button (fx).  Modify the connection string to look like this, replacing the catalog with the appropriate database name:

  ="Data Source=" & Parameters!Server.Value & ";Initial Catalog=DB"

Now, when you run the query you can type in the server name, and the report will execute the query/s on that server.

If you want to make it a bit cleaner, you could create a list of the appropriate servers in a data set and use that to populate the parameter.


If I understand you correctly you want to add up psbin values up to 250.000 from random rows

SELECT *
FROM my_players, (SELECT @psbin := 0) x
WHERE player_type = 'IF'
AND (@psbin := @psbin + psbin) <= 250000
ORDER BY RAND();


To me this looks like a quite typical join:

select t1.id,t1.value,t2.value,t3.value
from t1,t2,t3
where t1.id=t2.id and t1.id=t3.id


Do MySQL GTIDs get created for all transactions or just those that occur after enabling GTIDs?

As I expected, GTIDs will not be created for pre-existing data.

This makes sense because, especially without the binlogs, how would the server know what transactions were used to generate the data?


Is it important to release memory back to the server?

How exactly would you 'release' the memory back?

As a general rule SQL Server will steal all the memory from the OS and then use it for it's own purpose. See Dynamic Memory Management:

  The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs... The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory;


Update: Since Postgres 9.3 plpgsql can access the number of rows processed by COPY directly:

Get the count of rows from a COPY command

I recently ran into the same problem. I tried with a plpgsql function, but ROW_COUNT is not set by COPY - in Postgres 9.2 or older.

You could just run two queries. First count() and then COPY. With simple queries that's probably the way to go. But with huge / complex queries this is a pain and can double the execution time.

I came up with a solution that uses a temporary table and counts the rows before executing COPY.

I adapted what I have for you so you can COPY TO STDOUT and pipe to bzip2, which is not possible from within a plpgsql function:

1. Create a function that takes an SQL string and creates a temporary table with it:

CREATE OR REPLACE FUNCTION f_copy_prep(_query text)
  RETURNS void AS
$func$
DECLARE
   ct integer;
BEGIN
   -- If you deal with huge results, set more RAM for temp tables locally:
   -- SET temp_buffers = 512MB';  -- example value

   EXECUTE 'CREATE TEMP TABLE cp_tmp ON COMMIT DROP AS (' || _query || ')';

   GET DIAGNOSTICS ct = ROW_COUNT;
   RAISE LOG 'My text here. rows: %', ct;

END
$func$  LANGUAGE plpgsql;
ALTER FUNCTION f_copy_prep(text) SET search_path=public,pg_temp;
REVOKE ALL ON FUNCTION f_copy_prep(text) FROM public;
GRANT EXECUTE ON FUNCTION f_copy_prep(text) TO ???;

Function executes dynamic SQL, so you can use it for any query.
This is inherently unsafe, so run it with the minimum rights necessary, revoke all rights from public and grant EXECUTE exclusively to a trusted user. Follow the instructions in the manual!
Create temporary table with ON COMMIT DROP, so it gets dropped automatically at the end of the transaction.
Get the row count with GET DIAGNOSTICS - ROW_COUNT is set by the SELECT statement in EXECUTE. Write it to the log - your requirement. No need for a separate count(*).

2. Call from shell-script to pipe output through bzip2

psql "connection parameters" \
-c "SELECT f_copy_prep('SELECT * FROM tbl WHERE insertion_date > ''date'''); \
    COPY cp_tmp TO STDOUT WITH CSV HEADER;" \
| bzip2 -c > backup.csv.bz2

Put two SQL commands into your -c argument or put complex queries in a file and use the -f parameter. All is executed in one transaction. Only the output of the last command is returned - fits our need. Careful with the syntax - multiple layers of interpretation (first shell, then Postgres).
First command is the above function with your query-string as parameter. Second is the COPY TO STDOUT.

I tested this with PostgreSQL 9.1 on Linux and it worked for me: data in the file, message with row-count in the log.


importing csv file with ragged right format and " text qualifier

The options I can think of are.

Preprocess the file to remove the double quotes using an 'execute process' task. An easy powershell script that could do this for you would be something like :
powershell -Command {cat \yourpath\stuff.txt | %{if($_ -match '"?(\w+)'){$Matches[1]} }>\yourpath\stuffout.txt}

Process the file as a single column of variable text. Use an execute script task to process each record and apply a regular expression as the one above ("?(\w+)) or one that better serves your business rules. Redirect each non-compliant record to a file to review later.

The second option is more "correct" as your etl will be prepared to report on non-compliant rows. You can expand on your regular expression to make it more specific.


PostgreSQL Streaming Replication - Slave remains in recovery mode

This is what "streaming replication" means.  You stay in recovery, so that you can stream changes from the master.

That means you cannot run things that change the database on the replica.


group by left(col, x) text column - possible to index?

You may want to try something like this:
create table x
(
  y varchar (25),
  z varchar (10) generated always as (left(y, 19)) stored, -- also virtual
  key (z)
);

Or you could name the index as follows (see here):
CREATE INDEX x_z_ix ON x (z);

I find that giving explicit names to indexes is preferable - it gives more meaningful error messages.
Just because there is an index doesn't mean that the optimiser will actually use it! The table might be too small, too many nulls, skew in values &c.
I'm not exactly sure what your own create table syntax is actually doing - could you post the output of show create table tab\G.