Update fields with multiple values from different tables in MySQL

Full example.

1) Create tables and data (code)

CREATE TABLE Product_categories (product_reference VARCHAR(8), category_id INT, subcategories VARCHAR(8));
INSERT INTO Product_categories VALUES
    ('REF.001',1,'1/2'),
    ('REF.002',2,'1/2/3/4');

CREATE TABLE Categories (id INT, name VARCHAR(32));
INSERT INTO Categories VALUES
    (1,'example_category_1'),
    (2,'example_category_2'),
    (3,'example_category_3'),
    (4,'example_category_4');

CREATE TABLE Importer (product_reference VARCHAR(8), category VARCHAR(128));
INSERT INTO Importer (product_reference) VALUES
    ('REF.001'),
    ('REF.002');

2) Check data (console output)

mysql> SELECT * FROM Product_categories;
+-------------------+-------------+---------------+
| product_reference | category_id | subcategories |
+-------------------+-------------+---------------+
| REF.001           |           1 | 1/2           |
| REF.002           |           2 | 1/2/3/4       |
+-------------------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM Categories;
+------+--------------------+
| id   | name               |
+------+--------------------+
|    1 | example_category_1 |
|    2 | example_category_2 |
|    3 | example_category_3 |
|    4 | example_category_4 |
+------+--------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM Importer;
+-------------------+----------+
| product_reference | category |
+-------------------+----------+
| REF.001           | NULL     |
| REF.002           | NULL     |
+-------------------+----------+
2 rows in set (0.00 sec)

3) Perform update (code)

UPDATE Importer i, (SELECT pc.product_reference,GROUP_CONCAT(c.name ORDER BY n.n SEPARATOR '>') category
                    FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) n
                    JOIN Product_categories pc
                    JOIN Categories c ON SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('',pc.subcategories,'////'),'/',n.n),'/',-1) = c.id
                    GROUP BY pc.product_reference
                   ) d
SET i.category = d.category
WHERE i.product_reference = d.product_reference;

4) Check result (console output)

mysql> SELECT * FROM Importer;
+-------------------+-----------------------------------------------------------------------------+
| product_reference | category                                                                    |
+-------------------+-----------------------------------------------------------------------------+
| REF.001           | example_category_1>example_category_2                                       |
| REF.002           | example_category_1>example_category_2>example_category_3>example_category_4 |
+-------------------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)


Mysql insert fixed column value for each row during multi-insert

I am surely too late for you, but maybe somebody will have similar question in the future.

A way how to accomplish that is by creating temporary table #temptable, and while copying data to actual table, add constant column:

# en-US.sql
INSERT INTO #temptable (resource_key, resource_value) VALUES
    ("GEN.YES", "Yes"),
    ("GEN.NO", "No"),
    ("GEN.MAYBE", "Maybe")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

INSERT INTO table (resource_key, resource_value, locale) VALUES
    (SELECT t.*, "en-US" FROM #temptable AS t);


Get all distinct values of specific column in all tables in a given schema (Postgres)

You can use query_to_xml() to dynamically run a SQL statement and get the result as an XML string. Using XMLTABLE this can then be converted back into rows and columns
with queries as (
  select format('select distinct %L as table_name, %I as col from %I.%I', c.table_name, c.column_name, c.table_schema, c.table_name) as sql
  from information_schema.columns c
  where c.table_schema = 'public'
    and c.column_name = 'query_me'
), results as (
  select query_to_xml(q.sql, true, false, '')  as data
  from queries q
)
select x.*
from results r
  cross join xmltable('/table/row' passing r.data
                       columns table_name text path 'table_name',
                               value      text path 'col') as x
order by x.table_name

The first CTE returns something like this:
select distinct 'sql_test_1' as table_name, query_me as col from public.sql_test_1
select distinct 'sql_test_2' as table_name, query_me as col from public.sql_test_2

The second CTE then runs each of those queries and returns the result as XML. Something like this:
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
  <table_name>sql_test_1</table_name>
  <col>c</col>
</row>

<row>
  <table_name>sql_test_1</table_name>
  <col>a</col>
</row>

<row>
  <table_name>sql_test_1</table_name>
  <col>b</col>
</row>

</table>

And the final step then extracts the values from the generated XML and returns this based on your sample data:
table_name | value
-----------+------
sql_test_1 | c
sql_test_1 | a
sql_test_1 | b
sql_test_2 | g
sql_test_2 | f
sql_test_2 | a

Online example


In my experience 5.6/5.7 is notorious for not spotting the correct index to choose for (range, range) type queries on large tables. You could try an analyze readings to see if it improves matters but I doubt it.

I have seen many examples of a SELECT on a large table filtered with a range drawn from a small list of possible values (your IN (1, 2, 3)) and an additional filter from small range drawn a massive spread of values (your created_dt BETWEEN) ... and at some threshold the planner will always choose to filter on the small list, leading to the results you observe.

The only way I have ever been able to solve this problem is either force the index if I can alter the query, or archive a significant (think 30-40%) chunk of the historical data to have the planner once again begin to choose the correct index.

Mysql 8 might be better, but I haven't any experience with really large tables on it yet.


MySql Multi Master Active Active Mutli Write

1 & 2)
You should look at [auto_increment_increment] and auto_increment_offset variables.
This prevents the two masters from creating the same Primary Key (assuming you are using Auto Increment Values).

3)
Not Natively, for this you would need to look at moving to one of the Galera Cluster variants.

As it stands I wouldn't recommend an Active Active environment because you will almost certainly run into problems when the network drops out / replication stops on one or both sides.


MySQL change user login from one plugin type to another

Without knowing the user passwords, you can:

Migrate the user accounts as you have described in your question (SHOW CREATE USER)
Set the passwords to 'expired'
ALTER USER user@host PASSWORD EXPIRE;
Ask the users to login and change the passwords. On an expired password, login is possible but the only action possible is to SET the password.


Using T-SQL to dynamically create Extended Properties

As Jon mentioned, you can do it using system stored procedures.

Ref: Glenn Berry wrote this for 2008 and this is to add extended property on a table. Also MSSQL Tips has a good example as well.

You can adapt his script for columns as well.

-- Routine to add or update an extended property on a table
DECLARE @TableName SYSNAME
set @TableName = N'Kin_Test';   -- Name of table
DECLARE @MS_DescriptionValue NVARCHAR(200);
SET @MS_DescriptionValue = N'Short table description - Kin';    -- Short description
DECLARE @MS_Description NVARCHAR(200)
set @MS_Description = NULL;

SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(200)) AS [MS_Description]
FROM sys.extended_properties AS ep
WHERE ep.major_id = OBJECT_ID(@TableName)
AND ep.name = N'MS_Description' AND ep.minor_id = 0);

IF @MS_Description IS NULL
    BEGIN
        EXEC sys.sp_addextendedproperty
         @name  = N'MS_Description',
         @value = @MS_DescriptionValue,
         @level0type = N'SCHEMA',
         @level0name = N'dbo',
         @level1type = N'TABLE',
         @level1name = @TableName;
    END
ELSE
    BEGIN
        EXEC sys.sp_updateextendedproperty
         @name  = N'MS_Description',
         @value = @MS_DescriptionValue,
         @level0type = N'SCHEMA',
         @level0name = N'dbo',
         @level1type = N'TABLE',
         @level1name = @TableName;
    END
GO
-- End of routine


The amount of memory specified in innodb_buffer_pool_size is allocated when the MySQL Server daemon starts up, and if the system doesn't have that much memory free at that moment, MySQL won't start.

The amount of memory you specify is what the buffer pool gets, and it neither grows nor shrinks.

InnoDB reads pages from disk into the pool as they are needed for queries, and the data remains in the pool for future queries until the least-recently-used data is eventually evicted by different data, when the pool is out of space... but the allocated memory is not available for any other process.

MySQL will request additional memory from the system for various other buffers needed by individual threads, but when the buffer pool itself is exhausted for space, no additional memory will be used for storing cached table pages.  Anything that doesn't fit into the buffer pool will not persist in memory for subsequent access.

Ideally MySQL runs on its own hardware.  An environment where MySQL is competing with other processes for memory is not optimum.  This is particularly true with a web server like Apache on the same machine, which commonly causes problems under load, because both the web server and the database are making demands for memory at the same time.


Microsoft SQL Server 2014 Nested From Query in Cross-Apply

is this a bug in SQL Server?

Yes, certainly, the 1 that is returned in all rows in your final result only exists in the first row of the outer input so shouldn't even be in scope for the subsequent rows. It looks like the same basic issue as looked at in detail by Paul White here.

I executed your final query in dbfiddle (SQL Server 2019) and pasted the plan here https://www.brentozar.com/pastetheplan/?id=Sy4sBB5lI  It looks like the sort executes 4 times (once for each outer row) but for some reason it rewinds rather than rebinds so doesn't call the child operators of the sort more than once. This is a bug as the reference to the correlated parameter of the outer join (Union1004) should cause a rebind when this has changed value. As a result the reference to Union1004 in Node 5 of the plan is never re-evaluated.

I see you have now reported it here https://feedback.azure.com/forums/908035-sql-server/suggestions/39428632-microsoft-sql-server-2014-incorrect-result-when-s

  Is there any possibility to force evaluation of the nested query for every row?

Adding the query hint OPTION (MERGE UNION) works for your example, I don't know if this will necessarily be sufficient to avoid the bug in all cases but from the linked Paul White answer it appears it should work. In the case of your example it works as the sort is pushed down lower in the plan so it only rewinds the TestCaseTemp rows, not the entire unioned result. You could also add appropriate indexing to remove the sort entirely.


As you just want the TOP 10 ordered by name it thinks it will be quicker to work down the index on name in order and look to see if each row matches the CONTAINS(Name, '"John" AND "Smith"') ) predicate.

Presumably it takes many more rows to find the 10 matches required then it expects and this cardinality issue is compounded by the number of key lookups.

A quick hack to stop it using this plan would be to change the ORDER BY to ORDER BY Name + '' although using CONTAINSTABLE in conjunction with FORCE ORDER should also work.


Is there a way to find out if value exists with addToSet?

MongoDB does not return the results of field-level updates like an $addToSet operator; the overall document update either succeeds or fails.

However, assuming you are using MongoDB 2.6 or newer, you can infer whether the update was a no-op by inspecting the WriteResult returned on update/upsert.

Example below using MongoDB 3.2.

Test data:

db.fruit.insert([
    { _id: 'a', tasty: ['apple', 'banana', 'pear']},
    { _id: 'b', tasty: ['kumquat', 'banana', 'mango']},
    { _id: 'c', tasty: ['plum'] }
])

Value was added to array

Add "mango" to the tasty array in the a document:

db.fruit.update(
    { _id: 'a' },
    { $addToSet: { 'tasty': 'mango' }}
)

