SQLAnywhere: Rename Foreign Key or Primary Key I have a database with many duplicate index, foreign key, and primary key names. Duplicate index names can be renamed using:

ALTER INDEX <index name> ON <creator>.<tablename> RENAME TO <new name>

So that's fixed.

But keys are not indexes, so it doesn't work on keys:

"Cannot find index named '<index name>'"

There is a constraint rename syntax:

ALTER TABLE <creator>.<tablename> RENAME CONSTRAINT <constraint name> TO <new name>

This doesn't work on keys either:

"Constraint '<constraint name>' not found"

Is there any way to rename a foreign or primary key in SQLAnywhere without dropping and recreating the key?

You don't say which version of SQL Anywhere you are using, but in 16 I think you can use ALTER INDEX to do what you want. From the help:

  ALTER INDEX statement

  Renames an index, primary key, or foreign key, or changes the clustered nature of an index.

  Syntax

  ALTER { INDEX index-name
   | [ INDEX ] FOREIGN KEY role-name
  | [ INDEX ] PRIMARY KEY }
  ON [ owner.]object-name { REBUILD | rename-clause | cluster-clause }

  object-name : table-name | materialized-view-name
  rename-clause : RENAME { AS | TO } new-index-name
  cluster-clause : CLUSTERED | NONCLUSTERED

  Parameters

  rename-clause   Specify the new name for the index, primary key, or foreign key.

  When you rename the underlying index for a foreign or primary key, the corresponding RI constraint name for the index is not changed. However, the foreign key role name, if applicable, is the same as the index name and is changed. Use the ALTER TABLE statement to rename the RI constraint name, if necessary.

  cluster-clause   Specify whether the index should be changed to CLUSTERED or NONCLUSTERED. Only one index on a table can be clustered.

  REBUILD clause   Use this clause to rebuild an index, instead of dropping and recreating it.

  Remarks

  The ALTER INDEX statement carries out two tasks:

  It can be used to rename an index, primary key, or foreign key.

  It can be used to change an index type from nonclustered to clustered, or vice versa.

  The ALTER INDEX statement can be used to change the clustering specification of the index, but does not reorganize the data. As well, only one index per table or materialized view can be clustered.

  ALTER INDEX cannot be used to change an index on a local temporary table. An attempt to do so results in an Index not found error.

  This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots.

Note what it says about using the ALTER TABLE statement to rename the constraint name if needed. If you need more help try the dedicated SQL Anywhere forum at http://sqlanywhere-forum.sap.com/


Can Anyone please review my database after I did the Normalization ?.Any suggestion is appreciated User:
User_ID,user_name,E-mail
Staff: User_ID,Fname,Lname,DOB
Student: User_ID fname Lname DOB
Lecture: User_ID
Admin: User_ID
Position ID : P_ID,ROLE_NAME,
Staff_position :Staff_position_ID,P_ID,user_ID
Programme: Programme_ID P_code P_name
Course: Course_ID**Course_number** C_name C_level User_ID**
Co-programme: Co-programme_id,Course_ID,Programme_ID
Attendence:Attendance_ID,date,time
Attendance_course : Attendance_course_ID,Attendance_ID,Course_ID,date,time
Resources Resource_ID Type,size,Course_ID
Week_resources: Week_resources_ID,Week_number,term,resources_ID
Weekly_upload: Weekly_upload_ID, size,date, time,week_number
Message: Message_ID,user_ID,Body,date,time

Firstly, I must admit that I have been a little disingenuous, I'm afraid: My comment on your question was merely a ruse to trick you into explaining your schema to a degree which would minimize the amount of guesswork that I ( or any other member ) would have to shoulder in attempting to give you feedback of any reasonable quality on your work thus far.  You expertly evaded my trap however, leaving me with no recourse but to rebuild your schema from the ground up, along the way explaining what I think you've overlooked or otherwise omitted, in the context of what I perceive your problem to be.
Secondly, with little to no explanation of the notation you are using, it is difficult to immediately discern the relationships, either data-wise or real, between the entities in your table list: Attributes such as Position_ID show up in the Lecture and Admin tables using the ID notation, indicating a key of some kind, but with no obvious source;  the description for Course is littered with *'s without any indication as to why, further going on to include a User_ID attribute - This latter curiosity is particularly confusing and leads me to believe the model isn't normalized to any sufficient level at all, though I can hypothesize a ( short ) number of reasons why this relationship exists; the purpose of Co-Programme continues to be evasive; just looking at Consist of causes my brow to furrow violently - Unlike the User_ID in Course conundrum, I cannot even begin to fathom what is going on there.
So now that I've gotten all of that off my chest, we'll start by reverse engineering the structure you've provided into a word problem ( which is what I was trying to get out of you in the beginning ):

In the context of an e-learning system, we require a data model which can track our students as they participate in various courses, earning credits towards their programs.  Online courses are administrated by our staff, whom have expressed a desire to allow both themselves and students to upload various digital resources ( such as practice exams in the former case, or homework submission in the latter ) at varying levels of accessibility.  An adjudicator, for instance, would necessarily need to access the homework submissions of a class they preside over, whereas the fellow students of a submitter would necessarily not be able access such a resource.  We would also like to see some form of audit trail on the access of these resources ( though we don't really know why that's important ).

As @ETL points out in his comment, the very first thing to consider is that candidate keys make piss-poor primary keys, which are far better left to the database for auto-generation.  To be a little more clear on this point, despite frequent confusion between the two, there's no such thing as a primary key in relational algebra - this construct is instead referred to as a minimal superkey in model theory.  With that in mind, I'll be applying alternate keys ( also known as unique indexes or unique constraints ) on the minimal superkeys of our tables and letting the primary key be a nice, thin data type like INTEGER.  This practice will save you a lot of headache when foreign keys are involved and the data model needs updating.
"So, Mr. Words-A-Lot, are you going to help me or what?" or some such similar phrase may have indeed crossed your mind several times at this point.  Notice the description of your problem that I created above has a number of bold words.  These words, henceforth called nouns, in one way or another, are going to form the basic structure of your schema.  Some of them have already been realized in your original list, so some of this work has already been done, but for completeness ( and to illustrate good OOAD via identifying objects ), we will touch on them anyway.  Without further ado, here's the general idea of something relatively close to what you'd eventually end up with to solve our little blurb above:

Starting with relational model theory, meaning you can forget about things such as primary keys for now, "Noun-Verb" analysis is as follows:
A Student ( S ) accesses your online system with a ( has a ) Username ( UN ) and a Staff member ( which I'll be calling an Adjudicator ( A ), for my purposes ) has a Username as well.  Through this similarity, I can establish that there is a User ( U ) entity that has a Username ( UN ). ( A: B, C ), meaning entity A is a "container" ( or a collection, later called a table ) with B, C as "attributes" ( or atomic traits, later called columns ).  To make things a little easier on me, we'll expand this notation to also include the minimal superkey in the notation, denoting this ( eventually alternate key ) of a particular container with a subscript Un, where n is merely used to indicate attributes sharing an equal n are part of the same key in that container, separating them from the possible inclusion of an independently unique key, Um, should such a condition be necessary ( protip: It won't be, so don't worry about it ).

User ( U ) has a Username ( UN ), or ( U: UNU1 )

Based on the schema originally presented, I assume these Users ( U ) sign up through email correspondence, indicating there also is a Email ( E ) entity which must be accounted for.  While this is presented as attribute of the User ( U ) container, being a member of the information technology crowd, I can't help but wonder who these people are who only have one email address.  In my experience, it's generally much, much easier ( and preferable ) to simply under-use the capabilities of a robust data model than it is to work around the limitations of one that has been under-designed, so I find myself insisting on a one-to-many relationship between the two.  Email ( E ) thus becomes a container, with an Email Address ( EA ) as an attribute.  To illustrate the many-to-one relationship between Email ( E ) and User ( U ), the latter becomes a "compound" attribute of the former, meaning that the User ( U ) container is treated as an attribute of the Email ( E ) container.  This begins to illustrate where foreign keys will materialize in the final structure, and can be notated as:

( E: EAU1, ( U: UNU1 ) )

Students ( S ) and Adjudicators ( A ) are similar in other ways as well.  With attributes such as First Name ( FN ), Last Name ( LN ) and Date of Birth ( DOB ) in common, from a purely data-centric point of view, it becomes hazy as to whether they are indeed separate entities at all!  There is clearly a need to differentiate the two, or group them into their appropriate roles somehow, but the truth of this is as of yet unclear: Do the requirements dictate that a User ( U ) can not both be a Student ( S ) and a member of the Staff as well?  Does a Teaching Assistant not fill exactly this sort of Position, briefly alluded to in the original schema?
If a Student ( S ) can indeed also be an Adjudicator ( A ), it follows that there is a genuine one-to-one relationship between a User ( U ) and a Student ( S ) and also another genuine one-to-one relationship between a User ( U ) and an Adjudicator ( A ).  That is, a User ( U ) can be a ( is a ) Student ( S ) and a User ( U ) can be an ( is an ) Adjudicator ( A ).  A User ( U ) cannot be multiple of either however, barring clever online trickery.  Note than an Administrator could also be represented in this manner, through from the relative independence from the rest of the relationships in the model, I've omitted such an entity.

( S: ( U: UNU1 )U1 )
( A: ( U: UNU1 )U1 )

We know that both Students ( S ) and Adjudicators ( A ) have a First Name ( FN ), Last Name ( LN ) and a Date of Birth ( DOB ).  As such, we could do something silly, like the following:

( S: ( U: UNU1 )U1, FN, LN, DOB )
( A: ( U: UNU1 )U1, FN, LN, DOB )

But doing so would be in violation of, you guessed it, the Third Normal Form.  In case the reason a specific user shouldn't have two First Names ( FN ) isn't immediately obvious, since both Student ( S ) and Adjudicator ( A ) have a minimal superkey ( again, denoted by U1 ) which consists of the entirety of the minimal superkey of User ( U ), non-prime attributes First Name ( FN ), Last Name ( LN ) and Date of Birth ( DOB ) are transitively dependent on User ( U ).  The correct arrangement is then to instead associate those attributes with the User ( U ) entity, as follows:

( S: ( U: UNU1, FN, LN, DOB )U1 )
( A: ( U: UNU1, FN, LN, DOB )U1 )

The next piece of the puzzle to tackle involves Courses ( C ) and Programs ( P ).  From my understanding, a Program ( P ) is a curriculum offered by a Faculty, which despite probably being the correct word to use, I chose Department ( D ) in my model and am not about to go and change it now.  The Department ( D ) of Mathematics can offer a number of different Programs ( P ), such as a Bachelors, a Bachelors with Honors, a Doctorate in Philosophy or a certification for a Chartered Accountant.  A simple, one-to-many relationship.

( P: PCU1, PN, ( D: DCU1, DN ) )

I imagine that in order to complete a Program ( P ) and win yourself the associated expensive piece of paper, there will be a number of Program Requirements ( PR ) - at least, that's the way I had to do it.  These requirements arise in the form of Courses ( C ), each of which have a ( has a ) Credit Value ( CV ) towards a Program ( P ).  As an aside, I've added Course Level ( CL ) as a compound attribute as well, though it has little value in the context of the model relationships.

( C: CCU1, CN, CV, ( CL: CLCU1 ) )

As not all degrees are created equal, the Program Requirements ( PR ) may offer various choices and a particularly keen individual may opt to take a course in cryptology over one in data structures ( and subsequently get stuck listening to me ramble on the internet - not a desirable fate for the faint of heart ).  To keep Program Requirements ( PR ) flexible, we assume a one-to-many relationship between a Program ( P ) and Program Requirements ( PR ), with each of those requirements having a minimum number of Required Credits ( RC ) to meet the criteria.  The nature of this relationship is different from the other relationships we've seen thus far in that the Program Requirement ( PR ) deviates from the other entities by being a multiset ( there is no reason a Program ( P ) couldn't require 20 credits from two separate Course ( C ) lists ) and thus has no candidate key.  While this reality doesn't affect our model notation in any detrimental manner ( we simply leave the subscript off the attribute list ), in terms of implementation on a database, we'll be using a surrogate key.  If you noticed above, I'm using a surrogate key ( the respective <TableName>_PK attribute ) on every table anyway.

