Designing a friendships database structure: Should I use a multivalued column? Say I have a table called User_FriendList, which has the following characteristics:

CREATE TABLE User_FriendList (
    ID ...,
    User_ID...,
    FriendList_IDs...,
    CONSTRAINT User_Friendlist_PK PRIMARY KEY (ID)
);

And let us suppose that said table holds the following data:

 +----+---------+---------------------------+
 | ID | User_ID | Friendlist_IDs            |
 +----+---------+---------------------------+
 | 1  | 102     | 2:15:66:35:26:17:         |
 +----+---------+---------------------------+
 | 2  | 114     | 1:12:63:33:24:16:102      |
 +----+---------+---------------------------+
 | 3  | 117     | 6:24:52:61:23:90:97:118   |
 +----+---------+---------------------------+

Note: The “:” (colon) is the delimiter when exploding in PHP into an array.

Questions

So:

Is this a convenient way to “store” the IDs of a FriendList?
Or, instead, should I have individual rows with only one single FriendId value in each of them and, when I need to retrieve all the rows of a given list, simply perform a query like SELECT * FROM UserFriendList WHERE UserId = 1?

Managing an individual piece of information
Assuming that, in your business domain,

a User can have zero-one-or-many Friends;
a Friend must first be registered as a User; and
you will search for, and/or add, and/or remove, and/or modify, single values of a Friend List;

then each specific datum gathered in the Friendlist_IDs multivalued column represents a separate piece of information that carries a very exact meaning. Therefore, said column

entails a proper group of explicit constraints, and
its values have the potential of being manipulated individually by means of several relational operations (or combinations thereof).

Short answer
Consequently, you should retain each of the Friendlist_IDs values in (a) a column that accepts exclusively one sole value per row in (b) a table that represents the conceptual-level association type that can take place between Users, i.e., a Friendship —as I will exemplify in the following sections—.
In this way, you will be able to handle (i) said table as a mathematical relation and (ii) said column as a mathematical relation attribute —as much as MySQL and its SQL dialect permit, of course—.
Why?
Because the relational model of data, created by Dr. E. F. Codd, demands having tables that are composed of columns that hold exactly one value of the applicable domain or type per row; hence, declaring a table with a column that can contain more than one value of the domain or type in question (1) does not represent a mathematical relation and (2) would not permit obtaining the advantages proposed in the aforementioned theoretical framework.
Modeling Friendships between Users: Defining the business environment rules first
I highly recommend starting to shape a database delimiting —before anything else— the corresponding conceptual schema by virtue of the definition of the relevant business rules that, among other factors, must describe the types of interrelationships that exist between the distinct aspects of interest, i.e., the applicable entity types and their properties; e.g.:

A User is primarily identified by his or her UserId
A User is alternately identified by the combination of his or her FirstName, LastName, Gender, and Birthdate
A User is alternately identified by his or her Username
A User is the Requester of zero-one-or-many Friendships
A User is the Addressee of zero-one-or-many Friendships
A Friendship is primarily identified by the combination of its RequesterId and its AddresseeId

Expository IDEF1X diagram
In this manner, I was able to derive the IDEF1X1 diagram shown in Figure 1, which integrates most of the rules previously formulated:

As depicted, Requester and Addressee are denotations that express the Roles carried out by the specific Users that take part in a given Friendship.
That being so, the Friendship entity type portrays an association type of many-to-many (M:N) cardinality ratio that can involve different ocurrences of the same entity type, i.e., User. As such, it is an example of the classic construct known as “Bill of Materials” or “Parts Explosion”.

1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view of data, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

Illustrative SQL-DDL logical design
Then, from the IDEF1X diagram presented above, declaring a DDL arrangement like the one that follows is much more “natural”:
-- You should determine which are the most fitting
-- data types and sizes for all the table columns
-- depending on your business context characteristics.

-- At the physical level, you should make accurate tests
-- to define the mostconvenient INDEX strategies based on
-- the pertinent query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE UserProfile ( -- Represents an independent entity type.
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- Single-column ALTERNATE KEY.
);

CREATE TABLE Friendship ( -- Stands for an associative entity type.
    RequesterId     INT      NOT NULL,
    AddresseeId     INT      NOT NULL, -- Fixed with a well-delimited data type.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId)
);

In this fashion:

each base table represents an individual entity type;
each column stands for a sole property of the respective entity type;
a specific data typea is fixed for each column in order to guarantee that all the values it contains belong to a particular and well defined set, be it INT, DATETIME, CHAR, etc.; and
multiple constraintsb are configured (declaratively) in order to ensure that the assertions in the form of rows retained in all the tables meet the business rules determined at the conceptual schema.

Advantages of a single-valued column
As demonstrated, you can, e.g.:

Take advantage of referential integrity enforced by the database management system (DBMS for brevity) for the Friendship.AddresseeId column, since constraining it as a FOREIGN KEY (FK for brevity) that makes a reference to the UserProfile.UserId column guarantees that every value points to an existing row.

Create a composite PRIMARY KEY (PK) made up of the combination of columns (Friendship.RequesterId, Friendship.AddresseeId), helping to elegantly distinguish all the INSERTed rows and, naturally, protect their uniqueness.
Of course, this means that the attachment of an extra column for system-assigned surrogate values (e.g., one set up with the IDENTITY property in Microsoft SQL Server or with the AUTO_INCREMENT attribute in MySQL) and the assisting INDEX is entirely superfluous.

Restrict the retained values in Friendship.AddresseeId to a precise data typec (which should match, e.g., the one established for UserProfile.UserId, in this case INT), letting the DBMS take care of the pertinent automatic validation.
This factor can as well help to (a) utilize of the corresponding built-in type functions and (b) optimize disk space usage.

Optimize data retrieval at the physical level by configuring small and fast subordinate INDEXes for the Friendship.AddresseeId column, as these physical elements can substantialy assist in speeding up the queries that involve said column.
Certainly, you can, e.g., put up a single-column INDEX for Friendship.AddresseeId alone, a multi-column one that encompasses Friendship.RequesterId and Friendship.AddresseeId , or both.

Avoid the unnecessary complexity introduced by “searching for” distinct values that are collected together inside the same column (very likely duplicated, wrongly typed, etc.), a course of action that would eventually slow down the functioning of your system, because you would have to resort to resource- and time-consuming non-relational methods to accomplish said task.

So, there are multiple reasons that call for analyzing the relevant business environment carefully in order to mark out the typed of each table column with accuracy.
As expounded, the role played by the database designer is paramount to make the best use of (1) the logical-level benefits offered by the relational model and (2) the physical mechanisms provided by the DBMS of choice.

a, b, c, d Evidently, when working with SQL platforms (e.g., Firebird and PostgreSQL) that support DOMAIN creation (a distinctive relational feature), you can declare columns that only accept values that belong to their respective (fittingly constrained and sometimes shared) DOMAINs.

One or more application programs sharing the database under consideration
When you have to employ arrays in the code of the application program(s) accesing the database, you simply need to retrieve the relevant data set(s) in full and then “bind” it (them) to the concerning code structure or execute the associated app(s) process(es) that should take place.
Further benefits of single-valued columns: Database structure extensions are much more easy
Another advantage of holding the AddresseeId data point in its reserved and properly typed column is that it considerably facilitates extending the database structure, as I will exemplify below.
Scenario progression: Incorporating the Friendship Status concept
Since Friendships can evolve over time you might have to keep track of such a phenomenon, thus you would have to (i) expand the conceptual schema and (ii) declare a few more tables in the logical layout. So, let us arrange the next business rules to delineate the new incorporations:

A Friendship holds one-to-many FriendshipStatuses
A FriendshipStatus is primarily identified by the combination of its RequesterId, its AddresseeId and its SpecifiedDateTime
A User specifies zero-one-or-many FriendshipStatuses
A Status classifies zero-one-or-many FriendshipStatuses
A Status is primarily identified by its StatusCode
A Status is alternately identified by its Name

Extended IDEF1X diagram
Successively, the previous IDEF1X diagram can be extended in order to include the new entity types and interrelationship types described above. A diagram depicting the previous elements associated with the new ones is presented in Figure 2:

