Use array_position() function to get the most frequent value for column from pg_stats

Solution

Assuming the data type of your column tenk1.unique1 is integer:

SELECT array_position(most_common_vals::text::int[], 1000)
FROM   pg_stats
WHERE  tablename = 'tenk1'
AND    attname = 'unique1';

Use your actual column type and the corresponding array type.
You get the position, or NULL if the value is not in the MCV list.

The solution is short - unlike the ...

Explanation

The function array_position() is defined to take (anyarray, anyelement) (or (anyarray, anyelement, integer) for the second variant).

The column pg_stats.most_common_vals has the polymorphic data type anyarray to be able to hold arrays of any data type - for obvious reasons.

anyarray and anyelement are not allowed as data types for user-created tables. For users, both are polymorphic pseudo-types. (But Postgres can use them in system tables.)

Multiple polymorphic variables in the same function have to resolve to the same (or corresponding) data types. The manual:

  Furthermore, if there are positions declared anyarray and others
  declared anyelement, the actual array type in the anyarray positions
  must be an array whose elements are the same type appearing in the
  anyelement positions.

And:

  Thus, when more than one argument position is declared with a
  polymorphic type, the net effect is that only certain combinations of
  actual argument types are allowed.

Bold emphasis mine.

You have found a corner case where function type resolution fails for the combination of the polymorphic anyarray with an integer - or any non-polymorphic type in second position.

1000 in your expression array_position(most_common_vals, 1000) is a numeric constant resolving to integer. These would fail in similar fashion:

array_position(most_common_vals, '1000')  -- untyped string literal

ERROR:  function array_position(anyarray, unknown) does not exist

array_position(most_common_vals, '1000'::text)

ERROR:  function array_position(anyarray, text) does not exist

Furthermore, there are no casts defined for anyarray, being a pseudo-type in user-land:

SELECT * FROM pg_cast WHERE castsource = 'anyarray'::regtype;  -- nothing found

The workaround is to cast to text as stepping stone, since any type can be cast to text. Then cast to integer[], arriving at the solution above.

In closing, I think this is a shortcoming in function type resolution that could be resolved (easily?). But since the data type anyarray is not supposed to be used like this in user-land to begin with I doubt that any developer will spend time on it ...


When PostgreSQL will use Index Only Scan over Bitmap Index + Heap Scan

Index Only:  Will not visit the heap at all for a block if the block is marked as all-visible in the visibility map.  If it does need to visit a block, it might do so on multiple occasions, and those occasions might be separated from each other by a lot of time because multiple distant index entries can point to the same block.  During the intervening time, the block might have been pushed out of the cache and so need to be read back in.

Bitmap: Cannot use the visibility map to avoid visiting a block.  All visits to the same block will occur back-to-back, so it is unlikely to get shoved out of cache between visits.  It also visits in physical order even when the index correlation is low, and so gets credited with having its table heap IO be more sequential and less random.  But that is only important when you read a substantial part of the table, meaning fetching a lot of rows.

Higher pg_class.relallvisible means it is more likely than an IOS will not need to visit the table heap at all for a block, and so favors IOS.  Keeping relallvisible high requires vacuuming.

Higher pg_stats.correlation for the table column that is the leading column of the index means that multiple visits to a block are likely to occur from the same region of the index, and so the block is unlikely to be shoved out of the cache between visits when using an IOS, and so favors an IOS.

Higher values of effective_cache_size means blocks are estimated to stay in the cache for longer meaning repeated distant visits are likely to find them in the cache still, which again favors IOS.

These have a complex relationship.  If all blocks are marked allvisible, then the correlation and effective_cache_size do not matter, as there will not be any heap visits at all.


Stop agent job after executing for a certain number of hours

Personally I would create another job and schedule it to run each morning at 7am.

A job step within that job could execute something like

USE msdb ;
GO

EXEC dbo.sp_stop_job
    N'<Your job name>' ;


Replicate data between MySQL servers with a different structure

Not possible.

Master-Master allows writing to both, and, hence, provides no way to make changes during the replication, at least not bi-directionally.

Master-Slave does allow limited flexibility, such as different engine, different indexes, and, in a few cases, different datatypes.  However, you could lose data.  And it could fail depending on whether you are using binlog_format = ROW versus STATEMENT.  ROW is less forgiving.

Would you like to explain the need you have?  Perhaps we can devise a workaround.

OK, maybe it is possible with Triggers, since they don't have to be the same on both Master and Slave.  (M-M is M-S in both directions.)  So, if you can devise a pair of triggers that completely modify the table(s) at the time of writes, and if selects don't stumble over extra/missing columns/datatypes/tables, then maybe you could do what you want.


Mysql query to fetch records which are not matched form both tables

From both tables I have change in ean field and in quantity field

To solve THIS task you must use the next query:

SELECT t1.asn, t1.ean, t2.ean oldean, t1.belegnr, t1.quantity, t2.quantity Oldqty
FROM temp_table t1, details t2
WHERE t1.asn = t2.asn
  AND t1.belegnr = t2.belegnr
  AND (    t1.ean != t2.ean
        OR t1.quantity != t2.quantity )

BUT...

Looking at your example data and desured result I see that you want to get the record pairs when ean OR quantity is changed.

You can obtain the shown result from shown source data using the next query:

SELECT t1.asn, t1.ean, t2.ean oldean, t1.belegnr, t1.quantity, t2.quantity Oldqty
FROM temp_table t1, details t2
WHERE t1.asn = t2.asn
  AND t1.belegnr = t2.belegnr
  AND (   (     t1.ean = t2.ean
            AND t1.quantity != t2.quantity )
       OR (     t1.ean != t2.ean
            AND t1.quantity = t2.quantity ))

fiddle


I seriously suggest building another system with SQL Server Express and configure it as your witness server.  Then set your operating mode to "High safety with automatic failover (synchronous)".

This solution will alleviate you of this problem.  You will incur a cost for the desktop windows license, but as you may already know, SQL Server Express is of no cost monetarily.


Why does SQL Server Profiler trace not capture SELECT operator in execution plans?

Do not run profiler while grabbing execution plans (very expensive operation !), instead run a server side trace (modify as per your need) or sp_whoisactive and grab the tsql you are interested and then run the tsql separately using Plan explorer to get actual execution plan.

The best way to get all the relevant data is to run the query from sql sentry's plan explorer (its free - but worth spending a little money on Pro version).

The free version is sufficient as it gets you the Table IO (same like stats IO ON) along with Plan diagram, statements tree, plan tree and top operations, etc


There are no tools, so to speak. At least not that I am aware. The best thing to do is to test your code, verify your configurations and ensure that SQL Server is functioning optimally. I am hoping that you did this upgrade in a dev or test environment first and that you followed a process with steps that you can repeat again in production. If that's the case, do a full test of your code that interacts with SQL Server. Ensure everything functions as it did before and make sure that all of your maintenance works and that everyone can connect to it at as before.

I would also look in your SQL Server Error Logs and see if there are any issues. Same thing with your windows event logs (particularly your system and application event log)

I recently gave an answer here that talks a whole lot more about SQL Server Upgrades. Lastly, have you considered what you'll do about DB Compatibility Level? If you haven't, you might want to think about changing that for your databases to SQL 2008 and seeing if all works after a regression test. But please make sure you are in a dev or test. I do not advise just changing this in production, nor do I advise starting with an upgrade in production.


Way around Composite Primary Key requiring Composite Foreign Key

Both the designs are possible, reasonable, and non-redundant (in the sense that no data can be eliminated in one solution without losing information): 1) leaving the PK of Type as the pair (make, name) and use it as foreign key in Model, or introducing a surrogate PK in Type and using it in Model.

And their pro and cons balances: the first option allows to perform more efficiently a join between Model and Make, the second one uses slightly less memory.


What system information is stored in the primary file in SQL Server?

The system objects are the objects which store the schema for all your objects.  The stored procedures, table schemas, etc. are all stored in these system objects.

If the system objects become corrupt you have to restore the primary filegroup.

You can view the data within the system objects by querying the system catalog views such as sys.tables, sys.procedures, etc.

  Do they appear in the 'System' Database?

Not sure what you are talking about, there's no database called "System".  The system databases are master, msdb, model (to some extent) and tempdb.  There is no user database data stored within the system databases.  (Things like logins, jobs, SSIS packages not withstanding.)


SSIS Package - Error Exporting Data to Flat File - Invalid Character

You have to deal with the unicode replacement character by calling it what it is:

Row.Description = Row.Description.Replace(NCHAR(65533), "a")

EDIT:

I apologize as I first gave you a pseudo-SQL Server solution, but with some additional research learned that SSIS behaves a little differently. I'm linking my source that gives multiple solutions, and for an answer will include the first. It seems that you could add a derived column component that would allow you to add a new column (e.g. strDescription from your table using the Type Cast operator DT_STR which will convert the unicode to string. You should then be able to use this derived column in place of the original column in you CSV destination. source Hope that helps!


Create a view with dynamic column headers based on original table row data?

Answered by Bluefeet in https://stackoverflow.com/questions/10986031/sql-dynamic-view-with-column-names-based-on-column-values-in-source-table

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.label)
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT id, [user], ' + @cols + ' from
             (
                 select t.id
                        , t.[user]
                        , p.ticketid
                        , p.label
                        , p.value
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            ) x
            pivot
            (
                min(value)
                for label in (' + @cols + ')
            ) p '

execute(@query)


Can MySQL server send a "server started/running" signal to a remote server?

Such a feature cannot be in mysqld in the cloud.
In order not to pay for a cloud VM, it must be totally down.  That means that communication with it must be preceded by restarting your image.  Only then is mysqld awake.  At that point, it is ready to receive connections.  (And you must "connect" before doing "insert" or whatever you need to do.)
So, your "it's probably possible ..." paragraph is the approach to take.
To add to the complexity, there is probably no easy way to "automatically shut down" the VM after an idle period.  Consider having your "restart" script wait a few minutes, then shutdown.  (Where "a few minutes" is tuned to accommodate your typical activity time.)
For security reasons, MySQL deliberately has no built-in way to communicate with the rest of the world.  (A common request is to send an email based on some data change.)
Another thing to look into -- a proxy between your clients and the VM-MySQL server.  The proxy would live on the client machine(s).  It might be able to orchestrate the startup.  (There are many Proxy products out there; they are aimed at other things -- HA, sharding, routing, load balancing, etc.)


