Domain Admins can't connect to SQL Server I have a Windows Server 2012 R2 domain controller and a database server (W2K12R2/SQL Server 2012).

As allowed usergroup I have given the group "domain-admins" sysadmin rights.

But somehow any domain-administrator can't connect to that SQL Server, but when I add the domain-administrator user directly as allowed user it does work.
The active directory does work. RDP-Login works. Am I missing something?

Does anybody know how to grant access to the whole group "domain-admins"?

The error message is:

Servername: SRV-DB
Error Number: 18456
Schweregrad: 14
Status: 1
Line of Code: 65536

In the event log it says (translated):

  Error checking the token based server access with infrastructure failure.

State is "1" . I log on to that db-server with RDP as an domain-admin. I have to explicitly start SSMS with "run as administrator". Then I can access the "localhost". If I leave out starting SSMS as admin then I can't do that. I thought being in the group "domain-admins" would be enough.

I found the answer to my problem in this answer by Remus Rusanu.

As an domain-admin connected to that SQL-Server-Host via Remote Desktop. I had to start SQL Server Management Studio explicitly "as an administrator". (right-click -> run as administrator).

If SSMS isn't started in administrator mode, the current login is not in "builtin\administrators"-group. That was the problem.

It comes from Windows user access control (UAC). So that is actually a security "feature".

It's easy.
If you are able to Logon with old credentials ( the Administrator of local computer ) you have to use it to access a Remote Desktop and use it to add the Domain Controller Administrator


Select highest id of each type in mysql I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ids. My Query is below.

Select
  backups.id,
  harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
From
  backups Inner Join
  resource On resource.id = backups.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Group By
  backups.resource_id
Order By
  backups.start_time Desc,
  backups.id

This outputs the following:

The latest backup for SDSBackup_OFFICE should have an ID of 118. I have read about using either subqueries or different types of joins but I wasn't able to get them to work. Any ideas?

I tried the following query as one of the answers below suggested without success. The max backup.id was indeed selected, but it was incorrectly matched up with other values in the database.

Select
  Max(backups.id) As Max_id,
  harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
From
  backups Inner Join
  resource On resource.id = backups.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Group By
  backups.resource_id
Order By
  backups.start_time Desc,
  Max_id

I did some research and found that the groupwise max function only applies to the given column that it is applied to. It is left to the server to determine how the other data should be assigned. I don't know if I fully understand this. I am getting my information from a question/answer on stackoverflow. https://stackoverflow.com/questions/7594465/group-wise-maximum-of-a-certain-column.

All the examples on that page are fairly simple queries, I don't know how to apply these examples to my query. Help would be much appreciated.

I think you're looking at something like this:

Select A.Max_id,
harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
  FROM
  ( Select  backups.resource_id,
    Max(backups.id) As Max_id
      FROM backups  Group by backups.resource_id
      Order By backups.start_time Desc ) A
JOIN resource ON resource.id = A.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id

OR (order by outside, don't remember)

Select A.Max_id,
harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
  FROM
  (  Select  backups.resource_id,
    Max(backups.id) As Max_id
  FROM backups  Group by backups.resource_id ) as A
JOIN resource ON resource.id = A.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Order By   A.start_time Desc

harddrives.id As id1

Update it to

  (Max)harddrives.id As id1

I think that should work.


How to get the return value from a stored procedure executed with sp_executesql I need to call a stored procedure in another database and check the return value. The name of the other database can vary so I'm trying to use sp_executesql, but I haven't been able to find how to get the return value from the procedure.

This is a simplified version of what I've got:

DECLARE @errorLogId INT
DECLARE @otherDbName NVARCHAR(MAX) = 'Foo' -- Get the database name from somewhere
DECLARE @sql NVARCHAR(MAX)

SET @sql = @otherDbName + '.dbo.SomeProc'

-- I want @errorLogId to be the return value from SomeProc, this didn't work.
EXECUTE @errorLogId = sp_executesql @sql

IF @errorLogId <> 0
   RAISERROR('SomeProc failed, ErrorLogId = %d.', 16, 1, @errorLogId) WITH SETERROR

If an error occurs in SomeProc it is caught and an entry is written to an error table (in the other database), then SomeProc returns the error table ID (identity) of the record that was written. Otherwise SomeProc returns 0.

I know SomeProc is failing because an error is written to the error log in the other database, but in the local database @errorLogId is 0 which is treated as success. I thought the return value would be 'passed through' sp_executesql, but I think the return value is actually from sp_executesql not SomeProc (i.e. sp_executesql can be successful regardless of whether SomeProc succeeded or failed).

Is there a way to access the return value from @otherDbName.dbo.SomeProc in the local database?

Thanks

You need to use the OUTPUT parameter, as it is described in the MSDN article:

Given this dummy stored procedure:

CREATE PROCEDURE sp_test AS
RETURN 2

This code will give you the result:

declare @ret int

exec sp_executesql N'exec @ret = sp_test', N'@ret int OUTPUT', @ret = @ret OUTPUT
select @ret as result

result
-----------
2

(1 row(s) affected)

Execute procedure(containing Output and Input parameters) and view results

IF spCHGAssetGet is the stored-procedure name.

@CustomerID, @AssetID are the output parameters.

@IDTicketItem, @IDExtWarranty are the input parameters.

DECLARE @CustomerID NVARCHAR(100)
DECLARE @AssetID    NVARCHAR(100)
EXEC spCHGAssetGet
@IDTicketItem = 4764,
@IDExtWarranty = NULL,
@CustomerID = @CustomerID OUT,
@AssetID = @AssetID OUT
SELECT @CustomerID, @AssetID


Sql Server trace? Who is running it and how to stop it? Can you please help me out in finding who is running SQL Server Trace(profiler) on Server machine.?

It looks like, some people are running trace on live system on SQL DB, which make application super slow.

We need to find out who is running that or who ran that in the past? any idea how to do this?

Thanks.

Run the SQL command below:

   select
          [Status] =
          case tr.[status]
                when 1 THEN 'Running'
                when 0 THEN 'Stopped'
          end
          ,[Default] =
                case tr.is_default
                      when 1 THEN 'System TRACE'
                      when 0 THEN 'User TRACE'
                end
           ,[login_name] = coalesce(se.login_name,se.login_name,'No reader spid')
          ,[Trace Path] = coalesce(tr.[Path],tr.[Path],'OLE DB Client Side Trace')
          from sys.traces tr
                left join sys.dm_exec_sessions se on tr.reader_spid = se.session_id

Source:- https://mssqlwiki.com/2010/04/26/how-to-find-all-the-profiler-traces-running-on-my-sql-server/

This will stop a trace

DBCC TRACEOFF (1222,-1)

Some traces are system traces.  For example trace 1222 turns on deadlock logging into the SQL Server error logs.  Be careful and find out what you are turning off before you do it.


ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX; JOB_SCHEDULER is biggest occupant DBMS_SCHEDULER is occupying all of my SYSAUX tablespace. I ran dbms_scheduler.purge_log which deleted 100 million rows from dba_scheduler_job_run_details, however v$sysaux_occupants and the data file size remain unchanged. Is there some additional action I need to take to clear the SYSAUX tablespace of DBMS_SCHEDULER generated data?

Attempting to insert a single row into a newly created table in my regular tablespace fails with:

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX

I can see that JOB_SCHEDULER is taking up 92.4% of the SYSAUX tablespace by querying v$sysaux_occupants:

SELECT
    occupant_name,
    round (sum(space_usage_kbytes) * 100 / sum (sum(space_usage_kbytes)) over (), 2) Pct
FROM v$sysaux_occupants
GROUP BY occupant_name
ORDER BY 2 desc NULLS LAST
;

I originally had over 100 million rows in dba_scheduler_job_run_details.

Yesterday, I ran the purge command (which took 3.5 hours):

BEGIN
    dbms_scheduler.purge_log;
END;
/

Today, dba_scheduler_job_run_details has less than 1K rows.

However, the query on v$sysaux_occupants is unchanged; today it still says JOB_SCHEDULER is occupying 92.4%. Likewise querying my data file sizes show that SYSAUX is still maxed out:

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_data_files d,
    v$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

It seems like I have not actually deleted the space consumed by DBMS_SCHEDULER.

Is there some step I am missing to clean up all the space consumed by DBMS_SCHEDULER?

To release space to SYSAUX tablespace you can try:

alter table sys.scheduler$_event_log enable row movement;
alter table sys.scheduler$_event_log shrink space;
alter table sys.scheduler$_event_log disable row movement;

truncate table sys.scheduler$_event_log drop storage;


SQL Server: Log Shipping in "restoring" state: Can I temporarily take it out of restoring to check it? I know it is normal for the target db to be in "restoring" state

I also would like to do a few queries as a sanity check that the data is all up to date.

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain?

thanks!

I would suggest configuring the log shipping to STANDBY mode instead of NORECOVERY.  This will allow you to query the secondary db for any reporting, etc.

When you use NORECOVERY mode, the secondary database will not allow any users to access it, so the database does not have to worry about uncommitted transactions. The log can just be restored as it currently sits without worrying about any users accessing/changing things.

When you use STANDBY mode, the database restores in NORECOVERY mode, then looks at and rolls any open transactions. Once that is complete, users can access the database and it will be in read-only mode. When the next log is restored, the database disconnects all users and repeats the rollback/forward process and then, again, places the database in read-only mode. Lather, rinse, repeat.

Think of it as a transaction log restore.  When you restore each transaction log, you leave the DB in NORECOVERY so that you can continue to append any additional log files until you have restored to the point you require.  Then, you set it to RECOVERY and the database is able to be read. Make sense?

@DenisT brought up a good point that the 'disconnect all users' option must be checked in order for all users to get booted.  Otherwise, the log restore will fail.

Here's a great article on it
http://askmesql.blogspot.se/2011/01/log-shipping-norecovery-vs-standby-mode.html

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain?

No you cannot do this without breaking Logshipping. You cannot convert recovering database to read only without bringing it online and moment you bring it online Logshipping breaks.

Instead configure logshipping with secondary in standby mode. Note that when you check the box disconnect user when restoring database while configuring Logshipping it would disconnect all users running query on secondary DB for restore. This is one of the drawback.


Running a TPC-C Benchmark Without sleep() i.e. key-in + think time We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost 0 from 300 that we got at times) TPS graph even in supposedly steady state. During these tests we had not specified the sleeptimes for transactions (there are different sleep times recommended for different transaction types). So, by default all agents (for example, 100) continuously ran a mix of five transaction types without any sleeps in between. Later, we found out that we could in fact specify the sleep times; and when we did we could see a smoother graph without much variations.

