Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan)

Firs the displayed query plan says:

  Limit  (cost=0.43..44.98 rows=17 width=553) ...

Obviously you added a LIMIT clause to the query, which is not in the query you show. If you didn't do the same in you second test, you may already have your answer: The same query with LIMIT (and without ORDER BY) can stop as soon as enough rows are found and can be much faster. Can also lead to a different query plan to begin with.

In principle

EXPLAIN ANALYZE measures the time spent by the Postgres process. Measurement is taken on the server. It adds a bit of overhead as compared to just executing the SQL command alone. For very cheap commands, that overhead may be more than the query cost. Overhead also depends on used options to some degree. You can minimize overhead with EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF). (The TIMING option of EXPLAIN is in no way related to psql's \timing; used to toggle additional measurements per plan node.)

The manual:

  The ANALYZE option causes the statement to be actually executed, not
  only planned. Then actual run time statistics are added to the
  display, including the total elapsed time expended within each plan
  node (in milliseconds) and the total number of rows it actually returned.

About the TIMING option:

  Include actual startup time and time spent in each node in the output.
  The overhead of repeatedly reading the system clock can slow down the
  query significantly on some systems, so it may be useful to set this
  parameter to FALSE when only actual row counts, and not exact times,
  are needed. Run time of the entire statement is always measured, even
  when node-level timing is turned off with this option. This parameter
  may only be used when ANALYZE is also enabled. It defaults to TRUE.

\timing in psql, on the other hand, measures the time between sending the command and receiving results - including network latency. Measurement is taken on the client. That can be many times the execution alone. Seems to be your case.

Due to the mentioned overhead above, the opposite might be observed in cases with low latency and minimal data volume sent over the wire (especially with psql executed on the same server): \timing can report faster times.

There is a third good option: use the shell command time (also measuring on the client, obviously). See:

How can I time SQL-queries using psql from command line?

Asides:

  When I actually execute it (with \timing)  ...

You are aware that EXPLAIN ANALYZE does actually execute the given query, with all side effects - as opposed to just EXPLAIN.

  I tried to programmatically loop over the ids and run a separate query for each and it produces better results (around 1.5s)

A loop can produce query plans customized to each actual input value, based on statistics for the value. While a large IN or a large input array will produce a plan based on generic estimates. See:

How to use index for simple time range join?

You can achieve similar effects as with your loop with a LATERAL join from a (derived) table or with many SELECT statements tied together with UNION ALL or with a loop in a (plpgsql) server-side function - without adding many times the network latency and/or planing overhead. Related:

Optimizing a Postgres query with a large IN
Can spatial index help a "range - order by - limit" query
Optimize GROUP BY query to retrieve latest row per user


Fastest way to insert 30 thousand rows in SQL Server

If you are really just trying to insert 30000 ID values that increment by 1, starting at 1002000, I highly doubt you'll be able to rig up anything in C# that will be faster than something like this:

INSERT ##MyTable(ID)
  SELECT 1001999 + n FROM
  (
    SELECT TOP (30000) n = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.[object_id]
  ) AS x;

In my lowly Windows VM on a Mac host this occurs sub-second.

For other ideas about generating sets / sequences without looping (e.g. a numbers table may be even faster), see this blog series:

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3

If the values are defined and not some incremental sequence (e.g. 30,000 values you somehow coerced into a DataTable), then you can consider using a table-valued parameter. First, create this type in your database (you just do this once):

CREATE TYPE dbo.MyIDs AS TABLE(ID INT PRIMARY KEY);

Now have a stored procedure that accepts this:

CREATE PROCEDURE dbo.TakeMyIDs
  @m dbo.MyIDs READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT ##MyTable(ID) SELECT ID FROM @m;
END
GO

Now call your stored procedure from C# and pass the DataTable as a parameter:

// assuming an active connection object, conn,
// and a populated DataTable called dtIDs:

SqlCommand cmd = new SqlCommand("dbo.TakeMyIDs", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvp1 = cmd.Parameters.AddWithValue("@m", dtIDs);
tvp1.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
cmd.Dispose();


Storing point data for linestring in MySQL

GIS

Use GIS and stop doing all this madness.

MySQL Provides

LineString(pt1, pt2)
ST_LineStringFromText(wkt) (type-safe)
ST_GeomFromText(wkt)

They're easy to use and any program can output Well-known text or Well-known binary to create the lines. They're also indexable, more compact, and stored with SRID.

  also some lines are "forks" or "branches" from a main one, for this I am currently using the "forkFrom" column to store the ID of the point the "fork" would start from. how would i do this with a lineString? is that what a multiLineString is for?

A multiLineString can hold forks, yes. Or, you can just do it with another linestring entry in the table. It just depends on what you want to do. When you query it do you want to know what "fork" is returned (separate entries in the table make this easier and faster), or do you only care the pipe somewhere regardless of forks is returned.

Also obligatory statement if you're going to use GIS, you'll probably want PostGIS at some point.


tsql Process running forever... what is it doing

Download sp_whoisactive stored procedure written by Adam Machanic if you do not have it already. Execute with @get_full_inner_text = 0 option.

From the comment section of the stored procedure:

  --If 1, gets the full stored procedure or running batch, when available
  --If 0, gets only the actual statement that is currently
  running in the batch or procedure
  @get_full_inner_text  = 0


Can't mysql autocast encoding in query data?

There are good explanations why MySQL can't do it automatically:

https://stackoverflow.com/a/12260863/1261575

https://stackoverflow.com/a/21061305/1261575


How to get the last not-null value in an ordered column of a huge table?

A common solution to this type of problem is given by Itzik Ben-Gan in his article The Last non NULL Puzzle:

DROP TABLE IF EXISTS dbo.Example;

CREATE TABLE dbo.Example
(
    id integer PRIMARY KEY,
    val integer NULL
);

INSERT dbo.Example
    (id, val)
VALUES
    (1, 136),
    (2, NULL),
    (3, 650),
    (4, NULL),
    (5, NULL),
    (6, NULL),
    (7, 954),
    (8, NULL),
    (9, 104),
    (10, NULL);

SELECT
    E.id,
    E.val,
    lastval =
        CAST(
            SUBSTRING(
                MAX(CAST(E.id AS binary(4)) + CAST(E.val AS binary(4))) OVER (
                    ORDER BY E.id
                    ROWS UNBOUNDED PRECEDING),
            5, 4)
        AS integer)
FROM dbo.Example AS E
ORDER BY
    E.id;

Demo: db<>fiddle


Correct data model for users with clients

You can add a unique index across the user_id and username in the Clients table this will ensure that the username can only occur once per user_id. Though you should be aware that if the user_id column is nullable it will also apply to clients that do not have a user_id assigned to them.

EDIT

Using a composite primary key that consists of the client_id, user_id and the username will not meet your requirements. This is because the addition of the client_id makes the grouping unique. Take the following rows as an example:

client_id user_id username
1         1       John Doe
2         1       John Doe

If you had a composite primary key of all 3 columns the above would be allowed - however, if you just added a unique index across the last 2 columns the above would be invalid - which is your intended result.

You could add a composite primary key across the last two columns but this is not a good design as it adds additional complexity when attempting to reference the client. A better design would be to keep the client_id as the primary and add a unique index across the last 2 columns.


Alternatives for a LEFT OUTER JOIN query

You can rewrite the LEFT JOIN to a subquery, but it may still compile as an outer join query - I have no knowledge of those aspects of the H2 platform.

SELECT *
FROM tSysNls
WHERE nlsGuid IN (
    SELECT COALESCE(
           --- first option:
           (SELECT de_AT.nlsGuid
            FROM tSysNLS AS de_AT
            WHERE de_AT.nlsClazz=x.nlsClazz AND
                  de_AT.nlsAttribute=en.nlsAttribute AND
                  de_AT.nlsLocale='de_AT'),
           --- second option:
           (SELECT de.nlsGuid
            FROM tSysNLS AS de
            WHERE de.nlsClazz=x.nlsClazz AND
                  de.nlsAttribute=en.nlsAttribute AND
                  de.nlsLocale='de'),
           --- third option:
           en.nlsGuid)
    FROM tSysNLS AS en
    WHERE en.nlsLocale='en'
        AND en.nlsClazz='Contact'
        AND en.nlsAttribute='firstName');

Or you could build some type of temp table (or a regular table if H2 doesn't support temp tables), then populate it in three steps. Again, this will probably still compile using an anti-semijoin or some other type of outer join, but maybe it'll function as a workaround for your bug:

--- 1. de_AT, if there are any:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de_AT' AND nlsClazz='Contact' AND nlsAttribute='firstName';

--- 2. de for those that don't have de_AT:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

--- 3. en for the remaining records:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='en' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

SELECT * FROM #temptable;

But these are not pretty solutions.


mysql -> show changes in fields between the entries


Having performance issue for an extract date without time to compare with sysdate,

If you want an index to be used, you should remove the function from the column. You can rewrite the condition (which removes the time part from the two datetimes):

where trunc(timefield) = trunc(sysdate)

to the equivalent (where the function is applied only to sysdate and not the column):

where (timefield >= trunc(sysdate) and timefield < trunc(sysdate) + 1)

This way, the condition is "sargable" and an index on timefield can be used. With the previous condition, a table scan (or a full index scan) was required.


MySQL: select a row if that row is equal for every user

If there are 5 rows per month (1 for every user), this will do:

SELECT m.year, m.month
FROM month AS m
GROUP BY m.year, m.month
HAVING SUM(finished) = 5 ;


Select with if statement return 'column does not exist'

You have two errors in your statement:

There is no IF in SQL, you need to use a CASE expression
String constants are defined using single quotes in SQL ('), double quotes are for identifiers

select case
          when available_count < 5 then 'neni skladem'
          else 'na sklade'
       end as availability
from ...


Automatic MySQL Replication Fail-over Strategy

MySQL 5.6 introduces Global Transaction Identifiers to replication, and complements these with new failover and switchover utilities which can automate fault recovery

You can learn more about replication in MySQL 5.6 here:
http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html

You can also watch a tutorial demonstrating how these utilities work to provide self-healing clusters:
http://dev.mysql.com/tech-resources/articles/mysql-replication-utilities.html


JSON support was not introduced until SQL Server 2016, so you'll need to use custom functions. Check out Phil Factor's article on this where he provides some code for parsing JSON in earlier versions of SQL Server.


Are there any potential problems with running distinct Oracle rman archivelog backups of the same database at the same time?

Normally RMAN takes care of this. It will only make a backup of the archivelog files of which it does not have a backup.

The restore just reads the archivelogs that it needs from tape.


SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

Lots and lots of caveats here. I've tested this in a very limited way in a scenario where the data/log paths are congruent across all replicas, haven't added error handling, etc. You can call this stored procedure from the DDL trigger if you decide to go that way, as Kin suggested, or from a job, or what have you.

See additional comments inline.

CREATE PROCEDURE dbo.AddNewDBsToGroup
  @group SYSNAME = N'your_group_name', -- *** SPECIFY YOUR GROUP NAME HERE ***
  @path  SYSNAME = N'\\atel-web-be2\backups\',
  @debug BIT = 1
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE
    @sql        NVARCHAR(MAX) = N'',
    @remote_sql NVARCHAR(MAX) = N'';

  DECLARE @t TABLE(db SYSNAME);

  INSERT @t SELECT name FROM sys.databases
  WHERE replica_id IS NULL AND database_id > 4;

  DECLARE @r TABLE(s NVARCHAR(512));

  -- get the *healthy* replicas available for this group
  -- you'll need error handling to handle cases where any
  -- of the replicas is currently *not* healthy. This
  -- script does not tell you this happened.

  INSERT @r SELECT r.replica_server_name
  FROM sys.availability_groups AS g
  INNER JOIN sys.dm_hadr_availability_group_states AS s
  ON g.group_id = s.group_id
  INNER JOIN sys.availability_replicas AS r
  ON g.group_id = r.group_id
  AND r.replica_server_name <> @@SERVERNAME
  WHERE g.name = @group
  AND s.primary_replica = @@SERVERNAME
  AND s.primary_recovery_health_desc = 'ONLINE'
  AND s.synchronization_health_desc = 'HEALTHY';

  -- add the database to the group on the primary:

  SELECT @sql += N'ALTER AVAILABILITY GROUP '
    + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(db) + ';'
  FROM @t;

  IF @debug = 1
  BEGIN
    PRINT @sql;
  END
  ELSE
  BEGIN
    EXEC master..sp_executesql @sql;
  END

  -- back up the database locally:
  -- this assumes your database names don't have characters illegal for paths

  SET @sql = N'';

  SELECT @sql += N'BACKUP DATABASE ' + QUOTENAME(db) -- ** BACKUP HAPPENS HERE **
    + ' TO DISK = ''' + @path + db + '.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
    BACKUP LOG ' + QUOTENAME(db) +
    ' TO DISK = ''' + @path + db + '.TRN'' WITH INIT, COMPRESSION;'
  FROM @t;

  IF @debug = 1
  BEGIN
    PRINT @sql;
  END
  ELSE
  BEGIN
    EXEC master..sp_executesql @sql;
  END

  -- restore the database remotely:
  -- this assumes linked servers match replica names, security works, etc.
  -- it also assumes that each replica has the exact sime data/log paths
  -- (in other words, your restore doesn't need WITH MOVE)

  SET @sql = N'';

  SELECT @sql += N'RESTORE DATABASE ' + QUOTENAME(db) -- ** RESTORE HAPPENS HERE **
    + ' FROM DISK = ''' + @path + db + '.BAK'' WITH REPLACE, NORECOVERY;
    RESTORE LOG ''' + @path + db + '.TRN'' WITH NORECOVERY;
    ALTER DATABASE ' + QUOTENAME(db) + ' SET HADR AVAILABILITY GROUP = '
    + QUOTENAME(@group) + ';'
  FROM @t;

  SET @remote_sql = N'';

  SELECT @remote_sql += N'EXEC ' + QUOTENAME(s) + '.master..sp_executesql @sql;'
    FROM @r;

  IF @debug = 1
  BEGIN
    PRINT @sql;
    PRINT @remote_sql;
  END
  ELSE
  BEGIN
    EXEC sp_executesql @remote_sql, N'@sql NVARCHAR(MAX)', N'SELECT @@SERVERNAME;';
  END
END
GO

Once you've created the stored procedure, you can call it this way and look at the messages pane to see if it has identified the right group, databases, and servers before ever running it:

EXEC dbo.AddNewDBsToGroup @debug = 1;

When you are confident it is going to do the right thing (and you fully understand what 'the right thing" is), then change that to:

EXEC dbo.AddNewDBsToGroup @debug = 0;

If it fails, don't worry, it will tell you.


Limit max CPU usage SQL SERVER with WSRM

Is there any reason NOT to use the approach you've defined? Absolutely.

Imagine you had bought a car - a car that when you hit 50MPH the engine starts to overheat. Would your reaction to this situation be to artificially limit the car to 49MPH, or to find out what the fault is with the engine?

Why should you limit your car to 49MPH? The manufacturer stated that it could drive as fast as 80MPH - you like to drive your car fast so you want to get it to this speed - if it wasn't for that damn overheating issue.

The car you bought was also really, really expensive. Each engine cylinder needs to be utilised to the max so you aren't wasting that money!

By artificially limiting SQL Servers access to the CPU, you are missing out on performance. You may have temporarily resolved the performance issues by ensuring the CPU is available for the OS to use, but you haven't answered the real question - WHY is SQL Server using 100% of the CPU?

My advice is as follows:

Find out what the real issue is, and fix it. Don't cover the issue up with what is effectively a kludge. The issue WILL reappear and smack you in the face down the line when the workload of the server naturally increases with growth.

As a temporary fix, resource governor can be used to lower the CPU used, UNTIL YOU FIND THE REAL PROBLEM.


If you are using Windows cmd, you need chcp 65001 before entering mysql/mariadb.  Since you are using a Linux variant, terminal may need SET NAMES utf8mb4; from within the mysql/mariadb.
If the columns are not utf8mb4, you will have other problems.  Check a table by using SHOW CREATE TABLE tablename;
This provides a rundown on common problems and leads to their solutions:  https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored


Viability of a large database without using indexes and some other things

The use of views backed by linked server queries puts you in a pretty tough situation.  You get bad cardinality estimates coming out of the "remote query" operators (10k rows estimated, vs. the actuals of 521k and 30k respectively).  This results in SQL Server choosing to sort and use a merge join, when it would probably have chosen a hash join had the estimates been better.

For this specific query, you could try a hash join hint to avoid the sort + merge join plan:

From  eng_facven A
    inner hash Join
   eng_facved B On A.fv_serial = B.fd_serial

As a general solution, you might consider materializing the remote tables into temp tables first.  This will give you better estimates and thus likely better plans.

Of course, the feasability of this option depends a lot on the size of the data / number of rows.  In the query referenced above, you'd only be loading around 16 MB total into temp tables, which is probably reasonable - especially for a report that might only be run once a day, or a few times per day.

The example query you provided would look like this:

SELECT *
INTO #eng_facven
FROM eng_facven;

SELECT *
INTO #eng_facved
FROM eng_facved;

Select  A.fv_empresa, A.fv_ganado, A.fv_contra
   , Sum(A.fv_animales)        fv_animales
   , Sum(A.fv_primales)        fv_primales
   , Sum(B.fd_prima)/Nullif(Sum(A.fv_animales),0)  fv_prima_total
   , Sum(B.fd_kilos)         fv_kg_factu
   , Sum(B.fd_bruto)         fd_precio
   , Sum(A.fv_imp_dto_trans)       fv_dto_fac

   , (Sum(B.fd_bruto) - Sum(A.fv_imp_dto_trans))
    /
    Nullif(Sum(B.fd_kilos),0)       fv_fd_prec_kg_canal

From  #eng_facven A
    Join
   #eng_facved B On A.fv_serial = B.fd_serial

Group by A.fv_empresa, A.fv_ganado, A.fv_contra;

For larger tables, you might need to consider having a scheduled (daily / hourly) job that copies the contents of the remote tables into local tables, and have your queries reference those.  This depends on how up-to-date your reports are required to be.


CMEMTHREAD waits during Index Optimisation in Availability Group

I don't have access to an Availability Group secondary that's really cooking :-)
but i can share some things that'll get you a little farther.
When you have parallel redo on the secondary, activity like redo of heavy index maintenance is accompanied by CMEMTHREAD waits.  These waits disappear when Trace Flag 3459 is globally enabled.  And you are wondering if the CMEMTHREAD wait behavior is due to a bug.
It is unlikely the CMEMTHREAD wait behavior is due to a bug.
The post linked below is a few years old but still my favorite in explaining the intent of CMEMTHREAD waits as memory object serialization control.  Bob explains those memory objects with access potentially controlled by CMEMTHREAD waits may be global, partitioned per NUMA node, or partitioned per CPU.
How It Works: CMemThread and Debugging Them
Bob Dorr & Rohit Nayak
2012 December 20
https://techcommunity.microsoft.com/t5/sql-server-support/how-it-works-cmemthread-and-debugging-them/ba-p/317488
One of the important enhancements in SQL Server 2016 was the introduction of dynamic memory object partitioning.  Prior to SQL Server 2016, as contention was observed to be a significant issue on individual global memory objects, individual trace flags would enable partitioning that object by NUMA node(trace flag 1236 is an example).  And trace flag 8048 would promote many of the memory objects partitioned at the NUMA node level to being partitioned at the CPU level.
In SQL Server 2016, many of the memory objects became eligible for dynamic object partitioning: they'd start with a single global partition but as contention was detected they'd be promoted to NUMA node partitioned.  If further contention is detected, the object would be promoted again to CPU partitioned.
More about that in this blog post.
Dynamic Memory Object Scaling
Ajay Jagannathan
2016 June 27
https://techcommunity.microsoft.com/t5/sql-server/dynamic-memory-object-scaling/ba-p/384755
So while parallel redo activity is seeing high CMEMTHREAD waits, watching the results of the query below over time will reveal which memory objects are under contention. I think its likely to be a global object that is not eligible for dynamic partitioning.
SELECT omo.type, memory_node_id, omo.pages_in_bytes, omo.partition_type, omo.partition_type_desc,
omo.waiting_tasks_count, omo.exclusive_access_count, omo.contention_factor
FROM sys.dm_os_memory_objects omo
WHERE omo.waiting_tasks_count > 0
ORDER BY omo.waiting_tasks_count DESC;

So, enabling global trace flag 3459 mitigates the CMEMTHREAD wait time.  In this case, that's to be expected.  Global trace flag 3459 disables parallel redo, leaving the AG secondary with serial redo.  With a single redo thread for the Availability Group secondary redo activity, the contention for the memory object that was present with parallel redo is suddenly gone.
Whichever memory object is under contention in this scenario, it may be possible for enhancement to allow it to be dynamically promoted as many other objects are, which would alleviate the CMEMTHREAD waits even with parallel redo.  But some work is harder or more expensive to partition than to accomplish as a single global object.


Comparing multiple tables in Sqlite 3 using python:

I figured it out. I was able to do a FULL OUTER JOIN to display what is missing from one table but included in another table.

SELECT d.type,
         d.color,
         c.type,
         c.color
FROM dogs d
LEFT JOIN cats c USING(color)
UNION ALL
SELECT d.type,
         d.color,
         c.type,
         c.color
FROM cats c
LEFT JOIN dogs d USING(color)
WHERE d.color IS NULL;


Can I add a single trigger that will fire for every UPDATE, INSERT or DELETE on every table in a database?

No. As per the documentation each TRIGGER must resolve down to columns updated on a single table.

There is no magic solution to your problem, but there are several generic approaches that are generally used.

Utilise Stored Procedures to update a single table holding the records of the last update time of all other tables - this would need to be written into every Stored Procedure that runs an INSERT, UPDATE or DELETE.
Change Data Capture or Change Tracking could also be used if you set up some SQL Agent Jobs to check for updates on a regular basis. Pinal Dave has a great guide to using CDC for this here
Table-level DML triggers - as per your own post. This is likely to have a significant performance overhead and is likely a really bad idea.


Architecting table with multiple paths to common parent tables

You are thinking about this incorrectly. An assignment submission from a student not enrolled in the corresponding course, if it happens to get marked, is not fundamentally different from a properly submitted and marked submission from a student who subsequently drops the course. Allow the entry of the mark to exist in the database, by noting that when the final report of assignment marks for each course is generated, the (inner) join will fail for all submissions for students not enrolled in the course at exam time. That is sufficient to properly implement the business logic.


You must assign a name to the CHECK constraint during add:

ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT check_json_validity CHECK(JSON_VALID(jsonf));

Then you may freely remove it by name:

ALTER TABLE
DROP CONSTRAINT check_json_validity;

If you forget to assing the name you may to see it in SHOW CREATE TABLE output.


If you want to query in the INFORMATION_SCHEMA database I have the following:

Total Storage By Database in MB

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "DataSize",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size"
FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,
SUM(XSize) SXSize,SUM(TSize) STSize
FROM (SELECT table_schema DB,data_length DSize,index_length XSize,
data_length+index_length TSize FROM information_schema.tables WHERE
table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

Total Storage By Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Total Storage Database and Storage Engine

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "DataSize",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT table_schema,engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A ORDER BY TSize;

Please note in all the queries you see a subquery that looks like this: (SELECT 2 pw)

(SELECT 0 pw) present the Database Sizes in Bytes
(SELECT 1 pw) present the Database Sizes in KiloBytes
(SELECT 2 pw) present the Database Sizes in MegaBytes
(SELECT 3 pw) present the Database Sizes in GigaBytes
(SELECT 4 pw) present the Database Sizes in TeraBytes
(SELECT 5 pw) present the Database Sizes in PetaBytes (If you ever run with this, email me)


We face this daily at work too, DBCC or Re-Index run into lock timeouts/blocks

What we can do (there's no one solution)

Check the blocking query (we have job to save lock/deadlock every
minute), investigate root cause, and fix if possible
Change timing of Re-Index job if possible
For SQL Job, set Retry
Check Ola's dbo.CommandLog table (used by
IndexOptimize) to see the error -> from there we can copy command
manually to re-run, or use procedure to re-run the "failed commands"
only


Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Your query is pretty much the optimum. Syntax won't get much shorter, query won't get much faster:
SELECT name
FROM   spelers
WHERE  name LIKE 'B%' OR name LIKE 'D%'
ORDER  BY 1;

If you really want to shorten the syntax, use a regular expression with branches:
...
WHERE  name ~ '^(B|D).*'

Or slightly faster, with a character class:
...
WHERE  name ~ '^[BD].*'

A quick test without index yields faster results than for SIMILAR TO in either case for me.
With an appropriate B-Tree index in place, LIKE wins this race by orders of magnitude.
Read the basics about pattern matching in the manual.
Index for superior performance
If you are concerned with performance, create an index like this for bigger tables:
CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

Makes this kind of query faster by orders of magnitude. Special considerations apply for locale-specific sort order. Read more about operator classes in the manual. If you are using the standard "C" locale (most people don't), a plain index (with default operator class) will do.
Such an index is only good for left-anchored patterns (matching from the start of the string).
SIMILAR TO or regular expressions with basic left-anchored expressions can use this index, too. But not with branches (B|D) or character classes [BD] (at least in my tests on PostgreSQL 9.0).
Trigram matches or text search use special GIN or GiST indexes.
Overview of pattern matching operators

LIKE (~~) is simple and fast but limited in its capabilities.
ILIKE (~~*) the case insensitive variant.
pg_trgm extends index support for both.

~ (regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.

SIMILAR TO is just pointless. A peculiar halfbreed of LIKE and regular expressions. I never use it. See below.

% is the "similarity" operator, provided by the additional module pg_trgm. See below.

@@ is the text search operator. See below.

pg_trgm - trigram matching
Beginning with PostgreSQL 9.1 you can facilitate the extension pg_trgm to provide index support for any LIKE / ILIKE pattern (and simple regexp patterns with ~) using a GIN or GiST index.
Details, example and links:

How is LIKE implemented?

pg_trgm also provides these operators:

% - the "similarity" operator
<% (commutator: %>) - the "word_similarity" operator in Postgres 9.6 or later
<<% (commutator: %>>) - the "strict_word_similarity" operator in Postgres 11 or later

Text search
Is a special type of pattern matching with separate infrastructure and index types. It uses dictionaries and stemming and is a great tool to find words in documents, especially for natural languages.
Prefix matching is also supported:

Get partial match from GIN indexed TSVECTOR column

As well as phrase search since Postgres 9.6:

How to search hyphenated words in PostgreSQL full text search?

Consider the introduction in the manual and the overview of operators and functions.
Additional tools for fuzzy string matching
The additional module fuzzystrmatch offers some more options, but performance is generally inferior to all of the above.
In particular, various implementations of the levenshtein() function may be instrumental.
Why are regular expressions (~) always faster than SIMILAR TO?
The answer is simple. SIMILAR TO expressions are rewritten into regular expressions internally. So, for every SIMILAR TO expression, there is at least one faster regular expression (that saves the overhead of rewriting the expression). There is no performance gain in using SIMILAR TO ever.
And simple expressions that can be done with LIKE (~~) are faster with LIKE anyway.
SIMILAR TO is only supported in PostgreSQL because it ended up in early drafts of the SQL standard. They still haven't gotten rid of it. But there are plans to remove it and include regexp matches instead - or so I heard.
EXPLAIN ANALYZE reveals it. Just try with any table yourself!
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO 'B%';

Reveals:
...
Seq Scan on spelers  (cost= ...
  Filter: (name ~ '^(?:B.*)$'::text)

SIMILAR TO has been rewritten with a regular expression (~).
Ultimate performance for this particular case
But EXPLAIN ANALYZE reveals more. Try, with the afore-mentioned index in place:
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ '^B.*;

Reveals:
...
 ->  Bitmap Heap Scan on spelers  (cost= ...
       Filter: (name ~ '^B.*'::text)
        ->  Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ...
              Index Cond: ((prod ~>=~ 'B'::text) AND (prod ~<~ 'C'::text))

Internally, with an index that is not locale-aware (text_pattern_ops or using locale C) simple left-anchored expressions are rewritten with these text pattern operators: ~>=~, ~<=~, ~>~, ~<~. This is the case for ~, ~~ or SIMILAR TO alike.
The same is true for indexes on varchar types with varchar_pattern_ops or char with bpchar_pattern_ops.
So, applied to the original question, this is the fastest possible way:
SELECT name
FROM   spelers
WHERE  name ~>=~ 'B' AND name ~<~ 'C'
    OR name ~>=~ 'D' AND name ~<~ 'E'
ORDER  BY 1;

Of course, if you should happen to search for adjacent initials, you can simplify further:
WHERE  name ~>=~ 'B' AND name ~<~ 'D'   -- strings starting with B or C

The gain over plain use of ~ or ~~ is tiny. If performance isn't your paramount requirement, you should just stick with the standard operators - arriving at what you already have in the question.


How to set a flag in SQL, when there is a change in value

It depends on what you want to do based on that flag.

If you only care that the row has "changed" at some point, then a flag might be enough.
If you care when the change happened, then a [nullable] timestamp might be more useful.
If you care if the change happens more than once, then you need a whole new table to record all of those changes, possibly even with "before" [and "after"] values.

In any case, the answer is the same - a Trigger.


How to mark a Sybase ASE DB as offline for startup

Thanks to Learning_DBAdmin for correcting me: the database does not have to be offline, the load process was failing due to remaining connections of other users on this target database. After killing the offending connections and putting the database in single user mode, it was possible to load a dump into it.


SQL Server 2014 uses high memory at startup

It's not unusual for SQL Server to have rather high memory utilization, even when the server is idle.

SQL Server will increase memory utilization up to the configured maximum--then hold on to that memory, even if it is not being actively used. This increase in memory usually corresponds to data being read from disk, into memory, and held in cache (thus improving the performance the next time that data needs to be used). The only time you might see SQL Server release memory is if the system is under extreme memory pressure and SQL Server is also idle.

The default configuration for SQL Server is to use a minimum of 16 MB, and a maximum of 2147483647 MB (ie, unlimited). Under this configuration, you are essentially allowing SQL Server to self-manage it's own memory.

To see how your instance of SQL Server is configured, you can run this in Management Studio to see the minimum & maximum memory configuration:

EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'min server memory (MB)'
EXEC sp_configure 'max server memory (MB)'

If you wanted to configure SQL Server to allocate 128MB of memory immediately on startup, and max out at 1024MB, you would use this script:

EXEC sp_configure 'min server memory (MB)',128
EXEC sp_configure 'max server memory (MB)',1024


Constant writing operations on google cloud database

For a university course in Data Visualisation, I used the Prometheus/Grafana stack  (great s/ware) to present images of various database metrics while running various HammerDB benchmarks (also an excellent tool).
As part of this, to provide a baseline, I looked at my (single) server "at rest" and noticed (as you have) that there was activity - albeit a very small amount (~ 10 writes/second) - on an "inactive" server.
Basically, this is "house-keeping" functionality which happens even if there is no ongoing external DDL or DML.
There is an interesting article here and here (more recent and probably more accurate) showing the impact of the number of idle connections on the NOTPM (No. of Transactions Per Minute) results from the DBT2 benchmark tool.
As you can see from the results displayed in the second article, there is a small (but not non-existent) effect from just having idle connections.
You say that you are running a cluster - I would imagine that there is a  communications overhead there with nodes pinging each other even in the absence of any serious workload (or even any workload at all).
You don't mention what cluster (NDB? Galera?), but I'm sure that a delve into the code (way above my pay grade... :-) ), a search, or a question on your cluster's architecture/guru list would be able to provide more detail.
If you're interested and have the time and the inclination to test what's going on, you may like to leave your "inactive" cluster running for a while (say over a weekend?) and run (some variant of) this query before and after you begin the test (adapted from here):
SELECT
     table_schema AS the_db,
     table_name AS the_table,
     round(((data_length + index_length) / 1024 / 1024), 2) `size_in_mb`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

You might like to include the information_schema (and the mysql schema) as well since some/much/all of this activity that you are seeing might be occurring there.
This would presumably be dependent on the architecture of your cluster. Inserting the results of these queries into tables of their own should render any comparisons which you may wish to do relatively trivial.
Also, bear in mind that there may not be a whole lot to see, as these writes may simply be UPDATEs of a single or just a few records?
p.s. welcome to the forum and +1 for an interesting question!


how to transfer data from table to table

following INSERT INTO statement:

INSERT INTO TABLE2 SELECT * FROM TABLE1

Now suppose you do not want to copy all the rows, but only those rows that meet a specific criteria. Say you only want to copy those rows where COL1 is equal to "A." To do this you would just modify the above code to look like this:

INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'

See how simple it is to copy data from one table to another using the INSERT INTO method? You can even copy a selected set of columns, if you desire, by identifying the specific columns you wish to copy and populate, like so:

INSERT INTO TABLE2 (COL1, COL2, COL3)
SELECT COL1, COL4, COL7 FROM TABLE1

The above command copies only data from columns COL1, COL4, and COL7 in TABLE1 to COL1, COL2, and COL3 in TABLE2.


SSAS MD dimension joins to same table twice (but processing fails as attribute has 2 names for same ID)

You have a product dimension using all 3 tables. The dimension key is DimSellableItemVersion.SellableItemId and you can use SellableItemAvailability.Id two times as an attribute, once for the product and once for the parent-product. But SSAS does not know which is which or when you want to use it only once, which on you mean. Do you want to go left or right along your foreign-key-circle.

When you want both SellableItemAvailability instances then I would use the data source view to create a second copy of the table SellableItemAvailability. Then you can break the circle and the dimension can work.


Converting old SQL Server outer join syntax

I just quickly grabbed your query and re-wrote it without looking at how you rewrote it.

SELECT  DISTINCT
   a_stmt.cyc_dte,
   a_carr.carr_nm,
   a_stmt.amt_du_ic
 FROM  a_stmt
    LEFT OUTER JOIN a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id
    INNER JOIN a_carr on a_carr.acna = a_stmt.acna
    INNER JOIN s_dataccs on s_dataccs.acna = a_stmt.acna
WHERE (a_icc.usg_ind = 'S') and
(s_dataccs.user_id = @user_id)  and
( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND
( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND
( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND
( a_stmt.amt_du_ic < 0 ) ;

It seems like you have the same thing as me (or at least basically), so I would say you are good.  Obviously if you could test that would be best but I feel comfortable that it is the same thing logically.


Difficult Query: Can Only Modify The WHERE Clause

Count the number of rows for every distribution and compare that to the number of rows returned for a distribution where you apply your filter.

declare @T table
(
  Distributor char(3),
  Reseller varchar(10),
  State char(2)
);

insert into @T values
('ABC', 'BestBuy',    'CA'),
('ABC', 'RadioShack', 'NJ'),
('XYZ', 'BestBuy',    'CA'),
('XYZ', 'RadioShack', 'MO');

select T1.Distributor
from @T as T1
where (
      select count(*)
      from @T as T2
      where T1.Distributor =T2.Distributor
      ) =
      (
      select count(*)
      from @T as T3
      where T1.Distributor =T3.Distributor and
            (
            T3.Reseller = 'BestBuy' and T3.State='CA' or
            T3.Reseller = 'RadioShack' and T3.State='NJ'
            )
      );

select T1.Distributor
from @T as T1
where (
      select count(*)
      from @T as T2
      where T1.Distributor =T2.Distributor
      ) =
      (
      select count(*)
      from @T as T3
      where T1.Distributor =T3.Distributor and
            (
            T3.Reseller = 'BestBuy' and T3.State='MO' or
            T3.Reseller = 'RadioShack' and T3.State='NJ'
            )
      );


Error 13141 in mongos shell while trying to write to a result of the mapReduce invocation to a sharded collection

As at MongoDB 3.2, the only supported key for a sharded output collection for Map/Reduce is the _id field (non-hashed).

There are known issues with Map/Reduce output to a sharded collection using a hashed shard index; the two features don't play well together yet and this isn't a supported combination. The documentation currently only suggests that _id can be used, however there could be an explicit note that hashed indexes are not supported yet.

There's a relevant Jira which you can watch/upvote: SERVER-16605: Mapreduce into sharded collection with hashed index fails.

  What can be the cause of this error?

I believe your specific chunk map error is likely due to the hashed index and sharded Map/Reduce both assuming they should create the initial chunks for an empty collection.

When you create a Shard a Collection Using a Hashed Shard Key:

  If you shard an empty collection using a hashed shard key, MongoDB automatically creates and migrates empty chunks so that each shard has two chunks. To control how many chunks MongoDB creates when sharding the collection, use shardCollection with the numInitialChunks parameter.

With a Sharded Collection as Output, Map/Reduce also tries to create the initial chunks:

  For a new or an empty sharded collection, MongoDB uses the results of the first stage of the map-reduce operation to create the initial chunks distributed among the shards.


Can I move / Modify the Object in Object Storage Service (OSS)

The stored files in OSS do not have a directory structure, and the OSS directory structure we see is a simulation with “/“.

If a file on OSS (oss:/bucket/123/456/789.jpg/)  ends with “/“, it is recognized as a directory.

That is why moving an object follows the same principle with renaming an object: you need to manually upload the object to the new path location, and delete the old file (to delete the directory, first delete the files under it).

For more details click here


Postgresql, escape character in string_agg function

The doubled double-quotes ("") are the necessary consequence of " being the default quote character for COPY with CSV format. The manual:

  QUOTE

  Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

  ESCAPE

  Specifies the character that should appear before a data character that matches the QUOTE value. The default is the same as the QUOTE
  value (so that the quoting character is doubled if it appears in the
  data). This must be a single one-byte character. This option is
  allowed only when using CSV format.

You could use something like (assuming Postgres 9.1 or later):

COPY (...) TO stdout (FORMAT CSV, QUOTE '\');

Then double-quotes are not escaped.

To ...

  add escape character \ just for fields param_name and value

.. you could simply replace():

string_agg(distinct '{name:"' || replace(param_name, '"', '\"') || '",value:"' || replace(value, '"', '\"') || '"}',',') as "params"

Or:

string_agg(distinct format('{name:"%s",value:"%s"}', replace(param_name, '"', '\"'), replace(value, '"', '\"'), ',') as "params"

But string_agg() has nothing to do with either. And I am not sure what you are trying to achieve.


Oracle generated column aliases different on two DB's

It turns out there's a parameter called cursor_sharing that was set to FORCE instead of EXACT

select nvl(1,0.0) from DUAL;

The query above returns the following depending on the value of the parameter:

FORCE=NVL(1,0)
EXACT=NVL(1,0.0)


Why is pg_stat_activity.query_start null for almost all queries?

The only meaning I know of for a NULL query_start field is that no query has ever been started on that connection.


Page Break is splitting in the middle of a single row in SSRS

@matt you might want to adjust the tablix or row KeepTogether property. This affects how it tries to have all rows/table on the same page.


Copy Data To Different Server using SELECT * INTO?

You might be interested in FEDERATED table engine. It allows a local server to see tables in different server. I don't recommend it in normal usage, but to move table data around, it could be an option.

http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html


Challenge with modelling a basic membership table

Approach 5:

is_in_room_now TINYINT DEFAULT NULL, -- always either 1 or NULL
UNIQUE KEY (user_name,chatroom_id,is_in_room_now)

Nulls are not equal to each other, so you can have infinitely many rows with "the same" (user_name, chatroom_id, NULL) without violating the unique constraint... but you can only set is_in_room_now to 1 for exactly one (user_name, chatroom_id, 1) row.

When the user is removed from the room for whatever reason, set the value to null (not 0).


Optimizing a query so MySQL will use the indexes for the joins

Here are my notices:

Make sure that creator exists only in table1, or add [... WHERE table1.creator=..]
It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key.
From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned 'long list of fields..'). Try to execute the query by selecting one field only, or be select count(*) and see if there is a significant change
Note that 400*270=108000, which is not a tiny number of scanned rows :)


Collect all Similar Persons to One Group

It will be better to make such calculations in iterative manner.

The "issue" of such solution is that, in case of large source table, the output table will contains all the rows. But, actually, this is the benefit also: in case of large source table, such code will work much more faster, that recursions.

if object_id('tempdb..#src') is not null drop table #src;
create table #src (id1 int not null, id2 int)

if object_id('tempdb..#rez') is not null drop table #rez;
create table #rez (id int not null, chainid int not null);

declare @chainId int;

insert #src
values (10, 20), (10, 30), (30, 30), (10, 40), (50, 70), (60, 50), (70, 70)
--values (4457745,255714),(4457745,2540222),(2540222,4457745),(255714,4457745)

set @chainId = 1

while 1 = 1
begin
    insert #rez(id, chainid)
    select top 1 id1, @chainId
    from #src
    where
        Id1 NOT IN (select Id from #rez)
        and id2 NOT IN (select Id from #rez)

    if @@rowcount = 0 break

    while 1 = 1
    begin
        insert #rez(id, chainid)

        select id1, @chainid
        from #src
        where
            id2 in (select id from #rez where chainid = @chainId)
            and id1 not in (select id from #rez where chainid = @chainId)

        union

        select id2, @chainId
        from #src
        where
            id1 in (select id from #rez where chainid = @chainId)
            and id2 not in (select id from #rez where chainid = @chainId)

        if @@rowcount = 0 break
    end

    select @chainId = @chainId + 1
end

select [NewId] = chainid, OldID = id
from #rez
order by 1, 2;

In some details, the "logic" of the script is the following:

set new ID (chainid) initial value (ex. 1)

get the first entry which was not found yet (external cycle)

find and store all related to it (in both columns) (internal cycle)

increment the new ID
find next entry which is not in the result table
continue till have data to work

return sorted result


Dropping/creating procedure causes transaction log to empty

SQL Server acts like simple mode until you've established the log chain. This means that it will be happy to discard log information from already-committed transactions.

Paul Randal goes into a little more detail about the mechanics behind this here, but basically, this behavior will stop once you start proper backups:

New script: is that database REALLY in the FULL recovery mode?


A common mistake is

SELECT ... SUM(a.foo) ...
    FROM a
    JOIN b ON ...
    WHERE ...
    GROUP BY a.id

When you JOIN two tables, you usually get an explosion of the number of rows.  The SUM is then done, probably summing up a.foo repeatedly, leading to the 'wrong' (too big) answer.

But that was not what you asked.

A JOIN is almost always executed this way:

Pick a table to start with.  Apply any filtering (WHERE).  So far, the single-table rules apply.
Move on to another table.  But this time, use "Nested Loop Join".  This means "foreach row from step 1, reach into table 2".

So, the new question you are asking is how to reach into table 2?  There is an ON clause (assuming it is not a "cross join").  That gives you one (or more) field that should be used in the 'best' index for table 2.  Then use that field, plus any other fields in WHERE, together with the single-table rules to construct the optimal index for that JOIN.

Since the optimizer might pick a different order of tables, or might like to have multiple choices, find the best indexes for each order.

Then simplify.

INDEX(x), INDEX(x,y) --> INDEX(x,y)
Don't have indexes with more than, say, 5 fields.
If you are joining to the PRIMARY KEY of table 2, you don't need a longer index.
Consider "covering" indexes.  (EXPLAIN will say "Using index", not to be confused with "Using index condition", which is ICP.)

Here are some tips on how the optimizer thinks when joining two tables:

A table with a WHERE filter will be chosen before one without.
Other things being 'equal', the smaller table will be chosen first.
Sometimes an ORDER BY will be preferred over a WHERE in ordering the tables.
LIMIT is rarely a factor, although it probably should be.

My cookbook also focuses on single-table; maybe this answer will help me write about JOINs.


Why do we need to compare keys from different table?

What is the student and registration? Are these tables? I used to plural 's': student_s_, table_s_, to mean a set of objects, which the tables actually are. As of not using s.id = r.sid, it gives you two records (objects), where one refers the other. I guess that r.sid is a foreign key, referencing a student. You then check if two registrations are for the same course at the same semester, then you select that course. By s1.sid < s2.sid, you exclude the same student (which obviously always takes courses with himself) and also exclude duplicates. The duplicates appear because select s1 from students and select s2 from studentds gives you all pairs of students, which means that both ('Joe Bull', 'Sarah The Terrible') and ('Sarah The Terrible', 'Joe Bull') show up. Why duplication in this case?

As of why to check s1.sid = r1.sid look at the CROSS JOIN (aka Cartesian product) filtered with WHERE clause and INNER JOIN. It turns out to be the same thing.

I also believed that there is a computational difference between taking Cartesian product and then filtering it with where but it turns out that modern SQL servers realize that it is just and a join operation and accomplish it in linear N+M time rather than N*M time.


When using DAC, receiving the "[…] the maximum number of '1' dedicated administrator connections already exists" message in error log

After working up through several layers of Microsoft support, it was discovered that the solution to this issue was to change " pooling=false" to "Pooling=False" It is case sensitive.


How to identify unused databases on my sql server

Sorry, SQL Server does not track database use. People have come up with not-so-clever ideas like a logon trigger that checks the default database of the login and/or ORIGINAL_DB_NAME(). But this misses all kinds of things like contained users, people who later issue a USE command, cross-database queries, etc.

My recommendation is SQL Server Audit if you have an appropriate edition (your question is light on details). Stealing from my own post on this topic:

USE master;
GO
CREATE SERVER AUDIT Test_Server_Audit
    TO FILE ( FILEPATH = 'C:\Audits\' );
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = ON);
GO

USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT ON Person.Address BY PUBLIC)
    WITH (STATE = ON);
GO

SELECT *
    FROM Person.Address;
GO

SELECT *
    FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);
GO

USE AdventureWorks;
GO
ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

If you don't have Enterprise Edition there is a slightly less reliable way to do this, and that is to check the index usage statistics DMV. You'll need to check this often and potentially for a long time to be sure you're not missing anything crucial, as it only contains data since the last restart of SQL Server.

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

;WITH agg AS
(
    SELECT
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT last_user_seek, NULL FROM agg
    UNION ALL
    SELECT last_user_scan, NULL FROM agg
    UNION ALL
    SELECT last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);

(There are slightly cleaner ways to write that syntax, but I'm lazy and borrowing from code I wrote more than six years ago.)

Caveat Emptor

You can use these methods to determine potential candidates this way, but you still can't be sure that someone won't try to use the database tomorrow, at the end of the next business cycle, etc. So please don't just rush to drop databases that don't show up in the audit or the DMV for a few days - this really isn't a good indicator that the database is no longer being used. When you think you've found candidates to drop, please don't drop them. Take a proper, full backup; store it on another machine; then set those databases to OFFLINE:

ALTER DATABASE whatever SET OFFLINE;

Now, if someone's application suddenly breaks, or a developer lost his work, or the CFO can't run her report, you can fix it with a single statement instead of having to restore a backup or simply telling them "tough luck" because you don't even have a backup:

ALTER DATABASE whatever SET ONLINE;


Why can a differential backup not be copy-only?

It can be specified in TSQL but the documentation states

  COPY_ONLY has no effect when specified with the DIFFERENTIAL option.

There's no need for copy only differential backups because you can just take a normal differential backup.

Taking a differential backup doesn't break anything as each such backup is cumulative and contains all changes since the last full backup.

If you were to take a full backup without copy_only that would potentially be problematic as it resets the differential base (clears the bitmaps containing information about which extents have changed).


Creating one stored procedure for all CRUD operations

I would strongly advise against this route. While it seems advantageous to only have 7 stored procedures rather than 28, it will turn into a troubleshooting, tuning, and maintenance nightmare.

I don't know what dbms you are using, but the advice should apply to all of them.

From a troubleshooting and usage analysis standpoint you will have a hard time.  Most reporting you get from your dmvs doesn't show the parameters the user submitted to execute the stored procedure. You are going to have to capture them yourself or run traces to see it in real time. This makes it incredibly difficult to determine how these stored procedures are being used, and how the users are using the system.  Are they reading from the db more than inserting or deleting? Without digging deep you won't know. If you break them out into different stored procedures you can see which ones are used when and how often.
From a maintainability standpoint you would think that less files would be better, but it actually makes it worse. Now, whenever you want to change something to the insert portion of a stored procedure you are touching the part that inserts, updates and deletes, which means that they are all added to the regression testing scope. It also means that when you deploy new stored procedure code you'll  impact people that are utilizing that table no matter what they are doing.
From a tuning standpoint you are actually working against most dbms query optimizers. The optimizers will look at the data in the tables needed and try to determine the best way to retrieve data from them.  The issue here is that the operation is not predictiable because your operation relies on parameter sniffing.  Also, the strategy of 1 stored procedure to rule the table makes a few assumptions. Mainly that the way that you select, insert, update, and delete data will always be the same.  What happens when that changes? You won't want to keep adding because it won't be easy to navigate. It's also hard to determine which indexes to make being that it's extremely difficult to determine how the optimizer is going to react.

From a usability standpoint, it makes things difficult. When you insert you'll need every column passed in as a parameter, but when you select it will only be the id you are filtering on, when you update it may only be the column you want to update and you have to make the query understand this. This normally leads to dynamic sql which is its own nightmare.

The long and short of it is that stored procedures are really good for doing very specific things. The more specific the better most of the time. While this doesn't lead to code reusability and a lot more stored procedures to work with, it makes things a lot easier to troubleshoot, maintain and tune along your applications growth. It is also more intuitive to developers that need to use the database.

If you do want to go this route, the only real way to do it would be if statements trying to sniff our what a user wanted to do based on parameter:

IF @action = 'insert'
 INSERT INTO......

I still would not recommend this though.


Take a Column, Split it into New Columns

Worked with an example of CB column with maximum character size of 3 sqllite(WeB),just a demonstration how to split characters.

   CREATE TABLE TABLE1 (ID number,CB text(3),CB2 text(1),CB3 text(1),CB4 text(1));

INSERT INTO TABLE1 VALUES(1,'ABC','','','');
INSERT INTO TABLE1 VALUES(2,'DE','','','');

  select id,
CASE
 WHEN LENGTH(CB) >= 1 THEN SUBSTR(CB,1,1)
 END CB,

CASE
 WHEN LENGTH(CB) >= 2 THEN SUBSTR(CB,2,1)
 END CB2,

CASE
 WHEN LENGTH(CB) >= 3 THEN SUBSTR(CB,3,1)
 END CB3

from
table1

Note:Not a solution,you can work out from this idea.

Updating on same table

   --table creation
CREATE TABLE TAB1 (ID integer,CB text(5),CB1 text(1),CB2 text(1),CB3 text(1),CB4 text(1));
 --- Insert sample rows
 INSERT INTO TAB1 VALUES(1,'ABCDE','','','','');
 INSERT INTO TAB1 VALUES(2,'DE','','','','');
 INSERT INTO TAB1 VALUES(3,'KLM','','','','');

---update rows
  UPDATE tab1
  SET
    cb  = CASE WHEN length(cb) > 1  THEN substr(cb,1,1)  END,
    cb1 = CASE WHEN length(cb) >= 2  THEN substr(cb,2,1) END,
    cb2 = CASE WHEN length(cb) >= 3  THEN substr(cb,3,1) END,
    cb3 = CASE WHEN length(cb) >= 4  THEN substr(cb,4,1) END,
    cb4 = CASE WHEN length(cb) >= 5  THEN substr(cb,5,1) END
  WHERE id IN ( SELECT id FROM tab1);

If you have two tables just join by id(inner) and update target table by using case statement


Difference Between SELECT and select in oracle sql

There is nothing specific about SELECT here. There is no semantic difference between SELECT, select and SeLeCt.

The issue being described is that if you issue these three semantically identical statements:

 SELECT thing FROM mytable;

 select thing from mytable;

 Select Thing From MyTable;

the database treats them as three entirely different entities, that will each occupy some space in the library cache. They will each have been parsed individually, costing CPU each time, even if the resulting execution plan are are identical.

Same thing if you have two identical queries but one with a comment and another without - they will be treated as different statements entirely by the engine, and each consume resources. (I believe even spacing matters.)

(This is actually helpful for debugging. If you're trying different settings to optimize a query without changing the query text, add a comment describing the current settings each time you run it. You're guaranteed a re-parse, and can find the individual runs in [g]v$sql easily with those comments. This wouldn't work if the engine "normalized" the query text before parsing.)

What this means for you is that you should be consistent with your query syntax. Choose a "coding" style and stick with it to avoid littering the various caches with semantically identical queries.

Example: notice the three different SQL IDs.

select a from foo;
Select a From foo;
SELECT a FROM foo;

select * from v$sql where sql_text like '%foo%';

SQL_ID        SQL_TEXT
------------- -------------------
dxdhrwqjpn6b7 Select a From foo
0dqb3y3bask98 select a from foo
1y13x9j2rwzuz SELECT a FROM foo
...


If you've only got two instances just run something like the following against each and compare...

SELECT @@SERVERNAME AS ServerName
    ,j.job_id AS JobID
    ,j.NAME AS JobName
    ,j.enabled AS JobEnabled
    ,j.description AS JobDescription
    ,j.date_created AS JobCreated
    ,j.date_modified AS JobLastModified
    ,l.NAME AS JobOwner
    ,ss.schedule_id AS ScheduleID
    ,ss.enabled AS ScheduleEnabled
    ,sjs.next_run_date
    ,sjs.next_run_time
FROM msdb..sysjobs j
LEFT JOIN master.sys.syslogins l ON j.owner_sid = l.sid
LEFT JOIN msdb..sysjobschedules sjs ON sjs.job_id = j.job_id
LEFT JOIN msdb..sysschedules ss ON ss.schedule_id = sjs.schedule_id

Maybe it's just me but it sounds like one of those things where you need to just grab the data from each and look at it. It doesn't sound like you have specific enough definitions of the term 'similar' to write a sure-fire script.


If you want to return both the total number of logins and the total number of sessions that are less than 10 minutes, then you can get the result using your current query with some minor changes.

You can move the where clause filter inside of a CASE expression to get the count of the sessions less than 10 minutes. Then just add a second count() to get the total number of sessions for each username.

select username,
  count(case when duration<=600 then username end) as `<= 10 min`,
  count(username) TotalLogins
from logins
group by username
order by username asc
limit 10;

See SQL Fiddle with Demo.  You could also use the MySQL shorthand IF() syntax instead of the CASE expression:

select username,
  count(if(duration<=600, username, null)) as `<= 10 min`,
  count(username) TotalLogins
from logins
group by username
order by username asc
limit 10;

See SQL Fiddle with Demo. Both queries will give a result:

| USERNAME | <= 10 MIN | TOTALLOGINS |
|    user1 |         4 |           6 |
|    user2 |         3 |           4 |
|    user3 |         1 |           5 |
|    user4 |         4 |           9 |


Reclaiming unused space from datafile in oracle

Prior to resizing, you have to remove fragmentation in the datafile. That means every segment (sets of blocks assigned to each object in that tablespace) needs to bring together all rows inside their db blocks.

For this you have several options:

If you're on 11gR2 you can use

ALTER TABLESPACE <TABLESPACE_NAME> SHRINK SPACE KEEP 700M;

That will remove the fragmentation and shrink the associated datafiles plus it will set the HWM in the "lowest" possible position.

CAUTION: This is a n I/O intensive operation, never do it during business hours or outside a maintenance window for production environments.

Another option that can be used is the creation of a "backup as copy" of the fragmented datafiles and then switch the database to point those "image copies". Then drop the original and redo using the image copy as the original and backing them up "as copy" to the original location. It's a little longer and some more complex, but the "downtime" is just a few seconds while the switch is performed.

Refer to the Oracle Database 11.2 RMAN Reference Manual for more details on this option.

Hope this helps you.

If not, please add more detail to the question. Like version of the software, maintenance window time, accepted downtime, etc.


Accidental deletes at SQL Server Subscriber

Maybe I can use the deny_db_writer role at the subscriber.
  But I believe sysadmins will still be able to run the deletes.

Yes.  But they are sysadmins.  And so are responsible for their own "resume-generating-events".  If there are sysadmins who aren't trusted DBAs who should know better, then you'll need to reduce their permissions.

The right answer here is to prevent the data modification at the subscriber through security.  You can run

deny delete to public

This will override any permissions and prevent deletes database-wide for all users (public is the 'everyone' role in a SQL Server Database).

Or you can limit this to a single schema:

deny delete on schema::dbo to public

Which would allow users to delete from tables located in schemas they own.

With DELETE denied to PUBLIC, only dbo, or a sysadmin (who always connect as dbo), or a stored procedure owned by dbo will be able to delete.


Query fails from application but runs fine in SSMS

Your WHERE clause contains an explicit datatype conversion, which is terrible for performance, and can also cause this type of problem.

This query will probably show you what's wrong:

select serialno AS notVeryDecimalSerialNo
from kshistory_akt h
where try_cast(h.serialno as decimal(28)) IS NULL;

As for possible solutions, try changing the CAST to TRY_CAST - but note that this may lead to results that you don't expect, because TRY_CAST returns NULL instead of a conversion error.

If I were to guess, your problem is that there is a serialno which won't convert to decimal, but that value is not found if you filter on material='11105031'. So if SQL Server filters the material first, your query succeeds, otherwise the conversion fails. For more details, here's a blog post I wrote on the subject.

The performance problem that I mentioned is caused by the CAST in the WHERE clause, which prevents SQL Server from using an index properly. The TRY_CAST won't solve this, it only takes away the actual conversion error. For more on this, check out "sargable" expressions.


Import and Export database in Mongo just like MySQL

If you look inside mongodb/bin folder (ls -l)
You can find a couple of binaries.

The mongoDB has import and export tools.

If you're running mongod locally on the default port, you can just do:

$ ./mongodump

Example: Dumping Everything

$ ./mongodump --host prod.example.com

Example: Dumping a Single Collection

$ ./mongodump --db blog --collection posts

Others like

mongoexport, mongoimport, mongorestore, bsondump etc.

for more http://www.mongodb.org/display/DOCS/Import+Export+Tools


Slow MySQL Database Import with mysqldump and USE .. SOURCE

For your dump command, you could use the following

mysqldump --single-transaction --extended-insert -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Extended inserts are better.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
The Single Transaction option is better for dumping InnoDB tables.

In your my.cnf file, make the following changes temporarily

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_doublewrite

  innodb_doublewrite=0

and also

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

  innodb_flush_log_at_trx_commit = 2

Restart MySQL and then remove these options after your import and restart MySQL again.

Memory Tables

If you're entire database could fit into memory, you could change the ENGINE=InnoDB to ENGINE=MEMORY in your dump sql file for every table or just the tables that are taking the longest to import, import the dump file back into your database and then change the engine back to innodb.

ALTER TABLE name_of_table ENGINE=InnoDB;

mysqlimport command

http://linux.die.net/man/1/mysqlimport

I have used mysqlimport to import data backed up using the tab format option in mysqldump. It has proven faster than importing via the mysql command.

Dump the database using mysqldump

mysqldump --tab=/some_directory/ -u [USERNAME] -p [DBNAME] [TABLENAME]

Import the data into your database.

mysqlimport -u [USERNAME] -p [DBNAME] /some_directory/tablename.sql


How to differentiate between "no child rows exist" and "no parent row exists" in one SELECT query?

I think I managed to get a satisfactory solution using the following two features:

Subselect bound to a column, which allows me to check if a row exists and (importantly) get a NULL value otherwise (e.g. SELECT (SELECT id FROM a WHERE id = 1) as a_id))
Common Table Expressions

Initial data:

CREATE TABLE people
(
  id integer not null primary key,
  name text not null
);

CREATE TABLE thing_types
(
  id integer not null primary key,
  name text not null
);

CREATE TABLE things
(
  id integer not null primary key,
  person_id integer not null references people(id),
  thing_type_id integer not null references thing_types(id),
  name text not null
);

INSERT INTO people VALUES (1, 'Bill');
INSERT INTO thing_types VALUES (1, 'game');

INSERT INTO things VALUES (1, 1, 1, 'Duke Nukem');
INSERT INTO things VALUES (2, 1, 1, 'Warcraft 2');

And the query:

WITH v AS (
  SELECT (SELECT id FROM people WHERE id=<person_id_param>) AS person_id,
         (SELECT id FROM thing_types WHERE id=<thing_type_param>) AS thing_type_id
)
SELECT v.person_id, v.thing_type_id, things.name
FROM
  v LEFT JOIN things
    ON v.person_id = things.person_id AND v.thing_type_id = things.thing_type_id

This query will always return at least one row. In case if both parent table ids are valid and there are some records, I get

person_id  thing_type_id  name
-------------------------------------
        1              1   Duke Nukem
        1              1   Warcraft 2

If either person_id or thing_type_id are invalid, I get one row where name is NULL and either person_id or thing_type_id is NULL:

person_id  thing_type_id  name
-------------------------------------
     NULL              1         NULL

If both person_id and thing_type_id are valid but there are no records in things, I get one row where both person_id and thing_type_id are not NULL, but the name is NULL:

person_id  thing_type_id  name
-------------------------------------
        1              1         NULL

Since I have a NOT NULL constraint on things.name, I know that this case can only mean that there are no matching records in things.


If all tables are myisam, should I use myisam engine instead of default innodb?

Software that uses MyISAM tables is often a sign that you are dealing with amateur quality software.  There have been very few valid reasons to use MyISAM for several years, now... ACID and intelligent crash recovery and foreign key constraints are just a few things that come to mind that MyISAM will never dream of being able to provide.

Turning off InnoDB because you aren't using it is also an exercise in futility and short-sighted at best.  It is essentially just tinkering with your server for no good reason.  Leave that setting, and virtually all of the other settings, at their default values unless you have a specific reason to change them.  Tweaking parameters requires discipline and deliberate, well-informed action.

Oracle has officially deprecated the ability to do this, anyway.  As of now, in 5.6.23, disabling InnoDB still works, but results in a warning that it is a capability that will subsequently be removed... which should tell you something about whether it's a good idea.
http://dev.mysql.com/doc/refman/5.6/en/innodb-turning-off.html


Text string stored in SQLite Integer column?

This is a well known "quirk" of SQLite.

SQLite uses what it calls a dynamic typing system, which ultimately means that you can store text in integer fields - in Oracle, SQL Server and all the other big hitters in the database world, attempts to do this will fail - not with SQLite.

Take a look here:

  SQLite uses a more general dynamic type system. In SQLite, the
  datatype of a value is associated with the value itself, not with its
  container. The dynamic type system of SQLite is backwards compatible
  with the more common static type systems of other database engines in
  the sense that SQL statements that work on statically typed databases
  should work the same way in SQLite. However, the dynamic typing in
  SQLite allows it to do things which are not possible in traditional
  rigidly typed databases.

The advantages of this system are outlined here.

  Note: The datatype limitations can be cumbersome, especially if you
  add time durations, or dates, or things of that nature in SQL. SQLite has
  very few built-in functions for that sort of thing. However, SQLite
  does provide an easy way for you to make your own built-in functions
  for adding time durations and things of that nature,

I think the point of SQLite is a bit like the quote about the C programming language -

  "C allows you to do very stupid things because it also allows you to
  do very clever ones."

Same goes for SQLite.

Check out the entire StackOverflow thread referenced above.

The datatype in SQLite is more of a "hint" than a command.


syntax error on mathematical operation postgresql

@ypercube pointed out the immediate cause for the syntax error. But there is a lot more:

CREATE OR REPLACE FUNCTION pg_temp.atualiza_qtfunc()
  RETURNS trigger AS
$f_at_qtfunc$ --f_at_func = função atualiza quantidade funcionários
BEGIN
   CASE TG_OP
   WHEN 'INSERT' THEN
      UPDATE tabemissora SET qtfunc = qtfunc + 1
      WHERE  some_id = NEW.some_id;  -- reference NEW row
   WHEN 'DELETE' THEN
      UPDATE tabemissora SET qtfunc = qtfunc - 1
      WHERE  some_id = OLD.some_id;  -- reference OLD row
   END CASE;
END
$f_at_qtfunc$ LANGUAGE plpgsql;

Remove the SELECT completely, you can refer to the column itself in the UPDATE statement directly.
Consequently, remove the needless variable declaration altogether.
Don't UPDATE without WHERE condition, that would alter all rows in the table, which is most probably not what you want. io made the command depend on the inserted / deleted row at hand.
I used CASE instead of IF to simplify. Optional.
Don't quote the language name.

Even with the fixed function, your whole approach may be a bad idea in a multi-user environment. Multiple concurrent inserts / deletes for the same parent-ID can produce race conditions that this kind of function is not able to resolve. If that can happen, you'd rather do the count after the fact in a view or materialized view and drop the trigger altogether.


Is there a limit to how many credentials SSMS can remember?

It is still a bug (sql server 2008 - 2014 (version 12.0.2000.8 still affected)) as highlighted below :

SQL Server 2012 Management Studio spuriously forgets password, despite being set to remember it.

You can vote up the connect item to get it HOPEFULLY fixed if you are experiencing the bug as well.

%UserProfile%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

might have a limit but I don't know of any kind of hard limit.

BTW, how big is your sqlstudio.bin file now ?


You shouldn't care enough about the physical column ordering of an existing table that you would want to invite the risks involved with changing it. The only case where this might be a concern is if you're using SELECT * and want that output arranged in a certain way - and you shouldn't be doing that, you should be listing the column names explicitly (and you can then put the columns in any order you want, or create a view that does that).

You can drop and re-create the table, like others have said, with the columns in your preferred order. But this is going to be very disruptive (blocking, I/O) if your table is large, since all of the other users on the system will have to wait for that to complete. If you want to see exactly what Management Studio does, create this table:

CREATE TABLE dbo.foo(b INT, a INT, r INT);

INSERT dbo.foo(b,a,r) VALUES(1,2,3),(4,5,6);

Now, in Object Explorer, right-click the table and choose Design. Select the box to the left of the column name that you want to reorder. Drag the column to another location within the table. I moved a above b because I'm OCD and wanted the columns in alphabetical order:

Then I clicked the "Generate Change Script" button, and here is the monstrosity that it created (and note this is a heap with no keys, constraints, triggers etc. - this re-create can get pretty complex).

/*
   To prevent any potential data loss issues, you should review
   this script in detail before running it outside the context
   of the database designer.
*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_foo
    (
    a int NULL,
    b int NULL,
    r int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_foo SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.foo)
     EXEC('INSERT INTO dbo.Tmp_foo (a, b, r)
        SELECT a, b, r FROM dbo.foo WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.foo
GO
EXECUTE sp_rename N'dbo.Tmp_foo', N'foo', 'OBJECT'
GO
COMMIT

For fun, let's create something a little more complex:

CREATE TABLE dbo.flarb
(
  a INT PRIMARY KEY,
  b INT NOT NULL CHECK (b>1),
  c ROWVERSION,
  d DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  e UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
);

CREATE TABLE dbo.blarf
(
  a INT NOT NULL FOREIGN KEY REFERENCES dbo.flarb(a)
);
GO

CREATE TRIGGER dbo.flarb_tr
ON dbo.flarb
FOR UPDATE
AS
  SELECT 1;
GO

CREATE VIEW dbo.flarb_v
WITH SCHEMABINDING
AS
  SELECT a FROM dbo.flarb;

Now we try the same thing:

  'flarb' table
   - Error validating the default for column 'e'.
  - Warning: The following schema-bound objects will be modified:
    - View 'dbo.flarb_v': schema binding will be removed.

Oops. Thankfully, we can still proceed to see what the script will look like:

/*
  To prevent any potential data loss issues, you should review
  this script in detail before running it outside the context
  of the database designer.
*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER VIEW dbo.flarb_v

AS
  SELECT a FROM dbo.flarb;GO
ALTER TABLE dbo.flarb
    DROP CONSTRAINT DF__flarb__d__1B7E091A
GO
ALTER TABLE dbo.flarb
    DROP CONSTRAINT DF__flarb__e__1C722D53
GO
CREATE TABLE dbo.Tmp_flarb
    (
    a int NOT NULL,
    b int NOT NULL,
    d datetime NOT NULL,
    c timestamp NOT NULL,
    e uniqueidentifier NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_flarb SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_flarb ADD CONSTRAINT
    DF__flarb__d__1B7E091A DEFAULT (getdate()) FOR d
GO
ALTER TABLE dbo.Tmp_flarb ADD CONSTRAINT
    DF__flarb__e__1C722D53 DEFAULT (newsequentialid()) FOR e
GO
IF EXISTS(SELECT * FROM dbo.flarb)
     EXEC('INSERT INTO dbo.Tmp_flarb (a, b, d, e)
        SELECT a, b, d, e FROM dbo.flarb WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.blarf
    DROP CONSTRAINT FK__blarf__a__1E5A75C5
GO
DROP TABLE dbo.flarb
GO
EXECUTE sp_rename N'dbo.Tmp_flarb', N'flarb', 'OBJECT'
GO
ALTER TABLE dbo.flarb ADD CONSTRAINT
    PK__flarb__3BD0198EF8B8CFAF PRIMARY KEY CLUSTERED
    (
    a
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.flarb ADD CONSTRAINT
    CK__flarb__b__1A89E4E1 CHECK (([b]>(1)))
GO
CREATE TRIGGER dbo.flarb_tr
ON dbo.flarb
FOR UPDATE
AS
  SELECT 1;
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.blarf ADD CONSTRAINT
    FK__blarf__a__1E5A75C5 FOREIGN KEY
    (
    a
    ) REFERENCES dbo.flarb
    (
    a
    ) ON UPDATE  NO ACTION
     ON DELETE  NO ACTION

GO
ALTER TABLE dbo.blarf SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Yeah, that really looks like something I want to run in production, even if the table is small.

The other alternative is to do this quite manually. Say you have this table:

CREATE TABLE dbo.Employee
(
  EmployeeID INT,
  Name NVARCHAR(255),
  Salary INT
);
INSERT ...

Theoretically, and leaving out the complications caused by keys, constraints, triggers, and schemabound objects, you could "change" the order of the columns by - for each column to the left of the one you want to move - creating a new column, moving the data, dropping the old column, and renaming the new column. This works because the new column is always added to the "end" of the table. Leaving out error handling and any of the other complications:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
ALTER TABLE dbo.Employee ADD Nametmp NVARCHAR(255);
UPDATE dbo.Employee SET Nametmp = Name;
ALTER TABLE dbo.Employee DROP COLUMN Name;
EXEC sp_rename N'dbo.Employee.Nametmp', N'Name', N'COLUMN';
COMMIT TRANSACTION;

This of course has the potential to be as disruptive (or worse) as the Management Studio approach of just swapping out the entire table, and gets really complicated if you want to move more than one column.

Now, do you really need to change the column order?


Is Azure Managed Disks enough to ensure high-durability for a database?

But I want to ensure that data that gets stored will not be lost, i.e. is durably stored with very high probability.

Yes.  It could happen, but it's an extremely low-probability event, and there's many other low-probability events that are more likely.  It's vastly more likely that your data will be lost due to a guest OS driver problems, human error, or malware than Azure would lose data on a disk.
See:
LRS provides at least 99.999999999% (11 nines) durability of objects over a given year.

Azure Storage redundancy
And whatever that means, you can bet that a lot of things would have to go very wrong to lose a disk.
If you are happy recovering from backups for those other scenarios, you should be fine.`


How to dump whole database from remote server on sequel pro

You can dump database with SequelPro (Mac OSX) with File > Export (⇧⌘E) option.


Field types database design best practice

Not discounting @gbn's suggestion (in a comment on the question) to possibly store this data somewhere other than in a RDBMS, I will say that if you do decide to go the RDBMS route, you are better off using a single table with a column per each "type" such that they can be strongly-typed. Or, you could use a single table with a single string field as all data can be converted/serialized to a string, but then you have to be careful to do the conversion properly in both directions for all operations, and that might prove problematic if some uses of that table either incorrectly convert or forget to convert altogether.

The reason for choosing a single table is mostly logistics: it is far easier to dynamically select a different column based on a COALESCE or CASE (or CASE-like) clause than it is to dynamically pull from different tables in a query based on the TypeID of the row (at least not without having to LEFT JOIN all "type" tables in all queries (yikes!).

Depending on which RDBMS you are using, there might even be vendor-specific features to assist in this model. For example, SQL Server, starting in version 2008, I believe, has a SPARSE option that can be added to NULLable columns. This allows for a NULL value to take up 0 bytes whereas normally, for fixed-length fields (i.e. pretty much all of them that aren't [N]VAR{something} or {something} VARYING or XML or generically text or SQL_VARIANT), they would always take up their specified number of bytes. The downside is that for non-NULL values, they now take up 4 extra bytes per row. But it can be a huge savings when the column is 75% or more NULL across all rows. This feature was designed specifically to address this type of model.

Just make sure to have a CHECK CONSTRAINT to ensure that for all of the value_ fields, either none, or only one, of them is NOT NULL.

Then you can access via something like:

COALESCE(value_int, value_string, value_datetime, etc.)

And again, if you are using SQL Server (and maybe one or more other vendors have this feature), you can make use of "filtered indexes" to index each specific value_ field where that value is not null:

CREATE NONCLUSTERED INDEX [IX_FieldValues_ValueInt]
  ON dbo.[FieldValues] ([ValueInt])
  WHERE [ValueInt] IS NOT NULL;

This will allow you to target rows that do have values, although probably not when using the generic COALESCE access method, but when targeting a specific Value% field.

AND, looking again at the two options, I would actually go with Option 3, which is a single table. In Option 2 it does not seem like there is much purpose in breaking field_type_id out as that data is not really a parent to the field_value data. A field_id can only have a single value, right? So just put field_type_id in the field_value table. And make use of it in the CHECK CONSTRAINT that ensures a single field at most is NOT NULL, to ensure that the one field, if any, that is NOT NULL matches the field_type_id.


DB2 - Archive Mode - on what values to set LOGPRIMARY,LOGSECOND,LOGFILSIZ?

LOGPRIMARY, LOGSECOND and LOGFILSIZ combine to define the maximum size of the transaction log on your server using the following formula:

Total Size = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096 bytes

In your case, this results in (13 + 15) * 4096 * 4096 = 469762048 bytes.

The maximum transaction log size limits 2 things:

1) the absolute size of a single transaction
2) the "timespan" between the oldest active transaction and the newest transaction.

You need to make it large enough to handle both scenarios, although if you're running into problems relating to #2 then you really have a problem with a poorly-behaved app that isn't committing.

Choosing an appropriate value for LOGFILSIZ should be based on how regularly you want to archive log files, as this will have an effect on your recoverability in the event of a problem.  If your database archives log files only once every hour (or less), you will potentially lose much more data in the event of a serious problem than if your database archives a log file every 2-3 minutes (because every time a log file is archived it should be copied to NetBackup).


Free Drive Space and SQL Server Agent Alerts

This is an OS information. I don't think there will be an Alert in SQL that will track the remaining space on the drives.
Pretty much all of the monitoring products monitor this and can send you alerts when you reach a specific threashold for disk space.
If you do not have any monitoring tool, then I think that a SQL job that check the free space and send an email seems like a good option.
Here's a script I use to see how much space available there is on the drive where I have a database file (it doesn't work for drive that doesn't host any database file)
SELECT Drive,PercentFree FROM (SELECT  VS.volume_mount_point AS Drive,
 CONVERT(DEC(4,2),CONVERT(DEC(18,0),VS.available_bytes)*100.00/VS.total_bytes) AS PercentFree
,ROW_NUMBER() OVER(PARTITION BY VS.volume_mount_point ORDER BY MF.database_id,MF.[file_id]) RN
FROM sys.master_files MF CROSS APPLY sys.dm_os_volume_stats(MF.database_id, MF.[file_id]) VS) as SP WHERE RN=1 ORDER BY SP.Drive


Rows deleted in the subscriber - what to do? - SQL Server Transactional Replication

If you have mistakenly removed a row on the subscriber that should receive an update through replication, the easiest way to fix would be to re-construct the missing row on the subscriber that matches the row that is failing to update.

Once the row is on the subscriber, the update can run successfully (even if it just writes the same value into the desired column), then replication should continue happily.

I have had this same scenario occur in the past and replication continued without issue.


First of all you should find out, which service/process consuming maximum CPU.

You can you Process Monitor tool provided by Microsoft, which give details information.

You can find it here https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx

If it's SQL Server, then

1st I would recommend to use master.dbo.sysprocess as

select * from master.dbo.sysprocesses order by cpu desc

if it returns spid < 50 on top means, it's SQL Server own process not user process. So, on the basis of lastwaittype column value you can change recommended (according the wait type) SQL Server configuration or tune your HW.

If it returns process id >50 means it's user process and you can use below query to find details about the process and tune your query.

if the session is active (running, runable, suspanded)

select
            db_name(sp.dbid),sp.spid,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
            ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
            ,es.status,es.cpu_time,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
            ,es.reads,es.writes,es.logical_reads

from
                sys.dm_exec_requests er
inner join      master.dbo.sysprocesses sp
on              er.session_id=sp.spid
inner join      sys.dm_exec_connections ec
on              er.session_id=ec.session_id
inner join      sys.dm_exec_sessions es
on              ec.session_id=es.session_id
inner join      sys.dm_exec_query_memory_grants qmg
on er.session_id=qmg.session_id
cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp

If the session is not active (sleeping).

select a.spid,db_name(b.dbid) [DB Name],b.text [Query],a.cpu
from master.dbo.sysprocesses a
cross apply sys.dm_exec_sql_text(a.sql_handle) b where status='sleeping'

Thanks


Worse performance after create empty table

It would help to see the slow query, and its EXPLAIN.

Meanwhile, try

ANALYZE TABLE invoice_requests;

There are no useful 'statistics' on an empty table.  InnoDB should have recalculated the stats before you got to this problem, but we may be missing something.

Indexes are a mess:

PRIMARY KEY (`REQUEST_ID`),   -- notes 1,2
UNIQUE INDEX `UNQ_INVOICE_ID` (`INVOICE_ID`),  -- notes 1,3
INDEX `IDX_REQ_CODE_END_TIME` (`REQUEST_ID`, `ENDTIME`),  -- note 2
INDEX `IDX_INV_NUMBER` (`INVOICE_ID`)  -- note 3

Notes:

Why have two unique indexes?  Can't you live with just one?  (Not knowing a "request" from an "invoice", I cannot advise further.)
Don't bother adding an index that starts with the PK's column(s)
A UNIQUE index is an index plus a uniqueness constraint.  So, don't bother adding another index with the same column(s).

Having a bunch of tables like 201805_invoice_requests will be a problem in the long run.  (There are many threads discussing having multiple identical table.)


Determining the optimal Column Widths from a .csv File

Trying to guess appropriate column lengths is best avoided if possible - if you can get the source of the CSV to specify the expected format in more detail (column X will contain no more than Y characters) then try that.

I would suggest initially going for long variable length types - this may be less efficient than more constrained types but will ensure that you do not experence errors in later imports if some fields grow larger than expected (or worse silently lose data because mysql silently truncates something). Under MSSQL text fields would want to be nvarchar(max) or varchar(max) in this case, unless they need to be part of an index in which case they'll need to be more constrained (no index can be over more than 900 bytes of data under MSSQL). Unless you can get more garantees of data format from the source, this is the safest way, though of course you can use a little guesswork (a field containing a national insurance number shouldn't ever be longer than 11 characters for instance, though be careful still as a field may be incorrectly named nad/or overloaded to store things other than what its name suggests).

If you really have no choice but to guess and using larger types feels too inefficient to you then to help the guestimating process import the file(s) you have into a table where all fields are maximum length variable text types, then perform some rudementry analysis such as SELECT MAX(LEN(<field1>)), MAX(LEN(<field2>)) FROM <table> to get indications to base your guesswork on, and for non-string fields you can use more complex pattern matching to determine expected ranges (i.e. field X is always a data in ISO format, field Y is a number between -500 and 45,123 with up to two decimal places, and so forth).

With regard to "optimal": while it may be inefficient to use larger variable-length types where a short fixed length will do, efficient and optimal are not neccesarily the same: being efficient will be little benefit if you expereience errors or lose data due to accidentally setting the type too restrictively.


"No such interface supported" after upgrading SourceSafe project to 2012 SSMS format.

Microsoft has logged this issue:
http://support.microsoft.com/kb/2727824

and has included the fix starting in the following update:

2723749 Cumulative update package 3 for SQL Server 2012

According to Microsoft, this fix should be available in Service packs:

  Microsoft SQL Server 2012 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2012 Service Pack RTM hotfix to an installation of SQL Server 2012 Service Pack RTM. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

So, the latest SQL Server 2012 service pack contains the fix, and it worked for me.