use variable table in OLE DB source I try to export data to a flat file from OLE DB source. Here is what I do under OLE DB source editor. In SQL command Text:

declare @t
   table (customerid varchar(100),[enrollment_Date] varchar(100))
insert into @t exec master.dbo.my_stored_procedure
select * from @t

In the preview, I can see data but when I execute it, I get empty text file. Do you know why?

use SET NOCOUNT ON

SET NOCOUNT ON
declare @t
   table (customerid varchar(100),[enrollment_Date] varchar(100))
insert into @t exec master.dbo.my_stored_procedure
select * from @t

http://www.bidn.com/blogs/dustinryan/ssis/835/ssis-ole-db-source-using-table-variables-returns-no-rows


Diagnosing a maintenance plan that's ballooned in execution time Environment is SQL 2008 R2, 26GB RAM, 4-core Xeon E5-2650 @ 2.6GHz.

Please see the image below, our maintenance plan which reorganises indexes (not rebuilds). This is run on a weekly basis, and within one week, this plan went from taking ~5 minutes way up to 1hr - 1hr:15, and it has not improved since. We've had to disable this plan and work around this in another way.
I've looked at the amount of data in the database and it hasn't grown by a considerable enough volume to cause this issue.

I've been in touch with our hosting company to see if anything has changed within our environment, but they are saying nothing's changed, there's no limitations/restrictions in place on our VMs, IO seems ok. We haven't made any changes to the underlying application neither.

From what I can see, other things have started to fail within this week period too - some queries which ran perfectly fine are starting to timeout. I'm at a bit of a loss as to what could have caused this and am unsure how to retroactively find out. Could anyone point me in the direction of where to begin to discover what could have been wrong here?

Question you asked is a very generic one and people working with database have this challenge all the time.  There is no one thing you can do will give you the answer right away.  Your case is more difficult than others as your databases are hosted by third party.

Troubleshooting long running process is a science and art at the same time. Everybody has their own way and it is not unique.

First you want to make sure your reorganize process still doing the same work.  Which is not easy in this case because few factors:

Number of index.
Size of each index.
Fragmentation level of each index.

Once you check that out and confident that the amount of work is same, you can use method called ‘waits and queues’ to find out why the duration increased?

A SQL server process can be in three states.  Running, Runnable and Suspended.  I suggest you read this more here.  There is nice method for capturing wait stats for one single operation.

Once you know what resources your process was waiting for and how long (during the whole duration) the waits were, you should be able to quantify the run time. You can be blocked but underneath that is also a wait because some other process is holding the resource that you need.

As you are in a hosting environment and if you have baseline number of your hardware you should check those out too, to make sure nothing changed there. I understand you did check with them but I suggest you confirm what they are saying.

Few things to check:

storage performance
Network


Database Merging / Re-integration I am a SQL Server DBA.  I recently had the misfortune of losing a database due to drive failure. I was able to restore the data but for whatever reason, it stopped at a certain time and not all of the data was restored.

I restored the database again to a test server only to find and restore ALL of the data but the problem is, I now have to get the rest of the data that did not restore on to the server where it is supposed to be.  Can that be done?

Given all the primary key contraints and uniqueness of those keys involved, imagine we will have to renumber some of the rows that were affected because those keys have been reused once the database came back up and was operating.

So you attempted to restore the database in production but it didn’t finish? Can you elaborate more, did it finish completely, we’re there any error messages, etc.

Then you were able to successfully restore the database to a test server?

Have you verified if the backup file is good? See this article if not

https://www.mssqltips.com/sqlservertutorial/113/checking-to-make-sure-a-sql-server-backup-is-useable/

You may want to check that and if ok try to restore in production again.


Incorrect "Sensitive data columns should be classified" report Every week I'm getting a failed "Vulnerability Assessment" email report from my Microsoft Azure SQL Database that is incorrect.
It complains about columns that have is_computed column in sys.columns set to 1.
It is not possible to add a classification for those columns.

Is it possible to stop the warning?

Basically it will not fade away when we approve it as a baseline. Sometimes it does. But in most cases we will get baseline mismatch where we should go inside the database to classify those data that we approved as the baseline in the azure portal.
For more details on how to classify the data inside SQL please follow the below links.Data discovery and classification
RDBMS security


Quickly change NULL column to NOT NULL I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I execute the command

ALTER TABLE MyTable ALTER COLUMN MyColumn BIGINT NOT NULL;

the query takes forever relatively speaking. It actually takes between 10 and 20 minutes, more than twice as long as adding a check constraint. Is there a way to instantly update the table's metadata for that column, especially since I know that no row has a NULL value for that column?

@ypercube's answer does manage this partially as a metadata only change.

Adding the constraint with NOCHECK means that no rows will need to be read to verify it, and if you are starting from a position where the column does not contain NULL values (and if you know none will be added between checking and adding the constraint) then, as the constraint prevents NULL values being created from future INSERT or UPDATE operations, this will work.

Adding the constraint can still have an impact on concurrent transactions however. The ALTER TABLE will need to acquire a Sch-M lock first. Whilst it is waiting for this all other table accesses will be blocked as described here.

Once the Sch-M lock is acquired the operation should be pretty quick however.

One problem with this is that even if you know the column in fact has no NULLs the constraint is not trusted by the query optimiser which means that the plans can be sub optimal.

CREATE TABLE T (X INT NULL)

INSERT INTO T
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values

ALTER TABLE T WITH NOCHECK
  ADD  CONSTRAINT X_NOT_NULL
    CHECK (X IS NOT NULL) ;

SELECT *
FROM T
WHERE X NOT IN (SELECT X FROM T)

Compare this with the simpler

ALTER TABLE T ALTER COLUMN X INT NOT NULL

SELECT *
FROM T
WHERE X NOT IN (SELECT X FROM T)

One possible problem you might encounter with altering the column definition in this way is that it not only needs to read all the rows to verify that they meet the condition but also can end up actually performing logged updates to the rows.

A possible half way house might be to add the check constraint WITH CHECK. This will be slower than WITH NOCHECK as it needs to read all rows but it does allow the query optimiser to give the simpler plan in the query above and it should avoid the possible logged updates issue.


Manually increase PostgreSQL's table size limit of 32 TB? I need a free database software and thought of PostgreSQL. But then I read it has a table size limit of 32 terabytes, but I need more (and don't want to spend thousand/millions on high end database software like Oracle).

Is there a way to manually increase that limit? And if so, what is the penalty for doing so?

Just use a different setting for the block size:

  --with-blocksize=BLOCKSIZE

  The default, 8 kilobytes, is suitable for most situations; but other
  values may be useful in special cases. The value must be a power of 2
  between 1 and 32 (kilobytes).

Using 32 kilobytes, your table has a maximum size of 128TB.


Installed Oracle XE 11.2. How do I create a database? I'm new to Oracle, so please bear with me.

I recently installed Oracle XE Release 11.2 on a Windows 2008 Server that we have. The installation was successful. During installation, I was asked to set a password for sys, and I did that. Also, the HTTP port used was 8085.

So now I'm trying to create a new database. I have Toad Data Point, but I'm not sure how to connect to the server or how to create the database.

In a browser, I went to http://OurServer:8085/ but it asks for password. I tried logging in with sys, but it doesn't let me. I also tried logging in using my company userid (the one I use to log on to my Windows PC), but it didn't work either.

What else can I do?

Oracle 11 does not use containers so you already have a database and the default users such as sys and system and all the other oracle built in users.

Go to Computer Management/Local Users and Groups and verify that the group ora_dba exists.
Assuming this is a development project add your regular windows username to the ora_dba group. Now you can log on as sysdba using these steps:

open a command window
enter SET ORACLE_SID=XE
enter sqlplus / as sysdba

This should get you logged on as sys which you should not do normally.  For this one time create a user and grant them a role.

CREATE USER < your new user name > IDENTIFIED BY < your new password >;
GRANT DBA to < your new user name>;

Then enter exit to close the session.  Log on as your new user using Toad and you should be good to go.

I note that the operating system and database are out of support and you would be better off using the new 18xe on linux.


Full text search partial search with 'in:*' not working select to_tsvector('english', 'JAVA') @@ to_tsquery('english','ja:*');

select to_tsvector('english', 'india') @@ to_tsquery('english','in:*');

The first one is returning true while the second is returning false. How to address this?

That is because "in" is a very frequent word in the English language, so it is eliminated as a "stopword".
You could use the simple text search configuration, but I suspect that full text search is not the right tool for this. Trigram indexes work better for substring searches, but nothing will really work well for strings as short as two characters.


Avoiding MySQL Fabric to become a single point of failure I managed to install and configure MySQL Fabric that manages multiple MySQL Server nodes. It works very well since I tested it by connecting to the Master node directly and played with the data (insert, delete). The slave nodes become identical to the master.

Now, the fabric server should manage everything. Hence, the application should connect to the Fabric and not to the master node. This way, when the Master fails, Fabric will nominate a new Slave.

Q1: How can I avoid Fabric server itself from becoming a single point of failure?

Q2: Anyone has experience implementing Fabric? Is what I am planning to do is the best practice?

Regards,

The MySQL Fabric node itself is not currently redundant but as stated in the MySQL Fabric FAQ:

  There is currently only a single instance of the MySQL Fabric node. If that process should fail then it can be restarted on that or another machine and the state and routing information read from the existing state store (a MySQL database) or a replicated copy of the state store.

  While the MySQL Fabric node is unavailable, Fabric-aware connectors continue to route queries and transactions to the correct MySQL Servers based on their cached copies of the routing data. However, should a Primary fail, automated failover will not happen until the MySQL Fabric node is returned to service and so it's important to recover the process as quickly as possible.

In terms of best practices, this MySQL Fabric blog post provides an end-to-end worked example of using MySQL Fabric and then the MySQL Fabric white paper adds more of an overview and context information.

Finally, you can watch the MySQL Fabric webinar replay and read Q&A transcript - there are some good, technical questions + responses there.

If you're looking for more discussion on the topic then there's also a dedicated forum for MySQL Fabric, Sharding, HA & Utilities.


Can't login with Workbench I use Ubuntu 16.04 and MySQL 5.7.16 and trying to connect from Windows10 with Workbench; My aim is to be able to login from any computer, anywhere --- I have no intention to allow only one IP.

I get failed connect after filling credentials:

method    | TCP/IP
hostname  | IPv4
port      | 3306
username  | I use the MySQL root user (the one I used to login to PHPmyadmin with)
password  | MySQL root password.

The error is:

  Is not allowed to connect to this MySQL server

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
# 127.0.0.1

Why it's not likely a firewall issue:

Given I use CSF-LFD as firewall software, I don't use the native UbuntuFireWall (UFW). Yet, I did allow port 3306 ongoing and outgoing in csf.conf, and restarted CSF via sudo csf -r.

