Is my mssql 2008 standard suitable for web edition 2014 A web edition seems enough for me when I read this.

I thought it had a 2gb size limit before? I have three databases our current db and am not doing anything fancy. Just some server agent jobs from http://ola.hallengren.com
This is our current db:

  Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)   Apr  3 2015 14:50:02   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (VM)

output of exec sp_spaceused
db1:

database_size 1355.81 MB
unallocated space 264.66 MB
reserved 758112 KB
data 647008 KB
index_size 105536 KB
unused 5568 KB

db2:

database_size 3141.25 MB
unallocated space 146.16 MB
reserved 1955672 KB
data 1725656 KB
index_size 216800 KB
unused 13216 KB

db3: (can be removed I hope) and seems similar to db2

it all runs on an xeon e5-2650 v3 @2.3ghz (4 cpu's) and 8gb ram. So I thought that a D2 on azure will perhaps be similar and a web edition of sql 2014.
(west Europe)
https://azure.microsoft.com/en-us/pricing/details/virtual-machines/#Windows
or is it recommended to move away from running a sql server on a vm and towards hosted sql. https://azure.microsoft.com/en-us/pricing/details/sql-database/

Web edition looks like Standard edition except "It is only available through an SPLA" going by the specs listed in the article you linked to.

With regard to a 2Gb limit, are you thinking about Express edition (or MSDE as it was called pre 2005)? That has had a per database size limit of 10Gb since SQL2008R2 (it was 4Gb in 2008 and 2005 and 2Gb in 2000 & SQL7). Note that is is per-database and data files only, so you could have several databases each under 10Gb that consume much more than 10Gb in total, and log files don't count towards the total. The key limit on Express is that it won't use more than 1Gb of RAM per instance which will limit performance for large data. You can mitigate this a little by running multiple instances. So running Express in a VM may be suitable for your needs and the least hassle option as you don;t have to worry about differences between SQLServer .

I don't have much experience with Azure SQL so I can't comment in any detail on the relative suitability of that for your needs. There are some differences in what is supported so you'll need to find documentation on that and test any potentially affected parts of your application. Be ware of which resources you use for this research: there have been many changes recently so articles form a couple of years ago may be pretty much irrelevant. See this SO question and other similar recent questions for potentially useful references. As well as feature compatibility you'll need to judge the performance requirements of your app against the "Database Throughput Units" (and therefore the cost) of the account types you are looking at, see this MS page for more details about the performance specs for each service tier.


Possible to tell what a query was in session I just killed? Our website was briefly down and we determined it was due to SQL Server not responding. I opened the Activity Monitor in SSMS and killed sessions belonging to a couple of devs.

The site then came back but now I have no idea what the were doing, or whether it was just coincidental that the site came up the same time I killed their processes. I have talked to the devs but didn't find out anything useful.

I don't suppose SQL makes any record of the query that was running in a session that's just been killed?

By default nothing keep tracks of all running query in SQL Server.  Depending on what the query was you might explore few things. None of these guarantee that you will find what you are looking for.

sys.dm_exec_query_stats (Transact-SQL)

Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

You will need to join these with other DMVs. Link has examples.
A Few Cool Things You Can Identify Using the Default Trace by Greg Larsen
Use the system_health Session if you still have the trace files.
Since you mentioned SQL Server not responding I would also look at SQL Error log, Windows event logs.


Does Oracle 10g Express Edition have enough storage? I am going to develop a simple library database with less than 10 tables. Each table would only keep text records. The library has about 10,000 volumes of books and about 2,000 members.

Would the storage capacity of Oracle 10g Express Edition (5GB if am not mistaken) be enough for the library database for the next 20 years? (Assume volume of books increase at a rate of about 1,000 books per year and the number of members increase about 100 people every year).

@a_horse_with_no_name is correct in recommending the 11g express and you would have more certainty that the project would not run out of room.

You should consider a proviso when you supply the database that you make no representation as to how long it can be used.  Even though you think you will only have ten tables software grows and requests to change or tinker with things never stop. For example:

oh, we have video content now, can you do that?
we need to have lending and borrowing audited
our client interface needs x
we need fast free text searching

All of these are possible but some are not advisable but they all need more tables and storage.

Also keep in mind that you are more likely to hit the limits for memory depending on how many concurrent users and the connection pooling methods used.
"Other hardware restrictions remain the same with CPU usage capped at one physical core and memory at 1GB".


Can stable (or immutable) functions call volatile functions? The PostgreSQL documentation states:

  Any function with side-effects must be labeled VOLATILE...

Consider the following function:

CREATE OR REPLACE FUNCTION count_items()
  RETURNS integer AS
$BODY$
DECLARE
  v_result INTEGER DEFAULT 0;
BEGIN
  SELECT
    count( t.id )
  INTO
    v_result
  FROM
    some_table t;

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    PERFORM error_log_insert( SQLSTATE, SQLERRM, current_query() );
    RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 10;

Since error_log_insert alters the database (performs an insert upon an exception), does this mean that the count_items function has a side-effect (albeit indirect), and thus cannot be declared STABLE, but must be VOLATILE?

In other words, does the stability or volatility of a function also depend on the functions it calls within its exception block?

If that is the case, then how would you create STABLE functions in PostgreSQL that log all exceptions to a database table?

Reading the documentation is in order. That comes from someone who just takes interest and never used Postgres and found the answer in the first google link:

http://www.postgresql.org/docs/current/static/xfunc-volatility.html

You may want to put particular emphasis on that part of stable:

  This category allows the optimizer to optimize multiple calls of the
  function to a single call.

So, the problem is - you want the calls to the method logged. But the optimizations of a stable function will lead to the function maybe not executed every time (because the return value for the same parameters is known, so it is reused), so the log will not be complete.

THAT BEING SAID:

As you only want to log exceptions, it may be ok - this also will keep your log smaller as you get only one instance in a query, even when the funciton WOULD be called 100 million times, as long as the optimizer optimizes it out. So, in your case - of exception logging - it may actually be ok. You will have to try out whether postgres developers are putting in protection agaisnt database manipulations, but my bet is no, and I would assume you get away with it.


Functional Dependency and Superkey As I understand Functional Dependency X -> Y in R, indicates Y can be determined by X and is FD if and only if X maps to one and only one Y.

if this is true then is it true to say X is a superkey?

I believe I have misunderstood functional dependency because I see questions related to determining superkeys keys based on the list of functional dependencies.

Is my above description of FD incorrect ?

"Y can be determined by X" is not clear. "X maps to one and only one Y" is not clear. You are not clearly saying what a FD is or what it means to functionally determine in R.

For attribute sets X & Y, a FD is an expression of the form "X -> Y". For attribute set S and attribute A, "S -> A" is another way of saying "S -> {A}" and "A -> S" is another way of saying "{A} -> S".

"FD 'X -> Y' holds in R" and "X -> Y in R" and "X functionally determines Y in R" are ways of saying "all the tuples with the same subtuple value for the attributes of X have the same subtuple value for the attributes of Y in R".

"X is a superkey of R" is another way of saying "X functionally determines every attribute in R".

Your question seems to be, does X -> Y in R imply that X a is superkey of R. No, it doesn't.


Add query hints to a CROSS APPLY I need to apply a query hint (like NOWAIT or NOLOCK) to the CROSS APPLY in this query. How do I do it:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT *
FROM sys.objects obj WITH (NOLOCK)
INNER JOIN sys.stats stat WITH (NOLOCK) ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) sp

I don't have access to the function contents (for obvious reasons.)
I went ahead and removed the repro from the question to make it more clear. I'm less concerned about the ability to reproduce the blocking. (It's also happening at customer environments, and in the First Responder Kit in issue # 2548.)

You can't, as far as I'm aware.
One might try things like:
OPTION (TABLE HINT (sp, READUNCOMMITTED))

...but this will fail for things that aren't real tables.

Table-valued or OPENROWSET function 'sp' cannot be specified in the TABLE HINT clause.

It would also fail because the desired hint is semantic-affecting.
If the thing you need to access doesn't respond to setting the isolation level, you'll have to report the deficiency and hope for a fix.
There was a Connect item about DMVs and whatnot not respecting isolation level, and as best I recall, the official word was that isolation level was not guaranteed for system functions.
Aaron Bertrand said:

There are some built-ins that simply don't obey isolation level no matter how the hint gets there (e.g. OBJECT_SCHEMA_NAME().

Also, as Martin Smith remarked:

The issue is really how to apply it to a TVF - not a CROSS APPLY
SELECT * FROM sys.dm_db_stats_properties(1,1) WITH (NOLOCK)

also fails. But conversely this succeeds:
SELECT *
FROM sys.objects obj WITH (NOLOCK)
INNER JOIN sys.stats stat WITH (NOLOCK) ON stat.object_id = obj.object_id
CROSS APPLY sys.objects obj2 WITH (NOLOCK)