how to interpret log files with high transactions

Usually, these questions arise from a misunderstanding of what the log file does. Usually, a larger than expected log is caused by a database in full recovery where log backups aren't being taken.

If you are taking log backups or your database is in simple recovery, there is probably a very large or long running transaction that has required 60GB of log space. KB317375 is a good starting point for understanding why unexpected log growth may occur and includes steps for identifying transactions that could be the cause.


Disclaimer: The below solution is probably unnecessarily complicated - the obvious solution to this would perhaps involve a cron job or maybe a database event running at some interval to execute SHOW SLAVE STATUS; and extracting the output into your table.

Nevertheless, here is what I think is an interesting solution which gives you instant access to the SHOW SLAVE STATUS output through a SELECT query. However, I've only been tested this with MariaDB (version 10.1.32), and it may or may not be portable to MySQL. It requires the CONNECT storage engine, and I'm not sure you can get that for MySQL.

First install the connect storage engine package in your OS. Then in the mysql client:

INSTALL SONAME 'ha_connect';
CREATE DATABASE IF NOT EXISTS `_`
  DEFAULT CHARACTER SET = 'utf8'
  DEFAULT COLLATE = 'utf8_general_ci';
USE _
CREATE OR REPLACE SERVER `_`
  FOREIGN DATA WRAPPER MYSQL OPTIONS (
    HOST 'localhost',
    USER 'root', PASSWORD 'your_password_here',
    PORT 3306,
    DATABASE '_'
  )
;
CREATE TABLE show_slave_status
  ENGINE = CONNECT
  TABLE_TYPE = MYSQL
  SRCDEF = 'SHOW SLAVE STATUS'
  CONNECTION = '_';

Now you can get all the values you usually get from SHOW SLAVE STATUS with a SELECT: SELECT * FROM _.show_slave_status;

Credits: I borrowed almost all of this from Federico Razzoli's materialize.sql.


I'm afraid you misunderstand how transactions with delayed durability work. There won't be a magic rollback after a reportedly successful commit -- the client application will not know if the changes it thinks have been committed are never actually persisted. As described in the documentation, in the delayed durability mode the server reports a successful commit immediately, but delays writing the log buffer to the disk until some time later. If the server crashes or shuts down before then, these changes will be lost and the client will not know about that.


Efficient way to perform approximated search?

I think that what you need to use is a Nonequi join

select *
from
    table_a,
    table_b
where
    table_a.id1 = table_b.id1
    and table_a.id2 = table_b.id2
    and table_a.evnt_sec BETWEEN table_b.evnt_sec -2 and table_b.evnt_sec +2


Squashing aggregated rows with PostgreSQL

Using percentile_disc

I would think something like this would be the fastest,

SELECT id,
  percentile_disc(0) WITHIN GROUP (ORDER BY name NULLS LAST)    AS name,
  percentile_disc(0) WITHIN GROUP (ORDER BY surname NULLS LAST) AS surname,
  percentile_disc(0) WITHIN GROUP (ORDER BY age NULLS LAST)     AS age
FROM my_data
GROUP BY id;

 id | name | surname | age
----+------+---------+-----
  1 | john | smith   |  32
(1 row)

Here we're using percentile_disc an Ordered-Set Aggregate described as "discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction". So,

if that position is "0", it's essentially first_value over the rows being aggregated.
if NULLS are last (NULLS LAST), the first value won't be null, and that's all that matters here (because you said "for the same id, the values (if present) in the respective columns are always the same")

You could also do this, I think, with first_value in a Window Function, and then pull from that with DISTINCT ON.

Using mode

If you want the consensus of the non-values, we can do that too. I was assuming percentile_disc was what the OP wanted. Another option is to use mode() which is a different Ordered-Set Aggregate Function. It's described as, "returns the most frequent input value (arbitrarily choosing the first one if there are multiple equally-frequent results)". It looks like,

SELECT id,
  mode() WITHIN GROUP (ORDER BY name)    AS name,
  mode() WITHIN GROUP (ORDER BY surname) AS surname,
  mode() WITHIN GROUP (ORDER BY age)     AS age
FROM my_data
GROUP BY id;


Oracle XE Fedora 15 Installation problem

Some thoughts:

First, I also have tried to get Oracle XE up and running under F15. Finally managed it, but it wasn't as simple as I would have thought it should be -- It's easier to get the silly thing configured under Ubuntu. IIRC, I just ended up installing EE instead (under F15).
Second, your environment needs to be set up correctly in your .bash_profile (in your home directory). Make sure to export ORACLE_HOME, ORACLE_SID (=XE), and make sure $ORACLE_HOME/bin is exported in your PATH. Once done, log out and back in and you should be able run sqlplus fine. (If you don't want to log out, just run ". ~/.bash_profile", and it should also work.)
Third, I don't know why Oracle doesn't set this up during the install process. And of all users not to have the environment set up (when XE usually has to create the user from scratch)... the oracle user should simply be set up with it from the start. But that's a rant and not really related to your question.

Hope that helps...


In a trigger, how to check if no fields have changed?

To bring back rows where at least one value has changed you can use

SELECT /*TODO: Column List*/
FROM   INSERTED I
       JOIN DELETED D
         ON I.ID = D.ID
            AND EXISTS (SELECT I.*
                        EXCEPT
                        SELECT D.*)


What I can do to speed up Key Lookup in my Query

Remove it by making the index covering

That is, add an INCLUDE clause to the index so all columns needed are in the index.


The error is caused if there is offline and removed disks inside the cluster (that are no longer presented to the server nodes). Removing the disks and re-running the installer fixed the problem.


Select data where column value exists in seperate rows for same reference id

Here's your query:

SELECT
   T.location_id,
   GROUP_CONCAT(T.service_id) AS serviceids
FROM `Table` AS T
GROUP BY T.location_id
HAVING serviceids IN ('2,3');

You could use ORDER BY inside the GROUP_CONCAT():

SELECT
   T.location_id,
   GROUP_CONCAT(T.service_id ORDER BY service_id ASC) AS serviceids
FROM `Table` AS T
GROUP BY T.location_id
HAVING serviceids IN ('2,3');

EDIT, Another way:

SET @LOCATION_ID=0;
SET @LOCATION_ID=(
    SELECT
        T.location_id
    FROM `Table` AS T
    GROUP BY T.location_id
    HAVING GROUP_CONCAT(T.service_id ORDER BY T.service_id ASC) LIKE '%2,3%');
SELECT
    T.location_id,
    T.service_id
FROM `Table` AS T
WHERE T.service_id IN (2,3) AND T.location_id=@LOCATION_ID;

Result:

Hope this help.


Adding indexes to production MySQL database without downtime?

If you wanted to add indexes to a live table, there are two major approaches:
APPROACH #1 : pt-online-schema-change (Preferred Choice)
This tool is spectacular. It will create a temp table for you and populate it.
In oversimplified terms, it does this for mydb.mytable:
CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
RENAME mydb.mytable TO mydb.mytable_old,mydb.mytable_new TO mydb.mytable;
DROP TABLE mydb.mytable_old;

However, pt-online-schema-change goes way beyond this.
What if rows already copied to the temp table are updated or deleted ? Well,
pt-online-schema-change adds triggers to the source table to handle those post-INSERT changes for you. When the copy to the temp table is complete, the triggers are removed just before the switch.
There also are options to throttle (such as --max-load and --critical-load) or halt (such as --pause-file) the copy to the temp table if the live system has a high load or if the server has slave that fall behind a certain number of seconds (such as --max-lag).
You also have options to test the syntax only (--dry-run) or actually run the change (--execute).
There are options to set variables like sql_log_bin to 0 and check for foreign key constraints.
The limitations to this tool ?

Make sure there is enough room for the temp table before the switch happens. For example, if the source table is 200G, I would make sure there is at least 250G free on disk.
Momentary outage in seconds to switch the temp table and the old table.

APPROACH #2 : Online DDL
MySQL is also capable of performing DDL online using native SQL.
See my old posts

Aug 01, 2017 : Alter large table without downtime
Feb 12, 2015 : How to evolve MySQL schema while maintaining integrity

Unfortunately, Online DDL does not have options to throttle or halt the operation.
EPILOGUE
These days I use pt-online-schema-change because I can control how fast or slow the process takes. I can even stop and resume it over a series of day. One time I, along with two other DBAs, performed pt-online-schema-change on a 30GB that had super high reads and writes. The Dev Team we worked with asked us to pause the copy to the temp table during the day and resume the copy overnight. We did this in a span of three days. Everything was successful and applications never suffered.


Count of rows for each 2 secs in PostgreSQL

Something like this should work.

SELECT gs.dt, count(*)
FROM (
  SELECT
    date_trunc('second',min(dt)) AS min,
    date_trunc('second',max(dt))+interval '1 second' AS max
  FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt)
INNER JOIN foo AS f
  ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)
GROUP BY gs.dt;

First we need to get the min and the max...

SELECT
  date_trunc('second',min(dt)) AS min,
  date_trunc('second',max(dt))+interval '1 second' AS max
FROM foo;
         min         |         max
---------------------+---------------------
 2017-08-17 21:36:10 | 2017-08-17 21:37:22
(1 row)

now we generate a series with that...

SELECT gs.dt
FROM (
  SELECT
    date_trunc('second',min(dt)) AS min,
    date_trunc('second',max(dt))+interval '1 second' AS max
  FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt);
         dt
---------------------
 2017-08-17 21:36:10
 2017-08-17 21:36:12
 2017-08-17 21:36:14
 2017-08-17 21:36:16
 2017-08-17 21:36:18
 2017-08-17 21:36:20
 2017-08-17 21:36:22
 2017-08-17 21:36:24
... etc.

now we just need to join that to our data. Using integer division on the epoch by 2, we can reduce this to every other second..

INNER JOIN foo AS f
        ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)

However if you want the groups with 0, we make that LEFT OUTER JOIN and count just the right side.

SELECT gs.dt, count(f.*)
FROM (
        SELECT
                date_trunc('second',min(dt)) AS min,
                date_trunc('second',max(dt))+interval '1 second' AS max
        FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt)
LEFT OUTER JOIN foo AS f
        ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)
GROUP BY gs.dt;

If they're out of order, add an ORDER BY gs.dt

         dt          | count