So, the question is, if not specifying the sleep could be the real reason behind the bad results in first few tests?

Apologies for the late response to this, but there is a key reason as to why you are not observing an expected smooth behavior:  You have a client-server model violation when you eliminate think time.

The client server model is built upon a foundation that not all user are using system resources at the same time.  This is different than the mainframe model with terminals where all users are being serviced at the same time by the core host.   With client-server distributed processing exists and there is a delay between requests during which the resources are free'd to service the needs of other inbound users.  Normally when you place a host under load with think time these free internals tend to become smaller and smaller and as load increases you eventually reach a tipping point of no free resources to service requests.

By eliminating the think time you eliminate the free resource window to service additional requests and your tests become invalid as a predictor in real world performance.   If you ever hear a performance tester tell you that they have eliminated think time and every one user is equal to n real world users, fire them on the spot.

yes. As jdbcrunner creates thread for each client and throws query on database for execution.
Thread can gives many heavy queries at a time so to have some duration after each query get execute we need to specify sleep time for performance test.


Measure Performance for INSERT, UPDATE and DELETE commands on DB2 I have a DB2 database with a table for daily transactions, which is cleared to a history table at the end of the day, every day.

I insert rows to this table via java-JDBC INSERT commands and they usually take less than a second to complete but sometimes (I'm still not sure what is the pattern) it can take up to a minute to complete the INSERT command and since I don't have a timeout configured on the JDBC driver, it waits until the command is completed and then carries on.

These INSERT commands are executed thousands of times per day but this only happens on about 5 or 10 of them almos every day. The table has some indexes and a trigger attached on AFTER INSERT event.

Is there any tool or technique I can use to measure what is taking so long to execute on my INSERT command? I want to know if it is the indexes, the trigger or, for any reason, the query itself.

I guess it's a locking issue.

While such inserts take so long use admin view below. The column HLD_CURRENT_STMT_TEXT shows the blocker statement (what cause the insert took so long):

db2 select * from SYSIBMADM.MON_LOCKWAITS


How to insert values into a table from a select query in PostgreSQL? I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int).

Now I want to insert a row into items_ver from items. Is there any short SQL-syntax for doing this?

I have tried with:

INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item_id = 2);

but I get a syntax error:

ERROR:  syntax error at or near "select"
LINE 1: INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item...

I now tried:

INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;

It worked better but I got an error:

ERROR:  column "item_group" is of type integer but expression is of type
character varying
LINE 1: INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;

This may be because the columns are defined in a different order in the tables. Does the column order matter? I hoped that PostgreSQL match the column names.

Column order does matter so if (and only if) the column orders match you can for example:

insert into items_ver
select * from items where item_id=2;

Or if they don't match you could for example:

insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;

but relying on column order is a bug waiting to happen (it can change, as can the number of columns) - it also makes your SQL harder to read

There is no good 'shortcut' - you should explicitly list columns for both the table you are inserting into and the query you are using for the source data, eg:

insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;

dbfiddle here

INSERT INTO gate_pass(
     site_id, gate_pass_element, sequence_no, createdby, createddate, lastmodifiedby, lastmodifieddate)
SELECT 1,   gatepasselement, 3, 1,now(),1,now()
FROM unnest(string_to_array('Bhushan,Amol,pallavi', E',')) as gatepasselement;


A transport-level error has occurred when sending the request to the server In our Vb.net application(userd within a local network connection inside a office), which is used from long time now some of the users get the errors only sometimes a day

  A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.

I have already checked following

All databases are set to autoclose OFF
Didn't find much network problems and network speed is good.
When I checked in SSMS the under server properties remote query time-out set to 600 seconds
I checked sql server log and no errors found relating to it or same error message

So what may be the reason for this error? And how to troubleshoot it?

If an application opens a connection (successfully) to SQL Server, and leaves it open in anticipation of sending a (or possible 'another') query, and in the meantime, SQL kills that connection for some reason (eg, service restarts, or a kill command is sent), then when the connection (which thinks it's open) tries to send a command, you'll get this error.

So catch the error in your application, and reconnect. Then try the command again.

in my case this error getting if your C drive space low,and every second generate new logs files.

So resolution  is all logs delete in c drive then working fine again.


How do I identify the source of the 17886 errors ("server will drop the connection") in my SQL errorlog? I have a SQL 2012 server that is throwing the following error every 5 minutes or so:

Error: 17886, Severity: 20, State: 1.
The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.

I alert on all Sev 20 errors, so I can't just ignore this.
The good news is that I have some leads on how to fix it (seems like a connection string issue):

At DBA.SE: How to troubleshoot “client driver has sent multiple requests while the session is in single-user mode”
At SO: Error 17886 - The server will drop the connection

In MS forums: Event ID: 17886 Source: MSSQLSERVER (MSSQL Server 2008R2)

The bad news is that I have no idea which application is causing the errors!
The server has over 50 databases, from a wide variety of commercial vendors, contractors, or in-house developers. The error contains no details about which spid or login or hostname or database caused the problem.
I've tried looking at active spids (using sp_WhoIsActive) when the error is occurring, but there is too much activity on the server for anything to stand out (nor do I know what I'm looking for). Same thing with running SQL profiler - without knowing how to filter, I'm just seeing the firehose of data the server handles on an ongoing basis.
I am trying to narrow it down by figuring out which application teams have made recent changes, but that hasn't panned out so far.
Any ideas?

Try to use
select * from sys.dm_os_ring_buffers

to get more details about source clients.
Try filter type RING_BUFFER_CONNECTIVITY.


How to get the timezone from a PostgreSQL timestamp This is probably a very simple question, but it's hard to Google because it matches too many different questions.

I want to get the timezone (or offset) of timestamps saved in my database.

For example, right now in my database I have

     expired_at
---------------------
 2018-04-28 00:00:00
 2018-03-28 08:00:00
 2018-02-28 05:00:00

I want to find all expired_at that has the PST offset.

So my pseudo-code would be something like this

SELECT expired_at FROM table WHERE expired_at IS IN TIMEZONE('PST')

Can I get some help. Thanks!

PS. Basically I am trying to fix a data integrity issue by finding all UTC timestamp and updating them to be PST. The database is screwed up right now with a mix of many different timezones.

The documentation on Postgres timestamps says:

  For timestamp with time zone, the internally stored value is always in
  UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
  Time, GMT). An input value that has an explicit time zone specified is
  converted to UTC using the appropriate offset for that time zone. If
  no time zone is stated in the input string, then it is assumed to be
  in the time zone indicated by the system's TimeZone parameter, and is
  converted to UTC using the offset for the timezone zone.

Contrary to what a reasonable person might expect when reading timestamp with time zone, this Postgres datatype doesn't actually store a timezone. Internally, the value is always converted to UTC.

The information about the input timezone is lost when saving the value to the database. As far as I know, there is no way to get the information you seek.

It, probably, depends on the version of your SQL and how table was formatted.
Accordingly to
https://www.postgresql.org/docs/current/functions-datetime.html
you can extract the timezone using the keyword
SELECT EXTRACT(timezone_hour FROM your_timestamp_column)

that gives "The hour component of the time zone offset"
(also you may consider "timezone" and "timezone_minute").
There is also option to extract timezone_minute for the half-hour zones


How to determine right value for innodb_sync_array_size How do I determine the right value for innodb_sync_array_size in MySQL? We have version 5.7 deployed on AWS RDS with 32 vCPUs, and it sees about 26 concurrent threads on average. When I measure waits using:
 show engine innodb mutex, the total number of waits increases by about 143k / hour.
The MySQL documentation does say that a higher number will help with high concurrency and wait situation, but I cannot find any documentation on what to set this number and how to determine whether this will indeed help me.

This might help in understanding it more in details.


Why is optimistic locking faster than pessimistic locking? Both forms of locking cause a process to wait for a correct copy of the record if its currently in use by another process. With pessimistic locking, the lock mechanism comes from the DB itself (a native lock object), whereas with optimistic locking, the lock mechanism is some form of row versioning like a timestamp to check whether a record is "stale" or not.

But both cause a 2nd process to hang. So I ask: why is optimistic locking generally considered faster/superior than pessimistic locking? And, are there are use cases where pessimistic is preferred over optimistic? Thanks in advance!

You misunderstand optimistic locking.

Optimistic locking does not cause transactions to wait for each other.

Optimistic locking possibly causes a transaction to fail, but it does so without any "lock" ever having been taken.  And if a transaction fails because of optimistic locking, the user is required to start all over again.  The word "optimistic" derives from exactly the expectation that the condition that causes transactions to fail for this very reason, will occur only very exceptionally.  "Optimistic" locking is the approach that says "I will not be taking actual locks because I hope they won't be needed anyway.  If it turns out I was wrong about that, I will accept the inevitable failure.".

It is false to say that pessimistic locking is slower rather than optimistic or to say that optimistic is faster.
One classic query to demonstrate this unappropriat way of thinking is to do an aggregate on the different RDBMS, like :

SELECT COUNT(*) FROM atable

