Why availability group resource goes offline in WSFC? Sometimes I find the AG Cluster role in offline state which results the AG in resolving state. It doesn't allow the application to access the databases during this time. And then after sometime it comes to online state. Why is that happening?
If one of the cluster networks is down, then the cluster role goes to offline state?

Seems it's your follow-up question of this.. Hope you got root cause of the issue using FailoverDetection tool. Perhaps the cause could be from either Internal Health Issue or system wide issues as detailed here...

Why is that happening?

As you found in error log, the AG trying to take recovery actions, as part of that either AG resources could fail-over to new primary replica as AG is determined unhealthy and will report failures on IsAlive calls, or if the quorum lost, resources goes offline.
Further reading on lease-time and health-check timeout

If one of the cluster networks is down, then the cluster role goes to offline state?

Yes, if the quorum lost.
For instance, if the WSFC formed with 2 nodes (total 2 votes), to have quorum to be healthy, always more than half of the quorum vote nodes to be alive. In this case if one node is down cluster resource may go off-line as the cluster consider to be unhealthy while there is 1 vote is not more than half of the total votes.
To maintain healthy-cluster, configure a file-share-witness (Node and File Share Majority), so that the quorum can have an additional vote from file-share to support the cluster to be healthy. If one node is down there will be 2 valid votes (1 Node + 1 File-share) out of 3.
In Summary:

If you found that the root cause is quorum lost, you may need to configure proper quorum mode.
If it's Network interface failure, consider having dedicated NIC for the cluster heart-beat communication (screenshot for your reference).
If the cause is aside from those two, you need further investigate to find bottlenecks of resource pressure in servers: some free-tools from Microsoft (Ref1, Ref2, Ref3), or third-party tools can help.

Multiple NIC for WSFC:


Running MongoDB and SQL Server on the same host- memory pressure We have a new application that is making use of both traditional MS SQL and MongoDB for data storage. We're running both instances on the same Windows 2008R2 x64 machine, with 64GB RAM.

There have been some anecdotal reports of poor performance that I've begun to investigate.

I can see that SQL Server is not consuming as much memory as I would expect, and is showing signs of memory pressure.

Eg.

AppDomain 16 (mssqlsystemresource.dbo[runtime].15) is marked for unload due to memory pressure

In contrast, MongoDB is using a huge working set, which is expected behavior based on the documentation

It relies on Windows to manage memory use.

It seems that SQL server isn't able to, or isn't requesting use any of the memory in the Mongo working set and that is impacting performance. The server is under very low load at the moment.

Page Life Expectancy seems high at: 1536
and Buffer cache hit ratio: 404002
Free list stalls/sec: 695
Free pages: 26529

Has anyone come across this issue? What it the expected behavior of SQL server in this scenario? I'm considering using the min server memory configuration option.

Set the min server memory option for SQL Server.  It will still release pages under memory pressure, unless you also allow SQL Server to lock pages in memory. The combination of setting min server memory and locking pages in memory should resolve the problem.

I've never used MongoDB, so I don't know if you can manage memory usage.  A quick search makes it seem you can't.

Assuming you're running a 64-bit server, and have more than 4 GB of RAM, you could run a 32-bit MongoDB instance, which will limit the memory to 2, 3, or 4GB depending on it's architecture.  Does a 32-bit MongoDB even exist?


Identity/Primary Key columns pattern for performance in SQL? I was discussing about database layout/design with a friend and I got some questions about performance and best practices. I'm mainly a software programmer, so I may lack "common sense" regarding databases, so if you can please explain as you would for a newbie.

I have a table that records the transactions of users and their credit card usages. It has a few millions of records per day. People in my country have a 11 digit number that is supposedly unique.

I've always designed tables with an auto-increment int column as identity.
In this case, the identity column is a composite of the user's social number + the date, which is a huge number column, and the fact that it's non-incremental seems like it's worse when doing a query with WHERE.
What's the best option in this case?

Another question is about primary keys, stores do need to look up the table and they usually send the social number or, most commonly, the user's name.
Would making the user name column as primary key enhance performance? And why is that?

On tables with a large number of rows being inserted having a ever-increasing clustered index generally improves write performance as it ensures that new records are added together "at the end" of the table. (Note that clustered indexes and primary keys are not the same thing, you can actually have a different primary key from your clustered index. Its the clustered index which matters here, not the primary key).

If you have another auto-incrementing column in your table then you could use this as your clustered index and keep your primary key the same, however your clustered index needs to be unique (so you can't use a DATETIME), which generally means an auto-incrementing column, which may as well be your primary key.

That said, you should run some performance / stress tests and try it for yourself. In the past I worked on a system which intentionally clustered on a GUID on a frequently updated table, the theory being that spreading out the writes actually reduced lock contention and improved performance (I wasn't part of that change so to be honest I was always kind of skeptical). Try it out and see which is faster with your data.

To answer your second question, looking up a user by its username should be very fast with proper indexing - its possible that clustering the the username could improve performance, however its going to be very marginal.


design table to store country name I have a php page where user can select multiple locations like USA, UK, Finland etc

At the moment i am storing all the data in mysql table comma separated.
It is working fine but when i try to get count for individual locations i am having problem to write sql query.

I want to know what is the best way to save multiple locations in db table.

Edit:
Sample data

Location
India
UK,Finland, India
USA, NZ, AUS, Spain

Please advise.

select l.location, count(*)
from posting l join
     posting p
     on find_in_set(l.location, p.location) > 0;

So if these are your requirements:

User select a few countries on a web page.
Countries selected by that user are stored in database.

You need to do the following:

create table country(
countryID number,
name varchar(50));

Set countryId as a primary key.

create table user(
userId number,
username varchar(50));

set userId as a primary key.

Since they are connecting to your page to click on these countries, we can call these "sessions", so new rule:

Every time a user connects to the web site, a session is logged.

Which then produces this table

create table session(
sessionId number,
userId number,
countryID number))

Set sessionID as a primary key.
Set userID as a Foreign key referencing table user.userId.
Set countryID as a foreign key referencing table country.countryId.

If you want to count how many countries each user has selected in a given session, you would then do the following:

select sessionId,UserId, count(*)
from session
group by sessionId,UserId

If you want to know all countries selected for every time a user went on your web page, group by UserId only.

Again, this is based on very very limited requirements.
You should be able to reuse the concept for your own purpose however.

I hope that helps.


SQL Server Backup Sizes Differential, Compressed Backup Sizes I'm trying to backup my organization's production databases on SQL Server 2008 R2 Standard.  I'm using a T-SQL script to backup with DIFFERENTIAL and COMPRESSION selected.

I'm astounded at the size of the backups relative to the mdf file sizes.  For example, we have one sharepoint database that is 307 mbs in mdf.  It's backup--with differential and compression selected--is 280 mbs.  How is this compressed or differential?

I've tried backing up using .7z and zip and it doesn't really achieve any more compression.

Am I missing something?  Why would a differential backup compressed be almost the size of the mdf?

Thanks!

Why would a differential backup compressed be almost the size of the
  mdf?

A differential backup contains all extents that have changed since the last FULL backup. Could be that a large percentage of the database has changed since the previous FULL, could be that an index maintenance task has rebuilt/re-organised tables.

Try Paul Randal's How much of the database has changed since the last full backup? script to verify what percentage of the database extents have changed.

If zip compressing a backup has as little effect as using SQL backup compression, good odds a large percentage of the space is taken up with LOB data that can't be compressed further e.g. PDF or image types.


PostgreSQL, storing encrypted data or encrypt database with pgcrypto - what is more secure? I have two machines: client and server (a database server). I want to connect from client to server and manipulate data from database (insert, update, delete, select) using dblink.

I would like to ask what is more secure:

Encrypting data on client side, send them to the server from client as ciphertext and then store in PostgreSQL database
Send data from client to server and do the encryption/decryption on server side with pgcrytpto?

Is that secure that server obtains the encryption key temporarily (for the time of one of possible operations only) from client / other server? Im wondering if there's a possibility that no matter if server obtains a key only temporarily, is there still posibility that the key will be available in PostgreSQL server's logs (or any other database server) somehow?

Yes - it is certainly possible that a key used in pgcrypto function would end up in server logs or leak somewhere else.

Postgres could be configured to log long running queries (and for example password hash functions are long running by design) or even all of them. There are utilities that show current running queries. Or an error in a query can leak it in an error message.

So, if I had a choice, I'd use cryptographic functions from a frontend language.


Write to different schema with own tablespace from another account Say I have created these two accounts:

CREATE USER u1 IDENTIFIED BY "&&u1Password"
    DEFAULT TABLESPACE u1tbs
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON u1tbs
    QUOTA 256M ON users
    QUOTA 0M ON ket;

CREATE USER u2 IDENTIFIED BY "&&u2Password"
    DEFAULT TABLESPACE u2tbs
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON u2tbs
    QUOTA 256M ON users
    QUOTA 0M ON u1tbs;

I have granted u2 full access to u1's schema. All tables in u1 are in the tablespace u1tbs as you can see. Where will data go when u2 adds data to u1's schema? If so won't it contradict with quota 0M on ...?

Edit: Question should have been on dba.stack...

Quota calculation is based on table owner, not on the user that inserts (or deletes or updates) data.

For example if u1 creates a table, u2 inserts 300MB of data, then u3 deletes 100MB of that same data and u4 updates 100MB of that data, only the quota of u1 is checked, for other users, just permissions at the table level (grant insert/delete etc) are checked.

In the example you give, there is no contradiction, u2 inserts into the table owned by u1, so only the quota of u1 is checked.

Note there is no need to specify QUOTA 0M as the default is not to allow quota on tablespaces.

http://docs.oracle.com/cd/B10500_01/server.920/a96521/users.htm#15298

You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does two things:

Users with privileges to create certain types of objects can create those objects in the specified tablespace.

Oracle limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.


SQL Server 2012 defrag job runs interactively, but not as a job - why? I have recently started using the dba_indexDefrag_sp stored procedure from SQL Fool (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) against my production databases running on SQL Server 2012 (Standard 64-bit). When I execute the procedure I use all of the default settings, the only parameter I provide is the @database, which I use to pick out the active databases which need maintenance - for example:

USE MASTER
GO
EXEC dba_indexDefrag_sp @DATABASE='MyDb'

If I run this from SQL Management Studio, on the server, the procedure executes to completion, doing its work and recording its activity, taking about an hour and a half. If, however, I create a SQL Agent job for it, using the same login account (my own) to run the job then I see the job execute successfully BUT I see that the job completed in 0 seconds and the job history indicates an issue:

Job Name        Weekly Index Defragment
Step Name       Defragment MyDb Db
Duration        00:00:00
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
Executed as user: dbo. The user does not have permission to perform this action. (Line Number: 442) [SQLSTATE 01000] (Message 0).  The step succeeded.

The problematic line 442 is an INSERT, as follows:

        INSERT INTO dbo.dba_indexDefragStatus
        (
              databaseID
            , databaseName
            , objectID
            , indexID
            , partitionNumber
            , fragmentation
            , page_count
            , range_scan_count
            , scanDate
        )
        SELECT
              ps.database_id AS 'databaseID'
            , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
            , ps.[object_id] AS 'objectID'
            , ps.index_id AS 'indexID'
            , ps.partition_number AS 'partitionNumber'
            , SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
            , SUM(ps.page_count) AS 'page_count'
            , os.range_scan_count
            , GETDATE() AS 'scanDate'
        FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL, @scanMode) AS ps
        JOIN sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL) AS os
            ON ps.database_id = os.database_id
            AND ps.[object_id] = os.[object_id]
            AND ps.index_id = os.index_id
            AND ps.partition_number = os.partition_number
        WHERE avg_fragmentation_in_percent >= @minFragmentation
            AND ps.index_id > 0 -- ignore heaps
            AND ps.page_count > @minPageCount
            AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
        GROUP BY ps.database_id
            , QUOTENAME(DB_NAME(ps.database_id))
            , ps.[object_id]
            , ps.index_id
            , ps.partition_number
            , os.range_scan_count
        OPTION (MAXDOP 2);

