Pricing of databases in Azure I am a little bit confused about pricing for Data in Azure.
I found on one MS Blog that 50GB has a price of $125.

Missing part for me is, if I have needs only for a database in the cloud:

Do I pay only the amount of $125?
Or I have to pay separately for RAM, storage, processors and others parts which one server needed to operate?
What is the story about the performance of an Azure database?
Do all the databases have the same resources available to operate?

That blog is a bit old, so I don't know if everything is still accurate. In the Azure world things work very fast and information changes often.

Info from the main Azure site:

  The prices are per database per month!

  Database Size Price Per Database Per Month    Database Units (DU) that
  will appear on your Bill Greater than 10 GB to 50 GB  $45.954 for
  first 10 GB, $1.996 for each additional GB

  4.6 DU for first 10 GB
  0.2 DU for each additional GB

That means that if you have a single database that's under 50 GB (and higher than the previous category of 10 gb), you'll pay 125$ per month while the database is online.


How to remove tables and indexes? I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed.

How to remove table and indexes from a database? Is there a generic way to remove them in MySQL and PostgreSQL?

The generic way to "remove" a table is to drop it:

DROP TABLE foo;

When you drop the table all indexes will also be dropped. If you need to drop them separately use

DROP INDEX foo_index;

For PostgreSQL the easiest way to get rid of all objects in a schema is to drop the schema:

DROP SCHEMA test_schema CASCADE;

Of course you could have found out that yourself by simply looking at the manual:

http://www.postgresql.org/docs/current/static/sql-droptable.html
http://www.postgresql.org/docs/current/static/sql-createindex.html

http://dev.mysql.com/doc/refman/5.5/en/drop-table.html
http://dev.mysql.com/doc/refman/5.5/en/drop-index.html


How do I find the cumulative distance between geometry points in SQL Server 2014? I have a database designed to be a proof of concept for delivery of products from a central depot to customers distributed at random locations within ten miles of the depot. The depot location is expressed in absolute coordinates (0,0) and customer locations are stored in the database in two columns with Coord_X and Coord_Y. The code I have at present to determine which deliveries are due on a particular day is as follows:

SELECT t_Orders.Order_ID, t_Orders.Del_Sess, t_Customers.Contact_Name, t_Addresses.Cust_ID, t_Addresses.Town, t_Addresses.County, t_Addresses.Coord_Y, t_Addresses.Coord_X, t_Customers.Comp_Name, t_Orders.Cust_ID,
t_Orders.Del_Date
FROM t_Orders
JOIN t_Customers
ON t_Orders.Cust_ID=t_Customers.Cust_ID AND t_Orders.Del_Sess=1 AND t_Orders.Del_Date = '2016-05-21'
JOIN t_Addresses
ON t_Orders.Cust_ID=t_Addresses.Cust_ID AND (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X <= 0 AND SQUARE(t_Addresses.Coord_X) < SQUARE(t_Addresses.Coord_Y))
OR (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X >= 0 AND (t_Addresses.Coord_X) < (t_Addresses.Coord_Y))
ORDER BY SQUARE (t_Addresses.Coord_X) + SQUARE (t_Addresses.Coord_Y) DESC

This returns a list of all deliveries, with the furthest away first. I'd like to be able to take the output of this query and run queries on it (I looked into temporary tables and views, but can't seem to get this into one) to first remove any duplicates (e.g. multiple deliveries to one customer on a particular day as each order is treated as one delivery) and then run a query that says:

Starting at the furthest away, calculate distance to second furthest away, then from second furthest away, calculate distance to third furthest and so on until back at HQ.

I also have a geometry column for each customer location if that helps - e.g. is there a very easy way to do this using geometry, declaring HQ as a point first and letting SQL server work out the cumulative shortest distance back?

It's travelling salesman in a way, but a very simple version of it. I'm not looking for the shortest route per se. I'm looking for:

Order deliveries by distance from HQ
Draw line between furthest out delivery and HQ, which moves to through each point of delivery in order as determined earlier at 1
Measure length of that line.

This is the SQL I have at present to bring back the delivery locations and coordinates:

SELECT t_Addresses.Coord_X, t_Addresses.Coord_Y, t_Addresses.Cust_ID
FROM t_Addresses
WHERE t_Addresses.Cust_ID IN
(SELECT DISTINCT t_Orders.Cust_ID
FROM t_Orders
WHERE t_Orders.Del_Date='2016-05-21' AND t_Orders.Del_Sess=1)
AND (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X <= 0 AND SQUARE(t_Addresses.Coord_X) < SQUARE(t_Addresses.Coord_Y))
OR (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X >= 0 AND (t_Addresses.Coord_X) < (t_Addresses.Coord_Y))
ORDER BY SQUARE (t_Addresses.Coord_X) + SQUARE (t_Addresses.Coord_Y) DESC

OK, then this is the query, SQL Server 2012+ . I'll use test data instead of your query and omit all customer, order details but orderid. The query also doesn't use spatial features which doesn't help much here as we use no geography but plain geometry distance.

with points as(
select orderid, id, Coord_X, Coord_Y,
    rn = ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY SQUARE (t.Coord_X) + SQUARE (t.Coord_Y) DESC)
from (
    values
        (1, 1, 10, 20),
        (1, 2, 15, 30),
        (1, 3, 25, 5),
        (2, 1, 15, 20),
        (2, 2, 17, 30),
        (2, 3, 27, 8)
    ) t(orderid, id, Coord_X, Coord_Y)
)
select pfr.orderid, pfr.rn, pfr.id, pto.id,
    dist = SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)),
    cumdist = SUM(SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)))
        OVER (PARTITION BY pfr.orderid ORDER BY pfr.rn)
from points pfr
join points pto on pto.orderid = pfr.orderid and pto.rn=pfr.rn+1

May I say that this cumdist is far from being shortest in many cases.

Trying to use the second query from the question as a data source, not tested:

with points as(
select orderid, id, Coord_X, Coord_Y,
    rn = ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY SQUARE (t.Coord_X) + SQUARE (t.Coord_Y) DESC)
from (
   -- data source
    SELECT odrerid = 1, -- the data source returns single transportation order or route i suppose
        id = t_Addresses.ID -- probably you have it. It is intended to be a separate id for every point to be visited
        Coord_X = t_Addresses.Coord_X,
        Coord_Y = t_Addresses.Coord_Y
    FROM t_Addresses
    WHERE t_Addresses.Cust_ID IN (
            SELECT DISTINCT t_Orders.Cust_ID
            FROM t_Orders
            WHERE t_Orders.Del_Date='2016-05-21' AND t_Orders.Del_Sess=1)
        AND ( (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X <= 0 AND SQUARE(t_Addresses.Coord_X) < SQUARE(t_Addresses.Coord_Y))
           OR (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X >= 0 AND (t_Addresses.Coord_X) < (t_Addresses.Coord_Y)) )
    --
    ) t
)
select pfr.orderid, pfr.rn, pfr.id, pto.id,
    dist = SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)),
    cumdist = SUM(SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)))
        OVER (PARTITION BY pfr.orderid ORDER BY pfr.rn)
from points pfr
join points as pto on pfr.orderid = pto.orderid and  pto.rn=pfr.rn+1

I also changed the WHERE to:

WHERE t_Addresses.Cust_ID IN (..) AND ( (..) OR (..) )

My guess is you need proper Cust_ID AND points within a quadrant.

I made some assumptions, please correct me if they are wrong.


SQL Server Group By Very Slow unless using temp tables I have a simple query:

INSERT INTO #Values
SELECT 2, MONTH(f.Datum), SUM(Quant * Price) FROM r_Fakt_Otpr_Data fd INNER JOIN r_Fakture f ON fd.FK_Faktura = f.ID_Faktura
WHERE f.Type = 2 AND f.Stock = @stock AND f.Year = @year
GROUP BY MONTH(f.Datum)

It has about 10000 records to go through. It takes more than a minute.

If I omit GROUP BY and SUM it runs in less than a second.

Now, funny thing, if I split the query into SELECT and GROUP BY separately, using temp table, it runs in less than a second again:

CREATE TABLE #ValuesBuff
(
    Type int,
    Mnth int,
    Value float
)

INSERT INTO #ValuesBuff
SELECT 2, MONTH(f.Datum), (Quant * Price) FROM r_Fakt_Otpr_Data fd INNER JOIN r_Fakture f ON fd.FK_Faktura = f.ID_Faktura
WHERE f.Type = 2 AND f.Stock = @stock AND f.Year = @year

INSERT INTO #Values
SELECT 2, Mnth, Sum(Vakue) FROM #ValuesBuff
GROUP BY Mnth

Why would this be the case?

What should I do to instruct SQL Server to perform better. Obviously it is GROUP BY that is the culprit, but it's fairly simple query, and i can't wrap my head around it.

Best Regards,

Sounds like a bad plan to me - and possibly around statistics.

When you insert the data into a temp table, you can get statistics auto created on it.

If your statistics think there aren't many records for that combination of stock and year, it could choose a really bad plan. Try updating your statistics and seeing how it goes. Or put a better indexing strategy together, to help the join as well as the filter and grouping.

You could also post the sqlplans...


Naming Convention for Login, User and Roles? What are the Naming Conventions for a Login, A database User and a Database role?

Is there any best practice to follow?

As far as I know there won't be one documented for objects like logins users and roles. Just to make a note to keep the names not very familiar or similar to database name or any common naming convention that can be prone to hack. Check your company standards how existing environments uses the setup, as I am sure this won't be the first of them.


GROUP BY with OR/AND clause? Lets say my database looks like this:

Table: messages

+----+------+----+----------+----------+
| id | from | to | double_1 | double_2 |
+----+------+----+----------+----------+
| 1  | 1    | 2  | 0        | 0        |
| 2  | 1    | 0  | 1        | 2        |
| 3  | 2    | 0  | 2        | 1        |
| 4  | 2    | 1  | 0        | 0        |
| 5  | 2    | 3  | 0        | 0        |
| 6  | 2    | 0  | 1        | 3        |
+----+------+----+----------+----------+

Then in my selection from the table, I want to GROUP BY the following:

group rows where double_1 and double_2 is the same, while to = 0
group rows where from is the same, while to = $id and double_1 = 0 and double_2 = 0
group rows where to is the same, while from = $id and double_1 = 0 and double_2 = 0

Anyone have any ideas how to do that?

