Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)? We have a job table that looks like this

CREATE TABLE [dbo].[Clearing](
    [Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL,
    [BsAcctId] [int] NULL,
    [Status] [varchar](20) NULL,
     CONSTRAINT [csPk_Clearing] PRIMARY KEY CLUSTERED ( [Skey] ASC )
)

with a covering index like this

CREATE NONCLUSTERED INDEX [IX_Status] ON [dbo].[Clearing]
(
    [Status] ASC
)
INCLUDE ( [Skey], [BsAcctId])

and we use this query to pick the next job

select top (1) Skey, BsAcctId, Status from Clearing with ( readpast, updlock )
  where (Clearing.Status = 'NEW')
  order by Clearing.Skey

(The real table has about 10 columns. They are all in the index include() clause and the select column list.)

The execution plan is very simple.  It does an index seek using IX_Status, then a top operator.  Since the index is sorted on (status, skey) the plan does not need a sort.

The table is in a database in an AlwaysOn Availability Group.  The group has 2 DB servers. (It is a test system.)

Normally this table and query work great.
So we go to apply Windows updates, and do the usual.

Fail over the primary to the secondary
Apply Windows updates on the former primary
Fail over back to the original primary
Apply Windows updates on the secondary

After the second fail over and all the worker processes get new connections to the new primary, the query starts failing in the sense that multiple processes start getting the same jobs.

The problem is load related.  With 4 worker processes running it did not happen.  But with 10 workers it happens consistently.

This is using SQL Server 2016 Enterprise.  We do not have the query store enabled to see if the execution plan was weird at some point.

Any suggestions on why the query would start failing after two fail overs?

Since the query is only using the index and not touching the table, is UPDLOCK reliable?

Update 1 - we changed the process to list the locks held by the spid (using sp_lock @@spid) just after doing the select.  For the same skey, we are seeing different KEY locks held on the IX_Status index (indid=9)

KEY (aad9d6e672f9)  U
KEY (154698b9131c)  U

Update 2 - using index hint did not help.

Update 3 - Removing order by clause in query avoided the problem.  But we have a second table with same problem that needs the order by.

Update 4 - Our worker processes maintain a db connection pool.  ODBC does not tell us when a fail over happens, so connections to an old primary stay in the pool until we try to use them and they fail.  We suspect after we failover DB1 -> DB2 -> DB1, then old connections to DB1 might not fail like they should.  We made a change to close all pooled connections after any one connection is lost, and this seems to have avoided the problem.  (SQL Server ODBC added a "Connection Resiliency" feature that is fueling this suspicion.)

Getting the same row from different indexes

As David mentions in his answer, you can get the same row from multiple sessions if you happen to access that row via different indexes.

The UPDLOCK hint only applies to the specific access method. Having a nonclustered index row U locked does not prevent another query acquiring a U lock on a different index (including the clustered index, if any).

Running these two queries (with index hints) from different sessions results in the same row being returned:

-- Session 1
BEGIN TRANSACTION;

SELECT TOP (1) Skey, BsAcctId, Status
FROM dbo.Clearing WITH(READPAST, UPDLOCK, INDEX(2))
WHERE ([Status] = 'NEW')
ORDER BY Skey;

-- Session 2
BEGIN TRANSACTION;

SELECT TOP (1) Skey, BsAcctId, Status
FROM dbo.Clearing WITH(READPAST, UPDLOCK, INDEX(1))
WHERE ([Status] = 'NEW')
ORDER BY Skey;

After the failover, new execution plans will be compiled for incoming queries - so this could explain why you ended up with the new behavior after the failover.  As David also said, you could force the index to avoid this problem.

As a side note, you should also use a ROWLOCK hint, since READPAST can only skip locks taken at the row granularity.

Getting different rows in the same session due to concurrency

You also mentioned this:

  The problem is load related. With 4 worker processes running it did not happen. But with 10 workers it happens consistently.

So it sounds like the failover was not the only thing that changed - you also increased concurrency on the application side of things.

I tried loading up your table / index with some data:

INSERT INTO dbo.Clearing
    ([Status])
SELECT TOP 100
    'NEW'
FROM master.dbo.spt_values;

INSERT INTO dbo.Clearing
    ([Status])
SELECT TOP 10000
    'COMPLETE'
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

Then I loaded up SQL Query Stress with your query, set it to run on 10 threads at once, every 100 ms:

While that was running, I periodically ran the same query with EXEC sp_lock @spid1 = @my_spid; tacked on to the end in SSMS.  If I run the SELECT query in the same session multiple times (without rolling back), I can get multiple locks held by that session:

Which you can see using the %%lockres%% predicate:

SELECT * FROM dbo.Clearing WITH (NOLOCK, INDEX(2)) WHERE %%lockres%% = '(36aaaeef6267)';
SELECT * FROM dbo.Clearing WITH (NOLOCK, INDEX(2)) WHERE %%lockres%% = '(84d6be32a10d)';

Without much concurrency, you'll generally get the same row if you run that SELECT more than once in the session.  But with other queries taking and releasing locks all the time, it's easily possible to get different rows.  So make sure you are not depending on the SELECT returning the same ID twice (we don't have the whole context of your workload, so this is just speculation / FYI).

Unexpected locking behavior

It's can be unsafe to depend solely on specific locks to be taken.  Consider an optimization described in this blog post from Paul White (or another one like it): The Case of the Missing Shared Locks

The post outlines a situation where a row protected by an X lock can still be read by SELECT queries:

  SQL Server contains an optimization that allows it to avoid taking row-level shared (S) locks in the right circumstances. Specifically, it can skip shared locks if there is no risk of reading uncommitted data without them.

Related reading:

Using tables as Queues by Remus Rusanu
When READPAST Doesn’t Read Past by Erik Darling


What is best way to storing geo coordinates related to planned route? Here is my scenario:

Application "A" produces lat and long points that correspond to planned route. Depending on route distance it could have several thousands of points. I know of Geography and Geometry datatypes, even thought they came out with SQL 2008 I have not seen anyone use them yet and not sure what would be good scenario for using it. In addition to points that App "A" generates, I need to store points from App "B" that correspond to actual route. After it is all stored I need to find deviations from the planned route.

This might be an NP-Complete problem, http://en.wikipedia.org/wiki/NP-complete  Compare it to the travelling salesman problem, http://en.wikipedia.org/wiki/Travelling_salesman_problem   I'm not sure if it's NP-Complete since my college texts are in storage, and it's been a while since my complexity classes.

Not to say that we can't just do some simplistic "drift math" using SQL Server spatial data types starting with the table approach suggested by @kenwilsondba.

A more thorough, (if not NP-Complete,) approach would look for where the actual route went back on track amoung other things.

However, we could do the following utilizing SQL Server spatial data types if all we need is simplistic calculation of drift where we could just throw away extra destinations if the actual route goes over, or repeat the last actual end point if the actual route goes under, and disregard any segments where the route went back on track, and assume that the stop ids are actually sequential.

Note that this approach to calculation also penalizes actual routes that only stray in the beginning and never stray for the remainder of the route, and rewards those who stay on track until the final points.

Another caveat is that fields in the table design below are redundant.

The distance calculations are in meters by default.

CREATE TABLE a_planned_point (
    route_id            INT,
    stop_id             INT,
    lat                 DECIMAL(10,7),
    long                DECIMAL(10,7),
    pointspatialdata    GEOGRAPHY,
    city                VARCHAR(20),
    state               CHAR(2) )

CREATE TABLE b_actual_point (
    route_id            INT,
    stop_id             INT,
    lat                 DECIMAL(10,7),
    long                DECIMAL(10,7),
    pointspatialdata    GEOGRAPHY,
    city                VARCHAR(20),
    state               CHAR(2) )

CREATE TABLE c_planned_segment (
    route_id            INT,
    start_id            INT,
    stop_id             INT,
    lat_planned_stop    DECIMAL(10,7),
    long_planned_stop   DECIMAL(10,7),
    city_planned        VARCHAR(20),
    state_planned       CHAR(2),
    segmentspatialdata  GEOGRAPHY)

CREATE TABLE d_actual_segment (
    route_id            INT,
    start_id            INT,
    stop_id             INT,
    lat_actual_stop     DECIMAL(10,7),
    long_actual_stop    DECIMAL(10,7),
    city_actual         VARCHAR(20),
    state_actual        CHAR(2),
    segmentspatialdata  GEOGRAPHY)

CREATE TABLE e_drift_segment (
    route_id            INT,
    planned_stop_id     INT,
    actual_stop_id      INT,
    lat_planned_stop    DECIMAL(10,7),
    long_planned_stop   DECIMAL(10,7),
    city_planned        VARCHAR(20),
    state_planned       CHAR(2),
    lat_actual_stop     DECIMAL(10,7),
    long_actual_stop    DECIMAL(10,7),
    city_actual         VARCHAR(20),
    state_actual        CHAR(2),
    distance_drift      FLOAT,
    segmentspatialdata  GEOGRAPHY)

INSERT INTO a_planned_point (route_id, stop_id, lat, long, pointspatialdata, city, state) VALUES
    (1, 0, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'),
    (1, 1, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ'),
    (1, 2, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'),
    (1, 3, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'),
    (1, 4, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY'),
    (1, 5, 42.37, -71.03,  CAST('POINT(-71.03 42.37)' AS GEOGRAPHY),  'Boston', 'MA')

INSERT INTO b_actual_point (route_id, stop_id, lat, long, pointspatialdata, city, state) VALUES
    (1, 0, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'),
    (1, 1, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'),
    (1, 2, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ'),
    (1, 3, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'),
    (1, 4, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY')

INSERT INTO c_planned_segment (
    route_id,
    start_id,
    stop_id,
    lat_planned_stop,
    long_planned_stop,
    city_planned,
    state_planned,
    segmentspatialdata
    )
    SELECT
        x.route_id,
        x.stop_id,
        y.stop_id,
        y.lat,
        y.long,
        y.city,
        y.state,
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+
            CAST(y.long AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM
        a_planned_point x
    LEFT OUTER JOIN
        a_planned_point y
    ON
        y.stop_id = x.stop_id + 1
    WHERE
        y.stop_id IS NOT NULL
        and
        x.route_id = 1
    ORDER BY x.stop_id

INSERT INTO d_actual_segment (
    route_id,
    start_id,
    stop_id,
    lat_actual_stop,
    long_actual_stop,
    city_actual,
    state_actual,
    segmentspatialdata
    )
    SELECT
        x.route_id,
        x.stop_id,
        y.stop_id,
        y.lat,
        y.long,
        y.city,
        y.state,
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+
            CAST(y.long AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM
        b_actual_point x
    LEFT OUTER JOIN
        b_actual_point y
    ON
        y.stop_id = x.stop_id + 1
    WHERE
        y.stop_id IS NOT NULL
        and
        x.route_id = 1
    ORDER BY x.stop_id

INSERT INTO e_drift_segment (
    route_id,
    planned_stop_id,
    actual_stop_id,
    lat_planned_stop,
    long_planned_stop,
    city_planned,
    state_planned,
    lat_actual_stop,
    long_actual_stop,
    city_actual,
    state_actual,
    distance_drift,
    segmentspatialdata
    )
    SELECT
        x.route_id,
        x.stop_id,
        y.stop_id,
        x.lat,
        x.long,
        x.city,
        x.state,
        y.lat,
        y.long,
        y.city,
        y.state,
        x.pointspatialdata.STDistance(y.pointspatialdata),
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+
            CAST(y.long + 0.0000001 AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM
        a_planned_point x
    LEFT OUTER JOIN
        b_actual_point y
    ON
        y.stop_id = x.stop_id
    WHERE
        x.stop_id IS NOT NULL
        and
        y.stop_id IS NOT NULL
        and
        x.route_id = 1
    ORDER BY x.stop_id

To find the total amount of drift in meters:

select sum(distance_drift) from e_drift_segment where route_id = 1

To show both planed, actual and drift route segments at one time in the SQL Server "Spatial Results" tab:

select segmentspatialdata from c_planned_segment where route_id = 1
union all
select segmentspatialdata from d_actual_segment where route_id = 1
union all
select segmentspatialdata from e_drift_segment where route_id = 1


How to use left join with Where clause I was thinking that I have a very good understanding about my sql and joins but i miserably failed to make this query. I have to make employee leave report, therefore I want all employee list no matter he make leave or not, if there is no leave then zero will be displayed. I have this query currently:

select
leave_detail_id, emp_id, Employee_Name, leave_type, leave_name, sum(leave_total_days) total_leave, leave_current_status_id
from employee emp
left join  tbl_emp_leave_details eld
ON emp.PK = eld.emp_id
inner join tbl_leave_types lt
ON lt.leave_id = eld.leave_type
where leave_current_status_id = 1
and leave_start_date >= '01-07-2019'
and leave_end_date <= '31-07-2019'

and emp.Active=1
    group by leave_type

You can see that even after applying left join i am unable to bring all the employees. Please help me to understand the problem. I know that where filter is also apply to join. but how can i correct this with (an explanation)?

You might try this, however as I do not have any understanding of your schema, the result might not be what you search for.

select
  leave_detail_id
  ,emp_id
  ,Employee_Name
  ,leave_type
  ,leave_name
  ,sum(leave_total_days) total_leave
  ,leave_current_status_id
from employee emp
left join  tbl_emp_leave_details eld
  ON emp.PK = eld.emp_id
left join tbl_leave_types lt
  ON lt.leave_id = eld.leave_type
  and leave_current_status_id = 1
  and leave_start_date >= '01-07-2019'
  and leave_end_date <= '31-07-2019'
group by
  leave_detail_id
  ,emp_id
  ,Employee_Name
  ,leave_type
  ,leave_name
  ,leave_current_status_id


Installing pgcrypto Extension I have this password hash function that hashes a password to sha256. I've already got it running in pgadmin3 in a local database. I've done it by importing the extension, a feature that has been added a few versions ago.

To my schoolwork I need to run the trigger in a phppgadmin server but when I execute

CREATE EXTENSION pgcrypto;

I get

ERROR:  could not open extension control file "/usr/share/postgresql/9.1/extension/pgcrypto.control": No such file or directory

How can I resolve this without having to change my hash to a weaker one (md5 or sha1)?

I'm assuming that you want to create this extension in another server. I'm guessing that the server didn't get the "postgresql contrib" package installed. This package contains the "standard" extensions that are available from the PostgreSQL source code.

I think you need to get the system administrator to install the required package for your OS.

For Debian and Ubuntu it's the "postgresql-contrib-9.1" package, for other platforms it has a similar name.


MySQL Grouping Data Together From Same User I need a database guru. I'm trying to solve a problem that I'm pretty sure can be solved with a simple query I cant figure out. First, this is the table ve_messages:

MessageID | RecieverID | SenderID | Message | SentOn

I use a while statement to display the messages inside dynamic bootstrap panels but the query below will generate one panel every time the user receives a message which is good. But I need something more specific. I'm trying to gather all the messages from the same user inside one panel instead of creating a new one everytime so that the users can view all the messages that were sent so that they can follow the conversation. How can I do that? I tried GROUP BY SenderID but then I cannot access all the messages...

       <?php
       $i=1;
        $inboxmsgQ="SELECT * FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID WHERE m.RecieverID= '$loginid_session' ";
        $inboxmsgresult=mysqli_query($db,$inboxmsgQ);
        while($inbox=mysqli_fetch_array($inboxmsgresult)){$i++?>
      <?php
      $checkavatareview=mysqli_query($db,"SELECT * FROM ve_photos p WHERE p.UserID={$inbox['SenderID']} AND IsPrimaryPhoto='1' ORDER BY InsertDate LIMIT 1");
       $rowviewe = mysqli_fetch_array($checkavatareview,MYSQLI_ASSOC);
      ?>

          <a data-toggle="collapse" data-parent="#accordion" href="#collapseM<?=$i;?>"><?=$inbox['ProfileName'];?> | <?=$inbox['SentOn'];?>

      <div id="collapseM<?=$i;?>" class="panel-collapse collapse">

               <a href="profile.php?viewuserprofile=<?=$inbox['SenderID'];?>" >
              <?php if(mysqli_num_rows( $checkavatareview)==0):?>

              <?php elseif(mysqli_num_rows( $checkavatareview)>0):?>
           <img style="width: 15% !important;" class="tableimg" src="core/<?=$rowviewe['PhotoPath'];?>" alt="profilename">
           <?php endif;?>

   <?=$inbox['Message'];?>

    <?php };?>

If I'm following, you want to have one panel per user the current user has exchanged messages with, and show all messages from that person in that panel.

To get all people the current user has received messages from (broken into multiple lines for readability - you can put it in one line in your code):

SELECT SenderID, ProfileName
  FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID
 WHERE m.RecieverID= '$loginid_session'
 GROUP BY UserId, ProfileName
;

Loop through these results to create your panels.

You'll need a second loop within each panel to show each message, using the results of this query:

SELECT *
  FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID
 WHERE m.RecieverID= '$loginid_session'
   AND m.SenderID = {$inbox['SenderID']}
 ORDER BY SentOn DESC
;

You may actually want to change the name of the $inbox associative array, as it now just has the ID and profile names of senders.

The ORDER BY assumes that you'd want the most recent messages at the top; change DESC to ASC to flip that.


Why are InnoDB's index keys limited to 767 bytes? MySQL's official documentation states about InnoDB:

  An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.

The meaning of this statement is clear to me, however I do not understand the reasons of this limitation, which seems arbitrary (why 767?).
Any pointers?

This seems to be linked to the size of the InnoDB pages:

  If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

Thanks to XL on the #mysql IRC chat for the pointer to page sizes. I will update this answer when I find out more about the InnoDB page format and its limitations.

UPDATE
The quote above applies to the total index key length for a row, whereas my original question was about the index key length for a single-column index. My guess at this point is that the 767-byte limitation is legacy from the time when all pages were 4KB in size, and thus both single-column and multi-column indexes were limited to a total length of 767 (+1?) bytes.
I will update this question again when I have more information about:

how the page size influences the index key length,
why there is a 1-byte difference in maximum key length between single-column indexes and multi-column indexes, and
why the legacy value of 767 bytes (if it is indeed a legacy value) was kept for single-column index key lengths.


What is the difference between SQLEXPRESS and MSSQLSERVER? I am using SQL Server 2008 R2, when I looked into the services for SQL Server I saw there are two different services with different instances.

SQLEXPRESS
MSSQLSERVER

Can anyone help me to get clear the difference between #1 and #2?

Thanks in advance

You can install multiple copies of SQL Server onto the same machine. To keep them apart, all "copies" (called instances) except the first (default) one have to be named.

So by default, when you install your first instance of SQL Server, you won't give it any instance name - it'll be the default instance that you connect to use the server name of ., (local) or your machine name (or IP address).

Any further instance you want to install needs to get a separate, unique instance name.

Visual Studio will by default install SQL Server Express onto your machine (unless you explicitly tell it not to), and that will be called the SQLEXPRESS instance on your machine (hence the service name also is called SQLEXPRESS). You connect to that named instance using .\SQLEXPRESS or (local)\SQLEXRPESS.

So most likely, in your case, you installed a "full" version of SQL Server onto your machine (as the default instance - service is called MSSQLSERVER), and Visual Studio installed a second copy of SQL Server - an Express version - as SQLEXPRESS


How do I create a point with an SRID in MySQL? MySQL supports using ST_PointFromText to construct a point with an SRID,

  ST_PointFromText(wkt[, srid [, options]])

  Constructs a Point value using its WKT representation and SRID.

This assumes the input is Well-Known Text (WKT). How do I construct a point with latitude and longitude as doubles (or floating point types).

MySQL 8.0+

MySQL supports Point(x,y) which is a GIS function that constructs a point. With MySQL 8.0 and newer, you can further assign an SRID to that point with ST_SRID(srid)

SELECT ST_SRID( Point(0,0), 4326);

This is a relatively new feature implemented in MySQL 8.0 with #WL8543. MariaDB does not support it.

In PostGIS, you would use,

SELECT ST_SetSRID( ST_MakePoint(0,0), 4326);

In MariaDB, your only option is,

ST_PointFromText(wkt[, srid [, options]])


How can I do a SQL Server 2000 bak? I have 2 databases .mdf SQL Server 2000, and I need to restore them on SQL Server 2014. After some research, I saw that the best approach is making a .bak of this 2 files.

Well, I need to install SQL Server 2000 to do this. I tried some youtube tutorial videos but without results.

Can you show me a way to do this?

Here is a process that you need to go through for upgrading from SQL Server 2000 to SQL Server 2014.

http://sqlmag.com/sql-server-2014/sql-select-steps-migrate-sql-server-2000-sql-server-2014

You will see that you need to

Restore a SQL Server 2000 backup to SQL Server 2008 as one step, followed by
Restore of the (now upgraded) 2008 version to SQL Server 2014.

If you only have .mdf files (not a full backup of the database) then you will need to create .ldf (log) files for the instance. This is not a recommended method for restores, but may be what you need.  Perhaps use marc_s script:

CREATE DATABASE Test_Data
ON PRIMARY
(FILENAME = N'D:\Test_Data\Test_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO

Or, there are several posts out there about attaching a database without a transaction log file.  One random choice:

http://www.mytechmantra.com/LearnSQLServer/How-to-attach-database-without-a-transaction-log-file-in-SQL-Server.html

If your SQL Server 2000 .mdf was from SQL Server 2000 SP4 then you may be able to attach it to a SQL Server 2008 server.  If the file has been corrupted, of course, then there is little to be done.

From this location you can apparently download the MSDE engine and SP 4.  The MSDE engine will only handle a 2GB database.

https://msdn.microsoft.com/en-us/sqlserver/bb895925

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A
Microsoft SQL Server 2000 Service Pack 4

MSDE has many limitations, including only 2GB databases.  (And it has been so long since I used it that I do not remember all the problems and limitations.)  See:

https://technet.microsoft.com/en-us/library/ms165672(v=sql.90).aspx

If you need a full version of SQL Server 2000, then I would suggest that you talk to other SQL Server users in the area and find one who still has retained an original install disk.

There are websites that say they have the media, but I cannot recommend them.


FILE privilege still allows for file writing on server (localhost) I created a user without the FILE privilege, and a web app connected to the DB with this user is still able to write text files on my localhost. To write these files, the program just uses fopen and fwrite.

I'm reading the document here, and it seems to me that FILE allows query-level file-writing/reading LOAD DATA INFILE. Am I interpreting this right?

Please note the FILE privilege as described in the Documentation

  The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also enables the user to create new files in any directory where the MySQL server has write access. This includes the server's data directory containing the files that implement the privilege tables. As a security measure, the server will not overwrite existing files.

The context here refers to the DB User being able to read and write files on the DB Server using

LOAD DATA INFILE
SELECT ... INTO OUTFILE
LOAD_FILE()

These commands have their fopen and fwrite commands issued from the mysqld server daemon, not the client program. Your web application is not restricted in any way from reading and writing ordinary files because you are doing so from the application layer, not the DB layer. If you click on each link to those three(3) commands, the FILE privilege is specifically mentioned.


SQL Server 2008 Replication - Fixed Overhead of Replication? We are using SQL 2008 Transactional replication to replicate data from our production database to our reporting database.

We recently created a new 'Global' database that we would like to have replicated to our various production databases around the world. The number of tables in this database is small (less than 30) and the maximum number of rows in any given table might be a couple thousand. We would not be adding tables and data to this Global database very often.

Our DBA said that the "fixed overhead of replication is disproportional to the amount of data that would be actually replicated". I know a little about transactional replication but not enough to refute this argument. Is there really a great deal of 'fixed overhead' with replication, such that if we're not replicating much data, there is still significant resource usage?

I'll throw an answer. The physical overhead of a new replication should not be too big, on an existing replicator. The more difficult part of a replication is the configuration and the monitoring.

You said that right now you have replication only to the reporting server, and you actually "...like to have replicated to our various production databases around the world", so you'll have subscribers all over the world for this new database.

This means you'll have to establish many physical connection between the home server and those new ones (firewall, permissions, SQL Server installation ..etc). You'll have new replications to monitor, new jobs to check, new logs to verify...etc.

In the case of a small database I'd go with a simpler case of copying it's data: just copy the backup over the wire and restore in the new places, use log shipping, anything that would not require cumbersome monitoring and configuration.


MySQL data transfer to different schema We have two servers: one for the developer (me) and one for the tester.

While working on the app, I sometimes make little changes in tables's schemas. (Like a new field, or a field type)

I'm trying to find a way to update the TEST's schema from the DEV without altering the TEST data.

I tried to dump the DEV structure only, execute it on the TEST db, and then fill it with the TEST data. But the new schema is replaced with the old TEST schema...

Do you have a way to do that ?
Or will my tester be forced to fill the database everytime we update?

Thanks...

Take a look here (from one of the founders of stackexchange). Be ABSOLUTELY sure to follow the five links therein to K. Scott Allen's series on "the philosophy and practice of database version control" - in the top 1-2% of the best technical writing that I have ever read.

There are many tools (see here and here) for this (pay and Open Source) - the one that seems to get the most coverage around here is liquibase (disclaimer - haven't used any - my DB versioning involved using v. old Unix VCS software.).


What is technical meaning of point in time recovery? Please can I have a technical meaning of point in time recovery? Does it mean that given a backup file, we must be able to recover upto any point in time during the span of the time covered by the backup?

If so, then is it right to say only a transaction log backup will allow for this? Or is only full or differential backup sufficient? How about recovery models - is full recovery model a must? Or can it be bulk recovery model also?

Point in time recovery is the concept that a particular set of data can be restored to an exact point in time, rather than just to the time of the last backup file.

In this case with SQL Server, log backups are the usual mechanism for accomplishing this to ensure database consistency.

FULL or BULK recovery modes must be utilised to allow for point in time restore, with the BULK option minimizing the amount of log space needed for certain operations (CREATE INDEX, SELECT INTO, BULK INSERT...). If any of these mechanisms are used, the ability to restore to a point in time using a log backup is dropped, forcing you to restore to before / after the bulk logged operation, for that individual log backup. For more information on this see SQL Server Recovery Models. The reason for this is that the bulk-logged model utilising minimal-logging will not log the data pages to the log file during bulk-logged operations - it will only log the extents that have been changed. Whether you want to use BULK recovery model or not will depend on your individual circumstances and whether you can afford to drop the point in time restores in favour of potentially faster bulk logged operations.

Other than that, FULL recovery model will support your use case.


Default Backup to be Copy-Only We have a 3rd party Payroll application which uses a SQL database on our server. On the database server we have weekly full backups, nightly differential and regular log backups. These are kept for a period of time to meet the business DR requirements.

The problem we have is that when a payroll is run, the payroll department require that a backup of the database be taken and kept for a specified period of time (5-7 years depending on the country for legal reasons). When they run the backup from the Payroll application it does a full backup. This obviously puts out our differential backups, as SQL now thinks it has a new full backup.

Is there a way that we could capture the backup script coming from the Payroll application and alter it to be Copy-Only, or is our only options to get the program changed and/or do the backup outside of the payroll program?

=======

Update to try and explain our goal a bit better:

What we want to do is intercept the backup statement coming out of the payroll program, add the command copy-only to it, and then pass it to SQL, and then return the results back to the payroll program.

Not really an answer - it's not possible to do it.

2 work arounds that we considered:

Creating a Stored Proc/SQL Agent Job that could be executed by the user that did the backup
Convince the business that they can trust us to do their backups for them

We went with option #2, and scheduled a copy-only backup to run every week on payroll day, and also at the end of the financial year. We told them that we can also run the backup whenever they ask us to do it. We also demonstrated that we can restore any database up to the minute based on our RPO. It made some people in HR disappointed, but the manager signed off on it.

We also disabled their access to backup to prevent any accidents.


sp_sequence_get_range equivalent in Oracle Do we have sp_sequence_get_range equivalent in Oracle.

I need to advance my sequence by some numbers always how can I achieve this.

Just alter then increment the sequence.

For example, to increment by 1000:

ALTER SEQUENCE yoursequence INCREMENT BY 1000;

You then have to SELECT the NEXTVAL from the sequence and re-alter it to increment by the usual value to restore "normal" operation.

To check the current value without incrementing:

SELECT yoursequence.CURRVAL FROM DUAL;

If you need to decrease the sequence, just ALTER SEQUENCE by a negative amount (very intuitive!).


Identical subquery optimisation in an update update activitybooking set `submitted`='1' where id='958'
    and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and
                            (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool1_count) &lt 10
    and (select SUM(pool2_count) from (select pool2_count from `activitybooking` where `abt`='12' and
                            (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool2_count) &lt 5
    and (select SUM(pool3_count) from (select pool3_count from `activitybooking` where `abt`='12' and
                            (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool3_count) &lt 20

The statement above has three identical subqueries and seems to execute them independently. How can I best rewrite the update to perform the subquery only once?

(The idea of this query is to only perform the update if doing so would not cause the sum of pool1_count to exceed the maximum allowed pool1_count and the same for pool2, pool3 ....)

MySQL lacks common table expressions but you can achieve the same goal here with the multiple-table update syntax:

update activitybooking
       cross join ( select sum(pool1_count) p1, sum(pool2_count) p2, sum(pool3_count) p3
                    from `activitybooking`
                    where `abt`='12' and (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8') ) z
set `submitted`='1'
where id='958' and p1

(SQLFiddle here)


How to IFNULL or COALESCE rewrite in a SQL query? I there a way to rewrite a SQL query, so I can avoid IFNULL or COALESCE, because the HXTT DBF JDBC driver (supports SQL92) throws the following error.:

Caused by: java.sql.SQLException: The aggregate function SUM(NETTO) can only be used in select-list, HAVING clause, and ORDER BY clause of SELECT statement.

The following query causes the above error.:

SELECT
(   SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
    FROM tckopf AS shipManagementInvoice
    WHERE shipManagementInvoice.referenzid = 1
        AND shipManagementInvoice.btyp = 5 )
-
(   SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
    FROM tckopf AS shipManagementCreditNote
    WHERE shipManagementCreditNote.referenzid = 1
        AND shipManagementCreditNote.btyp = 6 )
 AS shipManagementResult

If I remove IFNULL or COALESCE, there is no error.

There are several ways to rewrite the query and even more because both subqueries use the same base table. Not sure why the error is thrown and who is to blame, the JDBC drivers, the Foxpro or something else, so here are a few alternatives:

(1) using one query for both searches:

SELECT COALESCE(SUM(CASE WHEN t.btyp = 5 THEN t.netto
                         WHEN t.btyp = 6 THEN - t.netto
                                         ELSE 0
                    END), 0) AS shipManagementResult
FROM tckopf AS t
WHERE t.referenzid = 1 ;

(2) pulling the COALESCE() or IFNULL() function call in the external level, either using the original query:

SELECT
  COALESCE( ( SELECT SUM(i.netto)
              FROM tckopf AS i
              WHERE i.referenzid = 1
                AND i.btyp = 5
            ), 0)
- COALESCE( ( SELECT SUM(c.netto)
              FROM tckopf AS c
              WHERE c.referenzid = 1
                AND c.btyp = 6
            ), 0)
  AS shipManagementResult ;

(2b) or the above (1st) query:

SELECT COALESCE(
  ( SELECT SUM(CASE WHEN t.btyp = 5 THEN t.netto
                    WHEN t.btyp = 6 THEN - t.netto
                                    ELSE 0
                END)
    FROM tckopf AS t
    WHERE t.referenzid = 1
  ), 0) AS shipManagementResult ;


How can I calculate future interest charges and loan balance I have a table like below:

Need to calculate interest for next month 100*(4.29/100).
Row+1:Capital_Balance = Row:Capital_Balance_Calculated - Calculated interest from above step.

Resultant table is like below:
[![enter image description here][2]][2]

For sample data:

CREATE TABLE #Test (
     Account_Number int
    ,Interest_Rate float
    ,LastDateOfMonth Date
    ,Capital_Balance_Calculated Float
    ,Interest float
    ,Payment float

);

INSERT INTO #Test VALUES (12345,4.2,'2019-03-31',100,5,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-04-30',0,NULL,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-05-31',0,NULL,5);
INSERT INTO #Test VALUES (12345,4.2,'2019-06-30',0,NULL,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-07-31',0,NULL,7);
INSERT INTO #Test VALUES (54321,5.1,'2019-03-31',200,6,8);
INSERT INTO #Test VALUES (54321,5.1,'2019-04-30',0,NULL,6);
INSERT INTO #Test VALUES (54321,5.1,'2019-05-31',0,NULL,8);
INSERT INTO #Test VALUES (54321,5.1,'2019-06-30',0,NULL,10);
INSERT INTO #Test VALUES (54321,5.1,'2019-07-31',0,NULL,8);

Please take a look at the calculation:

Your sample data script has an error, it uses 4.2 and 5.1 instead of 4.29 and 1.54.
This threw off the numbers so please take care next time to make sure you get it right...
Also, after the edit the desired result is missing.
It is unclear whether the payment is from the 'current' row or the previous one, and in either case, for the first customer the balance drops and then increases even though the payment is more than the interest... Please check that as well.

Here is my solution, and while there is still some variance from your original desired result which I believe is wrong, this should get you on the right path so you can adjust it to fit your needs.
I chose to use ARN.Payment in the balance calculation in the recursive part of the CTE for the 'current row', change that to 'RI.Payment' if you want to use the 'previous row' payment. It is impossible to tell from your original table which one you meant.

The solution adds row numbers per account to allow the following recursive CTE to calculate the interest and payments row by row.

WITH Added_Row_Numbers AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY Account_Number ORDER BY LastDateOfMonth) AS Rn
FROM   #Test
)
, Recursive_Interest AS
(
SELECT *
FROM   Added_Row_Numbers
WHERE  Rn =1
UNION ALL
SELECT ARN.Account_Number,
       ARN.Interest_Rate,
       ARN.LastDateOfMonth,
       RI.Capital_Balance_Calculated + (ARN.Interest_Rate * RI.Capital_Balance_Calculated / 100) - RI.Payment
       AS Capital_Balance_Calculated,
       (ARN.Interest_Rate * RI.Capital_Balance_Calculated / 100) AS Interest_Paid,
       ARN.Payment,
       ARN.Rn
FROM   Recursive_Interest AS RI
       INNER JOIN
       Added_Row_Numbers AS ARN
       ON ARN.Rn = RI.Rn + 1
          AND
          RI.Account_Number = ARN.Account_Number
)
SELECT RI.Account_Number,
       RI.Interest_Rate,
       RI.LastDateOfMonth,
       RI.Capital_Balance_Calculated,
       RI.Interest,
       RI.Payment
FROM   Recursive_Interest AS RI
ORDER BY Account_Number, LastDateOfMonth

Here is the DBFiddle script.

HTH


How to prevent duplicates while storing result from different application For my case multiple application uses data from db based on date, this data is date specific so if for a date data exist application should just read it otherwise it should fetch information from different site and do some calculations to create data for a date and store it in db and use it.

Since multiple applications require this data I'm looking for data in db by date if it exist simply read it otherwise fetch data from internet and after some processing store it in db with date.

But as same thing will be done by multiple application there are chances that first application comes and look for data in db for a date and founds no data so it start fetching this data from internet, in the meanwhile other application also looks for same date data but it also finds no data so it will also start fetching data and by the time its done other application would have already stored that information so second application would try to insert duplicate information in the table.

What is the best approach in this scenario to avoid duplicate ? Should checking just before insert or using transaction can help ?

You can add unique key constraint on the db table , the table itself will prevent the second application to insert duplicate data and return error code such as "unique key violation" ,  in the application you will need to handle this error/exception.


Using psql with string variable that has two periods psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string='Me' -v version="1.7.3"

into the script

begin;

create table db_log (
    db_owner varchar(255) not null,
    db_user varchar(255) not null,
    version varchar(255) not null,
    created timestamp,
    id serial primary key
);

insert into db_log (db_owner, db_user, version, created)
    VALUES (current_user, :db_user_string, :version, current_timestamp);

commit;

gives...

psql:db_log.sql:10: ERROR:  syntax error at or near ".3"

Any ideas?

I am using 9.3.0

Try an extra layer of quotes around your variables:

psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string="'Me'" -v version="'1.7.3'"


ALTER DATABASE MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES I would like to change all file groups in my database to AutoGrow_all_Files, but only if it is currently AutoGrow_Single_File, like this:

ALTER DATABASE mydatabase MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES

How can I check this database property?

Complementing to both Max and Kenneth's answers ..

I would suggest you to apply SQL Server 2016 CU1 as there is a known bug with AUTOGROW_ALL_FILES property -

  The property isn't preserved after the database or the server restarts. The sys.filegroups viewstill shows that the corresponding column for the property (is_autogrow_all_files and is_read_only) is set to 1. However, it's not actually effective.

Also, its important to note that in SQL Server 2016 you dont need TF 1117 since is_autogrow_all_files is equivalent of TF1117


The Service Broker endpoint is in disabled or stopped state - AoAG - SQL Server 2012 - 15 minute interval I receive this error message in SQL Server error log every 15 minutes. I have setup AOAG on this server. The database option "Service Broker - Broker Enabled" is turned on for the Blackberry related databases and MSDB databases on this instance. Blackberry support mentioned they do not need this enabled. I have other AOAG instance and they do not receive this error.

Please share your wisdom in this regard. I have not used service broker yet and did not find a similar post on stack exchange from my search.

Thank you

According to this link,

  It’s a default behavior. The message will show when AlwaysOn
  availability group Listener is created and SQL Server binds the
  Listener’s IP dynamically. There is no method to suppress the message.
  These messages are informational message and do not cause any harm and
  can safely be ignored.

That link also gives a query to see which databases do and do not have brokers enabled:

SELECT is_broker_enabled, name  FROM sys.databases


SQL Server 2005/2008 system timezone I work on a ship that has multiple SQL Servers, both 2005/2008 versions.

As the ship moves from area to area so does our timezone. Sometimes we can jump ahead one hour and then back again.

My question is what is the best practice for this sort of situation, is it safe to change the system time so repeatedly? Should the DB be stopped before doing so? What sort of problems can arise?

Any feedback appreciated.

Thanks,
Kris

Even when a machine is fixed in a given timezone, storing timezone-dependent date/time information is not very safe. What do you do during Daylight Saving Time? You have the same problem - you skip ahead or repeat an hour. As the others suggested, you should always store your data using UTC, then it is easy to convert to any time zone you want, without having to know what time zone the server was in when the data was stored (though you can optionally store that information separately, or you can use DATETIMEOFFSET - but I haven't found that very useful since it, also, doesn't account for DST).

For easy situations (e.g. no DST involved), you can write a simple helper function to convert UTC to whatever time zone is appropriate. Even better if you just know the offset (e.g. +5 hours or -4 hours). This is a simple DATEADD operation.

For situations where DST is involved, I've always used a calendar table. It is very easy to populate a table with 30 years of data (one row for each day) and also store the offset, in minutes, for each day (given your time zone and whether the date falls in DST or not). Yes these can change for future dates, but the only time this can become tricky is if you're booking events farther into the future than the advance warning you get when the government decides to change the policy (as they did a few years ago). Converting UTC data on a given date to the time zone of your choice is still a simple DATEADD operation, except now it involves looking up the actual difference for that date in a table.

For data you're entering now, you can simply use GETUTCDATE() instead of GETDATE()/CURRENT_TIMESTAMP. This returns UTC time regardless of your time zone, and whether or not the machine is set up to observe DST.


Database schema tracking patient stats over time I want to create a database which allows to record patient reading data points like: weight, height, pressure, etc., over time.

I found a database schema like the one discussed in this Q & A which is looks similar to my needs. I'm wondering if such design offers a good performance for a large number of users?

For example, if we're planning to track 10 parameters for 10 000 users at least 20 times a year - it's around 2 000 000 rows/per year.

Available engine - InnodDB, MyISAM.

DB healthstats
TABLE user
    memberID (int*, auto increment, unsigned, primary key)
    name (varchar, 50)
    gender (char, 1)
    birthdate (date)

TABLE reading
    readingID (int*, auto increment, unsigned, primary key)
    memberID (int*, FK: TABLE user)
    date (datetime)

TABLE stat
    statID (int*, auto increment, unsigned, primary key)
    readingID (int*, FK: TABLE reading)
    type (varchar, 3)*
    value (decimal 4.1)*

Does readingID need to be ZEROFILL?
Does birthdate need to be DATETIME?
Use InnoDB.
Do not use EAV!  10 columns of stats is no big deal, and you can use appropriate datatypes for each column.

That is, have only 2 tables -- One for Users, one for Readings.

If you graph things, please use dates for the x-axis.  I saw one clinic that evenly spaced the readings across the x-axis.  That's just plain bad.

For weight you could pick FLOAT or DECIMAL(4,1) or SMALLINT UNSIGNED (4/3/2 bytes, sufficient for pounds or kg -- but be consistent on what you store)

For Gender, is using CHAR, do CHAR(1) CHARACTER SET ascii and allow more than the usual choices (these days!).

If you want to disallow two sets of measurements in a single day, get rid of readingID and have PRIMARY KEY(memberID, date).  This will have the side effect of making looking up all the info about a person more efficient.

2M rows/year is "medium sized"; not a problem.  By getting rid of stat, you are down to 200K rows/year, and the entire dataset will be closer to "tiny".  The database would be perhaps 8MB/year.  (Versus perhaps 100MB/year for EAV with stats.)

The insert rate would be 200K/year.  MySQL can easily handle 200K/day, so the server would be very idle.

With my suggestions, should able to handle even a million rows without further tweaking.  (With EAV, you would be struggling long before that.)

Why or why not EAV

EAV shines when there is a lot of variation in attributes.  Your application has a fixed set of attributes -- Everyone has a pulse rate; at worst you might store NULL if that test was not performed.

If, on the other hand, you choose to store the hundred(?) readings you can get from blood samples, ...  Usually blood work focuses on a small number of possible tests.  That is, the data is very sparse.

For blood work, I would recommend having a column of JSON text.  (Depending on whether you are using a recent MySQL or MariaDB, there might be a JSON datatype, or just a TEXT datatype; the effect is similar.)  In this column you would list only the readings that you have information for.  Meanwhile, the 'regular' readings (pulse, temp, etc) would have their own columns.  You might have a 'real' column that says "there is some bloodwork reading that is out-of-range" as a clue to look into it.

Yes, JSON has hassles when rummaging inside it.  But I claim that EAV is even worse -- for size, for speed, for coding.

In your Comment you mentioned adding 10-20 new readings.  The necessary ALTER TABLE .. ADD COLUMN .. NULL, ADD COLUMN .. NULL; would be a one-time nuisance.  But I still prefer it to either EAV or JSON.

Another thing to note:  INDEXes are not always useful.  When you have values that are mostly missing (columns, EAV, or JSON), they may be costly to search.  If you would like to show us some of the queries, we can discuss further.

For the simple query of "SELECT * FROM Readings WHERE userID = 123" to build graphs, then the optimal schema is a single Readings table with or without JSON, and without EAV.  And I gave the optimal PRIMARY KEY.


SQL how to remove duplicate results from same table I created an sql table on visual studio for visual basic, where i can put dynamic data every time i click on a button. I get the table results from textboxes and one datagridview with values(quantity,price,discount). I use the "command insert values into" inside a for loop to be able to get all the datagridview results. The problem is that the table has rows with duplicate values. I want to remove the duplicate values and keep only the values from datagridview e.g I have a table with the following format:

Following code should work, tested in Memory table:

DECLARE @TBL AS TABLE
(
    ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    First VARCHAR(10),
    Last VARCHAR(10),
    Address VARCHAR(30),
    Quantity INT,
    Price INT,
    Discount VARCHAR(10)
);

INSERT @TBL (First, Last, Address, Quantity, Price, Discount )
    VALUES
    ('rolp', 'Gaflp', '24 street', 1, 155, '10%'),
    ('rolp', 'Gaflp', '24 street', 3, 202, '5%'),
    ('rolp', 'Gaflp', '24 street', 4, 312, '6%');

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY First, Last, Address ORDER BY Quantity) AS Row
        FROM @TBL
    )
SELECT  ID,
        CASE WHEN Row = 1 THEN First ELSE '' END AS First,
        CASE WHEN Row = 1 THEN Last ELSE '' END AS Last,
        CASE WHEN Row = 1 THEN Address ELSE '' END AS Address,
        Quantity, Price, Discount
    FROM CTE;


Is there a standard formula to calculate the optimal resource required by SQL Server base on the Ram size of the server My server that SQL Server runs on has 8GB of Ram. Is there a standard formula that DBA use to gauge the minimum and maximum resource to be allocated for SQL Server base on the server Ram size?

I need to know how much of MB is optimal for these settings:

Minimum Server Memory(MB)
Maximum Server Memory(MB) and
Minimum memory per query.

My research get me to this link:Guideline

But I think the best solution is to know how he get to those figures.

The link that you pointed out gives a good starting number, which in most cases is sufficient enough as a good configuration.

I would suggest you to leave min memory as DEFAULT and adjust the max memory.

Also, best is to baseline your database server usage during your full business cycle as that will give you the best number based on your workload using below PERFMON counters :

SQL Server:Buffer Manager\Page Life Expectancy
SQL Server:Buffer Manager\Page reads/sec
Physical Disk\Disk Reads/sec
Memory\Available Mbytes
SQL Server: Memory Manager - Total Server Memory
SQL Server: Memory Manager - Target Server Memory

Note: If you are going to use any formula or online calculator to calculate SQL Server memory configuration then best is to read Beaware of Wow… An online calculator to misconfigure your SQL Server memory ! - by Jonathan Kehayias  first.

Some good references :

How much memory does my SQL Server actually need? - by Jonathan Kehayias


MySQL how to migrate to new server without data lost? I'm using AWS RDS, with 1 master & 1 slave.

Just now I alter 1 of the table and hangup halfway, so I force stop the process. Now the database got some problem.

Let say I wanted to instantiate a new RDS instance and migrate the whole DB over (around 1TB++), how can I fully migrate, because every seconds got new data comes in.

The problem now is because if want to copy the DB over, estimated around 30 minutes. What about the data in this 30 minutes during the migration?

Any idea?

Take the slave offline and clone it.

Replication will be backed up, and will catch up after bring brought back online.


Truncation of transaction logs Just trying to gather some knowledge on this topic as I have seen numerous answers and they are just confusing. I am using SQL Server 2012/2014 EE.

Is it a good idea to shrink transaction log files? If yes, then I see a lot of people say that back up your transaction log and then shrink. We have a backup tool that does a full backup @ 9:00 PM and does transaction log backup every 3 hours, if I have to shrink a log file, then I will make a manual backup and shrink the file? Correct?

I believe this is going to break up the log backup chain of our back up tool. How should this be handled?

If the answer is No to shrinking the log file, then what is the solution to transaction log drive filling up?

Also, do I have to take any special consideration when shrinking transaction logs of the databases which are in mirroring or Always On?

I am confused about the tlog backups as well, what do they do? What I mean is that I have a database whose log file space (after taking a log backup) is 20386.00 MB and the available free space is 20256.09 MB (99%). This makes it look that the entire TLog drive is full, however when I shrink this file then the 20256.09 MB is released to OS.

The "Corporate" policy does not allow me to change the timing of log backups. I have 20 gigs on the server for transaction logs files. There were some batch processes running which caused the transaction logs to grow to full 20 GB and since there is no more space left the database went down. This is why I need to shrink the files and release the space to OS so that database can come up.

Under normal operating conditions, shrinking your transaction log file is not recommended. The file grows large enough to handle your current active transactions (with SIMPLE recovery model), or to handle transactions between your transaction log backups (with FULL recovery model).

Unless something very unusual has happened, your shrinking the transaction log file simply means that it will have to grow again.

If your transaction log file is filling up your drive, you should look into the following:

If your database is in the FULL recovery model, do you have regular transaction log backups running? Are they running to a different drive/server? Can you increase the frequency of the transaction log backups? (This last will increase the frequency with which completed transaction activity is released from the log, so the log file should not need as much space)
If your database is in the SIMPLE recovery model, do you have very large transactions running? Can you break those transactions into smaller chunks?
Can you add space to that drive? Alternately, can you add a new drive, so you can add a second transaction log file there?

That said, if you have had an unusual event:

A database in the FULL recovery model where transaction log backups were not being taken;
a log file that grew beyond its normal size due to a rare bulk load of data;
a database that has permanently been switched from FULL to SIMPLE recovery model, where transaction log backups had been done relatively far apart (for instance, once a day).

If you are going to try to shrink a transaction log file, it is easiest to do so immediately after a backup has been taken. If backups are taken regularly, then you don't need to run a backup manually; you can simply wait until a backup completed, then try to shrink the file.

If you don't know what's a good size to shrink to, take your best guess based on the activity that expanded the file - if you have a 350GB transaction log file after running for about a year with no transaction log backups, for example, I'd start with 512 MB. I'd also set the growth factor for the transaction log to a specific value, rather than X% of the current value. In general, I'd aim for about half the starting size, but that's just me.

And, yes, I do believe there are special considerations when the database is involved in mirroring, replication, or always-on; however, I'm not certain what they are.

To expand a bit on what a transaction log backup does:

The transaction log is a record of every transaction that's been committed to your database, as well as any transactions currently running.

Under the SIMPLE recovery model, transaction log data no longer is needed once the transaction is committed. At that point, the space in the log file that's taken up by that transaction can be reused.
Under the FULL recovery model, transaction data is kept in the log file until both the transaction is committed, and the data has been backed up via a transaction log backup.

Transaction log backups allow you to perform "point-in-time" recovery.

If your database crashes at 5PM on Friday, and you take a full backup on Sundays, then you have lost all the data for the past week. Even if you take a nightly differential backup, you've lost all of Friday's data. If you run a transaction log backup every 3 hours, then the most data that you will lose would be three hours' worth.

With transaction log backups, you can also choose not to restore through the point where the last backup was taken, but to a specific time before that (hence, "point-in time"). If someone did something to the database 4 hours ago that basically destroyed it, then you want to restore your full backup, then your transaction logs up to 4 hours and five minutes ago. The transaction log includes information on exactly when each change was applied, so that will get back your database to how it was just before this disastrous change was made.

You mention that after a transaction log backup, you looked at your log file and it had about 20GB of unused space; if you shrink it, that space is indeed released from the file, and available for other uses.

However, I'd try looking at how much space is free in the file right before each backup happens. If there's less than 1 GB left free? Leave the file at 20 GB. You see, if you shrink it to, say 100MB, but it really needs 20 GB every three hours for your transaction load, then the system will simply need to reclaim that20 GB over the course of the next three hours. This is bad for 2 reasons:

SQL Server takes some time to add space to the log file; unlike data files, this cannot be done "instantly" by turning on an option.
If a 20 GB log file has eaten up all the space on your disk, and you've shrunk it to 100 MB, what happens if someone else drops a 10 GB file onto your hard drive a hour after you shrunk the log file? Around 30 minutes later, anyone trying to make a change to the database will get an error, because the log file is full.

Even if the log file has 19 out of 20 GB free at the end of an hour, you still may not want to shrink it down to (say) 2 GB. Something made it grow to 20 GB in the first place. You may have a job that runs overnight, or once a week on the weekend, that creates a lot of transactions, with a lot of data. If that is happening on a weekly basis, you'll need that 20 GB at least once a week. Why shrink the file to the 2 GB it needs Monday to Saturday, when every Sunday it will have to grow back to 20 GB? You've still got the two problems I mentioned above, and if you do run out of log space while some major off-hours process is running, it may take more than just the time to free up space for the log to resolve the problem fully, which might just ruin your weekend. Or, even worse, you don't find out there was a problem until Monday morning, and this system may be functionally down for users for most of the day!

By the way - If your transaction log does record 20 GB of changes in 3 hours, I'd want to be taking my transaction log backups much more frequently: maybe every 5 minutes.

See also: The Transaction Log (SQL Server) in the product documentation.


Get a number nth bit value in oracle How can I get nth bit value in oracle database?
I am currently using this query:

WITH a AS
     (SELECT 7 val
        FROM DUAL)
SELECT CASE BITAND (a.val, POWER (2, n-1))
          WHEN 0
             THEN 0
          ELSE 1
       END nth_bit
  FROM a

The shortest expression you can get is:

least(bitand(a, power(2, n-1)), 1)

where you want to find the nth bit of a.

The shortest query with this expression is:

select least(bitand(a, power(2, n-1)), 1) from dual;

From what I've read the above expression also works in PL/SQL, eg:

l_bit_n := least(bitand(a, power(2, n-1)), 1);


MSSQL Server 2012 Query Do we Need T-SQL for query with IF Condition? I have a database with tables Student(StudId, StudName,...) and Grades (GradeExam1, GradeExam2,..) etc. And I want to do a query to assign a letter grade to each grade. I am thinking of doing something like:

SELECT StudId, GradeExam1 AS 'A' IF GradeExam1>= 90 AND GradeExam1 <=100
UNION
SELECT StudID, GradeExam1 AS 'B' ..........

I know that I can use non-conditional aliases within plain SQL, but I have never done conditional aliases within an SQL query.

Or maybe I need T-SQL in order to do a loop for a given exam, instead of doing a union of select statements for different conditions?

Thanks in Advance.

You're talking about a CASE expression:

DECLARE @Table table (StudentId INT, GradeExam1 int, GradeExam2 int);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (1,95,88);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (2,85,93);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (3,75,87);

SELECT StudentId
    ,CASE
        WHEN GradeExam1 >= 90
            AND GradeExam1 <= 100
            THEN 'A'
        WHEN GradeExam1 >= 80
            AND GradeExam1 <= 90
            THEN 'B'
        WHEN GradeExam1 >= 70
            AND GradeExam1 <= 80
            THEN 'C'
        END AS GradeExam1
    ,CASE
        WHEN GradeExam2 >= 90
            AND GradeExam2 <= 100
            THEN 'A'
        WHEN GradeExam2 >= 80
            AND GradeExam2 <= 90
            THEN 'B'
        WHEN GradeExam2 >= 70
            AND GradeExam2 <= 80
            THEN 'C'
        END AS GradeExam2
FROM @table;

My example of a CASE expression is Standard SQL and not specific to just the T-SQL syntax.

Check here, if you'd like to learn more about the implementation of the CASE expression.


Which SQL Server/Windows logins require the sysadmin role? I'm setting up a new SQL Server instance. Which of these SQL Server/Windows logins should have the sysadmin role?

NT AUTHORITY\SYSTEM
NT SERVICE\SQLWriter
NT SERVICE\Winmgmt
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT

I'm not sure about NT AUTHORITY\SYSTEM. But according to the SQL Server documentation the others are added to the sysadmin role by SQL Server Setup, so I'm guessing it is required.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2016#DE_Prov


Sql Server Backup Job failed I have a sql agent job setup for taking full backup every night. Last night the job failed with the following error message.

Sql Server Error Log Message

  BackupIoRequest::ReportIoError: write failure on backup device
  'H:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\My_Database_backup_2014_04_28_233008_2135500.bak'.
  Operating system error 2(failed to retrieve text for this error.
  Reason: 15100).

  Error: 3041, Severity: 16, State: 1.

SqlAgent Job History Log

  Executed as user: DomainName\sqlagent. Microsoft (R) SQL Server
  Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright
  (C) Microsoft Corporation 2010. All rights reserved.    Started:
  23:30:01  Progress: 2014-04-28 23:30:06.81     Source:
  {123123123-F70A-430F-8041-12312312313}  Executing query "DECLARE @Guid
  UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End
  Progress  Error: 2014-04-28 23:30:16.19     Code: 0xC123123
  Source: Back Up Database (Full) Execute SQL Task     Description:
  Executing the query "BACKUP DATABASE [My_Database] TO  DISK =
  N'H:\MSS..." failed with the following error: "Write on
  "H:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MY_Database_backup_2014_04_28_233008_2135500.bak"
  failed: 2(failed to retrieve text for this error. Reason: 15100)
  BACKUP DATABASE is terminating abnormally.  10 percent processed.  20
  percent processed.  30 percent processed.  40 percent processed.  50
  percent processed.  60 percent processed.". Possible failure reasons:
  Problems with the query, "ResultSet" property not set correctly,
  parameters not set correctly, or connection not established correctly.
  End Error  DTExec: The package execution returned DTSER_FAILURE (1).
  Started:  23:30:01  Finished: 23:30:16  Elapsed:  14.844 seconds.  The
  package execution failed.  The step failed.

After doing the research on web mostly posts suggest I do not have enough space on the disks, I have checked the space on my disks I have have atleast 30 plus GB of free space on almost every drive, (disk for .mdf and disk for .ldf files). I have almost 15 gb of free space on my C drive. as One of the posts on web suggested not having enough space on C drive can also cause issues.

Has someelse ever faced the same issue, how you fixed it? what can be the reason for this error and how I can avoid it in the future. Any suggetions pointers in the right direction are most welcome. Thank you.

Operating system error 2 is a standard Windows operating system error--file not found.  Check the permissions on the folder and make sure that the account that owns the agent job has access to the folder and is able to traverse the path to the folder where the backup is trying to write.

Unfortunately, this is a Windows error message and not a SQL error.  I found something on Microsoft Connect (related to restore, not backups) where they said they were not able to reproduce the problem and confirmed that this is an OS, not a SQL Server, error message.


Difference between Full backup and Copy-only full backup I saw in the SQL Server Central thread Does a full backup truncate the log? that full backup does not truncate the log:

  No. Neither Full or Differential backups truncate the transaction log. - Lynn Pettis
  No - a full backup does not truncate the log. - Chad Crawford

So what is the difference between full backup and copy-only full backup?

For the log backup there is copy-only backup which prevent the log chain from breaking without truncating the log. So what is copy-only full backup?

The key difference between Full and Copy-only backups is whether or not the LSN (Log Sequence Number), and specifically the DatabaseBackupLSN is updated.

When you take a Full backup, the DatabaseBackupLSN is updated. After taking the full backup, if you take a Differential backup that backup has a DatabaseBackupLSN which matches that of the Full backup, and therefore SQL can link the two together (eg it knows from those LSNs that the diff followed the full).

The problem comes when you have scheduled backups running, so you have an initial Full backup, and then a sequence of Differentials. If you manually take a Full backup it will update the LSN, and then from that point onwards each Differential backup you take via scheduled backup will reference that new LSN not the original one. In the event of needing to restore, you could restore your scheduled Full backup, but when you tried to restore any of the Differential backups taken after the manual job you'd find it would fail as the LSNs no longer match.

With a Copy-only backup it doesn't touch the DatabaseBackupLSN, and as such doesn't break that chain of backups.

There's a good description of the issue, as well as why so many people misunderstand it in Breaking the Backup Chain – REDUX (Or Eating Crow) by Michael K. Campbell, which includes good visual guides like this one:

For a good explanation of the four different LSNs and how they're used, have a look at Understanding SQL Server Log Sequence Numbers for Backups by Simon Liew.

The way to avoid the issue is to not have more than one thing doing standard backups of a database. Any adhoc or secondary backups should be done with the copy-only option, see Copy-Only Backups (SQL Server) for full details, but essentially you use the "Copy Only Backup" option in SSMS, via T-SQL specify WITH COPY_ONLY in the command, or with PowerShell use the -CopyOnly parameter.


T-SQL90 vs Transact-Sql In Sql2012 Management Studio, there are separate options for "T-SQL90" and "Transact-Sql". My .sql files always seem to open in "Transact-Sql". My question is what is a "T-SQL90" file, how does it differ from a regular transact sql file, ...and how does Management Studio automatically decide what type of file it is? (different extension? if so, what is it?)

I suspect that the 90 refers to the deprecated 90 compatibility level, which is SQL Server 2005.  It is most likely there to support legacy code, as important changes to T-SQL syntax were made in SQL Server 2008, causing previously acceptable code to fail unless the compatibility level was downgraded.
The most likely reason that you are unable to find out further information is that Microsoft have removed a lot of online documentation for versions of SQL Server that are no longer supported.
The reference below seems to be consistent with this use of "TSQL90".
https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsql90parser?view=sql-dacfx-150


SQL server: create local copy of remote table I have read access to a remote SQL Server that contains table 'dbo.myTable'. The problem I am facing is that it is slow to read the data remotely, especially for small frequent calls.

I also have a local SQL server. What is the most simple way to have a local copy of 'dbo.myTable', that is synchronised automatically with the main one? (I don't need second precision, maybe a few minutes lag is enough)

The data will only be modified on the remote one, as locally I will only read.

Thank you

You may want to consider transactional replication, which would allow you to have an initial snapshot of the table copied from the source system to the target system. After this initial snapshot, you can then configure transactional replication to push the data changes to the target system.

The requirement for transactional replication is that the table has a primary key, however, it is a feature that I have used for the purpose you described on many occasions.

Bear in mind that replication would need to be activated on the source system and requires some administration to ensure it runs correctly. However, for a single table syncronization setup as described, it should cover everything you need.


Is mongodb upsert the right option for write intensive use case? I'm designing a data model for a transactional use case which is write intensive. The write load would be 900 - 1,600 Transactions Per Second (TPS) which would account for 30 million Inserts/Updates per day and read load would be 600 - 800 TPS

Since we have opted for an embedded data model, we are using upsert queries in most places. We have unique indexes built on the right fields to make the search faster. Out of the 30 million TPS day, we will be performing at least 10 million upsert per day

I would like to know:

Is it wise to keep the documents embedded and perform upserts every time (considering the load) rather than keeping the documents in 2 separate collections and perform joins to report the data to client applications?
Considering the load, I suspect that too much of upsert would created high IO and crash the system.

To answer you question, and this is my opinion not hardwired:

1. Is it wise to keep the documents embedded and perform upserts every time (considering the load) rather than keeping the documents in 2 separate collections and perform joins to report the data to client applications?

Ans: MongoDB is designed to handle embedded document structure, so don't worry if you are using upserts to update/ insert documents. Also two separate collection will increase your index overhead.

2. Considering the load, I suspect that too much of upsert would created high IO and crash the system.

Ans: If you add an correct index, then the upsert can become faster: after all the index is used to 'find' the document. The caveat is in the field(s) the index operates on and the fields that you're updating. If the updated portion is part of the index, you will have a performance impact on updating the document. If the updated portion is not part of the index, you will not incur a penalty for writing in the existing document. If the document is added though, you will have a minor performance impact, since the index collection is update.

Please feel free to revert back your opinion on this.


Transaction log dump - backup log from all database to one Dump file I am working with some old jobs and I found this code snippet.
I am just wondering why would someone dump transaction log files from ALL databases in to one file TxnLogDump.bak?

Is this simply to dump(ignore) the transaction log and shrink?

IF  EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'TransactionLogDump')
EXEC master.dbo.sp_dropdevice @logicalname = N'TransactionLogDump'
GO

    EXEC master.dbo.sp_addumpdevice  @devtype = N'disk',
@logicalname = N'TransactionLogDump',
@physicalname = N'\\BackupServer\Backup_Drive\Temp\TxnLogDump.bak'
    GO

USE DB1
BACKUP LOG DB1 TO TransactionLogDump
dbcc shrinkfile ('DB1', 2)

USE DB2
BACKUP LOG DB2 TO TransactionLogDump
dbcc shrinkfile ('DB2', 2)

USE DB3
BACKUP LOG DB3TO TransactionLogDump
dbcc shrinkfile ('DB3', 2)

USE DB4
BACKUP LOG DB4TO TransactionLogDump
dbcc shrinkfile ('DB4', 2)

USE DB5
BACKUP LOG DB5TO TransactionLogDump
dbcc shrinkfile ('DB5', 2)

Just a guess, but I suspect someone likely only wanted to manage one backup file without thinking of the consequences if it became corrupted.

This approach does backup the TLogs, so you can recover from this backup file.  However, shrinking the log is a bad idea for many reasons (as explained by Brent Ozar and Aaron Bertrand).


Questions regarding second normal form I understand that in order to fulfill 2.NF, attributes must not be dependent on part of the key. Now, the question is, let's say we have a relation R with a set of attributes {A,B,C,D,E,F,G,H,I,J,K} and its functional dependencies {A→GH, B→IJ, C→A, F→B, FC→DK, K→E} and the candidate keys C AND F. Do these FDs like C→A violate 2.NF because it is not fully functionally dependent on BOTH F AND C ?

So the question is

  Do attributes in functional dependencies need to be dependent on the whole set of candidate keys - in this case FC ? Or is the dependency on one of the keys sufficient to fulfill 2.NF ?

If all your dependencies are those you have shown: {A->GH, B->IJ, C->A, F->B, FC->DK, K->E} then you have come to the wrong conclusion.

Your only candidate key is CF. The C and F are not candidate keys on their own.

Therefore, the F->B dependency (and the C->A as well) means that the relation violates 2NF.

For the other question, if you had for example these dependencies:

BC -> AF
AF -> BC
 F -> DE

where the candidate keys are BC and AF, then again the F -> DE would mean that the relation violates 2NF. To be in 2NF would mean that there is no dependency on any part of any candidate key.


Database design for conditional detail table I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables:

setupExpenses (ExpenseID, Title, IsDaily)
ExpenseMaster (ExpenseMasterID, Month, EmployeeID)
ExpenseDetails (ExpDetID, ExpenseMasterID, ExpenseID, Date, Amount)
ExpenseDetailsDaily(ID, ExpDetID, Date, Amount)

if isdaily is true in setupExpenses then an entry is made in the 3rd table otherwise I have entries in the other two transaction tables. Any better suggesstion is appreciated. This design doesn't feel right to me.

Without knowing more about the business requirements, with the given structure you defined I would probably go with something like this:

Expense (Id, DetId, TypeId, Amount, Date, EmployeeId)
ExpenseType (Id, Name)

Where Expense.TypeId is a foreign key constraint to ExpenseType.Id.  ExpenseType could contain rows for daily or monthly expenses.  This is scalable, as if down the road you have the requirement for weekly expenses, or yearly expenses (etc.) then this normalization will benefit.  Expense.EmployeeId would be a foreign key constraint to your employee table, and Expense.DetId sounds like it'd be a foreign key constraint to another table that I can't seem to translate from the column name.  If it is just details for each expense, no reason not to just put it directly in the Expense table (provided a 1:1 relationship between expense and expense details).  I may be missing a few columns, but I'm sure you get the idea.


Where oracle saves lobs? I am new to oracle, so I don't know how lobs are saved, like in postgresql all lobs are saved in a table ( pg_largeobject) and all tables with lobs column have an id for this "main table".
So does oracle save lobs in a table and all tables just have an id to this "main table" or each table has his own copy of the lob.

Internal LOBs are stored within the database, as table columns. External LOBs are stored outside the database as operating system files.  Only a reference to the actual OS file is stored in the database.


Insert where not already available for FK? I'm looking for a simpler way to do this as I'm clearly thinking like a developer instead of a DBA.  How would a DBA tackle this?

I have a table with 3 column:  PK, Name, FK.

PK   Name   FK
1 someNameA 1
2 log     1
3 someNameB 2

I want to say if a foreign key of n does not have "log" for a name value then add it to the table.

I think I'm heading down a dark road as this seems rather vebose:

declare @ids table (ids varchar(8))
declare @log_ids table (ids varchar(8))
declare @nolog_ids table (ids varchar(8))

insert into @ids
    select fk
    from table
    where fk is not null

insert into @log_ids
    select fk
    from table
    where fk is not null
    and name like 'log%'

insert into @nolog_ids
    select distinct ids
    from @ids ids
    where ids.ids not in (select ids from @log_ids)

while ((select count(1) from @nolog_ids) > 0)
begin
    declare @currentid int
    set @currentid = (select top 1 ids from @nolog_ids)

    insert into table (name, fk)
    select 1, 'log', @currentid

    delete from @nolog_ids where ids = @currentid
end

You can just insert into table where fk not in the criteria you specified. This all can be a single insert statement. No need to do RBAR.

It sounds like you're looking to insert where you have fk records that do not have a corresponding "log" record, then insert.

insert into table
select distinct name, fk
from table t
where fk not in (
  select distinct fk
  from table t1
  where name like 'log%'
    and t1.fk is not null)

This is really crude but... just want to make sure I understood you correctly.

Also, in the future, if you do need to do a RBAR operation...

while ((select count(1) from @nolog_ids) > 0)
begin
    declare @currentid int
    set @currentid = (select top 1 ids from @nolog_ids)

    insert into table (name, fk)
    select 1, 'log', @currentid

    delete from @nolog_ids where ids = @currentid
end

I prefer to use this instead... more readable and potentially faster.

declare @currentid int

while exists (select * from @nolog_ids)
begin

    select top 1 @currentid = ids from @nolog_ids

    insert into table (name, fk)
    select 1, 'log', @currentid

    delete from @nolog_ids where ids = @currentid

end


Management Studio 2012 Backward Compatibility Two quick questions for anybody that's tried this:

Will Management Studio 2012 install and run side-by-side with, say, Management Studio 2008? I vaguely recall not being able to do this with 2008 and 2005.
Does Management Studio 2012 fully support connecting to and administering SQL Server 2008? Does Intellisense still work against older versions?

I have had no problems with it.
No problems there either. Intellisense works against 2008 (not 2005 of course).


MySQL : LEFT JOIN not fetching required data I'm using left join in MySQL as mentioned below:

SELECT  Q.QUESTION_ID,
        school_id
FROM    Q_MASTER AS Q
LEFT JOIN (
            SELECT  keyword,
                    school_id
            FROM    E_HEADER
            WHERE   low_q = 1
                    AND school_id = 6
          ) AS X
        ON X.SCHOOL_ID = Q.SCHOOL_ID
WHERE   Q.QUESTION_ID IN ( X.KEYWORD )

Here Q_MASTER is having question_id as primary key (INT).
E_HEADER is having keyword as comma seperated values of question id. (VARCHAR100)
Ex: 1,2,3,4

I want to exclude all question id's in select query from Q_MASTER which are present in "KEYWORD" column of E_HEADER

But above mentioned query is only execluding first value in comma seperated string.
I tried , NOT EXISTS clause also, but every time only first comma seperated value is getting execluded.

Can you please let me know , how to solve this issue.

Storing a string of comma-separated values in a column is not an ideal design; ideally you would have a table of keywords and a 2-column map table with a row for every (E_HEADER.id,keyword_id).

However, given your current structure, the FIND_IN_SET() function should work, although this will not be the best possible performance, since a function in the where clause that takes a column as its argument will generally prevent the use of indexes for evaluating that condition -- but given your structure, you don't have an alternative.

FIND_IN_SET() returns the ordinal position of the first argument within the comma-separated string that is the second argument, or in this case "something true" if a match is found; no match returns 0 ("false") and if either argument is null, the function returns NULL.

Also, it's difficult to say without a better understanding of your structure exactly what you want the query to do, but the derived table seems unnecessary.

SELECT Q.question_id, Q.school_id
  FROM Q_MASTER Q
  LEFT JOIN E_HEADER X on X.school_id = Q.school_id AND X.low_q = 1 AND X.school_id = 6
 WHERE NOT FIND_IN_SET(Q.question_id, X.keyword);

This would select every question_id and school_id from Q_MASTER where there exists a row in E_HEADER with a matching school_id and low_q is equal to 1 and school_id is equal to 6 and where the value of Q.question_id is not found in the comma-separated string X.keyword.

Based on my interpretation of the original query, only Q_MASTER records with school_id = 6 will be returned in spite of the left join, because the join requires school_id to be identical, and will project a NULL for X.keyword otherwise; FIND_IN_SET() returns NULL if either argument is null, and the expression "NOT (NULL)" returns evaluates to NULL which will be considered untrue by the WHERE clause.  The query will perform much better as an inner JOIN than a LEFT [outer] JOIN because fewer rows will need to be considered, but the ultimately correct approach requires a valid logical expression of what you actually need the server to do.


How to update a (very) large table without locking in MySQL I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table.

Unfortunately, whoever designed our schema didn't think things through properly, and opted to use usernames to represent this relation, instead of the numerical ids for the user records. As things progressed (like they usually do), our usernames are no longer a valid, unique representation of a player so I need to convert these relations to use numerical ids.

Adding the fields without locking was easy thanks to Percona Toolkit, which offers pt-online-schema-change that can ALTER on a live table. Populating the tables, however might be trickier.

The tables looks like this (I've stripped the creates of non-relevant fields), with the two unpopulated fields being player_id and target_id:

CREATE TABLE `player_targets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player` varchar(20) NOT NULL,
  `player_id` int(10) unsigned DEFAULT NULL,
  `target` varchar(20) NOT NULL,
  `target_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=58000000 DEFAULT CHARSET=latin1;

CREATE TABLE 'player_uuids' (
  `id`int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=600000 DEFAUL CHARSET=latin1;

I was planning on populating the two new fields with a query like this one:

UPDATE player_targets t
INNER JOIN player_uuids u1
  ON u1.username = t.player
INNER JOIN player_uuids u2
  ON u2.username = t.target
SET
  t.player_id = u1.id,
  t.target_id = u2.id
WHERE
  t.player_id IS NULL
  OR t.player_id IS NULL;

With the table storing relations being MyISAM, my interpretation of the documentation is that the UPDATE-query will lock the table until it's finished with all the rows. As the table is large, this will likely not work very well in a live environment.

What would the best approach for this be? writing a script to iterate over batches of relations? Changing the table engine to InnoDB (the table is read-heavy, which I believe is the reason for it being MyISAM)?

You can delete or update by one row in loop statement. In this case there will be no blocking but it will work a little slower. Use limit 1 to do this in procedure:

  PROCEDURE myProcedure()
    BEGIN
    DECLARE c int; -- to calculated affected rows
    set c = 0;

      ml:LOOP
          UPDATE player_targets t
          INNER JOIN player_uuids u1
           ON u1.username = t.player
          INNER JOIN player_uuids u2
           ON u2.username = t.target
          SET
           t.player_id = u1.id,
           t.target_id = u2.id
          WHERE
           t.player_id IS NULL
           OR t.player_id IS NULL
           LIMIT 1;

          -- check if the loop has completed
          IF  ROW_COUNT() = 0 THEN
              LEAVE ml;
          END IF;

          set c = c + 1;

          IF c MOD  100  = 0 THEN
              SELECT CONCAT(c, ' row(s) updated');
          END IF;
      END LOOP;

      SELECT CONCAT(c, ' row(s) updated; The statement has completed');
    END

And call procedure:

Call myProcedure();


SQL Server 2016 Standard AlwaysOn group In my SQL Server, I can’t see that options (Basic Availability Group, Database level health Detection, Per DatabaseDTC Suport) at New Availability Group – Specifiyname. I just see to write the [Availability group name].

So, when I execute New Availability Group Wizard, it didn't succeed.

This is the error:

  The specified command is invalid because the Always On Availability Groups join availability group (basic) feature is not supported by this edition of SQL Server.

While AlwaysOn/Availability Groups are a welcome edition to SQL Standard Edition 2016, the GUI is not supported for group creation. Try using the T-SQL code to create the group. Per Microsoft: https://msdn.microsoft.com/en-us/library/mt614935.aspx

"To create a basic availability group, use the CREATE AVAILABILITY GROUP transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). For more information, see CREATE AVAILABILITY GROUP (Transact-SQL). At this time, there is no UI support to create basic availability groups in SQL Server Management Studio."

The GUI is spamming the Advanced group used by Enterprise Edition (to your Standard Edition) while, Standard Edition only supports Basic.


How do you dump specific table(s)? Hi i have a shell script for doing a selected databases backup.
Now my requirement is i wanted to take a particular tables in a database to be backed up.
Each database backup need to be created in seperate file. The script is

#!/bin/bash

MyUSER="user"       # USERNAME
MyPASS="pw"         # PASSWORD
MyHOST="localhost"  # Hostname

# If cleanup is set to "1", backups older than $OLDERTHAN days will be deleted!
CLEANUP=1
OLDERTHAN=60

# Backup Dest directory
DEST="/backup/mysql"

# Directory, where a copy of the "latest" dumps will be stored
LATEST=$DEST/latest

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%Y-%m-%d")"

# DO NOT BACKUP these databases (separate database names by space)
EXCLUDE=""

### Libraries ###
MYSQL="$(which mysql)"
if [ -z "$MYSQL" ]; then
    echo "Error: MYSQL not found"
    exit 1
fi
MYSQLDUMP="$(which mysqldump)"
if [ -z "$MYSQLDUMP" ]; then
    echo "Error: MYSQLDUMP not found"
    exit 1
fi
CHOWN="$(which chown)"
if [ -z "$CHOWN" ]; then
    echo "Error: CHOWN not found"
    exit 1
fi
CHMOD="$(which chmod)"
if [ -z "$CHMOD" ]; then
    echo "Error: CHMOD not found"
    exit 1
fi
GZIP="$(which gzip)"
if [ -z "$GZIP" ]; then
    echo "Error: GZIP not found"
    exit 1
fi
CP="$(which cp)"
if [ -z "$CP" ]; then
    echo "Error: CP not found"
    exit 1
fi

[ ! -d $DEST ] && mkdir -p $DEST || :
[ ! -d $LATEST ] && mkdir -p $LATEST || :

# Only root can access it!
#$CHOWN 0.0 -R $DEST
#$CHMOD 0600 $DEST

# Get a list of all databases available
DBS="$($MYSQL -u$MyUSER -p$MyPASS -h $MyHOST -Bse 'show databases')"

# start dumping databases
for db in $DBS
do
    skipdb=-1
    if [ "$EXCLUDE" != "" ];
    then
        for i in $EXCLUDE
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then

            FILE="$DEST/$db.$HOST.$NOW.gz"
            # do all in one job in pipe,
            # connect to mysql using mysqldump for select mysql database
            # and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u$MyUSER -p$MyPASS -h $MyHOST --single-transaction $db $j  | $GZIP -9 > $FILE
        $CP $FILE "$LATEST/$db.$HOST.latest.gz"
    fi
done

# Remove files older than x days if cleanup is activated
if [ $CLEANUP == 1 ]; then
    find $DEST/ -name "*.gz" -type f -mtime +$OLDERTHAN -delete
fi

I will first answer your question, what you ask, but that is probably not what you want.

If you want to dump a single table using mysqldump, you can just put its name after the database name. So, if you execute:

mysqldump [connection parameters here] a_database a_table > $DEST/a_table.sql

It will backup only the a_table table from the database a_database. If you write:

mysqldump [connection parameters here] a_database a_table another_table yet_another_table \
> $DEST/a_database.sql

It will dump a_database.a_table, a_database.another_table and a_database.yet_another_table.

I mentioned that this (exporting each table separatelly) is probably not what you want because if you execute mysqldump several times and the server is not in read_only mode, you will end up with several tables in an inconsistent state (the backup was done at different times). In many cases, that is undesirable because if you have to restore more than one table they could end up with incompatible data. Using several runs of mysqldump in parallel can be interesting, as it can speed up the backup to some point, but you must makes sure that you lock the tables for a consistent backup.

Another approach is to use mysqldump --tab: This will let you export each table on a couple of separate files (a .sql and a .txt), which you can later import with mysqlimport (an it will be faster than a .sql file). You can still select specific tables in the same way:

mysqldump [connection parameters here] --tab=$DEST \
a_database a_table another_table yet_another_table

$ ls $DEST

a_table.sql
a_table.txt
another_table.sql
another_table.txt
yet_another_table.sql
yet_another_table.txt

The idea is that you should avoid exporting the tables at different times, as you will lose the consistency that mysqldump --lock-tables (default) or --single-transaction will give you.


Change Tracking Retention from a backup? I have a backup of a SQL Server 2008 database with change tracking enabled on it. The retention period is 2 days. AutoCleanUp is set to true.

The database backup is 5 days old and has 2 days worth of change tracking information when it was backed up 5 days ago.

If I restore the database today, would I lose all change tracking information from the database?

According to TechNet Change Tracking is not supported across a data restore - for not only the complexities you reveal in your question but more specifically because clients who might be keeping track of version info separate from the server will now be out of sync.  Read the technet article for more info.

If client version tracking doesn't apply to your scenario and you really need the change history you can most likely keep the server from auto-cleaning it after a restore.  The reason I say this is that the auto_cleanup task is a background thread that runs every 30 minutes (according to Microsoft).  So immediately after a restore you should have a small window of time to turn the auto_cleanup setting off and maintain any change tracking retained within the table.  But yes, if you leave auto_cleanup on it will be gone the next time the task runs.

As for your follow up question.

Paraphrase: Is retention period of 1 day a 24 hour period or a calendar day?

I can find nothing official but my gut says it's got to be a 24 hour period.  Making a change at 11:30PM shouldn't disappear an hour later (12:30AM) if retention is set to 1 day.

I have no documentation to back this up - just my gut on that one.  But I will have proof in 24 hours as I am testing it as I type :)

Follow up question test: UPDATE

My test has proven inconclusive.  I set retention for 1 day and 48 hours later and the change tracking info has still not been removed.  I tested a different table with a 1 hour retention period and it was removed 86 minutes later.  So I can not definitively answer if 1 day period is 24 hours or a full calendar day.

Microsoft documentation says that retention period refers to the minimum amount of time it should keep the data.  So the one thing my testing has proven is it doesn't mean it will be deleted the very moment that time period elapses.