Index fragmentation is much overrated.  Do not worry about it.

Two adjacent, somewhat-empty, blocks are merged together by InnoDB as the natural processing.

Random actions on a BTree cause it to naturally gravitate toward an average of 69% full.  Sure, this is not 100%, but the overhead of "fixing" it is not worth it.

SHOW TABLE STATUS gives you some metrics, but they are flawed -- "Data_free" includes certain "free" space, but not other "free" space.

There is unused space in each block; free 16KB blocks; free "extents" (nMB chunks); MVCC rows waiting to be reaped; non-leaf nodes have their own fragmentation; etc.

Percona and Oracle have different ways of looking at how big (number of blocks) an index is.  I find neither of them to be useful because of the limited definition of "free".  It seems that blocks (16KB each) are allocated in chunks (several MB), thereby leading one to believe that there is all sorts of fragmentation.  In reality, it is usually just most of one of these multi-MB chunks.  And OPTIMIZE TABLE does not necessarily recoup any of the space.

If SQL Server is using BTrees, then it is lying to say that there is "no fragmentation".  Think of what happens on a "block split".  Or think of the overhead of continually defragmenting.  Either way you lose.

Further note that a table and an index are essentially identical structures:

B+Tree, based on some index
The "data" is based on the PRIMARY KEY; each secondary index is a B+Tree based on its index.
The leaf node of the "data" contains all the columns of the table.
The leaf node of a secondary index contains the columns of that secondary index, plus the columns of the PRIMARY KEY.

If you have innodb_file_per_table = ON, you can clearly see the shrinkage (if any) after OPTIMIZE TABLE by looking at the .ibd file's size.  For OFF, the info is buried in ibdata1, but SHOW TABLE STATUS may be reasonably accurate since all "free" space belongs to every table.  Well, except for the pre-allocated chunks.

You may notice that a freshly optimized file-per-table table has exactly 4M, 5M, 6M, or 7M of Data_free.  Again, this is the pre-allocation, and the failure to give you the minute details.

I have worked with InnoDB for over a decade; I have worked with thousands of different tables, large and small.  I say that only one table in a thousand really needs OPTIMIZE TABLE.  Using it on other tables is a waste.

105 columns is a lot, but perhaps not too many.

Do you have 55 indexes on one table?  That is bad.  That is 55 updates per INSERT.  Let's discuss that further.  Keep in mind that INDEX(a) is useless if you also have INDEX(a,b).  And INDEX(flag) is useless because of low cardinality.  (But INDEX(flag, foo) may be useful.)

Q1: There is no good way to check for all forms of fragmentation in either the data or the secondary indexes.

Q2, Q3: OPTIMIZE TABLE rebuilds the table by CREATEing a new table and INSERTing all the rows, then RENAMEing and DROPping.  The re-inserting of the data in PK order assures that the data is well-defragmented.  The indexes are another matter.

Q4: You could DROP and reCREATE each index to clean it up.  But this is an extremely slow process.  5.6 has some speedups, but I don't know if they help with defragmentation.

It is also possible to ALTER TABLE ... DISABLE KEYS, then ENABLE them.  This may to a more efficient rebuild of all the secondary indexes at once.


What exactly is an unstructured data and why to use Non-relational DBMS for that?

Unfortunately the answer to when a NoSQL database system is better than a RDBMS is a little subjective which is why it's highly debated. A lot of misinformation has caused the incorrect connotation that a NoSQL database is faster than a RDBMS, particularly with big data, which isn't necessarily true. This connotation is based on the origination story of NoSQL databases being used by companies who do have big data who were trying to solve a scale problem actually, but not because of scaling performance rather one of flexibility to allow them the ability as developers / infrastructure administrators to scale faster. (In my opinion, with the availability of modern Cloud services and decoupled services like Azure and AWS, that problem is mostly solved in an alternative way now.) But to this day, those same companies, such as Facebook still leverage a RDBMS in addition to a NoSQL database system, for likely similar amounts of data, because it's not a question of database performance, rather of flexibility and using the right tool for the right job dependent on use case.
At a high level, as previously mentioned, NoSQL databases have the advantage of flexibility at the trade off of consistency, which is a trade-off between the ACID Database Principals for eventual consistency. This flexibility allows a NoSQL database to store unstructured, semi-structured, and highly variable structured data; and it allows that database to be distributed easily when horizontally scaling across a multi-node database cluster. Because of the flexibility with the schema of the data it can store, it doesn't have to worry about schema changes in one node vs another node in a multi-node cluster. Additionally, because of its ability to be eventually consistent, it generally doesn't have to worry about having 100% of the same data across every node in the cluster.
Because of its flexibility for scaling horizontally, one can loosely argue (at a high level) that this is where the performance benefit comes from when using NoSQL over a traditional RDBMS. But the other tradeoffs are your ability to transform and query the data become limited when you're dealing with a NoSQL database due to it's lack of a consistent schema. I want to be objective, so I will credit MongoDB with offering a multitude of ways to query the data which I believe are also a little more procedurally programmatic by design, so may be a little user-friendly to a developer who is not very experienced with traditional relational logic. But there's only so much one can do when guaranteed eventual consistency at best as opposed to the actual guaranteed consistency of a RDBMS.
As far as actual use-cases for a NoSQL database, it depends heavily on the schema of the data (or lack thereof) and the developer's specific use cases and types of querying they'll need to support. Some of the examples you've read are potential use cases, such as an Email for example. Emails are semi-structured in schema, as far as I would define them. They have a few consistent fields like the From, SentDateTime, ReceivedDateTime, and Subject but they all have highly variable fields like the To, CC, BCC and the Body. So a NoSQL database could make sense in the scenario where the type of querying you'll ever do has predicates dependent only on the From and/or SentDateTime field. Those would be your keys in your Emails table, and the Body of the message and the To field will be the rest of your entity (maybe in a JSON format) that's stored in the table. (Note the term table is used loosely here for conceptualization.)
Now of course you're saying to yourself "but you can accomplish just the same in a RDBMS" - which is true, but again the flexibility of NoSQL with its eventual consistency allows you to then take the above example table and shard it across multiple nodes more easily when horizontally scaling. So while even some of the main features of a NoSQL database are available in a traditional RDBMS (in a sense I almost see NoSQL being a subset of RDBMS from a features perspective) it's flexibility and the rules it has to follow differ enough from a traditional RDBMS, allowing it a place in the world we live in.
That being said, over time a lot has changed, even NoSQL systems in their original sense have changed a lot and adapted to the progression of the database world (or the ones who haven't die off). The lines between NoSQL and RDBMS continue to blur (for the better) as both grow to accommodate the things they're missing from one or the other. For example, ACID compliant NoSQL databases now exist, and RDBMS databases with much easier horizontal scaling capabilities exist as well.


How to get Column Names list if row IS NOT NULL in PostgreSQL

If your list of columns is small and fixed you could always do:

SELECT *,
       CONCAT_WS(',',
                 CASE WHEN col1_name IS NULL THEN NULL ELSE 'col1_name' END,
                 CASE WHEN col2_name IS NULL THEN NULL ELSE 'col2_name' END)
       AS "colNew_name"
FROM table1

See http://sqlfiddle.com/#!15/a88af8/2


Database behind a Multilingual User Interface

It is possible to store strings with different collations in the same column using SQL_VARIANT:

CREATE TABLE dbo.Localized
(
    text_id     INTEGER NOT NULL,
    lang_id     INTEGER NOT NULL,
    text_body   SQL_VARIANT NOT NULL,

    CONSTRAINT [PK dbo.Localized text_id, lang_id]
        PRIMARY KEY CLUSTERED (text_id, lang_id),
)
GO
INSERT dbo.Localized
    (text_id, lang_id, text_body)
VALUES
    (1001, 2057, N'Database problems' COLLATE Latin1_General_CI_AS);
GO
INSERT dbo.Localized
    (text_id, lang_id, text_body)
VALUES
    (1001, 1025, N'قاعدة بيانات المشاكل' COLLATE Arabic_CI_AS)

This design has several drawbacks (including being limited to 8000 bytes), not least in the search area: SQL_VARIANT cannot be full-text indexed, and some string comparison features (e.g. LIKE) cannot be used directly either.  On the other hand, it is possible to create a regular index on SQL_VARIANT and perform the more basic comparisons (e.g. <, =, >) in a collation-aware fashion:

CREATE UNIQUE INDEX uq1 ON dbo.Localized (text_body)
GO
-- One row
SELECT
    l.*
FROM dbo.Localized AS l
WHERE
    l.text_body = CONVERT(SQL_VARIANT, N'Database problems' COLLATE Latin1_General_CI_AS)

-- No rows (and no collation error!)
SELECT
    l.*
FROM dbo.Localized AS l
WHERE
    l.text_body = CONVERT(SQL_VARIANT, N'Database problems' COLLATE Arabic_CI_AS)

-- One row, index seek, manual version of "LIKE 'D%'"
SELECT
    l.*
FROM dbo.Localized AS l
WHERE
    l.text_body >= CONVERT(SQL_VARIANT, N'D' COLLATE Latin1_General_CI_AS)
    AND l.text_body < CONVERT(SQL_VARIANT, N'E' COLLATE Latin1_General_CI_AS)

We can also write the usual sort of procedures:

CREATE PROCEDURE dbo.GetLocalizedString
    @text_id    INTEGER,
    @lang_id    INTEGER,
    @text_body  SQL_VARIANT OUTPUT
AS
BEGIN
    SELECT
        @text_body = l.text_body
    FROM dbo.Localized AS l
    WHERE
        l.text_id = @text_id
        AND l.lang_id = @lang_id
END
GO
DECLARE @text SQL_VARIANT

EXECUTE dbo.GetLocalizedString
    @text_id = 1001,
    @lang_id = 1025,
    @text_body = @text OUTPUT

SELECT @text

Of course, full-text indexing is also problematic in the "single table for all translations" design, since full-text indexing (all but) requires a language id setting per column.  The multiple table design described by Joop Eggen could be full-text indexed (though it would naturally require one index per table).

The other main option is to have one column per locale in the base table:

CREATE TABLE dbo.Example
(
    text_id     INTEGER NOT NULL,
    text_2057   NVARCHAR(MAX) COLLATE Latin1_General_CI_AS NULL,
    text_1025   NVARCHAR(MAX) COLLATE Arabic_CI_AS NULL,

    CONSTRAINT [PK dbo.Example text_id]
        PRIMARY KEY CLUSTERED (text_id)
)

This arrangement does have a certain simplicity to it, and works well with full-text indexing, though it does require a new column to be added with each new language, and many developers find this sort of structure inelegant and unsatisfactory to work with.

Each of the alternatives has advantages and disadvantages, and will require indirection at some level or another, so it might depend on where the developers concerned feel happiest locating that indirection.  I imagine most people will prefer the multiple-table design for most purposes.


Change SQL Server 2005 from Standard Edition to Workgroup

Standard to Workgroup is a downgrade

This is the BOL page on upgrades

Unfortunately, you'll have to uninstall and reinstall. On the bright side, the master, msdb etc will simply restore into the "lower" version as long the version number is the same.


I found my answers here. The default value is not the same as default constraint. It is created when we create first default constraint and can't be changed.

So, to answer my question - no, it is not possible. We can't change the default value for a column. This magic works only once. After this, we should do regular DML to change values in rows.


How to migrate huge MySQL databases one by one to new server?

I think I found a solution which is close to what I'm trying to achieve: https://dba.stackexchange.com/a/76198/107793.

The only thing I have to look into is, how to avoid the possible downtime with locking the tables. Since I'm on InnoDB exclusively (apart from the MySQL system DBs) that should be possible.


Recommended maximum memory setting for PostgreSQL 9.4+ with Huge Pages

If there are no other applications on the server potentially benefiting from huge pages, just set vm.nr_hugepages to rounded up shared buffers / pagesize. When you start PostgreSQL check its log. If it reports fall back to normal pages, increase it slightly.
But I'm not sure if 16GB RAM (i.e. shared buffers 4-6GB) is enough to see the significant difference. Probably you need to set it and compare.


Postgresql Rename Table without Updating View Definitions

The simple answer - because views reference the OID and not the object name. The name is translated back in the internal function.

A view in PostgreSQL can be thought of as an empty table with a select rewrite rule. Using your example, the internal query tree for the rewrite rule is located in the pg_rewrite.ev_action table/column and there you'll see the references to the individual table and column OIDs.

What I'd recommend doing is enclose the table rename, and a CREATE OR REPLACE VIEW statement within a single transaction, that way everything becomes relatively seamless and the view is recreated pointing to the new table's OID.


Why does MS SQL Server return a result for empty string check when Unicode string is not empty

While I am not sure of the exact reason for those specific characters, t The issue has to do with the older collations (please see UPDATE section at the end). And it is not just empty string that they equate to, but also to just one of those characters:

SELECT * FROM (SELECT N'ግዜ') tab(col) WHERE tab.col = N'ግ';

And if you try a case-sensitive collation, even with multiple characters, they still equate:

SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግግግግ' COLLATE SQL_Latin1_General_CP1_CS_AS;
SELECT * FROM (SELECT N'ግ') t(c) WHERE t.c = N'ዜዜዜዜ' COLLATE SQL_Latin1_General_CP1_CS_AS;
SELECT * FROM (SELECT N'ዜ') t(c) WHERE t.c = N'ግግግግ' COLLATE SQL_Latin1_General_CP1_CS_AS;

Even the "equivalent" Windows Collations have the same issue:

SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_CS_AS;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_CS_AS_KS_WS;

BUT, it seems that newer versions of the Windows Collations (i.e. the 100 series or newer) "fixes" the issue and these no longer equate:

SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_100_CI_AI;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_100_CI_AS;

And, of course, the binary Windows Collations (both older and newer series) work just fine as the following do not report a match:

SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_BIN;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_BIN2;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_100_BIN;
SELECT * FROM (SELECT N'ግዜ') t(c) WHERE t.c = N'ግ' COLLATE Latin1_General_100_BIN2;

UPDATE (2015-08-20)
After 6 hours of pouring through documentation on http://www.unicode.org/, http://site.icu-project.org/, and a couple of other Unicode-related sites, I gave up trying to find evidence of a "weighting" change that might have occurred just prior to 2008 (the new 100 series of collations were introduced in SQL Server 2008). I did, however, find the following info at www.fileformat.info for the two characters being tested here:

ግ = Unicode Character 'ETHIOPIC SYLLABLE GE' (U+130D)
ዜ = Unicode Character 'ETHIOPIC SYLLABLE ZEE' (U+12DC)

So, I moved on to the next project and moments later came across the following on the SQL Server 2008 MSDN page for Collation and Unicode Support:

  SQL Server 2008 has introduced new collations that are in full alignment with collations that Windows Server 2008 provides. These 80 new collations are denoted by *_100 version references. They provide users with the most up-to-date and linguistically accurate cultural sorting conventions. Support includes the following:

  ...
  Weighting has been added to previously non-weighted characters that would have compared equally.

No sort weight for a character means that it is effectively invisible.

Moral of the story: don't try so hard; give up sooner ;-)

UPDATE (2018-09-20)

For a more visual indication of what is going on, the query below compares each BMP character (Code Points 0 - 65535 / U+0000 - U+FFFF) to an empty string. The comparison is repeated using different collations: BIN2, a SQL Server Collations, Latin1_General that started with SQL Server 2000, Latin1_General that started with SQL Server 2008, Japanese_XJIS that started with SQL Server 2008, and Japanese_XJIS that started with SQL Server 2017. The two Collations starting in SQL Server 2008 are showing that both return the same number of matches, yet the newer Japanese_XJIS Collation returns a different number (the only Collations updated in SQL Server 2017 are the Japanese Collations). This is done to show how many characters are missing sort weights across the various Collation versions.

;WITH nums AS
(
  SELECT TOP (65536) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS [CodePoint]
  FROM   [master].[sys].[columns] col
  CROSS JOIN [master].[sys].[objects] obj
)
SELECT nums.[CodePoint],
       COALESCE(NCHAR(nums.[CodePoint]), N'TOTALS:') AS [Character],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Latin1_General_BIN2) THEN 1 END) AS [BIN2],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE SQL_Latin1_General_CP1_CS_AS) THEN 1 END) AS [SQL Collations],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Latin1_General_CS_AS_KS_WS) THEN 1 END) AS [SQL2000 Latin1],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Latin1_General_100_CS_AS_KS_WS) THEN 1 END) AS [SQL2008 Latin1],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Japanese_XJIS_100_CS_AS_KS_WS) THEN 1 END) AS [SQL2008 Japanese],
       COUNT(CASE WHEN (NCHAR(nums.[CodePoint]) = N''
                 COLLATE Japanese_XJIS_140_CS_AS_KS_WS) THEN 1 END) AS [SQL2017 Japanese]
