Is it a good idea to have an index when the column value changes several times over record lifetime?

If the query against this column is rare, then will you really gain from it? Depending on cardinality, the index might not really help anyway, or even be chosen to satisfy the query. And remember that it has to be maintained 100% of the time.


Most efficient table storage for determining recurrence

For optimal store and later search, I would consider using two tables.

The first table would hold the event/schedule definition (frequency, internal, recurrences etc) - holding 1 line per event
The second table will be maintained by a process running on the first table and calculating->adding the data for future occurrences of each event.

Based on your system requirements you can decide how far ahead the second table should be filled.

This will make it easy to later query a certain events running within a specific time frame.

This solution should be more elegant than trying to calculate the date range on-the-fly, which is more complex and definitely slower in performance.


Sql Server Agent is part of the sysadmin role even tough Sql Server says it's not

After some reading I think I got it.

"NT SERVICE\SQLSERVERAGENT" is a member of the local group "SQLServerSQLAgentUser$RINUS$MSSQLSERVER".
This group has al the right that a sql server agent account needs

"NT SERVICE\SQLSERVERAGENT" is a part of the sql server fixed sysadmin role

"NT SERVICE\SQLSERVERAGENT" can't be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows, but Windows knows they aren't real users. Windows can authenticate them, but they don't have passwords that any human can use. If you run lusrmgr.msc and look at the groups, you will see groups like SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICE\MSSQLSERVER is a member of the group.

The dedicated account that I have created for the agent is set for the windows service

If you connect the dots you will get a chain that explains this. The part thats new for me is that the NT SERVICE\SQLSERVERAGENT can represent the dedicated agent account.

Sources :

http://blogs.msdn.com/b/varun_sharma/archive/2007/08/30/how-to-run-sql-server-agent-and-sql-server-jobs-with-least-privilege-in-sql-server-2005.aspx
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9e6bb2de-8fd0-45de-ab02-d59bbe05f72e/servicedatabase-accounts-nt-servicemssqlserver-nt-servicesqlserveragent-what-are-they-for?forum=sqlsecurity


Result occurs often with fast random select query

This has 8 'fast' ways to pick a random row from a table.

AI with gaps may be the best choice.

Or the 50/10 trick.

Add a FLOAT column may work well for you.

etc.


Stored Procedure With an Optional Parameter Limiting Rows Returned if Specified

The "easiest" way is to parameterize input to TOP. The number I'm using is the maximum value for BIGINT. You'll probably never have more rows than this returned by a query.

USE master
GO

CREATE PROCEDURE dbo.Limiter (@Top BIGINT = 9223372036854775807)
AS
BEGIN

SELECT TOP (@Top) *
FROM sys.databases

END

EXEC dbo.Limiter @Top = 1

EXEC dbo.Limiter

Just be careful, because you can introduce parameter sniffing symptoms here.

If you review the plans for both of these, the TOP operator for both is expecting one row.

Whichever input to top executes first will be cached, unless you recompile, or add an OPTIMIZE FOR hint.

You should also be aware that adding a TOP operator may introduce row goals.


Why does mysqldump require drop command?

By default mysqldump will output commands to drop tables, if they exist. This is why you get the error. It doesn't come from the mysqldump command, but from the mysql command you are piping the commands to. The user doesn't have permission to drop the table.

You can append --skip-add-drop-table to the mysqldump command to remove the DROP TABLE commands, but then you will get errors if the tables exist.

mysqldump parameters


Matching two rows in a single table if two specific keys have different values

You can do a join on Value <> Value for the settings you are looking for.

select S1.Settings as Settings1,
       S1.Value as Value1,
       S2.Settings as Settings2,
       S2.Value as Value2
from Settings as S1
  inner join Settings as S2
    on S1.Value <> S2.Value and
       S1.Settings = 'clientvisits' and
       S2.Settings = 'displayvisits'

Result:

Settings1            Value1               Settings2            Value2
-------------------- -------------------- -------------------- --------------------
clientvisits         2                    displayvisits        4


The backup program is trying to update msdb database but cannot because either the database has only read permissions or the user running the program does not have permissions to write to the database.


How can I write to replicated environments without impacting production?

Wouldn't simple backup/restore be enough? Just restore the production backup on all your dev servers and you should be fine. Btw. Sql server does not allow writes to replica servers as it would corrupt the data and it would no longer be able to figure out what should be updated.


SQL Server 2016 deprecation of DML keyword ROWGUIDCOL

I think you are misreading the deprecation notice. Specifically, you seem to be confusing DML (Data Modification Language) with DDL (Data Definition Language). What is being deprecated is the ability to use ROWGUIDCOL in INSERT / UPDATE / DELETE / MERGE statements:

-- DDL statement
CREATE TABLE #Test
(
  ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT (NEWID()) ROWGUIDCOL,
  SomeColumn INT NULL
);

INSERT INTO #Test ([SomeColumn]) VALUES (1), (100);

SELECT * FROM #Test;

DECLARE @RowID UNIQUEIDENTIFIER;
SELECT TOP 1 @RowID = tbl.[ID]
FROM   #Test tbl;

SELECT @RowID AS [RowToUpdate];

-- DML statement
UPDATE tmp
SET    tmp.[SomeColumn] += 2
FROM   #Test tmp
WHERE  ROWGUIDCOL = @RowID;

SELECT * FROM #Test;

If you run the above, only one row will be updated. This example shows that ROWGUIDCOL when used in a DML statement (not in a DDL statement) is dynamically replaced with the actual column marked as ROWGUIDCOL. And this is, in fact, why you are able (or required?) to mark a column as ROWGUIDCOL in a DDL statement: so that you (and other processes) can find it later :-).


you just need add Colon character (:) before new keyword same as below query:

create or replace TRIGGER TRG_ENV_CONFIG_INSERT
AFTER INSERT
  ON ENV_CONFIG
  FOR EACH ROW
BEGIN
  INSERT INTO ENV_CONFIG_HIST (
    PARAM_NAME
    ,PARAM_DESCR
    ,PARAM_VALUE
  ) VALUES (
    :new.PARAM_NAME
   ,:new.PARAM_DESCR
   ,:new.PARAM_VALUE
 );
END;


How can I configure a connection string in SSRS 2012 using named pipes?

You'll need to indicate that you want to use the named pipes provider in the connection string.

Consider this VB.Net code that connects to my local SQL Server 2012 instance via Named Pipes:

Module Module1

    Sub Main()
        Dim cb As New SqlClient.SqlConnectionStringBuilder With {
            .DataSource = "\\.\pipe\MSSQL$MV2012\sql\query",
            .NetworkLibrary = "dbnmpntw ",
            .IntegratedSecurity = True
            }
        Using conn As New SqlClient.SqlConnection(cb.ConnectionString)
            conn.Open()
            Using cmd As SqlClient.SqlCommand = conn.CreateCommand
                cmd.CommandText = "SELECT ServerName = @@SERVERNAME;"
                cmd.CommandType = CommandType.Text
                Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader
                While reader.Read
                    Console.WriteLine(reader.GetString(reader.GetOrdinal("ServerName")))
                End While
            End Using
        End Using
    End Sub

End Module

The .NetWorkLibrary = "dbnmpntw" indicates that I'm going to use the Named Pipes Provider, as detailed in this Microsoft Docs page.

I validated that the pipe name, \\.\pipe\MSSQL$MV2012\sql\query, is correct by looking at the SQL Server Configuration Manager under "SQL Server Network Configuration -> Protocols for MV2012 -> Named Pipes Properties".

I'd say your connection string needs to be (I've wrapped the text simply for clarity):

Data Source=\\.\pipe\MSSQL$MSSQLSERVER02\sql\query;
Network Library=dbnmpntw;
Initial Catalog=cleverdataout

FYI, the \\. piece indicates the name of the server where SQL Server is installed.  In this example, that indicates the localhost server.  If you are connecting via named pipes remotely, you'd need to replace the . with the name of the server.  So, if the server was MySQLServer, then you'd need the pipe name to be:  \\MySQLServer\pipe\MSSQL$MSSQLSERVER02\sql\query.

The MSSQL$MSSQLSERVER02 part of the named pipe in your question indicates that you are attempting to connect to a SQL Server Named Instance named MSSQLSERVER02.  If your SQL Server is installed as the default instance, i.e. with no name, then you'd need to specify the pipe like:

\\MySQLServer\pipe\sql\query

Note, for this to work you'll need to enable the File and Printer Sharing (SMB-In) Windows firewall rule that allows inbound TCP traffic via port 445 at the server.


You are specifically asking for the OrderNumber, CustomerId and OrderDate columns.  If you want the customers name you have to include that in your field list.

SELECT OrderTable.OrderNumber, OrderTable.CustomerID, OrderTable.OrderDate,
     CustomerTable.Name
FROM [OrderTable] INNER JOIN [CustomerTable]
    ON CustomerTable.CustomerID = OrderTable.CustomerID
WHERE OrderTable.CustomerID = 1

A basic SELECT statement looks something like this:

SELECT FieldList
FROM TableOne
INNER JOIN TableTwo
    ON JoinCondition between TableOne and TableTwo
WHERE [Conditions that restrict returned rows]

You will only be returned those columns that you explicitly list in the FieldList unless you use a * (returns all rows) or TableName.* (returns all rows for TableName).

Note: It is recommended that you list out the fields you want back explicitly for a number of reasons including:

Performance : Pulling one (or more) columns that you actually need requires less data transferred than pulling all of them.
Code stability : If you add a column to the table you will get back columns in your query you may not want and could break your code.


Conversion failed when converting date and/or time from character string