netstat -plnt:

    tcp    0    0 0.0.0.0:3306    0.0.0.0:*    LISTEN -

My question:

Given these circumstances, what else can prevent my remote login?

In MySQL, a user contains 2 mandatory parts:

user name - like root, or sam
host allowed for this user to login, like - localhost, 10.30.10%, 10.% etc.

all these users are different and could have different credentials:

root@localhost
root@10.%
root@10.30.10.%

each from example list - separate user and have unique access list

So you have a choice:

simple - change wildcard for root, I would not recommend it
create another user with remote access:

like:

CREATE USER 'other_root'@'%' IDENTIFIED BY 'other_root_password';

GRANT ALL ON *.* TO 'other_root'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;


Does Oracle 11gR2 is supported on RHEL 8 or Oracle Linux 8? Oracle (afaik) did not updated their compatibility matrix yet. Can I run Oracle 11gR2 (which still is in extended suporte) in the new OS?

Most likely you can run it without any issues but it is not supported by Oracle. As Phil suggests in his comment this combination will never be supported as you are mixing an almost out of support database (extended is over on December 2020) with a just added Linux release.

Best solution is use Oracle 12.2 which, in my experience, is a minimal problem migration target for most applications using Oracle 11.  The 12.2 release allows an easy upgrade to Oracle 18 or 19 when the time comes.


SQL regex to identify alphanumeric values I am trying to exclude values like 3708.DK in sql. I'm using pattern like LIKE '%0-9.%' and '%[0-9.A-Z]%' and its not working .

Please suggest.

If you're attempting to exclude rows in a table, then a LIKE comparison would work.

Take this minimally complete verifiable example:

IF OBJECT_ID(N'tempdb..#LikeTest', N'U') IS NOT NULL
DROP TABLE #LikeTest;

CREATE TABLE #LikeTest
(
    d varchar(50) NOT NULL
);

INSERT INTO #LikeTest (d)
VALUES ('this is a 3708.dk test')
    , ('this is another test');

Here's the pertinent bit:

SELECT lt.d
    ,  Match = CASE WHEN lt.d LIKE '%[0-9][0-9][0-9][0-9]\.[a-z][a-z]%'
                ESCAPE '\'
            THEN 1 ELSE 0 END
FROM #LikeTest lt;

Results:

╔════════════════════════╦═══════╗
║           d            ║ Match ║
╠════════════════════════╬═══════╣
║ this is a 3708.dk test ║     1 ║
║ this is another test   ║     0 ║
╚════════════════════════╩═══════╝

The LIKE comparison above says "match where the data contains 4 consecutive digits, followed by a period, followed by two alpha characters in the range of [a-z].  Be aware the LIKE statement is collation-sensitive; if you have a server or database collation that is case sensitive, my example LIKE won't match upper case alpha characters.  You can force a case-insensitive search by adding a COLLATE clause to the LIKE comparison, like this:

SELECT lt.d
    ,  Match = CASE WHEN lt.d LIKE '%[0-9][0-9][0-9][0-9]\.[a-z][a-z]%'
                ESCAPE '\'
                COLLATE SQL_Latin1_General_CP1_CI_AS
            THEN 1 ELSE 0 END
FROM #LikeTest lt;


Is there a document that specifies un-installation of SQL server 2016 R server Basically I want to remove all components of R server from SQL Server 2016 install?

Try this guide, maybe it will work for you: Cleanly Uninstalling Stubborn SQL Server Components by Aaron Bertrand.

The easiest way: Control Panel -> Programs & Features -> and uninstall every program that contains the words "SQL Server".

or, using PowerShell:

Get-ChildItem HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall |
select @{Name='Guid';Expression={$_.PSChildName}}, @{Name='Disp';Expression={($_.GetValue("DisplayName"))}} |
where-object {$_.Disp -ilike "*SQL*R"} |
where-object {$_.Guid -like '{*'} |
% {"rem " + $_.Disp; 'msiexec /x "' + $_.Guid + '"'; ''} > uninstallR2.bat


How to split a row based on a delimiter into multiple rows and retain the ID and ordering by using a single SQL statement for MySQL Scenario:

A MySQL table
A Delimiter (e.g. comma) is used to separate entities inside a string store in a column of the MySQL table
MySQL table contain 2 columns - ID and Countries_Involved

=======================================
| ID |   Countries_Involved           |
=======================================
| 1  | Japan, India, France           |
| 2  | Benin, Chad, Chile, Iraq, Iran |
=======================================

How do I use a single SQL statement (very good to have, if possible) to generate a table below:

====================================
| SN | ID | Country    |  Ordering |
====================================
| 1  | 1  | Japan      | 1         |
| 2  | 1  | India      | 2         |
| 3  | 1  | France     | 3         |
| 4  | 2  | Benin      | 1         |
| 5  | 2  | Chad       | 2         |
| 6  | 2  | Chile      | 3         |
| 7  | 2  | Iraq       | 4         |
| 8  | 2  | Iran       | 5         |
====================================

SQL is a real (but **DECLARATIVE**, not imperative) programming language! This has its pros and cons, see here.

But without the sarky comment from @RickJames :-), I was going to say the same thing. You'll always get problems like this when you don't properly normalise (you have what are called repeating groups - a breach of Codd's Rule no. 2 - scalar values).

As @RickJames says, you'll have to programme your way out of this one using an imperative language, but NOT SQL.


Need help finding inactive users in my database I am trying to figure out how to get this figured out.

Problem:

Users of inactive customers are receiving scheduled maintenance notifications

Indicators:

Organizations have a custom binary field to indicate if they are Active
Users have two binary fields to indicate if they can access the
system

Solution:

Identify any users that have their Active and/or CanLogIn values set
to 1 AND
Are associated by OrganizationID to an Organization with the custom
Active field set to 0 (zero)

You can find the logins on an instance by querying sys.server_principals and checking the is_disabled column to know if they are still useable.

SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE type IN ('U','S')

The other notes you have in your question though indicate that this request is based on an application table. You state things like Active and/or CanLogIn values set to 1 and associated by OrganizationID to an Organization. All of that would tell me this is custom tables within an application database. Which unless you provide the table definition(s) there is not much we can do to help.


Inserting Values into Temp Table Can anyone help me to work out below T-SQL code. I am facing issue while adding the second value. The first value gives the state of SQL Service and second value gives the SQL last restart time. I am not a core developer so can anyone help me .

if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1
([Current Service State] nvarchar(200),
[SQL_Server_last_RestartOn]nvarchar(200))

insert into  #temp1
EXEC master.sys.xp_servicecontrol N'querystate',N'MSSQLSERVER',
(select min(login_time) from master..sysprocesses) as SQL_Server_last_RestartOn

Give this a try

IF object_id('tempdb..#temp1') IS NOT NULL
    DROP TABLE #temp1

CREATE TABLE #temp1 (
    [Current Service State] NVARCHAR(200)
    ,[SQL_Server_last_RestartOn] NVARCHAR(200)
    )

INSERT INTO #temp1 ([Current Service State])
EXEC master.sys.xp_servicecontrol N'querystate'
    ,N'MSSQLSERVER'

UPDATE #temp1
SET SQL_Server_last_RestartOn = (
        SELECT min(login_time)
        FROM master..sysprocesses
        )

SELECT *
FROM #temp1


Difference beetween mysql 5.5 and 5.6 I have a big performance problem beetwen my local mysql instance and the server.
I have this request :

DELETE a
FROM a
LEFT OUTER JOIN
( SELECT MAX(id) as id,
`v_id`,
`v_statut`,
`c_controle`,
`c_d_controle`
 FROM `a`
 GROUP BY `v_id`,
 `v_statut`,
 `c_controle`,
 `c_d_controle`
 ) AS t1 ON `a`.id = t1.id
 WHERE t1.id IS NULL;

The purpose of the request is to remove duplicate identified by the GROUP BY. I have already created key for the group by.

In my local server (5.6.17) this request is about 15 seconds on 700 000 lines, on the server(5.5.43) this request is endless.

Is there any mysql tuning or SQL request optimization that can help me.

Thanks.

CREATE TABLE :

Create Table"
temp_import_vigipresse  "CREATE TABLE `temp_import_vigipresse` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_creation` datetime DEFAULT NULL,
`vigipresse_id` int(11) NOT NULL,
`vigipresse_tele_id` varchar(8) DEFAULT NULL,
`inutile_date` date DEFAULT NULL,
`vigipresse_statut_depot` tinyint(4) NOT NULL,
`vigipresse_commentaire` varchar(255) DEFAULT NULL,
`vigipresse_type_parution` tinyint(4) DEFAULT NULL,
`vigipresse_admission_id` int(11) DEFAULT NULL,
`date_depot` datetime DEFAULT NULL,
`etablissement_code_regate` varchar(6) DEFAULT NULL,
`etat_date_film` tinyint(4) DEFAULT NULL,
`date_film` date DEFAULT NULL,
`vigipresse_code_hld` tinyint(4) DEFAULT NULL,
`vigipresse_lot_stp` tinyint(4) DEFAULT NULL,
`code_statut_resultat` tinyint(4) DEFAULT NULL,
`commentaire_resultat_controle` varchar(255) DEFAULT NULL,
`valeur_resultat_controle` varchar(255) DEFAULT NULL,
`code_critere_controle` tinyint(4) NOT NULL,
`code_departement_postal_controle` varchar(2) DEFAULT NULL,
`numero_oqs_saisi` smallint(6) DEFAULT NULL,
`vigipresse_numero_parution_constate` int(11) DEFAULT NULL,
`inutile_numero_parution` varchar(255) DEFAULT NULL,
`vigipresse_type_parution_declare` tinyint(4) DEFAULT NULL,
`vigipresse_date_depot_prevue` date DEFAULT NULL,
`vigipresse_quantite_articles` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `vigipresse_id_statut_code`

(`vigipresse_id`,`vigipresse_statut_depot`,`code_critere_controle`),
KEY `vigipresse_id_statut` (`vigipresse_id`,`vigipresse_statut_depot`),
KEY `vigi_unique`
(`vigipresse_id`,
`vigipresse_statut_depot`,
`code_critere_controle`,
`code_departement_postal_controle`)
) ENGINE=MyISAM AUTO_INCREMENT=730705 DEFAULT CHARSET=utf8"

EXPLAIN LOCAL (5.6):

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,PRIMARY,temp_import_vigipresse,ALL,NULL,NULL,NULL,NULL,228470,NULL
1,PRIMARY,,ref,,,5,qspresse.temp_import_vigipresse.id,10,"Using where"
2,DERIVED,temp_import_vigipresse,ALL,vigi_unique,NULL,NULL,NULL,228470,"Using temporary; Using filesort"

