Does concating a string like this open me up to SQL injection? I recently answered a question with the following code sample:

Create Table #Testing
(
  emaildomain varchar(100) -- Still bigger than functionally needed but better than MAX
);
INSERT INTO #Testing VALUES (REVERSE('@myfreepaysite.com')); -- Changed

Create Table #DataToCheck
(
  fullemail varchar(200) -- Still bigger than functionally needed but better than MAX
);
Insert Into #DataToCheck VALUES(REVERSE('rr1234@myfreepaysite.com')); --Changed

Select Top 1
  REVERSE(fullemail)
  , REVERSE(emaildomain)
FROM #DataToCheck
  INNER JOIN #Testing ON fullemail LIKE emaildomain + '%';

As I was writing that last line I wondered if concating EmailDomain column with the wildcard symbol was an opening for SQL injection. Is this something I should watch out for this type of code or will SQL Server evaluate this as a single unified parameter so I don't need to worry about using this approach?

No. That value will always be interpreted as a string and there's no way to put anything into the emaildomain column that could make the query execute unexpected code. It's either going to find matches or it's not.

Of course, this is relevant to the specific code sample you posted, and only that. You should always be concerned about any data you accept from a user... since you might not know where else it might be used now or in the future.


Creating a mongoDB backup/dump on remote server via local CMD/Shell I hope you can help me.

It is a question that was asked before, but I have tried all solutions, and simply cannot get it right.

I have spend quite some time researching before posting this question. I have looked at the official MongoDB documents and many other blogs.

How can I make a DB backup/dump remotely, as well as restore that dump locally?

This is what I have thus far:

Running this from a new CMD prompt shell:

C:\Users\Admin>mongodump --host 10.13.9.210 -d C:\Program Files\MongoDB\Server\3.0\bin --port 29039 --out /backup/mongodump-2015-11-13

I get this error:

2015-11-13T10:26:41.371+0200    error parsing command line options: expected argument for flag `/o, /out', but got option `/backup/mongodump-2015-11-13'
2015-11-13T10:26:41.371+0200    try 'mongodump --help' for more information

Secondly, how will I be able to restore this backup locally?

On Linux and Mac, arguments for command-line tools normally have one of these two formats:
--option1 value1 or  -o value1.

On Windows, the standard is different:
/option1 value1 or /o value1.

mongo, mongodump and other command-line MongoDB programs actually preserve the Linux way of passing arguments. But the Windows cmd.exe shell will still recognize the character / as a beginning of a command-line option.

Also, in UNIX systems, / is used to separated parts of a file system path, e.g. /data/db. On Windows, \ is used instead: C:\Data\DB.

Your problem is this part of the command: --out /backup/mongodump-2015-11-13. You kept the backup path in the UNIX format, and / was recognized as a starting character of a command-line option.

You should specify a real file system path on your hard drive instead. For example:

--out C:\backup\mongodump-2015-11-13

Also, your data path (C:\Program Files\MongoDB\Server\3.0\bin) contains a space. Because of the space, cmd.exe will treat C:\Program and Files\MongoDB\Server\3.0\bin as separate parameters. To prevent this, surround the path with quotes:

-d "C:\Program Files\MongoDB\Server\3.0\bin"

And as a final note, do not use the bin folder to store your data files (as you seem to be doing judging by the path you're passing to -d).


Cannot connect to SQL server through SSMS from other machine after IP change There was an ip change on the sql server and after that I cannot connect from other machines  ssms but can connect locally .

The firewall is disabled on the server and moreover its listening on port 1433 and all the network protocols are open named pipes, shared memory and tcpip.

TCP/IP properties IPAII - TCP port set to 1433 and TCP Dynamic Ports are blank

Am i missing something?

Possible Solutions

Did you restart the SQL Server instance after the IP address was changed? If not, then the SQL Server was possibly still listening on the old IP address.
When connecting locally you normally utilise the Shared Memory connection, that is why you were able to connect locally, but not from remote (see 1.). The Shared Memory connection does not require the IP address. You can verify this by running the following query after connecting locally:

select
    sdec.net_transport,
    sdec.session_id,
    sdes.login_name
from sys.dm_exec_connections as sdec
join sys.dm_exec_sessions as sdes
    on sdec.session_id = sdes.session_id

Possible output:

net_transport session_id  login_name
-------------------------------------------------
TCP           51          NT SERVICE\ReportServer
Shared memory 52          Domain\Account
Shared memory 55          NT SERVICE\SQLSERVERAGENT
Shared memory 54          NT SERVICE\SQLSERVERAGENT
Shared memory 53          NT SERVICE\SQLSERVERAGENT
Session       55          NT SERVICE\SQLSERVERAGENT
Session       55          NT SERVICE\SQLSERVERAGENT
Shared memory 56          NT SERVICE\ReportServer
Shared memory 57          Domain\Account

TCP = IP Connection
Shared memory = Local Connection

Your local connection would show up as a Shared memory connection.
Did you flush the DNS cache on your other server/computer before you tried connecting? Sometimes cleaning the cache will resolve IP <> Hostname resolutions issues:

 ipconfig /flushdns

Verifying the network configuration in the SQL Server Configuration Manager can help resolve issues. Possible errors:

IP Address does not exist (See 1.: Reboot Instance)
IP Address is not configured to listen (Set Enabled: Yes and Active: Yes)

After rebooting the SQL Server instance check the SQL Server Errorlog file to verify that it is indeed listening on the TCP stack:

2018-08-30 16:15:47.12 spid15s     Server is listening on [ 'any' <ipv6> 1433].
2018-08-30 16:15:47.13 spid15s     Server is listening on [ 'any' <ipv4> 1433].
2018-08-30 16:15:47.13 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2018-08-30 16:15:47.13 spid10s     The resource database build version is 12.00.5589. This is an informational message only. No user action is required.
2018-08-30 16:15:47.13 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2018-08-30 16:15:47.14 Server      Server is listening on [ ::1 <ipv6> 1434].
2018-08-30 16:15:47.15 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-08-30 16:15:47.15 Server      Dedicated admin connection support was established for listening locally on port 1434.
2018-08-30 16:15:47.15 SQL Server is now ready for client connections. This is an informational message; no user action is required.

This instance was previously not listening on the TCP stack:

2018-08-24 08:36:09.06 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2018-08-24 08:36:09.07 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2018-08-24 08:36:09.08 Server      Server is listening on [ ::1 <ipv6> 1434].
2018-08-24 08:36:09.08 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-08-24 08:36:09.08 Server      Dedicated admin connection support was established for listening locally on port 1434.
2018-08-24 08:36:09.09 spid15s     SQL Server is now ready for client connections. This is an informational message; no user action is required.

It could be a firewall rule that hasn't been adapted to the new IP address of the server. In that case you would have to have your company's network department look at the rules on the firewall and fix the rule for your server or if you only having issues with your Windows Firewall, check the rules there.
(Added from comments)


Create trigger that executes maximum one time per day I have a table big_table with a large number of daily INSERTs, and this table has a column interesting_val. For the first row, and first row only, which is inserted after 00:00 UTC on each date, I want to SELECT interesting_val and INSERT to another table daily_interesting_val. Is this behavior something I can achieve with a combination of procedures and triggers?

Whenever big_table has at least one row created after 00:00 UTC, but daily_interesting_val does not have a corresponding row with the new interesting_val, my application is in an illegal state, and I want to make this time window as small as possible.

One solution I can imagine, but don't if is possible in MySQL:

Create a scheduled procedure that creates a trigger at 00:00 each day. This trigger calls a procedure which (1) checks existence of row, (2) if not exists, insert row, (3) if exists, delete trigger.

Use INSERT IGNORE and have a unique key on the date.  The first insert to run for the day will succeed; the rest will be "ignored".


What is a good way to verify that a restore completed successfully in SQL Server? Right now, we test our SQL backups once a month by pulling the most recent backup file and restoring it onto a test database. If SQL Server says the restore is successful, we spot-check a few tables to make sure they have data in them.

Is it safe to assume that the restore was completely successful if SQL Server says it was? Is there a good way to verify that the restored data/indexes/etc are correct?

It is considered good practice where I work to run DBCC CHECKDB after a restore, especially if the backup is of an unknown quality.

CHECKDB will at least tell you if your DB has any consistency errors, and checks the logical and physical integrity of all the objects in the specified DB.

A similar thread is on ServerFault: https://serverfault.com/questions/187869/should-i-run-dbcc-checkdb-before-full-backups-or-after


database design issue This is my first time on this  beautiful content rich site.  I have a sql server 2008 database, but am not too experienced with good relational database design.

My project is this:
I have a school that has Students.  The students offer subjects which are graded on a termly basis in a session. There is a session which means 1 year and in this session, there are 3 terms namely 1st,2nd and 3rd terms. Each student has a results/continuous assessment that are reviewed on a termly basis in one session (3 terms) in a session.
How do I design m database to encompass all these details, so that I can check results or performance per term, in the 3 terms and in a session or more than one session.

STUDENT TABLE
STUID|STUNAME|STUADDRESS

CONTINOUS ASSESSMENT TABLE
CAID| FIRST_CA(20) |SECOND_CA(20)| EXAM SCORE(60)|TOTAL SCORE|SUBJECTID|RANK|YEARID

SUBJECT TABLE
SUBJECTID |SUBJECT_NAME |SUBJECT DESCRIPTION

YEAR/SESSION/TERM TABLE
ID|YEAR|TERM|STATUS|FROM|TO

CLASS TABLE
CLASSID | CLASS_NAME | CLASS_TEACHER

DEMO QUERY

SELECT RESULT FROM CONTINUOUS ASSESSMENT WHERE STUDENT ID = ID
AND CLASS = CLASS5 AND TERM = 1ST TERM AND YEAR = 2012

OR

SELECT  RESULT FROM CONTINUOUS ASSESSMENT WHERE STUDENT ID = ID
AND CLASS = CLASS5 AND TERM = 1ST TERM, 2ND TERM ,3RD TERM AND YEAR = 2012

How do I link all these tables in order to execute this query successfully?

Get 4 pieces of paper.

On the first piece, create 2 columns across the page, StudentID, StudentName and write down 1,2,3 for the StudentIDs under the StudentID column and make up some cool names like 'Dr Cheat' under the StudentName column.

On the second piece of paper, create 3 columns called StudentID, ClassID, Score and enter 1,2,3 for the StudentID and 1,1,1 for the ClassID then make up some scores - like 100%, 99%, 0%.

On the 3rd piece of paper create 2 columns - ClassID, ClassName and enter the values 1, 'How To Win by Cheating'

Now, to understand relational databases, all you need to do is on the 4th piece of paper enter the columns StudentID, StudentName, ClassID, ClassName, Score and then populate the values from the other 3 pieces of paper.

You can see you have 3 students, and some scores, and some class names for those scores - the exercise of joining them together into that 4th piece of paper is similar to writing a SQL query.

Whenever you find yourself holding 2 pieces of paper together and looking back and forward between the 2 pieces to find matching values - the column you are matching on is your JOIN column.

That's relational database design.


How can I check if connection to Sql Server is encrypted? In SQL Server I can "Force Encryption" in protocols settings. If I leave that set to "No" any client can still require encryption.
Can I somehow see what connections to the database are using encryption?

From TechNet sys.dm_exec_connections DMV: the encrypt_option will display FALSE if the connection is not encrypted.

Here is an example TSQL statement that can be run by administrators (VIEW SERVER STATE is the required privilege):

SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address
FROM sys.dm_exec_connections


Replication priority I have two MongoDB instances running on two servers (instance1 and instance2) and they are configured as a replica set. Instance1 is the primary and instance2 is the secondary.

When instance1 was shut down, instance2 became the primary. However, they both had priority value set to 1. Therefore when instance1 was up again, instance2 kept being the primary node.

I changed the priority value of instance1=2 and instance2=1. If instance1 is shut down again, instance2 will become the primary. When instance1 is up again, will this new priority setting make sure that instance1 becomes the primary again?

Yes, giving instance1 a higher priority will make the replicaset re-elect instance1 as the primary when it rejoins. See the docs on Adjust Priority for Replica Set Member and Replica Set Elections for more details:

Member Priority
After a replica set has a stable primary, the election algorithm will make a “best-effort” attempt to have the secondary with the highest priority available call an election. Member priority affects both the timing and the outcome of elections; secondaries with higher priority call elections relatively sooner than secondaries with lower priority, and are also more likely to win. However, a lower priority instance can be elected as primary for brief periods, even if a higher priority secondary is available. Replica set members continue to call elections until the highest priority member available becomes primary.


Difference size DBCC Page and DBCC Fileheader I'm trying to read the current database size using dbcc command.
In my test I execute following commands:

DBCC Fileheader('tempdb',1) => size = 988152
DBCC Page('tempdb',1,0,3) => size = 5112

When I check tempdb initial size with the user interface I get 7.720MB (= 988152 * 8 / 1024).
How come there is a difference between the size found with dbcc fileheader and dbcc page?

Update:

Output from DBCC FileHeader can be found here, the output from DBCC Page can be found here

Update:

Yesterday I restarted the server but the difference is still present.
The size with dbcc page is now 0.

  BindingID = 2c0a5351-ab60-11d2-896a-00c04fd9374a
  FileIdProp = 1 FileGroupId = 1                     Size = 0
  MaxSize = 65535 Growth = 32768                      Perf = 0
  BackupLsn = (0:0:0) FirstUpdateLsn = (0:0:0)
  OldestRestoredLsn = (0:0:0)         FirstNonloggedUpdateLsn = [NULL]
  MinSize = 0                         Status = 0
  UserShrinkSize = 65535 SectorSize = 512                    MaxLsn =
  (0:0:0)                    FirstLsn = (0:0:0) CreateLsn = (0:0:0)
  DifferentialBaseLsn = (0:0:0)        DifferentialBaseGuid =
  00000000-0000-0000-0000-000000000000              FileOfflineLsn =
  (0:0:0) FileIdGuid = b0c06a0f-4ee5-43aa-92be-8f03dee24690
  RestoreStatus = 0 RestoreRedoStartLsn = (0:0:0)
  RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
  HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]
  TxfBackupLsn = [NULL] FstrContainerSize = [NULL]
  MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
  SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn =
  [NULL] ReadOnlyLsn = (0:0:0)               ReadWriteLsn = (0:0:0)
  RestoreDifferentialBaseLsn = (0:0:0) RestoreDifferentialBaseGuid =
  00000000-0000-0000-0000-000000000000        RestorePathOrigin