@STRTEMP_DATE is a datetime variable and @dateforaftersixmonth is a
  string. U_BEGDA and U_ENDDA are datetime fields and U_PYMON is
  varchar.

The error indicates that there is an implicit conversion from string to date/time. As you stated the variables and fields here, there is no implicit conversion. Since I trust the error message more than I trust you, I must conclude that you did not stated the variable and column types correctly and at least one of the following is true:

@STRTEMP_DATE is a string and U_BEGDA  or U_ENDDA is a date/time column
@STRTEMP_DATE is a date/time variable and U_ENDDA is a string column
@dateforaftersixmonth is a string and U_PYMON is a date/time columns
@dateforaftersixmonth is a date/time and U_PYMON is a string column

One of the above must be true for the conversion to occur and for the erorr message to appear. My money is on the @dateforaftersixmonth being a datetime and U_PYMON being a string (varchar or nvarchar). This also explains the aparent randomness of the error: it occurs only when you scan a range that contains values that cannot be converted.

Depending on which of the above cases happens, the solution is to appropiately modify the parameter/variable or the column to be of type datetime when storing dates/times.


Why this result in MySQL is different than Excel or Calc?

This is due to MySQL converting calculations to float which is at best an approximation of a number.  If you round the solution then 4415999.9558 rounded to 0 decimal places is 4416000.

In MS SQL the calculation results in 0 because the result is an integer and the steps are

10 /110 = 0
0 * 48576000 = 0

however

Select 1.0 * 10 /110 *48576000
4415995.584000

If you are explicit about the data types you get the correct answer i.e.

declare @a  decimal(18,10) = 10;
declare @b  decimal(18,10) = 110;
declare @c  decimal(18,10) = 48576000;
select @a /@b *@c
4416000.00000000000


which sql server edition is good for practice

The 180 day evaluation version from Microsoft is Enterprise Edition.  AFAIK there is no ability to "trial" Standard edition from Microsoft outside of a licensing agreement you might have.

From the point of view of overhead between editions, there's negligible difference.

If you want something longer than 180 days, get the developer edition as its feature complete compared to Enterprise but only $50 from Amazon


Query this table in order for you to see the platforms to which rman can convert your

SYS@EMR> select * from V$DB_TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME                                                                                ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
          7 Microsoft Windows IA (32-bit)                                                                         Little
         10 Linux IA (32-bit)                                                                                Little
          5 HP Tru64 UNIX                                                                                Little
         11 Linux IA (64-bit)                                                                                Little
         15 HP Open VMS                                                                                Little
          8 Microsoft Windows IA (64-bit)                                                                         Little
         13 Linux x86 64-bit                                                                                Little
         12 Microsoft Windows x86 64-bit                                                                          Little
         17 Solaris Operating System (x86)                                                                        Little
         19 HP IA Open VMS                                                                                Little
         20 Solaris Operating System (x86-64)                                                                     Little
         21 Apple Mac OS (x86-64)                                                                                Little

Step by Step restore

Connect to the target database using rman and backup the database -
Source Node/ System
copy following files to the Destination node ( backup pieces ,
control file backup and parameter file)
Perform the modificaiton on destionation node to parameter file -
PFILE
Once the pfile is ready, set the environment parameter and connect to
rman
connect to target - start up the db no mount mode
now restore the controlfile from the backup on destination node
(which copied from source db )

Example :

restore controlfile from '/backup/file name';

once the restore is complete - mount the db.

now main step is backup prices which you copied from the source db to destination db catalog themcatalog the backup pieces.
Now, you need to recover the db - find he sequence number

list backup of archivelog all;
Rename the Redologfiles

alter database rename file
Now final step is you need to recover the db to the last sequence number

Execute this series of statements:
set until sequence 123456; -- sequence which you go previous.
    set newname for datafile 1 to '/node2/database/db1/system01.dbf'; -- data file paths modified

restore database;
switch datafile all;
recover database;
alter database open resetlogs;

2 As a second approach this can be done using the Oracle Transportable Database  - follow the link for great article form IBM guys !


Which databases does `mysqlpump` ignore by default?

