Improving performance when ordering by a column of a joined table I have a parent table that contains a foreign key to a lookup table (simplified example):
CREATE TABLE [dbo].[Parent] (
    [Id] [uniqueidentifier] NOT NULL,
    [LookupId] [uniqueidentifier] NULL
)

CREATE TABLE [dbo].[Lookup] (
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](64) NOT NULL
)

In this case, the Parent table has over 10 million rows and the Lookup table has around 5,000. The real Parent implementation has several such foreign key references to other tables and each of those columns may contain NULLs.
Both example tables have unique clustered indexes for their Id columns, Parent has a non-clustered index for LookupId and Lookup has a non-clustered index for Name.
I'm running a paged query where I want to include the lookup value in the results:-
SELECT
    P.Id,
    L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY P.Id
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY

This runs quickly, as does ordering by P.LookupId.
If, however, I try to order by Name (or even L.Id), the query runs considerably slower:
SELECT
    P.Id,
    L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY L.Name
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY

The query plan for the second query is here: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
Other seemingly related questions seem to involve ordering by columns in the first table which could be resolved using an appropriate index.
I tried creating an indexed view for this query, however, SQL Server won't allow me to index the view because it contains a LEFT JOIN which I require because LookupId may be NULL and if I use an INNER JOIN those records would be excluded.
Is there a way to optimise this situation?
EDIT
Rob Farley's answer (thanks!) is great and works perfectly for the question as I originally asked it, in which I implied I was joining a single table.
As it is, I have multiple such tables and I was unable to reconcile all using INNER JOINs in order to use that solution.
For the moment I have worked around this by adding a "NULL" row to the lookup tables so I can use an INNER JOIN without losing any rows on the left.
In my case I use uniqueidentifier identities, so I create an indexed view like this:
CREATE VIEW [dbo].[ParentView]
WITH SCHEMABINDING
AS
SELECT
    P.Id,
    L.Name
FROM [dbo].Parent P
INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id

I then add a row to the Lookup table with a value of 00000000-0000-0000-0000-000000000000 for Id so there is always a match on the right of the join.
I can then create indexes on that view as needed.
Also, as I'm not using Enterprise, I found I needed to use the NOEXPAND hint to ensure those indexes are used:
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

Let's start by thinking about that first query.
You're joining between Parent and Lookup, but it's an outer join, so Parents are never removed from the results. I'm going to guess that Lookup.Id is unique, so therefore, no Parent is going to have multiple Lookups that it joins to.
Therefore, the 50000th row in Parent (ordered by Parent.Id) is going to be the 50000th row in the results if we don't have the OFFSET clause.
Therefore, the query can move past the 50000 rows for the offset, look at the next 50 rows, and use this to join to the Lookup table. It doesn't matter if the join doesn't find anything, it's a left outer join and it'll just return NULL.
If you order by a different column in Parent, and that's indexed, it can move past those 50000 rows just as quickly.
Now let's consider the second query.
You want the 50000 rows that you ignore (by the offset) to be the first 50000 based on the results of the join. Those 50000 rows may include some that are NULL, where the Parent.LookupId value doesn't exist in the Lookup table. Even if you have a nice index on Parent.LookupId, you'll probably need to involve most of the rows, because unless you find 50050 rows that don't join successfully, you'll need to keep going. Even 50050 is way more than the 50 rows you join on in the first query.
Now, if you have a foreign key in place then things might be a little different. Then, the SQL engine ought to know that if it has a value at all, then Lookup.Name isn't going to be null. So it could theoretically start by finding the ones that are null, to see if there are 50000 of them. But that's still a bit of a stretch, and the SQL engine is unlikely to produce a plan like this.
But you could.
So to solve the performance of the second query, I would do a few things.
Start by considering the ones that aren't null. That means the rows which are part of an inner join. You can make an indexed view on this, so that you can have an index which is in the order you want.
But you will also need the ones where Parent.LookupID is null - except that for these ones, you don't need the join at all.
If you do a UNION ALL across these two sets (and maybe include a constant column in both, to make sure that the NULL rows appear before the NOT NULL ones in your order by), you should be able to see some improvement.
Something like this:
SELECT ID, Name
FROM
(
  SELECT i.ID, i.Name, 2 as SetNumber
  FROM dbo.MyIndexedView i
  UNION ALL
  SELECT p.ID, NULL, 1 as SetNumber
  FROM dbo.Parent p
  WHERE p.LookupID IS NULL
) u
ORDER BY u.SetNumber, u.Name
OFFSET 50000 ROWS FETCH NEXT 50 ROWS ONLY;

Hopefully your plan will include a Merge Join (Concatenation) operator, so that it only pulls the rows that it needs from an Index Scan on the indexed view (in Name order) and an Index Seek on Parent (for the LookupIDs).


Where does Oracle store message properties for AQ messages? We create messages with various properties, like this:

    message :=  sys.aq$_jms_message.construct(0);
                  message.set_string_property('a', v_a));
                  message.set_int_property('b', v_b);
                  message.set_int_property('c', v_c);
                  message.set_string_property('d', v_d);
    DBMS_AQ.ENQUEUE(queue_name         => SOME_QUEUE,
                    enqueue_options    => queue_options,
                    message_properties => message_properties,
                    payload            => message,
                    msgid              => message_id);

And now I'm wondering where the message properties are actually stored.

General process to create an AQ

create an Object type
create a Queue Table
create the Queue

The queue message (1) is stored in a queue table (2).  You can view a list of queue tables by looking at ALL_QUEUE_TABLES.


File MDF growth SQL Server 2008 I have SQL Server 2008, I have 20 GB free on HD when I try to insert row in any table my HD was full before finished.

I try to insert about 200,000 rows each row with around 100 columns.

The operation consumed only space in my mdf file but not in my log file.

My destination table is empty, before today I don't have problems with this process.

Why is this happening?

If the database is set to "simple" mode, then the transaction log is truncated after the transaction.  Because you are only inserting 1000 records at a time, the transaction never needs to grow the entire amount.


How to use composite index in range query in mysql I have a table：

create table test (
   field1 bigint,
   field2 bigint
);

alter table test
add index field1_field2 (field1, field2)

Here is a sql :

select *
from test
    force index(field1_field2)
where field1 > 100
  and field2 = 2
limit 200 ;

and it doesn't run very well. I Explained it and found that only field1 condition used by index query, field2 condition was ignored.

I want know why, and I need a perfect answer.

There are lots of data with field1 > 100 and lots of data with field2 = 2.

You need a composite index on the columns (they are not fields!), but in reverse order, on (field2, field1) for this query.

When a condition has equality checks (=) and range/inequality checks (>, >=, <, <=, IN), then the columns involved in the equality checks should be first in the index and the inequality columns afterwards.

alter table test
    add index ix_field2_field1
    (field2, field1) ;

Additional notes:

A query with LIMIT and without ORDER BY will yield indeterminate results. Always use ORDER BY with LIMIT unless you don't care if you get different results per execution:

select *
from test
                          -- no need to force anything
where field2 = 2          -- the order here doesn't matter
  and field1 > 100        -- but is useful for developers
order by field2, field1
limit 200 ;

Really, choose some proper names for the columns, not "field1", "field2", etc.
Your table does not have a PRIMARY KEY defined. This is not good both from a theoretical and a practical point of view. Assuming that the table uses the InnoDB engine, then by not defining a primary key, InnoDB adds a  6-byte hidden column that is used as the clustered index. This also means that all your indexes have this 6-byte column attached, making them wider. I suggest you have a - preferably narrow - PRIMARY KEY in all tables.


Postgresql 9.5 JSON problems I have to update a complex query that takes JSON fields:

SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType
from
SELECT context->>'Source' as Product,
       context->>'SourceType' as Producttype ....

It uses the Product field at the bottom to search on.

But the original query uses

SELECT context::JSON, r.rboundaryid ...

and this doesn't work, giving a JSON error:

 invalid input syntax for type json

The 'context' field in the table is OK JSON as verified by Jsonlint.

I tried changing to

 SELECT to_json(context) as jcontext ...

changing the top ->> constructs to use the 'jcontext' name ... and this
eliminates the error ... but only because the result is NULL, not a JSON object ...

Any ideas on this?

The problem was that the screening part of the query was out of sync with the current database.

The corrected version uses:

context::json as contextobj

(like my effort)

WHERE typecode = 'IMAGERY'
    AND context != '  '
    AND r.createdon > '1/1/2016'

I didn't have this part.


Attach SQL Server 2008 R2 on SQL Server Express 2008 I have a database on SQL Server 2008 R2.

I want to attach it on a SQL Server Express 2008 instance running on Windows 2003.

I set the SQL server compatibility level on the database to SQL Server 2005 in SQL Server 2008 R2 Management Studio before detaching.

But I can't attach this database to my SQL 2008 instance running on Windows 2003 due to a "compatibility error".

How can I get around this?

You can't attach a SQL Server 2008 R2 database to a SQL Server 2008 or 2005 instance. You can only go up, not down; you can't make your 2008 R2 database compatible with previous versions. The solution is to remove your Express instance and replace it with a 2008 R2 instance, then you will be able to attach your database...


Select constant text that is the same as a column name I'm trying to select const text for an insert. However the text is the same as a column name and gets interpreted as such. How do I work around this?

INSERT INTO Table1 (a, b, c)
SELECT "a", a, b
FROM Table2

If Table2 looks like:

a, b
1, 1
2, 2

I want the inserted columns to look something like:

"a", 1, 1
"a", 2, 2

But with this SQL query, it interprets "a" as column a, which is clearly not what I want.

Values in SQLite should be wrapped in single-quotes, like:

INSERT INTO Table1 (a, b, c)
SELECT 'a', a, b
FROM Table2;

However, as @Ypercube asked in his comment, why do you want a column with the same value for every row?


Refactoring sql for tables with similar columns but different table names I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except that the table names are different. For example:

customerTransaction2011_Tbl

|ID | T.ID | Amount |
|---|------|--------|

CustomerTransaction2012_Tbl

|ID | T.ID | Amount |
|---|------|--------|

CustomerTransaction2013_Tbl

|ID | T.ID | Amount |
|---|------|--------|