FROM   nums
GROUP BY ROLLUP ((nums.[CodePoint], NCHAR(nums.[CodePoint])));

To see the details for all rows, execute the query above. But for just the summary, that is:

BIN2  SQL Collations SQL2000 Latin1  SQL2008 Latin1  SQL2008 Japanese  SQL2017 Japanese
1     21230          21229           5840            5840              3375


Tweaking indexes for tables with "geographical" data

A couple of things to mention about this query:

There is no need to use MyISAM.  InnoDB in MySQL 5.7+ supports geospatial types, and rtree indexes.
Independent of the above, this is a key use case for composite indexes.  Possibly: ALTER TABLE nests ADD INDEX s_s_l (status, spawn_type, latitude).  You will not be able to effectively use latitude and longitude in the same index, as the remainder of the composite index can not be used after a ranged condition.

It looks like there is some doubt in the comments that this works.  Let me show the EXPLAIN output:

ALTER TABLE nests DROP INDEX status, DROP INDEX latitude, DROP INDEX spawn_type;
ALTER TABLE nests CHANGE latitude latitude DECIMAL(10, 8) NOT NULL;
ALTER TABLE nests CHANGE longitude longitude DECIMAL(10, 8) NOT NULL;
ALTER TABLE nests ADD INDEX s_s_l (status, spawn_type, latitude);

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "s_s_l"
      ],
      "key": "s_s_l",
      "used_key_parts": [
        "status",       # All parts of the key
        "spawn_type",   # are used, including
        "latitude"      # the last part.
      ],
      "key_length": "15",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "11.11",
      "index_condition": "((`test`.`nests`.`latitude` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 50.898161073089) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "19.03",
        "eval_cost": "0.18",
        "prefix_cost": "19.21",
        "data_read_per_join": "49"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ],
      "attached_condition": "((`test`.`nests`.`longitude` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 6.8912601470947))"
    }
  }
}

An index on (s,s,l,l) only uses the index for (s,s,l), but applies Index Condition Pushdown for the remaining (l):

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "s_s_l_l"
      ],
      "key": "s_s_l_l",
      "used_key_parts": [
        "status",
        "spawn_type",
        "latitude"  # last part of key not used
      ],
      "key_length": "20",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "11.11",
      "index_condition": "((`test`.`nests`.`latitude` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 50.898161073089) and (`test`.`nests`.`longitude` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 6.8912601470947) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "19.03",
        "eval_cost": "0.18",
        "prefix_cost": "19.21",
        "data_read_per_join": "49"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

And l_s_s is only effective for filtering (l), with ICP again used for (s,s):

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "l_s_s"
      ],
      "key": "l_s_s",
      "used_key_parts": [
        "latitude"  # Only first part of key used
      ],
      "key_length": "15",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "0.89",
      "index_condition": "((`test`.`nests`.`latitude` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 50.898161073089) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "12.20",
        "eval_cost": "0.01",
        "prefix_cost": "12.21",
        "data_read_per_join": "3"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ],
      "attached_condition": "((`test`.`nests`.`longitude` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 6.8912601470947))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)


How can I detect the maximum value in several rows and columns at the same time in an SQL table?

If I understood what you are trying to do correctly then this should work (fiddle).

The CASE expression ensures that only values with 0 in the preceding column are considered for the aggregation. Prior to the aggregation the integers (presumed >= 0) are padded out with leading zeroes to make them exactly 10 characters long and concatenated to the Cases string.

The MAX is then found to get the lexicographically greatest concatenated string for that time (as here) and then this is split into its constituent parts.

In the event that there is more than one eligible row with the same maximum value for a specific time this will somwehat arbitrarily pick the one with the alphabetically latest Cases string

