Pros and Cons of creating a new table to store matches vs frequently running query to find matches I apologize if this type of theoretic question is not allow.
The application I am building will return a list of best matching venues from my database given an array of user selections. Typically we are returning around 50 venues. Users can also like or dislike a venue which will need to get stored in the database.  I am trying to decide if I should create a new table in my database to store best matches for each user or if I should run my search algorithm with the user selections to get this list.
Initially I thought storing the data would be best.  However, the user may want to change some of the answers on the questionnaire in which case I would need to rewrite the list of best matches.  Also this table will grow quite large if I need to store 50 entries per user. The pros of storing the data are that it is quite easy to query and find the user's matches.  Also it is easy to add a column into the matching table to indicate if a user liked or disliked the venue.
The other option is to resubmit the questionnaire each time.  The obvious con of this is that I will be searching through a large database frequently which may be expensive. On the pros side I would eliminate the large table of user matches from my database and it would be easy to handle changes to the questionnaire.
Any help would be appreciated as I am just getting into the database side of things and am looking to learn as much theory as possible.

It really depends on your requirement. Once the user has thier results, how much do you care about what they wanted, got, liked or disliked?
It sounds to me you want to learn from customer experience and preferences, so storing both the query and the response would be useful. Storing that on the db server means you can analyse the queries and results.
Personally i would save the query parameters and results (and thier response), as xml or json in 2 seperate fields.
Xml or json are useful because they allow your query/results to evolve over time with minimal changes to schema.
If you can just store the venue id, the results wont be huge. All depends on how much you need to store. If you can view the results with the query for context it should make sense.
Its worth considering that if venues are added or removed that a user will get a different result if they run the same query at a later date. So you may want go store queries and results in different tables if you want to retain a 1 to many relationship and history over time. E.g if i have my favourite query and run it once a week, do you want to store each result?


GROUP BY clause / Aggregate function error I am having a problem creating a Table View:

DROP VIEW IF EXISTS product_variation_stock_view;

CREATE VIEW product_variation_stock_view AS
    SELECT
        product_variations.product_id AS product_id,
        product_variations.id AS product_variation_id,
        COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) AS stock,
        CASE WHEN COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) > 0
            THEN true
            ELSE false
        END in_stock
    FROM product_variations

    LEFT JOIN (
        SELECT
            stocks.product_variation_id AS id,
            SUM(stocks.quantity) AS quantity
        FROM stocks
        GROUP BY
            stocks.product_variation_id
    ) AS stocks USING (id)

    LEFT JOIN (
        SELECT
            product_variation_order.product_variation_id as id,
            SUM(product_variation_order.quantity) AS quantity
        FROM product_variation_order
        GROUP BY
            product_variation_order.product_variation_id
    ) AS product_variation_order USING (id)

Error:

ERROR:  column "product_variations.product_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:   product_variations.product_id AS product_id,
      ^

Using PostgreSQL 10.3 on Ubuntu 18.04 via Laravel Homestead
I am running the query using the Postico OS X Client.

It looks like you might have to change your first few lines to reflect this:

CREATE VIEW product_variation_stock_view AS
SELECT
    product_variations.product_id AS product_id,
    product_variations.id AS product_variation_id,
    COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) AS stock,
    CASE WHEN COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) > 0
        THEN true
        ELSE false
    END in_stock
GROUP BY product_variations.id, product_variations.product_id
FROM product_variations


SQL Server 2019 Core - Always on Availability Group - Availability replica is disconnected all.
(I have read through all the other topics related to this one, and I cannot find anything to is similar to this issue, unless there is one hidden that I have missed.)

OK.

  SQL Server Always On High Availability

When going through the Wizard, it completes successfully, all the databases in the joined nodes are all set as Name (Restoring...)

Here is a picture of what the SSMS tree looks like for the Primary and Secondary Servers Group listings.
[

When connecting to the Windows Core through [b]Computer Mansagement[/b]
To access the SQL Server Configuration Manager (SQL 2019).
SQL Server Services is BLANK.

As you can see, this is like this when connecting to ALL Windows Core with SQL Server instances installed.
So, I cannot access this part of SQL Server Services, to administer it on any on the VM's

Wanted to also show the DASHBOARD for SQLCore-01
Which, as you can see, shows all the other SQLCore-02 - SQLCore-05 as bad.

I have checked all the ports that are set for SQL Server and the group, and they are there are reachable.
I am at a complete loss. I have tried and searched and tried some more, but cannot find anything to resolve this issue. I need this working by the end of the weekend, or I am going to have to go back to using the SQL Cluster again, which is what I want to stay away from. I like this method better, as it had multiple SQL Servers with databases on each server—more failover than the SQL Cluster.

Ports (5022) are open on all machines
Error in SQL Log is:

  Database Mirroring login attempt by user "Domain\sqlengine" failed with error:
  Connection handshake failed. The login domain\sqlengine does not have
  CONNECT permission on the endpoint. State 84.'.

The user domain\sqlengine
I ran this command on each instance

GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [domain\sqlengine]

The output is: Commands completed successfully.
But it still shows that same error in the logs.

I read this blog post SQL Authority .com
However, I cannot access through Computer Management, the
SQL Server Services for SQL 2019, for whatever reason.
See Image.

Another note.
Going by this article here. Always On Replica Disconnected After Changing SQL Server Service Account

The user is domain.local\sqlengine
This user I added to the SQL Server Logins for each SQL Server.
This is the account that is used for logging all actions of which AG will be doing but still have the same error message.

I found this page.   Availability replica is in disconnected
I used the script provided on the article and ran
I then restarted SQL Server on each Instance.

Restart-Service -Force 'MSSQL$SQL2019'

But to no avail. Still disconnected.

I also found this as well.
How to change SQL Server Service Account using PS
I tried running the script.
But I get an error.

  You cannot call a method on a null-valued expression.
  At D:\Files\SQL.ps1:23 char:1
  $ChangeService.SetServiceAccount($Uname, $PWord)
  Which is point at this section.

$UName="domain\sqlengine"
$PWord="**********"
$ChangeService.SetServiceAccount($UName, $PWord)

I was going to try and change the password, as maybe the password might have gotten corrupted or something. But, with this error, I am at a standstill.

Wayne

I just figured it out.

(You have to have the [Domain\sqlengine] added to the Logins for SSMS.)

In Management Studio
Click [Logins]

Perform the following (2) actions

  Action #1
  Double Click [Domain\sqlengine] Click on [Server Roles] (I
  have the following checked, I actually had them all checked for
  testing, and now these are the ones that are needed.)
  dbcreator
  public (default cannot uncheck this)
  serveradmin
  sysadmin

(This is what did the trick, this is what I was missing.)

  Action #2
  Click on [User Mapping]
  Put a check on [Master]

Click [OK]
Then Re-run the Always On Wizard

This is all it took.
For the last few days.
I have been banging my head against the wall to get this working.
And it was right here all along.

Hope this will help someone out.

Wayne


How can I call a remote User Defined Function? I have SQL server which contains a link to another server(For e.g: test) I have a function in linked server(name:getvalue(@parameter)).so this function output i want to retrieve in SQL server 2012.I am using below methodology.

DECLARE @sql nvarchar(4000);
DECLARE @somevalue nvarchar(16)

SET @sql = 'SELECT * FROM OPENQUERY(test,
                          ''SELECT @somevalue=getvalue(' +CAST('545454545' as nvarchar(10))+ ')'')';
EXEC (@sql)

I am getting error like

  OLE DB provider "OraOLEDB.Oracle" for linked server "test" returned
  message "ORA-00936: missing expression".
      Msg 7321, Level 16, State 2, Line 1
      An error occurred while preparing the query "SELECT @somevalue=getvalue(545454545)" for execution against OLE DB provider
  "OraOLEDB.Oracle" for linked server "test".

Please help me on this.Thanks in advance

Your remote query is nested twice, first in OPENQUERY, then in EXEC. The @somevalue variable is declared at the outermost level, it simply does not exist in the remote query's scope, so the reference is invalid.

But you also cannot use a variable there for two reasons:

First of all, as Colin 't Hart has noted, your linked server is Oracle. The SELECT @var = function(...) syntax is SQL Server and invalid in Oracle.
the OPENQUERY rowset function lets you treat the remote side's result only as a rowset – it is not designed to populate variables, whether along the way or as an alternative to the rowset. So, even if @var-like references were allowed in the underlying query (for instance, if the remote server was SQL Server), with OPENQUERY you would still be unable to reference outer ("this" side's) variables at the remote query's scope, nor would the outer scope be able to access variables declared at the inner scope ("that" side) in case you wanted to use them in your remote query.

To conclude at this point, the best way to get a result from an Oracle scalar function in SQL Server is probably to have your remote query return the result as a rowset while invoking the remote query using a rowset function like OPENQUERY. After you get the rowset from OPENQUERY, you can do with it whatever SQL Server allows you to do, and that includes storing its column values into variables. So, your OPENQUERY query could basically go like this:

SELECT
  @somevalue = somecolumn
FROM
  OPENQUERY(
    'test',
    'your Oracle query'
  )
;

Note the position of @somevalue = – it is moved one level higher, where it is now valid syntax and makes sense. The somecolumn would be the name that you would assign to your Oracle query's single column returning the result. This is probably how your Oracle query should look like:

SELECT getvalue(argument) AS somecolumn FROM DUAL

Now, if you did not need to parametrise argument, the complete Transact-SQL query could look like this:

DECLARE @somevalue nvarchar(16);

SELECT
  @somevalue = myresult
FROM
  OPENQUERY(
    test,
    'SELECT getvalue(545454545) AS myresult FROM DUAL'
  )
;

But as you want to be able to parametrise the argument, you need to make the SELECT FROM OPENQUERY query dynamic: build it as a string:

DECLARE @sql nvarchar(max);

…

SET @sql =
N'SELECT
  …