You will see that, in the RDBMS thats support natively optimistic approach, the time takes by this query is much more significant than is those who have a natively pessimistic lock

For instance on my PC, the same query take 27 ms on SQL Server and 109 on PostGreSQL...

The extra overhead needed in reading dead versions of MVCC rows and do not count the ghosts records in the aggregate adds an extra costs that the pessimistic does'nt have !


SQL: Storing Formatting in Data I have always taught that it is incorrect to store formatted data in a table. Examples include:

+----------------+----+
| Data           | OK |
+----------------+----+
| 0370101234     |  ✓ |
+----------------+----+
| 03 7010 1234   |    |
+----------------+----+
| (03) 7010 1234 |    |
+----------------+----+

(Why doesn’t SO support tables?!)

The data is an Australian phone number which is generally 10 digits long, including the area code.

Including the format spacing or characters introduces ambiguity, so it is harder to sort or search.

The question is: is there anything in the standards or in relational theory which covers this issue?

EDIT

This isn’t a question about telephone numbers as such. I was illustrating a point with data that should be stored in its simplest form, even if it is to be displayed with formatting characters.

Other examples include the $ sign which is clearly never stored with a currency value, and the thousands separator which has its own issues.

Formatting is meta-data.  If you include formatting in a column, you are mixing meta-data with data, which is clearly not a great idea.  I'm not a theory-guru, but I'd bet that is listed somewhere as a no-no.

For your specific example, which I know is only an example, you may actually want to store the format in a separate table.  Below is an example for SQL Server.  I must stress, this is only an example of how you might approach the problem by abstracting the format away from the data.  Please note, I'm not saying this is the best way, or even an appropriate way for your scenario.  Simply that it MIGHT work, as in the output shown below.

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

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

CREATE TABLE dbo.PhoneFormats
(
    PhoneFormatID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_PhoneFormats
        PRIMARY KEY CLUSTERED
    , FormatDetails varchar(30) NOT NULL
);

INSERT INTO dbo.PhoneFormats (FormatDetails)
VALUES ('(###) ###-####')
    , ('#### ###-###');

CREATE TABLE dbo.PhoneNumbers
(
    ClientID int NOT NULL
    , PhoneNumber bigint NOT NULL
    , PhoneFormatID int NOT NULL
        CONSTRAINT FK_PhoneNumber_Format
        FOREIGN KEY
        REFERENCES dbo.PhoneFormats(PhoneFormatID)
);

INSERT INTO dbo.PhoneNumbers (ClientID, PhoneNumber, PhoneFormatID)
VALUES (1, 2125551212, 1)
    , (2, 2300123456, 2);

SELECT pn.ClientID
    , FormattedNumber = FORMAT(pn.PhoneNumber, pf.FormatDetails)
FROM dbo.PhoneNumbers pn
    INNER JOIN dbo.PhoneFormats pf ON pn.PhoneFormatID = pf.PhoneFormatID;

the output:

+----------+-----------------+
| ClientID | FormattedNumber |
+----------+-----------------+
|        1 | (212) 555-1212  |
|        2 | 2300 123-456    |
+----------+-----------------+

This can be made to support currency symbols and thousands-separators exactly the same way.

No, as far I know at least data formating is metadata and here it dependes on context.

For example, if you are storing phone number around the world the formating will be part of the data because they will vary a lot. In fact not even ascii will sufice and your data will be prone to user typos.

If you are storing a US zip code you just need the number. The application UI can handle all the formating, for input and output and it can deliver sanitized data always for the DB.

If you are storing dates you will use a date type and again let application handle formating AND localization issues.

If you are storing phone number form a country where each region have it's local area code you can put that local codes in a separate table.


How do you kick users out of a SQL Server 2008 database? We need to do a restore, and cannot because other users are connected.
We thought we had disconnected every process, but apparently not.

How can we, from Management Studio, kick off everyone else so we can do this backup?

I always use the following:

USE master; -- get out of dbname myself
GO
-- kick all other users out:
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- prevent sessions from re-establishing connection:
ALTER DATABASE [dbname] SET OFFLINE;

Sometimes this may take a while, and sometimes it is blocked because you're the one running it, and you have an active connection to the database. Check for other query windows that might have the same database context - this can include open dialogs, Object Explorer, IntelliSense, long-running jobs, etc.

When I'm done making my changes to that database's config, I simply:

ALTER DATABASE [dbname] SET ONLINE;
ALTER DATABASE [dbname] SET MULTI_USER;

Though, sometimes, the thing I need to do to that database requires the database to be online, so sometimes I have to leave it in single-user mode and do this:

ALTER DATABASE [dbname] SET ONLINE;
GO
USE [dbname];

Now I can make my changes, and then when I'm ready for other users to connect, simply:

ALTER DATABASE [dbname] SET MULTI_USER;

I use this code:

ALTER DATABASE [Dbname] set offline with rollback immediate
GO
ALTER DATABASE [Dbname] set online
GO

But I can see the SINGLE USER example is less to type.


How to write a query in SQL Server to find nearest values Let's say that I have the following integer values in a table

32
11
15
123
55
54
23
43
44
44
56
23

OK, the list can go on; it doesn't matter. Now I want to query this table and I want to return a certain number of closest records. Let's say I want to return 10 closest record matches to the numer 32. Can I achieve this efficiently?

It is in SQL Server 2014.

Assuming the column is indexed the following should be reasonably efficient.

With two seeks of 10 rows and then a sort of the (up to) 20 returned.