DBCC Fileheader gives: minsize=131072, size=131072 and maxsize=-1

Restart SQL Server and see if they suddenly match.

There is a documented behaviour where if tempdb grows you can get different results depending on how you query what size it is. For example sys.master_files shows tempdb size on start; sys.database_files shows actual size (as do some of the Fileproperty() function results).

Nacho Alonso Portillo wrote on MSDN:

Well, it happens that when SQL Server autogrows a file which is part
of TEMPDB, the size change is not reflected in sys.master_files (or
sys.sysaltfiles for that matter). As per the functional specifications of
the storage engine, for TEMPDB the change in the size of one of its files
is only reflected in those system tables for explicit grows and shrinks,
not for those triggered by the automatic mechanisms.

https://blogs.msdn.microsoft.com/ialonso/2012/10/08/inaccurate-values-for-currently-allocated-space-and-available-free-space-in-the-shrink-file-dialog-for-tempdb-only/


how to find number of empty tables in any schema? I want to query how to find all the empty tables in my schema HR
i already tried with
select *
from dba_tables
where owner='HR' and num-rows is null;
but i dont know if this is the correct logic or not

When you say 'empty' tables, I take that to mean tables with zero rows.

Using the example in this post as a starting point,

WITH TablesAndRowCounts
AS (
    SELECT SCHEMA_NAME(sc.schema_id) SchemaName
        ,sc.NAME + '.' + ta.NAME TableName
        ,SUM(pa.rows) RowCnt
    FROM sys.tables ta
    INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
    INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
    WHERE ta.is_ms_shipped = 0
        AND pa.index_id IN (1,0)
    GROUP BY sc.schema_id
        ,sc.NAME
        ,ta.NAME
    )
SELECT *
FROM TablesAndRowCounts
WHERE rowcnt = 0
and SchemaName = 'HR'


Error while restoring the table dump ERROR 1067 (42000) at line 25: Invalid default value for 'created_on'

I'm getting this error while restoring a table after taking the dump(remotely). I have used

 mysqldump -h (hostname) -u -p (dbname) (tablename) > dump.sql

and restored using

 mysql -u -p (dbname) < dump.sql.

Thank-you.

Please check your current sql_modes by command:

show variables like 'sql_mode' ;

Or

You can include SET statement, before you run any statements put this in the first line:

SET sql_mode = '';

Problem might be because of sql_modes.

So change it by remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work.

This is the default sql_mode in mysql new versions.


SQL DB Trigger - mutiple column changes I need to trigger off 17 different columns and email the change from what it was to what it is changed to. The trigger below works but I feel like this is not the correct way to achieve this. Also, the email should only include fields that were changed. With this design all fields are in the email with "new" and "old" values even if the fields match. How do I remove if the values match?

ALTER TRIGGER [dbo].[Field_Change_Trigger]
ON [dbo].[Loan_Details]
after update
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @tableHTML NVARCHAR(MAX) = N'';
    DECLARE @subject varchar(255);
    DECLARE @recipients varchar(255);
    DECLARE @ID int;
    DECLARE @Status varchar(50);
    DECLARE @LoanAmount decimal(16,2);
    DECLARE @OldAmount decimal(16,2);
    DECLARE @APPDate date;
    DECLARE @OldApp date;
    DECLARE @FixedPymt decimal(16,2);
    DECLARE @OldPymt decimal(16,2);

    select  @LoanAmount = i.LOAN_AMOUNT, @OldAmount = d.LOAN_AMOUNT,
            @APPDate = i.DATE_OF_APPLICATION, @OldApp = d.DATE_OF_APPLICATION,
            @FixedPymt = i.FIXED_PYMT_AMOUNT, @OldPymt = d.FIXED_PYMT_AMOUNT
     from inserted i inner join deleted d on i.LOAN_ID = d.LOAN_ID

SET @tableHTML =

(SELECT   'Amount Changes:'   +  char(9) +'New: '+ convert(varchar(10),@LoanAmount)  + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(10),@OldAmount) + '  ' + char(10) +
        + 'Application Date:' +  char(9) +'New: '+ CONVERT(varchar(50),@AppDate)     + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(50),@OldApp)    + '  ' + char(10) +
        + 'Fixed Pymt Amount:'+  char(9) +'New: '+ CONVERT(varchar(50),@FixedPymt)   + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(50),@OldPymt)   + '  ' + char(10))

Since you only want to fire the trigger if certain fields are updated, you will probably want to wrap the content of the trigger with

IF UPDATE([field name])
BEGIN
[trigger actions here]
END

That at least will limit the number of executions.  I agree with the cautious statement by @LowlyDBA but since this will only fire 20 or so times a day, it's doubtful it would be a problem.

Once you've avoided unnecessary executions, I believe simplifying rest of the email contents in your SET statement can be resolved using a case statement.  Something like

CASE WHEN i.value = d.value THEN '' ELSE 'email string value' END


Step by step:

  Basically, for each work, I want to return the recording where is_art
  = 1 and is_vid = 0 AND were the release is the oldest (oldest year, month and date). Sometimes, the release can have the same year, month
  and day, so, in that case, I guess I need to find a unique identifier,
  so ill go with latest release_id

SELECT r.recording_id, r.name, r.work_id, s.year, s.month, s.day
FROM recording r
INNER JOIN releases s on s.release_id = r.release_id
WHERE r.is_art = 1 and r.is_vid = 0;

With above you will get multiple lines for 1 recording, when there is more than 1 release. We can sort, and take the first record.

