Migrating CLR Assemblies I am migrating a SQL Server 2008R2 database to a new server. (also 2008R2)

There are a number of CLR assemblies on the server. Will these automatically migrate with the database or will I have to manually script them?

Thanks!

How are you migrating the database? Copying a database to another server via Backup/Restore or Detach/Attach will include the Assemblies as well as the T-SQL wrapper objects that point to the code in the Assemblies.  Using a tool that lets you select object types might require that you at least verify that Assemblies have been selected to migrate.

If all of your Assemblies have a PERMISSION_SET of SAFE then you should be fine with no additional steps (outside of the obvious enabling of CLR Integration as mentioned in another answer).

If any of the Assemblies have a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE then you will need to do a few extra steps:

If you are using the non-preferred but easier and hence more common method of setting the database property of TRUSTWORTHY to ON, then you will need to ensure that it is set to ON on the new server. I would expect the Backup/Restore method to carry this setting over correctly, but other methods might not, especially if they create the database new on the destination.
If you are using the preferred method of having an Asymmetric Key-based Login, then those pieces live in the [master] database and you need to make sure they are copied or recreated on the destination:

Make sure that all Asymmetric Keys (that are used for CLR security) exist in [master] on the destination
Make sure that all Logins based on those Asymmetric Keys exist in [master] on the destination
Make sure that those Logins have the same server-level permissions on the destination: GRANT EXTERNAL ACCESS ASSEMBLY and/or GRANT UNSAFE ASSEMBLY

Regardless of which of the two options noted above you are using, make sure that the database owner SID matches between what is shown in the database properties and what is recorded in the [master] database meta-data. This is usually not an issue if creating the destination DB and then copying objects into it, but if using Backup/Restore then there can be a mismatch in DB owner SIDs, and that will prevent CLR code from running
I am less certain of this one as I haven't encountered it in a few years, but if the DB owner SID does change, then for any Assemblies that have a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE, you might need to at least reset the PERMISSION_SET property to SAFE and then back to what it was originally. If that doesn't help then you might need to DROP and reCREATE the Assemblies and the associated T-SQL wrapper objects.

It would also be a good idea to make sure that any of the pre-4.0 versions of the .Net framework that exist on the current / source server also exist on the destination server. It won't matter if additional framework versions exist on the destination, but it will make the transition smoother if there is at least nothing missing. Even though SQL Server 2005 - 2008 R2 is statically linked to the .Net 2.0 series (2012 and 2014 are statically linked to the 4.0 series) it is possible that an Assembly can require 3.0 or 3.5. If that happens and those framework versions have not been loaded then you will get a cryptic message that will not imply that a .Net framework version is missing. But any version that is not on the source server is clearly not needed if the CLR objects currently function.


What is SQL Server "Denali"? What's new? I found a new title called SQL Server "Denali" in the drop down list on MSDN, but I didn't find much information about it:

What's New (this documentation is for preview only as I see)
Top 9 New Features of SQL Server "Denali"

Does anyone have more detailed information about new features or significant bug fixes in this release? I'm hoping someone has used or tested it.

New Features

Sequences
Extended FILESTREAM (thanks to Eric Humphrey)
OFFSET & FETCH (ORDER BY Clause)
Memory Manager Changes
LAG(..) and LEAD(..) .. OVER (Partition By..Order By) clause (thanks to gbn)
Aaron's list

The new features include :

Multi-Subnet Failover Clustering
Programming Enhancements including sequences, ad-hoc query paging and full-text search tweaks
BI and Web Development Environment Improvements
Web-based Visualization
Data Quality Services enhanced

You can view the detailed review here :  New Features Of Denali

"Denali" is a code name. Here is the list of the code name of other versions of SQL Server:

1993 – SQL Server 4.21 for Windows NT
1995 – SQL Server 6.0, codenamed SQL95
1996 – SQL Server 6.5, codenamed Hydra
1999 – SQL Server 7.0, codenamed Sphinx
1999 – SQL Server 7.0 OLAP, codenamed Plato
2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
2003 – SQL Server 2000 64-bit, codenamed Liberty
2005 – SQL Server 2005, codenamed Yukon (version 9.0)
2008 – SQL Server 2008, codenamed Katmai (version 10.0)
2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
2011 – SQL Server 2012, Codenamed Denali


How to benchmark disk performance from within SQL Server? I know of tools such as SQLIO and diskspd to benchmark IO/disk performance, but need something that can be run from the SQL Server instance itself via T-SQL, as I'm working on a Cloud-hosted environment (AWS RDS) and do not have access to the server itself.

There is this article on using sys.dm_io_virtual_file_stats to monitor file IO but I need something that can be run to generate some IO load on demand on multiple servers and generate some kind of report I can use to compare.

Heavy analytical SQL queries with identical execution plans are taking x2 (or more) the time in different servers. I'd like to run this benchmark in order to try to to make sure that's the best this instance type can do in terms of I/O.

To get a general sense of I/O speed on the disks, you can perform any of these disk-intensive maintenance tasks.  Note that these won't really help if you're trying to test for a specific workload, but if you're purely interested in disk I/O driven by T-SQL commands only, this is a good "easy" solution.

Create or Rebuild Indexes

This will give you both reads and writes, as tables will be scanned, and then written to new indexes in the specified index order.  This is probably most helpful if you can do it on tables with lots of rows, or big data types.

DBCC CHECKDB

Running DBCC CHECKDB on a database will generate a large amount of reads, as it's checking for corruption throughout all of the indexes and tables in the database.  Since you're on 2008 R2, which doesn't have parallel CHECKDB, the utility of this option might be limited.

Database backups

If you weren't on RDS (which restricts backups to be performed to S3 through a wrapper stored procedure), running a native SQL Server backup would be a good option to push read and write I/O.

You may still be able to test read I/O by backing up to the NUL device.  See BACKUP (Transact-SQL), and especially:

  The NUL device can be used to test the performance of backups, but should not be used in production environments.

The general approach with any of these solutions would be to get a before and after from sys.dm_io_virtual_file_stats.  There is an example of how to do that in the Paul Randal blog post you linked to in the question.


Best way to store country and city information I always wondered what is more efficient between two. There is one "main" table where I store all user related informations. Also, there are two more tables, country and city tables. city table contains column country_id so I can relate to which country this city belongs.

Now, the main question : should I store only city_id in user table and query for country name using country_id from city table or to store both, city_id and country_id ?

======

Other thing is related to counting. If have some fourth table, like message, and I store ID of user who send message, ID of user who receive message, text, etc. Now, if I want to know how many messages some user sent, I can query for messages where sender=ID and count rows, but I used another approach. I created column count in user table and whenever user send message, I increase this number. Is this approach good and if no, is the first one efficient since that table can have billions of rows.

Countries are relatively stable so it is easy to have a countries table.  City names can be duplicated multiple times within a country.   Maintaining the city table may be difficult for a couple of reasons.

Some cities have different spellings in different languages.
Some countries have multiple cities with the same name.  These are likely in different states or provinces.

You may want to allow free format for the city and keep it in the user table.  Otherwise, investigate adding a state/province table.  You will still need to deal with how to add new cities as needed.

If you index the sender, the count can be done on the index.  This can be extremely efficient for smaller counts.  Updating the user table for each message will increase the log data volume, and increase the cost of saving each message.  It is also error prone, and can lead to deadlocks if you are not consistent in the order the accesses are done.  De-normalizing the model should be done only after determining you have a performance problem.

A query like SELECT COUNT(1) FROM message WHERE sender=?ID is likely better than maintaining a count.


Converting and removing full text files I have a database that was created many SQL Server versions ago that has Full Text files. It is currently on a SQL 2012 server. How can I confirm that there is no data in use in the Full Text index files so I can delete them?

To see if the index is in use you can use sys.dm_db_index_usage_stats. Here is an example query with some description.

T-SQL Script to Correct SQL Server Full Text Indexes Not Updating by Pablo Echeverria

SELECT [t].[name] [table_name],
       [i].[name] [index_name],
       [fi].[change_tracking_state_desc],
       [fi].[has_crawl_completed],
       [fi].[crawl_type_desc],
       [fi].[crawl_end_date],
       [ius].[last_user_update],
       [ius].[last_user_seek],
       (SELECT [name]+',' FROM [sys].[fulltext_index_columns] [fc] INNER JOIN [sys].[columns] [c] ON [c].[object_id] = [fc].[object_id] AND [c].[column_id] = [fc].[column_id] WHERE [fc].[object_id] = [fi].[object_id] FOR XML PATH('')) [columns],
       (CASE WHEN [fi].[crawl_end_date] < ISNULL([ius].[last_user_update], [ius].[last_user_seek]) THEN 'ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING MANUAL; ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING AUTO' ELSE '' END) [Command]
FROM [sys].[fulltext_indexes] [fi]
INNER JOIN [sys].[indexes] [i] ON [i].[index_id] = [fi].[unique_index_id] AND [i].[object_id] = [fi].[object_id]
INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [fi].[object_id]
LEFT JOIN [sys].[dm_db_index_usage_stats] [ius] ON [ius].[index_id] = [fi].[unique_index_id] AND [ius].[object_id] = [fi].[object_id] AND [ius].[database_id] = DB_ID()
ORDER BY [table_name], [index_name]

This article refers to more system objects regarding Full-Text Search. You can use this to find more details especially about population status.


Sqlplus parameterized query via file I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file.  Is there a way to do this?  The query has more parameters than is reasonable for a person to be prompted for and we would like to eliminate user error when entering the parameter for this.

The reason we need to issue a parameterized query is that we are seeing a huge difference in performance between a straight sql statement and the same query issued as a parameterized query in our application.  We would like to remove any application code from the chain and have the difference evident with only Oracle tools.

If you want the SQL in one file and the parameters in another, this is an option. Have query.sql assigning positional parameters to bind variables and then executing a query:

variable p_owner varchar2(30);
variable p_column varchar2(30);

set verify off
set feedback off

begin
    :p_owner := '&1';
    :P_column := '&2';
end;
/

set feedback on

select table_name
from all_tab_columns
where owner = :p_owner
and column_name = :p_column;

exit 0;

And put the parameter values in a text file, say parms.txt:

SYS
OSUSER