Logical structure additions
Afterwards, we can lengthen the DDL layout with the following declarations:
--
CREATE TABLE MyStatus ( -- Denotes an independent entity type.
    StatusCode CHAR(1)  NOT NULL,
    Name       CHAR(30) NOT NULL,
    --
    CONSTRAINT MyStatus_PK PRIMARY KEY (StatusCode),
    CONSTRAINT MyStatus_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE FriendshipStatus ( -- Represents an associative entity type.
    RequesterId       INT      NOT NULL,
    AddresseeId       INT      NOT NULL,
    SpecifiedDateTime DATETIME NOT NULL,
    StatusCode        CHAR(1)  NOT NULL,
    SpecifierId       INT      NOT NULL,
    --
    CONSTRAINT FriendshipStatus_PK             PRIMARY KEY (RequesterId, AddresseeId, SpecifiedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipStatusToFriendship_FK FOREIGN KEY (RequesterId, AddresseeId)
        REFERENCES Friendship  (RequesterId, AddresseeId), -- Composite FOREIGN KEY.
    CONSTRAINT FriendshipStatusToMyStatus_FK   FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipStatusToSpecifier_FK  FOREIGN KEY (SpecifierId)
        REFERENCES UserProfile (UserId)
);

Consequently, every time that the Status of a given Friendship needs to be put up-to-date, the Users would only have to INSERT a new FriendshipStatus row, containing:

the suitable RequesterId and AddresseeId values —taken from the concerning Friendship row—;

the new and meaningful StatusCode value —drawn from MyStatus.StatusCode—;

the exact INSERTion instant, i.e., SpecifiedDateTime —preferably using a server function so that you can retrieve and retain it in a reliable manner—; and

the SpecifierId value that would indicate the respective UserId that entered the new FriendshipStatus into the system —ideally, with the aid of your app(s) facilities—.

To that extent, let us suppose that the MyStatus table encloses the following data —with PK values that are (a) end user-, app programmer- and DBA-friendly and (b) small and fast in terms of bytes at the physical implementation level—:

 +-——————————-+-—————————-+
 | StatusCode | Name      |
 +-——————————-+-—————————-+
 | R          | Requested |
 +------------+-----------+
 | A          | Accepted  |
 +------------+-----------+
 | D          | Declined  |
 +------------+-----------+
 | B          | Bloqued   |
 +------------+-----------+

So, the FriendshipStatus table may hold data like shown below:

 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 | RequesterId | AddresseeId | SpecifiedDateTime       | StatusCode | SpecifierId |
 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 |        1750 |        1748 | 2016-04-01 16:58:12.000 | R          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-02 09:12:05.000 | A          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-04 10:57:01.000 | B          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-07 07:33:08.000 | R          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-08 12:12:09.000 | A          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+

As you can see, it can be said that the FriendshipStatus table serves the purpose of comprising a time series.

Relevant posts
You might as well be interested in:

This answer in which I suggest a basic method to deal with a common many-to-many relationship between two dissimilar entity types.
The IDEF1X diagram shown in Figure 1 that illustrates this other answer. Pay special attention to the entity types named Marriage and Progeny, because they are two more examples of how to handle the “Parts Explosion Problem”.
This post that presents a brief deliberation about holding different pieces of information within a single column.


Getting the value from a column during a trigger After a test completes (on a SQL 2008R2 system), the system updates a row in the TestRun table to include an end time. I am creating a trigger that will take action after that update, and do some post-test analysis for me.

Right now it's very simple.

BEGIN

    SET NOCOUNT ON;
    DECLARE @errCount Int
    @errCount = exec USF_NumofErrors @RunID = '124'

END
GO

However I would like to have that integer replaced with a variable that is the RunID of the row updated (which is causing the trigger to fire).

How can I do that?

Triggers in SQL Server are fired once per set, not per row (like Oracle), so you need to build triggers that handle this.

In all triggers, you have two special tables called inserted and deleted, that contain  a virtual "mirror" of the entire table row(s) after and before the action that triggered it, respectively.

A word of warning: triggers should be small and very light-weight. In your case, running a cursor over your log rows and executing a stored procedure for each row can cause you locking mayhem and slow performance. (Triggers run synchronously, in the same transaction as the original DML statement.)

I would write your solution not in a trigger, but as a separate stored procedure and run it on-demand.


What does "cleaning up" mean in MySQL InnoDB transaction status and how to prevent it? In our database (MySQL 5.6.22 on RDS) we have a lot of transactions (~500) in a state "cleaning up" under the load:

> SHOW ENGINE innodb STATUS;
...
------------
TRANSACTIONS
------------
...
---TRANSACTION 70150348714, not started
MySQL thread id 132590, OS thread handle 0x2b2a712ca700, query id 10842420427 <ip> <user> cleaning up
...

The only information I have found is:

  The thread has processed one command and is preparing to free memory and reset certain state variables.

https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

And this question comment mentions:

  You may need to fine tune some memory related parameters if you see this often in your instance.

Many transactions are getting stuck in the "cleaning up" state - what could cause that?

What could be the actual problem? Why are so many transactions in "cleaning up" state? What memory parameters can I fine tune to prevent it?

Any advice will be greatly appreciated.

To quote the man himself:

  Hi!

  Yes, you can get processes in the 'cleaning up' state if your
  table_cache is too small.

  The general answer is that you should increase your table cache if the
  status variable 'opened_tables' tend to increase a lot over time.

  Yours,
  Monty

Source: Grokbase.com


How can I better optimize my tables and a LEFT JOIN query to find items that don't yet exist in the right table? I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference.

desc urllist;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| url   | text                | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

and

desc wordlocation;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| urlid    | bigint(20) unsigned | NO   |     | NULL    |       |
| wordid   | bigint(20) unsigned | NO   |     | NULL    |       |
| location | int(10) unsigned    | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+

The software app is a web spider. It crawls a list of urls, extracts those urls, and inserts these into the urllist table. Then, an indexer checks to see what urls have not yet been indexed, and then proceeds to index said urls.

Here is the query I am using to find items in the left table (urllist) that have not yet been indexed in the right table (wordlocation). This query is as suggested on the mysql.com website:

select * from urllist ul
left join wordlocation wl on ul.id = wl.urlid
where wl.urlid IS NULL;

As of this writing my test database has only 600 indexed urls, and the wordlocation table has 1.3 million rows.  However, my CPU is at 100%, and the longest I've waited to see if the query would complete is a half hour (which, it never did by the way).

To be thorough, here is the explanation of the query:

explain select * from urllist ul left join wordlocation wl on ul.id = wl.urlid where wl.urlid IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                   |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
|  1 | SIMPLE      | ul    | ALL  | NULL          | NULL | NULL    | NULL |   50364 |                         |
|  1 | SIMPLE      | wl    | ALL  | NULL          | NULL | NULL    | NULL | 1351371 | Using where; Not exists |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+

I need this query to finish in seconds, not minutes. Also, I'm worried about scalability. I have 40,000 unique urls that are waiting to be added to the index, so how do I take that into consideration with my table and query design? 400,000 urls?

Just a couple notes about my decisions on the current table structure.

I have no intention of stopping at 400,000 urls, but perhaps bigint(20) is a bit overzealous?

Url as text is for more practical reasons. I index a lot of Asian and other foreign language domains which do not appear as their equivalent Kanji or other characters in the database and frequently take more than 255 characters.

I'm using MySQL. I'm definitely open to suggestions as to better table and query design. Please let me know if I can provide more information.

First, your query is correct. You don't need the wordlocation columns though (they are going to be all NULL anyway) so I'd change  select * to select ul.*:

SELECT ul.* FROM urllist AS ul
LEFT JOIN wordlocation AS wl ON ul.id = wl.urlid
WHERE wl.urlid IS NULL;

There are two more ways this kind of (anti-join or anti-semijoin) queries are usually written. Using NOT IN which is not recommended if the 2 joining columns are not both not nullable. Your urlid columns are not nullable indeed so this would work as well:

SELECT ul.* FROM urllist AS ul
WHERE ul.id NOT IN
      ( SELECT wl.urlid
        FROM wordlocation AS wl
      ) ;

Or using NOT EXISTS:

SELECT ul.* FROM urllist AS ul
WHERE NOT EXISTS
      ( SELECT 1
        FROM wordlocation AS wl
        WHERE ul.id = wl.urlid
      ) ;

All three queries result in very similar execution plans and efficiency in MySQL. So, you should test with your data distribution (and table sizes) and the version of MySQL you are using, to decide which query to use.

The query is of course slow now because of the obvious missing index on wordlocation (urlid). The efficiency will improve (dramatically) when you add this index but that's not all is missing from this table.

You have no Primary Key defined. Check and think about your modelling and find which columns uniquely identify rows in this table. My guess is that you are processing web pages, extracting words from them and storing all (or some of the) words and their locations in the webpages. If that's correct, you could use the (urlid, location) as the primary key:

ALTER TABLE wordlocation
  ADD PRIMARY KEY (urlid, location) ;

And if you do that, you won't need a separate index on (urlid). You will probably be having other queries though, that search for words (I guess you also have a wordlist table), so you will need an index on (wordid) as well - or on (wordid, location) or some other combinations. What indexes you'll need depends on what queries you plan to run against these tables.

For the urllist.url datatype, I'd prefer to use VARCHAR(x) instead of TEXT. What is the longest url you could have? The length x can be as long as 65535 and if you want to index it, the index can be for up to the first 255 characters. (I assume that you are storing the URLs there and not the actual web pages. If you do store web pages, disregard this paragraph.)


How to enable a user using a script? I create a user with CREATE LOGIN...

sp_grantdbaccess does not seem to enable the user

How can I enable a user using a script?

UPDATE: GRANT CONNECT does not seem to work, maybe I wasn't clear enough,
This is a SQL user (not Windows user) on SQL Server 2014, I'm looking at the main "Security-->Logins" tab that is common to all databases...

if you want to access a database using a login you created, you need some decisions to make,
first : you have to create a user in database for the login you created, it could be a same name or a different name for it.
you also need to decide what amount of permissions you need for the login,

the sample below is for creating a login with a specific password, that is going to access adventureworks2012 with db_datareader permission over database:

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2012]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [AdventureWorks2012]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test] GO


How do I rename my local SQL development server? I'm a dev who inherited a mostly functioning box doing most of what I need. Except for the machine name is still that of the old dev (we name it "{username}-dt" or "{username}-lt" for ease of id on the network) and I want to rename it from old-username to my-username.

Naturally this will affect SQL as well, so I thought I would ask for more experienced advice on what I need to do before I rename my machine. I know there are some "sp_" sprocs to be run, but when do I run them? Do I need to restart my box before or after, and do I need a certain level of privilege? Will it destroy any existing windows-based auth on the box (those accounts are all AD accounts anyways)?

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

Rename the computer.
Restart the computer - SQL Server will recognize the new name during startup, but the sys.servers table will still contain the old name (you can run SELECT @@SERVERNAME to confirm it).
Run the sp_dropserver and sp_addserver procedures to update this table.

According to BOL, the former requires the ALTER ANY LINKED SERVER permission and the latter - membership in the setupadmin server role; however, brief sp_helptext and Google investigation suggests that actually they both require this ALTER ... permission.
Restart SQL Server and use SELECT @@SERVERNAME to verify that the previous step has been successful.

Apart from technical considerations explained in the How to: Rename a Computer ... article (e.g. computers involved in replication cannot be renamed), the thing I would worry most about is finding and changing all the connection strings:

the ones in app.config and web.config files,
the ones hard-coded in some prototype applications,
the ones hidden somewhere deep in configuration of SharePoint, Analysis Services and other systems,
the ones embedded in Excel files or Access databases).


How to send a parameter to a trigger function from an external resource I have a trigger that executes before delete to audit changes in tables. The problem I have is that one of the fields I save in the xtable_audit table is the user id (of an user table) that is used in the web application. How can I send that external iduser from the web application to the trigger function when a delete occurs in any table that has that trigger?

