linked servers between availability group to a server

You will need to create an identical linked server on each of your 3 Availability Group nodes (server1, server2, server3). Linked servers (like logins, alerts, and jobs) are instance level objects so do not sync between the Availability Group nodes. You need one on each node in case of a failover, and you need to keep them in sync manually if they ever change.

Once you import the data, referencing the linked server in your script on your primary node (Production listener) and preferably with a synonym,  it will sync to the secondary node DBs in the Availability Group automatically. Synonyms are a Best Practice when using linked servers. If your linked server name changes you just update the linked server, not the code that imports the data.


I recently ran into the same issue. We have an Availability group where the secondary was having TempDB blow up and the primary was fine.

I created three tables and ran three stored procedures every 1-5 minutes on the secondary node. They are pretty light loads, obviously you should test them beforehand.

Here are the tables:

USE [master]
GO

/****** Object:  Table [dbo].[TempDBDataGrowth]    Script Date: 4/12/2018 1:13:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TempDBDataGrowth](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TimeStart] [datetime2](7) NOT NULL,
    [name] [sysname] NOT NULL,
    [database_id] [int] NOT NULL,
    [Size] [decimal](38, 2) NULL,
    [UsedSpace] [decimal](38, 2) NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [master]
GO

/****** Object:  Table [dbo].[TempDBDataGrowth]    Script Date: 4/12/2018 1:13:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TempDBDataGrowth](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TimeStart] [datetime2](7) NOT NULL,
    [name] [sysname] NOT NULL,
    [database_id] [int] NOT NULL,
    [Size] [decimal](38, 2) NULL,
    [UsedSpace] [decimal](38, 2) NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [master]
GO

/****** Object:  Table [dbo].[WhoIsActive]    Script Date: 4/12/2018 1:15:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[WhoIsActive](
    [dd hh:mm:ss.mss] [varchar](8000) NULL,
    [dd hh:mm:ss.mss (avg)] [varchar](15) NULL,
    [session_id] [smallint] NOT NULL,
    [sql_text] [xml] NULL,
    [sql_command] [xml] NULL,
    [login_name] [nvarchar](128) NOT NULL,
    [wait_info] [nvarchar](4000) NULL,
    [tran_log_writes] [nvarchar](4000) NULL,
    [CPU] [varchar](30) NULL,
    [CPU_delta] [varchar](30) NULL,
    [tempdb_allocations] [varchar](30) NULL,
    [tempdb_current] [varchar](30) NULL,
    [tempdb_allocations_delta] [varchar](30) NULL,
    [tempdb_current_delta] [varchar](30) NULL,
    [blocking_session_id] [smallint] NULL,
    [blocked_session_count] [varchar](30) NULL,
    [reads] [varchar](30) NULL,
    [reads_delta] [varchar](30) NULL,
    [writes] [varchar](30) NULL,
    [writes_delta] [varchar](30) NULL,
    [physical_reads] [varchar](30) NULL,
    [physical_reads_delta] [varchar](30) NULL,
    [query_plan] [xml] NULL,
    [locks] [xml] NULL,
    [used_memory] [varchar](30) NULL,
    [used_memory_delta] [varchar](30) NULL,
    [status] [varchar](30) NOT NULL,
    [tran_start_time] [datetime] NULL,
    [open_tran_count] [varchar](30) NULL,
    [percent_complete] [varchar](30) NULL,
    [host_name] [nvarchar](128) NULL,
    [database_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [additional_info] [xml] NULL,
    [start_time] [datetime] NOT NULL,
    [login_time] [datetime] NULL,
    [request_id] [int] NULL,
    [collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Here are the three scripts I'm using to collect information: (For sp_whoisactive, you will need to download that procedure online.)

http://whoisactive.com/

USE tempdb
insert into master.dbo.TempDBDataGrowth
(TimeStart, name, database_id, size,UsedSpace)
   SELECT
        getdate() as timestart,
         SD.name,
         MF.database_id,
         SUM( CONVERT(decimal(10,2),(DF.size/128.0)) ) as Size,
         SUM( CONVERT(decimal(10,2), (CAST(FILEPROPERTY(DF.name, 'SpaceUsed') AS INT)/128.0 ) ) ) AS UsedSpace
   FROM sys.master_files MF JOIN sys.databases SD
      ON SD.database_id = MF.database_id
   JOIN sys.database_files DF
      ON DF.physical_name collate DATABASE_DEFAULT = MF.physical_name collate DATABASE_DEFAULT
   WHERE MF.type = 0
   GROUP BY SD.name, MF.database_id;

insert into master.dbo.TempDBDataUsage
(
TimeStart, QueryExecutionContextDBID, QueryExecContextDBNAME, ModuleObjectId,
Query_Text, session_id, request_id, exec_context_id, OutStanding_user_objects_page_counts,
OutStanding_internal_objects_page_counts, start_time, command, open_transaction_count,
percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, reads,
writes, logical_reads, granted_query_memory, HOST_NAME, login_name, program_name
)
   SELECT
   getdate() as Timestart,
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC;

USE [master]
GO

DECLARE @return_value int,
        @schema varchar(max)

EXEC    @return_value = [dbo].[sp_WhoIsActive]
        @get_full_inner_text = 1,
        @get_plans = 1,
        @get_outer_command = 1,
        @get_transaction_info = 1,
        @get_task_info = 1,
        @get_locks = 1,
        @get_avg_time = 1,
        @get_additional_info = 1,
        @find_block_leaders = 1,
        @delta_interval = 1,
                @destination_table = 'WhoIsActive',
        @schema = @schema OUTPUT

SELECT  'Return Value' = @return_value

GO

This takes 1-2 seconds to run in my environment, I scheduled it for every 5 minutes. You should adjust as necessary, I left this as a job running for a week at 5 minute intervals and then I looked at the DB growth portions to see when my TempDB was filling. Then I looked at sp_whoisactive to see what query was running at that time.

Here are some queries I ran afterwards to help me find my culprits:

SELECT TOP 1000 *
  FROM [master].[dbo].[TempDBDataGrowth]
  order by UsedSpace desc

SELECT TOP 1000  *
  FROM [master].[dbo].[TempDBDataUsage]
  order by writes desc

SELECT TOP 1000 *
  FROM [master].[dbo].[WhoIsActive]
  order by tempdb_allocations desc


Are there any good reasons for keeping date and time in separate columns?

My guess is that it's a legacy support thing (i.e back in version 1 of their software, they had date and time in seperate fields and they've been forced to support it because it always worked that way).

That said, there's nothing brilliant about seperating date and time, so there's nothing you're missing.

(The other option is that your ERP system developers didn't know that a datetime field can store a date and a time, but far more likely is that they had a business requirement to make it work the same as prior versions.)


How to create an alphanumeric sequence like AAAA0000 and so on

We can do this with a couple common table expressions (CTEs) that generate some simple series ...

with

letters as
(select chr(i) as letter from generate_series(65,90) i),

digits as
(select lpad(i::text,4,'0') as digit from generate_series(0,9999) i)

select l1.letter || l2.letter || l3.letter || l4.letter || d.digit

from       letters l1
cross join letters l2
cross join letters l3
cross join letters l4
cross join digits d

our first CTE is named letters
letters generates a series of numbers from 65 to 90, which happen to be the ascii codes for letters 'A' to 'Z' so ...
we can generate our characters via the chr(i) function call
at this point letters represents the set of characters 'A' to 'Z'
our next CTE is digits
digits generates a series of numbers from 0 to 9999, and since we're going to be dealing with characters ...
lpad(i::text,4,'0') allows us to convert this series of numbers to left/zero padded character strings
at this point digits represents a set of characters '0000', '0001', ... '9999'
from here we just need to perform a series of cross joins and append our letters/digits together

Here's a dbfiddle for the above.

For the dbfiddle you can remove the limit/offset clause to generate the complete set, alternatively adjust the limit/offset clause to see a range of outputs.

For example, with limit 20 offset 59990 we get:

?column?
--------
AAAF9990
AAAF9991
AAAF9992
AAAF9993
AAAF9994
AAAF9995
AAAF9996
AAAF9997
AAAF9998
AAAF9999
AAAG0000
AAAG0001
AAAG0002
AAAG0003
AAAG0004
AAAG0005
AAAG0006
AAAG0007
AAAG0008
AAAG0009


Replication Access denied in Replication

I had the same problem and solved with the following steps:

Right click shared folder and click properties
Go to Advanced Sharing > Permissions
In dialog "permissions for "
Add snapshot user (\repl_snapshot in my case)
Grant "Full Control"
All should be ok now, and to verify
Go to "View Snapshot Agent status" and click start, you should now see something like "[100%] A snapshot of 1 article(s) was generated."


How do I make PostgreSQL include all the months where I did not buy anything?


Oracle is not like MySQL in this. Like most other RDBMSs it comes with it's own built-in storage engine that cannot be exchanged for another.


How can I move a MySQL database to another drive?

Shutdown mysql.
Move all the files in your current data directory to the new location (check out the location in step 3 - datadir parameter).
Locate my.ini file (it is in the mysql installation directory). Change datadir parameter value to point to the new location.
Start mysql.


How to determine character encoding in Hyper SQL database

HSQLDB stores text data as Unicode. You can verify that by opening the .script file for the database and looking at the data.

For example, for a table with a row containing "Montréal" the .script file contains

CREATE MEMORY TABLE PUBLIC.TABLE1(ID INTEGER PRIMARY KEY,TEXTCOL VARCHAR(50))
...
INSERT INTO TABLE1 VALUES(1,'Montr\u00e9al')

where \u0039 is the Unicode code point for the letter é.


Once you have had some activity, I think the number of threads varies between

(misc) + thread_cache_size
(misc) + max_connections

thread_concurrency was specific to Solaris 8, and has been removed from newer versions of MySQL/MariaDB.

Where (misc) is 1 for the main process plus replication slave threads plus innodb read/write/purge threads.  (Thanks danblack)


How to use a trigger to only insert one row

Instead of INSERT, use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE.

The former (IGNORE) checks UNIQUE key(s) to avoid inserting a second time.  The latter (IODKU) will either insert a new row or update an existing row (based on any UNIQUE key(s)).


Unexpected behavior from ltrim in postgres

The 2nd parameter is the list of characters to trim, not a string as such.
The manual about ltrim(string text [, characters text]):

  Remove the longest string containing only characters from characters
  (a space by default) from the start of string.

So this:

select ltrim('abc cab b ca foo', 'abc ');

... only leaves 'foo'.

To actually remove one leading string 'Test ':

SELECT regexp_replace('Test ThingTest ', '^Test ', '');

But to update a table, this is typically much more efficient:

UPDATE tbl
SET    col = right(col, -5)  --  right(col, length('Test ') * -1)
WHERE  col LIKE 'Test %';

Only touches relevant rows (important!), can use an index where appropriate (important if only few rows in a big table are affected) and the expression is cheaper, too. About that index:

PostgreSQL LIKE query performance variations


Query the Owner of SQL Server 2008 R2 Jobs

This will generate the commands to change all non-sa-owned jobs to sa. You just need to copy and paste from the results pane to the top pane (or a new query window), and execute.

SELECT 'EXEC msdb.dbo.sp_update_job @job_id = '''
        + CONVERT(VARCHAR(36), job_id)
        + ''', @owner_login_name = ''sa'';'
    FROM msdb.dbo.sysjobs
    WHERE owner_sid <> 0x01;


How to search whole MySQL database for a particular string

$ mysqldump -pPASSWORD database --extended=FALSE | grep pattern | less -S

More: http://winashwin.wordpress.com/2012/08/28/mysql-search/


Return values as 1 or 0 together with percent

Use a CTE to calculate A,B,C,D and E using CASE or IIF and then calculate final percentatge.

;WITH tbl AS
(
SELECT
    u.id,
    u.name,
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'A')
         THEN 1 ELSE 0 END AS [A],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'B')
         THEN 1 ELSE 0 END AS [B],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'C')
         THEN 1 ELSE 0 END AS [C],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'D')
         THEN 1 ELSE 0 END AS [D],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'E')
         THEN 1 ELSE 0 END AS [E]
FROM
    users u
)
SELECT
    id,
    name,
    CAST((A+B+C+D+E) / 5.0 as decimal(10,1)) AS Percentatge,
    A,
    B,
    C,
    D,
    E
FROM
    tbl;
GO

id | name  | Percentatge |  A |  B |  C |  D |  E
-: | :---- | :---------- | -: | -: | -: | -: | -:
 1 | user1 | 1.0         |  1 |  1 |  1 |  1 |  1
 2 | user2 | 0.4         |  1 |  1 |  0 |  0 |  0
 3 | user3 | 0.2         |  0 |  1 |  0 |  0 |  0
 4 | user4 | 0.0         |  0 |  0 |  0 |  0 |  0
 5 | user5 | 0.0         |  0 |  0 |  0 |  0 |  0

db<>fiddle here


SQL Server audit query that fires a trigger

I have a number of triggers that do this, and I find that DBCC INPUTBUFFER is generally the best way to do it. Caution: the output is limited to 4000 characters. Very long queries will be truncated.

DECLARE @sql nvarchar(max)
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS nvarchar(100)) + ')'
CREATE TABLE #SQL (
    EventType varchar(100),
    Parameters int,
    EventInfo nvarchar(max)
)
INSERT INTO #SQL
EXEC sp_executesql @sql

SELECT @sql = EventInfo FROM #SQL
DROP TABLE #SQL

At the end of this, @sql contains the query for the current request. Also, you could just as easily use a table variable instead of a temp table.


How to get the consolidated ServiceID with the sum of File Size associated with over an year

Here is an example of how this could be done.

Declare @Query1 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query2 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query3 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query4 table (id int, ServiceID int, Year int, FileSize1 int)

insert into @Query1(id,ServiceID,Year,FileSize1) values
(1,121,1980,10),
(2,122,1980,20),
(3,123,1980,30),
(4,121,1980,40)

insert into @Query2(id,ServiceID,Year,FileSize1) values
(1,121,1981,10),
(2,122,1981,20),
(3,123,1981,30),
(4,123,1981,40)

insert into @Query3(id,ServiceID,Year,FileSize1) values
(1,121,1982,10),
(2,122,1982,20),
(3,124,1982,30),
(4,125,1982,40)

insert into @Query4(id,ServiceID,Year,FileSize1) values
(1,123,1983,10),
(2,121,1983,20),
(3,122,1983,30),
(4,121,1983,40)

;With _cte as
(
select ServiceID,Year,FileSize1 as FileSize1 from @Query1
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query2
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query3
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query4
)

select ServiceID,
sum(case when year = 1980 then FileSize1 end) as FileSize_1980,
sum(case when year = 1981 then FileSize1 end) as FileSize_1981,
sum(case when year = 1982 then FileSize1 end) as FileSize_1982,
sum(case when year = 1983 then FileSize1 end) as FileSize_1983
 from _cte
group by ServiceID

| ServiceID | FileSize_1980 | FileSize_1981 | FileSize_1982 | FileSize_1983 |
|-----------|---------------|---------------|---------------|---------------|
| 121       | 50            | 10            | 10            | 60            |
| 122       | 20            | 20            | 20            | 30            |
| 123       | 30            | 70            | NULL          | 10            |
| 124       | NULL          | NULL          | 30            | NULL          |
| 125       | NULL          | NULL          | 40            | NULL          |


Postgres is quite good at doing such things, this is one reason I like it.

You can create an arbitrarily complex role hierarchy, but for our purposes a simple one will be enough:

CREATE ROLE student;

REVOKE CREATE ON SCHEMA public FROM public;  -- the second public is a pseudorole
                                             -- every role (user, group) is a member of
REVOKE CREATE ON DATABASE class FROM public; -- disallows schema creation, see below

GRANT USAGE ON SCHEMA public TO student;

At this point, student is a role that cannot log in.  We will use it as the privilege collector for the actual students, which you will have to create like this:

CREATE USER jsmith IN ROLE student;  -- will be able to log in,
                                     -- and a member of the student role

You can put the latter two statements into a stored function/procedure (which will include some dynamic SQL), or a script in your second favourite language.

This way your students will be able to create a schema for themselves (the name can be anything), and as they own that schema, they can also create objects in it.  If you want to restrict them to a schema named after them, do the following after the CREATE USER:

CREATE SCHEMA jsmith AUTHORIZATION jsmith;

To allow access for the teacher to all student schemas and objects, the easiest solution is:

GRANT student TO teacher;

What remains now is to allow students to access the existing and future tables in the public schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO student;
ALTER DEFAULT PRIVILEGES IN SCHEMA public FOR ROLE teacher
      GRANT SELECT ON TABLES TO student;

The last command assumes the tables will be created by the user/role teacher.


How to query/join tables where foreign key constraint is set in schema

A foreign key constraint is just a constraint, i.e., it prevents you from inserting data that would violate the constraint.

Foreign key constraints have no effect on queries; if you want to join tables through these values, you still have to write the join. (Why? Because the SQL standard has always said so, so changing it would break backwards compatibility.)

The only way to avoid the ON is by giving the columns the same name in the parent and child tables so that you can use the USING clause instead:

SELECT ... FROM ParentTable JOIN ChildTable USING (ParentID);

(And this is less typing than a hypothetical ... JOIN ... USING FOREIGN KEY ConstraintName.)


Running queries against multiple servers

What you're looking for it sounds like is the Target Servers of a job. You can add your servers to this portion so that the job is run against your desired servers.

BOL documentation on Target Servers: http://msdn.microsoft.com/en-us/library/ms189553.aspx


ORA-02436: date or system variable wrongly specified in CHECK constraint

Try (for the CHECK constraint) ...

t_type_end_date IS NULL

Also, when using your DDL code in apex.oracle.com (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production), we get

ORA-00972: identifier is too long

this is caused by the line

CONSTRAINT unique_t_type_t_type_start_date UNIQUE(t_type,t_type_start_date)


postgresql select error: could not read block, Invalid argument SQL state: XX000

I hope my post won't sound too rude, but you have corruptions issues and are trying rather desperate measures to fix it. Did you have a look at the documentation regarding zero_damaged_pages ? Make it a habit of looking at the official documentation for such configuration:

  Setting zero_damaged_pages to on causes the system to instead report a
  warning, zero out the damaged page in memory, and continue processing.
  This behavior will destroy data

Please have a look at https://wiki.postgresql.org/wiki/Corruption first. As stated on the wiki, you need to do a file system backup, and work on a copy. If you do not deeply understand what commands and configurations like zero_damaged_pages, pg_reset_xlog, ... do, save yourself a LOT of troubles and avoid using them.

But all is not grey and horrible... If you want a safe way out, do a restore from a safe backup (because you do have backups), and as Christopher Pettus said on one of his talks on PostgreSQL corruption, receive the praise of a grateful nation.


The benefit of users mapped to a certificate is to use certificate signatures to allow a signed object (like a stored procedure) to have permissions the user executing it doesn't have at all.

For instance, you could have a stored procedure that does something unutterably horrible (SUMMON Cthulu), and you have to allow that for X bad business decision, but you don't want to give Bob permissions to just SUMMON any monster - what if they summoned a gremlin in your system!

In this case, you could create a user mapped to a certificate, and then sign the stored procedure with that certificate.  The certificate mapped user is given the GRANT SUMMON right, while Bob gets only GRANT EXECUTE spSummon_Cthulu.

Thus, Bob can execute spSummon_Cthulu, and that SP is allowed to SUMMON('Cthulu') because the user mapped to the certificate that signed the SP has that right.

WARNING: Every time spSummon_Cthulu is changed, you'll need to resign in.  This is deliberate - that way even if Bob is allowed to alter the procedure, and Bob changes it to SUMMON('gremlin'), the changed SP no longer has a valid signature... and is thus no longer allowed to use the SUMMON command.

P.S. SUMMON is used as a fictional example for simplicity - xp_cmdshell 'bcp xxx' vs xp_cmdshell 'format D:' is a better horrific example, but involves cross-database permissions that are more complex than is necessary.

P.P.S. this may also make more sense if you consider it in the context of a database with multiple schemas not all owned by the same owner.


Why should a CTE start with a semi-colon?

I always do it when posting here or on StackOverflow because for WITH - since the keyword is overloaded - the previous command requires a terminating semi-colon. If I paste a code sample that uses a CTE, inevitably some user will paste it into their existing code, and the previous statement won't have the semi-colon. So the code breaks, and I get complaints like:

  Your code broke! I got this error message: Incorrect syntax near 'WITH'...

While I'd like to believe that folks are becoming better about always terminating their statements with a semi-colon, I'd rather pre-empt the noise and just always include it. Some people don't like it, but <shrug />. You can include as many semi-colons before or after a valid statement as you want. This is valid:

;;;;SELECT 1;;;;;;;;;;;;SELECT 2;;;;;;;;SELECT 3;;;;;

So there is no harm in there being an extra semi-colon preceding a statement that by definition requires it. It is safer to do so even if it ain't so pretty.

It has to be worded weirdly to get the point across, but "not ending a valid statement with a semi-colon" is actually deprecated since SQL Server 2008. So as I describe in the blog post I link to above, even in cases where it's not required to bypass an error, it should be used wherever valid. You can see this here (and this entry has been there for multiple versions):

Deprecated Database Engine Features in SQL Server 2016(search this page for semicolon)

Of course it wouldn't be SQL Server if there weren't exceptions. Try this:

BEGIN TRY;
  SELECT 1/1;
END TRY;
BEGIN CATCH;
  SELECT 1/1;
END CATCH;

It's not the only exception to the rule but it's the one I find most unintuitive.


You were nearly there with your code. Fixed trigger below:

CREATE OR REPLACE TRIGGER add_item_in_order AFTER INSERT ON order_item
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
UPDATE orders
SET total = total + (:NEW.quantity * (SELECT cost FROM watches WHERE id_watch = :NEW.id_watch))
WHERE orders.id_order = :NEW.id_order;
END;


SQL Server 2008 R2 Transactional Replication "Cannot insert explicit value for identity column..."

Just to add that you don't have to drop and re-create the replication just to change the "not for replication" bit.

You can do it using T-SQL without generating a snapshot or breaking your replication --

sys.sp_identitycolumnforreplication

1 = not for replication

0 = for replication and this causes the problems with Identity colums on subscriber side

To change it for all the tables :

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

To change it for just 1 table, first find out the object_id of the table and then run below

EXEC sys.sp_identitycolumnforreplication table_object_id, 1

EDIT

Below tsql will give you a nice output with command that can be reviewed before running for entire database :

if exists (select 1 from sys.identity_columns where is_not_for_replication = 0)
begin
SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) as SchemaName,
        QUOTENAME(t.name) AS TableName,
        c.name AS ColumnName,
        c.object_id as ObjectID,
        c.is_not_for_replication,
        'EXEC sys.sp_identitycolumnforreplication '+cast(c.object_id as varchar(20)) + ', 1 ;' as CommandTORun_SetIdendityNOTForReplication
    FROM    sys.identity_columns AS c
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1
        and c.is_not_for_replication = 0
end
else
print 'There are no identity columns that needs NOT FOR REPLICATION set to 1'

For adventureworks database :


If you never or rarely need to access more than one type at a time, then consider splitting.  However, personally I wouldn't do so.  I've been involved in a number of projects where this has been done, and result in a lot of additional complexity with little benefit.

The companies table appears to be a good choice for the common data.  Migrating data when a company changes type will increase complexity.  To enable this to work well you will need a table to generate ids from, and rules to ensure the same id doesn't appear in more than one sub-table.

I would consider splitting type specific columns into separate tables.  This will require different queries by type.  Again this may make the application more complex.  As there are only a few smallish columns, keeping them in the main table likely has relatively little overhead and simplify the overall design.  If you have many or larger fields which are type specific and only of use when the type is known, then consider splitting the sub-type data into another table.  It can share the same key as the relationship will be an optional identity relationship.

Another case where I would consider splitting a record is where there is a mix of tombstone data and highly transactional data.  I've seen it work well with parts, and inventory data.

Having worked in a company where suppliers were also clients, revisiting how suppliers and clients are differentiated might be a worthwhile exercise.  I would consider splitting suppliers into a separate table based on the following criteria.

Systems dealing with suppliers are separate from those dealing with customers.
There is little prospect of reusing access routines between the systems.
Volumetric data indicates that a least one system could get substantially better performance from splitting the tables.
Security/access requirements for the two type of companies are significantly different.

Reviewing what happens to prospect data when a prospect becomes a client may be a worthwhile exercise.  If it is retained, it may have value in determining how clients are being generated.  It will also make converting clients back to prospects after keying errors simpler.

Partitioning a table like a customer table is likely to be of little use.  Hot clients will likely be cached at the database or o/s block buffer level.  If you have the ID it is pretty well a direct look-up.

Transactional data is more suitable for partitioning.  Order details can age out in hours depending on order processing times.   Customer data will likely take years to age out.   I don't see any data in the table which could be used to effectively partition the data base on 'hotness'.   I have generated an archiving system for customers and their data.  Determining the customers which could be archived was not trivial.


I suggest a filtered index:

CREATE INDEX ix_valid_text_column
  ON dbo.t1(Tid)
  WHERE text_column IS NOT NULL;

Now your query should be able to use that index instead of the clustered index, and it should be much more efficient.

Even better, it enables you to look at the metadata views, so you read one row (or a number of rows equal to the number of partitions) instead of doing any sort of manual count:

SELECT SUM(p.rows)
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i
  ON o.[object_id] = i.[object_id]
  AND o.[index_id] = i.[index_id]
INNER JOIN sys.tables AS t
  ON t.[object_id] = t.[object_id]
WHERE t.name = N't1'
  AND t.[schema_id] = 1
  AND i.name = N'ix_valid_text_column';

The query looks uglier but it will be far more efficient (in spite of what the execution plan may tell you), especially if the number of non-NULL values is small, and especially if the table is high-volume (your current query can block and can be blocked).


Last time, I answered your question by suggesting you perform a well-scripted parallel dump of all tables.

Since the tables are lopsided in size, parallel table dumps are not much help for the 20GB and 13GB tables. What can be done? You must try something out of the ordinary. The good thing here is that all the tables are MyISAM.

Have you ever considered running an rsync? How do you do that?

EXAMPLE

You have /var/lib/mysql as your datadir for a remote production database

Perform an rsync on /var/lib/mysql on prod server (IP Address 10.1.2.30) to /backups on a backup server. Just run a script something like this on the backup server:

INIT_TS=0
COMP_TS=0
DIFF_TS=61
RSYNC_THRESHOLD=60
DBSERVER_IP=10.1.2.30
FOLDER_TO_BACKUP=/var/lib/mysql
mkdir /backups/backup_temp

while [ ${DIFF_TS} -gt ${RSYNC_THRESHOLD} ]
do
    INIT_TS=`date +%s`
    rsync -arv user01@${DBSERVER_IP}:${FOLDER_TO_BACKUP}/ /backups/backup_temp
    CURR_TS=`date +%s`
    (( DIFF_TS = CURR_TS - PREV_TS ))
done

MYSQL_CONN="-h${DBSERVER_IP} -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 3
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

rsync -arv user01@${DBSERVER_IP}:/var/lib/mysql/ /backups/backup_temp

BACKUP_TIMESTAMP=`date +"%Y%m%d%H%M%S"`
mv /backups/backup_temp /backups/${BACKUP_TIMESTAMP}

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Here is what this basic backup script does

Step 01) rsync production's /var/lib/mysql (from remote server) to /backups on local server
Step 02) If running time of rsync is greater than 60 seconds, repeat Step 01
Step 03) Close all tables and issue global read lock
Step 04) Perform final rsync (SELECTs allowed only)
Step 05) Release global read lock

Notice the following:

the only mysql interaction is

issuing the global read lock
checking for the process ID that is holding the global read lock
releasing the global read lock

FOLDER_TO_BACKUP is the datadir. If you want to backup a specific database (say mydb), set FOLDER_TO_BACKUP to /var/lib/mysql/mydb
Actual downtime is from the acquisition of the SLEEP_ID to the processing killing of the SLEEP_ID

Give it a Try !!!

UPDATE 2012-02-02 11:47 EDT

The above script secures mysqld in such a way that it takes a disklevel copy of the database while allowing mysqld to perform INSERTs, UPDATEs, and DELETEs during the copy. This is necessary since the MyISAM Storage Engine does not have a natural mechanism for allowing a copying of itself. Even powerful products like MySQL Enterprise Backup and Percona XtraBackup perform full table locking on MyISAM tables just like this. My personal preference is to script it so I can see it, feel it, taste it, and touch it and just know for myself that MyISAM is fully protected.

The advantage of using my script does stems from the fact that I shorten the length of downtime. In the context of this anwser, downtime is really the period where the mysqld process will only allow SELECTs to all MyISAM tables during the final rsync.

An additional benefit is that all MyISAM tables copied will all have the same point-in-time should you wish to restore all MyISAM tables as of the same point-in-time. That point-in-time will be based, not on the start of the backup process, but when the global read lock as acquired.

If you need to copy only specific tables and you can guarantee no DB Connection will touch it, only then can you toss aside any failsafes and copy any MyISAM table to your heart's content.

UPDATE 2012-02-03 11:47 EDT

If the length of time the rsync takes concerns you, here is an additional suggestion:

You should also make nightly backups of the binary logs. By having nightly copies of binary logs, you would essentially have the incremental backups via those binary logs.

That being said, you could set up a weekly or monthly run of the backup script. Recovery to a specific point-in-time would be your responsibility from there, but you would have every piece of data necessary to do so.


171214 is terribly small.  Change my.cnf to set it to about 70% of available RAM.  Example, for a 16GB system, use 11G.  If that antique server is only 2GB, use 500M.

table_cache = 256M is terribly big -- the units is tables, not bytes.  Furthermore, in newer versions, the name is table_open_cache, so:

table_open_cache = 500

(How did the date get there?  171214 = Dec 14, 2017)

Or is the field left blank??

(From Comment, plus annotations)

 [mysqld]
 port   = 3306
 socket = /tmp/mysql.sock
 skip-external-locking
 key_buffer_size = 256M     -- Change to 50M
 max_allowed_packet = 1M
 table_open_cache = 64      -- let's change to 200
 sort_buffer_size = 512K
 net_buffer_length = 8K
 read_buffer_size = 256K
 read_rnd_buffer_size = 512K
 myisam_sort_buffer_size = 8M
 max_connections = 500    -- 100, until you have a good reason for more
 key_buffer = 256M      -- Remove, old spelling
 table_cache = 256M     -- Remove; this is an old spelling
 slow-query-log=1
 slow-query-log-file=D:\TABLETEMP\my.slow.log
 long_query_time=1       -- good
 log_error="mysql_error.log"

 innodb_buffer_pool_size = 1000M  -- compromise between 'safe' and 'useful'


Postgres suggests "Truncate table ... at the same time"

What does it mean to truncate a table at the same time?

It means with the same statement. You can truncate more than one tables:

TRUNCATE xxxxx, yyyyy RESTART IDENTITY ;

More details in Postgres docs: TRUNCATE.


LIKE to select the independent existence of the word whereever in the text

Using LIKE

Single line like search:

WHERE
 ' ' + [TextValue] + ' ' LIKE '%[.,;:() ]Search[.,;:() ]%'

/* [] contains list of allowable characters, adding spaces around
   [TextValue] removes need to have multiple OR [TextValue] LIKE */