---------------------+-------
 2017-08-17 21:36:10 |     5
 2017-08-17 21:36:12 |     0
 2017-08-17 21:36:14 |     0
 2017-08-17 21:36:16 |     1
 2017-08-17 21:36:18 |     0
 2017-08-17 21:36:20 |     2
 2017-08-17 21:36:22 |     0
 2017-08-17 21:36:24 |     0
 2017-08-17 21:36:26 |     0
 2017-08-17 21:36:28 |     0
 2017-08-17 21:36:30 |     0
 2017-08-17 21:36:32 |     0
 2017-08-17 21:36:34 |     0
 2017-08-17 21:36:36 |     0
 2017-08-17 21:36:38 |     0
 2017-08-17 21:36:40 |     0
 2017-08-17 21:36:42 |     0
 2017-08-17 21:36:44 |     0
 2017-08-17 21:36:46 |     0
 2017-08-17 21:36:48 |     0
 2017-08-17 21:36:50 |     0
 2017-08-17 21:36:52 |     0
 2017-08-17 21:36:54 |     0
 2017-08-17 21:36:56 |     0
 2017-08-17 21:36:58 |     0
 2017-08-17 21:37:00 |     0
 2017-08-17 21:37:02 |     0
 2017-08-17 21:37:04 |     0
 2017-08-17 21:37:06 |     0
 2017-08-17 21:37:08 |     0
 2017-08-17 21:37:10 |     0
 2017-08-17 21:37:12 |     0
 2017-08-17 21:37:14 |     0
 2017-08-17 21:37:16 |     0
 2017-08-17 21:37:18 |     0
 2017-08-17 21:37:20 |     1
 2017-08-17 21:37:22 |     0
(37 rows)


Why index REBUILD does not reduce index fragmentatation?

If an index is very small (I believe less than 8 pages) it will use mixed extents.  Therefore, it'll appear as if there is still fragmentation remaining, as the housing extent will contain pages from multiple indexes.

Because of this, and also the fact that in such a small index that fragmentation is typically negligable, you really should only be rebuilding indexes with a certain page threshold.  It is best practices to rebuild fragmented indexes that are a minimum of 1000 pages.


Shrink database is not shrinking as much as expected on a 1.5 TB database.

There will always be some free space in the database, stored within pages that are too full for other data. Shrinking databases is an incredibly intensive operation, and will take a lot of time as it moves almost every record to somewhere new. A terabyte is a lot of data. Interestingly, you may have more luck creating space by applying compression such as the Hyperbac stuff from Red Gate. That can help remove the free space and compress the rest, but potentially doing fewer writes (and certainly not having to do lots of log activity) in the process. Your mileage may vary though - I don't want to try to speak authoritatively on how Hyperbac compression of your data will work without knowing much about your data. Hope this helps though...


How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

Looking at the syntax I would expect that COLUMS size_clause is a global setting, that can be overridden by specific size_clause for a single column. So in case of FOR COLUMNS SIZE 5 col1 SIZE 6, col2 means SIZE 6 takes precedence for col1, but col2 falls back to the default SIZE 5. This is what I would certainly expect, but I haven't verified this experimentally.


Yikes, I'd be scared to look at your transaction log. This needs to be run in batches. You can experiment with that to determine the fastest runtime. What I like to do is strip out maybe a million or so records, then run updates in various batch sizes, like 1k, 2k, 5k, 10k, at a time to determine the quickest. I've done several hundred million row updates in an hour before. Then again, this also depends on the data size and I'm hoping you aren't messing around with blob fields.


Function taking a string as table and column name

The first problem is the syntax for UPDATE doesn't permit an optional TABLE after. It's just not needed. UPDATE only operates on tables; ALTER works on many targets. The second problem is that should be %s (literal/string) and not %I (identifier).

EXECUTE format(
  $$ALTER TABLE participation ADD COLUMN %I integer DEFAULT 0;$$,
  _sn
);
EXECUTE format(
  $$
    UPDATE participation
    SET %I = 1
    WHERE participation.participant_id = quote_literal(%s||participant_id);
  $$,
  _sn,
  _sn
);

That all said, this seems like a horrible idea. You're adding a column to the table, and setting the value of that column to the "colname+id"? How is that useful?


Separate data from 1 column into multiple columns

CREATE TABLE #Data
    (
      ItemNo VARCHAR(10),
      Variant INT,
      SalesCode VARCHAR(30),
      UnitPrice INT
    )
GO
INSERT  INTO #Data
        ( ItemNo, Variant, SalesCode, UnitPrice )
VALUES  ( 'A', 1, 'SRP', 100 ),
        ( 'A', 2, 'SRP', 200 ),
        ( 'A', 1, 'WSP', 50 ),
        ( 'A', 2, 'WSP', 100 ),
        ( 'B', 1, 'SRP', 300 ),
        ( 'B', 2, 'SRP', 400 ),
        ( 'B', 1, 'WSP', 150 ),
        ( 'B', 2, 'WSP', 200 )