( PR: RC, ( P: PCU1, PN, ( D: DCU1, DN ) ) )

Using this Program Requirement ( PR ) multiset, we can then define Course ( C ) lists which are able to satisfy those requirements.  That is to say, there is a Program Requirement Course ( PRC ) which is able to contribute in some way to the Required Credits ( RC ) of the requirements of a Program ( P ).  This construct allows us to set up a list of courses ( in a three of five case, for example ) or specifically require a list of courses to be completed.  If for some reason a particular Program ( P ) wished to alter the Credit Value ( CV ) of a particular Course ( C ) for its own purposes, an Alternate Credit Value ( ACV ) could be added as an attribute to the Program Requirment Courses ( PRC ) entity, but it's omitted from the diagram above, pretty much because I just thought of it now.  Note that Program Course Requirement ( PRC ) is a many-to-many relationship, allowing a specific Course ( C ) to be a requirement of any number of Program Requirements ( PR ) and Program Requirements ( PR ) to involve the completion of any number of Courses ( C ).

( PRC: ACV, ( PR: RC, ( P: PCU1, PN, ( D: DCU1, DN ) ) )U1, ( C: CCU1, CN, CV, ( CL: CLCU1 ) )U1 )

With the bulk of the work out of the way, it's really a matter of just tying the Courses ( C ) back to the Students ( S ) and Adjudicators ( A ) and continuing on to assert some of the more trivial relationships.  Since the definitions are getting a little long, I'm going to stop showing the attributes of the compound attributes and focus on the final structures we'll need instead.
We create a couple of many-to-many relationships in the form of Adjudicator Course ( AC ) and Student Course ( SC ):

( AC: AU1, CU1 )
( SC: SU1, CU1 )

An Adjudicator ( A ) which can instruct / administrate a Course ( C ) can now perform Lectures ( L ) for those Courses ( C ), designating a Start Time ( T1 ) and an End Time ( T2 ).  This is a multiset in my schema, though it could certainly be instead constrained to time blocks if desired.

( L: AC, T1, T2 )

A Student ( S ) who logs in to attend a Lecture ( L ) can be noted through a many-to-many Student Attendance ( SA ) relationship.

( SA: SU1, LU1 )

A Resource ( R ) may be uploaded by a User ( U ) in the context of a specific Course ( C ).  An Adjudicator ( A ) should be allowed sufficient privileges to view the Resources ( R ) on their own Courses ( C ), whereas a Student ( S ) should only be allowed to see Resources ( R ) for a Course ( C ) as posted by an Adjudicator ( A ) of that course.  Such permissions are hand-waved by my data model as "business logic" to be handled through procedures, views and the application.  To expand on your comment, you could easily add a Resource Type ( RT ) here.

( R: U, C, RD, ( RT: RTCU1, RTN ) )

Finally, a ResourceUser ( RU ) table is added to simply keep track of the times ( Date Accessed ( DA ) ) which a particular User ( U ) accesses a specific Resource ( R ).

( RU: R, U, DA )

All in all, creating the following structure would solve the blurb:

( U: UNU1, FN, LN, DOB )
( E: U, EAU1 )
( S: UU1 )
( A: UU1 )
( D: DCU1, DN )
( P: D, PCU1, PN )
( CL: CLCU1 )
( C: CL, CCU1, CN, CV )
( PR: P, RC )
( PRC: PRU1, CU1, ACV )
( AC: AU1, CU1 )
( SC: SU1, CU1 )
( L: AC, T1, T2 )
( SA: SU1, LU1 )
( RT: RTCU1, RTN )
( R: RT, U, C, RD )
( RU: R, U, DA )


The logical built in place to put this kind of metadata about columns might be in the description box when the column is selected in the table designer.

Behind the scenes this gets saved as an extended property with MS_Description as the property name.

It would be extremely pushing it to claim that this is "standard" or common practice though.


DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format
Thought to get the experts opinion.

Below options to convert / cast didn't work:

CONVERT(VARCHAR(19),CONVERT(DATETIME, wfm.date,3),112)
convert(varchar(50), cast(wfm.date as datetime2), 112)
cast(cast(wfm.date as datetime2(15))as datetime)

Getting the error message for convert syntax as

  Conversion failed when converting date and/or time from character string.

For cast syntax getting the error message:

  Line 1: Specified scale 15 is invalid.

That's very complex. It's easier than that. Just run,

ALTER TABLE foo
    ALTER COLUMN dt datetime2 NOT NULL;

Where dt is the name of your varchar date field. Now you have it in a date field, and you can simply,

SELECT convert(varchar, mydate, 112)
FROM foo;


Postgresql: How to add a role that inherits from another? Pretty cut and dry, I am just a total noob. Is there a way to make a given role inherit from another?

CREATE ROLE doe LOGIN INHERIT;
CREATE ROLE sales NOINHERIT;
CREATE ROLE marketing NOINHERIT;
GRANT sales to doe;
GRANT marketing to sales;

If you connect to PostgreSQL as doe, you will have privileges of doe plus privileges granted to sales, because doe user role has INHERIT attribute. However, you do not have privileges of marketing because the NOINHERIT attribute is defined for the sales user role.
Note:
The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are.


Relation Between Normalizations say we have a fixed table T. Is there a unique normalized version of T,
i.e., does the/a correct normalization, say up to 3NF produce a unique table up to
some equivalence? Basically, I am trying to find out how many different correct ways of normalizing a table there are.
Thanks.

There are often more then one ways to normalize a relation. An example:

Relation R (ABCD) with functional dependencies:

A -> BCD
B -> CD
C -> BD

Can be normalized to 3NF either:

R1 (AB)     A -> B

R2 (BCD)    B -> CD
            C -> BD

or:

R1 (AC)     A -> C

R2 (BCD)    B -> CD
            C -> BD

or:

R1 (AB)     A -> B

R2 (BC)     B -> C
            C -> B

R3 (BD)     B -> D

or: lots of other ways ...


Innobackupex: How to extract individual files from xbstream compressed backups We are scripting a mix of full and incremental backup setup for our prodcut backend with sunday triggering full backup and incremental backup on all the remaining days.

In this process, we should be able to read the LSN number of a file from the full backup file.

We have setup the script to compress the backup into xbstream format so it would result one single file compressing all the data.

Now the question is how to extract or read the LSN or one single info file from the complete xbstream file.

Though we can use log file for this, we wanted a more dependable solution.

Thank you.

XtraBackup logs the most recent LSN not only in xtrabackup_info which you'll have to extract, but to STDERR, too:

150731 16:33:28  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
150731 16:33:28  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1948062848'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1948062848)

The LSN would be 1948062848. Find string xtrabackup: The latest check point (for incremental): in the output. See http://bazaar.launchpad.net/~twindb-dev/twindb-agent/trunk/view/head:/twindb.py#L1888 for example:

def grep_lsn(output):
    """
    Finds LSN in XtraBackup output
    :param output: string with Xtrabackup output
    :return: LSN
    """
    lsn = None
    for line in output.split("\n"):
        if line.startswith("xtrabackup: The latest check point (for incremental):"):
            lsn = line.split("'")[1]
    return lsn

Then, pass 1948062848 with --incremental-lsn= option when you take an incremental copy. Example: http://bazaar.launchpad.net/~twindb-dev/twindb-agent/trunk/view/head:/twindb.py#L1429

if backup_type == 'incremental':
    last_lsn = job["params"]["lsn"]
    xtrabackup_cmd.append("--incremental")
    xtrabackup_cmd.append(".")
    xtrabackup_cmd.append("--incremental-lsn=%s" % last_lsn)


Imported database file size different with original database file I am using SQL Server 2000 Data Transfer Service (DTS) wizard for import database from another existing SQL Server 2000 database. I also choose "Copy objects and data between SQL Server databases" option when import.

After import finished, I saw that size of new imported database are smaller than original database. Is this normal? I worried if that any object are not imported to new database, such as stored procedure or view.

How can I make sure if the new database's object are same with old database?

Well, you created new empty tables and inserted all of the data into those tables in one shot. This means that the new tables do not have any of the fragmentation and page splits that would have happened as the original database was populated much more gradually and probably was subjected to a lot of updates and deletes. Hence, the same data will fit on a potentially much smaller number of pages, since the data is much "cleaner."

You can use a tool like Red Gate SQL Data Compare, if it still supports SQL Server 2000. There are alternatives if you search, of course, and you could write your own scripts to do it if you're really budget-conscious, but please read this first. Any of these tools (again, that support 2000) can help ensure that all of the data made it. Assuming, of course, that the source data hasn't changed substantially since you moved the data.