Without padding you will need to specifically handle string appearing at start/end of string:

WHERE
    [TextValue] LIKE '%Search%' --middle
    OR
    [TextValue] LIKE 'Search%'   --start
    OR
    [TextValue] LIKE '%Search'   --end

Using FULLTEXT INDEX

Edit2, for the full text purists

CREATE FULLTEXT INDEX on MSDN states for the AUTO option (the default)

  Although changes are propagated automatically, these changes might not be reflected immediately in the full-text index.

So, it may not give correct results. But then a few seconds later it will.

Also, for upto a few 10k rows it will perform adequately: it scales O(n). I use the LIKE on a table with around 25k rows but users know it will perform badly if they search this way (it's on an "advanced" page). I gain in the trade off by managing a full text index

Full text search isn't the correct solution, it's one option

Note: Experience has shown me that FullText indexing in SQL Server has severe performance implications, ie: Only implement if you really need to and you know what you're doing! (@Andrew Bickerton)


Call UPSERT function multiple times to different inputs

UPSERT 1 row at a time with function
A "table-function" is a function returning a set of rows (acting like a table when called with SELECT * FROM myfunc()). What you have is not a table-function. Since nothing is returned you can use a simple call:
SELECT merge_vehicles(vid, cid, vname, reg_no, name, name_1st)
FROM  (
   VALUES
     (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
    ,(2336, 55, '038THX', '038THX', '831', 1)
    , ...
   ) t(vid, cid, vname, reg_no, name, name_1st)        -- arbitrary column names

While that works, upserting 1 row at a time is very inefficient for big sets.
Bulk UPSERT
Assuming (vid, cid) is the primary key of our table test (information missing), I suggest a data-modifying CTE for the UPSERT after taking out an EXCLUSIVE LOCK on the table.
The EXCLUSIVE LOCK still allows concurrent transactions to read from the table, but no writes. It is only necessary if you actually can have concurrent transactions writing to the same table.
BEGIN;

LOCK TABLE test IN EXCLUSIVE MODE;

-- Or use a temp table for new rows. See below.
WITH my_rows(vid, cid, vname, reg_no, name, name_1st) AS (
   VALUES
      (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
     ,(2336, 55, '038THX', '038THX', '831', 1)
     , ...
   )
, upd AS (
    UPDATE test t
    SET   (  vname,   reg_no,   name,   name_1st)
        = (m.vname, m.reg_no, m.name, m.name_1st)
    FROM   my_rows m
    WHERE  t.vid = m.vid        -- match on PK columns
    AND    t.cid = m.cid
    RETURNING t.vid, t.cid      -- return only PK columns
    )
INSERT INTO test
      (  vid,   cid,   vname,   reg_no,   name,   name_1st)
SELECT m.vid, m.cid, m.vname, m.reg_no, m.name, m.name_1st
FROM   my_rows m
LEFT   JOIN upd USING (vid, cid)
WHERE  u.vid IS NULL;           -- only remaining rows

COMMIT;                         -- releases all locks

If the list is long, consider a temp table populated with COPY to replace the CTE my_rows and its VALUES expression.
Related:

Issue with composite type in an UPSERT function
How do I do an UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
Optimizing bulk update performance in PostgreSQL
Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

Call set-returning function multiple times in one SELECT
(Before update to the question)
Assuming we are dealing with a set-returning function (a.k.a. "table-function").
You can use a VALUES expression for literal input of a derived table of parameters (or any other derived or actual table in its place) and conveniently use a LATERAL JOIN (Postgres 9.3+) to get the united results in one query. You may want to order in some way ...
SELECT f.*
FROM  (
   VALUES
     (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
    ,(2336, 55, '038THX', '038THX', '831', 1)
    , ...
   ) t(vid, cid, vname, reg_no, name, name_1st)        -- arbitrary column names
     , merge_vehicles(vid, cid, vname, reg_no, name, name_1st) f;

The LATERAL JOIN is implicit with function calls in the FROM list. More details here:

How to turn JSON array into Postgres array?

Numeric literals in this example default to type integer and string literals become text. Add explicit casts if you need specific types.


Are you sure  stateTwoHistoryTableTrigger exists? I'm asking since at least some versions of MySQL don't support multiple triggers with the same action time and event for the same table. You can check if the trigger exists like:

select trigger_name
from information_schema.triggers
where EVENT_OBJECT_TABLE = 'syslog';

If that is the problem you can add both actions in one trigger like:

DELIMITER //

CREATE TRIGGER stateOneHistoryTableTrigger
AFTER UPDATE ON syslog
FOR EACH ROW
BEGIN
    IF (old.syslogState=0 & new.syslogState=1)
    THEN
        INSERT INTO stateOneHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
        VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);

ELSEIF (old.syslogState=1 & new.syslogState=2)
    THEN
        INSERT INTO stateTwoHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
        VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);

    END IF;
END; //

DELIMITER ;

May I ask why you want two history tables? If you keep all history in one table you can derive the two history tables from there:

CREATE TABLE stateHistoryTable (
    historyDate DATETIME DEFAULT NULL,
    eventID int(11) NOT NULL,
    eventTIME decimal(15,0) NOT NULL,
    newestEventTime decimal(20,0) DEFAULT NULL,
    deviceIP varchar(20) NOT NULL,
    syslogMessage varchar(1000) NOT NULL,
    syslogState decimal(2,0) NOT NULL,
    eventCount decimal(5,0) NOT NULL
) engine=innodb;

CREATE TRIGGER stateHistoryTableTrigger
AFTER UPDATE ON syslog
FOR EACH ROW
BEGIN
    IF (old.syslogState + 1 = new.syslogState)
    THEN INSERT ...
END IF;

CREATE VIEW stateOneHistoryTableTrigger AS
    SELECT ...
    FROM stateHistoryTable
    WHERE syslogState = 1;

CREATE VIEW stateTwoHistoryTableTrigger AS
    SELECT ...
    FROM stateHistoryTable
    WHERE syslogState = 2;


Update sys.job$ set this_date=:1 where job=:2 as one of the most time-consuming queries - oracle

If that is your most time consuming query, then your database does almost nothing.

That is 0.211599 and 0.002923 seconds per execution with a total runtime of 39.9 and 1.7 seconds.

order by elapsed_time / executions)         )  where rownum <= 10;