And put it together with:

sqlplus -s user/password @query `cat parms.txt`

(This is for Unix, obviously; not sure off-hand what the command-line equivalent is for Windows). Change parms.txt, or use different files, and you'll still get the same hard-parsed plan each time.

Your performance problem sounds like it might possibly be related to bind variable peeking, particularly the 'stuck with a bad plan' description. The execution plan is determined by the first query run, which might choose suitable indexes etc. for those parameters; subsequent runs then use the same plan even if different indexes might be more suitable for the new parameters. The stand-alone straight SQL will have a separate parse and thus possibly different (more suitable) plan. In the past I've had to gather stats manually to prevent histograms being used, which at least stabilises things.


Postgres Trigger function - Pg 9.1 There is a table on my database that stores a path for video files used by my website. Those files are stored in a SSD disk.

path character varying(255)
id serial

The path will always be like this:

/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)

Here are some examples of valid paths:

/files/client/743052/attachment/2123598/main
/files/client/743052/attachment/2123598/thumbnail
/files/client/1475296/user/3541234/main
/files/client/1475296/user/3541234/thumbnail

Question:
How can I create a Trigger that checks the value WHEN INSERTED and, if the row is less than the minimal path (/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)) then raise an exception?

I'm using PostgreSQL 9.1.

UPDATE:

Some more examples here:

/files/client/45345/user/3542341234/main -- PASS
/files/client/45345/user/3542341234/thumbnail -- PASS
/files/client/253623/attachment/35334/main -- PASS
/files/client/253623/attachment/35334/thumbnail -- PASS
/files/client/45312341245/users/12545/main -- WRONG!
/files/client/45312341245/users/12545/thumbnail -- WRONG!
/files/client/45345/attachment/1223545/mains -- WRONG!
/files/client/45345/attachment/1223545/thumbnails -- WRONG!

You can simply use a check constraint however I would personally restructure my schema. Essentially what you seem to be doing is serializing a lot of data into a path, and then you want to be sure it's correct. That's kind of yuck, from my perspective.

CREATE TABLE foo (
  id    serial,
  path  text
    CHECK ( path ~ '/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)' )
);
INSERT INTO foo(path) VALUES
  ('/files/client/743052/attachment/2123598/main'),
  ('/files/client/743052/attachment/2123598/thumbnail'),
  ('/files/client/1475296/user/3541234/main'),
  ('/files/client/1475296/user/3541234/thumbnail');

INSERT INTO foo(path) VALUES ('/STUD/EVAN/beefcake_hotstuff.jpg');
ERROR:  new row for relation "foo" violates check constraint "foo_path_check"
DETAIL:  Failing row contains (6, /STUD/EVAN/beefcake_hotstuff.jpg).

I would probably go the opposite direction and normalize to some degree.

Normalizing

Normalizing would look something like this,

CREATE SCHEMA aws;

CREATE TABLE aws.client (
  client_id serial PRIMARY KEY
);
CREATE TABLE aws.attachment (
  client_id       int REFERENCES client,
  attachment_id   int PRIMARY KEY,
  name            text
);
CREATE TABLE aws.user (
  client_id       int REFERENCES client,
  user_id         int PRIMARY KEY,
  name            text
);

SELECT '/files/client' || client_id || '/attachment' || attachment_id || '/' || a.name
FROM aws.attachment AS a
JOIN aws.client AS c USING (client_id);


Difference between inline view and WITH clause? Inline views allow you to select from a subquery as if it were a different table:

SELECT
    *
FROM /* Selecting from a query instead of table */
    (
        SELECT
            c1
        FROM
            t1
        WHERE
            c1 > 0
    ) a
WHERE
    a.c1 < 50;

I've seen this referred to using different terms: inline views, WITH clause, CTE and derived tables. To me it seems they are different vendor specific syntax for the same thing.

Is this a wrong assumption? Are there any technical/performance differences between these?

There are some important differences between  inline views (derived tables) and WITH clause(CTE) in Oracle.  Some of them are quite universal, i.e. are applicable to other RDBMS.