You could use the set_config() and current_setting() functions for that. In the application just after establishing the database connection you call something like

select set_config('myapp.app_user','johnny',false);

to set a custom configuration variable for the session. In the trigger you then use

select into v_actualuser current_setting('myapp.app_user');

to retrieve the value.

This blog post even has a sample trigger for your viewing pleasure.


MySQL - ERROR 1045 (28000): Access denied for user : permission issue ex :on first server :

    mysql> show grants for 'test'@'localhost';
    +---------------------------------------------------+
    | Grants for test@localhost                         |
    +---------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' |
    +---------------------------------------------------+

on second server its showing like this :

    mysql> show grants for 'root'@'localhost';

    | Grants for root@localhost                                                                                |

    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*****************' WITH GRANT OPTION |

1 row in set (0.36 sec)

So problem is when i try to grant all for any user or database then getting "subject line"

The problem is simple: On the second server, the table mysql.user is from a previous version of MySQL. How do I know this? I dealt with that error message back on April 12, 2012 : Cannot GRANT privileges as root

The problem stems from the number of columns in mysql.user. When you run

SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion
FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

You get these numbers from each MySQL version

43 in MySQL 5.6
42 in MySQL 5.5
39 in MySQL 5.1
37 in MySQL 5.0

You cannot run that query on MySQL4.x since the information_schema database does not exists prior to MySQL 5.x. You would have to run DESC mysql.user to see it has 31 columns for MySQL 4.x.

I addressed this back on May 01, 2013 : Can I find out what version of MySQL from the data files?

While my posts suggested radical methods for correcting this, the recommended way it to run the utility mysql_upgrade on the second server as follows:

# mysql_upgrade --upgrade-system-tables

This should add the missing grant columns to mysql.user.

Give it a Try !!!


Can not run PostgreSQL after drop table I've deleted database from PostgreSQL and after this I can do nothing.
Here what i receive with createuser

$ createuser -D new_user
createuser: could not connect to database postgres: FATAL:  role "postgres" does not exist

And this when try to run psql

$ psql
psql: FATAL:  role "postgres" does not exist

I've tried above with root, postgres, custom users. Still same output

Also I searched for pg_hba.conf like this
 find / -name 'pg_hba.conf'
and got this

/var/lib/postgres-xc/DN2/pg_hba.conf
/var/lib/postgres-xc/DN1/pg_hba.conf
/var/lib/postgres-xc/CN/pg_hba.conf
/etc/postgres-xc/coordinator/CN/pg_hba.conf
/etc/postgres-xc/datanode/DN2/pg_hba.conf
/etc/postgres-xc/datanode/DN1/pg_hba.conf
/etc/postgresql/9.3/main/pg_hba.conf

I've changed connection method to trustfor localhost and IPv4 in /etc/postgresql/9.3/main/pg_hba.conf.

What should I do get rid of this error?

Does connection method in other files above should be edited too?

Main problem was that I could not run psql command under no user.
Output I received was:

psql: FATAL:  role "any role" does not exist

any role means that I tried to run psql under each user on my system(root,postgresql,my_user)

After all I haven't figured out what the problem was, I decided to reinstall PostgreSQL.

And this process turned out rather hard to.

So if you have problems with deleting and reinstalling PostgreSQL, read on.

Here what I had to do.

echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

now we get error about missing postgresql-common
to fix it we need to install above package
lets determine its version

sudo apt-cache policy postgresql-common

and install it

sudo apt-get install postgresql-common=151.pgdg12.4+1

we can get error that postgresql-client-common not installed
so we need to do the same
determine it version

sudo apt-cache policy postgresql-client-common

and install

sudo apt-get install postgresql-client-common=151.pgdg12.4+1

after this we can install postgresql like this

sudo apt-get install postgresql-9.3 postgresql-contrib-9.3


question regarding csv format? CSV format cannot be used for importing text files to a database unless the table for the data is already defined in the database.

is this true or false?

For MS SQL Server.
False: You can use the Import Data Wizard to map your columns in the CSV file to columns in the Table that the Wizard will create when you run it.


SQL Server 2008 Agent keep stopping for unknown reason My MSSQL 2008 service keep stopping don't know why. I took the habit to just restart the service, but it's getting really annoying.

Does anyone has a clue on why it keeps shutting down ?

Sql server 2008, logged as local.

Here are 4 SQLdump from today.

09:29:36 09/28/16

10:05:42 09/28/16

10:32:18 09/28/16

10:46:14 09/28/16

found this in my event viewer :

  SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).

Error when I tried to install MSSQL 2008 SP3 (x64)

  The version of the instance of SQL Server Shared Component does not match the expected version of the update to SQL Server. The version of the SQL Server product installed is 10.50.1600.1 and the version of SQL Server expected is 10.0.1600.22.

SELECT @@VERSION res

  Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
      Apr  2 2010 15:48:46
      Copyright (c) Microsoft Corporation
      Developer Edition (64-bit) on Windows NT 6.2  (Build 9200: )

Installation is on RTM of SQL Server 2008 R2. Try upgrading to currently supported Service Pack 3 https://www.microsoft.com/en-ca/download/details.aspx?id=44271


BULK INSERT getting "Cannot bulk load because ... Access is denied" on a file I have access to I'm trying to run a BULK INSERT but I'm getting an access denied error.  So far I've tried the following:

I can open the file from my machine
I'm logged into the SQL Server (tried v2008 R2 & v2016) using windows auth
I have sysadmin permissions
I can type the file using xp_cmdshell (uses the SQL service account but it eliminates the idea that it's a firewall)
Path is a network URL not a local drive

The command I'm using is this:

BULK INSERT ken_temp
FROM '\\network_path\temp\kdf\text_file.txt'
WITH
   (
    FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'
   ,ROWS_PER_BATCH = 50000
   ,MAXERRORS = 10
   )

Any ideas?

The problem is that:

You are logging in using Windows Authentication: this means that SQL Server will attempt to impersonate your Windows / Domain account for the file system access.
You have logged in directly to a work station, and then connected remotely to SQL Server: this means that you connected to the server running SQL Server indirectly, by passing your authentication token from your workstation to the server running SQL Server.
You have not enabled delegation for SQL Server: this means that you are stuck with the default behavior of not being allowed to pass that token beyond 1 step from the direct login.
You are trying to read a file that exists on a remote resource: this means that it is more than 1 step beyond where you directly logged in.

This is why you can access the file from your workstation just fine. And it is why using xp_cmdshell also works since the service account is logged in directly to the server running SQL Server, and the remote resource is just 1 step beyond.

I suspect that if you were to remote desktop directly into the server running SQL Server, run SSMS on that server, and then execute the BULK INSERT, that it would succeed. Or, it should also work if you set up a SQL Server Login, grant the Login the "bulk admin" permission, then EXECUTE AS Login = 'JustReadTheDangFileAlready';, and then do the BULK INSERT. When executing BULK INSERT / OPENROWSET (BULK...) as a SQL Server Login, there is no Windows account to impersonate, so the file system access is done using the service account, which will be just like xp_cmdshell.

You have the following choices:

Enable Delegation for SQL Server (can't remember if it's for the service or the account, in which case it might be your Windows account that needs it, but there are guides on how to set this up, which includes setting up the SPNs). I believe this is the preferred / recommended choice. This allows the process to forward the authentication tokens to resources further-removed from the direct login.
Use a SQL Server Login for this process. I can't believe that I'm suggesting this, but if you had a SQL Server Login that was in the "bulk admin" fixed server role OR had the correct "bulk admin" permission, then you could create the associated User for that Login (in an appropriate DB), and place the BULK INSERT in a stored procedure that was created using WITH EXECUTE AS 'JustReadTheDangFileAlready'. Then you could execute that proc which should mask that the request was coming from a Windows account.
Open up the permissions on that remote file (and maybe also the folder?). You could give "read" permission to "Everyone" (and maybe also "read" or some other permission on the folder).
Delete this question and use this "error" as justification for needing an expensive SAN that will be connected via HBA card and appear as a local drive 🙃. Just tell the CFO: "It's really the only way. The internet said so."


Can I determine if a stored procedure uses dynamic sql without parsing the definition? Since there seems to be no way to strip the procedure definition from comments  without parsing cf. this, there seem to be no reliably T-SQL query to answer this question.

Or can I get this information from any system view?

cf. my question about stripping comments

No. There is no flag or metadata about "UsesDynamicSQL"

You have to search the definition...

There are 2 ways to execute dynamic SQL

sp_executesql
EXEC (.. or EXECUTE ('

You can search for the first in sys.sql_modules, the 2nd using LIKE

WHERE
   REPLACE(definition, ' ', '') LIKE '%EXEC(%'
   OR
   REPLACE(definition, ' ', '') LIKE '%EXECUTE(%'
   OR
   definition LIKE '%sp[_]executesql%'


Select rows from my table where a character column matches a pattern I'm using Postgres 9.5. I have a name column of type "character varying".  I wonder how to select rows where the column begins with a number, followed by a space, followed by any other data.

So I would want rows where the column looks like:

12 DAVE
1 abcDEF
12345 LAST

But not when it looks like:

MYCOLUMN
1TEXT
=END

Use a regular expression:

SELECT *
FROM   tbl
WHERE  name ~ '^\d+ .+';

The regular expression explained:

^ .. start of string
\d  .. class shorthand for digits
+ .. 1 or more of the preceding atom
 .. plain space
.+ .. any character, again 1 or more of those

There are many different characters that look like a space. ' ' is just the plain space character. You might want to use the class shorthand \s instead to cover all of them.


Cannot install Oracle Grid on Oracle Linux 6.4 I have an Oracle database 11gr2 and I want to install Oracle Grid on Oracle Linux 6.4.
At first, I have installed Oracle Grid with the tutorial on this website:

https://eudba.wordpress.com/2012/08/10/installing-oracle-11g-on-oracle-linux-6-with-asm-eng/

When the grid installation is finished, I have an error that says: "the Oracle grid service can not start".

When I want to install the database with oracle user and with ASM, I have an error that I attached it to this question.

Can any one help me?

The "inappropriate ioctl for device" error probably means it doesn't like either the filesystem or the options with which is mounted. Check your ASM setup and that it actually is using ASM.


sp_help with different object types with the same name I have a table with the same name as a user-defined data type (UDDT), and when I use sp_help, only the table definition is shown.

Is there a way to use sp_help or an equivalent to show the definition for the UDDT instead?

sp_help only appears to have an @ObjName parameter, with no way to specify I actually want information about a type.

This script demonstrates the ability to create a table and a user-defined data type with the same name and under the same schema:

CREATE TYPE [dbo].[Address] FROM [nvarchar](40) NULL;
GO

CREATE TABLE [dbo].[Address]
(
    [AddressID] Int IDENTITY(1,1) NOT NULL
);

If you have two objects with the same name, it is almost certain that one is in a different schema. So to differentiate, you need to include the schema name in the call to sp_help as well.

EXEC sys.sp_help N'dbo.tablename';
EXEC sys.sp_help N'otherschema.udtname';

You should always be using schema-qualified object names anyway:

Bad habits to kick : avoiding the schema prefix

And in the case where you managed to create a table and a UDDT with the same name under the same schema:

I believe this is a bug in the engine. It should not allow me to create a data type and an object with the same name under the same schema. And I don't know why you would want to do that, even though it is allowed.
sp_help is certainly not prepared for that (and I don't see any chance for their investment in fixing it, especially if 1. is correct). It will return the information for the table, but not the type. The documentation mentions nothing of this, of course - so at the very least there is information missing in the docs, and perhaps you could also consider this a feature gap (sp_help could take type as an argument, but again, don't hold your breath).

So your options in the meantime are:

Rename the type.
Don't expect to use sp_help to get information about the type.
Write your own version of sp_help.

sp_help has the following basic logic, which you could change to include both:

-- @objname must be either sysobjects or systypes: first look in sysobjects
....
-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
....
    -- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
....
    -- IF NOT IN SYSTYPES, GIVE UP

Or you could add a parameter that lets you decide which object class is more important.

Of course you need to fix any line that uses TypePropertyEx(), an undocumented system function, to do this instead:

--'Prec'  = Convert(int,TypePropertyEx(user_type_id, 'precision')),
[Prec] = [precision],
--'Scale' = Convert(int,TypePropertyEx(user_type_id, 'scale')),
[Scale] = [scale],
....
FROM sys.types
...

Why they use TypePropertyEx() here, I don't have a clue.

But, in all honesty, the best solution is to rename your type. Who knows what other conflicts you'll come across.


Querying on multiple joined rows I have a table called visits that is structured like so:

+----+--------------+-------------+
| id | name         | visitor_id  |
|----+--------------+-------------+
| 1  | event1       | 1           |
| 2  | event2       | 2           |
+----+--------------+-------------+

I have a table called params that is structured like so:

+----+--------------+---------------+---------------+
| id | a_key        | a_value       | visit_id      |
|----+--------------+---------------+---------------+
| 1  | key1         | value1        | 1             |
| 2  | key2         | value2        | 1             |
| 3  | key3         | value3        | 1             |
| 4  | key1         | value1        | 2             |
| 5  | key2         | value2        | 2             |
+----+--------------+---------------+---------------+

This query looks for visits with params that are 'key1' and 'value1'.

select *
from visits
right join params as p on p.visit_id = visits.id
where a_key = 'key1' and a_value = 'value1'

I need to write a query that will give me all the visits that have params 'key1', 'value1' and also 'key3', 'value3'. In this case it would only return visit 1.

What is the most performant way to achieve this?

I thought about joining the params on again, but that doesn't seem like the best solution.

Here it is in a fiddle:

http://sqlfiddle.com/#!15/06773/5

It's a case of relational-division.
We have assembled an arsenal of query techniques in this related answer on SO:

How to filter SQL results in a has-many-through relation

For your case this query will be among the fastest possible solutions:

SELECT v.*
FROM   params p1
JOIN   params p3 USING (visit_id)
JOIN   visits v  ON v.id = p3.visit_id
WHERE  p1.a_key = 'key1' AND p1.a_value = 'value1'
AND    p3.a_key = 'key3' AND p3.a_value = 'value3';

If you just need the visit_id you don't even need to join to table visits.

db<>fiddle here
Old sqlfiddle.


Does there exist a user with this role? I'm trying to write a query which will return a boolean value if there exists any user which has this specific role.

For example is there any user hwo's role is 'role1'

This is how far i got

SELECT CASE WHEN EXISTS
(SELECT --'Here i don't know how to select all user's which have a specific role'--    )
GO

And if it's possible to check user's from only one database .

It's old code, but it checks out:

SELECT
    user_name(member_principal_id) as memberName
FROM
    sys.database_role_members
WHERE
    user_name(role_principal_id) = 'db_owner'

So, in full:

    DECLARE @roleName varchar(20) = 'MF_Pilots'
    DECLARE @user varchar (20) = 'Rey'
SELECT CASE WHEN EXISTS
    (SELECT
    user_name(member_principal_id) as memberName
FROM
    sys.database_role_members
WHERE
    user_name(role_principal_id) = @roleName
AND
    user_name(member_principal_id) = @user)
    THEN 1
    ELSE 0
    END

That'll do what you want. Stick it in a function and profit.

Taken from https://stackoverflow.com/questions/3361778/get-list-of-all-database-users-with-specified-role


Column A or Column B required MS Access DB I'm in the situation where I would like to enforce the requirement where a record must contain either one column or another at a data level, I'm not sure whether it's achievable.

I've got an Enquiry table which is used for new enquiries being recorded; this can either be based on an existing customer, which is contained within the Customer table or prospective customer which will be created and stored within a ProspectiveCustomer table.

 Enquiry
-----------------------------------------------
 EnquiryID  | CustomerID | ProspectID | Notes |
       3    |     2      |     null   |   x   |
       4    |     null   |     3      |   y   |

There must be a record number in either CustomerID or ProspectID, but not in both. Is this achievable at a data level alone? or would I have to simply validate my inputs in this way and hope for the best?

I think of a scenario, where you can have a MasterUser table that will asign ID to both Customer and Prospect, auto incremented PKId can be named as UserID of the table.

let Say you create a table MasterUser and stored common details like names etc. that both Customer and Prospect have, now the unique info can be better moved to table Customer and ProspectiveCustomer.

Now as you have uniqueId for each user, no matter the user is Prospect or Customer.

So you Enquiry table will be something like

Enquiry
-----------------------------------------------
 EnquiryID  | UserID| Notes |
       3    |    2  |   x   |
       4    |    3  |   y   |


Snapshot folder Access I have set up a Transaction Replication, with the distribution server as subscriber itself and implemented pull subscription, to avoid load on my publisher.

When I created publisher at my source server, I got snapshot agent error saying that it cannot access the default D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL folder location. So I changed and gave a file location at Subscriber E:\ drive and it worked.

My question here is, if it is pull subscription with distribution server as subscribe:

Where should the snapshot folder be?

Because I see access issue.

Please comment if I am not clear.

You could have used the default location by mentioning it an UNC path. Snapshot Agent job will need write and Distribution Agent job will need read privilege. Snapshot file location (directory) is a property of Publication, keep that in mind when you mention alternate location.

When configuring a Distributor through the Configure Distribution Wizard or the New Publication Wizard, the snapshot folder defaults to a local path: X:\Program Files\Microsoft SQL Server\\MSSQL\ReplData. If you are using a remote Distributor or pull subscriptions, you must specify a UNC network share (such as \\snapshot) rather than a local path.

Reference:

Specify an Alternate Snapshot Folder Location (SQL Server Management
Studio)
Alternate Snapshot Folder Locations

  The Snapshot Agent must have write permissions for the directory you
  specify, and the Distribution Agent or Merge Agent must have read
  permissions. If pull subscriptions are used, you must specify a shared
  directory as a universal naming convention (UNC) path, such as
  \computername\snapshot.

Alternate snapshot locations enable you to store snapshot files in a location other than, or in addition to, the default location, which is typically located on the Distributor. Alternate locations can be on another server, on a network drive, or on removable media such as CD-ROMs or removable disks.

More about securing snapshot folder.

Secure the Snapshot Folder


SQL Alter Index within a Script I posted this in StackExchange and it was mentioned that it should be moved to this exchange. So here it is:

I've been reading about SQL Index Fragmentation and how to rebuild/reorganize. I'm curious on how to write a script about it. I have a script that will generate the table with the table index and display the fragmentation in percent:

SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                             AND indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.database_id = DB_ID()
ORDER BY
    indexstats.avg_fragmentation_in_percent desc

Can I add ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD to this script or do I have to write a new script.

I found http://sqlfool.com/2011/06/index-defrag-script-v4-1/ but am not sure on how I use it properly.

I'm new to database administration and am trying to setup a maintenance plan that will rebuild indexes when they hit a certain threshold.

Thank you in advance...Cheers!

That page count number is really not a thumb rule, it's more an arbitrary one. I am not able to recall Paul Randal's blog where he has explained that this is a made up number to set up a threshold.

For bigger tables yes you can have the Ola's job run as per the maintenance schedule you have. For smaller ones having page count <1000 should not be a concern unless you see some major performance blips because this operation comes with a extra cost in terms of resource utilization.

Also as far as I can recall rebuilding small indexes might not help in lowering the fragmentation due to allocation of mixed extents, unless you have the trace flag for that enabled (trace flag 1118).


How can I obtain the full text for a query that appears in sys.dm_pdw_exec_requests? I'm trying to tune a DB in Azure SQL Datawarehouse for queries produced by Power BI, which can connect directly to Azure SQL Data warehouse. I'm interested in studying execution plans, tweak things like indexes and statistics, retry queries to test changes and so on. A typical way to do this is by looking at sys.dm_pdw_exec_requests system view to get the execution times of queries, their text and their ID, in order to then study their steps with sys.dm_pdw_request_steps. Some of these queries are longer than 4000 characters because of the typical automated query generation syntactic clutter.

Microsoft documentation states that "command" column holds up to 4000 characters, so any query bigger than that is truncated.

... So I cannot study a query longer than that

I have not been able to find a way to get the queries yet. Any ideas?

This came up the other day and the reply was "switch on Auditing":

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-auditing-overview

I have not tried this myself yet so I'm not sure if you get the full text of the query.  I plan to test this feature in the next few weeks so will update the post, or you can : )

I have been having similar issues with Power BI queries which seem overly complicated, but I suspect this is a consequence of an overly complicated Power BI model.  One alternative I have thought of (but not yet done) is to create a schema-only model of the database in SQL Server 2016.  You could accurately reflect the columnstore, heap or clustered index and partitioning index choices, but not the distribution and data movement aspects.  This would at least give you access to tools like Extended Events (or Profiler if you're old-school), visual Execution Plans (although you can you EXPLAIN plans), bearing in mind the database engines are different.  I am yet to determine if the effort of creating and maintaining the copy is worth it, but I will update if I progress my thinking on this.

This was also discussed here:

https://stackoverflow.com/questions/41292662/identifying-needed-statistics-azure-sql-data-warehouse


MongoDB: move documents before capping The cappedCollection concept works well for most of my projects where cleaning old data without care makes sense.

For another projects, I need a more complex and safe concept. The requirement is nearly the same as logrotate.
The data is appended to the main collection, without compression/compact and no index except a timestamp for simple queries by time. => The focus is on writing and persistent data.

Similar to the logrotate rules, I'd like the main collection not to become too large => capped by size; if possible, capping by timestamp might be a plus.

This sounds like a cappedCollection, but I do not want any data loss when it's capped. The old data should be stored into another db's collection that must be compact:true and a non-capped collection. It's name depends on the current month and makes sure that there will be max 12 "archive" collections per year.

Example:

liveDB.mainCollection_capped grows and starts capping.

Before removing old documents, these are savely moved into archiveDB.compactArchiveCollection201303.

No data is lost and the main collection remains small and fast. Storing the data in another database avoids db locks, e.g. repairDatabase tasks on an archive file will not affect or delay the main collection.

Is there a good practice or how to achieve this - as reliable and automated as possible - without writing all the data transfer for a cronjob which handles the data transfer but should never ever be missed because data is lost if capping starts before old data is copied into the archive.

For the first part, expiring based on a timestamp, you will want to check out TTL collections (requires version 2.2+).  They won't cap size like a capped collection, but let's you set the constraints via time as long as you have a BSON Date type field to index on.

For the second part, automatically storing them to a new database before expiry, I think what you need is a periodic query (via cron, at, or other and in the language of your choice) to run and grab those documents before they expire.  This should just be a simple range based query over time, find the appropriate docs, insert to new DB, always at enough of an offset to avoid missing documents.

The other approach I can think of would be to tail the oplog, look for deletes on that collection, and then transcode them into inserts on a different DB.

For extra safety you could run a secondary on a slavedelay to give an extra window if one of the approaches above failed.

I don't know of anything that will do this by default, but I believe either of the approaches above should work.


70-761 Practice exam Question about @@Trancount final value There is this question in the practice 70-761 exam that states after executing the following script, what is the value of @@Trancount?

Exam script:

CREATE PROCEDURE dbo.up_CreateSalesInvoice
(
    @Date DATE,
    @CustomerID INT,
    @StockitemId INT,
    @Quantity INT,
    @UnitPrice DECIMAL(8, 2),
    @InvoiceID INT OUT
)
AS
BEGIN
    DECLARE @RETVAL INT;
    BEGIN TRANSACTION;
    BEGIN TRY

        BEGIN TRANSACTION;
        INSERT INTO dbo.SalesInvoice(InvoiceDate, CustomerID)
            VALUES (@Date, @CustomerID);
        SET @InvoiceID = SCOPE_IDENTITY();
        COMMIT TRANSACTION;

        BEGIN TRANSACTION;
        INSERT INTO dbo.SalesInvoiceLine(InvoiceID, StockItemID, Quantity, UnitPrice)
            VALUES (@InvoiceID, @StockItemID, @Quantity, @UnitPrice);
        COMMIT TRANSACTION;

        COMMIT TRANSACTION;
        SET @RETVAL = 0;
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @RETVAL = 1;
    END CATCH

    RETURN @RETVAL;
END;

The answer I came into conclusion is 0, but the expected answer is 1, so i moved to replicate the script behavior with the following script, and the result returned from @@Trancount is 0, unless my script is doing something different with the nested transactions if anyone can explain if the expected answer is wrong or if my logic emulating the nested transaction of the script i got something wrong witch gives me a different result

Script emulating the behavior:

CREATE TABLE #TBL(
    ID INT,
    TANK VARCHAR(12)
)

INSERT INTO #TBL VALUES (1, 'T-55-MA')

PRINT 'TRANCOUNT INICIAL :: ' +  CAST(@@TRANCOUNT AS VARCHAR)

BEGIN TRANSACTION  -- +1
PRINT 'BEGIN TRANSACTION 1 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
BEGIN TRY
    BEGIN TRANSACTION  -- +1
    PRINT 'NESTED BEGIN TRANSACTION 1 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
    INSERT INTO #TBL VALUES (2, 'M4A3-W(76)')
    COMMIT TRANSACTION  -- -1
    PRINT 'NESTED COMMIT 1 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)

    BEGIN TRANSACTION -- +1
    PRINT 'NESTED BEGIN TRANSACTION 2 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
    INSERT INTO #TBL VALUES (3, 'LEOPARD-A1')
    COMMIT TRANSACTION -- -1
    PRINT 'NESTED COMMIT 2 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)

    COMMIT TRANSACTION -- -1
    PRINT 'NESTED COMMIT 3 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'ROLLBACK EN CATCH _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
END CATCH

PRINT 'FINAL TRANCOUNT _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR) --This returns 0
SELECT * FROM #TBL

DROP TABLE #TBL

Edit posting the reasoning for the answer provided by the exam:

The reasoning for the answer to be 1 provided by the exam is the following:

  @@TRANCOUNT is set to 1, When the store procedure is run,
  just one transaction is executed, which increments @@TRANCOUNT to 1.
  Although there is a nested transaction within the outer transaction,
  SQL Server does not actually create a nested transaction.

But running the whole script and printing @@TRANCOUNT on every commit and begin tran it always prints 0 once it reach the end of the script, the
nested transaction commits are decreasing the @@TRANCOUNT value while
the nested BEGIN TRAN are increasing the value, though is true that
SQL Server point nested transactions to the top transaction.

Thanks in advance

The expected answer provided in the practice exam is incorrect. Microsoft's own documentation spells out clearly the same thing you've found - every BEGIN TRANSACTION increments @@TRANCOUNT by 1, every COMMIT TRANSACTION decrements it by 1 and ROLLBACK TRANSACTION resets @@TRANCOUNT to 0.

Nested transactions count and increment the @@TRANCOUNT value by 1 as well, so the statement that SQL Server doesn't actually create a nested transaction is also false.

See here for more info. It also does not make a difference if the code is in a stored procedure or not.


Index optimization for IN, BETWEEN, ORDER BY and LIMIT query Brief Background of issue

We have a aggregate table to store activities and have currently 6.6 Million rows and we need create listing from this table.

This activity listing have features of filtering as well as sorting on different different columns.

Data in this table Meaning Activities count is increasing at 1 million/month rate.

We are using MySQL with version 5.6

Table structure is as follows

CREATE TABLE `filter` (
  `source_id` bigint(20) unsigned NOT NULL,
  `entity_id` varchar(40) NOT NULL,
  `type` int(11) NOT NULL,
  `metrics_1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `metrics_2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `metrics_3` int(11) unsigned NOT NULL DEFAULT '1',
  `posted_on` datetime NOT NULL,
  `updated_on` datetime NOT NULL,
  PRIMARY KEY (`source_id`,`type`,`entity_id`),
  KEY `indx_posted_on` (`posted_on`,`source_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Primary Key for this table is

PRIMARY KEY (`source_id`,`type`,`entity_id`)

Query we are using to retrieve list of activities is

SELECT
    filter.`entity_id`
FROM filter AS filter
LEFT JOIN act_deleted AS act_deleted ON act_deleted.entity_id = filter.entity_id
WHERE
    filter.source IN(211,493)
    AND filter.`type` = 1
    AND filter.posted_on BETWEEN '2015-01-29 00:00:00' AND '2016-08-12 23:59:59'
    AND filter.metrics_1 BETWEEN 0 AND 9999999999
    AND act_deleted.entity_id IS NULL
ORDER BY filter.`posted_on` DESC
LIMIT 5000, 50;

NOTE
act_deleted contains deleted rows. we do not want to show deleted rows to perticular user.

type, metrics_1, metrics_2 condition is dynamic, if user select this filter, it will be there in WHERE part.

INDEX created for this query is

I read about indexes and created this index for the above query as we have sorting on posted_on for this query.

`indx_posted_on` (`posted_on`)

Execution Plan

Issues we facing are as follows

1) First time query execution time is about 28 seconds even with index.

Time Taken

NOTE consecutive query execution time is 1 second with same indexes. I read mysql buffers the table data and so consecutive query execution is faster.

2) There can be high (30 sources max) in IN part of the query and when i do that, applied indexes gets change in query execution plan and execution time increases to about 15 seconds. should be under 2 seconds.

Output of Explain for this scenario as follows

Time Taken

Solution can be for this issue is to use FORCE INDEX. is it okay if i use force index ?

3) Most important issue is i need to add sorting on metrics_1, metrics_2 field also.
do i need to create seperate indexes for that ?
Concern is if i make seperate indexes for each scenario it increases size of table and it results in deadlock while data insertion. (Currently table is about 10 GB in size)

UPDATE
Most simple query would be (without any filter by user)

SELECT
    filter.*
FROM `filters` AS filter
LEFT JOIN act_deleted AS act_deleted ON (act_deleted.entity_id = filter.entity_id AND act_deleted.user_id = 1)
WHERE
    filter.source_id IN (211,493,527,505,554,465,561,565,529,537,504,485,542,590,488,533,468,545,477,547,569,521,513,461,663) AND
    (filter.posted_on BETWEEN '2015-07-29 00:00:00' AND '2016-08-12 23:59:59') AND
    act_deleted.entity_id IS NULL
ORDER BY filter.`posted_on` DESC / ORDER BY filter.`metrics_1` DESC / ORDER BY filter.`metrics_2` DESC
LIMIT 0, 20;

UPDATE

When i add more indexes, it increases table size (currently about 10 GB TOTAL size where 2 GB of data and 8 GB index size)) and i'm getting dead lock in update part in one same query.

When i tried to update metrics_1 in batch, it results in dead lock few times.
Is it because of MORE INDEXES or can be some other reason ?

Please Let me know if any extra information required.
Any help will be appreciated.

Thanks

Shrinking the table size will help performance:

Don't use BIGINT unless you expect to need more than 4 billion ids; INT UNSIGNED takes half the space.
Don't use INT (4 bytes) for low-cardinality flags/types/levels(?)/etc.  ENUM or TINYINT UNSIGNED is only 1 byte.

Write a little more code to avoid useless parts to the WHERE, such as AND filter.metrcs_1 BETWEEN 0 AND 9999999999.

Pagination via OFFSET (LIMIT 5000, 50) is problematic.  See my blog

Don't USE INDEX or FORCE INDEX; it may help today, but it is likely to hurt tomorrow.

It is rarely useful to start with a column that will be used in a 'range':  KEY indx_posted_on (posted_on,source_id)

For that query: INDEX(type, source_id, posted_on),INDEX(type, posted_on).  The first of those covers the case where you have only onesource_id.  (Does such happen?).  The second one is better thanINDEX(posted_on)`.

More on optimal indexes  To summarize:  =s first, optionally INs, and one range last.  But the IN keeps the ORDER BY from being consumed.

Also, be sure that innodb_buffer_pool_size is about 70% of available RAM; this may help cut back on I/O.

UPDATE (based on OP's UPDATE):

INDEX(source_id, posted_on) -- IN + range -- should help with WHERE
INDEX(posted_on) -- part of WHERE, plus may consume ORDER BY

The Optimizer will pick between the two.

If AND metrics_1 BETWEEN... is added in, the performance will be worse.  So, keep it out of the query if the user does not need it.

Since you have a large set of possible queries, I recommend building a few indexes:

INDEX(posted_on) -- to handle the ORDER BY, in case nothing else works well
INDEX(const, posted_on) -- to partially handle WHERE, plus ORDER BY
INDEX(const, range) -- a few 2-column indexes;
          first is something used with '=', second is BETWEEN (or other range)
INDEX(in, range) -- a few more 2-column indexes;
          first is something used with 'IN', second is a range

Aim for no more than 10 total.  Watch what users usually ask about as a guide to which 'const'/'in'/'range' columns to index.  And/or look at what queries are "too" slow.

Don't say filter.* unless you really need all the columns.  In particular, if all TEXT columns can be omitted, there may be a performance gain in how temp tables are handled.

For large tables with text columns, this "lazy eval" may help performance:

SELECT filter.*
    FROM (
        SELECT primary-key-fields-of-filter
                  rest-of-existing-SELECT
                  order-by-limit
         ) x
    JOIN filter USING(primary-key-fields-of-filter)
    ORDER BY repeat-the-order-by-but-not-limit


How to count hierarchical rows count with a CTE Suppose that I have two tables Region and Devices.  Region provides tree structure (by self referencing a ParentRegionId foreign key).  Every device belongs to one region (by the RegionId foreign key).

See this fiddle for a better understanding of the tables.

I need to count 'Region''s devices including nested ones similar to this:

+---------+------------+--------------+
| RegioId | RegionName | DevicesCount |
+---------+------------+--------------+
|       0 | Region0    |            5 |
|       1 | Region1    |            2 |
|       2 | Region2    |            2 |
|      11 | Region11   |            1 |
|      12 | Region12   |            0 |
|      21 | Region21   |            0 |
|      22 | Region22   |            1 |
+---------+------------+--------------+

Here's the trick you're after - use the CTE to build a list of each region, cross referenced with every region which counts towards it:

WITH cte AS (
  SELECT [ID] AS [PID],[ID] FROM [dbo].[Regions]
  UNION ALL
  SELECT cte.[PID], r.[ID]
  FROM cte
    JOIN [dbo].[Regions] r ON r.[ParentRegionID] = cte.[ID]
)

SELECT [PID], [ID]
FROM cte
ORDER BY 1,2

And then let's use it as a cross-reference table to count devices:

WITH cte AS (
  SELECT [ID] AS [PID],[ID] FROM [dbo].[Regions]
  UNION ALL
  SELECT cte.[PID], r.[ID]
  FROM cte
    JOIN [dbo].[Regions] r ON r.[ParentRegionID] = cte.[ID]
)

SELECT
  r.[ID] AS [RegioId],
  r.[Name] AS [RegionName],
  count(d.[ID]) AS [DevicesCount]
FROM
  [dbo].[Regions] r
  LEFT JOIN cte x ON r.[ID] = x.[PID]
  LEFT JOIN [dbo].[Devices] d ON x.[ID] = d.[RegionId]
GROUP BY
  r.[ID],
  r.[Name]
ORDER BY r.[ID]

Gives:

| RegioId | RegionName | DevicesCount |
|---------|------------|--------------|
|       0 |    Region0 |            5 |
|       1 |    Region1 |            2 |
|       2 |    Region2 |            2 |
|      11 |   Region11 |            1 |
|      12 |   Region12 |            0 |
|      21 |   Region21 |            0 |
|      22 |   Region22 |            1 |


Getting SQL Server Agent to run multiple instance of a job at a time As I understand it, SQL Server Agent runs only one instance of a job (SSIS Package in this case) at a time.

I need it to Start a job 15 times, and then let them all run at the same time.

Up to now, I have accomplished this quite successfully, manually, by right clicking on the job and selecting "Execute". But i need to automate this now.

I have tried to schedule the job to start 15 times,  but it only
works sequentially. (1 job after the other)
I created 15 separate jobs and started them, but the first one starts
and the others not, stating the job is already in use.

Should I look at another scheduling tool to accomplish this?

Any pointers would be welcome.....

As stated in the Note of MSDN Create and Attach Schedules to Jobs

  Only one instance of the job can be run at a time. If you try to run a job
  manually while it is running as scheduled, SQL Server Agent refuses the
  request.

Then I'd rather rewrite the SSIS package to do the paralelism inside the package. As I  don't use SSIS - except with MAintenance Plans - I cannot tell you how to do that.


Potential issues migrating from SQL Server 2014 to 2012 by scripting tables and data? I have a very simple question.

What would be the typical "gotchas" involved with scripting all the data to migrate a 2014 SQL server database to 2012?

Is this a pretty solid process? Are there potential issues of the ordering of scripts being messed up based on FK relations and other rules?

Have there been times of "Oh man, didn't see that coming" some time later when you run into some type of conflict because of the migration that wasn't caught previously?

With business critical data, is this unwise to do? It's a cost/benefit situation of upgrading and licensing for a newer SQL.

You shouldn't have any problems scripting your database out and running it on the new database.  There isn't any data for foreign keys to affect.  After that you will have to be more careful to load the data in the correct order.  Lookup tables first, then data tables etc.

That isn't a huge issue since you can get the order down in test before trying it in production.  Where you may have your biggest problems is in any code/features that you are using that are new to 2014.  Follow the link and review the new features and search for them.  After that it's just testing, testing, testing.  Try it out in test and see what happens.  Document your changes, change them in production to remove any issues, and then start your move.


What does having the primary key as the last column in a composite secondary index in an InnoDB table do? Say I have a 1-to-N relationship (person_id, pet_id).  I have a table where pet_id is the primary key.

I understand that an InnoDB secondary index is essentially a B-tree where the values are the corresponding primary key values for the row.

Now, suppose one person can have thousands of pets and I often want a person's pets in order of pet_id.  Then it would matter if records in the secondary index are sorted by (person_id, pet_id) or just person_id with the pet_id's for that person_id being unsorted.  Guessing the later.

So, if person_id is non-unique, are records physically sorted by (person_id, pet_id) or JUST pet_id?

Thanks

No. If your table has the InnoDB engine and the PRIMARY KEY is (pet_id), then defining a secondary index as (person_id) or (person_id, pet_id) makes no difference.

The index includes the pet_id column as well so values are sorted as (person_id, pet_id) in both cases.

A query like the one you have:

SELECT pet_id FROM yourtable
WHERE person_id = 127
ORDER BY pet_id ;

will need to access only the index to get the values and even more, it won't need to do any sort, as the pet_id values are already sorted in the index. You can verify this by looking at the execution plans (EXPLAIN):

First, we try with a MyISAM table:

 CREATE TABLE table pets
 ( pet_id int not null auto_increment PRIMARY KEY,
   person_id int not null,
   INDEX person_ix (person_id)
 ) ENGINE = myisam ;

INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets
               WHERE person_id = 2
               ORDER BY pet_id asc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pets
         type: ref
possible_keys: person_ix
          key: person_ix
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

Notice the filesort!

Now, MyISAM with composite index:

 DROP TABLE IF EXISTS pets ;

 CREATE TABLE table pets
 ( pet_id int not null auto_increment PRIMARY KEY,
   person_id int not null,
   INDEX person_ix (person_id, pet_id)            -- composite index
 ) ENGINE = myisam ;

INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets
               WHERE person_id = 2
               ORDER BY pet_id asc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pets
         type: ref
possible_keys: person_ix
          key: person_ix
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using index
1 row in set (0.00 sec)

Filesort is gone, as expected.

Now lets try the same with InnoDB engine:

 DROP TABLE IF EXISTS pets ;

 CREATE TABLE table pets
 ( pet_id int not null auto_increment PRIMARY KEY,
   person_id int not null,
   INDEX person_ix (person_id)            -- simple index
 ) ENGINE = innodb ;                      -- InnoDB engine

INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets
               WHERE person_id = 2
               ORDER BY pet_id asc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pets
         type: ref
possible_keys: person_ix
          key: person_ix
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using index
1 row in set (0.00 sec)

No filesort either! Even though the index does not explicitly have the pet_id column, the values are there and sorted. You can check that if you define the index with (person_id, pet_id), the EXPLAIN is identical.

Lets actually do it, with InnoDB and the composite index:

 DROP TABLE IF EXISTS pets ;

 CREATE TABLE table pets
 ( pet_id int not null auto_increment PRIMARY KEY,
   person_id int not null,
   INDEX person_ix (person_id, pet_id)    -- composite index
 ) ENGINE = innodb ;                      -- InnoDB engine

INSERT INTO pets (person_id)
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets
               WHERE person_id = 2
               ORDER BY pet_id asc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pets
         type: ref
possible_keys: person_ix
          key: person_ix
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using index
1 row in set (0.00 sec)

Identical plans with the previous case.

To be 100% sure, I also run the last 2 cases (InnoDB engine, with single and composite indexes) enabling the file_per_table setting and adding a few thousands rows in the table:

DROP TABLE IF EXISTS ... ;
CREATE TABLE ... ;

mysql> INSERT INTO pets (person_id)
       VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id)
       VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id)
       VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id)
       SELECT a.person_id+b.person_id-1
       FROM pets a CROSS JOIN pets b CROSS JOIN pets c ;
Query OK, 54872 rows affected (0.47 sec)
Records: 54872  Duplicates: 0  Warnings: 0

In both cases, checking the actual file sizes, yields identical results:

ypercube@apollo:~$ sudo ls -la /var/lib/mysql/x/ | grep pets
-rw-rw----  1 mysql mysql     8604 Apr 21 07:25 pets.frm
-rw-rw----  1 mysql mysql 11534336 Apr 21 07:25 pets.ibd


How to install ROracle on Linux I am trying to install the ROracle library. This fails:

install.packages("/storage/Downloads/ROracle_1.1-11.tar.gz", repos = NULL, type = "source")

** package ‘ROracle’ successfully unpacked and MD5 sums checked
configure: error: OCI libraries not found

I have downloaded the Oracle Instantclient RPMs and installed them via alien. Then,

export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
R CMD INSTALL ROracle_1.1-11.tar.gz

Warning: invalid package ‘ROracle_1.1-11.tar.gz
Error: ERROR: no packages specified

How can I install the ROracle package on a Ubuntu 64 system?

Try this

R CMD INSTALL --configure-args='--with-oci-lib=/usr/lib/oracle/12.1/client64/lib
  --with-oci-inc=/usr/include/oracle/12.1/client64'  ROracle_1.1-11.tar.gz


Operation System error 665 with VLDB backup (The requested operation could not be completed due to a file system limitation) Has anyone seen the following error while doing backups? We have tried different LUNS from different storage pools and are seeing the same result.

Here is the error message:
Operating system error 665 (The requested operation could not be completed due to a file system limitation).

NTFS Compression is not enabled on the drives. This failure usually comes when the backup is about half completed. I believe when the backup file size is extended again.  We are striping against eight files. The database is 60+ TB. I also saw some references to file fragmentation and I see that these drives show 0% percent.

I also saw several references to DBCC CHECKDB and sparse files. We are just doing a full backup and not any DBCC CHECKDBs. The goal would be to do this on a secondary server until any errors are recorded.

This error usually means that NTFS cannot add more fragments to the file being written to. Normally, this happens for CHECKDB sparse files but it could happen to any file. It is not sparse file specific.

I suspect your NTFS volume is already heavily fragmented. If there are many free space holes NTFS is prone to create many small fragments for new files. I have just seen a 16GB paging file with 120,000 fragments that was created on such a volume.

There is no way to tell NTFS to allocate more smartly. (Subjective note: NTFS is really stupid when it comes to allocating files. Many simple improvements come to mind but no investment is being made.)

Use a defrag tool that compacts the drive and removes holes. O&O Defrag can do that for example. Of course you can manually compact by recreating the partition. But I don't know how realistic that is at your storage sizes.

  I also saw some references to file fragmentation and I see that these drives show 0% percent.

But the free space could be fragmented. That's where new data goes.


Replacing NULL values with one string from provided list I am trying to replace NULL values in table by using SELECT with one (randomized?) string from the set that I provide to the compiler.

Example:

|id | date |
+---+------+
| 1 | 2017 |
| 2 | NULL |
| 3 | NULL |

I want the NULL values to be '2015', '2012', etc, taken randomly from the set I specify.

Unfortunately COALESCE returns only first not null value rather than one from the specified. Is there some function that works like:

IF column_value = 'NULL (or something)' THEN RAND(string1, string2, string3, string4).

Thanks in advance.

You can use something like

SELECT Id,
       ISNULL(Date, (SELECT TOP 1 *
                     FROM   (VALUES(2015),
                                   (2012)) R(Ryear)
                     ORDER  BY CRYPT_GEN_RANDOM(DATALENGTH(Id))))
FROM   YourTable

The reference to Id in the ORDER BY is just to make it correlated so that SQL Server is less likely to spool the result and replay it for multiple rows.
Select a column that is unique for this.

BTW: There is a CHOOSE function that looks tempting but when this gets expanded out to CASE the random number function gets copied out too and so this is not suitable for the task.

Don't use this

SELECT Id,
       CHOOSE(1 + ABS(CRYPT_GEN_RANDOM(4) % 2),2015,2012)
FROM   YourTable

Because it is evaluated as

CASE
  WHEN (1 + ABS(CRYPT_GEN_RANDOM(4) % 2)) = 1
    THEN 2015
  ELSE
    CASE
      WHEN (1 + ABS(CRYPT_GEN_RANDOM(4) % 2)) = 2
        THEN 2012
      ELSE NULL
    END
END

(And CASE ABS(CRYPT_GEN_RANDOM(4) % 2) WHEN 0 THEN 2012 WHEN 1 THEN 2015 END would have the same problem)


Clustered Index Maintenance vs. Nonclustered Index Maintenance There is a plethora of information available detailing why an IDENTITY field should be use as both the primary key and clustered index of a table for most situations; yet I'm having difficulty deciding if my particular situation is an exception.

I have a table with around 250 columns—I don't want to start a normalization debate—and around 100M rows. The table is page compressed. On peak days, around 1M rows are entered into the table sequentially with 0 contention from other connections. The combination of a char(2) NOT NULL field and an int NOT NULL field is guaranteed to be unique and never changing. There are only about 10 unique values for the char(2) field.

My argument for an IDENTITY column being used as the clustered index is solely performance based. Since inserts will happen sequentially, I shouldn't haven't to worry about latch contention; thus the inserts should be faster since there will be no need to search the index. On the other hand, a composite key of 6 bytes is rather small; and I'll end up making it the primary key anyway (with the char(2) field first). Furthermore, there will be a few other tables that are bulk loaded based on the new rows in this table (likely via a filtered index). If I use an IDENTITY column, I'll likely use that column as the clustered index and primary key of these other tables (without the IDENTITY property) and not bring over the "natural" composite key.

Does making a nonclustered index on the composite key nullify the expected speed boost of using an IDENTITY column as the clustered index since this index will have to be searched when the inserts are done?

Edit

Based on the comments, I changed the title and am asking an amended question whose answer can be used to answer the original question above. How does maintenance of a unique clustered index compare to maintenance of a unique nonclustered index in regards to inserts? Does a non-sequential clustered index suffer similarly to a non-sequential nonclustered index?

Embarrassingly, I never knew of columnstore indexes. This table is primarily used for analytics; thus I used a clustered columnstore index with a primary key defined on the char(2) and int fields as per the suggestion from David Browne - Microsoft in the comments.

Since the question was about clustered index maintenance compared to nonclustered index maintenance, I'll "answer" that part to the best of my ability. From what I've read, it takes more resources when a clustered index has a page split than a page split on a smaller nonclustered index (with the same key). This is because the page contains far more data in the clustered index. Consequently, had I not used a columnstore clustered index, I would have used (and tested) an IDENTITY column for the clustered index. Having said that, the video
sp_BlitzErik provided in the comments is very informative—and easily digestible for plebs like me—and highlights how there is a lot of outdated recommendations "out there" based on older implementations of SQL Server; thus concerns about external fragmentation can be exaggerated.


How would you track DB storage use and then extrapolate it? I need to track the size of various DBs we have, then see it's growth rate so that we can guess where it will be at certain times. I'm using Oracle 11g first, but will eventually be applying this to other DBMSs like DB2 and Sybase.

I'm not even sure where to begin or what commands/queries I'll need to run.

You just need to start by collecting data.

There are a few different valid ways of judging the size of the database -- number of rows in each table, size of table and index extents, size of data files -- so first you have to define what decisions you intend to make based on the data. Do you want to know when you'll need to add storage? Or when you'll need to extend data files?


How to select from a table where in a list and then diff that against the same list? Here is what I need:

I have a LIST of keys:

A
B
C
D

A TABLE keys that contains:

A
B
**... and millions of other entries** ( but `NOT` `C` or `D` )

I want to see what is in the list AND NOT IN the table AND list.

the expected output would be:

C
D

Since C and D are in the LIST AND IN the TABLE.

I have found examples of how to create a table of a type and wrap the list I just could not get the syntax correct by putting together disparate examples to get what I want and they all failed.

Here is what I am stuck on:

  [42000][904] ORA-00904: "MISSING_KEY": invalid identifier

CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);

SELECT missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))
WHERE missing_key NOT IN (
    SELECT key
    FROM schema.keys
    WHERE key IN ('A','B','C','D')
);