This returns the shortest running queries, not the longest-running ones. Use order by ... desc.

No. This is an internal statement that updates the dictionary with the start time of a job on starting it. There is nothing to investigate about it.
You should find this information in  V$SQL_SHARED_CURSOR. Look for the column with a value of Y, and the name of that column specifies the reason for having another child cursor.


Optimize series of queries that recycle the same filter

The key thing to realize is that a common table expression (CTE) is not a table; it's an expression. In SQL Server at least, a CTE is not materialized; the query is not run once, with the results re-used if the CTE appears more than once in the main query. Every time you reference the CTE, that query is re-executed.

So, you may want to consider a hybrid approach.

I would build a temporary table with the basic filtered data - what's currently returned by the Filter CTE. I would also expand it slightly. Currently you say it only returns an ID value. If there are any other values that you'll need in the final query (especially if they come from tables that aren't involved in intermediate queries), include that in the temp table - otherwise, you'll just have to go back and get it later.

Note that you can index a temporary table. Unless the temp table rows are wider than I'd expect, I'd put a clustered index on the IDs. This may help with other joins. (Note also that populating a table using an ORDER BY may happen to insert data in a particular order, but it guarantees nothing that the DB engine will recognize - it will consider the data as unsorted).

Now, use the temporary Filter table in your list of CTEs. It will not have to be reconstructed for every query it's used in, which should improve things quite a bit.

If there are other CTEs in your list that are used more than once, you may want to consider making them temporary tables as well. However, depending on the complexity of the query, two or three uses may not cause a significant performance loss, compared to the benefit of maintainability.


How does Data Warehousing work in reverse?

This question is a bit too broad for a definitive answer, because there are endless possibilities for data to flow in all sorts of directions between various data stores. I also find it strange that the articles you read did not provide examples.

One general application of a data warehouse feeding back information to an operational system would be analytics, calculated over historical data, affecting operational system rules, such as:

Consolidated (in a DWH) daily sales data from all stores of a retailer are used to determine certain product discounts in the point-of-sale system and goods delivery schedules in the logistics application.
Bank capital level per Basel rules are calculated in the DWH nightly and generate orders and trading limits in the trading application for the next day.
Customer churn prediction model of a telco tells their marketing call center who to spam-call next.


How to add a named constraint in Oracle SQL?

Oracle 12c:

Table

create table clookup ( clookup_col varchar2( 64 ) );

Add a NOT NULL constraint

alter table clookup
modify ( clookup_col constraint lookup_9 not null ) ;

Optional

select
  table_name
, constraint_name
, constraint_type
from user_constraints
where table_name = 'CLOOKUP' ;

-- result
TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE
CLOOKUP     LOOKUP_9         C

Addendum

It may not always be necessary to use the ALTER TABLE ... MODIFY ... technique.  There is a "special rule" for defining NOT NULL constraints (see documentation):

  You can define constraints syntactically in two ways:

  As part of the definition of an individual column or attribute. This
  is called inline specification.

  As part of the table definition. This is called out-of-line
  specification.

  NOT NULL constraints must be declared inline. All other constraints
  can be declared either inline or out of line.

Also (here):

  Use the ALTER TABLE statement to alter the definition of a
  nonpartitioned table, a partitioned table, a table partition, or a
  table subpartition.

The following examples show what does NOT work:  just adding a NOT NULL constraint (not a column) to a table, via ALTER TABLE ... ADD ... (out-of-line).

-- Oracle 12c
create table T ( name varchar2( 32 ) ) ;

alter table T
add constraint name_nn not null ( name ) ;
-- ORA-00904: : invalid identifier

alter table T
add (
  n number
, constraint name_nn not null ( name )  -- out-of-line
) ;
-- ORA-00904: : invalid identifier

ALTER TABLE ... ADD ... can be used for other constraints eg UNIQUE or PRIMARY KEY "out-of-line" (as in the examples above), though.  It also works when we want to add NOT NULL constraints "inline" ie when specifying a column and a constraint eg

create table T ( name varchar2( 32 ) ) ;

alter table T
add n number not null ;
-- Table T altered

-- or
alter table T
add n number constraint number_nn not null ;
-- Table T altered


ORA-00932 - Error when I try to filter all_triggers

Maybe the simplest way is:

create table my_all_triggers as
select
    owner, trigger_name, trigger_type, triggering_event, table_owner,
    base_object_type, table_name, column_name, referencing_names, when_clause,
    status, description, action_type,
    to_lob(trigger_body) as trigger_body, --- <======== TO_LOB conversion
    crossedition, before_statement, before_row, after_row, after_statement,
    instead_of_row, fire_once, apply_server_only
from all_triggers
where upper(trigger_name) like upper('%ABC%') ;

select * from my_all_triggers t
where upper(trigger_body) like '%DEF%';

You can find the other methods using PL/SQL or XML functions here: working with long columns

Or just use ALL_SOURCE to find the trigger:

SELECT * FROM all_source t
WHERE type = 'TRIGGER' and UPPER(name) like UPPER('%ABC%')
AND UPPER(text) LIKE '%DEF%';


Monumental difference in execution time between queries when using RECOMPILE query hint

As documented in the article Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

  If you use a local variable in a query predicate instead of a
  parameter or literal, the optimizer resorts to a reduced-quality
  estimate, or a guess for selectivity of the predicate. Use parameters
  or literals in the query instead of local variables

When the optimiser has no usable statistics at all for a column it will guess that an = predicate will match 10% of rows, BETWEEN 9%, and any of >, >=, < and <= will match 30%. If there are column statistics available an = predicate will be treated differently as below.

  Even when local variables are used in a query, an estimate that is
  better than a guess is used in the case of equality predicates.
  Selectivity for conditions of the form "@local_variable = column_name"
  is estimated using the average value frequency from the histogram for
  column_name. So, for example, if the column column_name contains all
  unique values, then a selectivity estimate of 1/(number of unique
  values in column) will be used, which is accurate.

So this is essentially the same as using for OPTIMIZE FOR (UNKNOWN). It may well be more accurate than a flat 10% guess but it isn't tailored to the specific values that you are querying.

  To force SQL Server to optimize a query every time it is run, and use
  the values of local variables to estimate cardinality and cost during
  optimization of the query, add the RECOMPILE query hint to the query.

With the use of RECOMPILE you presumably are getting more accurate cardinality estimates and therefore a different plan with join orders/ join types more suited to the number of rows returned from different parts of your actual query.


Login to SQL Server using Windows Authentication

The only change required is in the connection string: remove the User ID=...; and Password=...;, and add Integrated Security=SSPI; instead.

More information can be found here.


Prevent SQL Server Management Studio changing destination DB on restore

No and as you're aware this is a pretty serious accident to make.

I'd suggest learning a little PowerShell. Why? Because that kind of restore is going to happen often, and you could script it into an agent job or one liner - and you won't make this mistake again. It's one of the great parts of scripting; reducing human error and improving the script each time rather than having to remember everything yourself.

As for other suggestions

You can backup from an account on PROD that can only backup and doesn't have restore privileges.
Never do just a restore. Always do a backup (with copy only) of what you're overwriting before the restore. Every time. (If you're extremely confident of your existing backups and can run a quick verify of them... eg that you've taken a log backup in the last minute or so and the log chains aren't broken and that the files are there... you may get away with that too).

