SQL Server 2012 MSG 4145 I'm trying to upgrade one of our applications and we have to create the database again due to the app now requiring case sensitive collation.  I've created the new DB and run the table creation scripts, but now they give us a SQL to run to compare the table configs between the old and new DB.  I keep getting the error below from using that SQL. I can't figure out if I'm missing something or if there's just a problem with the way I'm running it (SQL Server Management Studio). Can anyone explain what I'm missing?

SELECT
src.TABLE_NAME,
src.COLUMN_NAME,
src.DATA_TYPE,
src.CHARACTER_MAXIMUM_LENGTH,
dst.TABLE_NAME,
dst.COLUMN_NAME,
dst.DATA_TYPE,
dst.CHARACTER_MAXIMUM_LENGTH
FROM originalDB.INFORMATION_SCHEMA.COLUMNS src
INNER JOIN newDB.INFORMATION_SCHEMA.COLUMNS dst
ON dst.TABLE_NAME COLLATE DATABASE_DEFAULT = src.TABLE_NAME COLLATE DATABASE_DEFAULT
AND dst.COLUMN_NAME COLLATE DATABASE_DEFAULT = src.COLUMN_NAME COLLATE DATABASE_DEFAULT
WHERE dst.CHARACTER_MAXIMUM_LENGTH &lt; src.CHARACTER_MAXIMUM_LENGTH
OR dst.DATA_TYPE COLLATE DATABASE_DEFAULT &lt;&gt; src.DATA_TYPE COLLATE DATABASE_DEFAULT;

Msg 4145, Level 15, State 1, Line 14
An expression of non-boolean type specified in a context where a condition is expected, near ';'.

Try this query where I've replaced the &lt; for < and &gt; for >. I hope it will help.

SELECT
  src.TABLE_NAME,
  src.COLUMN_NAME,
  src.DATA_TYPE,
  src.CHARACTER_MAXIMUM_LENGTH,
  dst.TABLE_NAME,
  dst.COLUMN_NAME,
  dst.DATA_TYPE,
  dst.CHARACTER_MAXIMUM_LENGTH
FROM originalDB.INFORMATION_SCHEMA.COLUMNS src
  INNER JOIN newDB.INFORMATION_SCHEMA.COLUMNS dst
    ON dst.TABLE_NAME COLLATE DATABASE_DEFAULT = src.TABLE_NAME COLLATE DATABASE_DEFAULT
    AND dst.COLUMN_NAME COLLATE DATABASE_DEFAULT = src.COLUMN_NAME COLLATE DATABASE_DEFAULT
WHERE
  dst.CHARACTER_MAXIMUM_LENGTH < src.CHARACTER_MAXIMUM_LENGTH
  OR
  dst.DATA_TYPE COLLATE DATABASE_DEFAULT <> src.DATA_TYPE COLLATE DATABASE_DEFAULT;


Is it ok to have 40+ columns in a table I am currently re-designing some of DB's for a project. There is one DB in particular that is giving me a little of a headache. The idea is that for every client_id and date (client_id + date would be the Pri. Key) each column would contain the usage for each product that we offer (around 12 products, and for each one Cost, Margin, Total needs to be stored). I want to do domething like this:

+-----------+------------+--------------+-------------+-------------+------+--------------+
| client_id |    date    | prod_1_Total | prod_1_Cost | prod_1_Paid | .... | prod_14_Paid |
+-----------+------------+--------------+-------------+-------------+------+--------------+
| aa1       | 2014-01-01 | 245          | 54.97       | 50.58       | ...  | 431.23       |
| ...       | ...        | ...          | ...         | ...         | ...  | ...          |
+-----------+------------+--------------+-------------+-------------+------+--------------+

We have to note that for a record to exist that client_id has to have had some activity in the given date, in other words, given a client_id and a date, the rest of the columns can't be all NULL, otherwise that record won't be on the table.

It seems to me a bad practice to merge all this products in one table like above, specially, when for a given client_id and date many of them will be NULL.

But the alternative that occurs to me, creating separate tables for each of the products, presents the problem that to calculate for example total_paid for all producst for a client_id across some dates, we have to do 14 left joins, moreover if we use lets say product_1 in the query as the main query and we left join against it the result would be incorrect since most-likely the for some days the client used product_x and not product_1 (Full-outer joins are not supported in MySQL)

What design strategy would you recommend?

Thanks for your suggestions.

Considering that MySQL is still a relation database management system an (RDBMS if you will), you should considering the following things:

Your tables are defined by the data they contain and the relationships to other tables
Your data should be as non-redundant as possible

This last point leads to the process of normalization, which is a very important thing to do in a RDBMS.

Have a look at the Wikipedia article on Normalization to have a basic understanding of what that process entails: http://en.wikipedia.org/wiki/Database_normalization

I find that, oftentimes, it can be a good idea to take a step back from the "storage" aspect of your database. Try to look at your application, the data models used within and their relationships. Creating such an abstraction is called an ERM: Entity-Relationship Model, or an ERD: an Entity-Relationship Diagram.
More info on that: http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model

For your case specificially:

Apart from the fact that your not using a design that fits a relational database, please consider the fact that you are creating a very inflexible design!
What if -- for example -- you were to create a new product in the future? Would you add a new column to the table? Surely you'll agree that that's pretty stupid.

Instead, it makes MUCH more sense to create something along the lines of

+-----------+-------------+----------------+
| client_id | client_name | client_address |
+-----------+-------------+----------------+
| 1         | Snake Logan | ...            |
| 2         | Mad Max     | ...            |
| ...       | ...         | ...            |
+-----------+---------- --+----------------+

+------------+---------------+---------------+
| product_id | product_desc  | product_price |
+------------+---------------+---------------+
| 1          | Silver Bullet | 14.00         |
| 2          | Hand grenade  | 7.00          |
| ...        | ...           | ...           |
+------------+---------------+---------------+

+-------------+-----------+------------+-----------------+
| purchase_id | client_id | product_id | purchase_amount |
+-------------+-----------+------------+-----------------+
| 1           | 1         | 1          | 3               |
| 2           | 1         | 2          | 1               |
| 3           | 2         | 2          | 5               |
+-------------+-----------+------------+-----------------+

I'm not saying this is the greatest design ever, but this is more elegant.

If you want to have more info concerning the purchase, I'd recommand building a view that contains the necessary info by joing the tables together. Please don't create a field like purchase_total, I think that would once again be redundant data. The data is already there (purchase_amount and product_id), you should need to do the math (purchase_amount * product_price). You can put that in the view if you want. Tables are for storing data, views are there to present said data.


Assign Permissions to a SQL User I have active directory on my company, I have some login users created at the level of the SQL Server (2014) that I need to propagate to all my databases on that server. I want to create a read only user and also a read write user on sql server. So I created one user USER_R and another USER_RW, that match with the domain users, on my USER_R, I want to assign db_datareader and db_denydatawriter and then select all my dbs on the User Mapping section and click OK, this will create USER_R in all my dbs.

Question 1, how to automatically manage this in case I restore a new db on that server and I want to assign the user USER_R?

Question 2, now that I have the user USER_R, a user A want to connect to management studio, he doesn't see any table or stored procedure, what's missing to let them see all objects and besides be read only?

Question 1, how to automatically manage this in case I restore a new
  db on that server and I want to assign the user USER_R?

As described here you could add the user to the model database to automatically be
added to other, created databases.

However, this will not work for restored databases.

USE [model]
GO
CREATE USER [testlogin] FOR LOGIN [testlogin]
GO
USE [model]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testlogin]
GO

Restored databases are hard to track, more information on why life is hard in this article by Erik Darling.

Unfortunately, triggers won't directly work on restored databases.

If you where on SQL Server 2016, you could try and cook something up with an extended event on backup_restore_progress_trace, but that does not help you here.

In this case I would just add the user creation to your restore process.

Either with dynamic SQL if you have to

DECLARE @SQL nvarchar(max)
  SET @SQL =
   '
        USE ' + QUOTENAME(@RestoredDatabaseName) + '

IF NOT EXISTS (
SELECT * FROM sys.sysusers where name = ''testlogin'')
BEGIN
CREATE USER [testlogin] FOR LOGIN [testlogin];
ALTER ROLE [db_datareader] ADD MEMBER [testlogin];
END
'

EXEC(@SQL);

(Only use dynamic sql if needed)

Or without dynamic SQL if it is not needed

USE NewlyRestoredDatabase
GO
IF NOT EXISTS (
SELECT * FROM sys.sysusers where name = 'testlogin')
BEGIN
CREATE USER [testlogin] FOR LOGIN [testlogin];
ALTER ROLE [db_datareader] ADD MEMBER [testlogin];
END

Additionally, you could run a schedule a job to check if the user exists in all databases.

While doing all this, if the user already exists in the database, you have to watch out for orphaned users, and act accordingly.

If the user already exists in the restored database

Use NewlyRestoredDatabase
ALTER USER testlogin WITH LOGIN = testlogin

  Question 2, now that I have the user USER_R, a user A want to connect
  to management studio, he doesn't see any table or stored procedure,
  what's missing to let them see all objects and besides be read only?

I am guessing that this user needs to be able to see all objects, and what they are?
Like the T-SQL statements inside a stored procedure?

To do this, GRANT VIEW ANY DEFINITION to the login userA

USE master
GO
GRANT VIEW ANY DEFINITION TO userA;

The login still needs a mapped user to the databases that it needs to access, and be added to the public role (default).


Transaction Log for database 'myDatabase' is full due to ACTIVE_TRANSACTION when creating a Non Clustered Index I am trying to build a non clustered index as follows:

CREATE NONCLUSTERED INDEX IX_MyIndexName ON MyTable(MyColumn) INCLUDE (Column1,Column2,Column3)

However, when i do so, I get a 9002 error:

  The transaction log for database 'myDatabase' is full due to
  'ACTIVE_TRANSACTION'.

When I run DBCC OPENTRAN()I get

  No active open transactions

I am thinking that it is the create index statement itself that is the active transaction filling the log as by the time I run DBCC OPENTRAN() the transaction will have rolled back?

The database is in simple recovery mode (It is a reporting database created from a backup of the production database)

The log is set to an initial size of 2MB, to autogrow by 5120MB and is limited to 100000MB.

If I run DBCC SQLPERF(LOGSPACE) I can see the log size is 1.125MB and Log space used is 31%

The disk that holds the log file has over 1TB free space

I tried to change the initial size of the log using

  ALTER DATABASE ... MODIFY FILE....SIZE=...

and am getting

  MODIFY FILE encountered operating system error 59(An unexpected
  network error occurred.) while attempting to expand the physical file
  \\path\to\my\file.ldf

So I wonder if when SQL server is trying to expand the transaction log to process the transaction, there is this unexpected network error occurring and therefore the transaction fails.

I get the network error every time I run the MODIFY FILE command for the database in question and the same happens for another database (both of these databases have active connections) but I can resize other databases we have that do not have any active transactions (the files for all of the databases are in the same location) so I am thinking this network error may be related to number of connections?

SQL server is running as a domain account and that account has full control over the folder that the database files are stored in.

I suspect moving the database to a local drive rather than a network drive could solve this so that is my next step but that will have to be out of hours.

In the mean time, does anyone have any suggestions of what may solve this? Am I heading down the right track?

EDIT

I've added an additional log file to the database, this time it's initial size is 10,000MB, autogrowth by 64MB, unlimited max space. I set the location as a local drive and ran the create index statement and it worked.

I then dropped the new log file and recreated on the same network location, dropped the index I created before, re-ran the create index and all worked OK.