EXPLAIN SERVER (5.5)

Endless request

EXPLAIN the SELECT subquery.  (This will workaround the point that jynus brought up.)

Your KEY vigi_unique can be made somewhat better by adding id onto the end of it.  This will change the EXPLAIN to say "Using index" because it will be a "covering index".

But the real reason for 5.6 to be faster is ...

The subquery is after "LEFT JOIN", so 5.5 evaluates it over and over (I think), and
5.6 will create an INDEX (on the fly) for the subquery.

Suggest using EXPLAIN FORMAT=JSON SELECT ... on 5.6 to see a little more.

You should consider moving to InnoDB.

Another way to "dedup" is to create a new table (with a UNIQUE index to prevent future dups), populate it with INSERT ... SELECT DISTINCT ..., then RENAME TABLE to move it into place.


Identifying if spid on SQL Server is in rollback after low disk space On a test box I wanted to get an idea of how long adding an identity field would take to a table with roughly a half billion rows.  The process ran for about 10 hours and we noticed it was getting dangerously low on disk space on the lun for the log.

We addressed the log disk space issue by removing some other items but I'm afraid we might have been too late as it got down to like 10MB.

Is there a way to know immediately if the query began rolling back the alter table statement due to insufficient space?  I know a transaction that has had a rollback will show up in sp_who with a status of rollback.  I also know a transaction that was purposely killed will have a status of killed/rollback.

But, is there a way to know immediately if a process is being rolled back due to insufficient space before the rollback is complete and the message is returned to SSMS?

Looking at sp_WhoIsActive, sp_Who, sp_Who2 it still shows the original alter table as the command with a status of suspended - I'm concerned that it's actually rolling back however.  Is there a way to tell?  Am I mistaken and it would show up as rolling back like a killed spid would?

If it fails it fails, but I'd like to know if I'm moving forward or moving backwards.

We use sp_whoisactive which has a column called Status. When a query is rolling back you can see status = rollback. It also gives a percentage completion.

We're using SQL 2014, but I'm pretty sure the status was there on 2012 as well


Selectively ignoring tuples from a table The problem that we are tackling with a data mining application is best described with an illustrative example.
There is a sample table myTable, which is defined as follows:
CREATE TABLE myTable
(
  id        INT UNSIGNED AUTO_INCREMENT,
  colA      VARCHAR(8),
  colB      VARCHAR(12),
  revFlag   CHAR(8), -- 'REVISED' or any other value, including NULL

  PRIMARY KEY(id)
);

Any tuple with a revFlag value of REVISED takes precedence over any other tuple with the same value for colA, as long as the revFlag value of the latter tuple is not REVISED.  In other words when we select rows from the table we skip all rows for which the revFlag value is not REVISED and there exists a row with the same value for colA for which the revFlag value is REVISED.
We populate the table as follows:
INSERT INTO myTable(colA, colB) VALUES ('XSR0KA3V', 'OLD-O7RAR81X'),
('4F2JG71O', 'OLD-E71BE63L'), ('MML3HN48', 'OLD-B02PFB63'),
('5H0MWVSB', 'OLD-V70XLGHT'), ('JW73ZX0J', 'OLD-KME1GXQF'),
('XZV0EY0G', 'OLD-N06BURDF'), ('9HBQZ88V', 'OLD-76HSPUAL'),
('YI5AT6G4', 'OLD-X8KAWD7Z');

INSERT INTO myTable(colA, colB, revFlag) VALUES
('XSR0KA3V', 'NEW-O7RAR81X', 'REVISED'),
('MML3HN48', 'NEW-B02PFB63', 'REVISED'),
('9HBQZ88V', 'NEW-76HSPUAL', 'REVISED'),
('YI5AT6G4', 'NEW-X8KAWD7Z', 'XYZ'),
('Z8H2B5KY', '3RINJV0K', 'REVISED');

Naturally SELECT * FROM myTable yields the following:
+----+----------+--------------+---------+
| id | colA     | colB         | revFlag |
+----+----------+--------------+---------+
|  1 | XSR0KA3V | OLD-O7RAR81X | NULL    |
|  2 | 4F2JG71O | OLD-E71BE63L | NULL    |
|  3 | MML3HN48 | OLD-B02PFB63 | NULL    |
|  4 | 5H0MWVSB | OLD-V70XLGHT | NULL    |
|  5 | JW73ZX0J | OLD-KME1GXQF | NULL    |
|  6 | XZV0EY0G | OLD-N06BURDF | NULL    |
|  7 | 9HBQZ88V | OLD-76HSPUAL | NULL    |
|  8 | YI5AT6G4 | OLD-X8KAWD7Z | NULL    |
|  9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
| 10 | MML3HN48 | NEW-B02PFB63 | REVISED |
| 11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
| 12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ     |
| 13 | Z8H2B5KY | 3RINJV0K     | REVISED |
+----+----------+--------------+---------+

We would like to design a query that does not return any tuples that are REVISED by other tuples.  In our case the output should look like this:
+----+----------+--------------+---------+
| id | colA     | colB         | revFlag |
+----+----------+--------------+---------+
|  2 | 4F2JG71O | OLD-E71BE63L | NULL    |
|  4 | 5H0MWVSB | OLD-V70XLGHT | NULL    |
|  5 | JW73ZX0J | OLD-KME1GXQF | NULL    |
|  6 | XZV0EY0G | OLD-N06BURDF | NULL    |
|  8 | YI5AT6G4 | OLD-X8KAWD7Z | NULL    |
|  9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
| 10 | MML3HN48 | NEW-B02PFB63 | REVISED |
| 11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
| 12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ     |
| 13 | Z8H2B5KY | 3RINJV0K     | REVISED |
+----+----------+--------------+---------+

You can accomplish this with a window function like the ROW_NUMBER() function inside of a subquery or CTE like so:
SELECT id, colA, colB, revFlag
FROM
(
    SELECT id, colA, colB, revFlag,
        ROW_NUMBER() OVER
        (
            PARTITION BY colA
            ORDER BY CASE WHEN revFlag = 'REVISED' THEN 1 ELSE 0 END DESC, id DESC
        ) AS SortOrder -- Generates a unique ID for each row of the same value of colA sorted by revFlag = 'REVISED' first then by id descending
    FROM myTable
) myTableSorted
WHERE SortOrder = 1

As per my comment, the SortOrder field is generated by partitioning on colA and within that partition generating a unique incremental id that is sorted with the revFlag = 'REVISED' having priority first, and then by the myTable.id column descending (to get the latest row per colA).


Bigint and SQL Server performance I inherited a database in which many of the tables have bigint as a datatype for many of the fields, and when you see the content it does not requiere all the space that bigint offers. Does using bigint for fields that dont need it affect the database performance?

Using bigint compared to int has, at least, these potential performance drawbacks:

the data will use more pages on disk

this can affect how long it takes to read data from disk when it's not in RAM
it will also make any maintenance operations involving those fields take longer (backups, index rebuilds, CHECKDB)

these data pages will take up more space in RAM

this means you'll either need to purchase more RAM, or incur the cost of reading from disk more often

memory grants that include these columns will be larger

this affects memory-consuming query plan operators, like sorting and hashing data
as a secondary impact, this has the effect of reducing concurrency

That being said, how much practical impact these things have for you is very dependent on your specific environment and workload.

Note: the increases in disk and RAM usage issues can be mitigated by using row compression, at the cost of increased CPU usage.  As servers often have more CPU overhead than RAM, this is generally a good tradeoff (thanks to Andy for this reminder!)


Is Scalar UDF inlining broken? This SQL code returns "a" instead of "asdfg":

CREATE FUNCTION [dbo].[GetPayload2] (@ID int) RETURNS VARCHAR(300) AS
BEGIN
IF @ID = 1
RETURN 'asdfg'
RETURN ''
END
GO
SELECT dbo.GetPayload2(1)

Reproduced on both 15.0.2000.5 and 15.0.2070.41. Works fine in SQL Server 2017.
Workaround is to use WITH INLINE = OFF.

EDIT:
If you agree it's a bug, vote here please:
https://feedback.azure.com/forums/908035-sql-server/suggestions/39190126-sql-server-2019-scalar-functions-with-inline-are-b

EDIT 2:
CU2 for MS SQL Server 2019 is solving this issue :-)

Here's where the problem creeps in. The plan contains a Constant Scan defining the output.

  CASE WHEN CASE WHEN (1) = (1) THEN (1) ELSE (0) END = (0) THEN '' ELSE CONVERT_IMPLICIT(varchar(1),CASE WHEN (1) = (1) THEN 'asdfg' ELSE NULL END,0) END

Why does it down-cast to varchar(1)? I guess it takes the length of the first string it encounters during optimization (presumably the empty string) with a floor of 1. If I change the fall-through result to return 'yy' the implicit cast becomes varchar(2).

I think the best work-around would be to explicitly cast the result:

RETURN convert(varchar(300), '')

Bizarrely, it's also corrected by this:

declare @x sql_variant;
SELECT @x = dbo.GetPayload2(1);

select Result = @x, Property = SQL_VARIANT_PROPERTY(@x, 'MaxLength');

Result    Property
--------  --------
asdfg     300


Behavior of case sensitivity of RAISERROR when run remotely I have a remote SQL Server that I can connect to using a local instance of SSMS.

When raising errors, we use

RAISERROR( 'This works', 16, -1 )

Remotely, this works fine if it's all uppercase, or even if any one letter is uppercase.

raiserRor( 'Still works', 16, -1 )

If I try to run it using all lowercase though, it does not work

raiserror( 'Error', 16, -1 )

It returns the error message

  Msg 121, Level 20, State 0, Line 3
  A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Even if I comment out the lowercase raiserror statement it still fails with the same error message.

If I log on to the server and execute these statements, they all work. It only seems to be when I connect remotely that this is an issue.

Why can't RAISERROR be completely lowercase? Even if commented?

The server is an install of SQL Server 2012 Developer edition on Windows Server 2012.

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

I've used local installs of SQL Server Management Studio 2012 and 2014 and  tested against databases with 2005 and 2012 compatibility levels and the same behavior occurs.

I tested on other machines and the same results occurred. I don't currently have a machine to test outside of our companies network though.

This issue may actually be related to a very old bug with SQL Server, but not directly caused by SQL Server itself.

I found this MSDN thread which describes the exact same behavior

  Please note the strangeness of this: RAISERROR won't compile (connection time out), even if it is in a comment!

This was the accepted answer

  It turns out that the network that our SQL Server is on has an intrusion prevention system.  In that system there is a filter with the following description:

  This filter detects access to the raiserror procedure on port 1433. The raiserror procedure in a Microsoft SQL Server contains a buffer overflow vulnerability, which can be remotely exploited to execute arbitrary computer code on the affected system, thus allowing an attacker to gain complete control of the server. The procedures known to be vulnerable are: xp_sprintf, raiseerror() and formatmessage(). If the overruns are exploited, the code runs in the context of a local administrator account.