The SQL script I have to re-factor is below:

Select ID, Sum(Amount), '2011' as [Year]
into #Tbl_threeYear
From CustomerTransaction2011_Tbl
union all
Select ID, Sum(Amount), '2012' as [Year]
From CustomerTransaction2012_Tbl
union all
Select ID, Sum(Amount), '2013' as [Year]
From CustomerTransaction2013_Tbl;

To re-factor this, I had thought of using Dynamic SQL within a stored procedure, so that I could pass the years (2011, 2012, 2013) as parameters to the stored procedure, which would append these to the Dynamic SQL string.

But turns out I can't be given permission to create a stored procedure.

So 2 questions, please:

If I simply make use of Dynamic SQL like so

declare @sqlmain as varchar(500);
declare @tblPart as varchar(100);
set @tblPart = '2011';
set @sqlmain = 'select ID, sum(Amount) from customerTransaction' + @tblPart + '_Tbl;'

How could I perform a union over the results from exec(sqlmain) while simply changing the @tblPart variable - must be possible somehow?

Also, would you rather another way of re-factoring such a script? Since this is my first time re-factoring, I would more than welcome any suggestions/criticism. Perhaps anybody could suggest how to re-factor in basic SQL?

This is all being done in SQL Server 2005.

Much appreciated.

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server

Step1: Create function to split tokens

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0
BREAK
END
RETURN
END

GO

Step 2: Set parameter value @varyears and execute query

DECLARE @varyears VARCHAR(200)
SET @varyears = '2012, 2011, 2013 ,2014'

DECLARE @combinedString VARCHAR(MAX)