Next step is to go back to the single log file and put it on a local drive (I'll leave the size the same) and see if the log auto grows

We've changed over the log file disk location to a local disk and that has fixed the problem.

It is still not entirely clear why the transaction log was not expanding in the network location as there didn't seem to be a logical reason why it wouldn't but its something to bear in mind in the future


MS SQL Server Restore: Methods Without Exclusive Access I've got a team recovering an MS SQL Server database. This database is live and currently working with a live website.

An issue occurred (data was deleted) where we needed to restore a backup copy of the data without overriding the current database, in order to retrieve the missing data.

Currently, our website is down with the message:

  Cannot open database "{database_name}" requested by the login. The
  login failed. Login failed for user '{user_name}'

This is because the restoration process requires Exclusive Access the to live database and renders all other access denied.

I haven't worked in database backup/recovery in years and don't remember, and can't find relevant information.

Is this normal, as in, is this how every restore is done?
Is the restore causing this Exclusive Access because of how the backup was taken or setup?
Are there backup/restore methods which could be used which will not required this Exclusive Access, and what are they?

I may have more questions but this is it for now. I need to learn and understand what is possible and compare that with what my DBA Team is doing and why.

Thank you for all your help.

As far as I know there is no way with SQL Server to keep the database online while it’s restoring.  Restores are a point in time version of the database and without having exclusive access to perform the restore it can’t guarantee the state of the data if data was to get changed mid restore.

In the case of oops deletes and not a corrupted database.  If you have the space to do a full restore you can restore the needed database with a different name and file name(s) which will allow you to query the needed data and reinsert or update the live database.

There is a product called LiteSpeed from Quest that I use (not connected to them) to perform backups and compression that will allow you to read the backup files without having to do a real restore. You will then be able to query the tables in the backup and store the results into temp tables in another database on the server. This option requires that you have backed up your database with the software so some advanced DR planning is required.


Find databases with no recent activity Is there some SQL that I can use in order to find out which databases on the Server haven't been used recently?  I need to find the ones that are no longer used and detach them.

No, unless you

logged access in your tables
had a trace (SQL Profiler) running
have a logon trigger
...

You have to explicitly monitor this somehow.

If you need to do it now, you could make the database single user and see who calls...


How to count this table Hi, how to count value in table like this?

This just example:

SELECT COUNT(name1) AS newResult1 FROM table

As this code just count column name1 and it will give result of 3.

What I want to do is count value in name1, name2, name3 and it give result based on value yes or no. Thanks.

I guess this is what you are looking for

 CREATE TABLE  newResult1
    ( name1 CHAR(3),
      name2 CHAR(3),
      name3 CHAR(3)
     )

    INSERT newResult1 (name1,name2,name3)
    SELECT 'yes','no','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'yes','yes','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'no','no','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'no','no','no'

Declare @countofTableRows INT ;

select @countofTableRows= Count(1) from newResult1;

select [name1yes], @countofTableRows - [name1yes]  [name1No]
       ,[name2yes], @countofTableRows -[name2yes]  [name1No]
       ,[name3yes], @countofTableRows - [name3yes] [name1No]
from
(
select sum(case name1 when 'yes' then 1 else 0 end ) [name1yes]
,sum(case name2 when 'yes' then 1 else 0 end ) [name2yes]
,sum(case name3 when 'yes' then 1 else 0 end ) [name3yes]
from newResult1
)Resetset;


Rotate a table in PostgreSQL Problem description

I need to rotate (with DROP and CREATE) a table which is heavily used by other clients.

At present, I have a program which replaces (DROP + CREATE) this table.

Sometimes, just after the table is replaced, I get "ERROR:  could not open relation with OID xyz" from concurrent clients accessing the table.

One could say this behaviour breaks transaction isolation... Does it?

I understand this is caused by system catalogs being cached by postgres backend (which is normally a good thing). Am I right on it?

Is there any way to force a backend to "forget" table OIDs (DISCARD does not help)?

I know it will help if I switch to "DELETE and INSERT" pattern instead of "DROP and CREATE" pattern. But this is some legacy program and we do not want to change it unless absolutely needed.

I will be grateful for any suggestions how to get rid of this problem.

The goal is to rotate the table transparently for other clients.

The test case

Here is the minimal test case that I reduced the problem to:

A. This is the client (multiple clients will run this SELECT in parallel). We will use pgbench for stressing the database.

$ echo "SELECT num FROM x;" > pgbench.minimal.sql

B. This is the "rotator".

$ cat > rotate.x.sql <<EOF
BEGIN;
DROP TABLE x;
CREATE TABLE x ( num integer );
COMMIT;
EOF

C. create empty database and table x.

$ createdb dev
$ psql dev -c "CREATE TABLE x ( num integer )"

D. Start 30 clients.

$ pgbench -c 30 dev -T 60 -n -f pgbench.minimal.sql

E. (in other terminal) Run the "rotator".

$ psql dev -f rotate.x.sql

F. observe what happens to the clients (just after the "rotator" COMMITs).

Client 4 aborted in state 1: ERROR:  could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
                        ^
Client 0 aborted in state 1: ERROR:  could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
                        ^

(...and so on - every client fails)

Comments and ideas

What's interesting - even if the clients work in "new connection for each transaction" mode (add "-C" option to pgbench), same error occurs. Not always, and not for every client, but it does.

I asked this question some time ago on mailing list: http://archives.postgresql.org/pgsql-general/2012-04/msg00431.php - this SO post is just a copy.

For more audience: Same thing applies to rotating a partition. So it might be interesting for all people who use partitioning.

This is all on PostgreSQL 9.0, Linux.

Solution

(thanks to Chris Travers)

filip@srv:~$ cat rotate.x.sql
BEGIN;
DROP TABLE IF EXISTS xold;
ALTER TABLE x RENAME TO xold;
CREATE TABLE x ( num integer );
COMMIT;

What about ALTER TABLE RENAME followed by CREATE TABLE?


Discover SQL Time on Oracle10g I'm using Oracle10g and I have a problem that is challenging me. The application has a timeout of 45 seconds. It accesses a user from my database and always performs the same action. The problem is that the application when a session ends by timeout, ends only in the level of application not in the database.

This causes the database to continue processing and perform the action successfully, however the application execute another call causing duplication of data. (A primary key is determined by sequence)

What is my idea? End the session when the execution of certain instruction exceed 45 seconds in the database, from this specific user. The problem is that the application uses a connection pool, and the application treats those who enter or not the database.

I can not implement it for profile, because the connections are on the bench for days, and only when necessary to make the appointment.

I need to know how long it is taking a particular query to run. My need is to know when a user, it only makes a single query in the database, it times out the application, which is 45 seconds.

Where can I get this information? With it I could determine which session closed, since a profile would be unworkable.

I apologize for my bad English. I'm still learning.

Cheers
Rainier Morilla

It sounds like the transaction is being committed (perhaps automatically) even when the application times out.  Changing the COMMIT behavior would be the best option.

If you can't do that, you could put the database action in a package and then have the package check after it runs the action to see how much time has elapsed.  If the step took more than 45 seconds then it should ROLLBACK, otherwise COMMIT.

Although this answers your question, it is a fragile solution and you really should look into changing the COMMIT behavior or causing a database disconnect when a timeout occurs.


Move a MySQL 5.5 database to a server running MySQL 5.1 I have a backup of a MySQL database (using InnoDB) from a Windows machine that was running MySQL 5.5. The Windows machine is no longer available and now I need to import the backup into an Ubuntu box running MySQL 5.1.73. Is this possible? Any hints on how/where to copy the files?

Most of the info I find is about "upgrading" from 5.1 to 5.5 but in my case I need to go the opposite way.

I have a script to move MySQL data from one version to another as long as it is MySQL 5.x

If you look at my old post MySQL upgrade 5.0.88 to latest, I handle this situation going to a higher version. With slight modifications, I can make this process go backwards.

STEP 01 : mysqldump all databases except mysql, information_schema, performance_schema

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > Data.sql

STEP 02 : dump the grants as pure SQL commands

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sql

STEP 03 load each file into a new MySQL 5.1 Instance

Login to MySQL as root and run

mysql> source Data.sql
mysql> source Grants.sql

Give it a Try !!!

CAVEAT #1

You cannot mysqldump everything and load it into a different version of MySQL because the mysql schema has columns in the mysql.user table specific to a version.

You cannot run mysql_upgrade to fix it because you are doing a downgrade.

I have mentioned splitting the grants from the data before

Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
Apr 11, 2013 : MySQL upgrade 5.0.88 to latest (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

CAVEAT #2

The solution I posted would be for Linux, but your database is on a Windows machine. So let me rephrase the solution with Windows in mind ...

STEP 01 : Get the name of every database in the Windows machine (except mysql, information_schema, performance_schema) and dump them. For example, if your databases as db1, db2 and db3, dump them to a text file like this:

C:\> mysqldump -uroot -ppassword --routines --triggers --database db1 db2 db3 > Data.sql

STEP 02 : Dump the Grants to a text file

C:\> mysql -uroot -ppassword -ANe"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),''';') FROM mysql.user WHERE user<>''" > grant_cmds.sql
C:\> mysql -uroot -ppassword -AN < grant_cmds.sql > Grants.sql

STEP 03 : Edit the Grants.sql

Open Grants.sql in notepad and append a semicolon to every line

STEP 04 load each file into a new MySQL 5.1 Instance

Login to MySQL as root and run

mysql> source Data.sql
mysql> source Grants.sql

CAVEAT #3

I have techniques for using MySQL for Windows with mysqldump along with user-defined variables in the Windows shell : MySQLdump on specific db date


Checking for Alwayson manual sync process for redo queue data I have seen frequently few databases are having synchronization issue with secondaries and monitored the same and found redo data queue size is around in 2MB in secondary replicas. I have tried in GUI for resume data and do syncup but it's failed giving error.

Do we have any code for apply Redo pending data on secondary replica databases manually.

I have seen frequently few databases are having synchronization issue with secondaries and monitored the same and found redo data queue size is around in 2MB in secondary replicas.

This is indicative of two possible items (or both).

The system (storage, memory, cpu) isn't fast enough or have enough bandwidth to redo the log blocks.
REDO may be blocked by a user thread if it's a readable secondary.

  I have tried in GUI for resume data and do syncup but it's failed giving error.

Was it not synchronized? If not, that's a different problem. Pausing and resuming will potentially only make it worse, depending on the root cause.

  Do we have any code for apply Redo pending data on secondary replica databases manually.

No, there isn't a button to press to tell the REDO to run. It runs. Constantly. All the time.

It doesn't run when it's blocked and it runs very slowly when it doesn't have the hardware to run properly (primary may be more powerful than secondary, etc.).

If you look at the secondary, there should be an AlwaysOn Health extended events session running. Opening the XEL file in management studio (SSMS) will give you what has been captured. Look for any REDO BLOCKED messages. If there aren't any, run a perfmon on the system to check on IO latency and throughput, memory, and cpu utilization.


COBOL programs connecting to SQL Server Has anyone successfully made a connection to SQL Server using a COBOL program? Hoping to make a connection so that hundreds of COBOL programs don't need to be rewritten.

We use Microfocus to emulate the z/os (and DB2) environment on the Windows platform - we have a couple thousand Cobol programs successfully accessing Sql Server daily to run our entire legacy system.  It's not cheap though.


Counting and grouping over multiple OUTER JOINs I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a single query that I can use as a view (not a materialized view, as this will be real-time data).

The tables are:

CREATE TABLE advert
(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE advert_event
(
  code CHAR(1) NOT NULL PRIMARY KEY
);

CREATE TABLE advert_log
(
  advertisement integer NOT NULL REFERENCES advert(id),
  event_code CHAR(1) NOT NULL REFERENCES advert_event(code)
);

And some sample data that covers all possible cases:

INSERT INTO advert VALUES (1);
INSERT INTO advert VALUES (2);
INSERT INTO advert VALUES (3);
INSERT INTO advert VALUES (4);

INSERT INTO advert_event VALUES ('I'); -- Impression
INSERT INTO advert_event VALUES ('C'); -- Click

INSERT INTO advert_log VALUES (1, 'I');
INSERT INTO advert_log VALUES (1, 'C');
INSERT INTO advert_log VALUES (2, 'I');
INSERT INTO advert_log VALUES (2, 'I');
INSERT INTO advert_log VALUES (2, 'C');
INSERT INTO advert_log VALUES (3, 'I');
INSERT INTO advert_log VALUES (3, 'I');

For references, here is a set of the things I want to count in advert_log:

Query A.

SELECT * FROM advert,advert_event;

Result A.

 id | code
----+------
  1 | I
  1 | C
  2 | I
  2 | C
  3 | I
  3 | C
  4 | I
  4 | C
(8 rows)

Event counts by advertisement:

Query B.

SELECT DISTINCT advertisement,event_code,COUNT(*) OVER (PARTITION BY advertisement,event_code) FROM advert_log;

Result B.

 advertisement | event_code | count
---------------+------------+-------
             1 | I          |     1
             1 | C          |     1
             2 | I          |     2
             2 | C          |     1
             3 | I          |     1
(5 rows)

For any individual advertisement, the correct counts can be obtained by queries like:

Query C1.

SELECT COUNT(*) FROM advert_log WHERE advertisement=4 AND event_code='I';
 count
-------
     0
(1 row)

Query C2.

SELECT COUNT(*) FROM advert_log WHERE advertisement=4 AND event_code='C';
 count
-------
     0
(1 row)

Of course, my earlier query excludes zero counts, so it doesn't catch either of the above two cases.

Ultimately, what I am attempting to do is to turn the above numbers into the following, using clicks (the 'C' entries) divided by impressions (the 'I' entries) to derive the cpi column:

 advertisement | impressions | clicks | cpi
---------------+-------------+--------+-----
             1 |           1 |     1  | 1.0
             2 |           2 |     1  | 0.5
             3 |           1 |     0  | 0.0
             4 |           0 |     0  | 0.0 <- or NULL, NaN, 1.0, ...

My initial approach was to create a view for queries C1 and C2, and to call that function from a view based on Query A.

I suspect there is a simpler way to accomplish my goal with a single query.

I was able to find a solution while I was composing the question, but I've decided to post both the question and answer in case it helps someone else in the future.
Alternatively, if anyone has a simpler or better-performing solution to this problem, I would be happy to hear it.

I managed to solve my NULL-counting problem by using a CROSS JOIN prior to the OUTER JOIN:

SELECT * FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code;

 id | code | advertisement | event_code
----+------+---------------+------------
  1 | I    |             1 | I
  1 | C    |             1 | C
  2 | I    |             2 | I
  2 | I    |             2 | I
  2 | C    |             2 | C
  3 | I    |             3 | I
  3 | I    |             3 | I
  3 | C    |               |
  4 | I    |               |
  4 | C    |               |
(10 rows)

The above gave me the intermediate table I needed. Adding grouping and counting, I finally get the numbers I'm looking for:

SELECT advert.id,advert_event.code,COUNT(advert_log.advertisement) FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code GROUP BY advert.id,advert_event.code;

 id | code | count
----+------+-------
  1 | C    |     1
  1 | I    |     1
  2 | C    |     1
  2 | I    |     2
  3 | C    |     0
  3 | I    |     2
  4 | C    |     0
  4 | I    |     0
(8 rows)

Finally, using two sub-SELECTs (one for 'I', one for 'C'), I composed a query to obtain the counts by advertisement:

CREATE VIEW advertisement_dashboard AS
SELECT a.id,i.impressions,c.clicks,c.clicks::float/greatest(i.impressions, 1) AS cpi FROM advert a,
(
  SELECT advert.id,COUNT(advert_log.advertisement) AS impressions FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code
GROUP BY advert.id,advert_event.code HAVING code='I'
) i,
(
  SELECT advert.id,COUNT(advert_log.advertisement) AS clicks FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code
GROUP BY advert.id,advert_event.code HAVING code='C'
) c WHERE i.id=a.id AND c.id=a.id ORDER BY a.id ASC;

SELECT * FROM advertisement_dashboard;

 id | impressions | clicks | cpi
----+-------------+--------+-----
  1 |           1 |      1 |   1
  2 |           2 |      1 | 0.5
  3 |           2 |      0 |   0
  4 |           0 |      0 |   0
(4 rows)


SQL Server 2012 Express Service Pack 2 - installation error 0x84B40001 I'm trying to install SQL Server 2012 Express Service Pack 2 through command line:

SQLEXPR_x86_ENU.exe /ACTION=Install /HIDECONSOLE  /FEATURES=SQL /INSTANCENAME=SQLEXPRESS /SECURITYMODE=SQL /SAPWD="SOMEPWD" /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SQLSYSADMINACCOUNTS="Builtin\Administrators" /AGTSVCACCOUNT="NT AUTHORITY\Network Service"

However I get this error message:

  The syntax of argument "/HIDECONSOLE" is incorrect;Either the
  delimiter '=' is missing or there is one or more space characters
  before the '='"  Error 0x84B40001

I removed the entire /HIDECONSOLE argument in my command, but I still get the same error.

@Nissanna, As you said, you are getting the error during cmd installation of sql server express 2012.

  The syntax of argument "/HIDECONSOLE" is incorrect;Either the
  delimiter '=' is missing or there is one or more space characters
  before the '='" Error 0x84B40001

when you are running this syntax:

  SQLEXPR_x86_ENU.exe /ACTION=Install /HIDECONSOLE  /FEATURES=SQL
  /INSTANCENAME=SQLEXPRESS /SECURITYMODE=SQL /SAPWD="SOMEPWD"
  /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
  /SQLSYSADMINACCOUNTS="Builtin\Administrators" /AGTSVCACCOUNT="NT
  AUTHORITY\Network Service"

I would like to say that definitely you are doing some arguments mistake or permission. As MSDN BOL https://msdn.microsoft.com/en-us/library/ms144259(v=sql.110).aspx the simple syntax of installation would be as mention below format:

  Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER
  /SQLSVCACCOUNT=""
  /SQLSVCPASSWORD=""
  /SQLSYSADMINACCOUNTS="" /AGTSVCACCOUNT="NT
  AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS

NB: For local installations, you must run Setup as an administrator.

AS local installations, you must run Setup as an administrator. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share. For failover cluster installations, you must be a local administrator with permissions to login as a service, and to act as part of the operating system on all failover cluster nodes.

As your refence https://social.msdn.microsoft.com/forums/sqlserver/en-US/87bb5999-1126-4809-a353-0f5b06c9f81d/sql-express-2008-command-line-options and https://msdn.microsoft.com/en-us/library/ms144259(v=sql.110).aspx


Does Traceflag 1800 require a restart? We have recently migrated our database mirroring secondary to new hardware. The log drive (and other SQL Server drives) on the secondary has been formatted to 64KB block size, whereas the primary server is on the default 4KB.

As a result of this we are getting the following entries in the log of the secondary almost constantly -

There have been N misaligned log IOs which required falling back to synchronous IO.  The current IO is on file L:\SQLSERVER\Logs\MyDatabase_log.ldf.

this article suggests a fix is to turn on trace flag 1800 on the server

  with 512-byte sector size as per Microsoft's recommendation

I have run the following command on both servers:

fsutil fsinfo ntfsinfo L:\

and can see for the primary:

Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment :    1024

and the secondary:

Bytes Per Sector  :                512
Bytes Per Physical Sector :        4096
Bytes Per Cluster :                65536
Bytes Per FileRecord Segment :     1024

Therefore it seems traceflag 1800 should be enabled on the primary.

With this in mind, I ran DBCC TRACEON (1800, -1);  on the primary and verfied it was on with DBCC TRACESTATUS

I then checked the error log on the secondary and can see the messages regarding Async IO still persist.

Does this traceflag need setting as a startup parameter (with subsequent reboot) to take effect? Obviously I would do this anyway so it persists reboots but just ran the DBCC TRACEON first so it is active

The documentation doesn't reference that a reboot is required.

Obviously the way to find out is to test but due to this being a production server, I would need a maintenance window to that which isn't available to me at the moment

Eventually, the primary server will be migrated to new hardware and I will ensure the disks are formatted in the same manner but for the tie being I was hoping this traceflag would provide a quick fix.

If I cannot get this to work, I suppose the intermediate option would be to reformat the log drive on the existing primary to 64KB block size

The documentation you referenced says that:

  A global trace flag must be enabled globally. Otherwise, the trace
  flag has no effect. We recommend that you enable global trace flags at
  startup, by using the -T command line option. This ensures the trace
  flag remains active after a server restart. Restart SQL Server for the
  trace flag to take effect.

and

  Using the DBCC TRACEON and DBCC TRACEOFF commands. For example, to
  enable the 2528 trace flag globally, use DBCC TRACEON with the -1
  argument: DBCC TRACEON (2528, -1). The effect of enabling a global
  trace flag with DBCC TRACEON is lost on server restart. To turn off a
  global trace flag, use DBCC TRACEOFF with the -1 argument.

And on the definition of flag 1800 it says:

  Enables SQL Server optimization when disks of different sector sizes
  are used for primary and secondary replica log files, in SQL Server
  Always On and Log Shipping environments. This trace flag is only
  required to be enabled on SQL Server instances with transaction log
  file residing on disk with sector size of 512 bytes. It is not
  required to be enabled on disk with 4k sector sizes. For more
  information, see this Microsoft Support article.

  Note: This trace flag applies to SQL Server 2012 (11.x) SP1 CU13, SQL
  Server 2012 (11.x) SP2 CU3, SQL Server 2014 (12.x) RTM CU5, and higher
  builds.

  Scope: global only

That means a restart is required depending on the configuration method you used: if by DBCC TRACEON, no restart required. If by -T, a restart is required.

Since you don't have a maintenance window to restart the server now by using the -T recommended option, you could use DBCC TRACEON to activate it emediately and also configure the -T on the service properties without rebooting SQL Server. That way when the server reboots next time the flag configured by DBCC TRACEON will be lost, but the option -T will take effect.


Oracle Transportable Database export ORA-39340: unsupported object, INDEX I want to get data dump from PDB(Transportable Database). DB version 12.2.0.1.0. Enterprise Edition.
Before I get export I applied oracle 27162931 and 27162975 opatch. Then I altered tablespaces except sysaux and system, temp and undotbs1:

alter tablespace users read only;

Next I logged in to the database using sysdba and then created directory and granted read, write access:

CREATE OR REPLACE DIRECTORY exp_dir AS 'D:\export_db';

GRANT READ, WRITE ON DIRECTORY exp_dir TO system;

Then I logged from system and ran export command like below,

expdp system/XXXXXX@D1943 dumpfile=DB12cdmp.dmp directory=exp_dir transportable=always full=y version=12.2.0.1.0 logfile=exp_test1.log

But I'm getting error like below,
Processing object type

  DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX

  ORA-39340: unsupported object, INDEX:"ABC"."SYS_IL0000903003C00006$$"
  will be skipped. ORA-39340: unsupported object,
  INDEX:"ABC"."SYS_IL0000903008C00002$$" will be skipped.

ORA-39043 Or ORA-39340 May Be Raised During Full Transportable Export of the Database (Doc ID 2365679.1)

  This issue is currently investigated in bug 27244546.

  When encountering this problem, recreate the text index after
  importing into the new database.


MySQL 5.5 High CPU Usage I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was high CPU usage in the server. All the time CPU load is 15-20. We used litespeed and MySQL 5.1 with CentOS 5.9 in Dual Xeon L5506, 12GB RAM server with Sata HDD.

So I analyzed the database and found there is only 4GB of data and index size of that DB and decided to convert to InnoDB. Once we did, we ended up having 80-150 CPU load and server was about to crash. So we transferred MySQL to another server with same config but to MySQL 5.5.

In New DB server CPU load is 1-2 and web server still on 4-6 constant CPU load.

Here is my my.cnf

[mysqld]
innodb_file_per_table=1
local-infile = 0
default-storage-engine = InnoDB
max_connections = 1000

innodb_buffer_pool_size = 8G
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_file_format = Barracuda

myisam_sort_buffer_size = 16MB

query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 256M
thread_cache_size = 16K

key_buffer_size = 128M
max_heap_table_size = 128M
tmp_table_size = 128M
join_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 1M
sort_buffer_size = 32M
table_cache = 4K
open_files_limit = 65535

log-slow-queries = /var/log/mysql/slowqueries.log
long_query_time = 3

PS : Not all of our  DB's are InnoDB, so MyISAM values are placed after good analysis.

Statistics : For 4 hours

Questions since startup: 7,339,471 Documentation
ø per hour: 1,704,102
ø per minute: 28,402
ø per second: 473

   Traffic              ø per hour
Received 4.8 GiB    1.1 GiB
Sent     248.5 GiB  57.7 GiB
Total    253.3 GiB  58.8 Gi

OBSERVATION #1
When committing to use InnoDB, you also need to commit to tuning for multiple cores.

innodb_read_io_threads : The default is 4. I would raise it 16.
innodb_write_io_threads : The default is 4. I would raise it 16.

OBSERVATION #2
I see you have innodb_thread_concurrency = 8. If you set innodb_thread_concurrency to 0 (which is the default), you will have infinite concurrency. That let's the InnoDB storage engine decide how to many threads it feels it needs and can handle.
OBSERVATION #3
Your DB Server has 12GB of RAM. Your InnoDB Buffer Pool is bigger than half the RAM. You need to partition the Buffer Pool by setting innodb_buffer_pool_instances to 2. In conjunction with this, you need to run numactl --interleave=all (Not applicable to VMs).
OBSERVATION #4
I see you have innodb_file_format = Barracuda. I wish you can go back to innodb_file_format = Antelope. Why go back to uncompressed? It tends to bloat the InnoDB Buffer Pool because compressed and uncompressed data and index pages coexist in the Buffer Pool. I just wrote about this : See my post innodb_file_format Barracuda
EPILOGUE
Here are some of my past posts on tuning InnoDB

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?

Give it a Try !!!


Disabled or enabled trigger from another trigger I have two tables: dbo.old_roles and dbo.new_roles. I need to sync between the two.

When created or modified role in one table it should update the other one. To do it I created a trigger in each of the tables.

To prevent a loop, I want to disable the trigger in one table when a trigger is fired from the other table.

When I call to ALTER TRIGGER statement., I get an error.

What can I do?

You have three options:

Disable "nested triggers" using sp_configure. This is a server/instance -level option, so it might disable functionality in other areas where you might have triggers calling other triggers. But it should at least be mentioned. You can see the current setting using the following:

EXEC sp_configure 'nested triggers';

And you can disable this ability using the following:

EXEC sp_configure 'nested triggers', 0;
RECONFIGURE;

You can read more about this setting here: Configure the nested triggers Server Configuration Option.
Have each trigger detect how many levels deep they are using TRIGGER_NESTLEVEL function. Use this at the beginning of each trigger to simply exit if it is not the 1st or 2nd trigger execution in the stack. Something along the lines of:

IF (TRIGGER_NESTLEVEL() > 2)
BEGIN
  -- Uncomment the following PRINT line for debugging
  -- PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;

This will require a little bit of testing to see how it is affected by the initial insert being done by yet another trigger (in case that ever becomes an issue, but it might not). If it doesn't work as expected when called by another trigger, then try setting some of the parameters to this function. Please see the documentation (linked above) for details.
Set a flag in the session-based "context info" using SET CONTEXT_INFO. Context info is a VARBINARY(128) value that exists at the session level and retains its value until overwritten or until the session ends. The value can be retrieved either by using the CONTEXT_INFO function or selecting the context_info column from either of the following DMVs: sys.dm_exec_requests and sys.dm_exec_sessions.

You could place the following at the beginning of each of those triggers:

IF (CONTEXT_INFO() = 0x01)
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;
ELSE
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Initial call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  SET CONTEXT_INFO 0x01;
END;

This option doesn't work so well if you are already using Context Info for some other reason. But, anyone using SQL Server 2016 can make use of SESSION_CONTEXT, which is a new session-based set of key-value pairs.


Postgres permission issues for new views I have a RDS postgres database that only uses the public schema.

I expect the webservice user to get access to all newly created views. When I create a view using the master user, I see all the grants as expected.

Here are the DEFAULT PRIVILEGES I have setup.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES TO webservice;

When the rormigration user creates a view in the public schema, I see it is owned by rormigration  and none of the DEFAULT PRIVILEGES are present. I have to switch the owner to master and grant rights.

What can I do to fix this permission issues?

DEFAULT PRIVILEGES only apply to objects that the user creates. I didn't have DEFAULT PRIVILEGES setup for rormigration so users didn't get rights to objects created by rormigration.

I reviewed DEFAULT PRIVILEGES and then ran the missing grants while logged in as rormigration.

SELECT *
FROM pg_default_acl acl
JOIN pg_namespace ns ON ns.oid = acl.defaclnamespace;


MySQL Client out of memory when running a simple select query from MSSQL ODBC Table has a total of 1,120,000 rows and 20 columns. When I run a select query on mysql directly like:
select * from <tablename> where UpdatedOn > '2020-10-05'

it gives the results which is about 50+ rows.
But when I run the same from MSSQL using ODBC Connection it throws "MySQL Client Out of Memory" every single time.

The server has 8 GB RAM and no one else is using the server
Running Select top gives the results without the error
Running select with a single integer column like select id from ... throws a "MySQL Client out of memory"

What could be going wrong? I haven't been able to the issue.

The comparison of datetime with a string may cause odbc to retrieve the entire table before eliminating rows.  Either compare with a datetime constant or set the prefetch parameter in the odbc config to a reasonable value.


How to generate a sequence in mysql Consider this table in mysql

create table numbers (number int);
insert into numbers values (3), (2), (9);
select * from numbers;

+--------+
| number |
+--------+
|      3 |
|      2 |
|      9 |
+--------+

Is there a simple query to generate a table with the following columns

The numbers from 1 till 10
1 if the number is present in the table numbers, and 0 otherwise

I guess you have to create a sequence of numbers in order to do this. If possible, I want to create such a sequence without storing it in the database.

Related question: is there a select query that generates a sequence of numbers from 1 till 10 (or 100 or 1000)?

IN MariaDB

MariaDB has a SEQUENCE Storage Engine. So, if you are using MariaDB, all your sequence troubles are over (or have just begun).

Sequence of 10 numbers

select * from seq_1_to_10;

NOT USING TABLES

Sequence of 10 numbers

select * from
(select 0 x union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A;

Sequence of 100 numbers

select (t*10+u+1) x from
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B
order by x;

Sequence of 1000 numbers

select (h*100+t*10+u+1) x from
(select 0 h union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) C
order by x;

Sequence of 10000 numbers

select (th*1000+h*100+t*10+u+1) x from
(select 0 th union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 h union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B,
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) C,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) D
order by x;

USING TABLES

Sequence of 10 numbers

use test
drop table if exists seq10;
create table seq10
(x int not null auto_increment primary key);
insert into seq10 values (),(),(),(),(),(),(),(),(),();
select * from seq10;

Sequence of 100 numbers

use test
drop table if exists seq100;
create table seq100
(x int not null auto_increment primary key);
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 select x + 50 from seq100;
select * from seq100;

Sequence of 1000 numbers

use test
drop table if exists seq1000;
create table seq1000
(x int not null auto_increment primary key);
insert into seq1000 values ();
set @p= -1;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
select * from seq1000;

Sequence of whatever numbers (Example: 3.5 million)

use test
drop table if exists seq;
create table seq
(x int not null auto_increment primary key);
insert into seq values ();
set @maxseq = 3500000;
set @p = -1;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
select max(x),count(x) from seq;

YOUR ACTUAL QUESTION

Using the sequence 0..9, you left join the sequence against the table

select A.number,1-ISNULL(B.number) present from
(select 0 number union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A
left join numbers B using (number);

Your sample data with the new query

mysql> drop table numbers;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists numbers;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table numbers (number int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into numbers values (3), (2), (9);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select A.number,1-ISNULL(B.number) present from
    -> (select 0 number union select 1 union select 2 union select 3 union select 4 union
    -> select 5 union select 6 union select 7 union select 8 union select 9) A
    -> left join numbers B using (number);
+--------+---------+
| number | present |
+--------+---------+
|      0 |       0 |
|      1 |       0 |
|      2 |       1 |
|      3 |       1 |
|      4 |       0 |
|      5 |       0 |
|      6 |       0 |
|      7 |       0 |
|      8 |       0 |
|      9 |       1 |
+--------+---------+
10 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Query to get current column based on date range I have a scenario with the following tables. The issue is when a car is booked in table tBooking it is due to following scenarios.
Example
User books a car in branch (branch is a location of company offices where the cars are kept)

User A books a Car at branch A on date range 4-dec-202- to 6-dec-2020 (tBranch.StartBranchId)  and wants to return it to branch B (tBranch.EndBranchId)

User B is searching for cars available on the 7-dec-2020 in a specific location branch B

How do I write a query where I get the next available car by date range and the requested location?
Let's say am searching for available cars at branch B, this is what I have tried so far:
SELECT * FROM tVehicle WHERE VehicleID NOT IN (
    SELECT VehicleID FROM tBooking
    WHERE StartDate >= @requestedDate
    AND EndDate <= @requestedDate
)
AND BranchId IN (
    SELECT BranchId FROM tBooking WHERE EndBranchId = @branchId
    -- I dont know how to write correct logic for the dates
    AND StartDate >= @requestedDate
    AND EndDate <= @requestedDate
)

Assuming that tVehicle.BranchId contains Branch where the vehicle is currently at and branchId of branch B is 2 this should do the trick
DECLARE @branchId int= 2;
DECLARE @availableDate date= '2020-12-09';

SELECT tv.*
FROM tVehicle tv
WHERE tv.BranchId=@branch
AND NOT EXISTS(SELECT 1 FROM tBooking tb WHERE tb.VehicleId=tv.VehicleId AND @availableDate BETWEEN tb.StartDate AND tb.EndDate);


Encountered unexpected growth Transaction log file size We are using SQL Server 2008 R2 Standard version, we created Maintenance plan Rebuild Index, Reorganized Index and update Statistic , Scheduled Every day at 1 AM. After Executing this Maintenance Plan, Database transaction log file size increased abnormally. For suppose, if Primary Data file is 7 GB, the transaction log file grows to 55 GB.

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance)

The Other solution ever one recommended is Transaction log backup after Full Backup. We did same and observed no change in file sizes it shows same size as before Primary file is 7 GB and Transaction log file is 55 GB.

Please suggest me best way to reduce log file size without any effect on Performance.

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance)

That is not true. log shrink is quite benign, you are thinking data shrinks. See How to shrink the SQL Server log for an explanation why it grows, how to shrink it and why is benign.

My first recommendation is to use a smart index maintenance plan. Overactive rebuild is expensive, harmfull and completely unnecessary. Many swear by Ola Hallengren's index maintenance scripts.

You must also look into leveraging minimal logging. Index maintenance are a prime candidate for minimally logged operations, but you must use enable them by using the bulk-logged recovery model for your database. See Operations That Can Be Minimally Logged:

  If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online.


Total_worker_time over SQL 2008 linked server I’ve come across a lengthy SP that is being recorded as generating a high amount of total_worker_time in sys.dm_exec_procedure_stats. (But I think it might be a red-herring)

The SP contains cursors and distributed updates over linked MS SQL Servers.

When a query takes place over a linked server, does the local server record Elapsed time in place of CPU time, or does the remote server return the actual CPU time to the local server?

When a query takes place over a linked server, does the local server
  record Elapsed time in place of CPU time, or does the remote server
  return the actual CPU time to the local server?

When executing a query over Linked Server the SQL Server session will enter an OLEDB wait while waiting for results, and that time will not be accounted for as CPU_time.   The linked server does not return wait stats.


Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query I have a simple select select statement, see below.

SELECT     JobTasks.JobTaskID,
           CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20)
FROM       JobTasks
INNER JOIN JobExecutions
ON         JobTasks.JobExecutionID = JobExecutions.JobExecutionID
INNER JOIN JobStatus
ON         JobExecutions.JobStatusID = JobStatus.JobStatusID
WHERE      (JobExecutions.JobID = 1)
AND        (JobTasks.FileName IS NOT NULL)
ORDER BY   CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20)

It fails on converting the varchar to a date time, but if I change the select part of the script to include the filename field without the convert part it never fails, like below:

SELECT JobTasks.JobTaskID, CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20), JobTasks.FileName