GO
SELECT  ItemNo, Variant, 'SRP' AS SalesCode_SRP,
        SUM(CASE SalesCode
              WHEN 'SRP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_SRP, 'WSP' AS SalesCode_WSP,
        SUM(CASE SalesCode
              WHEN 'WSP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_WSP
FROM    #Data
GROUP BY ItemNo, Variant

GO
DROP TABLE  #Data


Changing Compatibility level from 90 to 120 in sql 2014 migration

Yes you can migrate, but that totally depends upon the results of testing you have done pre-migration. That is , you can test the application on test-staging server before you make any changes in production. As per the results you may get a go-head.

Also, please refer to this MSDN article, to understand the difference between various compatibility levels in SQL server.Therefore you will need to go what has been changed since OLD Compatibility versions till now and how will it behave when you change to new.

As mentioned :

  When a stored procedure executes, it uses the current compatibility
  level of the database in which it is defined. When the compatibility
  setting of a database is changed, all of its stored procedures are
  automatically recompiled accordingly.

Also, running upgrade advisor will be helpful during you're migration strategy which shall let you know for any issues, so that they can be looked after accordingly.

In addition you can read Bad habits : Clinging to old compatibility levels and What is the actual behavior of compatibility level 80? from Aaron.


Reinstall MySql but keep database tables and data

If the database files are accessible, you can follow these steps:

mv /var/lib/mysql /var/lib/mysql.bkp
sudo apt-get install mysql-server
sudo /etc/init.d/mysql stop
mv /var/lib/mysql.bkp /var/lib/mysql
sudo /etc/init.d/mysql start

It should work if you don't have any table file corruption.


What data will be lost with DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS)?

Yes, if you know what data to insert after corrupt data pages are deleted, you should be fine.

make sure you find out the root of corruption to prevent it from happening.

also, once everything is fixed, then do a full back and restore it on a different server to make sure everything is fine.

Your plan sounds good to me.


What is the purpose of the NOLOGGING clause on Oracle database TABLES and INDEXES

There is no such hint as NOLOGGING.

Sure, UPDATE and DELETE operations can not take advantage of NOLOGGING, but there are other operations that can:

logging_clause

  NOLOGGING is supported in only a subset of the locations that support
  LOGGING. Only the following operations support the NOLOGGING mode:

  DML:

  Direct-path INSERT (serial or parallel) resulting either from an
  INSERT or a MERGE statement. NOLOGGING is not applicable to any UPDATE
  operations resulting from the MERGE statement.

  Direct Loader (SQL*Loader)

  DDL:

  CREATE TABLE ... AS SELECT (In NOLOGGING mode, the creation of the
  table will be logged, but direct-path inserts will not be logged.)

  CREATE TABLE ... LOB_storage_clause ... LOB_parameters ... CACHE |
  NOCACHE | CACHE READS

  ALTER TABLE ... LOB_storage_clause ... LOB_parameters ... CACHE |
  NOCACHE | CACHE READS (to specify logging of newly created LOB
  columns)

  ALTER TABLE ... modify_LOB_storage_clause ... modify_LOB_parameters
  ... CACHE | NOCACHE | CACHE READS (to change logging of existing LOB
  columns)

  ALTER TABLE ... MOVE

  ALTER TABLE ... (all partition operations that involve data movement)

  ALTER TABLE ... ADD PARTITION (hash partition only)

  ALTER TABLE ... MERGE PARTITIONS

  ALTER TABLE ... SPLIT PARTITION

  ALTER TABLE ... MOVE PARTITION

  ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION

  ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION

  CREATE INDEX

  ALTER INDEX ... REBUILD

  ALTER INDEX ... REBUILD [SUB]PARTITION

  ALTER INDEX ... SPLIT PARTITION


SQL Server spatial geometry properties on index

The SRID does not appear to be an integral part of the spatial index nor can it be added.

It is integral to the geometry even though there are no real tools for manipulating geometries based on the SRIDs in SQL Server. It does have a few rules about SRIDs.

Geographies must have a SRID from a list of specific SRIDs.
When comparing, manipulating geometries and geographies they must have the same SRID.

For example using the following set up.

CREATE TABLE TEST  (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,GEOM GEOMETRY
    )

INSERT INTO TEST (GEOM) VALUES
(Geometry::Point(0,1,0)),
(Geometry::Point(3,2,1)),
(Geometry::Point(2,3,2)),
(Geometry::Point(1,0,2))

CREATE SPATIAL INDEX TEST_SDX ON TEST(GEOM) WITH (BOUNDING_BOX=(0,0,5,5))

The first query determines distances between points.  It will only return a distance between the 3rd and 4th point as they are the only ones with the same SRID.  The other distances will be NULL.
The second query is for selecting geometries inside a buffer.  Only a single row is returned since the query buffer was created with SRID 2.

SELECT a.ID, b.ID. a.GEOM.STDistance(b.GEOM)
FROM TEST a
    INNER JOIN TEST b ON a.ID < b.ID

ID          ID
----------- ----------- ----------------------
1           2           NULL
1           3           NULL
2           3           NULL
1           4           NULL
2           4           NULL
3           4           3

SELECT *
FROM TEST a
WHERE Geometry::Point(1,1,1).STBuffer(3).STContains(a.GEOM) = 1

ID          GEOM
----------- --------------------------------------------------
2           0x01000000010C00000000000000000000000000000040

Also note SQL Server let me build a spatial index over the geometry column that contains mixed SRIDs, but it is of limited use.  Even if there was a lot more data, the index would be of limited use.

I would suggest to make life easier that you pick a single projection to use across all you data and transform geometries to that.  This can be done alongside the current geometries or replace them.  Also if you choose a Lat/Lon projection, use the Geography data type.

There is an open source project, SQL Server Spatial Tools, that has some tools that will allow you to do projections.  You will however have to know the parameters for the projections you are using as it does not appear to have a list of projections to work with.  I can't advise how good these are, as I haven't used them.


Left Join of a Subselect query very slow

I'm not sure in my accuracy... test

SELECT nlni.id as id,
       COALESCE(GROUP_CONCAT(nlga.name ORDER BY nlga.name SEPARATOR '|'),
                'Worldwide') as countries
FROM newsletter_newsitem as nlni
LEFT JOIN newsletter_newsitem_areas as nlni_areas ON nlni_areas.newsitem_id = nlni.id
LEFT JOIN newsletter_geoarea as nlga ON nlni_areas.geoarea_id = nlga.id AND nlga.type = 2
GROUP BY nlni.id
WHERE nlni.archive_id IS NULL
  AND nlni.id IS NOT NULL


How to setup two mysql servers with group replication to work individually, when no network

Group replication is not a good match for scenarios where server diverge in time.

What you want is a Master to Master setup, where given two server M1 and M2, when the network is restored, M1 is a replication slave of M2 and M2 is a replication slave of M1.

A crucial details is that both servers must not do conflicting updates to the same data, so you should partition your data accordingly and you can also use auto increment settings to generate different ids on different machines.


I have done similar things with images stored in the database but that is a a lot different than PDF.

Actually, for this type of operation, there is no difference between images, PDF, or anything. If you have a binary representation of a file and can save it to disk, then that will work for all file types. Bytes are bytes, and as long as the same bytes are written in the same sequence that they were read in (written as binary, not text), then it doesn't matter what those bytes end up being, even if it is a plain text file or image or DLL or mp3, etc.


SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs'

I wonder if your config.inc.php has a properly configured controluser. The error message (indicating that permission was denied for user '') suggests some problem with your configuration file or the controluser itself.

Please look for the two controluser lines which should read as:

$cfg['Servers'][$i]['controluser']   = 'admin_db1';
$cfg['Servers'][$i]['controlpass']   = 'mypassword';

If they are there and correct, please try connecting as that account from the command line client (on the same machine running phpMyAdmin) to see if you can manually USE the phpmyadmin database and that the table pma_table_uiprefs exists.

If all that fails, you may wish to post your config.inc.php here, obscuring any sensitive information such as usernames, passwords, and hostnames.

Or, alternatively, if you have no need for the advanced configuration options you can just remove the controluser, controlpass, and pmadb lines from your config.inc.php and it will bypass that part completely.


To restart Oracle, connect via SQL-PLUS as SYSDBA as you did previously, and type in the commands

shutdown normal;
startup mount;
alter database open;
exit;

Oracle SQL Developer can help you graphically administer the Oracle Database.

Most Oracle metadata are stored in special tables. For example,

To show all tables: select * from cat;
To show all users/schemas: select * from dab_users;
To change schemas: alter session set current_schema=NEW_SCHEMA_NAME;

Are there any other specific Oracle commands you were looking for?


Why is my composite index not used for this table?

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.


Assertion failure from InnoDB while restoring using Innobackupex

Luckily there is a small fix for this if you do not have the luxury of stopping traffic on the DB. By using --ibbackup xtrabackup_55 the recovery recognizes the pending transactions and handles them accordingly.

Our script now looks like this:

nice -n 19 innobackupex-1.5.1 --stream=tar ./ --user XXXX --password 'YYYYY' \
  --ibbackup xtrabackup_55 | \
  nice -n 19 /usr/bin/pigz - | \
  ssh -p 32231 ***@backup.****.com \
    'cat - > /var/backup/db/#{Time.now.strftime("%Y-%m-%d")}.tar.gz'

and

innobackupex-1.5.1 --apply-log . --ibbackup xtrabackup_55

respectively.

Just thought I would share this tidbit, due to the fact that looking for a solution cost me approx. 3,5 hours.


Copy Specific tables from one database to another database on the same server

There are a few possible options to this. It kind of depends on your requirements I guess.

First of all it depends on your table definitions, indexes, keys and constraints.

If your table is just a simple heap and you just want to create a copy of the data you could use

drop table mytable;
select * from [databasename].[schemaname].[mytable] into mytable;

If your table is an actual copy including indexes you could use

truncate table mytable;
insert into mytable select * from [databasename].[schemaname].[mytable] ;

That obviously wouldn't work if you have foreign key constraints on mytable and the constraints don't resolve. Then you would need to update all related tables first or disable they constraints and enable/recheck them if you don't have any extra records in the referenced tables.

The import and export wizard suggestion isn't excluded because you can choose to save the result as a package and you could call that package from a script using dtexec.exe


Adding DATETIME() and Status column with xp_FileExist result

You need to add code to your question that doesn't work (that causes the error you are seeing).

Having said that, you probably want to modify the code you are using to something like:

DECLARE @path varchar (100);
DECLARE @file varchar (100);
SET @file = 'C:\temp\*.*';
SET @path = 'dir /B ' + @file;

DECLARE @FILEEXISTS TABLE(
        [NAME] VARCHAR (255) NULL
);

INSERT into @FILEEXISTS
exec master..xp_cmdshell @path ;

SELECT *
FROM @FILEEXISTS;

DECLARE @table TABLE(
        [NAME] VARCHAR (255) NULL
        , somecol VARCHAR(255) NULL
);

INSERT INTO @table(NAME)
SELECT name
FROM @FILEEXISTS;

UPDATE @table SET somecol = 'test' /* add a 'WHERE' clause if necessary */;

SELECT *
FROM @table;

This uses a table-variable to store the results, and doesn't execute the unnecessary xp_cmdshell right after the set @path=... line.

The @table declaration (and other code) I added to the end allows you grab the results into @fileexists, then insert those results into @table and add the other columns as necessary.  xp_cmdshell returns a single column of results, one line for each line output by the command it runs.


Trying to repair query store, but sp_query_store_consistency_check does not exist

Like the other Query Store related DMVs, functions, etc, sp_query_store_consistency_check is in the "sys" schema, not the dbo schema.  I imagine this is just a typo on Tracy's blog.  Still, the command should work even when referencing the dbo schema.

You also need to call the procedure from the user database where you have query store enabled.  Try this:

USE YourDatabaseName;
GO

EXEC sys.sp_query_store_consistency_check;

Tony mentioned in the comments that he couldn't find the stored procedure definition anywhere.  This is typical of extended stored procedures.  Running EXEC sp_helptext 'sys.sp_query_store_consistency_check'; on it, even from a DAC connection, returns this:

Text
-----------------
(server internal)

You can see it mentioned in sys.all_objects though:

SELECT
    [name],
    [type_desc],
    is_ms_shipped
FROM sys.all_objects
WHERE [name] LIKE '%consistency%';

Results:

name                              type_desc                 is_ms_shipped
--------------------------------- ------------------------- -------------
sp_query_store_consistency_check  EXTENDED_STORED_PROCEDURE 1

My mistake, I just realized that you are on SQL Server 2016.  The above procedure was introduced in SQL Server 2017.

The only way I know of to get the Query Store back into a good state in 2016 is to clear out all the data:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE CLEAR ALL;


Will you ever need to show a user's likes of different types all at once? If so, I would keep it in one table. If you run into scale issues, you can always start partitioning by the like type column and basically have separate tables on the back end.


Amazon EBS Type for MySQL Replication Server

Your "distribution master" -- assuming it is running MySQL Server, not a specialized program specifically for directly replicating  binlogs -- cannot "just get data from Master and replicate to the Slave when gets connection" and relay the incoming events to downstream replicas.  It will also be keeping a copy of the entire data set and processing the replication events, keeping its local copy in sync before sending the events downstream.

It will, therefore, need to have as much I/O capacity as needed in order to keep up with the master.

The question of whether sc1 Cold Storage (mechanical) HDD EBS volumes offer sufficient capacity in both write throughput and IOPS is not a question that can be answered in general.

The answer depends entirely on your workload.

There is nothing in sc1 that is intrinsically incompatible with MySQL, but a 500 GiB sc1 volume has a baseline throughput of only 6 MiB/s and a burst throughput of 40 MiB/s.  There is a reason it is marketed as "cold storage," and a reason it is inexpensive.

An st1 Optimized Throughput (also mechanical) HDD volume has > 3x the performance for < 2x the price of sc1 so is a somewhat safer bet.  I have recently begun testing st1 volumes for binary log and relay log storage on some non-critical replicas -- with the datadir still on gp2 SSD volumes -- and have not been seeing any replication lag nor taxing of these volumes according to the CloudWatch metrics nor significant iowait... but binary logs and relay logs are, of course, largely sequential write and read, which puts them within the design parameters of both st1 and sc1.

The bottom line, though, is that your experience will be highly correlated to your workload.

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html

Remember also that if you create a volume of any storage class from an EBS snapshot (such as when you are testing different storage classes -- a snapshot made from one type of volume can be used to create a volume of a different type) that you will see degraded performance until the volume is warmed up.

Assuming the volume is /dev/xvdf, this might look like dd if=/dev/xvdf obs=1M | pv -pterab > /dev/null ... followed by a few hours of waiting.  Don't try to optimize a warmup with fio or a large block sizes on smaller instances -- you'll DoS your own instance if you hit a stretch of already warmed-up blocks and the volume's read throughput spikes significantly above the available network bandwidth on the instance's Ethernet interface.  If this happens, the instance will fail its health checks.


Does DBA need (a read) access to virtualization infrastructure? And why?

Short Answer: Yes.

There are many reasons but the few that stick to mind:

1.) Trust but verify - SQL cares a lot about its environment, the hardware or virtualized system it is on. When I help a company with SQL on VM issues it is normally a misconfigured VM. In many cases the idea of SQL on VM is about to be thrown away.

2.) DBAs should look at memory reservations, alerts and performance conditions, how overcrowded a physical host is ( or isn't) and understand how it works.

3.) DBAs are learning more and more about virtualization. Through excellent blog posts and SQL events, the VMware knowledge in the SQL dba community is rising. An extra set of eyes may help ensure things are well tuned and future proofed.

