Why does an UPDATE against a table with an INSTEAD OF UPDATE trigger appear to do a clustered index insert, as well as a clustered index update? I'll start with a very simple example: two tables, both with the same schema, clustered on PK, but one of which has an INSTEAD OF UPDATE trigger:

CREATE TABLE Standard
(
    PK  UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
    V   INT NOT NULL
)
GO

CREATE TABLE InsteadOf
(
    PK  UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
    V   INT NOT NULL
)
GO

INSERT Standard (PK, V) VALUES ('1E58B555-B073-471E-B576-4B09C8E18976', 0)
INSERT InsteadOf (PK, V) VALUES ('1E58B555-B073-471E-B576-4B09C8E18976', 0)
GO

CREATE TRIGGER TR_InsteadOf_Update ON InsteadOf INSTEAD OF UPDATE
AS
BEGIN
    DECLARE @PK UNIQUEIDENTIFIER
    DECLARE @V INT
    DECLARE @cursor CURSOR
    SET @cursor = CURSOR FOR SELECT PK, V FROM Inserted
    OPEN @cursor

    FETCH NEXT FROM @cursor INTO @PK, @V
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE InsteadOf SET
            V = @V
        WHERE PK = @PK

        FETCH NEXT FROM @cursor INTO @PK, @V
    END
    CLOSE @cursor
    DEALLOCATE @cursor

END
GO

If I view the query plan for an update against the standard table, I get the expected clusted index update:

UPDATE Standard SET
    V = 1
    WHERE PK = '1E58B555-B073-471E-B576-4B09C8E18976'

However if I perform a similar update against the table with the trigger, I get what appears to be a clustered index insert, as well as the clustered index update:

UPDATE InsteadOf SET
    V = 1
    WHERE PK = '1E58B555-B073-471E-B576-4B09C8E18976'