The easiest way to know is to test, on Percona Server for MySQL 5.7.28 (latest as of this writing) it shows that mysqlpump includes the mysql schema in the backup:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mytest` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;


What do the columns in SYS.USER$ represent?

Test cases below show:

CTIME is the date the user was created.
LTIME is the date the user was last locked. (Note that it doesn't get NULLed when you unlock the user).
PTIME is the date the password was last changed.
LCOUNT is the number of failed logins.

Here's the test code:

SQL> create user philtest identified by philtest;

User created.

SQL> alter session set nls_date_format='HH24:MI:SS DD/MM/YYYY';

Session altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012                     14:21:51 26/04/2012

SQL> alter user philtest account lock;

User altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012 14:23:15 26/04/2012 14:21:51 26/04/2012

SQL> alter user philtest identified by foofoo;

User altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012 14:23:15 26/04/2012 14:27:47 26/04/2012

SQL>

This isn't documented in the Oracle docs, as it's an internal data dictionary view. However, the DBA_%/ALL_%/USER_% views that sit on top of the SYS.% views are documented here.

I often find that doing a DESCribe on one of the DBA_% views gives a good indication of what the underlying columns represent. The DBA_% views are often commented too.


You will need to assign ownership of the table to the group the two users have in common.

Demo:

Setup:

CREATE ROLE thegroup;
CREATE USER user1 IN ROLE thegroup;
CREATE USER user2 IN ROLE thegroup;
CREATE TABLE t1 ( x integer not null );
ALTER TABLE t1 OWNER TO user1;

Without further changes, here's what happens if user2 tries to add an index on t1.x:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
ERROR:  must be owner of relation t1

The solution is to grant the table ownership to the shared role. As user1 or a superuser:

ALTER TABLE t1 OWNER TO thegroup;

now:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
CREATE INDEX
regress=>


Optimising file shrink and index reorganization operations

Move your indexes into a new pre-sized (but fairly small) filegroup. Moving them will involve a rebuild, and your file will be small. If you do this to all the objects in your current filegroup you will be able to remove it completely, keeping only the new, tight, filegroup, with only unfragmented indexes.

And please stop shrinking your database files. If you need the space on a temporary basis (eg, for loading data prior to transformation) and need to reclaim it after for other uses, then consider using a different database for that temporary stuff, so that you can drop that database once your loading (or whatever) is done.


34 tables is a lot.

Over-nomalization
Many-to-many mappings that should be 1:many
Excessive use of "added by"
Use minimal datatypes

Some examples:

Because of the nested nature of districts-cities-states, and the fact that city and state names "never" change, there is no need to carry that normalization past district (or maybe even area).

I suggest it is OK to use the 2-letter state abbreviations and not have the full name.  Note: that should be CHAR(2) CHARACTER SET ascii to minimize the size (2 bytes).

Don't bother 'normalizing' simple things like keywords, course types, etc.  When there is a relatively small and static number of such things, consider using ENUM for them.

An "accreditation" is for one institute, correct?  It is not a blanket for many institutes?  So that is really 1:many, not many:many.  Hence, the intermediate table is unnecessary.  (Some relations should continue to be many:many.)

For dozens of items, use TINYINT UNSIGNED (1 bytes), not INT UNSIGNED (4 bytes). For hundreds, maybe thousands, use SMALLINT UNSIGNED (2 bytes).

What is a type_id?


What to do when the active log directory is full?

the upper limit of log file size, combined with the upper limit of the number of log files (logprimary + logsecond), gives an upper limit of 1024 GB of active log space.

You can increase the size of the log file size setting by issuing the command:

db2 UPDATE db cfg for <databaseName> using LOGFILSIZ <new_value>


Stuck REDO Thread of an availability group

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler
to migrate a worker from the runnable queue of another scheduler on
the same NUMA node and immediately resume the task of the migrated
worker. This enhancement provides more balanced CPU usage and reduces
the amount of time long-running tasks spend in the runnable queue.
A long-running task that is enabled for worker migration is no longer
bound to a fixed scheduler. Instead, it will frequently move across
schedulers within the same NUMA node which naturally results in less
loaded schedulers. Together with the existing load factor mechanism,
worker migration provides SQL Server with an enriched solution for
balanced CPU usage.

Source
Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group
parallel redo tasks are enabled for worker migration to address a
commonly observed scheduler contention issue among redo tasks on
secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.
Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on
secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.
One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .
Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel.


Using SQL Server Import\Export Wizard with Ms. Access as a source DB

There are several good options for migrating data from Microsoft Access into SQL Server:

SQL Server Migration Assistant

SQL Server Migration Assistant for Microsoft Access provides a reliable method for migrating database objects from Access into SQL Server.  Optionally, it provides a way to link SQL Server tables into an Access back-end to enable you to continue to use an Access-based front-end with the SQL Server back-end data.
Import/Export Data Wizard in SQL Server Management Studio

Importing data via SQL Server Management Studio is a straight-forward method for copying data from an Access database into a SQL Server database.  Running the wizard allows you to pick an Access database, then choose individual objects in Access to import into SQL Server.  Tables and Views (queries) can be copied from Access into SQL Server.  The source data is not modified in any way.
Microsoft Access Upsizing Wizard.

The Upsizing Wizard moves database objects and the data they contain from an Access database to a new or existing SQL Server database.

There are three ways to use the Upsizing Wizard:

Upsize all database objects from an Access database to an Access project so that you can create a client/server application. This approach requires some additional application changes and modification to code and complex queries.
Upsize only data or data definitions from an Access database to a SQL Server database.
Create an Access database front-end to a SQL Server database back-end so that you can create a front-end/back-end application. This approach requires very little application modification since the code is still using the Access database engine (ACE).

If you are simply importing one or two tables, it is likely easiest to use the Import Data wizard in SSMS (option 2, above), whereas if you are attempting to migrate an entire application's data into SQL Server, I'd recommend using the SQL Server Migration Assistant (option 1, above).

For very large databases, migration can take a significant amount of time, and can use significant temporary space in SQL Server; ensure you have enough free space, at least temporarily, on the SQL Server to store 2 copies of your Access data.  You'll want to plan to prevent users from updating the data in the Access database while doing the migration, and directly afterwards, in order to ensure the data stored in SQL Server is the most current version.  Do this migration during a period of planned outage.  Plan the outage based on tests you perform of this migration in a test environment.


I always find views and subqueries easier to read and develop mainly as you can test and edit one section at a time. Depending on the number of messages and your system it may be a little slower though I doubt it. In this case I am going to use a union query.

I would also check first and if the limits are not exceeded then perform the insert (unless you doing millions of messages).
Create a view of the total number of sent messages

 CREATE view vwMessagesSent AS
 SELECT *
 FROM `contact`
 WHERE date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))

This reduces the overall complexity of

SELECT Least(4,4 - count(id)) as sendLimit
FROM `vwMessagesSent`
WHERE user_id = ?
UNION ALL
SELECT Least(2,
       IF(
          (SELECT COUNT(id) FROM `vwMessagesSent` WHERE receiver_id = ? AND  user_id = ?)=1, 4, 2)
       )  - (SELECT COUNT(id) FROM `vwMessagesSent` WHERE user_id = ? AND receiver_id = ?)
)
FROM `vwMessagesSent`
WHERE user_id = ? AND receiver_id = ?
order by SendLimit
LIMIT 1

Note: Not tested - should be close to what you are after

We are using a few interesting SQL items out of the box.
Least takes the lowest number of a collection of numbers. It allows us to only return the number of messages the user is allowed to send and for you to warn the user that the insert/message send was not going to be undertaken as they are over quota.

UNION allows you to merge two queries and we use the ALL to make sure we have multiple rows (rather than using the distinct key

LIMIT 1 We sort the order of the two rows returned with the minimum number available to be sent and then only return that row.

Hope this helps. Happy to clarify or fix the main SQL and I would be looking at using at a stored procedure to improve the logic even further.


Number of active connections and remaining connections

This SQL will help you

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal
from
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3

Result:

max_conn | used | res_for_super | res_for_normal
---------+------+---------------+----------------
  100    |    2 |             3 |             95
(1 row)

You can put this in shell:

#!/bin/bash
for (( c=1; c<=3600; c++ ))
do
     gsql -U pgdba -W pgdba -p 6432 -c "sql" >> /home/pgdba/res_data.log
     sleep 1  # once per second
done

or you can record the results into a table, then execute

postgres=# copy restbl to '/home/pgdba/res.csv' csv header;

to get result csv file.


replication monitor: why is current average performance poor?

Personally I ignore the excellent, poor, etc.  As long as the number of records pending isn't very high, and the amount of time to catch up isn't very high I consider everything is fine.  That front screen monitor is pretty misleading most of the time.


Is there a setting to suppress C-Style comments appearing as DOC in Oracle Output?

SET DOC[UMENT] OFF

At least in 9i. I can't make these comments display at all in 10g, so I think they've made this option even more obsolete - it still controls explicit DOC blocks, but apparently not comment blocks...


Shortest method to upload database to server

Personally, I'd do it using a compressed backup file.

Install an (Express will do) instance of SQL Server locally and run the script. Then, back it up and compress the backup file with a good utility (7-Zip/WinRAR compress very well and can create self-extracting archives). Transfer the compressed backup file (e.g. FTP), uncompress and restore the backup.

You can also compress just the 3GB textual script file and compare to see which is smaller to transfer.


Migrating ssrs reports between different domains

We have had some good experiences using ReportSync to copy selected folders from one report server to another.

https://gallery.technet.microsoft.com/SSRS-How-to-Import-Export-28660d6e

The tool is free and straighforward to use.


How to select duplicates into new columns?

Subqueries are the easiest solution for the problem, I think.

You can so a simple select or create a new table with CREATE TABLE ... FROM SELECT ...:

select product_id,
    (select image_url from images as images_1 where images_1.product_id = images_o.product_id limit 0, 1) as image_url_1,
    (select image_url from images as images_2 where images_2.product_id = images_o.product_id limit 1, 1) as image_url_2,
    (select image_url from images as images_3 where images_3.product_id = images_o.product_id limit 2, 1) as image_url_3
    from images as images_o
    group by product_id
    limit 10

Try it here: http://sqlfiddle.com/#!9/bc915d/1/0


Fastest query to filter product by countries

Since wrote you won't mind changing the design... Change the design.
Instead of that complicated logic with the flag and implicit inclusions/exclusions, just have a table that maps products to countries. Let's simply call it productcountry. If, and only if, a record for a country exists for a product in productcountry the product is available in that country.
The query then just uses some JOINs and a WHERE.
SELECT DISTINCT
       p.*
       FROM product p
            INNER JOIN productcountry pc
                       ON pc.productid = p.productid
            INNER JOIN usercountry uc
                       ON uc.countrycode = pc.countrycode
       WHERE uc.userid = @userid;

For that you should try indexes on usercountry (userid, countrycode), productcountry (countrycode, productid) and product (id).
It will need a DISTINCT though because a product can be available in more than one country a user uses. (I silently assume here that the products are distinct by themselves, i.e. they have a key.) You can experiment, if you yield a better plan, if you instead use EXISTS and a correlated subquery.
SELECT p.*
       FROM product p
       WHERE EXISTS (SELECT *
                            FROM productcountry pc
                                 INNER JOIN usercountry uc
                                            ON uc.countrycode = pc.countrycode
                            WHERE uc.userid = @userid
                                  AND pc.productid = p.productid);

Here you can try indexes on usercountry (userid, countrycode) and productcountry (productid, countrycode).


SQL Server Management Studio backup not shown when restoring

Normally when you want to restore a database from a different server,  you're looking to create a device backup to a network drive, and restore from that.

The way the restore from database interface works is by querying the msdb database for recent backups, to see what you can restore. Accordingly you should be using a device backup:

  If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. In this case, select Device to manually specify the file or device to restore.

As the msdb database on server 2 will have no knowledge of the backups taken on server 1.


advice on modeling schema to have better access to foreign entity

The following schema may be better to match your requirement.

{
    name: String,
    username: String,
    address: {
        street: String,
        city: String,
        state: String,
        zip: String,
    }
}

Try to avoid having multiple collections like a relational database. Joining is a costly operation in NoSQL databases.

References:

6 Rules of Thumb for MongoDB Schema Design: Part 1

6 Rules of Thumb for MongoDB Schema Design: Part 2

6 Rules of Thumb for MongoDB Schema Design: Part 3


The mysql client has the init-command option, which does what you want, I think.

From the manual:

  --init-command=str

  SQL statement to execute after connecting to the server. If
  auto-reconnect is enabled, the statement is executed again after
  reconnection occurs.


The problems I was experiencing:

Synchronous secondary replica falling behind semi-permanently
Huge log backups

These were solved by enabling trace flag 3459. In my case it was extremely easy to see that the flag immediately fixed the wait type of parallel_redo_flow_control
dirty_page_table_lock
parallel_drain_redo_worker and showed a rapid decrease in size of the redo queue.

I wonder why, in the bug report, Microsoft calls this "assertion": https://support.microsoft.com/en-us/help/3200975/fix-assertion-occurs-when-you-use-parallel-redo-in-a-secondary-replica

Credit to Jason AKA CirqueDeSQLeil from SQLServerCentral.com
https://www.sqlservercentral.com/Forums/1871286/AlwaysOn-Missing-Redo-Thread


You cannot update a field in the underlying table of a form when the control is bound to a function (a calculation in this case). However, you can run an update query, use VBA to update the field, use VBA to write the calculation to a bound control, or use a calculated field (MS Access 2010 and later versions only).

However, you must ask yourself if you really want to store a calculated result. It will depend on whether qty and expenses change over time and whether you need a fixed result for the current values. If qty and expenses will always be the same, just use a query to show the result of the calculation. For example:

SELECT t1.qty * t2.expenses As Total
FROM table1 t1
INNER JOIN table2 t2
ON t1.ID = t2.MatchedID


wordpress table is crashed and should be repaired

It seems that you have network connectivity issues.

Assuming the SAN of your VPS is connected using the same network (very likely), the database server could lose connection to its drives intermittently. That can cause all kinds of consistency issues within the databases.

As this is a hosted environment, you cannot diagnose or fix network issues yourself. Talk to your hosting provider and have them look at it.


Cannot Decrypt the encrypted columns from the database backup on local machine

Looks like you did not encrypt the encryption key with the database master key. There has to be a complete chain from the service master key to the encryption key, usually is like this:

service master key -> database master key -> [optional certificate private key] -> encryption symmetric key

You are going to have to open the encryption key using the original password and then add the missing master key encryption.


The server principal "" is not able to access the database "" under the current security context

I can't create any comment yet. I don't have the required reputation. So I will post an answer!!!

You can view orphanage users in a database using:

exec sp_change_users_login 'report'

And it also might be because the default database of you login try:

exec sp_helplogins 'Login1'

You can change the default db with:

ALTER LOGIN Login1 WITH DEFAULT_DATABASE=test1


Increase Max Memory setting on a SQL Server Production

I have a slightly different opinion.

If you are not experiencing memory pressure and your application is just working fine, why introduce an unknown variable (in your case - increasing sql server max memory) ? You have not tested this change, so its a risk.

If you have more bandwidth on your server, you can look into consolidating more instances on your server.

  Can something suddenly break (or make a performance degradation) if SQL server suddenly has so much more memory to play with ?

Yes, and I faced a perf degradation issue with PROD having more memory and using TF 2335 fixed the issue.

you can read Paul's answer for more details but the gist is :

  The potential size of the buffer pool also affects the optimizer's cost model for data access.


Make a column for each count(*) in a day of the week

Here's one way. Assuming this table and sample data:

USE tempdb;
GO
CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE);

INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'),
  ('t0001','Tod','20150629'),('t0001','Tod','20150628'),('t0001','Tod','20150628'),
  ('b0002','Ben','20150629'),('b0002','Ben','20150629'),('b0002','Ben','20150628');

Then with a variable or parameter to dictate the week you care about:

DECLARE @dt DATE = '20150702';

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt(dt) AS
(
  SELECT TOP (7) dt = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @dt)
  FROM sys.all_objects
  ORDER BY [object_id]
),
x AS
(
  SELECT
    s.Name, dt.dt,
    dd = DATEDIFF(DAY, @dt, dt.dt),
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt
  LEFT OUTER JOIN dbo.splunge AS s
  ON s.Date = dt.dt
  GROUP BY Grouping SETS((s.Name),(s.Name,dt.dt))
)
SELECT
  Name,
  [S] = COALESCE(MAX(CASE dd WHEN 0 THEN c END), '-'),
  [M] = COALESCE(MAX(CASE dd WHEN 1 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 2 THEN c END), '-'),
  [W] = COALESCE(MAX(CASE dd WHEN 3 THEN c END), '-'),
  [T] = COALESCE(MAX(CASE dd WHEN 4 THEN c END), '-'),
  [F] = COALESCE(MAX(CASE dd WHEN 5 THEN c END), '-'),
  [S] = COALESCE(MAX(CASE dd WHEN 6 THEN c END), '-'),
  TOTAL = MAX(CASE WHEN dt IS NULL THEN c END)
FROM x
WHERE Name IS NOT NULL
GROUP BY Name;

Results:

Name    S   M   T   W   T   F   S   TOTAL
------  --- --- --- --- --- --- --- -----
Ben     1   2   -   -   -   -   -   3
Tod     2   5   -   -   -   -   -   7

Another way:

DECLARE @dt DATE = '20150702';

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT Name, dw = DATEPART(WEEKDAY, Date)
  FROM dbo.splunge
  WHERE Date >= @dt AND Date < DATEADD(DAY, 7, @dt)
),
x AS
(
  SELECT Name, dw = COALESCE(dw, 8),
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt
  GROUP BY GROUPING SETS((Name),(Name,dw))
)
SELECT Name,
  [S] = COALESCE([1],'-'),
  [M] = COALESCE([2],'-'),
  [T] = COALESCE([3],'-'),
  [W] = COALESCE([4],'-'),
  [T] = COALESCE([5],'-'),
  [F] = COALESCE([6],'-'),
  [S] = COALESCE([7],'-'),
  TOTAL = [8]
FROM x
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt;

Don't forget to clean up:

DROP TABLE dbo.splunge;


What's the default password for SYSTEM in Amazon Oracle RDS?

The SYS user, SYSTEM user, and other administrative accounts are locked and cannot be used
  More

The Amazon documentation for Oracle on Amazon RDS says you have to use master user which has the DBA privilege and other users like SYS and SYSTEM are locked by default and cannot be used.

SYSDBA privilege is not available for customer in Amazon RDS so Amazon EC2 might be a better choice for you and can have full control over the database and high privileged user like SYS and SYSTEM user.

Useful links:
Best Practices for Running Oracle Database on Amazon Web Services
Common DBA Tasks for Oracle DB Instances
Oracle on Amazon RDS


How to influence files (pre)allocation steps (max growing value) in MongoDB 3.4 with WiredTiger

WiredTiger does not preallocate data files. Only journal files are pre-allocated (at 100MB per pre-allocation), see journaling process.

The disk exhaustion you're seeing is likely due to these journal files. Journal files are optimized for quick writes, and their contents will be persisted in a more permanent manner on a WiredTiger checkpoint which occurs every ~60 seconds. Typically, once persisted in data files, you should see less disk used, since now the journal files can be removed. Note that the journal files are only removed by WiredTiger following a checkpoint and at no other times.

I would suggest you to allow for some temporary extra space during this restore/data load process. Once the database is in a steady state, you would see a more accurate size.

The power-of-2 preallocation is specific to MMAPv1 storage engine, which is deprecated. This is not relevant to WiredTiger.


how to perform regular backups of SQL Express 2008 R2 databases

I've found several ways to get the job done:

script + Windows Scheduler on every machine;
script + linked servers;
third-party software;
some other option

As my company is very intolerant to anything that implies extra bills I had 3 options left. Some kind of script plus Windows Scheduler seemed the most natural solution so I opted for it.

For scripting I chose Powershell as it is able to get every piece of the task done including sending e-mail notifications. I implemented this strategy.

This is where problems began. In fact, the main source of evil was the Scheduler On one machine some files were missing so the Scheduler did not work, on another machine  problems with permissions, on third machine the OS is too old and so on... So 10-20 machines out of 150 were not being backed up.

Running task every 15 minutes via Linked Servers was not a very good idea to me. At this moment I was solving other issue and realized that SQL Server   sp_procoption might be of help in my situation. A procedure that starts at SQL Server startup is a really good candidate for doing statistics and index maintenance, checking for corruption and backing up every morning and every 15 minutes subsequently.

I used Ola Hallengren's maintenance script to reindex, update stats and checkdb. Of course modified a little to meet my company's needs. It also writes data on backups and checks to a specific table which can be queried via Central Management Server for analysis.

Now the amount of DB's not backed up is no more than 2-3. And the only reason is that machines were not turned off.


Upgrading to SQL Server 2012 from SQL Server 2008 R2

Benchmarks aren't done as frequently as before, at least not publicly. This is true across the industry, not just SQL Server.

Internally, performance improvements are always pursued for each major release, though it may target some specific areas or something broader. One area that still gets somewhat regular updates are TPC-H benchmarks. Check out the TPC website and filter on SQL Server.

That said, you really should get your customer to reconsider their business preference on "upgrading" to SQL Server 2012. Mainstream support for 2012 SP3 ends in July 2017. See the Microsoft product support lifecycle search page for details.

That means they're investing in the upgrade for a product that is out of mainstream support within a year. The business might think it's ok, but make sure they understand the impact. Definitions of mainstream and extended support are documented in the Microsoft Business, Developer and Desktop Operating Systems Policy.

Fixes aside, some industries have specific requirements about only using products that are still in mainstream support so make sure your client is not affected there.

Try to get them on 2016 or at least 2014. You'd be doing your client a huge service.


How to optimize query with ORDER and LIMIT?

While the records under branch_id = '12345678' and each of the two providers are sorted in the order you want, they aren't in a sorted order once they've been pulled out (because you've got two sets that are pushed together).

You might get an improved sort if you pulled the top 13 records for the branch_id and each of the providers separately. These should already be in the order you want. Then, UNION ALL the two result sets and grab the top thirteen from that. Since they're already sorted in the right order, the engine may realize that and do something faster than a HEAP SORT.

NOTE: Depending on the total number of rows involved, the simple expedient of grabbing the top 13 rows for each branch and provider may speed things up tremendously, in that the heap sort has to sort through all the possible matches to find the top 13, whereas we've limited the possible row count to 26 at most.

Of course, this doesn't scale well. If the number of branches and providers in the query is ultimately variable, then you would either need to write a new version of the query each time the number of either of those changed, or build some really hairy dynamic SQL. If that's the case, then trying to further limit the pool of candidate rows should help; consider putting some sort of date range into the query.

UPDATE: joanolo kindly put together the actual code for this, with a data sample of 900 records; here's the dbfiddle.uk link. It confirmed that the engine did pick something other than a heap sort - specifically, a quicksort. However, as joanolo notes, you need a reasonably large number of records for this to make a difference; with 900, it's faster, but not that much.


Use a SP to Archive/Revision related records in several related tables?

Creating such a stored procedure is probably going to be complex and error prone. That's why there are temporal databases or temporal features in a database. If you really wanted to do this with a stored procedure, you can have a duplicate schema with a version number column added to all tables.

That way, on each write operation, you can first call the stored procedure which does an INSERT INTO.... SELECT using the same WHERE clause as your write operation. The stored procedure is best initiated by the app which runs the stored procedure before the actual write (UPDATE/DELETE) statement.

If you use triggers, it can be very tricky just figuring out which table(s) to host the trigger and making sure you don't get a trigger storm.

Far easier path is to implement something like Temporal tables in SQL Server 2016: https://msdn.microsoft.com/en-us/library/dn935015.aspx. Takes a bit of work to setup but it's only somewhat tedious work, not very complex work. If you want to keep this history around for a long time, you can even stretch the temporal history table so you don't have to deal with administration and storage challenges with a massive history table.


This may not be a suitable answer, but would achieve your goal...

What about, for the time you want it offline, renaming the database? This way, whatever application you have accessing the data would fail to connect to the database.

Moving the database can be achieved by

mysqldump -u user -p database -c > whatever.sql

Then import whatever.sql into another server, finally dropping the original DB.


ORA-13249: SDO_NN cannot be evaluated without using index

Got same error message, but without DB-Link. http://www.orafaq.com/forum/t/154844/ helped (replace c and s with your table aliases and spatial_index with your spatial index name):

/*+ LEADING(c) USE_NL(c s) INDEX(s spatial_index)*/


Change owner of relation in PostgreSQL database

Community wiki answer:

A generic reason that affects those new to psql is forgetting the semicolon at the end of the command, so that it doesn't get executed. Otherwise your command looks good and should do what you want.


You could create a view that looks like something like this:

create view test_view
as
select a,b,c,d,
       case
          when a = greatest(a,b,c,d) then 'a'
          when b = greatest(a,b,c,d) then 'b'
          when c = greatest(a,b,c,d) then 'c'
          when d = greatest(a,b,c,d) then 'd'
       end
from test;

Note that the above will not work correctly if any of the columns contains a null value because greatest() will return null then. If you need to handle that, you need to use e.g.:

when a = greatest(coalesce(a,0),coalesce(b,0),coalesce(c,0),coalesce(d,0)) then 'a'

When two columns have the same highest value this would display the "first" one. So for (2,2,4,4) it would return c


Categorize age ranges and display in one column

Instead of using multiple CASE expressions, one for each group, just combine them into a single expression. Then, in the THEN clause you can use something more descriptive to group on later. In the example below I used the column aliases you provided.

WITH AgeData as
(
  SELECT [Username],
         [Birthdate],
         DATEDIFF(YEAR, [Birthdate], GETDATE()) AS [AGE]
  FROM @table
),
GroupAge AS
(
  SELECT [Username],
         [Birthdate],
         [Age],
         CASE
             WHEN AGE < 30 THEN 'Under 30'
             WHEN AGE BETWEEN 31 AND 40 THEN '31 - 40'
             WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
             WHEN AGE > 50 THEN 'Over 50'
             ELSE 'Invalid Birthdate'
         END AS [Age Groups]
  FROM AgeData
)
SELECT COUNT(*) AS [AgeGrpCount],
       [Age Groups]
FROM GroupAge
GROUP BY [Age Groups];


High CPU Utilization for Mongodb ReplicaSet Secondary Nodes

As per MongoDB blog documentation from Asya Kamsky here CPU load is almost
never the bottleneck/limiting resource for MongoDB (or databases in
general).

Unless you are running a large number of MapReduce jobs and/or aggregation framework queries, high CPU utilization tends to indicate that you have poorly tuned queries possibly with in-memory sorts (as opposed to indexes supporting sorting by reading documents in correct order).

Note: write heavy load means that rather than worrying about using available disk drive space you should be concerned about available disk IO bandwidth.

To View Metrics related to Alerts and Monitoring here. The serverStatus command returns a document that provides an overview of the database’s state. Monitoring applications can run this command at a regular interval to collect statistics about the instance.

db.runCommand( { serverStatus: 1 } )

For example, the following operation suppresses the repl, metrics and locks information in the output.

db.runCommand( { serverStatus: 1, repl: 0, metrics: 0, locks: 0 } )

Note: The output fields vary depending on the version of MongoDB, underlying operating system platform, the storage engine, and the kind of node, including mongos, mongod or replica set member.

For the serverStatus output specific to the version of your MongoDB, refer to the appropriate version of the MongoDB Manual.

And the dbStats command returns storage statistics for a given database. The command takes the following syntax:

db.runCommand({ dbStats: 1, scale: 1 })

where scale is optional and defaults to 1.

For example

> db.runCommand({ dbStats: 1, scale: 1 })
{
        "db" : "test",
        "collections" : 1,
        "views" : 0,
        "objects" : 4,
        "avgObjSize" : 86.75,
        "dataSize" : 347,
        "storageSize" : 36864,
        "numExtents" : 0,
        "indexes" : 1,
        "indexSize" : 36864,
        "fsUsedSize" : 275137204224,
        "fsTotalSize" : 499632828416,
        "ok" : 1

The scale argument allows you to specify how to scale byte values. For example, a scale value of 1024 will display the results in kilobytes rather than in bytes:

db.runCommand({ dbStats: 1, scale: 1024 })

For your further ref here and here


Do Constraints move from one database to another?

Yes, in SQL Server constraints are defined within each database within the table definition. So if you physically promote a database to a higher environment through backup and restore, the constraints will remain within the database. If you script out objects, the default behaviour will be for the constraints to be scripted out with the table definition.

Microsoft's TechNet has a decent explanation:
https://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx


Documenting Databases with Extended Properties - value is being overwritten

Your query is missing the joining clause against sys.extended_properties. It's currently doing the cartesian product against the previous join result (notice the comma at the end of the first join and no condition on the WHERE linking the tables).

FROM
    sys.master_files mf
    INNER JOIN sys.databases d ON d.database_id = mf.database_id,
    sys.extended_properties SEP
WHERE
    d.database_id > 4 -- Skip system databases
GROUP BY
    -- ...

I'd recommend always explicitly writing the JOIN operator (and avoid writing the relationships in the WHERE) since it makes the query more readable. If you want to apply cartesian just write CROSS JOIN.

Extended properties on database object are tricky because their record on sys.extended_properties have no id (they are listed as 0). The documentation says that database objects are listed with class = 0, so we are gonna query those:

The way you can retrieve these is through the system function sys.fn_listextendedproperty, by supplying all parameters as DEFAULT:

SELECT F.*
FROM fn_listextendedproperty(default, default, default, default, default, default, default) AS F

However, this will only retrieve the extended properties of the database in which you are executing the function. So to see ADatabase's extended properties you will need to write ADatabase.sys.fn_listextendedproperty(...). This means that you will require a dynamic, database by database solution to correctly retrieve all these (in which case you can use the sys.extended_properties while preceding the corresponding USE <DatbaseName> before).

The following solution uses a temporary table and a cursor to retrieve all databases extended properties and finally join with your query by database_id. I've tested it and ran successfully (but always review it!).

IF OBJECT_ID('tempdb..#DatabaseExtendedProperties') IS NOT NULL
    DROP TABLE #DatabaseExtendedProperties

CREATE TABLE #DatabaseExtendedProperties (
    DatabaseID INT,
    DatabaseName SYSNAME,
    PropertyName SYSNAME,
    PropertyValue VARCHAR(MAX))

DECLARE
    @DatabaseName NVARCHAR(100),
    @DatabaseID INT

DECLARE DatbaseCursor CURSOR FOR
    SELECT
        S.name,
        S.database_id
    FROM
        sys.databases AS S
    WHERE
        S.database_id > 4

OPEN DatbaseCursor
FETCH NEXT FROM DatbaseCursor INTO @DatabaseName, @DatabaseID

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @DynamicSQL NVARCHAR(MAX) = '
        USE ' + QUOTENAME(@DatabaseName) + '

        INSERT INTO #DatabaseExtendedProperties (
            DatabaseID,
            DatabaseName,
            PropertyName,
            PropertyValue)
        SELECT
            DatabaseID = ' + CONVERT(NVARCHAR(10), @DatabaseID) + ',
            DatabaseName = ''' + QUOTENAME(@DatabaseName) + ''',
            PropertyName = T.name,
            PropertyValue = CONVERT(VARCHAR(MAX), T.value)
        FROM
            sys.extended_properties AS T
        WHERE
            T.class = 0'

    EXEC (@DynamicSQL)

    FETCH NEXT FROM DatbaseCursor INTO @DatabaseName, @DatabaseID

END

CLOSE DatbaseCursor
DEALLOCATE DatbaseCursor

-- Your query with the temporary table's join
SELECT d.database_id                     AS [ID],
       d.NAME                            AS [Database Name],
       Round(Sum(mf.size) * 8 / 1024, 0) AS [Size (MB)],
       d.state_desc                      AS [State],
       d.is_read_only                    AS [Read Only],
       d.create_date                     AS [Created Date],
       Max(CASE
             WHEN SEP.PropertyName = 'Shared' THEN SEP.PropertyValue
           END)                          AS [Shared],
       Max(CASE
             WHEN SEP.PropertyName = 'Product' THEN SEP.PropertyValue
           END)                          AS [Product],
       Max(CASE
             WHEN SEP.PropertyName = 'Customer' THEN SEP.PropertyValue
           END)                          AS [Customer]
FROM   sys.master_files mf
       INNER JOIN sys.databases d ON d.database_id = mf.database_id
       INNER JOIN #DatabaseExtendedProperties AS SEP ON SEP.DatabaseID = d.database_id
WHERE  d.database_id > 4 -- Skip system databases
GROUP  BY d.database_id,
          d.NAME,
          d.state_desc,
          d.is_read_only,
          d.create_date
ORDER  BY d.NAME


Patching after upgrade (windows 11gr2 -> 12cR2)

That would depend on what the "same" patches are.  At the very least, you'd need the patch specifically for the new version of the database.  But it could be that the the 11g patch fixed something in 11g that was inherently fixed in 12c.  You just have to look at each patch on a case-by-case basis.  Also, are you talking about individual patches or patchset bundles?  If the latter, simply apply the latest for your version of the database.


In one of my comments to you

  I was thinking about solving your problem with a DML trigger. That
  would allow the work to be done in the database engine without having
  to return all of the data to C# for evaluation - are you open to a
  solution that blows away the current data and lays out a new set of
  rows with the proper start/finish dates based on the recent DML
  action? It would not be as elegant as updating existing rows.

You said (paraphrasing)

  I think it's worth a look but I tend to avoid blowing data in that
  manner... could you give at least a brief account of your solution?

Well, it's a slow work day today and since no one else has come on stage with an answer yet, I'll step up to the mic and prepare for the hecklers.  This is a prototype without any performance considerations (indexes, etc).  Even with proper indexing, it may not suit your needs.  Naturally, if I have misunderstood your question (or worse, have a fatal flaw), go easy on the downvotes ;)

I tried to come at this problem with the KISS principle.  My approach relies on a table trigger which deletes existing data and lays out a new timeline when inserts, updates and deletes are executed.  It relies heavily on logic I got from Itzik Ben-Gan's post entitled New Solution to the Packing Intervals Problem.  You should definitely read Itzik's post to fully understand the logic in my trigger.

Your original question indicated that "For every user, I want zero overlapping date ranges.".  I took the liberty of adding a UserID column to your table.  My table also uses an IDENTITY for the Id column.  Since my solution blows away existing data and inserts a new timeline, I don't specify the Id column on my inserts.

IF OBJECT_ID('dbo.DateRange') IS NOT NULL
    DROP TABLE dbo.DateRange

CREATE TABLE [dbo].[DateRange](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [UserId] VARCHAR(10) NOT NULL,
   [Start] [datetime] NOT NULL,
   [Finish] [datetime] NOT NULL
)
go

Here's the trigger where the real fun happens

CREATE TRIGGER dbo.DateRange_Merge_Ranges
   ON  dbo.DateRange
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    --Dropping temp tables used in the trigger
    IF OBJECT_ID('tempdb..DataToInsert') IS NOT NULL
        DROP TABLE #DataToInsert;
    IF OBJECT_ID('tempdb..UniqueUserIdsAffected') IS NOT NULL
        DROP TABLE #UniqueUserIdsAffected;

    --Get all UserId's affected by the Insert, Update, Delete
    SELECT * INTO #UniqueUserIdsAffected FROM
    (
    select UserId from inserted
    union
    select UserId from deleted
    ) a

    --Calculate a new column called prvend which contains the 'previous' rows Finish date
;   WITH C1 AS
    (
        SELECT a.*,
            MAX(Finish) OVER(PARTITION BY a.UserId
                            ORDER BY a.start, a.finish
                            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prvend
        FROM dbo.DateRange a join
        #UniqueUserIdsAffected b on
            b.UserId = a.UserId
    ),

    --Determine whether each row starts a new 'packed-interval' - call the column grp
    C2 AS
    (
    SELECT *,
        SUM(isstart) OVER(PARTITION BY C1.UserId
                        ORDER BY C1.Start, C1.Finish
                        ROWS UNBOUNDED PRECEDING) AS grp
    FROM C1
    CROSS APPLY ( VALUES(CASE WHEN Start <= prvend THEN 0 ELSE 1 END) ) AS A(isstart)
    )

    --Insert the results into a local temp table because we're going to delete
    --all rows fromthe real table matching the UserId's affected in the Insert, Update, Delete
    SELECT C2.UserId, MIN(C2.Start) AS Start, MAX(C2.Finish) AS Finish into #DataToInsert
    FROM C2
    group by C2.UserId, C2.grp;

    --Delete all rows fromthe real table matching the UserId's affected in the Insert, Update, Delete
    DELETE a from
        dbo.DateRange a join
        #UniqueUserIdsAffected b on b.UserId = a.UserId

    --Insert into the real table the newly calculated time slices
    INSERT INTO dbo.DateRange(UserId, Start, Finish)
        SELECT UserId, Start, Finish from #DataToInsert

END
GO

Now that we have our table with the trigger defined, let's insert some rows
I'm inserting rows for TOM and JOE.  My DML examples deal with TOM, but I wanted you to see that there was no magic up my sleeve in keeping updates isolated to whom they belong.

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-01-01', '2017-02-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-03-01', '2017-04-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('JOE', '2017-01-01', '2017-02-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('JOE', '2017-03-01', '2017-04-01');
select * from DateRange order by UserId, Start, Finish

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

Each of the following cases build on the previous case (if one exists)

Case 1: New range covers an existing range...

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2016-12-15', '2017-02-15');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

Case 2: New range is covered by existing range. So...

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-01-15', '2017-01-16');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

Case 3: New range partially overlaps a new range.

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2016-12-15', '2017-01-15');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

Case 4: Update an existing row

UPDATE DateRange SET Finish = '2017-03-02' WHERE UserId = 'TOM' and Start = '2016-12-15';

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-04-01 00:00:00.000 |

Case 5: Insert a new range

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-06-01', '2017-07-01');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-06-01 00:00:00.000 | 2017-07-01 00:00:00.000 |

Case 6: New range partially overlaps a new range.

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-04-01', '2017-05-01');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-05-01 00:00:00.000 |
| TOM    | 2017-06-01 00:00:00.000 | 2017-07-01 00:00:00.000 |

Case 7: New range partially overlaps a new range.

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-05-05', '2017-06-05');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-05-01 00:00:00.000 |
| TOM    | 2017-05-05 00:00:00.000 | 2017-07-01 00:00:00.000 |

Case 8: Delete an existing row

delete from DateRange WHERE UserId = 'TOM' and Start = '2016-12-15';

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-05-05 00:00:00.000 | 2017-07-01 00:00:00.000 |

If you can achieve the same results in C# in an easier way, you should stick with that.  Of course, a 'smartie' on this site may have a better solution.


Same product name with different price in different states

You should normalize your tables.  In third normal form (3NF) you tables would look like this:

Notice that this is different from your normalized table picture in two important respects:  First, states and districts should be separated into two tables so you don't repeat the state names.  Second, and more importantly, prices depend on district and product.  Therefore the price should be in the intersection table between district and product, not in the same table as states and districts.


Views are just stored queries: you don't execute views, but you SELECT data out of them. When you combine multiple views joining them, you are doing nothing different from inlining the view definition in the query. In fact, that's what the optimizer does: it's called view expansion.

If your views will never be used in isolation, but always joined together instead, I suggest that you stop tuning them individually, but you concentrate on the overall query that will use them. The optimizer will expand the views and generate a plan that will have nothing to do with the plan used when selecting from the individual views.

On the other hand, what makes your individual views faster (well-thought T-SQL code, indexes, statistics) won't probably hurt the performance of the overall query. Just don't expect the resulting plan to be a simple merge of the individual views' plans.


How do I join 2 columns in query result (SQL report builder)

In SSRS you don't join them in the result set (unless you do it in the data source) but you can use expressions in the design to show them concatenated in your report.

Have a look at Expressions (Report Builder and SSRS).

Your expression would be something like

=Fields!FirstName.Value & Fields!LastName.Value


Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?

Yes if you:

are running SQL Server 2014 or later; and
are able to run the query with trace flag 176 active; and
the computed column is PERSISTED

Specifically, at least the following versions are required:

Cumulative Update 2 for SQL Server 2016 SP1
Cumulative Update 4 for SQL Server 2016 RTM
Cumulative Update 6 for SQL Server 2014 SP2

BUT to avoid a bug (ref for 2014, and for 2016 and 2017) introduced in those fixes, instead apply:

Cumulative Update 1 for SQL Server 2017
Cumulative Update 5 for SQL Server 2016 SP1
Cumulative Update 8 for SQL Server 2016 RTM
Cumulative Update 8 for SQL Server 2014 SP2

The trace flag is effective as a start-up –T option, at both global and session scope using DBCC TRACEON, and per query with OPTION (QUERYTRACEON) or a plan guide.

Trace flag 176 prevents persisted computed column expansion.

The initial metadata load performed when compiling a query brings in all columns, not just those directly referenced. This makes all computed column definitions available for matching, which is generally a good thing.

As an unfortunate side-effect, if one of the loaded (computed) columns uses a scalar user-defined function, its presence disables parallelism for the whole query, even when the computed column is not actually used.

Trace flag 176 helps with this, if the column is persisted, by not loading the definition (since expansion is skipped). This way, a scalar user-defined function is never present in the compiling query tree, so parallelism is not disabled.

The main drawback of trace flag 176 (aside from being only lightly documented) is that it also prevents query expression matching to persisted computed columns: If the query contains an expression matching a persisted computed column, trace flag 176 will prevent the expression being replaced by a reference to the computed column.

For more details, see my SQLPerformance.com article, Properly Persisted Computed Columns.

Since the question mentions XML, as an alternative to promoting values using a computed column and scalar function, you could also look at using a Selective XML Index, as you wrote about in Selective XML Indexes: Not Bad At All.


Existing data to new table, how to set up the PK & Identity

This is simple enough to test on your own. Here's how:

Simple table:

CREATE TABLE dbo.whatever
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Whatever VARCHAR(10)
);

Simple insert:

INSERT dbo.whatever ( Whatever )
SELECT x.Whatever
FROM   ( SELECT TOP 100 'A' AS Whatever FROM sys.messages AS m ) AS x;

Check on the max Id and current identity value:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 100.

Do an out of band insert:

SET IDENTITY_INSERT dbo.whatever ON;

DECLARE @maxid INT = (SELECT MAX(w.Id) FROM dbo.whatever AS w);

INSERT dbo.whatever ( Id, Whatever )
SELECT x.Id + @maxid, x.Whatever
FROM   (   SELECT TOP 100 ROW_NUMBER() OVER ( ORDER BY @@SPID ) AS Id, 'A' AS Whatever
           FROM   sys.messages AS m ) AS x;

SET IDENTITY_INSERT dbo.whatever OFF;

Check on stuff:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 200.

Do another insert:

INSERT dbo.whatever ( Whatever )
SELECT x.Whatever
FROM   ( SELECT TOP 100 'A' AS Whatever FROM sys.messages AS m ) AS x;

Check on stuff:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 300.

Hope this helps!


SQL Server 2008 - Partitioning and Clustered Indexes

A partitioned table is really more like a collection of individual tables stitched together. So your in example of clustering by IncidentKey and partition by IncidentDate, say that the partitioning function splits the tables into two partitions so that 1/1/2010 is in partition 1 and 7/1/2010 is partition two. The data will be layed out on disk as:

Partition 1:
IncidentKey    Date
ABC123        1/1/2010
ABC123        1/1/2011
XYZ999        1/1/2010

Partition 2:
IncidentKey    Date
ABC123        7/1/2010
XYZ999        7/1/2010

At a low level there really are two, distinct rowsets. Is the query processor that gives the illusion of a single table by creating plans that seek, scan and update all rowsets together, as one.

Any row in any non-clustered index will have have the clustered index key to which it corresponds, say ABC123,7/1/2010. Since the clustered index key always contains the partitioning key column, the engine will always know in what partition (rowset) of the clustered index to search for this value (in this case, in partition 2).

Now whenever you're dealing with partitioning you must consider if your NC indexes will be aligned (NC index is partitioned exactly the same as the clustered index) or non-aligned (NC index is non-partitioned, or partitioned differently from clustered index). Non-aligned indexes are more flexible, but they have some drawbacks:

non-aligned indexes require large amounts of memory for certain query plans
non-aligned indexes prevent efficient partition switch operations

Using aligned indexes solves these issues, but brings its own set of problems, because this physical, storage design, option ripples into the data model:

aligned indexes mean unique constrains can no longer be created/enforced (except for the partitioning column)
all foreign keys referencing the partitioned table must include the partitioning key in the relation (since the partitioning key is, due to alignment, in every index), and this in turn requires that all tables referencing the partitioned table contain partitioning key column value. Think Orders->OrderDetails, if Orders have OrderID but is partitioned by OrderDate, then OrderDetails must contain not only OrderID, but also OrderDate, in order to properly declare the foreign key constraint.

These effects I found seldom called out at the beginning of a project that deploys partitioning, but they exists and have serious consequences.

If you think aligned indexes are a rare or extreme case, then consider this: in many cases the cornerstone of ETL and partitioning solutions is the fast switch in of staging tables. Switch in operations require aligned indexes.

Oh, one more thing: all my argument about foreign keys and the ripple effect of adding the partitioning column value to other tables applies equally to joins.


Select query taking too long MYSQL 5.6.17

You could try doing your Case selects as a cte.
The  line: A.'stagione' IN (SELECT DISTINCT STAGIONE FROM wb_mod_deposit_out WHERE CREATED BETWEEN '2017-12-11' AND '2017-12-17')

will also be a memory hog. Do you have an index on your CREATED column?

Also, if you can find a way to take out TRIM(LEADING '0' FROM a.'commessa') = TRIM(LEADING '0' FROM c.'commessa') - maybe do it in an update statement or a third cte.


Creating row number for multiple columns?

What is sor_hdr?
I think you need to drop the Partition by and replace row_number by rank


This should actually work with a correlated subquery:

SELECT dblink_connect(
                'host=remote port=5432 dbname=remote_db user=user password=pass');

INSERT INTO local_table (column1, column2)
SELECT *
FROM   dblink((
          SELECT '
             SELECT remote_column1 ltrim(remote_column2, ''' || uniq_id || ''')
             FROM   remote_table
             WHERE  remote_column2 ILIKE ''' || uniq_id || '%''
             AND    row_counter > ' || coalesce((
                SELECT max(row_counter)
                FROM   yet_another_local_table), -1)
          FROM   some_local_table
          --WHERE <some_condition>
          LIMIT  1  -- make sure it's one row
          )
       ) AS t(column1 varchar, column2 varchar);

SELECT dblink_disconnect();

The meta-levels and layers of single-quotes may be a bit confusing.

This form is vulnerable to SQLi. Make sure nothing bad can come from some_local_table or use quote_literal(uniq_id) in place of plain uniq_id.

In reply to the comment I added a nested correlated subquery to the example. It returns a single row, so it should work fine. Could also be done by left-joining to it in the FROM clause.

Note that I rewrote the expression to make COALESCE() the outer wrapper, so the query won't fail if there are no rows yet_another_local_table. The way you had it would only catch the case where max(row_counter) IS NULL.

Probably easier to read with format() (requires Postgres 9.1+):

INSERT INTO local_table (column1, column2)
SELECT *
FROM   dblink(
         (SELECT format(
            'SELECT remote_column1 ltrim(remote_column2, %1$L)
             FROM   remote_table
             WHERE  remote_column2 ILIKE %2$L
             AND    row_counter > %3s'
            ,uniq_id,  uniq_id || '%'
            ,COALESCE((
               SELECT max(row_counter)
               FROM   yet_another_local_table), -1)
            )
          FROM   some_local_table
          --WHERE <some_condition>
          LIMIT  1)
   ) AS t(column1 varchar, column2 varchar);


A Difficult Case of "A transport-level error has occurred when receiving results from the server" SQL Server

I'm not sure what authentication method you are using, but I've seen issues when using Windows Authentication when the DC (Domain Controller) is being overwhelmed with requests you can see issues.  Usually it is a timeout issue though.

If you are using Windows Auth, try switching to SQL Auth to rule that out.  Sorry I tried to add a comment but it wouldnt let me b/c i'm new.


MySQL: How to troubleshoot when simple queries are not returning anymore?

You are hardly using MyISAM, so ignore the issues about MyISAM.

Ignore the recommendation to OPTIMIZE and 'fragmented'; that tool always says that, even if it is of no use (which is usually the case).

Joins without indexes -- you are better off looking at any 'slow' queries.

Find the slow queries.  First try SHOW FULL PROCESSLIST when it "hangs" to see if you can catch a query.  Another thing to do is to turn on the Slowlog, run for a day, then use pt-query-digest to summarize the slowlog.  Let's look at the first couple of queries.

"Sort aborted" sounds like a poorly written query; the PROCESSLIST should show it before killing it.  Perhaps it is a JOIN without an ON.


How can I give a group of developers the ability to view the query plan, without the ability to run a trace or to view the server state?

DENY trumps GRANT, so yeah, you can’t let people view plans if you’ve explicitly denied them the ability to alter trace. But just because you haven’t denied a right doesn’t mean it has been granted.

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE.

It also shows that, unless you explicitly grant any trace-related permissions, they won't be able to read trace data, or even be able to tell there are traces running at all, unless they have inherited those rights in some other way (Windows group, server role, etc). So you don't need to explicitly deny ALTER TRACE in order to protect trace data from these users - just be aware that it is possible for them to can inherit it without an explicit grant.

USE master;
GO
CREATE LOGIN blob WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE USER blob FROM LOGIN blob;
GO
GRANT SHOWPLAN TO blob;
GO
CREATE TABLE dbo.splunge(plonk INT);
GO
GRANT SELECT ON dbo.splunge TO blob;
GO
EXECUTE AS USER = 'blob';
GO
SET SHOWPLAN_XML ON;
GO
-- plan is shown:
SELECT plonk FROM dbo.splunge;
GO
SET SHOWPLAN_XML OFF;
GO
-- error:
EXEC sp_trace_getdata @traceid = 1;
GO
-- empty result set:
SELECT * FROM sys.traces;
GO
REVERT;
GO
USE master;
GO
DROP DATABASE floob;
DROP LOGIN blob;


Can I refuse inserts/updates with timestamps without time-zone in postgresql?

Not sure if it is feasible in your case, but you could use functions for inserting data. Use a text parameter for the timestamp and throw an error if the string doesn't contain a + or -.


Yes, of course. Every index you create requires maintenance (it needs to be updated for every insert, update and delete operation) and occupies space. Indexes aren't free performance boosts, and should certainly be considered as a trade-off. You should also be very careful about throwing an index on a table to satisfy one query that is called once a week if that table is written all week long. In other words, don't look at the missing index DMVs or a single execution plan and judge the validity of a suggested index in isolation.


Getting empty folder when using mongodump to back up my MongoDB

I was using the default mongodb-client deb package with Ubuntu. I removed those and installed the mongodb-org-tools package from mongodb.com https://docs.mongodb.com/master/tutorial/install-mongodb-on-ubuntu/?_ga=2.36209632.1945690590.1499275806-1594815486.1499275806

They have other install instructions for your specific OS if you are not on Ubuntu https://www.mongodb.com/download-center?jmp=nav#community


It means nothing, really. PostgreSQL has a number of replication features, as the relevant chapter in the manual details.

That article is apallingly wrong and outdated. Ignore it. "No referential integrity" in MySQL? Bah.

If you have an account on that site, please ask for it to be removed or fixed or whatever can be done. As it stands it's dangerously misleading and wrong, and has no place claiming to be current accurate information.


Unable to turn off automatic memory management (AMM)

In case one does not want to use AMM MEMORY_MAX_TARGET setting has to be not set at all:

alter system reset memory_target;
alter system reset memory_max_target;


ORA-12154: TNS:could not resolve the connect identifier specified

As Munchi pointed out the issue was caused by wrong syntax. I used:

sqlplus -L user_name/tns_alias@Password @d:\my_bat_file\sql_script.sql

Instead of:

sqlplus -L user_name/Password@tns_alias @d:\my_bat_file\sql_script.sql


pgpool-II doesn't execute my failover script

I had issues with getting the script to run as well. But eventually figured it out.

A few high level steps to keep in mind.

In your failover script redirect results to a log file like:

ssh -T postgres@$SLAVE1 "touch /tmp/postgresql.trigger.5432" > /tmp/failover.log 2>&1

This might help you identify why it is failing.

However, here is a high level overview of how I managed to get the failover  script running:

install failover script on pgpool server
pgpool is running under root. So...
as user root run ssh-keygen -t rsa on the pgpool server and cat id_rsa.pub and >> to authorized_keys on the db servers

Doing so will allow the script to run without interactive authentication, which is what my problem was.


Next step in progression from 3NF to BCNF

First, you assumption about the 3NF is correct.

Then, in the analysis algorithm to find the BNCF, when you start to remove a dependency X → Y since it violates the BCNF, you should put in the first relation H1 not only XY, but X+, while in the second relation you should have H2 = H - X+ + X.

So, in the first step, the two resulting relations are:

H1 = {A,B,C) with dependencies A → B and B → C and key `A`,
H2 = {A, D} (with no dependencies)

In H1 the dependency B → C violates the BCNF since B is not a superkey, and you can decompose it in:

H11 = {A, B} with the dependency A → B and key A
H12 = {B, C} with the dependency B → C and key B

So, at the end, the final decomposition is H11, H12 and H2. Note that this decomposition determines the loss of the dependency {C, D} → A, and this happens also if we start by considering first the dependency B → C (obtaining the same decomposition).


How can I drop an index on all partitions?

Greenplum doesn't (as at version 4.3.8) maintain a record in the catalogue linking indexes on partitions to indexes on base tables. The best option is to follow the partitions and find indexes on the partitions matching the definition of the base index.

CREATE OR REPLACE FUNCTION drop_child_indexes (index_name varchar)
RETURNS VOID
AS
$functionBody$
DECLARE
  child_index_name varchar;
BEGIN

  FOR child_index_name IN
    SELECT child_index.indexrelid::regclass
      FROM pg_index AS parent_index
        -- Find the partitioning scheme for the table the index is on
        INNER JOIN pg_partition ON pg_partition.parrelid = parent_index.indrelid
        -- Follow the links through to the individual partitions
        INNER JOIN pg_partition_rule ON pg_partition_rule.paroid = pg_partition.oid
        -- Find the indexes on each partition
        INNER JOIN pg_index AS child_index ON child_index.indrelid = pg_partition_rule.parchildrelid
          -- Which are on the same field as the named index
          AND child_index.indkey = parent_index.indkey
          -- Using the same comparison operator
          AND child_index.indclass = parent_index.indclass
      -- Filtered for the index we're trying to drop
      WHERE parent_index.indexrelid = $1::regclass::oid
      -- Drop leaves first, even if it doesn't really matter in this case
      ORDER BY pg_partition.parlevel DESC

LOOP
  RAISE NOTICE '%', child_index_name||' ';
  EXECUTE 'DROP INDEX '||child_index_name||';';
END LOOP;

END
$functionBody$
LANGUAGE plpgsql;

If there another index with the same definition (fields, comparison operators) it's partition indexes will also be dropped. Not a perfect answer but better than substring matching.


Note that mysqlbackup is not a hot backup solution. For databases the size of your one, there really is no debate; see the related Q & A Mysql backup strategies?

As Shlomi Noach (GitHub's MySQL guy) says in his answer, Percona's XtraBackup is your go-to choice. It is also worth reading MySQL Backup and Restore Best Practices by Krzysztof Ksiazek.

By the way, no backup strategy on earth will protect you from devs/DBAs deleting records/dropping tables, that's why you should be using incremental backups; maybe LVM snapshots or flush logs. However, incremental backups are also performed by XtraBackup. See also Example Backup and Recovery Strategy in the MySQL reference manual.

On a more general note, broad approaches to this issue are discussed in:

Database Backup and Recovery Best Practices by Ali Navid Akhtar, Jeff Buchholtz, Michael Ryan, and Kumar Setty
Backup and Recovery Best Practices by Omelchenko Alexander

In any case, on a production system, devs (or DBAs for that matter) should not be wandering through the filesystem and/or database deleting stuff ad libitum. Any and all changes on production should have been performed at least twice on test/UAT or similar systems before being implemented in prod! This is best practice.


It can't use two separate indexes to satisfy that WHERE clause - it will have to pick one or the other. If you want to enable seeks you will need to make a single index with both columns, and order them from most selective to least selective. Think about designing indexes in such a way that SQL Server can eliminate as many rows as possible, as quickly as possible, rather than the traditional way most people think about it (finding as few rows as possible).

You should also consider adding any other columns from the SELECT list to the INCLUDE list of the index (making it "covering"), this way you can avoid key lookups. So, perhaps, drop the two indexes you have and create (guessing at selectivity):

CREATE INDEX IX_BetterIndex
  ON dbo.MyChecklistStatus -- My? Really? Why?
    (ProfileId, AppFormSubSectionId)
  INCLUDE(mychecklist0, ...);

(Note that you don't have to include the clustered index key column(s), since they exist in all non-clustered index pages anyway.)

This is a general recommendation for something to try. It could very well be that selectivity is high enough that the number of key lookups does not justify using space in the index pages for the covering columns. It could also be that this query isn't run often enough to optimize the indexes on the table to help this query in a vacuum - there may be other queries that are run more frequently that would benefit from a different index. And it may be that your SELECT list has columns in it that aren't really necessary, which can change the formula further. There is no one right answer.

And no, the order of columns in the index has no relevance whatsoever to the ColumnId order in sys.columns.


Send SSRS Report Many Times Based on Sql Query

In a data-driven subscription you specify a query. Each row that the query returns corresponds to a single email, rendering a new report for each one.

So have your query do something like: SELECT WidgetID FROM dbo.Widgets

...or if it's already doing that, use CROSS JOIN dbo.Widgets to make your query return four times as many rows (assuming there are four).

Then use the WidgetID field as a parameter to your report.


Query notification equivalent for SQL Server 2016

Query Notifications have not been removed or changed.  The Documentation has just not all been ported to docs.microsoft.com.

See, eg:

Working with Query Notifications

Query Notifications - sys.dm_qn_subscriptions

Detecting Changes with SqlDependency


It seems likely the very large ntext data is highly fragmented, causing a large amount of random I/O (or other inefficiencies) when locating LOB fragments to delete. Maybe the elastic thingy needs more I/O horsepower too.

You may need to export and reload the data to solve this problem. Copying to a new table, dropping the old, then renaming the new would also work.

Unless you have an extremely good reason not to, I suggest you also change the data type from the old, deprecated ntext to the replacement nvarchar(max) while reloading the data.

It may also simply be a SQL Server limitation with large LOBs. See the related Q & A Slow Delete's of LOB data in SQL Server.

The usual recommendation is to move to an alternate storage solution when data averages 1MB or more. See the SQL Server technical paper FILESTREAM Storage in SQL Server 2008 by Paul S. Randal for details. This is not yet supported in Azure SQL Database, unfortunately.

Perhaps you would be better storing the large LOB data in Azure Blob storage instead, and only holding a link in the database itself.


SSIS Error :Cannot roll back .No transaction or savepoint of that name was found

The general best practice is to perform transaction management in either the client application (SSIS), server-side T-SQL, but not both.
This allows the SSIS package to determine and control the transactional unit of work and manage transactions among tasks that support transactions.
Individual stored procedures shouldn't start transactions in this case since the outer transaction will ensure all-or-none behavior.

You could instead design the package such that it is idempotent (rerunnable) and not use transactions at all, perhaps also using checkpoints to avoid rework of expensive processing. In that case, the proc could be coded to insert only if the new row
doesn't already exist. This technique helps avoid issues caused by long-running transactions, such as blocking and large transaction logs.


Open up /var/log/messages and search for mongo.

If you see "oom-killer" or "Out of memory" then this points to your problem.

We had that problem when running a mongos and multiple mongods on the same machine, for testing purposes.

Since there is no way to limit mongo's memory usage, we just added a 4GB swapfile, so there was some leeway when memory was at its limit.  (Using swap is the recommendation here.)

More on the OOM killer here.


Data modeling with many attributes where few apply to each record

Option 4:  Class Table Inheritance

This is a design technique used to implement a class/subclass situation.  In this situation, attributes often apply only to one subclass, and not to all rows in the table. Visit the tag of the same name over in SO to see a bunch of relevant  questions and answers.

This has the advantage eliminating NULLS (mostly), providing fast and easy joins, and enforcing the 1:1 neture of the class:subclass relationship.

Your Option 1 is sometimes called "Single Table Inheritance"


Where is the MySQL variable - innodb_flush_method?

The only way for you to have it is to add it to your my.cnf.

Before you can add this option, you must make sure the InnoDB storage engine is available.

Please run these commands

SHOW VARIABLES LIKE 'have_innodb';
SHOW ENGINES;

If you see this:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_innodb   | DISABLED |
+---------------+----------+
1 row in set (0.01 sec)

mysql> SHOW ENGINES;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | NO       | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO       | Example storage engine                                         |
| ARCHIVE    | YES      | Archive storage engine                                         |
| CSV        | YES      | CSV storage engine                                             |
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
| ISAM       | NO       | Obsolete storage engine                                        |
+------------+----------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

mysql>

then you have skip-innodb in your my.cnf. You should comment it out and restart mysql

What you want to see is this:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW ENGINES;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES      | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO       | Example storage engine                                         |
| ARCHIVE    | YES      | Archive storage engine                                         |
| CSV        | YES      | CSV storage engine                                             |
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
| ISAM       | NO       | Obsolete storage engine                                        |
+------------+----------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

mysql>

Add that option to the my.cnf

[mysqld]
innodb_flush_method=O_DIRECT

then run

service mysql restart

Afterwards, you should be able to see it if you run

mysql> show variables like 'innodb_flush_method';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.00 sec)

mysql>

UPDATE 2013-04-12 14:25 EDT

I just realized you are running the Windows version of MySQL. What was I thinking ?

As point by @yercube's comment, innodb_flush_method is not available for Windows. Here is the MySQL Documentation on it:

  Controls the system calls used to flush data to the InnoDB data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.

If you need this performance enhancement, migrate your data to the Linux version of MySQL.