I have checked the data and it is all correct and can be converted to a datetime without issue. I have stripped out all the non numeric characters from the data and converted them all to datetime using CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20) without issue. The field FileName is  a varchar(200) datatype. Removing or changing the order by does not make any difference. It still doesn't allow it to run

The filenames have a naming convention of

  A20120224_ABC_DEF.txt
  B20120225_XYZ_ABC.txt
  C20120225_XYZ.txt
  A20120225_ABX.txt

FYI

This is an old system that was built using sql server 2008r2 and has been in use for several years, the issue has only just cropped up very recently. Date wasn't a datatype when it was built and I believe datetime type 20 is being used because this data is being inserted into another datetime field.

Can anyone shed some light on the reason for this?

Thanks
Noelle

EDIT

From the answers and comments below and further research I have come to to the following understanding:

Adding the field being used in the where clause alters the execution path of the select statement, in that it excludes the null values before it tries to cast them, where as excluding it from the select doesn't invoke the where clause until after it tries to cast them.

Would this be correct?

I think you'll only get the out-of-range error if you try to convert, say a "June 31st" or a 13th month. If the text is just totally un-datelike (is that a word?) it'll give a different error.

With that inb mind, TRY_CONVERT() is a variant of CONVERT() which won't error if the format isn't quite right. You can use it to look for filenames which'll fail the convert.