EDIT: As it seems that i did make some people misunderstanding this. I will now explain better.
As I say the ways that I would like to GROUP BY, then when saying group rows where double_1 and double_2 is the same..., then its grouping the rows where double_1 and double_2 are the same. Like I have those two rows:

+----+------+----+----------+----------+
| id | from | to | double_1 | double_2 |
+----+------+----+----------+----------+
| 1  | 1    | 0  | 1        | 1        |
| 2  | 1    | 0  | 2        | 2        |
+----+------+----+----------+----------+

They should not be grouped, as ID 1's double_1 AND double_2 is not the same as ID 2's double_1 AND double_2. While this would group:

+----+------+----+----------+----------+
| id | from | to | double_1 | double_2 |
+----+------+----+----------+----------+
| 1  | 1    | 0  | 1        | 2        |
| 2  | 1    | 0  | 1        | 2        |
+----+------+----+----------+----------+

Im very sorry for the misunderstanding, hope that someone here are still willing to help me out.

You can generate a flag column for each group that you need to get:

 CREATE OR REPLACE VIEW vw_messages AS
 SELECT id
      , `from`
      , `to`
      , double_1
      , double_2
      , case when double_1 = double_2
              and `to` = 0
             then 1
        else 0
        end flag_1
      , case when double_1 = 0
              and double_2 = 0
              and `to` = $id
             then 1
        else 0
        end flag_2
      , case when double_1 = 0
              and double_2 = 0
              and `from` = $id
             then 1
        else 0
        end flag_3
 FROM messages

This way you can later get the rows that accomplish either one or any combinations of groups.


Spool in Shell script not working for me #!/bin/sh

echo "Started at" `date +%Y/%m/%d" "%H:%M:%S`

sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF 2>&1 > /dev/null
SET SERVEROUTPUT ON
SET FEED OFF
spool ech.txt create
BEGIN
DBMS_OUTPUT.PUT_LINE('xyz');
END;
/
spool off
EOF

if [ $? -ne 0 ]
then
   echo " ech.sh sql   Error"
   exit 1
else
   echo " ech.sh  Successful"
fi

I am getting an SQL error message. Any ideas?

I took some code snippets from a backup script that I use.
You can redirect all output with the exec command, then
redirect it back. You can also surround everything with
braces and redirect that to a log file.

Personally I think bash is a better shell to program in,
if you don't have bash ksh works too.

#!/bin/bash

export MY_LOG_FILE=my_log_file.log

exec >${MY_LOG_FILE}
exec 2>&1

export ORACLE_BASE=/home/oracle/admin
export ORACLE_HOME=/software/oracle/product/11.2.0
export TNS_ADMIN=${ORACLE_HOME}/network/admin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export ORACLE_SID=mysid

echo "Started at" `date +%Y/%m/%d" "%H:%M:%S`

sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
BEGIN
   DBMS_OUTPUT.PUT_LINE('xyz');
END;
/
EOF

exec 1>&-
exec 2>&-
exec >/dev/tty
exec 2>&1

if [ `egrep -n "ORA-|RMAN-" ${MY_LOG_FILE}` -ne 0 ]
then
   echo " ech.sh sql   Error"
   egrep -n "ORA-|RMAN-" ${MY_LOG_FILE}
   exit 1
else
   echo " ech.sh  Successful"
fi


Unable to grant permission on table I've been given credentials to a postgres database that I'm told it has privileges to create new users and grant them access to tables. This is half true.

I wish to create a new user:

create user foo login encrypted password 'bar';
commit;

This is OK. Now I'm trying to that user grant some specific access to selected tables:

grant select, insert, update on table baz to foo;

The response is [42501] ERROR: permission denied for relation baz.

It seems that this user I've been given can't event select from the tables I wish to grant access to. select * from baz also gives a 42501 error.

The query select distinct(grantor) from information_schema.role_table_grants; returns one username (let's call it 'bob'). Should I be speaking with Bob about getting the privs I need?

At a guess, your user isn't the owner of the tables and hasn't been granted rights to them WITH GRANT OPTION so it can delegate them.

The database administrator must do something like:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser WITH GRANT OPTION

and should probably also GRANT similar rights on SEQUENCEs, on the schema its self, etc.

The WITH GRANT OPTION gives the user myuser the right to in turn GRANT the assigned rights to others.

See the manual for details.

You might also be interested in ALTER DEFAULT PRIVILEGES.


42702 'column reference "id" is ambiguous' for RETURNING of table-returning PL/pgSQL function This works:

CREATE OR REPLACE FUNCTION sql_fun()
RETURNS TABLE (id UUID) AS $$
  INSERT INTO foo DEFAULT VALUES
  RETURNING id
$$ LANGUAGE SQL;

SELECT *
FROM sql_fun();

This doesn't:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO foo DEFAULT VALUES
    RETURNING id;
  END
$$ LANGUAGE PLpgSQL;

SELECT *
FROM plpgsql_fun();

Is this a bug in PL/pgSQL? How can I fix this while keeping the return type as is?

The solution is to qualify all columns from RETURNING which have the same name as the columns from RETURNS TABLE with the name of the table which was INSERTED INTO:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO foo DEFAULT VALUES
    RETURNING foo.id;
  END
$$ LANGUAGE PLpgSQL;

If the name of the table is long and there are multiple columns, the name can be aliased:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID, foo INT, bar INT) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO table_with_a_very_long_name AS x DEFAULT VALUES
    RETURNING x.id, x.foo, x.bar;
  END
$$ LANGUAGE PLpgSQL;