I can tell you there's been a number of times I've been given specific server and database names, had them double checked, then had someone realise while its in progress that THEY were wrong and had mixed something up. Having a quick rollback on hand is a godsend.


what happens If I remove mysql-bin.index file?

If you erased all the binary logs, the mysql-bin.index can also go.
Do you still need binary logging ? You must restart MariaDB, and the mysqld process will recreate mysql-bin.index it for you.
If you no longer want binary logging, you must comment out log-bin from the MariaDB config file. You still need to restart MariaDB.


Is there a free/web-based game that requires knowledge of SQL to win?

I don't know of a gamified SQL learning tool, but I'll recommend The Manga Guide To Databases as a way to cut through the boredom factor. It's a fast read, even for retro-technical managers, and does a quick survey of how to design and use a DB.


Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range

Default B-Tree indexes can't operate very intelligently on range data types.  What you want is a GiST index on the range, so something like:

create index on fin_data using gist (holding_time);

For your main query, this would probably give a BitmapAnd plan which would use one index to get the list of row pointers satisfying broker_id and the other to get the list satisfying holding_time, and then take the intersection of the lists before visiting the table.  This could be quite a bit faster.  This is the most flexible case, because that gist index can be used in combination with any other indexes you have.  If that is not fast enough, you could try create extension btree_gist and then making the index:

create index on fin_data using gist (broker_id, holding_time);

This would be less flexible for other query variants, but ideal for the query you identified as your main query.

Note that GiST indexes take a lot longer to initially build than B-Tree indexes.  They are also are slower to maintain when new rows are inserted, so if your system struggles to keep up with insertion rate, you would want to benchmark it first.

Another idea which doesn't involve making more indexes, is to select just the columns you need rather than '*' in your queries.  That might allow index-only scans to be used where they otherwise cannot be.


Server 2012 Linked Server From An Instance

Comments were very useful in leading to the solution.  After running SQL Server Kerberos Configuration Manager I saw that while the first was on 1433 for SiteADefaultInstance and the second instance of the database was listening on a dynamic port for SiteASecondInstance.

Went into SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SiteASecondInstance > TCP/IP and change IPALL TcpPort to a different port, say 9999.

SetSPN -s "MSSQLSvc/SiteAServer:9999" "user"

After this could create a linked server.


For proper normalization, I would split them up into two tables. It makes for a more logical design, and easier to maintain down the line if you want to make additional modifications.


That would be 000000010000000000000001 or 000000020000000000000001. That's timeline 1 or 2, log 0, segment 1. Which one is being accessed depends on which timeline your system in (you can use pg_controldata to find that out, but basically the system switches timeline when you do a PITR recovery or a replication failover)

The error message basically says that file is corrupt, which is really bad. Another reason this could happen is if that file is from a different version of postgresql - any chance you accidentally mixed two different versions?


Well product_tree_length is a table alias, not a column alias. So if you use that alias in the SELECT list, obviously you get a record.

You have two options to properly define an alias for the column

Solution one: use a column alias inside the derived table:

select t.page
       product_tree_length.len,
       count(*)
from "Table" t
  cross join lateral (
     SELECT length(....) as len
  ) AS product_tree_length
...

Solution two: define a column alias as part of the table alias:

select t.page
       product_tree_length.len,
       count(*)
from "Table" t
  cross join lateral (
     SELECT length(....)
  ) AS product_tree_length(len)
...


Only variation I'd suggest on your check list is to replace the word BACKUP with RESTORE. Checking that backups complete is a good start, what really matters is whether or not you can restore from them. Alert on a backup failure, automate a random sampling of restores so you know your backups are good.

The next step on from a daily/weekly/monthly check list is history. A check on x/y/z performance counters is meaningless without a baseline to compare today with yesterday. Without understanding the today vs yesterday, it's impossible to predict next month.


How do I force a Writable Materialized View instead of an Updatable one?

Updatable materialized view was an Advanced Replication feature.
A writable materialized view was simply an updatable materialized view that was not a member of a materialized view (replication) group.
Desupport of Advanced Replication

Starting in Oracle Database 12c release 2 (12.2), the Advanced
Replication feature of Oracle Database is desupported.
The Oracle Database Advanced Replication feature is desupported in its
entirety. The desupport of this feature includes all functionality
associated with this feature: multimaster replication, updateable
materialized views, hierarchical materialized views, and deployment
templates. Read-only materialized views are still supported with basic
replication.

You are looking for a solution for a feature that became deprecated 7 years ago (in 12.1) and desupported 4 years ago (12.2).
The FOR UPDATE clause still exists for backward compatibility, but it is not even documented anymore.
I do not know why Oracle implemented it this way, but at this point, I would just let it go.
For the use case you described, a simple table is fine. You can do incremental refreshes with MERGE on key + timestamp, and do a complete refresh with truncate + insert ... select ....


Restrict record to be update or deleted according to dates inside row for SQL Server 2005

A constraint can't control DML operations unless the update actually attempts to change the data in that column. It certainly can't control deletes unless we're talking about table-level constraints but I recommend against this.

Most people go with after triggers for this type of logic, but this means (a) attempt to perform the change (b) if it fails your logic, roll it back. I much prefer an INSTEAD OF trigger for this. The following will allow updates / deletes only to rows that fall within your date span (of course you will have to replace the 'date span ' strings with actual dates), even if a multi-row operation might affect some rows but not others.

CREATE TRIGGER dbo.PreventUpdate_table_name
ON dbo.table_name
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE t
      SET t.col = i.col
      FROM dbo.table_name AS t
      INNER JOIN inserted AS i
      ON t.key = i.key
      WHERE t.date_column >= 'acceptable date span start'
      AND t.date_column < 'acceptable date span end';
END
GO

CREATE TRIGGER dbo.PreventDelete_table_name
ON dbo.table_name
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DELETE t
      FROM dbo.table_name AS t
      INNER JOIN inserted AS i
      ON t.key = i.key
      WHERE t.date_column >= 'acceptable date span start'
      AND t.date_column < 'acceptable date span end';
END
GO


Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA

If you do this:

SELECT * FROM sys.databases

And the log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group replicas. One of the replicas may be lagging behind due to a slow network, or it may be down altogether.

If you check the AG dashboard and it shows no queues, you may have been a victim of thread exhaustion. It's a known issue that the AG dashboard stops updating after worker thread exhaustion. You'll need to check the status on each replica directly rather than relying on the primary. Nick's note in that Connect item says that you can just alter a replica's properties to restart replication, but that doesn't always work (especially if you have hundreds of databases on a replica with a large amount of data that needs to be sent, and restarting replication can just cause the worker thread exhaustion again.)

If the last guy set up an AG replica and it's not supposed to exist anymore, then it's time to remove that AG and/or replica. Just be careful that apps aren't pointing to the listener name in order to connect to your SQL Server.


Generate a table that shows a monthly series and the number of tasks done per month