WITH Grouped As
(
SELECT [ID],
      MAX(CASE WHEN [Time2] = 0 THEN FORMAT([Time3],'D10') + [Cases] END) AS TC3,
      MAX(CASE WHEN [Time3] = 0 THEN FORMAT([Time4],'D10') + [Cases] END) AS TC4,
      MAX(CASE WHEN [Time4] = 0 THEN FORMAT([Time5],'D10') + [Cases] END) AS TC5,
      MAX(CASE WHEN [Time5] = 0 THEN FORMAT([Time6],'D10') + [Cases] END) AS TC6,
      MAX(CASE WHEN [Time6] = 0 THEN FORMAT([Time7],'D10') + [Cases] END) AS TC7,
      MAX(CASE WHEN [Time7] = 0 THEN FORMAT([Time8],'D10') + [Cases] END) AS TC8,
      MAX(CASE WHEN [Time8] = 0 THEN FORMAT([Time9],'D10') + [Cases] END) AS TC9
FROM YourTable
GROUP BY [ID]
)
SELECT [ID],
       Time3 = 0+LEFT(TC3,10),
       Cases3 = SUBSTRING(TC3,11,8000),
       Time4 = 0+LEFT(TC4,10),
       Cases4 = SUBSTRING(TC4,11,8000),
       Time5 = 0+LEFT(TC5,10),
       Cases5 = SUBSTRING(TC5,11,8000),
       Time6 = 0+LEFT(TC6,10),
       Cases6 = SUBSTRING(TC6,11,8000),
       Time7 = 0+LEFT(TC7,10),
       Cases7 = SUBSTRING(TC7,11,8000),
       Time8 = 0+LEFT(TC8,10),
       Cases8 = SUBSTRING(TC8,11,8000),
       Time9 = 0+LEFT(TC9,10),
       Cases9 = SUBSTRING(TC9,11,8000)
FROM Grouped


Indexing very similar character varying field in postgres

What a terrible query! Consider using different software.
The only thing that could help are trigram indexes on both columns:
CREATE EXTENSION pg_trgm;

CREATE INDEX ON upload_file USING gin ((name::text) gin_trgm_ops);
CREATE INDEX ON upload_file USING gin ((id::text) gin_trgm_ops);

Then you might get two bitmap index scans and a "bitmap or".
If not, the condition is probably not selective enough, and a sequential scan is the most efficient way to handle it.


Does listener gets affected when we replace secondary replica with another server in Availability Group?

The listener is a cluster ressource. If you new server is part of the cluster and is added to your availability group, it should work just fine.


How to extract pipe-delimited data using BCP?

Now that I better understand that you are trying to extract data, here is a new answer. This just simply extracts data from a table that I created in tempdb.

use tempdb

-- drop table mytable

create table mytable
(id int, customer_name varchar(55), cityname varchar(55), statename varchar(55))

insert into mytable
values (1, 'a', 'a city', 'a state')
     , (2, 'b', 'b city', 'b state')
     , (3, 'c', 'c city', 'c state')
     , (4, 'd', 'd city', 'd state')

select cast(id as varchar(10)), customer_name, cityname, statename from mytable

Which results in the following bcp command

bcp "select cast(id as varchar(10)), customer_name, cityname, statename from mytable" queryout c:\temp\myTable.csv /S.\instance_name /d tempdb /c /t"|" -T

Update This bcp command will add double quotes

exec Master..xp_Cmdshell 'bcp "select cast(id as varchar(10)), quotename(customer_name,char(34)), quotename(cityname,char(34)), quotename(statename,char(34)) from mytable" queryout "c:\temp\myTable.csv" /S.\instance_name /d tempdb /c /t"|" /T'

or just with bcp

bcp "select cast(id as varchar(10)), quotename(customer_name,char(34)), quotename(cityname,char(34)), quotename(statename,char(34)) from mytable" queryout "c:\temp\myTable.csv" /S.\instance_name /d tempdb /c /t"|" /T

This bcp command uses the quotename function. Which allows you to delimit your output using whatever characters you like.


Archivelog file deleted, resulting in 'ORA-01113: file 1 needs media recovery'

Ran an RMAN archivelog backup to a new directory
  Changed my mind and deleted that directory.

Assuming you still have the archivelog files and they were not deleted when they were backed up, you just need to tell RMAN that those files aren't coming back:

  Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository

If, on the other hand, the original archivelog files were deleted along with their backups, you do not have a full recovery set and it is a good thing you said "This is not a production database. I'm practising in a staging environment". A full backup run online is not a consistent backup — you need the archivelog files containing log records from just before the backup starts until just after it finishes or there is no way to restore the database consistently.


Backup suddenly is taking too long to complete

There's basically 4 things involved with backup speed:

Reading from the data files - you can test this by doing a backup to NUL:, as in this example, which won't break your log-backup chain for full recovery model databases:

BACKUP DATABASE xyz TO DISK = 'NUL:' WITH COPY_ONLY;

Compressing the data - watch CPU pressure during the backup.
Getting it through the network path to its final location (optional, only for network backups) - test this with a network file copy.
Writing it to the disk on its final target location - test this with something like CrystalDiskMark or DiskSpd, but has to be done locally (not over a network pipe, since you want to avoid #3 affecting your results).

Third party backup products like Quest LiteSpeed, Idera SQLsafe, and Red Gate SQL Backup (disclaimer: I'm independent, but I've worked with all of those companies in the past) have wizards that will test each component of that, and then tell you where the bottleneck is. If you want to find it yourself, you're going to have to test each of those components, and then find the weakest link in the chain (the lowest throughput).

For example, if other SQL Servers are writing to the same target location, they may have suddenly had an increase of data or added more servers, thereby slowing down the target.


I guess I'll answer myself, since it's working...

The required permissions seem to be as follows:

"AlmostAdmin" is a SQL Login in the public server role, GRANTed securables "Connect SQL" and "View Server State".
The login is mapped to "AlmostAdmin" user in MyDB, which is in the db_owner role with default schema dbo.

My problem seems to have been with impersonation, because even though it fails for me when I run the following--

EXECUTE AS USER = 'AlmostAdmin'
EXEC sp_WhoIsActive
REVERT

--It works for the actual user when he's logged in himself (via SSMS).  And of course then I logged in as that user and verified that it was working for me too (when using that login, instead of my "TheAdmin" master login!).

Lesson learned: don't rely on impersonation 100% for testing security/permission issues; get the target user involved to test it out as well (or if you're in a position to know the credentials, lower yourself to that level and test it, instead of completely relying on EXECUTE AS).


Windows Authentication Doesn't Work Locally

I never figured out the root cause of the problem, but installing SQL Server 7 SP4 fixed the issue.


The  following gives you the most recent record per id

SELECT * FROM
(
   SELECT a.* ,
   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date DESC) rn
   FROM USER1.ROAD_INSPECTION a
)a
WHERE a.rn =1;

I'm not sure what you mean by complications when querying column of type DATE. 2010-01-01 is the same as midnight of Jan 1, 2010.

Update. Fixed missed FROM clause.


Will a nonclustered index with a unique column always address all queries filtering that column first?

Yes, given the constraints in the question, particularly that the primary key column is the leading column in the indexes. Also assuming the primary key never changes.
Not necessarily.

The optimizer can indeed infer uniqueness without marking the nonclustered index unique.

Marking the index unique may introduce a Split-Sort-Collapse combination in execution plans that change an index key. The extra Sort in particular has the potential to be performance-affecting.

On the other hand, not marking the index unique risks data integrity if the primary key is ever changed.

Example

CREATE TABLE dbo.Test
(
    pk integer PRIMARY KEY NONCLUSTERED,
    c1 integer NOT NULL,
    c2 integer NOT NULL
);

-- Not unique on pk, c1
CREATE NONCLUSTERED INDEX ic1 ON dbo.Test (pk, c1);

-- Unique on pk, c2
CREATE UNIQUE NONCLUSTERED INDEX ic2 ON dbo.Test (pk, c2);

Uniqueness

-- Neither plan has an aggregate
SELECT DISTINCT T.c1 FROM dbo.Test AS T WHERE T.pk = 1;
SELECT DISTINCT T.c2 FROM dbo.Test AS T WHERE T.pk = 1;

Split, Sort, Collapse

-- No split, sort, collapse
UPDATE dbo.Test SET c1 = CHECKSUM(NEWID());

-- Split, sort, collapse updating unique key
UPDATE dbo.Test SET c2 = CHECKSUM(NEWID());

Note the split-sort-collapse plan is also a wide (per-index) update.

Uniqueness is a huge topic though. I would normally mark something that is unique as unique, unless there is a good reason not to. Some further reading from my blog:

Enforcing Uniqueness for Performance
Avoiding Uniqueness for Performance
Sneaky Reads with Unique Indexes
SQL Server, Seeks, and Binary Search

To anticipate comments about heap tables: Most tables benefit from being clustered. You need good reasons to choose a heap structure, especially from a space management point of view, if the table ever experiences deletes. Updates can also introduce performance impacts if columns expand beyond the space available on the original page (forwarded records).


Get Visual Studio if you don't have it already.
Get Oracle SQL Developer 4.1.3.
Install the SSDT add-in for visual studio.
Install a local instance of GIT for version control.
Install the MS GIT Plugin for Visual Studio.
Create a SQL Server Database project in Visual Studio for your database.
Make all database modifications using the SQL DB Project going forward.
Use the Datamodeler tool that comes with Oracle SQL Developer to build a relational model of your MS SQL Database so that you can visualize it - yes, this will work, and it's free.

Other Notes:

Don't include security roles or users in your SQL Database Project.
Do include Linked Servers, and Schema definitions.
Getting a handle on this is not going to happen over night, so don't beat yourself up.
It would be a lot better to have a remote git repo with a local copy that you replicate - but a local repo is better than nothing, and I suggested it because it's faster to get up and running.


Adding new databases to existing maintenance backup plan

Yes, you can use the well used ola hallengren.scripts.

Make use of the SQL Server Backup scripts available on the above.

Eg:

  Back up all user databases, using checksums and compression; verify
  the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

Then run the above script on SQL server agent job as per the backup plan strategy to execute this.

Also, just to mention you need to first create the scripts CommandExecute.sql and DatabaseBackup.sql in order to use the backup plan.


What clustering features are required to implement Availability Groups?

MSDN has an excellent article about this exact topic.

Some salient points:

  Ensure that each computer is a node in a Windows Server Failover Clustering (WSFC) cluster.

SQL Server Clustering can be used, but is not required.

From this MSDN page, we get:

  AlwaysOn Availability Groups, the high availability and disaster recovery solution introduced in SQL Server 2012, requires Windows Server Failover Clustering (WSFC). Also, though AlwaysOn Availability Groups is not dependent upon SQL Server Failover Clustering, you can use a failover clustering instance (FCI) to host an availability replica for an availability group.


How to get the date part from a date with timestamp column in SQL Server?

Here is an example using GETDATE():

SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');

Yields 12Nov2018. Three days ago, it would yield 09Nov2018.

To run this against your table:

SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
  FROM dbo.YOUR_TABLE_NAME_HERE;

In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).

SELECT FORMAT(GETDATE(), 'ddMMMyyyy');


Error during SQL Server 2008 R2 SP3 upgrade: MODIFY FILE failed. File 'MSDBLog' does not exist