I don't understand why it fails as a SQL Agent job yet succeeds 'interactively'. Is there something about that INSERT statement which needs special attention? I would appreciate some help to allow me to resolve this problem. Thanks.

Ok, I found a resolution by doing the following:

I moved dba_indexDefrag_sp and its associated objects into a 'HouseKeeper' database, rather than putting them in the Master database. (I note Michelle Ufford recommends this, saying: 'It’s up to you where you create it. You could technically create it in the MASTER database, but I recommend creating a utility database for your DBA administrative tasks.')
I created a new 'SQLJobRunner' Windows login, then added it to SQL Server, setting it as db_owner on the dbs I'm defragmenting. I also applied to same to the HouseKeeper db, to give the login full access to that database.
I changed the defrag SQL Agent job to run using the 'SQLJobRunner' login.

and then it worked, which deals with my issue. It is notable that I have the same security setup against my own login and that still doesn't work. Odd... but that isn't really a problem for me now - I don't know why it that doesn't work though.

Note that I'm aware that giving the login db_owner is a bit of a sledgehammer approach, but now that it works I'm able to make the security a bit more fine-grained.

Thanks!


How could i search between two string type date Consider these two tables. I use persian dates, so I have to declare startdate and enddate as a string(nvarchar(10)).

crt table:

id title startdate enddate
--------------------------
1  xyz   1395/01/01  1395/12/29
2  xyy   1394/12/01  1395/05/05
3  cvb   1392/02/03  1394/02/03

season table:

id title startdate enddate
--------------------------
1  spring95 1395/01/01  1395/03/31

I want to write a query that gives me crt records that are valid in a season. IE crt.startdate<=season.startdate and crt.enddate>=season.enddate.

How do I do that?

If you encounter performance problems you could store the dates as integers instead of varchar. 1397-12-09 becomes 13971209, all the numbers remain in sequence so you can still carry out BETWEEN queries. Then convert your results to date format at the final point of presentation.


Mail Alert when threshold of 10000 pendingcmds are breached I currently have the below running to monitor replication and send a mail when the threshold of 10000 pendingcmds are breached, however the alert mail is generated every 5 minutes regardless of the threshold. I need assistance to re-code the threshold part as this is not delivering the expected results.

        DECLARE @RESULT nvarchar(100)
        DECLARE @cmd varchar(100)

        SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds')
        EXEC sys.sp_executesql @RESULT
        PRINT @RESULT
        IF @RESULT > '10000'