I think I understand why you used generate_series() now. If you know your monthly_tasks table will always have all months in it, then you can write your query this way instead:
SELECT to_char(date_completed,'MON YYYY') AS month, SUM(CASE WHEN job_status = 1 THEN 1 ELSE 0) AS jobs_completed
FROM monthly_tasks tbl
WHERE tbl.date_completed BETWEEN '2021-01-01' AND '2021-12-01'
GROUP BY month;

If there's a chance your monthly_tasks table could be missing months, then you might want to create a dates table that has the all the months, and then left joining on (by month) to ensure you always have all the months. A dates table can be useful in a lot of future situations too.
Here's a pretty good boilerplate date's dimension table script you can use.


Is it possible to backup and restore part of a database in sql-server?

To be honest, this is easiest:

backup/restore a copy locally
remove unwanted data from the copy (with DELETE or TRUNCATE TABLE, not DROP...)
ship the copy

I wouldn't bother with filegroups because of the added complexity you noted...


Find MIN and MAX Database Backup duration

Use the ROW_NUMBER() aggregate to get the min and max rows, then show only those rows:

;WITH cte AS (
    SELECT d.name
        , bs.backup_start_date
        , bs.backup_finish_date
        , DurationInSeconds = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
        , RnMin = ROW_NUMBER() OVER (PARTITION BY d.name
               ORDER BY DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) ASC)
        , RnMax = ROW_NUMBER() OVER (PARTITION BY d.name
               ORDER BY DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) DESC)
    FROM msdb.dbo.backupset bs
        INNER JOIN master.sys.databases d ON bs.database_name = d.name
)
SELECT cte.name
    , cte.backup_start_date
    , cte.backup_finish_date
    , cte.DurationInSeconds
FROM cte
WHERE cte.RnMax = 1
    OR cte.RnMin = 1
ORDER BY cte.name
    , cte.backup_start_date;


Transfer database backup from Production to Staging during business hours

Usually copying your latest database backup (from the production backup location) to an area where you can restore it over the top of your staging area should have no effect on your production database.  The only time where this would affect it is if you're storing the backups on the same drive(s) that is hosting the live database, but hopefully you're not.  In this instance, the file copy operation will just generate extra, and competing, I/O with your normal database operations.

Your question doesn't provide enough context around how this sync process works, but a 15GB file copy isn't usually large enough (in my experience) to crash a server or cause any severe performance degradation.


ERROR: canceling statement due to conflict with recovery

Some old row versions were removed by VACUUM on the primary server, but these row versions could still be required by the query on the standby.  So there is a conflict between the running query and the startup process applying changes that come from the primary.
PostgreSQL waits and delays replay for max_standby_streaming_delay, then cancels the query. So to avoid that problem, set max_standby_streaming_delay to -1 on the standby.
You can read more in my blog.


mysql master.info shows password in plain text

"The password" is a password the slave uses to log itself in to the master. In MySQL replication, the master's role is passive with regard to connections between master andbl slave -- the slave always initiates the connection.

The slave needs a password in order to connect to the master and authenticate itself.  Fundamentally, there's no way around that.  Encrypt it?  Okay, but the key then needs to be on the slave, so no ground is gained.  Hash it?  Well, no, the slave is the client.  It needs the plaintext password so it can generate a response to the server's auth challenge.  TLS?  Same problem, the slave needs a copy of the private key.

You can store it in a table instead of a file, but that doesn't really accomplish anything.

Correctly configured, though, this seems like a non-issue.