WriteResult({
  "nMatched": 1,
  "nUpserted": 0,
  "nModified": 1
})

Since the only possible update was to the array, nModified:1 indicates that "mango" was added.

Value already exists in the array

Add "mango" to the tasty array in the b document:

db.fruit.update(
    { _id: 'b' },
    { $addToSet: { 'tasty': 'mango' }}
)

WriteResult({
  "nMatched": 1,
  "nUpserted": 0,
  "nModified": 0
})

Since the only possible update was to the array, nModified:0 indicates that "mango" already existed.

Updating with more fields

If you combine multiple field updates, you won't know which field was actually updated. In this case, you could specifically match documents where the array value isn't set already:

db.fruit.update(
    // query
     {
         _id: 'c',
         tasty: { $nin: ['mango'] }
     },

     // update
    {
        $set: { fruitBasket: true },
        $addToSet: { 'tasty': 'mango' }
    }
)

WriteResult({
  "nMatched": 1,
  "nUpserted": 0,
  "nModified": 1
})


Match string pattern to any array element

It would be nice if PostgreSQL allowed ANY on the left hand operand, but sadly it doesn't.  So you need the commutator of LIKE.  PostgreSQL doesn't come with one, but you can create your own.

You need a function that reverses the order of arguments to LIKE, and then use that to create an operator that can be used in conjunction with ANY.  User created operators have to be named with symbols, not text, so I'm picking '<~~'.  '~~' is the built-in synonym for LIKE, so I'm adding '<' as a pneumonic to make it "go the other way".  You can pick any unused name you want, though.

create function reverse_like (text, text) returns boolean language sql as $$ select $2 like $1 $$;
create operator <~~ ( function =reverse_like, leftarg = text, rightarg=text );
SELECT 'ab%' <~~ ANY('{"abc","def"}');

You should probably tag the function as immutable, and if you are using v9.6 or above, also as parallel safe.

create or replace function reverse_like (text, text) returns boolean language sql as
 $$ select $2 like $1 $$ immutable parallel safe;


For a one-time use, you don't need to persist a function. Use a DO statement:

DO
$do$
DECLARE
   rec record;
BEGIN
FOR rec IN
   SELECT a.attrelid::regclass::text AS tbl, a.attname
   FROM   pg_class c
   JOIN   pg_attribute a ON a.attrelid = c.oid
   WHERE  c.relkind = 'r'
   AND    a.attname = 'timestamp'
   AND    a.atttypid = 'timestamp'::regtype
   AND    a.attnum > 0
   AND    NOT a.attisdropped
LOOP
   RAISE NOTICE '%', format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
   -- Check test output before uncommenting EXECUTE!
   -- EXECUTE format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
END LOOP;
END
$do$;

Major points

As mentioned at the top, probably no need for a function. The body of a DO statement is identical, default language is plpgsql.
Use the implicit cursor of a FOR loop. Simpler and faster. Explicit cursors are rarely necessary in plpgsql.
Table names are not unique in a Postgres database. There can be any number of tables with the same name in multiple schemas.
Avoid SQL injection and basic exceptions with non-standard (double quoted) identifiers.

A cast to regclass takes care of the table name. At the same time, table names are schema-qualified automatically where necessary (taking the current search_path into account).
format() with %I sanitizes the column names.

Details:

SQL injection in Postgres functions vs prepared queries

I assume you are aware of timestamp and timstamptz data types? This only removes columns with data type timestamp [without time zone].
Identify the data type with a.atttypid = 'timestamp'::regtype, so you don't have to join to pg_type. Simpler.
Exclude system columns and dead columns:
AND    a.attnum > 0
AND    NOT a.attisdroppedand


To pinpoint the issue being with your installation of SSMS on your workstation, can you launch management studio on the database server, assuming you have installed it on the server, and, if so, do you get the same behavior?


How to check user defined schemata creation date in SQL server

You may find the information in sys.traces as long as the information not reset which happens upon service restart.

If that's not the case, try following query, but this is quick solution, the proper solution in your case is Extended Events which stores the selected trace events information outsize SQL Databases, so that you can retrieve  and monitor such event info whenever you need since the captured data won't be retested upon service restart.

For more details on all event classes and sys.traces

select PATH, Trace.*
from sys.traces AS T
        CROSS APPLY fn_trace_gettable(T.path, default) as Trace
Where ObjectName like '%YourSchema%'
go


Can't get past password lock on fresh install of postgreSQL

Try to add the option -U postgres to your createdb command.  The error clearly states that PostgreSQL tried to execute it with the current Windows username, while you set superuser password (presumably) for user postgres.


How do I select the total count of records over the past week, including days which have no records in MySQL

The most straightforward approach is to use a date table with all possible dates, then OUTER APPLY to get your hit count for each date, where appropriate.
Something like this:
SELECT  d.Date
        ,ISNULL(hc.HitCount, 0) AS HitCount
FROM    dbo.Date AS d
        OUTER APPLY (
                        SELECT  COUNT(1) AS HitCount
                        FROM    Table AS t
                        WHERE   1 = 1
                                AND t.Date = d.Date
                    ) AS hc
WHERE   1 = 1
        AND d.Date BETWEEN @StartDate AND @EndDate;

Here's a script to build and fill your date table:
CREATE PROC [dbo].[usp_GrowDateDimension]
(
    @Years TINYINT NULL = 25
)
AS
DECLARE @MaxDate DATE;

IF NOT EXISTS (
                  SELECT    1
                  FROM      sys.objects AS o
                  WHERE     o.name = 'Date'
              )
BEGIN
    CREATE TABLE dbo.Date
    (
        DateKey               INT          NOT NULL
        ,FullDateAlternateKey DATE         NOT NULL
        ,DayNumberOfWeek      TINYINT      NOT NULL
        ,EnglishDayNameOfWeek NVARCHAR(10) NOT NULL
        ,DayNumberOfMonth     TINYINT      NOT NULL
        ,DayNumberOfYear      SMALLINT     NOT NULL
        ,WeekNumberOfYear     TINYINT      NOT NULL
        ,EnglishMonthName     NVARCHAR(10) NOT NULL
        ,MonthNumberOfYear    TINYINT      NOT NULL
        ,CalendarQuarter      TINYINT      NOT NULL
        ,CalendarYear         SMALLINT     NOT NULL
        ,CalendarSemester     TINYINT      NOT NULL
        ,Yesterday            AS (CASE
                                      WHEN FullDateAlternateKey = CONVERT(DATE, DATEADD(DAY, (-1), GETDATE())) THEN (1)
                                      ELSE (0)
                                  END
                                 )
    );
END;

/* Get ceiling of Date dimension */
SELECT  @MaxDate = MAX(d.FullDateAlternateKey)
FROM    dbo.Date AS d
WHERE   d.FullDateAlternateKey <> '2999-12-31';

/* Set Start/Stop points based on ceiling */
DECLARE @CurrentDate DATE = DATEADD(DAY, 1, ISNULL(@MaxDate, '2000-12-31'));
DECLARE @EndDate DATE = DATEADD(YEAR, @Years, @CurrentDate);

/* Insert Rows */
WHILE @CurrentDate < @EndDate
BEGIN
    INSERT INTO dbo.Date
    (
        DateKey
        ,FullDateAlternateKey
        ,DayNumberOfWeek
        ,EnglishDayNameOfWeek
        ,DayNumberOfMonth
        ,DayNumberOfYear
        ,WeekNumberOfYear
        ,EnglishMonthName
        ,MonthNumberOfYear
        ,CalendarQuarter
        ,CalendarYear
        ,CalendarSemester
    )
    SELECT  YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate) AS DateKey
            ,@CurrentDate AS FullDateAlternateKey
            ,DATEPART(dw, @CurrentDate) AS DayNumberOfWeek
            ,DATENAME(dw, @CurrentDate) AS EnglishDayNameOfWeek
            ,DAY(@CurrentDate) AS DayNumberOfMonth
            ,DATENAME(dy, @CurrentDate) AS DayNumberOfYear
            ,DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
            ,DATENAME(mm, @CurrentDate) AS EnglishMonthName
            ,MONTH(@CurrentDate) AS MonthNumberOfYear
            ,DATEPART(q, @CurrentDate) AS CalendarQuarter
            ,YEAR(@CurrentDate) AS CalendarYear
            ,((DATEPART(QUARTER, @CurrentDate) - 1) / 2) + 1 AS CalendarSemester
    WHERE   NOT EXISTS  (
                            SELECT  1
                            FROM    dbo.Date AS d
                            WHERE   d.FullDateAlternateKey = @CurrentDate
                        );

    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate);
END;


Oracle database connection instant timeout

That timeout can be configured with the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter.

For example, place the below in sqlnet.ora, which results in outbound connections timing out after 10 seconds without answer:

SQLNET.OUTBOUND_CONNECT_TIMEOUT=10


Interesting question. It looks like in second scenario you firstly convert default charset for table:

  To change only the default character set for a table, use this
  statement: ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

So then you try to MODIFY charset of your xml column. But database
may think that this column is already in the desired charset (remember you already change default charset)

Try to use one query like this (see the same page of mysql manual):

ALTER TABLE spool
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  To change the table default character set and all character columns
  (CHAR, VARCHAR, TEXT) to a new character set, use a statement like
  this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

  The statement also changes the collation of all character columns. If
  you specify no COLLATE clause to indicate which collation to use, the
  statement uses default collation for the character set. If this
  collation is inappropriate for the intended table use (for example, if
  it would change from a case-sensitive collation to a case-insensitive
  collation), specify a collation explicitly.


If the depth of tree is limited to 3 (case of your sample). You can do like this.

(SELECT t1.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1)
UNION
(SELECT t2.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1)
UNION
(SELECT t3.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1);

My solution don't respect DRY principle but this is the only simple solution that I found for now.

If you don't know in advance the maximum depth of your tree please check following questions and links that discuss the subject :

https://stackoverflow.com/questions/5688376/mysql-select-tree-parent-ids
https://stackoverflow.com/questions/18083546/mysql-recursivetree-parent-child-category
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Hope this help!


Active/current (1-5%) and historical records in same table: index or partitioning?

Partitioning should work better than index if the number of rows is large. If the query needs to scan only one partition, it is more efficient than finding current rows through index.

If SRC_CURRENT is updated to 0 when newer rows are loaded, you need to enable row movement for the table. This will allow rows to move from current partition to historical partition.

Alternatively, if current rows are always the most recent rows, you could range-partition the table by date. This would allow you to clear expired rows very fast by running drop partition for the oldest partition. This will also benefit queries against historical data that access a range of dates but not the whole history.


Since gadget_id is a vehicle, you need to monitor two things as you look at each row

when a gadget_id switches location
when a gadget_id switches to another gadget_id