SELECT  @combinedString = COALESCE(@combinedString + ' UNION ALL ', '')
        + CHAR(13) + ' Select ID, sum(amount) TotalAmount, ''' + item
        + ''' as [Year] FROM CustomerTransaction' + item
        + '_tbl  group by ID ' + CHAR(13) --group by clause added to your query for using aggregate function
FROM    dbo.fnSplit(@varyears, ',')

EXEC ( @combinedString )


SQL Syntax Error - Unsure if it is empty value or backslash I am getting a syntax error when the following query is being run:-

 INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,,'100/1')

I originally thought it was because there was an empty value between the two , as I can manually populate the empty value like below and it works fine:-

 INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,1,'100/1')

But I am not entirely sure, as the error reads SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use **near ''100/1')'** at line 1, query was: INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,,'100/1')

Please could someone point out what exactly the syntax error is? Is it the lack of value between the two commas or in fact the backslash in the last value - possibly not being escaped?

If you want to omit a column you can either apply a column list:

INSERT INTO mw_reward_point_order (col_1,col_2col_3,col_4,col_6)
VALUES(9477,0,0,144,'100/1')

or specify NULL:

INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,NULL,'100/1')


'Logical file name "MyDB" is already in use' Even though the database doesn't exist I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the system databases that install along with SQL Server.

My intent was to have DDAS pointing to its own drive, tempdb pointing to its own drive and transaction logs pointing to their own drive. The user account running SQL Server has Full Control access to all the drives, I made sure of it before doing anything.

I successfully moved tempdb using the following SQL statements:

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = N'F:\tempdb.mdf')
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'F:\templog.ldf')

I restarted SQL Server, voila, tempdb is relocated (can I safely delete the old tempdb files from their original location? Maybe that's another question...)

Now, because DDAS is still in development, I was able to delete all data from all the tables before doing anything to move it to its own drive. So now the file is ~5MB in size. I perform the same statements using the DDAS files:

ALTER DATABASE [DDAS] MODIFY FILE ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf')
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf')

I restart SQL Server and it doesn't work... I get DDAS (Recovery Pending) on the database in Management Studio indefinitely. I deleted the database completely because I have a script that will create all the objects etc... intending to just try to re-create it on the drive it is supposed to be on. I attempted to create the database with the following statement:

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DDAS')
BEGIN
    CREATE DATABASE [DDAS]
    CONTAINMENT = NONE
    ON  PRIMARY
        ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf' , SIZE = 1024000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB  )
    LOG ON
        ( NAME = N'DDAS', FILENAME = N'G:\DDAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
GO

but I get this error now:

I used this query to list file names being used by SQL Server:

SELECT DB_NAME(database_id) AS DatabaseName,
       name AS LogicalFileName,
       physical_name AS PhysicalFileName
FROM sys.master_files AS mf

and got this result:

How is this possible? I tried restarting SQL Server a number of times to no avail. I also checked that the original file on the C:\ drive that DDAS was using has been deleted, as well as its transaction log. There's no trace of DDAS anywhere other than this error message. I also have no open connections to DDAS that might have somehow lingered through all of those restarts.

If it matters: SQL Server Enterprise 2014 x64

If i am able to figure out correctly from below query

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf')

I believe tempdb to be [DDAS] here..

Also, in the CREATE DATABASE Query

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DDAS')
BEGIN
    CREATE DATABASE [DDAS]
    CONTAINMENT = NONE
    ON  PRIMARY
        ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf' , SIZE = 1024000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB  )
    LOG ON
        ( NAME = N'DDAS', FILENAME = N'G:\DDAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
GO

Above the logical file name of log file seems wrong, instead of LOG ON
            ( NAME = N'DDAS'....) it should have been NAME = N'DDAS_log'..

For more info please refer CREATE DATABASE


function return multiple json rows The function below returns a single row of data, and I have not been able to  modify it to  return all 2 rows.  Ive added a ROWS parameter at the bottom, then tried returning a SETOF, TABLE, and a few others - and all return errors. How can I return (using the date below) 3 rows, each formatted as json?  Thanks!

-- drop table mytable;
create table mytable (
retailerid integer not null,
retailername character varying(200),
createdate timestamp default current_timestamp,
constraint pkretailerid primary key (retailerid)
);
insert into mytable (retailerid, retailername) values (1000, 'Johns Burgers');
insert into mytable (retailerid, retailername) values (1001, 'Joses Tex-Mex');
insert into mytable (retailerid, retailername) values (1002, 'Anns BBQ');
-- drop view myview;
create view myview as select retailerid, retailername from mytable;
-- select * from myview;

-- with SETOF below returns "RETURN cannot have a parameter in function returning set"

-- drop function myfunc();
create or replace function myfunc()
  returns **SETOF** json as
$body$
 declare jrow myview%rowtype;
 begin
select retailerid,retailername into jrow from myview;
return row_to_json(jrow);
end
 $body$
  language plpgsql volatile
  cost 100;
--  select myfunc()

You need to also return multiple rows, not just a single value. You also don't need PL/pgSQL for this. A simple SQL function will do:

create or replace function myfunc()
  returns table (document json)
as
$body$
  select row_to_json(myview)
  from myview;
$body$
language sql;

returns table is the same as returns setof but you can define the column names.

You also need to put the function call into the from clause:

select *
from myfunc();

You don't really need function for that in the first  place. You could do that in the view directly:

create view myview
as
select row_to_json(t) as document
from (
  select retailerid, retailername
  from mytable
)t ;


MySQL recovery procedure After the server die, I have "old" HD (it is external now) with sock file and data files in mysql directory. Where I should find information how to restore the data in fresh installed MySQL?

What will happens if I replace files and folders brutal way?

UPD:

Old files have a version mysql-5.1.62-osx10.6-x86_64
New installation have a version mysql-5.6.22-osx10.8-x86_64

Since MYISAM is a filebased Databasesystem that stores Tables in files, it might be possible to just copy the folder of your database into your data folder of your MySQL installation.

BUT if you are using INNODB or something else for any of your tables then it might run into problems.

Easiest way to check that would be to just set up a mysql installation on any other machine (for the sake of systemsanity you can just set up an XAMPP or LAMMP and use that for short term) and copy your files into the data folder and check if all tables you know of are there and accessible.

Once that is done you can acces that seperate system to do an export via a backup Tool of your choice (mysqldump is also possible) and then input the backup you just made into the production server.

Next step would be to set up a nice backup for your new production machine (at least once or twice a day depending on the load of data that will be put into your machine) so that dataloss is limited.

If you want to be really shure and have verry frequent backups without killing your production system you can set up a replication system where the production server works as a master and replicates all data to the slave which will be the backup server. On the slave you then do the backup in a way you are comfortable (using mysqldump or some more sophisticated tool). In case the production server crashes again, you have your backups AND the slave server that could be easily swapped and work as a fast response short term replacement.


Adding a dropped check-constraint what queries the table I'm using a Postgres to preist a hierarchical data set; the table I'm using is something like this:

CREATE TABLE layers (
    bucket      text not null,
    uniq_id     serial not null,
    name        text not null,
    parent      text,
    value       text,

    constraint layers_pk primary key (bucket, uniq_id)
);

The name value should be unique in a defined scope, determined by parent value. In order to restrict and guarantee this I decided to use a CONSTRAINT CHECK, by a function relying on SELECT the current state of database.

CREATE FUNCTION layers_name_uniqueness_check(bk text, nm text, pr text) RETURNS BOOLEAN AS $$
    DECLARE
        cnt integer;
    BEGIN
        IF pr IS NULL THEN
            SELECT INTO cnt COUNT(*) FROM layers WHERE bucket = bk AND name = nm AND parent IS NULL;
        ELSE
            SELECT INTO cnt COUNT(*) FROM layers WHERE bucket = bk AND name = nm AND parent = pr;
        END IF;
        RETURN cnt = 0;
    END;
$$
LANGUAGE PLPGSQL;

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
;

This approach seems working well if provided from the starting point. If I want to apply this CONSTRAINT CHECK to a table that has already some records, this approach will not work boviously, as I'm running query on the table, and the given record is already there.

INSERT INTO layers (bucket, name, parent, value) VALUES ('bisphone', 'A', null, 'Hello');
INSERT INTO layers (bucket, name, parent, value) VALUES ('bisphone', 'A.A', 'A', 'Hello');

ALTER TABLE layers DROP CONSTRAINT layers_name_uniqueness;

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
;

The Output

[23514] ERROR: check constraint "layers_name_uniqueness" is violated by some row

I need to know that is it possible to fix this by a trick or should I address the problem (preventing duplicated name value in the the determined scope by parent) with a different approch. Any help?

By adding the NOT VALID option to the ALTER ... ADD CONSTRAINT statement it's possible to ignore the the validation of the CONSTRAINT on the existed data.

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
    NOT VALID
;


Where do you get Example/Sample Databases from? I'm looking for some example or sample databases (sales / production / employee / sports / Etc) to test my skills on other systems. So far my skills are limited to one sector education and i want to branch out see if the skill I've learned can be applied easily to other markets.

Ideally they would be large data sets. I can't seem to find any myself that are free or of any use. Format isn't much of an issue as i have used most forms of SQL, etc.

Thanks in advance.

For MySQL, there are sample databases used (frequently in examples) and they are to be found here (rather confusingly) in the "documentation" section, under "other docs" - here. I'm afraid that I can't help with SQL Server, but I'm sure that there are others here who can.

Example Databases Title  Download DB HTML Setup Guide    PDF Setup Guide
employee data (large dataset, includes data and test/verification suite)      Launchpad
world database
sakila database
menagerie database

You might also want to check out my reply here to a poster who was looking for NoSQL datasets (shouldn't make a difference whether it's NoSQL or not).


MySQL 5.6 Slaves Fail to Reconnect to older MySQL server when it goes down for backups I have an existing replication setup with Masters and Slaves running MySQL 5.0. A long existing script runs at night which restarts MySQL on the master with skip-networking, runs a backup, and then restarts again without skip-networking. The slaves lose connection but then retry to connect every 60 seconds until they successfully connect a couple of minutes later.  Here's what the log looks like on an existing slave:

150520  4:12:01 [Note] Slave: received end packet from server, apparent master shutdown:
150520  4:12:01 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.<binlognumber>' position <binlogpos>
150520  4:12:01 [ERROR] Slave I/O thread: error reconnecting to master '<user>@<host>:<port>': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'  errno: 2013  retry-time: 60  retries: 86400
150520  4:14:01 [Note] Slave: connected to master '<user>@<host>:<port>',replication resumed in log 'mysql-bin.<binlognumber>' at position <binlogpos>

I introduced a few new slaves that are running MySQL 5.6. They are able to replicate the existing databases without any problems or errors and data is consistent. Everything seems to be working except at night when the original Master goes offline for backups the new servers fail to reconnect on their own.

This is what the log looks like on the new servers in the morning:

2015-05-20 04:12:01 32401 [Note] Slave: received end packet from server due to dump thread being killed on master. Dump threads are killed for example during master shutdown, explicitly by a user, or when the master receives a binlog send request from a duplicate server UUID <newslaveUUID> : Error
2015-05-20 04:12:01 32401 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.<binlognumber>' at position <binlogpos>
2015-05-20 04:12:01 32401 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2015-05-20 04:12:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 1, Error_code: 2003
2015-05-20 04:13:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 2, Error_code: 2003
2015-05-20 04:14:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 3, Error_code: 2003
2015-05-20 04:15:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 4, Error_code: 0
2015-05-20 04:16:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 5, Error_code: 0
2015-05-20 04:17:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 6, Error_code: 0

All I have to do is go into the servers and issue a STOP SLAVE; , START SLAVE; and everything starts right back up again without trouble. It just won't do it on its own.

If I replicate between 5.6 servers and one goes offline the 5.6 slaves can reconnect to the 5.6 master just fine.

The replication user has only replication client and replication slave privileges on everything. The Connect_Retry value is 60 on both new and old slaves.

While replicating from a MySQL 5.0 Master to MySQL 5.6 Slave is allowed, it is not good to keep that scenario indefinitely. Why ? The way MySQL 5.6 reconnects to an older Master may be more difficult with older Masters.
To illustrate problems of this nature, consider how MySQL Replication can break between versions because of Binary Logs changing between versions of MySQL. I have discussed this before in my answer to the Jan 02, 2015 question mysql replication master 5.5 slave 5.1 error on create database.
In that answer, I mentioned how binary log events have been added with each new major release of MySQL. This may call for MySQL 5.6 to ignore or bypass events from the binary logs it does not understand. In some rare cases, the SQL will break while trying to unravel and binary log event that may have changed between versions. In that instance, the SQL thread for MySQL Replication WILL NEVER START BACK UP.
While I am talking about the SQL thread in my scenario, your situation involves the I/O thread. There may some issues (bug or untested feature) causing reconnects not work. In light of this, a Slave reconnecting to a Master of a previous version of MySQL is no longer a trivial matter.
There is a current bug report for MySQL 5.6.20 that has been triaged since August 2014 (Bug #73494 : Slave can't reconnect after 2003 error when sha256_password is used). This is not directly related to connecting to a older Master but the problem exists with MySQL 5.6 itself.
You should either consider one of the following

Increase max_allowed_packet to 1G on the Master and all Slaves
refrain from using MySQL 5.6 as a Slave to an older Master
upgrade to MySQL 5.6 across the board

UPDATE 2015-05-21 18:06 EDT
If Master and Slave have max_allowed_packet set at 16M, then there is no need to change it.
If you can live without changing skip-networking, try the following

Step 01 : STOP SLAVE IO_THREAD;
Step 02 : Run mysqldump --single-transaction ...
Step 03 : START SLAVE IO_THREAD;

This way, the I/O Thread stays connected 24 / 7 and you never have to restart mysql.
GIVE IT A TRY !!!


How to optimize table_cache? I searched Google but couldn't find an exact answer. Some have this value at 10000 or even 20000. Others say that even 1000 is too much for 1gb of RAM. I'm Confused.

My number of opened_tables is growing. Mysqltuner says I should raise the table_cache value. I tried to raise it to 2K, then 3K, then 4K, then I made it 512 to see what happens. Here is the report:

Currently running supported MySQL version 5.0.67-community-nt-log
Operating on 32-bit architecture with less than 2GB RAM
Archive Engine Installed
Berkeley DB Engine Not Installed
Federated Engine Installed
InnoDB Engine Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Data in InnoDB tables: 12M (Tables: 3)
Data in MEMORY tables: 380K (Tables: 2)
Data in MyISAM tables: 83M (Tables: 84)
Total fragmented tables: 16
All database users have passwords assigned
Up for: 16h 12m 55s (161K q [2.000 qps], 6K conn, TX: 281M, RX: 22M)
Reads / Writes: 69% / 31%
Total buffers: 128.0M global + 2.1M per thread (100 max threads)
Maximum possible memory usage: 334.3M (16% of installed RAM)
Slow queries: 1% (11/161K)
Highest usage of available connections: 10% (10/100)
Key buffer size / total MyISAM indexes: 32.0M/4.1M
Key buffer hit rate: 97% (849K cached / 20K reads)
Query cache efficiency: 28% (26K cached / 93K selects)
Query cache prunes per day: 0
Sorts requiring temporary tables: 1% (1 temp sorts / 21K sorts)
Temporary tables created on disk: 1% (2 on disk / 15K total)
Thread cache hit rate: 99% (10 created / 6K connections)
Table cache hit rate: 5% (139 open / 2K opened)
Open file limit used: 20% (232/1K)
Table locks acquired immediately: 99% (116K immediate / 116K locks)
InnoDB data size / buffer pool: 12.5M/32.0M
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increase table_cache gradually to avoid file descriptor limits
table_cache (> 512)
Scan Complete

mysql> show global STATUS LIKE 'open%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 222   |
| Open_streams  | 0     |
| Open_tables   | 127   |
| Opened_tables | 2335  |
+---------------+-------+
4 rows in set (0.00 sec)

However, opened_tables is still growing. Should I raise it again? Or is it not worth attention?

In addition, some people say that the problem is usually in creating a lot of temp tables. As we can see from the stats, it's not my case. What's the ratio between table_cache and RAM?

The reason you didn't find an answer is that there's not an answer.  The appropriate value for table_cache doesn't correlate to an amount of system memory.
Note that table_cache was renamed table_open_cache in MySQL 5.1.3 and is referred to by the new name in newer versions of MySQL.
As I mentioned in my answer to this similar recent question, among the things that can increment opened_tables, some of them are both harmless and inevitable (such as making backups with mysqldump, which, in most cases, issues some variant of FLUSH TABLES, which increments the counters as each table is reopened after the flush, either for the purpose of backing up the table or by other client threads accessing the recently-flushed tables, which must naturally be re-opened).
What I didn't think to mention in that post is -- and I trust you will find some measure of amusement in this -- that the act of running mysqltuner against your server can, itself, increment the opened_tables counter, thus invalidating its own results.
A commenter on my answer referenced this article, where the author points out that tweaking table_cache worsens performance the more "optimally" you set it.
The bottom line is that this is a good example of a variable that is best left alone unless you have a specific reason to tweak it, and the output of a tuning script doesn't count.  Tuning scripts are often well-intentioned but that is sometimes the best thing that can be said about them:

The problem with correlations that sometimes appear to be true is that people begin to believe they will always be true. Oracle DBAs abandoned ratio-based tuning years ago, and we wish MySQL DBAs would follow their lead.
We wish even more fervently that people wouldn’t write “tuning scripts” that codify these dangerous practices and teach them to thousands of people. This leads to our second suggestion of what not to do: don’t use tuning scripts! There are several very popular ones that you can find on the Internet. It’s probably best to ignore them.
We also suggest that you avoid the word “tuning,” which we’ve used liberally in the past few paragraphs. We favor “configuration” or “optimization” instead (as long as that’s what you’re actually doing; see Chapter 3). The word “tuning” conjures up images of an undisciplined novice who tweaks the server and sees what happens. We suggested in the previous section that this practice is best left to those who are researching server internals.
“Tuning” your server can be a stunning waste of time.
-- Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Locations 12173-12182). OReilly Media - A. Kindle Edition.

As a side note, MySQL 5.0 is end of life.  MySQL 5.0.67 was released over 4 years ago and a significant number of bugs were fixed in the 5.0 release series since then.  If I had a 5.0.67 server, and it was working, then the last thing I would want to do would be to change anything about its configuration.


Primary Key efficiency If I need a table to hold Point of Sales transactions, and am told I need to store:

Country ID
Store Number
POS Terminal Number
Transaction Date
Item Code
Teller ID
Another Field
More Fields

Now, in this case, the uniqueness would be:

Country ID, Store Number, POS Terminal Number, Transaction Date, Item Code

I am always unsure if it's best to have a identity colmn as the primary key - in this case, maybe TransactionID INT NOT NULL PRIMARY KEY, and then a unique constraint across the unique fields.

Or, should a primary key be created across all the unique fields?

The benefit of the TransactionId I guess would be, joins. To join back to a transaction, you just use the single field. I can't see the benefit of the PK across a number of fields. (Save space of an extra column?).

I would say yes, create the surrogate key of TransactionID. It would create a narrow, unique identifier across the table.

One of the reasons why a surrogate key would be best is because of relationships with other tables. If you need to relate the Transaction table with another table (Line_Item_Detail for instance) the entire primary key of the Transaction table would need to be in the related table as a foreign key. If you decided to use your candidate key of Country ID, Store Number, POS Terminal Number, Transaction Date, and Item Code these columns would need to be in every related table. If you would need to update any of these fields they would need to be updated in the related tables too. This gets messy very quickly. With a surrogate key of TransactionID you would only need to add the TransactionID column to your related table. Since this will, should be, and IDENTITY column we should never have to update it.

Another thing you should be thinking about when creating SQL Server tables is the clustered index. A clustered index is how SQL Server physically stores the data for a table. You can have a table without a clustered index called a heap. In most cases you really should create a clustered index (read more about heaps vs. clustered indexes) Michelle Ufford has an excellent post on creating Effective Clustered Indexes. In short, your clustered indexes should be:

Narrow – as narrow as possible, in terms of the number of bytes it
stores
Unique – to avoid the need for SQL Server to add a
"uniqueifier" to duplicate key values
Static – ideally, never updated
Ever-increasing – to avoid fragmentation and improve write
performance

A clustered index on TransactionID would fill this criteria nicely.

Since you'll be adding a surrogate key to the Transaction table you should also consider adding an alternate key of Country ID, Store Number, POS Terminal Number, Transaction Date, and Item Code or some other candidate key. This will guarantee there will be no accidental duplicates added to the table. If you don't add the alternate key there is a strong probability that duplicates will creep into the table.

One last thing, you may want to consider normalizing the table further. With the table design your provided if more than one item is added to a transaction you will be adding duplicate data to every row (Store Number, POS Terminal Number, etc). Querying will also become more difficult due to the duplication of data.


How to clean up tables that don't belong into mysql/mysql database? It seems that a *.sql file I recently imported had use mysql instead of use dbname. Now the mysql database contains a large number of tables which don't belong there.

Is there an easy/efficient way to clean this up?

Note: I am using MariaDB, but I think a generic answer would be more useful for others.

mysql  Ver 15.1 Distrib 10.1.10-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

No clean way...

USE MyDatabase;
RENAME TABLE mysql.mytable1 TO mytable1;
RENAME TABLE mysql.mytable2 TO mytable2;

etc.

You said "large number"... You could get the list thus:

SELECT CONCAT('RENAME TABLE mysql.', TABLE_NAME, ' TO ', TABLENAME, ';')
    FROM information_schema.TABLES
    WHERE schema_name = 'mysql';

But be very careful not to rename the system tables!


How to find the nth highest salary in SQL? I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)?

EmpID         Salary
1             90000
2             80000
3             54000
4             37000
5             12000
6             69000
7             50000

SELECT * FROM Employee E1
WHERE (N-1) = (
                SELECT COUNT(DISTINCT(E2.Salary))
                FROM Employee E2
                WHERE E2.Salary > E1.Salary
              )

If N= 4, then how does the query work? I'm a complete beginner in SQL, please help!

What's happening here is the subquery is looking at each individual salary and essentially ranking them, then comparing those salaries to the outer salary (a separate query against the same table). So in this case if you said N = 4 it is saying:

WHERE 3 = (number of salaries > outer salary)

So looking at the data you have, let's rank them in order, and compare.

EmpID   Salary   How many *distinct* salaries are greater than this one?
-----   ------   -------------------------------------------------------
5       12000    6
4       37000    5
7       50000    4
3       54000    3
6       69000    2
2       80000    1
1       90000    0

So when n = 4, the row that will be returned is EmpID 3 (54000).

A much more intuitive way to write this query, in my opinion, is to use windowing functions like RANK(), ROW_NUMBER() or DENSE_RANK() (depending on whether or not you want ties). Let's take a look at how these different functions work against your data (and I've added an 8th row to represent a tie for 4th place):

DECLARE @salary TABLE(EmpID INT, Salary INT);

INSERT @salary VALUES
(1,90000),(2,80000),(3,54000),(4,37000),
(5,12000),(6,69000),(7,50000),(8,54000);

;WITH x AS
(
  SELECT EmpID, Salary,
    r  = RANK()       OVER (ORDER BY Salary),
    dr = DENSE_RANK() OVER (ORDER BY Salary),
    rn = ROW_NUMBER() OVER (ORDER BY Salary)
  FROM @salary
)
SELECT EmpID, Salary, r, dr, rn FROM x;

Results:

EmpID  Salary   r   dr  rn
-----  ------   --  --  --
5      12000    1   1   1
4      37000    2   2   2
7      50000    3   3   3
8      54000    4   4   4
3      54000    4   4   5
6      69000    6   5   6
2      80000    7   6   7
1      90000    8   7   8

I don't think you'd want to use RANK() for this specific problem, because of the way it works there is no 5th place, for example. So now it comes down to whether you want to include multiple rows in the case of a tie, and if not, if you want an arbitrary row or a specific row based on some criteria. So adjusting the statement slightly:

-- if you want ties:
;WITH x AS
(
  SELECT EmpID, Salary,
    dr = DENSE_RANK() OVER (ORDER BY Salary)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE dr = 4;

-- results:
-- 3   54000
-- 8   54000

-- to take the *lowest* EmpID:
;WITH x AS
(
  SELECT EmpID, Salary,
    rn = ROW_NUMBER() OVER (ORDER BY Salary, EmpID)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE rn = 4;

-- results:
-- 3   54000

-- to take the *highest* EmpID:
;WITH x AS
(
  SELECT EmpID, Salary,
    rn = ROW_NUMBER() OVER (ORDER BY Salary, EmpID DESC)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE rn = 4;

-- results:
-- 8   54000


Tool for converting all VARCHAR to NVARCHAR in one or few steps I am using SQL Server Management Studio and need to convert all VARCHAR columns to NVARCHAR. Is there a tool or something similar to do the conversion in one or few steps? It should be applied to different databases.

Addition:

Excuse me, but is it possible that you gave me a script to convert NVARCHAR to VARCHAR? I need it the other way round. Also when I run the script which was produced by your script it gave me the error

Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von 'GO'.

multiple times. Actually I thought that the solution your script produce, to simply alter the tables doesnt work when there are constraints related to tables. Thats why I asked whether there is a tool that can do this, because I thought a simple script has restrictions.

no tool needed just some good old TSQL code.

This will generate all the statements.

DECLARE @Statements table (Statement varchar(max))
DECLARE @CMD varchar(max)
DECLARE @DB varchar(500)
DECLARE c CURSOR for select distinct name from sys.databases where database_id > 4
OPEN C
FETCH NEXT FROM C into @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = ' USE '+quotename(@DB)+';

select ''USE '+quotename(@DB)+' GO
ALTER TABLE ''+QUOTENAME(s.name)+''.''+QUOTENAME(t.name)+''
ALTER COLUMN ''+quotename(c.name)+'' varchar(''+cast(c.max_length as varchar)+'')
GO'' from '+QUOTENAME(@DB)+'.sys.tables t
inner join '+QUOTENAME(@DB)+'.sys.schemas s on s.schema_id = t.schema_id
inner join '+QUOTENAME(@DB)+'.sys.columns c on c.object_id = t.object_id
where is_ms_shipped = 0
and system_type_id = 231
'

INSERT INTO @Statements
EXEC (@CMD)

FETCH NEXT FROM C into @DB

END
CLOSE C
DEALLOCaTE C

SELECT * from @Statements


Sql Server Standard 2014: Reindex job locked by other transactions On a customer of ours we have Microsoft SQL Server 2014 - 12.0.4100.1 (X64) installed. It's a heavy transactions schema (especially during nights) and we have a scheduled REINDEX maintenance job that runs every early morning at 4 am.

We have noted that tonight the reindex job got stuck for like 45minutes on a alter index on a particular table because it was locked by another transaction (an UPDATE statement issued by a user, a very common transaction for our system).

The guy responsible for the night check promprly killed the transactions (reindex included) since we were already receiving complaints from the users; when he killed it the current ALTER INDEX statement was blocked by 45 minutes, but the entire REINDEX job took at that moment almost 4 hours (he killed it at 7:51 am).

Is there a way to avoid the REINDEX job to get blocked by other transactions?

EDIT: the fragmentation levels we noted (< 30%) showed that we could probably go for a REORGANIZE instead of a REINDEX thus eliminating the chance of other transactions being locked by the reorganize, but the reorganize itself could still be blocked.

and we have a scheduled REINDEX maintenance job that runs every early morning at 4 am.

Wait..is that really necessary to run the reindex job every day ?. Do you even look at whether the indexes are fragmented ?. Since you said its a critical system I will take liberty of assuming the underlying storage is fast that further lowers the possibility of re-indexing everyday. Please think about it.

  Is there a way to avoid the REINDEX job to get blocked by other transactions?

If you absolutely want this, then run index maintenance during downtime or when load is relatively very very less and no DML operations are going on. You can use online index rebuild ( If you have enterprise edition) but that also takes lock and may be blocked. Plus frequently rebuilding index out dates stats more often leading to more compilations

Suggestion: Use Ola Hallengren Index rebuild solution.


Remove string after first and second specific character in a string I have some text in a field like below;

Confirmed Diagnosis
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes:

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified

I would like to extract any text after Confirmed Diagnosis ================ into a new field, and any text after Suspected Diagnosis ================ into another field.

Thank you all.

For a SQL solution, try something like this. It sounded like the section of text you wanted.

-- the same logic will work against a column in a query too.
if object_id('tempdb..#textThing','U') is not null
    begin
        drop table #textThing;
    end;
create table #textThing (
     id int identity(1,1) not null primary key
    ,text_stuff text
    ,varchar_stuff varchar(max)
);
insert into #textThing (text_stuff, varchar_stuff)
select 'Confirmed Diagnosis
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes: brain fell out, recommend duct tape

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified || suspect brain loose'
,'Confirmed Diagnosis
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes: jose fell of my left eye onto my right pinky toe.

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified || there''s something missing from jose''s eye '

;with initialText as ( -- to clean, reducing line ends to only single char(13) (carriage return) stripping off char(10) (line feed), this only applies to the info in memory and will not affect the column in the table.
select
     replace(replace(replace(convert(varchar(max),text_stuff),char(10),''),char(13),''),'Confirmed Diagnosis ================','') cleanstext
    ,replace(replace(replace(varchar_stuff,char(10),''),char(13),''),'Confirmed Diagnosis ================','') cleanvarchar
    ,reverse(replace(replace(convert(varchar(max),text_stuff),char(10),''),char(13),''))  cleanstext1
    ,reverse(replace(replace(varchar_stuff,char(10),''),char(13),'')) cleanvarchar1
from #textThing
)
select
     left(cleanstext,charindex('Suspected Diagnosis================',cleanstext) -1) textConfirmedDiagnosis
    ,left(cleanvarchar,charindex('Suspected Diagnosis================',cleanvarchar) -1) varcharConfirmedDiagnosis
    ,right(cleanstext,charindex(reverse('Suspected Diagnosis================'),cleanstext1) -1) textSuspectedDiagnosis
    ,right(cleanvarchar,charindex(reverse('Suspected Diagnosis================'),cleanvarchar1) -1) varcharSuspectedDiagnosis
from initialText

Results were

[textConfirmedDiagnosis]
Stroke || Entered: 19-02-2019 01:36:39 || Notes: brain fell out, recommend duct tape

and

[varcharConfirmedDiagnosis]
Stroke || Entered: 19-02-2019 01:36:39 || Notes: jose fell of my left eye onto my right pinky toe.

If your results are different then you got some extra characters in your string that I do not have in mine.

I may have written it, but I have no idea how it works, logic dictates I'd need to reverse() it one more time to get the right value, but hey, who am I to argue with a working script, tested on 2014, 2017 and 2017 on centos


SQL Server Backup failing for just 1 DB ("The system cannot find the path specified.") For the last few mornings, I have had a backup of our ReportServerTempDB fail. We are not using SSRS on this server yet, but will in the next month, so the DB is the default 8mb .mdf and 8mb .ldf (i.e. tiny).

We are using Ola Hallengren's scripts to run backups to a network location, and all the backups run perfectly until they get to the ReportServerTempDB. We mount the network location at runtime, as it is not on our domain and requires different credentials to be accessed. This is not ideal and we are looking into changing it, but for now, I need to figure this out please.

My script is as follows:

EXEC xp_cmdshell 'net use Y: \\10.1.1.200\data\SQLSANBackups /user:BackupUser Password'
GO

EXECUTE dbo.DatabaseBackup
@Databases = 'ALL_DATABASES',
@Directory = 'Y:\\',
@BackupType = 'FULL',
@ChangeBackupType = 'N',
@CleanupTime = 168,
@FileExtensionFull = 'bak',
@Compress = 'Y',
@CleanupMode = 'AFTER_BACKUP'
GO

EXEC xp_cmdshell 'net use Y: /delete'
GO

It fails with the following errors:

  BackupDiskFile::CreateMedia: Backup device 'Y:\\Servername \ReportServerTempDB\FULL\ReportServerTempDB_FULL_20190118_033124.bak' failed to create. Operating system error 3 (The system cannot find the path specified.).

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

As mentioned, all DBs before the ReportServerTempDB complete successfully.
If I manually run the exact script, just specifying the ReportServerTempDB, it completes perfectly. Things were working fine earlier in the week, and there have been no network or hardware changes since.

There are 3 production databases after the backup of ReportServerTempDB. They are not getting backed up because of the failure. I don't think adding a delay will help here but will try regardless.

I am aware that ReportServerTempDB would be easy to recreate, and I will remove it if i have to, but for a tiny database, and to keep things consistent, it would be easier if I don't have to.

Ok i finally figured out the issue. It was completely random that it happened on the ReportServerTempDB, and i think that's what threw me off.
My daily maintenance runs at 3AM and includes the backups as above. I also have 30min log backups happening 24/7, also with the specified 'Y' drive and deleting it.

The Log backup was running during the normal backup, and deleting the 'Y' drive from the full backup, therefore killing the full backup as the location now no longer exists.
I have added some logic in, so that the Log backups do not remove the drive between 3AM and 6AM and that's solved the issue.
Thank you all for the assistance though.


Manually moving SQL instance to another node causes immediate database mirror failover I have a two-node SQL cluster (2008 R2).

Some of the databases within that SQL instance are mirrored to another server on a remote site, using the High safety with automatic failover.  The mirroring connection timeout value for those databases is set to 90 seconds.

When I move SQL from one node in the cluster to another node, using the Failover Cluster Manager application's option of "Move this service or application to another node" the databases that are mirrored are instantly failing over to the mirror.

This is undesirable behaviour.  My reason for setting the mirroring connection timeout value is that I only want to fail over to the database mirror if the cluster fails completely and there are no functioning nodes.

Is there any way to achieve this?  It feels as though it should be possible, otherwise the concept of mixing clustering and automatic failover database mirroring would be unworkable as every node failover within the cluster would trigger a mirror failover.

Thanks.

One way to prevent the mirror failover is:

Pause Mirroring with ALTER DATABASE XYZ SET PARTNER SUSPEND
Move the SQL instance
Resume mirroring with ALTER DATABASE XYZ SET PARTNER RESUME

The instance is failing over to the mirror because both the witness and the secondary can no longer see the primary instance.

It sounds like you are attempting to recreate SQL Server 2012 Availability Groups by combining mirroring and clustering.

Database mirroring only times-out on so-called "soft" errors.  Hard errors, like a cluster failing over are reported to the mirroring session immediately causing the immediate failover.  Read more at http://msdn.microsoft.com/en-us/library/ms190913.aspx

Possible causes of hard errors include (but are not limited to) the following conditions:

A broken connection or wire

A bad network card

A router change

Changes in the firewall

Endpoint reconfiguration

Loss of the drive where the transaction log resides

Operating system or process failure

Conditions that might cause mirroring time-outs include (but are not limited to) the following:

Network errors such as TCP link time-outs, dropped or corrupted packets,
    or packets that are in an incorrect order.

A hanging operating system, server, or database state.

A Windows server timing out.

Insufficient computing resources, such as a CPU or disk overload, the transaction
    log filling up, or the system is running out of memory or threads. In these
    cases, you must increase the time-out period, reduce the workload, or change
    the hardware to handle the workload.

For more information about mirroring and potential issues, you might want to see my question What can cause a mirroring session to timeout then failover? SQL Server 2005


Repeat row in the result based on the value of a column FIRST_COL VAL_COL
A          2
B          3
C         4

I need output  as below.

FIRST_COL VAL_COL
A          2
A          2
B          3
B          3
B          3
C          4
C          4
C          4
C          4

example: my original table contains a=2 but I need in output as a=2 two times same for of b=3 three times c=4 four times

If you are using Oracle 12c or higher, you can CROSS APPLY with a CONNECT BY LEVEL query generating the numbers from 1 to VAL_COL. Something like this (I don't have an instance ready and SQLFiddle uses 11g, which doesn't support this):

SELECT FIRST_COL, VAL_COL
FROM table1
CROSS APPLY (
  SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= table1.VAL_COL
);

If you are on 11g or higher, you can use Recursive Refactored Subquery.

with data as (
  -- example data
  select 'A' FIRST_COL, 2 VAL_COL from dual union all
  select 'B' FIRST_COL, 3 VAL_COL from dual union all
  select 'C' FIRST_COL, 4 VAL_COL from dual
), counter(first_col,val_col, iterator) as (
  select first_col, val_col, val_col
  from data
  union all
  select first_col, val_col, iterator - 1
  from counter
  where iterator - 1 > 0
)
select first_col, val_col
from counter
order by first_col;

Prior to 11g, you would need to use a pipeline function


Query Store Force Plan feature doesn't work Query Store Force plan feature doesn't appear to be enforcing the plan.

I'm aware of Query Store - Forced Doesn’t Always Mean Forced; however, my plan may not change insignificantly, but query optimizer may continue to choose incorrect indexes, loop choices etc.

Basically: it doesn't honor my forced plan choice. I've forced many plans and It Just Doesn't Work.

There are 0 failure counts or reasons when I look at sys.query_store_plan force_failure_count.
Extended event query_store_plan_forcing_failed doesn't yield anything. 0 Events.

For example, a plan which was forced on 20.09. Only 1 compilation happened to use forced plan.

The plans differ wildly, one using Hash Match join with INDEX 1, the other using Loop Join with INDEX 2.

Version: Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) - 13.0.4202.2 (X64)

What am I missing here?

From sys.query_store_plan (Transact-SQL) (emphasis added)

  Plan forcing limitations

  Query Store has a mechanism to enforce Query Optimizer to use certain execution plan. However, there are some limitations that can prevent a plan to be enforced.

  First, if the plan contains following constructions:

  Insert bulk statement.
  Reference to an external table
  Distributed query or full-text operations
  Use of Global queries
  Cursors
  Invalid star join specification

You are using a cursor.

You can often force a cursor plan with a plan guide instead. See Using the USE PLAN Query Hint on Queries with Cursors.

And yes, I agree that the current situation does not make for the best user experience. I would expect that attempting to force a plan for something that cannot (currently) be forced would raise an error or warning, or log something. You could log a Feedback item to request an improvement in this area.

As of SQL 2019 CTP 2.3 forcing query execution plan for Fast Forward and Static cursors are supported

  Query Store now supports the ability to force query execution plans for fast forward and static T-SQL and API cursors. Forcing is now supported via sp_query_store_force_plan or through SQL Server Management Studio Query Store reports.


Add leading zeros to integers less than 7 digits and ignore anything 7+ I have a query and it almost doing what I want. It is adding leading zeros but when it comes to things 7+ long it turns everything into a 7 number digit.

Select RIGHT(rtrim('0000000' + cast(column as nvarchar)), 7) from table

I just think my brain is not working and I cannot figure this out. Can anyone help me with this?

Examples:

324 -> 0000324
2232 -> 0002232
3324124 -> 3324124

All I'm trying do is add 0s to anything that is less than 7.

The column is nvarchar.

Ugly (and won't perform all that great), but

SELECT
CASE WHEN len(Column) > 7 THEN CAST(Column AS nvarchar(20))
ELSE RIGHT(rtrim('0000000' + CAST(column as nvarchar(20)), 7) END
FROM table


Index Maintenance with Ola starting from day x (7 or 9) till the end of month I want to run the index maintenance from Ola daily, but in my company the firsts business days every month I can't, cause heavy application usage during the night.

So I want to run Ola's script only from the 7 till the end of the month.

How can I to this?

In my script I handle it with a simple if:

if
datepart(dd,getdate()) < 7 return
else begin

But how can I do this with Ola's script?

Best regards
Guenter

I wouldn't recommend making changes directly to Ola's script for your situation.  Any customization you make to his scripts will be lost when you download a newer version sometime in the future.

Instead, create a SQL Server Agent job to run daily and put your conditional execution logic in the job step

if datepart(day,getdate()) > 7
    EXECUTE dbo.IndexOptimize


How to detect the "Server admin" user on Azure SQL Server The following article describes the AZURE "Server admin" user.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

This user has access to every database but unfortunately the HAS_DBACCESS(%database_name%) function fails to return 1 so I need some other way to detect this user and recognize that they have access to all databases.

I tried a number of the other methods including:

IS_MEMBER ('db_owner')
IS_SRVROLEMEMBER ('serveradmin')
IS_SRVROLEMEMBER ('sysadmin')

Note: Oddly enough the HAS_DBACCESS(%database_name%) actually returns NULL.

Any suggestions would be appreciate.

The below queries were run from the Master database while logged in as rootadmin, which is the name of my Server Admin user.

Edit: When I switched to using the "Demo" database the query results changed and HAS_DBACCESS(t.name) returned NULL for the Master database and "1" for the "Demo" database.

So now I am confused.

With Microsoft SQL Azure Databases you're user won't be a member in any server role and you won't have access to that information. So, IS_SRVROLEMEMBER will, in fact, return zeros. You only have access at the database level and not the server level--unless you build and use a SQL VM instead.

From the Microsoft documentation you can use the following to test this:

IF IS_SRVROLEMEMBER ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
print 'ERROR: The server role specified is not valid.';

However, your user will have access to the database level roles. From Microsoft documentation you can test this example:

IF IS_ROLEMEMBER ('db_datareader') = 1
print 'Current user is a member of the db_datareader role'
ELSE IF IS_ROLEMEMBER ('db_datareader') = 0
print 'Current user is NOT a member of the db_datareader role'
ELSE IF IS_ROLEMEMBER ('db_datareader') IS NULL
print 'ERROR: The database role specified is not valid.';

There is a caveat though, you need to be in the context of your personal database and not the master database (my database is called TESTING):

Once you're in the correct database context, the following functions, and the like, work as expected:

select HAS_DBACCESS('testing')

select IS_MEMBER('db_owner')

Note:

select HAS_DBACCESS('db_name') will return a NULL value because there's no database called 'db_name'. You can change this to the db_name() function. For example,

select HAS_DBACCESS(db_name())

or, hard code the database name--TESTING in my case.

select HAS_DBACCESS('TESTING')


SQL Error [42501]: ERROR: permission denied for table I am very new to postgres so please my apologies in advance if I sound naive. I am still trying to learn. I am trying to create a readonly role and then create a role and assign readonly role to the user. I logged in as postgres user
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE test_db TO readonly;

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

CREATE USER readonlyuser WITH PASSWORD 'read123';
grant readonly  to readonlyuser;

Now I can login as user readonlyuser but I can't read data from any tables. I get error SQL Error [42501]: ERROR: permission denied for table.
Any help would be appreciated.

The ALTER DEFAULT PRIVILEGES statement you ran will only affect tables created by postgres. If a different user creator creates the tables, you need
ALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public GRANT SELECT ON TABLES TO readonly;


Trigger working on more than 50 million records I have a table with more than 50,000,000 records. The table doesn't have any partition. The table is in PROD.

I need to create a trigger there which will update previous entries flag to false for each upcoming latest entry. Each upcoming latest entry will have true value on flag.

The table has around 1300 inserts per minute. One of the column of that table contain json data as well.

Here is the trigger script:

CREATE TRIGGER trig_updaterow
before insert on tab
    for each row execute procedure updaterow();

CREATE OR REPLACE FUNCTION updaterow()
RETURNS trigger AS
$$
BEGIN

    update tab
    set    flag ='0'
    where  s = new.s
           and p = new.p
           and c = new.c
           and m = new.m
           and e = new.e
           and flag = '1';

    RETURN NEW;

END;
$$
LANGUAGE plpgsql;

I need to check in case of large number of inserts if the table will going to face any lock because of this trigger. I have tested it will bulk inserts on prod copy. However, still I'm not sure about this.
Also, I have observed a strange thing, when I'm inserting a sample 50,000 records via a pgsql loop, it's placing a lock on any other insert(blocking). However, when I'm inserting same number of records manually(manually writing 50,000 inserting statement and run that file in 1 batch)-it's not blocking any new insert. Why is that difference?

The row being updated by the trigger will be locked until the end of the transaction.  If the same combination of s,c,p,m,e is inserted by two concurrent transactions, one will block until the other commits or rolls back.  If concurrent transactions never touch the same rows, they will not block each other.

  via a pgsql loop

What is that?  Are you talking about PL/pgSQL?  All in one transaction?  If two concurrent transactions are inserting 50,000 rows each, it is almost certain that there is at least one overlap between them.

  run that file in 1 batch

Again, what is that?  psql -f?  Is it just a list of insert, or is it wrapped in BEGIN, COMMIT statements?


How can I import my server list from pgAdmin 3 to pgAdmin 4? I wonder whether there is any way to import my server list from pgAdmin 3 to pgAdmin 4 (so that I don't have to create servers one by one again).

For Windows in C:\Users\%user%\AppData\Roaming\pgAdmin you can find pgadmin4.db which is where pgAdmin4 saves its configuration. It is SQLite format 3 and with SQLite browser you can open it and insert servers into server table and then save and they are added to pgAdmin4. Note that passwords are saved encrypted so leave NULL to input them later.
Now you could make sql script that reads registry export from pgAdmin III and inserts into table for fully automated process.


MySQL 'LOAD DATA INFILE' : Can't replace entry when foreign key exists Here's a minimal case for my database scheme:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Well , And what we have inside:

    mysql> select * from test;
+-----+-------+
| id  | name  |
+-----+-------+
| 111 | AAABA |
+-----+-------+
1 row in set (0.00 sec)

mysql> select * from test2;
+-----+
| id  |
+-----+
| 111 |
+-----+
1 row in set (0.00 sec)

Now i need to read from an external csv file , with duplicate primary key , into the first table:

mysql> LOAD data infile '/tmp/tar.csv' REPLACE INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`eduman`.`test2`, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`))

And the contents for /tmp/tar.csv:

%> cat /tmp/tar.csv
111,AAA

As i know from MySQL , it probably does a 'delete' then 'insert' , which caused the problem , it can't be deleted as the foreign key exists.
So how can i force an update to be executed , instead of a 'delete then insert' ?

Appreciate any of your responses.

From internet , i found some workable way , which suppress the checks temporarily , but is there any better response , it looks like an dirty hack , and i'm not sure if this could be a problem in multi-user environment , e.g another table with foreign key constraints may be damaged.

SET FOREIGN_KEY_CHECKS = 0;
Blabla ...
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

The above link clearly explains that mysqldump too puts set foreign_key_checks = 0; in the dump file so that data can be restored without the foreign key constraint error. I have always used set foreign_key_checks = 0 for importing data into tables and then set it back to 1.


sp_depends output in a table I need to run sp_depends on multiple stored procedures on Sybase to get dependencies. Since I have a lot of stored procedures, I wanted to know if there is any way to force sp_depends output into a temp table and then select from there all dependent objects. If this is possible, I can just run sp_depends from a stored procedure for multiple stored procedures and get the result set in one go.

I tried below without any luck (using Toad):

Insert #test_tbl
exec sp_depends sp_proc

Above gives error as:

  "Incorrect syntax near the keyword 'exec'"

Please advise if there is any other efficient way to do this. My goal is to get all dependencies for analysis.

Update:
I do have a way to generate mass 'sp_depends' in excel. Looking for something to run them in one go and get the whole output of supplied objects.
Also, since this is Sybase utility, unfortunately I don't have a way to edit it

A script like this could help you:

isql -Usa -S${ASE_Servername} <<EOF
$password

set nocount on
go
select 'sp_depends '+name from <dbname>..sysobjects
where type='P' and name in ( <list_of_SPs> )
go

EOF


RECOVERY MODEL changed on a database, no record in Default Trace I have a Log backup job running every hour via SQL Agent. This morning it failed at 3:00 AM with this error:
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.
But it ran fine at 2:00 AM. So sometime between 2 and 3, it went from FULL to SIMPLE.
My response was:

Change it back to FULL recovery
Run a FULL DATABASE BACKUP
Run the Log Backup job to make sure it would still work.
All good, no data loss.

So I got to investigating the Default Trace for events about possibly WHO might have made the change between 2 and 3. I first identified the event on Step #1 above so I knew what types of events I was looking for. Unfortunately, there is no event to say, "The recovery model changed from x to y". All it has is "Object: Altered" for my login for the database in question, with rows for BEGIN and COMMIT.
Here's the issue: I found NOTHING in the default trace in the time span to indicate that anyone made a change to this database.
Question: How could this change have occurred and it NOT get recorded in the Default Trace? It certainly recorded the fact that I changed it back. Why not the first change?
Any help would be appreciated.

Answering my own post as I found additional information:
The trace log had no indication of Object: Altered for that database, which happened to be msdb (might be important here). In the SQL logs, I found indications that someone had applied updates, and after the first instance start-up, there was indication that msdb had an upgrade script applied then setting of SIMPLE recovery, along with creation of several synonyms.


How to enforce SQL Server policies in order to avoid users enabling bad practices I have some rogue Windows Administrators who give themselves SA and then grant end-users SA.
These users then end up enabling auto-shrink, auto-growth percentage and all kinds of other poor practices - no matter how many times I tell them not to do it, they are still doing it.
Is there a way for me to use SQL Policies to forcefully stop these settings from being enabled? Currently I can only evaluate them and get a notification when the policies are failing.

I assume you are referring to Policies in SQL Server here. Some types/level of policies, you cannot prevent. Technically, to "prevent" something using a policy in SQL Server means that the operation is performed, but the policy engine then does a rollback. Not all operations are rollback-able, hence the prevent functionality doesn't exist for all policy types.
So, in the end, you need to work out who has sysadmin privileges and make sure that only trusted persons has. In the end, this might have to go higher in the organization to get support.


Odd behavior of between operator when updating I have to maintain FRP (Financial Resource Planning) Software that was implemented by some shady consultants (they came up with the jobs), and it stores the data in a SQL Server database. The software loads some info in the morning from a text file (from an ERP) and then 3 hours after it runs 4 jobs. If I run all these processes by hand, I get the same result at the first job. This is why I have isolated this one job.

I have checked for triggers and there are none. I have checked for procedures and all belong to stuff users can do in the software. Even with every user logged out (I took down the service, so no users would log in) it still happens.

Job number 1

UPDATE SOME_TABLE SET VALUE_DATE = DATEADD(dd, DATEDIFF(dd,0,GETDATE()),0)
WHERE VALUE_DATE < DATEADD(d,0,GETDATE()) AND FLAG BETWEEN '0' AND '1'

This baffles me because Flag can only take 3 values 0,1,2 and are numeric values.

DataSet prior to job

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-05-22  |    0    |
                |  AAA  |   19999  |   2018-05-21  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-05-15  |    0    |
                |  CCC  |   15000  |   2018-04-15  |    1    |
                +-------+----------+---------------+---------+

DataSet after the is job run (today's date is 2018-06-14)

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-06-14  |    1    |
                |  AAA  |   19999  |   2018-06-14  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-06-14  |    1    |
                |  CCC  |   15000  |   2018-06-14  |    1    |
                +-------+----------+---------------+---------+

Second job executed

UPDATE SOME_TABLE SET FLAG='0'
WHERE OTHER_FLAG IN ('COND1', 'COND2'...) AND FLAG='1'

Result of dataSet after second job - expected

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-06-14  |    0    |
                |  AAA  |   19999  |   2018-06-14  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-06-14  |    0    |
                |  CCC  |   15000  |   2018-06-14  |    1    |
                +-------+----------+---------------+---------+

This query doesn't only update the VALUE_DATE it also sets FLAG to 1 on every entry. Then the second job is in queue to set the FLAG value back to 0 where it updated. I find it convoluted and inefficient. I want to update it because it takes a great deal of time and people once a month to curate the information in the db.

Why is the FLAG column getting updated? Will changing FLAG BETWEEN '0' and '1' to FLAG in ('0','1') have any corrective effect? Or any adding "context" delimiter will help ("",'',(),[],{},etc)?

How can I make the first job only update the Value_date when flag is 0 or 1 and ditch the second job completely? This way I can get the expected table results

I'm sure it's not a software issue, because the date integrity holds just fine when doing stuff in the software. It only happens when this job is executed.

The SQL statement you've listed, run by itself, would not alter the FLAG column.

I've put together a SQLFiddle that shows what that statement does on its own, and you'll see that FLAG isn't changed by it.

This is why so many have assumed there's a trigger, or another statement, involved in the process.

You've said that there's no trigger on SOME_TABLE. If so, then some other statement is making the change.

The best way to find what that statement is would be to use SQL Profiler (or extended events) to track what's being executed when your job runs. If some other statement is being triggered by the relevant job, this should show you what that is. If you search this site, you'll find information on these options.

If you need to prove to yourself whether or not the UPDATE statement in and of itself is causing the change to FLAG, I'd recommend the following:

During a maintenance window:

Begin a transaction
Run the UPDATE statement inside that transaction
Check the results of the statement on SOME_TABLE (again from within the transaction)
Roll back the transaction.

If there's no transaction, and that SQL Statement (not the whole job, just that statement) does change the value of FLAG, then either this is tied to a bug in SQL Server (not impossible, but somewhat unlikely), or there's something seriously wrong with your SQL Server installation. Or, of course, there really is a trigger - I'm assuming not, based on what you've said, but that's a much more likely solution than a bug or a messed up SQL Server instance.


How do I get a list of all the partitioned tables and partitioning details in my Teradata? How do I get a list of all the partitioned tables in teradata?

and

How do I get a partitioning columns and details for a table  in teradata ?

dbc.PartitioningConstraintsV contains info about all partitioned tables.

SHOW TABLE mytable returns the CREATE TABLE statement for this particular table.


Tablespace consumed by views I was in an SQL class where the instructor said "DBAs don't like it when users create views, because they will clobber tablespace."

Obviously, I understand that any schema object will consume some tablespace, but isn't it a huge exaggeration to say that views will eat up tablespace, since all you need to store is just a query (and privileges and some other metadata), and as such they won't take significantly more space than an ordinary row in an ordinary table?

Not true, unless they meant "materialised views"

ORAFAQ

  Permanent tablespaces are used to store user data and user created objects like tables, indexes and materialized views.


Social file sharing database schema I have a project called social media file sharing. I have this database schema and I need your opinion about what I should remove or add or edit on my database schema.

Every user can upload media types like Text, Images, Audio and Video. I assume that every media type can be set into album (like photo album) each album has many images. With this I create a table called Holder and table Connector to connect media type and their holder.

First of all, some tables are seperated but really hold the same kind of data.

For example, video, audio and images are all saved as urls, so you can join them in one table instead of 3.

This will greatly simplify your schema, and allow you to permit several types of content in one collection (for example, a wedding album that contains images & video).

Second, if users upload the content, how come you can keep it in urls? Where is the data actually saved? My first thought when seeing this was that you need to keep a binary object column for the content itself.

Third, did you mean to have a many-to-many connection between content and albums? It means every item can be in several albums. If you would like to limit an item to only one album, you would not use a "connector" table, but instead keep an album id in the content table itself (a foreign-key to the album table).

And last - as a "social media file sharing" schema, it seems to be missing the social aspect (where is sharing information saved?).


Something like this should work (it's hard to verify without a fiddle):

WITH temp AS (
  SELECT code, sma_9, sma_26, rsi,
    RANK() OVER (PARTITION BY code ORDER BY date DESC) AS rank
  FROM Stock_price_Table
)
SELECT t1.code
  FROM temp AS t1
  INNER JOIN temp AS t2
    ON t1.code = t2.code
   AND t1.rank = 1
   AND t2.rank = 2
  WHERE t1.sma_9 >= t1.sma_26
    AND t2.sma_9 <= t2.sma_26
    AND t1.rsi >= t2.rsi

Basically, in the temp table you select the stock records, RANKed by date and 'grouped' by code (through the PARTITION BY clause). In the main SELECT, you link the first and second ranked records per stock code, and select only those which match your criteria (the WHERE clause).


Overview of leaked/unclosed cursors/statements on DB2 LUW server? We're encountering a "too many open cursors issue" on DB2/LUW.

Is there any way to get a (database-server-side) list of the corresponding SELECT statements related to these open cursors?

We're doing that on Oracle all the time.

I know we could review the application logic but that is an awful lot harder in our particular case. So I wanted to get this question out first. Thanks.

SELECT
  application_handle
, uow_id, activity_id, activity_type, activity_state
, timestampdiff(2, char(current timestamp - entry_time)) elapsed_sec
, stmt_text
FROM TABLE(MON_GET_ACTIVITY(null, -2))
order by elapsed_sec desc;

MON_GET_ACTIVITY table function - Return a list of activities


Trying to get password has for a login through PowerShell I've trying to get the password hash for a SQL authentication login via powershell.

The code I'm executing is something like this

Invoke-Sqlcmd -Query "SELECT LOGINPROPERTY('$LoginName', 'PASSWORDHASH') as PasswordHash"  -ServerInstance $SourceInstance

This works in that the query executes fine but the output I get is

PasswordHash

{2, 0, 87, 116...}

I'm guessing (probably incorrectly) that this is due to it returning a varbinary, has anyone experienced this and/or fixed it before?

On a side note I'm pretty sure that a script I have which configures tempdb gets similar output.  I'd like to understand not just how to fix it but why it happens too :)

Cheers
Mat

For SQL 2005+

Invoke-Sqlcmd -Query "SELECT sys.fn_varbintohexstr(CONVERT(VARBINARY(MAX), LOGINPROPERTY('$LoginName', 'PASSWORDHASH'))) as PasswordHash"  -ServerInstance $SourceInstance

You can't use this for everything as it has length limitations, but for this purpose it will be fine.


SSIS: script task on Visual studio 2013 can not open since Visual Studio 2015 was installed Until the installation of Visual studio 2015, it was always opening Visual studio 2012.

Attached the only error he throws, It seems he was trying to open Visual Studio 2015.

I have VS 2012, 2013, 2015 installed and faced the same issue.
After looking in the Event viewer it was apparent that it was 2015 VSTA causing the issue.

This solved it for me without re-installing everything....

Uninstalling:

Microsoft Visual Studio Tools For Applications 2015
Microsoft Visual Studio Tools For Applications 2015 Language Support

Repairing:

Microsoft Visual Studio Tools For Applications 2012


Are cursors/While loops the only way to make administrative changes to multiple databases? I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in PowerShell can solve this problem very easily:

Import-Module SQLPS
CD SQL\*ServerName*\*InstanceName*\databases
foreach ($database in (ls -force)){
$database.recoverymodel = 'Full'
$database.update
}

I'm looking for the most efficient T-SQL solution.
It has been ingrained in my head to avoid cursors at all costs, however I can't think of a set based solution to perform alter statements.
Here is the cursor based solution.

DECLARE @sql varchar(MAX), @name varchar(50)

DECLARE cur CURSOR FOR
SELECT name
FROM sys.databases
where name <> 'tempdb';
OPEN cur

FETCH NEXT FROM cur
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER DATABASE ' + @name + ' SET RECOVERY FULL;'
    exec(@sql)
    FETCH NEXT FROM cur INTO @name
END
CLOSE CUR
DEALLOCATE cur

This can also be done with a while loop, but that solution still goes through a looping process... do while loops offer significantly better performance?
Am i being paranoid about performance? I say that because administrative tasks such as this aren't done very often, and there generally isn't a massive amount of objects you need to loop through. However i always think "What if i worked in a large environment with thousands of objects" and then i begin to feel guilty that I may not be using the most efficient solution.

As far as loops go for this type of thing, don't worry about it.  Loops and cursors have a bad reputation because there are normally better set-based approaches that are often faster.  For admin stuff, sometimes loops are the only way, and no set-based ways of doing this spring to mind, although you can parallelise tasks with tools like start in DOS, SSIS, Powershell etc.

Having said that, I prefer to use SQLCMD mode for this type of thing.  It's a special mode you can switch on in SQL Server Management Studio (SSMS) via the main menu > Query > SQLCMD Mode ... like this:

Once you have switched this mode on, you have access to all kinds of commands like :connect to connect to another server, :r to read a file, :out to redirect output, SQLCMD variables, and any DOS commands by prefixing them with two exclamation marks, eg !!dir.

For your example, you could do something like this:

:connect .\sql2014
SET NOCOUNT ON
GO

-- Redirect output back to normal
:out d:\temp\temp.sql
GO

SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL;'
FROM sys.databases
WHERE recovery_model_desc != 'FULL'
  AND database_id > 4
  AND name Not In ( 'distribution', 'SSISDB' )
GO

PRINT 'GO'
GO
-- Redirect output back to normal
:out STDOUT

-- Optionally read/run the temp file you have scripted
--:r d:\temp\temp.sql
GO

This allows you to "script a script", then you can review it, change if required and there is an audit trail of what has been run.  Give SQLCMD mode a try!


Cant find the exe of mysqldump I'm just learning about MYSQL after 6 years focusing only on sql server(dumb me).
I'm studying about how backups works there, but I can't find the mysqldump file to execute the backup. on DOS it says the command is not recognized and i cant find it to use the full patch in the command line.
is it something that should be download apart?
and also as we are here. with SQL Server we create a physical file for the backup. with MYSQL if i'm not mistaken, we create a DUMP FILE that has the "commands" to insert data and create objects again, right?

It is stored where all binaries are from MySQL Server (if you didn't change the default location)
C:\Program Files\MySQL\MySQL Server 8.0\bin

And yes MySQL creates a sql file(dump), depending on the options you can have different commands like triggers and so on includes or excluded.
for a complete list of options always read up on the manual


How to execute AFTER INSERT TRIGGER with an auto-incremented (seq.NEXTVAL) column value in Oracle PL/SQL? Below is my AFTER INSERT TRIGGER in Microsoft SQL Server:

   CREATE TRIGGER new_airline
   ON Airline
   AFTER INSERT
   AS
   BEGIN
   DECLARE @Airline_Name VARCHAR(50), @Country VARCHAR(50)
   SELECT @Airline_Name=Airline_Name, @Country=Country
   FROM INSERTED
   PRINT 'New Airline has been added'
   END

   INSERT INTO Airline (Airline_Name, Country)
   VALUES ('Malaysia Airlines', 'Malaysia')

The above AFTER INSERT TRIGGER in Microsoft SQL Server works successfully. Another thing to be noted that there is also Airline_No column in the Airline table for which I didn't have to insert any values because Airline_No column has been set to AUTO-INCREMENT with INTEGER IDENTITY (1,1). Hence, the value will be auto generated everytime a new row is inserted.

Now, I want to execute this same AFTER INSERT TRIGGER logic in Oracle PL/SQL which is shown below:

CREATE OR REPLACE TRIGGER new_airline
AFTER INSERT ON Airline
FOR EACH ROW
BEGIN
INSERT INTO Airline (Airline_No, Airline_Name, Country)
VALUES (:NEW.Airline_No, :NEW.Airline_Name, :NEW.Country);
DBMS_OUTPUT.PUT_LINE('New Airline has been added');
END;

The above TRIGGER got compiled successfully but it was showing error when I was trying to insert the values which is shown below:

INSERT INTO Airline (Airline_No,Airline_Name, Country)
VALUES (airline_seq.NEXTVAL, 'Malaysia Airlines', 'Malaysia');

When I was trying to insert the values in Airline table I was getting the following error message which is shown below:

Error starting at line : 10 in command -
INSERT INTO Airline (Airline_No,Airline_Name, Country)
VALUES (airline_seq.NEXTVAL,'Malaysia Airlines', 'Malaysia')
Error report -
ORA-04091: table DEMO.AIRLINE is mutating, trigger/function may not see it
ORA-06512: at "DEMO.NEW_AIRLINE", line 2
ORA-04088: error during execution of trigger 'DEMO.NEW_AIRLINE'

Is there any logic in ORACLE PL/SQL AFTER INSERT TRIGGER that I possibly might have missed out ?

The trigger you have written will try and insert a second row, and is causing the triggers view of the table to mutate (as it will end up in an infinite loop, essentially).

You actually only need a BEFORE INSERT trigger that sets :NEW.Airline_No :

create trigger new_airline_btrig
  before insert on airline
  for each row
begin
  :new.airline_id := airline_seq.NEXTVAL;
end;
/

You can then insert using just:

INSERT INTO Airline (Airline_Name, Country)
   VALUES ('Malaysia Airlines', 'Malaysia')

:new.columnname alters the given inserted value (in this case, from NULL to the sequence value, as airline_id was not provided).


Login lifespan for password on SQL Server What is the "lifespan" for a login with the option Enforce Password Expiration Enabled?

Where can I find configuration/option to change the time?

Per the following article, This information can be found in:

Start -> All Programs -> Local Security Policy

Then navigate to Account Policies -> Password policy

You should see something similar to this:

http://www.mssqltips.com/sqlservertip/1088/sql-server-login-properties-to-enforce-password-policies-and-expiration/


Sql Server 2014 IO Resource Governor I've have configured the resource governor for cpu, memory, and IO.  I have my dbcc db checks running under a pool and workload that only allows 10% of memory and cpu, and only 100 IOPS.  I have also been tracking it as it happens and it looks like it is working just fine.  Limiting things the way I would expect.  However, it seems to be affecting other pools and workloads at the IO level.  Each db mdf and ldf sit on a netapp san, on their own volume.  There doesn't seem to be any io contention on the netapp side.  I wouldn't expect the IO governor to affect other things like this.  What should I be looking at to fix this?  The wait type on the dbcc is pagelatch_sh.  I also see plenty of pagelatch_up and _ex for the other jobs that end up taking longer than normal.

Do you know what pages are getting the latches? DBCC heavily uses tempdb as @ShawnMelton was alluding. Have you considered that tempdb contention could be the source of your pagelatches?  You should be able to investigate that with sp_whoisactive if you aren't already. How many data files do you have for tempdb? It sounds like you've only got one, try bumping it up to 4 and increase in intervals of 4 as needed, though 8 is often enough for most systems and many suggest starting with that number of data files. You'll also want to ensure the files are equally sized. You can also look at this post which has lots of info and links to reduce the impact of dbcc including a post from Paul Randall on running DBCC on a VLDB which might be helpful.


Restore/import tables from unsuccessful database dump file I'm in an urgent need of restoring a backup dump taken three weeks ago which was not completed successfully. I'm using Oracle 11g database in nonarchive log mode. The export command used was as follows.

expdp "sys/********@TEST AS SYSDBA" DUMPFILE=Backup_01022017.dmp LOGFILE=BakupLog_01022017.log FULL=y

The backup log shows that the process terminated with the following error.

ORA-39097: Data Pump job encountered unexpected error -1089
ORA-39065: unexpected master process exception in MAIN
ORA-01089: immediate shutdown in progress - no operations are permitted

But, fortunately the necessary tables are seen as exported. Are there any ways by which I can restore these tables or can I import these tables?

It depends, lets test it.

[oracle@server1 Desktop]$ expdp system/oracle directory=DATA_PUMP_DIR schemas=jay dumpfile=jay.dmp

Export process has started and I tried to shut the database down in a separate session.

SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.

And I noticed the following error in the export log.

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JAY"."MY_OBJECTS"                          98.58 MB 1017888 rows
. . exported "JAY"."TBL2OBJECTS"                         98.58 MB 1017888 rows
. . exported "JAY"."TBLOBJECTS1"                         8.225 MB   84824 rows
. . exported "JAY"."FOOD"                                6.875 KB       6 rows

UDE-01089: operation generated ORACLE error 1089
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1
Process ID: 28921
Session ID: 36 Serial number: 45

Now I tried to import one of the tables which was exported.

[oracle@server1 Desktop]$ impdp jay/jay directory=DATA_PUMP_DIR tables=my_objects dumpfile=jay.dmp

Import: Release 11.2.0.4.0 - Production on Wed Mar 22 04:47:15 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JAY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "JAY"."SYS_IMPORT_TABLE_01":  jay/password directory=DATA_PUMP_DIR tables=my_objects dumpfile=jay.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JAY"."MY_OBJECTS"                          98.58 MB 1017888 rows
Job "JAY"."SYS_IMPORT_TABLE_01" successfully completed at Wed Mar 22 04:47:50 2017 elapsed 0 00:00:33

And it succeeded.

But sometimes you may end up with the following errors.

  ORA-39002: invalid operation ORA-39059: dump file set is incomplete

  ORA-39246: cannot locate master table within provided dump files

Conclusion: You can restore exported tables as far as the dump file contains the  export job's master table - a table which is created during the startup of export process to keep the location of database objects within a dump file. You could see that master table called SYS_IMPORT_TABLE_01 is loaded before importing the table specified at the starting line of impdp log in the above section.