There is an old MS Security Bulletin for this bug and a more recent TechNet thread describing the same issue.

It's possible there is something on our network that I am not aware of that is filtering out anything with the text raiserror in it (but doing a poor job of it since the check is case sensitive). Not that it matters since the bug was fixed.

Since this isn't recognized behavior of SQL Server then it could likely be a network configuration issue.


What is the purpose of SET NULL in Delete/Update Foreign Keys constraints? I am probably being narrow minded, but if I create a foreign key constraint and a row gets updated or deleted, I lose that connection if the child table's column gets set to NULL.

What is the purpose of intentionally keeping these orphaned rows?

Whether set null is useful or not depends on what you have chosen null to mean in the particular context - with all the confusion and opinion around null IMO the sensible approach is for the DBA to

Choose (and document) what it means for each nullable field
Make sure it means one thing only

With those rules, consider the following use case:

You have a table 'shop' (eg individual premises)
You have a table 'retailer' (eg chains)
The 'shop' table has a lookup field referring to the key of 'retailer'
You have defined null to represent an independent shop (ie one that is  not part of a chain)
A 'retailer' closes branches to the point that you consider its shops to be independent

In this case, an on delete set null makes sense. There are other ways of modelling these business rules but this is the simplest and if it accurately fits the facts that you care about in the real world, I suggest it is perfectly ok


Get SQL server instance installation date I would like to know the date when the sql server installed on the system.
I did try to look in %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\ directory but there are so many files and folder not sure from where I can get the required information.

You could use the sys.server_principals view to get the date:

SELECT  create_date
FROM    sys.server_principals
WHERE   name = N'NT AUTHORITY\SYSTEM';

Check out server_principals on MSDN.


Is it possible to recover deleted tables? One user deleted some tables from the database. Fortunately after that the base was not compressed and did not recover.

Now I have a question. Is it possible to restore these tables, with all the contained data? Sorry if the question is "childish" but although I thought I had seen a similar topic, I searched but can't find anything.

According to Microsoft's Support website:

  To restore a database, you must already have a backup copy of your database.

This is true of essentially all data-loss recovery situations. You should consider your data utterly lost unless you have a backup from which you can restore.

Having said that, there may be options for you to recover, but every single action you take on the computer affected may make each recovery option less likely to succeed. Depending on how critical this data is, you may want to simply remove power from the computer now and image the disk drive to preserve possible forensic recovery. Other lower-impact but correspondingly-less-likely-to-succeed options may include duplicating the MDB file onto external storage.

This is only "Step 1". The next steps would involve analyzing the data you've preserved and trying to extract the data you need. With a disk image safely made, you may be able to boot the computer back up and check Volume Shadow Copy to see if there are any older revisions of the MDB file.

You need to weigh the time investment you're willing to make against the possibility that your data is simply gone. No recovery option is a guarantee and you could spend hours, days, or weeks on recovering the data that was deleted.

And having said all of that, not to needle, but having a backup routine could have avoided all of this.


SQL Server Backups on DataDomain Our sysadmins keep pushing us to use data domain and DD boost for taking our SQL Backups.  Has anyone successfully used this solution?  I know what Brent Ozar says about Data domain and sql backups. But apparently the DD boost makes it a better solution.  Has anyone used DD boost and also used SQL Backup by redgate? Just wanted to get thoughts on what you would say to your sysadmins and managers apart from this article from Brent Ozar.

Data Domains use their own deduplication and compression features, which don't always play well with SQL Server's own compression. That's the main point.

Whether this is problematic or not depends on many factors. If you're not compressing your backups with SQL Server, the Data Domain will be able to deduplicate the data more efficiently, but more data will have to travel through the network. If you're compressing your backups, the Data Domain will deduplicate less efficiently, so your sysadmin will likely push you in the uncompressed route.

Another possible issue is an higher amount of time for restores, since the DD will have to apply its magic to the data before making it available to you.

In the end, there is only one way to know for sure: test it. Take backups with and without DD for a significant amount of time (take at least the number of backups you will normally keep online - say 1 month), with and without compression. Restore those backups to a test server regularly, compare the backup/restore times with and without DD and draw your conclusions.

We tested the impact of DD on our backups and it turned out to have an acceptable impact on performance in our case, especially taking into account the advantages offered by DD. YMMV: your RTO may or may not not allow such impact.


What will be the initial size of my tempdb data file(s) after restart? sys.database_files and sys.sysfiles report on size, but that's just the current size of the file. Is there a way to find out what the initial size of the tempdb datafiles will be after it's recreated during a system restart?

Looks like it's

select name, file_id, size
from sys.master_files
where database_id = 2
and type = 0

(If necessary, the first datafile will be increased to the size of the model database).


Find the winner of each stage I am learning SQL, and I am trying to get a job. I have the following table in MS SQL Server 2014:

The table is called Game with the following fields: Name, Stage #, Score.

My goal is to write the winner of each stage with his/her name (winner of the stage is the person who earned the maximum score).

Here is the original table:

Name    Stage #         Score
George     A              10
Joe        A              10
Pete       A               9
Jane       B               7
Sally      B               6

Here is what the output should look like:

Winner Name    Score         Stage
George           10             A
Joe              10             A
Jane              7             B

How can I accomplish this task? A colleague referred me to http://sqlfiddle.com/ to help me figure this out, but this website is apparently not working well for MS SQL Server 2014 or MS SQL Server 2008. Therefore, can I receive some assistance here?

I understand some of the basic functions, such as SELECT, WHERE, GROUP BY, JOIN, and HAVING, but I am having trouble putting it all together to get the correct three-line output that I desire above.

There are tens of different ways to do this in SQL. Lets start with the simple correlated subquery (mind the fancy name, once you see and write a few of them, they are very easy to understand):

select                                -- show
    g.name, g.stage, g.score          -- all data
from                                  -- from
    game as g                         -- the table
where                                 -- where
    not exists                        -- there isn't
        ( select *                    -- any other
          from game as g2             -- from the same table
          where g2.stage = g.stage    -- and the same stage
            and g2.score > g.score    -- with bigger score
        ) ;

Another simple way would be to first find the biggest score for each stage using GROUP BY (in a subquery, either a derived table or a CTE) and then JOIN back to the original table:

-- using derived table
select
    g.name, g.stage, g.score
from
    game as g
  join
    ( select stage, max(score) as score
      from game
      group by stage
    ) as m
  on  m.stage = g.stage
  and m.score = g.score ;

-- using CTE
with stage_max as
    ( select stage, max(score) as score
      from game
      group by stage
    )
select
    g.name, g.stage, g.score
from
    game as g
  join
    stage_max as m
  on  m.stage = g.stage
  and m.score = g.score ;

A more modern way would be to use window functions (available in your SQL Server versions), i.e. the RANK() function, so first you get the "rank" of everyone per stage and then select only the ones with rank=1. This can also be done with either a derived table or a CTE:

-- window functions, using derived table
select
    w.name, w.stage, w.score
from
    ( select name, stage, score,
             rnk = rank() over (partition by stage
                                order by score desc)
      from game
    ) as w
where
    w.rnk = 1 ;

-- window functions, using CTE
with ranking as
    ( select name, stage, score,
             rnk = rank() over (partition by stage
                                order by score desc)
      from game
    )
select
    w.name, w.stage, w.score
from
    ranking as w
where
    w.rnk = 1 ;


Postgres triggers for group_id I have a table that looks like this:

CREATE TABLE transactions(
id SERIAL NOT NULL PRIMARY KEY,
value NUMERIC(10,3),
note TEXT,
group_id INTEGER);

I'd like to update the group ID automatically with a trigger, if it's not set on insert.  So something like this:

CREATE FUNCTION trigger_update_group()
RETURNS trigger AS $$
DECLARE max_group_id_ INTEGER := 0;
BEGIN
IF NEW.group IS NULL THEN
  SELECT MAX(group_id) INTO max_group_id_ FROM transactions;
  max_group_id_ := 1;
  NEW.group = max_group_id_;
END IF;
RETURN NEW;
END;
$$ language plpgsql;

The problem with this is that if I set the trigger to FOR EACH ROW, then each inserted row gets a different group_id (not to mention we run the SELECT statement for each row too).

Instead, what I was hoping for was that all the rows in a given INSERT statement would get the same group_id. Is there a way to do that (even if it involves transactions)? It seems FOR EACH STATEMENT doesn't allow access to the individual rows.  Maybe there's some way to wrap the INSERT?

Instead of triggering it by ROW you can use FOR EACH STATEMENT.

CREATE TRIGGER trg_upd_group AFTER INSERT ON transactions
    REFERENCING NEW TABLE AS inserted
    FOR EACH STATEMENT
    EXECUTE FUNCTION upd_group();

Quoted from Postgres Docs:

  FOR EACH ROW
  FOR EACH STATEMENT

  This specifies whether the trigger function should be fired once for every row affected by the trigger event, or just once per SQL statement. If neither is specified, FOR EACH STATEMENT is the default. Constraint triggers can only be specified FOR EACH ROW.

The you can define an AFTER INSERT trigger that updates all rows WHERE group_id IS NULL, assigning to them the same group_id.

CREATE FUNCTION upd_group()
RETURNS trigger AS
$upd_group$

DECLARE

    max_group_id_ INTEGER := 0;

BEGIN

    SELECT MAX(group_id) INTO max_group_id_ FROM transactions;
    max_group_id_ := COALESCE(max_group_id_, 0) + 1;

    UPDATE transactions t
    SET    group_id = max_group_id_
    FROM   inserted i
    WHERE  t.id = i.id
    AND    t.group_id IS NULL;

    RETURN NULL;

END;
$upd_group$ language plpgsql;

INSERT INTO transactions (value, note, group_id) VALUES
(12, 'note 12', NULL),
(13, 'note 13', NULL),
(14, 'note 14', NULL),
(15, 'note 15', NULL);

INSERT INTO transactions (value, note, group_id) VALUES
(112, 'note 112', NULL),
(113, 'note 113', NULL),
(114, 'note 114', NULL),
(115, 'note 115', NULL);

SELECT * FROM transactions;

id |   value | note     | group_id
-: | ------: | :------- | -------:
 1 |  12.000 | note 12  |        1
 2 |  13.000 | note 13  |        1
 3 |  14.000 | note 14  |        1
 4 |  15.000 | note 15  |        1
 5 | 112.000 | note 112 |        2
 6 | 113.000 | note 113 |        2
 7 | 114.000 | note 114 |        2
 8 | 115.000 | note 115 |        2

db<>fiddle here