In all honesty, though, I would much rather perform a backup and restore than rely on all this stuff - even if it means I have some wasted space in the copy.


PostgreSQL error: OperationalError: FATAL: role "username" does not exist I got this error When I'm trying to run the Code.

I searched the site like this problem

But It couldn't solve my problem.

When I'm trying to create superuser with my username (which is TCOYUKSEL) , It create another one with "tcoyuksel"

ozgur.py Line 9  db = psycopg2.connect("dbname=news")

ozgur.py Line 73 get_pop_articles(), str("views"))

init.py  Line 130    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)  --I didn't do anything with this file--

The role name is case sensitive, but with your queries it gets translated to lower case tcoyuksel,

CREATE ROLE TCOYUKSEL superuser;
ALTER ROLE TCOYUKSEL WITH LOGIN;

as key words and unquoted identifiers are case insensitive (4.1.1. Identifiers and Key Words).

  There is a second kind of identifier: the delimited identifier or
  quoted identifier. It is formed by enclosing an arbitrary sequence of
  characters in double-quotes ("). A delimited identifier is always an
  identifier, never a key word. So "select" could be used to refer to a
  column or table named “select”, whereas an unquoted select would be
  taken as a key word and would therefore provoke a parse error when
  used where a table or column name is expected.

You need to use the quotes to bypass the normalization i.e. use TCOYUKSEL as delimited identifier:

CREATE ROLE "TCOYUKSEL" superuser;
ALTER ROLE "TCOYUKSEL" WITH LOGIN;

If you run it directly from the command line, you need to double the quotes:

postgres "CREATE ROLE ""TCOYUKSEL"" superuser; ALTER ROLE ""TCOYUKSEL"" WITH LOGIN;"


How can I install a provider for linked server? I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database.

I would like to use this same provider on a sql server 2012 machine.

Is there a way to transfer this provider from the 2005 to the 2012 machine?

The 'provider' is the driver required to allow SQL Server to talk to the remote server. These are normally installed pieces of software, so can't simply be copied between machines.

While not being overly familiar with PervasiveDB, I would assume that like most other manufacturers drivers, it will be included in their client release, which is available for download here - http://www.pervasivedb.com/psqlv11/pages/default.aspx

Once you've installed this, then you can build your linked server connection. Within SSMS you can right click on your existing Linked Server and make a create script which you can use on your SQL2012 instance. Though be warned that this won't copy all the security details (passwords in particular), so you may need to look this up in the documentation, or have a new user created in the target database.

Sometimes you can have issues with different versions of client software, differing client setup or not getting the permissions identical on the rebuild. You can avoid this in future (or make someone's life a lot easier in the future) by documenting everything as you set it up.


Apply procedures in multiple databases I have reach a support line that gave me a T-SQL Script for one DB that I now need to apply many more times.

The original script goes like:

IF EXISTS  Procedure
THEN DROP
GO

CREATE PROCEDURE [dbo].[Procedure]
...
END
GO

exec Procedure 'With Arguments'

What I'm trying to do goes like:

DECLARE @db_name VARCHAR(50) -- database name
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
where create_date > DATETIMEFROMPARTS(2017,12,27,0,0,0,0)
AND create_date < DATETIMEFROMPARTS(2017,12,30,0,0,0,0)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE('USE ' + @db_name)
    exec Procedure 'With Arguments'
    FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor

But I' m not getting the expected results. I believe that is because I'm not creating the procedure inside each DB.
What is the clean way to do the "If Exists" and "Create Procedure" repeatedly?

I also used :

USE master
CREATE PROCEDURE sp_procedure AS ...

and

USE aDB
exec sp_procedure 'With Arguments'

But it says sp_procedure does not exists...

I'm working with windows SQL server 2014

Help appreciated.

EDIT:
The end code after reading all the answers and marking as System was:

...
BEGIN
    SET @CMD = 'exec ' + @db_name + '.dbo.sp_procedure ''With Arguments'';';
    exec sp_executesql @CMD;
    ...

I believe that is because I'm not creating the procedure inside each
  DB. What is the clean way to do the "If Exists" and "Create Procedure"
  repeatedly?

If I understood you correct, you need to execute the same procedure in many databases.

For example, it can be a procedure that makes a script of all the indexes of the specific table, in this case the procedure is always the same but it should be "local" respect to database where the table resides, this way you can use sys.indexes/sys.columns that are "database sensitive".

The simplest way to do this is to make your procedure "system" procedure, i.e. it will be created in master database, it will have sp prefix and marked as system, so that it can be called from any database and will always use system tables "local" to the database from which it was called.

To accomplish this you should create your procedure only one time in master database as you did:

USE master
CREATE PROCEDURE sp_procedure AS ...

And mark this procedure as system one:

exec sys.sp_MS_marksystemobject 'sp_procedure'

Noe when you run your code like this

USE aDB
exec sp_procedure 'With Arguments'

You will not get

  sp_procedure does not exists

anymore.

........................................................

If you still prefer to create this procedure many times in every database the simplest way to do it is to save your procedure script in a file including the rows

IF EXISTS  Procedure
THEN DROP
GO

And then launch this script from every database:

sqlcmd -S <ComputerName>\<InstanceName> -d <dbname> -i <MyScript.sql>

(you should add -U MyLogin -P MyPassword if you don't want to use Integrated Security).

You should format this string dynamically in your cursor addind dbname like this:

set @cmd = 'sqlcmd -S <ComputerName>\<InstanceName> -d '+ @db_name + ' -i <MyScript.sql>';

exec master.dbo.xp_cmdshell @cmd

.................................................................

Otherwise, you should put the whole text of your sp (create proc...) in a string variable and execute it in your cursor:

declare @sp varchar(max) = cast(' CREATE PROCEDURE ' as varchar(max)) + '[dbo].[Procedure].....'

declare @sql varchar(max) = 'USE ' + @db_name + @sp
EXECUTE(@sql);


Load PostgreSQL database from corrupt drive I suffered corruption of a hard drive but managed to recover files for a PostgreSQL server. We are running on the assumption that the files themselves are not corrupted (yet to be able to tell). My server was running on Windows 10 Pro.

Is it possible for me to load the database(s) onto another PC running the same server version? I have the entire directory recovered from "Program Files/PostgreSQL/11" onward (so I have all of "data" etc).

If so can someone guide me on how to do this, or direct me to existing guidance? I really need to recover this data and sadly with the above corruption I lost the drive containing my dump files (backups).

Can I simply copy the "data" folder into a new build, or is it a bit more complex than that?

Theoretically, your database does not have problem, if it is running.

But to verify it, first Stop your database, make a physical copy of PostgreSQL folders.

After that, start your database make an ordinary backup using pg_dump, after that try to restore this backup in a new environment, if you don't have a problem your database is ok.

If you have some problem I will share a link with the main problems of corruption and how to solve it.

https://stackoverflow.com/questions/11030173/repair-corrupt-database-postgresql

But analyze all of the possibilities before starting this process does not exist the only way to resolve this problem.


How do I write backup procedure I've read that a decent backup strategy would be:

1) Full backup weekly
2) Differential backups daily
3) Log backups every 5 minutes.

Now question is how do I write this stored procedure and create a sql job out of it. For eg. For differential backup, it is necessary that full backup must exist. Now when it is Monday 12 AM and it is time for both weekly and differential backup to run. It is possible that differential backup runs before full backup. How do I handle these tricky conditions? Could anybody please share the solution for this?

First, I would recommend you go get Ola Hallengren's backup scripts.  They are full featured and well documented, supporting exactly the type of operations you are looking to complete.  This saves you a lot of effort of writing your own scripts from scratch and the solution is recommended by many notables in the community.

Second, I would create SQL Agent jobs to call the backup stored procedures, with the following jobs:

Full backup job that runs weekly, creating full backups for all databases.  For argument's sake, this is at 2 AM Sunday.
Differential backup job that runs on all days other than the day of the full.  This would be 2 AM Monday-Saturday.
Log backup job that executes every 5 minutes.

This is a very basic and general schedule and you will probably need to tweak and adjust it based on your environment.  The largest area of adjustment will probably be how often you run your log backups.  Depending on the size, activity, the number of databases in FULL recovery mode, and the fact that this approach backs up your databases serially (one after the other), all log backups on your instance may not complete in 5 minutes.  There are several ways to handle this, such as increasing the window between log backups and creating different backup jobs for different databases (so you can have them execute in parallel).


Microsoft SQL Server 2008 Memory I am running MSSQL 2008 Enterprise and in the configuration of the server the Max memory amount is set to 2147483647 MB.
My Question is, the server has 192 GB of ram,the number in the config can't be increased, if the config is set to that, does that mean the server can use whatever it needs? Or how can i enable it that the server uses all the ram that is available.

If i open the task manager, the memory usage never goes above 19 GB.

Thank you for the help

Knowing this is 64-bit SQL Server 2008 Enterprise simplifies the discussion, so thanks for that.  The first thing to say is that it is almost never right to leave the max server memory configuration option at the default of 2147483647.

The max server memory option only limits the size of the buffer pool (used for all single-8KB allocations pre-2012).  SQL Server can also require significant amounts of non-buffer pool memory (allocations > 8KB).  Other instances, applications, and the OS also need room to work (and 2GB is not always a good rule of thumb there).  Amit Banerjee, an Escalation Engineer with Microsoft PSS has three excellent posts on this topic, which I encourage you to read.

You will generally want to set max server memory to a fairly conservative maximum value to start with, monitor the actual amount of free memory on the server over a full load cycle, preferably over a decent period of time, and gradually increase it as appropriate and necessary.

The exact level to start at depends on how much non-buffer pool memory SQL Server might be expected to require, and how much memory will be needed by other instances, applications and the OS.  On a server dedicated to a single instance of SQL Server, I might start max server memory around 160GB with 192GB installed.  Your circumstances might dictate that a much lower starting point is indicated.

I also recommend you consider using the Lock Pages in Memory (LPIM) option (that link explains why in great detail).  This will be enabled by default if the SQL Server service is running as LocalSystem (which is not a best practice).  See this CSS article for details including how to tell if SQL Server is currently using LPIM or not.

Memory used via this mechanism is not visible to Task Manager; it is better to monitor SQL Server memory usage via Performance Monitor counetrs and/or the sys.dm_os_sys_memory DMV.


What is a suitable index for this query? Below query is taking long time - around 540 seconds.
The table did not have any indexes. I created indexes on columns in the WHERE clause and other fields but the query is not using these indexes.
Please suggest good indexes for this query.