WITH can be used to build recursive subqueries, inline view -not (as far as I know the same is for all RDBMS that support CTE)
Subquery in WITH clause is more likely be physically executed first ; in many cases, choosing between WITH and inline view makes optimizer to choose different execution plans (I guess it's vendor specific, maybe even version specific ).
Subquery in WITH can be materialized as a temporary table ( I'm not aware if any other vendor but Oracle supports this feature).
Subquery in WITH can be referenced multiple times , in other subqueries, and in the main query (true for most RDBMS).


Join and count boolean columns by logical OR There are two tables: user_table and user_schedule. Each user has four schedule rows (one for each 6 hour period per day) with one column for every workday.

The desired result is to count the number of users that are present for at least one period per day of the work week. For example, if a user is present two quarters of a day, that counts as one.

This is what I thought would work for a Monday, but it's incorrect:

SELECT
 count(CASE WHEN TRUE = ANY(ARRAY_AGG(mon)) THEN 1 ELSE null END) as mon,
FROM user_table
LEFT JOIN schedule_table ON user_table.id = schedule_table.user_id
GROUP BY user_id

I created a SQLFiddle to demonstrate.

EDIT:

I was looking for an summary table like this

+-----+-----+-----+-----+-----+
| Mon | Tue | Wed | Thu | Fri |
+-----+-----+-----+-----+-----+
|   4 |   3 |   4 |   4 |   4 |
+-----+-----+-----+-----+-----+

I am running PostgreSQL 9.3

Here are the table definitions I am using:

CREATE TABLE user_table
(
  id integer,
  start_dt date
);

CREATE TABLE schedule_table
(
  id integer,
  user_id integer,
  mon boolean,
  tue boolean,
  wed boolean,
  thu boolean,
  fri boolean
);

All users present on Monday:

SELECT u.*
FROM  (
   SELECT user_id
   FROM   schedule_table
   GROUP  BY 1
   HAVING bool_or(mon)
   ) s
JOIN   user_table u ON u.id = s.user_id;

The aggregate function bool_or() comes in handy.

Summary table:

SELECT count(mon OR NULL) AS mon
     , count(tue OR NULL) AS tue
     , count(wed OR NULL) AS wed
     , count(thu OR NULL) AS thu
     , count(fri OR NULL) AS fri
FROM  (
   SELECT user_id
        , bool_or(mon) AS mon
        , bool_or(tue) AS tue
        , bool_or(wed) AS wed
        , bool_or(thu) AS thu
        , bool_or(fri) AS fri
   FROM   schedule_table
   GROUP  BY 1
   ) s;

Result as desired:

mon tue wed thu fri
4   3   4   4   4

Note that I didn't join to user_table at all here. It's not needed.

SQL Fiddle.

About counting techniques:

For absolute performance, is SUM faster or COUNT?

However, your database design is odd, schedule_table has no information to signify the period of the day. And why bundle attributes for the same period on all days in one row?

Either, to store explicit information:

CREATE TABLE schedule (
  schedule_id int PRIMARY KEY
, schedule text NOT NULL
);

INSERT INTO schedule VALUES
  (11, 'mon, period 1')
  (12, 'mon, period 2')
  (13, 'mon, period 3')
  (14, 'mon, period 4')
  (21, 'tue, period 1')
  (22, 'tue, period 2')
  -- etc.
  ;

CREATE TABLE user_schedule (
  user_schedule_id integer,
  user_id int REFERENCES users,
  schedule_id int REFERENCES schedule
);

Only enter rows for periods with presence.
Or, to optimize storage:

CREATE TABLE users (
  user_id serial PRIMARY KEY
, start_dt date
, presence_bitmap bit(20)
);

INSERT INTO users (start_dt, presence_bitmap) VALUES
 ('2015-02-14', '11111111111111111111'),
 ('2015-02-14', '00000000000000001111'); -- etc.

Only needs a fraction of the storage on disk.


Does a re-index update statistics? I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is:

  Does a re-index update the statistics?

We found discussions online arguing both that it does and that it doesn't.

You can keep the following in mind when caring about updating statistics (copied from Rebuilding Indexes vs. Updating Statistics (Benjamin Nevarez)

By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

Rebuilding an index, for example by using ALTER INDEX … REBUILD will also update index statistics with the equivalent of using WITH FULLSCAN unless the table is partitioned, in which case the statistics are only sampled (applies to SQL Server 2012 and later).

Statistics that were manually created using CREATE STATISTICS are not updated by any ALTER INDEX ... REBUILD operation, including ALTER TABLE ... REBUILD.  ALTER TABLE ... REBUILD does update statistics for the clustered index, if one is defined on the table being rebuilt.

Reorganizing an index, for example using ALTER INDEX … REORGANIZE does not update any statistics.

The short answer is that you need to use UPDATE STATISTICS to update column statistics and that an index rebuild will update only index statistics.  You can force an update to all statistics on a table, including index-stats and manually created stats, with the UPDATE STATISTICS (tablename) WITH FULLSCAN; syntax.
The following code illustrates the rules encapsulated above:
First, we'll create a table with a couple of columns, and a clustered index:
USE tempdb;

IF OBJECT_ID(N'dbo.SomeTable', N'U') IS NOT NULL
DROP TABLE dbo.SomeTable;

CREATE TABLE dbo.SomeTable
(
    rn int NOT NULL IDENTITY(1,1)
        CONSTRAINT pk
        PRIMARY KEY NONCLUSTERED
    , i int NOT NULL INDEX i
    , d sysname NOT NULL
) ON [PRIMARY] WITH (DATA_COMPRESSION = NONE);

CREATE UNIQUE CLUSTERED INDEX cx ON dbo.SomeTable (i, d);

CREATE STATISTICS d ON dbo.SomeTable (d) WITH FULLSCAN;

INSERT INTO dbo.SomeTable (d, i)
SELECT c1.name, c1.id
FROM sys.syscolumns c1;

This query shows the date when each stats object was last updated:
SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

The results show no updates have yet taken place, which is correct since we just created the table:
╔═══════════════╦═══════════╦═══════════╗
║  ObjectName   ║ StatsName ║ StatsDate ║
╠═══════════════╬═══════════╬═══════════╣
║ dbo.SomeTable ║ cx        ║ NULL      ║
║ dbo.SomeTable ║ i         ║ NULL      ║
║ dbo.SomeTable ║ pk        ║ NULL      ║
║ dbo.SomeTable ║ d         ║ NULL      ║
╚═══════════════╩═══════════╩═══════════╝
Let's rebuild the entire table, and see if that updates stats:
ALTER TABLE dbo.SomeTable REBUILD;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.590 ║
║ dbo.SomeTable ║ i         ║ NULL                    ║
║ dbo.SomeTable ║ pk        ║ NULL                    ║
║ dbo.SomeTable ║ d         ║ NULL                    ║
╚═══════════════╩═══════════╩═════════════════════════╝

The results show only the clustered index stats were updated.
Next, we perform a discrete UPDATE STATS operation:
UPDATE STATISTICS dbo.SomeTable(d) WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

As you can see, we've just updated the stats on the d column:
╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.590 ║
║ dbo.SomeTable ║ i         ║ NULL                    ║
║ dbo.SomeTable ║ pk        ║ NULL                    ║
║ dbo.SomeTable ║ d         ║ 2018-09-17 14:09:13.597 ║
╚═══════════════╩═══════════╩═════════════════════════╝
Now, we'll update stats on the entire table:
UPDATE STATISTICS dbo.SomeTable WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

╔═══════════════╦═══════════╦═════════════════════════╗
║  ObjectName   ║ StatsName ║        StatsDate        ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.600 ║
║ dbo.SomeTable ║ i         ║ 2018-09-17 14:09:13.600 ║
║ dbo.SomeTable ║ pk        ║ 2018-09-17 14:09:13.603 ║
║ dbo.SomeTable ║ d         ║ 2018-09-17 14:09:13.607 ║
╚═══════════════╩═══════════╩═════════════════════════╝
As you can see, the only way to be certain all the stats are updated is to either update each one manually, or to update the entire table with UPDATE STATISTICS (table);.


MYSQL matching one column to another in a different table via a FULLTEXT index I current have two different tables.

The first table has a list of titles and IDs associated to these titles; the second table is a list of random heading.

What I would like to know is if there is a way to match up all the titles in table 2 to the closest matching title in table 1. Is this possible?

I've tried :

SELECT    title_table .*,
          random_titles.*,
          MATCH (title_table.title) AGAINST (random_titles.title) AS relevance
FROM      title_table
ORDER BY  `relevance` DESC

But that did not work.

I know I could use this query as each title is being put in table 2 with PHP but I already have a lot of titles in the database.

AGAINST() only accepts a string literal or a variable containing a string -- something the optimizer can deterministically resolve to a constant.

AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.
-- http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

This rules out any construct I can think of where an ordinary query could be used to join tables based on a fulltext index match.
...however...
You can wrap the fulltext query in a stored function:
DELIMITER $$

DROP FUNCTION IF EXISTS `best_fulltext_match` $$
CREATE FUNCTION `best_fulltext_match` (search_string TEXT) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN

  RETURN (SELECT id FROM title_table WHERE MATCH(title) AGAINST(search_string)
           ORDER BY MATCH(title) AGAINST (search_string) DESC LIMIT 1);

END $$

DELIMITER ;

Now...
SELECT best_fulltext_match('your title here');

...returns the id of the best match according to the fulltext index in title_table, and this function will accept a variable, no problem.
You could then use this to update your random_title_table.
UPDATE random_title
   SET title_id = best_fulltext_match(title);

The function is invoked once per row in random_title, and gives the fulltext search a static value to search with.
You could also use that same function when creating new entries in random_title.


Meaning of "update conflicts" in CollStats result db.collection.stats() returns
wiredTiger: {
    .....
    "transaction" : {
        "update conflicts" : NumberInt(1410)
    },
    ....
}

It's  MongoDB 3.6, but I don't use its new features related to client sessions and retryable writes.
Couldn't find much of documentation about meaning of the number apart from

Conflict between concurrent operations.
This error is generated when an operation cannot be completed due to a conflict with concurrent operations. The operation may be retried; if a transaction is in progress, it should be rolled back and the operation retried in a new transaction.

at http://source.wiredtiger.com/3.0.0/group__wt.html#ga5ee3c6fcd074e11efd118f3e68e91db8
Does it mean there were 1410 failed updates since the collection was created, or some of them were successfully "retried" implicitly?

The number records how many times two or more threads are trying to update the same document inside the WiredTiger storage engine. It is not persisted between server restarts.

This statistic is specific to the WiredTiger storage engine (as it is under the WiredTiger section). Under MongoDB this is known as write conflict, and will be transparently retried under valid conditions.

That is, the update is retried by MongoDB (using the WiredTiger's API) until it completes without a conflict. From the client perspective, the write succeeds and is otherwise normal except for increased latency.

Write conflicts indicates that multiple clients are trying to update exactly the same document within the same fraction of a second. This window is the time between the execution of the WiredTiger API's WT_SESSION::begin_transaction() and WT_SESSION::commit_transaction() operations. There is no fixed minimum time for these operations. It could be double or even single-digit microseconds if the amount of data to be written is small and the server has no queues for CPU or memory channels. There is no fixed maximum time either, although it is somewhat dependent on the performance of the server.

These conflicts can arise due to WiredTiger's optimistic concurrency control, which is described in concurrency FAQ for WiredTiger.

Note that comments in WiredTiger's source code might be misleading from MongoDB's point of view, since WiredTiger and MongoDB uses mostly the same term, but attach slightly different meanings to them.

For example, "transaction" in WiredTiger has always been available, but "transaction" in MongoDB means a multi-document ACID transaction feature for MongoDB 4.0 (not yet released as of this writing).


Efficient predictable ordering in PostgreSQL I have a large table of locations. I would like to efficient paginate though the table. I had previously being using an OFFSET approach however the size of the table made that approach unusable. So I am now trying a cursor approach using the location id.

In order to ensure consistent ordering for cases where a user has 2 rows with identical timestamp, I am also ordering by id.

SELECT  *
FROM locations
WHERE
  user_id = 1
ORDER BY timestamp desc, id
LIMIT 100;

However after adding id to the ORDER BY, the query has become slow. It is now doing a Seq Scan which takes ~20 seconds.

QUERY PLAN
Limit  (cost=502534.86..502535.11 rows=100 width=152) (actual
time=22822.113..22822.142 rows=100 loops=1)
  ->  Sort  (cost=502534.86..515512.80 rows=5191175 width=152) (actual time=22822.110..22822.133 rows=100 loops=1)
        Sort Key: ""timestamp"" DESC, id"
        Sort Method: top-N heapsort  Memory: 51kB
    ->  Seq Scan on locations  (cost=0.00..304131.89 rows=5191175 width=152) (actual time=1.603..21284.908 rows=5169237 loops=1)
          Filter: (user_id = 1)
          Rows Removed by Filter: 3048468
Planning time: 0.204 ms
Execution time: 22822.194 ms

Timestamp collisions are edge cases and id is a primary key. So why does the execution plan require a Seq Scan?

For context

SELECT indexdef
FROM pg_indexes
WHERE tablename = 'locations'

results

CREATE UNIQUE INDEX locations_pkey ON locations USING btree (id)
CREATE INDEX index_locations_on_user_id_and_timestamp ON locations USING btree (user_id, "timestamp")
CREATE INDEX index_locations_on_user_id_and_point ON locations USING gist (user_id, point)
CREATE INDEX index_locations_on_user_id ON locations USING btree (user_id)
CREATE INDEX index_locations_on_user_id_and_timestamp ON locations USING btree (user_id, "timestamp")
CREATE INDEX index_locations_on_user_id_and_timestamp_and_id ON locations USING btree (user_id, "timestamp", id)

Do you have INDEX(timestamp DESC, id ASC)?

Or, try ORDER BY timestamp DESC, id DESC .


What does 'nls' in nls_date_format stand for? For example:

alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss';

changes the date format for the session.

What is nls here?

National Language Support (because Americans have this weird idea of using MM-DD-YYYY as a date format, and different languages have different words for their months).


How to make a unique datetime without seconds? I need to build a table accepting one row for each minute. If I set the type of primary key to datetime, 60 records can be added for each minute.

Is there any trick to design a table to keep 0000-00-00 00:00 unique?


Table-based custom type does not verify check constraints Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table?

Assume the following table that serves as a source for the custom type:

CREATE TABLE source
(
  id character varying(20) NOT NULL,
  weight smallint NOT NULL,
  CONSTRAINT source_pkey PRIMARY KEY (id),
  CONSTRAINT source_weight_check CHECK (weight > 0 AND weight < 6)
)

Now, I intend to create the following data table:

CREATE TABLE final
(
  name character varying(20) NOT NULL,
  data source NOT NULL,
  CONSTRAINT final_pkey PRIMARY KEY (name),
)

When inserting records into the final table, the check constraint will not be observed.

Requirements:

I'd like to avoid creating my own type based of source, because when
source changes I'll be forced to remember to also update the custom
type.
I realize that table-based custom types obey domain check constraints. But, on my particular case, the weight field in the source database is ill-suited for a domain. Its check constraints varies from table to table. FYI, it's a value used in calculating weighted averages.

A record will only be null if all of its fields are null or if the record itself is null. If you want to check if any of the fields is null then check one by one:

create table final
(
    name character varying(20) not null,
    data source check (
        (data).id is not null and (data).weight is not null
    ),
    constraint final_pkey primary key (name)
);

insert into final (name, data) values ('a', null);
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, null).

insert into final (name, data) values ('a', (null, null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (,)).

insert into final (name, data) values ('a', ('1', null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (1,)).

insert into final (name, data) values ('a', ('1', 1));
INSERT 0 1


MySQL replication - invalidating query cache entries (table) We lately moved to MySQL V5.5 on CentOS release 6.7 with master and 2 slaves. Since then the replication process lags a lot behind the master with the line in the process list of:

  invalidating query cache entries (table).

The global query_cache_type & size were set to 0.

I have checked around and I can see it's a known old bug in MySQL but could not find a workaround for this issue.

Any advice?

query_cache_type OFF in my.cnf before server restart.
query_cache_wlock_invalidate set to OFF.
using default % storage_engine as InnoDB.

We ran some more tests and we had an issue with disk IO - sar -u showed about 3-4 %iowait, disks infra changes help reduce this to about 1.5-2% but it still doesn't help the replication not lagging for 5000(!) seconds behind the master.

Was query_cache_type OFF (0) in my.cnf, not merely turned off later?  (This seems to be critical for having it really off.)
Another possible common thread in the bugs -- MyISAM.  If you are using MyISAM, you really should convert to InnoDB.  There are many reasons for converting.  Here are some tips on doing the conversion:  http://mysql.rjweb.org/doc.php/myisam2innodb
Make sure that query_cache_wlock_invalidate is OFF.


PostgreSQL Initial Database Size There are 2 parts to my question.

Is there a way of specifying the initial size of a database in PostgreSQL?
If there isn't, how do you deal with fragmentation when the database grows over time?

I've recently migrated from MSSQL to Postgres, and one of the things we did in the MSSQL world when creating a database was to specify the initial size of the database and transaction log. This reduced fragmentation and increased performance, especially if the "normal" size of the database is known beforehand.

The performance of my database drops as the size grows. For example, the workload I'm putting it through normally takes 10 minutes. As the database grows, this time increases. Doing a VACUUM, VACUUM FULL and VACUUM FULL ANALYSE do not appear to solve the issue. What does solve the performance problem is stopping the database, de-fragmenting the drive and then doing a VACUUM FULL ANALYSE takes the performance of my test back to the original 10 minutes. This leads me to suspect that fragmentation is what's causing me pain.

I've not been able to find any reference to reserving tablespace/database space in Postgres. Either I'm using the wrong terminology and thus finding nothing, or there is a different way of mitigating filesystem fragmentation in Postgres.

Any pointers?

The Solution

The supplied answers helped confirm what I'd begun to suspect. PostgreSQL stores the database across multiple files and this is what allows the database to grow without worry of fragmentation. The default behaviour is to pack these files to the brim with table data, which is good for tables that rarely change but is bad for tables that a frequently updated.

PostgreSQL utilizes MVCC to provide concurrent access to table data. Under this scheme, each update creates a new version of the row that was updated (this could be via time stamp or version number, who knows?). The old data is not immediately deleted, but marked for deletion. The actual deletion occurs when a VACUUM operation is performed.

How does this relate to the fill factor? The table default fill factor of 100 fully packs the table pages, which in turn means that there is no space within the table page to hold updated rows, i.e. updated rows will be placed in a different table page from the original row. This is bad for performance, as my experience shows. As my summary tables get updated very frequently (up to 1500 rows/sec), I opted to set a fill factor of 20, i.e. 20% of the table will be for inserted row data and 80% for update data. While this may seem excessive, the large amount of space reserved for updated rows means that the updated rows stay within the same page as the original and there's a the table page isn't full by the time the autovacuum daemon runs to remove obsolete rows.

To "fix" my database, I did the following.

Set the fill factor of my summary tables to 20. You can do this at creation time by passing a parameter to CREATE TABLE, or after the fact via ALTER TABLE. I issued the following plpgsql command: ALTER TABLE "my_summary_table" SET (fillfactor = 20);
Issued a VACUUM FULL, as this writes a completely new version of the
table file and thus by implication writes a new table file with the
new fill factor.

Rerunning my tests, I see no performance degradation even when the database is as large as I need it to be with many millions of rows.

TL;DR - File fragmentation wasn't the cause, it was table space fragmentation. This is mitigated by tweaking the table's fill factor to suit your particular use case.

There is another thing in play that hasn't entered your equations yet: HOT update. Related answers:

PostgreSQL unexplained table bloat
Are regular VACUUM ANALYZE still recommended under 9.1?
What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables
How to speed up a slow UPDATE query

Setting FILLFACTOR to as low as 20 does seem excessive. It bloats the table to up to five times its size. If HOT updates work, you shouldn't have to go that low - normally.

There are exceptions: HOT updates can only reuse dead tuples from previous transactions, not from the same or concurrent ones. Therefore, heavy concurrent load or long transactions repeatedly updating the same rows can warrant such a low (or even lower) setting.

If you have big updates, changing large portions of the table at once, you might want to split them up in a couple of chunks, ideally only changing as many rows at once as fit locally on the data page. But that's hard to estimate and regulate.

Note that HOT updates only work when the changed columns are not involved in indexes in any way (neither as data nor as condition in a partial index). You might be blocking HOT updates with indexes on updated columns. If those are expendable, you might get better overall performance without them.

Finally, you can set autovacuum parameters per table. You could target heavily updated tables with aggressive settings allowing a somewhat tighter packing of rows than only FILLFACTOR 20.


Move Oracle to new drive I have a customer who currently runs Oracle on Windows (not my choice, I just have to deal with their current setup)
The basic layout of the server
C:\ - operating system and oracle application
D:\ - oracle datafiles
E:\ - oracle log files
The server recently died. The customer managed to recover the drives to new ones. But while D and E seem fine, C drive is constantly throwing "file system corrupt" errors, which is massively slowing down system. I believe they just took a ghost image of the drive, which means they just transferred the corrupted data along with it
I've suggested formatting C and reinstalling Windows, but their concern is about reinstalling Oracle. Mostly because they took over the company from the original owners and don't know the license details
So they've now asked, is it possible to "transfer" the currently installed Oracle application to another drive temporarily, format C and reinstall Windows and then transfer the Oracle app back to C again?

You have my sympathies.
This sounds like [yet another] amateur-hour set-up where the "Administrators" think they can treat an Oracle database server just like they do a file server full of Word documents.
The fact that their attempted "Recovery" was done at the disk level instead of reaching for their database backups tells me they that have very little idea what they're doing.
Ask them directly about their "database backups" and see how blankly they look at you.   Based on that, consider your "escape" options.
Moving forward:

Backup the database.  The Data matter, not which server they host it on.
Build a clean Windows server,
install Oracle database,
port the data across,
test everything!

their concern is about reinstalling Oracle ... took over the company ... don't know the license details.

"Danger, Will Robinson!"
Start pointing them, strenuously, in the direction of an Oracle Account Representative.
Running Oracle database without a support contract is bordering on sheer insanity.


Is it worth it to run VACUUM on a table that only receives INSERTs? In a 2015 re:Invent talk, AWS mentioned that vacuum should be run not only after updates or deletes but also after inserts. Here is the relevant part of the talk:

http://www.youtube.com/watch?v=tZXp19q8RFo&t=16m2s

Supposedly there is some cleanup that must be done on blocks even if they only received inserts and this cleanup can be done either the first time a block is selected (slowing down reads) or during vacuum. Is this true and if so, exactly what cleanup must be done?

tl;dr: The first process that reads data after it is committed will set hint bits. That will dirty the page, creating write activity. The other thing VACUUM (but not other commands) does is marks the page as all-visible, if appropriate. VACUUM will eventually have to hit the table to freeze the tuples.

The work that needs to be done after an insert isn't really cleanup, at least not in the sense of the other work VACUUM normally does. Before I go into the details, note that this answer is based on current (unreleased) 9.6 code and I'm ignoring the effects of streaming replication, even though it can have an affect on visibility.

Because of MVCC, every time Postgres evaluates whether a tuple should be visible to a query, it must consider whether the transaction that created the tuple (recorded in the xmin hidden field) committed, along with some other criteria. That check is expensive, so as soon as it is known that a transaction is visible to all currently open transactions a "hint bit" is set on the tuple header indicating that. The setting of that bit dirties the page, which means it will have to be written to disk. This can be very confusing if the next command to read the data is a SELECT that is suddenly creating a lot of write traffic. Running a VACUUM after the insert commits will avoid that. Another important distinction is that VACUUM will ALWAYS hint tuples on a page (as long as it got the cleanup lock on the page), but most other commands will only hint if the inserting transaction committed before the command started.

An important point about writing all these hint bits is that VACUUM can be throttled (and autovacuum is throttled by default). Other commands are not throttled and will generate dirty data as rapidly as possible.

VACUUM is the only method for marking pages as all-visible, which is an important performance consideration for some operations (notably, index only scans). If you do a large insert it's very likely that there are many pages with nothing but newly inserted tuples. VACUUM can potentially mark those pages as all-visible, but only if the oldest running transaction when VACUUM started was newer than the transaction that inserted the data.

Because of how MVCC works, tuples that were inserted more than ~2 billion transactions ago must be marked as "frozen". By default autovacuum will kick in to do that every 200M transactions. Running a manual vacuum with vacuum_freeze_min_age set to 0 after a bulk insert can help reduce the impact of that. More aggressively, you could run VACUUM FREEZE on the table after inserting. That would "reset the clock" on when the next freeze scan would happen.

If you want to know the specific details, take a look at the HEAPTUPLE_LIVE case after the call to HeapTupleSatisfiesVacuum() inside lazy_scan_heap(). See also HeapTupleSatisfiesVacuum() itself, and compare it to HeapTupleSatisfiesMVCC().

There are two other presentations of mine that might be interesting.  The first video is available from http://www.pgcon.org/2015/schedule/events/829.en.html, while the second one (which I think was a bit better) at https://www.youtube.com/watch?v=L8nErzxPJjQ


Can a replication subscription also be used as a publication? Some background:  We are working with a vendor who makes a read-only copy of their source database available to us in the cloud via filtered transactional replication.  The cloud database is a subscriber to the source database's publications.  The publishing database is multi-tenant, so only our customer data is replicated to our read-only copy.
The question is:  Could we could we publish from the subscriber/cloud database to a subscribing database in one of our own data centers?  If so, how might that work and would it be a management nightmare?

Could we could we publish from the subscriber/cloud database to a subscribing database in one of our own data centers?

Sure.  See Republish Data.  You would need to be the admins of the instance hosting the subscription database to make this all work.


Restore Database in SQL Server 2014 with different name I am using SQL Server 2014.
I have a database but as the user deleted some records, I need to create another database with a different name
and then transfer the data from the restored database to the current one.

I know I need to rename the restored database.

From my understanding, I also need to go to the Files page and then click on Relocate all files to folder.

In terms of renaming, do I need to rename both the Data file folder and the Log file folder?

Any assistance would help clarify

Perform a RESTORE FILELISTONLY first, so you understand the logical and physical names of the files.

RESTORE FILELISTONLY FROM DISK = 'c:\temp\foo.bak';

From there, you just need to fill in the pertinent pieces here:

RESTORE DATABASE foo_copy FROM DISK = 'c:\temp\foo.bak'
  WITH REPLACE, RECOVERY,
  MOVE N'foo_data' TO 'c:\...\foo_copy.mdf',
  MOVE N'foo_log' TO 'c:\...\foo_copy.ldf';

In case it isn't clear: don't use the UI for this. Pointing and clicking is not something you can easily reproduce, document, or store in source control. It can be a useful way to generate the commands in some cases (using the handy Script button), but in the long run you're much better off knowing the commands the UI would have generated anyway.


no matching unique or primary key for this column-list My parent table
Create table spare(
Sid int ,
Sname varchar(20),
Mid int,
primary key(Sid,Sname),
foreign key(Mid) references manu(Mid) on delete cascade

);

My child table
Create table Branch(
Bid int primary key,
Loc varchar(20),
Sid int,
Sname varchar(20),
foreign key(Sid) references spare(Sid),
foreign key(Sname) references spare(Sname)
);

I have made the references to the primary keys itseld but I'm still getting this error

you have a combined Primanry key.
So use
Create table Branch(
Bid int primary key,
Loc varchar(20),
Sid int,
Sname varchar(20),
foreign key(Sid,Sname) references spare(Sid,Sname)
);

Or you define in your spare table two new indexes for the column, so you can have different foreign keys


dividing quatity INT value into unique separate rows for each item I am working on a project where we are selling tickets. How do I create multiple rows from a quantity value. Currently it looks like this

line_item_id | order_id | product_id | quantity
------------------------------------------------
      1      |     1    |      6     |    5
------------------------------------------------

I wish for my end result to look like this into a separate table

ticket_id    | customer_id | product_id | quantity
------------------------------------------------
      1      |     2       |      6     |    1
------------------------------------------------
      2      |     2       |      6     |    1
------------------------------------------------
      3      |     2       |      6     |    1
------------------------------------------------
      4      |     2       |      6     |    1
------------------------------------------------
      5      |     2       |      6     |    1
------------------------------------------------

I am using a stored procedure but every time I call it it does what I want but it also duplicates the previous data into the table as well as adding the new tickets.

Also here is the stored procedure. Maybe I'm doing something wrong

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_item_import`()
BEGIN
    # Declare variables to read records from the cursor
    DECLARE parent_id_val INT(10) UNSIGNED;
    DECLARE item_type_val VARCHAR(255);
    DECLARE quantity_val INT(11);

    # Declare variables for cursor and loop control
    DECLARE no_more_rows BOOLEAN;
    DECLARE item_qty INT DEFAULT 0;

    # Declare the cursor
    DECLARE item_cur CURSOR FOR
        SELECT oi.MerchID, m.MerchName, oi.quantity FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            JOIN merchandise m ON m.MerchID = oi.MerchID
                WHERE o.customer_id = 2 AND
                MerchCatID = '7' OR MerchCatID = '8';

    # Declare handlers for exceptions
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

    # Open the cursor and loop through results
    OPEN item_cur;

    input_loop: LOOP

        FETCH item_cur
        INTO parent_id_val, item_type_val, quantity_val;

        # Break out of the loop if there were no records or all have been processed
        IF no_more_rows THEN
            CLOSE item_cur;
            LEAVE input_loop;
        END IF;

        SET item_qty = 0;

        qty_loop: LOOP

            INSERT INTO items_new
                (itemID, itemDesc)
            SELECT
                parent_id_val, item_type_val;

            SET item_qty = item_qty + 1;

            IF item_qty >= quantity_val THEN
                LEAVE qty_loop;
            END IF;

        END LOOP qty_loop;

    END LOOP input_loop;
END$$
DELIMITER ;

To prevent all existing order_items rows for being added to items_new, you have to exclude rows that have already been processed from the cursor.

There are several ways to do this; the best option depends on things we don't know, at this point.

You could pass order_id into the procedure, so you're only processing the order_items from a specific order each time through.
If there's a date attached to orders or order_items, you could store the procedure's last run date, and only process orders with a date between the last run date and the current run date.
If you have some control over the database structure, you could add a bit field to order_items to indicate whether it had been processed or not, and only process the unprocessed rows.
Similarly, if the items_new records are retained as long as order_items, you could add order_id and line_item_id to items_new, and only insert records if there are no records with those two values already in the table.

NOTE: Working off dates could create a race condition, where a new order has been assigned its date, but hasn't actually been saved when the procedure runs - in that case, it might possibly be missed, and never have the ticket numbers generated.

UPDATE: - If you modify your procedure to accept 1 argument, @proc_order_id, which would be the order_id of the order you want to create ticket numbers for, then your cursor would be:

# Declare the cursor
DECLARE item_cur CURSOR FOR
    SELECT oi.MerchID, m.MerchName, oi.quantity FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN merchandise m ON m.MerchID = oi.MerchID
            WHERE o.customer_id = 2 AND
            MerchCatID = '7' OR MerchCatID = '8' AND
            o.order_id = @proc_order_id;

On the other hand, if you add order_id and line_item_id to items_new, the cursor would be:

DECLARE item_cur CURSOR FOR
    SELECT oi.MerchID, m.MerchName, o.order_id, oi.line_item_id, oi.quantity FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN merchandise m ON m.MerchID = oi.MerchID
            WHERE o.customer_id = 2 AND
            MerchCatID = '7' OR MerchCatID = '8' AND
            NOT EXISTS (SELECT 1 FROM items_new
                         WHERE order_id = o.order_id
                           AND line_item_id = oi.line_item_id);


To use a cursor or something else in my while loop? From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select statement and pass a few columns from each row into another stored procedure for processing. The only way I see to do this is to create a cursor and run a while loop to use the cursor to grab each row so the data can be processed. So is using a cursor to step though each row from a select statement for processing the best way to do this or is there another way? In my case I have to make sure every single row is processed.

Ideally, you should change the other stored procedure to work on a set rather than take individual values (or create a new stored procedure that works on the set). If you show the actual logic in the procedure and the schema of the affected table(s), rather than just describe the high level process as a word problem, you can get some ideas about that. One way to avoid calling the procedure multiple times would be to pass the values for the different rows as a single set using a table-valued parameter (but again this requires re-writing the procedure or creating a new one).

In the meantime, if you absolutely must use a loop, you must also be willing to accept the performance consequences. One way to offset those - in cases where you really are using a cursor as a read-only, single pass through a set - is to use the following keywords when declaring your cursor:

DECLARE c CURSOR LOCAL FAST_FORWARD
FOR SELECT ...

More info here:

http://www.sqlperformance.com/2012/09/t-sql-queries/cursor-options


Does ELT data end up in an RDBMS? I think I am confusing things.

In ETL, data conversion starts at Database A and ends up in Database B, both are relational databases.  Maybe I have a 20-year-old system.  I use ETL to get the data between the two databases.  I do not know what else ETL is used for.

In ELT, is that the same except the data from Database A never ends up in Database B?  Instead, the raw data is sitting in tables or some other data structure on Server B, from Database A, but requires something like Hadoop to transform that data into something an application that is specifically designed to use Hadoop?

Edit:  I have done it again.  Hadoop != ELT.  I was looking only at Hadoop and thought it was ELT or the manifestation of it. edit: and that ELT meant you required a unique non-RDBMS file data structure instead of an RDBMS and you dumped the RDBMS altogether.

I think this will be easier with an example:
Database A has table C.  Database B has table D.  C and D are very similar but not identical and the data needs to be cleaned up before being loaded in to D.

ETL
An ETL program (SSIS, Pentaho, whatever) pulls the data from table C.  It then makes some changes to the data to clean it up and put it in the format that is needed.  The ETL process then moves/copies that data up to table D on Database B.

ELT
The data from Table C is extracted.  It is uploaded to Database B as table C.  Database B now has two tables, C and D.  A database process (SQL, a job, whatever) now makes some changes to the data in table C and puts it in the format that is needed.  That same process then copies the now clean data in to table D.  The temporary Table C is no longer needed in Database B and can be deleted or truncated.

In both cases the data is now loaded in Database B, Table D.  The front end application continues to use Table D.


How to directly query other user tables I am trying to learn Oracle database and I am having difficulties on importing a backup file with impdp command. I googled deeply but couldn't find answers to my problems. What have I done until now is:

I created a user as Test1 with tablespace
I granted some privileges through sqlplus
I imported the backup file by using impdp command with options fromuser=Test2 touser=Test1
I imported the backup file by using impdp command with options fromuser=Test2 touser=Test1
I started sqldeveloper and connected to Test1
I expanded tables but there is no table to be displayed
I expanded other users. There is also another Test1 among other users
I checked tables of Test1 and the tables were all there

Now my questions:

Although I chose touser=Test1 option, why the tables are displayed in Test1 which is in other users group? I expected the tables to be in first users' table list
How can I import database directly to the user I created? I don't want to use other users because I have to use [schema_name].[table_name] in my selects which is a waste of time for me.
I can click on tables in other user table list and display the data but I cannot open a blank sql page to write some queries. Is there any way to do that?

EDIT:

The command I used when importing is:

impdp Test1/123 DIRECTORY=backup_dir DUMPFILE=Test.dmp FULL=Y fromuser=Test2 touser=Test1

The result is as follows:

Just in case somebody else needs, I am posting the solution I used. Instead of using fromuser and touser options, I used remap_schema option:

> impdp user1/password DIRECTORY=backup_dir DUMPFILE=Dump.dmp FULL=Y
> remap_schema=user2:user1

Also, if the tablespaces are different, they must be remapped, too:

> impdp user1/password DIRECTORY=backup_dir DUMPFILE=Dump.dmp FULL=Y
> remap_schema=user2:user1 remap_tablespace=table2:table1


How widespread is the adoption and usage of database snapshots? After reading up on database snapshots, it seems that they are useful as a backup and quick restore method for small  DDL and DML changes. Additionally they are useful for large scale dev/test environments.

My intended use will be in our test environment where we need to test DDL/DML scripts and want to revert to the previous state without requiring lengthy restore times. Are there other methods besides snapshots here (excluding VM solutions)? Has this been widely adopted and are there any issues to be aware of? I'm trying to get a sense of the community outside of MS documentation and the few blogs I have read. If your impression that the documentation available online is representative, that's a fine answer for me.

Thanks again, gurus!

Database snapshots are used in SQL Server 2005 or later every time you do a DBCC CHECKDB - so they are widely used, even if it is behind the scenes. Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support.

It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs. The only downsides, apart from no tools support, is to watch disk capacity where you put the snapshot file, and ensuring you don't have too many snapshot databases running on the server at the same time.


Database for a startup We are in the process of putting together a whole system for a new startup company. This project is meant to store thousands of requests and each one needs to be stored and displayed in real-time.

This data will then be analysed so as to gather insight on traffic patterns etc.

After conducting a good amount of research we thought that HBase would be a perfect fit due to its HDFS replication, MapReduce via Hadoop and fast read/write mechanism. However the issue is that it is recommended to have a couple of million rows when implementing Hbase for the first time.

Obviously being a startup we would not have such traffic in the beginning however the number of requests is expected to increase quite quickly over a short period of time.

Therefore what would you suggest, still going for HBase or starting with e.g. MySQL and change once we have the right amount of traffic? The issue with using MySQL or any other DBMS is the aspect of real-time whilst also having to shift the whole system over to HBase should we start running into issues of sharding etc.

NoSQL and RDBMS are totally different animals.  You should pick the one which suits your data and how you intend to record and access it.

Don't pick your style of database system on the grounds of performance.  Performance can be managed in a range.  If HBase isn't performant at your start up volumes, then maybe you'll just have to work harder to tune it, or throw more money at hardware, or just live with slightly lower performance until your volumes catch up to HBase's sweet spot.


Is my function nondeterministic one I have two functions: fn_Without_Param and fn_With_Param

 CREATE FUNCTION [dbo].[fn_Without_Param]
 (
 )
 ...
 INNER JOIN .. ON .. AND SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 )

and

/*
     I am requesting it so:
    declare @SubmitDate     datetime
    set @SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 )
    select * from [dbo].[fn_With_Param] (@SubmitDate)
*/

CREATE FUNCTION [dbo].[fn_With_Param]
(
    @SubmitDate datetime
)
...
INNER JOIN .. ON .. AND SubmitDate = @SubmitDate

In the first case I have nondeterministic (?) function (because of GETUTCDATE) and I have called the second one with the same input parameter (CONVERT( varchar(10), GETUTCDATE(), 101 ) - meens today date without hours, minutes, seconds, millisecond).
Is my function nondeterministic one? How to detect this, maybe sql server has some public mark. Why is the second function slower?

I'm not sure if there is a flag, but there is a reference available at http://msdn.microsoft.com/en-us/library/ms178091.aspx of which built in functions are deterministic.

GETDATE() and GETUTCDATE() are nondeterministic because if you call them multiple times with the same parameters, which in this case are none, you get different values. In other words, the return value is not determined by input.

As far as why the second is slower than the first, check for implicit conversion between SubmitDate = @SubmitDate. Are they both datetime?


Why is MySQL InnoDB table copied despite using ALGORITHM=INPLACE for ALTER TABLE I run an ALTER STATEMENT on an InnoDB table on a MySQL server version 5.7 that:

drop some foreign key constraints
drop some indexes (non unique)
add some columns (without index or foreign key constraint, but they are not added at the end of table, I use the AFTER clause)

I use the ALGORITHM=INPLACE option. But the table is still being copied, I see the temp file created in the database directory. According to the DDL operations documentation, all these operations support INPLACE modifications. Why is my table being copied anyway? I didn't get any error message.

From the documentation:

INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.

The table is being rebuilt because the order of the columns is changing. As you said in your question:

[The columns] are not added at the end of table, I use the AFTER clause

If you need to avoid table rebuilds as much as possible, the new columns will need to be appended to the table definition.


How to get full query text from performance_schema.threads? According to documentation:
Access to the threads table does not require a mutex
and has minimal impact on server  performance. The other
sources have negative performance consequences because they require a mutex.

So yes, I want to use threads table to examine running queries. The trouble is, the PROCESSLIST_INFO column does not contain the full query. It just contains a truncated version of the query. There is a flag viz performance_schema_max_sql_text_length which controls how many bytes are allocated for the SQL query. But it seems to affect the events_statements_history_long table. There is no change in the thread table output. There is also the events_statement_current table. But it is not clear if accessing that table affects performance or not.

Digging more into documentation:
The Performance Schema is intended to provide access to
useful information about server execution while having minimal
impact on server performance

It looks safe to use performance_schema.events_statement_current for monitoring currently running queries and events_statement_current.SQL_TEXT gives full query text as long as it fits performance_schema_max_sql_text_length bytes.


Cannot create perlplu function Running PostgreSQL 9.1.8 on Xubuntu 12.04, installed from the repos.

From the shell, I have executed:

createlang plperl db_name;
createlang plperlu db_name;

As the superuser running psql, I have executed:

GRANT ALL ON LANGUAGE plperl TO account_name;
GRANT ALL ON LANGUAGE plperlu TO account_name;

The pg_language table reveals:

select lanname,lanpltrusted from pg_language where lanname like 'plperl%';

"plperl";t
"plperlu";t

When I create the following function:

CREATE OR REPLACE FUNCTION get_hostname()
  RETURNS text AS
$BODY$
  use Sys::Hostname;
  return hostname;
$BODY$
  LANGUAGE plperlu IMMUTABLE
  COST 1;
ALTER FUNCTION get_hostname()
  OWNER TO account_name;

I receive the following error:

ERROR:  Unable to load Sys/Hostname.pm into plperl at line 2.
BEGIN failed--compilation aborted at line 2.
CONTEXT:  compilation of PL/Perl function "get_hostname"

Yet the following works:

CREATE OR REPLACE FUNCTION get_hostname()
  RETURNS text AS
$BODY$
  return '127.0.0.1';
$BODY$
  LANGUAGE plperlu IMMUTABLE
  COST 1;
ALTER FUNCTION get_hostname()
  OWNER TO account_name;

The following Perl script works as expected from the shell:

use Sys::Hostname;
print hostname;

I tried to run the function as an anonymous block:

DO $$
  use Sys::Hostname;
  print hostname;
$$ LANGUAGE plperlu;

This returned the same error as before, with this additional information:

ERROR: Unable to load Sys/Hostname.pm into plperl at line 3.
BEGIN failed--compilation aborted at line 3.
SQL state: 42601
Context: PL/Perl anonymous code block

From the documentation, error 42601 is a syntax error.

I see no syntax error.

Furthermore, the function loaded fine with the superuser account when I imported the database:

psql -d db_name -U account_name -W -f db-dump.sql > import.log 2> error.log

I tried to simplify the code to no avail:

CREATE OR REPLACE FUNCTION get_hostname() RETURNS text AS
$$
  use Sys::Hostname;
  return hostname;
$$
LANGUAGE plperlu;

Same error as before.

What I don't understand is why, according to the error message, PostgreSQL is trying to load the code into plperl instead of plperlu.

Any ideas?

Update #1

Note that the postgres user is a Superuser:

                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}

Due to plperlu being untrusted, the account requires superuser authorization:

DROP LANGUAGE plperl;
DROP LANGUAGE plperlu;
CREATE OR REPLACE TRUSTED LANGUAGE plperlu;
UPDATE pg_language set lanpltrusted = true where lanname='plperlu';
GRANT ALL ON LANGUAGE plperlu TO account_name WITH GRANT OPTION;
ALTER USER account_name WITH SUPERUSER;

I do not know if plperl and plperlu are allowed to be installed at the same time, so I dropped the former. The function can now be created.


How to UPDATE a SQLITE Column with an INNER JOIN on TWO FIELDS How can I translate this query to SQLite:

UPDATE Table_1
INNER JOIN Table_2 ON (Table_1.Field1 = Table_2.Field1)
AND(Table_1.Field2 = Table_2.Field2)
SET Table_1.Field3 = Table_2.Field3

This is what I have tried.

UPDATE Table_1
SET Field3 = (SELECT Field3
            FROM Table2
            WHERE (Field1 = Table_2.Field1)
            AND (Field2 = Table_2.Field2))
WHERE Field1 IN (SELECT Table_2.Field1
            FROM Table2
            WHERE (Field1 = Table_2.Field1)
            AND (Field2 = Table_2.Field2))
AND Field2 IN (SELECT Table_2.Field2
            FROM Table2
            WHERE (Field1 = Table_2.Field1)
            AND (Field2 = Table_2.Field2))

The query works but I get wrong results.

In order to implement the join on two columns simultaneously, you can use an EXISTS predicate:

UPDATE Table_1
SET Field3 = (SELECT Field3
              FROM Table_2
              WHERE (Table_1.Field1 = Table_2.Field1)
                AND (Table_1.Field2 = Table_2.Field2))
WHERE EXISTS (SELECT *
              FROM Table_2
              WHERE (Table_1.Field1 = Table_2.Field1)
                AND (Table_1.Field2 = Table_2.Field2));

If you are using version 3.15.0 or later, you can also take advantage of row value comparisons:

UPDATE Table_1
SET Field3 =
    (SELECT Field3
     FROM Table_2
     WHERE (Table_1.Field1, Table_1.Field2) = (Table_2.Field1, Table_2.Field2))
WHERE (Field1, Field2) IN (SELECT Field1, Field2 FROM Table_2);


Group by minute and two minute intervals I have a datetime column and I can easily run queries with a group by on my datetime column. However, I would like to run group queries for

1-minute intervals
2-minute intervals
5-minute intervals
1 hour intervals
etc.

How do I do this?

First, a table and some sample data to play with:

USE tempdb;
GO
CREATE TABLE dbo.SomeTable(dt DATETIME);
GO
SET NOCOUNT ON;
GO
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -22, GETDATE());
GO 45
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -19, GETDATE());
GO 32
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -17, GETDATE());
GO 21
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -12, GETDATE());
GO 16
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -5, GETDATE());
GO 55
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -2, GETDATE());
GO 26
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -1, GETDATE());
GO 71
INSERT dbo.SomeTable(dt) SELECT GETDATE();
GO 14

(I'd do this in sqlfiddle but I'm not sure it supports GO <int> to have many rows, and it chokes on INSERT > 8000 characters.)

Now a stored procedure:

CREATE PROCEDURE dbo.GetGroupedIntervals
    @MinuteInterval TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @IntervalCount INT, @StartDate SMALLDATETIME;

    SELECT
        @StartDate = DATEADD(MINUTE, -1, MIN(dt)),
        @IntervalCount = (DATEDIFF(MINUTE, MIN(dt), MAX(dt))
            + @MinuteInterval) / @MinuteInterval
          FROM dbo.SomeTable -- WHERE ...;

    ;WITH dates(s,e) AS
    (
        SELECT
            DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
            DATEADD(MINUTE, @MinuteInterval*(n.n), @StartDate)
        FROM
        (
          SELECT
            TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
            FROM sys.all_objects AS o CROSS JOIN sys.all_columns AS c
            ORDER BY o.[object_id]
        ) AS n(n)
    )
    SELECT StartDate = d.s, c = COUNT(s.dt)
    FROM dates AS d
    LEFT OUTER JOIN dbo.SomeTable AS s
        ON s.dt >= d.s AND s.dt < d.e
        -- AND any filter criteria for dbo.SomeTable?
    GROUP BY d.s
    ORDER BY d.s;
END
GO

And some sample usage:

EXEC dbo.GetGroupedIntervals @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 5;

For brevity, I'll show the results for the last call, but you can play with the others.

StartDate            c
-------------------  ----
2012-05-16 12:51:00  77
2012-05-16 12:56:00  21
2012-05-16 13:01:00  16
2012-05-16 13:06:00  55
2012-05-16 13:11:00  111

Some notes:

The join using s.dt is likely to perform better than any extraction methods using datepart if the datetime column in your base table has an index (or might in the future).
I assumed you wanted to show all the intervals within the range. If you don't want to show the intervals with 0 counts, just change the left outer join to an inner join.
I go down a minute in case the start date was rounded up when converted to SMALLDATETIME. For the 1 minute interval and possibly others, this can lead to 0 counts for the first interval. You can adjust how this rounding happens (e.g. you can use FLOOR() to make sure it always rounds down). All depends on how accurate you need to be.
I didn't include any WHERE clause but you may need to have those to filter. For example your query may want all the intervals for a given day. You may want to change the calculation of dates to produce all the intervals for the day, rather than all the intervals between the min and max time found in dbo.SomeTable on that day. The following variation takes care of this, by presenting the data for a single day starting from midnight and incrementing by @MinutInterval:

...

CREATE PROCEDURE dbo.GetGroupedIntervalsByDay
    @Date           DATE,
    @MinuteInterval TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @IntervalCount INT,
        @StartDate     SMALLDATETIME = @Date;

    SELECT
        @IntervalCount = 1440 / @MinuteInterval;

    ;WITH dates(s,e) AS
    (
        SELECT
            DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
            DATEADD(MINUTE, @MinuteInterval*(n.n),   @StartDate)
        FROM
        (
          SELECT
            TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
            FROM sys.all_columns AS o
            ORDER BY o.[object_id]
        ) AS n(n)
    )
    SELECT StartDate = d.s, c = COUNT(s.dt)
    FROM dates AS d
    LEFT OUTER JOIN dbo.SomeTable AS s
        ON s.dt >= d.s AND s.dt < d.e
        -- AND any filter criteria for dbo.SomeTable?
    GROUP BY d.s
    ORDER BY d.s;
END
GO

Sample calls:

EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 5;

Abridged results from the last call:

StartDate             c
-------------------   ----
2012-05-16 00:00:00   0
2012-05-16 00:05:00   0
2012-05-16 00:10:00   0
...
2012-05-16 12:40:00   0
2012-05-16 12:45:00   0
2012-05-16 12:50:00   45
2012-05-16 12:55:00   53
2012-05-16 13:00:00   16
2012-05-16 13:05:00   55
2012-05-16 13:10:00   111
2012-05-16 13:15:00   0
2012-05-16 13:20:00   0
...
2012-05-16 23:45:00   0
2012-05-16 23:50:00   0
2012-05-16 23:55:00   0

(Again if you don't want to include the intervals with no counts, change the left outer join to an inner join. You also might find some odd results if you choose an interval that doesn't fit nicely inside of 1440. I'll leave that case as an exercise to the reader.)


WHERE in based on multiple columns I am trying to achieve something like this in sql server 2012. But this syntax does not exists. What are the alternatives to it and why does sql server does not support such syntactic sugar?

Which means C1 match to D1, C2 match to D2, Cn match to Dn. When all of the columns match it only will filter out the result.

SELECT * FROM tableA
WHERE
   ([C1], [C2],..., [Cn]) IN (SELECT [D1], [D2],..., [Dn] FROM tableB)

Your query is standard SQL. Unfortunately it hasn't been implemented yet in SQL Server. You can express your support for this feature to be implemented: Microsoft Azure Feedback: Add support for ANSI standard row value constructors.

You can use the common method of transforming IN to an EXISTS subquery. Works with just one or more columns:

SELECT *
FROM tableA AS a
WHERE EXISTS
      ( SELECT 1
        FROM tableB AS b
        WHERE a.[C1] = b.[D1]
          AND a.[C2] = b.[D2]
          ---
          AND a.[Cn] = b.[Dn]
      ) ;


Different Results From sys.databases As Different Users We have a SQL Agent job that is configured to run as user "dbo".  This job gets a list of databases defined in sys.databases that match a naming scheme.  The job then performs some dynamic SQL to cleanup some old transaction tables.  The job starts against tempdb.

The agent job runs fine on my own development database (SQL Server 2008 R2 Standard Edition 10.50.2500.0).  The job correctly gets the list of databases.  However, on a customer system (SQL Server 2008 R2 Standard Edition 64-bit 10.50.1600.1), the job does not find any matching databases despite them existing.

On the customer system, a simple select * from sys.databases run against tempdb in a query window returns all 10 databases in the system.

Using the profiler, I saw SQL Agent call EXECUTE AS USER = N'dbo' WITH NO REVERT when running this job.  I duplicated the issue using the following SQL:

use tempdb

-- results: tempdb, <my SQL user>, dbo
select
    db_name() as [Current Database],
    suser_name() as [Current Context],
    user_name() as [Current User]

-- results: tempdb, 10 (which I expect)
select
    db_name() as [Current Database],
    count(*)
from sys.databases

execute as user = N'dbo'

-- results: tempdb, sa, dbo
select
    db_name() as [Current Database],
    suser_name() as [Current Context],
    user_name() as [Current User]

-- results: tempdb, 2 (?!?)
select
    db_name() as [Current Database],
    count(*)
from sys.databases

revert

If I change 'use tempdb' to 'use master', the results are the expected 10 databases for each query.

Why would sys.databases return different results when run as the user dbo from the tempdb database?  And why does it return all databases with use master as the dbo user?

More information - If I run this script in SQL Server Management Studio without including the revert at the bottom, my list of databases shrinks to the same two that this query  returns from sys.databases.

This appears to be a matter of access rights and the specific user in the database.  The first thing to understand is that sys.databases in any database is simply a view that exposes parts of the system table that lives in resourcedb.  Anytime you are querying that in any database, you're hitting the same table but through a different view.

The second thing to understand is each database has a dbo user.  When you use the execute as user = N'dbo', that means you're dbo in the context of that specific database.  In your specific example, the dbo user in master and the dbo user in tempdb are two different security principals.

What you're seeing is simply a matter of what each principal is allowed to see.  dbo users, being the owner login of each specific database, only have rights to see the following databases:

master (in order to look up control information)
tempdb (in order to create temporary objects when necessary)
The database they own

Note that the dbo user in master, since that database needs to see and supply control information for all databases, has rights to view all databases on the server.


MySQL - get latest record when date and time are two fields I have a DB in which date and time are two columns. I need to get the latest (by latest I mean the record with the highest time) row. This is used as a sub query to feed another query.

DB

id      date       time    currentOdometer    totalOdometer       vno
 1   2020-03-21    10:00      250                  3001          ABCD
 2   2020-03-21    09:59      249                  3000          ABCD
 3   2020-03-21    10:01      251                  3002          ABCD
 4   2020-03-21    10:02      252                  3003          ABCD

My requirement is that when I get a new record of vno ABCD, i need to find the latest record and manipulate the data.

For instance, when i get a new record at 10:03, i need to find the latest record, ie 10:02 and the value.

My query was

(
SELECT Y.currentOdometer+(3200-Y.totalOdometer)
FROM tbl Y
WHERE timeStamp(date,time) =
(
  SELECT MAX(timeStamp(X.date,X.time))
  FROM tbl X WHERE vno= 'ABCD') AND vno= 'ABCD'
)

This query is part of an INSERT statement like this

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno)
VALUES ('2020-03-21', '10:03',(SELECT Y.currentOdometer+(3200-Y.totalOdometer)
FROM tbl Y
WHERE timeStamp(date,time) =
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X
  WHERE vno= 'ABCD') AND vno= 'ABCD'),3003,'ABCD'
)

When I run the query in this manner, i get the error the Subquery returns more than one row.

The i re framed the query with LIMIT 1 in each of the select statemtn and then there is a deadlock on the

ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
ER_LOCK_DEADLOCK
undefined

For questions like this, CREATE TABLE statements together with INSERT statements for sample data is truly useful. Even better is a Fiddle or similar. Right now, all I can do is give you some ideas, but I won't be able to test it.

Looking at your query:

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno)
VALUES ('2020-03-21', '10:03',(SELECT Y.currentOdometer+(3200-Y.totalOdometer)
FROM tbl Y
WHERE timeStamp(date,time) =
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X
  WHERE vno= 'ABCD') AND IMEI = 'ABCD'),3003,'ABCD'
)

There are probably some syntax errors ( un-balanced ")" for the sub-select, or at least they are in the wrong place ), but you should not use the VALUES clause together with a sub-select, better to re-write like:

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno)
SELECT '2020-03-21', '10:03', Y.currentOdometer+(3200-Y.totalOdometer)
FROM tbl Y
WHERE timeStamp(date,time) =
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X
  WHERE vno= 'ABCD') AND IMEI = 'ABCD'),3003,'ABCD'
)

As a side-note, avoid using reserved words as identifiers (date, time, timestamp).

The WHERE clause also looks wrong, I assume 3003 and 'ABCD' are values for totalOdometer, vno. Is this what you mean?

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno)
SELECT '2020-03-21', '10:03', Y.currentOdometer+(3200-Y.totalOdometer)
      , 3003, 'ABCD'
FROM tbl Y
WHERE timeStamp(date,time) = (
  SELECT MAX(timeStamp(X.date,X.time))
  from tbl X
  WHERE vno= 'ABCD'
    AND IMEI = 'ABCD'
);


SQL Server command line usage? Is it possible? I've been trying to use the SQL Server Management Studio 2008's built in "Solution Explorer" to manage a project and after 2 weeks of struggling with it, I've decided I don't really care for it. It's "convenience" features are actually quite a hindrance.

Unfortunately, the place where I work is a MS shop, and is pretty stuck in their ways. I want to go back to the command line (I crave Vim!). I've installed a local subversion repository with a TortiseSVN front end, to replace the above mentioned "Solution Explorer" and have it running quite well. I have also installed Vim and while still working on customizing it, have it humming.

However, the one thing that I am yet to find a solution to is how to run commands to SQL Server via the command line. I'm used to MySQL/Postgres command line prompt since that is what I have used in the past and was using while going through my classes. However, I can't seem to find a way to execute my scripts via the command line in windows. I currently edit them in Vim, refresh the page in SSMS, and execute. I would prefer to not even touch SMSS unless needed.

I've seen posts suggesting sqlsharp, but when I went to the page, I get the feeling that isn't quite what I'm looking for.

--edit

I really wish that it was possible to have 2 answer check marks.  P. Campbell has the technical right answer, and I've tried it a few times (I keep getting an error when I try sqlcmd...  In researching that it's how named pipes is operating... Even though I have it set up and enabled, it doesn't seem to work.)

After trying that and not having any success, I tried the just copy and paste into Management studio method, but that was ridiculous... So I decided that maybe HardCode is correct too.

Have you considered SQLCMD?
Here's an article on Using the SQLCMD command line utility.
You can execute adhoc SQL statements, or run .sql scripts.

The following example runs the C:\MyScript.sql script from the command line and outputs the queries and results to a text file located at C:\MyResults.txt.
C:\Windows>sqlcmd -S NOTTINTRA3\SQL2005 -i C:\MyScript.sql -o c:\MyResults.txt -e


Update expiring Mongo cluster SSL certificate I have a multi-node mongo cluster using SSL requireSSL and the certificates are going to expire. Is there a zero-downtime method to update the certs? I know the easiest way would be to stop all nodes, swap out the certs, and restart mongo. Same for all clients. I have not found a way to execute it without a service interruption.

If you CA don't change, just cert expiration time, you should not have any problems to change cert one node at the time (rolling upgrade).

But...
Basically following this procedure, so you "downgrade" you nodes ssl level allowSSL and doing change of certificate one node at time. After that you can bring ssl level back to requireSSL after everybody have new certs.


Time-series window query for calculating rolling average across group by subquery is inaccurate due to missing rows I'm trying to generate a rolling average based on a generated time series joined with a subquery on each day of the time series. However, the subquery occasionally does not have a result for a given day, and hence it does not return a row for said day. This breaks the rolling average calculation since it's based on preceding/following rows. It still calculates, but the calculation is wrong since it's not counting "0" days since those rows aren't present.
SELECT
    d::date AS day,
    updates.login as login,
    avg(coalesce(updates.counts, 0)) over (order by date(d) rows between 3 preceding and 3 following) as rollingavg
from generate_series(date '${from}' - 3, date '${to}' + 3, interval '1' DAY) AS t(d)
LEFT JOIN (
    SELECT
        date(item.updated_at) AS day,
        u.login,
        count(date(item.updated_at)) AS counts
    FROM
        user_item as item
        inner join user u ON u.id = item.user_id
    WHERE
        item.updated_at >= (date '${from}' - 3)
        item.updated_at <= (date '${to}' + 3)
    GROUP by day, login
) updates ON updates.day = t.d

If I remove the login from the group by clause and modify the query to fit, it appears to work, but that's only because there is enough data that no day goes without at least one update. In the off chance there were a day without an update, this would experience the same problem.
My initial thought was to join a generated table that is essentially a cross join between the time series and every possible author, or possibly modify my from generate_series to represent such a table that includes a baseline '0' count for every user on every day, but something about that feels wrong.
Am I on the right path?
EDIT: Some simplified sample data to make it a bit more obvious what I'm getting at. This is focused purely on the subquery since that is where the problem begins:
User:

id
login

1
userA

2
userB

UserItem:

updated_at
user_id

'2020-01-01'
1

'2020-01-01'
2

'2020-01-02'
1

'2020-01-03'
2

The desired result is as follows:

date
user
count

'2020-01-01
userA
1

'2020-01-01
userB
1

'2020-01-02
userA
1

'2020-01-02
userB
0

'2020-01-03
userA
0

'2020-01-03
userB
1

But what I'm getting:

date
user
count

'2020-01-01
userA
1

'2020-01-01
userB
1

'2020-01-02
userA
1

'2020-01-03
userB
1

As Lennart mentioned, you'll need to CROSS JOIN your generated series with your users to create the required combinations of (Login,Date):
SELECT
  U.Login
 ,t.Date::date AS Day
 ,COUNT(UserItem.UserId) AS Count
 ,AVG(COUNT(UserItem.UserId)) OVER (PARTITION BY U.Login ORDER By t.Date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS RollingAvg
FROM
  "User" U
CROSS JOIN
  generate_series(date '2020-01-01' - 3, date '2020-01-01' + 3, interval '1' DAY) AS t(date)
LEFT JOIN
  UserItem UserItem
    ON UserItem.UserId = U.UserId
        AND UserItem.Updated_At = t.Date
GROUP BY
  U.Login
 ,t.Date
ORDER BY
  U.Login
 ,t.Date

You can restrict to only those users with activity in the date range by adding:
WHERE
  U.UserId IN
    (
      SELECT
        UserId
      FROM
        UserItem
      WHERE
        Updated_At >= date '2020-01-01' - 3
        AND Updated_At <= date '2020-01-01' + 3
    )

Fiddle here.


MySQL (MariaDB) bound to 0.0.0.0, but doesn't appear to be listening to all IPs? My MySQL (actually MariaDB) server accepts connections at port 43210 without problems on the public IP address of, we'll say, 123.123.123.123 on eth0. This is on an Ubuntu 18.04 server that also has 192.168.1.10 configured for eth0.
I have a user configured to with permissions for all hosts (%):
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| myuser           | %         |
...

From a remote server (another host that is on the same LAN) I can connect to the public IP address of this MariaDB server:
$ mysql --user=myuser -pMYPASSWORD --host=123.123.123.123 --port=43210 mydatabase
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 340
Server version: 10.2.31-MariaDB-1:10.2.31+maria~bionic-log mariadb.org binary distribution
...

However, the problem is that when I try to connect to the LAN IP address, mysql times out:
$ mysql --user=myuser -pMYPASSWORD --host=192.168.1.10 --port=43210 mydatabase
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.10' (110 "Connection timed out")

Just to verify, netstat -tlpn | grep mysql shows it is listening on 0.0.0.0 which should be listening on all interfaces (I believe including the local IP):
tcp        0      0 0.0.0.0:43210           0.0.0.0:*               LISTEN      964/mysqld

I also run PostgreSQL on this same server with MariaDB and if I connect remotely from the same other test host from which I'm trying to make the mysql connection, it connects without a problem:
$ pg_isready -d mydb -h 192.168.1.10 -p 43211 -U myuser
192.168.1.10:43211 - accepting connections

The point of the last note about PGSQL is that I just want to clarify that connections are able to get through to the actual Linux server itself, just not MySQL (MariaDB).
I have also disabled the UFW firewall temporarily in order to keep that out of the equation.
What could be going wrong here?  What can I test further to isolate the problem?
I suspect that perhaps the bind-address = 0.0.0.0 in /etc/mysql/mariadb.conf.d/50-server.cnf may be the problem, but I thought that the quadruple 0 was in fact able to handle this.

Solved this issue!
It turns out that it was a bug in the local private IP assignment with Ubuntu 18.04 and the host provider, Linode. After looking into things a little deeper, even though the private IP was assigned, manually setting up Netplan instead of the pre-existing automated networking setup resolved the communications issue.


when to put a business rule in the business application layer or into a database constraint I need you wisdom, I am debating with another person when is it recommended to put the code of a business rule into a database constraints or inside of the application business layer?

There is a particuliar field that is used in several calculations for the machinerie settings in the plant. Today, we faced an issue because the file maintenance people did not specified a required field that somehow was not validated in the UI of the ERP. That field cause a division by zero error in a calculation that crash the PLC machine and halt the production the time to reboot the PLC machine.

So I filled a request to file maintenance to populate all field that were zero value and a second request to the database administrator to create a constraint in the database for the field so the record get rejected if the required field is 0.

The DBA denied my request and told me to validate the particuliar field in the UI or in the Business logic layer. I told him that even if I do that, someone in Microsoft Access or any other tools with the right permissions could assign zero to the particuliar field and it will again halt the production. He answered me: "so what? do you expect me to reprogramming all the business rules from the Business logic layer into database constraints?"

After that, I was wondering the question "when to put a business rule in the business application layer or into a database constraint"?

Thank you!
Sebastien

My 2 cents, although I am not nearly as experienced as many of the others on this site.

If it is related to data integrity (either from a relationship standpoint OR a standardized formatting standpoint), then I firmly believe that it should be handled by the database. If it's simply for a nice presentation, then I think that should be handled by the presentation layer.

In the example you site where the value caused a division by 0 error in a calculation .. if that calculation happened outside the database, then I do not believe a constraint on the data in the database is valid at that point. The database shouldn't need to know what the code does with its values and the code shouldn't need to know what the database does with its values...

The short answer (again, in my opinion) - the database should be responsible for delivering clean and reliable data in a standardized format ... what happens WITH that data is up to the person/code that is requesting it.