Try running:

SELECT JobTasks.FileName
FROM   JobTasks
WHERE  TRY_CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20) is null


Instant File Initialization How can i check if IFI is enabled on my PROD server?

I did the following checks:

Ensured that the service account under which SQL Server is running is part of Administrators group.
Perform Volume Maintenance tasks permission is set for "Administrators"

Does this mean that IFI enabled?

Easiest way is to

enable TF 3004 and 3605 (turn them off when done)
Create a dummy database
Check in error log for "SQL Server is zeroing out ..."

SQL Server 2016 will log the message by default when Instant file Initialization is enabled ... instant file initialization is enabled

Obviously, you can use xp_cmdshell whoami /priv or tsql way.

Good PFE blog reading : How and Why to Enable Instant File Initialization


Restore deleted record from a table I got a  call today from the platform owner, there was a bug in the code and some records got deleted over 2 weeks (3 records every day on average). He gave me a list with the records and when they got deleted but the time is in HH:mm. I restored one backup to this time also 1 min before but still didn't find this record. He said there is a chance the record got created and deleted at the same time.

I feel I am dead in the water. Can anyone help?

IF the database is running in FULL recovery mode and you can somehow manage to restore the transaction log file from the time the DELETE statement ran, then there is a chance that you could recover the actual statements that were executed by reading the actual transaction log.