Query:

select
    alias_to,
    fake_uri,
    concat(ifnull(parameters,''),ifnull(parameters2,''),ifnull(parameters3,'')) as parameters,
    precedence,
    template_name
from site_map_template
order by precedence, fake_uri desc;

Explain output:

+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | site_map_template | ALL  | NULL          | NULL | NULL    | NULL | 7616 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

site_map_template table:

CREATE TABLE `site_map_template` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `template_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `alias_to` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `package` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `fake_uri` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `precedence` int(11) DEFAULT NULL,
  `parameters` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `parameters2` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `parameters3` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  KEY `idx_1372` (`template_name`),
  KEY `idx_n1` (`precedence`),
  KEY `idx_n2` (`fake_uri`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

Using SQL Server 2012 (hopefully there is an equivalent in MySQL):

CREATE TABLE dbo.site_map_template
(
  row_mod datetime NULL,
  row_create datetime NULL,
  template_name varchar(50) COLLATE Latin1_General_BIN2 NULL,
  alias_to varchar(100) COLLATE Latin1_General_BIN2 NULL,
  package varchar(50) COLLATE Latin1_General_BIN2 NULL,
  fake_uri varchar(255) COLLATE Latin1_General_BIN2 NULL,
  precedence integer NULL,
  parameters1 varchar(255) COLLATE Latin1_General_BIN2 NULL,
  parameters2 varchar(255) COLLATE Latin1_General_BIN2 NULL,
  parameters3 varchar(255) COLLATE Latin1_General_BIN2 NULL
);

A good clustered index for this query is:

CREATE UNIQUE CLUSTERED INDEX PK__site_map_template_precedence_fake_uri
ON dbo.site_map_template (precedence, fake_uri DESC);

Or, a nonclustered index:

CREATE NONCLUSTERED INDEX nc1
ON dbo.site_map_template
    (
    precedence,
    fake_uri DESC
    )
INCLUDE
    (
    alias_to,
    parameters1,
    parameters2,
    parameters3,
    template_name
    );

The query (again, in SQL Server syntax) is:

SELECT
    alias_to,
    fake_uri,
    [parameters] = CONCAT(parameters1, parameters2, parameters3),
    precedence,
    template_name
FROM dbo.site_map_template
ORDER BY
    precedence,
    fake_uri DESC;

And the execution plan is a simple scan of the index:

Or, using the nonclustered index:

The important thing in both cases is that each index covers all the columns needed by the query, and the index keys are precedence ASC and fake_uri DESC. Without the descending second key, a full sort would be required, and this is likely the cause of your slow performance.


What are the compelling resons to update SQL Server name to a real one after its changing? Took the job of monitoring and tuning a few dozens SQL Server 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed. running inside virtual machines.

All of them on running

select @@servername
select * from sys.servers

return  the same machine autogenerated during Windows setup name WIN-MIQD9ME78FK.

These production servers works over 2 (probably much more) years.

As far as I can guess the hosts' name were changed after installing SQL Server.

What are the compelling reasons for updating SQL Servers name for its real host names?

And which surprises should I be ready for after such changes?

If you aren't using SQL Server Replication or remote jobs (MSX Jobs) then it really doesn't matter all that much.  It's a bit annoying when the server names don't match when you run select @@servername, but it's really not that big a deal for the most part.


Aggregate data from multiple rows into single row In my table I have rows, and each row has some data and its version. When column is not-null, it overrides an old value otherwise nothing happens.

Pseudocode (in C#)

var resultTable = new List<Row>();
foreach (var group in table.GroupBy(x => x.ID).Select(g => g.OrderBy(x => x.Priority)))
{
    var row = Row.GetEmpty();
    foreach (var anotherRow in group)
    {
        foreach (var column in group.Columns)
        {
            if (column.GetValue(anotherRow) != null)
                column.SetValue(row, anotherRow);
        }
    }
    resultTable.Add(row);
}

Table is large enough (several tens of columns, several hundreds of thousands rows), so it's desired to do it in single table scan (if possible).

I appreciate any help and any possibility to achieve it without any bias, SQLCLR, DSQL and so on, everything is applicable if it works fast and makes its job.

Current script for a test table:

CREATE TABLE [dbo].[T](
    [id] [int] NOT NULL,
    [A] [int] NULL,
    [B] [int] NULL,
    [C] [int] NULL,
    [Priority] [int] NOT NULL
) ON [PRIMARY]

GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20160204-122857] ON [dbo].[T]
(
    [id] ASC,
    [Priority] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, NULL, 3, 4, 1)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, 5, 6, NULL, 2)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, 8, NULL, NULL, 3)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (2, 634, 346, 359, 1)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (2, 34, NULL, 734, 2)
GO

input:

id  A       B       C       Priority
1   NULL    3       4       1
1   5       6       NULL    2
1   8       NULL    NULL    3
2   634     346     359     1
2   34      NULL    734     2

desired result:

id  A   B   C
1   8   6   4
2   34  346 734

In this example table is small and has only 5 columns, but in real table it will be much larger. I really want this script to work fast. I tried do it myself, but my script works for SQLSERVER2012+ so I deleted it as not applicable.

Numbers: table could have 150k of rows, 20 columns, 20-80k of unique ids and average SELECT COUNT(id) FROM T GROUP BY ID is 2..5

Itzik Ben-Gan's blog post (link provided by Daniel Hutmacher) has some nice solutions to the problem for SQL Server 2012 and later:

The Last non NULL Puzzle.

Here is one more that will work in older versions (even 2005) and can be easily adapted for more columns. I haven't tested for efficiency or compared to the other solutions but I'd expect it to work better if the table had a clustered index on (id, priority). But performance may depend on many other things, like the distribution, the number of distinct id values, the number of versions (rows per id values), the number and types of columns, etc.:

SELECT
    d.id,
    d1.col1,
    d2.col2,
    d3.col3
FROM
    ( SELECT id, @point_in_time AS priority
      FROM tablename
      GROUP BY id
    ) AS d
  OUTER APPLY
    ( SELECT TOP (1) col1
      FROM tablename
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col1 IS NOT NULL
      ORDER BY priority DESC
    ) AS d1
  OUTER APPLY
    ( SELECT TOP (1) col2
      FROM tablename
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col2 IS NOT NULL
      ORDER BY priority DESC
    ) AS d2
  OUTER APPLY
    ( SELECT TOP (1) col3
      FROM tablename
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col3 IS NOT NULL
      ORDER BY priority DESC
    ) AS d3 ;

Test in SQLfiddle.

The @point_in_time above is a parameter, that can be removed if we want the latest non-null values across all the table.

If however, we want the latest non-null values for a specific point in time (say for when priority=55), we can replace that @point_in_time with 55 and have the latest non-null values up to that point in time (55).


Inserting values into a column when Table1 column A value matches Table 2 Column B Value I have two tables ImportData and PurchaseInfo. I am trying to import [Supplier Contact Name] from ImportData to PurchaseInfo, when *[SupplierName] in ImportData = *[SupplierName] in PurchaseInfo

This is the query I tried:

INSERT INTO PurchaseInfo ([Supplier Contact Name])
SELECT [Supplier Contact Name]
FROM ImportData
WHERE PurchaseInfo.[Supplier Name] = SupplierInfo.SupplierName

I get the error

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "PurchaseInfo.Supplier Name" could not be bound.

Any help would be greatly appreciated.

Taking a guess that Vinny wishes to update the PurchaseInfo table, and that SupplierInfo is same as ImportData

UPDATE PurchaseInfo
SET [Supplier Contact Name] = S.[Supplier Contact Name]
FROM ImportData I JOIN PurchaseInfo PI ON PI.[Supplier Name] = I.SupplierName


Normalization from UNF to 3NF I keep getting confused trying to normalize this data.

A company makes and sells a product online. There are ten types of Products, each having a code and price.

Their Sales Order department takes customer information (name, address, trimmer product code and purchase quantity) from a simple email posting.

The products are shipped to the customer and the shipments logged into a delivery book, giving customer name and address, shipper (Parcel force, TNT, ANC, Overnite, Royal Mail), date shipped, quantity and product code.

Products are sold on a 30 day return policy, so after one calendar month of shipping an invoice is sent to the customer. The invoice details each product line supplied, unit cost of each line and sub-total, as well as the shipment date and the overall invoice cost (grand total).

Customer payments (cheque numbers) are logged and matched against a copy of the invoice before banking. Every month the log is searched for outstanding debts and these are compiled into an outstanding invoice report.

So I've created some data to help me understand:

This data has helped me alot when it comes to understanding what I need to do in terms of my final database, but I'm finding normalizing it tricky as I've got so much information. So I came up with this:

I would appreciate it if someone could give me some feedback and advice as this attempt feels wrong.

The simplest way to think about normalizing data is that you want to get to where all the data in the table is either your primary key or dependent on the primary key.  It's also important to know how your entities relate to each other, because you'll have a different model for one-to-many versus many-to-many relationships.

I think the relationship modeling is where you've gone wrong. You're moving every relationship into its own table, which is not appropriate for a one-to-one or one-to-many relationship.  For instance, unless a single order can belong to multiple customers, the Customer_Order table is not the correct way to model this.  What you'd want instead is a CustomerNo column in your Order table, with the CustomerNo as a foreign key to the Customer table.  That identifies the customer who placed the order.

Same thing with your shipping info.  A table that contains only ShipperNo and DateShipped doesn't do you any good.  If the entire order must go out in a single shipment, then adding ShipperNo and DateShipped to the Order table would work.  If an order may be split into multiple shipments, then I'd create a Shipment table that contains (ShipmentNo, OrderNo, ShipperNo, DateShipped), as well as a ShipmentItems table that links the Shipment to the actual products from the order that were sent in that shipment.  Now the shipments that resulted from an order can be identified by selecting all Shipment rows where OrderNo matches the order you're viewing.

Your Invoice_Order table is appropriate if you may issue multiple invoices for a single order, else I'd just add OrderNo to the Invoice table as a foreign key.

Is that enough to help you see the general pattern?  You've done a good job separating your entities, if we can get you over the hump on modeling the relationships between the entities.


Rebuilding a spatial index with minimum downtime We just upgraded from SQL Server 2008 to 2014.
It went fairly well except for a problem we have on a spatial index.
On this table we receive the error

  Cannot insert duplicate key row in object 'sys.extended_index_1527780600_384000' with unique index 'lu_unit__geolocation'. - duplicate key value:(0x20330a3504, 95469304).

A spatial index can't have a unique constraint, something is wrong.

I would think the safer way forward is to rebuild the index. I experimented a bit and I find it takes about 50s to rebuild the index on 1.6 million rows.
The production table is about 5.5 million rows, making it at least 3min when the base table won't be accessible since a spatial index can't be built online.

Does anybody have experience with rebuilding spatial indexes with minimum down time? We can do with 30s but not 3min.

Concept of this solution is to prepare copy of the data with the same structure and swap with origial table using sp_rename. It is preety the same for other big changes in a table structure.
Let's create table and populate it with geometry data.
CREATE TABLE SpatialTable (id int IDENTITY(1,1) primary key, geometry_col geometry);
CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col1
   ON SpatialTable(geometry_col)
   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );
GO

INSERT INTO SpatialTable (geometry_col)
SELECT TOP 1000000 geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
FROM sys.all_columns a,sys.all_columns b;
GO

Creating copy of the data and preparing appropriate indexes. Those operations don't harm original table and should be fast except spattial index creation.
SELECT * INTO SpatialTable2 FROM SpatialTable
GO

ALTER TABLE SpatialTable2 ADD CONSTRAINT PK_SpatialTable_Id PRIMARY KEY CLUSTERED (id);
GO

CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col1
   ON SpatialTable2(geometry_col)
   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );
GO

Now let's simulate some insert on the base table.
INSERT INTO SpatialTable (geometry_col)
SELECT TOP 1000 geometry_col FROM SpatialTable

Below is the trick which should take only short time.
BEGIN TRANSACTION

    SET IDENTITY_INSERT dbo.SpatialTable2 ON

    INSERT INTO SpatialTable2 (id,geometry_col)
    SELECT id,geometry_col FROM SpatialTable sp WITH (TABLOCKX)
    WHERE NOT EXISTS (SELECT ID FROM SpatialTable2 sp2 where sp2.id=sp.id)

    SET IDENTITY_INSERT dbo.SpatialTable2 OFF

    EXEC sp_rename 'dbo.SpatialTable', 'SpatialTable_old';
    EXEC sp_rename 'dbo.SpatialTable2', 'SpatialTable';

COMMIT TRANSACTION

Now we have new table and new spatial index structures with the old data. Of course it is only proposition of steps and you should apply it to your requirements and data structure, especially UPDATE and DELETE operations if they occur in your case.
After this operation you can drop the old table to clean database or use it for any other purpose.


T-SQL Query runs quickly on local PC, but slow on SQL Server? I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters.

It's been running without issue for months - but has suddenly started running very slowly on the server (4 core, 10GB RAM, disks on fast SAN)

If I run the query locally on my PC (2 core, 2GB RAM, one local disc) then it takes approx 1:20 to run.

Prior to this week, run on the server took approx 0:15 to run.

This week, run on server taking approx 12:30 to run!!!

This is a massive difference, and I have no idea what to start looking for to resolve the issue.

What I HAVE noticed is that this does not seem to be getting cached...OR something is hogging/not releasing cache.

e.g.Run this locally, 1:20...run it again immediately - almost instant.
Run on server 12:30...run it again immediately - 12:30 again?

Any suggestions very gratefully received!

A few of the most common scenarios for occasionally slow, but usually fast, SELECT queries:

A suboptimal plan is being used - perhaps due to bad stats, parameter sniffing, or other factors. In your case, seems unlikely that it would be parameter sniffing, if you really mean both no explicit parameters and no literals (e.g. hard-coding WHERE x = 1 is still parameterized in most cases). In order to make any useful guesses about this, you'll need to supply actual execution plans after the successful completion of one of the "slow" versions of the query.
Your query is getting blocked - again, this is not likely to be your issue either, since you say you are the only one working on this database. But to determine if you are being blocked, when you observe the query is running slow, do this in one window:

PRINT @@SPID;

Once you have that output, run the slow query in that same window:

SELECT ... query that runs slowly ...

Now, in a new window, run this:

SELECT command, status, wait_type, last_wait_type, blocking_session_id
  FROM sys.dm_exec_requests WHERE session_id = <@@SPID FROM ABOVE>;

Make note of the wait type (see below), but if blocking_session_id is not 0, check what that user is running (run the query above again, changing the session_id to the blocker).
Some other resource is bottlenecked - it could be that at certain times the underlying disk that the database is on or the network you're moving data across can be under extreme pressure, and this will filter down to just about every operation that uses them. This could also be true if your SQL Server is on a virtual machine and the underlying host is under some kind of resource pressure.

In addition to the execution plan you get from the slow version of the query, you should examine the waits that are experienced while your query is running. This is beyond the scope of this localized answer, mostly because it is a very lengthy topic to digest, but you can start with this white paper from Microsoft.

The free execution plan analysis tool, Plan Explorer, tries to make problems with a given execution plan a lot more obvious than it will ever be in Management Studio. As a bonus, the paid version, which you can trial for free for two weeks, also gives details about the exact waits that your session experienced - this is pretty cumbersome to do on your own. Sorry about the ad but it really can be a time-saver that helps isolate the real problem behind a particularly nasty query.


Page Lookups Percentage - SQL Server performance monitoring I'm currently doing some work on monitoring SQL Server performance.  I've found the following script[1] which calculates 'Page Lookups Percentage' - the suggestion being that a "good" value is something less than 100.

SELECT 1.0*cntr_value /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec')
AS [PageLookupPct]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec'

Now I've run this script on about a dozen different servers - mostly virtual but a couple physical, mostly 2008R2 Enterprise but a couple of 2005, some live and some development, and even a physical 2008R2 Express edition that has only ever been a witness server and is currently dormant (no mirrors set up with active failover).  On only one of these servers has the value been below 100. In most cases it has been in the 1000's or at least in the mid 100's.  On the dormant witness server the value was a ridiculous 20,000 !

Does this indicate that we have widespread problems in this area, or is there some innocent explanation for why this figure should be so high on so many different servers?  We are experiencing slow performance on some of these servers, so it would be useful for me to get a better understanding of what this figure is telling me.

[1] - http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/

The metric is trying to measure the number of logical IO operations performed by each request, in average. If your server is, lets say, serving a busy web site, then is probably a good metric (perhaps 100 is even too high for such!).

The witness will only have internal activity. In other words it will touch data files internally, due to various background processes, despite never seeing any actual batch. As such all internal logical IO will be reported as caused by the few batches this server has seen.

I would say that the metric Thomas is recommending has some value but only in a a workload similar to what Thomas had in mind. A server that has only background activity, like your witness, is not going to report anything meaningful with this ratio. A server that runs intensive DW reports may see only 1 batch request per hour and run millions of page lookups for that one request. An absolute number like 'must be below 100' is only valuable accompanied by smallprint disclaimers like must be bellow 100 if your workload has frequent enough batch request to establish significant statistical trends and each batch requests is expected to perform under 100 page lookups in average. You get the idea...


Array exceed allowed size in Postgres I have a function that accepts as parameter an array like:

CREATE OR REPLACE FUNCTION my_func(some_data custom_datum[]) RETURNS VOID AS $$
    BEGIN
        create table foo_table as (

           select
           coalesce(foo_ind, bar_ind, ter_ind) as foobarter,
           import_date,

           -- do some stuff here

           from unnest (some_data) as T
           group by grouping sets ((foo_ind, import_date), (bar_ind, import_date), (ter_ind, import_date))
        );
    END
$$ LANGUAGE plpgsql;

The input array is generated by another function foo. So I call everything in this way:

select my_func(array(select foo()));

where the function foo is:

CREATE OR REPLACE FUNCTION foo() RETURNS SETOF custom_datum

The problem is that for a big amount of data array(select foo()) returns:

  ERROR:  array size exceeds the maximum allowed (1073741823)

What I am trying to workaround is the lack of possibility to pass different functions, as at the moment, the input array gets generated by different functions:

select my_func(array(select foo()));
select my_func(array(select bar()));
select my_func(array(select ter()));
.... etc

How can I workaround this problem?

You can possibly limit the output of foo():

SELECT foo() LIMIT 10000;

Or, most probably better, avoid using arrays altogether (pass the set, not an array made of it).  For this, you will, of course, have to rewrite my_func().  Thinking about it, I can imagine that this will be beneficial from a performance point of view, too.


two-table-over  functional relationship: maintaining referential integrity I have three tables, the last two having a many to one relationship with the first, written here in SQL:

create table study (
    id bigint primary key,
    name text
);

create table treatment (
    id bigint primary key,
    name text,
    study_id bigint references study
);

create table visit (
    id bigint primary key,
    name text,
    study_id bigint references study
);

These are brought together into a session which ties a person assigned a treatment on some visit in some study like so:

create table session (
    id bigint primary key,
    person_id bigint references person,
    study_id bigint references study,
    visit_id bigint references visit,
    treatment_id bigint references treatment
);

What is the best way to make sure that for any row in the session table, the study_id, the visit_id, and the treatment_id all belong to the same study? I am working in a Postgres 9.x database. Check constraints can't refer to other tables, so maybe a trigger before insert and update?

You add a (redundant) unique constraint on (study_id, id) on both the treatment and visit tables. Then let the foreign keys from session refer to these unique constraints by including study_id into the respective foreign keys.

Then your condition will automatically be satisfied.

The unique constraints are not quite as superfluous as they seem, because the foreign key constraint on study_id should be indexed anyway.

To put it into SQL:

ALTER TABLE treatment
   ADD UNIQUE (study_id, id);

ALTER TABLE visit
   ADD UNIQUE (study_id, id);

ALTER TABLE session
   DROP CONSTRAINT session_treatment_id_fkey,
   DROP CONSTRAINT session_visit_id_fkey,
   ADD FOREIGN KEY (study_id, treatment_id) REFERENCES treatment (study_id, id),
   ADD FOREIGN KEY (study_id, visit_id) REFERENCES visit (study_id, id);


Restoring from pg_dump with foreign key constraints In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored.  An example:

ERROR:  insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"
DETAIL:  Key (channel_id)=(1) is not present in table "channels".

Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.

caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem...

version: PostgreSQL 9.4.4.

How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created?

You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.

But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.

See https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.


cannot modify a column which maps to a non key-preserved table Hello everyone am trying to insert data into base tables via a view that joins more than two tables. I have an instead of trigger that should insert the data into the tables, not sure where have gone wrong on the code. The following is the codes have used.

View

CREATE OR REPLACE FORCE VIEW "NEW_APPLICANT" ("FIRST_NAME", "LAST_NAME", "ADDRESS",
     "MATH_SCORE", "ENGLISH_SCORE", "SCIENCE_SCORE") AS
select APPLICANT.FIRST_NAME as FIRST_NAME,
       APPLICANT.LAST_NAME as LAST_NAME,
       APPLICANT.ADDRESS as ADDRESS,
       RESULT.MATH_SCORE as MATH_SCORE,
       RESULT.ENGLISH_SCORE as ENGLISH_SCORE,
       RESULT.SCIENCE_SCORE as SCIENCE_SCORE
from   RESULT RESULT, APPLICANT APPLICANT
where  APPLICANT.APPLICANT_ID=RESULT.APPLICANT_ID
/

Trigger

create or replace TRIGGER application_new_insert
   INSTEAD OF INSERT ON NEW_APPLICANT
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN
     INSERT INTO APPLICANT
       (FIRST_NAME, LAST_NAME, ADDRESS)
     VALUES (
     :new.FIRST_NAME,
     :new.LAST_NAME,
     :new.ADDRESS);
   INSERT INTO RESULT ( MATH_SCORE, ENGLISH_SCORE, SCIENCE_SCORE)
   VALUES (
     :new.MATH_SCORE,
     :new.ENGLISH_SCORE,
     :new.SCIENCE_SCORE);
   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate customer or order ID');
   END application_new_insert;

Table definitions. Table applicant:

CREATE TABLE "APPLICANT"
( "APPLICANT_ID" NUMBER NOT NULL ENABLE,
  "FIRST_NAME" VARCHAR2(45) NOT NULL ENABLE,
  "LAST_NAME" VARCHAR2(45) NOT NULL ENABLE,
  "ADDRESS" VARCHAR2(35),
  CONSTRAINT "PK_APPLICANT_ID"
    PRIMARY KEY ("APPLICANT_ID") ENABLE
) /

CREATE OR REPLACE TRIGGER "TR_APPLICANT"
before insert on APPLICANT
for each row
begin
  if :NEW.APPLICANT_ID is null then
    select APPLICANT_ID_SEQ.nextval
    into :NEW.APPLICANT_ID
    from dual;
  end if;
end; /

ALTER TRIGGER "TR_APPLICANT"
  ENABLE /

Table result:

CREATE TABLE "RESULT"
( "RESULT_ID" NUMBER NOT NULL ENABLE,
"APPLICANT_ID" NUMBER NOT NULL ENABLE,
"MATH_SCORE" NUMBER NOT NULL ENABLE,
"ENGLISH_SCORE" NUMBER NOT NULL ENABLE,
"SCIENCE_SCORE" NUMBER NOT NULL ENABLE,
CONSTRAINT "PK_RESULT_ID" PRIMARY KEY ("RESULT_ID") ENABLE
) /

ALTER TABLE "RESULT"
  ADD CONSTRAINT "FK_RESULT_APPLICANT_ID"
    FOREIGN KEY ("APPLICANT_ID")
    REFERENCES "APPLICANT" ("APPLICANT_ID")
  ENABLE /

CREATE OR REPLACE TRIGGER "TR_RESULT"
before insert on RESULT
for each row
begin
  if :NEW.RESULT_ID is null then
    select RESULT_ID_SEQ.nextval
    into :NEW.RESULT_ID
    from dual;
  end if;
end; /

ALTER TRIGGER "TR_RESULT"
  ENABLE /

In your view you do not fetch all the key fields from both tables. Oracle needs that to be able to do the update. In your case you do an insert so I suppose that you do not provide the primary keys for both tables. Since you did not gave the structure of both tables I cannot be more specific.


Primary and secondary replicas on same server We're considering different options for HA/DR with SQL Server 2016.
AlwaysOn Availability Groups are obviously an option.

I understand we can read from secondary replicas, unlike DB mirroring for example.

Can we spread the write load during normal operations, by using each server in the cluster to be a primary database/replica for some databases, and a secondary database/replica for others?

Lets say we have 2 servers, sql01 and sql02, and 10 databases to protect.
Can we set this up:

sql01 holds db01-db05 as primary, db06-db10 as secondary.
sql02 holds db06-db10 as primary, db01-db05 as secondary.

During failover, each server can take over as primary for all DBs.

Are there additional options when using AlwaysOn to spread write operations?

You can and that's one of the beautiful things with availability groups over traditional clustering.  You'll need to set up two (or more if you wanted) AGs, one would initially have SQL01 as it's primary node and contain db01-db05 and the other would initially have SQL02 as it's primary node and contain db06-db10.

You are then free to fail them over independently as you wish, you could put all AGs on one server or split them out over the two.

I can't find any documentation or demos showing the setup of two AGs (perhaps it could be a subject for a future blog post).  The big difference between traditional clustering and using AGs is that the SQL servers aren't clustered in the way that your DBAs are probably thinking and there's no need for shared storage.  Basically, the SQL instances are installed as stand alone instances on each node of the cluster.  The AGs themselves are created as individual roles within the cluster and because of that can be failed over individually.

Hopefully that makes some sense, it's quite tricky to explain without an example.


Resume Logshipping after Primary database restored We have Database (D1) as a primary database and Log shipping setup to the secondary database called (LS_D1). Now what we are doing is taking backup of D1 database and restore it on D2 database. We do schema and data changes for new Release deployment on to D2 database.

What we want is to restore D2 database on D1 database and resume the log shipping to the LS_D1 database without copying backup file. Copying a backup file from primary data center to DR data center cost us too much money for every Release so that's why we want to resume the Log shipping.

When i tried to do this LSN numbers got out of sync, is there any way that we can solve this problem ? or can we have any other alternative to Log Shipping that can provide us DR capability and solve this problem ?

Note: Any solution that can require one time cost of log shipping would also be fine.

Thanks,
Piyush Patel

When you restored the primary the log shipping has broken. The only way to resume it is to re-initiate it, with a new full backup. End of discussion.

  We do schema and data changes for new Release deployment on to D2 database.

And that is your real problem. Do all changes as code changes and deploy by running deployment scripts. When you want to migrate the changes done in development on D2 to D1 simply run the migration scripts on D1. Use Visual Studio Database Projects for instance.

Even w/o proper development process you could had applied the changes between D2 and D1 as scripts run on D1, and don't break the log shipping relation. Eg. tools like Red Gate SQL Compare and Data Compare would had done the job.


Which SQL Server product does version 622 represent? I've manually attached the data and log files I received from a client to a SQL Server 2005 Express SP3 database.  During the upgrade to SP4, I get the following error:

  The database 'MyDatabase' cannot be opened because it is version 622. This server support version 612 and earlier. A downgrade path is not supported.

Does anyone know what database version 622 is and how I can install it? This database is from a Canadian customer so maybe it comes from an international version of SQL Server. I really wish there was a reference for the database versions so I knew what 622 is. Much Googling resulted in much frustration and no answers.

Adding some more information. The original database was corrupt. I was only able to restore it using a backup. The data files were a little newer (2 days) so we were seeing if we could use those as well.

Upgraded from SQL Server 2005 Express to SQL Server 2008 Express successfully while the corrupt database was attached. SELECT @@VERSION reports Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86). I ran sp_resetstatus, SET EMERGENCY and DBCC CHECKDB and received the following:

  Msg 946, Level 14, State 1, Line 1
  Cannot open database 'MyDatabase' version 622. Upgrade the database to the latest version.

622 is an internal intermediate version, never released. How come you have an 622 version DB? The explanation is simple actually: an aborted upgrade. Look at the sequence Aaron posted:

Converting database 'x' from version 611 to the current version 655.
Database 'x' running the upgrade step from version 611 to version 621.
Database 'x' running the upgrade step from version 621 to version 622.
Database 'x' running the upgrade step from version 622 to version 625.

If the last step above crashes, your database is a version 622 now (each step commits). This version can only be attached to a SQL Server 2008 (or newer) to continue and finish the upgrade. It seems to me you had a SQL Server 2005 DB (v. 611) you attached to a SQL Server 2008 or higher, it started to upgrade but the upgrade failed at step 622->625 and now you're trying to attach it back to the SQL 2005 instance. It can't be re-attached, your only chance is to try to move forward. If the upgrade continue to fail, you will have to start again from an original SQL 2005 database (a copy of the files, a backup...).

Why would a database upgrade fail? Same reason any operation can fail: CTRL-C, power outage, corrupted original database, hardware problems, bugs in the product. Knowing what the upgrade step 622-625 does, my moneys are on a corrupted source DB. Recovering from a failed upgrade is a bit more tricky. As usual, having a good, tested, backup and restore strategy pays off.


Data - Changes Audit Trail What are typical scenarios where the user might require a data-change-audit-trail?

There are many requirements for capturing changed data. I list only three common ones below.

Audits (HIPAA, SAS70, etc.)...one may need to audit SQL Server logins as well as attribute data changes to specific users
EDI...some insurance carriers require one to send to them current and previous data when a policy is altered, say, due to a life event. One may need to send one's previous coverage together with the new coverage. This is a good candidate for using SQL Server Change Data Capture (CDC).
ETL for DWs...require data change capture in order to add new data to facts and dimensions as well as process updates to slowly changing dimensions. Usually, something like SQL Server's Change Tracking would be right for this, since it is a "lighter" tool than CDC.


XML queries with case insensitive tag I have a requirement where i want XML tags to be case insensitive in my xml queries.

For example:

DECLARE @myTable TABLE ( yourXML XML )

INSERT INTO @myTable SELECT '<z>1<c>2</c></z>'
INSERT INTO @myTable SELECT '<Z>1<c>2</c></Z>'

SELECT * FROM @myTable WHERE ( [yourXML].exist('for $x in /z/a where  ( ($x/b[1]) = 1 )  return $x')>0 )

Returns output as

yourXML
<z>1<c>2</c></z>

But i want the output to display without considering case of the XML tag. So my desired output should be

yourXML
<z>1<c>2</c></z>
<Z>1<c>2</c></Z>

How it can be done?

Use predicates with name tests like this instead of "simple" node steps:

/*[lower-case(local-name(.)) = 'z']/*[lower-case(local-name(.)) = 'a']


What is the undo pass that SQL Server does during a RESTORE WITH STANDBY operation? It's my understanding that SQL Server generates undo files when applying a restore to a database using the RESTORE WITH STANDBY.

From the MSDN documentation (Emphasis mine):

  The standby file is used to keep a "copy-on-write" pre-image for pages modified during
  the undo pass of a RESTORE WITH STANDBY. The standby file allows a
  database to be brought up for read-only access between transaction log
  restores and can be used with either warm standby server situations or
  special recovery situations in which it is useful to inspect the
  database between log restores. After a RESTORE WITH STANDBY operation,
  the undo file is automatically deleted by the next RESTORE operation.
  If this standby file is manually deleted before the next RESTORE
  operation, then the entire database must be re-restored. While the
  database is in the STANDBY state, you should treat this standby file
  with the same care as any other database file. Unlike other database
  files, this file is only kept open by the Database Engine during
  active restore operations.

  The standby_file_name specifies a standby file whose location is
  stored in the log of the database. If an existing file is using the
  specified name, the file is overwritten; otherwise, the Database
  Engine creates the file.

  The size requirement of a given standby file depends on the volume of
  undo actions resulting from uncommitted transactions during the
  restore operation.

What is the undo pass referred to at the beginning?

From what I understand, those are the operations written in the log file that haven't been committed. If this is correct, why are those operations on the log file if they haven't been committed in the first place, and why does the RESTORE WITH STANDBY operation need to store them someplace to be able to bring the database up for read-only access (in other words, why can't they just be thrown away)?

why are those operations on the log file if they haven't been
  committed in the first place

All operations, committed or uncommitted, are written in the log. A commit ensures that all log entries are made durable (flushed to disk), but nothing prevents uncommitted entries from being flushed before that (either because a log block fills or because another transaction commits, thus forcing the flush for every transaction). A rollback must analyze all the transaction entries and generate compensating actions (for every insert do a delete, for every delete do an insert, for every update do an update that reverses the data back). These compensating actions are, of course, logged.

When a database is recovered it must rollback any transaction that is not committed in the log. It must as such analyze the log, figure out the uncommitted transactions, and then generate compensating actions for any all actions belonging to uncommitted transactions. Online recovery will write the compensation actions into the log itself. Standby recovery will write the compensating actions into an alternative stream, thus allowing for further log to be applied form a 'master' source later (this how log shipping standby read only access works).

Before asking any clarification question, please read ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging.


How to concatenate these two statements (RIGHT and LEFT)? I am trying to trim two characters off both the beginning and end of cell Is it possible to either combine or concatenate the following two statements?

RIGHT(wp_postmeta.meta_value, LENGTH(meta_value) -2)

LEFT(wp_postmeta.meta_value, LENGTH(meta_value) -2)

What I am ultimately trying to do is strip the first two and last two characters from a field (for display or output only...I am not trying to modify or replace anything in my database).

Below is my full query, which works great...except the output of wp_postmeta.meta_value is a URL encased with [" and "].

SELECT wp_download_log.ID, wp_download_log.download_date, wp_posts.post_title,

(SELECT group_concat(wp_terms.name separator ', ') FROM wp_terms
INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'dlm_download_category' and wp_posts.ID = wpr.object_id ) AS "Categories",

wp_postmeta.meta_value, wp_download_log.download_id, wp_download_log.version_id, wp_download_log.user_id, wp_download_log.user_ip, wp_download_log.user_agent
FROM wp_posts
LEFT JOIN wp_download_log ON wp_posts.ID = wp_download_log.download_id
LEFT JOIN wp_postmeta ON wp_download_log.version_id = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = '_files'

In there you will see that one column I am trying to output is wp_postmeta.meta_value where wp_postmeta.meta_key = '_files'.

Inserting one of my two statements I am trying to combine in place of wp_postmeta.meta_value works great. Inserting both works too...I just have two columns.

With my search for a unified solution coming up dry, I have been trying for the past hour or two to concatenate the two to get the contents of wp_postmeta.meta_value WHERE meta_key = '_files' into a single field, sans the first two and last two characters, but I have yet to figure out how.

Surely there is a SQL solution here without having to manually process the column in Excel with each export?

3 is the first position after the first 2 chars are removed. The length is reduced by 2 chars in the beginning + 2 characters at the end.

select MID(wp_postmeta.meta_value, 3, LENGTH(wp_postmeta.meta_value) -4 ) ...

simplied fiddle


Sql Server Backup - Log shipping vs VM snapshot Setup

I have Sql Server Standard running on an AWS VM

For one of my DBs on this server, I perform:

1x daily backup
And ship logs every 15mins to another DB Server VM, dedicated to log shipping for this DB.

Longer term, I had intended the log shipping VM to be a reporting DB and play a part in reducing load on the primary server. But this requirement is now secondary to saving money.

Recovery Criticality (RTO/RPO)

There is no offcial SLA. But the following is the general concensus: Recovering to within minus 1 hr of live data is seen as pretty much acceptable for this system. Although if we had to go to last night's backup then we would still survive, after taking some abuse. Recovery within 2 hours would be ideal, but up to 12 could be tolerated.

New approach to save money - VM Snapshots

I am considering switching off the log shipping VM and switching to snapshotting the production DB VM every 1hr. (This will save many $1,000s per year.). Note I am referring to full AWS EC2 VM EBS snapshots, not Sql DB Snapshots.

I have been very happy spinning up these VM snapshots in the past for non-critical investigations into old data. But I haven't relied on them as part of a DR plan.

Question

-Given the DB recovery profile I have alluded to above, would you switch to snapshotting, or do something else?
- Has anybody had issues spinning up old Sql Server VM Snapshots?

thanks

(This answer below was kind of helpful, but I would be interested in real-life experiences too.
Are VM snapshots enough to backup SQL?)

The problem with VM snapshots is 1) they aren't the cheapest option.  They generally cost more to store than your database backups.  and 2) they don't protect you from as many things.

To save money, just continue with your Full and Log backups, and keep a cold standby VM that you can start up and restore to in case of a disaster.  Just boot it up every couple of weeks to get patches, and test the restore process.

You can probably get this done with minimal changes to your current setup by perhaps modifying the log shipping jobs and shutting down your secondary VM.  You can bring it up every week and apply all the logs.  Then you're always just a few restores away from a complete database recovery.


DISTINCT Query on an INNER JOIN I have two tables, one (ARTISTS) with a list of different artists, details and biographies, but no images. I have another table (ARCHIVECATALOGUES), full of auction results, which have the images I need. I want to generate a query which will give me all of the detail from the ARTISTS table, and include an image (does not matter which one) from the other table for each of the artists.

So I need a query which INNER JOINs the two tables. The query below is pulling every result from the ARCHIVESCATALOGUE table, i.e. multiple results per artist. But I only need one result per artist. I used DISTINCT on the artist.ArtistID field, but to no avail. Here is the code:

SELECT DISTINCT artist.ArtistID
    ,ArchiveCatalogues.IMAGER
    ,ArchiveCatalogues.AUCTION
    ,artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
FROM artist
INNER JOIN ArchiveCatalogues ON (ArchiveCatalogues.ARTIST = artist.surname)
    AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)
WHERE artist.surname >= H *
    AND artist.surname < I
GROUP BY artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
    ,ArchiveCatalogues.AUCTION
    ,artist.ArtistID
    ,ArchiveCatalogues.IMAGER

Perhaps I should be using OUTER JOIN?

First (common misunderstanding), distinct is not applied to individual columns, what you get is distinct rows. This is exactly the same as your GROUP BYdoes, so distinct is redundant.

Second, you need to determine which row among duplicates that you are interested in (or get a random one). You can use window functions to achieve this by enumerating ArchiveCatalogues per Artists:

 SELECT artist.ArtistID
    ,ArchiveCatalogues.IMAGER
    ,ArchiveCatalogues.AUCTION
    ,artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
    , row_number() over (partition by artist.ArtistID
                      -- add wanted order her as:
                      -- order by ...
                        ) as rn
FROM artist
JOIN ArchiveCatalogues
    ON ArchiveCatalogues.ARTIST = artist.surname
   AND ArchiveCatalogues.FIRSTNAME = artist.firstnames
WHERE artist.surname >= H *
  AND artist.surname < I

Note that I removed the group by since I'm not sure what the purpose was. Now you can select the first row from there:

SELECT ArtistID
     , IMAGER
     , AUCTION
     , surname
     , firstnames
     , dates
     , honorific
     , biog
FROM (
    SELECT artist.ArtistID
        ,ArchiveCatalogues.IMAGER
        ,ArchiveCatalogues.AUCTION
        ,artist.surname
        ,artist.firstnames
        ,artist.dates
        ,artist.honorific
        ,artist.biog
        , row_number() over (partition by artist.ArtistID
                          -- add wanted order her as:
                          -- order by ...
                            ) as rn
    FROM artist
    JOIN ArchiveCatalogues
        ON ArchiveCatalogues.ARTIST = artist.surname
       AND ArchiveCatalogues.FIRSTNAME = artist.firstnames
    WHERE artist.surname >= H *
      AND artist.surname < I
) as T
WHERE rn = 1;

The join looks strange but it's hard to tell without knowing what the tables and their key's look like.


Workaround for the limit of the number of missing indexes suggested by SQL Server's DMVs In production, each instance of SQL Server has over 250 databases (what we call 'OrgDb's). The project I'm currently working on, aims to send all the reported missing indexes by SQL Server's DMVs to telemetry, in order to do some post analysis on how well the queries sent to these orgDbs are doing and possibly do some optimizations.

Sounds straightforward, right? The problem however, is that there is a max limit of 500 for the number of missing indexes that DMVs can report in a single SQL Server whereas we expect around 20 missing indexes per OrgDb (~ 5000 in total).

Can anyone think of a workaround for this limit? One solution I initially thought of, was to delete the DMVs' missing index tables:

sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group

after each update, but turns out those tables cannot be modified:

Error:Ad hoc updates to system catalogs are not allowed.

You can't reset the DMVs, however you can work around this limitation and remove rows from the DMVs by creating a small filtered index on the tables mentioned in the DMVs then immediately dropping that index.

For instance:

CREATE INDEX IX_temp
ON dbo.SomeTable(SomeKey)
WHERE SomeKey IS NULL;

DROP INDEX dbo.SomeTable.IX_temp;

I've created a script to automate this process.

IF OBJECT_ID('dbo.RemoveMissingIndexSuggestions') IS NOT NULL
DROP PROCEDURE RemoveMissingIndexSuggestions;
GO
CREATE PROCEDURE dbo.RemoveMissingIndexSuggestions
(
    @Database SYSNAME = NULL --optional, if NULL, clear all suggestions
                             --if specified, only clear suggestions for that database
    , @Table SYSNAME = NULL --if not NULL, only clear suggestions for the specified table
)
AS
BEGIN
    /*
        Max Vernon, 2016-04-08
        Inspired by work by Joe Sack and Glenn Berry at
        http://www.sqlskills.com/blogs/joe/clearing-missing-index-suggestions-for-a-single-table/

        Creates one index for each table that is mentioned in sys.dm_db_missing_index_details
        then promply drops that index.  The index is created with a WHERE clause that is likely
        to eliminate all or almost all rows, and therefore will be created quite quickly.
    */
    SET NOCOUNT ON;
    DECLARE @ObjectName SYSNAME;
    DECLARE @DatabaseName SYSNAME;
    DECLARE @CreateStmt NVARCHAR(MAX);
    DECLARE @DropStmt NVARCHAR(MAX);
    DECLARE @stmt NVARCHAR(MAX);
    DECLARE @msg NVARCHAR(2000);
    DECLARE @vars NVARCHAR(1000);
    DECLARE @Uniquifier NVARCHAR(48);
    SET @vars = '@stmt NVARCHAR(MAX)';
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
    FOR
    WITH cte AS
    (
        SELECT ObjectName = d.name + '.' + s.name + '.' + o.name
            , DatabaseName = d.name
            , CreateStmt = N'CREATE INDEX [IX_temp]
ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N'(' + mid.equality_columns + N')
WHERE '
              + (
                SELECT TOP(1) cols.ColName FROM (
                    SELECT TOP(1) ColName = QUOTENAME(c.name) + N' IS NULL'
                    FROM sys.columns c
                        INNER JOIN sys.key_constraints kc ON c.object_id = kc.parent_object_id
                    WHERE c.object_id = o.object_id
                        AND kc.type_desc = N'PRIMARY_KEY_CONSTRAINT'
                    UNION ALL
                    SELECT TOP(1) QUOTENAME(c.name) + N' = -2147483648'
                    FROM sys.columns c
                        INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
                    WHERE ty.name IN
                        (
                              N'bigint'
                            , N'binary'
                            , N'hierarchyid'
                            , N'int'
                            , N'uniqueidentifier'
                            , N'varbinary'
                        )
                    ) cols
                )
                + ';'
                , DropStmt = N'DROP INDEX ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + '.[IX_temp];'
                , rn = ROW_NUMBER() OVER (PARTITION BY mid.object_id ORDER BY mid.index_handle)
        FROM sys.dm_db_missing_index_details mid
            INNER JOIN sys.objects o ON mid.object_id = o.object_id
            INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
            INNER JOIN sys.databases d ON mid.database_id = d.database_id
        WHERE o.name NOT LIKE '#%' -- ignore temp tables
            AND (d.name = @Database OR @Database IS NULL)
            AND (o.name = @Table OR @Table IS NULL)
    )
    SELECT cte.ObjectName
        , cte.DatabaseName
        , cte.CreateStmt
        , cte.DropStmt
    FROM cte
    WHERE rn = 1;
    OPEN cur;
    FETCH NEXT FROM cur INTO @ObjectName, @DatabaseName, @CreateStmt, @DropStmt;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @msg = 'Flushing ' + @ObjectName + ' indexes.

';
        RAISERROR (@msg, 0, 1) WITH NOWAIT;
        SET @stmt = 'EXEC ' + QUOTENAME(@DatabaseName) + '.sys.sp_executesql @stmt;'
        SET @Uniquifier = CONVERT(NVARCHAR(48), NEWID(), 0);
        SET @CreateStmt = REPLACE(@CreateStmt, '[IX_Temp]', '[IX_Temp_' + @Uniquifier + ']')
        SET @DropStmt = REPLACE(@DropStmt, '[IX_Temp]', '[IX_Temp_' + @Uniquifier + ']')
        SET @CreateStmt = 'SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
' + @CreateStmt + '

';
        SET @DropStmt = 'SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
' + @DropStmt + '

';
        RAISERROR (@CreateStmt, 0, 1) WITH NOWAIT;
        RAISERROR (@DropStmt, 0, 1) WITH NOWAIT;
        EXEC sp_executesql @stmt, @vars, @stmt = @CreateStmt;
        EXEC sp_executesql @stmt, @vars, @stmt = @DropStmt;
        FETCH NEXT FROM cur INTO @ObjectName, @DatabaseName, @CreateStmt, @DropStmt;
    END
    CLOSE cur;
    DEALLOCATE cur;
END
GO

This can be executed with the @Database parameter set to the name of a database to eliminate recommended indexes only related to that database, or without parameters to eliminate all recommended indexes.  One can optionally limit this to the suggestions for a single table by passing the name of the table into the @Table parameter.

EXEC dbo.RemoveMissingIndexSuggestions @Database = 'tempdb', @Table = 'SomeTable';

It creates at most one index per table. The index has a unique name and is constructed using a single column, giving priority to using the table's PRIMARY KEY, if it has one.  Tables will be missed by this proc if the table does not have a primary key or at least one of the following types of columns:

bigint
binary
hierarchyid
int
uniqueidentifier
varbinary

I've written a short blog post on this issue over at SQLServerScience


Does Microsoft SQL Server offer an Advisory Locks feature like Postgres? In Postgres, the Advisory Locks feature provides for arbitrary application-defined locks to be administered centrally in the database server. These locks are not related to tables or rows or such.

Does Microsoft SQL Server offers something like these Advisory Locks?

Application locks. They are arbitrary locks held by the engine on behalf of the app. Acquired with sp_getapplock and released with sp_releaselock (if needed). Shared, Update, Exclusive and intent modes with the usual compatibility matrix. Can have transaction or session scope. The behavior matches the PG advisory locks in every aspect, but is a stored procedure, not a function (so it cannot be used inline in a SELECT, for instance).


MySQL lock on MEMORY storage engine, when does it apply? I want to use MySQL MEMORY storage engine to handle my PHP session variables (actually I already implemented it but since all my tests are being done by a single user, me, I am unable to notice this).

As it says here:
http://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

  MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes.

I don't understand this statement. It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct? Can this be disabled? I need to write and read quite frequently and at the same time and I am not planning on doing transactions on this table:

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `data` varchar(64000) NULL,
  `expires` int(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=MEMORY;

You said in your question

  It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct?

You are correct.

The MEMORY storage engine performs a full table lock for every DML statement. That is why the link you mentioned shows Lock granularity : Table just like MyISAM. You cannot disable this.

In addition to this, please keep in mind that the MEMORY storage engine still imposes some mild disk I/O for both reads and writes. Why ? An open file handle is maintained on a MEMORY table to at least check on the table's existence and accessibility (See my old most I am using the MEMORY storage engine but MySQL still writes to my disk...Why?)

Please see my other old post Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM? on the pros and cons of using MEMORY tables.

You may have to resort to using InnoDB for such session data.


Is it worth duplicating a table for different types of an entity when one should be unique? In my database, I have a CUSTOMER table. I'm migrating an OPPORTUNITIES table, which contains two different types of opportunity, each having identical columns (e.g. status, reason lost, probability of success, etc.). One such type is the opportunity of gaining a new customer. The other such type is the possibility of a sale to an existing customer.

I see two possible ways to do this, and am uncertain which is better (or if there's a third, better way).

Option A:
Migrate into two separate tables: POSSIBLE_CUSTOMER and POSSIBLE_SALE. Both tables would have the same columns, including a foreign key to CUSTOMER. POSSIBLE_CUSTOMER's foreign key would have a unique constraint on it.

Option B:
Migrate into a single table, with a column to determine type. This way we're not duplicating the table's columns as above... but there's (to the best of my knowledge) no way to ensure that there's only ever at most one possible customer per customer.

Some people might wince at this, but if you have at least one column that distinguishes sale opportunities from customer prospects you could make this column nullable and create a unique index that includes the customer ID and this column.

Db2 allows only one NULL value in a column in a unique index (in other words, from the uniqueness point of view all NULL values are considered equal). Thus you will store a NULL in this column for "possible customer" and something unique for each "possible sale", and Db2 will ensure that only one "possible customer" (i.e. NULL) value is present for a given customer ID.


creating and populating table using pl/sql I have been looking for a way to create a table in pl/sql format like using DECLARE and BEGIN. Below is what i tried in sql Developer and but continiously getting the error below.

Please advise what am i doing wrong and if any good resource to learn on populating table using pl/sql? cheers!!

Error report : ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:***

begin case declare exit for goto if loop mod null pragma
raise return select update while with an identifier

Code :
set serveroutput on;
DECLARE
    v_customer_ID  VARCHAR(10) not null := 3025;
    v_customer_Name VARCHAR(15) :='Michel Jackson' ;
    v_room_code  VARCHAR(5) := 6536;
BEGIN
CREATE TABLE CUSTOMER
( v_customer_id VARCHAR2(10) NOT NULL,
  v_customer_name varchar2(15),
  v_room_code varchar2(5),
  CONSTRAINT CUSTOMER_PK PRIMARY KEY (v_customer_ID)
);
 END;
 /

You can't. You have to use execute immediate. Check here

DECLARE
    v_customer_ID  VARCHAR(10) not null := 3025;
    v_customer_Name VARCHAR(15) :='Michel Jackson' ;
    v_room_code  VARCHAR(5) := 6536;
BEGIN

  execute immediate 'create table customer....';

END;
/


Merge Replication SQL I am currently having a problem with the following
Creating a merge replication is simple if the servers are on the same network, i would like to know how can i do a merge replication if the servers are on seperate networks
I am using SQL 2008 R2

There's some very good tutorials on the web about this that go in depth and have lots of screenshots.

I'm assuming you also have to cross untrusted domains, correct?  Usually if the domains are trusted it means some sort of network link with AD/LDAP has been established.  If that's the case, you're looking at:
Merge replication across untrusted domains using web synch

Pull Merge Replication Security with Sql Server 2005 with no Domain Set Up (Largely applies to 2008)
http://www.jstawski.com/archive/2007/03/06/pull-merge-replication-security-with-sql-server-2005.aspx

How to: Configure a Subscription to Use Web Synchronization (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms345214.aspx

You will also run into questions such as 'do I use a sql user, do I use security certs?  SSL or SQL certs, etc.?'  There's going to be a lot of decisions you'll have to make so be prepared to re-do it several times.