SELECT r.recording_id, r.name, r.work_id, s.year, s.month, s.day
FROM recording r
INNER JOIN releases s on s.release_id = r.release_id
WHERE r.is_art = 1 and r.is_vid = 0
ORDER by s.year DESC, s.month DESC, s.day DESC
LIMIT 1;

This should get all releases, which can be linked to work:

SELECT w.work_id, w.name, rr.recording_id, rr.name
FROM work w
INNER JOIN (
    SELECT r.recording_id, r.name, s.year, s.month, s.day
    FROM recording r
    INNER JOIN releases s on s.release_id = r.release_id
    WHERE r.is_art = 1 and r.is_vid = 0
    ORDER by s.year DESC, s.month DESC, s.day DESC
    LIMIT 1
) rr on rr.work_id = w.work_id

Above is untested, so change if needed...

untested, and indeed it is/was wrong, so the changed query to return the recording and release info is:

SELECT r.recording_id,
       r.name,
       r.work_id,
       MAX(s.year) as year,
       MAX(s.month) as month,
       MAX(s.day) as day
    FROM `recording` r
    INNER JOIN `release` s on s.release_id = r.release_id
    WHERE r.is_art = 1 and r.is_vid = 0
    GROUP BY r.recording_id, r.name, r.work_id
    ;

Above is wrong! Is there is one release with a recording in (yyyy/mm) '2020/05' and '2019/06' the month returned for that recording will be '2020/06' ...


Why is my SQL Server consuming so much memory? I have a 64GB Windows Server 2019 Standard server in my TEST Server.
I have SQL Server 2019 CU9 Developer Edition and my the instance is consuming 21,199 MB as shown in sql_physical_memory_in_use_MB.
I have 4 user databases.  Their sizes are approximately 5GB, 2GB, 400MB, and 200MB.
My tempdb is configured to be 8GB at instance start-up.
I'm trying to understand why my instance is using 21GB of memory.  Just worried about a possible memory leak.
Can anyone calm my nerves?
SELECT
    (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
    SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
    SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
    count(*)AS TotalPageCount,
    cast(count(*) * 8192.0 / (1024.0 * 1024.0) as decimal(8,2)) as BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPageCount desc
GO

OUTPUT

Database Name
DirtyPageCount
CleanPageCount
TotalPageCount
BufferPoolMB

SFI_WMS
55
805665
805720
6294.69

tempdb
18648
210842
229490
1792.89

DBAtools
483
82035
82518
644.67

Resource Database
0
3896
3896
30.44

msdb
42
1898
1940
15.16

master
24
766
790
6.17

ReportServer
4
476
480
3.75

ReportServerTempDB
0
308
308
2.41

model
0
245
245
1.91

SELECT
    physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage,
    process_physical_memory_low AS sql_process_physical_memory_low,
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

OUTPUT

sql_physical_memory_in_use_MB
sql_large_page_allocations_MB
sql_locked_page_allocations_MB
sql_VAS_reserved_MB
sql_VAS_committed_MB
sql_VAS_available_MB
sql_page_fault_count
sql_memory_utilization_percentage
sql_process_physical_memory_low
sql_process_virtual_memory_low

21199
0
0
124464
21461
134093263
9673913
100
0
0

SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)';

value
value_in_use

60288
60288

Why is my SQL Server consuming so much memory?

You're looking at total process memory, and one particular consumer of memory.  To drill down from total process memory and get a high-level allocation of SQL Server's memory use use sys.dm_os_memory_clerks, eg:
select type,name, (pages_kb + virtual_memory_committed_kb + awe_allocated_kb) / 1024. committed_mb
from sys.dm_os_memory_clerks
order by committed_mb desc

or the old-school
dbcc memorystatus
If you see large amounts of process memory (large_page_allocations_kb + locked_page_allocations_kb + virtual_address_space_committed_kb) that can't be accounted for by the memory clerks, see if you have any OleDb drivers for linked server loaded in-process.  They allocate memory outside of the SQL Server memory managers.
The two biggest consumers of memory are the the buffer pool and the lock manager.  Both of these will allocate memory and not release it unless the system is under memory pressure.
MEMORYCLERK_SQLBUFFERPOOL Client-Default        9801MB
OBJECTSTORE_LOCK_MANAGER  Lock Manager : Node 0 6929MB

So there's your answer.  Since it's started SQL Server has at some point grown the buffer pool to 9GB and the lock manager memory to 7GB, which accounts for enough of your 21GB to indicate that you don't really have a problem.
See related: memory used by Locks


Is adding rows better then adding columns in case their value is not consistent? In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be more products in the future, but the business won't exceed 30 products. It's easier for me to not create special products table but to add columns to customer table (each new product - new column).

People advised me against it, but I didn't get an explantaion WHY still can't understand why in such case that product's prices has no relation (each customer has his own special price) adding rows to Products table is better then adding columns of products to Customers table? The only benefit I could think of is that customer that has only 5 products won't have to 'carry' 20 nullable products (saves space on server)?

any help would be greatly appreciated

First, it's about normalization. Today you have max 30 special products, tomorrow (or after 10 years for that matter) you might need another extra 5. You will need to write more code, but you will have greater flexibility after all. At least you won't need to code (or design) 30 columns!
You can have a schema like this:

CREATE TABLE Product (ProductId...)
CREATE TABLE Customer (CustomerId...)
CREATE TABLE CustomerProductPrice (ProductId, CustomerId, Price...)

In the third table you can put price for product/customer, but remember to create a unique constraint on foreign key columns like ProductId, CustomerId.


Populate a table on a selective replication slave We have a setup with a data mining slave with a lot of replicate-ignore-table= for tables that aren't needed. Now the developers have requested to populate a table that is currently ignored. How do I go about populating it so that the replication can be started again at the correct point.

I want to avoid locking the table on the master instance as that would lead to application downtime.

We are not yet using GTID.

I have access to MySQL Enterprise Backup if that helps.

Do these two steps while nothing is being added to the table:

Manually copy the table to the Slave(s) (even if empty -- need table definition).
Remove the replacate-ignore setting.


How could I get the value as Y or N in one table if value is exists on another that meets certain condition? I have to generate simple report from three tables named customer, product, and order. My report should have a column that shows whether the customer ordered that product or not. How could I get the Y or N value based on customer id  that exists on order with the product id? Sample table with data is as below:

customer
customer_id name    address phone
123 govinda nepal   16910833
234 arjun   nepal   15546235

product
product_id  name    brand   type    price
1   samsung gayalexy s5 samsung smartphone  55000
2   samsung gayalexy s6 samsung smartphone  65000

order
date    product_id  customer_id
1/1/2016    1   123
5/1/2016    2   123

And want the report column as below

cust_id    name      product_id    order_stat

and order_stat should be as Y or N.

Seems like you need all combinations of products and customers (so a CROSS JOIN between the two tables) and then a correlated EXISTS subquery. This could either be a correlated subquery in the SELECT list:

SELECT
    c.customer_id,
    p.product_id,
    CASE WHEN EXISTS
              ( SELECT *
                FROM "order"  o
                WHERE o.product_id = p.product_id
                  AND o.customer_id = c.customer_id
              )
         THEN 'Y' ELSE 'N'
    END AS order_stat
FROM
    product  p
  CROSS JOIN
    customer  c ;

or a LEFT JOIN to a dummy table with one row (the dual does that in Oracle):

SELECT
    c.customer_id,
    p.product_id,
    CASE WHEN d.dummy IS NOT NULL
         THEN 'Y' ELSE 'N'
    END AS order_stat
FROM
    product  p
  CROSS JOIN
    customer  c
  LEFT JOIN
    dual  d
      ON EXISTS
              ( SELECT *
                FROM "order"  o
                WHERE o.product_id = p.product_id
                  AND o.customer_id = c.customer_id
              ) ;

Random notes:

It's not very good to use reserved keywords in table or column names, like the order. Because you'll have to quote them every time they are used.


Update Server JDK and JRE after Oracle Database Installation and creation Will there be any impact on existing Oracle Database if we upgrade the server JDK/JRE versions in Linux?

Oracle Database ships with its own JDK, it does not use the JDK you install on Linux.

So you can upgrade that without any impact.


Database Full Backup Maintenance Task has large delay between databases I have several database servers that use the same basic maintenance task to do daily full backups over night. Intermittently the backup job which normally takes 4 or 5 hours will suddenly take 13 to 15 hours. At first I figured the network was busy or the operations team started their nightly processing early, but I've been digging into this further and I noticed that something odd.

The maintenance task is set to do a backup of all databases ignoring those that are offline. It saves the backups to a Data Domain server.
This is what is expected to happen and usually does:

At 9pm the backup job starts.
System databases backup first and take a combined 2 minutes
DB1 backup starts and takes 1.5-2.5 hours done around 11pm give or take
DB2 backup starts and takes 2.5-3.5 hours
Job finishes between 1-3 am

Looking at the backup set history in MSDB this is what I'm seeing

At 9pm the backup job starts.
System databases backup first and take a combined 2 minutes
DB1 backup starts and takes 1.5-2.5 hours done around 11pm give or take
tsql command to start backup of DB2 begins executing
7-9 hour delay
DB2 backup actually started, according to MSDB.dbo.backupset history, and takes 2.5-3.5 hours
Job finishes between 8am and 12pm

As far as I can tell there is no activity before 2am that should be causing locks or contention, and the delays appear to be happening irregularly

My Question is this, what could be causing this large delay from when tsql is executed to when the backup is actually started?

Edit
Rough Server Specs:
VM Ware Virtual Machine (All resources dedicated)
Windows Server2012 R2
SQL Server 2014 SP2
CPU: 16 total cores
RAM: 225GB

This is sometimes due to an MSDB.dbo.backupsethistory table that's never been purged. SQL Server inserts a row into that table, then goes back and updates it later during the course of doing backups.

If your MSDB is on really slow storage, and you can't cache it in memory, and it's got a lot of history, then accessing that table can be your biggest bottleneck. I wrote a blog post called Brent's Backup Bottleneck: MSDB about a really bad occurrence of it.

To check that, run sp_Blitz (disclaimer: free open source script I wrote) and one of the warnings is MSDB History Not Purged, which warns if you've got more than 60 days of history stored.