WITH CTE
     AS ((SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC

(i.e. potentially something like the below)

Or another possibility (that reduces the number of rows sorted to max 10)

WITH A
     AS (SELECT TOP 10 *,
                       YourCol - 32 AS Diff
         FROM   YourTable
         WHERE  YourCol > 32
         ORDER  BY Diff ASC, YourCol ASC),
     B
     AS (SELECT TOP 10 *,
                       32 - YourCol AS Diff
         FROM   YourTable
         WHERE  YourCol <= 32
         ORDER  BY YourCol DESC),
     AB
     AS (SELECT *
         FROM   A
         UNION ALL
         SELECT *
         FROM   B)
SELECT TOP 10 *
FROM   AB
ORDER  BY Diff ASC

NB: Execution plan above was for the simple table definition

CREATE TABLE [dbo].[YourTable](
    [YourCol] [int] NOT NULL CONSTRAINT [SomeIndex] PRIMARY KEY CLUSTERED
)

Technically, the Sort on the bottom branch shouldn't be needed either as that too is ordered by Diff, and it would be possible to merge the two ordered results. But I wasn't able to get that plan.

The query has ORDER BY Diff ASC, YourCol ASC and not just ORDER  BY YourCol ASC, because that was what ended up working to get rid of the Sort in the top branch of the plan. I needed to add the secondary column in (even though it won't ever change the result as YourCol will be the same for all values with the same Diff) so it would go through the merge join (concatenation) without adding a Sort.

SQL Server seems able to infer that an index on X seeked in ascending order will deliver rows ordered by X + Y and no sort is necessary. But it is not able to infer that travelling the index in descending order will deliver rows in the same order as Y-X (or even just unary minus X). Both branches of the plan use an index to avoid a sort but the TOP 10 in the bottom branch are then sorted by Diff (even though they are already in that order) to get them in the desired order for the merge.

For other queries/table definitions it may be trickier or not possible to get the merge plan with just a sort of one branch - as it relies on finding an ordering expression that SQL Server:

Accepts that the index seek will supply the specified order so no sort is needed before the top.
Is happy to use in the merge operation so requires no sort after the TOP

Refinement of Martin's second suggestion:

WITH AB
     AS (SELECT *, ABS(32 - YourCol) AS Offset
         FROM   YourTable),
SELECT TOP 10 *
FROM   AB
ORDER  BY Offset ASC


SQL Server DB restore fails I am trying to do a SQL Server database restore using this T-SQL code:

----Make Database to single user Mode
ALTER DATABASE xyz
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE xyz
FROM DISK = 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\xyz_backup_201204100301.bak'
WITH MOVE 'abc' TO 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.MDF',
MOVE 'abc_log' TO 'E:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.LDF'

but while running the above i get the following error:

  Msg 5064, Level 16, State 1, Line 1
  Changes to the state or options of database 'xyz' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
  Msg 5069, Level 16, State 1, Line 1
  ALTER DATABASE statement failed.

How can I run my restore successfully?
Is restarting the database the only way to solve the problem. Any other suggestions?!

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of
  database 'xyz' cannot be made at this time. The database is in
  single-user mode, and a user is currently connected to it.

This error does not occur if YOU are the single-user in the database.  It only occurs if someone else is in it AND it is ALREADY in single-user mode.

For example, try this:

create database test
--
use test
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Note: there are 4 separate batches, and the 4th one doesn't error.  You're setting the mode while IN the database, and also setting it a 2nd time.  No error.

Assuming you have been given the mandate to restore the DB regardless of what is happening, do an sp_who2 to look for the user who is connected, and KILL the spid.  You can then proceed with the RESTORE.

Assure that you are the only account trying to access the database, then try restarting the database.


SSMS 2014 (and 2012) CTRL+TAB requires pressing enter Sometime last week my CTRL+TAB behavior changed.  I used to hit CTRL+TAB, release, then I would be in my previous document tab.  Now I hit CTRL+TAB, release, and the nav window is still open requiring me to hit enter.

I have tried restarting ssms and rebooting several times.  This is an issue in both SSMS 2012 and 2014.  It is not an issue in Visual Studio (2010 and 2013).

In all VS 2010/2013 and SSMS 2012/2014 the hotkey Ctrl+Tab (Global) is set to Window.NextDocumentWindowNav.  Though I have tried modifying it then putting it back.

CTRL+F6 works the same as it always has.

Is there a way to get the CTRL+TAB behavior back?

To fix this issue: Tools \ Options \ Keyboard.  Bring up the command in the "Show commands containing:" list.  Then in the "Press shortcut keys:" text box press CTRL+Tab and click Assign.  This will re-assign the command back to itself, but fixed the "sticky" window.  Just had the same issue. Hope this helps.


mongodb :: failures in bring up mongo server I am unable to add a replica to the cluster.

Steps tried:

done hot backup for existing member.
Copied it to new replica.
Tried to start the server, it's failing with these errors:

2019-12-31T13:03:38.277+0000 E STORAGE  [initandlisten] WiredTiger error (0) [1577797418:277479][8130:0x7ff6a95f9b80], connection: __wt_cache_destroy, 358: cache server: exiting with 169 bytes in memory Raw: [1577797418:277479][8130:0x7ff6a95f9b80], connection: __wt_cache_destroy, 358: cache server: exiting with 169 bytes in memory
2019-12-31T13:03:38.279+0000 W STORAGE  [initandlisten] Failed to start up WiredTiger under any compatibility version.
2019-12-31T13:03:38.279+0000 F -        [initandlisten] Fatal Assertion 28559 at src/mongo/db/storage/wiredtiger/wiredtiger_util.cpp 65
2019-12-31T13:03:38.279+0000 F -        [initandlisten] aborting after fassert() failure

We are using percona mongodb - 4.0.13-7

What could be the reason?

If you add a new replica node, Mongodb will itself copy the data across to the new node. Unless if your data size is huge and do not want your network to be flooded in transferring the data you can follow the backup method.


Trigger needs dbms_alert declared A trigger has given me the following:

 ORA-04098: trigger 'adminuser.DB_ERROR_CHANGE_TRIG' is invalid and failed re-validation

and this is when working on altering schemas/triggers/etc from an import of a client database.

I ran

show errors trigger adminuser.db_error_change_trig

and the results were:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5      PL/SQL: Statement ignored
2/5      PLS-00201: identifier 'DBMS_ALERT' must be declared

Now, googling, I have found that I need to grant explicit execute privileges to the public user which I did with

grant execute on dms_public to public

However, this has not worked.
How can I determine what is going on, as well as how to fix this.  Thirdly, is dbms_alert schema specific? Meaning, my understanding is the the full name of the object is SYS.dbms_alert, is there another for another schema or is one global one?

I think you may be missing synonym for dbms_alert for adminuser schema. A object grant and synonym both are normally required (unless you own the object).

Try following as sys account, perhaps it will fix the problem.

create or replace synonym adminuser.dbms_alert for sys.dbms_alert;

If you don't want to create a synonym, you should use sys.dbms_alert, if your create a synonym as shown above, using just dbms_alert is sufficient in your code.


Can we disable binary logging for one specific session or user in AWS RDS Currently we have a situation to disable binary logging for one set of queries on AWS RDS. We thought to set log-bin=0 for that specific session. As per Amazon, this is not possible. Is there any work around for us ?

We want binary logging to be working for the remaining activity that happens in parallel
to this session.
If needed, we can stop and restart slave.

Thanks in advance.

One solution proposed in https://forums.aws.amazon.com/thread.jspa?threadID=45258 requires setting backup retention period to zero. Which should mean no automatic backup! But you can still take manual snapshots.

I apologise if I misunderstood (I don't use Amazon RDS). But can you use:

SET SESSION SQL_LOG_BIN = 0;

-- DO STUFF HERE

SET SESSION SQL_LOG_BIN = 1;

After setting it to 0, it will prevent everything in your current session being written to the Binary logs. Setting it back to 1 will resume writing to be binary logs. Everything else continues as per normal.


Using DateTime Variable In Where Clause I am trying to pull all data where a sale date is >= to a certain datetime.  When I execute my query I get an error of:

  Msg 241, Level 16, State 1, Line 15
  Conversion failed when converting date and/or time from character string.

What should I alter to get his query operational?
Below is sample DDL

Declare @datetime datetime = GetDate(), @sql varchar(max)

Create Table #dinonuggets
(
  saledate datetime
  ,item varchar(500)
)

Insert Into #dinonuggets Values
('2016-03-30 12:02:00.000','chair')
,('2016-03-04 08:12:48.000','stove')
,('2016-03-07 09:37:39.000','washer')

Set @sql = 'Select * FROM #dinonuggets WHERE saledate >= '+@datetime+' '
Print @sql
Exec (@sql)

My expected output would be
03/30/2016 12:02:00.000 chair
 as that is the only data that meets the criteria

Your issue relates to the way you are building your dynamic SQL. If you look at the output from your PRINT statement, there are no quotes around your @datetime.  Additionally, selecting from a #temp table within dynamic SQL isn't going to do what you want it to do. #dynonuggets is going to be out-of-scope for your dynamic SQL. If you really do need to work with a dynamic table, you'll need to make a global temporary table, named ##dynonuggets.

I'll assume, DynoNuggets is a real table, and you have to use dynamic SQL, and your example is just simplified for your question.

You could add quotes, but the more appropriate solution is to use sp_executesql to parameterize your dynamic SQL.

Try this:

DECLARE @datetime datetime = GETDATE(),
        @sql nvarchar(max),
        @params nvarchar(1000)

CREATE TABLE DinoNuggets
(
  saledate datetime
  ,item varchar(500)
)

INSERT INTO DinoNuggets VALUES
('2016-03-30 12:02:00.000','chair')
,('2016-03-04 08:12:48.000','stove')
,('2016-03-07 09:37:39.000','washer')

SET @sql = N'SELECT * FROM DinoNuggets WHERE saledate >= @ParamValue';
SET @Params = N'@ParamValue datetime';
PRINT @sql;
EXEC sp_executesql @sql, @params, @ParamValue = @datetime

Declare the @datetime as a datetime2.

Then alter the @sql query to:-

Set @sql = 'Select * FROM #dinonuggets WHERE saledate >= ''' + cast( @datetime as varchar(23)) + ''' '

This will get around the date formating issue as well as allowing the correct string concatenation.
Without the cast you would receive this error:-

  Msg 402, Level 16, State 1
  The data types varchar and datetime2 are incompatible in the add operator.


Backup a portion of the database? I have a database that is over 50Gb. I'd like to take a backup of this database, but I don't want a full backup. I need to extract some of the data for a development test db. Is this possible?

Using the SQL Server management console, you can choose the database and Export option to export the table definition and data. Then, you can import this data to your test data base.

Hope you have already created the test database manually. While importing data, it will automatically create the tables, if they are not already exists.

You can only back up the entire database if you intend to restore it elsewhere.

If the database consists of multiple datafiles you can back these up separately but all the files are needed to restore.

You may be better off setting up snapshot replication for the tables you are interested in.


psql: id SERIAL PRIMARY KEY not starting with 1? This is something I don't understand, can somebody explain?

I create a table

CREATE TABLE cali (id SERIAL PRIMARY KEY,
    ALK_from char,
    ALK_to char,
    AND_from char,
    AND_to char);

Then I simply try to insert some data from a csv file like so

\COPY cali FROM '/home/.../data/output/id_cali.csv' (FORMAT CSV);

Which gives me this Error:

ERROR:  value "4372840000" is out of range for type integer
CONTEXT:  COPY cali, line 1, column id: "4372840000"

I know that 4372840000 results in an integer overflow. That is why I'm trying to insert the values as chars in the first place.

But my understanding is that id SERIAL PRIMARY KEY is some sort of auto increment why is the value 4372840000 used for the column id?

The file looks like this

4372840012,4372840013,13,14,
4372840014,4372840014,15,15,

The table should be
create table cali (
    id serial primary key,
    alk_from bigint,
    alk_to bigint,
    and_from int,
    and_to int
);

And do
\COPY cali (alk_from, alk_to, and_from, and_to)
FROM '/home/.../data/output/id_cali.csv'
(FORMAT CSV);

So that it knows to not insert into the id serial column. The serial type is not a true type. From the manual:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)
Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator

and for your problem you should take columns datatype as bigint so you
  will not get this error.

  you can not insert value in primary key column which is
  autoincremented

CREATE TABLE cali (id SERIAL PRIMARY KEY,
        ALK_from bigint,
        ALK_to bigint,
        AND_from bigint,
        AND_to bigint);


Does anyone deployed a succesful SSAS on docker? I'm trying to create a docker image that contains MS SQL server, analysis services, and restore a db (bak) and a cube (abf). The Sql server part is the easy stuff, and I imagine that the restore will be too quite simple... the unsuccessful part is the installation of SSAS.

I've already tried without success:

Splitting images (1 for MSSQL and 1 for SSAS)
installing from the inside of a container with SQL Server installed
trying differents account (NT AUTHORITY\NETWORK SERVICE,BUILTIN\ADMINISTRATORS)

Any chance that anyone as done something similar or is "impossible" to achieve such a [working] image?

This is a clean sample of what I'm doing:

FROM mcr.microsoft.com/windows/servercore:1903

ENV exe "https://go.microsoft.com/fwlink/?linkid=840945"
ENV box "https://go.microsoft.com/fwlink/?linkid=840944"

ENV sa_password="_" \
    attach_dbs="[]" \
    ACCEPT_EULA="_" \
    sa_password_path="C:\ProgramData\Docker\secrets\sa-password" \
    appname="demo"

SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

WORKDIR /

RUN Invoke-WebRequest -Uri $env:box -OutFile SQL.box ; \
        Invoke-WebRequest -Uri $env:exe -OutFile SQL.exe ; \
        Start-Process -Wait -FilePath .\SQL.exe -ArgumentList /qs, /x:setup ; \
        .\setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQL,AS,Tools /UPDATEENABLED=0 /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS /BROWSERSVCSTARTUPTYPE=Automatic /ASSVCSTARTUPTYPE=Automatic  /SQLSVCACCOUNT='domain\user' /SQLSVCPASSWORD='somepassword' /SQLSYSADMINACCOUNTS='domain\user' /ASSVCACCOUNT='domain\user' /ASSVCPASSWORD='somepassword' /ASSYSADMINACCOUNTS='domain\user' ; \
        Remove-Item -Recurse -Force SQL.exe, SQL.box, setup

Windocks offers Windows SQL Server containes, and plans to release support for SSAS and SSIS early in 2020 (ie., Jan).


How to Dynamically change the database using TSQL I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL:

EXEC sys.sp_executesql N'USE db1 ' ;

It executes successfully however the database context of SSMS doesn't change.

I've tried a slight modification to the above like so

DECLARE @sql NVARCHAR(100) DECLARE @db NVARCHAR(50)
SET @db = N'db1' SET @sql = N'Use ' + @db
EXEC sp_executesql @sql

Again, it executes successfully, but the database doesn't change.

SSMS WILL NOT, I REPEAT, WILL NOT SWITCH TO THE CONTEXT OF A USE COMMAND YOU RUN IN DYNAMIC SQL.
If the ultimate goal is to execute some other dynamic SQL inside the chosen database, this is easy enough:
DECLARE @db sysname = N'db1';

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'SELECT DB_NAME();';

EXEC @exec @sql;

If you need to pass parameters, no problem:
DECLARE @db sysname = N'db1', @i int = 1;

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'SELECT DB_NAME(), @i;';

EXEC @exec @sql, N'@i int', @i;

If the goal is to execute some static SQL inside the chosen database, maybe you should consider storing that static SQL in a stored procedure in each database, and calling it dynamically like this:
DECLARE @db sysname = N'db1';

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'EXEC dbo.procedurename;';

EXEC @exec @sql;

And hopefully the ultimate goal is not to run all this code in SSMS just so that SSMS is now in the context of @db... Daniel would really like it if I state explicitly that this is not possible, as @Lothar's comment also stated.

There is undocumented sp_MSforeachdb procedure:
DECLARE @TSQL nvarchar(2000)

SET @TSQL = '
IF ''?'' like ''mydbset-%''
BEGIN
   USE [?];
   PRINT ''********** database: [?] ************''
   select count(*) from information_schema.tables; -- ANY SQL or SP Call in database
END
'
-- Executing TSQL for each database
EXEC sp_MSforeachdb @TSQL


Error 3154 while restoring a backup using WITH REPLACE I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak.

I have a database with the name test2 which is the same database, but the data changed.

I want to restore test.bak over test2 database.

I am always getting the error:

  Error 3154: The backup set holds a backup of a database other than the existing database.

I tried:

I right-cliked on test2 -> Restore database -> From device

I chose test.bak and checked With Replace but I get the error.
Then I tried to right-click on test2 -> Restore file and filegroups

I chose test.bak and checked With Replace but I get the error.

I can delete my old database and then restore my backup with the right name, but when I was using SQL 2008, I had no problem restoring over an existing database.

It seems that since I use SQL2012, I get this error a lot!

You should use WITH REPLACE and in general avoid using the point-and-click thingies in Management Studio - they're inflexible and often have bugs.

This worked for me:

USE [master];
GO

CREATE DATABASE test;
GO

CREATE DATABASE test2;
GO

BACKUP DATABASE test TO DISK = 'c:\temp\test.bak' WITH INIT, COMPRESSION;
GO

RESTORE DATABASE test2
  FROM DISK = 'c:\temp\test.bak'
  WITH REPLACE,
  MOVE 'test' TO 'c:\temp\test2.mdf',
  MOVE 'test_log' TO 'c:\temp\test2.ldf';

Also you should make sure when you backup databases you use WITH INIT and/or don't point the device at a file that already contains a backup (since it might not be the same database you're backing up now - especially if you reuse names like test...).

Please stop your running server and then restore db, it will help you out. :)


MySQL Logical grouping of tables I am coming from MSSQL world and learning MySQL now. I see that schema and database is same in MySQL, however these are different in MSSQL.

I want to logically group my tables as can be done using schema in MSSQL. How can I achieve this?

Edit 1

As per comments, I can't use schema to logically group my tables. Is there any other system which I can use? Maybe using a prefix in table names?

Edit 2

My use case is, I am designing a big software which would have multiple modules. Each of the modules would have multiple tables. I want these tables segregated from each other. Although all the tables would be used for this software only, but they should be kept apart. E.g. Employee module tables should be separate from Inventory module tables.

i like to tell you in mysql , its also schema and database are different things.

schema is present under database.

means in two different database, same named schema may present.

i.e; in any one database this may possible -

create new schema sch1;
CREATE TABLE sch1.pet (name VARCHAR(20), owner VARCHAR(20),

create new schema sch2;
CREATE TABLE sch2.pet (name VARCHAR(20), owner VARCHAR(20),

in any 2nd database which having different connection ip have

above same schema  and  may possibly present..


Use CASE to select columns in UPDATE query? I can use CASE to choose which columns to display in a SELECT query (Postgres), like so:

SELECT CASE WHEN val = 0 THEN column_x
            WHEN val = 1 THEN column_y
            ELSE 0
       END AS update, ...

Is something similar at all possible when performing an UPDATE query in Postgres (i.e. choose which columns should be updated)? I assume not since I couldn't find anything about this, but maybe someone has a clever alternative (besides using a procedure or updating each column using a CASE to determine if the value of the column should be assigned a new value or simply reassigned the existing value). If there is no easy alternative, I'll of course accept that as an answer as well.

Extra info: In my case I have 14 potential columns that may be updated, with only one being updated per matching row (the table to be updated is joined with another in the query). The amount of rows to update will most likely vary, could be dozens or hundreds. I believe indexes are in place for the joining conditions.

If you specify a column should be updated then it will always be updated, but you can change the value you put in conditionally and put back the original values depending on your conditions. Something like:

UPDATE some_table
SET    column_x = CASE WHEN should_update_x THEN new_value_for_x ELSE column_x END
     , column_y = CASE WHEN should_update_y THEN new_value_for_y ELSE column_y END
     , column_z = CASE WHEN should_update_z THEN new_value_for_z ELSE column_z END
FROM   ...

So if the conditions are not right for an update to a particular column you just feed back it's current value.

Do note that every row matched will see an update (even if all the columns end up getting set to the values they already have) unless you explicitly gate this circumstance in you filtering ON and WHERE clauses, which could be a performance problem (there will be a write, indexes will be updated, appropriate triggers will fire, ...) if not mitigated.

update Practicing  -- table you will be updating
 set email = case -- column you will be updating
    when FName = 'Glenn' then 'Ace@Assasin.com'
       when FName = 'Riddick' then 'fallguy@Drunk.com'
       when FName = 'Jeffrey' then 'sorcerer@wizcom'
       else email
    end


How can I check for stored procedure consistency across multiples servers I've got a stored procedure on 10 distinct servers, how can I check across all servers for stored procedure consistency?

I'm currently pulling the definition for each server manually, and comparing it in notepad++ compare tool.

It's quite time consuming, and surely I'm not the first one to have this problem.

Kankuru is a great and FREE monitoring tool for SQL Server developed here in France. Aside from its monitoring capabilities, it also has a DB compare tool that is easy and simple to use.

It will not only show you if your stored procedures, tables, indexes, types, etc. exist on two servers, but also what those differences are between those two servers.

Here is another compare screen

It's very easy to use and as mentioned before, won't cost you a dime.

To setup Kankuru

Create an empty database in some SQL Server called Kankuru. The kankuru database just stores configuration information and some history and its very compact.
Start Kankuru and from the Home Menu select Configuration

Configure kankuru to use the server\instance and database that you just created.

There is documentation, but its in French. Google translate will be able to get you through one way or another.

One option to consider is to use SQLCMD to execute code on multiple servers from a single source.  You further could log the process for each server by qualifying a distinct log file on each instance calling the code.


limiting sql server backups inside .bak file I have a job scheduled to run a few times everyday. The job backups my databases, like:

BACKUP DATABASE [databasename]
TO DISK = N'd:\sqlserver\backup\databasename.bak'

I can track the backups in the file by running:

RESTORE HEADERONLY FROM DISK = N'd:\sqlserver\backup\databasename.bak'

And then I can restore a database file by using something like:

RESTORE DATABASE databasename
FROM DISK = N'd:\sqlserver\backup\databasename.bak'
WITH FILE = 1455,
RECOVERY,
MOVE 'databasename_data' TO 'c:\...',
MOVE 'databasename_log' TO 'c:\...'

But I have thousands of backups, and I'm reaching my storage limit.

Because I don't need so many backups, I'm wondering if there is a easy way to limit the backup file to keep only the last 90 backups.

Any tips?

UPDATE:

I have just one .bak file with n backups inside. I can read a specific backup using the withfile option in the restore statement. The bellow answers are supposing I have multiple .bak files. What I need to delete is a outdated backup inside the .bak file.

The easiest solution would be using a maintenance plan task to clean up old backup files. However, you could consider scripting your way out of this, using something like the following script:

--====================================================================
CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime

SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
END
--============================
-- xp_delete_file information
--============================
-- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such
-- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is
-- not documented by MS. Just be aware that it will not delete just any file type

-- First argument is:
-- 0 - specifies a backup file
-- 1 - specifies a report file
-- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files
-- you're deleting with the third argument.)
--
-- Fifth argument is whether to delete recursively.
-- 0 - don't delete recursively (default)
-- 1 - delete files in sub directories
--====================================================================

Credit: http://www.sqlservercentral.com/Forums/FindPost1034057.aspx

There's also xp_delete_file:

master.dbo.xp_delete_file

If you use the SQL Server Maintenance plan wizard to create your jobs to back up the databases you can set an Expiration date on your backup set.

click on the link below it explains hows its done.

Set Expiration Date on Backup set


Include Columns in Index : SQL Server I have one question regarding include column in the indexes. If I have an include column which is updating its value quite often, does it update the index every time it's updating the value or does the index only update when key columns are updating? Does include column updates add performance issues? According the example below, if the Balance and DiscountedAmount keep updating does it update the index IX_Temp_RegistrationNumber for every updates?

CREATE TABLE #Temp (
    TheID BIGINT
    ,RegistrationNumber BIGINT
    ,ClientName VARCHAR(50)
    ,Balance NUMERIC(10, 2)
    ,DiscountedAmount NUMERIC(10, 2)
    )

CREATE NONCLUSTERED INDEX [IX_Temp_RegistrationNumber] ON [#Temp] ([RegistrationNumber] ASC) INCLUDE (
    [Balance]
    ,[DiscountedAmount]
    )
GO

Actually if you get Actual Plan, you will be able to see, indexes that modified by every update.

When you do update, Cluster Index and all indexes that keys and included column , had updated, will be modified.

Example 1 :
update #Temp set ClientName = 'foo'
Execution plan : The clustered index will be update

Example 2:
update #Temp set Balance = 'foo'
Execution plan : The clustered index and [IX_Temp_RegistrationNumber] will be update.

Tip :If your table haven't so many indexes or update operations isn't high, then there isn't any performance issue.


Database design for big table I'm handling a big table which is at 200GB+ and has around 1 billion rows.

I'm looking into splitting the table into smaller tables and then joining the results whenever I query.

We do mainly reading on that table with occasional writing.

It it a good idea? Or, is it something I need not worry?

My database server has 4CPU and 32GB RAM.

I have the primary key as index to save space. Currently, there are no issues. However, I just considering whether to split it earlier or just keep it this way. What are the pros and cons of splitting into smaller tables with , let's say, 250 million rows each instead of plain 1 billion rows table? Do you think my 32GB RAM will be able to handle it?

There are a couple of patterns for splitting a single logical entity across several physical tables.

Vertical partitioning puts some of the columns in one table and some in another. There can be several such additional tables, if required. All these tables share the same primary key. Columns that are used together are stored together so one page fetch read all the required values. The advantage is there are more rows per page so scans and aggregates require fewer IOs. Sometimes it can be tricky deciding which columns should go together. Every INSERT becomes proportionately more expensive as the number of partitions increases.

Horizontal partitioning splits the data by key range. For example, all users with a surname beginning A through M go into table User_1 and N through Z go into User_2. The application figures out which sub-table to use at run-time, usually algorithmically or through a look-up, though most DBMS these days will offer this as a build-in feature, implemented through DDL, transparent to the application. If you have hot spots in data writes this can spread the pain out. The optimiser can eliminate whole partitions from range scans, improving response time. Loading and removing whole partitions can be very fast metadata operations.

Sharding is where key ranges are moved, not just to a different table, but to a whole different instance of the DBMS. The application decides which instance to connect to, depending on key range. This is a scale-out technique. Some DBMS support this as a feature, such as Galera for MariaDB. The obvious cost is in additional hardware to run the other instances, duplication of reference data across all nodes to maintain RI and application complexity.

The techniques may be combined, of course, so a table could be both horizontally and vertically partitioned.

General advantages of splitting:

Indexes may becomes shallower, saving costs on index look-ups.
Each partition can write to separate disk, spreading IO load.

Disadvantages include:

Application complexity
It will be more difficult to ensure data integrity
Upgrades become trickier.
Purging data is incrementally more difficult to get right.

Basically, if you don't have a problem, and don't see one coming, don't bother. Horizontal partitioning using DBMS functionality is the only one worth considering as a pre-emptive measure. Even then it will require a re-write of 200GB. Do you have the disk and service window to accomplish this?

I would suggest you use table partitioning


Autocomplete too slow: possible optimizations? The autocomplete search functionality of my website searches a varchar field which contains the model number of sale items.

This field can contains strings varying from 1 to 75 characters and the table contains 400 000 rows. I have come up with a query that searchs only from the start of the string and that takes about 150-250ms to execute which is acceptable but now my manager wants the query to search for any substrings which makes the query 3-10 times slower (around 1000-2000ms).

I have built a JS fiddle to give you an example of what the data looks like and what the two query are like.

http://sqlfiddle.com/#!6/9efa3/2/0

There already are a few indexes on the table. What would be the best practice to speed-up this auto-complete search field ?
(database version is SQLSERVER 2008R2)

Here is a brief example of the data I'm dealing with :

CREATE TABLE [Products](
    [productid] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [model] [nvarchar](75) NOT NULL
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
    [productid] ASC
));

insert into products values ('UMPX1AA0011 danish e-315 woot');
insert into products values ('P27y719VC');
insert into products values ('VG2y439m-LED');
insert into products values ('UMUyX165AAB01');
insert into products values ('U28y79VF');
insert into products values ('U52417HJ');
insert into products values ('VA25746M-LED WITH FLYING CORNERS');
insert into products values ('S19F350HNN 1pc california storage');
insert into products values ('VA211917A');
insert into products values ('PM2500X2');
insert into products values ('E22470SWHE');
insert into products values ('V22465WLYDP');
insert into products values ('I129LMH1HKC');
insert into products values ('OM5EN X 35 the new version');
insert into products values ('DLS3060WDB');
insert into products values ('PVW');
insert into products values ('LI23721S');
insert into products values ('V173516LBM');
insert into products values ('VX2376-SMHD-A');
insert into products values ('GUM5FX1AA1001');
insert into products values ('GPM300X11');
insert into products values ('GUM-WH6AA002');
insert into products values ('2435V5LSB');
insert into products values ('P2418HZ');
insert into products values ('Stylish sectional one of a kind y-5151');

and these are the two queries I'm comparing

--runs acceptably fast, about 100-250ms
select * from products where model like 'y-5151'+'%';
--takes too long, around 1000-2500ms
select * from products where model like '%' + 'y-5151' +'%'

The solution I went with is to build a "half-triangram" table which contains a pre-processsed version of all the model # substrings as suggested by Aaron Bertrand in the two following blog posts of his :

https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server

https://sqlperformance.com/2017/02/sql-performance/follow-up-1-leading-wildcard-seeks

The solution for me was to create a new table where I look for models starting with the search string. Each model is listed as such, let's say the product_ID was 51 and the model was 7500 Twin Bed

ID|Model
----------------
51|7500 Twin Bed
51|500 Twin Bed
51|00 Twin Bed
51|0 Twin Bed
51| Twin Bed
51|Twin Bed
51|win Bed
51|in Bed
51|n Bed
51| Bed
51|Bed
51|ed
51|d

This way you never need to do a full wildcard search, a simple select distinct id_product from products_dictionary where model like 'Twin%' will return the required results. The query now takes less than 100ms.

Here is the code I used to create the table and populate it. Again, this is all described properly on Aaron's blog post:

CREATE TABLE [dbo].[products_dictionary](
    [Id_product] [int],
    [model] [nvarchar](75) NOT NULL
)
insert into [products_dictionary]
select p.id_product,f.fragment from products p
cross apply dbo.CreateStringFragments(p.model) AS f;

create clustered index index_idprod_substrmodel on [Products_dictionary]([model],[Id_product])

There is no real way to speed this up. "In the middle" means no index will be used, period. (as in: this is the way sql server is built).

What you could do is load those strings onto a separate table and then optimize your sql to not issue any locking statements (with reacommitted). An in memory table may be the next step.

There is no way a search on 400.000 strings of max 80 characters takes 2.5 seconds - there is locking congestion or other things going on. Make sure you set proper locking hints.


How to optimize this Select MAX() SQL query? In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this query?

SELECT MAX(StartDate) AS Expr1
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1

I already tried with the following, which also takes 1 min to get the result

SELECT TOP(1) *
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1
ORDER BY StartDate DESC

Is there another way to optimize the query?

As mentioned in the comments, an index on StartDate will let your query seek right to the row it needs, rather than scanning the whole table or another index (which is what it's likely doing right now):

CREATE NONCLUSTERED INDEX IX_StartDate
ON dbo.ProductActivity (StartDate DESC);

Like any other index, this one will need to be maintained anytime a row is inserted into, or deleted from, the ProductActivity table.  And anytime the StartDate column is updated (although that seems unlikely based on the name alone).

This doesn't seem like it would add a lot of overhead, but you should test with your workload to be sure.  If the MAX(StartDate) is needed very often, it's probably worthwhile to keep this index around.

If there are lots and lots of writes to this table (hundreds or thousands per second), and the MAX(StartDate) isn't needed often, then you might be better off just periodically waiting the 1 minute or so for that query to finish rather than paying the overhead on each write.

Easiest way is to create an index that supports your query.

As you mentioned there is no supporting index. I suggest to use query hint option(maxdop noofcpucore) to make your query go parallel, where noofcpucore = numeric value, equal to the number of cpu cores that will be used by the query (for example 4).


Prevent UPDATE TRIGGER to fire when no changes made in SYBASE In Sybase I have a main table test and an audit table test_a. The audit table is updated by triggers on insert, update and delete. Now the issue is, when I use update query with old data (no changes), the trigger still gets fired and logs in audit table, which I don't want (to prevent duplication when no changes).

test has

Id | NAME | DESC

test_a has

updated_by | date | Id | NAME | DESC

This is the update trigger I have:

create trigger test_utrig on test for update as
insert into audit..test_a select 'update',update_by(),getdate() inserted.* from inserted

I tried to use if (update(Id) or update(NAME) or update(DESC)), but still the update trigger get fired on no changes. Please help me how to stop trigger from firing when no changes are done.

I don't know SyBase but from the documentation, it appears as if you can check whether one or more columns are updated. It should be sufficient to check if one is updated like:

create trigger test_utrig on test
for update as
    if update (id)
        insert into audit..test_a (updated_by, date, Id, ,NAME, DESC)
        select update_by(), getdate(), inserted.Id, inserted.NAME, inserted.DESC
        from inserted

It appears to be a mismatch between the number of columns in the trigger and in the audit table, so I removed the constant 'update'.

Got the solution:

CREATE TRIGGER test_utrig
ON test
FOR UPDATE
AS
if UPDATE(id) and exists(select id from inserted where id not in (select id from inserted))
begin
insert into test_a select
  'update',
  UPDATE_BY(),
  GETDATE(),
  inserted.*
FROM
  inserted


PostgreSQL - Base Backups and PITR According to the PostgreSQL documentation for producing Standalone Hot Backups:

These are backups that cannot be used for point-in-time recovery, yet
are typically much faster to backup and restore than pg_dump dumps.

It seems that all that is required to produce these kinds of backups is to use pg_basebackup utility and include the -X option, which essentially includes the required WAL files necessary to start the cluster. Why can't these backups be used for PITR? If I don't start this backup immediately, shouldn't I be able to place it in recovery mode and allow it to restore up to any point after the backup was created?

I agree that the wording is confusing.
A pg_basebackup taken with -X stream can of course be taken as a basis for point-in-time recovery.
I think the assumption here is that you take a pg_basebackup but do not have WAL archiving configured. Then the backup contains just the WAL segments required to recover to the end of the backup and reach a consistent state, but you don't have the WAL required to recover beyond that point.

That seems like a doc bug, in that -X needs an argument, and it doesn't clarify what options you are supposedly using for it.  The description it gives would seem to apply to either fetch or to stream, but not apply to none.
You can use such a basebackup generated with -X stream or -X fetch for PITR as long as also have a WAL archive.  I think the assumption here is that you would not use those options if you were keeping a wal archive anyway.  I think that is a bad assumption.  I like my backups to be as flexible as possible.  If your backup includes the minimal set of its own WAL, then you can either restore it standalone for a single time point, or you can use it to seed PITR with the wal archive.  This seems useful to me.  Actually this is your bad assumption, not the doc's.  You have juxtaposed two different parts of the docs, talking about two different things (pg_dump and pg_basebackup), as if they were talking about the same thing.


How to recompile all stored procedures and table valued functions in SQL Server database? In a SQL Server database sp_recompile can be run on a stored procedure to update the execution plan.  I would like to run this on all stored procedures in a database.  Also, I would like to run its equivalent on all table-valued functions but I do not know which sys procedure to run.

Is it possible to do this without manually typing out a sp_recompile line for all of the stored procedure names in SQL Server Management Studio?  Likewise for the table valued functions?

I believe I need to do this because the VM SQL Server has had its memory significantly increased but I am only seeing a marginal increase in execution time. The execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures.

You can run sp_recompile on everything by using a cursor to produce ad-hoc SQL for each and run it, if you think that will help:

DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF'));
DECLARE @name SYSNAME;
OPEN C;
FETCH NEXT FROM C INTO @name;
WHILE @@FETCH_STATUS=0 BEGIN
    EXEC sp_recompile @name;
    FETCH NEXT FROM C INTO @name;
END;
CLOSE C;
DEALLOCATE C;

or you could produce ad-hoc SQL and run that via EXEC, takes less code which might be marginally more efficient:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_recompile '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF');
EXEC (@sql);

(though I find this form sometimes throws people due to looking set-based but building the string up iteratively, and not being a standard SQL pattern)

Another set of objects that might be a similar concern here is views. You can similarly mark them as needing to be reassessed to make sure stored plans and other meta-data is not stale with sp_refreshview, by small modifications to either the cursor or ad-hoc SQL methods shown above:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_refreshview '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('V');
EXEC (@sql);

  The execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures.

There is sometimes more to optimisation than preferring seeks over scans and so forth, sometimes an index scan is more efficient than many executions of seek operations, and the cost estimates upon which the percent figures you are looking at are calculated are that (estimates) at best (a useful guide but sometimes far from at all accurate).

While "throw more memory at it" can help some database performance issues, at least temporarily, if your bottlenecks are very CPU bound rather than memory and/or IO bound then adding more memory will have very little effect.

Building on David Spillett's answer, if running SQL Server 2017 or newer you can use the String_Agg function and further simplify:

declare @sql nvarchar(max) = (select String_Agg('exec sp_recompile '''+[Name]+'''', Char(10)) from sys.Objects where [Type] in ('P', 'FN', 'IF'));
exec (@sql);


Difference between database vs user vs schema I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)?

If they are same then, what are the similarities between them? How do we use them? And how do we create them?

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

See this post on Stack Overflow: difference between a User and a Schema in Oracle? for more details and extra links.

You create users with the create user statement. This also "creates" the schema (initially empty) - you cannot create a schema as such, it is tied to the user. Once the user is created, an administrator can grant privileges to the user, which will enable it to create tables, execute select queries, insert, and everything else.

The database is the thing that contains all the users you've created, and their data (and a bunch of predefined system users, tables, views, etc. that make the whole thing work). You should look at the Oracle Database Architecture documentation in the Concepts Guide (actually, that whole page is worth a read - there's a section about users and schemas higher up in that page) to get an introduction to what a database is, and what a database instance is - two important concepts.

You can create a database with the create database statement, once you've installed the Oracle software stack. But using dbca (database creation assistant) is easier to get started.

User!= Schema , User and Schema are not same but they are used interchangeably


Computing date from record fields In an SQLite DB I have a table with a station integer field and an at date field.

How can I combine these two values to obtain, in a WHERE clause a date which is at plus station months?

I.e. for a record with at = '2019-01-01' and station = 3, my WHERE clause should look like WHERE before <= '2019-04-01'

I have looked at several resources online, including https://www.sqlite.org/lang_datefunc.html but no one refers to using filed value to compute a date.

You can use the following:

 WHERE before <=  date(at, (station || ' months') )

Please note that according to the documentation:

  "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.

as in my comment : https://stackoverflow.com/questions/1845563/sqlite-equivalent-of-sql-server-dateadd-function

->

WHERE before<=date(at,'+'+station+' month')

if that doesn't work - try building '+'+station+' month' in a concat statement

date will give you GMT / UTC based value - if you need it for local timezone, use

DATETIME(at,'LocalTime', '+'+station+' month')


Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock? Can two or more instances of the following deadlock with one another?

START TRANSACTION
SELECT mycolumn FROM mytable WHERE myid IN (list-of-ids) FOR UPDATE
:
COMMIT

Points to keep in mind:

mycolumn is indexed
This is mariaDB/innoDB
There is only one table involved (mytable)

Example A:

Thread 1:

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Thread 2: (note that IN-list is in opposite order)

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (200,100) FOR UPDATE
    :
    COMMIT

Could these threads possibly deadlock on the SELECT in mariaDB/innoDB?

Example B:

Thread 1:

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Thread 2: (note that IN-list is in same order)

    START TRANSACTION
    SELECT mycolumn FROM mytable WHERE myid IN (100,200) FOR UPDATE
    :
    COMMIT

Could these threads possibly deadlock on the SELECT  in mariaDB/innoDB?

UPDATE: A traditional way of avoiding deadlocks is to be sure to acquire locks in the same order, so that you can't have Thread 1 owning lock A and waiting on lock B while Thread 2 owns lock B and is waiting on lock A. However, I've seen several posts suggesting that even getting a single lock can't be considered atomic, because there are asynchronous processes going on in the engine that could still result in deadlock (and as a corollary to this, advising developers to always expect deadlocks and recover from them by retrying the transaction). I'm really asking whether deadlock can be eliminated by using proper coding practices in innoDB.)

This thread is discussing one example of deadlocks.  There are dozens, maybe hundreds, of ways to get a deadlock.

Sure, sorting the ids might prevent the IN(100,200) case, but what about all the rest?  Sure, go ahead and sort the ids.  And be sure to have suitable indexes.  And so on.  These might at least diminish the frequency of deadlocks.

Another example of a deadlock cause:  Lack of an index that leads to a table scan instead of an index lookup.  I think this can lead to locking all rows in the table, thereby both slowing things down and by having more locks, thereby begging for a deadlock.

I'll tell you what I have said many a time:  Deadlocks happen; live with it.  Be ready to restart the transaction from the BEGIN.

If you ever get a second deadlock, you should probably abort the program and spend some quality time figuring out how to speed up the entire transaction.  That is the best way to minimize deadlocks.

(OK, I'll admit that I don't know the definitive answer about IN().  Long ago (ver 4.1?), I thought I had proof that sorting was beneficial.  Later I heard that MySQL sorted the IN list, thereby killing my theory.  Or is all this hearsay evidence?)

What happens if you run 2 sessions concurrently, selecting the 2 data sets (myid = 100 / myid = 200) in reverse order, eg:

-- session #1 runs:
start transaction;
select mycolumn from mytable where myid = 100 for update;
do sleep(30);
select mycolumn from mytable where myid = 200 for update;
commit;

And while session #1 is sleeping:

-- session #2 runs:
start transaction;
select mycolumn from mytable where myid = 200 for update;
do sleep(30);
select mycolumn from mytable where myid = 100 for update;
commit;

Do you get a deadlock?

NOTE: (Obviously?) You can run the above test, selecting myid=100/200 in the same order, to see what happens re: deadlocking.


Full backup on database creation When new databases are created the Differential and transaction log backup maintenance plans start failing and sending alerts as full backup is not taken. What could be the best solution to handle it.

I tried to do full backup on database created trigger but that is failing as backup cannot be taken in the same transaction.

To achieve this task kindly follow below steps:-

create server level trigger on create_database.
Create sql job and add code to dynamic get the name of database and initiate backup.
Add Code in the trigger to invoke job to create database backup.

e.g

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
   EXEC msdb.dbo.sp_start_job N'backup_job' ;
GO

I would generally recommend not to use the built-in maintenance plans in SQL Server.

I found Ola Hallengren's maintenance solution to be much more reliable:
https://ola.hallengren.com/downloads.html

For example, it wouldn't try to perform log backups on a database that didn't perform a full backup yet.


Create View in specified database with dynamic sql? I am writting a dynamic sql to drop and create view in different database.

So I wrote:

set @CreateViewStatement =
                '
                USE ['+ @DB +'];
                CREATE VIEW [dbo].[MyTable]
                AS

                SELECT ........something
exec (@CreateViewStatement)

It gives me error:

  'CREATE VIEW' must be the first statement in a query batch.

If I remove the USE DATABASE statement it works fine, but then the database is not specify anymore....

How can I solve this problem?

You can use nested EXEC calls. The database context changed by the USE persists to the child batch.

DECLARE @DB SYSNAME

SET @DB = 'tempdb'

DECLARE @CreateViewStatement NVARCHAR(MAX)
SET @CreateViewStatement = '
      USE '+ QUOTENAME(@DB) +';
      EXEC(''
             CREATE VIEW [dbo].[MyTable] AS
             SELECT 1 AS [Foo]
      '')

                          '
EXEC (@CreateViewStatement)

One way I have handled when run into this case is placing GO after use  statement.

set @CreateViewStatement =
'
  USE ['+ @DB +']; GO
  CREATE VIEW [dbo].[MyTable]
  AS

  SELECT ........something'
exec (@CreateViewStatement)


Unable to drop database user without login One of our developers with blanket privilege on a database wanted to create a regular user mapped to a login when what he mistakenly created was a user without login and now I'm unable to remove the user. DROP USER XX command runs forever with no result nor an error. The user doesn't own any schemas as well. How can I remove this user?
Thank you

Check for any blocking SPIDs when you have issued the DROP USER XXX command.

Then kill.


Error while configuring mirroring, Microsoft SQL Server, error : 1418 I have a SQL Server 2008 Enterprise SP1 installed on my both principle and mirror server. SQL Server and SQL Server agent service is also started by same ID having sysadmin rights.

But when I am trying to establish mirroring between them, it is reflecting an error message, Microsoft SQL Server, Error:1418

I have checked the endpoint of both server that are in started state. and also dropped and create endpoints again. please note i am not using the witness server here.

Principle server endpoint port:- 5022
mirror server endpoint port :- 5023
mirror server at endpoint port 5022 also didn't work.
Both 5022 and 5023 ports are opened on firewall

but when I execute the following script on both server, asynchronous(high safety mode) mirroring established.

TO SETUP MIRRORING in synchronous(high safty) ON PRINCIPLE SERVER:-
ALTER DATABASE SMPP
SET PARTNER = 'TCP://principle server IP:5022'

TO SETUP MIRRORING ON MIRROR SERVER:-
ALTER DATABASE SMPP
SET PARTNER = 'TCP://mirror server ip:5023'

I also tried to put hostname of principle and mirror server instance in above script but no luck.

Please suggest any working solution as soon as possible.

Microsoft error no 1418:-
this error occurs due to communication problem between endpoints.first of all you have to check TCP/IP for the instance was enabled or not,if any duplicate endpoints exists,in
case login was not created inn the other instances of mirroring pair,check for the edition of principal and mirror servers.


What type of database can I use to store mutual scores between every combination of users? Let's say I have an application that calculates and stores a mutual score between every combination of users.

I'm currently using Postgres to store this information.

So if my system had users with id 1, 2, and 3 that table might look like

CREATE TABLE scores (id,user_id,friend_id,score)
AS ( VALUES
    ( 1, 1, 2, 15.2 ),
    ( 2, 2, 1, 15.2 ),
    ( 3, 1, 3, 19.7 ),
    ( 4, 3, 1, 19.7 ),
    ( 5, 2, 3, 21.0 ),
    ( 6, 3, 2, 21.0 )
);

As you can see, the data itself is pretty simple.

Every combination of users has a mutual score calculated between them (the derivation of that score or what it means isn't important here) and I store it in the table.
Because I could look up either user, I have to store the pairings "both ways" (e.g. 1 -> 2 and 2 -> 1)

The problem is that this increases quadratically. With 10,000 users there are 100 million records in this table. With 100,000 users (which is a reasonable scope for my app) there would be 10 billion records.

So my question is - surely Postgres can't be the most scale-able way to store this information right? I don't need access to it constantly, just periodically when I'm running a batch job so I'm happy to store it in some separate type of DB on a separate server.

What kind of DB specializes in storing this type of paired data and can perform quick reads?

For example, I commonly query for all the ranked friends of a given user with SELECT friend_id FROM my_table where user_id = ? ORDER BY score DESC so I'd like to run the equivalent of that query on this new database.

Thanks!

P.S Someone had suggested a Graph DB - didn't know if that would apply here.

Because I could look up either user, I have to store the pairings "both ways"

Well, that's not true. You could normalize the data because the relations aren't directed anyway.

CREATE TABLE scores (
  user_id   int,
  friend_id int,
  score     int,
  CHECK ( user_id < friend_id ),
  PRIMARY KEY (user_id, friend_id)
);

INSERT INTO scores (user_id, friend_id,score)
VALUES
  ( 1, 2, 15.2 ),
  ( 1, 3, 19.7 ),
  ( 2, 3, 21.0 );

Now you can find the relation easy.

SELECT *
FROM scores
WHERE user_id = 1
  AND friend_id = 2;

If you don't want to put the rule in the app, just rewrite the query into.

SELECT *
FROM scores
WHERE (user_id, friend_id) =
  ROW( least(1,2), greatest(1,2) );


What is the fastest way to fetch the last row from a table? I have a PostgreSQL table, Prices, with the columns:

price (Decimal)
product_id (Int)

There are also created_at and updated_at columns.

Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table is the current price.

What is the most efficient way to get the last price for a specific product:

Index product_id and query for the last record
Add a third column active (Boolean) to mark the latest price and create a composite index (product_id and active)
Or something else?

You'll need an index on product_id regardless of solution.

Provided you have an index on the updated_at column, and all you need is to fetch "a specific product" as you stated, then I would do:

select *
from Prices
where product_id = ?
order by updated_at desc
limit 1

But if I did not get the results I wanted or if I needed to get the current price for many products, then I would try the option of adding a active column, and setting it to N for all prices other than the new one when doing updates of the prices and then I would create a partial index where active as suggested by a_horse_with_no_name. I would go there only if I needed to as it adds a layer of complexity of updating previous price rows to not be active, etc.

You do should have a column of wef date and time (with effect from) in price table. User will enter its value while updating price. After that fetch only value with desc wef limit 1.


Find the first gap in an aggregation of integers in SQL Server Let's say I have a table called dbo.GroupAssignment.

GroupID | Rank
------------------
1    1
1    2
1    3
2    1
2    3
2    4
3    2
3    4
3    5

The PK is GroupID, Rank. Normally, the ranks within a group are a contiguous sequence of integers starting from 1. But it's possible for a GroupAssignment to get removed, leaving a gap. When a new assignment is made for the group, I want to fill the first gap available.

So, how could I calculate this in SQL Server?

You can use ROW_NUMBER to generate what the rank should be

;WITH ranks AS
(
    SELECT
       GroupID, Rank,
       ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Rank) AS newRank
    FROM
       dbo.GroupAssignment
)
SELECT
   GroupID, MIN(newRank), MIN(Rank)
FROM
   ranks
WHERE
   newRank < Rank -- don't need <> here
GROUP BY
   GroupID;

Personally, I wouldn't re-use this Rank if one of these applies

It can be generated at runtime with ROW_NUMBER etc
It is some kind of ID column: the old value will live in history tables etc and will be ambiguous
It is simply an arbitrary internal key

Also, do you want RANK or DENSE_RANK to deal with "joint 2nd" or "equal values" instead of ROW_NUMBER

DECLARE @GroupIdPrevious BIGINT, @GroupRankPrevious BIGINT
        DECLARE @GroupId BIGINT, @GroupRank BIGINT

        SET @GroupId = 2

        DECLARE GroupAssignmentCursor CURSOR FAST_FORWARD FOR
        SELECT
                GroupID
              ,GroupRank
          FROM
               #GroupAssignment
         WHERE
               GroupId = @GroupId

        OPEN GroupAssignmentCursor

        FETCH NEXT FROM GroupAssignmentCursor INTO @GroupIdPrevious, @GroupRankPrevious

        IF @@FETCH_STATUS = 0
        BEGIN
            FETCH NEXT FROM GroupAssignmentCursor INTO @GroupId, @GroupRank
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @GroupRank - @GroupRankPrevious = 1 -- There is no gap
                BEGIN
                    SET @GroupIdPrevious = @GroupId
                    SET @GroupRankPrevious = @GroupRank
                    FETCH NEXT FROM GroupAssignmentCursor INTO @GroupId, @GroupRank
                END
                ELSE
                BEGIN
                    SELECT @GroupIdPrevious AS GroupIdPrevious, @GroupRankPrevious AS GroupRankPrevious, @GroupId AS GroupId, @GroupRank AS GroupRank, 'Found gap' AS OutputMsg
                    BREAK
                END
            END
        END

        CLOSE GroupAssignmentCursor
        DEALLOCATE GroupAssignmentCursor

======================================================================
Another way is to a loop with Cursor. here are the steps (I am just typing this - you need to make sure the syntax is correct)

Declare the cursor
Fetch the 1st row
Save the Id in a local variable (Previous Id)
Fetch the 2nd row
Start a WHILE Loop WHEN NOT EOF (@@FETCH_STATUS....)
Compare the Current Id with the Previous Id
If the difference is 1 skip
if not set the value or print
Fetch Next

If you are interested, I can write the complete code and post it

Ravi Ramaswamy