Constructing 3-step sql query I have:

a table of Characters with a character_id column;
a join table of Chats with character_id and conversation_id columns;
and a table of Conversations with a conversation_id column.

I am trying to construct a query that finds the Conversation between two characters (sender and recipient) and ONLY those two characters (other conversations may exist with sender, recipient, and other characters involved - group conversations).

I'm using Ruby on Rails.

The query involves three steps:

Find all Conversations belonging to sender.
Select from these the ones that also belong to recipient.
Select from these the one that belongs only to sender and recipient.

I can't get the query quite right. The best I've managed is below.

Where am I going wrong?

The query below gives this error: PG::SyntaxError: ERROR:  syntax error at or near "GROUP"

SELECT Conversations.id FROM
(
    (
        (
            Conversations INNER JOIN ( SELECT conversation_id FROM Chats WHERE Chats.character_id=#{sender_id} ) AS sterling
                          ON Chats.conversation_id=Conversations.id
        )
        INNER JOIN ( SELECT conversation_id FROM Chats WHERE Chats.character_id=#{recipient_id} ) AS archer
        ON Chats.conversation_id=Conversations.id
    )
    GROUP BY conversation_id
    HAVING COUNT(Chats.conversation_id)=2
);

Why not just use:

SELECT id FROM Conversations
INNER JOIN (
    SELECT conversation_id FROM Chats  WHERE Chats.character_id= '#{sender_id}'
) AS sterling
    ON sterling.conversation_id = Conversations.id
INNER JOIN (
    SELECT conversation_id FROM Chats WHERE Chats.character_id = '#{recipient_id}'
) AS archer
    ON archer.conversation_id = Conversations.id;

Since those are inner join, it has to be in both sender and recipient. It will only return conversation which are in both 3 tables.

The count does not seem useful here unless you are trying to get something like people with more than 1 conversation together.

It should also work with this simpler version although it does not check for the existence of conversation_id in Conversations:

SELECT conversation_id
FROM Chats AS sterling
INNER JOIN  Chats AS archer
    ON sterling.conversation_id = archer.conversation_id
WHERE sterling.character_id = '#{recipient_id}'
    AND archer.character_id = '#{sender_id}';

Exit with count:

SELECT cv.conversation_id
FROM (
    SELECT conversation_id
    FROM Chats AS sterling
    INNER JOIN  Chats AS archer
        ON sterling.conversation_id = archer.conversation_id
    WHERE sterling.character_id = '#{recipient_id}'
    GROUP BY conversation_id
    HAVING count(distinct character_id) = 2
) ce
INNER JOIN  Chats AS ch
        ON ce.conversation_id = ch.conversation_id
    WHERE ch.character_id = '#{sender_id}';


How to pass a row as a parameter to a Postgres function? I have a function build_details(), which takes as one of its parameters, a single row/record from another table.

This is what I want to do, but it doesn't work:

SELECT build_details(
    SELECT * FROM my_table LIMIT 1,
    1000,
    TRUE)

I want to take a single row from my_table and pass it to the function so that I can make it run. How do I call the function from the terminal with a record from my_table?

It's much simpler that that. While one would expect it to be ...

SELECT build_details(my_table.*, 1000, TRUE) FROM my_table LIMIT 1;

... the actual syntax is:

SELECT build_details(my_table, 1000, TRUE) FROM my_table LIMIT 1;


Computed field when NULL I have two fields in my table : A int, B int. I want to add two computed fields (CF1 & CF2) that uses these two fields A & B.

CF1 = (case when [A]>[B] then (CF1=3) else case when [A]<[B] then (CF1=0) else (CF1=1) end end)

CF2 = (case when [A]<[B] then (CF2=3) else case when [A]>[B] then (CF2=0) else (CF2=1) end end)

This works fine, but when the two fields (A & B) are NULL, CF1 and CF2 are = 1. How do I avoid this? I mean when either one of the fields A or B is NULL, both CF1 and CF2 should also be NULL.

In other words: there should only be a value of 0, 1, or 3 in CF1 and CF2 if there is a value NOT NULL in fields A & B.

+------+------+------+------+
|  A   |  B   | CF1  | CF2  |
+------+------+------+------+
| 2    | 3    | 0    | 3    |
| 5    | 2    | 3    | 0    |
| 2    | 2    | 1    | 1    |
| NULL | NULL | NULL | NULL |
| NULL | 1    | NULL | NULL |
| 1    | NULL | NULL | NULL |
+------+------+------+------+

First, let me explain why your code doesn't work when A or B is NULL by formatting your code to show that you have 2 CASE expressions, one inside the other:

CF1 = (case
           when [A]>[B] then (CF1=3)
           else case
                    when [A]<[B] then (CF1=0)
                    else (CF1=1)
                end
       end)

If A > B is true, the result is (CF1=3). If that isn't true, and if A < B is true, the result is (CF1=0). Otherwise, if neither A>B nor A<B is true, the result is (CF1=1). This last case (the ELSE - ELSE part) covers any remaining case which means when A = B or A is null or B is null.

So to solve the issue, you need to explicitly compare A = B or compare A and B with NULL, to differentiate. As a result the CASE expression should have 4 parts, not 3. And (irrelevent to the issue) we don't need two nested expressions, we can have the result we want with one expression.

And I assume that you want the result to be 3, 1, etc and the (CF1=3) is just pseudo-code.

CF1 = case
          when A > B then  3
          when A < B then  0
          when A = B then  1
          else             null
      end

Note that the ELSE NULL is not needed as it is the default for CASE expressions. If you want, you can have even more strict and clear with your intentions code:

CF1 = case
          when (A > B) then  3
          when (A < B) then  0
          when (A = B) then  1
          when (A is null or b is null) then
                             null
          else               null     --  'ERROR: This should never happen'
      end


Benefits of table partitioning on same disk Maybe is stupid question, but I must ask to be clear with that.

I know that table partitioning is powerful mechanism of speeding data search when partitions is placed on different disks.

What I need to know what I can get if I place partitions on same disk?

It can help query performance by employing partition elimination. This means large sections of big tables can be ignored when looking for values which means much less IO. Index alignment needs to be looked into when partitioning. See details here

You can break your backups by partition. This can be useful if you are struggling to complete your backups in time. See here for details

Index rebuilds can be done at the partition level instead of the whole table.

Large inserts can be done with partition switching.

Queries can also run parallel across partitions.


What's the best way to track Updated By, Updated On In MS SQL Server, what's the best way to update an Updated By, Updated On set of fields on a table?  I've seen it done it triggers, in the code. etc.  The upside I've seen to triggers is that it all happens in the same place.  On the downside there are occasions when an administrator has to bulk fix a table and doesn't want to obliterate the username/time of the last user update.

Note:  I don't want a timestamp.  I want the windows user ID and a human readable date/time of the last change.

Looking for what you recommend and WHY it's a good choice.

1) Your trigger idea is good: we use it sometimes.

It is foolproof mostly except:

issues you mentioned
you don't always have SUSER_SNAME if folk connect via a web server or middle tier

You can mitigate this with SET CONTEXT_INFO to control admin behaviour, or more carefully to pass in some end user details.

2) All writes via stored procedures. We use this method mostly.

We also use defaults on UpdatedOn so...

UPDATE
   ...
   UpdatedBy = whoever, UpdatedOn = DEFAULT

3) Let your client do it. I won't take this any further...

Finally, we implemented "AffectedBy", and "AffectedOn" fields to track admin/indirect updates.


Database structure for multiple users sharing one dataset, but also many users with their own dataset I'm building a web app with php/mysql, and I'm trying to figure out how to set up multiple users sharing data.  Here's the best analogy I can come up with on what I'm trying to do:

You have a calendar app, and a user wants to allow his/her spouse to access the events on the calendar and update them as they see fit.  There would be no difference in roles and both users would log in with their own credentials.  You also have other users who may or may not have a significant other to share a calendar with.

Right now I have a users table with a user_id_PK field that contains the username & hashed password of the user.  Its functioning fine in and of itself, but I want to add the functionality detailed above to allow multiple users to access the same data.  So one user might send out some kind of automated email link to have the other person register for an account, and something about that link would tie the 2 accounts together.  Any ideas???

One option to do it is having a many-to-many table, which will hold a calendar id and a user id who can see it. A calendar belonging to two spouses will have two rows in that table.


reuse of temp table getting more time to execute i'm creating temp table as,

SELECT
    T.CD,
    T.OBJECT_CD,
    T.PRICE
    INTO #TEMP FROM TABLE_NAME T

EDIT : ADDED CLUSTERED INDEX TO TEMP TABLE

CREATE CLUSTERED INDEX IX_Temp_OBJECT_CD
    ON #Temp (OBJECT_CD);

and using this table #TEMP two times in another query to get results as,

SELECT MAX(T1.PRICE),
        MIN(T1.PRICE),
        MAX(T2.PRICE),
        MIN(T2.PRICE)
        FROM PP INNER JOIN #TEMP T1 ON T1.OBJECT_CD = PP.OBJECT_CD
        INNER JOIN #TEMP T2 ON T2.CD = PP.CD AND T2.OBJECT_CD = PP.OBJECT_CD

but the query is taking more time to execute. when i remove any one of T1 or T2 from JOIN the query retrieves result instantly. what could be the reason?

I would add a second column to the clustered index statement, make it be

CREATE CLUSTERED INDEX IX_Temp_OBJECT_CD ON #Temp (OBJECT_CD, CD)

That may help, depending on how many CD's there are to OBJECT_CD's.


MySQL Error wth Relay logs - Could not open relay logs file for logging We are receiving the following error.

2016-03-08 15:32:09 1184 [ERROR] MYSQL_BIN_LOG::move_crash_safe_index_file_to_index_file failed to move crash_safe_index_file to index file.

2016-03-08 15:32:09 1184 [ERROR] MYSQL_BIN_LOG::add_log_to_index failed to move crash safe index file to index file.

2016-03-08 15:32:09 1184 [ERROR] Could not use (null) for logging (error 9). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

2016-03-08 15:32:09 1184 [ERROR] Could not open E:\Program Files\MySQL\MySQL Server 5.6\data\bin_relay_logs\relay\mysql-relay-log.001641 for logging (error 9). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

2016-03-08 15:32:09 1184 [ERROR] Slave I/O: Relay log write failure: could not queue event from master, Error_code: 1595

2016-03-08 15:32:09 1184 [ERROR] Slave I/O: Relay log write failure: could not queue event from master, Error_code: 1595

2016-03-08 15:32:09 1184 [ERROR] Error reading relay log event: error switching to the next log

2016-03-08 15:32:09 1184 [Note] Slave I/O thread exiting, read up to log 'mysql-bin-log.000821', position 4

2016-03-08 15:32:09 1184 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594