The way to do this requires a 3rd party tool though. It's called ApexSQL Log and it has a free trial download which you could use to test it out.

To understand how to use this tool properly, the creators of it have published an evaluation guide that walks through common scenarios of reading transaction logs.

ApexSQL Log evaluation guide for first time users – High level walkthrough with links to core topics


Virtualize SQL High CPU This is a client environment which needs significant overhaul so all I am trying to do is slow the bleeding.
Situation
CPU is getting crushed, often 80% or higher utilization

VMWARE 5.5, Windows Server 2008r2, SQL Server 2012
1 vCPU with 6 cores (red flag here)
70GB of RAM
16 separate instances, the one driving most of the CPU use has 80 user
databases
All most all instances MAXDOP 0, all Cost threshold for parallelism is
5

Outage is not an option right now, so no reboots, no vCPU changes but I need to slow the bleeding that is the high CPU.
This a sample of top 10 current waits for the instance with 80 DBs, the CPU hog:
╔══════════════════════╦══════════════════╦════════════╦═════════════╗
║      wait_type       ║ Wait Time (Secs) ║ # of Waits ║ Avg ms/Wait ║
╠══════════════════════╬══════════════════╬════════════╬═════════════╣
║ WRITELOG             ║ 9.8              ║ 5563       ║ 1.8         ║
║ PAGELATCH_SH         ║ 7                ║ 21742      ║ 0.3         ║
║ SOS_SCHEDULER_YIELD  ║ 6.5              ║ 8891       ║ 0.7         ║
║ PAGELATCH_EX         ║ 5.3              ║ 20106      ║ 0.3         ║
║ CXPACKET             ║ 4.2              ║ 174        ║ 24.2        ║
║ LATCH_EX             ║ 2.7              ║ 1287       ║ 2.1         ║
║ LCK_M_S              ║ 2.2              ║ 123        ║ 18.1        ║
║ LCK_M_U              ║ 1.7              ║ 385        ║ 4.5         ║
║ PAGEIOLATCH_SH       ║ 0.9              ║ 972        ║ 0.9         ║
║ ASYNC_IO_COMPLETION  ║ 0.5              ║ 3          ║ 150.3       ║
╚══════════════════════╩══════════════════╩════════════╩═════════════╝

My thought, set MAXDOP at 3, cost threshold at 25 for all instances.

Things I see wrong right away

Way to many instances
Not enough vCPUs
vNUMA probably doesn't match NUMA
I'm guessing there's a ton of missing indexes
MAXDOP of 1 or 2
Cost threshold of 50
Optomize for adhoc workloads = on (it's probably off)

Questions

What do the waits look like?
What are all the memory configs setup for?

Fixing this is going to take an outage.  Probably a few of them.


What is the difference between `MAX(column + 1)` and `MAX(column) + 1` I know I should be writing MAX(column) + 1 in my queries rather than MAX(column +1). However, by mistake I wrote the wrong one and it seems to prevent a deadlock issue that I was having.

Couldn't find an explanation on this after digging for sometimes. Do point me to the write direction if this is already answered or looking forward to learn some new interesting things.

max(column)+1 will compute max value of the column and then adds 1 to the results
max(column+1) will compute max value on column value+1

Tables meta data/index will be used when you use max(column) but max(column+1) needs to scan all the rows and compute the values one by one. So good to use max(column)+1


Total billions of rows and daily millions of row insert: What database system suits best We developed a system with very high volume of data but very simplified data structure. We had only cellX, cellY, timeStamp and value columns.

Operations are:

Insert 455+ thousand rows per day
Query using range filters on cellX, cellY and timeStamp
Query is not required to return instantly, we can notify the user when requested data is ready.

Because the data was too large and we required index for query, we used this scheme:

Use SQL Server.
Clustered index on cellX, cellY and timeStamp.
Separate tables for every year, therefore total number of rows in a table remains within limit (~166 million).
Use a custom format for timeStamp. Skip year, keep only month, date and hour. We were able to keep it within 16 bit int.
Use partition on each years table.
Insert one day data at a time. Use partition switching to keep the database live while inserting data.

This has been working good so far. Although we notify the user after data is ready, the delay is never more than a few seconds as long as the query is reasonable.
But recently we got the opportunity to get more precised data and the data volume has been increased by 68 times!. Therefore, now we have:

insert 30+ million rows per day.
store 11 billion rows in a table for a year. This can be reduced by making quarterly (2.7 billion) or monthly (1 billion) tables.

This is possible that we might be able to receive more precised data in a year or two. Therefore it is possible that data volume will increase again by a significant factor.

The question is, will this scheme we are using sustain? Or we should migrate to another scheme, may be another database system leaving SQL Server?

Edit

The three dimension columns cellX, cellY and timeStamp are very regular in nature. You can define all of them by
f(x) = mx + c, for some integer x ranging (0, 1, 2, ..., X).

I've worked with a 30+ billion row monthly partitioned table with page compression and 10 years of history.  The table schema was fairly simple with a datetime2(2) clustered index and 3 non-clustered indexes on varchar columns and a couple of non-indexed columns.  Storage was about 2TB and it performed reasonably well.  SqlBulkCopy was used to insert about 15M rows continuously throughout the day as data was needed in near real time.

Based on this anecdote, I'm confident SQL Server could handle your expected volume with adequately sized hardware.  That being said, I completely agree with @DamianoVerzulli that your application is an excellent candidate for a less costly NoSQL solution due to your tolerance for delay.


Is there a way to see how SQL Server 2012 defines new identities? On Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64), is there a way to see what mechanism SQL Server uses to calculate the new identity values for tables created by SELECT INTO?

SAMPLE DATA

-- Create our base table
CREATE TABLE dbo.A
(A_ID INT IDENTITY(1, 1),
x1 INT,
noise1 int DEFAULT 1,
noise2 char(1) DEFAULT 'S',
noise3 date DEFAULT GETUTCDATE(),
noise4 bit DEFAULT 0);

-- Create random data between the range of [0-3]
INSERT INTO dbo.A(x1)
SELECT s1000.n FROM
( SELECT TOP (10) n = 1 FROM sys.columns) AS s10 -- 10
CROSS JOIN
( SELECT TOP (10) n = 1 FROM sys.columns) AS s100 -- 10 * 10
CROSS JOIN
( SELECT TOP (10) n = ABS(CHECKSUM(NEWID())) % 4 FROM sys.columns) AS s1000; -- 100 * 10

SELECT * FROM dbo.A ORDER BY A_ID DESC;

In my case, the results are:

And running

DBCC CHECKIDENT('A')