Why is this? I can see the clustered index update I expected later in this query plan (query #4), but why do I get this extra insert at query #1?

An INSTEAD OF trigger stores a copy of the rows that would be affected in a hidden work table*. This is the Clustered Index Insert you see. The trigger body reads from this work table* and any data changes in the trigger use the 'normal' operator (Clustered Index Update in your example).

*The query processor internally renames the work table when constructing the user-visible form of the execution plan. When writing to it, it is renamed to the target base table, when reading, it is renamed as inserted or deleted more or less as people would expect to see in a trigger.

For more details, see my article Interesting things about INSTEAD OF triggers.


SQL Server : query code I am trying to write a T-SQL query for SQL Server to pull some information from all the databases. I will then run this nightly and email the report to who it needs to go to.

I have two separate scripts which I want to merge so all the data is formatted nicely in a table.

SELECT
    db.name AS DBName,
    type_desc AS Filetype,
    Physical_Name AS Location,
    (size * 8) / 1024 AS SizeMB,
    DATABASEPROPERTYEX(db.name, 'Status') AS Recovery
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

This returns Name, Type, Location, Size and Status. I would like to merge it with this script:

SELECT
    Database_Name,
    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
    DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM
    MSDB.dbo.BackupSet
WHERE
    Type = 'd'
GROUP BY
    Database_Name

This produces a table with Name, Last backup and days since last backup.

The end result is one query I can run to produce a table with:

Name, Type, Location, Size, Status, Last backup & Days since last backup.

I will then try to work out a way to run this, export it as HTML and email it to myself every morning so I have a quick overview of my environment.

Thanks,
Rob

I think something like the following will work.  You already have the database name (sys.databases.name) in your first query, so you can use this to join to MSDB.dbo.BackupSet.  Beyond that, you are using an aggregate function in your second query.  So you simply need to group by the columns you are selecting from the first query.

SELECT
db.name AS DBName,
mf.type_desc AS Filetype,
mf.Physical_Name AS Location,
(mf.size*8)/1024 AS SizeMB,
DATABASEPROPERTYEX(db.name, 'Status') AS Recovery,
CONVERT( SmallDateTime , MAX(back.Backup_Finish_Date)) as Last_Backup,
DATEDIFF(d, MAX(back.Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM sys.master_files mf
INNER JOIN sys.databases db
    ON db.database_id = mf.database_id
INNER JOIN MSDB.dbo.BackupSet back
    ON back.Database_Name = db.name
GROUP BY db.name, mf.type_desc, mf.Physical_Name, mf.size

In the future, please post the queries that you have tried so far so that we can help point out where they may have gone wrong.


Oracle hierarchical update (reparent) I have been reading on Oracle hierarchical queries and all of the documentation/examples seem to be focused on SELECTing.

I have a table with parent-child relations and would like to change parents of children. Everything is done on the same table.

I can select current parents
I can select new parents
Old/new parent pairs have a common (otherwise unique) identifier

The data looks something like this:

ID | DISABLED | NAME | PARAM | PARENT
1  | 1        | qwer | 11    | NULL
2  | NULL     | qwer | 12    | NULL
3  | NULL     | tyui | 3     | 1
4  | NULL     | wert | 4     | 1
5  | NULL     | erty | 5     | 1
6  | 1        | asdf | 21    | NULL
7  | NULL     | asdf | 22    | NULL
8  | NULL     | ghjk | 6     | 6
9  | NULL     | hjkl | 7     | 6
10 | NULL     | jklp | 8     | 6

And expected output (changed IDs: 3,4,5 and 8,9,10):

ID | DISABLED | NAME | PARAM | PARENT
1  | 1        | qwer | 11    | NULL
2  | NULL     | qwer | 12    | NULL
3  | NULL     | tyui | 3     | 2
4  | NULL     | wert | 4     | 2
5  | NULL     | erty | 5     | 2
6  | 1        | asdf | 21    | NULL
7  | NULL     | asdf | 22    | NULL
8  | NULL     | ghjk | 6     | 7
9  | NULL     | hjkl | 7     | 7
10 | NULL     | jklp | 8     | 7

EXTRA INFO:
The data consists of multiple roots, each having multiple children (I'm testing on a table with ~20k records). The data has only 2 levels. Although I am interested in the PARAM value, I cannot simply update that, because part of application references "correct" roots and another part references children pointing to broken roots, so reparenting is the only option.

I can distinguish roots from children (PARENT IS NULL)
I can find roots to be changed (DISABLED IS NOT NULL)
I can find replacement root IDs (NAME columns are identical)

Parents to be changed are not static (have to be found in database).

To better illustrate this is how replacement pairs can be constructed (not good at this):

SELECT
  NEW.ID REPLACEMENT,
  OLD.ID TO_REPLACE
FROM
  HIERARCHICAL OLD
LEFT JOIN
  HIERARCHICAL NEW
  ON
    OLD.NAME = NEW.NAME
    AND NEW.DISABLED IS NULL
WHERE
  OLD.PARENT IS NULL
  AND OLD.DISABLED IS NOT NULL
  AND NEW.ID is NOT NULL

I would like to update the table so that entry 3 is parented to 2 instead of 1. Of course there are multiple disabled roots so handwriting updates would be a pain.

I cannot find examples to at least get on track with this, so anything would be appreciated.

I can do this with LOOPs, something like:

DECLARE CURSOR c1 IS
  SELECT OLDPARENT, NEWPARENT FROM
  <...>;
BEGIN
  FOR item IN c1 LOOP
    EXECUTE IMMEDIATE 'UPDATE <...>'
  END LOOP;
END;

But performance of such query is dreadful.

To be honest, with only 2 levels of data, it could be simply achieved with something like this:

drop table t1 purge;

create table t1
(
  id number,
  disable number,
  name varchar2(10),
  param number,
  parent number
);

insert into t1 values (1,  1,    'qwer', 11, null);
insert into t1 values (2,  null, 'qwer', 12, null);
insert into t1 values (3,  null, 'tyui', 3,  1   );
insert into t1 values (4,  null, 'wert', 4,  1   );
insert into t1 values (5,  null, 'erty', 5,  1   );
insert into t1 values (6,  1,    'asdf', 21, null);
insert into t1 values (7,  1,    'asdf', 22, null);
insert into t1 values (8,  null, 'ghjk', 6,  7   );
insert into t1 values (9,  null, 'hjkl', 7,  7   );
insert into t1 values (10, null, 'jklp', 8,  7  );
insert into t1 values (11, null, 'asdf', 23, null);
commit;

I added another row, so 'asdf' has a third entry and we expect parent 7 to turn in to parent 11.

SQL> select * from t1;

        ID    DISABLE NAME            PARAM     PARENT
---------- ---------- ---------- ---------- ----------
         1          1 qwer               11
         2            qwer               12
         3            tyui                3          1
         4            wert                4          1
         5            erty                5          1
         6          1 asdf               21
         7          1 asdf               22
         8            ghjk                6          7
         9            hjkl                7          7
        10            jklp                8          7
        11            asdf               23

And the update:

update t1 set parent =
(
  select new_parent_id from
  (
    select id, name, disable, parent, lead(id) over (partition by name order by id) as new_parent_id , row_number() over (partition by name order by id desc ) as r from t1
  ) new_parent
  where disable = 1 and parent is null and r = 2 and new_parent.id = t1.parent
)
where t1.parent in (select id from t1 where disable = 1);

6 rows updated.

The result:

SQL> select * from t1;

        ID    DISABLE NAME            PARAM     PARENT
---------- ---------- ---------- ---------- ----------
         1          1 qwer               11
         2            qwer               12
         3            tyui                3          2
         4            wert                4          2
         5            erty                5          2
         6          1 asdf               21
         7          1 asdf               22
         8            ghjk                6         11
         9            hjkl                7         11
        10            jklp                8         11
        11            asdf               23

The above statement updates rows with disabled parents, and finds the new parent value within the subquery. Notice how 6 rows were updated, but the subquery was executed only twice, once per parent value, not for every row.

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT          |      |      1 |        |      0 |00:00:00.01 |      38 |       |       |          |
|   1 |  UPDATE                   | T1   |      1 |        |      0 |00:00:00.01 |      38 |       |       |          |
|*  2 |   HASH JOIN SEMI          |      |      1 |      6 |      6 |00:00:00.01 |      10 |  1344K|  1344K|  788K (0)|
|   3 |    TABLE ACCESS FULL      | T1   |      1 |     11 |     11 |00:00:00.01 |       7 |       |       |          |
|*  4 |    TABLE ACCESS FULL      | T1   |      1 |      3 |      3 |00:00:00.01 |       3 |       |       |          |
|*  5 |   VIEW                    |      |      2 |     11 |      2 |00:00:00.01 |      14 |       |       |          |
|*  6 |    WINDOW SORT PUSHED RANK|      |      2 |     11 |     20 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|   7 |     WINDOW SORT           |      |      2 |     11 |     22 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|   8 |      TABLE ACCESS FULL    | T1   |      2 |     11 |     22 |00:00:00.01 |      14 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."PARENT"="ID")
   4 - filter("DISABLE"=1)
   5 - filter(("DISABLE"=1 AND "PARENT" IS NULL AND "R"=2 AND "NEW_PARENT"."ID"=:B1))
   6 - filter(ROW_NUMBER() OVER ( PARTITION BY "NAME" ORDER BY INTERNAL_FUNCTION("ID") DESC )<=2)


Why is SQL Server 2012 working set memory low? I have recently migrated a 100gb database for a busy website from:

MS SQL 2008 Standard Edition R2
Windows Server 2008
48gb RAM

to

MS SQL 2012 Web Edition
Windows Server 2012
64gb RAM

I have noticed that the usage of memory seems different in 2012, with the working set memory relatively low compared to the 2008 server (see screenshots below).

Question

Does this seem right, and should I be concerned? I want to be sure that MSSQL has access to as much memory as it needs. The usage profiles of the two servers, in terms of requests from the website they power, is the same, so the pattern or volume of queries has not changed.

SQL Server 2008

SQL Server 2012

SQL Server 2008 memory settings

SQL Server 2012 memory settings

Thanks for any tips.

SQL Server 2012 has vastly changed the way it controls and manages memory. Before 2012, max server memory controlled only 8K pages, and now it controls much more than that.

Here are some good articles about this from the SQLOS team:

Article 1 | Article 2 | Article 3 | Article 4

With these changes, what you see in working set, max server memory, etc. will almost certainly be different between versions. The key is to focus on what "normal" is for these figures and see if they deviate vastly when there is a specific performance issue. I would not just look at a number when the system is working perfectly fine and panic and assume something is wrong (this happens a lot with magic numbers like 300 for page life expectancy - maybe PLE on your system is always 120).

A few queries that might help ballpark and set some baselines:

SELECT TOP (10) page_type,
  numpages = COUNT(*),
  size_kb = 8*COUNT(*)
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY page_type
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_process_memory;

SELECT TOP (10) [type],
  size_kb = SUM(pages_kb)
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Broker%'
   OR object_name LIKE '%Buffer Manager%'
   OR object_name LIKE '%Memory Manager%'
   OR object_name LIKE '%Memory Node%';


Can't do a full backup because the log is full I have a server with 25GB of free space. The software stopped working because the transaction log is full. I noticed that the database has two logs files; one is 45Gb and the other is 65Gb.

I think the guy working here before me (he is not anymore) added a second log file so the software would continue working, however there is almost no space left on disk.

I tried using SQL Server Management Studio to take a full backup but I can't since the transaction log is full.

What are my options? I can shrink the logs but I don't have ANY backup and can't do any of them.

Should I change it to simple mode first? Can I script the whole database and save it as 'backup'?

I assume you don't have space in your drives to take a log backup. You can use the following query to trick SQL server into thinking that you are actually saving the log backup to disk, But you're not.

BACKUP LOG dbname TO DISK = 'NUL:';

Also if you're running SQL server 2005 or less, You can use the below query

BACKUP LOG dbname WITH TRUNCATE_ONLY;

Once the query completes you can go ahead and shrink the log file as usual using

USE dbname;
DBCC SHRINKFILE(fileid , 0);

Also do note that the above backup query will break your log backup chain and your log-shipping setup, if you have one.


Autopostgresqlbackup - DBEXCLUDE doesn't works I'm trying to exclude some databases using the DBEXCLUDE option,but there's daily backups anyways.

# ===============================
# === Debian specific options ===
#================================

# By default, on Debian systems, only 'postgres' user
# is allowed to access PostgreSQL databases without password.
# In order to dump databases we need to run pg_dump/psql
# commands as 'postgres' with su.
#
# The following setting has been added to workraound this issue.
# (if it is set to empty, 'su' usage will be disabled)
SU_USERNAME=openerp

#=====================================================================
# Set the following variables to your system needs
# (Detailed instructions below variables)
#=====================================================================

# Username to access the PostgreSQL server e.g. dbuser
USERNAME=openerp

# Password
# create a file $HOME/.pgpass containing a line like this
#   hostname:*:*:dbuser:dbpass
# replace hostname with the value of DBHOST and postgres with
# the value of USERNAME

# Host name (or IP address) of PostgreSQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"

# pseudo database name used to dump global objects (users, roles, tablespaces)
GLOBALS_OBJECTS="postgres_globals"

# Backup directory location e.g /backups
BACKUPDIR="/var/lib/autopostgresqlbackup"

# Mail setup
# What would you like to be mailed to you?
# - log   : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
# - quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT="quiet"

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE="4000"

# Email Address to send mail to? (user@domain.com)
MAILADDR="root"

# ============================================================
# === ADVANCED OPTIONS ( Read the doc's below for details )===
#=============================================================

# List of DBBNAMES for Monthly Backups.
MDBNAMES="template1 $DBNAMES"

# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)
DBEXCLUDE="DrFabioGonzalez","DraLuisaFPaez","DraMariaVRomero","DraNhoraOrtiz","DraMyriamRamirez","Entrenamiento","GastroCenter","PsicologiaFUAC"

# Include CREATE DATABASE in backup?
CREATE_DATABASE=yes

# Separate backup directory and file for each DB? (yes or no)
SEPDIR=yes

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=6

# Choose Compression type. (gzip or bzip2)
COMP=gzip

# Compress communications between backup server and PostgreSQL server?
# set compression level from 0 to 9 (0 means no compression)
COMMCOMP=0

# Additionally keep a copy of the most recent backup in a seperate directory.
LATEST=no

# OPT string for use with pg_dump ( see man pg_dump )
OPT=""

# Backup files extension
EXT="sql"

# Command to run before backups (uncomment to use)
#PREBACKUP="/etc/postgresql-backup-pre"

# Command run after backups (uncomment to use)
#POSTBACKUP="/etc/postgresql-backup-post"

Other values and comments like DBNAMES suggest the syntax is a single pair of double quotes around a space separated list of DB names:

DBEXCLUDES="DB1 DB2 DB3"

Which is a syntax easy to handle for shell scripts.


Disable full-text logging (SQL Server) Any way to disable FT logs completely? I spent hours googling - no luck.

I get tons of "informational" messages literally every second.

2020-01-01 10:43:16.48 spid33s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:23.48 spid34s     Informational: Full-text Auto population completed for table or indexed view zzz
2020-01-01 10:43:23.48 spid36s     Informational: Full-text Auto population completed for table or indexed view xxx
2020-01-01 10:43:24.64 spid12s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:25.64 spid12s     Informational: Full-text Auto population completed for table or indexed view xxx
2020-01-01 10:43:26.58 spid36s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:26.98 spid17s     Informational: Full-text Auto population initialized for table or indexed view xxx

P.S. My cloud hosting provider charges me for "i/o ops per second" so this is something I want disabled. Also, these logs grow really fast, several gigabytes every week, so I had to write a log maintenance script (rollover + archive etc.)

I'd recommend configuring the full text index for manual population; this will vastly reduce the number of "full-text auto population" messages written to the log.

The Microsoft Docs page shows how to configure a full text index for manual population.

Essentially, you explicitly define the index like so:

CREATE UNIQUE INDEX ui_ukDoc ON Production.Document(DocumentID);
CREATE FULLTEXT CATALOG AW_Production_FTCat;
CREATE FULLTEXT INDEX ON Production.Document
(
    Document                         --Full-text index column name
        TYPE COLUMN FileExtension    --Name of column that contains file type information
        Language 1033                 --1033 is LCID for the English language
)
    KEY INDEX ui_ukDoc
    ON AW_Production_FTCat
    WITH CHANGE_TRACKING OFF, NO POPULATION;
GO

Then, you'd need to schedule population via a SQL Server Agent job with the following command:

ALTER FULLTEXT INDEX ON Production.Document
   START FULL POPULATION;

Once you've completed the full population, you can schedule partial, incremental updates of the full text index via:

ALTER FULLTEXT INDEX ON Production.Document
   START INCREMENTAL POPULATION;

  INCREMENTAL
  Specifies that only the modified rows since the last population be retrieved for full-text indexing. INCREMENTAL can be applied only if the table has a column of the type timestamp. If a table in the full-text catalog does not contain a column of the type timestamp, the table undergoes a FULL population.

or

ALTER FULLTEXT INDEX ON Production.Document
   START UPDATE POPULATION;

  UPDATE
  Specifies the processing of all insertions, updates, or deletions since the last time the change-tracking index was updated. Change-tracking population must be enabled on a table, but the background update index or the auto change tracking should not be turned on.

You only need to do the full population once, after you initially create the full-text index. This page has details regarding incremental or update populations, and the requirements.


Oracle installation fails due to ORA-00443 I hope that you help me to resolve my problem.
I'm trying to install Oracle 11g on RHEL 6.
All prerequisites are ok, but I'm getting such error during step "Creating and starting Oracle instance". The error text is ORA-00443: background process "DIAG" did not start

Sometimes installation fails on 8%, sometimes on 44%. It's very strange. I thought it's related to RAM but I have enough memory (10Gb).

I tried to retry installation several times and sometimes it shows me that another background process (not DIAG, but RDBM for example) did not start. It seems that second part of error is random. But the error code ORA-00443 remains the same.

Here are some information from logs which says nothing to me where is the root cause of a problem

[Thread-10] [ 2013-12-17 23:04:34.027 EET ] [CloneDBCreationStep.executeImpl:460]  calling zerodbid
[Thread-10] [ 2013-12-17 23:04:41.620 EET ] [CloneDBCreationStep.executeImpl:470]  Shutdown database
[Thread-10] [ 2013-12-17 23:04:41.622 EET ] [CloneDBCreationStep.executeImpl:492]  Startup ......nomount......
[Thread-14] [ 2013-12-17 23:07:03.433 EET ] [BasicStep.handleNonIgnorableError:430]  oracle.sysman.assistants.util.UIMessageHandler@1719f30:messageHandler
[Thread-14] [ 2013-12-17 23:07:03.433 EET ] [BasicStep.handleNonIgnorableError:431]  ORA-00443: background process "DIAG" did not start

I tried to find solution within all possible resources, but no luck. Please help to resolve issue. Thanks in advance.

Your OS is x86 but not amd64?
It seems SGA setting larger than system limit. Set your oracle memory under 2GB.


How do I configure SQL Server 2012 so that it can restore and see files in my user account? I have an SQL Server 2012 instance running as a service on my computer, and according to the service page it logs on as account "NT Service\MSSQLSERVER".  However I can't see that account name anywhere else including in the "Local Users and Groups" area in the computer management screen, because, as a link below says, that's not a User Account, it's a service name, in that box that Microsoft so helpfully labelled "account".  At this point I can see many people being confused.

The task I'm trying to accomplish is to restore files using the SSMS dialog "Locate Backup File", which uses a dialog completely unlike any of the standard windows file open dialogs, probably because it's doing a "remote" job and operating from the security context of the SQL server, another rich source of end user confusion, that I hope this question might help clear up.

So far if I want to restore a backup .mdf/.bak file that I have in one of my folders, I have to set that folder to readable by Everyone or else I can't get in there with the SQL Server "Locate Backup File" window.   I find this idea that you're using a GUI talking to a service that has different user accounts and rights than you, that nobody at Microsoft even cared to make clear to you, very confusing even when I have years of experience with windows system administration.

I'm hoping I missed some documentation pages for SQL Server that would tell you, after installing a new SQL server instance, how you might set up security.

Forum posts like this one have even Microsoft staffers saying "this is complicated", and that it "changed, again" in Denali.  How does this work now in SQL Server 2012, and how might I add permission to read files belonging to Users to the SQL Database engine's security SID.

For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with the tools provided and how they function. The file dialog box has been that way in SSMS since SSMS came out with SQL Server 2005. This is why you will general see most stick with T-SQL statements for backing up, restoring, or attaching a database that have been using it since then.

To configure file system permissions with SQL Server you can follow the instructions from MSDN here.

The way service accounts are handled did not come with or because of SQL Server, it was due to the change on the operating system level. Window Server 2008 R2 put a bit more of a security layer around service accounts. The advantage you have is that the service account can more easily access resources on a domain even if installed with the default settings. This link provides a pretty detailed look at how service account permissions are handled with SQL Server 2012. Excerpt from the link is below on the Virtual Accounts used by default in SQL Server 2012. There is also a link provided in the article that goes into more discussion on the Service Account concept with Windows, here. It is from Window Server 2008 R2 but I believe still holds true on Window Server 2012, and likely Window Server 2012 R2.

  Virtual Accounts

  Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed
  local accounts that provide the following features to simplify service
  administration. The virtual account is auto-managed, and the virtual
  account can access the network in a domain environment. If the default
  value is used for the service accounts during SQL Server setup on
  Windows Server 2008 R2 or Windows 7, a virtual account using the
  instance name as the service name is used, in the format NT
  SERVICE\. Services that run as virtual accounts access
  network resources by using the credentials of the computer account in
  the format \$. When specifying a virtual
  account to start SQL Server, leave the password blank. If the virtual
  account fails to register the Service Principal Name (SPN), register
  the SPN manually. For more information on registering a SPN manually,
  see Register a Service Principal Name for Kerberos Connections.
  Note Note
  Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.

  The following table lists examples of virtual account names.

  Default instance of the Database Engine service: NT SERVICE\MSSQLSERVER
  Named instance of a Database Engine service named PAYROLL: NT SERVICE\MSSQL$PAYROLL
  SQL Server Agent service on the default instance of SQL Server: NT SERVICE\SQLSERVERAGENT
  SQL Server Agent service on an instance of SQL Server named PAYROLL: NT SERVICE\SQLAGENT$PAYROLL


What is Oracle equivalent for Linked Server and can you join with SQL Server? In SQL Server I can create a view that is a join between two tables that are in completely different servers using Linked Server. If I change one of the servers from SQL Server to be Oracle, can I still do the same?

I need the join table to be in Oracle

Yes, you can.
In Oracle, this is called "Heterogeneous Connectivity" This Oracle admin manual has details. The basic outline is

Install ODBC drivers on Oracle server
Configure ODBC to talk to the SQL Server DB (create a system DSN)
Test the ODBC
Configure Global_Names parameter to false.
Create initodbc.ora file to configure Heterogeneous services
Modify listener.ora to connect to both Oracle and ODBC drivers
Modify tnsNames.ora file
Reload the listener to see the above changes.
Create a DB Link from Oracle to the ODBC connection.
Test via SQL Select statement.

This article walks through an example case of doing the above steps with details.


Unable to fix ROW size too large even with innodb_file_per_table I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large"

Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table`
Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline..
    SQL Error: 1118

We might just be hitting the limits about we can do in the way our
database is currently constructed. I’ve seen
many
recommendations in similar
situations, nothing has worked.

I realize when it comes to large row size a better solution is vertical partitioning. But at the moment restructuring the database is not an option. Is it possible to fix this in configuration? Some of the answers to similar questions make it seem like it is.

In lots of reports I found people were using VARCHARS, which end up
increasing the row sizes, and people recommended moving to TEXT or BLOB,
in some of our problematic tables we’re using LONGTEXT, I tried converting them all to TINYTEXT and still ended up with my row size issue

What I have tried unsuccessfully, on multiple servers, some of which were with fresh installs of MySQL and fresh databases.

First always make sure the following set, before creating any databases:

innodb_file_format              = Barracuda
innodb_file_per_table           = 1

Then a few of the various changes I tried:

Increase the log file size up to 8G -
‘innodb_log_file_size = 8G’
‘ROW_FORMAT=COMPRESSED’ on an individual table - I was unable to get this to work.
‘innodb_default_row_format = dynamic’ globally
‘internal_tmp_disk_storage_engine=MyISAM’ - Bug
report
Increase page size ‘innodb_page_size = 64K’ - Doc
info

Complete innodb settings:

innodb_file_format              = Barracuda
innodb_large_prefix             = 1
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_thread_concurrency       = 16
innodb_write_io_threads         = 16
innodb_read_io_threads          = 16
innodb_buffer_pool_size         = 256M
innodb_log_file_size            = 8G

I did also find a bit of information on a bug
report saying that the error
is incorrect, and it was supposed to have been corrected, but I’m not
sure if that applies here. Maybe we are actually ok, and it can be
ignored? When he says
it’s not a bug, I’m not sure he means it’s not an error?

[3 Jun 2014 19:28] Daniel
Price

  The “Row size too large (> 8126)” error is not a bug.

Set the following variable in your MySQL conf file and try

innodb_strict_mode=OFF


Slow import for users .DMP files in Oracle 11G After upgrading my DBMS Oracle 10g to 11g, when it will import user files, they are slower than the old version.

A .DMP file in Oracle 10g with the size of 20GB takes almost 1 hour to import, but Oracle 11g version takes almost 3 hours for the same .DMP file.

Export script:

NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss' ; export NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS=',.'; export NLS_NUMERIC_CHARACTERS

exp system/datapar1 file=copy.dmp log=exp.log owner=username \
    buffer=4096000 recordlength =65535 statistics=none \
    feedback=500000 consistent=yes

Import script:

NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'; export NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS=',.'; export NLS_NUMERIC_CHARACTERS

imp system/password file=copy.dmp log=imp.log fromuser=username \
    touser=newusername ignore=yes

Could anyone help me with this?

It could be that one of the imp default parameters for Oracle 10G is different in Oracle 11G.  I would try adding the

buffer=4096000

parameter to the imp command to see if that helps.  That parameter is available on both exp and imp commands.


Oracle 19c cannot create master key I cannot create master key in oracle 19c...
I set parameter in sqlnet.ora like as:

WALLET_LOCATION=
  (SOURCE=
    (METHOD=file)
    (METHOD_DATA=
       (DIRECTORY=/opt/oracle/product/19c/db_home/admin/ALF/wallet/tde)))

Also set parameter TDE_CONFIGURATION:

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;

After I restart oracle and performed following commands:

alter session set container=CDB$ROOT;
administer key management create keystore identified by "mypassword";
administer key management set keystore OPEN identified by "mypassword";

And tried to create master key:

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'master key' IDENTIFIED BY "mypassword" WITH BACKUP USING 'masterbackup';

And I always get following error:

ERROR at line 1:
ORA-28362: master key not found

Do you know, how I resolve this issue? Although, I performed commands by instruction...
Thank you.

So what instructions did you follow?

Below is an example with your commands, where I did not use sqlnet.ora at all, because it is optional in 19c:

SQL> !mkdir /u01/app/oracle/admin/MIN19_O71/wallet

SQL> alter system set wallet_root='/u01/app/oracle/admin/MIN19_O71/wallet' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             297795584 bytes
Database Buffers          759169024 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL> alter system set tde_configuration="keystore_configuration=file" scope=both;

System altered.

SQL> administer key management create keystore identified by "mypassword";

keystore altered.

SQL> administer key management set keystore OPEN identified by "mypassword";

keystore altered.

SQL> !ls -l /u01/app/oracle/admin/MIN19_O71/wallet
total 0
drwxr-x---. 2 oracle oinstall 25 Dec 30 16:01 tde

SQL> !ls -l /u01/app/oracle/admin/MIN19_O71/wallet/tde
total 4
-rw-------. 1 oracle oinstall 2555 Dec 30 16:01 ewallet.p12

SQL> administer key management set key using tag 'master key' identified by "mypassword" with backup using 'masterbackup';

keystore altered.

SQL> !ls -l /u01/app/oracle/admin/MIN19_O71/wallet/tde
total 12
-rw-------. 1 oracle oinstall 2555 Dec 30 16:02 ewallet_2019123015024590_masterbackup.p12
-rw-------. 1 oracle oinstall 4171 Dec 30 16:02 ewallet.p12

SQL>


View Specific Transaction in LDF There was an update statement which was ran in our production environment which should not have been run.

I wanted to know if there was a way I can find when and who ran that specific update statement. I no longer have the corresponding .trn files but I do have the .mdf and .ldf file from that specific day.

Is it possible to find this without the .trn files?

The short answer is maybe. You can read the SQL Server transaction log (that is your .LDF file) in an online database. Using the fn_dblog function. This post describes that process.

There is a slightly longer answer, though:

Why/how do you have your .mdf and .ldf from that day? If you had a crash consistent copy of them - in other words, a Volume Snapshot Service (VSS) orchestrated snapshot of the files with a tool that is SQL Server aware - then you are in good luck. If the snapshot was just taken randomly with some other tool that doesn't instruct SQL Server to freeze/thaw the IO before and after the snapshot there is a chance that your log file and data file are not 100% consistent (as in from the exact same moment in time) and it won't be of any use.

The steps you could go down are:

Attach that MDF and LDF (note since you want to read the log file, you can't do an attach rebuild log - you need to attach both so see the note above). Attach them as a new Database, lest you overwrite the existing database entirely and then someone comes after you with a question that says "how do I find out who overwrote the production database while trying to find out who dropped a table" it could get messy then.
Look to the link I shared above and start seeing what you can see in the log file.

You can also do things like - figure out who has permissions. Honestly, as a DBA, when an object gets dropped there is a shared responsibility there. The person who did the drop, and the person who let the person who did the drop do the drop. When I'm on dad duty, and one of my kids does something a little silly that mom would never allow when on mom duty, we're both in trouble when mom comes home. They shouldn't have done that, but I shouldn't have been answering questions on DBA.SE and I should have been controlling their access to whatever mess they caused. It's sort of like that. So this is a good reminder about least privilege.


Numeric variables in psql I am running a number of queries in psql using for a time interval where a part of the query goes like

 where g2.datetime between g1.datetime+'19 minutes' and g1.datetime+'21 minutes'

i.e. I want an interval around 20 minutes after g1.datetime

If I want to do this several times, both to make it easier and less error prone (I am also refering to 20 minutes a couple of times in the query). I intended to do something like:

\set timelag 20
\set before :timelag-1
\set after :timelag+1

if I do a

select :timelag, :before, :after;

this seems to work nicely, but obiously, I am just concatenating strings (so the query is really 'select 20, 20-1, 20+1'), because if I do try make what I need:

\set beforemin '\'' :before ' minutes\''

it turns out that what I have is '20-1 minutes' - that does not work as intended in the query.

Is there any possibility to make psql variables work as numbers or is that beyond the scope of those so I will have to define the query in some other language or as a function?

Not sure of the question, but psql doesn't have variables at all. It's just a macro language.

\set p 50
\set z 50::numeric(20,5)

SELECT pg_typeof(:p), pg_typeof(:z);
 pg_typeof | pg_typeof
-----------+-----------
 integer   | numeric
(1 row)

\set after '''1 day''::interval'
\set before '''1 day''::interval'
SELECT 1 WHERE now() BETWEEN now() - :before AND now() + :after;


Is there a way to force inheritance of a procedure's schema to all procedures called within it If I have a stored procedure schema1.proc1 and it selects from table1, it correctly selects from schema1.table1. However, when schema1.proc1 executes proc2, it tries to find and execute dbo.proc2.

There is no dbo.proc2 so it returns an error that stored procedure is not found.  It never looks for schema1.proc2 even though sys.sysdepends links schema1.proc1 to schema1.proc2. However, there is also a schema2.proc2, but the error returned is just that proc2 is not found or does not exist.

Is there any way other than making that user's default schema = schema1 to have the stored procedure execute the procedure from its own schema (i.e. assume schema1.proc2 should be executed prior to searching for dbo.proc2)?

I know that we could simply preface proc2 with schema1 explicitly, but we are trying to avoid having to find all stored procedure calls within the stored procedures to append the schema prefix of the calling procedure to the procedure being called.

I can see from sys.sysdepends that it correctly links proc1 to the correct database object proc2, that is also associated with schema1, but for some reason it does not seem to look for that proc2, but instead for dbo.proc2.

Please let me know if there is some database level setting that I am just missing to tell the compiler which to check first.

Honestly, the right way to do this is to fix the code. Schema designation should always be explicit. You can use synonyms in the meantime to mask the problem and buy you time:

CREATE SYNONYM dbo.proc2 FOR schema1.proc2;

However that doesn't help if you have two procedures in the database, schema1.proc1 and schema2.proc2, and they both say EXEC proc2, because they're both going to follow the path of the synonym.

The other thing I highly recommend is to not use schemas as a division for storing different objects with the exact same name. I can see the purpose in a development environment, where you want to easily switch schemas to test your code against a QA or test schema, but this should be wholesale, and schema1.Employees and schema2.Employees should not both be valid at the same time - a better way to have this type of independence would be databases rather than schemas (IMHO).

It shouldn't be "quite a chore" to find all the stored procedures that reference other stored procedures without schema references

You can easily find all the objects that are referenced without a schema:

SELECT CallerSchema = caller_sch.name, CallerName = callers.name,
  dep.referenced_entity_name, caller_mod.definition
FROM sys.sql_expression_dependencies AS dep
  INNER JOIN sys.procedures AS callers
  ON callers.[object_id] = dep.[referencing_id]
  INNER JOIN sys.schemas AS caller_sch
  ON callers.schema_id = caller_sch.schema_id
  INNER JOIN sys.sql_modules AS caller_mod
  ON callers.[object_id] = caller_mod.[object_id]
WHERE dep.referenced_schema_name IS NULL;

You can also do this with brute force parsing, though this is a little fuzzier, and assumes your schema names are not common enough to cause false positives (and that the schema isn't referenced anywhere - frankly the above is much more reliable):

SELECT CallerName = callers.name, CallerSchema = caller_sch.name,
    CalleeName = callees.name, CalleeSchema = callee_sch.name,
    Definition = caller_mod.definition
FROM sys.sql_dependencies AS dep
  INNER JOIN sys.procedures AS callers
  ON callers.[object_id] = dep.[object_id]
  INNER JOIN sys.schemas AS caller_sch
  ON callers.schema_id = caller_sch.schema_id
  INNER JOIN sys.procedures AS callees
  ON callees.[object_id] = dep.referenced_major_id
  INNER JOIN sys.schemas AS callee_sch
  ON callees.schema_id = callee_sch.schema_id
  INNER JOIN sys.sql_modules AS caller_mod
  ON callers.[object_id] = caller_mod.[object_id]
WHERE caller_mod.definition NOT LIKE N'%' + callee_sch.name + N'%';

Also sysdepends (you shouldn't be using this anymore, there are more modern views for this) just reports dependencies. It does not dictate optimizer access path.


How to capture client host name in postgresql? pg_stat_activity system view has client_address and client_hostname, but it always empty or null. Do I need to tweak anything to update the view?

You need to set log_hostname to on in postgresql.conf


How to implement optional query parameters I have a relatively big (10^7 rows and growing) table and I want to query some rows from it.
Since table is this big - I want to use every perfomance boost that I can.

My query has optional parameter (let's call it agent_id) - it may be NULL or may have some numeric value.
The column agent_id in table is NOT NULL.

I have thought of two ways to create such a query, here are simplified examples:

Query 1:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and c.agent_id = nvl(agent_id$i, c.agent_id)

Query 2:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and (
        user_id$i is null
        or
        c.agent_id = user_id$i
    )

Is there any perfomance advantage for either one?

I guessed that second one will be slightly faster, because it can evaluate only half of expression, excluding the need to evaluate other half - but it's only a guess, and I want to hear what experts have to say about this issue.

You should not use user_id$i is null or c.agent_id = user_id$i. The optimizer is not able to handle the 2 branches separately and you will end up with a full table scan. Here is a simple blog post about it: link

The NVL version (c.agent_id = nvl(agent_id$i, c.agent_id)) is slightly better. There is a feature called NVL optimization, where the optimizer creates 2 branches from an NVL predicate and performs an UNION ALL on them. This is also demonstrated in the above blog post. This works on very simple queries like the one you posted above, but if you have more parameters like this, it will not work and you will end up with full table scan again.

You can also rewrite your query to use UNION ALL as already suggested in the comment.

To be honest, I do not recommend any of the above.

I have seen this countless times, where for example there was a form with multiple search fields, and clicking on search eecuted a query with lots of columnX = nvl(paramX, columnX) predicates. That will perform poorly, because the above small optimization does not work on complex queries with a lot of parameters.

The same goes for UNION and predicate pushing. If you have a predicate outside of the UNION that could be pushed down to the branches, the database can do that. It works with simple queries, but if you a complex query with multiple UNION, that may or may not happen.

Such queries cause a lot of performance problems. Developers think it results in simpler code and it is elegant to handle separate cases with a single SQL. The database is not good at that. It can not optimize complex queries for multiple outliers based on multiple parameters. If you want performance, you have to work for it. You should change the SQL based on the parameter. With your example, if a value is specified for the parameter use this:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and c.agent_id = agent_id$i;

If there is no value specified for the parameter use this:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1;

With such a simple query it does not make sense, but as your query gets more complex with more predicates, that is the way to go.


What is the easiest way to get started using databases with real data? I have a project that could benefit from using a database, but I have no experience with databases, don't have access to a server, and have relatively little experience working with things living server-side.

If I'm going to have to tackle a learning curve, I'd prefer to learn something with broad applicability (such as SQL) but would settle for learning something like Access if it is sufficiently powerful for the task I'm currently trying to tackle. Of course, I'd also rather not drop $150 on Access if it can be helped since I'm just tinkering.

I've downloaded LibreOffice Base as well as something called SQLiteBrowser, but I wanted to check first before I invest time learning those particular applications and their flavors of SQL whether those tools will be sufficient for what I want to do.

I want to be able to:

import data from a CSV or from Excel
run queries that equate to "select x where this is that and this contains that and any of these contain that"
write(?) a new field which indicates those results which match a given query

Again, I'm willing to learn, but it would be nice not to have to learn a bunch of intermediate stuff about IT before I can focus on learning databases and, if necessary, the particulars of a given application.

Charles,

You mentioned MS Excel in your comment so it's pretty much safe to assume you're in a Microsoft environment. You definitely have much power if you know how to mess with a database management system.

If you're doing some serious data analysis, I'd say go for enterprise databases like Oracle, SQL Server, MySQL, DB2, etc., which are Relational Databases. It's good to note that there are also non-relational databases out there that are gaining traction in the database market.

Since you're probably on a Microsoft environment, I suggest you go for SQL Server. You can ask your IT department if you have an enterprise database already in place. If none, you can download the "express" version of SQL Server (see link on the other comment). While you're there read about the limitation of an express edition. The express edition is FREE.

The express edition is a fully functioning, production-ready version of the SQL Server although it's limited in some ways (storage capacity, memory usage, etc). You can even run a Reporting Services off the express version. Look for "SQL Server Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search" --> download here: http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx

If you think that there is a necessity for an enterprise-wide database in your company (assuming your company doesn't have it yet), go ahead and buy the Developer version. The developer version contains everything that the Enterprise version of SQL Server has. If you are testing what SQL Server can do in an enterprise setting, this is the version you want to get your hands on.

Note that you cannot use the developer version for production. It's intended for testing only. The good thing is, when your company database is ready for prime time - production - you can easily "switch" the developer version to the licensed SQL Server instance.

As you start learning SQL Server, it is just fitting to start learning TSQL, which is SQL Server's implementation of the SQL language.

You mentioned you want to learn running some queries, so I suggest you start with:

Data Manipulation Language (DML) - SELECT, INSERT, UPDATE, DELETE,
MERGE
Data Definition Language (DDL) - CREATE, ALTER, DROP database
objects (Tables, Views, Constraints, Index, Stored Procedure, etc)

The SQL Server 2012 Books online is also a great starting point in learning SQL Server: http://msdn.microsoft.com/en-us/library/ms130214.aspx

The SQL Server community is also a great resource. Follow me on Twitter @MarlonRibunal. Some tips on where to find necessary SQL Server skills:

Books
Blogs

SQL Server Central
SQL ServerPedia (Now Toad World)
List of SQL Server Bloggers (Blog Directory)

User Groups - there is this Professional Association for SQL Server (PASS). Good resource for anything about SQL Server - events, people, etc. Explore the site to learn what the organization is all about.
Events

SQL Server User Group Meetings/Meetups - Check if there's a PASS Chapter near you - http://www.sqlpass.org/PASSChapters.aspx
SQL Saturday Event - free, one day quality conference/training for SQL Server (database administraion, development, business intelligence, etc.)

#sqlhelp hashtag on Twitter - your helpline on Twitter. I suggest you start following those people that used the #sqlhelp tag - either they were asking for help related to SQL Server or answering a question.
SQL University - collection of topic-specific blogs
SQL Server Study Group Meetup - assemble your members and study for SQL Server Certification exams or just for enhancement of SQL Server knowledge


SQL Blocking, SPID of -2 We have some blocking going on in our database and when I run sp_who2, in the BlkBy field, I see "-2" for two of the rows.  What does this mean?  Running SQL Server 2008 R2

I see "-2" for two of the rows. What does this mean?

It means you have an open DTC transaction managed by an external ITransaction interface but no Sessions currently using it.

you have to use KILL UOW where UOW = UnitOfWork associated with that transaction instead of the session id.

---- in text mode run below query

select 'kill ' , req_transactionUOW
from master..syslockinfo
where req_spid = -2


Why does importing a 12 GB .sql file take more than 36 hours? I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB.

Considering the triggers and stored procedures are at the end of the .sql, I only think MySQL should be adding data and key indexes.

The site.sql was generated using this command from another server:

mysqldump -R -e --databases site --add-drop-database --add-create-database --add-drop-table -C --single-transaction --triggers

What's taking so long?

Try this:

$ ps -ef|grep [m]ysql

Identify the process id then

$ strace -cp <pid>

Leave it 10 seconds or a minute then ^C. That will tell you where the process is spending its time, e.g. it could just be waiting for the disk if you seen read and write dominate.


How to write a query which recursively returns all foreign key references to a column? I need to obtain a list of foreign keys which refer to a given column, as well as all foreign keys which refer to those keys, and so on. Order does not matter. I have

SELECT
    s.name,
    t.name,
    c.name,
    fk.name
FROM
    sys.foreign_key_columns fkc
    INNER JOIN sys.foreign_keys fk
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c
        ON c.object_id = fkc.parent_object_id
        AND c.column_id = fkc.parent_column_id
    INNER JOIN sys.tables t
        ON t.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas s
        ON s.schema_id = t.schema_id
WHERE
    fkc.referenced_object_id = OBJECT_ID(N'dbo.MyTable')
    AND fkc.referenced_column_id = (
        SELECT TOP 1
            column_id
        FROM
            sys.columns
        WHERE
            name = 'MyColumn'
            AND object_id = OBJECT_ID(N'dbo.MyTable')
    )

However, this just returns the foreign keys which immediately reference the column, not the entire tree of foreign keys referencing this column.

Use a recursive CTE, it's essentially no different to the examples that involve HumanResources.Employee, except that you're joining across more tables. Instead of EmployeeIDs, you're collecting object_id and column_id combinations.


Oracle 11g how to change nls_characterset only for one table or tablespace Is it possible to change nls_characterset only for one table?
If it not, could I change or create new tablespace with different nls_characterset?

The simple answer: no.
The character set is a characteristic of the entire database.


A Query in the Query Store shows high Logical Reads When I look at the Query Store reports for Top Resource Consuming Queries,
there is one query that skews the report so much that I can barely see the next query because this one is so high.

Highest one:
Total logical reads: 12956022792
execution count:     2458
plan count:          6

The next highest one:
Total logical reads: 76670280
execution count:     106
plan count:          1

What are some recommendations for improving this query?

Here is the plan:
https://www.brentozar.com/pastetheplan/?id=B1AlsOMn-

Remove the OPTION(FORCE ORDER). This greatly limits the optimizer's freedom to find a good plan.
Modify the Index Seek indexes to include the columns referenced in the following Key Lookup operators (Output List and Predicate)
Capture actual (post-execution) plans for representative executions.
Consider adding OPTION (RECOMPILE) if different executions require different plan shapes to run well, or OPTIMIZE FOR hints for the main cases.

If nothing else, attempt to capture a post-execution plan for a problematic case, as the basis for your tuning efforts.


Why to put unstructured data in NoSQL DBs? The statement that NoSQL databases are better for unstructured data because of their schemaless nature can be found everywhere. Frankly, I don't understand that. A human voice recording is often be used as an example for unstructured data. I can store it in a relational database with datatype BLOB, in a well defined schema. Can somebody explain?

Check the comparison where NoSQL has better results.

NoSQL databases can store structured, semi-structured and unstructured data. Their main advantages focus on semi-structured (JSON, XML, not all fields are known) and unstructured.

But, you can safely store BLOB in a RDBMS, e. g., Oracle Database and many others relational databases.

  The people talking about NoSQL be a better sollution to store
  unstructured data, not always had built an IT infrastructure, and
  they're replaying what they heard.

  Also the trend to think that newer is always better push to this.

But some scenarios NoSQL+unstructured data could be better: to scale out, hundreds of db instances, big data and cloud.


Common TVF in SQL server to get results from differnt schema I have been using SQL Server for past one month and I need a suggestion from SQL Server folks to help me on this use case.
The tables below are just to explain about the idea that I am looking for.
I have tables in different schema like
MyDb.dbo.Festivals
MyDb.India.Festivals
MyDb.China.Festivals
MyDb.USA.Festivals

I am writing a table valued function without any schema prefixed in it like
CREATE FUNCTION getFestivals()
RETURNS TABLE
AS
RETURN
(
 SELECT * FROM festivals
)

As I haven't applied any schema, it defaults to dbo and creates the TVF as dbo.getFestivals(). Now I have created synonyms for all other schemas
CREATE SYNONYM India.getFestivals  FOR dbo.getFestivals;
CREATE SYNONYM USA.getFestivals  FOR dbo.getFestivals;

I tried to query like select * FROM MyDb.India.getFestivals() and still it returns the festivals from dbo.festivals and not india.festivals. I understand that though the synonyms were created it just executes the select query in the dbo schema context and not in india schema context.
I want suggestions on how to have a common table valued function that will query based on the schema prefixed, i.e. MyDB.India.getFestivals() should get festivals from India and MyDB.USA.getFestivals() should return festivals from USA.
Question
Is there a way I can have a table valued function that can query based on the schema context?
The only possible way I can think of is to create the same table valued function in all schemas.
Caveats

I have to stick to table valued function only and the above use case is a sample scenario to explain my problem.

Is there a way I can have a table valued function that can query based
  on the schema context?

No. The TVF is bound to the table at the time of creation, not at execute time, so you won't be able to dynamically access different tables from a single function.

  The only possible way I can think of is to create the same table
  valued function in all schemas.

That would be the cleanest method. Another approach is to create a UNION ALL view like the example below and pass the desired schema as a parameter. This will not perform as well since all the referenced tables will need to be scanned.

CREATE VIEW dbo.vw_Festivals
AS
          SELECT 'dbo' AS SchemaName, Col1, Col2 FROM MyDb.dbo.festivals
UNION ALL SELECT 'India' AS SchemaName, Col1, Col2 FROM MyDb.India.festivals
UNION ALL SELECT 'China' AS SchemaName, Col1, Col2 FROM MyDb.China.festivals
UNION ALL SELECT 'USA' AS SchemaName, Col1, Col2 FROM MyDb.USA.festivals;
GO

CREATE FUNCTION getFestivals(@SchemaName sysname)
RETURNS TABLE
AS
RETURN
(
    SELECT Col1, Col2
    FROM vw_Festivals
    WHERE SchemaName = @SchemaName
);
GO


Identical query slower when searching for `T` rather than `F` I have a legacy database that uses char(1) with 'T' and 'F' for boolean values. The only values in the field are T and F, of the following queries, the first is instantaneous, the second takes 30 seconds.

select top 50 * from vw_Movement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved = 'F'

select top 50 * from vw_Movement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved = 'T'

The execution plans are very similar but the slower one uses a different index.

I fix the issue by changing the sql to:

select top 50 * from vw_Movement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved <> 'F'

This gets the db to use the same index as the first query and executes instantly, but I'd prefer to understand what's actually happening. I'm pretty green at reading execution plans, but some pointers as to what's might be going on would be appreciated.

From reading around, it seems that the expected number of records meeting the criteria would impact performance, the table in question has many more F than T:

movisresolved
------------- -----------
T             70054
F             2734444

What's happening here?

EDIT:

The index that's fast for T:

CREATE NONCLUSTERED INDEX [ix_comms_composite3] ON [dbo].[tb_MOVMovement]
(
    [MOVTypeId] ASC,
    [MOVIsResolved] ASC,
    [MOVRequiresResolution] ASC,
    [MOVSupplierId] ASC,
    [MOVArrangementId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
       SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
       ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]

After a bit of prompting from the comments, this only occurs when run against the view, not on the table directly ie:

select top 50 * from tb_MOVMovement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved = 'F'

select top 50 * from tb_MOVMovement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved = 'T'

Are both equally quick, view definition here: http://pastebin.com/raw.php?i=FER0bqwh

Table script: http://pastebin.com/nSpSah7b
Poor Index: http://pastebin.com/V7qEpqsY
Execution Plans: https://drive.google.com/file/d/0B5O3tLIEkg56bWhXcU5KcldPa2s/edit?pli=1

As already stated in the responses below your request it is most certainly due to the unbalanced nature of F and T causing SQL Server to prefer scan over seek.
Though this is quite some simplification as you are querying a view containing quite a lot of tables and WHERE as well as JOIN conditions.

In case you query "[...] and MOVIsResolved = 'F'" the SQL Optimizer most likely prefers a SCAN over a SEEK, as the given result is that large.
with "[...] ='T'" it prefers a SEEK as the result set is small enough.
And finally with "[...] <>'F'" the optimizer has no other choice but to SCAN and check each entry, whether the column contains not 'F'. In the end for you it is quite clear "<> F" equals "= T" as you know there can only be these 2. SQL Server does not know this.
Therefore the resulting execution plans for "<> 'F'" and "= 'F'" are the same.

As an alternative you might wanna look into OPTION( OPTIMIZE FOR UNKNOWN ), thought I do not know, whether this actually works with hard coded queries as yours.


Restore in Full Recovery model with no transaction log backups Just realized one of our databases has been in Full Recovery mode but we've not been making log backups. On a nightly basis, we've been running a job that runs this T-SQL command to do the database backup:

BACKUP DATABASE [foo] TO  DISK = N'J:\NAV Backup\foo.bak' WITH NOFORMAT, INIT,  NAME = N'Foo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

After searching the intertubes, I'm still uncertain about what would have happened if we would have had to restore with only just the .bak file (no transactional log file or backup of the transaction log). Would we have been able to get back to the point of the last nightly backup?

Assuming a restore would have worked, would the new transaction log been has big as the old (lost) one (~300GB in our case)?

Yes, you should be able to restore and you'd be at the point in time of the backup.

Restoring from a backup will literally create a physically identical structure as existed at the point of the backup - data and log files will be the same size, so yes, you will still have a 300 GB log file if that was its size at the time of the backup (even if it was only 1% full).

Now, you need to do one of two things once you're back up and running:

Turn on transaction log backups to better protect your data in the event of another failure in the future.
Switch to simple recovery mode. Normally (there are always exceptions), this will prevent your transaction log from growing as much as it has. Your ability to restore remains the same as in full recovery and not bothering to take log backups.

For a lot more information about what you should do next (including what to do about your 300 GB log file), please take the time to read this question and its answers - yes, it's a lot of information, but it's going to be invaluable, I promise:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Your gut reaction may be to try to shrink the log file to as small as possible, but that's not necessarily a good idea.


Upgrade Advisor Or DMA (Database Migration Assistant) We are migrating from SSRS 2012 to SSRS 2017.
MS documentation suggests - "Even though you are migrating rather than upgrading the installation, consider running Upgrade Advisor on your existing installation help identify any issues that could affect migration."
In the Upgrade Advisor page, they are talking about upgrading to SQL 2014 and NOT SQL Server 2017.

Is the upgrade Advisor still relevant to check for potential upgrade issues if we are migrating to SSRS 2017?

Can the DMA (Database Migration Mssistant) tool be used to check for SSRS 2017 upgrade? OR is it only relevant for DB Engine upgrade checks?

Thanks for the help!

The list on the page you reference is the versions of SSRS that are supported to upgrade FROM.  So, basically any version of SSRS from 2008 onwards is supported to upgrade to SQL 2017.
https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/migrate-a-reporting-services-installation-native-mode?view=sql-server-ver15

...one of the following supported versions of a Reporting Services native
mode deployment to a new SQL Server Reporting Services instance:
SQL Server 2016 (13.x)
SQL Server 2014 (12.x)
SQL Server 2012 (11.x)
SQL Server 2008 R2
SQL Server 2008

Yes, the migration assistant is useful, as it mentions it will check for non-standard configurations of the source SSRS installation to make sure that nothing weird will happen.

By running Upgrade Advisor, you can find out about custom settings
that might not be supported in a new SQL Server installation.

My preference though is not to migrate/update SSRS but to do a fresh deployment from source control (upgrade the project to the target version) and go from there.  This means that you will need to recreate the schedule for any reports, etc, etc.  But this is a good time to go over your SSRS installation and cull the cruft that has built up.  Look at the execution history, find out which reports are actually used!  Make sure that they are sharing data sources and you don't have any wonky connections that are over privileged.  It's more work but you are making your environment better by taking the time.


Why is the output of this query being returned like this? I have a T-SQL query :

select
    CAST(0 as float(53)),
    CONVERT(nvarchar(max),CAST(0 as float(53)),128)

the output of which is

0  ,  0.0E0

Can some one explain why it is not 0 , 0?  The database is SQL Server 2008.

Syntax for CONVERT:

 CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

[style] for float data type :

0 (default)   A maximum of 6 digits. Use in scientific notation, when appropriate.
1 Always 8 digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.
126, 128, 129 Included for legacy reasons and might be deprecated in a future release.

You can use below command to get expected result :

CONVERT(nvarchar(max),CAST(0 as float(53)),0)


How do I place an UpdateLock on a row with an index Let's assume I have a table like this on a SQL Server 2014:

CREATE TABLE Accounts (
    Id int IDENTITY(1,1) NOT NULL,
    OwnerId int NOT NULL,
    Balance bigint NOT NULL
CONSTRAINT PK_Account PRIMARY KEY CLUSTERED (Id ASC),
CONSTRAINT UQ_OwnerId UNIQUE NONCLUSTERED (OwnerId)
)

Let's assume that there is a row with Id=1, OwnerId=1 and Balance=100.

I need to select the Id for a specific Account (only the Id) but at the same time would like to lock the row against updates.

In the first session I execute the following statement (in a transaction):

SELECT Id FROM Accounts WITH (UPDLOCK, ROWLOCK) WHERE OwnerId = 1;

In the second session I execute the following statement (in another transaction):

UPDATE Accounts SET Balance = 500 WHERE Id = 1;

Both statements complete without being blocked, because the first statement locks on the unique index. What can I do to force the second statement to wait?

Solutions I've already come up with, but don't like:

Select the whole row (SELECT *) instead of only the id on the first query → This results in too much overhead in my application.
Add an INDEX=PK_Account hint to the first query to force locking on the PK → I lose performance because this results in a clustered index scan.

Is there any other way to retrieve only the Id (while using the UQ_OwnerId) and prevent any update to the row?

Two seeks, and lock on PK,

select id
from Accounts WITH (UPDLOCK)
where id = (select a.id from Accounts a where a.OwnerId=1)


Passing a value to a nested subquery I have the following query (stripped down for brevity), the aim of which is to create the w8 value used to order the results:

SELECT elements.id, [ ... ],
(SELECT
    COALESCE(craft_w8_a.weight, 0) + COALESCE(SUM(craft_w8_b.weight), 0)
    FROM `craft_w8` `craft_w8_a`
    LEFT JOIN `craft_w8` `craft_w8_b`
        ON craft_w8_b.elementId
        IN ( SELECT targetId FROM `craft_relations`
                WHERE fieldId IN (15, 16)
                  AND sourceId = elements.id)
    WHERE craft_w8_a.elementId = elements.id
) as w8
FROM `craft_elements` `elements`
[ ... ]
GROUP BY `elements`.`id`
ORDER BY `w8` DESC, `name` ASC LIMIT 100

The problem I'm having is that the second nested subquery (the one in the left join) is unable to find the column elements.id from the initial select.

From what I've found searching around SQL only passes values one level deep, and I've been unable to find a suitable work-around.

Is it possible to force SQL to pass a value deeper than one level? Or is there a way of modifying the query to not use another subquery, but still get the same result?

Sorry if I'm doing anything stupid or missed anything obvious, SQL is not my strong point!

To my knowledge, this limitation pertains to SQL specifically in MySQL. All the other SQL products that I am aware of do not have it.

There does not appear to be a solution to this problem in general: you cannot force a column reference to be recognised deeper than one level in MySQL. However, there is an easy workaround in your specific case: replace the elements.id in the innermost query with craft_w8_a.elementId:

SELECT elements.id, [ ... ],
(SELECT
    COALESCE(craft_w8_a.weight, 0) + COALESCE(SUM(craft_w8_b.weight), 0)
    FROM `craft_w8` `craft_w8_a`
    LEFT JOIN `craft_w8` `craft_w8_b`
        ON craft_w8_b.elementId
        IN ( SELECT targetId FROM `craft_relations`
                WHERE fieldId IN (15, 16)
                  AND sourceId = craft_w8_a.elementId)
    WHERE craft_w8_a.elementId = elements.id
) as w8
FROM `craft_elements` `elements`
[ ... ]
GROUP BY `elements`.`id`
ORDER BY `w8` DESC, `name` ASC LIMIT 100

This is an equivalent substitute because whatever craft_w8_a.elementId will be passed to the IN subquery is going to match elements.id based on the filter in the query that passes the value.


Transactional replication altering tables and adding stored procedures I have a database that is replicated in several locations. It is setup as transactional replication. However, now I need to add more columns to the table. Add more stored procedures and etc. Hence, if I make the change in the publisher, will it replicate across the subscribers or will it negatively impact the replication?

Suggestions will be very helpful.

You should be able to make DDL changes as described in this article .

Make Schema Changes on Publication Databases

make sure you dont use SSMS. Use T-SQL only.

How to: Replicate Schema Changes (Replication Transact-SQL Programming)
specify a value of replicate_ddl for @property and a value of 1 for @value.

EXEC sp_changepublication
@publication = N'publication_name',
@property = N'replicate_ddl',
@value = 0;

EDIT : Below is what you need to take care, if you want to avoid reinitializing  subscription(s).

@sync_method=N'character'

Script out the replication and look for sp_addpublication --- @sync_method=N'character'
If the sync_method is “character” and you add a column to the publication, it will generate a message and ask to reinitialize subscription(s).
Change the sync_method to “native”.

@replicate_ddl=0

By default, the @replicate_ddl is set to 0 “false”, meaning no schema changes are replicated.
Changing the value to 1 “True” will allow changes done on publisher to propogate to subscribers (as shown in below figure).

Using T-sql:

EXEC sp_changepublication @publication='kin_test_Trepl'
    ,@property='sync_method'
    ,@value='native'
-- sync_method = native ==> this will not reinitialize subscribers
-- sync_menthod = character ==> this will generate below message:
--DDL replication is forcing reinitialization because either publication'kin_test_Trepl' uses character mode bcp, or timestamp/identity column is being replicated as base type only for article 'numbers'.
--Reinitialized subscription(s).
GO

EXEC sp_changepublication @publication='kin_test_Trepl'
    ,@property='replicate_ddl'
    ,@value=1
-- 1 = allow ddl changes to be replicated to subscribers
-- 0 = DO NOT propogate ddl changes to subscribers
GO

Then we can add the column to table that is replicated  as below:

e.g: ALTER TABLE numbers ADD comment varchar(200)

Note that the column is nullable. If it is not null then it will generate below error:

  Msg 4901, Level 16, State 1, Line 1
  ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'comment' cannot be added to non-empty table 'numbers' because it does not satisfy these conditions.


What does this Oracle TO_CHAR format do: "FM99990.90" I have this number format and not able to find examples of how it will look. It is not listed on Oracle website. This below SQL is what I have and not sure what the result will look like since I don't have access to Oracle SQL at the moment to be able to run and see the result.

to_char(deductionAmount, 'FM99990.90')

Does anyone know? Deduction amount is in this format most likely: xx.xx

Of course it is listed/documented.

Format Model Modifiers

  FM

  Fill mode. Oracle uses trailing blank characters and leading zeroes to
  fill format elements to a constant width. The width is equal to the
  display width of the largest element for the relevant format model:

  Numeric elements are padded with leading zeros to the width of the
  maximum value allowed for the element. For example, the YYYY element
  is padded to four digits (the length of '9999'), HH24 to two digits
  (the length of '23'), and DDD to three digits (the length of '366').

  ...

Example:

SQL> select to_char(1.5, 'FM99990.90') from dual;

TO_CHAR(1
---------
1.50

SQL> select to_char(1.5, '99990.90') from dual;

TO_CHAR(1
---------
     1.50

SQL>


connect to psql and run SQL command from command line I'm trying to write a quick line in bash that will use psql to connect to a database and delete all entries in two tables:

\connect some_db
DELETE from some_table
DELETE from another_table

I'd like to do this in one line of bash scripting like so:

psql -U <username> -c "\c some_db; DELETE from some_table; DELETE from another_table"

but it seems like I can't do that because the \c command creates a new context that the subsequent commands don't apply towards.

What's the right way to do this in a line or two from the CLI?

You don't need the \c in the command specified with -c, you can specify the database as a parameter to psql:

psql -U <username> -d some_db -c "DELETE from some_table; DELETE from another_table"


Inline expansion of inline composite expression I want something like this:

SELECT 1, (CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END).*;

...hoping to get 1 | 2 | 3. But in PostgreSQL 11 this query throws:

  ERROR:  record type has not been registered

and without * it obviously returns 1 | (2,3).

Is this possible?

The (2, 3) has type record, i.e. Postgres doesn't know which (or even how many) columns it contains. You can use the .* syntax only with known ("registered") types.

You could define your own tuple type and use that instead of a record:

CREATE TYPE tuple AS (a int, b int);
SELECT 1, (CASE WHEN TRUE THEN '(2, 3)'::tuple ELSE '(4, 5)' END).*;
SELECT 1, (CASE WHEN TRUE THEN (2, 3)::tuple ELSE (4, 5)::tuple END).*;

If you absolutely need to have an expression that returns records, you can cast those to tuples afterwards only by going through a text representation:

SELECT 1, ((CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END)::text::TUPLE).*;

(online demo)


SQL: How to Select Multiple Row Data for the Same ID? My data is like this:

ID      col1
0000001 00020
0000001 00023
0000001 00019
0000001 00010
0000003 00030
0000003 00020

I want to find which IDs have a combination of 2 values in the column 'col1' (appearing at least once each); for example, for the values 00020 and 00023 here I would get the ID 0000001 only.

If I interpret of your requirement correctly:

SELECT id
    FROM tbl
    WHERE col1 IN ('00020', '00023')
    GROUP BY id
    HAVING GROUP_CONCAT(DISTINCT col1 ORDER BY col1) = '00020,00023'

Performance might benefit from INDEX(col1, id).

A different query might include HAVING (COUNT DISTINCT col1) > 1, but the implied presence of dups may mess it up.

To select ids that have at least one of those col1s:

SELECT DISTINCT id
    FROM tbl
    WHERE col1 IN ('00020', '00023')

To select rows that have at least one of the values in ('00020', '00010') and in ('00023', '00033'):

SELECT id
    FROM tbl
    WHERE EXISTS( ( SELECT 1 FROM tbl
                      WHERE id = a.id
                        AND col1 IN ('00020', '00010') ) )
      AND EXISTS( ( SELECT 1 FROM tbl
                      WHERE id = a.id
                        AND col1 IN ('00023', '00033') ) )

(There are probably other ways, but this was the first one that came to mind.)


Stored Procedure taking over 10 seconds I have this stored procedure that is taking over 10 seconds to run. It returns a data set (list of files) to the application. Any suggestions on how to speed it up?

Details about the plan. It looks like query 14 in the plan takes 98% relative to the batch. Inside query 14 is a sort that is 62% of query 14.

I'm using SQL Server 2016. Estimated query plan.

Here is the actual plan. Took 5 min. and 21 seconds to run.

First insert

The actual plan for the first insert statement reveals some things you should improve:

I would add an OPTION (RECOMPILE) hint to this query. This will allow the optimizer to produce a plan taking into account the runtime values of variables. This should simplify the plan quite a bit.
Once the hint above is in place, you should ensure the Uploads table has an index with ClientID, UploadCompleteDttm as the leading keys. This will provide the required sort order, and allow a seek when @startdate_local is not null.
The index should also include FileName, FileUploadID, ProcessErrorText to avoid lookups. An example index definition would be:

CREATE INDEX IndexName
ON fileManager.FileUploads (ClientID, UploadCompleteDttm)
INCLUDE (FileName, FileUploadID, ProcessErrorText);

The Clustered Index Scan on FilesReceived looks to be very expensive. Five and a half billion rows are tested (across multiple scans) to return around 5,000 rows. This cost is well hidden by the residual predicate (as shown in Sentry One Plan Explorer):

Add an index to FilesReceived keyed on FileUploadID. If that index already exists, and the scan persists in the actual execution plan after the previous steps listed here have been applied, use a FORCESEEK hint on the FilesReceived reference. You may also try OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));.

Second insert

The problematic part that jumps out at me is:

This corresponds to the code:

(select count(1)
     from fileManager.FilesReceived fr
      join fileManager.Tracking t on fr.FilesReceivedID = t.FilesReceivedID
     where parentfileid = z.FilesReceivedID) as 'HasChildren'

The rest of the execution plan is quite large, but does not seem too terrible. I would still try OPTION (RECOMPILE) again, for the same reasons as for the first query.

If that does not improve performance enough on its own: I would remove the code fragment shown above from the query, persist the result rows (only 5,239 for the example given) in a separate temporary table, then find the HasChildren value as a separate step. I would also rewrite the final INSERT to use an explicit TOP (5) clause instead of using SET ROWCOUNT 5.

Ensure the FilesReceived table has an index on ParentFileId.


JOIN ON syntax error in Microsoft Access 2016 I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed by the ON syntax when removing that (the latter for clearer reasons based on prior knowledge of SQL JOIN ON syntax).

Here is the code:

SELECT Column1, Column2, Table1.Column3
FROM Table1
JOIN Table2
    ON Table1.Column3 = Table2.Column3
   AND Column4 = '{NAME}'
ORDER BY Column4;

Is there something incorrect about this syntax? By the way, this is using ANSI-89 Syntax.

Access requires INNER JOIN. The simple JOIN (which should default to INNER JOIN according to the SQL standard) is not allowed in Access.


Active/active SQL Server Clustering in Installation Center (setup.exe) I like to know which option to choose in the setup.exe in SQL Server 2012/2008? What I have here is Windows Active/Active Cluster setup with one quorum and one msdtc configured. I installed my first node by choosing "New SQL Server failover cluster installation", now I am not sure what to choose on the second node. On the second node should I choose the same "New SQL Server failover cluster installation" OR choose "Add node to a SQL Server failover cluster" option. Remember it is going to be A/A SQL Server Cluster.
Appreciate your answers.

You want to choose "Add node to a SQL Server failover cluster".  When creating a Failover Cluster Instance (FCI), you will always do one initial cluster installation for the instance, then add nodes to that installation.

Keep in mind that Active/Active and Active/Passive as descriptors is very misleading.  By it's very nature, each SQL Instance you install can only be active on a single node at any one time. To leverage multiple physical nodes at the same time, you will need to install multiple named FCIs to your cluster.

For example, let's assume a two node cluster.  If we install a single named FCI to this, our layout would look like this:

NODE1:  CLSTSQL01\SQLServer01 (Active)
NODE2:

This would be the result of you performing one "New SQL Server Failover Cluster installation" with the additional node additions for each node in your cluster.  Now let's add a second instance to it so we can utilize each node.  For this, we would install a second FCI to the cluster and end up with something like this:

NODE1:  CLSTSQL01\SQLServer01 (Active)
NODE2:  CLSTSQL02\SQLServer02 (Active)

Now, keep in mind that in a failover situation, on either node, the SQL instances could end up on the same node.  For example, if NODE1 failed:

NODE1: <<DEAD>>
NODE2: CLSTSQL01\SQLServer01, CLSTSQL02\SQLServer02 (Active)

This is the basics of how you would arrange your instances.  You will need to monitor your cluster if you want to make sure your instances are properly balanced across your nodes.


SSIS/SSAS 2016 intermittent fault: An error was encountered in the transport layer I have an intermittent fault in my SSIS ETL process when I try and process my OLAP cube.

There are no error messages in the SQL log, and there's nothing else significant running at the time. The only error message I can find in the event log is:

  An error was encountered in the transport layer.

SSIS and SSAS are SQL are all running locally on the same server, so I don't believe it's network related at all.

The same package has been deployed and has run successfully for weeks. If we manually connect to the cube it processes without any problem.

Windows Server 2012 R2 6.3  (Build 9600: ) on VMWare 64gb of RAM.

Microsoft SQL Server 2016 Enterprise (SP1-CU1) - 13.0.4411.0 SQL Max mem = 48gb

My gut feel is that this is more of an SSIS issue than a SSAS issue.

Has anyone else encountered this? Can anyone reccomend a solution or workaround? Is it possible to get additional debugging information?

I think we solved this or at least found a workaround.

I believe the problem is a timeout when connecting to SSAS when it has been inactive for some time. It has nothing to do with the actual processing of the cube.

We added a step to our ETL process which queried the OLAP cube before the processing step was triggered. This causes SSAS to 'wake up' before it is needed.

Since we added this step we haven't had a repeat of this problem.


An admin (GUI or shell tool) for managing all kinds of databases I needed an interface/admin (web or desktop) from which I could manage all the databases in my system. Currently I use separate admins for each (phpmyadmin for mysql, pgadmin/phppgadmin for postgres, sqlite manager in firefox for sqlite etc). Whenever I have to work with all the databases, the only option I am left with is shell access to all the databases.

I wanted to know if there is an admin which can manage all kinds of databases in a single interface, possibly similar to the one used by jennifer widom in her coursera lectures?

A bit too late, but the product Data Utensil aims to be just that. As of now, it supports SQL Server, My SQL, Oracle & Firebird (embedded).

Disclosure: I work for Maxotek, who created Data Utensil.


Is it possible to import DBs information_schema, performance_schema and mysql from mysql-community-server-5.6.x to mariadb-server-5.5.x? Is it possible to import the following DBs from an existing mysql-community-server-5.6.x configuration to a fresh mariadb-server-5.5.x :

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

I guess no here because the table structure of mysql.users should be different between mysqld and mariadb.

Maybe some of it.

information_schema is not a real set of tables; it is in-memory data that pretends to be tables.  So, a definite "no" for that.

performance_schema is mostly transient, so don't bother.

mysql changes between versions, so it would be risky to do so.  Use mysqldump (or other tool) to dump routines.  Do SHOW GRANTS ... to get grant statements to replay; this is somewhat tedious.  Start fresh for most things in mysql.

Also, Oracle 5.6 to MariaDB 5.5 is a step backward in many aspects.  Can't you go to 10.0, at least?  That version has more of what is in 5.6.


Fast way to import 2GB csv file into SQL Server 2008 I am new to database,I am using SSMS 2008 R2 to import 2GB of data from a .csv file.

It is taking around 115mins to import.

Can anybody let me know any better way to perform this import.

You need to use bcp.exe or SSIS. Read Import and Export Bulk Data by Using the bcp Utility (SQL Server) and The Data Loading Performance Guide. Also, read How to Analyze SQL Server Performance to be able to tell why something is slow.

After you've read those, come back with a more actionable question, including structure of table, csv structure, repeatability of the job, access issues etc.


Restore SQL Server database using Windows Powershell 3.0 I'm trying to restore a SQL Server database with a PowerShell script, but I'm having problems.

Here is the error I'm getting:

  Exception calling "SqlRestore" with "1" argument(s): "Restore failed
  for Server 'WUSFK250042-OLU\SQLSERVER2008R2'. " At line:48 char:1
  + $smoRestore.SqlRestore($server)

Here is my code:

#clear screen
cls

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$backupFile = "C:\SafewayRT\SafewayRTFUll.bak"

#we will query the database name from the backup header later
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "WUSFK250042-OLU\SQLSERVER2008R2"
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#restore settings
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
$smoRestorePercentCompleteNotification = 10;
$smoRestore.Devices.Add($backupDevice)

#get database name from backup file
$smoRestoreDetails = $smoRestore.ReadFileList($server)

#display database name
"Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["Safeway_LogixRT"]

#give a new database name
$smoRestore.Database =$smoRestoreDetails.Rows[0]["Safeway_LogixRT"]

#specify new data and log files (mdf and ldf)
$smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

#the logical file names should be the logical filename stored in the backup media
$smoRestoreFile.LogicalFileName = $smoRestoreDetails.Rows[0]["Safeway_LogixRT"]
$smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $smoRestore.Database + "_Data.mdf"
$smoRestoreLog.LogicalFileName = $smoRestoreDetails.Rows[0]["Safeway_LogixRT"] + "_Log"
$smoRestoreLog.PhysicalFileName = $server.Information.MasterDBLogPath + "\" + $smoRestore.Database + "_Log.ldf"
$smoRestore.RelocateFiles.Add($smoRestoreFile)
$smoRestore.RelocateFiles.Add($smoRestoreLog)

#restore database
$smoRestore.SqlRestore($server)

You're going to have to get into that exception to see what the problem is. Powershell keeps track of errors for you in a system variable called $Error, which is a heap (i.e. index 0 is the most recent error). I usually do something like this:

$e = $error[0]
$e.Exception
$e.Exception.InnerException
$e.Exception.InnerException.InnerException
...

Until I find the real error. It could be a problem with the actual restore (i.e. database with that name already exists, bad paths for the physical files, etc). But until you get to the root of that exception you won't know!


trigger to update row number I have a MariaDB table

id | key  | year | .... | position
1  | key1 | 2004 | ... |  3
2  | key1 | 2011 | ... |  2
3  | key1 | 2017 | ... |  1
4  | key2 | 1994 | ... |  2
5  | key2 | 2004 | ... |  1
6  | key3 | 2004 | ... |  1
7  | key4 | 1980 | ... |  2
8  | key4 | 2011 | ... |  1

Position is the row number within they Key field order by year descending

How can I create a trigger to autofill position when I insert, delete or update any row. thanks

If you are running MySQL8.0, you could drop the column position, and create a view like this:

CREATE VIEW view_amariadbtable AS
SELECT
  id,
  `key`,
  year,
  RANK() OVER (PARTITION BY year ORDER BY year DESC,id DESC) as position
FROM amariadbtable

BTW, you should NOT use reserved words as column name (key)


Can I have 'Query Store' automatically on when I create new databases? Query Store is available in SQL Server 2016+ and on Azure SQL DB it is on by default. With an on-premises SQL Server, it has to be started on each database individually.

Is there a way I can have Query Store on by default when new databases are created?

Query Store is not active for new databases by default.

If you turn it on for model database, it will be on for any new databases you create in the same instance.  I tested in SQL 2017.

To turn it on in model database run following:

ALTER DATABASE model SET QUERY_STORE = ON;

I suggest you read this article and decide if you want to turn it on for all databases and settings you want to modify.

Best Practice with the Query Store


Table "normalization" and tradeoffs It's been a while since my databases course in university, so bear with me as I try to be unambiguous here.

In the domain I'm working in, there are Sources and Destinations.  Both have 2D coordinates (an X and Y coordinate).  Both Source and Destination have separate data in addition to the coordinates.  Finally, there is a one-to-many relationship between Source and Destination.

Idea #1

Use two tables, one for each type of coordinate:

SourceID  |  XCoord  |  YCoord |  Other Source Columns |
--------------------------------------------------------
332       |  33      |  4      |          ...          |
423       |  213     |  342    |                       |
211       |  365     |  12     |                       |

DestinationID  |  Source ID  |  XCoord  |  YCoord |  Other Dest. Columns |
--------------------------------------------------------------------------
455            |  332        |  24      |  4      |          ...         |
892            |  332        |  653     |  32     |                      |
112            |  211        |  5       |  122    |                      |

Idea #2

Use three tables, where Coordinates are stored in their own table, but everything else is the same:

CoordinateID  |  XCoord  |  YCoord |
------------------------------------
1             |  33      |  4      |
2             |  213     |  342    |
3             |  365     |  12     |
4             |  24      |  4      |
5             |  653     |  32     |
6             |  5       |  122    |

SourceID  |  CoordinateID |  Other Source Columns |
---------------------------------------------------
332       |  4            |         ...           |
423       |  5            |                       |
211       |  6            |                       |

DestinationID  |  SourceCoordID |  CoordinateID |  Other Dest. Columns |
------------------------------------------------------------------------
455            |  332           |   1           |          ...         |
892            |  332           |   2           |                      |
112            |  332           |   3           |                      |

The differences I can think of are these:

The second one will perform worse: in order to get the (x, y) coordinates of a source or destination, the source or destination table must be joined with the coordinate table.
The second one is more maintainable: only one table needs changing if, say, a timestamp needs to be stored with every (x, y) pair.

Are those differences correct?

(I'm happy to remove the next question if it's too open ended or off-topic.)  Are there other significant differences?

JOIN operations are not as expensive as you might expect. Many times the benefit of normalization will far outweigh the "costs" of joining the tables, specifically if you have "common" coordinates that are used by more than one destination/source.

I would likely model this using two tables, one for coordinates, and one to describe the points (sources and destinations).

For instance:

CREATE TABLE dbo.Coords
(
    CoordinateID INT NOT NULL
        CONSTRAINT PK_Coords
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , x INT NOT NULL
    , y INT NOT NULL
);

CREATE TABLE dbo.Points
(
    PointID INT NOT NULL
        CONSTRAINT PK_Points
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CoordinateID INT NOT NULL
        CONSTRAINT FK_Points_CoordinateID
        FOREIGN KEY
        REFERENCES dbo.Coords(CoordinateID)
    , RelativePointID INT NULL
        CONSTRAINT FK_Points_RelativePointID
        FOREIGN KEY REFERENCES dbo.Points(PointID)
);

INSERT INTO dbo.Coords (x, y)
VALUES (1, 2)
    , (1, 3)
    , (1, 4);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (1, NULL);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (2, 1);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (3, 2);

SELECT SrcX = src_c.x
    , SrcY = src_c.y
    , DestX = dest_c.x
    , DestY = dest_c.y
FROM dbo.Points src
    INNER JOIN dbo.Coords src_c ON src.CoordinateID = src_c.CoordinateID
    LEFT JOIN dbo.Points dest ON src.RelativePointID = dest.PointID
    INNER JOIN dbo.Coords dest_c ON dest.CoordinateID = dest_c.CoordinateID;

Results:

This design allows any point to serve as the destination for any other source point, effectively allowing you to create a map of directions from one point to any other.


Should I upgrade compatibility level of MS Report Server database Recently upgraded from SQL Server 2008 to SQL Server 2012. The MS Report Server databases did not automatically upgrade compatibility level. Should I force the upgrade?

This is not a question we can decide for you. Please review this Microsoft document especially the section with heading Differences Between Lower Compatibility Levels and Levels 110 and 120. Go through each item and decide for yourself.

Once you test thoroughly with new compatibility level you will know if you have any reason to keep old compatibility level (for backward compatibility). I prefer to use new compatibility level as you can see newer versions of SQL Server only support up to 3 or 4(maximum) older versions.  If you do not do it now at some point you will be upgraded anyway.