The solution lies in organizing a set of user variables to monitor that change. Please forgive you are about to see:
First, let's load your data in the test database in a table called gadget_location:
mysql> use test
Database changed
mysql> drop table gadget_location;
Query OK, 0 rows affected (0.07 sec)

mysql> create table gadget_location
    -> (
    ->     id int not null auto_increment,
    ->     gadget_id int,
    ->     location  varchar(30),
    ->     submitted_date date,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into gadget_location (gadget_id,location,submitted_date) values
    -> (1,'calicut' , '2012-07-15'), (1,'calicut' , '2012-07-14'),
    -> (1,'calicut' , '2012-07-13'), (2,'thrissur', '2012-07-12'),
    -> (1,'calicut' , '2012-07-11'), (1,'kannur'  , '2012-07-10'),
    -> (2,'thrissur', '2012-07-09'), (1,'calicut' , '2012-07-08');
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from gadget_location;
+----+-----------+----------+----------------+
| id | gadget_id | location | submitted_date |
+----+-----------+----------+----------------+
|  1 |         1 | calicut  | 2012-07-15     |
|  2 |         1 | calicut  | 2012-07-14     |
|  3 |         1 | calicut  | 2012-07-13     |
|  4 |         2 | thrissur | 2012-07-12     |
|  5 |         1 | calicut  | 2012-07-11     |
|  6 |         1 | kannur   | 2012-07-10     |
|  7 |         2 | thrissur | 2012-07-09     |
|  8 |         1 | calicut  | 2012-07-08     |
+----+-----------+----------+----------------+
8 rows in set (0.00 sec)

mysql>

OK, here comes the mess:
SET @dupcount = 0;
SET @group_number = 0;
SET @cur_gadget_id = 0;
SET @cur_location = MD5("1");
SET @cur_gadget_location = MD5("1");
SELECT gadget_id,location,GroupNumber,COUNT(1) DaysStopped FROM
(
SELECT
    *,
    @dupcount     := IF(@cur_gadget_location=gadget_location,@dupcount+1,1) Dup_Count,
    @group_number := IF(@cur_gadget_location=gadget_location,@group_number,@group_number+1) GroupNumber,
    @cur_gadget_location := gadget_location
FROM
(
    SELECT *,CONCAT(gadget_id,'-',cur_loc) gadget_location FROM
    (
        SELECT *,
        @cur_location  := IF(MD5(location)=@cur_location,@cur_location,MD5(location)) cur_loc,
        @cur_gadget_id := IF(gadget_id=@cur_gadget_id,@cur_gadget_id,gadget_id) cur_gadget
        FROM gadget_location
    ) AAA
) AA ) A GROUP BY gadget_id,location,GroupNumber;

Want to see it work ??? Here it goes:
mysql> SET @dupcount = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group_number = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_gadget_id = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_location = MD5("1");
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_gadget_location = MD5("1");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT gadget_id,location,GroupNumber,COUNT(1) DaysStopped FROM
    -> (
    -> SELECT
    ->     *,
    ->     @dupcount     := IF(@cur_gadget_location=gadget_location,@dupcount+1,1) Dup_Count,
    ->     @group_number := IF(@cur_gadget_location=gadget_location,@group_number,@group_number+1) GroupNumber,
    ->     @cur_gadget_location := gadget_location
    -> FROM
    -> (
    ->     SELECT *,CONCAT(gadget_id,'-',cur_loc) gadget_location FROM
    ->     (
    ->         SELECT *,
    ->         @cur_location  := IF(MD5(location)=@cur_location,@cur_location,MD5(location)) cur_loc,
    ->         @cur_gadget_id := IF(gadget_id=@cur_gadget_id,@cur_gadget_id,gadget_id) cur_gadget
    ->         FROM gadget_location
    ->     ) AAA
    -> ) AA ) A GROUP BY gadget_id,location,GroupNumber;
+-----------+----------+-------------+-------------+
| gadget_id | location | GroupNumber | DaysStopped |
+-----------+----------+-------------+-------------+
|         1 | calicut  |           1 |           3 |
|         1 | calicut  |           3 |           1 |
|         1 | calicut  |           6 |           1 |
|         1 | kannur   |           4 |           1 |
|         2 | thrissur |           2 |           1 |
|         2 | thrissur |           5 |           1 |
+-----------+----------+-------------+-------------+
6 rows in set (0.02 sec)

mysql>

According to this output, here is what you have:
Gadget 1

Group 1 : stopped at calicut for 3 days
Group 3 : left and came back for 1 day
Group 4 : left calicut and went to kannur for 1 day
Group 6 : left kannur and went to calicut for 1 day

Gadget 2

Group 2 : stopped for thrissur for 1 day
Group 5 : left and came back for 1 day

For some reason, the GroupNumbers came out different in SQLFiddle`. Notwithstanding, the rest of the output is the same.
I hope this is right ...


Choosing shard key and friendly URL Ids for my MongoDB

Before you go further you need to answer a few questions

how do you represent files within folders into the database
how do you represent folders
do you have relations between folders (parent -> child)
how often do you expect to create folders and files
how often do you update existing files into folders and what is the number of files you update

Based on your answers you can have a write optimized schema or a read optimized schema. Write optimized is a schema that contains many entries that are very small
or you can use built in operators like $inc over a collection. Read optimized is generally a larger collection like the one you described, into your scenario you could have very easy something like this (assuming all folders are at the same level)

{ "userid" : "email or id",
  [
     { "folder1" : [ "file1", "file2"] },
     { "folder2" : [ "file3", "file4"] },
  ]
}

But with this schema it gets quite complicated if you need to link a folder to a parent folder ... But is obvious that the userid is the shard key.


Oracle SQL for left outer join to rownum = 1 of another query?

Basically, I think you should just get the max date, if that is all you are looking for here, using your same filter, etc. You already have STATUS (=COMP) and WONUM (JOIN). If you needed the whole record from this table, and it was more complicated than this, I would recommend the oracle inline analytic functions with over/partition by logic to filter by the max date.

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM AS STATUSWONUM
                               , STATUS AS STATUS
                               , MAX(CHANGEDATE) AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        GROUP  BY WONUM, STATUS)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM )

;


Not sure what you mean, but I think you are looking for something like this:

Case
  when A_staff_Service_Type.type = 'v' and pt_service.service_code = 'N06'
     then isnull(Indirect_Duration,0)/60.00  + isnull(direct_Duration,0)/60.00
  else null
end

I also don't know if the AND is correct for your use-case. Maybe you want an OR - but based on the little information you have provided this is impossible to answer.

The obvious answer to "I want the results added" is:

Case when A_staff_Service_Type.type = 'v'
  then isnull(Indirect_Duration,0)/60.00 else 0 end
+
Case when pt_service.service_code = 'N06 '
  then isnull(direct_Duration,0)/60.00 else 0 End


I think you are backing up all the Databases.You only need to backup those three databases.use mysqldump like:

mysqldump -uuser -ppassword --databases db_name1 db_name2 db_name3 > Three_db_dump.sql

you can see various options of the mysqldump using      mysqldump --help.

In Case If you want to drop the 47 databases which you didn't want on new server If they all are having only MyISAM tables you can easily drop them.It will also reclaim the space.

If the databases have the InnoDB tables then you have to go with another process because only dropping the database doesn't recliam the InnoDB table space.For this follow the link

What is the best way to reduce the size of ibdata in mysql?


Audit Requirement - Capture Logins vs Database

For this I would default to extended events for the 2008 server and up. There is a good blog post about it here.

Additionally, for the 2005 server, you would need to use a trace as the extended event functionality wasn't available. There was another post for this shown here.

Both of these options give you different ways of storing and what to capture such as login name, time, database name, Etc.


count total matching id if a single column contain multiple value separated by comma

The root cause of your problem is that database is not normalized. Assuming your table looks like:

CREATE TABLE COURSES
( APPLICATION_CODE ... NOT NULL PRIMARY KEY
, ...
, TEACHERS VARCHAR(100) NOT NULL );

INSERT INTO COURSES (APPLICATION_CODE, TEACHERS)
VALUES ('MA101', '1,2,5');

The correct thing to do is to normalize this into two tables:

CREATE TABLE COURSES
( APPLICATION_CODE ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE COURSE_TEACHERS
( APPLICATION_CODE ... NOT NULL
, TEACHER_ID INT NOT NULL
,     PRIMARY KEY (APPLICATION_CODE, TEACHER_ID) );

INSERT INTO COURSES (APPLICATION_CODE)
VALUES ('MA101');

INSERT INTO COURSE_TEACHERS (APPLICATION_CODE, TEACHER_ID)
VALUES ('MA101', 1), ('MA101', 2), ('MA101', 5);

In this case, it is sufficient to count the rows where TEACHER_ID = 1

SELECT COUNT(1)
FROM COURSE_TEACHERS
WHERE TEACHER_ID = 1;

Not only will it be easier to ask questions like this, the queries will be much more efficient since they can utilize indexes in a way that is not possible in your current model.

That said, if you know that the teacher string always looks like for example:

' 1, 2, 5,'

You can use LIKE to count the rows:

SELECT COUNT(1)
FROM COURSES WHERE TEACHERS LIKE '% 2,%';

Pitfalls, if the last item is not terminated with the separator token (in this case ",") you need to handle that in a special predicate. The same goes for the start item, it needs to start the same way as the remaining ones.

If you can not trust the string to be in this simple form, you may be able to come up with a regular expression that describes a teacher item in the list of teachers.


How to filter out sales to find customers who have not purchased in the last 2 years

No sales in the last two years means:

having max(invc.invdate) < (SYSDATE - INTERVAL '2' YEAR)


The best way to understand how date math works is to break a query down into its parts.

Start with this:

SELECT GETUTCDATE() AS utc_date

On this particular day, it returns 2017-10-04 19:34:20.050.

In date math, the number 0 and the date 1900-01-01 are interchangeable.

   SELECT DATEDIFF(DAY, 0, GETUTCDATE()) AS [what does zero mean?],
          DATEDIFF(DAY, '19000101', GETUTCDATE()) AS [it means 1900-01-01]

Which means that you can convert nearly any number to a date. Even negative numbers.

   SELECT CONVERT(DATETIME, -1) AS [how is -1 a date?]

This will bring back 1899-12-31 00:00:00.000, which is the same thing as this:

SELECT DATEADD(DAY, -1, '19000101') AS [zero minus 1 days]

Just subtracting one day from 1900-01-01. Weird, right?

How does this help us?

Let's look inside our original query:

SELECT DATEDIFF(DAY, 0, GETUTCDATE())

That gives us the number of days between 1900-01-01 and the current date. Which means the full expression:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)

Is the adding the number of days between 1900-01-01 and current to 1900-01-01. That gives us the very start of the current day, at 0 hours.

The second part does the same thing, except subtracting 30 days from 1900-01-01, at 0 hours.

DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);

If this is all too much to remember, I totally understand.

For a cheat sheet of important date calculations, see Tim Ford's article.

For a calendar table of important dates, see Aaron Bertrand's article.


Query to report disk space allocation and used space

Here is the query Management Studio uses to populate those numbers:

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
   WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
SUM(a.total_pages) AS [SpaceUsed],
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
   WHERE df.type in (1, 3)) AS [LogSize]
FROM
sys.partitions p join sys.allocation_units a
  on p.partition_id = a.container_id
left join sys.internal_tables it
  on p.object_id = it.object_id

You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:

SELECT
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df
   WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df
   WHERE df.type in (1, 3)) AS [LogSize]
FROM sys.partitions p join sys.allocation_units a
  on p.partition_id = a.container_id;

With this update, assuming your database doesn't change in the meantime, it should yield:

753475.94    744030.07    2900.00

Doing some simple math, and isolating just the three numbers you want:

;WITH t(s) AS
(
  SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
   FROM sys.database_files
   WHERE [type] % 2 = 0
),
d(s) AS
(
  SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
   FROM sys.partitions AS p
   INNER JOIN sys.allocation_units AS a
   ON p.[partition_id] = a.container_id
)
SELECT
  Allocated_Space = t.s,
  Available_Space = t.s - d.s,
  [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
FROM t CROSS APPLY d;


MySQL: Capture every 5 values from column a in Table A to create one row in Table B

The TRIGGER can be simplified down to

INSERT ... ON DUPLICATE KEY UPDATE ...

It is not proper to have "redundant" data in a database.  Why can't you just leave it in the original table?  When you need it, do a JOIN.


Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000).

The mistake you did with your extra path expressions is that you forgot Stuff in the path.

Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found that one extra path is enough for your queries to make it use only the index to fetch the data [4] = '/Root/Stuff/Forms' as SQL nvarchar(25). You can have [3] = '/Root/Stuff/Forms' as XQUERY 'node()' in there as well and perhaps it will make a difference or not depending on what your XML look like.

Note 1: You have mixed up the names of the columns in the index and your query. That might contribute to the problems you have with extended event.

Note 2: The events for missing selective XML indexes is generated when the query is compiled. If the query plan is used from the cache you will not see any events.


I would use DATETIME2 as opposed to DATETIME for any new development work at this point. If you don't need sub-second granularity, use DATETIME2(0) and you'll see some space savings as well.

From here I would start experimenting. If performance is poor just querying off the single column, I'd then try adding persisted computed columns to separate out the date and time components. This will ensure that the separated DATE and TIME columns will always be the same as the "master" DATETIME2(n) column.


How to update a SET statement so I can use the MIN() function?

I'm not going to try to understand or re-write your entire query, but the typical form to get around this parser limitation is to calculate the aggregates in a CTE. The basic idea:

;WITH cte(id, dt) AS
(
  -- simplifying here, you'll need to with your logic
  SELECT DrvrID, MIN(datetimecolumn)
  FROM dbo.spr_DriverTimeEntry
  -- WHERE ...
  GROUP BY DrvrID
)
UPDATE t
  SET t.StartPlus8 = cte.dt
  FROM #tmpDriverTime AS t
  INNER JOIN cte
  ON cte.id = t.EmployeeNo;

Why create the #temp table with a different column name (EmployeeNo) instead of what it really represents (DrvrID)?

Are you updating a #table or setting a variable? Pick one.

Also try to avoid converting a date to a string to see if they're on the same day - this is by far one of the least efficient ways to do so.


Since you're only wanting to use DATE values to determine your historical changes, I'm assuming the data will only be updated once per day, otherwise you will potentially have multiple historical entries with the same day value that you cannot identify an order for with only a DATE value.

Given this assumption, you can possibly rely solely on the SysStartTime for beginning and end information by querying your table and history table. To this end, if you add a computed column to your table like below:

CREATE TABLE dbo.Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
      CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
    ValidFromDate AS CAST(SysStartTime AS DATE), -- This is a computed column
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
      CONSTRAINT DF_Department_SysEndTime
    DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Your history table does not have the column as computed but rather persists the value at the point of any change to the record. This won't work for SysEndTime because it will not recalculate the value when it is changed by system versioning, so it is always 9999-12-31.

Queries to Department will return the computed value, which is simply the SysStartTime datetime2 value converted to a date, and queries to DepartmentHistory will return the persisted value which is the computed value as it was at the point of being versioned (which is the SysStartTime datetime2 value converted to a date).

If you can rely only on SysStartTime for your Type 2 SCD, then this should meet your requirements.


Clustered Index and PK - Two different Objects?

The leaf level of the Clustered index is the table.  You still have the other levels of the B-Tree  (root, intermediate) as a part of the clustered index which account for why there is an index for your PK on the table.


How Does Oracle Handle Multiple Concurrent INSERTs Against One Table

The inserts don't affect each other unless they would have a conflict with unique or primary key constraints. They should be independent. I believe this concurrency issue is one of the reasons why Oracle's sequence is a separate object (cached, surrogate PK generation separated from insert).

In that respect, they operate on the uncommitted table as it is seen to their session. This behavior is described in the stackoverflow post below.

https://stackoverflow.com/questions/3194999/dml-by-multiple-users-commit-scenarios-in-oracle


Covering index used despite missing column

Case A
Query:

WHERE thread_id = 12345
  AND placeholder = FALSE
ORDER BY some_column DESC
LIMIT 20

Index:

(thread_id, date_created)

Plan:

Index is used
Using Where
Using filesort

No problem there, right? If the index is used (to partially match the WHERE condition), we still need a sort operation to order the results by some_column (which is not in the index). We also need an extra check (Using Where) to keep only the rows that match the 2nd condition, too. OK.

Case B (the question)
Query:

WHERE thread_id = 12345
  AND placeholder = FALSE
ORDER BY date_created DESC
LIMIT 20

Index:

(thread_id, date_created)

Plan:

Index is used
Using Where
-- no "Using filesort"

So, why does it not need a sort here? Because the index is enough to sort as the query wants. There is of course the additional problem of the extra condition (AND placeholder = FALSE) which is not covered by the index.

OK but we don't really need a sort here. The index can provide us with results that match the first condition (WHERE thread_id = 12345) and are in the wanted order for output. The only additional check we need - and what the plan does - is to get the rows from the table, in the order provided by the index, and check this 2nd condition until we get 20 matches. That's what the **Using Where"" means.

We may get the 20 matches in the first 20 rows (so really good and fast) or in the first 100 (still likely fast enough) or in the first 1000000 (probably very, very slow) or we may get just 19 matches from the table even after reading all the matching rows from the index (really very slow on a big table). It all depends on the distribution of data.

Case C (even better plan)
Query:

WHERE thread_id = 12345
  AND placeholder = FALSE
ORDER BY date_created DESC
LIMIT 20

Index:

(placeholder, thread_id, date_created)

Plan:

Index is used
-- no "Using Where"
-- no "Using filesort"

Now  our index matches both conditions and the order by. The plan is pretty simple: get the first* 20 matches from the index and read the corresponding rows from the table. No extra check (No "Using Where") and no sort (no "Using filesort") needed.

first*: the first 20 when reading the index backwards from the end (as we have ORDER BY .. DESC) but that's not a problem. B-tree indexes can be read forwards and backwards with almost equal performance.


Convert all the data of table (which have both encoding data) into single encoding either utf8 or latin1

Depending on what RDBMS you are using:

You can try altering the Latin1-encoded column to use an encoding of UTF-8, if the RDBMS supports such an operation.

If you are using MySQL, you should look at this related Question on S.O.:
Converting mysql tables from latin1 to utf8
You can try:

Adding a new column with an encoding of UTF-8
UPDATE new_column = Latin1_column  (I am assuming that the RDBMS would be able to implicitly convert since it knows both encodings)
DROP Latin1_column
rename new_column to be former Latin1_column's name


Assuming Point_ID is a primary key or unique key shared by the two tables, then you can do this to create a new table Table3 with the data you specified:

CREATE TABLE Table3 AS
  SELECT t1.Point_ID, t1.Size, t1.Age, t1.Dist, t1.Treatment, t2.Path, t2.Location
  FROM Table1 t1
    LEFT OUTER JOIN Table2 t2 ON t1.Point_ID = t2.Point_ID
  UNION
  SELECT Point_ID, Size, Age, Dist, Treatment, Path, Location
  FROM Table2;

For any row with Point_ID in Table1 that doesn't exist in Table2 the Path and Location columns will be NULL. If it does exist, the Path and Location columns will have the values from Table2. For any row with Point_ID in Table2 that doesn't exist in Table1, the whole row will be added to the new table Table3.


Below is a sample query to return information from the default trace using T-SQL.

SELECT
     trace.DatabaseName
    ,trace.ObjectName
    ,te.name AS EventName
    ,tsv.subclass_name
    ,trace.EventClass
    ,trace.EventSubClass
    ,trace.StartTime
    ,trace.EndTime
    ,trace.NTDomainName
    ,trace.NTUserName
    ,trace.HostName
    ,trace.ApplicationName
    ,trace.Spid
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
    FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
LEFT JOIN sys.trace_events AS te ON
    trace.EventClass=te.trace_event_id
LEFT JOIN sys.trace_subclass_values AS tsv ON
    tsv.trace_event_id = EventClass
    AND tsv.subclass_value = trace.EventSubClass
ORDER BY trace.StartTime;

By filtering on WHERE TEXTDATA LIKE 'DBCC SHRINK%' you will find all Shrink events.


How to output more than 4000 characters in sqlcmd

To be clear, the issue is really with the PRINT command and not the SQLCMD utility.

I am guessing that your variable is actually NVARCHAR(MAX), not VARCHAR(MAX) since the PRINT command is limited to only 4000 characters using NCHAR / NVARCHAR. Otherwise it can output up to 8000 characters using NVARCHAR / CHAR. To see that VARCHAR does go beyond 4000 characters, but not beyond 8000, run the following:

sqlcmd -Q "DECLARE @Yo VARCHAR(MAX) = REPLICATE(CONVERT(VARCHAR(MAX), '#'), 9000); PRINT @Yo;" -o out.txt

If you need more than 8000 characters printed, then you need to iterate over the variable in chunks of up to 8000 characters (the PRINT command will only display up to 8000 CHAR / VARCHAR characters at a time). But you don't want to simply chop it into even 8000 character chunks as that could be right in the middle of something that should not be split, like a word / object name / number, etc. Since the PRINT command will always include a newline character at the end, it is best to print up to the last newline character of each 8000 character chunk. The result is that the only lines to get split across multiple displayed lines are those that are over 8000 characters before the next newline character, and there is really nothing that can be done about that.

Try the following proc in place of your print(@sql) command (FYI: you don't need parenthesis around @sql for PRINT like you do for EXEC). Please note that finding the last newline character in a string requires creating a LastIndexOf function since one is not built into T-SQL. You can create a T-SQL UDF to do this. You can also use SQLCLR in which case you can code it yourself or simply get the Free version of the SQL# library (which I am the author of).

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

IF (OBJECT_ID(N'dbo.Display') IS NOT NULL)
BEGIN
  DROP PROCEDURE dbo.Display;
END;

GO
CREATE PROCEDURE dbo.Display
(
  @TextToDisplay VARCHAR(MAX)
)
AS
SET NOCOUNT ON;

  DECLARE @Length INT = LEN(@TextToDisplay),
          @Buffer VARCHAR(8000),
          @BufferLength INT,
          @StartIndex INT = 1,
          @LastNewlineIndex INT;

  SET @TextToDisplay = REPLACE(@TextToDisplay, CHAR(13), ''); -- normalize

  WHILE (1 = 1)
  BEGIN
    SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 8000);
    SET @BufferLength = DATALENGTH(@Buffer);

    IF (@BufferLength < 8000)
    BEGIN
      BREAK;
    END;

    SET @LastNewlineIndex =
                   SQL#.String_LastIndexOf(@Buffer, CHAR(10), @BufferLength, 1);

    IF (@LastNewlineIndex > 0)
    BEGIN
      PRINT SUBSTRING(@Buffer, 1, (@LastNewlineIndex - 1));
      SET @StartIndex += @LastNewlineIndex;
    END;
    ELSE
    BEGIN
      PRINT @Buffer;
      SET @StartIndex += @BufferLength;
    END;
  END; -- WHILE (1 = 1)

  -- Don't print empty line if final chunk was 8000 chars leaving final loop with 0
  IF (DATALENGTH(@Buffer) > 0)
  BEGIN
    PRINT @Buffer;
  END;
GO

Tests:

DECLARE @Test VARCHAR(MAX);
SET @Test = '';
SET @Test = @Test + '1' + REPLICATE('a', 7998) + '1';
SET @Test = @Test + '2' + REPLICATE('b', 7998) + '2';
SET @Test = @Test + '3' + REPLICATE('c', 7998) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';
----
SET @Test = '';
SET @Test = @Test + '1' + REPLICATE('a', 7998) + '1';
SET @Test = @Test + '2' + REPLICATE('b', 7998) + '2';
SET @Test = @Test + '3' + REPLICATE('c', 7000) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';
----
SET @Test = '';
SET @Test = @Test + '1.1' + REPLICATE('a', 7000) + '1.1' + CHAR(13) + CHAR(10);
SET @Test = @Test + '1.2' + REPLICATE('a', 400) + '1.2' + CHAR(13) + CHAR(10);
SET @Test = @Test + '1.3' + REPLICATE('a', 4000) + '1.3' + CHAR(13) + CHAR(10);
SET @Test = @Test + '2' + REPLICATE('b', 1798) + '2' + CHAR(13) + CHAR(10);
SET @Test = @Test + '3' + REPLICATE('c', 7000) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';

NOTES:

You could get away using the built-in CHARINDEX function to look for each next newline, and in this respect it would function almost like using a string splitting function. The difference between what is needed here and doing a simple split on newlines is that any element returned that is over 8000 characters will still need to be split into chunks of no more than 8000 characters.
In order to handle NVARCHAR(MAX) instead of VARCHAR(MAX), use the code that I posted to PasteBin: T-SQL Stored Proc to PRINT NVARCHAR(MAX) values
There is a stored procedure, Util_Print, in the SQL# library that handles NVARCHAR(MAX) and has some functionality beyond the T-SQL code shown above, but that is only available in the Full (i.e. paid for) version, not in the Free version. But the code shown here will handle most cases.


How to replace foreign reference in PostgreSQL?

Sample data,

CREATE TABLE foo ( a int UNIQUE, b int UNIQUE );
CREATE TABLE bar ( c int REFERENCES foo(a) );

Simple ALTER statement,

ALTER TABLE bar
  DROP CONSTRAINT bar_c_fkey,
  ADD FOREIGN KEY (c) REFERENCES foo(b);

And, that worked!

                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 c      | integer |           |          |
Foreign-key constraints:
    "bar_c_fkey" FOREIGN KEY (c) REFERENCES foo(b)


I am not 100% sure what solved the issue because I ended up writing a script to do the following and when it finished (unattended) several hours later, the errors stopped. I am betting it was the mysqlcheck command but only in conjunction with rebuilding every index on every table. Not sure which one actually was the issue.

My script did the following. (not posting the code because it is ugly and quick and dirty)

1) Alter every table only setting the engine to innodb (which they already were but this forces a rebuild)
2) ran mysqlcheck on each database one at a time

I logged all of this to a file but no errors were detected. Everything said it was OK.

So with everything saying it was OK that would make me think nothing was ever wrong but it was and it no longer is so this was just some bit of confusion in some supporting file.

Bogus error with a bogus fix but it actually worked so I don't care any more.


You say that this promotion of DIFF to FULL backups is "random" but I'd wager you can find a connection between this activity and data churn (or index maintenance) in the database itself.
Because you're using ChangeBackupType='Y', the backup job is looking at sys.dm_db_file_space_usage to see how much of the database has been changed, and performs a FULL backup if it exceeds a threshold (I'm having difficulty discerning the default threshold from the source code). You can change that threshold by adjusting the ModificationLevel parameter, which is a percentage. From the documentation

ModificationLevel
Specify a percentage when a differential backup will be changed to a full backup. This option can only be used together with @ChangeBackupType = 'Y'.
DatabaseBackup checks allocated_extent_page_count and modified_extent_page_count in sys.dm_db_file_space_usage to calculate how much of a database that has been modified.


A lot of people want to do what you are describing.  They get into big trouble when the dataset gets big.

  All of the custom fields need to be searchable.

Only one solution:  MariaDB with "Dynamic Columns".  For discussion of why it is impractical otherwise, and discussion of lesser solutions:
http://mysql.rjweb.org/doc.php/eav

  an auto increment primary key ID along with an app generated uuid key which will be indexed

Once the table (or at least the index) is too big to be cached, UUID/GUIDs lead to lots of random disk I/O and destroy the cache.  Further discussion (and a workaround):  http://mysql.rjweb.org/doc.php/uuid


You should also grant access to the database and SCHEMA itself:

GRANT CONNECT ON DATABASE toto TO toto_group;
GRANT USAGE ON SCHEMA s_test TO toto_group;


Can connect using SQL Server Management Studio but cannot ping or via program

If you can situationally access SQL Server over the network, this sounds like a firewall issue. To quickly test, simply turn the firewall off on myremotedb.myremotedomain.gbl first. Then, retest the application connectivity. If that doesn't work, turn the firewall of on your app server and retry connecting.

Baring that, I'd start testing ports using nmap to see which ports are able to get through to the target server. nmap is like ping but allows you to test port numbers as well. Test to see whether you can nmap port 1433 (the default sql port) from the app server to myremotedb.myremotedomain.gbl.

https://nmap.org/book/inst-windows.html


Size, audit and rowcount of a table in Vertica

Using below query, you will get the size of Vertica tables.

SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes) / (1024 * 1024 * 1024) AS TABLE_SIZE_GB
FROM   v_monitor.projection_storage
GROUP  BY anchor_table_schema,
anchor_table_name
order  by sum(used_bytes) desc;

Check out the most important queries to check table size

https://www.orahow.com/2018/06/how-to-check-size-of-tables-in-vertica.html


Is SQL Server Mirroring dependent on SQL Server Agent running?

Mirroring is not dependent on SQL Server Agent running.  Communication between primary and secondary happens using TCPIP ports.  In database mirroring, principal and mirror server communicate through TCP connection and write data change directly to remote transaction log file.

Database Mirroring Overview
COMPARISON BETWEEN MIRRORING VS LOG SHIPPING AND MIRRORING VS
CLUSTERING

But if you want to monitor and get alerted you need the agent job running which get created automatically when you set up mirroring. Details below:

Monitoring Database Mirroring (SQL Server)


How to know which security updates to install?

Part of my mistake was in comparing QFE and GDR builds and release dates.  The QFE downloads are much larger (~600 MB) than the GDR downloads (~200 MB).  The dates and build numbers vary widely as well

I found the information at Latest Builds of SQL Server 2014 quite helpful.  So just comparing the related security bulletins and GDR releases:

ms14-044 gdr = 183 MB   build 2254  8/12/2014
ms15-058 gdr = 205 MB   build 2269  7/13/2015

The one released on the later date is the higher version.  I'm not yet sure how the person that maintains the page deduced that build 2269 includes the fixes from build 2254, so perhaps someone can comment on that or edit this answer.


SQL Server and SSPI handshake failed error

Why does a reboot of the server sometimes fix this issue?

I think the OS has an issue talking with the DC (for whatever reason(s) you determine) and when this happens, it just gets out of sync somehow with DC, AD, etc. and a quick fix is to reboot the SQL Server OS when this occurs and there is no network communication with the DC at the time of the reboot, and all comes up fine with everything back in sync at that point. To accurately answer this, you probably need to find the reason first.

Proving that [the] Domain Controller(s) is/are or is/are not the issue

1. Troubleshoot & Consider

  I'm not suggesting you make a bunch of changes before trying to find
  and fix the specific problem beforehand but there are many factors to
  consider I would think. It's just something you have to troubleshoot
  one step at a time unless there's something obvious causing the issue.

2. Access Perspective

  In my setup, I'm the domain admin as well as the DBA so I have access
  to everything and all servers (two domains with two-way trusts) to
  troubleshoot at all levels whereas you may be a bit more restricted
  with what you can review, etc. on each server in the loop of the
  issue.

3. No Simple Oversight

  I usually start with the simple items first that don't require changes
  and see if there are any obvious issues (e.g. event viewers on all
  servers, DNS testing on all servers, review all server configurations,
  etc.).

4. No Telling Logic

  This could be an issue at the OS level of the SQL Server for the
  Windows version it is on. This could also be an issue with the NIC of
  the SQL Server or a mis-configuration of TCP/IP or DNS settings on the
  SQL Server or any of your DCs.

5. Collect & Update

  Gather information on or ensure the SQL Server is fully updated with
  Windows Updates, and ensure server firmware is up-to-date if
  applicable with BIOS version as well as any hardware device firmware
  updates. The domain admins could also do the same on the DCs but use
  caution with making a bunch of changes and do so one step at a time.

6. Best Practices

  I'm not sure how your AD and DCs are configured topology wise, but
  best practices should be followed as best as possible and a DC should
  be close to the network where both domains are physically rather than
  going across a slower network pipe or routers, etc. which adds another
  level where there could be issues or configurations to review.

  The DCs should have their TCP/IP settings configured so that their DNS
  settings point to other DNS servers or follow best practices otherwise
  as per Microsoft for the DCs configurations applicable in your
  environment.

7. Request Access or Configuration Disclosure Assistance

  You'll need to do basic troubleshooting to find the issues to resolve,
  ask questions, see what all could be updated and patched or changed,
  and get your domain admins to help you troubleshoot or disclose domain
  configurations to you if you're not sure how to see if everything is
  configured as best it could be for optimum performance and per best
  practices.

Important: This not to say there couldn't be a very simple reason this suddenly happened but if nothing has changed anywhere (i.e. OS on DCs or SQL Server, network configurations, AD or forest functional levels, hardware upgrades, Windows Updates, etc.) then you just have to troubleshoot for the reason why one step at a time.


Something tells me you are either using a version of MySQL prior to 5.0, you called an older version of mysqldump, or you messed with the settings of the dump.

What usually blows up a mysqldump past the size of its dataset is the option --skip-extended-insert.

In older versions of MySQL, there was no extended insert. That means each and every row in a table had an INSERT command to itself. If a table had 2,000 rows, the mysqldump output will have 2,000 INSERT commands. That's a whole lot of commas, parentheses, single quotes, and "INSERT INTO" tags to place in a dump file.

In newer versions of MySQL, --extended-insert was added to group together dozens (or even hundreds) of rows in a single INSERT. SO, instead of...

INSERT INTO tbname VALUES (1);
INSERT INTO tbname VALUES (2);
INSERT INTO tbname VALUES (3);
INSERT INTO tbname VALUES (4);
INSERT INTO tbname VALUES (5);

You could have this:

INSERT INTO tbname VALUES (1),(2),(3),(4),(5);

For mysqldump, --opt includes --extended-insert. If you use --skip-opt when doing mysqldump, it disables --skip-extended-insert. Here are the options of mysqldump that affect extended insert:

  -e, --extended-insert
                      Use multiple-row INSERT syntax that include several
                      VALUES lists.
                      (Defaults to on; use --skip-extended-insert to disable.)
      --opt               Same as --add-drop-table, --add-locks, --create-options,
                          --quick, --extended-insert, --lock-tables, --set-charset,
                          and --disable-keys. Enabled by default, disable with
                          --skip-opt.

CAVEAT

Run these at the Linux command line

mysql --version
mysqldump --version
which mysqldump

If these do not match the version of mysql you are running on the server, or multiple versions of MySQL exist on the same machine, get that straightened out. Until then, make sure you call the correct version of mysqldump and don't use --skip-opt.


pg_timezone_names view is empty - can be recreated?

The pg_timezone_names view is populated when the server starts - it recursively reads the timezone files from <your Postgres dir>/share/timezone and they are used to provide data for the view.

Double check that the files exist (as they should in a full installation) and that the user you are running the database under has read permission on all of the directories and files under <your Postgres dir>/share/timezone.

You'll need to restart the RDBMS for changes to take effect.


Indexing from start or when performance problem arises?

Should I start indexing right from the start or when performance
  problem arises?

Indexing strategy tends to evolve as usage patterns emerge. That said, there are also strategies and design guidelines that can be applied up front.

Choose a good clustering key. You can usually determine the appropriate clustered index at design time, based on the expected pattern of inserts to a table. If a compelling case emerges for a change in the future, so be it.
Create your primary and other unique constraints. These will be enforced by unique indexes.
Create your foreign keys and associated non-clustered indexes. Foreign keys are your most frequently referenced join columns, so index them from the start.
Create indexes for any obviously highly selective queries. For query patterns you already know will be highly selective and likely to use lookups rather than scans.

Beyond the above, take a gradual and holistic approach to implementing new indexes. By holistic, I mean assess the potential benefit and impact to all queries and existing indexes when evaluating an addition.

A not uncommon problem in SQL Server circles is overindexing, as a result of guidance from the missing index DMVs and SSMS hints. Neither of these tools evaluate existing indexes and will merrily suggest you create a new 6 column index rather than add a single column to an existing 5 column index.

-- If you have this
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable]
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
)

-- But your query would benefit from the addition of a column
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable]
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

-- SSMS will suggest you create this instead
CREATE NONCLUSTERED INDEX [IX_MyTable_AnotherIndexWithTheSameColumnsAsTheExistingIndexPlusCol6] ON [dbo].[MyTable]
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

Kimberly Tripp has some excellent material on indexing strategy that while SQL focused is applicable to other platforms. For the SQL Server folk, there are some handy tools for identifying duplicates like the example above.

  We can also create temporary index while executing a query. What are
  the pros and cons of such techniques?

This usually only applies for rarely run queries, typically ETL. You need to assess:

Does the time taken to create the index reduce the execution time of the query.
Does the maintenance overhead of leaving the index in place outweigh the time taken to create/drop when it's needed.


Multiplication Using Values From Various Tables

First, excellent that you provided create table statement. For additional bonus you can provide insert statements and expected output of the query ;-)

Second, some things are missing in your model. What is the primary key of ContainsItems, I guess it should be invoiceID,itemID and for that they need to be declared as NOT NULL. What does it mean if itemQuantity is null? I assume it can't be that (may be incorrect):

CREATE TABLE ContainsItems (
    invoiceID nvarchar(5) NOT NULL REFERENCES Invoices(invoiceID),
    itemID nvarchar(5) NOT NULL REFERENCES Items(itemID),
    itemQuantity numeric(2, 0),

    primary key (invoiceID, itemID)
);

In the same spirit I assume that an Item has a name, unit and PricePerUnit:

CREATE TABLE Items (
    itemID nvarchar(5) NOT NULL PRIMARY KEY,
    itemName nvarchar(20) NOT NULL,
    unit nvarchar(20) NOT NULL,
    pricePerUnit float NOT NULL
);

If my assumptions are wrong you will have to adjust the query to handle this.

What you want is the SUM of the price of items per invoice. I assume this to be:

ContainsItems.itemQuantity * Items.pricePerUnit

Your query then only needs some slight modification. First we need to add information on what to apply the ? later on:

SELECT Invoices.invoiceID, Invoices.invoiceDate, ?
FROM Invoices
JOIN ContainsItems
    ON Invoices.invoiceID = ContainsItems.invoiceID
JOIN Items
    ON ContainsItems.itemID = Items.itemID

GROUP BY Invoices.invoiceID, Invoices.invoiceDate

Now per such group we can sum the ContainsItems.itemQuantity * Items.pricePerUnit

SELECT Invoices.invoiceID, Invoices.invoiceDate

     , SUM( ContainsItems.itemQuantity * Items.pricePerUnit )

FROM Invoices
JOIN ContainsItems
    ON Invoices.invoiceID = ContainsItems.invoiceID
JOIN Items
    ON ContainsItems.itemID = Items.itemID

GROUP BY Invoices.invoiceID, Invoices.invoiceDate


Table Partitioning - database file increased after partitioning

In this instance rather than messing with the complexity of partitioning I'd either:

Just move the entire DB over to the drive with more space.
Detach the DB, move the files over and reattach.
See https://docs.microsoft.com/en-us/sql/relational-databases/databases/detach-a-database and the other related sections.
Or backup, drop original, and resore with the files on the other drive.
Create a new datafile as you have done, but instead of partitioning tables, just move whole tables and indexes over to the new file group like so:

    CREATE CLUSTERED INDEX PK_MY_TABLE
    ON my_schema.my_table(DATE ASC, ID ASC)
    WITH (DROP_EXISTING = ON, ONLINE = ON)
    ON MY_FILEGROUP

In either case I would undo the partitioning first as it would not be needed, and for paranoia's sake take regular manual backups during the process.
With regard to partitioning, note that all your examples above have done is move the data (the clustered index) for rows one side of your partitioning function. The data for non-clustered indexes will still all be in the original filegroup unless you partition those also. This may explain not as much data moving over as you expected.
Note that is also applies to moving tables between filegroups without partitioning: the non-clustered indexes need to be moved explicitly also, or they will stay where they are. In fact having the indexes in another filegroup to the table will work just fine so you don't have to move them, or you could move then instead of the main table data (the clustered index or heap) and leave that where it is.
Freeing Space
Once all this is done, which ever way you do it (partitioning or moving while tables/indexes to a new filegroup), the file(s) making up your primary filegroup will not automatically shrink to release space to the OS, you will need to do this as an extra step afterwards.


Doing a join to search geolocation points in zones

This should do it:

SELECT p.id, latitude, longitude, z.id, z.name
  FROM points p
  LEFT JOIN zones z
    ON ( latitude BETWEEN LAT_S AND LAT_N
          AND
         longitude BETWEEN LONG_W AND LONG_E ) ;

[EDIT]

Per your comment, suppose you want to add a FK column to your points table that associates the point to its zone id.  There are some reasons why this would be not recommended, such as if your zone boundaries could ever change, you would have incorrect/inconsistent data.  However for raw performance, maybe you want the zone id denormalized into the points table, keeping in mind that it's like a cached zone id.  The MIN() operator is there to protect you in case you have two zones overlapping.  In that case, you've got bigger problems, but at least the UPDATE works, and picks the lower zone id by fiat.

ALTER TABLE points ADD (zone_id int references zone(id));

UPDATE points SET zone_id =
( SELECT MIN(z.id)
  FROM zones z
  WHERE latitude BETWEEN LAT_S AND LAT_N
          AND
        longitude BETWEEN LONG_W AND LONG_E
);


Query MongoDB collection and find rows from 7 days ago

I can't test this at the moment, but that's the basic format. You should change your date data type to ISO so it works easily with the below code:

var lastWeek = new Date();
lastWeek.setDate(lastWeek.getDate() -7);
db.messages.find({ "rep_header.share_date_2.$date": { $gte: lastWeek} } } );


Least recently used replacement strategy - where is pin count stored?

(I have not heard of a pin count; does "pin" stand for something?)

A cache of things needs some subset of the following for each entry:

A timestamp or sequence number of when it was last used.  Or this may be implemented via a linked list.  (This may be the LRU queue you are referring to.)
An expiration date (rare).
A "dirty" flag, indicating that it has been changed.  More specifically, that it disagrees with disk, so it must be written out before being freed.
Reference count -- This indicates how many threads (or whatever) have latched onto this item.  (Not always present.)
A lock -- either on the item or on the entire table.  This is used to avoid modifying data, links, flags, etc, versus freeing up the item.  You must not allow one thread to free up an item while another thread is using the item.  (This sort of addresses your last question.)
Forward and backward links to other items.  (Especially useful if items are variable length.)
The item may be completely self-contained (eg when using RAM), or it may involve two blocks of info when using disk -- Keep the links, dirty bit, etc in RAM.  That way you don't have to do an expensive I/O for following links or checking/setting the dirty bit.  (So, the location of the dirty bit "depends".)

My rambling answer points out that there are many ways to implement a LRU cache.  Your questions indicate that you are beginning to understand the complexity of this basic construct.


Table is marked as crashed and should be repaired

Please try the below query,

REPAIR TABLE nagios_servicechecks;

Try the above query in terminal or check the below link to repair table or databases via phpmyadmin

http://php.about.com/od/mysqladministration/qt/repair_mysql.htm


Can I move a system database from one version of SQL server to another?

No you can not move the master database (or any system database) from one version of SQL Server to another version of SQL Server.  You would need to install SQL 2005 on the server, then restore master.  Then upgrade to SQL 2008.


What Type of Join to Use in this Search?

Probably something along these lines, courtesy of OUTER APPLY:

select
    --A bunch of stuff
    ISNULL(pic.PImage_Path, 'None') AS PImage_Path
FROM PERSON a
  LEFT OUTER JOIN COMPANY c
    ON a.Person_Company_ID = c.Company_ID
  OUTER APPLY (
    SELECT TOP 1
      PImage_Path
    FROM PIMAGE
    WHERE PIMAGE.PImage_Subject_ID = a.Person_ID --Fix this if I'm making the wrong assumption about your key columns.
    ORDER BY ... --Something sensible to determine which picture will be used
  ) pic


Indexes in MySQL are maintained synchronously.  When an insert/update/delete query (or an alter table to add, remove, or enable indexes) finishes running, the indexes are consistent with the table's data.

There is no such thing in MySQL as an index being in a state that is not "ready to use."


Querying an ordered list with OR or IN clause

A Postgres solution would be to put all the IDs into an array, then use something like this:

select t.*, il.listindex
from contacts t
   join unnest(array[13259, 12472, ..., 12422]) with ordinality as il(id, listindex)
     on t.conidcontact = il.id
order by il.listindex;

(The above is actually standard ANSI SQL - but to my knowledge only Postgres and HSQLDB support that)

The array could also be passed as a parameter in a prepared statement (or whatever the term for that is in the programming language you are using).

If you can build a dynamic query, another option would be to use a row value constructor:

select t.*, il.listindex
from contacts t
   join (
      values
        (1, 13259),
        (2, 12472),
        .....
        (568, 12422)
  ) as il(listindex, id) on t.conidcontact = il.id
order by il.listindex;

(The above is 100% standard ANSI SQL)


Where clause to filter rows for Azure Stretch Database?

Documented now for the latest CTP release. See how to write a TVF that a filter predicate uses to define what is a cold row (that gets moved) in a table with a mix of hot & cold rows: https://msdn.microsoft.com/en-us/library/mt613432.aspx


ANALYZE TABLE .. may refresh the statistics such that queries run faster.  (Defragging (OPTIMIZE TABLE) takes a long time and is probably overkill.)
For future purging, implement PARTITION BY RANGE; see this for discussion.
The first query would be faster if the ORDER BY said the same thing as the GROUP BY.
Instead of AND DATE(TimeStamp) = @D1, say AND Timestamp >= @D1 AND Timestamp < @D1 + INTERVAL 1 DAY, plus have composite INDEX(IMEI, Timestamp).  (The index you have is not as efficient for this query.)
innodb_buffer_pool_size should be somewhere around 70% of RAM.  Increasing it too much will lead to swapping, which is terrible for performance.


How to write a query in SQL Server to find nearest values

Assuming the column is indexed the following should be reasonably efficient.

With two seeks of 10 rows and then a sort of the (up to) 20 returned.

WITH CTE
     AS ((SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC

(i.e. potentially something like the below)

Or another possibility (that reduces the number of rows sorted to max 10)

WITH A
     AS (SELECT TOP 10 *,
                       YourCol - 32 AS Diff
         FROM   YourTable
         WHERE  YourCol > 32
         ORDER  BY Diff ASC, YourCol ASC),
     B
     AS (SELECT TOP 10 *,
                       32 - YourCol AS Diff
         FROM   YourTable
         WHERE  YourCol <= 32
         ORDER  BY YourCol DESC),
     AB
     AS (SELECT *
         FROM   A
         UNION ALL
         SELECT *
         FROM   B)
SELECT TOP 10 *
FROM   AB
ORDER  BY Diff ASC

NB: Execution plan above was for the simple table definition

CREATE TABLE [dbo].[YourTable](
    [YourCol] [int] NOT NULL CONSTRAINT [SomeIndex] PRIMARY KEY CLUSTERED
)

Technically, the Sort on the bottom branch shouldn't be needed either as that too is ordered by Diff, and it would be possible to merge the two ordered results. But I wasn't able to get that plan.

The query has ORDER BY Diff ASC, YourCol ASC and not just ORDER  BY YourCol ASC, because that was what ended up working to get rid of the Sort in the top branch of the plan. I needed to add the secondary column in (even though it won't ever change the result as YourCol will be the same for all values with the same Diff) so it would go through the merge join (concatenation) without adding a Sort.

SQL Server seems able to infer that an index on X seeked in ascending order will deliver rows ordered by X + Y and no sort is necessary. But it is not able to infer that travelling the index in descending order will deliver rows in the same order as Y-X (or even just unary minus X). Both branches of the plan use an index to avoid a sort but the TOP 10 in the bottom branch are then sorted by Diff (even though they are already in that order) to get them in the desired order for the merge.

For other queries/table definitions it may be trickier or not possible to get the merge plan with just a sort of one branch - as it relies on finding an ordering expression that SQL Server:

Accepts that the index seek will supply the specified order so no sort is needed before the top.
Is happy to use in the merge operation so requires no sort after the TOP


Counting occurrence of words in table is slow

The best way I have found to speed up leading wildcard LIKE searches is to use n-grams. I describe the technique and provide a sample implementation in Trigram Wildcard String Search in SQL Server.

  The basic idea of a trigram search is quite simple:

  Persist three-character substrings (trigrams) of the target data.
  Split the search term(s) into trigrams.
  Match search trigrams against the stored trigrams (equality search).
  Intersect the qualified rows to find strings that match all trigrams.
  Apply the original search filter to the much-reduced intersection.

It may be suitable for your needs, but be aware:

  Trigram search is no panacea. The extra storage requirements, implementation complexity, and impact on update performance all weigh heavily against it.

Test

I ran a quick test using the Complete Works of Shakespeare to populate the body column of the items table with 15,838 rows. I loaded the words table with 7,669 unique words from the same text.

The trigram structures built in around 2 seconds and the following update statement completed in 5 seconds on my mid-range laptop:

UPDATE dbo.words WITH (TABLOCK)
SET hits =
(
    SELECT COUNT_BIG(*)
    FROM dbo.Items_TrigramSearch
        ('%' + word +'%') AS ITS
);

A selection of the updated words table:

The modified trigram scripts from my article are below:

CREATE FUNCTION dbo.GenerateTrigrams (@string varchar(255))
RETURNS table
WITH SCHEMABINDING
AS RETURN
    WITH
        N16 AS
        (
            SELECT V.v
            FROM
            (
                VALUES
                    (0),(0),(0),(0),(0),(0),(0),(0),
                    (0),(0),(0),(0),(0),(0),(0),(0)
            ) AS V (v)),
        -- Numbers table (256)
        Nums AS
        (
            SELECT n = ROW_NUMBER() OVER (ORDER BY A.v)
            FROM N16 AS A
            CROSS JOIN N16 AS B
        ),
        Trigrams AS
        (
            -- Every 3-character substring
            SELECT TOP (CASE WHEN LEN(@string) > 2 THEN LEN(@string) - 2 ELSE 0 END)
                trigram = SUBSTRING(@string, N.n, 3)
            FROM Nums AS N
            ORDER BY N.n
        )
    -- Remove duplicates and ensure all three characters are alphanumeric
    SELECT DISTINCT
        T.trigram
    FROM Trigrams AS T
    WHERE
        -- Binary collation comparison so ranges work as expected
        T.trigram COLLATE Latin1_General_BIN2 NOT LIKE '%[^A-Z0-9a-z]%';
GO
-- Trigrams for items table
CREATE TABLE dbo.ItemsTrigrams
(
    id integer NOT NULL,
    trigram char(3) NOT NULL
);
GO
-- Generate trigrams
INSERT dbo.ItemsTrigrams WITH (TABLOCKX)
    (id, trigram)
SELECT
    E.id,
    GT.trigram
FROM dbo.items AS E
CROSS APPLY dbo.GenerateTrigrams(E.body) AS GT;
GO
-- Trigram search index
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.ItemsTrigrams (trigram, id)]
ON dbo.ItemsTrigrams (trigram, id)
WITH (DATA_COMPRESSION = ROW);
GO
-- Selectivity of each trigram (performance optimization)
CREATE OR ALTER VIEW dbo.ItemsTrigramCounts
WITH SCHEMABINDING
AS
SELECT ET.trigram, cnt = COUNT_BIG(*)
FROM dbo.ItemsTrigrams AS ET
GROUP BY ET.trigram;
GO
-- Materialize the view
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.ItemsTrigramCounts (trigram)]
ON dbo.ItemsTrigramCounts (trigram);
GO
-- Most selective trigrams for a search string
-- Always returns a row (NULLs if no trigrams found)
CREATE FUNCTION dbo.Items_GetBestTrigrams (@string varchar(255))
RETURNS table
WITH SCHEMABINDING AS
RETURN
    SELECT
        -- Pivot
        trigram1 = MAX(CASE WHEN BT.rn = 1 THEN BT.trigram END),
        trigram2 = MAX(CASE WHEN BT.rn = 2 THEN BT.trigram END),
        trigram3 = MAX(CASE WHEN BT.rn = 3 THEN BT.trigram END)
    FROM
    (
        -- Generate trigrams for the search string
        -- and choose the most selective three
        SELECT TOP (3)
            rn = ROW_NUMBER() OVER (
                ORDER BY ETC.cnt ASC),
            GT.trigram
        FROM dbo.GenerateTrigrams(@string) AS GT
        JOIN dbo.ItemsTrigramCounts AS ETC
            WITH (NOEXPAND)
            ON ETC.trigram = GT.trigram
        ORDER BY
            ETC.cnt ASC
    ) AS BT;
GO
-- Returns Example ids matching all provided (non-null) trigrams
CREATE FUNCTION dbo.Items_GetTrigramMatchIDs
(
    @Trigram1 char(3),
    @Trigram2 char(3),
    @Trigram3 char(3)
)
RETURNS @IDs table (id integer PRIMARY KEY)
WITH SCHEMABINDING AS
BEGIN
    IF  @Trigram1 IS NOT NULL
    BEGIN
        IF @Trigram2 IS NOT NULL
        BEGIN
            IF @Trigram3 IS NOT NULL
            BEGIN
                -- 3 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.ItemsTrigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                INTERSECT
                SELECT ET2.id
                FROM dbo.ItemsTrigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                INTERSECT
                SELECT ET3.id
                FROM dbo.ItemsTrigrams AS ET3
                WHERE ET3.trigram = @Trigram3
                OPTION (MERGE JOIN);
            END;
            ELSE
            BEGIN
                -- 2 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.ItemsTrigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                INTERSECT
                SELECT ET2.id
                FROM dbo.ItemsTrigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                OPTION (MERGE JOIN);
            END;
        END;
        ELSE
        BEGIN
            -- 1 trigram available
            INSERT @IDs (id)
            SELECT ET1.id
            FROM dbo.ItemsTrigrams AS ET1
            WHERE ET1.trigram = @Trigram1;
        END;
    END;

    RETURN;
END;
GO
-- Search implementation
CREATE FUNCTION dbo.Items_TrigramSearch
(
    @Search varchar(255)
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Result.body
    FROM dbo.Items_GetBestTrigrams(@Search) AS GBT
    CROSS APPLY
    (
        -- Trigram search
        SELECT
            E.id,
            E.body
        FROM dbo.Items_GetTrigramMatchIDs
            (GBT.trigram1, GBT.trigram2, GBT.trigram3) AS MID
        JOIN dbo.Items AS E
            ON E.id = MID.id
        WHERE
            -- At least one trigram found
            GBT.trigram1 IS NOT NULL
            AND E.body LIKE @Search

        UNION ALL

        -- Non-trigram search
        SELECT
            E.id,
            E.body
        FROM dbo.Items AS E
        WHERE
            -- No trigram found
            GBT.trigram1 IS NULL
            AND E.body LIKE @Search
    ) AS Result;

The only other change was to add a clustered index to the items table:

CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.items (id);


How to add an additional table over two one to many relations tables?

If the relationship between TableA and TableB is meant to be one-to-many then you don't need a third table (TableC).
However, if TableA to TableB are meant to have a many-to-many relationship, then you would need a third table (TableC).  In this case you would have foreign keys in TableC to both TableA and TableB.  Normally these two foreign keys in TableC would have a unique constraint (and probably index) applied to the combination of these two keys (a compound key).
Often this compound key is also used as the primary key of TableC.  This doesn't have to be the case. It's possible for TableC to have it's own single column unique key.  It depends on the nature of the data you're storing in TableC.


count duplicate records and select first 2

select * from (SELECT
    row_number() over (partition by 1,2,3,4 order by 1,2) as RowNo,
    a.dbsnum,
    b.chknum,
    a.name,
    b.fname
    FROM
    sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
    GROUP BY
    1,2,3,4) as innerSelect
where RowNo <=2


I'm going to add my answer, which was correct in the comments, as the answer here.

Be specific about your types!

In your master table, you have point_date as a timestamp with time zone.

In your CHECK constraint, you have used timestamp without time zone.

Then, in your query, you just use point_date >= '2015-08-25 00:00:00'::timestamp, thus using a timestamp type in your WHERE predicate.

To fix your problems, make sure that all of these entries are referring to a consistent type.  In your comments above, you indicated that you changed all entries to refer to the type timestamp with time zone, and that it fixed your issues.


Composite vs auto incrementing integer as Primary key

As Kumar said, using a composit primary key will prevent you from having two entries with the same userID and activityID - you have to decide if this is the desirable configuration. Logically, I would say that a user can complete the same activity twice, so the "completed" list would have two entries, both with the same userID and activityID. In this case you would need a seperate primary key.

Generally I would recommend using the activityID as a foreign key. If you configure ON DELETE CASCADE the deletion of an activity will also delete all entries in both lists that refer to this activity. However I would rather recommend using ON DELETE RESTRICT which would prohibit the deletion of an activity as long as there is a corresponding entry in either list (this will prevent you from having invalid lists where an activityID cannot be resolved).


How to limit the range of an insert-update to only look at certain rows/ rows with field value: X?

Unfortunately, MySQL does not allow for filtered indexes (otherwise you can just filter on open tickets only). The next best thing is to do something similar to the two table approach you describe: Partitioned tables.


SQL Agent running Ola scripts: all fine and perfect but a differential backup on a specific database fails

For the help of everybody I post the resolution I did found to my problem.  Scanning other forums I noticed that this I/O error 112 - "not enough space on disk" is fairly a common issue, erratic but not so infrequent.

I read a post where the issue is described to come from the sql backup algorithm which, before starting the backup operation itself, is calculating, based on DB dimension, how much space on disk will be needed. If the result (space calculated to be needed) is not enough, you get the error and the backup is aborted. Unfortunately sometime this algorithm seems to have glitches. This should be applicable on backups made with compression option on only, but in my (and other) cases may create issues independently from.

There is a TRACE OPTION which is disabling such algorithm, and the backups start anyhow without such pre-check. I've therefore set DBCC TRACEON(3042, -1) on the SQL instance, as suggested, and bingo!, now also my DIFF backups are going on smoothly.

Thanks anyhow for the interest.


Community wiki answer:

That particular error message is the key:

  Compile error: cannot find  project or library

Opening the office 2010 database in 2016 removed all of the VBA references to 14.0 Object Libraries in the References menu, and replaced them with 16.0, which is not present in Office 2010.

Broken references are to be expected when the .mdb is opened in an older version. It happens when there are references to Office libraries (Word, Excel, etc). Edit the references (in the VB Editor) in the oldest version (the one that doesn't run the code).


I don't know how these things apply to RDS in particular, but regarding community PostgreSQL at large, there are a few options:

Use logical replication to replicate data from the other servers into a single PostgreSQL database.  A major caveat here is that the tables on the central server need to have the exact same table name, including schema name, as they do on the outlying servers.  So if the outlying servers have tables with the same table name and schema name as each other, that will be a problem.  If you are starting from scratch, it is pretty easy to solve this by having each server include the server descriptions in all of its schema names so that all schema names are unique (i.e. "london_accounts", "chicago_accounts", rather than just "accounts" on both servers).  But if you are adapting existing systems which didn't abstract away the schema names, it can be a major pain to have to change everything to fit this pattern.

Or, you could just leave the data where it is, and use the foreign data wrapper postgres_fdw to query it from the central location.  Caveats here are that all the outlying servers have to be online for this to work, the high ping time and possible low network throughput to them can be major performance problem, and FDW has higher overhead and often comes up with worse plans than than native data.

You can overcome the uptime, ping, and throughput issues by using physical replication of each outlying servers to the central location (e.g. three database instances running on three different port numbers of the same piece of hardware), and then use FDW from a 4th server to each replica to tie those replicas together. You might still suffer from some overhead and planning issues.


Is Query Slower When There Are More Columns in one Table?

I have a related blog post here: Do You Have Tables In Your Tables? that I'm going to quote a bit from.

  What Makes Them (wide tables) Bad?

  They’re nearly impossible to index
  efficiently:

  Queries will hit them in many different ways
  WHERE clauses will be
  unpredictable
  SELECT lists will vary wildly

  When indexes pile up to support all these different queries, locking and blocking will start to become larger issues.

Other things you may need to consider:

LOB data (MAX columns, etc.)
Query Memory Grants
Modification concurrency

Putting everything in one table sounds like a bad idea. If users are allowed to change any information related to a profile that you've listed (name, telephone, personal information, etc.), those queries are now competing to locate and modify rows alongside your app tracking login information.

It's a recipe for bad blocking and deadlocks down the road.

The "one big table" approach sounds quite convenient at first, but the convenience early on will never surpass the inconvenience you face later of needing to split the table up into a proper schema.


Why am I getting "Invalid object name 'sys.sysobjvalues'."

This is one of the tables that can only be accessed over DAC, from this blog

  They had been moved to the imageval column of sys.sysobjvalues, which can only be accessed when using the Dedicated Admin Connection (DAC).

Prolly not a good idea, but for proof it'll work connect to DAC (-S admin:localhost on Linux)

sqlcmd -S admin:localhost -U sa -q "SELECT * FROM sys.sysobjvalues;" | less -S


Verifying Oracle 12 client server encryption

SOLVED - My .bash_profile was referencing Oracle 11 and thus the wrong sqlnet.ora was being loaded.


Can I apply production statistics to an empty local database to test PostgreSQL planner hypotheses?

You cannot readily do that.  pg_statistic uses a bit of magic to be able to store the statistics in a column of type anyarray.  There is no ready way to access this magic from user-land, even if that user is the super-user.


Issue ALTER ROLE client VALID UNTIL 'infinity' and it will remove the VALID UNTIL clause.


Transactional Replication data during rebuild

The reporting DB no longer contains data but only views that replicate the structure of the tables in the data repo. During a rebuild of the "data repo" database the "reporting db" is set offline

Yes, that's reasonable.  You can further optimize this approach by pointing the users to a snapshot or restored copy of the subscriber database during the downtime, eg per https://techcommunity.microsoft.com/t5/DataCAT/Microsoft-SQL-Server-Database-Snapshots-and-Synonyms/ba-p/304987, or by reinitializing the subscription from a backup, which might be faster than using a snapshot.

Or you could just disable the reporting users logins and kill their sessions during the downtime.


Does changing "sa" password require a SQL restart (in mixed mode)?

Q1: Does changing the sa password require a SQL restart?

No, but changing the authentication mode does. Since you're just changing the password and the authentication mode is already set to mixed, you're good to go with just changing the password.

  Q2: Can I change the sa password the "normal" way?

Yes, it's just another SQL Login account.

  Q3: Should we try to do this password rotation on a regular basis? Or only when we find an issue?

To be quite honest, I would disable and rename the SA login. This way it won't be used, at all and if you need a highly privileged login, then you can make one as needed.


How did you setup MySQL Replication with autofailover that is app transparent?

I recently had to setup a similar rig but with ubuntu and not centos. I used MySQL cluster and HaProxy in a master-master setup and its been solid as a rock, no down time and haven't experienced any instances were latency has been an issue.

I used this guide  to help configure the MySQL cluster. MySQL cluster is great in my opionion but see the link bellow for an explanation as to why.

HaProxy is able to detect down nodes and you could use the answer provided in this question to help with your configuration of automatic fail over to a slave node. Also I would configure a second HaProxy with heartbeat, just incase.

It might be worth taking a look into the answers provided in this question to help decide what kind of cluster is really best for your environment(If you don't already know).


Move SQL Server 2008 database from machine A to B with no downtime

Database Mirroring (SQL Server 2005+) would allow as little as 20 seconds of downtime.  This is likely the fastest way of moving a database from machine A to machine B.

You can setup mirroring during the day with no downtime, then cutover at the time of your choosing.  As long as the clients are programmed to retry whatever operation they are doing at the time of cutover, there will be no data loss (if you do the client correctly).