This happens because the msdb log file name has been changed from the default which is "MSDBLog". In my case, it was called "msdb_log". The name "MSDBLog" is hard-coded into the upgrade script so it fails if the name has been changed.

To fix the problem, we need to rename the log file to 'MSDBLog'. We have to start SQL with trace flag 902 to stop it from running upgrade scripts when it starts. We can then do our fix, remove the trace flag and start it again.

Open SQL Server Configuration Manager
Navigate to "SQL Server Services" in the left-hand pane
Right click on the SQL instance and select "Properties"
Click on the "Advanced" tab
In "Startup Parameters", add ";-T902" to the end (excluding quotation marks)
Click "OK" to save it
Start the SQL instance
Rename the log file back to its original name:

ALTER DATABASE msdb MODIFY FILE (NAME=N'msdb_log', NEWNAME=N'MSDBLog')
Stop the SQL instance
Undo the changes to the startup parameters
Start the SQL instance again

The upgrade scripts will now run and should succeed.


How to deal with unbounded growth in cassandra shards

Usually, people add some 'bucket' field to the partition key - it could be simple bucket, with limited number of possible values (1-10, for example), or it could be time-bounded bucket, like, year+month, or even daily bucket. Main thing here is that it should be predictable, so you can send queries against these buckets.

With comments it's becoming trickier, as for really popular videos the number of comments could grow really fast, but this could be solved by lookup table (or as in article, shard table). In this case you need to do a read from this table as a first step, and then send many reads in parallel to fetch necessary partitions - this could be relatively fast, as you don't want to load all comments at once, and you're usually show only recent comments, and then load older on demand.


Database Mail sending functionality not working on local system

I'm not sure what you are talking about with "gmail profile". SQL Server Database Mail has its own profiles and has nothing to do with any email clients on your machine.

When you create a Database Mail profile (SQL Server Management Studio, Management, Database Mail) you must specify the ip address of the SMTP server or servers to use. What you are confiruing is the "outgoing email server" for Database Mail (similar to configuring an Internet email client like Thunderbird or Windows Mail). Those SMTP servers must be configured to allow connection and to allow relaying from the SQL Server machine's ip address, something the email administrators will be able to do.

That error message suggests to me that either the above has not been configured or that there is a firewall between the SQL Server machine and the SMTP server that is blocking the traffic.


Oracle 11g PGA advice view is empty when it shouldn't

Bug 11872813 - V$PGA_TARGET_ADVICE VIEW IS EMPTY.

  After setting PGA_AGGREGATE_TARGET > approximately 12G the
  V$PGA_TARGET_ADVICE view is empty.
  This issue will be fixed in version 12.1 and a fix is expected to be included  in patch set
  11.2.0.4.

  Apply Patch 11872813


Does 'update global indexes' not work if the index is already unusable?

The update [global] indexes option does not rebuild indexes after whatever operation you ran. It maintains indexes during the operation (see Updating Indexes Automatically), so that they remain available throughout.

After your first truncate, the index needs to be rebuilt - it can't be updated, it's already "broken" and unavailable. Oracle won't rebuild it automatically for the next operation you do on the table, even if you include the update clause.


Use of Trigger on table which can have 100 hits / second. Will it hamper performance?

Unless you've badly coded it, then no. It is probably less then 10 writes per second. Even very write heavy databases are over 90% reads and under 10% writes.

Consider though that such things can be replaced by, say, an indexed view on this table that looks like the separate table. Or the DML can be done in a stored procedure so it's all in one place for clarity.

If you do hit issues, consider snapshot isolation so that the writes do not block reads.

Summary: should be OK, but there are things to mitigate if not.

Edit, read:write ratio

SELECT  SUM(ddius.user_updates) AS user_writes,
        SUM(ddius.user_lookups + ddius.user_scans + ddius.user_seeks) AS user_reads,
        OBJECT_NAME(ddius.object_id),
        1.0*SUM(ddius.user_lookups + ddius.user_scans + ddius.user_seeks)/NULLIF(SUM(ddius.user_updates), 0)
FROM    sys.dm_db_index_usage_stats AS ddius
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY
 1.0*SUM(ddius.user_lookups + ddius.user_scans + ddius.user_seeks)/NULLIF(SUM(ddius.user_updates), 0)

Of 227 objects with indexes in my main OLTP system

only 24 have more writes then reads and about half are log or history tables.
101 have zero writes (mostly lookup or metadata tables)
Of the remainder, some of our heaviest use table have x2 or x10 or x25 and higher more reads than writes


PostgreSQL WHERE clause with two OR'ed conditions, priority to first

You have two conditions to check during the authentication:

There is a user with matching username; do not need to match by email.
There are no users with matching usernames; need to match by email.

So the query is:

WITH matching_username AS (SELECT * FROM member WHERE username = 'john@domain.com')
SELECT * FROM matching_username WHERE (SELECT COUNT(*) FROM matching_username) = 1
                                   AND pw_hash = 'x1j34'
UNION
SELECT * FROM member            WHERE (SELECT COUNT(*) FROM matching_username) = 0
                                   AND email = 'john@domain.com'
                                   AND pw_hash='x1j34';

On the other hand, there is a problem with your design of the table.

What if two users have have the same email, but neither of them have email as their username? If one of them tries to authenticate with an email, you will not be able to tell, which user record to authenticate against.


Does combining columns into 1 column helps to optimize a large table?

This is a bad idea.

Putting more than one data element into a single field invariably leads to headaches.

You will be much better off making a new table to store this data, like:

Table UserItems

UserID int
ItemId int

And then one row per item per user.  You can query this as needed and update it without any issues.

Think about adding one additional item to your proposed structure - it would be a challenge since you need to concatenate another space and another id to the end of the existing string.

It will also be a pain to check for a single item in that structure since you need to do a string search for every record.


Designing database with Category and Tags for Posts - Seeking for guidance/advice

One way to understand what's going on with normalization is to find out what normalization buys you.  In other words, what's going to happen if you depart from any given normal form?

For normalization forms second through fifth normal forms, the answer is "update anomalies".  That is, when you insert, update, or delete rows, can you end up leaving things in a bad way?  One particular kind of bad way is a database that contradicts itself, that stores a given assertion somewhere in the database and stores a contradictory assertion somewhere else in the database.

Normalization helps with this by obviating the problem.  That is, it cuts down or eliminates the possibility of contradictory assertions by assuring that facts are stored once, and only once.  The fact is either right or it's wrong, but it's not self contradictory.

There are other ways to avoid self contradictory databases, but they involve careful, disciplined programming.  There are situations where a non normalized approach gives you more benefits than a normalized approach.  But this calls for judgement, and judgement comes with experience.

With regard to EAV, one attraction to EAV is that it allows you to get started without coming up with a logical model for the structure of the data.  The reason is that any logical model whatsoever can conform to EAV.  So  why doesn't everybody do EAV?  Because sometimes, the data that results is relatively worthless compared to the expected value when the database was built.  I'm not saying EAV is always wrong.  I am saying that it often is.  Again, this calls for judgement, and judgement comes with experience.


Column To generate Flag Values for use in bitwise comparisons

I would use something like this:

CREATE TABLE #test
(
    id INT IDENTITY(0,1) NOT NULL
    , val AS (POWER(2,id)) PERSISTED
    , CONSTRAINT pk_test PRIMARY KEY
        (
            val
        )
);
GO

INSERT #test DEFAULT VALUES;
GO 15

SELECT * FROM #test;


How to check AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS is enabled for database

This uses a Dynamic SQL query. It excludes system databases. Checks for is_auto_create_stats_on or is_auto_update_stats_on 0 OFF and change it to ON

declare @sql    nvarchar(max);

; with
db as
(
    select  name, is_auto_create_stats_on, is_auto_update_stats_on
    from    sys.databases
    where   name    not in ('master', 'tempdb', 'model', 'msdb')
    and     (
                is_auto_create_stats_on = 0
            or  is_auto_update_stats_on = 0
            )
)
select  @sql    = isnull(@sql + char(13), '')
                + 'ALTER DATABASE ' + quotename(db.name) + ' '
                + 'SET ' + o.opt + ' ON;'
from    db
        cross apply
        (
            select  opt = 'AUTO_CREATE_STATISTICS'
            where   is_auto_create_stats_on = 0

            union all

            select  opt = 'AUTO_UPDATE_STATISTICS'
            where   is_auto_update_stats_on = 0
        ) o

-- print out for verification before execute
print   @sql
exec    sp_executesql @sql

You may mask the sp_executesql and verify the result before actual execution


Make Xtrabackup ignore myisam tables & backup only innoDB tables?

Xtrabackup website says something different:

  Percona XtraBackup can back up the following storage engines by briefly pausing writes at the end of the backup

This information used to be in the documentation, probably better detailed, but nowadays only this enigmatic claim remains. Probably you are worrying too much, but I will answer the rest of the question anyway.

There doesn't seem to be any way to easily exclude non-InnoDB tables from the backup. What you can do is to manually exclude those tables with --table-exclude If you have entire databases dedicated to non-InnoDB tables, --databases-exclude should be easier to use.

You can dynamically compose the list of those tables with this query:

SELECT GROUP_CONCAT(CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) SEPARATOR ',')
    FROM information_schema.TABLES
    WHERE
        ENGINE <> 'InnoDB'
        AND ENGINE IS NOT NULL
        AND TABLE_SCHEMA NOT IN (
            'mysql',
            'performance_schema',
            'information_schema',
            'sys'
        );


Changing Databases Dynamically using Variables

I changed

execute @SQL

to

exec (@SQL)

and it worked for me. However, the scope of the use is only in the dynamic SQL.


Query takes long time to execute while there is an index configured

Perhaps auto-update stats kicked in. AUTO_UPDATE_STATISTICS_ASYNC ON is your friend in that case. Check the index stats date to see if it coincides with the slowness:
SELECT
    STATS_DATE(
          OBJECT_ID(N'YourSchema.YourTable')
        , INDEXPROPERTY(OBJECT_ID(N'YourSchema.YourTable'),N'YourIndexName','IndexID')
    ) AS LastStatsDate;

-- dan-guzman
If it isn't plan generation (SET STATISTICS TIME ON can be revealing), or the query processing per se (reading just a few rows with a pretty unique index), then the natural thing to suspect is blocking. - tibor-karaszi

There are several ways to monitor for statistics update events (profiler, extended events...). SQL Server 2019 introduces the WAIT_ON_SYNC_STATISTICS_REFRESH wait type, which can appear in the wait information section of 'actual' execution plans.
Sentry One Plan Explorer captures statistics building and refreshing (statman) and includes this information in the wait info.