returns

  Checking identity information: current identity value '1000', current
  column value '1000'.

Create new table

When we select a subset into a new table, it creates a new current identity value:

SELECT * INTO #temp FROM dbo.A WHERE x1 = 0;

The highest value in the identity column of this new table is 998 and when we check it's next identity:

DBCC CHECKIDENT('#temp')

  Checking identity information: current identity value '998', current
  column value '998'.

How?

How are these identity values inserted into the new table with the highest value correctly set as the current identity value of the new table?

SELECT INTO has two phases (not visible in execution plans).

First, it creates a table that matches the metadata of the query used to create it. This happens in a system transaction, so the (empty) created table will continue to exist even if the SELECT INTO is wrapped in a user transaction that is rolled back. At the end of the first phase, we have an empty table that includes a column named A_ID with the identity property.

The second phase performs an insert into the table the first phase created. This insert is done with identity_insert semantics, so the identity values from the source table end up in the destination, unchanged. The highest value actually inserted is set as the last value used. You can use IDENT_CURRENT or sys.indentity_columns to see it.

Unrelated, but semi-interesting fact: There is a small window between creating the table and starting to perform the insert, where the source table in the query is not protected by a schema stability lock. If a concurrent process happens to change the schema of the source table (after the target has been created, but before the insert starts), error 539 is raised:

  Schema changed after the target table was created. Rerun the Select Into query.


How to create a running balance column based on account number? I have a stored procedure that returns account statement for one or multiple accounts
Here is the part I have issues with: returning a running balance in each record based on account number.
My query doesn't reset the balance for each account, how do I do this??

  SELECT
    acc_account_transactions.account_no,
      COALESCE(debit_balance) as debits
     , COALESCE(credit_balance) as  credits
     , (@running_balance := @running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) as balance
  FROM acc_account_transactions
  JOIN (SELECT @running_balance := 0) r
  ORDER BY account_no

When running this, I get something like this, but the balance doesn't reset on each account

account  debit           credit             balance
13  |2578.19        | 0.00      |  2578.19
13  |1500.00        | 0.00      |  4078.19
13  |1500000.00     | 0.00      |  1504078.19
13  |1500.00        | 0.00      |  1505578.19
14  |1500000.00     | 0.00      |  3005578.19  <--- reset before this
14  |0.00           | 13500.00  |  2992078.19
14  |0.00           | 13500.00  |  2978578.19
14  |33458.43       | 0.00      |  3012036.62
15  |0.00           | 100000.00 |  2912036.62  <--- reset before this
15  |18506.34       | 0.00      |  2930542.96
16  |946.20         | 0.00      |  2931489.16  <--- reset before this
17  |90364.77       | 0.00      |  3021853.93  <--- reset before this
21  |0.00           | 0.00      |  3021853.93  <--- reset before this
22  |0.00           | 0.00      |  3021853.93  <--- reset before this
23  |0.00           | 0.00      |  3021853.93  <--- reset before this
23  |1105500.00     | 0.00      |  4127353.93
24  |1327.00        | 0.00      |  4128680.93  <--- reset before this
25  |0.00           | 0.00      |  4128680.93  <--- reset before this

Thanks

It seems you need in simple

SELECT *, SUM(debit_balance - credit_balance)
             OVER (PARTITION BY account_no ORDER BY id) balance
FROM acc_account_transactions

fiddle


IP address to use with FCI, Always On AG and Always On (AG) FCI My understanding is that HA can be achieved using either of the below and my concern was which IP address should client applications use when connecting to SQL Server:

Failover Clustering: For this setup the windows cluster and assign DNS/IP to this cluster. Then using SQL Server installation media we install SQL Server on a node and add more nodes to the cluster. If primary fails then another node will be brought online and will continue to service request. Am I right to assume that the clients should use the above cluster IP address to access SQL server? And that the Cluster IP address will decide where to direct the request to?
Always on Availability groups: For this setup the windows cluster and assign DNS/IP to this cluster. Then we configure the AG on primary SQL Server and add more instances to the AG. We configure the Listener and give it a DNS/IP address. I know that client apps should use the Listener IP address and that if primary fails then the requests will to auto directed to the secondary by the listener. So what is the purpose of having the cluster DNS/IP address also?
Combine the above - to get Always on Clustering (Clustering+AG): For this setup the windows cluster and assign DNS/IP to this cluster. Then using SQL installation media we install SQL server on a node and add more nodes to the cluster. Then we install a separate new SQL Server instance, add this to the same or separate Windows cluster. Then create an AG on one of the clustered nodes and add the other clustered node and also the newly created separate SQL Server instance into the AG. Here also I assume that client apps should be connecting to the Listener IP address and not the cluster IP address?

For Failover Clustering: ... Am I right to assume that the clients should use the above cluster IP address to access SQL server?And that the Cluster IP address will decide where to direct the request to?

When dealing with a SQL Server FCI, you'll want to use the virtual name provided in the SQL Server setup as the point of connectivity. Do not use the "Cluster Name" as that points to the node owning the cluster resources and not necessarily the SQL Server resource group.

  Always on Availability groups: ... We configure the Listener and give it a DNS/IP address.

Correct, you use the listener.

  So what is the purpose of having the cluster DNS/IP address also?

This is the administrative endpoint for the cluster. You'd use this to deal with cluster settings, since you're just looking at SQL Server there are high chances you'll never actually use it. It doesn't mean it isn't used, just you aren't actively using it with SQL Server as a client.

  Combine the above (FCI + AG): ... Here also I assume that client apps should be connecting to the listener IP address and not the cluster IP address?

Correct.


How to load textfiles into SQL Server Database? Sometimes I have to load csv-files to database. These files might be really huge (> 1TB).

I did not export them to this file and I cannot change anything about the format. The files just "fly in".
In most of the cases the files are seperated by "pipe".
Sometimes text is qualified. Within qualified text there might be "pipes", as well as there might be CR/NL-characters...
Sometimes text is not qualified. Within the text pipes are escaped with an backslash. A backslash might be escaped with another backslash...

The first thing I do to load these files is to get all content changed so that one line for the table is within on line of text.
The row delimiter must not remain within any textfield. I do this job with a commandline script using sed and/or tr.

The next step is to split the file in hany pices (< 50 MB). They would fit into any editor (more confort than less) and I can proceed them parallel.
(For a short time I need double the space of the original file in the filesystem).

Depending on the text formating I convert special characters within the text fields using commandline script using sed, if it gets more complex I use awk.
As the original file is split to hundreds of small files I can do this parallel.
To run (lets say 20) tasks parallel I use another commandline script.

The next step is to load these files into the database. For loading into MS-SQL-Server I use an SSIS-package with a for-each loop over all files
within a filesystem directory. For each file a table is created and the data of this file is loaded. If successfull the file is moved to a subfolder "success",
if an error occured the file is moved to a subfolder "error". So if something would stop the process, you could always restart at a defined point.

I build some copies of this package and filesystem-structures (load-folder_x, load-folder_x/success, load-folder_x/error) to start as many packages in parallel
as my I/O would accept.

When every file is in "success" I am almost done: At least data is inside the database. So I can loop through all the tables and do whatever I can do within a database.
All in all I need about 3 times the size of the original file for filesystem data and database file while the process is running.
A single process from file in filesystem to table in database would run with about a 20 to 30 MB/s. It does scale up to the maximum I/O of the server/disk-subsystem/network-connection.

My question is: How to improve this process?

The used tools are all "handmade", not documented and not so easy to understand for third parties.
errorhandling and logging is all "handmade" ... I guess there is a lot of potential improvement and I guess I am not alone with this kind of tasks.

Certainly you are person who knows a lot of technology tools etc. But I will just point here, that two things are probably worth of research:

Storage of the files on Linux filesystem. From my experience cames that even opening and listing directory with 100 000 or more files is a task which Microsoft cannot do in efficient way. Just using more efficient operating system here may give you at least some ergonomic gains.
Maybe you should research etl tools like Apatar. It works with many files by GUI, so in some cases you may have a visualization of your process, which probably makes it easier to automate or analyse.
It may be surprising in relation to this problem, but on the market there are very efficient data collection tools. Maybe thinking outside the box, and using clustered tools for system log acquisition, like Graylog log server, will be interesting choice. It has pipelining system, which takes data from network port, or through remote agents monitoring particular text files on remote system, and crunch it through various regex collectors, then has ability to divide that streams into output depending on given regex results. Output may be defined by you, and sent to another network device. Even on single machine acquisition of several hundreds of thousands records takes a seconds. It is very, very efficient. I believe at least part of your work may be automated here, for example it may test patterns and produce stream with wrong format entries ( or with good ones). And it gives you some basic statistical informations like dashboard with graphic representation of error types etc.


Will the Query Optimizer Ignore A Fragmented Index? Scenario: I have a heavy OLTP table with an index.  I see many inserts, updates and deletes and the index fragments heavily within a day or less.  While on day one of the index build, the optimizer uses the index, day two or three, the optimizer skips it entirely. This is for exactly the same query.

Question in my head: why would some query plans skip the index, since the index is created to help optimize these plans?

Question for this post: can the optimizer skip a heavily fragmented index, such as a scenario where we have 1 billion records and an index is built, then two hours later, all billion records are removed and we have five hundred million new records?

I'm beginning to think that adding an index to this table won't help at all, because of the nature of the table (data in quickly, data out quickly), but just want to understand why one day one, the optimizer will use the index in its plans, but day two, it won't.

AFAIK the optimizer is not aware of index fragmentation. This can be a problem if it picks a plan that scans a fragmented index.