2016-03-08 15:32:09 1184 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin-log.000820' position 104878880

I searched lots of posts but unable to get a solutions. Does anyone have a solution of it ?

Main thing is that, why it is happening ?

MySQL version: 5.6.24 in windows Platform

Windows server are virtual servers

This happened when system was running in Master - Master replication and Fabric enabled for DR.

Raised the issue to Oracle Support.

The issue is a bug where Oracle development team is still working on it.

Issue: MySQL Enterprise Backup tries to backup all the binlogs and during that time, internal mechanism cant take backup of binlogs and crashes the DB. Oracle development is still working on the issue.

Workaround:
Avoid taking backup of binlogs and relaylogs in both incremental and full backup.


ROLLBACK doesn't work after INSERT INTO newly created destination table I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers).

Before inserting contents of CSV-file into the mysql table I am first backing up the original customers table.

I am wrapping whole import process (including backing up) in a mysql transaction (to account for cases when CSV is corrupt somewhere in the middle, and to ensure import is atomic).

The problem is that ROLLBACK doesn't seem to work when I am calling it right after INSERT INTO statement: when checking database via phpMyAdmin I can see the newly created table AND ROWS INSIDE IT still present after roollback.

Here's the log of the operations:

[2015-01-19 14:08:11] DEBUG: "START TRANSACTION" [] []
[2015-01-19 14:08:11] DEBUG: SHOW TABLES LIKE :table_name; [] []
[2015-01-19 14:08:28] DEBUG: CREATE TABLE `customers__20150119_14_08_20` LIKE `customers` [] []
[2015-01-19 14:08:37] DEBUG: INSERT INTO `customers__20150119_14_08_20` SELECT * FROM `customers` [] []
[2015-01-19 14:08:50] DEBUG: "ROLLBACK" [] []

So I wonder why depsite ROLLBACK is called, the transaction is not cancelled. I do understand that CREATE TABLE is not transactional in nature and can't be rolled back. But I was assuming that INSERT INTO because it deals with inserting rows (not defining schema), WILL actually be transactional, and after ROLLBACK I will be left with empty destination table. Why is it not the case?

And here's output SHOW CREATE TABLE customers (so my table is InnoDb):