If you manually update any mysql.X table regarding privileges will require a FLUSH PRIVILEGES to effect the change.

Using GRANT is the better way of of ensuring privs exists (and doesn't require FLUSH PRIVILEGES).


How to check oracle error description in sqlplus?

You can use oerr utility.

Example:

[oracle@ora12c Desktop]$ oerr ora 01950
01950, 00000, "no privileges on tablespace '%s'"
Cause:    User does not have privileges to allocate an extent in the
          specified tablespace.

Action:   Grant the user the appropriate system privileges or grant the user
          space resource on the tablespace.

You can search error code on this webpage Search for Error Messages or you can download the Database Error Messages documentation from here.

Note: This utility is not available on Windows.


What you've got sounds like it should work and would get you online quickly, however you will have no HA plan if something fails before you've got the 200GB database replicated to at least one of your secondaries.

Also what will you do if your upgrade fails? I don't think there will be a way back to the old server without going back and restoring backups.

When we've done similar migrations (2012 to 2014), we've done the following:

Create the Availability Group
Take a full backup of current production
Restore on all servers in the AAG (leaving in restore mode)
Take a differential backup of current production shortly before your 30 minute window and restore to all servers on the AAG (leaving in restore mode)
Take regular log backups of the current production server and restore (you could automate this with log shipping)
When your 30 minute window starts, restore the last of the log backups, bring the new primary online, make sure everything is attached to the AAG and you're ready to go


Join two Json arrays to one with key and value

You need to unnest the arrays then aggregate everything back into a single JSON value:
select x.value
from the_table t
  cross join lateral (
    select jsonb_object_agg(k.ky, v.value) as value
    from jsonb_array_elements_text(t.keys) with ordinality as k(ky,idx)
       join jsonb_array_elements(t.values) with ordinality as v(value,idx) on k.idx = v.idx
  ) x

Online example


dump files maxing C drive with corrupt mdf

You should limit your error log size. According to support, you can accomplish this by executing on the target instance the following script:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

Another thing you might want to check is where you have your TempDB stored. If it's on C drive, than perhaps you should move it to another drive.

If you'te getting memory minidumps due to crashes of the database engine, than you should follow Dan Guzman's suggestion and create a script to move or delete them.


Optimizing column types for reduced overall database size

In addition to the links a_horse_with_no_name provided on padding a few points here,

This is tremendously wasteful.

latitud double precision DEFAULT 0 NOT NULL,
longitud double precision DEFAULT 0 NOT NULL,
altitud double precision DEFAULT 0 NOT NULL,
velocidad double precision DEFAULT 0 NOT NULL,

You're storing each 0 as a bigint rather than dealing with null which would be (almost) free one bit in the null bitmap.

USE NULL, stop using 0 to represent defaults. Your average row size will drastically shrink.

Also,

    odometro bigint,

Are you measuring kilometers in micrometers? That's a gigantic number for any odometer representation.


Ignore a particular column on update trigger

Documentation shows that you can define specific columns in an on update trigger:

create trigger foo
after update of column1, column2, ... -- other columns, except column10
on your_table ...


Does MySQL run WHERE clause before ORDER BY?

The logical order in which a query is evaluated is:

FROM / JOIN clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

so from a logical point of view, yes the WHERE should be evaluated before the order by.

But the DBMS:s (in this case MySQL) is allowed to evaluate the query in any order as long as the result of the query is preserved. So in a future version it might evaluate a query in another evaluation order which may cause unexpected performance effects.

Now, since ORDER BY basically transforms the query into a cursor I think it is pretty safe to assume that the WHERE clause will be evaluated before the ORDER BY (at least as long as the table is row-based, for a column-based table the situation may be different).


Migrate SQL Server from 2012 to 2014 (11 to 12)

I have just installed SQL Server Management studio 2014

SSMS wont install database engine. Its just a GUI to connect to SQL Server. You can check the SQL Server version using below T-SQL :

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

  How do I upgrade/migrate my server to version 12?

There are couple of ways to do.

InPlace Upgrade : You just upgrade the current instance by running the setup.exe. The older instance is replaced by the newer version.

Side by side migration: You install and configure a new instance that might exist on the same server or different server and then you move all you databases using backup/restore (preferred) or other methods like - detach/attach, logshipping, copy database wizard, etc.

Note: Depending on your downtime requirements, you have to plan accordingly. Prepare a detailed migration plan and discuss that will stake holders for an acceptable downtime.

If it is your local instance, then you dont have to worry about any downtime.

An excellent answer by Mike Walsh : Are SQL Server in-place upgrades as ill advised as they used to be?

Also, check out UPGRADING TO SQL SERVER 2014: A DOZEN THINGS TO CHECK as well as What to Consider When Creating SQL Server Database Migration Plan?


mysql function for 2 rows (same name) with group by

depending from what You are looking for, it could be:

SELECT  MIN(`id`) as id,  `name` , MAX( sallary ) as salary ,  `month` ,  `year`
FROM  `test`
GROUP BY  `name` ,  `month` ,  `year`

or because mike have 2 salary in Jan 2017 (and of course it same Mike ;) ):

SELECT  MIN(`id`) as id,  `name` , SUM( sallary ) as salary ,  `month` ,  `year`
FROM  `test`
GROUP BY  `name` ,  `month` ,  `year`

because as unique identifier You choose Name - MIN(id) allow avoid errors when MySQL default settings - ONLY_FULL_GROUP_BY

Specially for Rick James :)

From original question, we can see -  id, can not be used as IDENTIFIER

so queries example with MIN and MAX for id - could be (and this realistic live scenario) - first or most resent contact with person

Before we go to next form of query - first we need decide mike(id=1) for Jan 17 and mike (id=4) for Jan 17, it is same person? and salary for Jan - it MAX or SUM (as I include in my examples).


Selecting dates that are between the previous 12 to 9 month range

This should work:

...
WHERE
    -- >= 2016-02-01 00:00:00.000
    READDATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 12, 0)
    -- < 2016-04-01 00:00:00.000
    READDATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 10, 0)
...

Please note that with dates it is usually best to compare it this way:

X >= lower bound
    AND
X < upper bound

This is why I change <= '20160331' to < '20160401'. While it works fine with your sample using what seems to be DATE types, it may give incorrect results using dates with a TIME part.


February has 28 days.

February in 2017 doesn't have 29 days. It has 28. End of story. If you need to store a 29th day, then your definition of date is different than PostgreSQL's and it will not be able to do date math or use date functions with your definition.

What is '2017-02-29'::date + interval '1 day' in your system though? February 30, March 1, or March 2? And, what would '2017-02-29'::date + interval '1 day' - interval '1 day' be?


Default value of serial fields changes after restore

I ran the same process as you did, but got different results.  Restoring the database using your command didn't work when trying to overwrite the same database because you didn't use the -c --clean option.  Restoring to a new database with the same pg_restore command worked. Inserting new records was still possible however.

Here are the steps I took.

Create the database

  createdb testdatabase

Create the schema

  psql -d testdatabase -c 'create schema testschema;'

Create the table

  psql -d testdatabase

CREATE TABLE testschema.testtable(
  id serial, name character varying(255),
  CONSTRAINT pk_testtable PRIMARY KEY (id)
) with (OIDS=false);

  NOTICE:  CREATE TABLE will create implicit sequence "testtable_id_seq"
  for serial column "testtable.id" NOTICE:  CREATE TABLE / PRIMARY KEY
  will create implicit index "pk_testtable" for table "testtable" CREATE
  TABLE

Insert some records

testdatabase=# insert into testschema.testtable (name) values('person1'),('person2');
INSERT 0 2

Backup the database

pg_dump -F c -Z 9 -b -h localhost -U postgres -f backup testdatabase

Create the backup list file

pg_restore -U postgres -h localhost -l backup > backup.list

Relevant contents of backup.list

  2639; 1262 16468 DATABASE - testdatabase postgres 163; 1259 16472
  TABLE testschema testtable postgres 162; 1259 16470 SEQUENCE
  testschema testtable_id_seq postgres 2643; 0 0 SEQUENCE OWNED BY
  testschema testtable_id_seq postgres 2644; 0 0 SEQUENCE SET testschema
  testtable_id_seq postgres 2633; 2604 16475 DEFAULT testschema id
  postgres 2636; 0 16472 TABLE DATA testschema testtable postgres 2635;
  2606 16477 CONSTRAINT testschema pk_testtable postgres

Restore the backup

pg_restore -U postgres -h localhost --disable-triggers -O -d testdatabase -S postgres -Fc -L backup.list backup

pg_restore sends back errors

  pg_restore: [archiver (db)] could not execute query: ERROR:  schema
  "testschema" already exists pg_restore: [archiver (db)] Error from TOC
  entry 163; 1259 16472 TABLE testtable postgres pg_restore: [archiver
  (db)] could not execute query: ERROR:  relation "testtable_id_seq"
  already exists

Restore to another database

createdb testdatabase2
pg_restore -U postgres -h localhost --disable-triggers -O -d testdatabase2 -S postgres -Fc -L backup.list backup

Did not get any errors

Insert more records

psql -d testdatabase2

testdatabase2=# insert into testschema.testtable (name) values('person2'),('person3');
INSERT 0 2

View records

testdatabase2=# select * from testschema.testtable;

 id |  name
----+---------
  1 | person1
  2 | person2
  3 | person2
  4 | person3

View the table structure for testschema.testtable

pg_dump --schema-only -t testschema.testtable testdatabase2

As you mentioned in your question, postgresql does rewrite the default value

  ALTER TABLE ONLY testtable ALTER COLUMN id SET DEFAULT
  nextval('testtable_id_seq'::regclass);

Conclusion

Using your exact same commands, errors did occur when trying to restore over the same database, but when restoring to a new database, adding new records was possible.

You must add the -c or --clean, to drop existing objects so that they can be restored by pg_restore.

pg_restore -c -U postgres -h localhost --disable-triggers -O -d testdatabase -S postgres -Fc -L backup.list backup

Postgresql did rewrite the default value for your serial column as you mentioned in your question, but insertions were still possible.

Updated Answer

When using the query you provided, it is true I don't see the testschema in the search path after restoring the database.

testdatabase=# SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value FROM pg_attribute AS a JOIN pg_class AS c ON a.attrelid = c.oid JOIN pg_namespace AS n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum WHERE a.attnum > 0   AND n.nspname = 'testschema'  AND c.relname = 'testtable';
 attnum |  nspname   |  relname  |             default_value
--------+------------+-----------+---------------------------------------
      1 | testschema | testtable | nextval('testtable_id_seq'::regclass)
      2 | testschema | testtable |

But, when I use \d testschema.testtable I do see the testschema in the definition of NEXTVAL