The optimizer is aware of the allocated data size, though. If the index pages have a lot of free space (possibly due to internal fragmentation) this makes the index less likely to be used. 50% empty space means twice the amount of IO to scan. For random access that should not matter to any significant extent, though.

This is not a huge effect, though. It might explain what you are seeing.

If this small effect flips the query plan to not use the index then the index was never super great in the first place in the eyes of the query optimizer. This might be a hint that you can improve it.

Also, the optimizer seems to have a guess for how much of the index is cached in the buffer pool. There are some references to that in the XML execution plans. I have no detailed knowledge of that.

  I'm beginning to think that adding an index to this table won't help at all

I wouldn't go that far. Maybe all you need is a rebuild or a drop-DML-create sequence in the right places? Or, maybe this is just a query tuning problem (ask a new question with the actual execution plan included).


Setting READ UNCOMMITTED when reading DMVs I've seen several people call SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before reading system DMVs. Is there ever any reason to do this, assuming you aren't mixing calls to DMVs and tables in the same transaction?

As one of the guys writes demo DMV queries that way, I'll explain why.

Does it matter if you're only querying DMVs?  No.  But sooner or later, you're going to take one of your DMV scripts and tack on a join to sys.databases or sys.tables or some other system object in order to get more information about what you're looking at.  If you don't have read uncommitted on there, you can be blocked by other queries, and block other queries.  I've been burned by that repeatedly, so I just use READ UNCOMMITTED by default whenever I'm doing any diagnostic work whatsoever.


Will SQL Server go offline if it loses network connectivity to SAN where master and msdb system databases reside? I have a setup where 3 servers combined into Availability Group
All 3 servers have local directly attached SSD drives, and user database files are hosted on these drives

But the system databases (master and msdb) of each server in AG, are hosted on a SAN device that is accessed over the network

Did not move those to local SSD drives yet
Questions:
In a hypothetical situation where network connection between any of the servers and the SAN device is lost (bad cable, bad NIC, some temporary network glitch etc.),

Will SQL Server service on that server go offline or stop working properly immediately ?

Or it continues to work for some time if master and msdb were cached in RAM before network went down ?

You can't depend on the system databases being cached in memory, more likely they won't be because of their less frequent access relative to the user databases.
I think you'll end up in a quasi-functioning state, where your user databases will still be accessible but certain features of the server instance that rely on master and msdb will throw some weird errors, depending on what else your server is doing. The service for your SQL Server instance, should continue to remain online ("started" state). For example, if you have any Scheduled Agent Jobs, I would bet my money (but couldn't say for sure without testing) they'd encounter errors (either silently or apparently) when trying to run since most of their meta-data is stored in the msdb database.
If such was to occur, you'd be best to restore access to those system databases as soon as possible to guarantee 100% reliability in all features and functions.


Materialized views use older dbms_job instead of scheduler? Very new to this topic - using 11g.

When I create a materialized view, a matching job (not a scheduler job) is created.  But it's my understanding that dbms_job is being replaced by dbms_scheduler.

Am I doing something wrong or am I totally misunderstanding what's going on?

dbms_scheduler, while being much more powerful/flexible than dbms_job, is not a simple/complete replacement for it. dbms_job is still widely used as you have discovered and is unlikely to go away


Trun a query result to argument of EXEC sp_executesql I blatantly stole from the answer here to make this : (this is not the only case I am looking a solution for)

SELECT   'SELECT Top 10 [table] = '''
         + REPLACE(name, '''','')
         + ''', * FROM '
         + QUOTENAME(SCHEMA_NAME([schema_id]))
         + '.'
         + QUOTENAME(name)
         + ';' as SQLText
FROM     sys.tables AS t
ORDER BY name;

The reason was that in the answer the DECLARE @sql NVARCHAR(MAX) was not enough to hold all the tables names, I think we have 120 tables, (or whatever), and the SQL text was being clipped at NVARCHAR(MAX) limit.

Maybe in this case can EXEC sp_executesql SQLText and unionizing them,
but is there a way to turn all the rows into one text and execute that instead?

Is there a limit on the size of how big text of a stored procedure can be?

Please note, I am not after the answer for this specific case, but this is an example of type of problem I am trying to solve.

It wasn't problem with NVARCHAR(MAX) limit for sure. 120 tables isn't a lot and NVARCHAR(MAX) limit is a way above what is needed here (up to 2 GB data), more information link.

I've just checked this solution and it works for me (399 tables).

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql += 'SELECT Top 1 [table] = '''
            + REPLACE(name, '''','')
            + ''', 1 FROM '
            + QUOTENAME(SCHEMA_NAME([schema_id]))
            + '.'
            + QUOTENAME(name) + ';'
FROM     sys.tables AS t
ORDER BY name;

EXEC sp_executesql @sql

If you want to see the whole content of NVARCHAR(MAX) value in SSMS (SELECT and PRINT have limits), you could do something like that:

SELECT @sql AS [processing-instruction(x)]
FOR XML PATH('')

As suggested by @Dan Guzman, more reliable approach would be:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = STUFF((SELECT 'SELECT Top 1 [table] = '''
                            + REPLACE(name, '''','')
                            + ''', 1 FROM '
                            + QUOTENAME(SCHEMA_NAME([schema_id]))
                            + '.'
                            + QUOTENAME(name) + ';'
                     FROM sys.tables AS t
                     FOR XML PATH(''), TYPE).value(N'.[1]', N'varchar(max)'), 1, 0, '');

EXEC sp_executesql @sql


Issue with ROW_NUMBER and DISTINCT I have following query

SELECT Dept
      ,ID
  FROM (SELECT TOP (@RowTo) ROW_NUMBER() OVER (ORDER BY Dept) AS ROWID,
               Dept
              ,ID
          FROM Department
         WHERE Dept LIKE @Team + '%') as Calls
 WHERE RowID >= @RowFrom AND RowID <= @RowTo

I need to get a DISTINCT list of the Departments, but need the paging that this query returns.  I have tried to rewrite this many a times, but I'm failing miserably.  Anyone got any pointers?

Table data

ID    Department  Location
1     HR          London
2     HR          Berlin
9     HR          Paris
11    HR          Amsterdam
12    IT          Berlin
13    IT          London
15    HR          New York
18    IT          Paris
19    IT          Barcelona
20    HR          Barcelona
21    Finance     Paris
22    Finance     London
23    Finance     New York

Using the following SQL:

SELECT Dept
      ,ID
  FROM (SELECT TOP (@RowTo) ROW_NUMBER() OVER (ORDER BY Dept) AS ROWID,
               Dept
              ,ID
          FROM Department
         WHERE Dept LIKE @Team + '%'
         ORDER
            BY Dept) as Calls
 WHERE RowID >= @RowFrom AND RowID <= @RowTo

Returns:

Dept    ID
HR      1
HR      2
HR      9
HR      11
IT      12
IT      13
HR      15
IT      18
IT      19
HR      20

Now, I know that I need to drop out the ID column in order to get a DISTINCT list of values for the Department column.  By doing so, I only get:

Dept
HR
IT

None of the Finance Departments are dropping into the result set because the sub query is returning the TOP 10 records which it's not part of.  This is where I'm no getting stuck.

DISTINCT is evaluated last or almost last of all the clauses. Therefore, it can't be used on the same level as ROW_NUMBER to get the results you want. Either use a derived table with DISTINCT:

SELECT
  Dept
FROM (
  SELECT
    Dept,
    ROW_NUMBER() OVER (ORDER BY Dept) AS RowID
  FROM (
    SELECT DISTINCT
      Dept
    FROM Department
    WHERE Dept ...
  ) AS d
) AS Calls
WHERE RowID ...
;

or replace DISTINCT with GROUP BY:

SELECT
  Dept
FROM (
  SELECT
    Dept,
    ROW_NUMBER() OVER (ORDER BY Dept) AS RowID
  FROM Department
  WHERE Dept ...
  GROUP BY Dept
) AS Calls
WHERE RowID ...
;


Optimizing PostgreSQL where clause with OR I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate SQL for different technologies. The or clause works well in Oracle, for example. I'd like to consider other options besides union that might be less radical if there are any good alternatives.

Here's the minimal setup to make the requirement clear.

create table my_table as
select
    'EXISTING_RECORDS' as s
  , generate_series(10001,30000) as id
  , chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) as name
  , chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) as other_name
;
insert into my_table
select
    'NEW_RECORDS' as s
  , generate_series(1001,3000) as id
  , chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) as name
  , chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) as other_name
;

That create and insert gives us 20k EXISTING_RECORDS and 2k NEW_RECORDS. The name fields are 5 random characters, so we'll end up with a handful of matches when matching based on the following query.

select t1.s, t1.id, t1.name, t1.other_name, t2.name, t2.other_name, t2.s, t2.id
from
    my_table t1
  , my_table t2
where
  t1.s = 'NEW_RECORDS'
  and (
    t1.name = t2.other_name
    or
    t2.name = t1.other_name
  )
;

If I reduce the where clause to just t1.name = t2.other_name or the reverse, then it executes in about 7 ms in my environment. But with the or as shown it takes over 11500 ms.

I posted the explain plan in case that's helpful.

Is there any reasonable pattern I should consider in order to get a good execution plan besides converting this to two separate queries that I union together? For example: I'm happy to add indexes. But as far as I can tell, that doesn't provide any benefit.

Solution:

create index on my_table (name);
create index on my_table (other_name);

Credit: @a_horse_with_no_name

Comment: I am befuddled that I didn't find this myself. I'm sure I tested [multiple] variations of adding indexes, and I don't know how I missed the obvious one. But I'm so happy that I'm giggling, and I'm grateful for the additional eyes on the problem to find this simple workaround.