Code used to generate alert

        USE [TestDB]

        create table #pendingcmds
        (pendingcmdcount int,
        estimatedprocesstime int)

        Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
        select * FROM OPENROWSET('SQLOLEDB',
        'Server=Server\Instance;Trusted_Connection=yes;',
        'set fmtonly off;
        exec distribution..sp_replmonitorsubscriptionpendingcmds
        @publisher=''Server\Instance'',
        @publisher_db=''testproduction'',
        @publication=''Summary_Inventory_TestProduction'',
        @subscriber=''Server'',
        @subscriber_db=''TestReplAzure'',
        @subscription_type=0')

        SELECT * FROM #pendingcmds

        --DECLARE @RESULT nvarchar(100)
        ----DECLARE @cmd varchar(100)
        --
        --SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds')
        --EXEC sys.sp_executesql @RESULT
        --PRINT @RESULT
        --IF @RESULT > '10000'
        --
        --BEGIN

         /*************************************************************/
         /****************** HTML Preparation *************************/
         /*************************************************************/

        DECLARE @HTML  VARCHAR(MAX),
                @table VARCHAR(MAX)

        SET @HTML = --HTML layout--
                        '' +
                        '
Publisher to Subscriber has exceeded threshold
' +
                        '' +
                        '' +
                        '
' +
                        '
'+
                        'Pending CMD Count	' +
                        'Estimated Process Time
';

        SELECT @table = CONVERT(nvarchar(max) ,
            (SELECT
             td = CAST(pendingcmdcount as varchar(30)), '',
             td = CAST(estimatedprocesstime as varchar(30)), ''
             FROM #pendingcmds
             FOR XML PATH(N'tr'), TYPE));

        SET @HTML = @HTML + @table + CHAR(10) +
                     N'
';

        SET @HTML = @HTML + '

End of Report
';

        PRINT @HTML

        Declare @MailSubject varchar(100)
        SET @MailSubject = 'Alert - Publisher to Subscriber has exceeded threshold!!! - ' + DATENAME(weekday, getdate())

        IF LEN(@HTML) > 10

            EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'tester@test.com',
            @body_format =  'HTML',
            @body= @HTML,
            @subject = @MailSubject,
            @profile_name = 'MailRelay'

        DROP TABLE #pendingcmds
        --END

        --ELSE
        --DROP TABLE #pendingcmds
        --
        --END

The following suggestions are not fully tested.

Immediately after

Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
select * FROM OPENROWSET('SQLOLEDB',
'Server=Server\Instance;Trusted_Connection=yes;',
'set fmtonly off;
exec distribution..sp_replmonitorsubscriptionpendingcmds
@publisher=''Server\Instance'',
@publisher_db=''testproduction'',
@publication=''Summary_Inventory_TestProduction'',
@subscriber=''Server'',
@subscriber_db=''TestReplAzure'',
@subscription_type=0')

I would delete any rows where your threshold was not met.

DELETE FROM #pendingcmds WHERE pendingcmdcount <= 10000

Then, I would change your test for whether to send the email or not

From

IF LEN(@HTML) > 10

            EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'tester@test.com',
            @body_format =  'HTML',
            @body= @HTML,
            @subject = @MailSubject,
            @profile_name = 'MailRelay'

TO:

IF (SELECT COUNT(*) FROM #pendingcmds) > 0
    EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'tester@test.com',
    @body_format =  'HTML',
    @body= @HTML,
    @subject = @MailSubject,
    @profile_name = 'MailRelay'


Delete files from disk, with a job, respecting some rules ( SQL Server 2008 ) We have An application that our customers use to download and upload files.

I need to delete these files from disk after 30 days, of if it was downloded, in 7 days.

How can i Use the script below, in a powershell, to do this task?

select ArquivoServidor from MyTable
  where DATEDIFF(day,LogDownload,GETDATE())>= 7
    or DATEDIFF(day,LogUpload,GETDATE())>= 30

( i'm not in the server where files are )

Thank you very much.

Edit1:

I can use this ( After a lot of tests, it's working ):

    create table #temp
    ( ArquivoServidor varchar (max))
go
insert into #temp
select 'xp_cmdshell '+ ''''+'del '+ArquivoServidor+''''+';' from MyTable
  --set Arquivo = NULL
  where DATEDIFF(day,LogDownload,GETDATE())>= 7
    or DATEDIFF(day,LogUpload,GETDATE())>= 30

    select * from #temp where ArquivoServidor is not null
        drop table #temp

it will show me something like this:

  xp_cmdshell 'del \remote_server\folder\folder\file.txt'

I'm thinking in something like execute sql, But Im having problems with files with spaces in name ( jhon vacation.mp3 ) for example.

Any Ideas?

Edit2:

Query done, now I need to fix the Spaces in files name.

declare @sql varchar(max)
set @sql =

'select ''exec xp_cmdshell ''
    + ''''
    +''del ''
    +ArquivoServidor
    +''''
    +'';''
    + CHAR(13)
    + CHAR(10)
from Mytable
  --set Arquivo = NULL
where
    ArquivoServidor is not null
        and DATEDIFF(day,LogDownload,GETDATE())>= 7
    or DATEDIFF(day,LogUpload,GETDATE())>= 30
        and  ArquivoServidor is not null'''

$Computer = Read-Host "Input Computer Name (remote PC)"
$Path = Read-Host "Path of temp folder"
If((Test-Path $Path) -eq $True) {
    Invoke-Command -ComputerName $Computer -ScriptBlock {
        $File_Counts = (Get-ChildItem -Path $Path | Measure-Object).Count
        $FPath = $Path + "\*.*"
        Remove-Item -Path $FPath }
    $OutPut_File = "RemoteDeletes_log.txt"
    $File_Counts.ToString() + " files have been deleted from this $Path folder on remote $Computer PC" >> $OutPut_File
    Invoke-Item $OutPut_File
} else {
    Write-Host "Folder isn't exist, please input correct path"}

It doesn't follow the rules that I need.

Try something like this:
-Install SQL 2012 feature pack to get invoke-sqlcmd or change it to use sqlcmd.

-You can just pass in the raw TSQL but I would turn it into a stored procedure and just pass that in. Something like:

CREATE PROCEDURE [GetDeleteDate]
AS
select ArquivoServidor from tblExchange
  where DATEDIFF(day,LogDownload,GETDATE())>= 7
    or DATEDIFF(day,LogUpload,GETDATE())>= 30

####Loop through results and execute######
##Define the return as an array by putting it in @()
$lol = @(Invoke-Sqlcmd -Server $ServerInstance -Query "exec [YourAdminDatabase].[dbo].[GetDeleteDate]" | select-object -Expand Column1)

This will now create an array with the return item as 1 value.  You can now use this array in a loop in powershell to perform whatever work you need done and add error handling.  If you need to then execute a new SQL query and pass in the variable you'd do something like this:

#$lol.Count  ##this is to test how many items are in your array.
foreach ($row in $lol)
{
#Write-Output $row
Invoke-Sqlcmd -Server $ServerInstance -Query $row -verbose
}

Or if you want to pass the variable as a item to delete in a loop in powershell, feel free to do that too.  The $lol variable is an array with all the data requested.  At that point you can do whatever you need to do with it in PS using the loop above.  It could be a simple del, remove-childitem, etc. Whatever you're comfortable with.  Let us know if you need help writing the delete portion but in the comments you mentioned you're familiar with PS so I figured you could use what you like best.  If you have spaces in your file path make sure to encapsulate it within single or double quotes.


What is an "Obfuscation key" in SQL Server? Jokes aside, what is an "Obfuscation key"? I'm just going off the docs.

sys.sysclsobjs Exists in every database. Contains a row for each classified entity that shares the same common properties that include the following:
...
Obfuscation key.

This seems to involve

Content/binn/sqllang.dll
Content/binn/sqlmin.dll

I would like to know what the key does and what is being obfuscated, and if that's available for the user.

I would guess they mean rows created for symmetric keys.

create symmetric key KeyTest with
  algorithm = aes_128,
  identity_value = '123456'
  encryption by password = '123456!Q';

go

-- Needs a DAC connection
select *
from sys.sysclsobjs
where Name = 'KeyTest'

Result:

class id    name      status  type intprop  created                 modified
----- ----- --------- ------- ---- -------- ----------------------- -----------------------
56    259   KeyTest   0       A1   128      2017-12-22 08:43:33.170 2017-12-22 08:43:33.170


How to rank the product higher and the accessories lower? (MySql fulltext search) I hope this is not a silly question.

I have a table containg the products of an online store. I am using fulltext search in boolean mode.

People search for, let us say, Apple iPhone 11.

I don't want "Cleaning kit for Apple iPhone 11" to come first and the product itself "Apple iPhone 11" to come second.

You just need to link back to the Product table and sort by some other criteria on there.  You can ask the Full Text engine to rank the results, but that really just ranks how closely the result matches their search, which is not what you are looking for.  I have a sample here using MS SQL Server.

Since you didn't provide a schema, I put together something that would show what I mean.

DROP TABLE IF EXISTS dbo.Product

CREATE TABLE dbo.Product
    (
    ProductID INT NOT NULL IDENTITY(1,1)
    , ProductName NVARCHAR(100) NOT NULL
    , SearchPrecedence INT NULL
    , CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID)
    )

GO

BEGIN TRY
    CREATE FULLTEXT CATALOG FTC_FullText AS DEFAULT
END TRY
BEGIN CATCH
    PRINT 'Full Text Catalog already exists.'
END CATCH

CREATE FULLTEXT INDEX ON dbo.Product(ProductName) KEY INDEX PK_Product;

GO

INSERT INTO dbo.Product
(ProductName, SearchPrecedence)
VALUES ('Iphone', 1)
    , ('Iphone Clean', 100)

GO

/** NOTE: need to wait a bit here so that we give the engine time to build the full text results.
    This code works, but Full Text is not synchronous with writes
    **/

WAITFOR DELAY '00:00:30.000'

DECLARE @Search NVARCHAR(100) = '"*Iphone*"'

SELECT P.ProductID
    , P.ProductName
    , P.SearchPrecedence
FROM dbo.Product AS P
WHERE P.ProductID IN (SELECT PFT.[Key] FROM CONTAINSTABLE(dbo.Product, *, @Search) AS PFT)
ORDER BY P.SearchPrecedence


Why does STRAIGHT_JOIN consume more CPU? Why does STRAIGHT_JOIN consume more CPU than a regular JOIN? Do you have any idea?

When I use STRAIGHT_JOIN on one of my queries, it speeds up the query like from 12 seconds to 3 seconds. However, it consumes so much CPU. Might this be about server configuration or something else?

You might want to check the code after this comment / Topic Ids are OK, Getting Devices... /
Before this line there are some code about filling topic_ids to a temp table.

Here is the query:

CREATE PROCEDURE `DevicesByTopic`(IN platform TINYINT, IN application TINYINT, IN topicList TEXT, IN page_no MEDIUMINT UNSIGNED)
BEGIN

    DECLARE m_index INT DEFAULT 0;
    DECLARE m_topic VARCHAR(255);
    DECLARE m_topic_id BIGINT UNSIGNED DEFAULT NULL;
    DECLARE m_session_id VARCHAR(40) CHARSET utf8 COLLATE utf8_turkish_ci;

    -- Session Id
    SET m_session_id = replace(uuid(), '-', '');

    -- Temp table
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_topics(
        topic_slug VARCHAR(100) COLLATE utf8_turkish_ci
        ,topic_id BIGINT UNSIGNED
        ,session_id VARCHAR(40) COLLATE utf8_turkish_ci
        ,INDEX idx_tmp_topic_session_id (session_id)
        ,INDEX idx_tmp_topic_id (topic_id)
    ) CHARSET=utf8 COLLATE=utf8_turkish_ci;

    -- Filling topics in a loop
    loop_topics: LOOP
        SET m_index = m_index + 1;

        SET m_topic_id = NULL;
        SET m_topic= SPLIT_STR(topicList,',', m_index);

        IF m_topic = '' THEN
            LEAVE loop_topics;
        END IF;

        SELECT t.topic_id INTO m_topic_id FROM topic AS t WHERE t.application = application AND (t.slug_hashed = UNHEX(MD5(m_topic)) AND t.slug = m_topic) LIMIT 1;

        -- Fill temp table
        IF m_topic_id IS NOT NULL AND m_topic_id > 0 THEN
            INSERT INTO tmp_topics
                (topic_slug, topic_id, session_id)
            VALUES
                (m_topic, m_topic_id, m_session_id);
        END IF;

    END LOOP loop_topics;

    /* Topic Ids are OK, Getting Devices... */

  SELECT
    dr.device_id, dr.platform, dr.application, dr.unique_device_id, dr.amazon_arn
  FROM
    device AS dr
  INNER JOIN (
            SELECT STRAIGHT_JOIN
            DISTINCT
                d.device_id
            FROM
                device AS d
            INNER JOIN
                device_user AS du ON du.device_id = d.device_id
            INNER JOIN
                topic_device_user AS tdu ON tdu.device_user_id = du.device_user_id
            INNER JOIN
                tmp_topics AS tmp_t ON tmp_t.topic_id = tdu.topic_id
            WHERE
                ((platform IS NULL OR d.platform = platform) AND d.application = application)
                AND d.page_no = page_no
        AND d.status = 1
                AND du.status = 1
                AND tmp_t.session_id = m_session_id COLLATE utf8_turkish_ci
  ) dFiltered ON dFiltered.device_id = dr.device_id
  WHERE
        ((platform IS NULL OR dr.platform = platform) AND dr.application = application)
        AND dr.page_no = page_no
        AND dr.status = 1;

    -- Delete rows fFill temp table
    DELETE FROM tmp_topics WHERE session_id = m_session_id;

END;

With the STRAIGHT_JOIN this query takes about 3 seconds but consumes so much CPU like 90%, but if i remove the keyword STRAIGHT_JOIN, it takes 12 seconds but consume 12% CPU.

MySQL 5.6.19a - innodb

What might be the reason?

Best regards.

According to the MySQL Documentation on JOIN Syntax

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

The comment posted suggested using EXPLAIN on both versions of the query. Based on this suggestion and the join optimizer's current behavioral settings, the output of the two EXPLAIN plans should be noticeably different. Being more specific, the order of the tables in EXPLAIN plan of the STRAIGHT_JOIN will have tables being accessed in a different order from the other EXPLAIN plan of the regular join.
OPTIONAL SUGGESTIONS
You could change the optimizer switches to change its decision making process.
You could run one of the following switch settings with your regular JOIN:
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=off'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=on'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=off'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=on'; SELECT ... JOIN

Then, you could run one of the following switch settings with your STRAIGHT_JOIN:
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=off'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=on'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=off'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=on'; SELECT STRAIGHT JOIN

You could measure CPU and query performance for each of these eight(8) JOINs.
GIVE IT A TRY !!!
BTW other have good answers on STRAIGHT_JOIN

Apr 28, 2011 : Why does STRAIGHT_JOIN so drastically improve this query, and what does it mean when it is written after the SELECT keyword?
Feb 04, 2009 : When to use STRAIGHT_JOIN with MySQL


Replica Set not getting created I have database which is set up in data folder of mongodb. For accessing this database I usually run the below command

    mongod --dbpath "C:\program files\mongodb\data" --port 27017

Now I am trying to replicate database. For creating Primary database I run command

    mongod --port 27017 --dbpath "C:\Program Files\MongoDB\data" --replSet replicaSetMGL --smallfiles --oplogSize 128

I have created two empty folders in mongodb folder as replicaSet1 and replicaSet2. Now for creating replica set for port no. 27018, I executed below command in another terminal.

    mongod --port 27018 --dbpath "C:\Program Files\MongoDB\replicaSet1" --replSet replicaSetMGL --smallfiles --oplogSize 128

This is not working (throwing error):

C:\Users\well come>mongod --port 27018 --dbpath "C:\Program Files\MongoDB\replic
aSet1" --replSet replicaSetMGL --smallfiles --oplogSize 128
2017-06-12T17:25:33.164+0530 I CONTROL  [initandlisten] MongoDB starting : pid=6
936 port=27018 dbpath=C:\Program Files\MongoDB\replicaSet1 64-bit host=hp
2017-06-12T17:25:33.164+0530 I CONTROL  [initandlisten] targetMinOS: Windows 7/W
indows Server 2008 R2
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] db version v3.4.4
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] git version: 88839051587
4a9debd1b6c5d36559ca86b44babd
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] OpenSSL version: OpenSSL
 1.0.1u-fips  22 Sep 2016
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] allocator: tcmalloc
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] modules: none
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] build environment:
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten]     distmod: 2008plus-ss
l
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten]     distarch: x86_64
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten]     target_arch: x86_64
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] options: { net: { port:
27018 }, replication: { oplogSizeMB: 128, replSet: "replicaSetMGL" }, storage: {
 dbPath: "C:\Program Files\MongoDB\replicaSet1", mmapv1: { smallFiles: true } }
}
2017-06-12T17:25:33.166+0530 I -        [initandlisten] Detected data files in C
:\Program Files\MongoDB\replicaSet1 created by the 'wiredTiger' storage engine,
so setting the active storage engine to 'wiredTiger'.
2017-06-12T17:25:33.167+0530 I STORAGE  [initandlisten] wiredtiger_open config:
create,cache_size=3557M,session_max=20000,eviction=(threads_min=4,threads_max=4)
,config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal
,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,lo
g_size=2GB),statistics_log=(wait=0),
2017-06-12T17:25:34.184+0530 W STORAGE  [initandlisten] Detected configuration f
or non-active storage engine mmapv1 when current storage engine is wiredTiger
2017-06-12T17:25:34.185+0530 I CONTROL  [initandlisten]
2017-06-12T17:25:34.185+0530 I CONTROL  [initandlisten] ** WARNING: Access contr
ol is not enabled for the database.
2017-06-12T17:25:34.185+0530 I CONTROL  [initandlisten] **          Read and wri
te access to data and configuration is unrestricted.
2017-06-12T17:25:34.186+0530 I CONTROL  [initandlisten]
2017-06-12T17:25:34.308+0530 I FTDC     [initandlisten] Initializing full-time d
iagnostic data capture with directory 'C:/Program Files/MongoDB/replicaSet1/diag
nostic.data'
2017-06-12T17:25:34.447+0530 I REPL     [initandlisten] Did not find local voted
 for document at startup.
2017-06-12T17:25:34.448+0530 I REPL     [initandlisten] Did not find local repli
ca set configuration document at startup;  NoMatchingDocument: Did not find repl
ica set configuration document in local.system.replset
2017-06-12T17:25:34.451+0530 I NETWORK  [thread1] waiting for connections on por
t 27018

OK.. It's look like that you have managed to mix things. So, you have working mongodb at "C:\program files\mongodb\data" and now you want to convert that mongodb to three node replica set?!?
First after you have start that mongod process with --replSet replicaSetMGL parameter. Log in (with mongo -program) and give command rs.initiate().

Then remove all files from directory "C:\Program Files\MongoDB\replicaSet1" (because I can see from log file that there is mixed db files; mmapv1 and wiredTiger). Start that node with those parameters.

Go back to first nodes mongo and give command rs.add("localhost:27018") to add that node to replica set. With command rs.status() you can see your replica sets status.

Repeat those steps to add third node to RS.


Connect SQL Server 2016 SSDT with TFS I've just upgraded the SQL client tools on my PC to SQL Server 2016.

I no longer have a "Team" menu option in SSDT.

Under Tools > Options > Source control there is a drop down box for Source Control plug-in. But there are no options.

In my previous version SQL 2012 SSDT (VS 2010) I have the option of selecting "Visual Studio Team Foundation Server"

How do I get SSDT to connect to TFS?

The links and information are confusing but we did find a solution.

The short version is you need to install Visual Studio 2015. I used the free Visual Studio 2015 Community Edition. That includes the components needed to run Team Explorer.

Once we installed VS the Team menu is now visible in SQL Server 2016 SSDT. You can then add and configure TFS as before.


What exactly can SQL Server 2014 execute in batch mode? When a columnstore index is being used in a query SQL Server is able to use batch mode. Documentation is thin on what can run in batch mode and what can't. Please look at the following (motivating) query plan where a surprising number of things executes in batch mode (green):

(This is an estimated plan. I used the actual plan to verify that the actual execution mode is indeed batch.)

Note, that only the build side of T1 uses a columnstore index. All probe inputs (T2 and T3) are rowstores. Their data seems to transition into batch mode. I always thought that batch mode is being used for the data stream that runs through the probe side only.

It seems that data can transition into batch mode even if it does not originate from a columnstore index. That poses the question: Why doesn't SQL Server use batch mode for rowstore-only queries as well? Could be beneficial for some of them. Is using a columnstore index a formal requirement that is necessary to make SQL Server consider batch mode? Could we maybe add a zero row dummy table with a columnstore index to induce batch mode and realize performance gains?

What exactly can run in batch mode as of SQL Server 2014?

What exactly can run in batch mode as of SQL Server 2014?

SQL Server 2014 adds the following to the original list of batch mode operators:

Hash Outer join (including full join)
Hash Semi Join
Hash Anti Semi Join
Union All (Concatenation only)
Scalar hash aggregate (no group by)
Batch Hash Table Build removed

  It seems that data can transition into batch mode even if it does not originate from a columnstore index.

SQL Server 2012 was very limited in its use of batch operators. Batch mode plans had a fixed shape, relied on heuristics, and could not restart batch mode once a transition to row-mode processing had been made.

SQL Server 2014 adds the execution mode (batch or row) to the query optimizer's general property framework, meaning it can consider transitioning into and out of batch mode at any point in the plan. Transitions are implemented by invisible execution mode adapters in the plan. These adapters have a cost associated with them to limit the number of transitions introduced during optimization. This new flexible model is known as Mixed Mode Execution.

The execution mode adapters can be seen in the optimizer's output (though sadly not in user-visible execution plans) with undocumented TF 8607. For example, the following was captured for a query counting rows in a row store:

  Is using a columnstore index a formal requirement that is necessary to make SQL Server consider batch mode?

It is today, yes. One possible reason for this restriction is that it naturally constrains batch mode processing to Enterprise Edition.

  Could we maybe add a zero row dummy table with a columnstore index to induce batch mode?

Yes, this works. I have also seen people cross-joining with a single-row clustered columnstore index for just this reason. The suggestion you made in the comments to left join to a dummy columnstore table on false is terrific.

-- Demo the technique (no performance advantage in this case)
--
-- Row mode everywhere
SELECT COUNT_BIG(*) FROM dbo.FactOnlineSales AS FOS;
GO
-- Dummy columnstore table
CREATE TABLE dbo.Dummy (c1 int NULL);
CREATE CLUSTERED COLUMNSTORE INDEX c ON dbo.Dummy;
GO
-- Batch mode for the partial aggregate
SELECT COUNT_BIG(*)
FROM dbo.FactOnlineSales AS FOS
LEFT OUTER JOIN dbo.Dummy AS D ON 0 = 1;

Plan with dummy left outer join:

  Documentation is thin

True.

The best official sources of information are Columnstore Indexes Described and SQL Server Columnstore Performance Tuning.

SQL Server MVP Niko Neugebauer has a terrific series on columnstore in general here.

There are some good technical details about the 2014 changes in the Microsoft Research paper, Enhancements to SQL Server Column Stores (pdf) though this is not official product documentation.


Has dacpac/sqlpackage feature awareness We are deploying our database model (vs db project) to several customers via a setup routine. Internally a dacpac process is responsible for the upgrade process, if an already existing installation will be upgraded.
Our customers uses SQL Server 2012 up to SQL Server 2017, but our codebase has to maintain the least common feature. eg. STRING_SPLIT isn't useable, because it was introduced in SQL Server 2016.

My idea is to have target specific T-SQL funtions which are deployed depending on their target system. If target system equals SQL Server 2016, than STRING_SPLITT, else self made solution is taken.

any ideas?

As per MSDN BOL Here In general, DAC tools are capable of reading DACPAC files generated by DAC tools from previous SQL Server versions, and can also deploy DACPAC’s to previous versions of SQL Server. However, DAC tools from earlier versions cannot read DACPAC files generated by DAC tools from later versions. Specifically:

1. DAC operations were introduced in SQL Server 2008 R2. In addition to
    SQL Server 2008 R2 databases, the tools support generation of DACPAC
    files from SQL Server 2008, SQL Server 2005 and SQL Server 2000
    databases.

2. In addition to SQL 2016 databases, the tools shipped with SQL Server
        2016 can read DACPAC files generated by DAC tools shipped with SQL
        Server 2008 R2 or SQL Server 2012. This includes databases from SQL
        Server 2014, 2012, 2008 R2, 2008, and 2005, but not SQL Server 2000.

3. DAC tools from SQL Server 2008 R2 cannot read DACPAC files generated
        by tools from SQL Server 2012 or SQL Server 2017.

Hope it will help out to you.


Pgpool2 doesn't use the slave for load balancing I've enabled streaming replication in my postgres 9.5.10 installed from ubuntu xenial repo. I want to enable load balance for pgpool-2 (3.7.0 amefuriboshi) installed from sources. So I have this pgpool.conf: https://pastebin.com/qWWgejQN

As you can see, I set both nodes, turned off replication, turned on master/slave mode and set it to stream. And also I enabled memcached caching.

Now I have very big problem that pgpool doesn't want to use slave for balancing:

postgres=# show pool_nodes;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 0          | true              | 0
 1       | host2          | 5433 | unused | 0.500000  | standby | 0          | false             | 0
(2 rows)

What am I do wrong?

I found this page http://lists.pgfoundry.org/pipermail/pgpool-general/2010-May/002711.html

They say that I need to remove file pgpool_status. I did it and pgpool works now with two nodes! Seems the file was created when I had not the second node and after pgpool just trusted this file all time. So removing this file solved the problem.


Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery? I have a query that gets about 80k rows from said table with about 3m.  I only need to paginate ten rows at a time, but with ORDER BY (a+b) DESC (a and b are integer type columns.)

Right now my query's taking about 4 seconds to do this.  If I ORDER BY the primary key instead, it avoids seq scans and takes 200ms, which I would be happy with.

So I created an index on (a+b) DESC.

My thinking there is that the ORDER BY and primary key both have indexes (as do all the things in the WHERE clause--and those indexes are being used just fine), so the subquery should be able to accomplish everything it needs to without a seq scan, give up 10 indexed primary keys to the outer query, which, if it needs a seq scan because of the *, only needs to find ten rows.

It didn't make a difference, anyway.

Unfortunately, I can't share full table details, but I can say that my table has 21 columns, with some text, two integer, several timestamps and even one ts_vector. Aside from the expression one there are 4 indexes: the PK (text) is btree, I have another btree text, a btree timestamp, and a gin index on the tsvector.

Further: I can also order by the indexed timestamp and the non-primary-key indexed text, and just like the primary key, this reduces query time to 200ms thanks to index usage.  It's just my expression index that doesn't seem to work this way in the subquery context.

Other options, none of which seem great:

Add a computed column for (a+b) so the index is just on one column.  If it works for ORDER BY on the pk it should work for that column, right?
Cheat by somehow guesstimating a happy limiter on (a+b) due to its distribution.  I'm not sure how I would always know because the query inputs are dynamic, but in this case, if I add a simple AND (a+b) > 1000000, I still get ten rows back but it only takes 200ms--and uses my expression index for the WHERE clause where the seq scan would normally be.  (About the not knowing, I could start doing some kind of binary-search-esque thing, trying high numbers and reducing by half until I get at least 10 rows back and then calling it a day.  If I get it within less than 20 tries, I'm still ahead.  But this is beyond kludgy...)

Any ideas?  How can I get it to use my expression index and avoid a seq scan?

The reason I don't understand the above is that the docs (going back to 9.4 at least) explicitly put it this way:

  Index expressions are relatively expensive to maintain, because the derived expression(s) must be computed for each row upon insertion and whenever it is updated. However, the index expressions are not recomputed during an indexed search, since they are already stored in the index.

So how on earth is other_column any less expensive to ORDER BY than a+b when both are indexed?

Regarding query plans, let's try to get to the heart of it.  I've posted a follow-up question.

Query optimizers generally work by figuring out how to limit the number of possible rows for the query as quickly as possible, based on what it knows about the tables and their data, and the options it has on how to get to that data.

One of the factors it has to consider is how many pages it will have to load to get to the data you need. Reading pages from disk is one of the most expensive things a DBMS does, so if it can deal with fewer pages, that's generally going to be a good thing.

Your query doesn't normally include any check on a+b. So, that index will not help with the selection of the data, and there's no reason to load it in.

Once the rows to use have been identified, is it useful to use the (a+b) DESC index to sort them? I can only see two ways to do this:

Use the index backwards: instead of identifying rows based on values for a+b, we look through the index starting at the max value for a+b, and check each associated row to see if it's one of our candidates, stopping when we get ten rows. This requires that we load an unknown number or pages of the index, and then look up rows to see if they exist in the candidate data. Barring situations where we know almost all rows are candidates, I can't see this as being fast.
Calculate the a+b values for the candidate rows, and then use the index to sort: Since one of the ways to match the index against the values of a+b from the table is to sort the calculated values of a+b from the table, it would make more sense to do exactly what it sounds like the engine did: calculate a+b from the candidate rows and sort that directly, without bothering to load the index pages as well.

When you add a search criteria that involves a+b, the picture changes completely. Instead of having to use the index backwards (finding a+b values based on the rows), it actually needs to find rows based on a+b values. If it can keep the data in the sorted order while checking the other criteria, then yes, it would use that sorted order, and not have to perform a separate step to sort the data.

You should also note that, if your primary key is a clustered index, then the data is actually stored based on that value; that has an impact on how easy it is to sort by that.

Also note that the entire set of returned data has to be sorted by a+b to find the top ten a+b values.

If this query is critical, then you may be able to use variation on a covering index. Normally, this is an index that includes all the columns used in the query (whether in the SELECT list, in table joins in the FROM clause, in the WHERE clause, the ORDER BY - all the columns used).

Note that every column in the query does not necessarily have to be used as a part of the index itself. The actual index should cover enough columns to be useful in narrowing down the rows to be returned. Additional columns are generally included at the "leaf" level - they aren't used to sort the rows from the table, but the data is available in the index.

If all the columns in the query that come from this table are part of the index, or included at the leaf level, then there's no need to actually refer to the table; the covering index has all the data needed, and will normally be smaller than the full table, so we can get everything we need from the index, and load fewer pages.

It sounds like you're bringing back all the columns from the table, so a true covering index won't necessarily help much. However, an index that covers the columns except for those only in the SELECT list might work for you. By this, I mean all the columns from this table used in JOIN clauses, the WHERE clause, any GROUP BY or HAVING clauses, and (in this case) even those in the ORDER BY clause.

Using such an index, the engine could find all the rows it needs to return without referring to the actual table. Since we've included the columns from the ORDER BY clause, it should even be able to determine the 10 rows that need to be returned (thanks to your LIMIT clause). With only ten rows involved, the engine might find it faster to identify the ten rows using the index, and then to look those 10 rows up in the actual table to get the full set of values to be returned.

When a number of columns that aren't in any index it's using are involved in the identification of the actual rows to return, the engine is more likely to rely on a table scan for at least part of the query. It has to retrieve the final result set from the table, so if it thinks it will have to load more pages if it uses the index and then looks up the rows in the table, instead of going straight to the table, it might well choose to do that.

Again, please remember this is based on almost no specific information. This will work best if there are many columns (or even a few very large columns) that aren't needed in the query to identify the rows to be returned, but are only part of the ultimate SELECT list.

With respect to this statement:

  However, the index expressions are not recomputed during an indexed search, since they are already stored in the index.

I think you may be generalizing it a bit too much. Without the expression index, to search for all rows where a+b >= 12000000, a+b has to be calculated for each row. By having this value indexed, we can easily identify rows where the value of a+b meets our criteria. However, this does not mean that the value of a+b is also pre-computed at the table level. If there's no way to use the index to identify our rows, then (as discussed early in this answer) the index won't be used at all. And, if the index is not used, then a+b does still have to be calculated. Indexes are designed to find specific rows based on the indexed values, not to find the indexed values based on specific rows.


sp_setapprole taking 100 - 200 ms SQL Server 2008 SP1 here.

We have a machine where sp_setapprole calls take more than 100ms and they are done many times per user operation on an application.

The developer says that in his setup, sp_setapprole calls take less than 1ms. The machine specs are comparable (I have 12GB RAM, 6 cores, he has 16GB and 8 cores).

This problem makes the application performance to be unacceptable.

So, two actual questions:

1.- What are possible causes (and solutions) of slow sp_setapprole?

2.- How would you debug the problem?

I used HDTune to test the disks and they had an abysmal 10MB/s read speed. Diagnostics show a faulty RAID controller.


How to import Sybase .db file into SQL Server database? I've been given a Sybase .db file by a client and I need to import that into a new SQL Server database so that I may see the data as well as the tables and their relationships, any ideas on how that might be done shall be really appreciated :)

Since both Sybase and SQLServer share the same SQL dialect (T-SQL), I'd generate the DDL statements from the existing database (it will require a Sybase server) to build the SQL Server database and the an SSIS task to copy data between databases.

I don't know if it is possible to SQLServer to just open a Sybase file, but, albeit the same origins, I don't believe there's any kind of binary compatibility between inner formats of this two DBMS.


How can I improve this query I have the following table:

CREATE TABLE `twitter_relationships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  UNIQUE KEY `source_and_target` (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index` (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (user_id)
PARTITIONS 1000 */

This table is pretty big, ~150M records.

And I have the following query:

SELECT target_twitter_id
 FROM `twitter_relationships`
WHERE (`twitter_relationships`.`relationship_status` = ?
   AND `twitter_relationships`.`user_id` = ?
   AND `twitter_relationships`.`source_twitter_id` = ?)
LIMIT ?, ?

Here is the explain for this query:

           id: 1
  select_type: SIMPLE
        table: twitter_relationships
         type: ref
possible_keys: source_and_target,target_status_and_change_date_index,user_id_index
          key: source_and_target
      key_len: 12
          ref: const,const
         rows: 8560582
        Extra: Using where

Any ideas what I can do in the query or even in the table structure to speed this query up?

First, since you already have a UNIQUE index that contains the user_id, you should be able to get rid of the id field, and use the UNIQUE index as the PRIMARY KEY:

CREATE TABLE `twitter_relationships` (
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index`
    (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
PARTITION BY HASH (user_id) PARTITIONS 1000;

Unfortunately, while this removes an index, it may increase storage requirements, due to the way that InnoDB indexes data. See "How Secondary Indexes Relate to the Clustered Index" in http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html

Second, while the source_and_target index has two of the three fields in your WHERE clause, MySQL will have to do an additional read to find the relationship_status.

Therefore, to improve performance, create an index that includes all three fields in your WHERE clause:

CREATE INDEX user_source_status ON twitter_relationships
    (`user_id`,`source_twitter_id`,`relationship_status`);

Then, if MySQL doesn't use this index automatically, you can force using it, with:

SELECT target_twitter_id
 FROM `twitter_relationships` FORCE INDEX (user_source_status)
WHERE (`twitter_relationships`.`user_id` = ?
   AND `twitter_relationships`.`source_twitter_id` = ?
   AND `twitter_relationships`.`relationship_status` = ?)
LIMIT ?, ?

Lastly, you're missing the UNSIGNED attribute on the id, user_id, source_twitter_id, and target_twitter_id fields. I'm guessing these fields will never store negative values, so it would make sense to make them UNSIGNED.


Availability mode - manual failover mode, best practice for availability mode? We don't want automatic failover for our availability group, but I have set availability mode to Synchronous, was that a mistake?

The availability group runs on vmware with windows 2012R2, and I got the error messages below after the cluster "crashed". (There was some manual migration off the vmware at the same time)

Can I related the crash to availability mode settings?

Thank You in advance.
Regards Odd

Kl 13:58:56 – Critical : Cluster node ‘' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Kl 13:58:56 – Critical : Cluster node '' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Kl 13:59:58: -Critical:  File share witness resource 'File Share Witness' failed to arbitrate for the file share '\\Quorum'. Please ensure that file share '\\Quorum' exists and is accessible by the cluster.
Kl 13:59:58: - Error: Cluster resource 'File Share Witness' of type 'File Share Witness' in clustered role 'Cluster Group' failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Kl 13:59:58: -Critical:  The Cluster service is shutting down because quorum was lost. This could be due to the loss of network connectivity between some or all nodes in the cluster, or a failover of the witness disk.
Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Kl 14:01:22: - Error: Cluster resource '_Grp' of type 'SQL Server Availability Group' in clustered role '_Grp' failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Kl 14:01:34: - Error: Cluster resource '_Grp' of type 'SQL Server Availability Group' in clustered role '_Grp' failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Kl 14:01:34: - Error: The Cluster service failed to bring clustered role '_Grp' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.

Can I related the crash to availability mode settings?

No.  By the looks of your log messages, you actually lost cluster quorum due to the removal of two nodes' votes, and then your witness (file share).  Is this a 3 node cluster with a file share witness perchance?  In that case, if you pulled these events from one of the node's event logs, then it may appear to each of the nodes that there is a lack of communication with all voters.  That would generate a similar, if not same, error footprint like you have above.  Nobody can talk to anybody, if that is the case.

During that time, the quorum will be lost as you are currently seeing.  There is a level of assuming here, as I'd need to see way more diagnostic information to pinpoint the cause of voter removal, but that is why the quorum was lost.

Regardless, this appears to be a problem that surfaced in a down cluster, in which case your availability mode would have nothing to do with the WSFC cluster failing.

As for "best practices" for the availability mode to go with, you need to determine requirements for data loss, performance impact, and a few other factors that are best described in this BOL reference on Availability Modes.


Replication for Mirrored server I have 3  SQL servers  running SQL Server 2012 Standard Edition, called A , B and C. Server B is a mirror of server A. There is transactional replication between server A and Server C.

How can I ensure replication continues to C regardless of which server is the primary server in the mirrored pair?

The -PublisherFailoverPartner option should help you out in your case. I am assuming you want to have replication running irrespective of database running on principal/mirror server.

Below links have your issue covered and has all the details on how to configure it.

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/replication-and-mirroring-setup/

https://www.pythian.com/blog/how-to-configure-transactional-replication-mirroring-failover/

https://msdn.microsoft.com/en-us/library/ms151799.aspx

Test it out in your test environment before implementing it on PROD.


How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures? I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could also be used for extending the SQL Server base functionality. I am interested in knowing the following:

Are PostgreSQL extensions and SQL Server extended/CLR stored procedure having similar purpose? If not, why?
If answer to above is yes, then, which one is better - PostgreSQL extensions or SQL Server extended/CLR stored procedures? Why?

Thanks.

I think you have to look at extensions entirely differently.  The CLR stored procedures are most closely related to the procedural language interface which is one application of the extension system.  The extension system has goals quite a bit broader than mere stored procedure languages.  Extensions can define primitives or functions as well.

However if we limit the question to the stored procedure interface, the answer is, broadly speaking, that they have a similar purpose.  There are a significant number of major differences between the two and they come from different database viewpoints.

The two most obvious differences are that functions have a lot less transactional control in PostgreSQL than MS SQL, and that MS SQL can more easily return multiple result sets from functions than PostgreSQL can.  On the other hand, you can write stored procedures in PostgreSQL in a wide range of languages including TCL, Perl, Python, Java, and more.  I suspect that PL/Mono is relatively unmaintained, but it could be polished up without too much effort allowing you to run CLR code as well.  So PostgreSQL gives you a great variety of languages, while MS SQL gives a little more control in the stored procedures over things like transactions.

This being said, in general these just force different perspectives on database development.  Both are quite programmable databases, though I personally think PostgreSQL is slightly more programmable, this may say more about the fact that I know PostgreSQL better than MS SQL than it does about the two RDBMS's.

My reason for siding with PostgreSQL here has to do with the ability to hook a lot of these functions into indexes.  This is also useful when combined with filters on indexes (which both support).  In essence I am a little happier with the way functions fit into SQL with PostgreSQL.  However, this is a really minor detail.....

In the end I think it is probably possible to do pretty much anything you can do with one with the other.  I find the "object-relational" perspective of PostgreSQL to be very empowering.  I would, however, not recommend making a purchasing decision based on such an abstract criteria.  Nitty gritty matters.


how to turn off delimiters in generated SQL scripts for data types in SQL Server Management Studio We are a software engineering firm. We use SQL Server Management Studio (SSMS) to generate the few scripts we need to build and upgrade our databases.
While it does an OK job, it bugs me that it insists on surrounding data types with []. So a column definition will be like:

[ID] [INT] IDENTITY(1,1) NOT NULL

while this just works, I don't like it because it does not distinguish the column name from its type in the script. Including syntax highlighting (everything is just black or your default font color). I was wondering if anybody had any idea on how to alter this behavior. We just want the output to be like:

[ID] INT IDENTITY(1,1) NOT NULL

Purchasing a specific tool is not an option at this time based on how much scripting we really do.

Thanks in advance for any help,

Eric.

I don't know of any setting which will do this in SSMS.  Microsoft probably does it so that they don't need to check the data type name for special characters when generating the script (user defined data type names could have anything in them) so they just box everything.

You could submit that as a feature request to the tools team via connect.microsoft.com or to Mladen for including in his SSMS Tools Pack.


Grouping rows by looking at two columns without considering the order I have a table looks like this one below:

+----+---------+---------+
| ID | VALUE 1 | VALUE 2 |
+----+---------+---------+
| 01 | A       | B       |
| 01 | B       | A       |
| 02 | C       | D       |
| 02 | D       | C       |
+----+---------+---------+

So what I am trying to do is grouping them together by looking at the value1 and value2 without considering the order of this two values, which mean "A and B" is the same as "B and A".

I am looking for a result looks like this:

+-------+---------+---------+
| COUNT | VALUE 1 | VALUE 2 |
+-------+---------+---------+
| 02    | A       | B       |
| 02    | C       | D       |
+-------+---------+---------+

Does anyone has any idea about how can I get this?

Thanks a lot!

V

Something like:

SELECT COUNT(*) AS C, V1, V2
FROM   (SELECT CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
             , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
        FROM   input_table
       ) AS tbl
GROUP BY V1, V2

should do the trick, but may not be terribly efficient. Any filtering clauses should be added to the inner select, not the outer.

SELECT COUNT(*) AS C
     , CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
     , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
FROM   input_table
GROUP BY CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END, CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END

may also work (and may be more efficient) but the code repetition between the GROUP and SELECT clauses may become a maintenance problem.

Of course if you can ensure that the data is always the "right way around" (and this doesn't break your model in other ways - we can't tell if it might as your questions gives no detail on which to base a supposition either way) then

SELECT COUNT(*) AS C, Value1, Value2
FROM   input_table
GROUP BY Value1, Value2

is sufficient. You could enforce the two values being the right way around using INSTEAD OF triggers or in your business logic layer (updating existing data should be easy).


2 Identical Servers/SQL Servers Processing Transactions w/ Merge Replication Edited entire question for clarity.

Scenario: Have 2 identical SQL Servers on separate servers, processing transactions from different applications simultaneously while being completely synchronized (example mobile apps server 1 and web apps server 2).

We are going to use Always On to fail over one server to the other for maintenance and vice versa however, when maintenance is complete, both servers will be once again online processing transactions from our various applications and synchronized.

Trying to solve a request from a client to be online, even during maintenance, with 2 synchronized servers, each processing transactions from different applications.

What is the best approach to keep them synchronized?  Was leaning towards merge replication.

Current ver. SQL Server 2012 SP3-CU4

Thanks

Have you considered Peer-to-peer transactional replication?
You can find out more comparison here.

  Peer-to-peer transactional replication:  Use When...

  Replication is used to improve scalability and availability.
  Minimal latency is required.
  Data is not partitioned among Subscribers.
  Conflicts typically do not occur, but they must be detected if they do.

  Merge replication:  Use When...

  There are a large number of Subscribers.
  Data is replicated to mobile users.
  Replicated data is frequently updated at the Subscriber.
  Data filtering is needed so that Subscribers receive different    partitions of data.


MongoDB multiple Cluster data sync Can we have this type of configuration?

1)MongoDB - Multiple cluster data synchronizing master to master

2)MongoDB - Multiple cluster DR

3)MongoDB - Multiple cluster Blue-green deployment

1)MongoDB - Multiple cluster data synchronizing master to master

As MongoDB jira documentation Why was MongoDB not designed to have master-master replication actually, don't think master-master perform better than master-slave.
but it indeed easy to scale out.

my point about this is that mongo have provided too much functions to support all DB requirement.and lose its core design brief.this cause messes when using mongo db.it want to provide RDBMS function,also provide high availbility,scale out.

today mongo provide replica set deployment to meet normal data storage.and sharding cluster for large data storage.it is easy to convert replica set architect to sharding cluster deployment.but from application side,it is not easy to keep same query operation efficiency.

and other things like Data engine.mongo provide MMap,WireTiger,also other third party data engine.which also cause application developer to optimize the performance.

MongoDB doesn't do master-master replication.

  2) MongoDB - Multiple cluster DR

As per Ron Galbraith BMC documentation MongoDB configuration across 2 sites - and manual DR option  the issue arises around forming a Majority to elect a new Primary server (based on information on the MongoDB Strategy around fault tolerance.

  3)MongoDB - Multiple cluster Blue-green deployment

blue-green deployment strategy is Zero downtime during application deployment is one of the key requirements for continuos delivery. And no business would like their site to be down and showing maintenance page every few days/weeks during deployment.

To achieve this we decide to go for blue-green deployment. However, we were challenged with how to do this in legacy style data center infrastructure where we are,

Not able to spin new and throw away machines automatically using scripts
Don’t have ability to add/remove instances using scripts from load-balancer
Network level configurations are done manually like firewall setting

As per Sunit Parekh blog documentation of Decision 1 MongoDB database didn’t require any schema migration etc. However, we required to follow design principle, always write code which is backward compatible with the data models. And if there is any database migration required it should be run after deployment.


Design question: join data I have this simple tables (from Northwind database)

Orders-->[Order Details]<---Products

with a query can see  the order and the products using join very simple

  SELECT
  Orders.OrderID,
  Products.ProductID,
  Products.ProductName
FROM Orders
INNER JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products
  ON [Order Details].ProductID = Products.ProductID

OrderID     ProductID   ProductName
----------- ----------- ----------------------------------------
10265       17          Alice Mutton
10279       17          Alice Mutton
10294       17          Alice Mutton

but the problem when a user change the name of the product this will cause display the new name for that product

OrderID     ProductID   ProductName
----------- ----------- ----------------------------------------
10265       17          New product name
10279       17          New product name
10294       17          New product name

old Orders shouldnt change so my question is how keep the related data even if they change the name of the new product

thanks sorry bad english

The typical solution in OLTP is to copy the relevant attributes onto the Order or Order Details table.  Eg handle it like the product price, which is always copied onto the Order.  So the name of a Product at the time of the Order is modeled as an attribute of the Order, not through the relationship with the Product table.

In Data Warehousing, the typical solution is to maintain a Slowly-Changing Dimension for the Product, so each SqlesFact is linked with a historically-accurate version of the Product Dimension.


What makes an OLTP database an OLTP database? This is a pretty broad question.  I know the definition of OLTP.  But it seems like unless you have an environment that has a clear distinction between OLTP and OLAP, in the sense of a database that handles DML transactions and a replicated database that handles the reporting, then this is a bit of an abstraction.

Would it be safe to say that most production databases are OLAP and OLTP?

I would say that most production databases are OLTP with a little OLAP flavor. In a lot of shops the OLAP side of the world is getting more specialized and they really are performing the analytical functions outside of the normal application database. Are there shops that still plunk everything into one database? Of course. But as the capabilities improve (and Denali is no exception), I think you'll start to see a much more obvious divide.


Performance comparison in ways a SSIS package can be run I'm wondering if there is any difference in performance of a SSIS package if it runs using SQL Server job, vs running using DTEXECUI.EXE Utility or running in BIDS?

Also if the target database in on the server but package is running on a local machine, would it be using the memory on the server or the local machine?

Thanks in advance.

Cheers,
Nazila

Performance should be the same between running it as a job, via DTEXEC or BIDS.

If the package is run on the SQL Server is uses SQL Servers's buffer pool as it queries the data, then SSIS will use it's own memory space to do work.

If the package is run on the client machine it'll use the SQL Server's buffer pool and memory on the client machine.

Running packages on other machines is usually slower, especially if there is a lot of data to move as the data has to be moved over the network.


Indexing: Invalid default value for '[column]' I am working on a 10 year old web-app (!!!)
& currently running mysql locally, version 5.7.
This is the table I am currently working on:
CREATE TABLE `processes_history` (
  `p_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `exec_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `feature` varchar(100) NOT NULL DEFAULT '',
  `macro` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `ts` date NOT NULL DEFAULT '0000-00-00',
  `seen` int(10) UNSIGNED NOT NULL DEFAULT '1',
  `seen_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `focus` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `focus_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `mouse` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `keyboard` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `interactive` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `interactive_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(`ts`))
(
PARTITION p0 VALUES LESS THAN (736695) ENGINE=MyISAM,
PARTITION p201701 VALUES LESS THAN (736726) ENGINE=MyISAM,
PARTITION p201702 VALUES LESS THAN (736754) ENGINE=MyISAM,
PARTITION p201703 VALUES LESS THAN (736785) ENGINE=MyISAM,
PARTITION p201704 VALUES LESS THAN (736815) ENGINE=MyISAM,
PARTITION p201705 VALUES LESS THAN (736846) ENGINE=MyISAM,
PARTITION p201706 VALUES LESS THAN (736876) ENGINE=MyISAM,
PARTITION p201707 VALUES LESS THAN (736907) ENGINE=MyISAM,
PARTITION p201708 VALUES LESS THAN (736938) ENGINE=MyISAM,
PARTITION p201709 VALUES LESS THAN (736968) ENGINE=MyISAM,
PARTITION p201710 VALUES LESS THAN (736999) ENGINE=MyISAM,
PARTITION p201711 VALUES LESS THAN (737029) ENGINE=MyISAM,
PARTITION p201712 VALUES LESS THAN (737060) ENGINE=MyISAM,
PARTITION p201801 VALUES LESS THAN (737091) ENGINE=MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE=MyISAM
);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `processes_history`
--
ALTER TABLE `processes_history`
  ADD PRIMARY KEY (`p_id`,`exec_id`,`feature`,`ts`),
  ADD KEY `ts` (`ts`),
  ADD KEY `exec_ts` (`exec_id`,`ts`),
  ADD KEY `last_seen` (`last_seen`);

I keep getting an error when adding an index to p_id, exec_id, ts:
ALTER TABLE `dbname`.`processes_history` ADD INDEX `p_id,exec_id,ts` (`p_id`, `exec_id`, `ts`);

Error SQL query:
ALTER TABLE dbname.processes_history ADD INDEX p_id,exec_id,ts
(p_id, exec_id, ts) MySQL said: Documentation
1067 - Invalid default value for 'ts'

Following this post: On Create Index, Invalid default value
From what I understood, using 0000-00-00 as a default value breaks the 'date' type, and that's why it's not working.
But I just couldn't understand what is the solution for this situation. Using TIMESTAMP type instead?
Is there a way to solve this problem without breaking the structure (for now at least) unit I finish the whole web-app? Many things are dependent on that table and i reeeealy don't want to do something risky to index it the way I want.

@jerichorivera is on point with the problem, I will just expand on what he is meaning.

From MySQL 5.6 to MySQL 5.7, MySQL changed the default SQL_Mode from a "loose" one to a stricter, standards-complient "MySQL strict mode". On it, dates like '0000-00-00' are not allowed, the first allowed data is '1000-01-01'. Other examples can be seen here: https://www.slideshare.net/jynus/query-optimization-with-mysql-57-and-mariadb-10-even-newer-tricks/45

If you want a quick fix now so the application unbreaks, you can reset the sql mode by setting it to NO_ENGINE_SUBSTITUTION, nothing will break and you will get the 5.6 behaviour.

However, you may want to eventually "fix" your schema- using '0000-00-00' for an invalid date is as bad as using the empty string ('') for an invalid string, or a variable with an uninitialized value in C. SQL has a value to represent invalid/unknown states, and that is NULL- my suggestion is to use it to represent invalid dates (could be done implicitly with the property NULL), however, you will have to work for the application to have into account this new "state" for the column, plus any other design supposition regarding ordering and comparison both on application and on database levels (e.g. supposing any comparison will always return true or false, and not  a third state NULL).

If using NULL is not possible or desirable, you can use a special value within the range allowed, it is not best practice, but at least it is not mixed with invalid inputs.

Maybe you can drop the column if it is no longer in use?

Once you have fixed all database issues, the TRADITIONAL/Strict modes will give you less headaches in data consistency.


If Exists - Select Prevent Repeating Code We receive an order list, and validate it starts with certain prefixes, example if OrderNumber does not start with ABC, than display order list to user as error. We use exists, to search for first existence, don't want to consume much performance time. If none exist, then we can start conducting other task in code.

Is there anyway in sql to get rid of the repetitive code? We have Many checks like this on multiple tables, trying to make code more efficient.

if exists
(
    select *
    from dbo.OrdersImport
    where left(OrderNumber,3) <> 'ABC'
)
begin
    select OrderNumber as OrderErrorList
    from dbo.OrdersImport
    where left(OrderNumber,3) <> 'ABC'
end
else

Unfortunately I'm afraid by trying to prevent repeating code you might end up in writing more code and making the query less readable.
But I believe this can be advantageous for you - take into account situations where in the test you would only have to search in a one or two tables, where in order to retrieve all the details you will have to join to several more. In those situations you can simplify the test query thus also making them quicker to execute.

And while we are at it, you might also consider changing your WHERE statement. Using functions in the predicate makes them not SARGable and prevent using indexes in those cases.
A better approach in this example would be to check

WHERE OrderNumber LIKE 'ABC%'

as the optimizer will perform conversion to something along the lines of

OrderNumber >= 'ABC' AND OrderNumber < 'ABD'

which supports the use of indexes.
So this would be Index Scan (using left()) versus Index Seek (using LIKE).


Update an Oracle DB with a max number for the null rows I have an Oracle db and I need to update some columns and one of them is an number that has to be some kind of an id - max number on that column. If I use something like this:

update ss_chest ssc
    set SSC.NUMAR_CHEST = 'RO' || (select lpad(max(SSC.NUMAR_CHEST),5,'0')
                                   from ss_chest ssc where SSC.NUMAR_CHEST is not null)
where SSC.NUMAR_CHEST is null;

And the result is always the same and it updates my table with the same max NUMAR_CHEST. How can I update it always with the max number? (I have to concatenate my number with RO in front and with leading 0 on 5 spaces)
Thanks!

Is this a one-time update, or are you planning on running that query after inserts, where the insert statement is not setting SSC.NUMAR_CHEST? If you want ascending numbers, it sounds like you need to use an Oracle sequence; your subquery (select lpad(...  will return the max value at the point in time of the start of your query.

To use a sequence, create it once (using the name SEQ_CHEST in the examples below), then reference it in your update; every time you access SEQ_CHEST.nextval you will get a unique number:

create sequence SEQ_CHEST maxvalue 99999;  -- one time deal

update ss_chest ssc
   set SSC.NUMAR_CHEST = 'RO' || lpad(SEQ_CHEST.nextval,5,'0')
   where SSC.NUMAR_CHEST is null;

and your insert statements would be like

insert into ss_chest values('A', 'B', ... 'RO' || lpad(SEQ_CHEST.nextval,5,'0'));


Removing last two digits If I  have 12345678, 1234567, 123456 in a table as a column and using sql I want to create a new column that will have new numbers that removed the last two digits of each number. The new column will have 123456, 12345,1234.

Would someone help in writing this code in Microsoft sql environment

It's just a simple integer division.

So select (123456/100).

But that only valid is the column is integer type.

The example provided is big. If the column is integer then using a simple case logic is suffice. First check if the value is bigger than 99 then divide by 100, if not use what it is.

Declare @field_name;
Set @field_name = 123456;

Select (case when @field_name > 99 then (@field_name/100) Else @field_name);

change field name to any column you wish and adjust the query to target table.

Rather than using convert or cast , integer divide provide less cpu cycle because of dedicated math block inside the cpu. When using convert or cast you have many more cpu cycle because it will also include memory operation.


When should I start using connection pooling? I have a MySQL database that peaks at about 300 connections/sec.

What is the recommended connections per server and at what point should I start using connection pooling?

IHMO, using of connection pool hasn't a minimum. You can use it when you are sure there are a lot of accesses on your DB and they are very frequently, so you're using the CP guaranteed to use in better way the available connections that caught by users when they are idles.

The numbers of connections must consider the dimension of DB server. There's no a default number that's OK for all configurations.


Are master/slave replication relationship between different versions of SQL Server possible? I have a database server, SQL Server 2005, that's inside a private network. Now i need to make a database there available for a webserver, so i want to replicate this db to an external server, which is running SQL Server 2008. I want the internal server to be the Master and the external one the Slave. New data will be added a few times a day maybe, infrequently, and should be replicated smoothly without manually intervention.

Is this a possible scenario, or do i need to upgrade the SQL Server on the inside to the same version as the external one?

If it's possible, are there drawbacks in doing it in this way that makes the upgrade scenario more recommended anyway?

Short answer is yes that is possible and straight forward (I've just finished setting up transactional replication from SQL 2005 to SQL 2008 R2).

Note: in SQL Server replication the terms are 'Publisher' and 'Subscriber'


Does pg_restore continue after internet connection got lost I know a query runs until it returns data when connection to the client is lost. However, what if you use pg_restore. Does it upload the restore file and then does the restore on the server? Or does it restore step by step requiring the client to stay connected.
I want to know this because my employee uses a vpn that disconnects automatically after some time. And the restore takes longer that the timeout XD.
When the connection got lost I got the pg_restore: error: error returned by PQputCopyData: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.  error.
However on the server I can see the process is still up and running using SELECT * FROM pg_stat_activity;

pg_restore keeps executing SQL statements on the server. If the connection to the server fails, the restore was not successful.
What is strange here is that there should not be any long idle times in the connection, with the possible exception of creating indexes and refreshing materialized views.
At any rate, you should see that the network gets configured properly. You cannot really work with an unreliable network like that that drops your connection as soon as you turn your back.


Any ideas on how to parallelize mysql_upgrade? I am migrating from 5.5 to 5.6 and would like to speed up the mysql_upgrade process. I have thousands of databases per machine and 200 tables (innodb) per db. Any suggestion how to speed up the upgrade process?

I upvoted your question because this is a common problem, and a solution would be welcome. But unfortunately, I believe that there is no way to parellalize it. mysql_upgrade always operates on the whole server directory, there is no way to only upgrade one database, and it reads the system tables.


SqlAnywhere 12- service started but stopped I've been running my *db file on Windows 7 platform. Rrcently I have encountered a problem where I try to revoke the SqlAnywhere server but I received this error:

  The Sql Anywhere- myserv service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

Kindly help.

If you are still having problems with this, the first place to look is in the Windows Application Events log - you should see the reason (eg manual shutdown requested) why the server was stopped.

You can also get the database engine to write its own console log to file with the

-o <filename>

server start up option - that may give you more detailed information.

It's important to check that your connections aren't specifying the AutoStop connection parameter or the Stop Database After Last Disconnect ODBC setting. This only applies if you are asking the connection to automatically start the database engine if it is not already running - NOT if you are running the database engine as a service.


JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data? SQL Server 2016, I'm attempting to work with some regular data and return a JSON object for processing by another system. The other system does not recognize the array wrapper, and so I am attempting to use WITHOUT_ARRAY_WRAPPER to get rid of this. When used in a subquery odd results get returned...

SELECT @@SERVERNAME AS [Servername],
( SELECT [Name], [Recovery_Model_Desc]
    FROM sys.databases
    WHERE name in ('master', 'model', 'msdb')
    FOR JSON PATH
    ) AS d
FOR JSON PATH, ROOT('ServerInformation')

This produces expected data, with the array wrapper...

  {"ServerInformation":[{"Servername":"MyServer","d":[{"Name":"master","Recovery_Model_Desc":"SIMPLE"},{"Name":"model","Recovery_Model_Desc":"FULL"},{"Name":"msdb","Recovery_Model_Desc":"SIMPLE"}]}]}

However, WITHOUT_ARRAY_WRAPPER produces...

SELECT @@SERVERNAME AS [Servername],
( SELECT [Name], [Recovery_Model_Desc]
    FROM sys.databases
    WHERE name in ('master', 'model', 'msdb')
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS d
FOR JSON PATH, ROOT('ServerInformation')

  {"ServerInformation":[{"Servername":"MyServer","d":"{\"Name\":\"master\",\"Recovery_Model_Desc\":\"SIMPLE\"},{\"Name\":\"model\",\"Recovery_Model_Desc\":\"FULL\"},{\"Name\":\"msdb\",\"Recovery_Model_Desc\":\"SIMPLE\"}"}]}

whereas I would expect it to produce

  {"ServerInformation":{"Servername":"MyServer","d":{"Name":"master","Recovery_Model_Desc":"SIMPLE"},{"Name":"model","Recovery_Model_Desc":"FULL"},{"Name":"msdb","Recovery_Model_Desc":"SIMPLE"}}}

Bug or expected results?

Edit: adjust expected results

use  json_query,

SELECT @@SERVERNAME AS [Servername],
json_query(( SELECT [Name], [Recovery_Model_Desc]
    FROM sys.databases
    WHERE name in ('master', 'model', 'msdb')
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) ) AS d
FOR JSON PATH, ROOT('ServerInformation')


How many tables are needed to implement the scenario depicted in my entity-relationship diagram? How many tables are required to implement the scenario expressed in the ERD shown below?

My try:

Table (CR2) with the columns {B1, C1, C2}
Table (R1) with the columns {A1, P, B1}
Table (A) with the columns {A1, A2, A3}
Table (R3) with the columns {A1, B1, Q}
Table (B) with the columns {B1, B2}

Have I got it right? Am I missing something?

Your answer is mostly correct, except Q is a dependent attribute of R3, not a table on its own.


Creating Report Subscriptions on report that are using a Shared data source that uses Windows Integrated Security We've recently deployed a new SSRS Server 2012, that can use Windows Integrated Security. We configured the servers to properly use Kerberos authentication and we solved the double hop issue.

Now, all our reports are using Shared data sources that are configured to use Windows Integrated Security instead of storing an execution user on the data source setup page.

It all works fine and I was very happy about it, as it increased our security (as the execute permission is derived from the AD user and not from the hard-coded execution user), but now we're running into a problem:

When we try to create a Report Subscriptions on report that are using a Shared data source that uses Windows Integrated Security, we're getting the error message:

  Subscriptions cannot be created because the credentials used to run reports are not stored, or if a linked report, the link is no longer valid

I've googled around, and all the links I've found tell me to simply either configure the shared data source to have stored credentials, or configure the report to have custom data source with stored credentials.

This contradicts the whole point of us have Windows Integrated Security!

Is there any way around it?

The sole purpose of SSPI in our use case is to authenticate to the database. The RS server has reports that use data sources on different SQL Servers. We are not, for example, using Integrated Security for row-level security, or to show different data to different users.

Community wiki answer:

You have two choices:

Change the data sources to use a SQL login and password, then you can use the built in subscription method.
Use a scheduled app to generate the report, and have that do the e-mailing.  The scheduled app would need to run under an account with permissions into SQL/SSRS.

I would only recommend #2 if you don't really need/want the users managing their own subscriptions.  If the users are going to need the ability to generate their own subs then #1 is your only real option.