How do I give the column from the MISSING_KEYS TYPE a name?

If you only need your list to have the keys (I'm using minus to find the difference between the two lists, you could modify it to use NOT IN instead if you'd rather)

create type tbl_keys2 is table of varchar2(10);
/

create table tbl (
  key varchar2(10)
);

insert into tbl values( 'list' );
insert into tbl values( 'of' );

with keys as (
  select *
    from table( tbl_keys2( 'list', 'of', 'some', 'keys' ))
)
select k.column_value key
  from keys k
minus
select t.key
  from tbl t;

If your list potentially needs to have multiple columns (i.e. a key and a value where you're looking for everything that doesn't match), you can create an object type that can have those additional columns and do

create type obj_keys is object( key varchar2(10) );
/

create type tbl_keys is table of obj_keys;
/

with keys as (
  select *
    from table( tbl_keys( obj_keys('list'),
                          obj_keys('of'),
                          obj_keys('some'),
                          obj_keys('keys') ))
)
select k.key
  from keys k
minus
select t.key
  from tbl t;


Best Practices - Copying Table with datetime stamped on name For backing up purposes I need to copy a table into the same DB but with
Date and Time on the TableName.

My solution doing this with T-sql Job:

select * into dbo.tmp from dbo.something
go

declare @thedate as varchar(100)
set @thedate = 'something'+convert(nvarchar(MAX), getdate(), 13)