To fix it, add a History Cleanup Task to your maintenance plans. However, if you're in this spot - where updating this table is taking forever - then clearing out history is probably going to take forever, too. You may have to run sp_purge_jobhistory manually yourself, nibbling off a little history at a time, to keep downtime low.


How can I combine these tables? I have my system working with drinks, and now I'm ready to add burgers and other types of items.

Right now I have 1 orders table with separate item and itemorder tables for each type of item.

This is not sufficient because it is difficult to combine the items into 1 order.

Can you suggest any better way to do it? I'm confused about how to combine them because drinks and burgers have different options, and when I will add more and more items I expect a larger variety of options.

What's the most efficient way to do it?

EDIT 1:

I combined the item tables, combined the itemorder tables, and added a table with the price and surcharge information so that it won't be stored as often.

I feel it can still be improved.

EDIT 2:

I decided to remove all enums (thanks to Vérace and sibert), and I've improved my price structure (thanks to Lennart), but I'm  still trying to determine whether the price table and its relationship are as good as they can be. The itemorder table now refers to priceid so I think it will allow me to correctly retrieve historical itemorders with accurate prices.

I still feel it can be improved.

Rule of thumb 1: If the table contains stuff that may change (edit, delete or add columns) in the future, it probably belongs to the data level.

Rule of thumb 2: Always have an unique key to every table. It is simpler to edit order records if there is a unique key for each row.

Rule of thumb 3: Rules of thumbs is not always the best way to do things. It always depends...

One way to struct this:

article_type

type_id   1          2         3
type_desc Burger     Drink     Extra stuff

EDIT 1 ---> You do not need to have drink categories. You can have Burger Big, Drink too much etc as several types. The article is the king with all information gathered. Select article and you have all information you need.

article

art_id    1              2               3             4
art_desc  Burger Big     Burger Small    Burger King   Drink little
art_type  1              1               1             2
art_price 50             40              60            10

EDIT 2 ---> I can see no value (at this point) to have a separate price table. The price should be attached to the article. Which also means one less column in the order table.

order

order_id     11
order_status 1 (int) Lookup table
order_date   2018-01-01

order_rec

ordrec_id    1
ordrec_order 11
ordrec_art   1
ordrec_sum   50

When fetching an order you can sum on-the-fly. No need to save sum at order level.

SELECT order_id,sum(ordrec_sum)
LEFT JOIN order_rec ON ordrec_order=order_id
GROUP BY 1
WHERE order_id=11

article_type can be useful if you add more groups of meals to the articles.


Fetch partitions where values are present I have table partitioned by range of dates (monthly).
Having a list of dates, I want to select all the partitions, where values from the list of dates are present. That is to avoid scanning the partitions.
What would be the query to do that?
Say, I have:
partitions: calendars_y2021_m01, calendars_y2021_m02, calendars_y2021_m03
list of dates: 2021-01-01, 2021-02-01, 2021-02-02
I want to:
select partitions from calendars
where dates in (2021-01-01, 2021-02-01, 2021-02-02)

That should give me all the rows from partitions: calendars_y2021_m01, calendars_y2021_m02
The only solution I got myself is to identify the target partitions and fetch them one-by-one with python loop, but that is slow.
I am using RANGE partitioning. Selecting from calendars will scan the partitions for the specific dates. I just want to get all the data from the selected partitions into memory to avoid that.
If I select * from calendars where dates in (list of dates) PostgreSQL will first (1) define target partitions, then (2) SeqScan the partitions for the values in list of dates. I want to skip the second operation and get the entire partitions.
Once I have the partitions, I will process them in memory with python pandas. That is way faster, then seq scan. Also, I am just biased towards pythonic tools.

SELECT DISTINCT
       format(
          'calendars_y%s_m%s',
          lpad(CAST(extract (year FROM q) AS text), 4, '0'),
          lpad(CAST(extract (month FROM q) AS text), 2, '0'))
FROM (VALUES (DATE '2021-01-01'),
             (DATE '2021-02-01'),
             (DATE '2021-02-02')) AS q(q);

       format
---------------------
 calendars_y2021_m01
 calendars_y2021_m02
(2 rows)

But I seriously doubt that it is a good idea to do that on foot. You will be better off letting the PostgreSQL optimizer figure out which partitions to scan.


Need for reaching data through clustered index with a non-clustered index I have found that when a table has both clustered and non-clustered indexes (on different columns), the leaf level non-clustered pages, instead of pointing to the data row, point to the node of the clustered index, from where another search is instituted to find the data row. What is the point of this extra level of indirection? If the clustered index has, say, 8 levels, then the indirection from the NCI leaf page to the CI root would have to traverse through these 8 levels to reach the data. Why not store the normal RID in the NCI leaf page so that we can access the data at once without going through the CI index structure?

The reason for this is that the "fixed" physical location of your row - the RID (or row identifier) might (and will!) change over time - think page splits that occur when a row needs to be inserted into a table on a page that's already full.

Updating those RIDs in all the nonclustered indices that exist on a given table is quickly becoming both a hassle, and a huge performance killer. You might have 5, 10, 20 nonclustered indices on your table, and SQL Server would have to scan all those indices (basically scanning the whole index, all rows in the index, and that 10, 20 times) and update all RIDs.... that's just not practical - very quickly so.

If you store the value of the clustering index as a "row pointer" instead, that value should typically never change - and most definitely it doesn't need to be updated every time a page is split. Yes, it does involve a second index seek operation - the key lookup - but for simple scenarios, retrieving a single row or a few rows, that's still much more efficient than anything else.


Inserting into table: either invalid datatype or duplicate column name error. (But the datatype is valid and there's no duplicate) Spoiler: solution to this is here. the solution was really simple.
I'm trying to insert data from an external table.
Here's the code I'm trying to use:
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)
SELECT *
    FROM EXTERNAL ((
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      mw_kda NUMBER (7,3))

  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext

    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

But now i get this error:
Error at Command Line : 171 Column : 7
Error report -
SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"

Line 171 is the last line of this part
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)

Which is clearly not a duplicate column.
If I do:
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
    (construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    mw_kda)
SELECT *
    FROM EXTERNAL ((
      ext.construct_id NUMBER (10),
      ext.n_term VARCHAR2 (50),
      ext.enz_name VARCHAR2 (50),
      ext.c_term VARCHAR2 (50),
      ext.cpp VARCHAR2 (50),
      ext.mutations VARCHAR2 (50),
      ext.mw_kda NUMBER (7,3))

  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext

    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

I get
Error at Command Line : 174 Column : 10
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Line 171 is ext.construct_id NUMBER (10),

Background:
The reason I'm listing the columns out instead of just saying insert into construct select  is because I have a virtual column in the construct table taht's defined like this: construct VARCHAR2 (200) GENERATED ALWAYS AS  (n_term || '-' || enz_name || '-' ||c_term || ' ' ||mutations) VIRTUAL,
And there's no corresponding column in the csv file for it.
The second column in the ERD called CONSTRUCT is defined like this:

Edit: originally, I defined datatypes for columns from the table that I was inserting into. This was a mistake and I deleted that code, replacing it with the new version for readability. It was taking up a lot of room in this post.
(Edit: originally, I defined datatypes for columns from the table that I was inserting into. This was a mistake and I deleted that code, replacing it with the new version for readability. It was taking up a lot of room in this post.)

If you used an IDE to develop your code, the IDE should show you exactly where you have an error.
SQL Developer shows that you have a syntax error. (magenta squiggly underline)

Remove the data type definitions from your first list of columns.  (The data types are defined at the time of CREATE TABLE)
If you ever have a question about the syntax of something, always refer to the fine manual.  In your case, the "data type" (in your first list of columns) is not part of the formal syntax.  Therefore - you get an error.
Image from Documentation


Massive peer-to-peer replication topology? We have a client that's geographically distributed in remote communities, with somewhat unreliable network/internet connectivity between each of the physical locations. There is a single central location, and 52 satellite locations.

Normally, our application is deployed as a central solution where there is only a single database, and all locations connect to the one database. The data itself is partitioned by a location column in most of the tables (some data is centralized), and the application operates such that touching data at one physical location does not touch data at another physical location, including the centralized data (at least, we're pretty sure it doesn't -- if it does, it's likely a bug). Our client would like to do centralized reporting (which we normally support), and synchronization of several centralized configuration tables across the enterprise (same, because there's normally only one copy of the tables).

Every location has its own data center, and is licenced for SQL Server 2008 R2 Enterprise.

The goal is to deploy our application such that broken network/internet connectivity must not prevent local read/write operations, as our application is mission-critical.

My first thought was to use a giant peer-to-peer topology that replicates every object in the database. I haven't been able to find any documentation or references of any installation that's scaled out this much using this method, so I have no idea if it's technically feasible, and if so, what hardware is going to be required. Would this work with a centralized distribution database (for ease of management)? Is this even a good idea at all? How are database schema changes to be deployed? Does index maintenance generate a huge amount of network overhead?

There's also the option of creating a roll-our-own solution, which I figured would involve log shipping separate copies of the database (each non-central location would only contain its partitioned portion of the data) to the central location, and then using a merging tool (which we already have) to create a reporting database. Configuration table changes would be pushed out by some other mechanism, possibly merge replication, possibly a homebrew solution.

I considered and tested using merge replication on the entire database, but we require quite a lot of database and application changes/redesign/etc. to make this work correctly, so I've ruled that out already due to time constraints. (Thoughts on this?)

What I'm looking for here is some guidance from people with more experience. Obviously, planning for this is critical. Am I on the right path? Are there other options? (URLT?)

For a meer 52 client sites replication will do just fine. Consider your local copy as the 'master' site and have the application work only on the local copy, always. Use replication to aggregate the data to the central repository for aggregated reporting. Index maintenance operations normally do not generate replicaiton traffic. Manage schema changes through application deployment migrations, ie. have upgrade scripts for each released version.

As the number of sites increases, managing replication becomes harder and harder. Also licensing would push toward deploying Express on periphery. And SQL Server upgrade is an absolute pain when replication is involved, as the order of upgrading the components (publisher, distributor, subscriber) is critical is but difficult to coordinate with many sites. Deployments that I know of with +1500 sites use customized data movement based on Service Broker. See Using Service Broker instead of Replication for an example. I've even seen designes that use Service Broker to push out to periphery new bits (application changes) which in turn deploy, when activated, migrations and schema changes, making the entire deployment operated from the center.


how to add a user to local sql server when one has only windows admin rights on the server I'm in the admin group of a server, but yet I don't have access to the sql server, is there anyway to add myself without the need to reinstall the sql server?

It's pretty easy to do if you are a member of the local Administrators group.
The below steps are taken from this answer on StackOverflow:
Open a command prompt window. If you have a default instance of SQL Server already running, run the following command on the command prompt to stop the SQL Server service:
net stop mssqlserver

If you're running a named instance of SQL Server, the Service Name will be something similar to mssql$instancename.
Open SQL Server Configuration Manager from the Windows Start Menu.  Find the instance listed in the "SQL Server Services" window, and right-click it, then click "Properties".  Go to the "Service Tab", and make note of the "Binary Path" and CD into it as such:
CD "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn"

Run the following command to start SQL Server in single user mode.  As
SQLCMD is being specified, only one SQLCMD connection can be made (from another command prompt window).
sqlservr -m"SQLCMD"

Open another command prompt window as the same user as the one that started SQL Server in single user mode above, and in it, run:
sqlcmd

And press enter. Now you can execute SQL statements against the SQL Server instance running in single user mode.  To add a login for your account, run:
CREATE LOGIN [DOMAIN\USERNAME] FROM WINDOWS;

Replace DOMAIN and USERNAME with appropriate values.
-- For older versions of SQL Server:
EXEC sys.sp_addsrvrolemember @loginame = N'DOMAIN\USERNAME'
     , @rolename = N'sysadmin';

-- For newer versions of SQL Server:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\USERNAME];
GO