SQL Query help: co-workers and employees from one query I have two tables defining users and user employments. The user table looks like this:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `fullName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
);

and the employments table:

CREATE TABLE `user_employment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `start` date DEFAULT NULL,
  `end` date DEFAULT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employer_id`) REFERENCES `user` (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `user` (`id`)
);

A user can employ users, but can also be employed. I need one query that can get all users that is a co-worker of a given user. I have tried with this query, but it only gives me employees for employer, or employer for employees:

    select u.id, u.username, u.fullName
      from user u
 left join user_employment e1 on e1.employee_id = u.id
 left join user_employment e2 on e2.employer_id = u.id
     where (e1.end >= NOW() or e1.end is NULL)
       and (e2.end >= NOW() or e2.end is NULL)
       and (e1.employee_id = <user id>
            or e1.employer_id = <user id>
            or e2.employee_id = <user id>
            or e2.employer_id = <user id>)
           and (e1.status = "confirmed"
                or e2.status = "confirmed")
               and u.id <> <user id>
  group by u.id;

If anything is unclear, please ask!

Thanks.

select u.id, u.username, u.fullName
from user u
join user_employment e1 on e1.employee_id = u.id
join user_employment e2 on e2.employer_id = e1.employer_id
where (e1.end >= NOW() or e1.end is NULL)
       and (e2.end >= NOW() or e2.end is NULL)
       and e2.employee_id = 2
       and e1.status = "confirmed"
       and e2.status = "confirmed"
       and u.id <> 2
group by u.id;

http://sqlfiddle.com/#!9/b13e6/4

Find the employer of a given user and then find all (other) employees of that employer. Do not use left joins unless you need them (you do not in this case).


Passing database name to input parameter from a stored procedure I have some issue with this application. I know that there are a lot of topics about passing a Database name as input but still I couldn't figure out what is the issue my code.
Hopefuly, you guys can help me out !
I have few database and everyday, a new database is created with XXXXX-YEARMONTHDAY as name.
What i want to achieve is to pass a new database name as input parameter from my stored procedure.
When it's hard written, it's working like a charm :
GO
/****** Object:  StoredProcedure [dbo].[WinccToAlarms]    Script Date: 03-12-20 08:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[WinccToAlarms]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924].dbo.AlgRtTextsFRA
END

But i want to dynamically name the [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924] part of the request.
Here is the code that i wrote :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms20
    -- Add the parameters for the stored procedure here
    @NomDBAlarms sysname
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    IF DB_ID(@NomDBAlarms) IS NULL
    BEGIN
    RAISERROR('Nom de base de donnée invalide',16,1)
    RETURN
    END

    DECLARE @Sql NvarChar(max)

    -- Insert statements for procedure here
    SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM ['+(@NomDBAlarms)+'].dbo.AlgRtTextsFRA'

    Exec @Sql
    END
GO

Can you help me figure out what is wrong with this piece of code?
Thanks a lot !!!

Ok. This is solved. I had to enter as input parameter the value [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924] instead of WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924
Also for anyone who might want the final and working code; Here it is !
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms22
    -- Add the parameters for the stored procedure here
    @NomDBAlarms sysname
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    IF DB_ID(@NomDBAlarms) IS NULL
    BEGIN
    RAISERROR('Nom de base de donnée invalide',16,1)
    RETURN
    END

    DECLARE @Sql NvarChar(max)

    -- Insert statements for procedure here
    SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM ['+@NomDBAlarms+'].dbo.AlgRtTextsFRA'

    Exec sp_executesql @Sql
    END
GO

Thank you for your support and have a nice day !


Permission error when using sys.dm_exec_input_buffer with @@SPID When I run this blob of SQL on SQL Server 2016 or an Azure SQL DB:

--hello world
SELECT      event_info
FROM        sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)
WHERE       req.session_id = @@SPID

I get this error, followed by the expected results:

Msg 300, Level 14, State 1, Line 5
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line 5
The user does not have permission to perform this action.
--hello world
SELECT      event_info
FROM        sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)
WHERE       req.session_id = @@SPID

The documentation for sys.dm_exec_input_buffer states (emphasis mine):

  On SQL Server, if the user has VIEW SERVER STATE permission, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.

Thus, according to the documentation, I am allowed to access this data. Why, then, is the error being raised?

Using the condition WHERE req.session_id = <some value> triggers the error condition. Then the line is ignored, and the rest runs correctly. Try executing:

SELECT      event_info
FROM        sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)

without the WHERE clause, and you should get the correct output.

tl;dr Looking for a specific session_id triggers the error.


Failure during 2 backup jobs for one database I have 2 jobs for one database.  One does a full backup every hour.  The other one is a transaction log backup every 15 minutes.

I've checked the job history.   It shows some failures at 16:00.  The detail is as follows:

Does that mean that 2 jobs can't run at the same time?

The error detail is:

  Message Executed as user: NT SERVICE\SQLSERVERAGENT.
  Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorksLT_from_Azure.bak'.
  Operating system error 32(The process cannot access the file because it is being used by another process.).
  [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally.
  [SQLSTATE 42000] (Error 3013). The step failed.'

Could you please help me for that?

There are two possibilities that come to mind:
 1. The full backup and the transaction log backup are trying to use the same file.

  'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorksLT_from_Azure.bak'

Something else is locking the file i.e. tape backup
I have had tape backups cause this problem for me. I had to wait until the tape backup finished then my SQL Agent backup job would work.


Add data to second table on UPDATE (PostgreSQL only) I have table ticket which has these (and other) columns:

id
ticket_type
client

If ticket_type or client gets updated, then the table transfere_table should get filed. The ticket.id of the changed ticket should get inserted into the transfer table.

Here is the working code:

CREATE FUNCTION add_ticket_to_transfere_table () RETURNS trigger
       LANGUAGE plpythonu AS $$

plpy.execute(plpy.prepare(
'''INSERT INTO transfere_table (ticket_id) VALUES ($1)''',
     ['int']), [TD['new']['id']])
$$;

CREATE TRIGGER add_ticket_to_transfere_table_trigger
    AFTER UPDATE OF ticket_type_id, client_id ON ticket
    FOR EACH ROW
    WHEN (OLD.ticket_type_id != NEW.ticket_type_id or
         OLD.client_id != NEW.client_id)

    EXECUTE PROCEDURE add_ticket_to_transfere_table();

Since I only used plpythonu up to now, I solved it like this. It works, but I see two things to improve:

get rid of FOR EACH ROW: If N rows get updated, it would be nice if only one INSERT gets executed
Don't use plpythonu

How to realize these improvements?

Are there other things which could get improved?

I am using PostgreSQL 9.3.13

get rid of FOR EACH ROW: If N rows get updated, it would be nice if only one INSERT gets executed

How would that work, you wouldn't have access to OLD or NEW, because it'd be a statement-trigger.

  Don't use plpythonu

Python natively doesn't support running as a trusted language. You can easily use plpgsql.

Code for a plpgsql function,

CREATE OR REPLACE FUNCTION add_ticket_to_transfere_table()
RETURNS TRIGGER
AS $$
    BEGIN
      INSERT INTO transfere_table (ticket_id) VALUES (new.id);
      RETURN new;
    END;
$$
LANGUAGE plpgsql;

This isn't how I would personally run this though. What I would do is

Create a boolean on the row for triaged or whatever the transfere_table represents.
When you insert, have that Boolean default to true. Effectively a column-trigger if no data is provided. When you update the ticket, have that boolean set in the update statement.

If you don't want to touch that the table itself, I'd run your process in reverse.

Create the table transfere_done

CREATE TABLE transfere_done (
  ticket_id int PRIMARY KEY REFERENCES ticket.ticket_id
)

Copy over all the ids to it.
Create the below view,

CREATE VIEW transfere_queue
AS
  SELECT * FROM ticket
  WHERE NOT EXISTS (
    SELECT 1
    FROM transfere_done
    WHERE transfere_done.id = ticket.id
  );

Now, when you insert into ticket, it automagically shows up under transfere_queue until you insert into transfere_done. You may or may not want to MATERIALIZE the view. Either way, I really hate triggers when not needed and I try to engineer around them.


What Host Name is my application connecting to SQL Server on We have this 1 node broken cluster that I have been given the joy of fixing. Wont go into the reasons why its only got one node but I am planning on migrating this off onto a newly built cluster.

So the broken cluster is running on a single node "NODE1" on IP "10.1.1.10".

The old SQL Cluster Name has been added as a static entry into DNS "SQLClusterName" and its pointing to NODE1's IP address 10.1.1.10.

For the Migration I am going to use the old cluster name "SQLClusterName" so that we don't need to change a bunch of applications connection details.

The issue I have is that there is an application that uses a database on this server and we are unable to change the app at all. I am trying to make sure that said app is connecting on "SQLClusterName" and not "NODE1".

Is there a way without checking the actual application to see what Host Name the application is connecting through to SQL Server on? For the life of me I cant seem to find this information anywhere.

Thanks.

Assuming you can install the app on a Windows 7 (or higher) workstation you can perform the following steps to determine exactly what server and IP address the app is using:

Start a cmd.exe prompt "As Administrator"
Run "ipconfig /flushdns"
Run the app, and ensure it connects to the server
From the cmd.exe prompt, run "ipconfig /displaydns"

The output from step 4 will show all host names, along with the IP address(es) for those hosts that have been resolved in step 3.  Try to ensure nothing else is running between step 2 and step 4 to narrow down the results displayed in step 4.


MariaDB server with 80K-100K DataBases BACKGROUND :

We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB in /var/lib/mysql/ which is 500GB size.

We are using MariaDB 5.3.5 (FIXED, no chance of upgrade) on Centos6 VM.

Problem :

When the mysql volume is 90% used with 85K DataBases, creation of new Databases is very slow, and DB Dropping is also very slow.

Questions :

Is 85K DataBases too much for MariaDB 5.3.5 ?
What is the Documented safe limit ?
How do I make DB creation not get blocked by DB Deletion ?
What could be the real culprit for slowness when there are too many DataBases ?
What Parameters can I enable on the server to track the slowness ?

Details :
Each DB will have 12 tables. Each table will have 10~100 rows.
Each DB will be around 3MB~5MB.
We are using LVM, with / mounted as ext4.
MariaDB 5.3.5 is running on Centos6.6 VM with Linux Kernel 2.6.32-504.3.3.el6.x86_64.
VM has 4 cpus (3GHz) with 32GB RAM, hosted on VMWare ESX 5.5 running on a Cisco UCS Blade.

As already stated, the filesystem is the problem.  However, there may be some tuning that would help.
If innodb_file_per_table has been ON, then there are 2*12 files in each database directory.  Turning that OFF would lead to fewer files (but not fewer directories).  This might help some.
SHOW VARIABLES LIKE 'table%';
SHOW VARIABLES LIKE 'innodb%files';
SHOW VARIABLES LIKE 'open%';
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
SHOW GLOBAL STATUS LIKE 'Table%';

There is some info that can be gathered from those outputs.
Or, give me (1) amount of RAM, (2) SHOW VARIABLES;, and (3) SHOW GLOBAL STATUS;.  I will run a number of checks to see if the table_open_cache is too small, and other things.


Detect if any values in NVARCHAR columns are actually unicode I have inherited some SQL Server databases. There is one table (I'll call "G"), with about 86.7 million rows, and 41 columns wide, from a source database (I'll call "Q") on SQL Server 2014 Standard that gets ETL'd over to a target database (I'll call "P") with the same table name on SQL Server 2008 R2 Standard.

i.e.
[Q].[G] ---> [P].[G]

EDIT: 3/20/2017:
Some people have asked if the source table is the ONLY source to the target table. Yes, it is the only source. As far as the ETL goes, there isn't any real transformation happening; it effectively is intended to be a 1:1 copy of the source data. Therefore, there are no plans to add additional sources to this target table.

A little over half of the columns in [Q].[G] are VARCHAR (source table):

13 of the columns are VARCHAR(80)
9 of the columns are VARCHAR(30)
2 of the columns are VARCHAR(8).

Similarly, the same columns in [P].[G] are NVARCHAR (target table), with the same # of columns with the same widths. (In other words, same length, but NVARCHAR).

13 of the columns are NVARCHAR(80)
9 of the columns are NVARCHAR(30)
2 of the columns are NVARCHAR(8).

This is not my design.

I'd like to ALTER [P].[G] (target) columns data types from NVARCHAR to VARCHAR.
I want to do it safely (without data loss from conversion).

How can I look at the data values in each NVARCHAR column in the target table to confirm whether or not the column actually contains any Unicode data?

A query (DMVs?) that can check each value (in a loop?) of each NVARCHAR column and tell me if ANY of the values is genuine Unicode would be the ideal solution, but other methods are welcome.

Suppose one of your columns does not contain any unicode data. To verify that you would need to read the column value for every row. Unless you have an index on the column, with a rowstore table you will need to read every data page from the table. With that in mind I think it makes a lot of sense to combine all of the column checks into a single query against the table. That way you won't be reading the table's data many times and you don't have to code a cursor or some other kind of loop.

To check a single column believe that you can just do this:

SELECT COLUMN_1
FROM [P].[Q]
WHERE CAST(COLUMN_1 AS VARCHAR(80)) <> CAST(COLUMN_1 AS NVARCHAR(80));

A cast from NVARCHAR to VARCHAR should give you the same result except if there are unicode characters. Unicode characters will be converted to ?. So the above code should handle NULL cases correctly. You have 24 columns to check, so you check each column in a single query by using scalar aggregates. One implementation is below:

SELECT
  MAX(CASE WHEN CAST(COLUMN_1 AS VARCHAR(80)) <> CAST(COLUMN_1 AS NVARCHAR(80)) THEN 1 ELSE 0 END) COLUMN_1_RESULT
...
, MAX(CASE WHEN CAST(COLUMN_14 AS VARCHAR(30)) <> CAST(COLUMN_14 AS NVARCHAR(30)) THEN 1 ELSE 0 END) COLUMN_14_RESULT
...
, MAX(CASE WHEN CAST(COLUMN_23 AS VARCHAR(8)) <> CAST(COLUMN_23 AS NVARCHAR(8)) THEN 1 ELSE 0 END) COLUMN_23_RESULT
FROM [P].[Q];

For each column you will get a result of 1 if any of its values contain unicode. A result of 0 means that all data can be safely converted.

I strongly recommend making a copy of the table with the new column definitions and copying your data there. You'll be doing expensive conversions if you do it in place so making a copy might not be all that much slower. Having a copy means that you can easily validate that all of the data is still there (one way is to use the EXCEPT keyword) and you can undo the operation very easily.

Also, be aware that you might not have any unicode data currently it's possible that a future ETL could load unicode into a previously clean column. If there is not a check for this in your ETL process you should consider adding that before doing this conversion.


Local bus schedule structure/design I'm Gergo Nagy, and I'm learning databases. My project is a bus schedule program, in android, but I stucked with the database part.
My program will include 2 main parts:
-If I click on a number of a bus, it shows the whole schedule, and a number, that shows when will the next bus start from the departure point.
-if I click on the address of the bus stop, it shows which buses crosses this stop. But it only shows that buses, which arrives in an hour.

My problem is the many-to-many database, because one bus has a lot of stops, but a stop "has" a lot of buses.
I'll attach a picture, from the schedule, I hope with this it will be easier to understand my question, 'cause my english is really bad, and sorry for that.

I know it's a mess, but with this level of english knowledge, it's my best.
I hope you can help me.
Thank you in advance

You have these entities to convert in tables.

I suggest you build the fallowing tables in excel and see if all is there or something missing.

Stations : has name , and id .

Routes : connect stations and defines the bus time from one to another station.

Bus_and_start: when will every buss start and what route. Do you have a list of buses , like buss_long1 , bus_short1 , bus_extralarge4 ? Then split this table in 2 , the bus table and the start table. This would be good if a bus breaks or if you monitor real time if is to slow . Real_time monitoring will bring a list of challenges, not sure if this is part of your project.

To see for a station the next hour bus , you take all the routes that stop in that station and join with all bus_and_start and the route time to the bus start time and here is the list. If you need help , define the tables and I will help with the SQL statement.


Postgres IDLE State affect Max_Connection value set in Postgresql.conf I am using Postgres 9.5 and we have a max_connection set to 200 in our database postgresql.conf file.
We have 2 apps server running java apps which are load balanced. When we run our tests via JMETER, I run pg_stat_activity and I can se there is alot of IDLE connections with most of the queries called COMMIT.
Do I need to be concerned with the IDLE connections and will this affect any new tests I run. Could this be an issue with my Java apps not auto_commiting?
My assumption is that the connections will be re-used but concerned if we run more tests new connections will spin up and the max_connection of 200 users will be hit.
Any help is much apprecaited.

When the state is idle, then the query shown in pg_stat_acitivy is the last query that was run before it went idle.  Since that was apparently COMMIT (and since the state is idle, not 'idle in transaction'), then no, you don't need to worry about autocommit not working.

My assumption is that the connections will be re-used but concerned if we run more tests new connections will spin up and the max_connection of 200 users will be hit.

If your java connection poolers are set up correctly, they should be reused.  If you run out of max_connections, you will hear about it in the log files.  Why preemptively worry about it?  Are you running your tests against your production database?


checksum(newid()) as primary key - what happens if a collision occurs? Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). This makes me feel uneasy; the newids are guaranteed to be unique, but by applying the checksum (to a 32-bit integer, presumably for performance reasons?), you have some chance that you get a collision (with 1M rows in the table, I'd put that chance at around 1:4000, too much for my liking).

So:

Am I missing some crucial bit of information that says the above is actually okay?
If not - what happens if the application tries to insert a new row into the table and the checksum(newid()) gives a primary key that already exists? Will my table blow up? Will the insert fail and leave it up to the application what to do with it?

I have to presume that the application is written in such a way that it tries the insert, and if it fails, it just tries again. Otherwise the insert will fail (PK violation) and your users will have complained and filed bugs.

If the purpose of this is to just assign unique numbers in a random order, then it may be the case that the application is working just fine. I think you should only file a bug report if you can demonstrate that the application does something wrong when a collision occurs - not easy given the likely failure rate. An easier way, if you have source code access, would be to review the portion of the code where they perform the insert (or find out if it calls a stored procedure to do so).

That in mind, there is a much easier and fault tolerant way to do this. Build a table up front, with a slew of numbers ordered randomly, then just pluck a number off the top whenever you need one.

CREATE TABLE dbo.Destination(ID INT /*, other columns */);

CREATE TABLE dbo.Source
(
  RowNum INT PRIMARY KEY,
  ID INT, -- UNIQUE
  Used BIT NOT NULL DEFAULT 0
);

INSERT dbo.Source(RowNum, ID)
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), ID
  FROM
  (
    SELECT ID = CHECKSUM(NEWID())
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
      -- should produce about 4 million rows,
      -- on my crappy VM this took two minutes,
      -- add another cross join if you need more
  )
  AS x GROUP BY ID;

Then when you need to add a row, you can just say:

UPDATE TOP (1) dbo.Source SET Used = 1
  OUTPUT inserted.ID /*, @params */
  INTO dbo.Destination
WHERE Used = 0;

(You could also DELETE TOP (1) however I find keeping them useful for auditing etc.)


Could someone recommend a db management tool that supports SQLite, MySQL, PostgreSQL, and is web-based? Could someone recommend a db management tool that supports SQLite, MySQL, PostgreSQL, and is web-based? By 'web-based' I just mean it can be run in a browser locally or remotely.

If a remote desktop is an acceptable web-based solution, you could have Navicat Premium on a remote server that way you don't need the tool locally (premium is the cross-databases edition of the tool: it has MySQL, PostgreSQL, SQL Lite and even SQL Server) .

But you would find cheaper/free if you simply install a tool per database type (e.g. phpMyAdmin, Chive, phpPgAdmin, SQLiteManager).


Why is READ COMMITTED a common default transaction isolation level? I am wondering if anyone knows the history of why READ COMMITTED is the default transaction isolation level for PostgreSQL, SQL Server, Oracle, Vertica, DB2, Informix, and Sybase.

MySQL uses default REPEATABLE READ, at least with InnoDB, as do SQLite and NuoDB (they call it "Consistent Read").

Again, I am not asking for what the differences are between different isolation levels, but rather for some explanation of why the default was chosen to be READ COMMITTED in so many SQL databases. My wild guesses are: small performance benefit, ease of implementation, some recommendation in the SQL standard itself, and/or "that's the way it's always been". The obvious downside of this choice is that READ COMMITTED tends to be quite counterintuitive for developers and can lead to subtle bugs.

Though, I don't remember any reference mentioned in BOL thus can't provide it here but as per my understanding it is related to locking. Higher level of isolation level can cause locking issues. READ COMMITTED ISOLATION is more towards write locks than read, which fits good in OLTP environment when compared to OLAP. Commercial databases decides default isolation level which fits perfect for their internal algorithm. Choosing isolation level depends upon how RDBMS wants to deal with locking and care about reading correct data. Most of the RDBMS prefers READ COMMITTED for faster read, performance and to minimize locking.

A lower isolation level increase concurrency and descrease waiting for other transaction but increase the chances of reading incorrect data.
However, a higher isolation level decreases concurrency and increases waiting for other transaction, but decreases the chance of reading incorrect data.

As per Wikipedia

  The default isolation level of different DBMS's varies quite widely.
  Most databases that feature transactions allow the user to set any
  isolation level. Some DBMS's also require additional syntax when
  performing a SELECT statement to acquire locks (e.g. SELECT ... FOR
  UPDATE to acquire exclusive write locks on accessed rows).

  However, the definitions above have been criticized [3] as being
  ambiguous, and as not accurately reflecting the isolation provided by
  many databases:

  This paper shows a number of weaknesses in the anomaly approach to
  defining isolation levels. The three ANSI phenomena are ambiguous.
  Even their broadest interpretations do not exclude anomalous behavior.
  This leads to some counter-intuitive results. In particular,
  lock-based isolation levels have different characteristics than their
  ANSI equivalents. This is disconcerting because commercial database
  systems typically use locking. Additionally, the ANSI phenomena do not
  distinguish among several isolation levels popular in commercial
  systems.

You can read the complete synopsis here.


Logins Not being used in last one month I have a requirement to create a query which can get the logins details which were not used in last one month.
I have tried several way like extended events and audit tracing. But i'm unable to get the list of the logins that were not being used in last one month,
Please help me with this.

Since you've already added the successful login auditing to the instance, you can dump the error log data to a #temp table, then parse the individual login names, and then find all the rows in sys.server_principals that don't exist in the filtered data.

CREATE TABLE #x
(
  LogDate datetime,
  ProcessInfo nvarchar(max),
  [Text] nvarchar(max)
);

DECLARE @dt datetime = DATEADD(MONTH, -1, GETDATE());

INSERT #x(LogDate, ProcessInfo, [Text])
  EXEC master.sys.xp_readerrorlog 0, 1, N'Login succeeded', NULL,
    @dt;

;WITH p(pattern) AS
(
  SELECT SUBSTRING([text], CHARINDEX(CHAR(39), [text]) + 1, 500) FROM #x
),
x(name) AS
(
  SELECT SUBSTRING(pattern, 1, CHARINDEX(CHAR(39), pattern)-1) FROM p
)
SELECT p.name, p.create_date, p.is_disabled
  FROM sys.server_principals AS p
  WHERE p.principal_id > 255      -- no system logins
  AND p.name NOT LIKE N'##%##'    -- e.g. ##MS_AgentSigningCertificate
  AND p.name NOT LIKE N'NT %\%'   -- e.g. NT AUTHORITY\SYSTEM
  AND p.name NOT IN (SELECT name FROM x);

Assuming that perhaps your current error log file is less than a month old, you can see the following article for ideas on searching more than one error log at a time:

Search multiple SQL Server Error Logs at the same time

In the future, I would rather create an audit specification or extended events session to avoid polluting the error log.

CREATE SERVER AUDIT GoodLogins
TO FILE (FILEPATH = 'C:\temp\GoodLogins\');
GO

CREATE SERVER AUDIT SPECIFICATION GoodLoginsSpec
  FOR SERVER AUDIT GoodLogins
  ADD (SUCCESSFUL_LOGIN_GROUP);
GO

ALTER SERVER AUDIT GoodLogins WITH (STATE = ON);
GO

Then you can just query all of the audit data:

DECLARE @dt datetime = DATEADD(MONTH, -1, GETDATE());

;WITH x(name) AS
(
  SELECT server_principal_name
    FROM sys.fn_get_audit_file('C:\temp\GoodLogins\*', NULL, NULL)
    WHERE event_time >= @dt
    GROUP BY server_principal_name
)
SELECT p.name
  FROM sys.server_principals AS p
  WHERE p.principal_id > 255      -- no system logins
  AND p.name NOT LIKE N'##%##'    -- e.g. ##MS_AgentSigningCertificate
  AND p.name NOT LIKE N'NT %\%'   -- e.g. NT AUTHORITY\SYSTEM
  AND p.name NOT IN (SELECT name FROM x);


Another - ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be used in an aggregate function I am attempting to aggregate dependency_device_name into one string, "Dependency List". Here is the error:

  ERROR:  column "da2.dependency_device_name" must appear in the GROUP
  BY clause or be used in an aggregate function.

Here is the sql. I have added the field to the group by and still have the same error message:

Select
xy1."Dependency Count",
xy1."Dependency List",
ag2.report_type_name,
xy1.dependent_device_name
from
view_affinitygroup_v2 ag2
left join
 (
   Select
   da2.dependency_device_name,
   da2.effective_to,
   da2.dependent_device_name,
   da2.dependent_device_fk,
   sum(1) "Dependency Count",
  string_agg(da2.dependency_device_name,', ') "Dependency List"
  from
    view_deviceaffinity_v2 da2
     ) as  xy1
   on xy1.dependent_device_fk = ag2.primary_device_fk and xy1.effective_to is null
   where ag2.report_type_id = 1
   group by
     da2.dependency_device_name,
     ag2.report_type_name,
     xy1.dependent_device_name,
     xy1.effective_to

It needs to be in the GROUP by of the "xy1" subselect, not of the outer query.

But it probably really needs to be taken out of the select list of "xy1" and not put in the GROUP BY.  Having it in both the string_agg and the GROUP BY would be legal, but seems rather pointless.

Where the error message says "or be used in an aggregate function", it means "or be used only in an aggregate function"

There many ways to form a legal query, but the question is, which of them does what you want?

It seems like you want your subquery to be this:

Select
  da2.dependent_device_name,
  da2.dependent_device_fk,
  sum(1) "Dependency Count",
  string_agg(da2.dependency_device_name,', ') "Dependency List"
from view_deviceaffinity_v2 da2
  where effective_to is null
  group by da2.dependent_device_name, da2.dependent_device_fk

And then you would not want the "effective_to" in your join condition, having filtered them out already.


Downgrade SQL Server from Enterprise to Developer edition in an Availability Group I will be transitioning some non-production SQL environments from Enterprise to Developer edition over the next few months.  I'm pretty settled on the procedure I will be using, which is laid out in this blog post:
Downgrading SQL Server Editions
I have not been able to find any information specific to any special precautions needed when doing this in an environment with Availability Groups. We have a 2 node Synchronous commit AG.  Considering we are going from Enterprise to Developer, I am not concerned with Enterprise only features being an issue.  My assumed workflow will be:

Downgrade the secondary first according to the blog post above, make sure everything is synchronized afterwards.
Fail over to the secondary(now on developer edition)
Downgrade the old primary, and make sure db's are synced up.

It's essentially the same process I use for rolling patches and upgrades, but not sure if there are special considerations for downgrading editions...  Anyone have any issues doing this?

Given that, by definition, that only happens in non-production environments, I doubt any one else has tried it.  As always, a side-by-side migration is safer than any procedure involving uninstalling and reinstalling SQL Server.
Remember, cattle, not pets.  If you have the capability to easily create a new cluster and deploy an AG on it, that procedure will satisfy a large number of operational scenarios, including this one.


Is it wise to create indexes for the replication server only? At my workplace we have a requirement to have extra indexes on the replication server (we replicate data only). This is because we a bunch of reporting that runs off the replication server so some more indexes will help this run faster. These indexes would slow down actual transactions for our customers so we don't want them on the live DB.

I'm not an expert with databases and it's my first job, and so I'm turning here for a bit of advice, since I don't know whether my workplace is headed down the right path.

I know this is a somewhat broad question, but what I would like to know is:

are there better ways of achieving what we want
is what we want a really bad idea and we should avoid it

I am happy to implement what my workplace has asked me to do, but thought it might be better to seek some wisdom from people who have a lot more experience.

This is a common requirement.  Transactional Replication is typically used to off-load reporting to another server/instance, which can be near real-time in a best case scenario. One of the benefits of Transactional Replication is you can place different indexes on the subscriber(s) to optimize reporting.  Another benefit is that you can choose to replicate only a portion of the data if only a subset is needed for reporting.

So to answer both of your questions:

You are taking the correct approach.
This is a common requirement and is not a bad idea at all.

Just keep in mind that the indexes that you place on the subscriber(s) will need to scripted out and included in a post-snapshot script in case you ever reinitialize the subscriber(s).


Cannot restore azure database to local due to missing master key I create a bacpac from azure and restore to my local on-prem server. However, it throws an error like “Please create a master key in the database or open the master key in the session before performing this operation”. The reason is one of the column in the table is encrypted. I don’t know how to restore the database from azure with master key and certificate etc. Do you know what I could do?

Thanks in advance,

Restoring a BACPAC isn't the same as restoring a SQL Server native backup (created using the BACKUP DATABASE syntax).

A native backup using BACKUP DATABASE is created by exporting all schema & data by backing up at the page level into backup files. By convention, these generally have a .bak file extension. A RESTORE DATABASE does the inverse to create a database by copying the data pages out of the .bak and into the database. (It's a little more involved than this, but that's the big picture.) If the source database is encrypted with TDE, the data pages in the backup are also encrypted.

A BACPAC is created by generating CREATE statements for all of the schema, then doing an export of data. The .bacpac file itself is actually just a zip file of the CREATE scripts & data export. When doing a "restore" of the BACPAC, it simply executes the CREATE scripts, then does an import of the data. If the source database is encrypted with TDE, the exported data within the .bacpac file is not encrypted.

If you want to create a TDE-encrypted database using a BACPAC, simply pre-create an empty database and enable TDE on that database. Then, import the BACPAC into that empty database, and Bob's your uncle.


Minimize locking on UPDATE with multiple WHERE columns I have a problematic query that's causing a lock timeout:

UPDATE <some_table> SET col1=<some value>
WHERE col1 IS NULL AND col2 > <some value>
ORDER BY col2
LIMIT 100

I have two issues here:

I have multiple servers committing the query simultaneously, they lock each other out and I get lock timeout or even deadlock sometimes. Optimally the servers should update mutually exclusive rows so locking shouldn't happen at all. Is there a way I can skip any locked row in the update?
If I can't avoid locking, and I already have an index for col1 and another one for col2, will Innodb lock all rows that satisfy any condition in the WHERE clause or only the ones that satisfy both conditions? If the answer is the former, can I add an index for the two columns together or do I also need to remove the indexes that I have (for each column separately)?

You will need to lock all the rows yourself before each UPDATE.

See the MySQL Documentation on SELECT ... LOCK FOR UPDATE. This performs an exclusive lock on all the rows you pass through. Then, you can follow up with the needed UPDATE against the table.

In your particular case, you would do this:

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100

INDEXING

You should index the table fully to support all possible ways you will be querying the data. Notwithstanding, you must alternate between SELECT ... FOR UPDATE and UPDATE.
Since you have both row1 and row2 in the WHERE clause, you should have an index with both columns in it.
There is one warning: If these columns are indexed, expect some slowness because the column is being updated and the BTREE index pages are being updated per row. You should also expect rapid growth of the insert buffer section of ibdata1 (See InnoDB Map)

I have many posts on the subject of SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARED MODE.

Case Study I went through with @RedBlueThing

Reasons for occasionally slow queries?
Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?

Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
Oct 18, 2011 : Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
Jan 02, 2012 : LOCK IN SHARE MODE
Nov 19, 2012 : How long can Deadlock info hold in innodb status page?
Dec 13, 2012 : MySQL InnoDB locks primary key on delete even in READ COMMITTED
Feb 03, 2013 : MySQL InnoDB locking on combined UPDATE-JOIN statements
Mar 12, 2013 : How I prevent deadlock occurrence in my application?

UPDATE 2013-03-17 19:21 EDT

Since you have 9 WebServers hitting the DB Server, try this

On WebServer1 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 0,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 0,100;

On WebServer2 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100,100;

On WebServer3 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 200,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 200,100;

All the way to WebServer9, run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 800,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 800,100;

You will have to place some PHP header file that unique identifies which machine runs which version of the query.


Backup Performance best practices I'm in the process of re-writing our maintenance plans and at the moment the focus is backups. Whilst preparing this plan, I'd like to try and ensure that procedures are in place to ensure that backup performance is optimal. I am aware of implementing the below, however I'd like to get an idea of how I can improve beyond that scope.

Current Considerations:

Backup Compression
Multiple data files for larger databases - perhaps over 100GB?
Writing backups to another I/O subsystem
Defining values for parameters including BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT
Purging old files
Deleting backup history

Beyond the above, is there anything that would be benificial to implement. Also whilst setting the values for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT, are there any considerations I should take/how do I go about defining the correct values? I appreciate it will be a bit of trial and error with testing but it would be useful to get an idea of best practices.

To give an idea of my routine, I'm to run hourly transaction log backups, daily differentials and weekly full backups.

Thanks

Firstly, I'd encourage you to stop using maintenance plans for your backups.  Ola Hallengren's maintenance scripts are used by a LOT of people.  Simply schedule them using the SQL Agent.

I agree that taking steps to ensure your backup process is optimal, but I'd also encourage you to think about your restore strategy as well.

By taking transaction log backups every hour, you are saying it's ok to lose up to 1 hour of updates should you need to restore. IMO, implementing more granular backups is really no more difficult and brings some additional piece of mind.  See Back Up Transaction Logs Every Minute. Yes, Really.

When researching and developing a restore strategy, the main two things you'll tend to see over and over are: RTO (Recovery Time Objective) and RPO (Recovery Point Objective).

RTO basically is the maximum time allowed to get things back to 'normal' or at least functioning. You would need to take into consideration small problems (server went down) to big problems (the building just exploded).

RPO basically is, how much data could we 'possibly' lose should a bad problem happen? 5 minutes, 1 hour, 1 day, NONE?

Take some time to read The 9 Letters That Get DBAs Fired
(Basically RPO, RTO and CYA).

Also, The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy, discusses Designing a Restore Strategy.

One last thing - develop a plan to routinely test your backups.

The only way to verify a backup is good/usable is to restore it.

REPEAT – you don’t know you have a good backup until you have restored it!

I provided an example in my answer to How to conduct integrity test on SQL Server database backup file?


Where should I look for my.cnf? I want to add innodb_file_per_table statement to my.cnf file.

Where is my.cnf?

Could I change this file when I have an online site?

I don't know XAMPP that well, but a quick search turned this up:

  How to find my.ini and my.cnf

  To find these MySQL config files, do this:

  Go to Folder Options: in any folder, go to the top horizontal main text menu >> Tools >> Folder Options
  Enable 'View Hidden Files and Folders', and 'View Protected System Files', save & exit
  Go to C Drive, locate and rename the my.ini file, which will now be visible. Rename it as my.ini.bak
  Go to /xampp/msql/bin - locate and rename my.cnf, to my.cnf.bak
  If there is no file my.cnf, but there is one called my - then that is the one you want. Rename it to my.cnf.bak

As for whether you can do this with an online site, you can change it online, but unless you update your my.cnf, the change will be lost after mysql restarts the next time.


SQL Query Joins Multiple Tables - Results in Duplicate Rows This Question/Answer seems close to mine, but I think I have a different problem.

Here's my query:

SELECT
PL.PlantName AS 'Plant Name',
M.MachineNumber AS 'Line',
P.Type AS 'Record Type',
P.Reason,
P.ActivityDate,
P.OrderNumber,
P.Bundle,
P.Quantity,
P.ItemLength,
P.Pattern,
P.PartOption,
P.InvCoil,
P.Footage,
P.Material,
P.ScrapCode,
P.EmployeeName,
P.LBperFt,
P.DelayCode,
P.DelayReason,
P.Duration,
P.CostPerLb,
P.ListId,
P.ListText
FROM dbo.Production P WITH (NOLOCK)
JOIN dbo.Plants PL WITH (NOLOCK) ON P.PartnerId = PL.PartnerId
JOIN dbo.Machines M WITH (NOLOCK) ON PL.Id = M.Machine_Maintenance_Plant_Maintenance
WHERE ActivityDate >= '2018-04-01' AND ActivityDate <= '2018-04-07' ORDER BY 'Plant Name';

Before I added the third table, the query was fine.  After joining the third table, I'm getting 6 copies of each row returned.

This means there is 1 to Many relationship on the third table. You either need to add more in your join to make it 1 to 1 or create a crosswalk table to do the same.


When should I use the shrink option I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, etc.

Now the doubt I have is what are the scenarios where I should use the shrink option in the database.

I have never seen a scenario it was stated as the useful one. Is shrinking always evil?

You'll know when you need to shrink your database files, because it'll most likely come from a requirement to reclaim disk storage.  Even if there is a database that has a large amount of free space in it, I usually still like to keep it as such without shrinking.  No matter what people try to claim that it'll never use all that space, unless the database is read only then there is no guarantee for that.  Obviously, that's within reason.  If you for some reason have 1 TB free space on a 10 GB database, then that may warrant a shrink, unless it's expected to regrow at a quick rate.

The most important aspect about database file shrinking is to only do it when it is a hard requirement and do not do it routinely.


Limiting query to certain amount of rows evenly distributed Given a table:

id
--
1
2
4
6
8
10

I would like to be able to specify a limit and have that amount of rows returned evenly distributed across the entire set when it is ordered by a specific criteria. For example, 3 rows when the set is ordered by id would give me:

id
--
1
4
10

In the above scenario, either 4 or 6 could have been returned as the middle row. I am not specifically bothered as to which way the selection is rounded.

Ideally I'd like to do this in the most efficient way i.e. the entire table isn't parsed initially.

I found an excellent post here Select every n-th row, don't pull the entire table, regarding evenly distributed data sets but struggled when trying to calculate n in order to limit the final data set to a specific amount.

I'm currently running MySQL 5.6 on Ubuntu.

Ids are non sequential.
This database is dynamic so I can't hardcode the amount of rows or have select statements that reflect errors in a particular set of results.

You have a dynamic number of rows in the table, and want a specific number of evenly distributed results each time?

I'm asking because the solution you linked is already nearly there:

   SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, [column name]
    FROM (
        SELECT @row :=0) r, [table name]
    ) ranked
WHERE rownum % [n] = 1

Just replace [n] with the following:

(CEIL(@row/[m]))

where [m] is the number of results you want to have returned.


In memory table vs temporary table - benefits if temdb in already on ram disk In the docs, we have example of how temporary tables can be replaced with in-memory tables and because a memory-optimized table:

is stored only in memory, and has no component on disk
involves no IO activity
involves no tempdb utilization or contention

they said that:

  Memory-optimization results in speed increases that are often 10 times
  faster or more.

I am wondering if our system is already using ram for tempdb database, which of the benefits will still apply? I guess they are testing temdb on hard drive or ssd and doubt getting such great results.

More details. One of the system mostly used table variable function is the one calculating access to specific entity. Then, the result is joined to various table to get the request data. In order to optimize the latter joins, I am storing the result of this function in temporary table and the results are nice.

But for really large volumes (for example when the function returns 1-2 millions of entities) the execution of the function itself is slow (which is again normal since we are inserting so many rows in table variable).

So, I am thinking to rewrite this function as stored procedure and insert the entities in memory table, hoping that the CRUD operation with the table will 10 times faster or more.

In a comment from Ned Otter:

  If the bottleneck was due to locking/blocking on system tables in tembdb, then I would imagine that placing it in RAM would not make much difference.

If your current workload is getting slowed down by contention over internal components, then putting tempdb on a ramdisk won't help you.  This is because the problem is not I/O throughput related.  Check out this detailed post from Paul Randal on the subject:

The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention

  Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

Ned continues with:

  That's why SQL 2019 supports a memory-optimized version of tempdb.

This is a new feature in 2019 that's designed to help with these types of contention issues.  You can see a demo of how this helps, and how it's not quite ready in the current preview of SQL Server 2019, on Erik Darling's blog:

SQL Server 2019 In Memory tempdb

You really need to measure whether the slowdown is due to I/O, or due to contention.  Use Paul Randal's post above to dig into that.

Since you updated your question to mention that the slow cases are when you insert millions of rows into a table variable, I'll assume the above-mentioned contention isn't likely you're problem (unless you're creating these table variables from many different connections at a time).  In which case, the ramdisk approach could certainly help.

In your last update to the question, you said:

  It seems that parallel plan is not supported for in-memory plan table (at least on insert), so I failed to optimize anything

This is a well-known "issue" with in-memory OLTP.  Rather than using built-in parallelism, you need to "roll your own" by loading data into the in-memory tables from multiple connections.


Disabling password change for a MySQL user Is it possible for MySQL users to prevent/disable changing their password with just the USAGE option? There are no global permissions allowed.
I tried to google, but had no luck.

One way to achieve this could be by using pluggable authentication. This way the password is stored externally and therefore can't be changed from within mysql. This allows you to use PAM, LDAP or other authentication services.

For PAM, assuming you have configured PAM already in your OS (assuming Linux below), then in MySQL do:

INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';
CREATE USER bob@'%' IDENTIFIED WITH authentication_pam AS 'mysql';

... where AS 'mysql' refers to a file /etc/pam.d/mysql.

If the user then tries to set their password (I've tested this only on MariaDB with the unix_socket plugin):

SET PASSWORD = PASSWORD('My_very_clever_password');

... that query will "work", however it gives a warning:

  SET PASSWORD has no significance for users authenticating via plugins


Nested one to many relationships? I'm working on a "Bus stations" database. I want to design the next relationship:
"A bus line has many stops (one stop belongs to one line) and one stop has many arrival times (one arrival time belongs to one stop)".

So my design is like this:

Lines (1,1)<->(1,N) Stops (1,1)<->(1,N) Times

Lines(id, name, ...)
Stops(id, line_id, name, ...)
Times(id, stop_id, ...)

But when I was testing it, I realized that I needed a FOREIGN KEY line_id in Times table (because I could not distinguish between times of different bus lines). This transforms the diagram:

Lines (1,1)<->(1,N) Stops (1,1)<->(1,N) Times
  |                                       |
  |                                       |
   ---------------------------------------
  (1,1)                                  (1,N)

I'm a little bit confused because I think this design has redundancy: the relationship between Lines and Times could not be reached by the intermediate relationships?

You should not need the extra FK in Times as that information can be derived from the existing relationships, but you will need to JOIN in the other tables to get at the extra property. Adding the extra key like that is sometimes a necessary optimisation but it does break "normal form" as you are duplicating data (meaning that either your business layer becomes responsible for maintaining the referential integrity of that duplicate, or you need to do the same in the database using triggers and other "powerful but be very careful with them" features of your chose database).

Your select * from times where stop_id = 1 and line_id = 1 should be something like:

SELECT times.id, times.stopid, ...
FROM   times
JOIN   stops ON times.stop_id=stops.id
WHERE  times.stop_id=1 AND stops.line_id=1

To simplify queries you can create views that abstract out the underlying structure a little, meaning you can keep the data in best form while dealing with it as it it did have the extra columns with duplicated information.

I'm not sure I'd model a timetable store that way though. I would assume that stops are separate entities with a many-to-many link between lines (unless you are counting a stop at which the no. 4, 5, and 45x lines stop at as three separate stops even though they are physically the same location). Also I'd probably want to group the lines together so that all the times for the "no 4" service are identifiable as a related collection but you can distinguish between the arrival times of each (so you can ask "what is the arrival time at X for the service that leaves Y at HH:MM" and so forth. Of course I could just be misreading your intended model! I'm thinking something like:

                                       TimedStop
                   TimedRoute          =============
Line               =============       ts_id (PK)         Stop
============       route_id (PK) ===>  route_id (FK)      =============
line_id (PK) ===>  line_id (FK)        stop_id (FK)  <=== stop_id (PK)
line_name                              arrival_time       stop_name
                                       depart_time        stop_location

Here TimedStop becomes a many-to-many relationship store for timed routes and stops. Of course there are probably several perfectly valid ways to model such a system depending on your other constraints, and the above may be rendered an incorrect model when you consider the fuller design spec (without more detail of what you are trying to model and what outputs are desired this is not possible to completely pin down, I made a fair few assumptions, which may or may not be correct, when throwing the above diagram together).

As another aside on a point of style: select * is generally best avoided in permanent code where possible. If you are instead specific about what you want out this because part of your API and other code using this output has more guarantees of what columns will be returned, even if the underlying structures are updated. It can also allow the query planner/runner to apply extra optimisations (potentially avoiding extra heap lookups or being able to replace a table scan with an index scan, and so forth).


Mysql Select table records which have not been updated since last month I have a table names 'scoredata' having fields
id, lessonid, levelid, studentid, addedate
in which score for each day will be entered by the admin.
I want to select only those studentid whose score has not been
added from last four weeks. How can I do this in a single query.

SELECT studentid FROM
(
    SELECT MAX(addedate) lastdate,studentid
    FROM scoredata GROUP BY studentid
) A
WHERE lastdate < ( DATE(NOW()) - INTERVAL 4 WEEK );

Query Breakdown

The subquery gets the last date each student entered a score
The WHERE clause outside the subquery checks to all lastdate values older than 4 weeks

Make sure you index studentid,addedate. If you didn't then run

ALTER TABLE scoredata ADD INDEX studentid_addedate_ndx (studentid,addedate);

Give it a Try !!!


Rebinding a SQL Server user to its login after a restore with this user After performing a restore of a SQL Server (v12.0.2000) database, I figured that a user needs to be remapped to its login (this issue seems to be known as orphan users). And one of the simplest way to do it is to perform a :

use [MyDatabase];
alter user MyUser with login = MyUser;

Nevertheless, just after achieving a restore, the USE command fails because I'm trying to execute the above command with 'MyUser', which is the one that is orphaned...

Because it is in a script, I would like to avoid performing a restore with one user and a alter user with another user, in order the first user be correctly remapped to its login.

Is there a way to do this with 'MyUser' (who could connect to master without problem) ?

I tried it by fully qualifying 'MyUser' the following way without success :

use master;
alter user MyDatabase.MyUser with login = MyUser;

Any idea if this is possible and how to do it ?

Unfortunately no.  Remember that a database principal and a server principal are two separate objects.  Until you fix the orphan the server principal MyUser has no access to MyDatabase even though there is an orphaned user called MyUser in the database.

The other way to fix an orphan is to change the SID of the server principal to match that of the database principal.  This does have a risk of creating orphans in other databases and even more importantly in this case to do it you have to drop the principal and re-create it with the new SID.  Which of course isn't possible using that particular ID.

Your only method is going to be to fix this using a separate id with the appropriate permissions.  That could be an id with sysadmin permissions (which would have permissions to connect to the database even without an associated database principal) or an id that has DBO access on the database.  Note those are examples, there are several other permissions available that will allow you to modify server/database principals.


Why can't I use a CASE statement to see if a column exists and not SELECT from it? Why does something like this not work?

SELECT
CASE
WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL
ELSE Somecol
END AS MyTest
FROM Customers;

I am just checking if the column exists, however, SQL Server complains about Somecol not existing.  Is there an alternative to this in a single statement?

The following query uses the same idea as in this amazing answer by ypercube:

SELECT x.*
FROM (SELECT NULL AS SomeCol) AS dummy
CROSS APPLY
(
  SELECT
    ID,
    SomeCol AS MyTest
  FROM dbo.Customers
) AS x;

It works like this:

if dbo.Customers has a column named SomeCol, then SomeCol in SomeCol AS MyTest will resolve as dbo.Customers.SomeCol;
if the table has no such column, the reference will still be valid, because now it will be resolved as dummy.SomeCol: dummy columns can be referenced in that context.

You can specify multiple "spare" columns that way. The trick is not to use the table alias for such columns (which is a frowned-upon practice in most situations, but in this case omitting the table alias helps you to resolve the issue).

If the table is used in a join and the other table has its own SomeCol, you will probably need to use the above query as a derived table before using it in the join in order to keep the trick working, something like this:

SELECT ...
FROM
(
  SELECT x.*
  FROM (SELECT NULL AS SomeCol) AS dummy
  CROSS APPLY (
    SELECT
      ID,
      SomeCol AS MyTest
    FROM dbo.Customers
  ) AS x
) AS cust
INNER JOIN ...
;


PostgreSQL memory spike upgrade from 8.2 to 9.1 We recently upgraded a few of our development computers from PostgreSQL 8.2 to 9.1, with plans on eventually upgrading our production servers.  Everything seems well with the restore of the databases, and the local websites on the dev computers. What isn't working are some reporting ETL queries.

A particular query runs in under 30 seconds on 8.2.  The core query (we've broken it out) is simply a SELECT statement with 15 joins.  For some reason, when this query is run on 9.1, PostgreSQL spikes in memory usage, acquiring all the memory available on the machine, which is 2.8 GB on my machine. Consequently, the query does not finish in any reasonable amount of time (up to 17 mins compared to 30 secs, haven't let it run forever because I need to be able to use my computer).

What we have done so far:

Replicated config settings for 8.2 to 9.1
Ran EXPLAIN on the query on both 8.2 and 9.1: should run faster on
9.1 Explain 8.2 on explain.depesz.com (Raw data: explain_8.2.dat)Explain 9.1 on explain.depesz.com (Raw data: explain_9.1.dat)
Broken the query down by removing joins. When below any 10 joins,
the query will complete
Examined the joining tables for irregularities
Tested the query on different machines running 9.1
Reviewed Postgres config documentation and changelog
Ran VACUUM and ANALYZE on the db
Rebooted the machine
Updated kernel SHMMAX when postgres wouldn't start after reboot
Ran the query on CentOS box and it exhibited the same behavior
Ran the query on 8.4 Successfully

In my opinion, it seems fairly abnormal for PostgreSQL to use so much memory, and is alerting to the problem.  We just can't find it!

Any comments, thoughts, and ideas are appreciated!

Machine Info:

Ubuntu 11.04 64 bit
Intel i5-2400
4 GB ram
PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit

In a case like this where nothing significant has changed I think you are best off bringing this up on the PostgreSQL email lists.  Not only will you get help with your issue in terms of workarounds but if there is a planner bug somewhere the development team will be given notice so they can fix it.

This may be one of those cases where the level of help you can expect there is far better than you can expect here.


Azure SQL Databases hit cpu limits too quickly Background in a nutshell: -
We have a SAAS solution with the following main components.
1. We have a web-portal back-end where administrators can edit data.
2. We have a web API that is called by mobile devices. The mobile devices track or report on students reading progress

Up to now the solution was hosted on virtual servers.
Now we are migrating the solution to the Azure framework so that we can take advantage of the scalability of elastic database pools.
We are using event topics to handle large volumes of posts from the mobile devices when the posts can be processed asynchronously,
but there are some posts that need to be processed synchronously, and we are finding Azure's fabric really slow when it comes to multiple concurrent connections.

An example of the issue: -
So when Azure runs a query like the following: -

SELECT q.Category, COUNT(*)
FROM Question q
JOIN Answer a
ON a.QuestionId = q.QuestionId
GROUP BY q.Category
ORDER BY q.Category

The SQL CPU peaks above 97% in all the following scenarios: -
1. The DTU's are 50 and there is more than one concurrent call.
2. The DTU's are 1500 and there are 5 or more concurrent calls.
3. The DTU's are 4000 and there are 20 or more concurrent calls.

So we opened a support call with Microsoft.
We spent more than a week of investigating things from sql statistics and indexes up to web api pricing tiers.
After all that we still came up with the evidence that the CPU was peaking in the SQL database with the scenarios as outlined above.

This leads to the inevitable "re-write large chunks of your system" kind of argument.

So the underlying issue is that elastic database pools can't seem to perform anywhere near the ability of standard SQL databases.
Also, the stand-alone database's performance doesn't seem to compete with the virtual server's performance.

This is so frustrating because Elastic databases pools were recommended for us for reasons of maintaining performance and adding scalability.
We currently run 700+ customers on one virtual server; and were expecting to create one shard database per customer.
The idea being that we could then scale up from hundreds of customers to tens of thousands of customers.
In reality we are fighting to get the Azure fabric to perform anywhere near the kind of performance we have on virtual servers.
So this question is to ask if there is anyone with significant experience in making Azure perform non-trivial tasks at a reasonable pace?
(preferably without having to re-write large chunks of the system)

For questions like these you need to include the query plan and relevant table structure (what keys and indexes are in place) to get a good answer that is specifically relevant. I recommend that you update the question with that detail.

The first thing that springs to mind is the JOIN to Answer - is there a useful index for the QuestionId column that you are joining on? A common error is to assume that defining a foreign key constraint also defines an index (much like defining a unique constraint does) but this is not the case. Most RDBMSs don't because there are occasions where the index is not needed at all so creating one implicitly would be wasteful of space and unnecessarily add I/O to insert & update operations.

If there is no useful index on Answer.QuestionId then that table will be subject to a scan (and probably a sort too) which if the table is small enough to fit in memory will generally be a CPU-bound operation.

If you are seeing this a lot in your DB with the same pattern of query then it may be that your DB devs are working under the assumption that adding a foreign key also defines an index, possibly because they have a mySQL background as IIRC that does create an index implicitly when an FK is defined.


What happened to pg_xlog? There uesd to be a directory called pg_xlog that stored all of the WAL logs in PostgreSQL? Part of restoring a basebackup under the archiving scheme required me to copy the WAL into DATA_DIR/pg_xlog. What happened to this directory?

PostgreSQL 10+

In PostgreSQL 10 the WAL directory was renamed from pg_xlog to the easier to remember pg_wal.

Pg 10: WAL Internals (you can click the top for 9.6 to see the older version before the docs were updated to reflect the change).
New in postgres 10: Renaming of "xlog" to "wal" Globally (and location/lsn) describes the change in more details.


Distributed Availability Group Direct Seeding FAILED, failure_state SQL Error, failure_state 2 We just started setting up Distributed Availability groups to replicate our production databases into a new reporting cluster. The first availability group that we setup for replication worked great without any issues, however when we then moved on to the second availability group with much larger databases (over 3TB total) it took much longer and two of the 5 databases failed. We setup the distributed availability group to use direct seeding and when querying the sys.dm_hadr_automatic_seeding table it indicates the current_state as FAILED, with failure_state 2 (SQL Error) or 21 (Seeding Check Message Timeout):

What can we do to troubleshoot this issue?

The AlwaysOn Professional blog has some general troubleshooting steps for direct seeding and also includes some details about trace flag 9567 to enable compression during seeding, but I didn't find any details about the SQL Error or Seeding Timeout.

We previously have had issues with large databases causing problems in availability groups, but this usually is resolved by applying the latest transaction logs from the primary against the replica.

In this case the databases were listed on the secondary availability group as recovering, so I tried applying the latest transaction log backups from the primary and then joining the database to the secondary availability group:

--Restore transaction logs from primary and stay in recovery mode. Multiple backup files may need to be restored from oldest to newest.
RESTORE LOG stackoverflow from disk = '\\Backups\SQL\_Trans\StackOverflow_AG\StackOverflow\StackOverflow_LOG_20170810_175400.trn' WITH NORECOVERY;

ALTER DATABASE stackoverflow SET HADR AVAILABILITY GROUP = [StackOverflow_RAG];
ALTER DATABASE stackoverflow SET HADR RESUME;

This worked for both of the failed databases and fixed the replication issues. Our reporting cluster now has all databases kept in sync from the primary availability group:


PostgreSQL randomising combinations with LATERAL In the following example I have a table foo from which I'd like to pick out at random a row per group.

CREATE TABLE foo (
  line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);

Let's say that I'd like to group by line % 10. I could do this with:

SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 AS bin, random() x
    FROM foo
    ORDER BY x
) X

What I'd like to do is get random picks from each bin several times. I had thought I'd be able to do this with generate_series() and LATERAL

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, random() x
    FROM foo
    ORDER BY x
) X
ORDER BY bin) Q
ORDER BY bin, i;

However, when I do this in PostgreSQL 9.5 I find I get the same line for a given bin for each iteration i, e.g.,

i;line;bin
1;530;0
2;530;0
3;530;0
1;611;1
2;611;1
3;611;1
...

I'm confused, as I thought the subquery containing the random() would be run differently for each line from the generate_series().

EDIT: I realised that I can achieve the same objective by generating more combinations and choosing from these with

SELECT DISTINCT ON (bin, round) round, bin, line
FROM (
    SELECT line, line % 10 as bin, round
    FROM foo, generate_series(1,3) round
    ORDER BY bin, random()
) X;

So my question is simply why didn't the first way work?

EDIT: The problem appears to be that LATERAL only acts like a for-loop if the subqueries are correlated in some way (thanks to @ypercube's comment). Hence my original approach can be fixed by adding the following small change

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(
SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, m.i, random() x -- <NOTE m.i HERE
    FROM foo
    ORDER BY x
) X
ORDER BY bin
LIMIT 3
) Q
ORDER BY bin, i;

I'd write the query like this, using LIMIT (3) instead of DISTINCT ON.

The generate_series(0, 9) is used to get all the distinct bins. You could use (SELECT DISTINCT line % 10 FROM foo) AS g (bin) instead, if the "bins" are not all the integers from 0 up to 9:

SELECT
    g.bin,
    ROW_NUMBER() OVER (PARTITION BY g.bin ORDER BY d.x) AS i,
    d.*
FROM
    generate_series(0, 9) AS g (bin),
  LATERAL
    ( SELECT f.*, random() x
      FROM foo AS f
      WHERE  f.line % 10 = g.bin
      ORDER BY x
      LIMIT 3
    ) AS d
ORDER BY
    bin, x ;

Also, if you don't need the random() number in the output, you could use ORDER BY random() in the subquery and remove x from the select and order by clauses - or replace ORDER BY d.x with ORDER BY d.line.


Two MySQL Server accessing same database over NFS I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following:

one MySQL instance will be a producer
one MySQL will be read only consumer

On the MySQL site, it is mentioned this possible with MyISAM and merge storage engine only, but they never recommend it.

http://dev.mysql.com/doc/refman/5.6/en/multiple-data-directories.html

I just want to check if it is technically possible or not,
  recommendation is other story. specially my application assured
  write/read will not conflict

As @RolandoMySQLDBA said, this is not possible with InnoDB or MyISAM.

Stated more simply: some of the pages of data you modify are not written to disk immediately. This is done in the background, and not all at once. So if your second read-only instance tries to read data through NFS from the same data directory, it is certain to read changes that have been partially written to disk. Basically, garbage.

MyISAM doesn't have a buffer pool, but it does make use of the filesystem as a write-back cache, so you never know whether all your changes have reached the disk or not. In this case, you'll also get an unpredictable mish-mash of data if you try to read the files through NFS from another host.

But why do you want to do this in the first place? Is it to gain greater capacity from running a large number of queries per second? You can run more than one instance of MySQL with data that is kept more-or-less in sync, by using replication. That's a more common practice, and it works well.

Is your reason to have a failover capability? In that case, you might use DRBD for block-level replication. And there are quite a few other failover solutions besides that.


Apply the latest Service Pack and Cumulative Update I have given an RTM version of SQL Server 2014 and I need to apply the latest SP (currently SP2) and CU (currently CU8). Do I need to apply SP1 first? Or can I straight move to SQL Server 2014 SP2 CU8 without applying the old Service Pack 1 and the related Cumulative updates?

When patching SQL Server:

Apply the most recent Service Pack, then
Apply the most recent Cumulative Update for that Service Pack.

You never have to go through Service Packs or Cumulative Updates in order. Just use the most recent of each, in the order above.


Revert to old plan in Oracle A weekly, scheduled, schema wide stats update was run on our Oracle 11R2 RAC cluster on Sunday.  One of the queries had no apparent data or code change but now runs at 3 hours instead of 20 minutes.  Looking at dba_hist_sqlstat , its running the same SQL as before, but with a different plan.  The stats seem to have negatively affected it.  Because its a large query that touches a few dozen tables, they already decided that restoring the stats on the affected tables isn't an option.

I've got the old (better) and new (terrible) plans by executing:

select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('sqlid'));

But they're so different that its not a minor manual tweak to get them back.

Is there a way to force it back to the old plan, given that the database still has much of that information available to it?
(And we don't have the Tuning Pack.)

You need to use SQL baselines to force the execution plan.

This blog describes the steps involved. You'll need the sql_id for the statement and the plan_hash_value of the old plan.


How to make table with row name and column name I am new to databases and now i came to a point where i need more complex table.
I have a table which contains site details. Each site can have multiple devices. Each device have multiple measurement tables.

Structure looks like this:

                    DEVICE LIST TABLE 1 ► MEASUREMENT TABLE (each device)
SITE LIST TABLE ►   DEVICE LIST TABLE 2 ► MEASUREMENT TABLE (each device)
                    DEVICE LIST TABLE 3 ► MEASUREMENT TABLE (each device)

Measurement table looks like this

+----+----+----+----+----+
|    | F1 | t1 | F2 | t2 |
+----+----+----+----+----+
| A1 |    |    |    |    |
+----+----+----+----+----+
| A2 |    |    |    |    |
+----+----+----+----+----+
| A3 |    |    |    |    |
+----+----+----+----+----+
| A4 |    |    |    |    |
+----+----+----+----+----+
| A5 |    |    |    |    |
+----+----+----+----+----+
| A6 |    |    |    |    |
+----+----+----+----+----+

I do not understand how to make table like this. If not possible what is the best practice to link every ROW with device.

Or should i create dynamically measrements A1 to An?

+-----------+---------+----+----+----+----+
| device_id | name    | F1 | t1 | F2 | t2 |
+-----------+---------+----+----+----+----+
|           | A1...An |    |    |    |    |
+-----------+---------+----+----+----+----+

And then do

SELECT * FROM measurement_table WHERE device_id = x

You should revise your table structure.
You are trying to put too many different things into one table.

What you could do is have multiple tables like so:

Table: site
Has an ID and all other details about the site!
Table: Devices
Has an ID and all other details about the device.
Table: measurements
Has an ID and all other details about the measurement.

Now depending on if your measurements are linked to a single device or not you may need an intersection table or a device ID on the measurement,  and the same goes for the site.

When you wish to select all the details about a site,  you will need to use joins to get everything,  or maybe multiple queries.


Why does "DB2 Recovery Expert" web console doesn't enable the Run and Next button? Sometimes, when performing the Log Analysis and Recovery options in the Recovery Expert web console, the buttons "Next" and/or "Run" aren't enabled.
This is strange because we do provide all the optins requested to proceed with the operation. I am using DB2 R.E. 5.1, so I'm not sure about other versions have the same issue.

The reason could be that the tool identified that there's some information missing. Still in my case everything was there. So, I've found that the solution for my issue was just because, even if you don't have an alternative path for logs or backups, you still have to specify them when running these operations.

Sometimes even doing so they might not enable instantly. You must go "Previous" and then "Next" to make the web console notice the changes.


IBM DB2 Data Studio Web Console Installer I would really like to test the Web Console for Data Studio to see if it will do what we need.  However, I absolutely cannot find an installer/download for it.  Can anyone help?

It's part of the full Data Studio installation image. However, Web Console is deprecated. Use Data Server Manager instead.


What is the SQL Server cost, in terms of performance, of sharing physical disks? I am an application user in Health Care.  The application relies on communication with a large database, including image data and other large numeric arrays.  We add approximately 15GB per day to the database server (note image data might be file based).  The application crashes, sometimes coincident in time with querying the database e.g. when you query the patient list in opening a new case.

There are two instances of the application database, used by two groups, that currently share a physical disk in the database server.  I wonder what factors I'd need to understand in order to estimate whether or not there might be appreciable performance gain by giving each instance of the database a dedicated physical disk.

A performance factor which we saw in one of our environment was related to  high disk read write activity from one database on those shared disks. Since one had a higher transaction activity using the storage hitting hard caused slownesss for other. Also it becomes hard to troubleshoot when you have shared instance for those disks. We moved them to their respective virtual servers instead.

Also other factors which made us move was difficulty in performing any reboot activity after patching as one lob would be fine while other conflicting.

Moreover backup of databases in one instance at x timings may impact performance of other sql instance depending upon how big the servers we are talking and how good or bad the storage is.


Increase logging/tracing on Oracle 12c database I need to connect TLS-secured-ly to an Oracle 12c (from some random Java program, using JDBC connection)

I set up my listener to use TCPS, I made my wallet and everything and still my program is failing with a handshake failure.

Tracing it on client side is not helfpul.

How can I increase the logging on Oracle side (and where to find extra logs) to debug this?

You need to enable listener logging.

In your listener.ora, set:

LOG_DIRECTORY_listener=/path/to/your/oracle/home/network/admin/log
LOG_FILE_listener=listener.log
TRACE_DIRECTORY_listener=/path/to/your/oracle/home/network/admin/trace
TRACE_FILE_listener=listener.trc
TRACE_LEVEL_listener=10

... make sure you substitute your listener name for listener above.

You can also use ADR parameters instead - see the documentation.


Do queries called from within a transaction but found within another method act the same? Imagine the following code:

class foo {

    public function bar() {

        // Start the transaction
        $transaction = $this->db->beginTransaction();

        try {

            // Get our member data
            $member_data = $this->getMemberData();

            // Update users details
            $sql = $this->db->createCommand("UPDATE users SET......");
            $sql->execute();

            // Now let's commit our data
            $transaction->commit();

        } catch (\Exception $e) {

            // Let's rollback the transaction
            $transaction->rollBack();

        }

    }

    public function getMemberData() {

        $sql = $this->db->createCommand("SELECT * FROM users WHERE id=123 LIMIT 1 FOR UPDATE");
        $member_data = $sql->queryOne();

        return $member_data;

    }

}

Now, does the query within the getMemberData method act as part of the transaction since it is being called within the transaction and it doesn't matter to the database how it is being called since we have a transaction open or am I required to move the query inside the transaction code block?

MySQL does not care about your PHP code structure. One transaction is what is between start and commit/rollback, sent over one connection - thats important, if your $this->db->createCommand()->execute/query creates a separate connection for some reason then it won't work but that should not be the usual case, just mentioning it because it sometimes happen so better to check.

You can have multiple transaction "open" using multiple connections but then you can deadlock yourself so beware (MySQL can automatically resolve deadlocks on the DB layer but not if you lock a record from one connection and then try to access it from some other, because your app is single-threaded (probably) and the deadlock detection expects parallel clients.


PostgreSQL Full Text Search (FTS), what is the difference between ::tsvector and to_tsvector()? i have a table with tsvector type column (col).

I fill some rows via script and use cast data::tsvector, but (!) some rows was filled with to_tsvector(data), so i have rows with different values like this:

'в' 'г' 'зея' 'иеговы' 'местная' 'организация' 'религиозная' 'свидетелей'
'г':7 'зе':8 'иегов':5 'местн':1 'организац':3 'религиозн':2 'свидетел':4

It was surprise for me :) So what the overhead for values not prepaired with to_tsvector(data) ? Will pg do to_tsvector on runtime or what?

The difference between the string->tsvector cast and the function-constructor to_tsvector(text)

the cast (::) assumes the input is already a tsvector that's been casted to text. It does not use the language-specific stubbing mechanisms or store positional information, it simply assumes it is given a space-delimited text-search tokens (positions and lexemes) and assumes they've already undergone the proceess.
to_tsvector() takes string input and does all the requisite work to tokenize it, including normalizing and stubbing.

You should probably avoid the cast for everything but text representations of tsvector.

Examples

Here we read the string 'test testing tested' as three tsvector lexemes with undefined positions, notice the output lacks stubbing

test=# select 'test testing tested'::tsvector;
         tsvector
---------------------------
 'test' 'tested' 'testing'
(1 row)

Here we "convert" a string of words to lexemes with positions by sending the string through the process specified in your specific TEXT SEARCH CONFIGURATION, in my case for english.

test=# SELECT to_tsvector('test testing tested');
 to_tsvector
--------------
 'test':1,2,3
(1 row)

Here we explicitly tell PostgreSQL to use the simple text search configuration.

test=# SELECT to_tsvector('simple', 'test testing tested');
           to_tsvector
---------------------------------
 'test':1 'tested':3 'testing':2
(1 row)

So what is the cast used for? You could however use it for something like this

SELECT $$'test':1 'tested':3 'testing':2$$::tsvector @@ 'testing';

Here is another case demonstrating what it does.. (returns true)

SELECT to_tsvector(test) = to_tsvector(test)::text::tsvector
FROM ( VALUES
  ( 'This is my test. Hello from testing test tested' )
) AS t(test);

If you're getting the tsvector over the wire, or serializing it as text in the case of pg_dump, that can be useful.