testdatabase=# \d testschema.testtable;
                                    Table "testschema.testtable"
 Column |          Type          |                             Modifiers
--------+------------------------+-------------------------------------------------------------------
 id     | integer                | not null default nextval('testschema.testtable_id_seq'::regclass)
 name   | character varying(255) |

I found this on Postgresql's forums, which talks about the differences we are both seeing.

http://archives.postgresql.org/pgsql-admin/2009-06/msg00070.php


Calculate and show DATETIME difference on PIVOTING query

Let me know if this produce the desired result.

First I've set up an example on dbfiddle that returns unpivot data similar to the result of your question.

CREATE TABLE foo
(
  applicationId int,
  countryName varchar(20),
  name varchar(20),
  nationality varchar(20),
  agency varchar(10),
  newStatus varchar(10),
  dateChange datetime
);

INSERT INTO foo VALUES
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'OLA', '20170101'),
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'AWA', '20170110'),
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'TMB', '20170105'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'OLA', '20170203'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'AWA', '20170207'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'TMB', '20170212'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'OLA', '20170301'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'AWA', '20170311'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'TMB', '20170305');

Then I enclosed your pivot query inside a CTE function, and finally it calculates datediff using data returned by pivot query.

WITH p AS
(
    SELECT *
    FROM   (SELECT *
            FROM   foo) SRC
    PIVOT (MIN(dateChange) for newStatus IN ([OLA], [AWA], [TMB])) pvt
)
SELECT applicationId,
       countryName,
       name,
       nationality,
       agency,
       OLA,
       DATEDIFF(DAY, [OLA], [AWA]) X,
       AWA,
       DATEDIFF(DAY, [AWA], [TMB]) Y,
       TMB
FROM P
ORDER BY applicationId;
GO

This is the final result:

applicationId | countryName | name    | nationality | agency | OLA                 |  X | AWA                 |  Y | TMB
------------: | :---------- | :------ | :---------- | :----- | :------------------ | -: | :------------------ | -: | :------------------
         1001 | Mexico      | Juan    | Mexico      | CIA    | 01/01/2017 00:00:00 |  9 | 10/01/2017 00:00:00 | -5 | 05/01/2017 00:00:00
         1002 | Germany     | Michael | Germany     | FBI    | 03/02/2017 00:00:00 |  4 | 07/02/2017 00:00:00 |  5 | 12/02/2017 00:00:00
         1002 | Rusia       | Boris   | Rusia       | SNA    | 01/03/2017 00:00:00 | 10 | 11/03/2017 00:00:00 | -6 | 05/03/2017 00:00:00

dbfiddle here


AWE memory usage growing with SQL Server 2012

SQL Server is using the AWE memory allocation API to allocate buffer pool memory if "lock pages in memory" is enabled.

If SQL Server's max server memory setting is not set, SQL Server will eventually use as much memory as it needs. That will largely show up as AWE memory in RAMMap. A little more detail can be found here and all the SQL Server memory settings are explained here.

You should set the max server memory setting to allow an adequate amount for the operating system. If not enough memory is available for the OS, paging can occur and cause performance issues. A good starting point is 3 to 4 GB reserved for the OS, but watch the appropriate counters to see if the OS is under memory pressure.

You can get more information about how to select the right max server memory setting at the following links:

A Sysadmin’s Guide to Microsoft SQL Server Memory by Brent Ozar
Importance of setting Max Server Memory in SQL Server and How to Set it by Sakthivel Chidambaram

Full details of how SQL Server uses AWE and Locked Pages can be found in the Microsoft CSS SQL Server Engineers article:

Fun with Locked Pages, AWE, Task Manager, and the Working Set


MongoDB + Compass exporting strings and numbers "columns" with value of 1 as "true"(boolean) and 2 or null as "false" to CSV

While I have not been able to completely eliminate the possibility of MongoDB Compass exporting 1's and "true" (bool) or 0's and "false" (bool) nor remove NaN in some "columns" with NULLs mixed with numbers, using "Int32" and "Int64" data types for non-decimal values reduces the number of "false bools", significantly in my MongoDB Compass exports (but not completely - enough to greatly reduce the number of manual/script changes).
The best workaround I have for these quirks of Compass is to switch to Robo 3T (Formerly known as Robomongo) which seems much more consistent and less "auto-magic" happening in the background. It's just a better tool for MongoDB all-around, IMPO.
Link: https://robomongo.org/download
Note: Download just the 'Robo 3T: "The Hobbyist GUI"' and not the Studio 3T package unless you want to spend money for it in a few weeks.

This is just one example of the MANY inconsistent behaviors I've observed while using Compass in Linux with a Gnome GUI instead of the mongoshell or Robo 3T on the same machine.
You can just use the mongoshell commands inside of Robo 3T to perform your CSV exports:
Example: mongoexport --db=PetHotel --collection=pets --type=csv -- fields=_id,name,type,weight --out=data/pets.csv


Why am I able to query for more than 1024 columns but I can not make a view of the same query?

From CREATE VIEW (Transact-SQL):

  A view can have a maximum of 1,024 columns.

From Maximum Capacity Specifications for SQL Server:

Each table can have a maximum of 1,024 columns (30,000 for wide tables).

So, the simple answer is that the (somewhat arbitrary) limits are different.


Transaction isolation level - Read committed and select on large tables

In general, the storage engine has two options to perform a clustered index scan: an allocation order scan or an index order scan. For an allocation order scan, the storage engine reads the data in file order. In your scenario, this could result in the data from the insert showing up in the first query. For an index order scan, the storage engine reads data in linked list order. In your scenario, the new data from the insert will not show up in the first query. Even if the page that contains the new row splits the new page will not be read.

The storage engine decides between an allocation order scan or an index order scan depending on the requirements of the query and the isolation level. For example, for a query that requires data to be in order an index order scan is the right choice. For a query with a table lock that does not require the data to be in order, an allocation order scan is the right choice. Specifically, you will end up with an allocation order of the unsafe category (a scan can return multiple occurrences of rows or skip rows when page splits occur) when all of the following are true:

The query is running under the read uncommitted isolation level
Changes are allowed to the data (a table lock was not taken by the query)
The plan shows Index Scan, Ordered: false (the query does not require the data to be sorted)
The index size is greater than 64 pages.

In your example you have an isolation level of read committed. You also did not take a table lock with your clustered index scan query because the second session was able to insert a row. Therefore, the storage engine will perform a scan of the index order scan variety. The new data from the insert will not show up in the first query that does the scan.

My source for this is pages 65-69 from T-SQL Querying by Itzik Ben-Gan, Dejan Sarka, Adam Machanic, and Kevin Farlee.


Instead of temporary table, use a subquery (a derived table), then join it to the original marks table:

SELECT
    t.TaskId, t.StudentId, t.Marks
FROM
        tableX AS t
    JOIN
        ( SELECT
              TaskID
            , MAX(Marks) AS MaxMarks
          FROM
              tableX
          GROUP BY
              TaskID
        ) AS m
            ON  m.TaskId = t.TaskId
            AND m.MaxMarks = t.Marks ;

If you have an index on (TaskId, Marks, StudentId), it will help efficiency.

You can also use this variation:

SELECT
    t.TaskId, t.StudentId, t.Marks
FROM
        ( SELECT DISTINCT
              TaskId
          FROM
              tableX
        ) AS dt
    JOIN
        tableX AS t
            ON  t.TaskId = dt.TaskId
            AND t.Marks =
                ( SELECT
                      tm.Marks
                  FROM
                      tableX AS tm
                  WHERE
                      tm.TaskId = dt.TaskId
                  ORDER BY
                      tm.Marks DESC
                  LIMIT 1
                ) ;


AlwaysON AG Readable Secondary is set to 'Yes'. How to connect from the client?

If you want to access a secondary replica in an Always On configuration, the Readable Secondary property must be set to "Read-Intent" or "Yes".

If you set "Read-Intent" then your connection string must specify ApplicationIntent=ReadOnly otherwise the connection is rejected. If you set "Yes", your connection is allowed without ApplicationIntent=ReadOnly, however, any non-read queries you run will raise an error.

If you set "No" then all connections to the secondary will return an error. NOTE: That these conditions only apply when specifying an AG-joined database in the connection string. If you connect to a system database or non-AG database then you can connect to a secondary without issue, but you can't access the AG-joined databases. See here for more info.

Lastly, when connecting you have two options. Firstly, you can simply connect to the secondary replica via the normal name for that replica. Secondly, you can connect via the listener name.

When connecting via listener name, if you have configured read-only routing and your connection string specifies ApplicationIntent=ReadOnly then you will be routed to a readable secondary for your connection. If you do not specify ApplicationIntent=ReadOnly or you have not configured read-only routing, then you will connect to the primary replica. See here for more info.


You're doing the convert() in the wrong place I guess. You have to do it in the equals operation of the ON, it's still a datetime there, regardless of what you do on it in the list of selected columns.

Try:

SELECT CONVERT(date,db1.dbo.table1.datecolumn), db2.dbo.table2.datecolumn
FROM db1.dbo.table1
LEFT JOIN db2.dbo.table2
    ON CONVERT(date,db1.dbo.table1.datecolumn) = db2.dbo.table2.datecolumn


Oracle Hierarchical query: with two node attributes NodeId and NodeType

You're missing a PRIOR for the second condition in the connect by.

SQL> select * from TREETEST
     start with PARENTID = 1 and PARENTTYPE = 'A'
     connect by
          prior NODEID = PARENTID
      and prior NODETYPE = PARENTTYPE; -- note the PRIOR here too

    NODEID N   PARENTID P
---------- - ---------- -
         2 A          1 A
         3 A          1 A

Your query looks a bit odd to me, but that might very well be legit. I would have expected the first line in your sample data to be your root node, and thus a query like this:

SQL> select * from TREETEST
     start with NODEID = 1 and NODETYPE = 'A'
     connect by
          prior NODEID = PARENTID
      and prior NODETYPE = PARENTTYPE;

    NODEID N   PARENTID P
---------- - ---------- -
         1 A
         2 A          1 A
         3 A          1 A

And if you didn't want the parent to show up in the result set:

SQL> select * from TREETEST
      where level > 1       -- don't return the root node
      start with NODEID = 1 and NODETYPE = 'A'
      connect by
           prior NODEID = PARENTID
       and prior NODETYPE = PARENTTYPE;

    NODEID N   PARENTID P
---------- - ---------- -
         2 A          1 A
         3 A          1 A

Some references from the Hierarchical Queries documentation:

  PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

If you look at the railroad diagram there, you'll see the syntax is:

  starts with condition connect by [nocycle] condition [AND condition]*