EXEC sp_rename 'tmp', @thedate
go

Is there a better practice doing this?

I am wondering if copying into a tmp table and changing the name may be
not a good idea!!!. I am receiving a message saying
"Caution: Changing any part of an object name could break scripts and stored procedures."

The message warns you, "after rename the table, any dependent objects like views, triggers, SP or function will fail to execute. Which is normal.

If you want to avoid this message, you can use following script to backup table in same database.

DECLARE @TableToBackup VARCHAR(128) = 'Tbl_MyTable'
DECLARE @BackupTableName VARCHAR(512)  = @TableToBackup+convert(nvarchar(MAX), getdate(), 13)

EXEC  ('SELECT * INTO ['+@BackupTableName+'] FROM '+@TableToBackup+';')

Thanks!


SQL Job Fails - Why? This is my log - if I run the procedure locally it will still perform as it should and generate the message, but when it is run as a SQL Job it fails.  (For security I have changed the server name and database names)

12/07/2015 14:30:00,Send DBMail,Error,1,Hellfire,Send DBMail,Run Stored Procedure,,Executed as user: NT AUTHORITY\SYSTEM.
Mail queued. [SQLSTATE 01000] (Message 0)
Mail queued. [SQLSTATE 01000] (Message 0)
Mail queued. [SQLSTATE 01000] (Message 0)
Mail queued. [SQLSTATE 01000] (Message 0)
1234 [SQLSTATE 01000] (Message 0)
5678 [SQLSTATE 01000] (Message 0)
11111 [SQLSTATE 01000] (Message 0)
erqe [SQLSTATE 01000] (Message 0)
ssssss [SQLSTATE 01000] (Message 0)
ffff [SQLSTATE 01000] (Message 0)
Subquery returned more than 1 value. This is not permitted when the subquery follows =<c/> !=<c/> <<c/> <= <c/> ><c/> >= or when the subquery is used as an expression.
The step failed.,00:05:10,16,0,,,,0