Stored procedure unable to compare Hi I tried implementing the below stored procedure which always takes my if condition as true and implements .

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOpen`(in uid varchar(11),in S_ID varchar(20))
BEGIN
    if(SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID = 0) THEN

        UPDATE `smart_scheduler1`.`work_schedule` SET `Employee_ID`=uid WHERE `Schedule_ID`=S_ID;
        ELSE
        SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID;

END IF;
END

My question is how do I compare a query with a value or integer in stored procedure if condition?

I am novice in Database , please help me. I would be very pleased if you could get me a good tutorial on stored procedure as I was not able to find much materials on stored procedures online.

Your if condition should actually almost always be false, unless you have an entry where schedule_id = 0.

It may be surprising, but in MySQL you can write something like this:

root@localhost:(none) > select 1=1=1;
+-------+
| 1=1=1 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

root@localhost:(none) > select 1=1=2;
+-------+
| 1=1=2 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

So in your case you actually don't compare anything in your if condition, there is just a query which returns a number. And the if condition is true whenever it's not 0.

The solution here is to use parantheses.

IF((SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID = uid AND Schedule_ID = S_ID) = 0) THEN

Or you could do

IF NOT EXISTS(SELECT Slot_ID FROM smart_scheduler1.work_schedule where Employee_ID = uid AND Schedule_ID = S_ID) THEN

I personally like to use (NOT) EXISTS very much, not only because it's so easily readable, but EXISTS stops as soon as an entry is found and doesn't continue to search till the very end, although the exact number is of no interest.

If the exact number matters, you can of course also use variables like this:

SELECT @my_variable := COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID
IF(@my_variable = 0) THEN

or

DECLARE my_variable INT;
SELECT COUNT(Slot_ID) INTO my_variable FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID
IF(my_variable = 0) THEN

You can read about the differences of these two kinds of variables here and here.


SQL Availability Group restore database to standalone server Running: SQL Server 2012, Windows Server 2008 R2

I want to test a backup by restoring onto an isolated server. The backup was taken from an Availability Group (HADR) configuration, but the target server does not have the clustering/HADR configuration and I don't want to have to set that up.

When I try to perform the restore, this following is returned:

Could not process the operation. AlwaysOn Availability Groups replica manager is disabled on this instance of SQL Server. Enable AlwaysOn Availability Groups, by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable AlwaysOn Availability Groups, see SQL Server Books Online.

The message makes sense and I understand it, but have no way to take it out of an AG that doesn't exist on my target, and can't exist due to no cluster, etc.

Is there a way to make this scenario work?

That's the error you get when you restore the master database from a server with AlwaysOn Availability Groups configured, or try to restore additional user databases onto a server that had the master database from an AG replica restored.

Don't restore the master database from an AG replica onto a different SQL Server as the master database.

If you absolutely have to restore master to get user objects out of it, restore it as a different database (like master_old) and then transfer the objects you need.


Join tables in two databases make query slow? is better to partition db? I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2.

If I make a select query joining both tables, it's really slow to get the results.

A simple query like

SELECT *
FROM db1.dbo.table1 t1
LEFT JOIN db2.dbo.table2 t2 ON t1.k1 = t2.k2

is REALLY slow sometimes.

I not sure if this is common to SQL Server and "have to be like a rule" to "not join two tables that are from different databases".

In that case... I add to this question that I have one of those db binary data stored on a field and I like to separate from main db to not increase main table size... it is better to partition a db for this?

I tested with two simple tables and still joining those two tables is slow.

Thanks in advance for any help.

..Update some years later... 24-09-18

be sure that the fields that you are joining are of same type, size and collation.

Example: some property is varchar(255) and another varchar(20) ... that can be a problem because the engine have to cast one type to another (an implicit conversion occurs) and while sometimes it runs faster... if a reindex or some change in the db occurs, you can see that in some moment the query start to took a lot more time to complete....

If you cannot change the field type to match in one of the db/tables, try doing an explicit cast to see if that improves the query speed.
use
cast(fieldname as type(size)) = fieldName2)

You've got a bunch of different questions in here, so let's break 'em out individually.

Q: If I join two tables in the same database with the above query, why is it slow?

A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a WHERE clause to just get the data you need.

Once you've done that, note that you're using a LEFT OUTER JOIN. This tells SQL Server, "Not all of the table1 records will have matching records in table2." That's totally fine if it's true - but if you know all t1 records will have at least one t2 record, use an INNER JOIN instead.

Next, indexing starts to come into play - depending on the width of the tables and the numbers of fields, you may want to add indexes on the fields you're using for the join. To get good advice on that, it's best to post the actual execution plan you're working with.

Q: If I the tables are in different databases on the same SQL Server, does that change anything?

A: No. There's some interesting gotchas around things like default isolation levels in different databases, but for the most part, your queries should produce the same execution plans and speeds.

Q: Should I use table partitioning to make this go faster?

A: You mentioned database partitioning, but there's no such thing in SQL Server - I'm guessing you meant table partitioning. Generally speaking, no, I wouldn't jump to database design changes in order to make a join go faster. Start with the basics - understanding SQL Server execution plans - and only make database design changes to solve problems that you can't fix with things like indexes.


Oracle Can a Dropped View Be Recovered? I dropped a view 20 minutes ago.  Can I recover it? If yes, how?

Sure. For example: use flashback query on DBA_VIEWS.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> create or replace view v1 as select * from dual;

View created.

SQL> select sysdate from dual;

SYSDATE
-------------------
2019-11-29 12:30:48

SQL> select count(*) from dba_views where view_name = 'V1';

  COUNT(*)
----------
         1

SQL> select text from dba_views where view_name = 'V1';

TEXT
--------------------------------------------------------------------------------
select "DUMMY" from dual

SQL> drop view v1;

View dropped.

SQL> select count(*) from dba_views where view_name = 'V1';

  COUNT(*)
----------
         0

SQL> select text from dba_views where view_name = 'V1';

no rows selected

SQL> select sysdate from dual;

SYSDATE
-------------------
2019-11-29 12:32:17

SQL> select text from dba_views as of timestamp timestamp'2019-11-29 12:30:48' where view_name = 'V1';

TEXT
--------------------------------------------------------------------------------
select "DUMMY" from dual

This may or may not work, depending on whether you still have the required unto to perform the above flashback query.


How to identify who made changes in SQL server management? I'm working on the server using SQL Server Management Studio 2005. Is it possible to identify the person who made changes in the SQL server Management Studio 2005 tables data?

You have to log the data in some way manually.  My favorite is to include CREATE and UPDATE columns on each table as such:

ALTER TABLE TableName ADD [CREATE_TIMESTAMP] datetime NULL DEFAULT (getdate())
ALTER TABLE TableName ADD [CREATE_USERID] varchar(30) NULL DEFAULT (suser_sname())
ALTER TABLE TableName ADD [UPDATE_TIMESTAMP] datetime NULL DEFAULT (getdate())
ALTER TABLE TableName ADD [UPDATE_USERID] varchar(30) NULL DEFAULT (suser_sname())

Then add a trigger to force the update of the UPDATE columns.
After that you can tell who inserted or updated a row.  Still can't tell a delete though. You would need a log table for that.  Basically create a table TableName_Log that has the same structure as TableName then a DELETE trigger that moves the deleted rows (updated too if you want) into the log table.


Restore subscriber from subscriber's backup We have currently pull transaction replication with 1 publisher, 1 distributor and several subscribers. The replication is for several tables only.
I want to create new subscriber and this time restore it from backup. All the process and configuration is pretty simple, but I wanted to check if the following will work:

backup the publisher
backup one of the subscriber (right after no.1).
copy subscribers backup (which is much lighter) to the future subscriber and restore
define the new subscriber.

From my tests it is picking data and works ok, but I didn't test it heavily yet.
Is this practical?

Thanks in advance

All you have mentioned should be fine. Just remember to script out the  replication before doing this as a backup.

Script out the replication.
Take the database backup for Publisher.
Backup one of the subscriber (right after no.1).
Copy subscribers backup (which is much lighter) to the future subscriber and restore
Configure the new subscription accordingly.


Best Practice to Archive Old Data in MySQL I'm using a MySQL database to store logs from multiple servers. The database's growth on average is 50 GB per day. Its total size is now 1.7 TB.

It has ID and datetime columns.

I want to move data older than one week to another database on the same server.

What are the steps needed for this?

As of now I do this:

Create the Archive Database and table.
Insert into ArchiveDB.table select * from old_tbl where datetime
<= Archivedate
Then DELETE FROM old_tbl WHERE datetime <= Archivedate

I want to implement this with the following conditions:

Insert and delete via batches.
Reclaim the space after deleting from the old database.
Update stats without downtime.

...Or if you know of any best practice then let me know.

With InnoDB and PARTITIONs, you could set up daily PARTITION BY RANGE(TO_DAY(...)) and use "Transportable tablespaces" to disassociate a day from the table and move it separately.  This will be a lot faster than the queries it would take to do the INSERT...SELECT and DELETE.  Once detached, the partition (now a table in its own right) can be moved to another database or server without impacting the ingestion of more data.

One drawback:  InnoDB's disk footprint is a lot more than Archive's.

What kinds of queries do you apply to the logs?

Blogs:
partitioning
and
chunking lengthy deletes


Use of SET INTEGRITY for DETACH PARTITION I have some partitioned tables with DB2 LUW 10. We have one partition for each data.I am reviewing a DB2 stored procedure that runs daily detaching old data partition and creating a new partition because some days this stored procedure takes some hours to complete but usually it only takes some seconds. We found that the delay occurs in the detach processing,

According to some logic the stored procedure calculates how many partitions are going  to be detached (only one usually). For each partition to be detach the stored procedure issues

ALTER TABLE DETACH;
COMMIT;
SET INTEGRITY FOR TABLE XXX  IMMEDIATE ALL UNCHECKED;

Please can you explain me the function of the commit and set integrity commands in this stored procedure?

Under which conditions can this stored procedure takes some hours to complete the partition detach ?

Thanks you very much for your help

The COMMIT is fairly obvious; it's committing the effects of the prior ALTER TABLE DETACH... statement (and any statements prior to that and the preceding COMMIT/ROLLBACK - transactions are implicitly started in DB2). I assume there's a bit more to that DETACH statement but that it's omitted for brevity.

The SET INTEGRITY statement (I'd strongly recommend reading that page, especially the Notes section) is more complex. In this case it's bringing the table out of check-pending status by telling the database engine that the table's data still conforms to all the constraints on the table, but that the engine shouldn't bother checking that itself. This is as opposed to SET INTEGRITY ... IMMEDIATE CHECKED in which the database engine would check the data is correct.

The table has presumably been left in check-pending state by some earlier operation (I don't think detaching partitions causes check-pending, although attaching them certainly does - see ALTER TABLE for more information).

As the Notes section of the SET INTEGRITY reference page states:

  The fact that the table was taken out of the set integrity pending
  state without performing the required integrity processing will be
  recorded in the catalog (the respective byte in the CONST_CHECKED
  column in the SYSCAT.TABLES view will be set to 'U'). This indicates
  that the user has assumed responsibility for data integrity with
  respect to the specific constraints...

I'd strongly recommend reading the rest of the Notes section for more info on using IMMEDIATE UNCHECKED.


Need DDL for an Oracle Table that would be 1 row = 1 8K Block For performance testing in our performance environment we want a means to turn over SGA the way production does. Our performance environment is a fraction of production and production's very nature makes it difficult to simulate on the performance environment.

When we test on the performance environment we tend to be very focussed on the type of workload we are testing and on the data that we have configured for the scenario. The result of all of this is that after a few runs we get performance improvements that we are not likely to see on production because, on the performance environment we are churning over the same data again and again in cache.

Clearing SGA (its an Oracle environment) also clears system level buffers for table definitions and schemas that negatively impact performance the first time we test. The second time we test the cache is too full of data and are not simulating the IO properly.

The solution we have imagined is to have an app read data from some purpose built table to 'turn over' SGA in the same way diverse data and workloads turn over SGA in production.

The imagined solution has this app connect to each database on the cluster in turn (bypassing the connection load balancer) and reading a row from the database from a purpose built table. The app round-robbin's the connections reading a row in turn and returning some small portion of the data but forcing Oracle to read the block. Given that the purpose built table contains more row data in KB's than the total SGA of the cluster, and given that we throttle the reads we can then say that we turn over all SGA in N minutes, which is what we want. With this we should be able to simulate SGA turn over at different rates and thereby simulate how our test load behaves under the varying load levels that production experiences.

The above spec imagines a row of fixed size that will be small enough to fit into a block but large enough that no more than one row will fit into a block. This will make the calculations easier when calculating for how many rows are required to turn over SGA or what rate to read the rows at to turn over SGA say in 5 or 10 minutes.

My question is this - what will the imagined row definition look like given that we use 8K block sizes. What other parameters will be advised and what gotchas do we have to consider in this imagined solution?

Any competing ideas are most welcome.

If all you want to do is waste a lot of space, you don't need a fancy table at all. A single varchar(4000) (filled up) should be enough:

SQL> create table foo(a varchar(4000));

Table created.

SQL> insert into foo select rpad(to_char(rownum), 4000, '*')
                     from dual connect by level <= 1024 ;

1024 rows created.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks from user_tables where table_name = 'FOO';

  NUM_ROWS     BLOCKS
---------- ----------
      1024   1126

Reason: there's no room to put another 4000 byte row in a 8k block given the block and row headers.

Or play with a small row but a stupid PCT_FREE. For example:

SQL>  create table foo (a varchar(128)) pctfree 99 ;

Table created.

SQL> insert into foo select rpad(to_char(rownum), 128, '*')
                     from dual connect by level <= 1024 ;

1024 rows created.

SQL> commit ;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks from user_tables where table_name = 'FOO';

  NUM_ROWS     BLOCKS
---------- ----------
      1024   1126

Add an ordinary primary key and you can select whatever you want from there, with one row eating up about one block in SGA.

What you must really avoid is full-scanning that table (or anything that goes to direct reads and bypasses the SGA).

That being said, have you looked into simply reducing the SGA on your test system? If your volume of data (or the "width" of the dataset you can test) is X times smaller than production, start testing with X times less SGA and tweak (up or down) to get your IO subsystem to work. (You'll probably want more than X times less if your production SGA isn't large.)

I believe you'll have a better chance of having the SGA contents more representative of a real system (especially index/data block mix).


Measuring timing over many commands in psql The \timing command finds time needed for each individual update/query. But I have a .sql file with 10000 inserts and I want to see how much time it takes. How can I achieve this?

Use Unix time:
time psql -q -f script.sql


Workarounds for setting default schema for domain group? I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated (Using SSMA SQL Server Migration Assistant) it added a schema name onto the start of all of the table and procedures. Since then we have made lots of changes to some of the procedures within the database.

My problem now is that the application (.NET web app) is calling the procedures without the schema name prefixed. So when it calls the database it cannot find the procedure as it does not have the schema name at the start.

My client has made it clear that the only authentication that they want to use is Windows authentication. From what research I have done I can see that it is not possible to set a default schema for a domain group of users. and it would not be possible for us to add a user in the database for every user that will use the system from that domain group.

Does anyone have any advice on what I could do or a workaround for the default schema issue for domain users.

Thanks

Taking @Mr.Brownstone's idea, you could definitely try this:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA dbo TRANSFER custom_schema.' + QUOTENAME(name) + ';'
  FROM sys.objects
  WHERE SCHEMA_NAME([schema_id]) = N'custom_schema';

EXEC sys.sp_executesql @sql;

Now, it may be the case that you have foreign keys etc. that will need to be dropped and re-created (and I've shown how to automate that too), or as I mentioned in a comment, you may have explicit references to custom_schema in your existing objects. You may also have duplicate objects (say, dbo.Customers and custom_schema.Customers) that you'll need to handle. But the general idea should be sound.

Another workaround would be to upgrade to SQL Server 2012, where you can set a DEFAULT_SCHEMA for a domain group. I haven't tested this, but this Connect item says it has been made so.

In either case, I still think the app should be fixed eventually. As I've mentioned many times before, you should ALWAYS use an explicit schema prefix when creating or referencing objects.


Query product and product variants based on properties I'm trying to build a filter system for an e-commerce application. The following schema is given:

http://sqlfiddle.com/#!9/18b93

A product has many variants
A variant as has many property_values
A product has many property_values as well
A variant "inherits" the property_values of the related product

I cannot figure out the query to filter out all product variants that have for example property_id 1 = Red, property_id 2 = S and property_id 3 = Cotton. In this case the property_ids 1 and 2 are inherited from the product. The query sould also be flexible enough to be extended with any number of additional filters.

Can this be queried in a performant fasion? Would it make more sense to denormalize the data and keep a "index" table from these values?

Any help is greatly appreciated!

I followed @GerardH.Pille's advice and replaced the describable columns with a product_id and variant_id column. This way the querying is a lot simpler.


How do I set permissions for Package Body Here's my situation (slightly envaguened to protect the innocent): I have a package on a Test system and a package on Prod system that need to have identical permissions to access exiting packages and functions. Both systems are Oracle. I use the following query to check existing permissions:

with objects as (
  select owner, table_name, listagg(privilege, ', ') WITHIN GROUP (ORDER BYPRIVILEGE) AS PRIVS
    from all_tab_privs_recd
   where grantee = 'EABINTEG'
   group BY owner, table_name
   order by owner, table_name)

select ob.owner as OWNER, ob.table_name as NAME, ao.object_type as TYPE, ob.PRIVS as GRANTS
  from objects ob left join all_objects ao on ob.owner = ao.owner
                                          and ob.table_name = ao.object_name
  order by ao.object_type, ob.owner, ob.table_name;

The queries come back almost identical, except Test has some rows that say the account has EXECUTE permissions granted for the PACKAGE BODY. The administrator has run queries to grant my account execute on the packages, I can see identical EXECUTE permissions granted for the PACKAGE on both Test and Prod.

I've looked around and haven't found anything. Has anyone else run up against this? How do I grant EXECUTE privileges for a PACKAGE BODY. Is this a SYNONYM issue? Help! Thanks!

Anything else in addition to EXECUTE? When granting DEBUG, you implicitly "get" EXECUTE on the PACKAGE BODY (I replaced EABINTEG with U2 in your query):

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> grant create session to u1, u2;

Grant succeeded.

SQL> create package u1.p1 as
  2  procedure pr1;
  3  end;
  4  /

Package created.

SQL> create or replace package body u1.p1 as
  2  procedure pr1 as
  3    begin
  4      null;
  5    end;
  6  end;
  7  /

Package body created.

SQL> grant execute on u1.p1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.

SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------
U1    P1    PACKAGE             EXECUTE

Seems ok so far.

SQL> conn u1/u1
Connected.
SQL> grant debug on u1.p1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.

SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------
U1    P1    PACKAGE             DEBUG, EXECUTE
U1    P1    PACKAGE BODY        DEBUG, EXECUTE

If you revoke DEBUG now, the PACKAGE BODY row disappears.

SQL> conn u1/u1
Connected.
SQL> revoke debug on u1.p1 from u2;

Revoke succeeded.

SQL> conn u2/u2
Connected.
SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------
U1    P1    PACKAGE             EXECUTE


Why can't user with SQLAgentOperatorRole delete his own SQL jobs I created a login and a user. I assigned the user as the db owner of the database "test". Then I granted him the role SQLAgentOperatorRole so that he can delete/create/modify the jobs belonging to the database "test". But I can only view the jobs though I have granted SQLAgentOperatorRole. What should I do to allow this user to do anything to the jobs belonging to the database "test" ? Please advice.

According to the SQL Server documentation, a user with a SQLAgentOperatorRole can only modify jobs that it created itself.


Does READ UNCOMMITTED make any performance difference on static data? I have some static tables that gets a lot of select queries

I wonder if i add this to each query, does that make any performance difference?

SQL server 2014

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

here my wait times after i have executed the below query

    select *
from sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
order by wait_time_ms desc;

If your data is immutable mark the database as read only. The query execution engine recognizes this and skips locking. However the benefits are marginal. If locking is of concern it means that a) you have locking conflicts, which implies data is not immutable or b) you acquire too many locks, which implies large scans which needs to be addressed by adding an index as needed.

For more details read How to analyse SQL Server performance.


Good reason to convert LONGTEXT column to smaller width if field is smaller? We have a database that uses a LONGTEXT type for a column that does not need that much space. The maximum row size currently has about 6500 characters and that's with a few million inserts so it's very unlikely we will ever need more space.  Is it worthwhile to try to reduce the allocated size of this column?  Will we see performance improvements?

We are using MySQL 5.6 with InnoDB.

Thanks!

Let's do the math.

6500 characters, even in CHARACTER SET utf8mb4 takes no more than 26000 bytes.
TEXT has a limit of 64K bytes and needs a hidden 2-byte length field.
LONGTEXT has a 4-byte length field.
Let's say (for the 'math') that the average row length, including this text, is 3000 bytes.

Math...

Savings of switching from LONGTEXT to TEXT: 2 bytes per row
Percentage space savings:  2/3000 = 0.06%
Performance improvement:  Less than that.

Conclusion...  We have spent much more effort than this minuscule improvement is worth.  (But you probably learned some details.)


Subnetting IPv6 in Postgres I am using postgres 9.5, and I was wondering if postgres allows for any type of subnetting or calculations on IP addresses, specifically IPv6. I am aware they have the cidrformat for storing data like IpV4 and Ipv6 formats. However, can I do anything like find out how many client IP's are in the subnet stored in the DB?

For example. If I stored the IPv6 address of 2001:dd8::/64, could I find out how many host IPs there are (18446744073709551616, according to some calculators).

My guess is that the answer is NO, postgres does not support this.

As 3manuek mentioned, there are functions and operators to deal with IP addresses - be it IPv4 or IPv6.

I am wondering a bit where your number comes from.  The address range you gave translates to

2001:0dd8:0000:0000:0000:0000:0000:0000

which is an address range of length 1.

If you meant all possible addresses starting with '2001:dd8:', then the correct designation of this range would be 2001:dd8::/32, as it is the first 32 bits that are given.  According to a calculator tool, the resulting range is the following:

Start Range: 2001:dd8:0:0:0:0:0:0
End Range: 2001:dd8:ffff:ffff:ffff:ffff:ffff:ffff
No. of host: 79228162514264337593543950336

Meanwhile, if you omit the block size (/32), you get a single address (the one I showed above).

So, there is a way to get the number provided by the calculator, using builtin PostgreSQL functionality.  Our friend here is masklen():

SELECT round(2 ^ (128 - masklen('2001:dd8::/32'::cidr))::numeric);

             round
───────────────────────────────
 79228162514264337593543950336

The result is the same as form the calculator.  The rounding is there for removing the decimal point and the zeros after it, which are the result of casting to numeric.


Deadlock from mysqldump --single-transaction, is it possible? Is it possible to get a deadlock if you ran mysqldump with the argument --single-transaction ? or any other complication ?

If it is used to backup a live site with many other querys running at the same time.

Extra info:

The mysqldump process could take over few minutes to complete.

The tables are innodb.

EDIT

I am concerned about the deadlocks errors that could happen on the live applications relying on the database during the time where mysqldump is running.

PROBLEM

It is logically possible because --single-transaction gets  "thrown under the bus" if any ALTER TABLE commands are launched intermittently during the mysqldump (See  this post from mysqlperformanceblog.com).

What happens when a dump faces off against an ALTER TABLE ?

If session #1 is running the mysqldump on mydb.mytable, an ALTER TABLE from session #2 may pause because it needs to perform a table lock.
If session #1 is running ALTER TABLE on mydb.mytable, it could produce spurious results from session #2 trying to run mysqldump on that table (Possible empty table [missing data])

It is not so much a deadlock in the traditional sense, but it safe to say that the --single-transaction option can get its loyalty displaced and lose it point-in-time window from the first victimized table and all other tables after it.

SOLUTION

Setup Master/Slave Replication

Use binlog_format = ROW to have changes as granular as possible
Perform mysqldumps of the Data from the Slave

STOP SLAVE;
mysqldump --single-transaction ...
START SLAVE:

You should never encounter deadlocks as long as the Slave is used for reads and backups only.

GIVE IT A TRY !!!


Postgresql role_table_grants grantee differences between users I have two users [UserA, UserB] -- UserA is an existing user. I just created UserB and gave it GRANT ALL PRIVILEGES.

But when I try to see privileges by doing

select * from information_schema.role_table_grants

UserA sees a lot more rows than UserB in the public schema.

For example

UserA can see all the rows with grantee in "admin_group", but UserB can't.
UserA and UserB can see all the rows with grantee in "user_group".

This is confusing because I did not do anything special to "user_group" (that I know of). What additional permission do I have to give to UserB so it can see all rows in information_schema.role_table_grants.

One possible cause is that UserA belongs to "admin_group" and that UserB does not belong to "admin_group".
Please post output of following statements run by a superuser from psql CLI:

\du userA
\du userB
select * from information_schema.role_table_grants where grantee='user_group';
select * from information_schema.role_table_grants where grantee='admin_group';


How to check my MongoDB throughput and perfomance limit? How can I check how many queries per second my MongoDB is executing and whether it had reached the performance limit or not?

You can get your queries per second (and many other stats) using mongostat as well as many graphical tools (from MongoDB themselves and third parties). As for whether you have reached your maximum, that will depend on what you consider acceptable performance. If you are OK with the delay involved with fetching data from disk, then that would be vastly different than if you only find in-memory queries to be acceptable. It's basically impossible to say what your maximum is without (a lot of) context and data over time.


Designing a multi-tenant database for a scenario with multiple user types I'm developing a SaaS application for recruiting which has multiple user types such as Client, Recruiter, Panellist and Owner  etc. There is also a chance that the user types may increase.

The Owner has an Organization and this is what will be my tenant. Here I'm considering every owner's organization as a tenant. I'm using Django to develop this application and will be utilizing the schemas feature provided in the PostgreSQL, hence there will be a schema for every tenant.

What I'm planning to do is that there will be a global User table that will be kept in the public schema and stores generic user information such a first name, last name, email, password, etc. The User table will be used for logging into the site. And there will be additional tables for each of the respective user types that each have their own unique set of columns. These additional tables will be tenant/organization specific.

Now once the user logs into to site, he can then switch to his desired tenant/organization something similar to how workspaces function in Slack. Within the tenant/organization, the user can assume the role of the above-mentioned user types that were allotted by the Owner to the user. So the user can be a Recruiter in one tenant and he can also be a Panellist in another tenant.

There will be an UserOrganization junction table which will keep track of the fact that a User can belong to multiple Organization.

Here is a schema diagram of what I'm thinking:

NOTE: I did not make the schema connections for the tables in the second tenant because it was starting to get messy but for all intents and purposes assume that they are present over there.

My questions are:

What is exactly the best practice when dealing with multiple users in a multi-tenant scenario?
What are the deficiencies of my design?

Using multiple schemas to implement a multi-tenant scenario is a feasible solution if you have few tenants.  If you have too many tenants, you will end up with too many tables, and that will make life quite painful for you.

If you have many tenants, you could use one table for all tenants and use a column like tenant_id to identify them. If you don't want to trust to your application logic to restrict query results to the appropriate tenant, you could use row level security and let the database do it for you.

Another useful tool for the “single table” approach is partitioning. You can use list partitioning to split that large table in partitions based on the tenants. That way, you can bundle several (smaller) tenants together in a single partition. Partitioning will make it easier to delete all data of a single tenant (this is its main advantage), and it may save you an index on the tenant_id. Most queries won't get faster through partitioning though.


Pull data from one DB Server to another DB server I want to pull Few tables (33 Tables) from one DB server A to another DB server B.
I have Schema ready with few datas in DB Server B but when I try to pull data from DB Server A to DB Server B,
I am getting following  error:

The INSERT statement conflicted with the FOREIGN KEY constraint
"Table 1".
The conflict occurred in database"DB Server A", table "dbo.Parent Table",
column 'xyz'.

The code which I used for this Operation:
INSERT INTO server1table
(X,Y,Z)
from server2.database.dbo.server2table

So what are the possible ways to do this operation taking care of key constraints?

You have a few options.

Copy data in the correct order. Find the parent child relationships and insert data to the parent tables first.
Drop or disable the constraints, insert the data, then recreate or re-enable the constraints.
Use a tool like Redgate SQL Data Compare, which will identify the heirachy and copy data in the correct order for you.

If you want to know more about how to find foreign key constraints there is a great post here


Oracle sql combine several rows of the same table into one row I have a table where for each given ID, three rows are generated as shown. The first row is the header row:

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 10    | 0  | 0  | 1  |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 200   | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 2667 | 10000 | 1  | 0  | 0  |
+-------+------+-------+------+-------+----+----+----+

What im trying to achieve

+-------+------+-------+------+-------+----+-----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS  | SW |
+-------+------+-------+------+-------+----+-----+----+
| 12345 | MANX | Mango | 2667 | 10000 | 1  | 200 | 10 |
+-------+------+-------+------+-------+----+-----+----+

If it helps, if SU is 1, then CS and SW are always 0. If CS is 1 then SU and SW are always 0 and if SW  =0 then SU and CS are always 0.

So i would like to return:

The first 3 columns followed by
The value in the DEN and NUM columns where SU = 1 followed by the value in the SU column
The value in the NUM column on the row where CS = 1 followed by
The value in the NUM column where SW = 1 or 0 if SW = 0

I have tried googling a way forward and found self joins mentioned. i have tried this and  due to my limited understanding i just cant get it to work. I get back many more columns of data back !

I would greatly appreciate some help on this.

cheers

UPDATE

This is the SQL i tried to apply and the result was that each row was duplicated 3 times

select tab_su.id
,      tab_su.grp
,      tab_su."desc"
,      tab_su.den
,      tab_su.num
,      tab_cs.num as cs
,      tab_sw.num as sw
from   tab tab_su
,      tab tab_cs
,      tab tab_sw
where  tab_su.su = 1
and    tab_cs.cs = 1
and    tab_sw.sw = 1
and    tab_su.id = tab_cs.id
and    tab_su.id = tab_sw.id;

UPDATE AFTER RUNNING @ALEX07 CODE - RESULT WHEN ONLY ONE ROW EXISTS

BEFORE

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 8     | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+

AFTER

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | NULL | NULL  | 0  | 8  | 0  |
+-------+------+-------+------+-------+----+----+----+

WHAT I WAS EXPECTING - (so, no change expected)

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 8     | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+

PS: Alex07 code works well when there are 2 or 3 rows of data to be combined. How can the code be edited to ignore data that only spans one row?

ANSWER
I edited the second and third line of Alex07's code to be:

MAX(CASE WHEN SU = 1 OR CS = 1 THEN BUOMDEN END) AS BUOMDEN,
MAX(CASE WHEN SU = 1 OR CS = 1 THEN BUOMNUM END) AS BUOMNUM,

I think this will give you desired resultset :

select id,grp,itdesc,
MAX(CASE WHEN su=1 THEN DEN END) AS DEN,
MAX(CASE WHEN su=1 THEN NUM END) AS NUM,
MAX(CASE WHEN su=1 THEN SU END) AS SU ,  -- or just  "1 AS SU"
MAX(CASE WHEN cs=1 THEN NUM END) AS CS,
MAX(CASE
   WHEN sw=1 THEN NUM
   WHEN sw=0 THEN 0
   END) AS SW
FROM tab
GROUP BY
id,grp,itdesc


sp_MSforeachdb Continue on failure when running DBCC CHECKDB I have the following maintenance script

DECLARE @sql NVARCHAR(30) = 'DBCC CHECKDB(?)'
EXEC sp_MSforeachdb @sql

This checks every database for corruption. I have run this on my test server where I deliberately corrupted a database and as expected, the DBCC CHECKDB errors for that database, however, this then ends the script, so all the other databases that haven't yet been checked go unchecked.

Is there a way I can make the script continue on error so that the remaining databases get checked?

I have also tried Aaron Bertrand's sp_foreachdb and found the behaviour to be the same

1: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/ as well as Ola.Hallengren's SQL Server Integrity Check using the command DatabaseIntegrityCheck @Databases =  'ALL_DATABASES' which again gives the same behaviour

I have noticed management studio shows "Disconnected" at the bottom also

Since I can't in good conscious provide a solution that is undocumented, unsupported, and likely to skip databases, using my replacement, try adding TRY/CATCH:

EXEC master.dbo.sp_foreachdb
  @command = N'
    BEGIN TRY
      DBCC CHECKDB(?) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    END TRY
    BEGIN CATCH
      PRINT ''? failed.'';
    END CATCH';

Depending on the severity of the error, though, it's possible that it will sever the connection. The above works if you set two databases offline and then add the parameter @state_desc (required since naturally the procedure skips offline databases):

ALTER DATABASE db1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db2 SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

EXEC master.dbo.sp_foreachdb
  @command = N'
    BEGIN TRY
      DBCC CHECKDB(?) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    END TRY
    BEGIN CATCH
      PRINT ''? failed.'';
    END CATCH',
  @state_desc = N'OFFLINE';

Output:

db1 failed.
db2 failed.

If TRY/CATCH still fails in your scenario, you might either want to stop what you're doing and deal with that issue directly before proceeding with "all's ok" checks on the other databases or, at least once you've identified a database with an issue, bypass it altogether using the @exclude_list parameter added in the First Responders Kit:

SQL-Server-First-Responder-Kit/sp_foreachdb.sql


What options in /etc/mysql/my.cnf are applicable to percona-server-client-5.6? I installed percona-server-client-5.6 on Ubuntu; there is no mysql server installed on that same machine.

In /etc/mysql/, the only file is my.cnf. That file contains many sections of options and I am not sure which sections are applicable to the machine with only the client package installed.

I assume the [client] section of course, but why to the other sections exist at all? Will changing their values affect the mysql server the client connects to?

my.cnf:

[client]
...

[mysqld_safe]
...

[mysqld]
...

[mysqldump]
...

[mysql]
...

[isamchk]
...

Those other sections are there because they are included in the template cnf file from the package provider (Percona).

You can create a user specific cnf file in your home dir that looks like the one below and they will get used when you run the mysql command:

[client]
user=$myusername
password=$mypassword

n.b. if you're going to store your credentials in a cnf file make sure you  set the permissions to 400.


I'm trying to run an oracle database in virtual box under oracle linux I'm trying to run a oracle DB in VirtualBox and access it from my host mac OSX , i am using port forwarding to access my guest, I can telnet my guest on the port, however when i try to connect from SQL developer i get IO error got minus one from a read call.

have googled around, one potential gotcha was specific ips allowed in the sqlnet.ora config - not the problem,  any idea this is really annoying me now

Had the same issue. Turned out to be a firewall issue. Here is how I turned it off:

As root in terminal:
chkconfig iptables off
chkconfig ip6tables off
service iptables stop
service ip6tables stop

Then modify /etc/selinux/config and set SELINUX=enforcing to SELINUX=disabled.

A more elegant solution is to open the 1521 port in the firewall. You can do that in the System -> Administration -> Firewall menu. Add 1521 (tcp) under "Other ports".


How to check if staff member has end date with a Null value I have a database that records employees tasks throughout the organisation and when they finish etc., I need to identify if each employee, has at least one row with a null value in either finish.

Basically - show me any employee_no that does not have at least one row with a null value in column Finish.

There can be more than one null value but as long as there's one.

Sample database:

SELECT employee_no
FROM source_table
GROUP BY employee_no
HAVING COUNT(*) = COUNT(finish)

Is there a way to include the whole table in that sequence? – Chris
select serverproperty('ProductVersion') as [version], serverproperty('Edition') as [edition] and got Version 13.0.5102.14 of edition Devleoper Edition (64-bit) – Chris
This is (Microsoft) SQL server 2016 SP2. – Akina

WITH cte AS ( SELECT Employee_No,
                     Task,
                     Start,
                     Finish,
                     MAX(CASE WHEN Finish IS NULL
                              THEN 1
                              ELSE 0
                              END) OVER (PARTITION BY employee_no) have_nulls
              FROM source_table
            )
SELECT Employee_No,
       Task,
       Start,
       Finish
FROM cte
WHERE have_nulls = 0
/* ORDER BY 1,2,3 */


correlate subquery in hive SELECT ndo.sku ParentSKU, visitsWhenSKUWasOnline.s
FROM
Temp.NumberOfDaysOnline ndo
JOIN
(
    SELECT SUM(gasessiondata.sessions) as s
    FROM gasessiondata
    WHERE
    gasessiondata.date >= ndo.FromDate
    AND
    gasessiondata.date <= ndo.ToDate
  ) as visitsWhenSKUWasOnline
  ON 1=1

I can't use ndo.fromDate and ndo.ToDate inside of the subquery since hive does not support correlated sub queries. What could be an alternative for something like this?

Edit:

down vote
favorite
I have a table like this:

date          val

2016-1-1       8
2016-2-1       10
2016-1-2       30
2016-1-3       30

Now, I have two dates coming in from a different table, let's call them `fromdate` and todate

If fromdate was 1, 2016-1-1 and Todate was 2016-1-2, I need 8+30 = 38 as the final value

I need to check

SUM(val) of all those values dates corresponding to which are between the fromdate and twodate.

The syntax of you query is not often called "correlated subquery". A correlated subquery would be like this:

SELECT
    ndo.sku AS ParentSKU,
    (
        SELECT SUM(g.sessions)
        FROM gasessiondata AS g
        WHERE g.date >= ndo.FromDate
          AND g.date <= ndo.ToDate
    ) AS s
FROM
    Temp.NumberOfDaysOnline AS ndo ;

Your query is similar to a LATERAL join (CROSS / OUTER APPLY in SQL Server). This type of correlated joins has not been implemented in many DBMS.

It's easy to rewrite though so the join is done first and then the GROUP BY. This simple query should be supported by most, if not all DBMS:

SELECT
    ndo.sku AS ParentSKU,
    SUM(g.sessions) AS s
FROM
        Temp.NumberOfDaysOnline AS ndo
    JOIN
        gasessiondata AS g
    ON  g.date >= ndo.FromDate
    AND g.date <= ndo.ToDate
GROUP BY
    ndo.PK,             -- the PRIMARY KEY of the table
    ndo.sku ;           -- and any column used in the SELECT list

Seems that Hive supports only equi-joins and cross joins. Moving the condition to the WHERE clause might solve this:

SELECT
    ndo.sku AS ParentSKU,
    SUM(g.sessions) AS s
FROM
        Temp.NumberOfDaysOnline AS ndo
    CROSS JOIN
        gasessiondata AS g
WHERE
        g.date >= ndo.FromDate
    AND g.date <= ndo.ToDate
GROUP BY
    ndo.PK,             -- the PRIMARY KEY of the table
    ndo.sku ;           -- and any column used in the SELECT list


Large batch INSERTs / UPDATEs and DELETEs Performance problem I'm having a problem in finding the best option to approach the following problem (postgres 9.5):

I get update batches of about 100.000 rows at a time from another system. This happens every 10-15 minutes usually, but it's possible that I get multiple of these batches at the same time. The batches are separated by "category" and one batch only ever contains data from one. Every "category" gets an updated set every 10-15 minutes.
New rows get inserted, old rows deleted and still existing rows should get updated to the new values.

This poses the problem that the table emasses serious amounts of garbage data, the VACUUM processes run really slowly and general table performance is really poor.

Now I thought I could solve this problem by creating child tables for every "category" within the data and thus "sharding" the data.

Would this make sense in this case, or are there better options for me to persue?

Is vacuuming slow because you have inadequate IO throughput, or just because it is throttled too much?

The default throttling of autovacuum is not suitable for very write-intensive servers.  You should probably decrease autovacuum_vacuum_cost_delay or increase vacuum_cost_limit.  And I routinely set vacuum_cost_page_hit and vacuum_cost_page_miss to zero.  Page misses are inherently self-limiting as the vacuum process can't proceed until the page is delivered; so there is little reason to add intentional throttling on top of that.


Olson DB: which version in PostgreSQL? I have an application heavily relying upon timezones, and I am using PostgreSQL for storing and querying my data. I'm letting the user choosing their own timezone, by showing the list of postgresql's supported timezones, in their extensive form (i.e. Europe/Rome). All the information concerning time/date are crunched by PostgreSQL itself, whereas my scripting language (PHP) simply fetches data and serves it without altering time-based data.

When the user wants to modify her/his own timezone, I return the timezone list by using a query like this:

SELECT tz.name AS measure, tz.utc_offset AS utc_offset, tz.is_dst AS is_dst FROM pg_timezone_names() tz;

Users will be able to choose their timezone by specifying the extended timezone name tz.name. I'm not using nor allowing any timezone abbreviation in any way.

According to the official docucmentation I can change the timezone abbrevs, but I'm not finding any piece of information about the Olson DB's  version used by Postgres. I really would like to have control on the Olson DB, so:

Where can I find any information about the timezone's DB version used?
Is it possible updating the timezone's DB by replacing the newest official IANA version? Consider a legacy system with Postgres 8.4 and no chance of updating it.

Thanks so much guys

Currently (Jan 23, 2017)

PostgreSQL uses IANA (Olson) version 2016j
2016j is the latest version.
tz-updates are rolled out to supported major versions in point releases. 9.2 is still supported (current as of Jan 2017) with TZ upgrades.

You can see the change long for the tz database upstream here

  Where can I find any information about the timezone's DB version used?

check out the README in src/timezone/README which says "the code in this directory is currently synced with tzcode release 2016j."

  Is it possible updating the timezone's DB by replacing the newest official IANA version? Consider a legacy system with Postgres 8.4 and no chance of updating it.

It's already the latest version, but I imagine if it ever falls behind patches would be accepted. You can find the instructions to create the patch in the README file above.


SQL Globalization - DateTimeOffset What is the recommended best practice when it comes to storing date/time for an application that needs to support multiple timezones?

Which is best ?

Use the SQL DateTime data type and insert date/time as UTC (e.g. convert local time to UTC in application business layer and pass to DB already in UTC format)
Use the SQL DateTimeOffset data type which will record the offset relative to UTC?

Use the DateTimeOffset datatype.  Each server would store the data locally in its own timezone, storing the offset from UTC as well.  When data is gathered to a central area, you can easily convert to UTC for comparison purposes.


Postgres Slow Queries - Autovacuum frequency We've noticed the performance of our platform drop in recent weeks so I've run the following:

select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
from pg_stat_user_tables
where relname like 'core_%';

And noticed that our primary table hadn't been autovacuumed for more than a week. So last week I ran:

vacuum analyse verbose TABLENAME

Which seemed to help, but we've run into the same issue again now. Upon closer inspection, a lot of the tables have either never been analysed (auto or otherwise) and aside from the manual vacuum analyse run last week, none of the tables have been manually vacuumed, and a lot of the other tables haven't been autovacuumed since, at best, a few days ago, and at worse a few weeks ago.

My understanding of the terms is as follows:

vacuum: Clears out deleted records from disk
analyse: Updates the
query planner

In postgres.conf, the autovacuum property is commented out, but the documentation states that this is on by default, so my presumption is that even though it's commented out, it should still be on?

Can someone explain why the tables wouldn't be getting vacuumed and analysed frequently and more specifically, would these values not getting updated actually have that much of an impact on the system?

Info:
Postgres 9.1
OS: Ubuntu 12.04

Output of

SELECT relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

     Table       | Size | External Size
-----------------+------+---------------
"Primary Table"  | 27G  |     8232M

vacuum: Clears out deleted records from disk

Not exactly. VACUUM marks rows that aren't visible to any active transaction any more as dead (and ready for re-use). It does not shrink the physical file size that represents the table, except for completely dead / empty pages at the physical end. The manual explains everything and probably better than I could recap here.

  analyse: Updates the query planner

It's officially ANALYZE, with Z, but ANALYSE is accepted as alternative spelling, too. And it updates statistics used by the query planner. Again, the manual already provides the best explanation.

  In postgres.conf ... my presumption is that even though it's commented out, it should still be on?

That's correct. Again, consider details in the manual.

With that many write operations (a few thousand records a minute) your system is in bad need of regular VACUUM / ANALYZE runs. You have read the manual by now, so you understand the consequences. If your table ...

  hadn't been autovacuumed for more than a week.

... then that's bad for multiple reasons. Also consider @Daniel's answer how this may have transpired with a huge table like yours. Or maybe the high load constantly locks the table and never lets VACUUM do its work. Again, it's all documented in the manual. Here is a related case with good answers how to tune settings:

Aggressive Autovacuum on PostgreSQL

Remember that you can have per-table settings (STORAGE parameters) to fine tune for special needs of a special table and leave the rest of the system alone.

If you are mostly updating recently inserted rows, a FILLFACTOR below 100 may be very helpful. You can compact the table (once) with CLUSTER or pg_repack an then set the FILLFACTOR below 100. And for huge tables it may also help to set a higher STATISTICS target for key columns with irregular data distribution.

Configuring PostgreSQL for read performance
Are regular VACUUM ANALYZE still recommended under 9.1?

Also, if old rows are updated seldom, partitioning might be a good solution, to treat old sections differently. It really depends on the complete picture ...

Also, don't forget indices, those can get bloated, too. Only keep indices that you actually need.

To see a count of dead and live tuples:

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables


Sort operation SQL Server 2012 I am trying to optimize a query and the sort operation (91% cost) is the costliest operation. Any help will be appreciated.

Query is :

SELECT TOP 1
    ArchiveType,
    CONVERT(NVARCHAR(30), A.StartTime, 20),
    DateDiff(mi, A.StartTime,
             IsNull(A.EndTime, CASE
                                 WHEN ArchiveConfig.STATUS = 1
                                    THEN GetDate()
                                    ELSE ISNULL(B.StartTime, A.StartTime)
                               END)) TimeTaken,
    CASE
       WHEN A.Error IS NULL AND ArchiveConfig.STATUS <> 1
         THEN - 1
         ELSE A.Error
    END
FROM
    A WITH (NOLOCK)
INNER JOIN
    ArchiveConfig WITH (NOLOCK) ON Type = ArchiveType
LEFT JOIN
    B WITH (NOLOCK) ON LogID = A.ID
WHERE
    Type = 4
ORDER BY
    A.ID DESC, B.ID DESC

Table definition:

CREATE TABLE dbo.A
(
    ID int IDENTITY(1,1) NOT NULL,
    ArchiveType int NOT NULL,
    StartTime datetime NULL,
    EndTime datetime NULL,
    Error int NULL,
    NextAction int NULL,

    CONSTRAINT PK_ARCHIVELOG
       PRIMARY KEY NONCLUSTERED (ID ASC)
             WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
);

Table definition b:

CREATE TABLE dbo.B
(
    ID int IDENTITY(1,1) NOT NULL,
    LogID int NOT NULL,
    StartTime datetime NULL,
    ObjectType int NOT NULL,
    ActionType int NOT NULL,
    TableName varchar(500) NOT NULL,
    PatchNumber int NOT NULL,
    NumberRow int NULL,
    EndTime datetime NULL,
    Error int NULL,

    CONSTRAINT PK_ARCHIVELOGHISTORY
       PRIMARY KEY NONCLUSTERED (ID ASC)
              WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
);

CREATE NONCLUSTERED INDEX [NonClusteredIndex_20160205-153718]
ON dbo.b (LogID)
INCLUDE (ID, StartTime);

The ArchiveConfig table:

CREATE TABLE dbo.ArchiveConfig
(
    ID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_ARCHIVECONFIG
        PRIMARY KEY NONCLUSTERED
    , Type int NOT NULL
    , PatchSize int NOT NULL
    , FailureAction int NOT NULL
    , RetryCount int NULL
    , ServerName nvarchar(500) NULL
    , DatabaseName nvarchar(500) NULL
    , AuthenticationType int NULL
    , LoginName nvarchar(500) NULL
    , Password nvarchar(500) NULL
    , Runprearchive int NOT NULL
    , Prearchive nvarchar(4000) NULL
    , Runpostarchive int NOT NULL
    , Postarchive nvarchar(4000) NULL
    , ServerIP varchar(20) NOT NULL
    , Status int NOT NULL
    , LinkSrv varchar(500) NULL
);

First off, the sort operation on a simple query like this will probably always be the most costly.  That's because it's an operation that has to run after it has all of the data returned.  That being said, there are no clustered indexes on the tables.  That means they are heaps and they are storing the data totally unsorted on the disk.  Without adding a clustered index to those tables where your sort is applied, it's unlikely that any changes to your query will improve performance.

The only other thing that might help(might being a very big might), is move the 'WHERE type = 4' part into the corresponding join statement that it belongs to.  So instead of WHERE type = 4 it would be added to the JOIN ON for ArchiveConfig (I think that's where type comes from at least) as AND type = 4.  The other ON clause for that join seems redundant.  Keep in mind, I doubt very much that this change will actually improve performance but it will make your code a bit cleaner.

As the others have said, I don't know if it's a good idea to use the (NOLOCK) hint.  I doubt it will improve performance in this case and might result in bad data being returned.


Oracle copy DB from one windows server to another I am not an Oracle DBA but looking for the best approach to copy/clone an Oracle database from one windows server to another, here is my configuration and my requirements:

Configuration
Server 1 (Source)
Oracle DB Version: 10.2.0.4
OS: Windows Server 2008 Standard
Server2 (Destination)
Oracle DB Version: 11.2.0.3
OS:  Windows Server 2008 Standard
On the destination server, Oracle is installed but no DB is created

Requirements
1.  It is preferable that the source DB does not shutdown during the process, I switched to archivelog mode for this purpose
2.  The Source database size is around 200Gb and there is no enough space on the Source server to make an RMAN backup then copy the files to the destination server, I am looking for a way to back up the DB directly on the remote server (destination) but I am not able to do it through RMAN for Enterprise Manager

Sounds like a data pump using a network link would do the job.  It's always preferable to run it during quieter hours but if your source database is not under powered then you might be able to run it during regular hours without your user's noticing any difference in performance.

From the article

create a database link from the destination to the source
grant EXP_FULL_DATABASE on the source database to the user in the link
grant IMP_FULL_DATABASE on the destination database to the user with the link OR grant DATAPUMP_IMP_FULL_DATABASE role to the destination database user

impdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR logfile=impdpSCOTT.log remap_schema=SCOTT:TEST

Things to investigate

the PARALLEL options which will speed things up.
any custom tablespaces on the origin database?
are you going from the same character set to the same character set?
you do have a development environment to test this..


Handling Reporting Services and SharePoint Integration considering deprecation With one of our clients, we're currently faced with a dilemma. SQL Server Reporting Services Report Server (SharePoint mode) seems to have been deprecated, since the last version to support it is SQL Server 2016.
Now our client wants SharePoint integration with Reporting Services and SQL Server 2016 is currently supported, so there is the option to go that route. I just feel uneasy building something for a client with a technology that is essentially dead.
So, I've been researching on what sort of options there are going forward (SQL Server 2017 onwards). An article by John White pointed out that this is a good thing, because it allows for SSRS Native mode to be more tightly integrated into SharePoint. But his article is from the end of 2016 and points at future improvements that I'm battling to see.
Microsoft's own SQL Stream Blog addresses this issue in November 2016, with mention of three scenarios that they planned to focus on going forward. Namely: Embedding reports in SharePoint pages; Reporting on data in SharePoint lists; and Delivering reports to SharePoint libraries.
What I can find from Microsoft after that date is sparse. I only found one other article on the SQL Server Blog that mentions SharePoint, with the announcement of a "Report Viewer web part".
My question is, has anyone faced this dilemma before? And how what solution did you go with?
I know we can work with PowerShell scripts and create embedded reports on SharePoint, but it doesn't seem very elegant considering what "SharePoint mode" was.
EDIT: I realise I asked a question that warrants an answer that's more of an opinion than a fact. What I am interested in, is approaching Report Server - SharePoint integration from a SQL Server 2017 and newer perspective.

Now our client wants SharePoint integration with Reporting Services

If they just want to embed reports in SharePoint pages, or write reports consuming SharePoint List Data, go with a native-mode installation of SSRS or Power BI Report Server and use the Report Viewer Web Part, or just the Web Page Web Part for embedding.
You might suggest they go with Power BI Report Server as it not only has the SSRS functionality, but also the Power BI Interactive Reports that are the successor to the old PowerView reports in SharePoint.
SSRS SharePoint mode adds the ability to deliver scheduled reports to SharePoint Lists, but there's reasonable workarounds for that in Native Mode.  And adds the ability to manage report content (reports, data sources, etc) inside SharePoint, so that's the key difference between Native and SharePoint integrated mode.  If they really need that, go with SSRS 2016.  It will be supported until July 2026.


limiting db user password to 3 days Can any one help me with this query?

I want to change expiry date for db user to 3 days from current date, but I am not getting the query for the same.

I have installed Oracle Express 11g, it is sqlplus.

First, create a password policy (profile) that captures your time period. Then assign that profile to the user.

CREATE PROFILE SHORT_LIFE_PROFILE LIMIT
    PASSWORD_LIFE_TIME 3
    PASSWORD_GRACE_TIME 1/24;

ALTER USER rdj7 PROFILE SHORT_LIFE_PROFILE;

The password grace time is the number of days the user receives a "soft warning" saying their password will soon expire. During that period, they can still log in with the old password. After that time runs out, the password has expired and they are done. In this case, I've set that to 1 hour.

See the oracle docs for other parameters you can set in this profile.


SQL Server 2016 Collation and Internal table Glitch? My Steps:

installed SQL 2016 with collation SQL_Latin1_General_CP850_CI_AS
Restored Database to Stage instances with same collation as Database.(original Backup from 2012)
Ran > "C:\Program Files\Microsoft SQL Server\MSSQL13.STAGE\MSSQL\Binn\sqlservr.exe"  -m -T4022 -T3659 -s"STAGE" -q"SQL_Latin1_General_CP1_CI_AS"
this was to Changed the Instances and all database collation to SQL_Latin1_General_CP1_CI_AS. (what doesn't get changed User Type Tables)
Run Query

Select i.name, c.collation_name,i.internal_type,i.internal_type_desc
from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id
WHERE c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
AND object_name(c.object_id) NOT LIKE 'sys%'
AND object_name(c.object_id) NOT LIKE 'queue%'
AND object_name(c.object_id) NOT LIKE 'file%'
AND object_name(c.object_id) NOT LIKE 'spt%'
AND object_name(c.object_id) NOT LIKE 'MSrep%'

Will return the  internal tables with Old collation.

name         collation_name                  internal_type internal_type_desc
sqlagent_jobs SQL_Latin1_General_CP850_CI_AS   220         CONTAINED_FEATURES

Test 2:

Create New database on 2016 when the Instances collation is set to SQL_Latin1_General_CP850_CI_AS.
new database collation has SQL_Latin1_General_CP850_CI_AS.
Run this Query  where collation = SQL_Latin1_General_CP1_CI_AS

Select i.name, c.collation_name,i.internal_type,i.internal_type_desc
from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id
WHERE c.collation_name = 'SQL_Latin1_General_CP1_CI_AS'
AND object_name(c.object_id) NOT LIKE 'sys%'
AND object_name(c.object_id) NOT LIKE 'queue%'
AND object_name(c.object_id) NOT LIKE 'file%'
AND object_name(c.object_id) NOT LIKE 'spt%'
AND object_name(c.object_id) NOT LIKE 'MSrep%'

this will return results that don't match the current collation.
name          collation_name                internal_type internal_type_desc
sqlagent_jobs SQL_Latin1_General_CP1_CI_AS  220         CONTAINED_FEATURES

I don't think the application will be affected by this internal_table.
But still strange that the internal_table collation does not match, on Creating and after converting the instances to a new collation.

If I do the same process but this time on a 2012 Staging instances.

restore Database to a 2012 Stagging instances with collation SQL_Latin1_General_CP850_CI_AS
Ran > "C:\Program Files\Microsoft SQL Server\MSSQL11.STAGE12\MSSQL\Binn\sqlservr.exe"  -m -T4022 -T3659 -s"STAGE" -q"SQL_Latin1_General_CP1_CI_AS"
fix User Type Tables
Backup converted database.
restore database on SQL 2016 with collation SQL_Latin1_General_CP1_CI_AS.
Run Query

Select i.name, c.collation_name,i.internal_type,i.internal_type_desc
from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id
WHERE c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
AND object_name(c.object_id) NOT LIKE 'sys%'
AND object_name(c.object_id) NOT LIKE 'queue%'
AND object_name(c.object_id) NOT LIKE 'file%'
AND object_name(c.object_id) NOT LIKE 'spt%'
AND object_name(c.object_id) NOT LIKE 'MSrep%'

Nothing returns because I converted it in 2012.

If anyone can explain what is going?  Is this a bug?
Anyone know how to fix the internal table collation?

I have looked at the internal table columns collations across most of my test instances and they are all either SQL_Latin1_General_CP1_CI_AS or Latin1_General_BIN. Some instances are SQL Server Express LocalDB, which are always SQL_Latin1_General_CP1_CI_AS at the instance level (unfortunately), but I have a 2017 Express installed with Korean_100_CS_AS_KS_WS_SC at the instance-level and a 2019 CTP 2.3 installed with UTF8_BIN2 at the instance-level. I have DBs restored into the 2019 instance (from CTP 2.2, not an earlier version as in your case) with collations of French_100_CI_AS_SC_UTF8 and French_100_CI_AS.

I have another SQL Server 2017 Express instance currently using Estonian_100_CS_AS_SC at the instance and DBs levels, and that instance has been changed using the -q option many times for testing said option. It even has a Contained DB (containment_type = PARTIAL), and all columns in Internal Tables are either SQL_Latin1_General_CP1_CI_AS or Latin1_General_BIN, no exceptions.

I would not worry about these columns. They are clearly all MS Shipped objects that desire to keep their specific collation. And you will never have a time when 100% of collations are what you set the instance to. There are collations used for meta-data that have their own static collation. There are collations coming from expressions in system catalog views and functions with explicit collations set, etc. You can use the following query to see all of the collations that are not what you set the instance and database to:

SELECT OBJECT_SCHEMA_NAME(col.[object_id]) AS [SchemaName],
       ISNULL(obj.[name], OBJECT_NAME(col.[object_id])) AS [ObjectName],
       col.[name] AS [ColumnName],
       col.[collation_name] AS [Collation],
       obj.[type_desc],
       ISNULL(obj.[is_ms_shipped],
              OBJECTPROPERTY(col.[object_id], 'IsMSShipped')) AS [IsMSShipped],
       col.[object_id]
FROM   sys.[all_columns] col
LEFT JOIN sys.[all_objects] obj
       ON obj.[object_id] = col.[object_id]
WHERE  col.[collation_name] IS NOT NULL
AND    col.[collation_name] NOT IN (
                         CONVERT([sysname], SERVERPROPERTY('collation')),
                         CONVERT([sysname], DATABASEPROPERTYEX(DB_NAME(), 'collation')))
ORDER BY [SchemaName], [ObjectName], [ColumnName];

Some objects are not in sys.all_objects, such as sys.pdw_nodes_partitions, which is the same object_id everywhere (across instances and even versions of SQL Server, but did not exist in SQL Server 2012):

SELECT OBJECT_SCHEMA_NAME(-1046288262) AS [SchemaName],
       OBJECT_NAME(-1046288262) AS [ObjectName],
       OBJECTPROPERTY(-1046288262, 'IsExecuted') AS [IsExecuted],
       OBJECTPROPERTY(-1046288262, 'IsView') AS [IsView],
       OBJECTPROPERTY(-1046288262, 'IsMSShipped') AS [IsMSShipped];

Still, I am unsure how to explain the results you saw for Test 1. You said:

  Will return the internal tables with Old collation.

Based on everything I found across all of those versions, etc in the top part of this answer, it should not ever be possible to get a column in sqlagent_jobs to be anything other than SQL_Latin1_General_CP1_CI_AS. This could be a fluke with the database upgrade process when creating those internal tables. The "CONTAINED_FEATURES" tables did not exist in SQL Server 2012, so when you restored into SQL Server 2016, they had to be created at that point. And your 2016 collation is SQL_Latin1_General_CP850_CI_AS, which is the only connection I see so far.

I WAS ABLE TO REPRODUCE THIS!! I backed up a DB from SQL Server 2012 LocalDB that had a collation of Latin1_General_100_CS_AS_KS_SC. I then restored that into SQL Server 2019 (the instance-level collation being UTF8_BIN2). Checking the internal tables I saw that the 3 queue_messages_* tables and the filestream_tombstone_2073058421 table (all of which existed in SQL Server 2012) still had a collation of Latin1_General_BIN. BUT, the sqlagent_* tables and plan_persist_* tables (none of which existed in SQL Server 2012), all had a collation of Latin1_General_100_CS_AS_KS_SC (same as the DB that was restored).

I then backed up a DB from SQL Server 2014 LocalDB that had a collation of Latin1_General_100_CS_AS_KS_WS_SC. I then restored that into SQL Server 2019 (the instance-level collation being UTF8_BIN2). Checking the internal tables I saw that the 3 queue_messages_* tables and the filestream_tombstone_2073058421 table (all of which existed in SQL Server 2012) still had a collation of Latin1_General_BIN. BUT, this time the sqlagent_* tables (which did exist in SQL Server 2014), all had a collation of SQL_Latin1_General_CP1_CI_AS. The plan_persist_* tables (only 2 of the 6 existed in SQL Server 2014), all had a collation of Latin1_General_100_CS_AS_KS_WS_SC (again, same as the DB that was restored).

I changed the compatibility mode of the DB that was restored from SQL Server 2012 to be "SQL Server 2019 (150)", but that did not fix the collation issue.

This is clearly a problem with the Database Upgrade process, and has nothing to do with the sqlservr.exe -q method of changing all collations on a server (which is officially undocumented and unsupported, but I did document it here: Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?). I have reported this bug to Microsoft via the barely functional, substandard-even-by-2008-2010-standards reporting site, UserVoice: Restoring databases into newer versions of SQL Server will create missing Internal Tables with incorrect collation.

Regarding Test 2:

  collation = SQL_Latin1_General_CP1_CI_AS

Yes, it is to be expected that those internal tables will use that old, should-have-been-removed-by-now, collation.

Regarding Test 3:

  Nothing returns because I converted it in 2012.

Not true. Nothing returns because:

You filtered out the 3 queue tables via AND object_name(c.object_id) NOT LIKE 'queue%', and those use Latin1_General_BIN.
You filtered out all of the other tables via WHERE c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'.


How to effectively query and aggregate a normalized SQL db? In relation to Most efficient way to return multiple aggregates in a single stored proc?

I have an email type application and want to select all the messages (inbox) for a user. The problem is that I normalize the header part of the emails into the DB such that the flat data goes into a Message table and the from, to, CC, BCC get stored to another table.

What's the best way to select messages (in full - meaning denormalize the full message) so that each record contains all the message pertinent fields including all the Message table fields and any related records from the recipient table related to the message as per the PK/ FK relationship.

One thing I am placing much weight on is the efficiency of the SQL solution because this will be code that gets executed many many times over and will likely be the most run sql of the entire DB

For context here is a view of my DB schema.

This is how I would do it.  I regularly use Coalesce to place rows into delimeted fields and it always performs and scales well (as long as you realize that a subquery is ALWAYS going to cause some performance hit).

If you didn't like running it as a stored procedure you could also easily rewrite as a table valued function.

Another approach would be a CTE I suppose but I am not as familiar with that approach to type from scratch.

CREATE PROCEDURE GetMessageById
    @pMessageID int
AS
BEGIN

SET NOCOUNT ON;

Declare @pTo varchar(max)
Declare @pCC varchar(max)
Declare @pBC varchar(max)

SELECT @pTo = COALESCE(@pTo + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 1 /** or whatever the id of TO field is */

SELECT @pCC = COALESCE(@pCC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 2 /** or whatever the id of CC field is */

SELECT @pBC = COALESCE(@pBC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 3 /** or whatever the id of BCC field is */

SELECT Message.*, @pTo as [ToField], @pCC as [CCField], @pBC as [BCCField], (SELECT TOP 1 [EmailAddress] FROM MessageRecipient Where RecipientTypeID = 0 /**<sender id>*/ AND MessageID = @pmessageID) AS [FromField] FROM Message Where Message.ID = @pMessageID

END
GO

You might ask yourself how Coalesce works when used in this way ( I did, when I first saw it used ).  Basically it creates a recursive query returning each subsequent non-null value in the set, in turn, until the end of the return set.  Coming out the other end you get a coma delimited list of all results as a single string.


SQL Server Database Design for Reporting We are designing a reporting solution for survey results. Although datasets are reasonable in size (rarely more than 500.000 respondents and 50 questions), performance is obviously a major concern.

Due to the nature of the solution, most queries return aggregated values and no locks are needed.

Storing answers in a "normal" tabular format (i.e. a column for each question and a row for each respondent) works well in terms of performance, and allows us to query the data like so:

SELECT COUNT(*) FROM Answers WHERE Gender = 'M' and Age < 20

However, this design requires a new table for each survey as the questions (columns) differ, which is obviously not an ideal solution.

Therefore, we are considering a design where we store answer data in a table that would basically just hold a respondent ID, a question ID and an answer value, thereby "transposing" the data (i.e. there would be a row for each respondent/question combination in the Answers table).

In this design, we would have to use exists conditions (or joins) to filter our data, e.g:

SELECT COUNT(*)
FROM Answers AS A1
WHERE A1.QuestionID = 'Gender' AND A1.VALUE = 'M' AND EXISTS
(
    SELECT *
    FROM Answers AS A2
    WHERE A2.RespondentID = A1.RespondentID AND A2.QuestionID = 'Age' AND A2.Value > 18
)

This would allow as to handle any survey without changing the database schema but we are concerned about what the impact might be on performance?

Or perhaps there is a better way to deal with this issue altogether?

We did some performance tests and found that - with scenarios that are typical for our application domain - it is 10-20 times faster to query data when stored in a regular "tabular" format.


Slave can not handle replication events with the checksum that master is configured to log My slave is not connecting to the master and is showing the error

  Got fatal error 1236 from master when reading data from binary log:
  'Slave can not handle replication events with the checksum that master
  is configured to log; the first event 'mysql-bin.000002' a'

I have tried set global binlog_checksum='NONE'; and it works fine in masters but when I try to change slaves's cnf file binlog_checksum=none it fails and MySQL won't start after restarting it.

What can I do to resolve this?

Is your slave of older version than master? Say 5.5 or less.
As you say set global binlog_checksum='NONE'; fails on slave => you're on older slave and that is not required on slave as well.