And in this case, for the connect by part, each condition is a simple comparison condition with the syntax:

  expression operator expression

The PRIOR operator doesn't apply to the whole condition, it is part of the expression(s) in the conditions. I.e. it's as if it was parsed like this:

connect by ( PRIOR(child_column) = parent_column )

(PRIOR can also appear on the right-hand side.)

Your example query:

CONNECT BY PRIOR nodeid = PARENTID AND NODETYPE = PARENTTYPE;

is interpreted as:

CONNECT BY ( PRIOR(nodeid) = PARENTID  ) AND ( NODETYPE = PARENTTYPE );

The second condition (after the AND) applies to the type columns of the current row only, it doesn't reference the parent row at all. The PRIOR is part of the first condition only, and doesn't somehow influence to the second.


Restoring Database to a smaller test envrionment

You are stuck there. There is no option to re-size the files during restore. The backup is made by dumping the data pages directly to the backup device.

There is the option of exporting/importing the data using SSIS but that takes time and it would probably be cheaper to buy a new larger hard drive and take the test server down and exchange it's drive.


Setting up default values from another table

I think you are making this much more complicated than needed. If I understand the requirements, you want a single state, stored in the state table to cat as the default state value for all persons. And if that default state is changed, the change should be reflected in all - existing and future - rows of person.

If the above interpretation is correct, then you don't need to store a default in persons (and then update it every time the default changes). Store NULL instead! This is how it will work:

CREATE TABLE state
      ( state_name VARCHAR(20),
        population INTEGER
      );

INSERT INTO state VALUES ('Montana', 2000000);

CREATE TABLE person
      ( person_name VARCHAR(50),
        person_state VARCHAR(20) DEFAULT NULL,        -- yes!
        salary INTEGER
      );

INSERT INTO person
VALUES
  ('Alex', NULL, 12000),
  ('Bill', 'New York', 145000),
  ('Cath', 'Mississippi', 5000),
  ('Dean', NULL, 1000) ;

Then we use COALESCE() to get the default value from state if person_state is null:

    SELECT p.person_name,
           COALESCE(p.person_state, s.state_name) AS person_state,
           p.salary
    FROM person AS p
      CROSS JOIN state AS s ;

person_name | person_state | salary
:---------- | :----------- | -----:
Alex        | Montana      |  12000
Bill        | New York     | 145000
Cath        | Mississippi  |   5000
Dean        | Montana      |   1000

Change the default state:

DELETE FROM state;

INSERT INTO state VALUES ('Alabama', 50000);

And see the results:

    SELECT p.person_name,
           COALESCE(p.person_state, s.state_name) AS person_state,
           p.salary
    FROM person AS p
      CROSS JOIN state AS s ;

person_name | person_state | salary
:---------- | :----------- | -----:
Alex        | Alabama      |  12000
Bill        | New York     | 145000
Cath        | Mississippi  |   5000
Dean        | Alabama      |   1000

dbfiddle here


A port number is not necessary to use DBMS.
Individual programs could write directly to the DBMS's files.  But the rules of a DBMS have become very complex, and an immense amount of logic is needed to respect those rules and to respect the actions of other programs using the DB.  I've known that situation with Oracle, you could compile your programs to access the DB directly.
To avoid having even the most simple program becoming a huge blob, the DB authors came up with the DB server.  A single program had access to the DB, others wanting to use the DB, had to "talk" with that program, which executed their requests and sent back the results.  That single program has become a complete library, you can't count the processes of a single Oracle DB on your fingers and toes.
Now, "talking" between programs is inter-process communication.  One possibility is a "socket", no port needed, on the unixes it is presented as a file to which the program can write a request, the server reads it, executes, and writes the result, which the program can read.   Another possibility is TCP/IP, for which you need to know the port number.  The program sends its requests to that port, on which the DB server is listening, etc.  TCP is much more secure than UDP, slower, but no packets can get lost. You can't afford that with a DB.  Another big advantage: programs running on other systems can use the DB.


How to improve this query (using table variables or not?)

You can do this with a correlated sub query.

SELECT Cost,
       Cost * (SELECT TOP 1 Percentt
               FROM   Taxes t
               WHERE  t.MonthsId <= tc.MonthsId
               ORDER  BY t.MonthsId DESC) AS TaxedCost
FROM   TableCost tc

You may well get a more efficient plan though if you simply fill in the gaps in your Taxes table so you can do a straight forward join on MonthsId. At least that should allow the optimiser to consider something other than nested loops.


Simply answer:

DBCC SHRINKFILE('DB filename', 10240)

OR for MDF file

DBCC SHRINKFILE(1, 10240)

But if you have more than 10240 Mb of data in MDF file - it woun't help, then you have to add extra file to DB - where to move extra data from MDF file.

From BOL:

  DBCC SHRINKFILE

  Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file
  to other files in the same filegroup, allowing the file to be removed
  from the database. You can shrink a file to a size that is less than
  the size specified when it was created. This resets the minimum file
  size to the new value.

How to know filenames in DB:

SELECT file_Id, name FROM sys.database_files

Shrinking data files always affects performance - it makes it worse, so you really need to have a REASON to do that.


Below is a PowerShell script example that uses the T-SQL Script DOM parser to parse all existing T-SQL modules in the specified database and display errors if any cannot be parsed for the target SQL version. This will identify discontinued syntax for RAISERROR and any other discontinued T-SQL syntax as well.

$scriptDomAssemblyPath = "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
$scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/140.3881.1"

# if T-SQL script DOM assembly doesn't already exist at specifed path, download from NuGet and extract assembly from package to $scriptDomAssemblyPath
if(![System.IO.File]::Exists($scriptDomAssemblyPath)) {
    if([String]::IsNullOrWhiteSpace($scriptDomNuGetUrl)) {
        throw "Script DOM assembly not found at $scriptDomAssemblyPath and NuGet package download Url not specified"
    }
    $response = Invoke-WebRequest -Uri $scriptDomNuGetUrl
    if ($response.StatusCode -ne 200) {
        throw "Unable to download Microsoft.SqlServer.TransactSql.ScriptDom NuGet package: $response.StatusCode : $response.StatusDescription"
    }

    $tempZipFilePath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName() + ".zip")
    [System.IO.File]::WriteAllBytes($tempZipFilePath, $response.Content)
    $response.BaseResponse.Dispose()
    $tempUnzipFolderPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName())
    Expand-Archive -Path $tempZipFilePath -DestinationPath $tempUnzipFolderPath
    $tempZipFilePath | Remove-Item
    Move-Item "$tempUnzipFolderPath\lib\net46\Microsoft.SqlServer.TransactSql.ScriptDom.dll" $scriptDomAssemblyPath
    $tempUnzipFolderPath | Remove-Item -Recurse
}

Add-Type -Path $scriptDomAssemblyPath

# use the appropriate TSqlParser version for target SQL version (TSql120Parser for SQL Server 2014)
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]

# parse existing T-SQL modules in database
$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI;Application Name=Parse Modules"
$connection = New-object System.Data.SqlClient.SqlConnection($connectionString);
$command = New-object System.Data.SqlClient.SqlCommand("SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName, OBJECT_NAME(object_id) AS ObjectName, definition FROM sys.sql_modules;", $connection);
$connection.Open()
$reader = $command.ExecuteReader()
while ($reader.Read()) {

    $scriptReader = New-Object System.IO.StringReader($reader["definition"])
    $frament = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0) {

        Write-Host "error parsing [$($reader["SchemaName"])].[$($reader["ObjectName"])]"
        foreach ($parseError in $parseErrors) {
            Write-Host "`t$($parseError.Message)"
        }
    }

}
$reader.Close()
$connection.Close()


Keep original column names in SSRS report designer 3.0?

Find and Replace one of the desired characters, such as an Underscore, in the dataset query SQL with a non-sense string, such
as "3145234jhasdf" ..... (minus the quotes).
In Report Builder, create a DataSet with the resulting data.
Create a Tablix with that DataSet.  The headers in the Tablix will now look odd.  As an example the header, "Column_One", will now look something like this: "Column3145234jhasdfOne".
Save the file on your desktop or other local directory.  It will be an RDL file.  Close Report Builder.
Open the resulting RDL file you saved in a text editor, such as Notepad++.  You will see XML code.
Find and Replace your non-sense string: "3145234jhasdf", with the character you want it to display your headers: "_".
Save the file. Close the text editor.
Open the file in Report Builder.  Your headers should look like you want it.

You can replace more than one type of character by choosing a different non-sense string for each.  For ease, keep the string alphanumeric with no spaces.


Running Total which Resets when Customer changes (keeping existing RowNumbers)- attempt included

This should do what you want:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY Client ORDER BY RowNr)
    FROM #Checks
), CTE2 AS
(
    SELECT  *,
            RN2 = RowNr - RN
    FROM CTE
)
SELECT A.Client, A.RowNr, A.Amount, B.RunTotal
FROM CTE2 A
CROSS APPLY (SELECT SUM(Amount) RunTotal
             FROM CTE2
             WHERE Client = A.Client
             AND RN2 = A.RN2
             AND RowNr <= A.RowNr) B
ORDER BY RowNr

Results:

╔═══════════╦═══════╦════════╦══════════╗
║  Client   ║ RowNr ║ Amount ║ RunTotal ║
╠═══════════╬═══════╬════════╬══════════╣
║ Company A ║     1 ║ 1.00   ║ 1.00     ║
║ Company A ║     2 ║ 1.00   ║ 2.00     ║
║ Company A ║     3 ║ 5.00   ║ 7.00     ║
║ Company B ║     4 ║ 3.00   ║ 3.00     ║
║ Company A ║     5 ║ 1.00   ║ 1.00     ║
║ Company B ║     6 ║ 2.00   ║ 2.00     ║
║ Company B ║     7 ║ 1.00   ║ 3.00     ║
║ Company B ║     8 ║ 5.00   ║ 8.00     ║
╚═══════════╩═══════╩════════╩══════════╝


If it was a radio list where only one option could be specified then I'd use one column and store the selected value if something other than "other" were picked and the value from the free-text box otherwise.

As it appears that your application allows selecting multiple item (which may or may not include "other") then you need to store a list of possible options per form. A boolean per check box may be inefficient, but do avoid a comma separated list (i.e. a field called "foods" in which you store "rice,soup" in the example you give) as that can make reporting (amongst other things) difficult.

How I would go about storing this data would depend on the amount on the rest of the data set: there are a couple of methods that are optimal for different circumstances. Could you update the question to give more details about the application and its data?


Wrap this up in a stored procedure then:

CREATE PROCEDURE GetYearCounts
    @year INT