Source.


MS SQL difference between two latest records Given table is :

create table events (
      event_type integer not null,
      value integer not null,
      time timestamp not null,
      unique(event_type, time)
  );

need to return difference of value for event_type between latest and second latest registered.

You can use lead() window function to get next record value (order by time desc for each event). row_number()=1 will give you just the first row per event_type

select t1.event_type, t1.value - t1.prev_value as diff
  from (
     select t.event_type,
            t.value,
            lead(t.value) over (partition by t.event_type order by time desc) prev_value,
            row_number() over (partition by t.event_type order by time desc) rn
       from events t) as t1
where rn=1
  and prev_value is not null-- this will prevent event_types with just one row


Does mysql horizontal partitioning create new ibd files for each partition? To my best knowledge, each table created in mysql creates an ibd file as a pointer to your table. Any alteration made to this table happens on this ibd file.
Now if Table1 of size 1GB, with its ibd file x.ibd, is horizontally partitioned equally into p1,p2,p3,p4. Do the new partitions p1,p2,p3 and p4 having equal size around 250MB each have their own ibd files? If no, how can we do it?

Short answer: Yes; 4 .ibd files of about 1/4 the total size.

Long answer:

There are multiple things going on...

Different engines have PARTITIONs implemented.  In all cases from 5.1 (when Partitioning was introduced) through at least 5.7, a partition is effectively a separate table.
innodb_file_per_table controls whether InnoDB puts new tables (and its partitions) in the ibdata1 file, or in individual .ibd file(s).
"Native partitions" may be coming soon (5.8?); that will change much of this answer.
Depending on the data distrubution and the details of the PARTITION clause in your CREATE TABLE, the partitions are not necessarily similar size.

In my experience, PARTITION BY RANGE is the only useful one, and there are only 4 use cases where it is of any use.  Details.


Upgrade with DistributedAG vs adding a node with SQL Server 2019 We would like to migrate from SQL Server 2016 to SQL Server 2019.   The databases are part of an Availability Group.
I added a new replica using SQL Server 2019 on a new node and made that replica part of the AG and then failed over successfully to it.
Would a Distributed AG also provide me the same functionality?  What is the advantage of using a Distributed AG versus using the above method to migrate to SQL Server 2019?.
I'm trying to understand if the procedure I followed is correct and supported?  In what way is a Distributed AG useful?

Per the Microsoft Docs, a Distributed Availability Group is:

a special type of availability group that spans two separate availability groups. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment. This includes cross-domain and even cross-platform - such as between an availability group hosted on Linux and one hosted on Windows. As long as two availability groups can communicate, you can configure a distributed availability group with them.

Typically, a Distributed Availability Group is used to physically distance two AGs for disaster recovery purposes.   i.e if the primary AG goes down, you failover to the secondary AG, and return to normal operation.
The fact that you've added a SQL Server 2019 node to your existing AG is not a problem as long as you understand that you need to upgrade all the nodes in the AG to 2019 if you plan on ever failing back to those old nodes.  Once a database has been opened in a newer version of SQL Server, the database upgrade process means that you can never open that upgraded database in an older version of SQL Server.  Therefore, you won't be able to fail back to those old nodes if the new SQL Server 2019 node has a problem.


Break the link between a Database User and SQL Server Login When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User.

This is normally achieved with one of the following procedures.

Deprecated sp_change_users_login Stored Procedure

use <database>
go
sp_change_users_login 'Update_one', '<datbase_user>', '<sql server login>'
go

ALTER USER Command

use <database>
go
ALTER USER [<datbase_user>] WITH LOGIN = [<sql server login>]
go

These commands will re-link an orphaned Database User to a corresponding SQL Server Login.

Question

Is it possible to break this relationship without deleting either the SQL Server Login or the Database User?

Reasons

Deleting the Database User will remove the permissions in the database.
Deleting the SQL Server Login will remove the password. (Hashed value; unknown to DBA)
I linked a Database User to a SQL Server Login that is a Windows System Account.

Research

I have had a look at the sys.database_principals and sys.server_principals DMVs, but they are not modifiable.

You can accomplish this in a roundabout way be creating a temporary login, remapping the user to the temporary login, and then dropping the temporary login.  For a SQL login:

USE [master]
CREATE LOGIN [temp_user] WITH PASSWORD=N'asdf' MUST_CHANGE,
    DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
ALTER LOGIN [temp_user] DISABLE

USE [test_database]
ALTER USER test_user WITH LOGIN = [temp_user]
DROP LOGIN [temp_user]

For a Windows authenticated login/user, you would need to create a temporary Windows account, and then delete it, so it's not a 100% T-SQL solution:

-- Create a Windows account with a name of Temp_User
USE [master]
CREATE LOGIN [COMPUTERNAME\Temp_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
ALTER LOGIN [COMPUTERNAME\Temp_User] DISABLE

USE [test]
ALTER USER [COMPUTERNAME\Existing_User] WITH LOGIN = [COMPUTERNAME\Temp_User]
--Delete the Temp_User Windows account


Mongodb chunksize issue I have a collection which have size : 60 GB

I want to shard this collection. Shard key is hash index and unique.

I followed this document https://docs.mongodb.com/manual/reference/limits/#Sharding-Existing-Collection-Data-Size

Avg byte size of shard key values 56287232

maxCollectionSize is coming 9.28 GB

As maxCollectionSize and Actual collection size different is huge i am unable to get what chunk size should i use

Thanks for help

As per MongoDB documentation Modify Chunk Size in a Sharded Cluster The default chunk size for a sharded cluster is 64 megabytes.

As MongoDB documentation here New in version 2.6: MongoDB provides the mergeChunks command to combine contiguous chunk ranges into a single chunk. See Merge Chunks in a Sharded Cluster for more information.

Note: Be careful when splitting data in a sharded collection to create new chunks. When you shard a collection that has existing data, MongoDB automatically creates chunks to evenly distribute the collection. To split data effectively in a sharded cluster you must consider the number of documents in a chunk and the average document size to create a uniform chunk size. When chunks have irregular sizes, shards may have an equal number of chunks but have very different data sizes. Avoid creating splits that lead to a collection with differently sized chunks.

For further your ref here


Will PostgreSQL use parts of a composite index even if all parts aren't listed in the where clauses? I could obviously test this empirically but I've found that without loading fake data EXPLAIN shows that Postgres just loads the full page of memory containing the table and scans. I also haven't found the answer in the docs.
My question is, if I have an index on a table such as (colA, colB) and a query containing where colA = 'something' will Postgres use this index even though colB is not in the query? Assume that no index containing just colA exists. It stands to reason that this index could be useful in the query execution, but I've had a hard time tracking the answer down definitively.
(Postgres v12.x)

Yes, because that composite index in your example is also known as a covering index. Specifically mentioned in the previously linked documentation here:

...people sometimes made covering indexes by writing the payload columns as ordinary index columns, that is writing
CREATE INDEX tab_x_y ON tab(x, y);
even though they had no intention of ever using y as part of a WHERE clause. This works fine as long as the extra columns are trailing columns...

Basically, as long as the fields of your predicates are all sequentially part of the index definition (from left to right), then that index is covering and applicable to be used for that query.
The reverse of your example would not work though since the index does not cover that scenario. I.e. WHERE colB = 'someValue' is not covered by an index with the definition of (colA, colB) since the order of the field list in the index definition literally defines the structure of the underlying B-Tree used to sort the data. So first the data is sorted on colA and then colB, making it not possible to directly search on colB (aka directly seek on colB, rather an index scan could still be performed to indirectly search on colB) without a predicate to search on colA first.


Filter from the same user times i have a table with user id-s, and many times for the same users, like this:

id    time
1     13:00:01
1     13:00:01
2     13:00:09
1     13:00.23
1     13:00:24
2     13:00:11
2     13:00:11

I would like to get this dataset:

id    time
1     13:00:01
2     13:00:09
1     13:00.23
2     13:00:11

So i would like remove all the rows with the same id-time pair and with the same id, but where the time between the same user times is bigger than 20s. Can anybody help to me? I'm hanging on this problem for 4 days :(

http://sqlfiddle.com/#!9/c88a4/8

select t1.id, t1.t, time_to_sec(t1.t) s
from times t1
left join times t2 on t1.id = t2.id
  and time_to_sec(t1.t) > time_to_sec(t2.t)
  and abs(time_to_sec(t1.t) - time_to_sec(t2.t)) <= 20
where t2.id is null
group by t1.t, t1.id

uses "groupwise-max" method with dynamicaly defined group. Seems to work on your testing data.


How to restore a database to a point in time using a script in Microsoft SQL Server I have a database that must always restore to the date in which it was created, after test data have been added on it, I get the create_date from the script below:

WITH LastRestores AS
(
SELECT

    [d].[create_date] ,

    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
WHERE [d].[name] = 'MyDatabase'
)
SELECT create_date
FROM [LastRestores]
WHERE [RowNum] = 1

Now what I need to do is run a script that restores the database to the date in which it was created, assuming the date is static/hardcoded in the script i.e: is always '2019-03-08 11:31:50.237' and the database is not restored but stored in a certain folder i.e C:\Documents\BackUps\MyDatabase.bak , now lets say I normaly restore databases like this:

USE [master]
RESTORE DATABASE MyDatabase
FROM
DISK = N'C:\Documents\BackUps\MyDatabase.bak' WITH  FILE = 1,
MOVE N'MyDatabase' TO N'C:\Documents\BackUps\MyDatabase_rows.mdf',
MOVE N'MyDatabase_log' TO N'C:\Documents\BackUps\MyDatabase_log.ldf',
NOUNLOAD,  STATS = 5
GO

The only difference now is that it must restore to a point in time which is '2019-03-08 11:31:50.237' using a script.

You need at least two backups for this. One full backup and one transaction log backup. So first you use a command like your above command (but you can remove UNLOAD option, it is a dummy option when you restore from disk). And then you have something like:

RESTORE LOG MyDatabase
FROM DISK = N'C:\Documents\BackUps\MyDatabase.trn'
WITH
 FILE = 1
,STATS = 5
,STOPAT = '20190308 11:31:50.237'

That log backup had to been produced after the point in time to which you want to restore. And since you need a log backup, you need to have the database in FULL (or BULK_LOGGED) recovery mode, not SIMPLE.


Always On FCI vs Always On Availability Groups What is the advantage of Always On Failover Clustering vs Always On Availability Groups?

Basic Always On Failover Clustering provides server level protection, (eg: 2 servers with 1 shared disk space;  if server fails, it can utilize another server on the shared disk space).

Always On Availability Groups provides both disk storage DR and server-level HA Protection. It provides 2 servers with 2 shared space.

So what benefit functionally does Always On Failover Clustering have over Availability Groups?  Looking at the chart comparison I see none. Seems Availability Groups is better.

We utilize SQL Server 2016 Enterprise.
Thanks.

Reviewed documentation:

Failover Clustering and Always On Availability Groups
SQL AlwaysOn

What is the advantage of Always On Failover Clustering vs Always On Availability Groups?

The main advantages given 2012->2017 for an FCI is:

No instance level metadata or objects required to be synchronized
Databases can be in any recovery model
Only one copy of the databases is required (1x total disk space required)
Generally easier to troubleshoot as it isn't a distributed system

  Seems Availability Groups is better, Looking at the chart comparison I see none

It depends on what you're protecting against and what infrastructure you have in order to protect it. Then it comes down to what people have the most experience supporting.

In Availability Groups, here are some common disadvantages:

Instance level metadata needs to be manually synchronized between participating nodes (Logins, Jobs, certain encryption keys, proxy accounts, etc.)
It's a distributed system and is generally harder to troubleshoot
Requires your networking be of the utmost quality and lowest latency (even in asynchronous mode)
Requires x*n amount of storage where n is the number of replicas in the availability group and x is the sum total of size for the participating databases.


How to find out the models and prices for all the products (of any type) produced by maker B The database scheme consists of four tables:

Product (maker, model, type)
PC (code, model, speed, ram, hd, cd, price)
Laptop (code, model, speed, ram, hd, screen, price)
Printer (code, model, color, type, price)

The Product table contains data on the maker, model number, and type of product (PC, Laptop, or Printer). It is assumed that model numbers in the Product table are unique for all makers and product types.

Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed column, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, 4x) - cd, and its price.

The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen.

For each printer model in the Printer table, its output type (y for color and n for monochrome) – color column, printing technology (Laser, Jet, or Matrix) – type, and price are specified.

Now the query I came up with:

SELECT PC.model, price FROM PC
INNER JOIN Product ON PC.model = Product.model
UNION
SELECT Laptop.model, price FROM Laptop
INNER JOIN Product ON Laptop.model = Product.model
UNION
SELECT printer.model, price FROM printer
INNER JOIN Product ON printer.model = Product.model
WHERE Product.maker='B'

The result should be:

model   price
1121    850.0000
1750    1200.0000

I am doing SQL exercise and not able to get the same result.

In a UNION/UNION ALL query, a WHERE clause filters a single sub-SELECT (the one immediately preceding it) rather than the entire UNIONed set – thus, each sub-select can have its own WHERE clause. (This is unlike e.g. ORDER BY, which you would be allowed to specify only once and it would apply to the combined set.)

So, if you want products by the same maker B from each of the three category tables, you need to repeat the filter for each sub-select:

SELECT PC.model, price FROM PC
INNER JOIN Product ON PC.model = Product.model
WHERE Product.maker='B'
UNION
SELECT Laptop.model, price FROM Laptop
INNER JOIN Product ON Laptop.model = Product.model
WHERE Product.maker='B'
UNION
SELECT printer.model, price FROM printer
INNER JOIN Product ON printer.model = Product.model
WHERE Product.maker='B'

That may look like redundant coding, which it probably is, and there are ways to eliminate the redundancy in this case – for instance by using a derived table:

SELECT
  derived.model, derived.price
FROM
  (
    SELECT model, price FROM PC
    UNION
    SELECT model, price FROM Laptop
    UNION
    SELECT model, price FROM printer
  ) AS derived
  INNER JOIN Product ON derived.model = Product.model
WHERE
  Product.maker='B'
;

But in SQL, the DRY principle does not apply as often as in other languages, meaning that repetition is not always bad in SQL and eliminating it is not always good. In theory, the two queries above should be equivalent. In practice, however, not every query optimiser might recognise that, and the second query might result in a less efficient execution plan.

In short, repeating a condition several times across a single query is not uncommon in SQL. Sometimes repetition becomes too much and, to help maintainability of your queries, you may want to employ views or stored routines. But in such cases, too, revisiting your schema often has an even better impact.


Manually installing sample schemas in Oracle 11g I am trying to manually install HR schema in my oracle environment by following the steps specified by oracle Help Center (11.2). Web link: https://docs.oracle.com/cd/E11882_01/server.112/e10831/installation.htm#COMSC001
However, I have run into some problems. It is specified that I only need to call hr_main.sql script (located in human_resources directory)using this command @?/demo/schema/human_resources/hr_main.sql.
My human_resources has only a script named hr_code and when I run it I do not get any type of installation procedure (entering password, tablespace etc as oracle specifies in the procedure).
Any help as well as other possible solutions would be very much appreciated.
Alternatively, could it be more feasible if I created a new database using database configuration assistant (thus installing the schemas during the installation procedure itself)?

Manually Installing Sample Schemas

  Install Oracle Database Examples (Companion CD, part of the media kit)
  to include these scripts in the demo directory under $ORACLE_HOME.

The scripts for installing sample schemas is a seperate download and they are not installed by default.

Download Oracle Database Examples

Follow the instructions there.


Prevent MySQL to connect Master We switched a slave to master today and after restarting the new master it tries to connect to the old master.

Even with stop slave;, it tries to connect after restarting.

You need to use RESET SLAVE to tell the instance to forget about its former master.

The exact commands to run depend on your MySQL version.

For MySQL versions prior to 5.5:

stop slave;
change master to MASTER_HOST='';
reset slave;

For MySQL 5.5 and above:

stop slave;
reset slave all;

Either way, make sure that you also remove any “master-” statements from my.cnf if you have them.


How to sync daily the prod database to dev database in Azure SQL Server service? I have two Azure SQL Database, prod and dev.
How can i daily replicate the prod database into dev database?
I have tried to automate it by exporting and importing the BACPAC file but it takes too long time.
There is Data Sync service in Azure but I am not sure if it works in one direction.
Do you have any suggestions?

You can try "Geo-replication"
Go to your prod "SQL Database", Settings > Geo-replication
Configure your dev server/database to be readable "secondary"
All changes made at prod database , will be applied to dev database right away
Beware that you won't be able to change (insert,update,delete,create,etc) data at dev database while it is in the "readable secondary" role - only read (select)


Consistency errors in SQL Server 2000 I have SQL Server 2000 and ever since upgrading from SP2 to SP4 random errors began happening.  Mostly overflow errors, sometimes in SqlDateTime and sometimes in data type numeric.

For example, right now I have a column with 1099511628.776 as its value, the column type is decimal(18,3) which can hold up to 10^38, so clearly this is not an overflow, however when I try to update that column and set it to 1 I get the error Arithmetic overflow error converting numeric to data type
numeric.

I ran DBCC CHECKDB on the database and I got 6 consistency errors in two tables, for the first table:

Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 578101100, index ID 0, page ID (1:24157), row ID 61.
Column 'wh_id' was created NOT NULL, but is NULL in the row.

Second table:

Msg 8928, Level 16, State 1, Line 1
Object ID 933578364, index ID 0: Page (1:7160) could not be processed.
See other errors for details.

Msg 8939, Level 16, State 12, Line 1
Table error: Object ID 933578364, index ID 0, page (1:7160).
Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 65536.

Msg 8944, Level 16, State 2, Line 1
Table error: Object ID 933578364, index ID 0, page (1:7223), row 12.
Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed.
Values are 17 and 193.`

Msg 8928, Level 16, State 1, Line 1
Object ID 933578364, index ID 0: Page (1:7254) could not be processed.
See other errors for details.`