CREATE TABLE `customers` (
 `Code` varchar(32) NOT NULL,
 `Name` varchar(128) DEFAULT NULL,
 `Price` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`Code`),
 KEY `Price` (`Price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and here's output for the desination table:

CREATE TABLE `customers__20150119_14_08_20` (
 `Code` varchar(32) NOT NULL,
 `Name` varchar(128) DEFAULT NULL,
 `Price` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`Code`),
 KEY `Price` (`Price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The reason is that that some statements, like CREATE TABLE cause an implicit commit. You can read about them in the documentation: Statements That Cause an Implicit Commit.

So the original sequence of statements:

START TRANSACTION
SHOW TABLES LIKE customers
CREATE TABLE `customers__20150119_14_08_20` LIKE `customers`
INSERT INTO `customers__20150119_14_08_20` SELECT * FROM `customers`
ROLLBACK

will expand into:

START TRANSACTION ;   -- transaction context created
SHOW TABLES LIKE customers ;

COMMIT ;              -- CREATE TABLE forces commit before itself
                      --     (at this point the previous transaction is done.)
START TRANSACTION ;   -- and a new transaction
CREATE TABLE `customers__20150119_14_08_20`
    LIKE `customers` ;
COMMIT ;              -- CREATE TABLE forces commit after itself.
                      -- At this point there's no transaction context

START TRANSACTION ;   --  starts a new transaction
INSERT INTO `customers__20150119_14_08_20`
    SELECT * FROM `customers` ;
COMMIT ;              -- caused by "autocommit on" setting (guess).

ROLLBACK ;            -- this rollback HAS NOTHING to undo

The solution would be to start the transaction (or a new one) after the CREATE TABLE statement or use a temporary table.


MongoDB document size for collection - impact on RAM and query performance I have been advised on stackoverflow to ask this question on dba.stackexchange instead -

We are using MongoDB version 3.0 with WiredTiger storage.

As newbies to MongoDB, we may have designed our schema naively based on limited knowledge from various books and articles and want to improve the design for better performance.

One or 2 collections have an average object size of 52.3 KB and these collections may likely have millions of records eventually which we may shard. What I want to know is - what would be the impact on RAM if we query on the collection. Please note that the sizes of the documents will not grow much in time.

Eg - 1 document (with avg object size of 52KB) has 91 fields/attributes which include arrays and sub-documents. Say I am interested in around 5 fields in a particular query and I specify these fields in the projection argument- I have verified that appropriate indexes are being used on my query. Will mongoDB load only those 5-6 fields into RAM - the ones I am interested in - or the entire document with 91 fields and size 52KB. My question is for both -

Normal queries
Aggregation based queries

This will help in estimating my working set size.
And another thing, there are lots of other kinds of queries requiring different set of attributes on the same collection and documents - so covered query indexes may not be feasible for all of them.

Should I explore the possibility of splitting to different collections depending on usage patterns even though they may all be really 1:1 relationships? On the flip-side this will not guarantee atomic writes if lots of attributes are getting updated together.

The reason I ask is that I recently observed that geoNear aggregation queries are definitely faster if I trim down the collection to only a few essential attributes. I have a hunch that probably MongoDB may bring entire documents into RAM since it memory maps data files.

In the future, ask to have the question migrated here instead of double posting.

From the MongoDB Documentation,

  To calculate how much RAM you need, you must calculate your working
  set size, or the portion of your data that clients use most often.
  This depends on your access patterns, what indexes you have, and the
  size of your documents. Because MongoDB uses a thread per connection
  model, each database connection also will need up to 1MB of RAM,
  whether active or idle.

If the query is not completely covered by an index then the entire document is loaded into RAM by MongoDB. This is the same regardless of the type of query.

As for whether or not you should split your collection, that's really a design decision that requires a lot more background to understand and weigh in on. You'll need to perform tests to see if the gains from it based on the query patterns are worth it compared to the loss of atomicity (a business driven decision potentially).

I probably wouldn't recommend this approach though and would assess what your true SLA's are and whether or not you're using the right amount of hardware (RAM) for the desired results. Your real question here is "Do I have enough RAM to satisfy my query performance requirements when it comes to cached data?" If the answer is no, figure out what design works best with your constraints.


Is it good to avoid Linked Server by utilizing JOIN task under SSIS package? We are trying to setup a centralized SSIS server (SQL Server 2014). I want to know the following:

Any procedure which is utilizing Linked Server and if that procedure gets called from SSIS SQL Task, is it better to put that logic inside SSIS itself without utilizing Linked Server?
If we ask end user to put logic inside the SSIS package (i.e. JOIN...) and not use Linked Server, is it better performance wise?

Example: I have Table A from Server_1 and Table B from Server_2. If I am creating a stored procedure on Server_1, normally I will be joining Table A to Table B using Linked Server to Server_2. If I put this same logic inside the SSIS package and use [JOIN] task instead, would that be better?

You'll have to test to find which works best for your scenario.

By "join task" I assume you mean a Merge Join transformation which requires the input to be sorted. Sorting can be expensive especially if using a Sort transformation. Even sorting at source will take time.

You could try a Lookup transformation which works best using full cache, but if the reference data is large the cache will obviously require a lot of memory.

Another option is to use SSIS to localise the table and then do the join as you would do normally.

Rarely would I use linked servers for anything other than ad-hoc queries.


Giving access to only one SQL Agent Job I need to allow a user only to execute a specific Agent job, rather than be able to execute the other jobs that are there . Please advise how to accomplish this.

This user will only run a copy only backup before he updates the application and I don't want to give him access to the other jobs. Ola Hallengren scripts are used for that job.

I added the user to the DB_BackupOperator role. If I simply make him the owner of that job will he be able to run the other jobs that are owned by SA?

It'd be easier to just grant execute on Ola's proc to that user. Granted, here they'd be able to call the proc with whatever parameters they want. See Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it if you don't want to go that route. Depending on the route you take, you may duplicate the job and make them the owner of their own job.

While DB_BACKUPOPERATOR would be needed, it isn't enough here. If you read that answer it explains a way, but again it'd be easier to just let them execute the procedure or use a batch script and give them permission to run the batch script. Or use sqlcmd which Ola's scripts support - scsimon

If you make him the owner of that one job only and add him as a member of SQL Server Agent Fixed Database Roles SQLAgentUserRole should be able to execute the job. - sqlworldwide


Distributed database implementation in sql server Currently, We have central Database at Head Office and users from sub offices ,scattered around the country, connect to central database. Now we want to distribute the database in disconnected mode at each Sub office level and we want data to be synchronized at the day end at the central Database from all others Databases. We also want transfer of data to all Databases from central Database. Please refer me the tutorial or way to implement this scenario in SQL Server. Thanks in Advance.

This is pretty much a textbook example for Merge Replication.  Here is the Product documentation describing the scenario, and how to implement it.


mysql database migration of schemas with updated data and structures I am a newbie with MySQL database dumps and am currently trying to work with data migration between a source schema and a target schema.

Assuming the source schema is defined by (source_table_structure, source_data) and the target schema is defined by (target_table_structure, target_schema), the resultant schema that I am looking at is (target_table_structure, source_data_WithNewSize) where the source_data_WithNewSize is basically the same source schema data with additional columns values filled by NULL.

Is it possible to use MySQL workbench to perform this type of migration? Are there other tools that are optimized for such migration?

There could be following two ways of doing the same.

First is you take unloads from source using following command

  select * from table_name into outfile 'file_path' FIELDS TERMINATED BY ',' enclosed by '"' ;

Then on target you can do following

  load data local infile 'file_path' into table table_name(Column_list_comma_seprated) FIELDS TERMINATED BY ',' enclosed by '"' escaped by '' ;

Or Else you can try following of inserting data

  Replace into target_table(Column_list) select (Column_list) from source table;

Hope it helps.


IF else condition on DATE to determine String in other column I have 2 columns : a DATE column and a STRING column. String column contains 'NEW' and 'OLD' distinct data.

How do I right a conditional query:

if DATE is three days old from today
 Then NEW should be 'NO CHANGE'
else
 OLD should be 'CHANGE'

meaning if it is not older than 3 days then OLD.

NEW = 'CHANGE', OLD = 'NO CHANGE' like you will use in a CASE statement but how can I account for the date condition to drive the choice?

i didn't understood what you are meaning with "NEW = 'CHANGE', OLD = 'NO CHANGE' like you will ..." so i wrote 2 cases

Assuming that DataColumn and StrnigColumn are the columns names and myTable is the table name

you have to use a CASE with the DATEADD function

UPDATE myTable
SET StringColumn = CASE WHEN DATEADD(DAY,GETDATE(), -3) > DateColumn
                        THEN 'NO CHANGE'
                        ELSE 'CHANGE'
                   END

if you mean by "NEW = 'CHANGE', OLD = 'NO CHANGE' like you will ..." that you have also to check the value so your query will look like

UPDATE myTable
SET StringColumn = CASE WHEN DATEADD(DAY,GETDATE(), -3) > DateColumn AND StringColumn ='OLD'
                                            THEN 'NO CHANGE'
                        WHEN DATEADD(DAY,GETDATE(), -3) <= DateColumn AND StringColumn ='NEW'
                                            THEN 'CHANGE'
                        ELSE [StringColumn]
                   END


Drop article from Replication I have multiple tables with the same name but different schemas.  I want to script out the drop of one of these tables from replication.  I can't find where or how I can declare the schema name.  It only defaults to dbo if I run below

EXEC sys.sp_dropsubscription
@publication = 'SGSysTestLVSREP',
@article = 'DropArticleTableName',
@subscriber = 'Server11',
@destination_db = 'SysTest'

EXEC sys.sp_droparticle @publication = 'SGSysTestLVSREP',
@article = 'DropArticleTableName',
@force_invalidate_snapshot = 0

Microsoft has confirmed that the dbo table is the only table that will be removed.  They recommend writing my own SP


Postgres resource management I'm consolidating multiple Linux boxes running Postgres into one box.

Given the way Postgres handles resource utilization, would one single server service handling all the databases provide a much better performance than multiple server services (each one listening to different IP:port) running on the same host?

Thanks

In general, a single instance is more efficient, but has inferior performance isolation between databases.

If you want maximum throughput and best resource utilisation, put them all in a single instance. One busy DB will be able to affect other DBs more easily this way, especially when it comes to things like high rates of small write transactions. Features like async commit, commit delay, etc, help. If you group them all on one instance you'll also have to do any physical backups and streaming replication / PITR for the whole set of DBs, you can't isolate just one DB.

If you want to reduce the impact one busy/overloaded DB has on another DB, isolate them into their own instances and put each on a separate file system, perferably on separate underlying storage. You'll pay a price in wasted shared_buffers space, extra fsync()s, etc, and you'll find things like user management a major pain, but you'll be able to do streaming replication / physical backups of just one DB and you'll be more able to manage their relative resource allocations. If they're separate instances you can use things like nice, ionice, etc more effectively and can manage storage quotas somewhat better.


Cassandra nodetool repair open files issue I am using Cassandra 3.6. After nodetool repair Cassandra start takes too much time. The message is:
ViewManager.java:226 - Not submitting build tasks for views in keyspace system_schema as storage service is not initialized

The system stuck hours on this message. Any suggestions, please. The number of the open files raised significantly. From 100k to 1.5 millions.

Since SSTables can contain tokens from multiple token ranges, and repair is performed by token range, it was necessary to be able to separate repaired data from unrepaired data. That process is called anticompaction. Level Compaction Strategy (LCS) a very intensive strategy where SSTables get compacted way more often than with STCS and TWCS. LCS creates fixed-sized SSTables, which can easily lead having thousands of SSTables for a single table. The way streaming occurs in Apache Cassandra during repair makes that overstreaming of LCS tables could create tens of thousands of small SSTables in L0 which can ultimately bring nodes down and affect the whole cluster. This is particularly true when the nodes use a large number of the vnodes. I have seen happening on several customer clusters, and it requires then a lot of operational expertise to bring back the cluster to a sane state. A safety measure has been set in place to prevent SSTables from going through anticompaction to be compacted, for valid reasons. The problem is that it will also prevent that SSTable from going through validation compaction which will lead repair sessions to fail if an SSTable is being anticompacted. Given that anticompaction also occurs with full repairs, this creates the following limitation: you cannot run a repair on more than one node at a time without risking to have failed sessions due to concurrency on SSTables.
The only way to perform repair without anticompaction in “modern” versions of Apache Cassandra is subrange repair, which fully skips anticompaction. To perform a subrange repair correctly, you have three options :

Compute valid token subranges yourself and script repairs accordingly
Use the Cassandra range_repair.py script which performs subrange repair
Use Cassandra Reaper, which also performs subrange repair. Google it to find as repo's might change

To decrease the number of the open files and minimize the restart time use: nodetool cleanup; nodetool compact


What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? I have an indexed column that stores an MD5 hash.  Thus, the column will always store a 32-character value.  For whatever reason, this was created as a varchar rather than a char.  Is it worth the trouble of migrating the database to convert it to a char?  This is in MySQL 5.0 with InnoDB.

A similar question was asked before

Performance implications of MySQL VARCHAR sizes

Here is the excerpt of my answer

You must realize the tradeoffs of using CHAR vs VARCHAR

With CHAR fields, what you allocate is exactly what you get. For example, CHAR(15) allocates and stores 15 bytes, no matter how characters you place in the field. String manipulation is simple and straightforward since the size of the data field is totally predictable.

With VARCHAR fields, you get a completely different story. For example VARCHAR(15) actually allocates dynamically up to 16 bytes, up to 15 for data and, at least, 1 additional byte to store the the length of the data. If you have the string 'hello' to store that will take 6 bytes, not 5. String manipulation must always perform some form of length checking in all cases.

The tradeoff is more evident when you do two things:
 1. Storing millions or billions of rows
 2. Indexing columns that are either CHAR or VARCHAR

TRADEOFF #1
 Obviously, VARCHAR holds the advantage since variable-length data would produce smaller rows and, thus, smaller physical files.

TRADEOFF #2
 Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields. This is not any conjecture on my part. The book MySQL Database Design and Tuning performed something marvelous on a MyISAM table to prove this. The example in the book did something like the following:

ALTER TABLE tblname ROW_FORMAT=FIXED;

This directive forces all VARCHARs to behave as CHARs. I did this at my previous job back in 2007 and took a 300GB table and sped up index lookups by 20%, without changing anything else. It worked as published. However, it did produce a table almost double in size, but that simply goes back to tradeoff #1.

You could analyze the data being stored to see what MySQL recommends for column definition. Just run the following against any table:

SELECT * FROM tblname PROCEDURE ANALYSE();

This will traverse the entire table and recommend column definitions for every column based on the data it contains, the minimum field values, maximum field values, and so forth. Sometimes, you just have to use common sense with planning CHAR vs VARCHAR. Here is a good example:

If you are storing IP addresses, the mask for such a column is at most 15 characters (xxx.xxx.xxx.xxx). I would jump right at CHAR(15) in a heartbeat because the lengths of IP addresses will not vary all that much and the added complexity of string manipulation controlled by an additional byte. You could still do a PROCEDURE ANALYSE() against such a column. It may even recommend VARCHAR. My money would still be on CHAR over VARCHAR in this instance.

CHAR vs VARCHAR issues can be resolved only through proper planning. With great power comes great responsibility (cliche but true).

UPDATE

When it comes to MD5, the computation of strlen internally should be eliminated when switching  the entire row format. There would be no need to change the field definition.

If the MD5 key is the only VARCHAR present, I would go for it and convert the table row format to fixed. If there is a signifcant number of other VARCHAR fields present, they would benefit as well. In exchange, the table would expand to about twice its size. But queries should accelerate about 20% more without additional tuning.


How to configure SSRS (SQL Server 2012) to run on a different instance, but same server as SharePoint I have an instance of SQL Server 2012 running on Windows 2008R2 along side another instance of SQL Server 2012 that is running SharePoint (I think it is SharePoint 2010).

When I try to access the http://localhost/Reports_<InstanceName> I am getting:

A certificate warning (which I proceed through - I don't really care about this - its a dev machine)
The following text: Cannot connect to the configuration database.

Google says that that error message is SharePoint related so I am inclined to think that SharePoint is highjacking the SSRS request.

Is there a configuration I can make to SSRS or something else (IIS maybe??) that will let me access the Report Server?

Note: I can connect to Reporting Services through SSMS just fine.

Are you sure of your version of Sharepoint? I thought SP2010 could not run on SQLS2012 but 2008 and 2008R2.

With which machine do you try to access the report server ?

If you have SP and SSRS native mode installed on the same machine you will get error due to the address (SP and Report Server use the same). You can change the report server address in the Reporting services configuration manager (Start, Microsft SQL server 2012, Configuration Tools) and Report Manager URL tab.


Running a command in psql `-c` that requires identifier quoting I have created a database schema as "mytest.us" and inside this schema, I have created a table "a.b.c".

When I do

select * from "mytest.us"."a.b.c";
 year | mydata
------+--------
 2011 | test1
 2010 | test1
(2 rows)

but when I do

\\! $TARGET/sql -p $PGPORT -d $PGDBNAME -c "select * from "mytest.us"."a.b.c"";

sh: -c: line 0: unexpected EOF while looking for matching `"'

sh: -c: line 1: syntax error: unexpected end of file

How can I specify my table in that situation?

It is very poor practice to misuse reserved words. Frankly, messing with multiple periods ., the table/column separator operator, you deserve any hardships coming to your way. There is no need for funky table names, barring some odd edge cases. The DB should be transparent to applications anyway, so stick to sensible naming.

Escape the inner quotes. As how to do that, depends on your shell. The error message looks like *NIX shell, so try backslash \". Like so:

"select * from \"mytest.us\".\"a.b.c\"";


How to install SSIS on SQL Server 2014? I am installing SQL Server 2014, including SSIS, on a Windows Server 2012 R2 machine.

From this link:
https://msdn.microsoft.com/en-us/library/ms137861(v=sql.120).aspx
ssis uses port 135 which should be opened on the firewall(s).

I Create a domain account to run SSIS

Install SQL - features can be seen on the picture below

All services are running fine
see a special ad account to run the ssis services

Grant the logon as a service and lock pages in memory

On control panel -> administrative tools-> Computer management

Add the users and the ssis account to “distributed com users”

On control panel -> Administrative Tools > Component Services

Then go to computer-> my computer -> DCOM Config -> and look for Microsoft
SQL Server Integration Services 12.0

As you can see on the picture below

You right click on Microsoft SQL Server Integration Services 12.0

I have left the Authentication Level as “call”.
This is more detailed explained if you follow this link.

These 3 above have to be set up, I have done, and the results are below:

Doing all this above,
When I run SSMS “As Administrator” on the Server itself, then it works fine.

AFTER I REBOOTED THE SERVER I CAN CONNECT SO THE MESSAGE BELOW IS FIXED

  But on the Server itself if I run SSMS as myself, I get the following
  error message:

  Connecting to the Integration Services service on the computer
  "sqldw01" failed with the following error: "Access is denied."

  By default, only administrators have access to the Integration
  Services service. On Windows Vista and later, the process must be
  running with administrative privileges in order to connect to the
  Integration Services service. See the help topic for information on
  how to configure access to the service.

I am administrator on the box.

and when I try to connect from my local machine, then I get the following error:

I CANNOT CONNECT USING SQL SERVER 2012 OR LOWER, BUT I CAN CONNECT FINE USING SQL SERVER 2014

Connecting to the Integration Services service on the computer "sqldw01" failed with the following error:

  "Class not registered". This error can occur when you try to connect
  to a SQL Server 2005 Integration Services service from the current
  version of the SQL Server tools. Instead, add folders to the service
  configuration file to let the local Integration Services service
  manage packages on the SQL Server 2005 instance.

What am I missing?

The problem that remains solving is
How to connect to SSIS 2014 from a client using SQL Server 2012?

The SSIS intallation is working as a charm, the only problem was that we wanted to connect to SSIS 2014 from SSMS 2012.

At the end we agreed to upgrade the developers machines to sql server 2014 developer edition, and migrated the packages to sql 2014, and now everything is working fine.

I will leave the question here as a reference, for the community and for myself, because at the end I had so many problems, and they all have been solved.


Alter disabled trigger in SQL Server without enabling it It seems like triggers get enabled automatically after altering them. How can I alter triggers programmatically without changing their enabled/disabled status?

Code to reproduce:

create trigger trig
on MyTable
after insert
as select 0

go

disable trigger trig on MyTable

go

select is_disabled from sys.triggers where name = 'trig'

go

alter trigger trig
on MyTable
after insert
as select 1

go

select is_disabled from sys.triggers where name = 'trig'

You can't. You just need to disable it again. There is no ALTER TRIGGER syntax that prevents this.

The disable trigger topic explicitly calls out that

  Changing the trigger by using the ALTER TRIGGER statement enables the
  trigger.


How to figure out Disk I/O by table Is it possible to figure out which tables contribute the most to read / writes operations if all of them in same file?

I been looking at the following DMV

SELECT *
FROM sys.dm_io_virtual_file_stats

but it only shows statistics for the whole file and not sure how to split into individual tables. Trying to do this in order to identify tables that should be places in their own files on different disk.

The following two queries give index usage stats per table which should be a good indicator of which tables are causing the most IO operations.

SELECT d.name as [Database],  OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
Join sys.Databases d on s.database_id = d.database_id
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Order by USER_SEEKS + USER_SCANS + USER_LOOKUPS + USER_UPDATES desc

SELECT d.name, t.name, OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
 I.[NAME] AS [INDEX NAME],
  A.LEAF_INSERT_COUNT,
  A.LEAF_UPDATE_COUNT,
  A.LEAF_DELETE_COUNT

FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I          ON I.[OBJECT_ID] = A.[OBJECT_ID]
join sys.tables t on i.object_id = t.object_id
join sys.databases d on a.database_id = d.database_id
AND I.INDEX_ID = A.INDEX_ID WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

order by A.LEAF_INSERT_COUNT + A.LEAF_UPDATE_COUNT + A.LEAF_DELETE_COUNT desc


mysqldump is not dumping data by default mysqldump is dumping only the table structures, without data, by default.

mysqldump -u user -d database -p

Which is the option to dump is with data?
How can I configure it, to include the data? (I guess it's possible with a setting in /etc/mysql/my.cnf)

Use only

mysqldump -u user -p database

The -d option means no-data :

   --no-data, -d

       Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE
       statement for the table (for example, to create an empty copy of the table by loading the dump file).


Linux SQL Standard Edition being killed by OOM killer We have launched Linux with SQL Server standard edition for production environment, but the memory restriction feature in SQL Server is not working properly.

The server has a total of 32 GB of RAM.  We initially allocated 25 GB to SQL Server in mssql.conf and experienced crashes due to the OOM killer.

We have since reduced this to 18 GB memory, but it's still utilizing all the server memory, and the service is eventually killed.

What else can we do to prevent the SQL Server service from being killed by the OOM Killer?

Are there are other applications / workloads running on the server when SQL Server gets killed?

If Linux detects memory pressure, SQL Server will be the easy target - even if it's staying with the 18 GB limit you set.  Imagine SQL Server using 18GB, and 3 other applications using 6 GB each.  The Linux kernel will target the SQL Server process to free up RAM, because it is using the most of any single process.

You should monitor memory usage by all apps on the box leading up to the crash.  If other apps are using a lot of RAM, you should consider either:

removing them, or
further reducing the memory available to SQL Server

If SQL Server is really exceeding its limits, your best bet is likely to contact Microsoft support.

For a real life example, see this article from SQL Server support for recommendations on avoiding the OOM Killer:

How to safeguard SQL Server on Linux from OOM-Killer


Create window (PARTITION BY?) out of column values I have a table structured as:

| date       | key | value |
|------------|-----|-------|
| 2019-01-02 |S1   |20     |
| 2019-02-04 |S1   |30     |
| 2019-03-10 |S2   |15     |
| 2019-04-07 |S1   |0      |
| 2019-04-13 |S2   |35     |
| 2019-04-19 |S1   |10     |
| 2019-05-01 |S1   |30     |
| 2019-05-15 |S1   |40     |
| 2019-06-21 |S1   |0      |

I want to retrieve the date of the first record associated with each key and date, when the given key is 0. If there are multiple 0 associated with each key, I want to partition by 0 and compute that for each partition.

Using the table above as an example, below is the result for S1:

The first partition is as follows:

| 2019-01-02 |S1   |20     | <- Output this
| 2019-02-04 |S1   |30     |
| 2019-04-07 |S1   |0      | <- And this

and it's output should be

| date       | date_of_zero |
|------------|--------------|
| 2019-01-02 | 2019-04-07   |

The second partition will be

| 2019-04-19 |S1   |10     | <- Output this
| 2019-05-01 |S1   |30     |
| 2019-05-15 |S1   |40     |
| 2019-06-21 |S1   |0      | <- And this

and it's output will be

| date       | date_of_zero |
|------------|--------------|
| 2019-04-19 | 2019-06-21   |

The overall result expected:

| key | date       | date_of_zero |
|-----|------------|--------------|
| s1  | 2019-01-02 | 2019-04-07   |
| s1  | 2019-04-19 | 2019-06-21   |

I have tried to come up with solutions using PARTITION BY and LATERAL JOIN but I do not even know how to get started, and partition by value = 0 as WHERE clause do not work with partition expressions.

I am wondering if this is something solvable in SQL (within reasonable query complexity) or one is better off fetching the rows and doing windowing at the application layer?

WITH cte AS (
SELECT *, COUNT(CASE WHEN v=0 THEN 0 END) OVER (PARTITION BY k ORDER BY d DESC) r
FROM test
)
SELECT k AS key, MIN(d) AS "date", MAX(d) AS date_of_zero
FROM cte
WHERE r > 0
GROUP BY k, r
ORDER BY 1, 2

fiddle

If there are 2 adjacent "zeros" then 2nd record (by date) forms a separate group. The same for a "zero" with the most first date.

If there can be "zero" and "non-zero" with the same date you must decide what is their priority.

The final records are not listed if the most last record is "not zero".


Yet another "ERROR 1005 can't create table" question Well, I've been looking at the ERROR 1005 questions, but I haven't been able to see my error among the issues addressed by those questions. This code is generated by MySQL Workbench under Linux. Can anyone with sharp eyes spot the error? It occurs when trying to create AB_test_questions.

For context, table ABs refers to subjects of an A/B test, and AB_test_questions represents questions which compare two subjects against each other (hence two foreign keys referencing the ABs table).

CREATE TABLE IF NOT EXISTS `db`.`ABs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `text` VARCHAR(255) NOT NULL,
  `image` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'represents the subject of an AB test';

CREATE TABLE IF NOT EXISTS `db`.`AB_test_questions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `AB1` INT NOT NULL,
  `AB2` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `AB1_idx` (`AB1` ASC),
  INDEX `AB2_idx` (`AB2` ASC),
  CONSTRAINT `fk_AB_test_questions_ABs_id1`
    FOREIGN KEY (`AB1`)
    REFERENCES `db`.`ABs` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_AB_test_questions_ABs_id2`
    FOREIGN KEY (`AB2`)
    REFERENCES `db`.`ABs` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'represents an AB test question';

You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values.

Ref:- http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/


Issue with 2016 developer edition install Issue with 2016 developer edition install on new windows 10 machine.  Not finding any docs or similar issues posted.

  An error occurred for a dependency of the feature causing the setup process for the feature to fail.

  Component name:                SQL Server 2008 Setup Support Files

  Component error code:          1935

  Error description:             An error occurred during the installation of assembly 'Microsoft.VC80.ATL,version="8.0.50727.1833",publicKeyToken="1fc8b3b9a1e18e3b",processorArchitecture="x86",type="win32"'. Please refer to Help and Support for more information. HRESULT: 0x800736B3.

Given this image, I can imagine you are selecting to install every single feature during setup:

I would start with just the set of features you need, since selecting everything - especially if you don't actually need everything - creates a whole bunch of dependencies for little gain.

You can always go back and add features as you realize you need them.


Implicit conversion: when does it hurt performance a lot? I know that when data types are the same it is good and it hurts performance when they are not.
But sometimes it seems to have very little impact and basically a waste of time to fix it. Other times it will have a huge impact and performance gets a lot better.
Of course there are many scenarios, e.g. a insert select where the data type of the select is not the same, but compatible, with the insert column. Or it could be on the join predicates.
Where does implicit conversion hurt the most?
Also it seems that one also gets an implicit conversion when the lengths don't match, e.g. joining varchar(10) with varchar(20). How big a deal is these scenarios when we are only dealing with the length of the columns and not their type?
Basically I would like to know when I should worry about the compute scalar operator in the execution plan. How can I know if removing it will have a significant impact?

As Justin implies, the when it's a problem will vary from query to query and execution plan to execution plan. There's no definitive list of scenarios that guarantee you'll experience performance issues as a result of implicit conversion.
One way to tell if a query facing implicit conversion is experiencing performance issues is to check for cardinality estimate issues, generally the two go hand-in-hand when the implicit conversion is causing performance issues. You can do so by checking the Actual Number of Rows vs the Estimated Number of Rows for each operation of the query's execution plan, and if there's a large deviation between the two (generally an order of magnitude or more), then there's cardinality estimate issues. Generally you can look for the operations involved with the thickest lines to quickly spot which operations you can start checking cardinality estimate issues for. As Justin also mentioned, generally this is when the implicit conversion is happening on one of the indexed fields, specifically in a predicate (JOIN, WHERE, or HAVING clause) of the query.
But not all implicit conversion warnings are worth a grain of salt, and if you see such a warning but your query is running optimally, then you likely no need to spend time debugging it, especially if the execution plan doesn't also have cardinality estimate issues.