AS
    SELECT YEAR(crdate) AS y,
           MONTH(crdate) AS m,
           COUNT(*) AS tally
    FROM   MESSAGE
    WHERE  YEAR(crdate) = @year
           AND SUBJECT <> 'Welcome' --exclude default messages
    GROUP BY
           YEAR(crdate),
           MONTH(crdate);
GO

This will allow you to specify the year you want to get the data from in the call.  I.e.

EXEC GetYearCounts @year = 2012;
GO

  This is what I have but its limiting in that I need to manually change years.

Given your requirements there is no way to do this automatically.


Can an AD service account host SPNs for multiple SQL Instances?

Yes it can. Eventually If you register too much spns with the same maybe you can fill the ad attribute. SPN is a non-linked-value attribute, and so you run the risk of overrunning the hard-coded 8K limitation within the underlying ESE database.
What you can't do is to share one spn registration with two service accounts.
My suggestion is to take advantage of this tool:
Microsoft® Kerberos Configuration Manager
https://www.microsoft.com/en-us/download/details.aspx?id=39046
Run it (as admin) and it will generate the proper setspn command for kerberos setup.
Eventually you have to register your instances both with netbios & fqdn names.
And please remember that you can register an spn on a domain user account or on a domain coumputer account.


Using postgres database from another computer

Read the article Configuring PostgreSQL to Accept Connections From Computers on Your Network where it is explained with screen shots.


How do you create a job in SQL server 2008 R2 that will clear transaction logs?

You can use Ola Hallengren's  SQL Server Backup Solution.

Especially the CleanupTime will help you to delete the old backups (FULL / Diff or Transaction log backups).

  Specify the time, in hours, after which the backup files are deleted.


In past I noticed that query plan could be different, because of the Locale (language) being used on the client. Mostly the locale is enherited from client OS.


Reasonable way to predict query performance over time

With a good index present, the time taken to locate a matching row should scale roughly logarithmically, as long as you have room for the index in memory.
I'd make the index UNIQUE since the basename must be unique otherwise your workflow is invalid, and it makes the index more efficient.
CREATE UNIQUE INDEX IX_raw_records_basename
ON dbo.raw_records (basename);

Check the execution plan for the query to ensure the index is being used.
Make sure you have enough room in memory for the index, and assuming concurrency won't be a massive problem, you should be good for a very large number of rows.
I'd reconsider the length of the basename and filename columns since the query optimizer will use the length when calculating how much memory it will need to allocate to run the query.  If, for instance, the basename column will never hold more than 20 characters, but you have it defined as 512 characters, the memory grant for SELECT basename FROM dbo.raw_records; would be 25.6 times larger than actually required.  Column lengths are actually much more important than most people realize.
You could also change the query to be SELECT 1 FROM table1 WHERE basename = <basename> that way you wouldn't even need the id since all you're trying to do is verify its existence. Only do what you really need.  It looks like the index you show in your question would work fine for that.
In addition, if space is an issue you may want to consider data compression of the indexes and table.  This should allow the index to fit in a smaller memory footprint.  Evaluate DATA_COMPRESSION = ROW versus DATA_COMPRESSION = PAGE to see which is the best compression method for your requirements.


You need to use a window function like ROW_NUMBER() to generate a unique ID sorted on the WeekKey partitioned by IDNumber.
WITH CTE_Table1_Sorted AS -- CTE of the table1 dataset with a SortId generated from the order of the WeekKey field
(
   SELECT IDNumber, Attribute, WeekKey, ROW_NUMBER() OVER (PARTITION BY IDNumber ORDER BY WeekKey) AS SortId -- Generates a unique ID for every record, in the order of WeekKey, from least to greatest
   FROM table1
)

SELECT IDNumber, Attribute, WeekKey AS StartWeek
FROM CTE_Table1_Sorted
WHERE SortId = 1 -- Filter out every other record except the one with the least WeekKey, per IDNumber
Order by IDNumber


What is the safe way to update production data if data found inconsistent

How do you guys fix the data in production

Very very carefully!

This may be too general a question: there are many variables involved regarding data size, error severity, how active that data is, what service level agreements you have with the client, ...

Some tips:

Test the scripts in your dev/test environments as extensively as time allows.
If you can, take a copy of the production system to test the scripts on. You probably need to do this on production services if your application handles PPI or other sensitive data.
If you have an upcoming maintenance window when you can offline the production environment to test and apply the change (and rollback the DB if something goes wrong).
If there is time in that window take a fresh copy of production database to retest before applying to production proper.
Take a backup of production, so you can rollback if the update fails and makes more mess.
Apply the change to production and test that the result is as desired.
If all is well, drop the extra test copies and turn production back on, if not rollback to your backups and try again...

Of course if the data fix needs to be made more urgently, you may need to be much less cautious than the above list.


How can I wipe all databases and users from a SQL Server database?

Assuming you can get access to the box (if you need to recover access, see the link Kin already pointed out), you need to:

Drop all user databases. Please run the PRINT first and make sure you're not dropping anything you may regret. You may want to take backups of all databases first just in case.

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

SELECT @sql += N'
  DROP DATABASE ' + QUOTENAME(name)
  + N';'
FROM sys.databases
WHERE name NOT IN (N'master',N'tempdb',N'model',N'msdb');

PRINT @sql;
-- EXEC master.sys.sp_executesql @sql;

Drop all non-system logins. Again, please run the PRINT command first, and look the list over very carefully. I have a filter there to prevent dropping yourself if you are logged in via Windows Authentication, but you may want to add additional specific filters in the event you are logged in as sa or similar and don't want to drop your Windows login.

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

SELECT @sql+= N'
  DROP LOGIN ' + QUOTENAME(name) + ';'
FROM sys.server_principals
WHERE name <> N'sa'
AND name NOT LIKE N'##%'
AND name NOT LIKE N'NT [AS]%'
AND [type] IN ('R', 'S', 'U', 'G')
AND principal_id > 256
AND name <> SUSER_SNAME(); -- don't drop yourself!

PRINT @sql;
-- EXEC master.sys.sp_executesql @sql;

Correct the default database for any logins you keep. You may find you can lock yourself out if your default database no longer exists. So, set your login to default to master or tempdb:

ALTER LOGIN [DOMAIN\Login] WITH DEFAULT_DATABASE = tempdb;

Correct any jobs/maintenance plans that reference old databases. You'll need to look through the list manually and identify anything that touches these databases, otherwise they'll start failing. You can get a starting list with this, but this won't necessarily be a complete list - it won't tell you if the job uses ad hoc SQL that references a database with three-part naming or calls a stored procedure that does so. Thankfully you won't have to worry about synonyms (unless some evil person put synonyms in system databases).

SELECT j.name
FROM msdb.dbo.sysjobs AS j
WHERE EXISTS
(
  SELECT 1
    FROM msdb.dbo.sysjobsteps AS js
    WHERE js.job_id = j.job_id
    AND js.database_name NOT IN (N'msdb',N'master',N'tempdb',N'model')
);

In fact, you may want to run this step first, so that you can clean up all the jobs before they start failing. I'd also validate that all the jobs that remain still have valid owners. You'll have to correct any of these jobs after you delete any logins:

SELECT j.name FROM msdb.dbo.sysjobs AS j
WHERE NOT EXISTS
(
  SELECT 1
    FROM sys.server_principals AS p
    WHERE p.sid = j.owner_sid
);


How to get a breakdown of CPU usage at each point in time?

You can turn on Enhanced Monitoring and Performance Insights for more detail by modifying the RDS instance in the portal.

RDS > Databases > databasename > Modify

Also, on an RDS instance you can run Glenn Berry's DMV scripts and most of the queries will work. The stuff that breaks generally centers around drives, the errorlog, agent jobs, and the registry. Just download his scripts for the version of SQL you are running and search on CPU. You can start with the one titled - "Get CPU Utilization History for last 256 minutes (in one minute intervals)".

https://glennsqlperformance.com/2020/02/04/sql-server-diagnostic-information-queries-for-february-2020/

Depending on the version of SQL your RDS instance is running (must be SQL 2016 or newer), you can turn on Query Store as well.


Exported CSV from MySQL doesn't appear in specified directory

In windows use \\ or / while using in path of file. your command should be as follow

  SELECT * INTO OUTFILE 'C:\\Users\\Jim Smith\\Desktop\\zyzyz.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\r\n'
  from  limit 5;

or

  SELECT * INTO OUTFILE 'C:/Users/Jim Smith/Desktop/zyzyz.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\r\n'
  from  limit 5;

Hope it helps


Which database is designed for storing and incrementing values?

To be honest I think NoSQL would be a bad match here.  The eventual consistency traps may be an issue with very frequently updated information and this has problems here.

A better approach IMO would be to start with a standard SQL db (PostgreSQL is a good choice) and do things by logging visits and, asynchronously (separate process in the background) occasionally set counters at roll-forward points.  This avoids lock contention.

I doubt your problem is actually a matter of write intensiveness so much as it is lock contention.  What's probably happening is that you have several processes trying write to the same counter and only one can be allowed to autoritatively do this at a time.  By doing a log, aggregate, and snapshot approach you can avoid this lock contention because the high writes will occur mostly in sequential disk I/O (at least in PostgreSQL).

If I am right and the issue is lock contention I don't really see a viable alternative.  You have to have some way of incrementing a counter atomically and you don't want several concurrent attempts to be treated as a single attempt, incrementing by 1.


Strange behavior of TOP 1 clause sql server

But the data in your order by clause doesn't provide any ordering
verification_code is null
and all values for
created_date are the same
you have to give the engine a little bit to work with


Remove question marks from database(latin1_general_ci table)?

so, I understand that you just want to remove '?' have you tried this:

UPDATE tablename SET column=REPLACE(column,'?','');


How to unify three separate tables having same data, same columns but tuples in different languages. Only one key(caption) is common to all

If I understand your question correctly this is a possible solution. If not, could you please describe your problem more precisely?

To unify 3 tables to one virtual table you can create a view which has the following select query:

select 'en' lang, col1, col2, col3, col4, ... from table1
union all
select 'ru' lang, col1, col2, col3, col4, ... from table2
union all
select 'es' lang, col1, col2, col3, col4, ... from table3

If you need to modify the data in these 3 tables as well you have to know where the data came from. This is why I added the column "lang" -- perhaps it already exists.
With an instead-of triggers you can also manipulate the data in the table even though you insert/update/delete the view.

So here is an example:

update view set col4 = 'asdf' where lang = 'en' and col1 = 12345;

--> In the instead-of trigger you can then rewrite the update to the actual table:

update table1 set col4 = 'asdf' where col1 = 12345;