Msg 8939, Level 16, State 106, Line 1
Table error: Object ID 933578364, index ID 0, page (1:7254).
Test (m_freeCnt == freeCnt) failed. Values are 2442 and 2444.`

Now for the first error, I tried running the query:

SELECT *
FROM table1
WHERE wh_id IS NULL

However, it returns zero rows. For the rest of the errors, I don't know how to query the table to find the the bad row(s).

How can I fix these consistency errors, and are they the reason for the random errors?

Those errors are corrupt pages in the actual table themselves.  You will need to restore the table.  There's no way to repair the issue without loosing data as the corruption is in the base table.


Metrics for monitoring master-slave replication Is there some kind of query/view/table available in PostgreSQL that can reflect the actual status of a master-slave replication process? Perhaps the "degree" of sync between the nodes?

The above is to periodically monitor our servers to ensure they are actually "in sync" and the slave hasn't gone adrift.

In the primary side, you have to use the view pg_stat_replication and compare differents fields to see the drift. The function pg_xlog_location_diff give the drift in size :

select   pid, client_addr, state, sync_state,
         pg_xlog_location_diff(sent_location, write_location) as write_lag,
         pg_xlog_location_diff(sent_location, flush_location) as flush_lag,
         pg_xlog_location_diff(sent_location, replay_location) as replay_lag
from pg_stat_replication ;

You can use the lag in monitoring tools, as gauge, to monitor your replication.

If a standby is missing, you have to check in the log's standby why.

If you use replication slots, you have to check the pg_replication_slots view.


Handling a big table in mysql I have a really big table (approx. > 100.000.000 rows and a size of > 50GB) and it's getting a big performance kill right now. Beneath the primary key (id) it uses a fulltext key on a varchar(500) field to use the mysql fulltext search option.

However, saving and getting rows to this table is getting really slow right now.. How can I handle this? This is my first problem. My second problem is, that getting a backup with mysqldump of this table is no use, because it would take months to import it back. The third problem is, that this table uses the engine MYISAM and a conversion to INNODB is also not possible (I've tested it and aborted the proccess after 72 hours).

So, what would be a good future-proof approach to speed this table up, back it up correctly and maybe convert it to INNODB? (INNODB should accept FULLTEXT with my mysql version)

I'd suggest a more radical approach. For databases of your size a fulltext search is not only ineffective, but inefficient. I'm guessing that there's some kind of user-driven search function requiring your index.

How about you employ a real search engine? This would take the load of key generation and reordering off of your database. It would give you the chance to offload the load to even another machine.

Take a look at Apache Solr, a well-received, fast implementation based on Lucene. Many large non-profit and commercial websites use it with success.

Then, remove the fulltext indexing from your table. Inserts should then fly into the table with only the ID key left.

Also, if you regularily remove rows from the table, OPTIMIZE TABLE should be done regularily.

For backup purposes, you might consider replication. There are multiple ways to implement the replication, and all of them spread the load out over time, instead of creating a downtime for it, which is what you have now. As an added benefit some replications can produce a database that can be used as a stand-in replacement if the primary database fails, so that the application can be up again in little to no time.


Find growth from dates group by months This is a follow-up to my previous question Find month to month percentage growth rate

I have a table called Lead. I need to find out the month to month increase in lead how can achieve this in MySQL

I need to find percentage of increase/decrease  in Lead each months..

Table

CREATE TABLE Lead(leadId int(11) NOT NULL AUTO_INCREMENT,
  leadType varchar(99) DEFAULT NULL,
  leadName varchar(99) DEFAULT NULL,
  leadDate date DEFAULT NULL,
  PRIMARY KEY (leadId));

Table Data

insert into Lead values
(16,'individual','dinesh','2016-06-18'),
(15,'corporate','benz','2017-05-10'),
(14,'individual','fino','2017-05-05'),
(13,'individual','jubin','2017-04-30'),
(12,'corporate','chola','2017-06-16'),
(10,'corporate','Volvo','2017-03-31'),
(9,'individual','manoj','2017-06-16'),
(8,'individual','vijay','2017-06-16'),
(6,'corporate','CHS','2017-06-16'),
(5,'corporate','sastra','2017-06-16'),
(4,'individual','rajesh','2017-06-16'),
(3,'individual','karthi','2017-06-16'),
(2,'individual','vijo','2017-06-16'),
(1,'individual','suresh','2017-06-16');

AS I explained on my previous answer you can use a variable to store last entry value.

In this case you need to count entries grouped by year and month.

SELECT Year, Month, Entry,
       IF(@last_entry = 0, 0, ((entry - @last_entry) / entry) * 100) "Growth Rate",
       @last_entry := entry
FROM
      (SELECT @last_entry := 0) x,
      (SELECT YEAR(leadDate) Year, MONTH(leadDate) Month, COUNT(*) Entry
       FROM   Lead
       GROUP BY 1,2) y;

This is the result:

| Year | Month | Entry | Growth Rate      |
|------|-------|-------|------------------|
| 2016 | 6     | 1     | 0                |
| 2017 | 3     | 1     | 0                |
| 2017 | 4     | 1     | 0                |
| 2017 | 5     | 2     | 50               |
| 2017 | 6     | 9     | 77,7777777777778 |

Rextester here


Is this update, select combination query thread safe? I have a table called seats, which has a schema like this

  id,taken

For each user, I take a random untaken id and assign to that user, here for simplicity say, I make taken =1. The query I am using

   update seats u inner join  (
        SELECT id from seats
        where taken is null limit 1) s
   on s.id = u.id set taken = 1;

This query takes a random seat with taken flag null, and for that seat it makes the flag 1. While this query is working fine, is this  thread safe?

Consider this scenario, I have two users in parallel. For user1, I select row X, and just before update query is ran user2 checks in, and for that user select query returns the same row as user1. So I will end updating the same row twice.

Is this scenario possible with this query?

ASSESSMENT
Your query is kind of dangerous to the MySQL Query Optimizer.
I have an old post (Problem with MySQL subquery). The question posed involved this query
DELETE FROM test WHERE id =
(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1);

Although the MySQL Query Parser will work with this query and accept it's syntax, the MySQL Documentation implies that some data may disappear during certain optimization phases. In your case, you are accessing just one row to get an id and then update that row. Notwithstanding, the remote possibility of accessing data from a subquery that might not return an id because it can intermittently disappear does not strike me as transaction-safe, let alone thread-safe.
Your query looks similar to the query in that old post

Non-SELECT query
SELECT subquery
Subquery uses LIMIT

This issues goes a limit beyond just being thread-safe or not. Even if it is, you should not trust MySQL's interpretation of grabbing an available seat in the middle of a subquery. You need your query to be transaction-safe.
SUGGESTION #1
My advice to you would be to rewrite this as three queries in a transaction.
Perhaps something like this:
START TRANSACTION;
SELECT id from seats WHERE taken is null limit 1 FOR UPDATE;
SELECT id INTO @available_id from seats WHERE taken is null limit 1;
UPDATE seats SET taken = 1 WHERE id = @available_id;
COMMIT;

or with two queries
START TRANSACTION;
SELECT id INTO @available_id from seats WHERE taken is null limit 1 FOR UPDATE;
UPDATE seats SET taken = 1 WHERE id = @available_id;
COMMIT;

For further info, please read MySQL Documentation on SELECT ... FOR UPDATE.
SUGGESTION #2
I would also make sure that the seats table has an index on the taken column.
If not, add this index
ALTER TABLE seats ADD INDEX (taken);

Do this in Dev/Staging Environment and test your query before doing this in Production.
GIVE IT A TRY !!!


change server date postgresql I have a postgresql db (v9.4) and its default year is 2026.
Is there any way I can modify it?
I have read about modifying the postgresql.conf file, but I have just found the timezone property.

I don't think the database have any way of changing the current date. Its date is the computer date. You should probably check the date at operating system level and, possibly, correct it.


stored procedure case statement I am building this where condition by concatenating the @whereClause variable in my stored procedure. At this point I am using the case to add additional values. if there a better way to do this?
SET @whereClause  =
    ( CASE
        -- APPType Clinical
         WHEN (@ApplicationType = 'Clinical') THEN @whereClause + ' AND vpgl.AppType = Clinical'
         -- APPType Patients
         WHEN (@ApplicationType = 'Patients') THEN @whereClause + ' AND vpgl.AppType = Patients'
          -- APPType Web
         WHEN (@ApplicationType = 'Web') THEN @whereClause + ' AND vpgl.AppType = null'
         ELSE @whereClause
       END
    )

I think this is a better, safer, more self-documenting, and more scalable approach:
IF @ApplicationType IN ('Clinical','Patients')
BEGIN
  SET @whereClause += N' AND vpgl.AppType = @ApplicationType';
END

IF @ApplicationType = 'Web'
BEGIN
  SET @whereClause += N' AND vpgl.AppType IS NULL';
END

...
SET @sql = @sql + @whereClause;

EXEC sys.sp_executesql @sql,
     N'@ApplicationType varchar(32)',
     @ApplicationType;

If you really, really want to use a CASE expression (which is not meant for control of flow), you can say:
SET @whereClause += CASE

   WHEN @ApplicationType IN ('Clinical','Patients')
   THEN N' AND vpgl.AppType = @ApplicationType'

   WHEN @ApplicationType = 'Web'
   THEN N' AND vpgl.AppType IS NULL';

   ELSE N'' END;

...
SET @sql = @sql + @whereClause;

EXEC sys.sp_executesql @sql,
     N'@ApplicationType varchar(32)',
     @ApplicationType;


EN Express, no tablespace selection under storage I have logged in to EM Express as Sys dba, need to create new tablespace, steps are to navigate to storage and select tablespace where it would navigate me to tablespace window view but however under storage I do not have tablespace selection. Does anyone can give me directions why and if I am able to configure and have tablespace access?

This happens when you log in to the root container of a CDB, because tablespaces are local to PDBs.

Configure EM Express at PDB level, for example:

connect / as sysdba
alter session set container=pdb1;
exec dbms_xdb_config.sethttpsport(5501);

Then log in to EM Express using port 5501.

PDB Tablespace is Not Showing in CDB 12c EM Express page (Doc ID 2278612.1)


What causes "attribute key cannot be found when processing" errors? I'm consistently getting variations on this error when I process the cube in my first SSAS project:

  Errors in the OLAP storage engine: The attribute key cannot be found
  when processing: Table: 'dbo_Transactions', Column: 'TransactionSK',
  Value: '68342998'; Table: 'dbo_Transactions', Column:
  'TransactionDateTimeUTC', Value: '7625037'. The attribute is
  'Transaction SK'.

The phrase "attribute key cannot be found when processing" gets a number of Google hits, and several hits on Stack Overflow too (1, 2, 3).  However, these address broken FKs, NULL values, or duplicate values (e.g., due to inconsistent case sensitivity).  None of those situations apply.

There's just one table here: dbo.Transactions.  It has fields TransactionSK and TransactionDateTimeUTC, both INT NOT NULL.  As you can guess, the TransactionDateTimeUTC field is an FK to a DateTimes table, but there are no missing records, that's enforced in ETL and I've verified it in SQL.

What else could be causing this error message?  Why does it list two fields, TransactionSK and TransactionDateTimeUTC?  The similar situations I've bumped into so far consistently only reference a single field in the error message.

The table is being updated in near-real-time, including occasional deletions, though the two fields in question are not updated.  If a record is removed from the table after SSAS scans one column and before it scans another, will that produce this error?  Spot-checking a few problem records, they were not created in the middle of process the cube.

In my case, I had 2 tables: Sales and Company.

I had one record 'ABC' in Sales, but it was supposed to be in Company. Because of that, when I processed the package I got the same error.

To fix it, I just created a row with '0' Id  in Company, and kept all the columns in that particular row as unknowns. After that, I processed the package and it worked.


PostgreSQL + query planner + amount of entities I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan.

I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL calculates the amount of entities taken from the database.

But how does the RDBMS determine which rows must be taken. For example, database calculated that the current query need 1000 entities. But which exactly? How is it determined?

You may have missed that spot in the chapter of the manual you quote:

  Note also that since ANALYZE uses random sampling while producing
  statistics, the results will change slightly after any new ANALYZE.

Emphasis mine.

If you actually mean "how many" rows in your question: the query planner has selectivity estimators for certain operators in the conditions. In combination with the statistics gathered the planner estimates the number of rows for every step.

Details of row estimation can be found the chapter of the manual you already link to in your question.

Estimations can be way off if the data distribution is very uneven within a table. That's why raising the setting for default_statistics_target (and running ANALYZE afterwards) may help in such cases.

Some special operators have special selectivity estimators. Like text search. Details in the source code in src/backend/tsearch/ts_selfuncs.c


MySQL - SELECT INTO OUTFILE, with a UNION Is it possible to issue multiple select queries whose resultsets are combined via union, into the same outfile?

What I am trying to do, but not working:

(SELECT email FROM table WHERE createdate >= date_sub(now(), interval 1 year))
UNION DISTINCT
(SELECT email FROM table WHERE createdate < date_sub(now(), interval 1 year) AND email not like '%@gmail.com')
INTO OUTFILE '/tmp/emailDump.txt' FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";

Give this a Try

SELECT * FROM
((SELECT email FROM table WHERE createdate >= date_sub(now(), interval 1 year))
UNION DISTINCT
(SELECT email FROM table WHERE createdate < date_sub(now(), interval 1 year) AND email not like '%@gmail.com')) A
INTO OUTFILE '/tmp/emailDump.txt' FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";


Connect to SQL Server 2008 R2 from Windows 98 Is it possible to connect to SQL Server 2008 R2 from a Windows 98 machine?

If it is possible, what would I use?

I have an old testing machine that currently writes to an access database.  This testing machine is probably finally going to be replaced in a year or 2.

I want to migrate the Access database to SQL Server, but as part of that effort I'd need to be able to have the Windows 98 machine write to the SQL Server.

It takes about 6 months or so to build a replacement piece of test equipment, and lately we've hit some pretty serious growth in our business, so even with all the test equipment we have, we still run behind. So it will be 2 more pieces before we can replace it.

Per a comment from David Browne, you should try using the built-in SQL Server ODBC driver.

One approach for doing this is to connect to SQL Server through MS Access with Linked Tables.


SSIS Rollback Package I have several data flow tasks in my package that depend on other data flow tasks.  For testing reasons I want to run the entire package and then roll it back after it completes.  How do I do it?

Wrap all the changes in a transaction and then abort it.

How To Use Transactions in SQL Server Integration Services SSIS


OPENROWSET shuting down SQL Server service, but only running on the server I've never seen such issue.
when i'm using SSMS from my own machine, normally conected to the server I want to run the query, I can run openrowset to select from an excel file with no  problems:
SELECT top 10 *  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\path\ecelFile.xlsx', [Planilha1$])

it retrieves me the result.
BUT if I connect with for example remote desktop or mRemote to the server, and run this query inside it, the server just shuts down:

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred
when receiving results from the server. (provider: TCP Provider,
error: 0 - The specified network name is no longer available.)

I've never seen this. even testing with an excel with 10 rows and 3 columns, the SQL server service just shut down. and running from my machine to the server via SSMS, it never fails.
What could this be? theres nothing useful in the server log.

Go check your provider (Microsoft.ACE.OLEDB.12.0) settings in SSMS.

Server Objects -> Linked Servers -> Providers ->
Microsoft.ACE.OLEDB.12.0 -> Properties

If "Allow inprocess" is ticked off, the provider code is running inside the SQL Server process. This can cause the entire process to shut down. More info in this article


SQL Server equivalent to functionality of Oracle RAC? I did some Googling and couldn't find an answer to this question more recent than a few years ago, so I thought I'd ask. Oracle's RAC feature offers load-balancing for both read and write transactions, as well as scale-out and high-availability without downtime (at least, as I understand it - we're about to deploy our first databases that use RAC, so we'll see how it goes).

Is there any SQL Server feature set (or third party component you could install on top) that delivers equivalent functionality? We've always used Windows clustering, where a failover event causes about 20-30 seconds of SQL downtime - always tolerable, but not ideal. Now, with AlwaysOn in SQL 2012, SQL Server shrinks that to about 15 seconds and adds the concept of read-only-secondary databases, but they still require that write transactions are choked through a single connection point (much improved, since many transactions are just read, but still not really load balancing), and in the case of a node failure or the need to patch, there's still downtime.

I suppose it's just more curiosity - I feel like this is the only area that SQL Server falls behind Oracle (at least among the features I've personally seen used). I wanted to see if there are any options out there to close that gap and possibly improve our own SQL Server deployment while we wait for Microsoft's equivalent feature to be added - maybe in SQL 2014/2015?

No, nothing in SQL Server can give you the same thing out of the box.

Currently, the only SQL Server technology that allows simultaneous writes at multiple nodes is Peer-to-Peer replication. Note that I did not to say "write scale-out," because it works in a way such that the entire system has the write capacity of only a single node. The introductory paragraph of that page is worded carefully to include the term "scale-out" without saying "write scale-out." Yay for marketing-speak.

From what I've read, Oracle RAC is the same in that respect. Functionally, the only thing that's missing from SQL Server in this configuration is a load-balancing solution, which is both an advantage (you can implement it any way you want) and a disadvantage (it's not in the box or supported by Microsoft) when comparing against competing products.

Conversely, Oracle RAC does not give you the same thing as SQL Server out of the box either. For example, RAC is recommended to be implemented with nodes within 100km of each other because of realtime network latency, whereas Peer-to-Peer replication has no such restriction. (I'm not saying one solution is better than the other: I'm just pointing out that they are different. The business should choose a solution that best fits their specific needs.)


Data Type to use for Exchange Rate in SSAS Multidimensional So we have a cube setup that does a many-to-many currency conversion. Until this point, the Exchange Rate measure group has used a Currency data type for the exchange rate, but I didn't realize that that's limited to 4 decimal places. With the only other Data Types in SSAS being Double and Single, what are people using for their exchange rate data types when the rates may need to be more then 4 decimal places? How many decimal places would a double be accurate to from a FX rate perspective?

The range of the Double data type is huge but is it an imprecise floating point.


covering index mystery? So has anyone else applied a covering index in MySQL and gotten improved performance but when you run explain, it shows using where, using temporary under the extra column?  Any thoughts why this is the case?  I'm happy about the performance improvement but the engineer in me wants to know why the extra column is not saying "using index".  If you need me to provide details, I will but this question is directed to the experts who have already experienced this with MySQL and know why. I would add that in my particular case, I applied a covering index to an update statement.  All the columns in the query were included in the composite (covering) index. Here is an example of exactly what I did:
Table A has the following properties:
25,000 rows.
Has the following columns: one, two, three, four, five, six, seven, eight, and nine.
My covering index query target:
update table A set one = 'somevalue', two = 'somevalue2', four = 'somevalue3', five = 'somevalue4'
where seven = 'somevalue5' and eight = 'somevalue6'
covering index on table A:
idx_covering(seven,eight,
one,two,four,five)

The order of the index matters.
Your update query uses seven and eight. There must be at the left most begining of the index. Because both are with = value based where criteria the order doesn't matter.
create index udx_78 on A (seven, eigth)

Putting the updated columns into the index doesn't help an update query. If it was aselect query, then the query would return those columns from the index.
See how mysql uses indexes.
Welcome to DBA stack exchange. Please include the explain output next time along with show create table {tablename} as it conveys the message faster.


Sync point of SQL Server backup What is the sync point of a Sql Server backup? If I have a full backup that runs from 20:12 to 20:38, when I restore the backup is it restoring to the state at 20:12? 20:38? Some other point?

If I have a full backup that runs from 20:12 to 20:38, when I restore the backup is it restoring to the state at 20:12? 20:38? Some other point?

To start, a full backup cannot be used to do point in time restores. If the backup finished at 20:38 and you restore it, it will restore all committed data uptill 20:38 and will rollback all uncommitted data uptill 20:38. For details about SQL Server full backup read technet article Understanding SQL Server backups. of course a full backup restores complete SQL Server database and to make sure restored database is consistent it also backups up few amount of transaction log so that when restored it can bring database to consistent point.