4.) Prove yourself... If you've done everything right and built a great environment, show it off. Read access to center isn't going to destroy your system and you can show those pesky DBAa everything is up to snuff. And if it isn't? Don't you want to know? ;-)


Update table based on reference values from another table

You need to use an UPDATE statement to do this. Unfortunately SQLite does not support using a JOIN clause in an UPDATE statement so you will have to emulate the JOIN within the WHERE clause like so:

UPDATE Table2
   SET Number = Number + 10
 WHERE (SELECT Value FROM Table1 WHERE Name = Table2.Name1) >
       (SELECT Value FROM Table1 WHERE Name = Table2.Name2);

Similar questions that might also help you:

How do I make an update while joining tables on sqlite

Update with JOIN in sqlite


How to modify the below script to take tables and its sizes for a particular databse to send via mail to a recipient?

My guess is the query in your question looks like PostgreSQL information_schema. The current database is assumed when you connect to it.

I believe you want the MySQL equilavent

Here is my suggestion for the Top 10 tables in the Current Database.

SET @lineitem = 0;
SET @topitem = 10;
SELECT line,tbl,units,dat,ndx,siz FROM
(SELECT
    (@lineitem:=@lineitem+1) line,tbl,
    CONCAT(SUBSTR('KMGTP',ex,1),'B') units,
    FORMAT(dt/POWER(1024,ex),2) dat,
    FORMAT(nx/POWER(1024,ex),2) ndx,
    FORMAT(sz/POWER(1024,ex),2) siz
FROM (SELECT *,FLOOR(GREATEST(1,
    LOG(GREATEST(dt,1))/LOG(1024),
    LOG(GREATEST(nx,1))/LOG(1024),
    LOG(GREATEST(sz,1))/LOG(1024))) ex FROM
(SELECT table_name tbl,data_length dt,
index_length nx,data_length+index_length sz
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY sz DESC) AAA) AA) A
WHERE line <= @topitem;

If you have a specific database in mind, like mydb, you would changed the subquery WHERE clause

SET @lineitem = 0;
SET @topitem = 10;
SELECT line,tbl,units,dat,ndx,siz FROM
(SELECT
    (@lineitem:=@lineitem+1) line,tbl,
    CONCAT(SUBSTR('KMGTP',ex,1),'B') units,
    FORMAT(dt/POWER(1024,ex),2) dat,
    FORMAT(nx/POWER(1024,ex),2) ndx,
    FORMAT(sz/POWER(1024,ex),2) siz
FROM (SELECT *,FLOOR(GREATEST(1,
    LOG(GREATEST(dt,1))/LOG(1024),
    LOG(GREATEST(nx,1))/LOG(1024),
    LOG(GREATEST(sz,1))/LOG(1024))) ex FROM
(SELECT table_name tbl,data_length dt,
index_length nx,data_length+index_length sz
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY sz DESC) AAA) AA) A
WHERE line <= @topitem;

Give it a Try !!!


Can an SQL Server Failover Cluster support a named instance?

You can use either a default instance or a named instance on a Failover Cluster. I have supported hundreds of clusters, most of them used named instances.

When connecting to it, use networkname\instancename. You can use other ways, but it makes most sense to use networkname since that moves to another node on failover.


Why does my database using the simple recovery model have such a large transaction log?

Your first stop for problems like this should always be this query:

SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
WHERE [name] = 'YourDatabaseName';

From the sys.databases documentation:

  Reuse of transaction log space is currently waiting on one of the following as of the last checkpoint.

There's a list of possible values on that page, and a fair amount of additional detail for the different values here: The Transaction Log (SQL Server) - Factors that can delay log truncation

It's possible that, if your server is not very busy, that it had been a while since an automatic CHECKPOINT ran.  This is the process that writes data pages to disk after they have been modified in memory.  Operations in the transaction log can't be overwritten until they have been successfully flushed to the data file, so you end up with a log_reuse_wait of 1 = Checkpoint.

Since you're using the simple recovery model, this seems like one of the more likely culprits (since you ruled out long-running transactions).  Running a full database backup causes a CHECKPOINT to run, which is likely what caused the log file to finally clear for you after running the backup.  From Database Checkpoints (SQL Server), specifically about the "Internal" checkpoint type:

  Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.


Query to return multiple rows with same UID and entered on the same date

If you need to output rows where the same date appears more than once for a give orderuid, the following should to the job :

SELECT orderuid, ordernumber, datein
FROM
(
  SELECT a.orderuid,a.ordernumber,a.datein,
   count(1) OVER(PARTITION BY a.orderuid,a.datein) as num_with_same_date
        from  ordertask a
)b WHERE num_with_same_date >1;

Updated.

SELECT orderuid, ordernumber, datein
FROM
(
  SELECT a.orderuid,a.ordernumber,a.datein,
   count(1) OVER(PARTITION BY a.orderuid,a.datein) as num_with_same_date,
   count(1) OVER(partition by a.orderuid) as total_num_uid
        from  ordertask a
)b WHERE total_num_uid =num_with_same_date  ;


Is it somehow possible to predict / foresee when SQL Server will trigger auto-growth?

I think the best way is to go based off of previous events, and one way to do that is to use a trace to start capturing history.

https://sqlblog.org/2007/01/11/reviewing-autogrow-events-from-the-default-trace

Also some ideas here:

https://serverfault.com/questions/33329/how-can-i-get-alerted-when-auto-growth-occurs-on-a-sql-server-database
http://www.sqlservercentral.com/articles/autogrowth/93229/

As you can see, there are many ways to approach this.  Let me know what you think.


Most probably there are three schemas (users) that contain that table. You need to include OWNER = 'FOOBAR' in your query to all_tab_columns (or use user_tab_columns). You can also include the OWNER column in your select list to verify this.

For VARCHAR (and other character columns) the size is stored in CHAR_LENGTH as documented in the manual. Note that you also need to check CHAR_USED to find out if the definition is in bytes or characters (VARCHAR(10 Bytes) vs. VARCHAR(10 Char)).

For numeric columns the definition is stored in DATA_SCALE and DATA_PRECISION.

This is all described in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2103.htm#I1020277