FROM
  OPENQUERY(
    test,
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

and then execute the string:

EXEC (@sql);

All that you are already doing. However, when you use the EXEC (...) syntax, you cannot directly receive the result into a variable1, you can only get it as a result set – yes, same limitation as with OPENQUERY. Unlike OPENQUERY, though, you cannot call EXEC in a FROM clause.

So, at this point you are still facing the problem of putting the result of getvalue into a variable.

Basically, there are two ways to go about it. First is to drop the @somevalue = bit from the dynamic query to let the OPENQUERY's result set pass through to the outermost scope. Although you cannot do ... FROM EXEC (...), you can do INSERT ... EXEC (...). So, insert the result set of EXEC into a temporary dataset (like a table variable):

DECLARE @tmpresult TABLE (result nvarchar(16));

INSERT INTO @tmpresult (result)
EXEC (@sql);

then read the column value from the temporary storage into a scalar variable:

SELECT @somevalue = result FROM @tmpresult;

The other, and, in my opinion, better, option is to use EXEC sp_executesql instead of EXEC () to execute the dynamic query. With the sp_executesql system stored procedure you can have parameters for the underlying query, including output parameters. An output parameter is precisely how you can pass the result into your @somevalue variable that you have declared in your main script.

So, keep the @somevalue = part of the dynamic query but declare it when invoking the query rather than in the query. That way the variable will work not just as a local variable but as a parameter. Including OUTPUT into the parameter declaration will make it an output parameter too. So, this is how you go:

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  …
';

EXEC sp_executesql @sql, '@tmpvalue nvarchar(16) OUTPUT', @somevalue OUTPUT;

You can see in the above code that I slightly changed the name of the variable in the dynamic query. I did that to emphasise the fact that the variable in your dynamic query and the one in your main script are different things. The @tmpvalue is the variable with which you work inside the dynamic query, while @somevalue is your main scope's variable that you will be passing as an actual receiver of the value stored in @tmpvalue. That is the way of exchanging data between variables of a dynamic query and those of the calling script. Note also that you must specify OUTPUT both in the parameter declaration and when specifying the receiver variable. This is the same convention as with output parameters of user-defined stored procedures.

So, to summarise, the final code would look something like this:

DECLARE
  @sql      nvarchar(max),
  @somvalue nvarchar(16)
;

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  OPENQUERY(
    test,
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

EXEC sp_executesql
  @sql,
  N'@tmpvalue nvarchar(16) OUTPUT',
  @somevalue OUTPUT
;

1Actually you can have parameters, including output parameters, with EXEC, but only if you have specified to execute the dynamic query at a remote server:

EXEC (@sql, @var1, @var2, ...) AT linked_server

Usually this is more useful when the remote server is a non-SQL Server one, as for SQL Server instances the EXEC sp_executesql method would likely be preferable.


On which server does a query execute? Given two Microsoft SQL servers:

Say ServerA has a view titled "View1":
SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3)

and a stored procedure "sp_Foo":
SELECT * FROM View1 WHERE Column1=1

When I execute EXEC sp_Foo, which query is executed on which server? Are they all executed on ServerB? All on ServerA? Or is the view query executed on ServerB and the SP query executed on ServerA?

When sp_foo executes there is only one query:

SELECT *
FROM (
  SELECT *
  FROM ServerB.database.dbo.Table1
  WHERE Column1 IN (1,2,3))
WHERE Column1=1;

This query can execute locally against a remote scan or it can execute remotely. What happens cannot be foretold, because it depends on the capabilities advertised by driver in use by the linked server. Typically a query like above over a linked server that uses SQLNCLI driver it will execute remotely. Local execution backed by a remote scan shows up on the execution plan as Remote Scan Showplan operator.

More complex plan can execute somehow mixed, partially remote partially locally, eg. see Linked Server behavior when used on JOIN clauses.


What is the difference between COUNT(*) and COUNT(*) OVER() Take the following code example:

SELECT MaritalStatus,
       COUNT(*) AS CountResult
       COUNT(*) OVER() AS CountOverResult
       FROM (schema).(table)
       GROUP BY Marital Status

COUNT(*) Returns all rows ignoring nulls right?

What does COUNT(*) OVER() do?

This question came in a practice exam so I didn't have the data to query.  I have been using Adventure Works and this site http://www.sqlishard.com/Exercise to practice.

If I enter a query like

SELECT ID, COUNT(*) AS 'COUNT(*)' , COUNT(*) OVER() AS 'COUNT(*) OVER()'
FROM Customers
GROUP BY ID

into the practice site I get 3794 rows returned with the Count(*) column full of ones and the Count(*) Over() column full of the total number of rows.  I didn't understand this pattern (sorry) so I came here.

COUNT(*) Returns all rows ignoring nulls right?

I'm not sure what you mean by "ignoring nulls" here. It returns the number of rows irrespective of any NULLs

SELECT COUNT(*)
FROM (VALUES (CAST(NULL AS INT)),
             (CAST(NULL AS INT))) V(C)

Returns 2.

Altering the above query to COUNT(C) would return 0 as when using COUNT with an expression other than * only NOT NULL values of that expression are counted.

Suppose the table in your question has the following source data

+---------+---------------+
|  Name   | MaritalStatus |
+---------+---------------+
| Albert  | Single        |
| Bob     | Single        |
| Charles | Single        |
| David   | Single        |
| Edward  | Married       |
| Fred    | Married       |
| George  | NULL          |
+---------+---------------+

The query

SELECT MaritalStatus,
       COUNT(*) AS CountResult
FROM   T
GROUP  BY MaritalStatus

Returns

+---------------+-------------+
| MaritalStatus | CountResult |
+---------------+-------------+
| Single        |           4 |
| Married       |           2 |
| NULL          |           1 |
+---------------+-------------+

Hopefully it is obvious how that result relates to the original data.

  What does COUNT(*) OVER() do?

Adding that into the SELECT list for the previous query produces

+---------------+-------------+-----------------+
| MaritalStatus | CountResult | CountOverResult |
+---------------+-------------+-----------------+
| Single        |           4 |               3 |
| Married       |           2 |               3 |
| NULL          |           1 |               3 |
+---------------+-------------+-----------------+

Notice that the result set has 3 rows and CountOverResult is 3. This is not a coincidence.

The reason for this is because it logically operates on the result set after the GROUP BY.

COUNT(*) OVER () is a windowed aggregate. The absence of any PARTITION BY or ORDER BY clause means that the window it operates on is the whole result set.

In the case of the query in your question the value of CountOverResult is the same as the number of distinct MaritalStatus values that exist in the base table because there is one row for each of these in the grouped result.


Renaming a production database We have a production database that we have identified needs some maintenance as a colleague leaves - some column deletion and renaming, and a couple of table renames

We're trying to find the best (quickest) way to do this so that it causes the least inconvenience to the users.

Ideally we'd like to do the following

Create a copy of the current database using mysqldump
On the copy make the changes to required
Update the current applications to use the new column names and test against the copy
Somehow push the changes to production database and release updated apps into the wild

If we restrict the problem to column renames then it seems that we should be able to find an application to help us manage the change.

MySQL Workbench uses CHANGE COLUMN to rename columns.  Having looked around SQYLyog and Red Gate MySQL Compare have both been suggested as being suitable.  However while both of these recognise the columns have been renamed, they both use DROP COLUMN / ADD COLUMN which does not preserve the data

Red Gate SQL Data Compare doesnt recognise that the columns are the same after the rename so that is out as an option.

We'd like to avoid having to take the database server offline to make the changes, but is that the only option?

We've ended up writing our own application that manages the Table and Column renames


Combine column from multiple rows into single row with id I have on table which store enter and exit of people in multi rows like:

EorL 1 = enter

EorL 2 = exit or leave

id   |  date_    |time    |EorL|
1    |  150201   | 09:14  | 1  |
2    |  150201   | 09:14  | 1  |
3    |  150201   | 09:15  | 1  |
1    |  150201   | 16:07  | 2  |
2    |  150201   | 16:07  | 2  |
3    |  150201   | 17:15  | 2  |

how can combine and get:

id    |   date_     |enter     |leave
1     |  150201     |09:14     |16:07
2     |  150201     |09:14     |16:07
3     |  150201     |09:15     |17:15

thank you in advance

This seems like a simple self join:

SELECT
    enter.id,
    enter.date_
    enter.time AS enter_time
    leave.time AS leave_time
FROM
    tablename AS enter
  JOIN
    tablename AS leave
  ON  enter.id = leave.id
  AND enter.date_ = leave.date_
  AND leave.EorL = 2
WHERE
    enter.EorL = 1 ;

The query assumes there are (max) 2 rows for any id and date_.

If there are cases where people have entered without leaving, use LEFT join instead. If there are people who have left without entering, use RIGHT join. If there might be both cases, use FULL join. For RIGHT and FULL, you'll have to adjust the SELECT list, using leave.id, leave.date_ and COALESCE(enter.id, leave.id), COALESCE(enter.date_, leave.date_).


Best way to remap database login to new database user and keep previous database user Here's what my environment looks like:

Active Directory (mydomain.local)
   - Groups
      - group01
         - Members
            - aduser01

SQL Instance
   - Logins
      - mydomain\group01
   - Databases
      - db01
         - Users
            - dbuser01 (login = aduser01, default schema = dbuser01)
         - Schemas
            - dbuser01
         - Roles
            - db_mydbrole

What I'd like to do is create a new database user and schema mapped to login aduser01:

USE [db01]
GO
CREATE USER [dbuser02] FOR LOGIN [mydomain\aduser01] WITH DEFAULT_SCHEMA=[dbuser02]
GO
EXEC sp_addrolemember N'db_mydbrole', N'dbuser02'
GO
CREATE SCHEMA [dbuser02] AUTHORIZATION [dbuser02]
GO

However since aduser01 is mapped to dbuser01 it fails with error:

  The login already has an account under a different user name
  (Microsoft SQL Server, Error: 15063)

What is the best way to go about this? If database user dbuser02 is created and mapped to aduser01, what are the possible states dbuser01 could be left in?

What is the best way to go about this? If database user dbuser02 is
  created and mapped to aduser01, what are the possible states dbuser01
  could be left in?

You can't create different database users mapped to the same login because a database user can be mapped to no more than one login.  The CREATE USER statement for dbuser02 failed so dbuser01 is still mapped to aduser01 and dbuser02 was not created.

I'm not exactly sure what you are trying to accomplish.  If your intent is to selectively grant database access to some, but not all, members of the mydomain\group01 and specify an individual default schema for each user, try the script below.

--add all members of mydomain\adgroup01 as role members
EXEC sp_addrolemember N'db_mydbrole', N'mydomain\adgroup01';
--create a default schema and user for desired mydomain\adgroup01 members
CREATE SCHEMA [aduser01] AUTHORIZATION [aduser01];
CREATE USER [mydomain\aduser01] WITH DEFAULT_SCHEMA=[aduser01];
CREATE SCHEMA [aduser02] AUTHORIZATION [aduser02];
CREATE USER [mydomain\aduser02] WITH DEFAULT_SCHEMA=[aduser02];


SQL query to find all parent records where all child records have a given value (not just some) An Event has many Participants. A Participant has a field of "status".

I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'.

I can find all Events where some of its Participants have a status of 'present' with the following SQL:

SELECT events.id, participants.status as status, count(*)
FROM `events` INNER JOIN `participants`
ON `participants`.`event_id` = `events`.`id`
WHERE (`participants`.`status` != 'present')
GROUP BY participants.event_id HAVING count(*) > 0

This almost works. The problem is that if one of the participant rows has a status of something other like "away", the event will still get fetched, because the sibling records are of a status equal to "present".

I need to ensure that I am filtering out every Event record with all Participants of a status of "present".

I am using PostgreSQL, so no non-PostgreSQL specific solutions please.

Thanks.

I don't have a copy of PostgreSQL in front of me.  Does this compile in your version:

...
HAVING count(*) = (select count(*) from participatns as x where x.event_id = events.id)

An alternative approach would be to remove from this list of all events those which have a participant who's status is not 'present', using the EXCEPT clause:

select
    event.id
from events

EXCEPT

select
    event.id
from event
INNER JOIN participants
ON participants.event_id = events.id
WHERE (participants.status != present)

The EXCEPT syntax is usually more efficiently expressed as NOT EXISTS or as a LEFT JOIN with the child identifer ends up as null.  I'm sure you can re-factor the query to suit your needs.


Extended properties are not copied using SMO I'm using the following code to copy a database from server to localhost using SMO. The code works well except the fact the extended properties are not copied.
    //Set Source SQL Server Instance Information
    Server sourceServer = new Server(sourceServerName);

    //Set Destination SQL Server Instance Information
    Server destinationServer = new Server(destinationServerName);

    try
    {
        //Using windows authentication
        sourceServer.ConnectionContext.LoginSecure = true;
        #region Optional - Using SQL Server authentication
        #endregion\
        sourceServer.ConnectionContext.Connect();

        //Using windows authentication
        destinationServer.ConnectionContext.LoginSecure = true;
        #region Optional - Using SQL Server authentication
        #endregion
        destinationServer.ConnectionContext.Connect();

        InitServer(sourceServer);
        InitServer(destinationServer);

        //Set Source Database Name [Database to Copy]
        Microsoft.SqlServer.Management.Smo.Database databaseSource = sourceServer.Databases[databaseName];
        //Set Destination Database Name
        Microsoft.SqlServer.Management.Smo.Database databaseDestination = new Microsoft.SqlServer.Management.Smo.Database(destinationServer, databaseName);

        databaseDestination.Create();

        //Set Transfer Class Source Database
        Transfer transferDatabase = new Transfer(databaseSource);

        //transferDatabase.CopyAllUsers = true;
        transferDatabase.CopyAllObjects = false;
        transferDatabase.CopyAllSchemas = true;
        transferDatabase.CopyAllUserDefinedDataTypes = true;
        transferDatabase.CopyAllTables = true;
        transferDatabase.CopyData = true;
        transferDatabase.CopyAllStoredProcedures = true;
        transferDatabase.DestinationServer = destinationServer.Name;
        transferDatabase.DestinationDatabase = databaseDestination.Name;
        transferDatabase.TemporaryPackageDirectory = @"C:\DATA\";

        transferDatabase.Options.WithDependencies = true;
        transferDatabase.Options.DriAll = true;
        transferDatabase.DropDestinationObjectsFirst = true;
        transferDatabase.Options.ContinueScriptingOnError = true;

        transferDatabase.TransferData();

    }
    catch (Exception ex)
    {
        log.Error(ex.Message);
    }
    finally
    {
        if (sourceServer.ConnectionContext.IsOpen)
        {
            sourceServer.ConnectionContext.Disconnect();
        }

        if (destinationServer.ConnectionContext.IsOpen)
        {
            destinationServer.ConnectionContext.Disconnect();
        }
    }

yes extended properties are a bit "special" to handle, fortunately Phil Factor has written a long article with helper functions which you can find here :- PhilFactorsArticle


Why is this user "Admin" created in all databases? When I create database in SQL server Management Studio, user with name "admin" is also created for all databases:

Why this user is created? And how I can change this behavior?

Looks like I have found the reason, this user exists in System Databases=> Model. That is why it was created for all new databases.


Masters database not replicating on slave after restarting client I'm trying to implement master slave replication and It's works fine with no errors, database is replicated on slave, but when I close my servers and open them again and add something in masters database it's not replicated in slave , do I have to follow these steps every time I start my server?

Setup on Masters server

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.20'
IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;

mysql> use mydb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> exit;

my.cnf file

[mysqld]
log-bin=mysql-bin
binlog-do-db=mydb
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

in terminal again

# service mysqld restart

mysql > SHOW MASTER STATUS;

# mysqldump -u root -p mydb > mydb.sql
# scp mydb.sql 192.168.1.20:/opt/

SETUP SLAVE 2 SERVER

 # vim /etc/my.cnf
 [mysqld]
 server-id=2
 replicate-do-db=mydb

 # /etc/init.d/mysqld restart
 # mysql -u root -p mydb < mydb.sql

 mysql>  CHANGE MASTER TO MASTER_HOST='192.168.1.10',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='secretpassword',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=107;

mysql> SLAVE START;
mysql> show slave status \G

Also it's is only replicating one database by dumping one database on slave server

 # mysqldump -u root -p mydb > mydb.sql

How can I dump all the databases from masters to slave server?

You need to rebuild the slave from a copy of the master. So take a full dump of the master:

mysqldump -uroot -p --single-transaction --master-data=2 > fulldump.sql

Restore the dump file on the slave server, reset slave and then execute CHANGE MASTER again. Make sure to remove replicate-do-db or any replication filters on slave my.cnf.


Postgresql: newly created database does not exist Recently I faced strange error. I'm running Postgresql behind pgBouncer. I created new database in pgAdmin (connected through pgBouncer). It shows up on the list, but when I try to select it I get error "Database not exists". When I SSH and psql directly to db, than this DB exists. Also when I connect pgAdmin directly to the postgresql I have no problem.

Here are few details about configuration:

PostgreSQL 9.5 on Ubuntu server 16.04
pgBouncer 1.7 (I connect pgAdmit through it)
pgAdmin 1.4
I login with user name with all privileges (including superuser)

This is my first time when I use pgBouncer, so I suspect there is the problem.

Ok.. After some investigation I found where was mistake. So after adding new database it is required to edit pgbouncer.ini file and manualy add this database in [databases] section. Example:

We have followig databases:

postgres
example1
example2

Than pgbouncer.ini should contain

[databases]
postgres = host=localhost dbname=postgres
example1 = host=localhost dbname=example1
example2 = host=localhost dbname=example2


Grouping() equivalent in PostgreSQL? I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below.

How can this be translated to PostgreSQL?

select top 100
    sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
   ,i_category
   ,i_class
   ,grouping(i_category)+grouping(i_class) as lochierarchy
   ,rank() over (partition by grouping(i_category)+grouping(i_class)
                             ,case when grouping(i_class) = 0 then i_category end
                 order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc
                ) as rank_within_parent
 from
    ssv_itemv_storev_IJGBAgg
 where
    d_year = 2001
    and s_state in ('TN')
 group by rollup(i_category,i_class)
 order by
    lochierarchy desc
   ,case when (grouping(i_category)+grouping(i_class) = 0) then i_category end
   ,rank_within_parent;

ROLLUP was implemented in Postgres 9.5.

Original answer for Postgres 9.4:
The rollup() in group by rollup(i_category,i_class) causes aggregation in hierarchical steps. It's an extension of the ISO SQL standard in SQL Server that's not implemented in Postgres. For two items, you need three steps in standard SQL - as well as in Postgres:

aggregate total
group by i_category
group by i_category, i_class

The grouping() function comes with this extension and indicates whether a given column is aggregated in each row. It's used here to sort the total and group sums first in the result.
rank() is a standard window function that works the same in Postgres, but we have to rewrite it due to the above adjustments.
top 100 translates to FETCH FIRST 100 ROWS ONLY in standard SQL, which is implemented in Postgres as well as the shorter LIMIT 100 (FETCH FIRST syntax works in SQL Server for 2012+ versions, too.) It's a bit odd to cut off after 100 rows in this aggregate query.
I am using a CTE to reuse the intermediary results in multiple aggregations below. Except for the LIMIT everything should basically work in SQL Server just as well:
WITH cte AS (
   SELECT sum(ss_net_profit) AS sum_profit
        , sum(ss_ext_sales_price) AS sum_price
        , i_category, i_class
   FROM   ssv_itemv_storev_IJGBAgg
   WHERE  d_year = 2001
   AND    s_state = 'TN'
   GROUP  BY i_category, i_class
   )
SELECT sum(sum_profit)/sum(sum_price) AS gross_margin
     , NULL AS i_category, NULL AS i_class
     , 2 AS lochierarchy
     , 1 AS rank_within_parent
FROM   cte

UNION ALL
(  -- parentheses required!
SELECT sum(sum_profit)/sum(sum_price) AS gross_margin
     , i_category, NULL AS i_class
     , 1 AS lochierarchy
     , rank() OVER (ORDER BY sum(sum_profit)/sum(sum_price)) AS rank_within_parent
FROM   cte
GROUP  BY i_category
ORDER  BY rank_within_parent, i_category  -- last item = my addition to break ties
)

UNION ALL
(
SELECT sum_profit/sum_price AS gross_margin
     , i_category, i_class
     , 0 AS lochierarchy
     , rank() OVER (PARTITION BY i_category
                    ORDER BY sum_profit/sum_price) AS rank_within_parent
FROM   cte
ORDER  BY i_category, rank_within_parent, i_class  -- last item = my addition to break ties
)
LIMIT 100;


Add UNIQUE index fails with duplicate entry error, but no duplicates found Given table:

CREATE TABLE mytable (
  field_a CHAR(15) NOT NULL DEFAULT '',
  field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_e CHAR(1) NULL DEFAULT '',
  field_f SMALLINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci;

Required index:

ALTER TABLE mytable ADD UNIQUE INDEX idx_key (field_a, field_b);

Data size is about 51 Mio. rows. Following problem:

Attempt 1: If I try to create the index after the data is in the table it fails with duplicate key error. A select on the failed key returns only one(!) row.

 [23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'idx_key'

 SELECT COUNT(*) FROM mytable WHERE field_a='aaaaaaaaaaaaaaa' AND field_b='11111'

returns 1 (!)

Attempt 2: If I create the index on an empty table or make a combined private key and then put the data into the table, the table contains only 27 of 51 Mio. rows (!).

Is there some kind of a limit on the unique index or a bug?

I use MariaDB 10.0.20. Please help.

Update 1

A count of unique rows

SELECT COUNT(DISTINCT field_a,field_b) from mytable;

returns 50 Mio rows. So there are about 1 Mio duplicates. This however does not explain the 27 Mio from attempt 2 or wrong exception from attempt 1.

Update 2

the table used by the attempt 2:

CREATE TABLE mytable (
  field_a CHAR(15) NOT NULL DEFAULT '',
  field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_e CHAR(1) NULL DEFAULT '',
  field_f SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY idx_key (field_a, field_b)
) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci;

Update 3

Attempt 2 error solved

To populate the table we use INSERT statements with multiple value rows in each. If a single value of the insert violated the unique constraint, all other values where also not inserted and caused 27 Mio rows instead of 50 Mio.

Approach 1:
However the bug with the wrong duplicate entry message still there even after the database were dropped and recreated, but this is an evil I can/must live with.

There is no guarantee that the valueaaaaaaaaaaaaaaa-11111 in the message

[23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'mykey'

is the value that actually causes the violation. Seems to be a bug in MariaDB and in MySQL.


SQL Server Rollback nested Stored procedures and linked servers I have read THIS StackOverflow article, and found it enlightening. However, I am having trouble assimilating that knowledge with my limited knowledge of transactions and linked servers. I am trying to do something very similar to the question in that article (Rollback everything from the parent stored proc, as well as what occurred in the sub-proc, if the sub-proc fails), except that my sub-proc does simple inserts into another SQL Server via a linked server.

Do any of the nuances of transactions and rollbacks change in this instance?

As a side note, we are using AWS EC2 with SQL Server 2008 R2, and we had problems configuring MSDTC a couple months ago. We tried using BEGIN DISTRIBUTED TRANSACTION and such, and could not get MSDTC to play nice despite being able to do simple inserts into the linked server easily without the distributed transaction. Must I use Distributed Transactions, or is the another way around it?

Yes you must use a distributed transaction for this since what you described is a distributed query (transaction across servers). You may want to post a new question regarding your MSDTC issue in EC2.


MySQL Sharding tables/servers or other methods to reduce table sizes? I'm currently working with a table exceeding 12 million rows (at about 3 GB when exported with mysqldump) and am curious how huge a table can realistically get without any serious performance impact. The table is growing about 100,000-200,000 rows a day or so.

Should I be starting to think a little about sharding this data across multiple tables or mysql instances now before the data gets much larger? Currently the server this is running on has 1 GB of ram (though it'll soon be moving to a machine with 3/4 GB).

Has anyone got any tips/recommended reading that would push me in the right direction, or is this something I don't need to worry about yet?

Thanks :)

A good RDBMS can grow to accommodate extremely large data. 3Gb databases are very manageable, and very probably, as long as you can get a server with enough RAM, most queries will run pretty fast with little effort.

Even when you exceed RAM, indexes, caching and partitioning let you still perform well. Very often, applications access most a relatively small working set- for instance, 90% queries might be limited to last month's data- while the 10% can be queries over older data. "Last month's" data tends to be somewhat stable- it grows when you have more users, but other than that, it doesn't tend to grow with time. This "working set" often fits in RAM, gets cached and you still get great performance.

But then, you can again get slowness. With proper monitoring and analysis, you can locate the queries which are running slow and take steps to solve them.

This is often simple:

Queries or incorrect code: often we write queries that retrieve information which is unneeded, or which execute several queries when one would suffice and be faster (the typical case is an operation which needs to display n rows of a table and performs n queries when 1 would be enough. Performing aggregates outside the database is also frequent). This is easily fixed by changing your code
Queries which do not run efficiently. EXPLAIN is your friend here. Often, creating indexes that the query can use is enough (roughly, you'll want to index on columns which appear in the WHERE clause). Also, sometimes tweaking the query itself will yield good results

Another approach which gives good results is throwing hardware at the problem:

Buying more RAM
Getting more and better disks (RAID10 setups get faster if you add more disks, SSDs often have significant benefits, etc.)
It is not frequent, but sometimes CPU is your bottleneck- you can get faster processors and more cores/processors

In some other cases, replication and sharding might be a problem. Replication is complicated, but stuff such as Oracle RAC let's you build monster clusters (at a price). Sharding is another option, but it's often one of the most complex to implement- even applications which shard easily require lots of work to be sharded, and some applications can be notoriously hard to shard.


Oracle 11g - Database Job About User Lock I have an Oracle database (Oracle 11gR2) and I want to create a job. The job will trigger my procedure.
The procedure must comply with the rules I have specified below:

If a user's expiry_date value is EXPIRED (select username, account_status, lock_date, expiry_date from dba_users where account_status='EXPIRED';), it will lock this user.

If a user's expiry_date value is EXPIRED and if it is already lock state, it will not do anything.

Exclude internal schemas for this operation.

I searched it but I didn't find any clear solution for it.
How can I create this procedure?
Best regards,

create or replace procedure p1 as
begin
  for u in (select username from dba_users where account_status in ('EXPIRED', 'EXPIRED(GRACE)') and username not in ('SYS', 'SYSTEM', '...'))
  loop
    execute immediate 'alter user "' || u.username || '" account lock';
  end loop;
end;
/

For 12c and higher you could use the ORACLE_MAINTAINED='N' for filtering the built-in users instead of the IN-list.


Is it possible to take same copy of mysql database in another instance(periodically) in the same server? My current mysqldb is slow due to more data. So we are planning to take a copy of production db into another instance in the same server and redirect all the select queries to that instance without affecting the production one. Is it possible we can syncronize all the changes from the prodcution to the second instance periodically in the same server ?

Yes , what you need is a replication environment !
Where your primary server (Master) will receive all updates and inserts, basiclly all DML's and the secondary server (slave) will receive only select statements !
 Here is a link to an article showing you how to do this.
 As for the load-balancing , in my case i use F5 to do this for me ! Here is link of the article that shows how this is done ! i have to worn you is quite hard to setup this puppy !

To run multiple instances using MySQL you need to have a couple of things separate from the initial install on MySQL like data directory, init script and config file.
 Ok i will place the steps here :
-all you need is for you complete them:

Create a new data directory
Create/copy existing mysql configuration file
Create/copy existing mysql init file to start/stop/reload etc on
this new instance

Edit the init file and make some minor changes to make it this instance specific - like port number ,instance name, take care at this step  !!

Install default tables for this new database instance

mysql_install_db --datadir=/var/lib/mysql_new --defaults-file=/etc/my_new.cnf --user=mysql
Start the new instance
Set password for this instance and connect to this new instance
Finally add it to server start-up list
And now setup your replication.


mongodb v3.4 - wiredtiger - compact does not work I am using mongo v3.4.10 with WiredTiger. I run the following command:

db.runCommand({ compact: "my_collection" });

the response is:

/* 1 */
{
    "ok" : 1.0
}

and the mongo log shows:

2017-10-31T00:12:20.282+0000 I COMMAND  [conn23] compact mydb.my_collection begin, options: paddingMode: NONE validateDocuments: 1
2017-10-31T00:12:21.662+0000 I COMMAND  [conn23] compact mydb.my_collection end
2017-10-31T00:12:21.663+0000 I COMMAND  [conn23] command mydb.my_collection appName: "MongoDB Shell" command: compact { compact: "my_collection" } numYields:0 reslen:22 locks:{ Global: { acquireCount: { r: 1, w: 1 } }, Database: { acquireCount: { W: 1 } } } protocol:op_command 1381ms

In short, everything looks fine, but the storageSize is exactly the same :/

I deleted lots of fields with a value of null, so I would expect a significant drop in disk usage.

I read online that early v3 had issues with this, but I'm on the latest version right now. The database is quite large so if I can avoid repairDatabase that would be good.

Any help is appreciated, thx.

UPDATE: I performed a mongodump / mongorestore, and it resulted in a 9% disk use reduction (admittedly I was expecting more). Nonetheless, it seems like compact should have had an impact.

UPDATE 2: I performed a repairDatabase and it had no effect. This is explicitly stated in mongo docs (https://docs.mongodb.com/manual/reference/command/repairDatabase):

  For WiredTiger, the operation rebuilds the database but does not result in the compaction of the collections in the database

but i thought i'd give it a crack. Still not sure why compact did nothing though.

@mils, The compact administration commands Rewrites and defragments all data and indexes in a collection. On WiredTiger databases, this command will release unneeded disk space to the operating system.

compact has the following form:

{ compact: <collection name> }

In you case after the db.runCommand(command) , you have got the message such as

{
    "ok" : 1.0
}

it means your command has successfully executed.

On WiredTiger, compact will rewrite the collection and indexes to minimize disk space by releasing unused disk space to the system. This is useful if you have removed a large amount of data from the collection, and do not plan to replace it.

I am also attaching the db.runCommand(command) picture from mongo shell command,for proper format understanding of that command.

WARNING:-
Always have an up-to-date backup before performing server maintenance such as the compact operation.

How to check the storage engine from the mongo command shell

  db.serverStatus().storageEngine

Note : -
As per MongoDB BOL  the WiredTiger Storage Engine From the MongoDB version 3.2: The WiredTiger storage engine is the default storage engine starting in MongoDB 3.2. For existing deployments, if you do not specify the --storageEngine or the storage.engine setting, MongoDB 3.2 can automatically determine the storage engine used to create the data files in the --dbpath or storage.dbPath.

For further your ref compact


Postgres FOREACH array as composite type I'm trying to loop over an array with two types as a composite type, but it seems that I can not use the composite type as the variable array to loop over it. What I'm trying to accomplish:

create type my_item as (
    field_1        timestamp,
    field_2        numeric
);

CREATE OR REPLACE FUNCTION unnest_2d_1d_my(ANYARRAY)
  RETURNS TABLE (ts timestamp, value numeric) AS
$func$
DECLARE
  a my_item[];
  timestamp timestamp[];
  value1 numeric[];
BEGIN
   FOREACH a SLICE 1 IN ARRAY $1 LOOP
      timestamp = array_append(timestamp, a.field_1);
      value1 = array_append(value1,a.field_2);

   END LOOP;
   RETURN QUERY select unnest(timestamp), unnest(value1);
END
$func$
LANGUAGE plpgsql IMMUTABLE;

Composite array types -> (timestamp, numeric):

CREATE TABLE test AS(SELECT * FROM unnest_2d_1d_my(array[['2018-01-01',2],['2018-01-02',5]]));

I'm expecting as an output:

ts           | value
'2018-01-01' | 2
'2018-01-02' | 5

but currently I only obtain the following error:

invalid input syntax for integer: "2018-01-01"

I wonder how can I work with a multi-type array to achieve the mentioned output.

An array always contains a single data type. ['2018-01-01',2] specifies an array with two different types: a varchar and an integer.

To initialize an my_type value you need a row() constructor:

row('2018-01-01',2)::my_item

Elements of that type can then be put into an array:

array[row('2018-01-01',2)::my_item,row('2018-01-02',5)::my_item

or you can simplify that by casting the whole array, which saves you from repeating the ::my_type cast for each element:

array[row('2018-01-01',2),row('2018-01-02',5)]::my_item[]

If you have such an array, there is no need for your own function. An unnest on it will return the result you want:

SELECT x.*
FROM unnest(array[row('2018-01-01',2),row('2018-01-02',5)]::my_item[]) as x;

returns:

field_1             | field_2
--------------------+--------
2018-01-01 00:00:00 |       2
2018-01-02 00:00:00 |       5


Commit or rollback SQLServer-SQLTransaction when no data changed we use the SqlTransaction-class in the backend to access data stored in the MS-SQLServer.
In some cases we definitely know that no data were changed by this transaction (and no errors occurred).
So the question is: How to close transactions in these cases?
Shall we use Commit() or Rollback(), or none of this both functions or something else?
The aim is to find the most performant way to finish the transaction in a "reasonable" way.

My advice
Think about it the other way around.
Only issue a ROLLBACK if you have a reason to undo everything since the start of the transaction--such as an exception, a bad state, or an explicit desire to undo everything. If everything is going as planned, then you should always COMMIT.
Why?
Issuing a ROLLBACK will result in SQL Server undoing any work that was performed inside the transaction.
Even if you "know" that SQL Server hasn't changed data, there's really no benefit in issuing a ROLLBACK unless you want SQL Server to undo changes because you're worried about inadvertent changes. If you are 100% sure that nothing has changed, then I can be 100% confident saying you should just issue a COMMIT instead.
If your code has made changes that are technically updates, but are logically no change, then a ROLLBACK would be extra work.
Here's an example:
First, let us create a new database that is fresh, clean, and pristine. It's as clean & fresh as new bed sheets:
CREATE DATABASE RollMeBack;

ALTER DATABASE RollMeBack SET RECOVERY SIMPLE;

USE RollMeBack
GO
--Create a table
CREATE TABLE dbo.WidgetQueue (
    QueueID bigint identity(1,1),
    QueueStatus char(1),
    SomeOtherStuff varchar(500)
    CONSTRAINT PK_WidgetQueue PRIMARY KEY CLUSTERED (QueueID)
);
GO
--put some stuff in it
INSERT INTO dbo.WidgetQueue (QueueStatus, SomeOtherStuff)
SELECT  QueueStatus    = CASE c2. column_id%3
                           WHEN 0 THEN 'Q'
                           WHEN 1 THEN 'S'
                           WHEN 2 THEN 'L'
                         END,
        SomeOtherStuff = c1.name + c2.name
FROM sys.columns AS c1
CROSS JOIN sys.columns AS c2;
GO 5

CHECKPOINT;

BACKUP DATABASE RollMeBack TO DISK = 'NUL;';

That'll make a nice big table. It probably doesn't need to be that big. Now, let's take a fresh CHECKPOINT and look at the transaction log. I get 3 rows returned. Doesn't matter what they say, that's not important right now.
USE RollMeBack
GO
CHECKPOINT;

SELECT *
FROM sys.fn_dblog(NULL,NULL);

Now, we're going to do an update, in a transaction, that logically is making no changes, but it's actually going to update a bunch of rows. For me, this runs in 2 seconds, and updates 1892100 rows setting the status to exactly the same value as is currently there.
BEGIN TRANSACTION
    UPDATE q
    SET QueueStatus = 'S'
    FROM dbo.WidgetQueue AS q
    WHERE QueueStatus = 'S';

Now, lets look at the transaction log again:
SELECT *
FROM sys.fn_dblog(NULL,NULL);

 For me, that returns 2916 rows--these are 2916 log records that record the transaction in the transaction log. Your mileage may vary. These log records are basically a measure of what SQL Server did during your transaction. All these log records tell us that SQL Server did a whole bunch of work inside that transaction. Logically, it had a net zero result, and the data will look exactly the same as at the start....except SQL Server actually updated those rows.
If I issue a ROLLBACK now, SQL Server is going to undo those updates. Logically, it's the same outcome as having left them alone, but SQL Server is just doing what it was told, and it has to go through those 2916 log records to untangle the word it did, and make sure it leaves everything exactly as it was at the start of the transaction.
If I issue a COMMIT instead, SQL Server just says "OK. DONE!" and logs the COMMIT without having to untangle work done.
And before we go, lets drop that database we created to test.
USE master
GO
DROP DATABASE RollMeBack;

Other Considerations
What if there was a trigger on my dbo.QueueStatus table? Then my update could have fired off a bunch of other work that I didn't realize, but is still important.
You're also putting the onus on the developer to know, understand, and maintain the fact that there was zero work done inside that transaction. Future changes to the application might introduce a code path that results in a change that should be persisted--now the application logic needs to be updated to know not to rollback.
What about logging? Even if it is just temporary logging during the development cycle to throw some stuff into a log table to try to troubleshoot a problem, that logging would be rolled back, too.


how to identify the default mail configurations In SQL Server, we can setup multiple email configurations out of which only some were set to default.

I have tried using the following query to get the list of accounts:

select *
from msdb.dbo.sysmail_profile p
    join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
    join msdb.dbo.sysmail_account a on pa.account_id = a.account_id
    join msdb.dbo.sysmail_server s on a.account_id = s.account_id

Is there any way to identify the default mail configuration profile?

Add a join to msdb.dbo.sysmail_principalprofile and check the is_default column to identify the default profile:

SELECT *
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1
JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id;


SQL SERVER Which Query is faster Which query is faster:
Query A
SELECT I.ItemName
FROM Location L
INNER JOIN Items I ON I.LocationID = L.LocationID
WHERE L.LocationID = 1

Execution Plan

Query B
SELECT ItemName
FROM Items
WHERE ItemID IN (
SELECT ItemID
FROM  Items
WHERE LocationID = 1)

Execution Plan

Neither. Try:
SELECT I.ItemName
FROM Items I
WHERE I.LocationID = 1

...and add a covering index on LocationID with INCLUDE column ItemName  .


SQL Server 2016 Query Crashes Server SQL Server 2016 continually crashes when running an ETL source query. Memory starts consuming at a fantastic rate then hits max and the service shuts off. This seems ridiculous, especially since the query runs perfectly on SQL Server 2014 with less memory and slower disks. <What could have changed and more importantly, is there a way to change server query or memory settings in 2016 that will prevent this crash scenario? Can anyone narrow the issue (and confirm it's a bug) or provide a more robust work around?

MAXDOP is set to 4, min memory is 8000 max is 24000 with 28gb on the system. The SQL 2014 machine had SharePoint running on it also (different SQL instance), and as a result has a lower max memory (12533mb). The SQL 2016 machine is an exact copy except it does not have the SharePoint install and was upgraded to SSDs.

The query only has 9 columns (6 INTs) for 7,122,737 records with two small table joins and minimal filtering... there's nothing that can change with the query and again it was working perfectly on SQL 2014. In fact, it runs a minute faster in SQL 2014 vs SQL 2016 despite the SSDs and almost twice the memory additions to a supposedly faster release!

So after a lot of searching, multiple different installs reverting to a database only install in default locations I have mostly identified the issue. One of the columns being hashed is a VARCHAR(MAX) which triggers a massive memory request from HASHBYTES. Not entirely unreasonable, even though the longest column is actually only 49 characters. Source system... work around will be to cast it dynamically I think.

If SQL Server reaches max memory, which it quickly arrives at with this query it crashes. This seems hit and miss, and may generate other errors instead, leaving the server up. Adding DISTINCT ensures the crash behavior unless excessive amounts of memory are present.

I've created a Microsoft Connect issue here if you have additional testing or ideas or can reproduce the issue. I'd say this is only partially the answer as it is still merely narrowed to a range of possibilities, that I don't have the time or capability to fully identify. However, it does not appear to be general memory pressure or a result of general SQL 2016 setup and services.

The following can reproduce the issue.

CREATE TABLE Testing (
    VarCharMAX VARCHAR(MAX) NULL,
    VarCharRealistic VARCHAR(100) NULL
) ON PRIMARY
GO

SELECT TOP 1000000
    IDENTITY(INT, 1, 1) AS Number
INTO Numbers
FROM master.dbo.syscolumns x
CROSS JOIN master.dbo.syscolumns
GO --Ensure 1 million rows were inserted

INSERT INTO Testing
        (VarCharMAX, VarCharRealistic)
SELECT
     CAST(Number As VARCHAR(MAX)) + 'a' AS VarCharMAX
    ,CAST(Number As VARCHAR(100)) + 'a' AS VarCharRealistic
FROM Numbers
GO

--Server crash under ~16GB memory
SELECT DISTINCT
    HASHBYTES('md5', VarCharMAX)
FROM dbo.Testing

I think I might have a legit answer .. and its a bug.

(similar was found when running checkdb in 2014 & fixed in SP1 + CU1)

When I run the query as is, SQL Server crashes

  2016-06-16 17:27:32.00 spid37s      Failed allocate pages: FAIL_PAGE_ALLOCATION 1

And in the error log I see :

MEMORYCLERK_SQLQUERYEXEC (node 0)                KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            10678248  ==> 10GB !!
2016-06-16 17:27:32.00 spid37s

With OPTION (MAXDOP 1) => serial execution

MEMORYCLERK_SQLQUERYEXEC (node 0)        KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          144

Please test it in your environment and let me know.


mysql - select random rows with max 3 rows per parent id I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it returns to me Ascending id's each time I try.

SELECT id, rest_id, rn
FROM (SELECT (@rn := if(@rest_id = rest_id, @rn + 1,
                    if(@rest_id := rest_id, 1, 1)
                   )
         ) as rn,
         meals.*
  FROM meals CROSS JOIN
       (SELECT @rn := 0, @rest_id := '') params
  ORDER BY rand()
 ) meals
WHERE rn <= 3
order by id ASC

If you want random rows per parent_id, your inner ORDER BY should arrange rows in a way that same parent_id rows are grouped together. So, instead of

ORDER BY rand()

it should be

ORDER BY parent_id ASC, rand()

As for this:

  it returns to me Ascending id's each time I try

The reason your query returns ascending IDs each time is because you have instructed it to return the results in the ascending order of id with this line at the end of the query:

ORDER BY id ASC

So, again, if you want the output to show same parent_id rows grouped together, add the parent_id column as the first sorting criterion to the final ORDER BY as well:

ORDER BY parent_id ASC, id ASC


To make a LVM backup of a MySQL database, would I have to reduce the logical volume size? I am trying to learn how to do a Ubuntu LVM backup of a MySQL database.

For this test I installed a fresh copy of Ubuntu 12.04.2 LTS on a new box, along with MySQL 5.5.  The box has one hard drive.  During the Ubuntu installation, I chose the partitioning option "Use entire disk and setup LVM".

Here is the space I have:

root@server2:~# df
Filesystem               1K-blocks    Used Available Use% Mounted on
/dev/mapper/server2-root 238088848 1206832 224787760   1% /
udev                        996264       4    996260   1% /dev
tmpfs                       402324     384    401940   1% /run
none                          5120       0      5120   0% /run/lock
none                       1005800       0   1005800   0% /run/shm
/dev/sda1                   233191   26733    194017  13% /boot

Ubuntu gave me a LVM setup of two logical volumes on one volume group:

root@server2:~# pvs
  PV         VG      Fmt  Attr PSize   PFree
  /dev/sda5  server2 lvm2 a-   232.64g 4.00m

root@server2:~# vgs
  VG      #PV #LV #SN Attr   VSize   VFree
  server2   1   2   0 wz--n- 232.64g 4.00m

root@server2:~# lvs
  LV     VG      Attr   LSize   Origin Snap%  Move Log Copy%  Convert
  root   server2 -wi-ao 230.68g
  swap_1 server2 -wi-ao   1.96g

The database data file is at /var/lib/mysql/:

root@server2:~# mysqladmin variables | grep datadir
| datadir  |  /var/lib/mysql/

Which is on the logical volume:

root@server2:~# df /var/lib/mysql
Filesystem               1K-blocks    Used Available Use% Mounted on
/dev/mapper/server2-root 238088848 1206836 224787756   1% /

There is not much free space left:

root@server2:~# vgdisplay server2 | grep Free
  Free  PE / Size       1 / 4.00 MiB

And finally I get to my question, in order to make a LVM backup of this MySQL database, will I first have to reduce the size of the logical volume so that there is more space?

Unfortunately, yes, you'll need to reduce the size of your root LV.

(You could reduce the size of swap, too, but that's already a fairly small volume)

Even if you can use that 4MB, a snapshot of your root volume will become invalid quite quickly when LVM runs out of "free" disk space.

You get prompted with a text box asking how much of your disk to use when you do an Ubuntu install (even if you select "use entire disk"). If you're building systems to muck around on, or virtual machines, I recommend setting this to 90% (leaving 10% for LVM) - you probably won't miss the space, and you never know when it'll come in handy.

The mechanics of how to shrink the root volume aren't too hard to find on the web, but questions about that are probably best suited to unix.se.

Update: Make sure you don't rely on LVM as your only backup strategy - if you change enough data on disk and your snapshot runs out of free LVM space to use, the snapshot will simply stop working. Using LVM is one of the only ways you can take file-based backups of MySQL (esp. in conjunction with FLUSH TABLES WITH READ LOCK) without shutting down the server, though, which is quite handy.


Sending a database dump Quite new to db admin here and I have a quick question.

I am collaborating with another colleague off site and they would like me to send them a dump of our (1.5GB sql db) database via email as they need to do some testing. The database contains personal info, and I am wondering if there is a safer way to do this?

We are unable to provide remote access to the db. Rather than zip the .sql file and email it, in order to ensure privacy/confidentiality etc what would be the best way to send this db?

Any help appreciated.

Of course you can put it up on Dropbox - or anywhere else.

Depends on how secure you want your data to be? Do you want the NSA looking at it? Dropbox employees?

I would put up an encrypted version of your mysqldump and securely transmit the key to your colleague - either face to face or over encrypted email, or have the password/key sent by courrier.

It all depends on how much the data is worth to you and/or your company. Is it worth a courrier? Do I encrypt my email? Those are questions only you can answer. If your internal system is good, then go with that.

The key (no pun intended) question is "how much is your data worth (now and in the future)"? You should afford your data as much protection as is reasonable given its value to you/your organisation.

If you're interested in these matters, go to the source of all wisdom - Bruce Schneier. "Security is a process, not a state". Once you start thinking in these terms, then solutions will become apparent. What is your data worth now? In a day's time? A year's? What would be the result if all of this data went live on internet now? If you wish to learn more, then I suggest you go to the crypto group - you ain't seen paranoid until you've lurked there for a while! :-)


How do I correctly design this database? I am not a database designer by any means.  I am a desktop programmer, but I am currently looking at implementing a website idea which will need a (possibly very large) back-end database.

In my site I will have users who can create and join "groups".  The number of users could be rather larger, as could the number of groups, and users could obviously be in more than one group if they so desire.

I am struggling with how to properly define this on the database end.

I could create a "groups" table with all the group information, and then another table that associates each user with groups (so each row in that table would have a user id and then a group id, and the user id could appear in multiple rows since a user could be in multiple groups).  Such a table would grow rather large with a large number of users, especially if users were involved in multiple groups.

A friend of mine suggested having a separate table for each group instead.  So every time a user creates a group, we create a table in the database which then gets populated with the user ids of each user that is in that group.  I wasn't so sure about this, because it seems like it would be a lot of overhead to create a new table for each group.

Also, coming from a desktop programmer's perspective, I really just want to create a class called "Group" and give it an array of user ids...but obviously I can't do that in the relational database world.

So, what can I do?  What's the best way to design this?  What will run most efficiently on a server?  Help enlighten a programmer who is wanting to learn a little bit about database design. :-)

Thanks!

Your first option is most likely the best. A table called users, a table called groups and a junction table called user_groups. User_groups allows for the many to many relationship between groups and users. As for size, what are you thinking in terms of "large"? 20,000 records? A million? It might be possible database types and programmers think differently in terms of "large", because I can't foresee this table having more than a few hundred thousand rows if the website gets popular and there are a lot of groups and that doesn't seem large. As long as the tables are indexed properly and the queries are written efficiently it should not be an issue...you would only be querying out of the table for specific users or specific groups anyway. Whichever RDBMS engine you use is designed to operate on tables structured this way. There's no good design reason to break the groups table into multiple tables - it means having to maintain new tables for each new group exactly as you said, plus you run into complicated queries where you want to bring back data from more than one group at once.


Procedure with Insert Data From One Table to Another Table I have two tables Person with PersonId, Name,Address,Mobile and PersonLog with Id,Name,Address,Mobile,FK_PersonId. I am trying store the old data in PersonLog and Person to be updated.

This is my procedure, but it is only updating Person and not storing selected(edited) data  from Person into PersonLog :

CREATE PROCEDURE [dbo].[UpdateInsertPerson]
(
@PersonId int,
@PersonName nvarchar(40),
@Address nvarchar(60),
@Mobile nvarchar(15)
)

AS

BEGIN

INSERT INTO
dbo.PersonLog(PersonName, Address, Mobile, FK_PersonId)
SELECT
Person.PersonId, Person.PersonName, Person.Address, Person.Mobile
FROM
dbo.Person JOIN dbo.PersonLog ON PersonLog.FK_PersonId = Person.PersonId;

UPDATE
    dbo.Person
SET
    PersonName = @PersonName,
    Address = @Address,
    Mobile = @Mobile
WHERE
    PersonId = @PersonID;

END

Any help?

I think you are just missing the @PersonID in the INSERT statement and there is no need to join to PersonLog. In other words, you should do this:

INSERT INTO
dbo.PersonLog(PersonName, Address, Mobile, FK_PersonId)
SELECT
Person.PersonId, Person.PersonName, Person.Address, Person.Mobile
FROM
dbo.Person
WHERE dbo.Person.PersonID = @PersionID  /*  Add this line */

Also, you will most likely want to surround the INSERT and UPDATE with a BEGIN TRANSACTION and an COMMIT TRANSACTION


Where clause to filter rows for Azure Stretch Database? Currently stretch database in SQL Server 2016 moves the entire table to Azure. However, this InfoQ article mentions an "archive row" mode for Stretch database that will allow you to filter which rows in a table to move to the cloud (ex. records before 2010). I haven't been able to find any official Microsoft documentation regarding this unreleased feature.

Can someone point me to an official source that says this is coming for RTM? I did find a French blog entry on MSDN mentioning it but nothing else.

Documented now for the latest CTP release. See how to write a TVF that a filter predicate uses to define what is a cold row (that gets moved) in a table with a mix of hot & cold rows: https://msdn.microsoft.com/en-us/library/mt613432.aspx


SSIS for/each loop to ETL tables across databases I have 100 pre-written SQL Server table insert statements, to pull data from a Progress database via openquery into 100 SQL Server tables e.g.

insert into table1 (col1 varchar(50), col2 varchar(10)) select X,Y from aprogresstable
insert into table1 (col1 varchar(40), col2 int, col3 varchar(15)) select X,Y,Z from anotherprogresstable
for 100 tables..

However, this is slow over openquery. Therefore, I'd like to import these data nightly from Progress into existing SQL Server tables using SSIS.

What is the best way to achieve this, without needing to create 100 data flow components (e.g. 100 sources, 100 destinations).

SQL Server Import/Export wizard is an option here but I've found it has data mapping issues with date fields.

Therefore, is it possible to create a For/Each loop to loop through 100 SQL insert statements via SSIS to pull data from Progress/Push to existing SQL Server Tables?

Thanks!

If I was dealing with your particular scenario, I would probably end up building a "mapping" table that had the [SourceTable] and [DestinationTable] tables.

Then I would use that table as parameters that feed into a cursor which Selects from [SourceTable] and pushes the records to [DestinationTable].

Of course, this would only work if all of the columns are the same accross all tables (or if you do "INSERT INTO [DestinationTable] SELECT * FROM [SourceTable]" which I wouldn't advise).

Pros to 100 components: Modular, allows for less changes if only one or a few needs to be changed.

Cons to 100 Components: 100 components to maintain and change, especially if it's a small change that applies to all of them.


Is there a way to obtain 'NaN' using only numbers? PostgreSQL can contains NaN (not a number) value in numeric and float columns and variables: https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-FLOAT

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=1fdf20510b1b6a1416754599c60fc93f

Is there a way to obtain NaN different of inserting or updating directy to such value?

In this question I considered that all of these are the same:

insert into t (v) values ('NaN');
insert into t (v) values ('N'||'a'||'N');
insert into t (v) values (E'\116\101\116');
insert into t (v) values (chr(78)||chr(65)||chr(78));
update t set v='NaN';

I try things like:

insert into numeros (id, n1, n2) values (5, 1e306/1e-306, 1e306/1e-306); -- not representable
insert into numeros (id, n1, n2) values (6, 0/0, 1/0);  -- division by 0

divide infinity by infinity.

jasen=# select cot(0)/cot(0);
 ?column?
----------
      NaN


Django Auth user as well as custom users table? So we've been using djangos database authentication for a while now and have recently decided we want to implement a completely new login system for our website.

We are keeping the django ORM as we rely heavily on it for the database modelling tool.

Anyway, the reason I'm asking this question is because we want to keep the Auth_user table so users can still use the admin panel!

So basically at the moment this is what's happen when a user signs up:

Signs up with FB, we grab their details
We insert their fb details to our NEW user database table
I'm trying to find the best solution for syncing this user table with djangos Auth_user table.

Any ideas?

I understand you might not still be looking but it might help someone else. The best way to do this, in my opinion, would be a simple OneToOne or ForeignKey on your new model to User. In the admin you can override the User admin and add an inline:

class FbDetailsInline(admin.TabularInline):
    model = FbDetails
    extra = 1 # Or however many you would like

class UserAdminWithFb(admin.ModelAdmin):
    ... # list display etc.
    inlines = [FbDetailsInline]

admin.site.unregister(User)
admin.site.register(User, UserAdminWithFb)

You can then set up a signal to update the relevant info and then view in the admin directly with the user.


What happens when the mirroring server goes offline (for a short while)? Let's say we have a principal server, a mirroring server (the one that serves as the failover backup) and a witness server.

Does it matter if I were to bring the mirroring server (not the principal) offline for a short while (five minutes, let's say). Is this going to break the mirror? Is there anything I need to do (such as turning the mirroring off temporarily on the mirrored databases) before I take the server offline?

There are plenty of resources online to explain what needs to happen when the principal goes down, but nothing I can find about this scenario.

I'd do a set partner suspend, then take the mirror down.

Some additional links:

Mirror backup & restore

Mirror backup script


Howto avoid textdata of trace gets truncated? I have a trace file including the TEXTDATA column created by a server-side tracing.
Some of the traced queries are very long.

If I re-open the trace file on Profiler, the relevant long query is displayed completely - it has 340 rows of text and 10951 characters.

But after I have imported the trace file into a SQL-Server table, the relevant query seems to be truncated. The table has a ntext column that had been created by the fn_trace_gettable method.

I queried the table using different methods:
The text output in SSMS (with maximized number of characters configured) interrupts the output at line 52.
The file output of SSMS (*.rpt) also truncates, here the query is getting truncated at line 250.

So right now I wonder if it's possible to get the WHOLE query out of the trace file into a SQL-Server table and how?

SSMS always truncates long strings unless typed as XML in which case you can set it to allow unlimited.

The workaround I usually use (from Adam Machanic's workaround to a connect item (internet archive link)) is below
SELECT (SELECT YourTraceDataColumn AS [processing-instruction(x)] FOR XML PATH(''), TYPE)
FROM YourTraceTable

This adds a few leading (<?x) and trailing  (?>) characters but otherwise leaves the data intact and without characters being replaced by XML entities.


Group by unknown ID, select all related rows where Column X = Y and Column X = Z I have a table that logs interactions from users with the following structure.

SELECT [SessionID]
      ,[Question]
      ,[Response]
      ,[DateCreated]
      ,[Terminal]
FROM [Quiz]
where Teminal in ('A1','A2','B1')

Output:

SessionID Question  Response  DateCreated              Terminal
--------- --------- --------- -----------------------  -------
123       Q1        A         2020-02-16 10:10:15.200  A1
123       Q2        A         2020-02-16 10:10:15.560  A1
123       Q3        B         2020-02-16 10:10:16.120  A1
124       Q1        B         2020-02-16 16:01:34.205  A2
124       Q2        B         2020-02-16 16:01:38.999  A2
124       Q3        A         2020-02-16 16:01:44.435  A2
125       Q1        D         2020-02-16 16:02:01.013  B1
126       Q1        A         2020-02-17 07:49:55.098  B1
126       Q2        A         2020-02-17 07:49:57.101  B1

A user reports they had issues during their interaction "the other day" but cant tell me the date or time, only it was one of those 'Terminals' (worked out to be A1, A2 or B1).

They also know they answered 'A' for 'Question 1' and also 'A' for 'Question 2'.

Is there away I can query the original 'Quiz' table (without creating a temporary table) and end up with an output something like the following that collates the response to each question into a related column on the one row per SessionID?

SELECT [SessionID]
    ,[Q1]
    ,[Q1DateTime]
    ,[Q2]
    ,[Q2DateTime]
    ,[Q3]
    ,[Q3DateTime]
    ,[Terminal]
FROM Quiz

Output:

SessionID  Q1     Q1DateTime               Q2     Q2DateTime               Q3     Q3DateTime               Terminal
---------  -----  -----------------------  -----  -----------------------  -----  -----------------------  -------
123        A      2020-02-16 10:10:15.200  A      2020-02-16 10:10:15.560  B      2020-02-16 10:10:16.120  A1
124        B      2020-02-16 16:01:34.205  B      2020-02-16 16:01:38.999  A      2020-02-16 16:01:44.435  A2
125        D      2020-02-16 16:02:01.013  null   null                     null   null                     B1
126        A      2020-02-17 07:49:55.098  A      2020-02-17 07:49:57.101  null   null                     B1

Or

SELECT [SessionID]
    ,[Q1]
    ,[Q1DateTime]
    ,[Q2]
    ,[Q2DateTime]
    ,[Q3]
    ,[Q3DateTime]
    ,[Terminal]
FROM Quiz
WHERE Q1 = 'A'
  and Q2 = 'A'

SessionID  Q1     Q1DateTime               Q2     Q2DateTime               Q3     Q3DateTime               Terminal
---------  -----  -----------------------  -----  -----------------------  -----  -----------------------  -------
123        A      2020-02-16 10:10:15.200  A      2020-02-16 10:10:15.560  B      2020-02-16 10:10:16.120  A1
126        A      2020-02-17 07:49:55.098  A      2020-02-17 07:49:57.101  null   null                     B1

Maybe you need

SELECT session_id,
       MAX(CASE WHEN question = 'Q1' then responce END) q1,
       MAX(CASE WHEN question = 'Q1' then DateCreated END) q1_datetime,
       MAX(CASE WHEN question = 'Q2' then responce END) q2,
       MAX(CASE WHEN question = 'Q2' then DateCreated END) q2_datetime,
       MAX(CASE WHEN question = 'Q3' then responce END) q3,
       MAX(CASE WHEN question = 'Q3' then DateCreated END) q3_datetime,
       terminal
FROM quiz
WHERE terminal IN ('A1','A2','B1')
GROUP BY session_id, terminal
-- HAVING MAX(CASE WHEN Question = 'Q1' then Response END) = 'A'
--    AND MAX(CASE WHEN Question = 'Q2' then Response END) = 'B';


SQL Server CE 4.0 network share access In our application we are planning to use SQL Server CE 4.0 using OLE DB. The connection string is shown below.

Dim strConn As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=\\pc137\d\desktop\mydb.sdf; SSCE:Database Password='password';"
Dim conn As New OleDbConnection
conn = New OleDbConnection(strConn)
conn.Open()

This one worked for both the local and network share files (.sdf)

We tried to connect using Visual Studio for the network share we get following error

  SQL Server Compact does not support opening database files on a network share.

We have referred this links

So is it allowed to connect SQL Server CE 4.0 using OLE DB in network share?

Does it cause any other problem?

No, this is not supported. And using the Oledb provider from a .net application is not supported either, use the Ado.net provider (System.Data.SqlServerCe)


Rebuild index in SQL Server does not reduce fragmentation I am trying to reduce the fragmentation of my indexes.

First of all I rebuild all indexes with fragmentation higher than 30%, but the most of my indexes still with high fragmentation or does not even change 1%.

Looking deeply, when I reduce the fill factor on the rebuild statement then reduces the fragmentation too.

Now, what is the best option?

A higher fragmentation with a high fill factor or reduce the fill factor and then gets lower fragmentation?

Thanks for now.

From this connect item Index Rebuild Doesn't Affect Fragmentation

  For small tables, usually performance impact on fragmentation is
  undectable. The first 8 page allocation would be from mixed extents
  and mixed extents could be anywhere in database files. Rebuilding
  indexes would not change this nature.

  If you have a small table, those mixed pages weight a lot during
  fragmentation calculation; therefore, rebuilding index may not reduce
  fragmentation. (As matter of fact, I could easily construct a case
  that fragmentation increases after rebuild.) Those fragmentation would
  not be a pain for your query performance; so basically you can ingore
  them.

  When page counts of an index reaches to certain big size (for example,
  1000 pages), then fragmentation may start to impact performance.
  Rebuilding index should reduce fragmentation.

Therefore probable reason rebuilding does not lower the fragmentation value due to fact that after rebuild the pages which are allocated to index is from Mixed extent.

Also, @Shanky has written a great article on same with in-depth analysis showing this behavior with a test scenario in What can Cause Index to be Still Fragmented After Rebuild


Adding Nullable Column To Production DB taking too much time I have running production DB which have normally 50-100 insert per minute. I am trying to add a new column in the production DB using,
ALTER TABLE MyDB ADD [MyColumn] varchar(30)
GO

This query keeps running and not finishing until I have stopped it. What else I can do?

Adding a nullable column like that is generally a meta-data-only operation.  That is it will complete nearly instantly as long as it can get a schema stability lock on the table for the short time it takes to add the column.
If you're seeing it take a long time, the cause is most likely that other sessions are blocking the ALTER TABLE statement.  Realize, too, that when you run the ALTER TABLE statement, while it's waiting to get a schema stability lock, it will block subsequent queries on that table.  You probably want to look at setting the lock timeout for your session prior to running the ALTER TABLE statement.
If blocking other sessions is problematic, you could wait until there is a period of lower activity, and run the command then.  Alternately, you could put the database into single user mode, alter the table, then put it back into multi-user mode - of course that would prevent any sessions from accessing the database during that time.
To put the database into single user mode and alter the table, you'd do something similar to this:
USE master;

ALTER DATABASE [MyDB]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

USE [MyDB];
ALTER TABLE [MyDB] ADD [MyColumn] varchar(30) NULL;
GO

USE [master];
ALTER DATABASE [MyDB]
SET MULTI_USER;
GO

Be aware though, setting the database into single user mode like that will cause any in-flight transactions to be rolled back, which, depending on how your client software is designed, might cause a loss of data.  Any transactions in flight at the time of the ALTER DATABASE statement would need to be re-executed once the database is brought back into multi user mode.  Take a look at the Microsoft Docs page for ALTER DATABASE, and in particular the section on WITH ROLLBACK, for various options that control how the server puts the database into single user mode.


can I quickly create tables for each partition? I have a table with two partitions and I would like to create two tables, one from each partition.  Is there a quick way to do this (a la partition exchanges) instead of a create table as select (CTAS) statement?

For what it's worth, this need arose because I want to exchange these two partitions into another table; from what I've read, in order to do a partition exchange the data needs to be in a non-partitioned table.

If you don't need the original data, you can exchange the partitions with empty tables that you create yourself.

Demo:

SQL> create table originaltable
(
  id number primary key
)
partition by range (id)
(
  partition original1 values less than (10),
  partition original2 values less than (20),
  partition original3 values less than (MAXVALUE)
);

Table created.

SQL> insert into originaltable values (9);

1 row created.

SQL> insert into originaltable values (11);

1 row created.

SQL> insert into originaltable values (55);

1 row created.

SQL> create table newtable1_lessthan10
(
  id number primary key
);

Table created.

SQL> ALTER TABLE originaltable
  EXCHANGE PARTITION original1
  WITH TABLE newtable1_lessthan10
  WITHOUT VALIDATION;

Table altered.

SQL> select * from newtable1_lessthan10;

        ID
----------
         9

SQL>


replicate master/slave without disturbing master for dump Is there a way to replicate master/slave without disturbing master for dump like mysqldump or cold copy of data files? I'm ok to bring down the instance only once for master to enable log-bin. But there is no feasibility for dump or even cold copy. As I have 400G of data and 1min down time only.

I would recommend Percona's Xtrabackup for this, especially since you mentioned all of your tables are InnoDB. From the page:

  Percona XtraBackup is designed to make backups a seamless procedure without disrupting the performance of the server in a production environment.

The tool will allow you to take a backup without locking innodb tables. It will do a lock to copy any MyISAM tables (such as the tables mysql in the mysql database) but those shouldn't take long.

I cannot tell you how long it will take on 400GB database, but it should do it without downtime.


Backup to Azure Storage Container: Shared Access Signature the only way? RESTORE FILELISTONLY FROM URL = '' WITH CREDENTIAL = '' fails in SQL Server 2016.

Copied script from SQL Server 2012 environment, successfully restoring from Azure Storage Container, with create credential statement, and it failed in 2016.

Error message "Forbidden".

Only after logging into the Azure Portal, creating a Shared Access Signature for the container, could I execute the restore, and only by not specifying the credential did the restore work.

No documentation advises that a SAS (Shared Access Signature) is required in SQL Server 2016 for Azure container backup / restore.

Is there another way to use T-SQL to access blob backups on Azure storage containers in SQL Server 2016, than using SAS?

MSDN - SQL Server Backup to URL SQL Server 2016

Looking at the MSDN tutorial on how to configure the backup service SQL 2016 introduces, a SAS is used. Refer MSDN Tut on SQL 2016 backup service

RESTORE FILELISTONLY FROM URL = '' WITH CREDENTIAL = '' WORKS in SQL Server 2016
IF your blob resides in a container in Storage Account (CLASSIC)...
Then Ola Hallengren's scripts work, and the world makes sense... even encryption works.


SQL Developer: how to script output to query results grid I am used to MS SQL Server Studio where I can DECLARE and SET variables and use them in my query, and then be presented with the results in a datagrid that I can copy and paste into Excel.

In Oracle SQL Developer, I can get simple query results returned in the 'Query Results' grid, but if I need to script something more complex (with variables), I need to use the 'Run Script' option and my results are ouput to the 'Script Output' window, where it's much harder to select the results for copying into Excel.

Here is example code, where the variable :v_yr is used three times:

var v_yr number;
exec :v_yr := 2014;

select job.employee_no,
mas.last_name,
mas.first_name,
pay.contract_salary,
pay.contract_days_actual,
CASE job.contract_year WHEN :v_yr THEN cal.contract_days_actual END,
pay.full_year_salary,
pay.full_year_contract_days,
CASE job.contract_year WHEN :v_yr THEN sal.annual_rate END
from hrs.employee_jobs job
LEFT OUTER Join hrs.employee_master mas on job.employee_no = mas.employee_no
LEFT OUTER JOIN hrs.employee_pays pay on job.employee_no = pay.employee_no AND
    job.job_seq_no = pay.job_seq_no
LEFT OUTER JOIN hrs.schedule_amounts sal on pay.schedule_id = sal.schedule_id AND
    pay.schedule_no = sal.schedule_no AND
    pay.schedule_level = sal.schedule_level AND
    pay.schedule_step = sal.schedule_step
LEFT OUTER JOIN hrs.calendar_master cal on pay.calendar = cal.calendar
where job.contract_year in (2013,:v_yr);

In my Googling so far, it seems that in order to display these results in a grid, I will need to set up this query as some kind of stored procedure, assign the results to a cursor.  I don't know enough about Oracle programming to understand this answer yet.

if you remove first two lines (var ... and exec), you can run this in sql_developer and get your results in a grid.

Now, if you must have variable declaration, you could replace the : to a & in "job.contract_year in (2013,:v_yr)" so it will prompt you to enter a value at run time.

If you must convert this to a procedure, then you need to go the ref_cursor route.


How to get First in and Last out with multiple records? I have this records and it is attendance of each employee. I'd like to get their attendance, what i have tried Query is like this which is getting their attendance but if it has a multiple record it will get the latest attendance.
Verify_States stands for 0 = Time in and 1 = Time out
This is the Query what i have tried :
select * from
(select A.*,

LEAD(A.Verify_State,1) OVER (PARTITION BY A.user_id
ORDER BY A.user_id, A.verify_date) as Nxt_row_State,

LEAD(A.verify_date,1) OVER (PARTITION BY A.user_id
ORDER BY A.user_id, A.verify_date) as TO_Date
from tbl_excel_attendance as A ) as tbl_DTR_Mapped
where tbl_DTR_Mapped.verify_state = 0 and tbl_DTR_Mapped.Nxt_row_State = 1

EDIT : I Also Tried This Query
    Select * from (
SELECT DISTINCT
       user_id,
       verify_state,
       DATE(verify_date) the_date,
       MIN(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) min_datetime,
       LEAD(Verify_State,1) OVER (PARTITION BY user_id
ORDER BY user_id, verify_date) as Nxt_row_State,
       MAX(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) max_datetime
FROM tbl_excel_attendance) as q
where q.verify_state = 0 and q.nxt_row_state = 1
ORDER BY q.user_id, q.the_date;

Sample SQL Fiddle
Desired Output :
id  user_id      verify_date    verify_state  Nxt_row_State    TO_Date
6      16    2019-11-06 07:00:00    0            1       2019-11-06 19:45:00
3      16    2019-11-07 07:35:00    0            1       2019-11-07 18:05:00
13     19    2019-11-05 06:57:00    0            1       2019-11-05 18:28:00
10     19    2019-11-07 06:42:00    0            1       2019-11-07 18:04:00
15     39    2019-11-07 07:39:00    0            1       2019-11-07 19:09:00
20     40    2019-11-06 20:39:00    0            1       2019-11-07 06:39:00

Which verify_date is time in and TO_Date is time out
The Problem is the night shift. I can't get the attendance of night shift with multiple rows.

Look for

with
cte1 as ( select *,
                 coalesce(lag(verify_state) over (partition by user_id order by verify_date asc),1) prev_state,
                 coalesce(lead(verify_state) over (partition by user_id order by verify_date asc),0) next_state
          from tbl_excel_attendance
),
cte2 as ( select *,
                 lead(verify_date) over (partition by user_id order by verify_date asc) next_date
          from cte1
          where (prev_state,verify_state,next_state) in ( (1,0,0), (1,0,1), (0,1,0) )
)
select *
from cte2
where verify_state = 0
  and next_date is not null
order by 2,3

fiddle


How to get data from multiple tables in one row I am looking for a way to get the table name and description field from all tables in my database that start with T_. What I have up till now is this:

DECLARE @tables TABLE(table_name VARCHAR(100), column_name VARCHAR(100), row INT);

INSERT INTO @tables (table_name, column_name, row)
   SELECT
       tables.name AS table_name,
       columns.name AS column_name,
       ROW_NUMBER() OVER (ORDER BY tables.name)
   FROM
       sys.tables
   INNER JOIN
       sys.columns ON tables.object_id =  columns.object_id
   WHERE
       tables.name LIKE 'T_%' AND columns.name = 'DESCRIPTION'

DECLARE @i INT
DECLARE @count INT

SELECT @i = MIN(row) -1, @count = MAX(row)
FROM @tables

WHILE @i < @count
BEGIN
    SELECT @i = @i + 1
    DECLARE @q VARCHAR(400)
    SELECT @q = 'SELECT  ''' + (SELECT table_name FROM @tables WHERE row = @i) + ''' AS TABLE_NAME, '+ (SELECT column_name FROM @tables WHERE row = @i) + ' FROM ' + (SELECT table_name FROM @tables WHERE row = @i)
    EXEC(@q)
END

This results in the data I need, however in multiple tables. How can I get that data in one table?

Build the SELECT statements
Add UNION ALL for all except the first row at the start

Easier than adding to the endexcept for the last row...

Use the XML PATH technique to concat rows into one string

Tested code:

DECLARE @sql nvarchar(max), @base nvarchar(max) = '';

SET @SQL =
    (
    SELECT
        CASE WHEN X.rn >= 2 THEN ' UNION ALL ' ELSE '' END + X.stmt
    FROM
        (
        SELECT
            @base + 'SELECT ''' + t.name + ''' AS table_name, ' + c.name + ' FROM ' + t.name AS stmt,
            ROW_NUMBER() OVER (ORDER BY t.object_id) AS rn
        FROM
            sys.tables t
            INNER JOIN
            sys.columns c ON t.object_id = c.object_id
        WHERE
            t.name LIKE 'T%' AND c.name = 'DESCRIPTION'
        ) X
    ORDER BY X.rn
    FOR XML PATH ('')
    );
PRINT @sql;
EXEC (@sql);

SQL Server 2012+ version:

DECLARE @sql nvarchar(max), @base nvarchar(max) = '';

SET @SQL =
    (
    SELECT
        CONCAT(CASE WHEN X.rn >= 2 THEN ' UNION ALL ' ELSE '' END, X.stmt)
    FROM
        (
        SELECT
            CONCAT(@base, 'SELECT ''', t.name , ''' AS table_name, ', c.name, ' FROM ', t.name) AS stmt,
            ROW_NUMBER() OVER (ORDER BY t.object_id) AS rn
        FROM
            sys.tables t
            INNER JOIN
            sys.columns c ON t.object_id = c.object_id
        WHERE
            t.name LIKE 'T%' AND c.name = 'DESCRIPTION'
        ) X
    ORDER BY X.rn
    FOR XML PATH ('')
    );
PRINT @sql;
EXEC (@sql);


How can I have SQL Server email me the error details when a job fails? SQL Server lets you configure a job to send email alerts when it fails.  This is a simple and effective way to monitor your jobs.  However, these alerts do not include any detail--just a success or failure notice.

If a job fails, this is what a typical alert email will look like:

JOB RUN:        'DBA - Consistency Check Databases' was run on 8/14/2011 at 12:00:04 AM
DURATION:       0 hours, 0 minutes, 0 seconds
STATUS:         Failed
MESSAGES:       The job failed.  The Job was invoked by Schedule 2 (Nightly Before
                Backup 12AM).  The last step to run was step 1 (Check Databases).

To determine the cause of the failure, you have to navigate to the instance in SQL Server Management Studio, find the job, and view its execution history.  In a large environment it can be a pain to have to constantly do this.

The ideal alert email would include the failure reason upfront and let you get straight to working on the solution.

I am familiar with this solution to this problem.  Does anyone have any experience with it?  Its drawbacks are:

you have to add a new step every job you have, and
you have to pray that no-one messes up the alert proc, spDBA_job_notification

Has anyone come up with a better solution?

Something you might do that is just a thought, throwing ideas out...

Create a single job that periodically checks the job table in msdb to see if any jobs show as failed, that can be done with a good T-SQL query. Then you could go into the sysjobsteps table and see if an output log is set for the job. Have a stored procedure send an email attaching that file to it. You would be able to see exactly what the job did from beginning to failure without having to touch the server.

Then could also have PowerShell script check the event log for errors. It allows you to filter down a pretty good bit to get exactly what message types you are looking for. You could set that up as a SQL Agent job to run periodically. Then in the PowerShell script use the email cmdlet to send the message if it finds one.

Far fetched ideas here, just some I thought about.