Mysql Query for Multiple table I have two table question and answer. The structure is given below

CREATE TABLE IF NOT EXISTS `question` (
`id` int(10) NOT NULL,
  `question` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `question` (`id`, `question`) VALUES
(1, 'Who is Mammotty'),
(2, 'Who Rahul Ghandi');

CREATE TABLE IF NOT EXISTS `answers` (
`id` int(10) NOT NULL,
  `q_id` int(10) NOT NULL,
  `answer` varchar(100) NOT NULL,
  `status` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

INSERT INTO `answers` (`id`, `q_id`, `answer`, `status`) VALUES
(1, 1, 'Actor', 'Y'),
(2, 1, 'Farmer', 'N'),
(3, 1, 'Engineer', 'N'),
(4, 1, 'Driver', 'N'),
(5, 2, 'Politician', 'Y'),
(6, 2, 'Teacher', 'N'),
(7, 2, 'Doctor', 'N'),
(8, 2, 'Pilot', 'N');

I want to display question and answer in this format

question         | ans1       | status1 | ans2   | status2 | ans3     | status3 | ans4   | status3
---------------------------------------------------------------------------------------------------
Who is Mammotty  | Actor      | Y       | Farmer | N       | Engineer | N       | Driver | N
Who Rahul Ghandi | Politician | Y       | Teacher| N       | Doctor   | N       | Pilot  | N

Can any one Help me ?

you can use the below query to get the result. i think this will work for you

SELECT qs.question,group_concat(an.answer) as myanswer FROM answers an,question qs WHERE qs.id=an.q_id group by an.q_id


Inspecting Anonymous Column in Stored Proc Resultset I am writing an SQL Script using a Stored Procedure, that somebody else wrote and I cannot change, which identifies whether or not a record can be safely deleted, based on its dependents.

My problem is that this stored procedure returns an anonymous resultset in the form of

IF <Condition>
BEGIN
  SELECT 'N', 'Cannot be deleted'
END
ELSE
BEGIN
  SELECT 'Y', ''
END

Now, I think this is poor design on the part of whoever wrote this because the messages are redundant, but that is besides the point because I cannot change it and so must work with what I have got.

The problem I have is that when I go to execute this proc

IF (EXEC CanDeleteRecord @RecordID = @Record_ID)

I don't really know how I am supposed to select the first column of the result set to compare with 'Y' or 'N' because the columns are anonymous. Is there some way of enumerating the results? This is a problem I have never encountered before with SQL and Google turns up squat.

Store the results in a temporary table or table variable and work with the stored set.

Here's a quick prototype you can work with.

create PROCEDURE [dbo].[TestMe]
AS
BEGIN
IF 1=1
BEGIN
  SELECT 'N', 'Cannot be deleted'
END
ELSE
BEGIN
  SELECT 'Y', ''
END
END

Declare @Results Table (CanDeleteIndicator char(1),Description varchar(30))
insert @Results exec dbo.testme
select * from @Results


How to import a varbinary(max) column with bulk import? I have a varbinary(max) column that I need to bulk import.  The data I'm putting into this column is a byte[] in C#.  How do I format the data in the comma-delimited file used for bulk import?  In other words, how do I convert from a byte[] to a string in a way the bcp utility will accept?

A varbinary value is of the form 0x01020304 for SQL Server to read.

So your CSV would be

col1,col2,varbinary
foo,1,0x01020304
bar,2,0x988776655443

Edit May 2013,

After testing, I can't get this to work with BULK INSERT with or without a format file.

I will post something more when I get it working. I can't delete this answer


selecting next todo item from child table I've a table with millions of records, relationship is one (object) to many (object_items).

object_items:

CREATE TABLE `object_items` (
  `item_name` varchar(50) NOT NULL DEFAULT '',
  `object_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sequence` int(10) unsigned NOT NULL,
  `completed` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_seq_object_id` (`sequence`,`object_id`),
  KEY `idx_object_id` (`object_id`),
  KEY `idx_seq` (`sequence`)
) ENGINE=InnoDB AUTO_INCREMENT=3408237 DEFAULT CHARSET=utf8mb4

Sample Data:

+-----------+-----------+----------+-----------+------+
| item_name | object_id | sequence | completed | id   |
+-----------+-----------+----------+-----------+------+
| ABCD      |        10 |        1 |         1 |    1 |
| BCDE      |        10 |        2 |         1 |    2 |
| CDEF      |        10 |        3 |         1 |    3 |
| DEFG      |        10 |        4 |         0 |    4 |
| ABCD      |        11 |        1 |         1 |    5 |
| BCDE      |        11 |        2 |         1 |    6 |
| CDEF      |        11 |        3 |         0 |    7 |
| DEFG      |        11 |        4 |         0 |    8 |
| ABCD      |        12 |        1 |         1 |    9 |
| BCDE      |        12 |        2 |         1 |   10 |
+-----------+-----------+----------+-----------+------+

Desired Result:

+-----------+-----------+----------+-----------+------+
| item_name | object_id | sequence | completed | id   |
+-----------+-----------+----------+-----------+------+
| DEFG      |        10 |        4 |         0 |    4 |
| CDEF      |        11 |        3 |         0 |    7 |
+-----------+-----------+----------+-----------+------+

The Query I run:

select
  a.*
from object_items a
where a.sequence = (
  select min(sequence)
  from object_items b
  where a.object_id = b.object_id
    and b.completed = 0
)

Which actually works but when I use limit, but if I run count(*) it just dies.

Explaining the query:

+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key           | key_len | ref             | rows    | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL          | NULL    | NULL            | 3268598 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ref  | idx_object_id | idx_object_id | 4       | db.a.object_id  |      21 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+

Is there a better way to get next TODO item that's not complete yet, by sequence, only for the objects those have at least one item to be done, with heavy database like this?

Thanks

Step by step. First look for not completed object_items, and the min(sequence). This is done with

-- Select object_items that are *not* completed, find min(sequence)
SELECT
    object_id, min(sequence) AS sequence
FROM
    object_items a
WHERE
    completed = 0
GROUP BY
    object_id
ORDER BY
    object_id, sequence ;

object_id | sequence
--------: | -------:
       10 |        4
       11 |        3

You can JOIN back with the original table to retrieve the rest of the corresponding row data:

SELECT
    item_name, q.object_id, q.sequence, completed, id
FROM
    (
    -- Select object_items that are *not* completed
    SELECT
        object_id, min(sequence) AS sequence
    FROM
        object_items a
    WHERE
        completed = 0
    GROUP BY
        object_id
    ) AS q
    JOIN object_items oi
         ON oi.object_id  = q.object_id
        AND oi.sequence   = q.sequence
ORDER BY
    q.object_id, q.sequence ;

item_name | object_id | sequence | completed | id
:-------- | --------: | -------: | --------: | -:
DEFG      |        10 |        4 |         0 |  4
CDEF      |        11 |        3 |         0 |  7

The query plan looks reasonable enough to scale well (it does not have a DEPENDENT SUBQUERY that needs reevaluation every time the DB loops):

id | select_type | table      | type   | possible_keys                           | key               | key_len | ref                    | rows | Extra
-: | :---------- | :--------- | :----- | :-------------------------------------- | :---------------- | :------ | :--------------------- | ---: | :-------------------------------------------
 1 | PRIMARY     | <derived2> | ALL    | null                                    | null              | null    | null                   |   10 | Using where; Using filesort
 1 | PRIMARY     | oi         | eq_ref | uni_seq_object_id,idx_object_id,idx_seq | uni_seq_object_id | 8       | q.sequence,q.object_id |    1 |
 2 | DERIVED     | a          | ALL    | null                                    | null              | null    | null                   |   10 | Using where; Using temporary; Using filesort

Adding the following index makes the plan even better, because it allows for covering the query named q:

CREATE INDEX idx_object_items_completed_object_id_sequence
    ON object_items (completed, object_id, sequence) ;

id | select_type | table      | type   | possible_keys                                 | key                                           | key_len | ref                    | rows | Extra
-: | :---------- | :--------- | :----- | :-------------------------------------------- | :-------------------------------------------- | :------ | :--------------------- | ---: | :--------------------------
 1 | PRIMARY     | <derived2> | ALL    | null                                          | null                                          | null    | null                   |    3 | Using where; Using filesort
 1 | PRIMARY     | oi         | eq_ref | uni_seq_object_id,idx_object_id,idx_seq       | uni_seq_object_id                             | 8       | q.sequence,q.object_id |    1 |
 2 | DERIVED     | a          | ref    | idx_object_items_completed_object_id_sequence | idx_object_items_completed_object_id_sequence | 1       | const                  |    3 | Using where; Using index

You can check everything at dbfiddle here


Select data from another database instance on the same server in sql server I have a query shown below:

select count(*) as Count, datepart(yyyy, [LogDate]) as [Year]
from ViewAssociate..Auth_Log
where ActionCode = 12
group by datepart(yyyy, [LogDate])
order by [Year]

This query is part of a stored procedure based on ReadAssociate database. It's trying to get data from ViewAssociate db. The user trying to run the stored proc doesn't have access to the ViewAssociate db so the query throws an error.

My question is can I modify the query somehow and provide it a username password to run so I can get data from the ViewAssociate Db. Someone suggested Execute As but that doesn't seem to work.

execute as login = 'viewassociate' I get this error:

  Cannot execute as the server principal because the principal "viewassociate" does not exist, this type of principal cannot be impersonated, or you do not have permission.

That viewassociate is a sql login which is db_owner of ViewAssociate database.

If the database that you are trying to connect to is on a different instance then I would recommend setting up a linked server which you can then use to obtain the data. This will allow you to explicitly use credentials which exist on the instance you want to connect to - it also keeps you query relatively simple by only needing to adjust to using a four-part name to reference the table.

-- create a linked server
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
     @server = '.\INSTANCE_NAME',
     @srvproduct=N'SQL Server' ;
GO
-- add a login to the linked server
EXEC master.dbo.sp_addlinkedsrvlogin
     @rmtsrvname = '.\INSTANCE_NAME',
     @locallogin = NULL,
     @useself = N'False',
     @rmtuser = 'user_name_from_other_instance',
     @rmtpassword = 'password_for_remote_user';
GO
-- now you can query the server
SELECT *
  FROM [.\INSTANCE_NAME].master.sys.databases;
GO

There are more options available for these stored procedures and you can also perform the same action using SQL Server Management Studio. I have included some links to the documentation below for you:

Linked Servers

Create Linked Servers

sp_addlinkedserver

sp_addlinkedserverlogin


Which user does Windows Authentication of a maintenance plan authenticate? before whom, why and for what? I am having a maintenance plan configured with Windows Authentication

being run by an SQL Server Agent job with owner sa

Whom does Windows Authentication of a maintenance plan authenticate, for whom and for what -  why is it needed and/or how can it be used?
To which user I should give permissions for accessing backup (being stipulated in maintenance plan) dir location?

And how does the owner of an SQL Server Agent job enter into all this considering that an owner sa has SQL Server authentication?

The Manage Connections dialog is for a connection to the Database or Instance you are performing maintenance on.

So if you type in a sql authenticated user called 'bob' there, the user bob will be the one that connects to the instance to do the maintenance tasks you are specifying.

You can prove that out by running SQL Server Trace/Profiler (in a dev environment) and seeing the connection being made.

That is basically the user that is connecting to the objects within SQL Server.

The connection to the folders and external resources will be, in this case, whatever your SQL Server Service Account is running as.

It's a bit confusing but it sort of works like this:

1.) Your SQL Server Agent Service Account (or the proxy account used) will do all of the executing. It will call out to the maintenance plan, it will handle the job logging for job history/etc.

2.) When the maintenance plan is run - any of the tasks inside of it will either use whatever permissions the agent account has, or whatever you have explicitly defined in the maintenance plan connection manager -as you have done.

3.) For any access to resources outside of SQL Server - the SQL Server Service account will actually be the account interacting. So to write a backup to a folder, your SQL Server Service account will need that permission. The agent tells the server to run the maintenance plan which tells the server to do a backup. The service account of the SQL Server service then performs what it was requested and it writes out the backup.

Also

You didn't ask here - but I'll mention it again like I did in the last answer. I don't get anything for recommending a free resource - but the Ola Hallengren maintenance solution I mentioned on a related question from you is a really good resource. It will intelligently do your index rebuilds/reorganizes so you only touch what needs to be, it will add more control and intelligence to your statistics updates, it does a great job with backups, etc. But the nicest thing going for it? It gets rid of the black box of maintenance plans and takes a variable out of play for you to have to worry about seeing inside of with some of the more advanced security questions you are trying to get answered.


SQL Server 2000\2005 compatibility with Active Directory 2016 We are currently using Active Directory 2008R2 and will be upgrading to AD 2016. I'm trying to determine if there is any known compatibility issues when running older versions of SQL Server (2000 and 2005) when upgrading to AD 2016. Has anyone been through this process? Most of our db servers are using SQL 2008 > 2016, but a few still runs with 2000/2005. Thanks

I have not gone through this process directly, but have worked with querying AD in a modern environment and a less modern environment.  I believe that the OLE DB Provider remains compatible between the older and newer versions of AD. Also I would not expect the loss of any commonly used (at least in my experience) properties, which would be the other risk.

If you do run into trouble, however, I would be prepared to migrate the databases to a newer instance of SQL Server running those DBs at a lower db compatibility level.  This will still be challenging, as I think you can only go back to 100 (2008). However, from a mitigation standpoint I would suggest trying to go this route regardless.  This would also allow you to retire the older instances (and likely old servers they are running on).  Why not make a bigger project even bigger (though you can do it ahead of time, so it doesn't all fail at once).

I will qualify this with my of querying AD had been generally focused on getting basic user properties and group membership, and do it so infrequently I have to go look everything back up.  And it's been a few years since I have to do much Windows Admin work.


How to decide whether to enable MySQL query cache using InnoDB How should the decision to enable / disable the MySQL query cache be made, on a server that uses just InnoDB tables. Say for example, if the cache is enabled, then how should the output of:

SHOW STATUS LIKE 'Qcache%';

be interpreted to make the decision? Or what other queries/profiling can be performed to get more information, and then how should those be interpreted? If it's to be enabled, how should its size be determined? I'm on Amazon RDS, using MySQL 5.6. I will end up being around ~250 separate databases totalling ~200gb in space.

I had discussed this in earlier posts

Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?
Sep 26, 2013 : query cache hit value is not changing in my database

InnoDB internals has a very hands-on approach to the query cache since it micromanages query cache entry invalidation. In the MySQL 4.x days, the query cache was disabled by default for the sake of InnoDB. In MySQL 5.x, it can be a toss-up. The comments from @akuzminsky show local issues center around  InnoDB.

Given the fact you are using Amazon RDS, you find the following challenges

Changing some InnoDB settings can be cumbersome
Some InnoDB settings cannot be changed
Tuning aspects of InnoDB has limits due to RDS

I would take away any guess work and just leave the query cache disabled. If having a bigger buffer pool size (as suggested in @akuzminsky's second comment) you will have to migrate to a larger Server Model ($$$ Cha-Ching $$$). Here are the Buffer Pool sizes for the Server Models

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)


Can anyone offer an opinion on handlersocket vs memcached performance in MySQL 5.6 I must say I was a little mis(un)-informed about the MySQL Memcached plugin which offers the same NoSQL like access to InnoDB tables as HandlerSocket, I had only had experience with entirely standalone memcached servers in cacheing scenarios.

We're using HandlerSocket quite heavily, as it provided a massive increase in IO, and from the original papers on the subject (at the time), it was actually faster than traditional memcached access, thus why we pursued it.

However in learning that the new memcache plugin serves the same purpose now, I went searching for benchmarks, comparisons, or even cock-eyed opinion, but wasn't able to find any specifically addressing both.

I'd like to know if anyone has had experience with both, switched to/from either and what their experiences were?

I would recommend using InnoDB memcached over Handlersocket.  The advantage of InnoDB memcached is that:

It uses a standard memcached protocol, with drivers available for many programming languages.
It does not automatically allow access to all tables in your database, but instead tables 'opt-in' via a mapping containers table.
It has a stated support policy of 8 years.  MySQL 5.6/5.7 support already exists for InnoDB memcached, and these version upgrades are required to unlock many of the scalability enhancements.


How to store search keyword or browsing history in database I'm making my final year project. Which is a website.
Things I can't do:

Store the browsing history of users between their login and logout session on the site.
Or
Store the search keywords.

But I dont know how to do it. Is there any query or functions that I can use???
I'm using xampp server.

I'm really stuck in this phase and don't know how to do it.
One of my friend told me it's IMPOSSIBLE.
Does anyone have a possible solution rather than just IMPOSSIBLE.
Please help.

So, I'm not sure just what you are looking for here.

Since you are using xampp you can manage user sessions in php using their session functions.

Apache also has it's own domain logs which you can keep for however long.

You can also use redis for session management if you are ok with the session information being volatile.

In general I don't see this as a database question, and suggest moving this question to one of the other SE sites such as Stack Overflow or Server Fault.


Changing database name based on project environment I have three environments for our BI project (DEV, QA, PROD) and two databases in each environment, source(EDB) and target(EDM)

DEV environment: Source = EDB_DEV   Target = EDM_DEV
QA environment: Source = EDB_QA   Target = EDM_QA
PROD environment: Source = EDB   Target = EDM

SSIS OLE DB Source component have connection to EDB_DEV but query also refer to EDM_DEV

SELECT A, B, C
FROM tblFirst F --(table in EDB_DEV)
INNER JOIN EDM_DEV.dbo.tblEDMTable T ON T.ID = F.ID

Now I have to  migrate to QA or PROD environment. So I am wondering, is there any way I can dynamically tell which database to go based on connection/environment.

For e.g EDM_DEV in query should be replaced with EDM_QA when running in QA environment.

You can use variables/parameters in your SSIS package to dynamically build your queries, probably the easiest method. It might be tedious to get setup based on how many queries you have used everywhere in your package. Just have something like an "User::Environment" variable that holds either: _DEV, _QA or _PROD. You then join that into your variable holding your query and then as you change that value it would query the appropriate table.

You could base it on the server you are connecting to as well, but would be the same premise of having to configure your queries dynamically in a variable + expression setup. I would probably use above method over validating against server I'm connecting to, just to have more precise control.


Truncating and inserting in a safe transaction on MySQL I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table.

I wanted to make sure that if any statement in the transaction failed, the data will be untouched.

I can't have an empty or corrupted table if something fails.

This is how I planned to do it.

BEGIN TRANSACTION
TRUNCATE TABLE table_name
INSERT INTO table_name
COMMIT

I then learned that TRUNCATE TABLE table_name causes an implicit COMMIT, which makes the whole transcation pointless.

I then replaced the TRUNCATE TABLE table_name with a DELETE FROM table_name approach, which wasn't ideal - but that too causes an implicit COMMIT!

DROP TABLE table_name and CREATE TABLE table_name also causes an implicit COMMIT!

What method / logic is best to perform what I am trying to do?

Using MySQL 5.6.17 64-bit server running on Windows Server 2008 R2

Since TRUNCATE TABLE does an implicit commit, don't use it.

As long as the table does not have foreign keys, you can do it quickly like this:

CREATE TABLE mytable_new LIKE mytable;
RENAME TABLE mytable TO mytable_old,mytable_new TO mytable;
INSERT INTO mytable ... ;

If the INSERT succeeds, then run this

DROP TABLE mytable_old;

If the INSERT rolls back, just revert back

RENAME TABLE mytable TO mytable_zap,mytable_old TO mytable;
DROP TABLE mytable_zap;

Give it a Try !!!


Alter the letters after "ACCOUNTS\" to lower case? I have a database table, users, with different username cases (see the example). How can I update the letters after ACCOUNTS\ to lower case?

ACCOUNTS\James.McAvoy
ACCOUNTS\michael.fassbender
ACCOUNTS\nicholas.hoult
ACCOUNTS\Oscar.Isaac

Desired result after the update:

ACCOUNTS\james.mcavoy
ACCOUNTS\michael.fassbender
ACCOUNTS\nicholas.hoult
ACCOUNTS\oscar.isaac

UPDATE dbo.[User]
SET username_column = N'ACCOUNTS' + LOWER(SUBSTRING(username_column, 9, 4000))
WHERE username_column LIKE 'ACCOUNTS\%';


Multiple Databases for one web solution I am working on a new project and I am in the process of designing the Microsoft SQL Server database behind the system.

The system will contain an infinite amount of customers and each customer has multiple users. Each customer will have their own subset of data and the amount of data is going to be vast. Worst case scenario is approximately 5+ million rows per month across multiple tables per customer.

To optimize performance per customer I was considering a system containing one system database and multiple customer databases. My thought is mainly that I see no reason why one customer should query through other customer's data. The issue as I see it is that this solution would cause relationships between the customer databases and the system database (cross-database relationships).

Am I overthinking the performance issue?

Given that you haven't started coding the app or DB yet, but do have a sense of the scope of the data (even if that will change somewhat as time goes on): no, you are not over-thinking this. I wouldn't spend months pondering this one aspect of the system design, but to not think of it at all is a bit foolish since refactoring app code is far easier than refactoring databases (since databases include "state").

My preference for multi-tenant systems, especially if dealing with large amounts of data and/or great variation of data sizes between clients, is to go the Database-per-Customer route. There are obviously advantages and disadvantages to any approach, but here is what I see from having gone through this a few times and trying it both ways:

ADVANTAGES

One customer does not adversely affect another performance wise. This would mainly be an issue when having customers with greatly varied amounts of data since parameter sniffing will cache the plan for the first CustomerID passed into a Stored Procedure, and that CustomerID might have 10 million rows whereas the next call to the Stored Procedure is for a customer with 1,000 rows.
You don't need to have a CustomerID field in every table (except lookup tables). This complicates indexing and performance tuning.
You don't need to worry as much about cross-customer data privacy issues when forgetting to include the CustomerID field in all JOIN conditions and WHERE conditions (either Cartesian products or simply Customer A seeing Customer B's data).
When a customer leaves, you just shut off their DB and archive it. No need to worry about purging obsolete data.
If a customer needs their data, you can simply send them their DB without worrying about cross-customer data issues.
You can host customer DBs geographically closer to each particular customer, especially when using a cloud-based system like Azure SQL Database, AWS, etc.

DISADVANTAGES

App code connectivity needs to take into account the Database, not just Server
More potential for Connection Pool Fragmentation if you include the Database in the ConnectionString rather than changing the DB context after making the Connection
DB code releases get a little more complicated since they need to be applied across all DBs (though there are ways to minimize this complication)
Increased potential to have differing code/schema between customers if you don't have a solid release process (item directly above), and this would be a maintenance nightmare
Support gets minorly complicated since you now need to know which DB to go to instead of needing just a CustomerID
If the customer model changes such that in the future a "parent" company  of Customer A and Customer B can be the true customer, then those are in two separate DBs and harder to manage and report on as a single entity.
Backups get a little more complicated now that you have 200+ databases (though there are ways to minimize this complication)

Regarding the thought of having a single SystemData DB and the related concern of not being able to FK across DBs: how much system lookup data do you expect to have? Assuming it is not 1 GB of data, you can create a Schema in each customer DB called "System" or "Common" or "Lookups" or whatever, and load the data into that schema into all customer DBs. Then you can FK to those tables. And by having one DB per customer you will already need a release process that updates the code and schema of each customer DB in the exact same manner anyway (such that they are always in sync with a common structure), so updating lookup tables fits right into that process. The main downside here is that you are duplicating this data per each customer DB, so 200 times perhaps? But that is over the course of years. You aren't starting out with 200 customers, right?

Also, please keep in mind that Table Partitioning is not "sharding" (like you might be thinking of in terms of MongoDB or PostgreSQL/GreenPlum, etc). It is not meant to magically solve the problem of multi-tenancy or distributed data. It is meant to handle the problem of quickly and efficiently handling bulk data loads and unloads. And it does quite well at its intended purpose. And occasionally it even helps queries on very large tables (1 billion rows or more). But indexes and statistics are still per-table, not per-partition (though you can create filtered indexes/statistics per each partition key to help make up for this). And while you can (in more recent versions) reindex per-partition, starting with SQL Server 2012, indexes no longer get a "free" UPDATE STATISTICS WITH FULL SCAN when doing a REBUILD, if they are partitioned ! Non-partitioned indexes still do :-).

For what it's worth, I worked on a multi-tenant system that did attempt to use table partitioning in this manner (was not my idea). It caused a lot of headaches, frustration, extra work trying to fix weird execution plans, and after a few years, was mostly agreed upon that it probably shouldn't have been done.


Ola Hallengrens code for Index Optimization - Clarifications I am trying to deploy Ola Hallengrens code for Index Optimization in our PROD environment.

What is the difference between using USER_DATABASES and AVAILABILITY_GROUP_DATABASES in the place of @Databases in below code .

Also I was going through the blogs and read :

Ola Hallengrens code for Index Optimization is meant to defragment only with page count more than 1000.

When I execute below command it updates only statistics not rebuild indexes -

Does this mean I am good with fragmentation , not to worry much about it as my end users does not complaint any thing about slow response or bad performance from Database end .

EXECUTE dbo.IndexOptimize
 @Databases = '',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30

The difference between user_databases & availability_group_databases is that when specifying user_databases you will optimize every user database (also the ones in the AG group) if you specify availability_group_databases you will only optimize the indexes of databases inside an availability group.

If you do not have tables with fragmentation above 1000 pages, this will not hurt your SQL Server performance. I recommend you also read this post from Brent Ozar. https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/


Do shorter names exist for "one-to-many", "one-to-one", "many-to-many" relationships? In terms of generic variable naming or even everyday geek discussion, do shorter and "cleaner" terms exist for one-to-one, one-to-many, and many-to-many relationships.

For example, in discussion of religion, one can say "relating to many denominations of Christianity", but it's much shorter to say, "ecumenical". In computer science, "Your arrangement of words and punctuation is incorrect" is less awkwardly said, "You have a syntax error."
Do such terms exist for the common database cardinalities?

As far as verbal communication, there are none that I'm aware of. There are symbols for writing but those wouldn't be of any help for verbal communication.


how to find the error description for the error codes found in the extended events file XEL generated by SSAS Tabular query end event how can I translate the error codes logged into XEL files to the corresponding description?
I'm importing the XEL files to a SQL Table using the sys.fn_xe_file_target_read_file function, but the returned xml does not contain the error description, only the error code
this is an example of errors I get in the XML
  <data name="Severity">
    <value>3</value>
  </data>
  <data name="Success">
    <value>0</value>
  </data>
  <data name="Error">
    <value>-1055129590</value>
  </data>

I think you should look at the [TextData] field.
Based on Microsoft doc here:
https://docs.microsoft.com/en-us/analysis-services/trace-events/errors-and-warnings-events-data-columns?view=asallproducts-allversions
it should contain the the text data associated with the error event, like the error message.
please, try this against your trace file.
    SELECT   xe_file.[FILE_NAME] AS [filepath]
        ,[ApplicationName] = xe_file.xml_data.value('(/event/data[@name=''ApplicationName'']/value)[1]','[nvarchar](128)')
        ,[BinaryData] = xe_file.xml_data.value('(/event/data[@name=''BinaryData'']/value)[1]','[varbinary](max)')
        ,[ConnectionID] = CAST(xe_file.xml_data.value('(/event/data[@name=''ConnectionID'']/value)[1]','[nvarchar](128)') AS INT)
        ,[CPUTime] = CAST(xe_file.xml_data.value('(/event/data[@name=''CPUTime'']/value)[1]','[nvarchar](128)') AS bigint)
        ,[CurrentTime] = CAST(xe_file.xml_data.value('(/event/data[@name=''CurrentTime'']/value)[1]','[nvarchar](128)') AS DATETIME2)
        ,[DatabaseName] = xe_file.xml_data.value('(/event/data[@name=''DatabaseName'']/value)[1]','[nvarchar](128)')
        ,[Duration] = xe_file.xml_data.value('(/event/data[@name=''Duration'']/value)[1]','[nvarchar](128)')
        ,[EndTime] = xe_file.xml_data.value('(/event/data[@name=''EndTime'']/value)[1]','[nvarchar](128)')
        ,[Error] = CAST(xe_file.xml_data.value('(/event/data[@name=''Error'']/value)[1]','[nvarchar](128)') AS INT)
        ,[EventClassId] = CAST(xe_file.xml_data.value('(/event/data[@name=''EventClass'']/value)[1]','[nvarchar](128)') AS INT)
        ,[EventSubclassId] = CAST(xe_file.xml_data.value('(/event/data[@name=''EventSubclass'']/value)[1]','[nvarchar](128)') AS INT)
        ,[IntegerData] = CAST(xe_file.xml_data.value('(/event/data[@name=''IntegerData'']/value)[1]','[nvarchar](128)') AS INT)
        ,[JobID] = CAST(xe_file.xml_data.value('(/event/data[@name=''JobID'']/value)[1]','[nvarchar](128)') AS INT)
        ,[NTCanonicalUserName] = xe_file.xml_data.value('(/event/data[@name=''NTCanonicalUserName'']/value)[1]','[nvarchar](128)')
        ,[ObjectID] = xe_file.xml_data.value('(/event/data[@name=''ObjectID'']/value)[1]','[nvarchar](128)')
        ,[ObjectName] = xe_file.xml_data.value('(/event/data[@name=''ObjectName'']/value)[1]','[nvarchar](128)')
        ,[ObjectPath] = xe_file.xml_data.value('(/event/data[@name=''ObjectPath'']/value)[1]','[nvarchar](128)')
        ,[ObjectReference] = xe_file.xml_data.value('(/event/data[@name=''ObjectReference'']/value)[1]','[nvarchar](128)')
        ,[ObjectType] = CAST(xe_file.xml_data.value('(/event/data[@name=''ObjectType'']/value)[1]','[nvarchar](128)') AS INT)
        ,[ProgressTotal] =  CAST(xe_file.xml_data.value('(/event/data[@name=''ProgressTotal'']/value)[1]','[nvarchar](128)') AS INT)
        ,[ServerName] = xe_file.xml_data.value('(/event/data[@name=''ServerName'']/value)[1]','[nvarchar](128)')
        ,[SessionID] = xe_file.xml_data.value('(/event/data[@name=''SessionID'']/value)[1]','[nvarchar](50)')
        ,[SessionType] = xe_file.xml_data.value('(/event/data[@name=''SessionType'']/value)[1]','[nvarchar](128)')
        ,[Severity] =  CAST(xe_file.xml_data.value('(/event/data[@name=''Severity'']/value)[1]','[nvarchar](128)') AS INT)
        ,[SPID] = CAST(xe_file.xml_data.value('(/event/data[@name=''SPID'']/value)[1]','[nvarchar](128)') AS INT)
        ,[StartTime] = CAST(xe_file.xml_data.value('(/event/data[@name=''StartTime'']/value)[1]','[nvarchar](128)') AS DATE)
        ,[Success] = CAST(xe_file.xml_data.value('(/event/data[@name=''Success'']/value)[1]','[nvarchar](128)') AS INT)
        ,[TextData] = xe_file.xml_data.value('(/event/data[@name=''TextData'']/value)[1]','[nvarchar](max)')
FROM    (
            SELECT   [FILE_NAME]
                    ,[XML_DATA] = CONVERT(XML, EVENT_DATA)
            FROM    sys.fn_xe_file_target_read_file(@trace_filepath, NULL, NULL, NULL)
        ) AS xe_file

;


Terminology No-SQL Database I'm looking for a good explanation of terminology for No-SQL.
I have some colleagues that won't get the point, and for the sake of advancing i need to have definitions of the names.. Though this seems real hard to find.

let's see a Database is a database, talking about No-SQL or SQL it keeps the same name. People do understand that.. But if you start looking on the internet.. there are people that say that you have: "a database with documents", "a database with rows and each row is a document" and "a document has rows",  It's real confusing if you ask me..

So could anybody give a good explanation of the terminology that exists in No-SQL, to improve communication about the subject. And also how this compares to the traditional SQL terminology?(like a row is a document? a table.. etc..)

EDIT
Can anybody confirm that this is the relation between the terms:
A Database has multiple documents.
In this case each documents will be the same as a row and the specification of an Table like in SQL does not exist. you could see all the tables with the same type as a table, if i'm right....?

EDIT 2

I'm specifically looking for couchDB.

http://docs.mongodb.org/manual/reference/sql-comparison/

check out full documentation. everything is perfectly explained.


Index with multiple leaf levels I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same index.

I am experiencing performance issues and I can't get avg_page_space_used_in_percent higher than 70% (leaf page count 1200, fill factor 80).

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)
    Apr 22 2011 19:23:43
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

"My question is how and when does SQL Server create multiple leaf
  levels (index_level 0) for the same index."

The 2008 R2 documentation for sys.dm_db_index_physical_stats includes a link to Table and Index Organization, which shows the following diagram:

It describes the data that may be stored in each of the three possible allocation unit types:

Your clustered index does contain three leaf levels, one per allocation unit type.  For example:

CREATE TABLE dbo.Example
(
    example_id  integer PRIMARY KEY,
    lob_data    nvarchar(max) NULL,
    padding     varchar(8000) NULL,
    overflow    varchar(8000) NULL
);

INSERT dbo.Example
    (
    example_id,
    lob_data,
    padding,
    overflow
    )
VALUES
    (
    1,
    REPLICATE(CONVERT(nvarchar(max), N'X'), 8001),
    REPLICATE('Y', 4000),
    REPLICATE('Z', 6000)
    );

SELECT
    ddips.index_id,
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID('dbo.Example'),
        1,
        1,
        'DETAILED'
    ) AS ddips;

Output:

╔══════════╦═════════════════╦══════════════════════╦═════════════╦════════════════════════════════╗
║ index_id ║ index_type_desc ║ alloc_unit_type_desc ║ index_level ║ avg_page_space_used_in_percent ║
╠══════════╬═════════════════╬══════════════════════╬═════════════╬════════════════════════════════╣
║        1 ║ CLUSTERED INDEX ║ IN_ROW_DATA          ║           0 ║ 50.3953545836422               ║
║        1 ║ CLUSTERED INDEX ║ ROW_OVERFLOW_DATA    ║           0 ║ 74.3019520632567               ║
║        1 ║ CLUSTERED INDEX ║ LOB_DATA             ║           0 ║ 99.0239683716333               ║
╚══════════╩═════════════════╩══════════════════════╩═════════════╩════════════════════════════════╝

Your table contains large object (LOB) columns (MAX or old-style text, ntext or image types) and variable-length column definitions which allow individual rows to exceed the 8060 byte INROW limit.

For rows that exceed 8060 bytes, ROW_OVERFLOW_DATA allocation units will be created.  This is often problematic for performance, since row data access requires following an off-page pointer to retrieve the overflowed data.

I would certainly look at the design of the table before worrying too much about how full the pages are on average.  Whether you should be concerned about page fullness depends on which allocation unit it refers to.


Script to identify which AD accounts are no longer active Trying to get a script that will identify which AD accounts/Windows authentication on a SQL Server are no longer active.  Say perhaps an employee was released and the login would be removed from AD, but SQL Server still shows it in the logins.

Any assistance would be great.

I'd highly recommend the dbachecks PS module which makes this (and pretty much every other important check) quite easy to run.

Running it is as simple as:

Invoke-DbcCheck -Checks ADUser -SqlInstance localhost


SSMS Restore Database wizard slow to respond I've just noticed this recently on one of the SQL Server 2012 SP3 instances that I oversee.

When I try to restore a database using SSMS 2012 (by right-clicking on the database,and choosing Tasks>Restore>Database), the Restore Database window (where you can pick the source, and destination database, etc.) takes a very long time before it finally shows up on the screen. I'm talking more than 10 minutes here at times.

I've noticed this both using my local SSMS, and on the SSMS installed on the Windows Server where the SQL Server instance is installed.

I am only noticing this on one specific instance. I don't see the same slow response issue when doing a Restore on the gui on the other instances.

Have any of you seen this before? I'd appreciate the help. Thanks.

Does the wizard show you stats as you go?

Have you tried using T-SQL to restore?  The wizard is probably that way because it's probably just busy in the background, which is certainly the case if your bak file is big (or trn files are plentiful).  The script below will show you stats every 5% in the "Messages" tab.

Might also help to take the database offline prior to a restore (ensure there are no users while doing this, or it will "do it for you"):

--Taking DB Offline due to errors received without this.
--Error: "Exclusive access could not be obtained because the database is in use."

ALTER DATABASE [DBToRestore]
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DBToRestore]
SET ONLINE
GO

RESTORE DATABASE [DBToRestore] FROM  DISK = N'Directory\SourceDBbackup.bak' --**Replace this value with the most recent FULL Backup file (BAK)
WITH
FILE = 1
,  NORECOVERY  --If Transaction Logs are available, use this
,  NOUNLOAD   --Tape remains loaded on the tape drive.
,  REPLACE    --Replace the destination database.
,  STATS = 5  --Displays stats update message for every ## Percent complete.

GO

--Finally, "restore" the database without actually restoring the data.
--This is done post-full restore in order to bring a database back to working order. (Technically optional)
--Details: https://msdn.microsoft.com/en-us/library/ms188696.aspx

RESTORE DATABASE [DBToRestore] WITH RECOVERY;

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql


Load balancing reads SQL Server 2016 AG I'm trying to balance read traffic between two SQL Server 2016 Enterprise Edition instances (Primary and Secondary) using availability groups and the read only routing.

Here is my current setup:

The servers are in AWS in two different AZ
The servers are in a failover cluster without shared storage
AG is enabled on both instances
The databases in an Availability Group have the "Readable Secondary" option set to Yes
I've setup the AG with the read only routing on both the current primary and secondary instances
Adding the read only switch in my application connection string allows me to route traffic to the secondary

Is there a way to have the AG Listener load balance read only traffic between the primary and secondary?

On SQL Server 2016+, you can load-balance read-only connections. (Note: this does not work on SQL 2012 or 2014 AGs.)

The full documentation is available here.

Essentially, when configuring your read-only routing list, you would use syntax like this:

ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQL16A'
    WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL16B', 'SQL16C'), 'SQL16A')));

This will make your first routing option SQL to load-balance between SQL16B & SQL16C. The second routing option is to send read-only connections to SQL16A. If server B is offline, then just that one server is taken out of the rotation--so all read-only connections would continue to go to Server C.

Load balancing is done in a round-robin fashion, so connections will be routed, A,B,A,B,A,B, etc.  Load balancing does not consider existing number of connections, existing server load, or anything fancy.


User guest does not have permission to run DBCC checkprimaryfile (SMSS) I am trying to attach an mdf file to a sql server using SMSS, and I got a message that says:

User guest does not have permission to run DBCC checkprimaryfile

Any clues about how can I solve that Issue? I can add more info about the details

Have you seen this post on the MSDN forums?

User guest does not have permission to run DBCC checkprimaryfile

One possible solution proposed in that thread is to run SSMS as an administrator.

The other option is to make sure that the user account you're using to the connect to SQL Server:

has a login setup on the instance
has the appropriate permissions for attaching a database (CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE)


Status of PostgreSQL replication after pause and resume To backup the PostgreSQL database we setup a slave server which uses streaming replication.

We found the commands pg_xlog_replay_pause() and pg_xlog_replay_resume(). So we thought we use the commands to pause the replication (the replaying of the xlogs), start pg_dump and then resume the replication.

How do I find out when the PostgreSQL database is again in sync after the "resume"? Usually I used this command to see the "lag":

SELECT extract(seconds from (now() - pg_last_xact_replay_timestamp())) AS time_lag;

If I use it after pg_xlog_replay_pause() then it shows only a "lag" in seconds. So it seems that it is not the right query here. So is there a good solution for this?

One solution would be to write a cronjob on the master server to update a timestamp every minute and then check on the slave when the timestamp is more or less up to date. But I think there should be a better solution from PostgreSQL itself.

Unless I misunderstood your question, SELECT extract(epoch from (now() - pg_last_xact_replay_timestamp())) AS time_lag; should give you what you are looking for - the lag in seconds, not seconds portion of the lag as your original query returns.  You can achieve the same with a number of extract (seconds ...) + extract(minutes ...)*60 + extract (hours ...) *60*24 +...


Sql server 2012 grant role permission to just validate view but not select data from it Is there a permission in SQL server 2012 to just validate/ existence an object (in my case it's a view) but not a select permission? Idea is if a usergroupA try select a view it just should not fail and return no records. Thanks in advance!

Is there a permission in SQL server 2012 to just validate/ existence
  an object (in my case it's a view) but not a select permission?

Consider granting VIEW DEFINITION  permission as defined in the below quoted reference; this still applies to SQL Server 2012 as well.

  VIEW DEFINITION

  The VIEW DEFINITION permission lets a user see the metadata of the
  securable on which the permission is granted. However, VIEW DEFINITION
  permission does not confer access to the securable itself. For
  example, a user that is granted only VIEW DEFINITION permission on a
  table can see metadata related to the table in the
  sys.objects
  catalog view. However, without additional permissions such as SELECT
  or CONTROL, the user cannot read data from the table. For more
  information about viewing metadata, see GRANT
  (Transact-SQL).

Creating a per Database Role for VIEW DEFINITION permissions

/* CREATE A NEW ROLE  -- View Definition */
-- Database specific
CREATE ROLE db_All_ViewDefinition
GRANT VIEW DEFINITION TO db_All_ViewDefinition

Members of this role in the database you create it on should only have access to the applicable metadata as long as they don't have other permissions to database objects such as explicit SELECT access to the a table or a member of the db_datareader fixed database role.

Granting VIEW DEFINITION permissions explicitly per object

USE [DatabaseName];
GRANT VIEW DEFINITION ON dbo.tablename
TO [domain\username]

The above TSQL will grant explicit VIEW DEFINITION access to the specific DB object and to the specific security principal as specified in the applicable TSQL logic

Further Resources

GRANT Object Permissions (Transact-SQL)
CREATE ROLE (Transact-SQL)
VIEW DEFINITION


Set cursor rows command I'm new to sqlserver and i'm learning about t-sql cursor.

I've seen in a book called "t-sql user guide" an example of cursor declaration.

There is  a command to return more rows for each fetch:

set cursor rows 3 for authors_crsr

I've tried it in my sqlserver2012, but it did not work.
I tried searching in google but had not luck.

What's wrong with this command? Maybe this command has been deleted from the version  i'm using? (2012)

Thank you.

This command is not a SQL server command, as others have commented. However, if you did need to fetch 3 lines into 3 different sets of variables, you could do so using:

FETCH ABSOLUTE 1 FROM cursor_name INTO @Variable1;

FETCH ABSOLUTE 3 FROM cursor_name INTO @Variable3;

FETCH ABSOLUTE 2 FROM cursor_name INTO @Variable2;

FETCH ABSOLUTE 1 FROM cursor_name INTO @Variable1;

Remember that each FETCH always resets the current row to the position of the cursor. This requires that you declare your cursor like: DECLARE cursor_name SCROLL CURSOR FOR .... Careful here though, this requires your SQL statement for the cursor declaration to have an ORDER BY to ensure you receive the cursor in an expected order, if that is important.

Lastly - try your hardest to NOT use cursors! Especially those that require scrolling. They are evil by nature, have black souls and each time you use one Edgar Codd turns over in his grave. OK, maybe not that bad, but you get what I mean :-)

EDIT:

Actually a better implementation would be to use a variable instead of the number after the ABSOLUTE key word, so your loop would look like:

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var1;

    SELECT @Ctr = @Ctr + 2
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var2;

    SELECT @Ctr = @Ctr - 1
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var3;

    SELECT @Ctr = @Ctr - 1
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var1;

    --Increment the counter
    SELECT @Ctr = @Ctr + 1
END


SQL Server Cluster Service Online Pending Status I have 2 Nodes of SQL Cluster running SQL 2012 Ent over Windows 2012 R2 and i don't know suddenly what happened the service is not coming online. Under Failover Cluster Mannager > Roles > SQL > Resource > SQL Server (instance-name) the status us Online Pending and then Failed.

Cluster have only below evetns;

The Cluster service failed to bring clustered role 'SQL Server (instance-name)' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.
Cluster resource 'SQL Server (instance-name)' of type 'SQL Server' in clustered role 'SQL Server (instance-name)' failed.
Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Event Viewwer;

Fault bucket , type 0
Event Name: Failover clustering resource deadlock
Response: Not available
Cab Id: 0

Problem signature:
P1: SQL Server (instance-name)
P2: SQL Server
P3: ONLINERESOURCE
P4:
P5:
P6:
P7:
P8:
P9:
P10:
Attached files:
These files may be available here:
C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Critical_SQL Server (instance-name)_217169f34c33c838a6522b285697633e938ebe_00000000_03a6e263
Analysis symbol:
Rechecking for solution: 0
Report Id: 11f7e038-5f26-11e7-80fb-7a2f1397e813
Report Status: 4100
Hashed bucket:

Kindly suggest what could be wrong.

The default restart settings in WSFC are such that a faulting resource is terminated and then retried once before being declared offline.

In this case: the cluster discovers the fault, calls for terminate and then attempts to re-online.  Storage Foundation for Windows detects that the Disk Group is not present and returns a status of Pending.  This creates a deadlock that lasts until either RHS.exe terminates (through hang protection); or the online pending timeout is reached.

So, recommend to change Pending timeout and deadlock timeout as per below link.

To change deadlock timeout(Values are in milliseconds)

(Get-ClusterResource “Resource Name”).DeadlockTimeout = 300000

To change pending timeout follow below steps:

In the Failover Cluster Management snap-in, if the cluster you want to configure is not displayed, in the console tree, right-click Failover Cluster Management, click Manage a Cluster, and select or specify the cluster you want.
If the console tree is collapsed, expand the tree under the cluster that you want to configure.
Expand Services and Applications.
Click the clustered service or application that you want to configure the pending timeout for.
In the center pane, right-click the resource for the service or application, click Properties, and then click the Policies tab.
Under Pending timeout, specify the length of time, in minutes and seconds, that the resource can take to change states between Online and Offline before the Cluster service puts the resource in the Failed state.

The default timeout value is 3 minutes. Change as per requirement.


Error during SQL Server 2008 R2 SP3 upgrade: MODIFY FILE failed. File 'MSDBLog' does not exist I have just installed SQL Server 2008 R2 SP3 and after installing it and trying to start the SQL Server instance again, it won't come online. In the SQL Server error log I can see that the following is logged:

2014-10-20 16:51:13.04 spid8s      MODIFY FILE failed. File 'MSDBLog' does not exist.
2014-10-20 16:51:13.04 spid8s      Error: 912, Severity: 21, State: 2.
2014-10-20 16:51:13.04 spid8s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2014-10-20 16:51:13.04 spid8s      Error: 3417, Severity: 21, State: 3.
2014-10-20 16:51:13.04 spid8s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2014-10-20 16:51:13.04 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

How can I fix the upgrade so it doesn't fail?

This happens because the msdb log file name has been changed from the default which is "MSDBLog". In my case, it was called "msdb_log". The name "MSDBLog" is hard-coded into the upgrade script so it fails if the name has been changed.

To fix the problem, we need to rename the log file to 'MSDBLog'. We have to start SQL with trace flag 902 to stop it from running upgrade scripts when it starts. We can then do our fix, remove the trace flag and start it again.

Open SQL Server Configuration Manager
Navigate to "SQL Server Services" in the left-hand pane
Right click on the SQL instance and select "Properties"
Click on the "Advanced" tab
In "Startup Parameters", add ";-T902" to the end (excluding quotation marks)
Click "OK" to save it
Start the SQL instance
Rename the log file back to its original name:

ALTER DATABASE msdb MODIFY FILE (NAME=N'msdb_log', NEWNAME=N'MSDBLog')
Stop the SQL instance
Undo the changes to the startup parameters
Start the SQL instance again

The upgrade scripts will now run and should succeed.


current_timestamp at time zone 'UTC' SQL Server equivalent What is the equivalent of the Postgres "current_timestamp at time zone 'UTC'" in SQL Server?

How do I select the current timestamp in UTC in SQL Server?

What you're looking for is the SYSUTCDATETIME function, which is used like this:

SELECT SYSUTCDATETIME();

It returns the current system date and time, adjusted to UTC time.

This seems similar to what the postgres equivalent does (except it looks like current_timestamp includes timezone information, while SYSUTCDATETIME does not).

If you need the time zone information, you can use a similar construction in SQL Server with the SYSDATETIMEOFFSET function:

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'UTC';

The results look like this:


How can I check if my user has create index permission? I'm working on a .NET library that is used with a third-party product I support.  My library may be used with many different versions of the product.  I don't control the schema of that product, but I can safely add additional indexes on standard tables.  My library is installed into other projects in such a way that we decided it needs to bootstrap itself at startup, rather than counting on whoever uses it to perform additional install steps.

My library has some startup code that depends on data from a standard table.  Some versions of the product are missing an index which, when missing, makes my library's startup painfully slow.

Some versions of the product do include the index, but the name of the index may vary.  I've already worked out how to detect if there is an index on the table that includes exactly the fields I want or not.  I also know how to create the index I want when it's not present.  (Maybe because this is the very first run, or maybe because someone dropped it.)

So I know whether to create the index, but I don't know if my connection has the permissions to do so.  I don't control the connection string -- I just use whatever is provided to me.

select @hasIndex = 1
    where {table has an index on the column I want}

if @hasIndex = 0
begin
    if {magic here: user has permission to create indexes}
    begin
        exec ('create index ix_TableName_FieldName on TableName(FieldName)');
    end
end

I could try to create the index and just eat the error if it fails.  My .NET app can ignore the problem and continue, but it will still show up in the SQL errorlog, which could raise unwarranted concern.  Besides, it feels sloppy.  There's gotta be a good way to not try something that is going to fail, right?

Apologies if this is a duplicate.  I searched around quite a bit. The nearest match I found was about granting permissions (one time, manually) to the user in order to restore a database.  I'm trying to check permissions (each startup, unattended) to keep things in line going forward.

You can use the sys.fn_my_permissions function to see if your user has ALTER on the table.

IF EXISTS(select * from sys.fn_my_permissions('dbo.thetable', 'OBJECT')
              WHERE permission_name = 'ALTER')
    BEGIN
        PRINT 'I have ALTER'
        --CREATE INDEX STATEMENT HERE
    END


Join with more records in result I have this query:

SELECT table1.foo, table2.bar, table3.abc, table4.xyz
  FROM table1 table1, table2 table2, table3 table3, table4 table4
 WHERE table1.id = table2.id
  AND table1.id2 = table3.id2
  AND table1.id3 = table4.id3
  AND (table1.foo BETWEEN 10 AND 30)

I'd like to get additional rows in results, with data in table1 that doesn't have matching records in table2 and table3, but still only those that match data in table4.
How can I write this?
I think I need two left joins and keep the third normal join.

Tables:

TABLE1:
 id (references TABLE2),
 id2 (references TABLE3),
 id3 (references TABLE4),
 foo

TABLE2
 id,
 bar

TABLE3
 id,
 abc

TABLE4
 id,
 xyz

You want query looking like this one:

SELECT foo.table1, bar.table2, table3.abc, table4.xyz
  FROM table1
 LEFT OUTER JOIN table2
  ON table1.id2 = table2.id
 LEFT OUTER JOIN table3
  ON table1.id3 = table3.id
 JOIN table4
  ON table1.id4 = table4.id
 WHERE table1.foo BETWEEN 10 AND 30

It's in SQL JOIN syntax, so You might want to check some tutorial or course, like one from CodeAcademy. Another great source helpful with learning about joins is Jeff Atwood's visual explanation of sql joins.

Just like @a_horse_with_no_name and @Vladimir_Oselsky commented, You should switch from old ANSI-89 SQL syntax to current ANSI-92, which allows for easier to write (and read) queries with more flexible JOIN syntax.


SQL Server Merge Replication - Safely shrink transaction log I have a couple of SQL databases running in merge replication.  My publishers HDD is full due to the transaction log of the database being replicated which is causing my snapshot agent to fail (out of disk space).

I want to shrink my transaction logs.  If I do, will it break my replication?  If it will break my replication, is there a way I can tell my publisher to put its files on a different HDD without having to restart replication.

Thanks!

Yes - you can shrink the transaction log without it breaking replication.  You can also move the publisher files to a different HDD by using ALTER DATABASE MODIFY FILE syntax, setting the publication database offline, copying the files to the new location, and bringing it back online.

I would start with shrinking the transaction log and see if that helps.


Find missing indexes tool available to find missing indexes in mssql 2008 ? can we find the missing index using Database Engine Tuning Advisor

SQL Server renders a handful of DVMs that can provide you with this information.  See the TechNet reference on About the Missing Indexes Feature.

Mainly, you will be dealing with four DMVs:

sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_details
sys.dm_db_missing_index_columns

Note: This is how you do it.  But be very careful when considering "missing indexes".  You could end up with a large amount of indexes and data manipulation will take a hit from that.  This does not replace prudent index consideration/creation.


How to control the order of servers listed in the SSMS connection dialog? My current project involves a lot of database shuffling as we upgrade from SQL 2008 -> 2012, re-task hardware, repeat.

One minor annoyance is the connection dialog in SSMS re-shuffling the server order to put the most recent on top.  Given that on any particular day I'm connecting to 6-10 DB servers and want to quickly find a specific one in the list, this behavior is much more of a hindrance than a help.

Is there a way to disable this behavior, having the servers listed alphabetically, or in the order they were added...or anything that doesn't change every time I connect to a server?

Barring that, is there an entirely different way to better manage connecting to a specific server from a list/farm of them?

I'm using SSMS for SQL 2012 (though I'm curious about 2008 solutions as well).

Use Registered Servers or set up a Central Management Server (CMS). I recommend the latter if you can take the (small) time to set up the infrastructure because it will allow everyone to see the same list of servers, instead of just you (Registered Servers are stored in the registry). The only "drawback" with a CMS is that you have to use Windows Authentication because passwords aren't persisted to disk.

Both these features allow you to maintain an ordered/grouped list of servers, and manage servers as groups. This is very powerful because Policy-Based Management is tightly integrated with the GUI.

This works with all server versions -- I run SSMS 2012 and manage 2005-2012 servers. A CMS server can be set up on an Express edition, so licencing isn't a concern; however, I do recommend taking system database backups so the server list is recoverable.


How can I minimize the risk of accidentally modifying the wrong database? I just learned the hard way that disconnecting from a server in the Object Explorer doesn't stop you afterwards from executing query windows that were already open on that server.

My situation is like this: I have one instance of SSMS that I use to connect to our dev/staging server and to our production server. I had to delete a bunch of data on dev so I figured I should close my connection to production, but I didn't pay attention to the query window I was using. (Luckily we had a backup of only a few hours old.)

I'm not the first person to destroy production data and I won't be the last I'm sure. So
I'm looking for checklists, best practices, etc. that help you to minimise the risk of executing queries on the wrong database. Have you had this happen to you before, and how have you adapted your workflow to try to avoid this?

One thing I like to do in SSMS is to use Custom Colours when connecting to database.
So you choose a nice bright Red for Live databases, and a gentle blue or green for dev or test systems.
I used to use the inbuilt SSMS, but these days I prefer the SSMS Tools Addon Colour coding.

Or like this for SSMS Tools (A really nice addon, and I find the colour better when it's on top, rather than on the bottom like the built in on)


pg_restore showing errors when specifying schema when backing up with pg_dump I have created two different dump files one without specifying the schema, another specifying the public schema
without specifying the public schema'
pg_dump -h IP_ADDRESS -p 5432 -U my_user -Fc my_db  > my_db_allschema.dump

and the pg_dump statement when specifying the public schema
pg_dump -h IP_ADDRESS -p 5432 -U my_user -Fc my_db -n public > my_db_publicschema.dump

When using pg_restore to restore the dump files, I get errors with the dump file that was generated when specifying the public schema.
postgres@debian:~$ pg_restore -h localhost -p 5432  -U my_user -d my_db my_db_publicschemaonly.dump
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 8; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;

pg_restore: from TOC entry 212; 1259 18102 TABLE abandoned_url real_estate
pg_restore: error: could not execute query: ERROR:  function public.gen_random_uuid() does not exist
LINE 2:     id uuid DEFAULT public.gen_random_uuid() NOT NULL
                            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE TABLE public.abandoned_url (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL
);

Looking at this statement that throws an error
CREATE TABLE public.abandoned_url (
        id uuid DEFAULT public.gen_random_uuid() NOT NULL
    );

The reason it's throwing an error is because pg_dump has put public before gen_random_uuid(), the following statement works fine when removing public before gen_random_uuid()
CREATE TABLE public.abandoned_url (
        id uuid DEFAULT gen_random_uuid() NOT NULL
    );

Am I creating the dump file incorrectly? Could this be a bug in pg_dump?

As Postgres does exports with if exists in both commands, but you can make the database be dropped before inserting the backup
use
When you create the dump
-c
--clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

The same exists for the pg_restore, but if you used it in pg_dump you don't need it here
-c
--clean

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)


Inserting two rows from single row I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind of contact information per row.

Here's how I'm doing it currently: http://sqlfiddle.com/#!6/c9b2e/1 (for some reason, it's not outputting anything, but the code works in SSMS).

Is there a way to do this without using two different selects? Maybe self-joining usersToImport or using case somehow.

As Aaron mentioned, for some reason you need to terminate each statement in Fiddle.

Anyway, for the query, you can use CROSS APPLY to expand the result set. This method scans the base table only once, and for each row applies the subquery, which in this case actually selects two rows:

INSERT INTO userContact(userId, contactType, contactInfo)
  SELECT
    ui.userId,
    c.contactType,
    c.contactInfo
    FROM usersToImport ui
    CROSS APPLY
    (
      SELECT 'email' AS contactType, ui.email AS contactInfo UNION ALL
      SELECT 'telephone', ui.telephone
    ) c;


What permissions are necessary for truncating a table? I have a SQL account with the following permissions on a database:

The db_executor role you see this account being a member of was created by this script:

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

GRANT EXECUTE TO [db_executor]
GO

When I run a select, update, insert or delete on the table, it works fine. When I try to truncate the table, it gives me this error message:

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

What permission is this account missing?

The best place to look for this information is in books online. The article on TRUNCATE TABLE here indicates:

  The minimum permission required is ALTER on table_name. TRUNCATE TABLE
  permissions default to the table owner, members of the sysadmin fixed
  server role, and the db_owner and db_ddladmin fixed database roles,
  and are not transferable. However, you can incorporate the TRUNCATE
  TABLE statement within a module, such as a stored procedure, and grant
  appropriate permissions to the module using the EXECUTE AS clause.

So ALTER is the minimum permissions required. You can get that as DB Owner, you can get that as DB_DDLAdmin. Or just grant alter.

If you think about what truncate does and how it works, this makes sense, it is a pretty "severe" command and empties the table of data and does it quickly.


With the advent of integrated source control, do stored procedure changes still need comments? Back in the old days, my team had a policy that any stored procedure modification required comments in two places:

Top of the stored procedure with an overall summary of the changes
made
Comments on each line where the change was made

It typically looked like this:

CREATE PROCEDURE usp_Test
/*****************************************************************
The purpose of this stored procedure is to get data.
Created by 8kb 2001-01-01
Modified: removed OR clauses from JOIN statement..8kb 2001-06-01
******************************************************************/
AS
BEGIN

SELECT *
FROM t1
JOIN t2
ON t1.colA = t2.colA
-- Removed OR clause..8kb 2001-06-01
-- OR t1.ColB = t2.ColB

END

But now with integrated source control, I can summarize the changes in the source control and then use the compare / diff functionality to see the differences between the old and new versions.

Is there still value in writing line-by-line change comments in stored procedures that are part of integrated source control?

This might be fine for one change per module, but imagine how your code will look like after severol years of development.

Before version control was universally adopted, there used to be modules that were 95% history log and only 5% actual code. Fortunately version control automated out that inconvenience long ago.

I would completely get rid of change logs in comments -  we are much more productive using modern tools. Besides, git and other version control tools allow you do do much more than see the log of changes if one file. For instance, they can will show you all other files changed in the same commit. Also they give you accurate information, while we cannot fully trust comments.


Find missing timestamps grouped by key: finding gaps in my data I have a table that has a timestamp, some data and a identifying key for the data source:

create table test_data (
    id serial primary key,
    key text,
    timestamp timestamp with time zone
);
INSERT INTO test_data
    (key, timestamp)
VALUES
    ('Source_A', '2018-03-15 01:07:06.603029+00'),
    ('Source_B', '2018-03-15 10:00:01.603029+00'),
    ('Source_A', '2018-03-15 11:05:06.603029+00'),
    ('Source_B', '2018-03-15 15:09:06.603029+00'),
    ('Source_B', '2018-03-15 16:09:06.603029+00');

I want to find the number of missing hours in the data grouped by each data source. I've got this code that works for a single group:

SELECT
COUNT(hours)-1 AS missing_hours,
'Source_A' AS key
FROM GENERATE_SERIES('2018-03-15', '2018-03-16', INTERVAL '1 hour') AS hours
  WHERE hours NOT IN
  ( SELECT TO_TIMESTAMP(FLOOR((EXTRACT('epoch' FROM timestamp) / 3600 )) * 3600) AS time_bit
   FROM test_data
   WHERE key = 'Source_A'
   GROUP BY time_bit)

Running this give me:

missing_hours,  key
22,             Source_A

I'm struggling to figure out how I can group by key and then get the number of missing hours for all data sources:

missing_hours,  key
22,             Source_A
21,             Source_B

Any ideas? This will be running on monthly partitioned tables with ~50 million rows each so I don't want to have it be too expensive. The single key query runs in about 2 secs.

One approach would be to count the hours for each key and substract that from total hours in given period.

WITH period as (
  SELECT COUNT(*) as total_hours
  FROM GENERATE_SERIES('2018-03-15', '2018-03-16', INTERVAL '1 hour') gs
),
key_counts as (
  SELECT key, COUNT(*) as hours
  FROM (
    SELECT distinct key, date_trunc('hour', timestamp)
    FROM test_data
    --apply period limit here
  ) kq
  GROUP BY KEY
)

SELECT key, total_hours-hours as missing_hours
FROM
  period,
  key_counts

dbfiddle


How to locate all the tables that have a field named username Is there a way in PhpMyAdmin to do a SQL search for all tables, that have a fieldname username in it?

I am trying to help a friend that has a database with over 1,000 tables in it.

He needs to find all of them that have username as a field name, so he can find the list of tables and not have to manually look at each table.

I don't know MySQL enough to do it.

Anyone know where to find the way?

I found it...

SELECT table_name, column_name, data_type, ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'dbname' AND column_name LIKE '%username%';


mysql_upgrade check if it's neccessary to run it How can I determine, do I need to run 'mysql_upgrade` command on database?

Percona-server-5.6.41 is installed and running now for a several monthes. Before it mysql-5.6 was installed. In-place upgrade was done (removed mysql and installed percona). But I don't know, was mysql_upgrade executed or not.

There're no errors about table mismatches in logs.

DB is large enough, 900+ tables and about 150Gb of size.

mysql_upgrade will generate a file informing if it was run on the datadir. If it was run for the specific version you have, the  mysql_upgrade_info file would contain the current version string and will prevent a new run of mysql_upgrade unless --force is used. Cat $datadir/mysql_upgrade_info file to know which version was ran on last.

Even if for some reason that file was missing, running mysql_upgrade is relatively safe- so much that most packages and init systems run it automatically on upgrade/start. I don't like to do it automatically, because I like to have control over when it is run- For example, it can take a very long time with many files, and it can create metadata locks due to the nature of the DDL commands it executes, but 99% of the installations can run it safely, assuming no other traffic is ongoing.

  DB is large enough, 900+ tables and about 150Gb of size

My 2TB database with 100K tables and 100K views takes around 30 minutes to run, to give you an idea (SSDs). My other 2TB servers with a few dozen tables just takes <10 seconds.

My recommendation is to just run it, unless you have long running queries on it. It can be killed in general in the worst case scenario. The problems when it is not run are worse (although it is true those normally only happen on major version upgrades or during the first minor versions within a major version).


sql server transactional - replication - the internal procedures often deadlock with user processes Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example:

  Deadlock of the replication procedure frame procname=Bocss2.dbo.sp_MSupd_dbotblBOrderItem line=79 stmtstart=8684 stmtend=14714 sqlhandle=0x03002a004d01df507b1245016ba3000001000000000000000000000000000000000000000000000000000000

With the following query:

SELECT 'INSERT INTO @TempTable1 (OrderNo, OrderSeqNo, OrderItemDate)
VALUES (LTRIM(''',strBxOrderNo, '''),', sintOrderSeqNo, ', ''',
  CONVERT(VARCHAR(20), sdtmOrderItemStatusUpdated, 113), ''')'
FROM tblBOrderItem
WHERE sintOrderItemStatusId = 4 -- In Picking
AND sdtmOrderItemStatusUpdated BETWEEN GETDATE() -30 AND GETDATE() -1
 -- Give the warehouse a chance to deliver the order
AND decFinalPrice > 0
ORDER BY strItemNo

I don't want to enable the snapshot isolation level - where writes don't block readers - at the cost of maintaining a row version at the tempDB, but I am considering partitioning the big tables that are usually the culprits on the deadlocks.

what stats I could have a look at so that I could guess whether or not this is a good idea?

Partitioning will likely not help, unless the user query in the deadlock can be rewritten in order to let partition elimination kick in.

I would focus on making both operations in the deadlock graph as fast as possible instead. This will involve ad-hoc indexing or rewriting the query in a different form.

If the replication agent is often the deadlock winner, you could consider lowering its deadlock priority, in order to let the user queries run without being killed. However, I would explore this option only as a safety net in case you're not able to reduce deadlocks to a very rare event.


pg_dump - how to split it into directories and files? I am looking for a way to dump PostgreSQL12 database model into a directory/file structure, instead of a single file - for versioning purposes.
I found this old thread, which mentions this exact case and describes a --split flag to be used with pg_dump.
However, this option does not exist in the current pg_dump for PostgreSQL12. I also tried the option --format=directory but that's not it. How do I achieve this effect?

The directory format will write one file per table, so that sounds like a good candidate. But if you are interested in a text diff, it is of course useless.
The only thing I can think of is to dump each table, view and function separately using the -t option. You might still be disappointed if you want a diff of the table contents, because rows in a PostgreSQL table keep changing position.
Best practice is not to meddle with the database and then to reverse engineer a diff, but to use a schema versioning tool like Liquibase and write SQL diff files yourself.


Migrating from MySQL RDS to Aurora RDS with CNAME record I understand migrating to Aurora is simply a matter of creating read replica from MySQL source and then promoting when caught up and stopping writes on MySQL RDS previous master.

I use Route 53 DNS that has a CNAME record. My plan is to just change the DNS to Aurora cluster but not sure if that is instant? Is there a way to stop the old MySQL RDS instance so it can’t be written to? (It as a Multi-AZ and there is no stop)

As far as i know you can not replicate Aurora (slave ) from EC2 (master), please correct me if I am wrong. Due to the super grant lack required to do the "Change Master". I would recommend to use DMS (databawse migration server) for that task.

migration steps:

set route53 TTL to 1 min
set read_only in current master, to make sure no more reads go into the master.
wait for all transactions go into the new master.
Stop DMS replication
change CNAME
...

Pay special atention if your application uses sticky connections, if so you can stop the old master completely to make sure all traffic goes into the new one.


Linked Server Error - Login Failed for user 'myaccount' I have a database on serverA. I have a windows account (myAccount) that is a member of db_datareader role on this db.

On serverB, I want to create a linked server to the database on server using myAccount.

However, I get Login Failed for user 'myAccount'.

Are there any other permissions I need to set?

In linked servers - you'll need to make sure you have permissions on both servers that you're looking to set the link between.

So for instance - if you're sa on Server A, you'll still need at least read-only access to the database you're trying to access on Server B in order to use the linked server.

Additionally - when you're configuring your linked server, it's important to make sure your security context is set correctly.

By default, Linked Servers are made without any security context which puts the responsibility for authentication on the connection string you specify.

Usually you'll want to use the credentials of who is attempting to connect through the linked server by selecting "Connections will be made using the login's security context", but this varies based on you and your companies needs.

More information on linked servers can be found on technet:

General Linked Servers -
https://msdn.microsoft.com/en-us/library/ms188279(v=sql.105).aspx

Linked Server Security -
https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx


Is it better to have multiple tables with the same primary key or one big data table? So there is one primary key so far used for IDs of locations.
Currently there are multiple tables (sheets) in Excel for the following

Events (Location ID, Date, Title, Description)
Animals (Location ID, Species, Name, Population)
Restrictions (Location ID, Permits, Zone)

Is the above bad practice? From what I have read online you should not use the same primary key across multiple tables.
How should the tables be structured to follow "best practices", have it be one big database table or is it fine to leave in multiple tables?

As per mustaccio's comment, when you have the same PrimaryKey defined in each of your Tables, that then guarantees a one-to-one relationship is enforced between each Table. In other words, based on the schema of your Tables provided in your question, you won't ever be able to have more than one Animal at one Event, or more than one Restriction for a specific Animal, etc. They all share the same unique Location ID.
If that meets your needs then your design is ok.
But if you want to be able to have multiple Animals at multiple Events with varying Restrictions for different locations etc, then you might be better off with the following design:
Add a fourth Table called Locations with the Location ID PrimaryKey.
Remove the Location ID PrimaryKey (and unique constraint) from all the other tables and instead create a ForeignKey relationship between it and your Locations table on Location ID.
Add a new unique PrimaryKey field to the Events, Animals, Restrictions tables with the appropriate names (e.g. Event ID for the Events table, Animal ID for the Animals table, etc).
And to directly answer your main question, it's better to keep these as separate Tables. That is called database normalization and is important for query efficiency and elimination of unnecessary data redundancy. In general, separation of concerns should be split into separate Tables.


DMZ server unable to connect through SQL Browser I have a Web server in my DMZ, I have a SQL Server 2008 R2 server inside the LAN, this database server has named instance with a NO dynamic port. I have set as firewall rules the connection from my DMZ server and the database server in the SQL Server instance port and the UDP port 1434 for SQL Browser.

I thought this will be enough to allow the connection between the two servers, however I created a UDL file in my DMZ server and tried to connect to the SQL server instance and it failed, but when I included the Instance port in the string, it worked.

Please can you help me, I want to be able to connect to the named instance without including the Instance port, instead use the SQL Browser as it would be for other serves inside the LAN.

Thanks

Although what you described should work as configured, another option would be just create an alias for your named instance.


get base urls with different "endings" after the last slash Okay, so have a wonderful script that checks the "base" url up to a certain number of slashes, but realized what I really need is checking the "ending" going backwards to the first non-escaped URL slash, and providing the prefixes are identical, display them.

I.e., let's say this is my data:

CREATE TABLE `testtable` (
  `field1` int(11) NOT NULL,
  `field2` datetime NOT NULL,
  `urlTest` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `testtable` (`field1`, `field2`, `urlTest`) VALUES
(1, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/somethingelse.php?id=1'),
(2, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/'),
(3, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/randomdata'),
(4, '2010-01-01', 'http://test1.com/identicalprefix/anotherprefix/randomdata/dada'),
(5, '2010-01-01', 'http://test1.com/singleprefix/randomdata'),
(6, '2012-02-02', 'http://test1.com/newscript/something'),
(7, '2013-02-02', 'http://test2.com/newscript/something'),
(8, '2014-02-02', 'http://test3.com/newscript/something'),
(9, '2014-02-02', 'http://test3.com/');

ALTER TABLE `testtable`
  ADD PRIMARY KEY (`field1`);

so - the script should find identical "base" urls with different endings, based on the last "slash" in the URL.

So in this case,

http://test1.com/identicalprefix/anotherprefix/somethingelse.php?id=1
http://test1.com/identicalprefix/anotherprefix/
http://test1.com/identicalprefix/anotherprefix/randomdata

would both be listed, because they have the same "base" URL (http://test1.com/identicalprefix/anotherprefix/), just with different suffixes, and same dates.

and obviously, "test3.com" would not display, because even though it shares a common base url, and common date - it is not the "same" entire base URL? (ie., "http://test3.com/" versus "http://test3.com/newscript/"

SELECT DISTINCT t1.*
FROM testtable t1, testtable t2
WHERE t1.field2 = t2.field2
  AND t1.urlTest != t2.urlTest
  AND SUBSTRING(REVERSE(t1.urlTest) FROM LOCATE('/', REVERSE(t1.urlTest)))
     =SUBSTRING(REVERSE(t2.urlTest) FROM LOCATE('/', REVERSE(t2.urlTest)))

fiddle


Why is it recommended to store BLOBs in separate SQL Server tables? This highly-upvoted SO answer recommends to put images in separate tables, even if there is only a 1:1 relationship with another table:

  If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee photo in the employee table - keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don't always need to select the employee photo, too, as part of your queries.

Why? I was under the impression that SQL Server only stores a pointer to some dedicated BLOB data structure in the table, so why bother to manually create another layer of indirection? Does it really improve performance significantly? If yes, why?

While I disagree that BLOBs should just be in another table -- they should not be in the database at all. Store a pointer to where the file lives on disk, and then just get that from the database...

The primary issue they cause (for me) is with indexing. Using XML with query plans, because everyone's got'em, let's make a table:

SELECT TOP 1000
ID = IDENTITY(INT,1,1),
deq.query_plan
INTO dbo.index_test
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq

ALTER TABLE dbo.index_test ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (ID)

It's only 1000 rows, but checking on the size...

sp_BlitzIndex @DatabaseName = 'StackOverflow', @SchemaName = 'dbo', @TableName = 'index_test'

It's over 40 MB for just 1000 rows. Assuming you add 40 MB every 1000 rows, that can get pretty ugly pretty quickly. What happens when you hit 1 million rows? That's just about 1 TB of data, there.

Any queries that need to use your clustered index now need to read all of that BLOB data into memory clarification: when the BLOB data column is referenced.

Can you think of better ways to use SQL Server memory than storing BLOBs? Because I sure can.

Expanding it to nonclustered indexes:

CREATE INDEX ix_noblob ON dbo.index_test (ID)

CREATE INDEX ix_returnoftheblob ON dbo.index_test (ID) INCLUDE (query_plan)

You can design your nonclustered indexes to largely avoid the BLOB column so regular queries can avoid the clustered index, but as soon as you need that BLOB column, you need the clustered index.

If you add it as an INCLUDED column to a nonclustered index to avoid a key lookup scenario, you end up with gigantic nonclustered indexes:

More problems they cause:

If anyone runs a SELECT * query, they get all that BLOB data.
They take up space in backups and restores, slowing them down
They slow down DBCC CHECKDB, because I know you're checking for corruption, right?
And if you do any index maintenance, they slow that down as well.

Hope this helps!


SQL Server Management Studio 2012 separated toolbars In SQL Server Management Studio 2012, is there a way to get the toolbars separated, so there is space between them? I can do this with 2008 R2 by just dragging the toolbar handle. But in 2012, I can snap them to a different row, but if I just drag them apart, they always dock right next to each other. I find it convenient to have some space between them, with the left-most button of each one being my most-used.

The only option close to what you need is to mark the control as "Begin a Group", to create a separator to the left of the control.

Open SSMS > Tools > Customize > Commands tab > under Toolbar select "Standard" > highlight "Activity Monitor" > click Modify Selection and you can (un)tick "Begin a group" and see how the toolbar changes. You could edit the Name to start with something like "__" for visual comfort (doesn't seem to take extra spaces).


User autoincrement ID for username I want to automatically create user's, which all have a unique ID.

Now here's the problem. If two users are created at exactly the same nanosecond, it may not cause problems. Thus, what i do now is:

Get the next Autoincrement number
Add a new user with the name "U" + autoincrement number + random(0-9)

When more than 10 users are created at the exact same nanosecond, this will still cause problems (in theory!). Thus, I'm looking for a way to let MySQL handle the username.

Consider the following table:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I want to use:

INSERT INTO test (id, name) VALUES (null, "U" + id)

where id is the autoincrement number. But, since id is not defined in MySQL, it will contain 0.

How to define id, so that it will contain the autoincrement number of that row.
This can easily be done by two queries, but I'm looking to do this in only one query :)

Thanks!

With MyISAM, there's a neat trick you can do:

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  PRIMARY KEY (name, id)
) ENGINE=MyISAM
  DEFAULT CHARSET=latin1
  AUTO_INCREMENT=1 ;

With the above PRIMARY KEY, if all users have different name, they will be given id = 1. If another user comes and chooses name that already exists, he will get id=2, etc.

UPDATE

It seems I misread your question and you only need to have name = 'U243' for user with id=243. If that's the case, you don't have to store the name at all. Use this (myISAM or InnoDB, it will work for both):

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=MyISAM
  DEFAULT CHARSET=latin1
  AUTO_INCREMENT=1 ;

and then, to retrieve the id and name:

SELECT
    id
  , 'U' || id  AS name
FROM
    test ;


How do I get Pentaho Reporting/Kettle to produce PDF reports by business entity? We use Kettle/PDI to handle usage records from legacy systems and feed them to an analysis database/DWH and also we report to our customers their activity and a backend commission report on a monthly basis from this legacy data.

Right now, I compile this into one enormous PDF using Pentaho Report Designer, then print it and hand it to the Finance gals who send checks out to the customer for the amount on the statement I've made.  They hand-collate all this.

Obviously we would like to put these online, since we have a file-area for each customer already.  The thing I need is for Pentaho to make, instead of a new page, a new PDF file for each business, and then name it the Business ID number and month, or something equally meaningful.  Is this possible?

We have experimented with splitting up the PDF but it takes someone several hours to process, and it is not pleasant work at all.  It seems this should be possible, but I do not know enough of the intricacies of Pentaho to make it work.

Parametrize your report and make sure that your query is parametrized too.
You can test your parametrized report inside the report designer.

Then use Kettle/PDI to feed the parameters to your report step, and once you produce the file, rename it or move it to the correct spot.

Running several smaller reports also allows you to run multiple reports in parallel. The reporting engine is inherently single-threaded, so one report process occupies one core. If you have more than one core, I recommend to run ([number of cores] - 1) instances of the step for optimum performance.