Are you aware that you can retrieve the full definition of a table using `DBMS_METADATA using a SELECT statement:

SELECT dbms_metadata.get_ddl('TABLE', 'EMPLOYEE', 'SCOTT')
FROM dual;


How to preserve ID generated from a PRIMARY KEY when moving data into a new table?

When you have a Primary Key with an auto_increment it will generate a new ID only if you insert a NULL value. If you set ID=4 in your INSERT, the ID will be 4 so you'll not loose your ID during your "move" operation.

We don't have the "SEQUENCE" notion like in Oracle database so your "global ID" problem it's not so easy to do.

Maybe you can try something like this (but it'll add complications for just a 4 millions rows table)

Create a table used for generates your "Global ID", with one int filed auto_incremented:

CREATE TABLE test.sequence_table (next_id int primary key auto_increment);

When you want insert a new row in your child table:

Solution 1: With SELECT in information_schema

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=(auto_increment - 1) FROM information_schema.tables WHERE table_schema="test" AND table_name="sequence_table"; -- Here I use a MySQL Variable but you can store it in PHP or whatever

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)

Edit after ypercube comment:

Solution 2: With LAST_INSERT_ID()

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=LAST_INSERT_ID(); -- Use of the MySQL function LAST_INSERT_ID()

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)


Create a secondary read-only copy of DB in a different server

If you only need the database updated once a day, why not simply have a SQL Server Agent job restore a backup from the production database onto the secondary machine nightly?

Simple to setup and provides a handy way to validate your backups.


Appending backups while overwriting expired sets

Unfortunately, no, that won't work.  It will keep appending but will leave the expired sets in the same archive.  This screenshot shows a backup archive using your script, set to RETAINDAYS = 2, run once in November (VM trickery) and again today.

I think what you're after is a rolling backup, so assuming this is weekly, then after a burn in period, it should retain 7 old backup sets, remove one and append a new one.

Personally, I would keep a single backup set per file and use the cleanup feature in the maintenance wizard to remove old files; that or a regular Windows Scheduled Task.  There shouldn't be any real need to keep backup sets in a single file.


SQL Server 2016 SQL Agent Token Not Working

Okay, I'm now thinking this is something with Windows Server 2016 as this is the first SQL Server 2016 install I've done on Windows Server 2016.

On my Windows Server 2012 R2 (Standard) machine with SQL Server 2016, this works in a SQL Agent CmdExec job step:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

But on my new Windows Server 2016 (Standard) with SQL Server 2016, the above does not work.  I have to do either:

C:\Windows\System32\cmd.exe -c sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

Or this works as well:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."
GO

The cmd.exe -c argument (I was unfamiliar with) is a batch terminator; from Microsoft docs:

  -c batch_terminator

  Specifies the batch terminator. By default, commands are terminated
  and sent to SQL Server by typing the word "GO" on a line by itself.
  When you reset the batch terminator, do not use Transact-SQL reserved
  keywords or characters that have special meaning to the operating
  system, even if they are preceded by a backslash.

I don't know why the batch terminator is necessary in this situation...

  The GO Command and the Semicolon Terminator

  The GO Command

  “GO” is a batch terminator. Technically speaking, the GO command is
  not even a part of the Transact-SQL language. It is really a command
  used by the SQLCMD, OSQL and ISQL utilities that can also be used
  within Query Analyzer and the Query Editor window.

  NOTE: A batch should not be confused with a script. A batch is a set of T-SQL statements that are submitted for execution as a group. A
  script is simply a file containing set of T-SQL statements. One script
  can contain many batches.


The account that owns the database objects should not be used by anyone to access that data.  (Except by you and your fellow DBAs, who know what they're doing!)

A simple, "additive", security model is far easier to understand and maintain.

Create "Application" and "User" roles and grant the appropriate levels of access, then grant these to user and application accounts.

create user data_owner ... ;
create table data_owner.table1 ... ;

create role app_role_1 ;
grant select, insert, update, delete on data_owner.table1 to app_role_1 ;

create role user_role_2 ;
grant select on data_owner.table1 to user_role_2 ;

grant app_role_1 to application_1_account ;
grant user_role_2 to fred, wilma ;


A table with a foreign-key, referencing the tables own primary key

DELETE RESTRICT in DB2 deals with when you wish to delete records from a parent table.

As noted from the DB2 Information Center:

  The delete rule of a referential constraint applies only when a row of
  the parent table is deleted. More precisely, the rule applies only
  when a row of the parent table is the object of a delete or propagated
  delete operation (defined below), and that row has dependents in the
  dependent table of the referential constraint. Consider an example
  where P is the parent table, D is the dependent table, and p is a
  parent row that is the object of a delete or propagated delete
  operation. The delete rule works as follows:

  With RESTRICT or NO ACTION, an error occurs and no rows are deleted.

  With CASCADE, the delete operation is propagated to the dependents of p in table D.

  With SET NULL, each nullable column of the foreign key of each dependent of p in table D is set to null.

  Any table that can be involved in a delete operation on P is said to
  be delete-connected to P. Thus, a table is delete-connected to table P
  if it is a dependent of P, or a dependent of a table to which delete
  operations from P cascade.

So, while it appears like this may prevent records from being deleted from the table, it doesn't. It just prevents records from being deleted from the table if there are records in the child table. Since there is no child table, that clause is essentially worthless and really only adds cost to determining INSERT/UPDATES/DELETES. It is fanciness that serves absolutely no purpose.

You can test this yourself by issuing the following commands:

db2 "insert into <schema>.act values (1, 'blah', 'another blah')";
db2 "select * from <schema>.act";
db2 "delete from <schema>.act where actno = 1";
db2 "select * from <schema>.act";

I tested that myself against the sample database and it ran fine. You will find that it does in fact delete the record just added.

Personally I think it is just fanciness that somebody threw in there that doesn't really belong.


Postgres - Where condition on nested joins table not working

The inner join between invoices and invoice_items turns the outer join between order_items as invoice_items back into an inner join. You need an outer join for invoices as well. And then you need to move the restriction on the invoice state into the join condition.

SELECT oi.*
FROM order_items oi
  LEFT JOIN invoice_items ii
         ON ii.order_item_id = oi.id
  LEFT JOIN invoices inv
         ON inv.id = ii.invoice_id AND inv.state <> 'cancelled'
GROUP BY oi.id
HAVING (booked_quantity - cancelled_quantity - COALESCE(SUM(invoice_items.quantity),0)) > 0
ORDER BY oi.id ASC;

Maybe an EXISTS query is what you are looking for:

select oi.*
from order_items oi
where exists (select ii.order_item_id
              from invoices inv
                join invoice_items ii ON inv.id = ii.invoice_id
              where ii.order_item_id = oi.id
                and inv.state <> 'cancelled'
              group by ii.order_item_id
              having booked_quantity - cancelled_quantity - coalesce(sum(ii.quantity), 0) > 0)
order by oi.id asc;


SQL Server Ubuntu server - failure: remote connection from Win 7

SQL Server Native Client 10 works for Win 10, Win 7, and Win XP.
I'm not sure not sure it was necessary yet but, for Win XP, I also installed windowsxp-kb4019276-x86-embedded-enu (and hacked the registry to enable TLS 1 & 2).
$2M worth of Mass Spectrometers and a $100K ICP-AES can now use the LIMS with SQL Server 2019.


SQL Server 2005 replication for a data warehouse

You have "data warehouse" in quotes, which could mean one of two things:  you're either looking for an actual data warehouse or you aren't (yay, truisms!).

If you're looking for an actual data warehouse, there's an entire Microsoft BI stack available to you as part of your already-purchased SQL Server licensing:  SQL Server Integration Services for ETL, SQL Server Analysis Services for creating cubes, and SQL Server Reporting Services and PowerPivot for analyzing data.  Doing a data warehouse right is not a trivial activity, even for somebody with a great deal of experience designing and developing transactional databases.  The big problem is not so much learning the tools and languages (though MDX is so different from T-SQL that it's in its own world), but getting the right mindset.  If you do go down this route, start with Ralph Kimball's Data Warehouse Toolkit.  It's a fairly long book and was not written for any particular technology stack, but instead tries to give you an understanding of what makes for a successful data warehousing project.  Doing it right could possibly take years even if you already know the tools, because the single most important thing about data warehousing is understanding the end user.  You really have to know what the end users (the business people) want, how they want it, how they can understand what they're seeing, and how what data and systems you have can get translated into what they want in a single, consistent manner.

If you're trying to do something "on the cheap," there's a much higher likelihood of failure, to be honest.  This is why I'm a little concerned about why you put "data warehouse" in quotes.  Yeah, there are easy-to-develop non-warehouse models you could come up with (such as possibly replicating all of the tables over to another server), but they're typically going to fall into one (or more) of a few categories:

Too complex for end users.  End users typically don't understand normalized data and shouldn't be expected to know SQL or the details of a GROUP BY clause.  Your best outcome in that scenario is that IT people continue writing the reports against this new system; the more likely outcome is that IT people continue writing the reports against the old systems and everybody forgets that the warehouse exists at all...
Inconsistent data.  As you go through this, you might find System 1 says that Order 37 includes an order of a dozen ink pens, but System 2 says it was 14 pens.  And System 3 had a free-form text field where instead of putting in a number, somebody typed in "dog."  If they are all supposed to represent the same thing, which is correct?  Finding (and working with the business on creating rules to solve) these data inconsistencies is a key part of building a good warehouse model.
Inconsistent grain.  If one system has order lines and a second only has orders, there are ways to reconcile this, but if you simply throw the tables out there and leave it to the end user, they may come up with the wrong method of reconciliation (i.e., one which gives incorrect results) without knowing it.

These are among the many problems you'll run into in the world of business intelligence.  Good data warehousing (be it Kimball-style, Inmon-style, some hybrid, or something else) doesn't avoid these problems, but instead gives you a mindset where you can understand and solve them.  Rolling a cheap-and-easy solution may let you paper over the problems in development, but we don't measure success simply through getting out of development; people have to use it for a warehouse of any type to have value.

Hourly updates isn't necessarily that big of a problem for a good warehouse, as long as you have the hardware necessary to do the job.  You can create SSIS packages to run on SQL Agent jobs (or through any other kind of scheduler, honestly) which perform the necessary tasks (pulling data from source systems, doing any transformations, doing any necessary staging, and building Analysis Services cubes), so scheduling isn't a problem if the individual processes are fast enough.


How to Move TempDB Files to a Different Drive or Folder?

Moving the TempDB files is a 2-step process:

Tell SQL where you want your new TempDB files to go to
Restart the SQL Server service for the change to take effect

To tell SQL where to create the new TempDB files, you can use:

DECLARE @newDriveAndFolder VARCHAR(8000);

SET @newDriveAndFolder = 'Z:\YourTempDBfolder';

SELECT [name] AS [Logical Name]
    ,physical_name AS [Current Location]
    ,state_desc AS [Status]
    ,size / 128 AS [Size(MB)] --Number of 8KB pages / 128 = MB
    ,'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name])
    + CHAR(9) /* Tab */
    + ',FILENAME = ''' + @newDriveAndFolder + CHAR(92) /* Backslash */ + f.[name]
    + CASE WHEN f.[type] = 1 /* Log */ THEN '.ldf' ELSE '.mdf' END  + ''''
    + ');'
    AS [Create new TempDB files]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb')
ORDER BY f.[type];

This will generate the T-SQL statements you need to run to move the files to the new drive:\folder you want. (click image to make larger)

When you have ran your moving statements, you can run the above query again, to check that the Current Location column is now showing your new drive:\folder.

Once you're happy with your changes, restart the SQL Server service.


How are precision and scale calculated when decimals are multiplied?

I have an answer to a very similar question on StackOverflow that goes over this:

How do I control the datatype of a computed column?

But, to state it more completely here:

I suggest reviewing the source documentation used in the posted linked in the Question as it describes the formulas to use for 6 different operations involving decimals: Precision, Scale, and Length. That chart shows:

Operation: e1 * e2
Result precision: p1 + p2 + 1
Result scale: s1 + s2

In the case of having a DECIMAL(19, 10) * DECIMAL(3, 0), we are starting with the following values:

p1 = 19
p2 =  3

s1 = 10
s2 =  0

Which gives us:

Precision = (19 + 3 + 1) = 23
Scale     = (10 + 0) = 10

And that matches the DECIMAL(23, 10) as reported by SQL Server.

And to make it even clearer, I added a 4th column to the test query to reduce the initial division computation to the resulting DECIMAL(19, 10) so there is no question of the order of the 3 mathematical operations happening in C2 and C3. I also am using the sys.dm_exec_describe_first_result_set DMV so that there is no need to create a temp table :-)

SELECT rs.column_ordinal, rs.name, rs.system_type_name, rs.max_length,
       rs.[precision], rs.[scale]
FROM   sys.dm_exec_describe_first_result_set(N'
SELECT CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) AS C1
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * 100 AS C2
     ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * CAST(100 AS DECIMAL (3, 0)) AS C3
      ,CAST(0.5 AS DECIMAL(19,10)) * CAST(100 AS DECIMAL(3,0)) AS C4
', '', 0) rs

Returns:

column_ordinal    name    system_type_name    max_length    precision    scale
--------------    ----    ----------------    ----------    ---------    -----
1                 C1      decimal(19,10)       9            19           10
2                 C2      decimal(23,10)      13            23           10
3                 C3      decimal(23,10)      13            23           10
4                 C4      decimal(23,10)      13            23           10

The full chart of Precision and Scale calculations from the linked MSDN page above is copied below for convenience:

Operation           Result precision                          Result scale *
---------           -----------------------------------       ------------
e1 + e2             max(s1, s2) + max(p1-s1, p2-s2) + 1       max(s1, s2)

e1 - e2             max(s1, s2) + max(p1-s1, p2-s2) + 1       max(s1, s2)

e1 * e2             p1 + p2 + 1                               s1 + s2

e1 / e2             p1 - s1 + s2 + max(6, s1 + p2 + 1)        max(6, s1 + p2 + 1)

e1 UNION |          max(s1, s2) + max(p1-s1, p2-s2)           max(s1, s2)
  EXCEPT |
 INTERSECT e2

e1 % e2             min(p1-s1, p2 -s2) + max( s1,s2 )         max(s1, s2)

* The result precision and scale have an absolute maximum of 38.
  When a result precision is greater than 38, the corresponding scale
  is reduced to prevent the integral part of a result from being truncated.


SQL Server - NTEXT columns and string manipulation

Converting to nvarchar(max) and back to ntext does make life simpler from a code point of view, but it does mean converting and rewriting the whole (perhaps very large) value, with all the CPU and logging overhead that implies.

An alternative is to use UPDATETEXT. This is deprecated, just like ntext, but it can reduce the logging overhead significantly. On the downside, it means using text pointers, and it only operates on one row at a time.

The following example code uses a cursor to work around that limitation, and uses PATINDEX instead of CHARINDEX since the former is one of the few functions that work directly with ntext:

Sample data

CREATE TABLE dbo.PhilsTable
(
    comment ntext NULL,
    anothercomment nvarchar(50) NULL
);

INSERT dbo.PhilsTable
    (comment, anothercomment)
VALUES
(
    CONVERT(ntext,
        N'This is a test UPDATEHERE This is the end of the test ' +
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)),
    CONVERT(nvarchar(50), N'. This is inserted.')
),
(
    CONVERT(ntext,
        N'This is a test UPDATEHERE This is the end of the test ' +
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)),
    CONVERT(nvarchar(50), N'. This is inserted.')
),
(
    CONVERT(ntext,
        N'This is a test UPDATEHERE This is the end of the test ' +
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)),
    CONVERT(nvarchar(50), N'. This is inserted.')
);

Cursor declaration

DECLARE c
    CURSOR GLOBAL
    FORWARD_ONLY
    DYNAMIC
    SCROLL_LOCKS
    TYPE_WARNING
FOR
SELECT
    TxtPtr = TEXTPTR(PT.comment),
    Src = PT.anothercomment,
    Offset = PATINDEX(N'%UPDATEHERE%', PT.comment) + LEN(N'UPDATEHERE') - 1
FROM dbo.PhilsTable AS PT
WHERE
    PT.comment LIKE N'%UPDATEHERE%'; -- LIKE works with ntext

OPEN c;

Processing loop

DECLARE
    @Ptr binary(16),
    @Src nvarchar(50),
    @Offset integer;

SET STATISTICS XML OFF; -- No cursor fetch plans

BEGIN TRANSACTION;

    WHILE 1 = 1
    BEGIN
        FETCH c INTO @Ptr, @Src, @Offset;

        IF @@FETCH_STATUS = -2 CONTINUE; -- row missing
        IF @@FETCH_STATUS = -1 BREAK; -- no more rows

        IF 1 = TEXTVALID('dbo.PhilsTable.comment', @Ptr)
        BEGIN
            -- Modify ntext value
            UPDATETEXT dbo.PhilsTable.comment @Ptr @Offset 0 @Src;
        END;
    END;

COMMIT TRANSACTION;

CLOSE c; DEALLOCATE c;


A partial answer:

$ mysql -u root --column-type-info -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

Note the extra parameters.  Now I will issue just one simple command; see what it comes back with:

mysql> SELECT * FROM things;
--------------
SELECT * FROM things
--------------

ERROR 1046 (3D000): No database selected
mysql> use try;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM things;
--------------
SELECT * FROM things
--------------

Field   1:  `thing_id`
Catalog:    `def`
Database:   `try`
Table:      `things`
Org_table:  `things`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field   2:  `name`
Catalog:    `def`
Database:   `try`
Table:      `things`
Org_table:  `things`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     96
Max_length: 7
Decimals:   0
Flags:      NOT_NULL

+----------+---------+
| thing_id | name    |
+----------+---------+
|        2 | thing 2 |
|        3 | thing 3 |
|        4 | thing 1 |
+----------+---------+
3 rows in set (0.00 sec)

mysql>

I don't think there is a way to get the 'type' of an expression.


Mysql Fragmentation. How bad is too bad?

Fragmentation should have little to no effect on performance of InnoDB, it only affects the size of the data file.  So it's "too much fragmentation" if you are running out of disk space.

If you have innodb_file_per_table set to OFF, then all InnoDB tables are stored together in one tablespace file, by default this file is called ibdata1.

The value of information_schema.tables.data_free shows the same value for all tables in this tablespace.

See http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html

  When you ask for available free space in the tablespace by issuing a SHOW TABLE STATUS statement, InnoDB reports the extents that are definitely free in the tablespace.

In other words, the value reported in data_free is not the amount of free space for any single table, it's the amount of space taken by totally free extents in the tablespace file.  An extent is a block of 1MB, which is 64 pages by default.

You may notice that 49085939712 is exactly 46812MB.  That's how many unused extents you have in your tablespace file.

But you probably also have partially unused space within other extents.  This is not reported by data_free.  It's a little bit harder to get this information, one way is to use the InnoDB tablespace monitor to report it.

Doing OPTIMIZE TABLE won't recover free space, since you are apparently not using innodb_file_per_table.  InnoDB will reuse those extents for new data before expanding the size of your tablespace file further, but if you need to recover that ~45GB right away, you unfortunately have to do it the hard way:

Dump all InnoDB tables (or at least convert them to MyISAM temporarily)
Shut down mysqld
rm ibdata1
Restart mysqld
Restore all InnoDB tables.


Does mysql "order by" guarantees to give the same order for the same query every time if the sort key is equal?

When the field being sorted on is not unique, then the order of the results are non-deterministic, meaning there is no guarantee on their exact ordering. Specifically in your example case where the first 10 results all share the same exact value for the s field could yield indeterminate results for the first time you run the query vs the second time. It is possible you see the same result in both cases or not at all as you guessed. You would need to add another field to the ORDER BY clause that uniqifies the sort in order to always guarantee deterministic results.
For a little proof of case and further reading, here is a DBA.StackExchange question and answer where the user was experiencing unpredictable ordering of results because the field they were sorting on wasn't unique. The comments on that answer briefly mention that being the cause.


How to group-by to get MultiPoint/GeometryCollection in MySQL?

As Evan Carroll said in a comment: Unlike PostGIS, spatial aggregates are not available for MySQL.

The workaround left in a comment by danblack works:

  It looks like you want MultiPoint to be an aggregate function, which is isn't. You'll probably need something like ST_GeomFromText(CONCAT('MultiPoint(', GROUP_CONCAT(...), ')')), where ... gets an X Y string from fix_geom.


You must group by all of the columns i the select clause:

GROUP BY ticket.queue_id, queue.name, article.create_time

or add aggregate functions such as MAX:

SELECT ticket.queue_id, MAX(queue.name), MAX(article.create_time),
       COUNT(article.id)
FROM article
JOIN ticket
    ON article.ticket_id=ticket.id
JOIN queue
    ON ticket.queue_id=queue.id
GROUP BY ticket.queue_id

I also moved the ON clause.

SQL92 required a full group by, SQL99 loosened this and requires that all columns in the select clause should be functionally dependent of the group by clause. AFAIK, all vendors kept the stricter SQL92 definition.

You can make MySQL more compliant with other vendors by adding ONLY_FULL_GROUP_BY to @@sql_mode. By default MySQL will not complain if you "under specify" the group by, instead it will give you a random row. Example:

create table t (x int, y int, z int);
insert into t (x,y,z) values (1,1,1),(1,2,1);

select x,y,count(*) from t group by x;
+------+------+----------+
| x    | y    | count(*) |
+------+------+----------+
|    1 |    1 |        2 |
+------+------+----------+
1 row in set (0.00 sec)

set @@sql_mode = 'ONLY_FULL_GROUP_BY'

select x,y,count(*) from t group by x;
ERROR 1055 (42000): 'mysql.t.y' isn't in GROUP BY

There is also a bunch of other settings (PIPES_AS_CONCAT for example) that can be added to @@sql_info to make MySQL more compliant.


It is not safe or reliable to ftp the files in your MySQL datadir while the MySQL server is running. MySQL keeps part of the data and metadata in memory, and writes them to the files on disk asynchronously. If you try to copy the files, this takes a non-zero amount of time, so you are likely to copy the files while they're being updated by MySQL Server. So you get the early part of the file before the update, and the latter part of the file after the update. The result is potentially an irreconcilable corrupt file.
A simple Wordpress blog is likely to have low traffic, so it's not like it's in a constant state of update. But the risk is still there.
You should work with your hosting provider to give you temporary ssh access. Or ask them to stop MySQL Server on your behalf.
And yes, make backups of your MySQL database regularly. I use jetpack.com to run automatic backups for me.


Let me try to explain why you should not do that, why you should never assume that an SQL-product will return a result set in a specific order, unless you specify so, whatever indices - clustered or non-clustered, B-trees or R-Trees or k-d-trees or fractal-trees or whatever other exotic indices a DBMS is using.

Your original query tells to the DBMS to search the SensorValues table, find rows that match the 3 conditions, order those rows by Date descending, keep only the first row from those and - finally -  select and return only the SensorValue column.

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC ;

These are very specific orders you have given to the DBMS and the result will most probably be the same every time you run the query (there is a chance it might not, if you have more than one row that match the conditions and have the same max Date but different SensorValue but lets assume for the rest of the conversation that no such rows exist in your table).

Does the DBMS have to do this, to run this query, the exact way I describe it above? No, of course not and you know that. It may not read the table but read from an index. Or it may use two indexes if it thinks it's better (faster). Or three. Or it may use a cached result (not SQL Server but other DBMS cache query results). Or it may use parallel execution one time and not the next time it runs. Or ... (add any other feature that affects execution and execution plans).

What is guaranteed though is that it will return the exact same result, every time you run it - as long as no rows are inserted, deleted or updated.

Now lets see what your suggestion says:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010 ;

This query tells to the DBMS to search the SensorValues table, find rows that match the 3 conditions, order those rows by Date descending,, don't care about the order, keep only one row and - finally -  select and return only the SensorValue column.

So, it basically tells the same as the first one, except that it tells that you want one result only that matches the conditions and you don't care which one.

Now, can we assume that it will give always the same result because of the clustered index?
- If it does use this clustered index every time, yes.

But will it use it?
- No.

Why not?
- Beacuse it can. The query optimizer is free to choose a path of execution every time it runs a statement. Whatever path it sees fit at that time for that statement.

But isn't using the clustered index the best/fastest way to get results?
- No, not always. It might be the first time you run the query. The second time, it may use a cached result (if the DBMS has such a feature, not SQL Server*). The 1000th time the result may have been removed from the cache and another result may exist there. Say, you had executed this query just before:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date ASC ;         --- Notice the `ASC` here

and the cached result (from the above query) is another, different one that still matches your conditions but is not the first in your (wanted) ordering. And you have told the DBMS not to care about the order.

OK, so only cache can affect this?
- No, many other things, too.

other indexes were considered, at that time by the DBMS as better for this query.
a developer changed or completely removed this clustered index you had.
you or some other developer added another index that the optimizer decided it's more efficient to use than the CI.
you updated to a new version and the new optimizer has a minor bug or a change in how it ranks and chooses execution plans.
statistics were updated.
parallel execution was chosen instead.

*: SQL Server does not cache query results but the Enterprise Edition does have an Advanced Scanning feature which is kind of similar in that you may get different results because of concurrent queries. Not sure exactly when this kicks in though. (thnx @Martin Smith for the tip.)

I hope you are convinced that you should never rely that an SQL query will return results in a specific order, unless you specify so. And never use TOP (n) without ORDER BY, unless of course you just want n rows in the result and you don't care which ones are returned.


Having primary key in both fields but repeated in either fields allowed repeating

You can achieve the same goal by having a UNIQUE composite index on the desired fields.

ALTER TABLE YourTable
  add CONSTRAINT YourTable_unique UNIQUE (A, B,....);

(A, B,...) - fields A, B,... should be declared as NOT NULL.

This might be helpful if you have a surrogate PRIMARY KEY which you may want to keep. Something similar was recently asked for MySQL here, but the principle is the same.

You can also perform the same operation during the CREATE TABLE statement:

CREATE TABLE table_name
(
  column1 datatype NOT NULL,
  column2 datatype NOT NULL,
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);


MYSQL Get column group with different values on other column

SELECT week, GROUP_CONCAT(DISTINCT status)
    FROM tbl
    GROUP BY week
    HAVING COUNT(DISTINCT status) > 1;


pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008

I've found it !

In Windows Service, i found that it was "adminpostgres" who was running the pgAgent service.

I just had to edit the pgpass.conf in c:/user/adminpostgres/AppData/postgresql with my old config to make it works !


Like @a_horse_with_no_name told on his comment, the differences are documented in here, but here is some information:

Size:

datetime - uses 8 bytes for each field

timestamp - uses 4 bytes for each field (half of the size)

Range:

datetime - 1000-01-01 00:00:00 to 9999-12-31 23:59:59

timestamp - 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC

Timezone:

as @ypercube mention, timestamp converts your data to utc and store it, and when you retrieve it, it converts from utc to your timezone connection.

Concept:

datetime - Is a calendar date(same point in time can be different depends on timezone).

timestamp - Is a point in time, does not matter the timezone your are.

Suggestion:

The 2 main differences are range and size, then think, do you really need dates bigger then 2038-01-19 03:14:07 at the moment(at the moment, no in the future!)?
If no, go with timestamp for now, when you reach a point where you need a date range outside timestamp range, convert it to datetime.


Error: "temporary file size exceeds temp_file_limit"

You are joining around 40 tables to retrieve around 200 columns. I did not dig in but, typically, such a query is a misunderstanding. Either way, no wonder you amass a lot of temporary files.

Also note that all temp files of the same session count against the limit. Bound cursors can become a problem that way. Or multiple large queries in the same transaction.

By default the parameter temp_file_limit is set to -1, which means "no limit". So either sanitize your query or remove or extend the limit. (Only superusers can do that.)

If you cannot change either, setting more work_mem might help to keep more operations in RAM, so less temp space is needed.

Don't confuse this setting with temp_buffers, which is mostly unrelated and sets the amount of RAM that can be used for temporary objects (like in CREATE TEMP TABLE ...)

And you did not bother to mention your version of Postgres. Maybe outdated?


Assigning a domain user to run MSSQLSERVER service 2008R2

If you check the event log, does the event say something about needing "log on as service" rights?

In my experience you will need to add 'log on as service' rights to the account you select. Although the SQL Server Configuration Manager is supposed to handle this, I've found that it does not always. Try the following:

Log on to your domain controller and load the group policy management console (depending on your AD version)
add 'log on as a service' to the account you created for the SQL Server service in an existing or new group policy object. (maybe ask a sysadmin to assign this right for you)
Load SQL Server configuration manager and set the service account to the account you created


SP2-0734: unknown command beginning "sys_contex..." - rest of line ignored

Use dual table part of select
SQL> SELECT sys_context('userenv', 'session_user') FROM dual;

Documentation:
SYS_CONTEXT


In the readme of the first responder kit there is a statement on Azure SQL DB

Azure SQL DB - It's a dice roll. Microsoft changes DMV contents in
here without warning, so no guarantees.

There is another explanation by Scott Hodgin and a possible workaround by Henrik Staun Poulsen in this question.
Per Henrik's words

I've tried to make a version of sp_blitz that works on Azure SQL DB.
See here  Now, I'm looking for people that will test it.

You could test his version of sp_blitz and give him some feedback.


Note: In order to expose the advantages of handling each discrete data point via its dedicated (logical-level) column, this answer is based exclusively on the following excerpt:

  …someone is assigned to work on this asset of this type. I was thinking of storing it like assetType.assetID so asset type 5 and id 99 would be decimal 5.99…

In order to analyze the scenario in full (every entity type, property and association of relevance), a description of all the informational requirements of the particular business domain you are working on is indispensable; in other words, one has to know the applicable business rules, which at the time of this edit, have not yet been provided.

If you want to supply all the information required, with all the relevant details involved, just let me know and I would be glad to share my take on the scenario as a whole.

You clarified via comments that your intention is building a relational database, so retaining two distinct pieces of information in the same column would imply multiple downsides, e.g.:

The logical level of abstraction of the database under consideration would not be representing the corresponding conceptual schema with the required precision.
Logical-level constraints (value validation) would be much harder (or impossible) to set up declaratively (one has to resort to archaic, sub-optimal, procedural methods to protect data integrity).
Data manipulation at the logical level becomes needlessly cumbersome.
The work that the database management system has to perform at the physical level in order to optimize or even support the logical operations becomes superfluously more complex.
Database usage, in general, becomes unnecessarily more difficult since the database structure and constraints would be puzzling for DBAs, application programmers and, more importantly, end users (in certain environments, there are power users who access databases without application program assistance).

The Asset example

Let us use the scenario about the Asset entity type as an example. So, starting the analysis from the conceptual level, one can say that:

An Asset is primarily distinguished by its Id
An Asset is alternatively distinguished by exactly-one Name
An Asset is classified by exactly-one Type.
A Type classifies zero-one-or-more Assets

Therefore, the informational requirement of that particular business domain entails keeping track of two distinct data points, i.e., Asset.Id and Asset.Type, each of which carries an exact meaning; hence, at this stage it is very clear that each of them:

entails a specific set of constraints; and
has the potential of undergoing manipulation individually.

Subsequently one can represent said conceptual elements by way of the following logical-level SQL-DDL layout:

CREATE TABLE asset_type (
    asset_type_id INT      NOT NULL,
    name          CHAR(30) NOT NULL,
    --
    CONSTRAINT asset_type_PK PRIMARY KEY (asset_type_id),
    CONSTRAINT asset_type_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE asset (
    asset_id      INT      NOT NULL,
    name          CHAR(30) NOT NULL,
    asset_type_id INT      NOT NULL,
    --
    CONSTRAINT asset_PK PRIMARY KEY               (asset_id),
    CONSTRAINT asset_AK UNIQUE                    (name), -- ALTERNATE KEY.
    CONSTRAINT asset_to_asset_type_FK FOREIGN KEY (asset_type_id)
        REFERENCES asset_type (asset_type_id)
);

As demonstrated, each datum would be kept in its dedicated column, and with this arrangement:

One can constrain asset.asset_id declaratively as the PRIMARY KEY of the asset table, which prevents the possibility of accepting duplicated values in such a column. The database management system programmers have worked on optimizing the related physical-level processes carried out “under the hood” for years.
Being declared as a PRIMARY KEY, the asset.asset_id column can now be “used as a target” from one ore more FOREIGN KEY constraints, in case it is necessary.
It is very easy to guarantee referential integrity by way of declaring a FOREIGN KEY constraint on the asset.asset_type_id column, making a reference to asset_type.asset_type_id.
You do not have to resort to functions accessing column value sub-parts (as in the case of the non-atomic combined column) in data manipulation operations, be it INSERTs, SELECTs, UPDATEs, DELETEs or combinations thereof. Functions would (1) increase processing time at the physical level and (2) would reduce code readability.
The (a) logical layout reflects the (b) conceptual schema with precision, which aids all the interested parties when interpreting data structure and result sets. Writing the queries themselves is much simpler because each column stands for a clear and individual piece of information.
At the physical level, having a separate asset.asset_type_id column permits fixing one (or more) single- or multi-column index(es) for this table, which can speed up execution of logical queries that, e.g., include the column as a condition in the WHERE clause.

Thus, in view of all of the above, my main point is: Each data point of interest should be represented at the logical level by its corresponding —properly typed and properly constrained— individual column.

I recommend re-analyzing thoroughly the scenario on your own, starting at (i) the conceptual level and then proceeding with (ii) the corresponding logical design, since that method facilitates obtaining an exact mapping and you will be able to avoid uncalled-for hacks straightforwardly.

Audit trail

You mention in the question that you are interested in enabling some kind of audit trail for the database at issue so, in light of that, I would suggest following an approach like the one I expose in this answer and in this other answer (both about temporal capabilities) with the pertinent adaptations, of course.


Out-of-Range and Overflow Handling for float values

In my view, you're on the right track and validating it in C# before sending it to the database is the better way, so you're able to show the changes before a submit.

A regular expression should indeed be able to solve your validating issue, but depending on how you allow the number to be entered plays a part in how complex it should be.
Say do you allow thousand-seperator, decimal points, negative numbers, the number zero and so on.
And such cases always make Regular Expression a daunting task if not rather familiar with the syntax.

So I'd say that in your situation, the very easiest way would be to simply check whether the entered number is indeed a number and then cast it to a decimal and simply check whether it's value is between -1000000.000 and 1000000.000 (not inclusive).
Decimal.TryParse and an if-statement or two would be all that's needed for this.


Building A SQL table horizontally VS vertically

The extreme example of the vertical design pattern is called entity-attribute-value. Generally speaking, you don't want to use the EAV design pattern with SQL Server due to challenges with indexing and constant joins.

To find the right middle ground, review Joe Celko's post on normalization in SQL Server Central's Stairways series. Note the sections on 3rd normal form.