If the account the slave is using follows the principle of least privilege and is locked down to holding only the necessary privilege level and being usable from only on that one machine (e.g. GRANT REPLICATION SLAVE ON *.* TO my_repl_user@ip.of.slave.svr IDENTIFIED BY PASSWORD 'slave_pw';) then the password and the associated account is fundamentally useless for compromising the master -- it's completely invalid unless used from the slave's IP (which the master reads from the IP stack -- this isn't a value the slave sends) and even then can only request the replication stream... so where's the problem?  If the slave is compromised, the password to the master one of the least interesting pieces of information, unless the account is not tied to the slave's IP address or is a reused password.


Do i need "CHECK CONSTRAINT" when i am using "WITH CHECK ADD CONSTRAINT"

The answer to both of your questions is really the same.  You are using a script generation tool.  It does what it's told to do.  Typically the second command is only needed if you are doing a WITH NOCHECK on the first one.  However it's included because the settings for the script generation component of SSMS told it to include it.

Here is a post I did on modifying those settings.

As far as the GO command.  This is a batch separator and is useful in breaking up a script both visually and for the computer.  It ends the scope of variables and affects how the parser looks at your script.  Again it's included because that is what the tool was told to do.


Populate main table with random employees (shuffled) from employee table based on department

You can use dbatools Invoke-DbaDbDataMasking and have the New-DbaDbMaskingConfig adjusted as per your requirements.

Above has some limitations but it will help achieve what you want to.


Stored Procedure triggered by Service Broker can't access another database in SQLServer 2017

From comments:

I don't believe the behavior has changed between SQL 2016 and SQL 2017. It may be that the SQL 2016 database was TRUSTWORTHY whereas the SQL 2017 database is not or has a database owner sid mismatch. The proper solution is module signing as Rusanu describes. Also see the Activation Procedures for Service Broker topic topic in the appendex of Erland's module signing article. - Dan Guzman

I also supposed SQLServer 2016 hadn't CLR security activated as per the Microsoft Docs - Vincent Chalmel


However as long as we continue to rebuild online or reorganize we will retain the 14 bytes per row. Is that correct?

This is true.  REBUILD WITH (ONLINE = ON) and REORGANIZE will leave the 14 bytes as-is.  A normal REBUILD will clear it.

If you disable RCSI, the 14 byte version tag will be cleared by any of the three operations you mentioned:

REBUILD
REBUILD WITH (ONLINE = ON)
REORGANIZE

For your future reference, it's not too hard to test this yourself.  Just create a database and table, and then try the different operations, checking the row size each time.

USE [master];
GO
CREATE DATABASE AreSeaEssEye;
GO

USE [AreSeaEssEye];
GO

CREATE TABLE dbo.Foo (c1 INT NOT NULL);
INSERT INTO dbo.Foo (c1) VALUES (1);

ALTER TABLE dbo.Foo ADD CONSTRAINT PK_Foo PRIMARY KEY (c1);
-- Baseline (11)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER DATABASE AreSeaEssEye SET ALLOW_SNAPSHOT_ISOLATION ON;
UPDATE dbo.Foo SET c1 = 2;
-- After enabling RCSI and updating (25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REBUILD WITH (ONLINE = ON);
-- After online rebuild (still 25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REORGANIZE;
-- After reorg (still 25)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

ALTER INDEX PK_Foo ON dbo.Foo REBUILD;
-- After normal rebuild (back to 11)
select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('AreSeaEssEye'), object_id('AreSeaEssEye.dbo.Foo'), null, null, 'DETAILED');

And the results?  As expected:


How can I determine which Innodb table is being written?

SINGLE DATABASE

DATABASE_TO_VIEW=mydb
cd /var/lib/mysql/$(DATABASE_TO_VIEW)
watch "ls -lt | grep ibd$ | head -20"

If you want just the table names

DATABASE_TO_VIEW=mydb
cd /var/lib/mysql/$(DATABASE_TO_VIEW)
watch "ls -lt | grep ibd$ | head -20 | awk '{print $9}' | sed 's/\.ibd//'"

MULTIPLE DATABASES

Create a script called /root/InnoDBChanges.sh with these lines

DATADIR=/var/lib/mysql
IBD1=/tmp/InnoDBFiles.txt
IBD2=/tmp/InnoDBFiles.srt
echo -n > ${IBD1}
cd ${DATADIR}
for DB in `ls -l | grep ^drw | awk '{print $9}' | grep -v mysql | grep -v performance_schema`
do
    FLDR=${DATADIR}/${DB}
    ls -l --time-style="+%s %Y-%m-%d %H:%M:%S" ${FLDR}/*.ibd|awk '{print $6,$7,$8,$9}' >> ${IBD1}
done
sort -nr < ${IBD1} > ${IBD2}
head -32 ${IBD2} | awk '{print $2,$3,$4}'

Then, you can monitor all the databases for changes at the same time

chmod +x /root/InnoDBChanges.sh
watch -n 5 /root/InnoDBChanges.sh

GIVE IT A TRY !!!

CAVEAT

If innodb_file_per_table is disabled, this will not work for you.

You will have to extract the tables from ibdata1. Then, you can try my solution.

STEP #1

Set innodb_file_per_table to 1 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 1

STEP #2

service mysql restart

STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql


Optimizing queries doing filtering on unindexed fields in large MongoDB collection

Analyze what are "most common" queries and create indexes for "top ten" queries, including only two first "columns" (keys). Remember to create only one index for key pair (if you have keys A and B, create only index A,B and not B,A). And keep only one key as "first" in index. (so If you have that A,B index and you should make A,C, make it as C,A).

After that create "single key indexes" for those keys what are NOT created on at the first step. (So single key index for key B, because it's not "first" key in indexes A,B or C,A)

You can use DEX to analyze "missing indexes".


SQL Server 2008 - Run the Check Database Integrity Task with the PHYSICAL_ONLY option?

No, the Maintenance Plan tasks are very limited.

I recommend using this maintenance script, which has an option for PHYSICAL_ONLY.

Also, consider reducing the frequency of running the checks. Is it really necessary to run them every night? This doesn't just use SQL Server resources -- it's also extra wear & tear on your SAN. Just something to think about.


How to join by the most recent preceding date?

I'm afraid that I cannot guarantee 100% Teradata compatibility for my answer as there are, to my knowledge, no Teradata fiddles available. However, I have used PostgreSQL, one of the more standards compliant systems and there are no PostgreSQL specific constructs used in my answer (see the fiddle here), so I think we're good to go!

I glanced at the Teradata documentation for Window functions (LAG and LEAD) and for Common Table Expressions (CTEs) and comparing it with PostgreSQL's syntax (Window functions, CTEs), it all seems fairly vanilla.

First thing I did was CREATE and populate the tables. As an aside, you should avoid using images on StackExchange for the reasons outlined in this link. Use text for DDL and DML or, better yet, provide a fiddle - if you can't find a Teradata one (I looked and couldn't), use something like PostgreSQL which should get you most of the way there.

Another small note, it's not a good idea to name your fields using SQL keywords. Liberal use of the _ (underscore) character is a big help here.

CREATE TABLE price
(
  item_id TEXT NOT NULL,
  item_price SMALLINT NOT NULL,
  change_date DATE NOT NULL
);

INSERT INTO price
VALUES
('A', 249, '2014-09-01'),
('B', 129, '2014-09-01'),
('A', 299, '2014-08-25'),
('B', 139, '2014-09-25');

and

CREATE TABLE purchase
(
  customer_id SMALLINT NOT NULL,
  item_id TEXT NOT NULL,
  purchase_date DATE NOT NULL
);

INSERT INTO purchase
VALUES
(1, 'A', '2014-08-27'), (2, 'A', '2014-09-02');

I then ran the following queries - I've left in extraneous fields which aren't required for the final calculation so that my thought processes could be followed - using LAG and LEAD can be tricky and it's easy to mix up which field you should be comparing with which other one, so my policy is to leave them all in and let the OP (Original Poster - i.e. you) decide which you find useful and want to keep and which you wish to eliminate.

WITH pr_cte AS
(
  SELECT
    p.item_id,
    LAG(p.item_price, 1)
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lag_price,
    p.item_price,
    LEAD(p.item_price, 1)
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lead_price,
    LAG(change_date, 1, '2000-01-01')
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lag_date,
    p.change_date,
    LEAD(p.change_date, 1, '2038-01-01')
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lead_date
  FROM price p
  -- ORDER BY p.item_id, p.change_date -- not allowed in Teradata according to @dnoeth
)
SELECT * FROM pr_cte;

If you don't have a version with CTEs, you can always use a subquery.

Result:

item_id     lag_price   item_price  lead_price       lag_date  change_date  lead_date
      A                        299          249    2000-01-01   2014-08-25  2014-09-01
      A           299          249                 2014-08-25   2014-09-01  2038-01-01
      B                        129          139    2000-01-01   2014-09-01  2014-09-25
      B           129          139                 2014-09-01   2014-09-25  2038-01-01

Notice the use of the defaults 2000-01-01 and 2038-01-01 - I'm implicitly assuming that the prices before the specified dates in the price table go back to 2000 and that prices after will continue till 2038 when *nix time will explode! Obviously, you can change these for values more suited to your use case. I've left it up to you to provide default prices, again as per your use case.

Then, I ran:

WITH pr_cte AS
(
  SELECT
    p.item_id,
    LAG(p.item_price, 1)
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lag_price,
    p.item_price,
    LEAD(p.item_price, 1)
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lead_price,
    LAG(change_date, 1, '2000-01-01')
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lag_date,
    p.change_date,
    LEAD(p.change_date, 1, '2038-01-01')
      OVER (PARTITION BY p.item_id ORDER BY p.change_date) AS lead_date
  FROM price p
  -- ORDER BY p.item_id, p.change_date -- see above and comment by @dnoeth
)
SELECT
  pr.item_id, pr.item_price,
  pur.customer_id, pur.item_id, pur.purchase_date
FROM pr_cte pr
JOIN purchase pur
  ON pr.item_id = pur.item_id
  AND pur.purchase_date >= pr.change_date
  AND pur.purchase_date < pr.lead_date;

Result (again, cull fields as you see fit):

item_id     item_price  customer_id     item_id     purchase_date
      A            299            1           A        2014-08-27
      A            249            2           A        2014-09-02

Which is, AFAICT, the correct answer. You may want to test with edge cases which I may have overlooked - if there's a problem, get back to me. +1 for an interesting and challenging question and welcome to the forum!


Incorrect syntax near '+' in stored procedure sql server

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value.

Consider changing the execution of sp_send_dbmail to this:

SET @usage_data += ' end of data'

exec msdb.dbo.sp_send_dbmail
@profile_name='some_mail_profile',
@recipients=@admin_list,
@subject='Mail from sql server',
@body=@usage_data,
@importance='HIGH' ;

An example with a user defined stored procedure:

CREATE PROCEDURE dbo.bla(@dbname varchar(255))
as
SELECT *
FROM sys.databases
WHERE NAME = @dbname;
GO

DECLARE @dbname varchar(200);
SET @dbname ='Half';

EXEC dbo.bla @dbname+'OtherHalf';

  Msg 102, Level 15, State 1, Line 46 Incorrect syntax near '+'.


Update:
It was related to specific construction of dataset.
if the time was in range from 00:00:00 to 00:59:00 it was presented as follows (examples):

4 ---> 00:04:00
57 ---> 00:57:00

So the previously posted code did not even take such a case into consideration.
The code solving the problem is following:
LEN(ArrTime) == 1 ? "00:0" + ArrTime + ":00" : (LEN(ArrTime) == 2 ? "00:" + ArrTime + ":00" : (LEN(ArrTime) == 3 ? "0" + SUBSTRING(ArrTime,1,1) + ":" + SUBSTRING(ArrTime,2,2) + ":00" : SUBSTRING(ArrTime,1,2) + ":" + SUBSTRING(ArrTime,3,2) + ":00"))

Now I solved the problem and all works fine.
Maybe someone will need such solution ;)


Which server to be master and which one slave database?

In general (until very recent versions), it is better to make the Slave the beefier machine.

Originally, you could easily run queries in parallel on the Master, but they would be run purely sequentially on the Slave.  If the system is busy enough, the Slave could get behind.  Later versions have changed to RBR and added some parallelism on the Slave.

Also, if the Slave is getting 9 times as many queries (assuming of similar length), then it would be good to have a beefier Slave for that reason.


Query optimization when no row is returned

The best index for this query is (u1_id, t)

My initial guess was right, that you have indexes on (t) alone and on (u1_id) alone. You haven't told us the exact EXPLAIN output (which index is used), so the most probable explanation is that mysql is choosing to use one of these existing indexes or none at all (doing a full scan of the table), which yields the not so efficient plan.


How to resolve foreign key references across different databases (iSeries and SQL Server)

If the foreign key constraints don't point to any other table inside of originating database (ie, if SQL Server doesn't point to SQL Server, etc.), then I would drop those foreign keys. They aren't useful.

If they do point to other tables, then perhaps you do have to copy what data is needed just to satisfy the constraint. (Ugh). Either that or re-consider that relationship and possibly drop the foreign key constraint again.

Another thing to look into (at least on DB2 for i) would be informational constraints. I know DB2 LUW had them. They were non-enforced constraints. They helped the optimizer when choosing query plans, but were not enforced as an actual constraint (you could violate them). Of course, a side effect of those is that sometimes you could miss data you wanted due to the informational constraint.

And not sure if SQL Server has something similar or not. I am still learning that platform.


Why Postgres replication write_lag is measured in seconds

does it mean that in case of the db crash last 10 seconds of transactions can be lost?

If the database has a "soft" crash, like power failure, it will go through autorecovery upon startup, and will recover all transactions (other than those possibly lost to synchronous_commit = off) using the log files it found in the pg_wal or pg_xlog directory.  pg_receivewal is irrelevant to this situation.

If the database has a "hard" crash, such that the storage media is permanently lost and you have to recovery from a backup, then you will lose much more than 10 seconds of transactions.  Since pg_receivewal is storing to the files in the same place as the db files, they will all be lost together.  Running pg_receivewal on the same machine as the database and storing the files to the same drive is a pointless exercise, suitable only for testing purposes.

Other than that issue, you are misinterpreting what the field is for.  It is not to measure the data at risk, it is "consistent with the goal of measuring synchronous commit and transaction visibility delays for recent write transactions."


try REPEATABLE READ

on the select statements:

  Specifies that statements cannot read data that has been modified but
  not yet committed by other transactions and that no other transactions
  can modify data that has been read by the current transaction until
  the current transaction completes.


You never ever need the id column in any table. It is a surrogate key which replaces the natural key for pragmatic reasons. Often this is because a narrow, monotonic key gives better performance, or because join clauses become cumbersome with multiple natural key columns.

Some shops have an id on every table as a standard. Consistentcy is good, so that would suggest having the id in this table, too. If this table is the parent of a 1:m relationship it can help performance to have the parent keys directly in the child table without having to follow surrogate keys.

It does take room on disk. If you have a gazillion rows that may be an important performance consideration. This would suggest omitting the id.

Broadly, in my experience it is best to omit the id from intersection tables, and add it later if the model changes.


get stuck trying to copy or backup database in microsoft SQL server

It doesn't matter if you're running SSMS from your laptop or any other computer.

When you successfully connect to a SQL Server instance using SSMS, you have to get the mindset that you are actually on the server that hosts that SQL Server instance.

So, when you use SSMS to connect to 'OldInstance' (located on Server1) and issue commands like BACKUP DATABASE ... TO DISK D:\MSSQL\BACKUP\backuptest.bak, you are referring to the D-drive on Server1 where that SQL Server instance is located.  If no D-drive exists on Server1, the backup command will fail.  However, your backup completed successfully, so the bak file would be on the D-drive of Server1.

The same idea applies to RESTORE DATABASE FROM DISK ....  'DISK' refers to disks available to that specific server (which could be D-driver, E-Drive, etc. or even a file share).

You don't go into much detail about whether OldInstance and NewInstance are hosted on the same server or not.

Somehow, you'll need to get the bak file that you created against OldInstance to a place where NewInstance can access it.

If 'OldInstance' and 'NewInstance' are on the same physical server (or VM),
you might be able to work with your hosting provider to help you access the backup created from OldInstance and restore it to NewInstance.

If OldInstance and NewInstance are on completely different servers and they both can't access a given file share, you will probably have to FTP the backup from OldInstance down to your computer (if that is even possible) and the FTP that backup file to the NewInstance server and issue the RESTORE DATABASE on NewInstance pointing the the backup file you just FTP'd


Using WITH clause with INSERT statement in POSTGRESQL

(Not tested in Postgres) Is this what you need?

INSERT INTO public.table_1(cust_code, issue, status, created_on)
SELECT cust_code, 'New Issue', 'Open', current_timestamp
FROM cust
WHERE cust_id=11


Try adding this to a PropertyGroup in the .sqlproj file directly:

<ModelCollation>1033,CI</ModelCollation>

I had a similar issue when was migrating a .dbproj to a .sqlproj. I had this suggested to me by our team's dba, I don't know exactly what this does..


Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Using OPTION (LOOP JOIN) isn`t suitable since it costs almost 15% more than MERGE JOIN

The cost percentages displayed in showplan output are always optimizer model estimates, even in a post-execution (actual) plan. These costs likely do not reflect actual runtime performance on your particular hardware. The only way to be sure is to test the alternatives with your workload, measuring whichever metrics are most important to you (elapsed time, CPU usage and so on).

In my experience, the optimizer switches from loops join to merge join for foreign key validation far too early. In all but the largest operations, I have found loops join to be preferable. And by "large" there I mean tens of millions of rows at least, certainly not the thousand or so you indicate in the comments to your question.

  In my opinion there must be a way to make the validation phase for the inserted set faster at least for the immutable attribute.

This makes sense in principle, but there is no such logic today within the plan-building logic that foreign key validation uses. The current logic is deliberately very generic and orthogonal to the wider query; specific optimizations complicate testing and increase the likelihood of edge-case bugs.

  Is there any common pattern to overpass the situation above using code structures, additional DDL objects, etc?

Not that I am aware of. The deadlock risk with merge join foreign key validation is well known, with the most widely-used workaround being the OPTION (LOOP JOIN) hint. There is no way to avoid shared locks being taken during foreign key validation, because these are required for correctness, even under row-versioning isolation levels.

There is no better general answer (than the loop join hint) if you want to retain the ability for multiple concurrent processes to add rows to the parent and child tables transactionally. But, if you are willing to serialize the data modifications (not reads), using sp_getapplock is a reliable and simple technique.


Install mysql-workbench failed: Requires: proj, tinyxml, python-paramiko >= 1.15.1

You need install additional packages:

sudo yum install epel-release
sudo yum install proj
sudo yum install tinyxml
sudo yum install libzip

if You have trouble with repo

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum install epel-release-7.noarch.rpm

than

sudo yum -y install mysql-workbench-community


Does logging a SLOW query impact postgres performance?

How is the production system reporting the query times now?  If you are already getting that data, it is questionable that getting it a separate way will be helpful.

But to answer your question, there are two kinds of overhead you will get.

Setting log_min_duration_statement to any non-negative value will cause the system clock to be queried more often.  This should be almost negligible on modern common hardware and kernels, but can be quite slow on older things.

The other overhead is actually writing the log entries.  If the server is very busy, this is probably going to be quite noticeable with log_min_duration_statement=0.  You can probably come up with some threshold greater than 0 will that still log the slow statements, but without logging every single statement.


possible to query a database for an empty jsonb

This is a very bad idea.

not null default '"{}"'::jsonb

That's not storing an empty object. That's storing a JSONB string literal of "{}". You never want that. You normally want to confine it to a specific subtype instead, something like this..

not null default '{}' CHECK (jsonb_typeof(unconfirmed_matrix_prices) = 'object')

You also normally don't want to make it not-null. I'd rather have nullable and prevent empty objects if I need that kind of constraint. That simplifies your query substantially (as seen here).


The answer is no, ANALYZE TABLE privilege is implicit if the user has SELECT and INSERT privilege on the table. There's no way to use access privileges alone to revoke privilege to run ANALYZE TABLE.
Are you sure the client is explicitly running ANALYZE TABLE statements? It's possible they are simply running SHOW TABLE STATUS or quering INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS. If so, then this might help:
SET GLOBAL innodb_stats_on_metadata=OFF;

For InnoDB tables, the work done by ANALYZE TABLE is identical to those metadata queries. Querying metadata triggers the resampling of table statistics, which can be a burden if it's done too frequently. Turning off the automatic update of statistics on any metadata query is useful. We disable that on all our MySQL instances at my company.
I recall years ago when phpMyAdmin added queries to the INFORMATION_SCHEMA to discover metadata, it caused some large hosting providers to crash, because hundreds of users at a time were viewing their databases in phpMyAdmin.
I also ran into this when I developed the database component for the Zend Framework 1.0. I found a workaround: use DESCRIBE instead of INFORMATION_SCHEMA queries. DESCRIBE doesn't trigger the update of table statistics.
If the client is really running the literal statement ANALYZE TABLE, and you can't change the client code (which would be the obvious solution), my only other suggestion is to run an SQL proxy in between the client and the database, and filter out ANALYZE TABLE statements.
For example, here's a blog describing use of ProxySQL to filter out offending queries:
https://www.percona.com/blog/2016/08/23/how-to-stop-an-offending-query-with-proxysql/


What does the DBMS do exactly when i send it a SELECT query regarding the .frm files and database pages? (MySQL)

The .frm file contains nothing more than the schema.  It is looked at when opening the file, then info is kept in RAM.

ibdata1 or the .ibd file is the "tablespace" for the data.  It contains meta info about the data, indexes, etc.  In particular, it points to the root nodes for the indexes of the table.  (Note:  The data is stored based on the PRIMARY KEY, so the "data" acts very much like an "index".)

From the "root" node of a B+Tree, you can drill down to an individual row.

From an individual row, it is possible (when doing a range/index/table scan) to easily find the next/previous row in the B+Tree.

The BTrees are organized in 16KB "blocks", pointed to by a tuple that contains (approximately): tablespace number and block number.  Since a tablespace is a file, the block number is easily translated into a byte offset in that file.  The OS has an extra layer of looking up such a logical address to find a physical address.

Within the Block, there may be, say, 100 "records" that are either data rows or index rows or links down the BTree to other blocks ("nodes").  (The "100" is a Rule of Thumb; in reality, it could be as few as 1, to as many as several thousand.)

To find a particular row (other than the "next" or "previous") within a block, some form of search is done.  (I don't know whether this is a linear scan or a binary search.)

SELECT name from mytable where id>1

Parse the command.
"Open" the table -- either cached (cf table_open_cache) or use .frm (or Data Dictionary in MySQL 8.0)
Figure out how to perform the query
Find index on id.  Let's say it is PRIMARY KEY(id)
Locate root of B+Tree for the PK
Drill down until locating the last (or, since a PK is unique, the only) row with id=1, then start at the 'next' record.
Scan forward, fetching name from each row until you hit the end of the table.


Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?

Per documentation:

  It is possible to access the two component numbers of a point as
  though the point were an array with indexes 0 and 1.

But geometric types are not arrays.
 While the value as a whole can be NULL, parts of a point (or any geometric type) cannot.

All geometric types can be input as string literals and cast to the respective type. None of them accept NULL as part of the input. This results in an error message:

SELECT '(NULL,1)'::point;

  ERROR:  invalid input syntax for type point: "(NULL,1)"
  LINE 1: SELECT '(NULL,1)'::point

On the other hand, if you try passing one or more NULL values to a constructor function, you get NULL for the whole value - which is a bit inconsistent, but still makes sense: if one component is unknown, the whole value is unknown. All of these tests return TRUE:

SELECT point(NULL, NULL) IS NULL
      ,point(1, NULL) IS NULL
      ,point(NULL, 1) IS NULL
      ,point(NULL, 1) IS NULL
      ,circle(point(1, 1), NULL) IS NULL;

This leaves the case where an UPDATE silently ignores an assignment of NULL to pt[0] or pt[1]. This should really do one of two things:

Set the point to NULL - like SELECT point(NULL,1) does.
Raise an exception - like SELECT '(NULL,1)'::point does.

I would say this is a bug and should be reported.


pg_dump and pg_dumpall tools backup create zero size backup file from linux putty terminal

just after being a superuser of the shell, try with supplying superuser postgres as:

    pg_dumpall -f filename.sql -U postgres

or

    pg_dumpall -U postgres > filename.sql

you do not need $bash-rc, just be superuser and run the commands, more info is here


How to convert execution time to a specific format?

I found this article: Working with Dates in Reporting Services

I can't test right now, but try this: =Format(Cdate(Parameters!Date.Value),"ddMMMyy")


The installation guide contains minimum (or recommended) values for each kernel parameter. On Metalink you can find at least one article (including formula) per each kernel parameter.


Selecting ports for Availability Groups Listener and SQL Instances

According to this Microsoft Tech Community post, you can do any of the three port configurations you have listed.

You can choose the port numbers in many different ways.

Specify different port numbers for each instance and a different port number for the Listener.
Specify the same port number for all the instances (as a standard), and a different port number for the Listener.
Specify the same port number for all the instances as well as the Listener. This is possible because the IP address of the SQL Server instance is different from the IP address of the Listener.

It goes on to suggest the following as a possible best practice.

Don't turn on the SQL Browser service
Use the same non-default port number for SQL Server instances as well as for the Listener
Specify the port number whether connecting to a SQL Server instance, or connecting to the AG Listener

This practice may or may not work for all customers, but maintains consistency of the connection strings with or without the SQL Server Browser, with default or non-default port numbers, or whether connecting to the SQL Server instance or the AG Listener.


Should I set a variable in the same line in which it was declared?

It was not possible until SQL Server 2008. Before this version you should declare the variable, and then set its value.

According MS DOCS:

  THIS TOPIC APPLIES TO:
    SQL Server (starting with 2008)
    Azure SQL Database
    Azure SQL Data Warehouse
    Parallel Data Warehouse


Just type it into the "Group" field. Besides the listed options you can type a new group name freely.

It's a setting of pgAdmin, completely independent of the database itself and not stored in the database.

The handling is flexible, but renaming a bunch of "servers" can get tedious.

As to your comment: I repeat what I already wrote in my last answer:

  Basically, each connection allows access to a whole database cluster

But if you follow my instructions over there, you connect to the right database automatically. I suggest, you read the manual about Managing Databases for basic understanding.


re 1) See the chapter "Where to log" in the manual.

http://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

You want to change the log_directory property.

re 2) No, you need to restart Postgres when you change this.


Got fatal error 1236 Slave has more GTIDs than the master has, using the master's SERVER_UUID

You probably have to restore the slave based on the procedure outlined on this blogpost. Once replication is fixed, you may then want to run pt-table-checksum and pt-table-sync to synch data between the two servers.


Inserting a 30 million row table from a linked server

MERGE doesn't seem to be what you want here. You're only inserting new rows with a CREATION_DATE from the past 24 hours, right?

SET NOCOUNT ON;

DECLARE @Yesterday DATE = DATEADD(DAY, -1, GETDATE());

INSERT dbo.ANNOUNCEMENTS(column list)
SELECT column list
  FROM [LINKEDSERVER].[DATABASE].[dbo].[ANNOUNCEMENTS] AS r
  WHERE CREATION_DATE >= @Yesterday
  AND CREATION_DATE < DATEADD(DAY, 1, @Yesterday)
  AND NOT EXISTS
  (SELECT 1 FROM dbo.ANNOUNCEMENTS WHERE ANN_ID = r.ANN_ID);

Of course having useful indexes on CREATION_DATE and/or ANN_ID will help.