EDIT
The user account NT Authority\System does not seem to be the issue as this account has no problem sending other messages using the same settings.  It is just this one procedure that errors out (will provide procedure code shortly)

EDIT # 2
This is the sql syntax I am using (may not make much sense as again I changed sensative data to random garbage details).

Set @fromDate = '12/01/2015'
Set @toDate = '12/05/2015'

Declare @query varchar(MAX), @name varchar(50), @toDate datetime, @fromDate datetime, @FQN varchar(500)
Create Table #problemtable (name varchar(50))
Create Table #PD (name varchar(50), LQ int)
    INSERT INTO #Datttta (nameName)
    SELECT DISTINCT(emp)
    FROM server.database.dbo.one
    ORDER BY emp ASC
        DECLARE C1 CURSOR FOR
            SELECT name FROM #Datttta
            OPEN C1
            FETCH NEXT FROM C1 INTO @name
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name)
                SET @Query =    'update #Datttta '
                            + 'set onetwothree = '
                            + '(select count(*) from '+@FQN+' '
                            + 'where terminationcategory in (select '
                            + 'termcat from '
                            + 'server.database.dbo.twotwo'
                            + 'where emp in '
                            + '(''' +@name+ ''')) '
                            + 'and CAST(termdate As Date) between ''' + CONVERT(VARCHAR(10),@fromDate,101) + ''' '
                            + 'and ''' + CONVERT(VARCHAR(10),@toDate,101) + ''' '
                            + 'and managersignoff is null) '
                        Begin Try
                            exec (@query)
                        End Try
                        Begin Catch
                            insert into #problemtable
                            (name) select @name
                        End Catch

                    FETCH NEXT FROM C1 INTO @name
            END
            CLOSE C1
            DEALLOCATE C1

--Send mail piece is here

As per the error message, more than a single row is being returned in a context where that is not permitted.

Looking at your query, the following line could easily be the culprit:

SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name);

You can eliminate that as the cause of the problem by modifying that line to:

SET @FQN = (SELECT TOP(1) fqn FROM fulldatabasemapping WHERE rciaem = @name)


When did a specific database come online? How can I determine when the state of a specific database switched to online?

Background: I want to determine how long the database is in use to find out how reliable sys.dm_db_index_usage_stats currently is.

As @SQLLearner specificed in his answer, the easiest way without auditing in place or an XE session running would be to just look in the SQL Server Log.  The below code will get the most recent startup of a particular database (using YourDatabaseName in this example, but obviously change it for your environment):

create table #error_log
(
    LogDate datetime not null,
    ProcessInfo nvarchar(128) not null,
    Text nvarchar(512) not null
)
go

insert into #error_log
exec xp_readerrorlog 0, 1, N'Starting', N'YourDatabaseName'
go

select top 1
    LogDate,
    Text
from #error_log
order by LogDate desc

As per @usr's comment an Connect link, in SQL Server 2012 Unicode parameters are required.


Server and Database role memberships required to add columns and constraints I have a user with the following server and database role memberships:

securityadmin (server)
public (server)
db_owner (database)
db_accessadmin (database)
db_securityadmin (database)

When running the below script in SQL 2005, it runs through without any issues:

BEGIN TRAN
IF NOT EXISTS (SELECT COLUMN_NAME
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'Opfix'
                 AND COLUMN_NAME = 'ModifiedBy')
BEGIN
    ALTER TABLE [OpFix] ADD [ModifiedBy] [int] NOT NULL
    CONSTRAINT DF_OpFixModifiedBy DEFAULT ([dbo].[fnSoftmarLoginUserID]())
END
ROLLBACK TRAN

When running the same script on SQL 2008 and SQL 2012, I get the below error:

  Cannot find the object "Opfix" because it does not exist or you do
  not have permissions.

So, my questions are:

Does it makes sense that I get different results between SQL 2005 and SQL 2008+, with the same update and the exact same user permissions?
What are the minimum requirements for this script to run through?
The only way I could get this script to run through on SQL 2008+ was to grant sysadmin membership, is that as expected?

First, the rollback transaction will undo what you are doing. Use commit transaction instead.

Yes
DB_owner in a database named XXX
Do you have a "USE XXX" in your script? I normally remove that stuff to protect DB names and server names when posting.  What you are saying here does not make sense to me.


Can a witness server be a witness for more than one pair of mirrored servers? In the interest of saving time and money (licenses).

For example:

We have a mirrored SharePoint backend as well as a mirrored Lync SQL backend.

I want to use the witness for the Lync servers to be the witness for the SharePoint mirrored pair at the same time.

There's no reason why it can't be done. In fact, Microsoft documentation states that one server can be a witness to multiple sessions.


Oracle: Delete data from one table based on starttime and finishtime column of another table I want to delete all the data from Table1 which is getting inserted between specific timeframe. And that timeframe value I am getting from Table2 columns starttime and finishtime and these two tables have no relations between them. So for doing this, I wrote the below query:

delete from table1 where
SCN_TO_TIMESTAMP(ora_rowscn)>=(select starttime from table2 where taskid=10502)  and
SCN_TO_TIMESTAMP(ora_rowscn)<=(select finishtime from table2 where taskid=10502)

The above query is working but it is very slow. So, I have tried another query which is not working

delete from table1 where
SCN_TO_TIMESTAMP(ora_rowscn) in
(select starttime, finishtime from table2 where taskid=10502);

I have searched on Stack Overflow, but I didn't get anything. Could anyone please help me in making a better query?

In your second query, your where clause has 1 column trying to compare to TWO columns in your subquery.  That's probably the error you're getting.

Have you tried using CTE?
http://www.dba-oracle.com/t_common_table_expression_cte.htm

However, I think your REAL problem is that you're using a function (SCN_TO_TIMESTAMP) in your where clause, and it's ignoring any indexing on the ora_rowscn column.

Here's the CTE anyway, but I doubt it will be much of a performance improvement.

WITH CTE_TasksToDelete
AS
(select starttime,
        finishtime,
        taskid --Best if you're going to be using multiple taskIDs
 from table2
 where taskid=10502)
DELETE FROM table1
WHERE SCN_TO_TIMESTAMP(ora_rowscn) BETWEEN ( SELECT starttime
                                             FROM CTE_TasksToDelete)
                                   AND (SELECT finishtime
                                        FROM CTE_TasksToDelete);

If anyone else wants to edit this and make it better, feel free.  Just wanted to give a starting point.

Again, I'm fairly certain your issue is that SCN_TO_TIMESTAMP function.  Find a way to make that an actual column in your table1, then index it, and you'll be a happy camper.
Also, batch your deletes friend.


SQL server 2012 service pack installation which service pack I should apply first after installing sql server 2012 enterprise edition either sp1 or sp2 ?

Thanks in advance.

The service packs are cumulative, so sp2 includes any pieces from sp1 that were not subsequently updated in sp2.