Moving away from CmdExec steps in job without losing logging detail (SQL Server 2008) My department currently relies heavily on CmdExec steps in SQL Server Jobs, called using a SQL Server Login.

For example

osql /e /n /S SERVER01 /U USER /P PA$$$ /d DATABASE_01 /i "\\LOCATION\OF\SQL\SCRIPT\scriptfile.sql" /o "\\LOCATION\OF\SQL\LOG\scriptfile.log"

For a lot of reasons (not least security and the expectation that this method will become obsolete) I'd like us to move away from this model and replace with a mixture of stored procedures and SSIS packages.

I've proposed this several times and I'm always told that we can only move in this direction if we can recreate the detail of logging possible by the method above, which our department has come to rely on quite heavily. The method above outputs the contents of the script fed in along with any server messages including all rowcounts and error messages, in context.

This does admittedly mean that errors that wouldn't count as errors for SQL Server are far more easily picked up -  Unexpectedly low row counts etc. You could put in as much error handling as you liked and never get something as easy to follow as the logging from this method, so I can see this side of the argument.

To sell my boss on a move away from cmdexec steps I'd like to find a method of recreating something like the output of the logging that cmdexec currently gives us - perhaps to SQL tables rather than files (although either would be fine) - in SSIS packages and stored procedures.

I'm familiar with using raiserror for error handling - I use this extensively in stored procedures - but the task here is not just to catch SQL errors, but to catch all that cmdexec logging catches and ideally in context of the script being executed... Short of writing a stored procedure and executing it after every step in every script I can't see a way of doing this.

Also I'm keen to hear about best practice approaches to logging for people who don't use cmdexec steps. I've learnt SQL in this department and this is the only way I've ever known of doing things.

Increasingly for my own development I try to write stored procs with error handling. And if I come across issues that don't throw errors, I do my troubleshooting by rerunning the code interractively on a dev server so I can monitor the step by step information. This is less convenient than the full logging we get from cmdexec but more secure.

Do other people do more than this? Is there a better way?

Set the output of the T-sql job step to output to a file. It's on the advanced tab of the job step properties.


postgresql statement taking ages to cancel in pgAdmin III I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for slightly over an hour before it reported as cancelled. For all that time the CPU was around 98-99%.

The statement that was running was a call to a function. It was only part way through the function when it got stuck and all the time was taken on the following statement:

SELECT ST_UNION(shape)
FROM descendant_areas
INTO best_fit_areas;

where descendant_areas is a temporary table with no indexes containing nine records. Shape is of type geography.

Versions:

PostgreSQL=PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

PostGIS=POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.0.2, released 2016/01/26" LIBXML="2.9.1" LIBJSON="0.12" TOPOLOGY RASTER

My question is:

Is it normal for cancelling a query to take that long or is it indicative that there are config settings set up badly?

PostgreSQL generally does not visibly respond to cancellation requests while it is inside an externally-defined function.  It will simply set a flag, and then process the request between calls to ST_UNION, but not within them.


How do I keep a SQL Server Profiler trace from jumping to the bottom entry? When monitoring a trace in SQL Server Profiler, is there a way to prevent it from jumping to the most recent event without pausing the trace?

I'm often trying to scroll up and read descriptions of events, and as a new one comes in, it jumps to the bottom.

Simple Answer :

From the toolbar, select Window>Auto Scroll


How to split data from column to multiple columns I am just starting DBA and curious about how can I split entire column with data like: "1-2/456/£25.00-26.00/56/10+". My delimiter is "/" and I need to get data in 5 columns accordingly. So data to be displayed as: Col01: 1-2; Col02: 456; Col03:£25.00-26.00, Col04:56; Col05: 10+. I tried Jeff Moden, but it splits data to rows, not to columns. I am struggling to link function to use data from column, not just values.

SELECT * FROM [dbo].[DelimitedSplit8K]('Masha/Sasha/Nadia/Loki','/')

So I actually need column instead of 'Masha/Sasha/Nadia/Loki' and convert to columns, not rows.

String splitting functions return the data in rows, because handling dynamic columns is a lot more complex in SQL because that's not really what SQL is supposed to be used for.

To get data in columns, you can use for example pivot or max + case, something like this:

select
  max(case when ItemNumber = 1 then Item end),
  max(case when ItemNumber = 2 then Item end),
  max(case when ItemNumber = 3 then Item end),
  max(case when ItemNumber = 4 then Item end),
  max(case when ItemNumber = 5 then Item end)
from
  [dbo].[DelimitedSplit8K]('Masha/Sasha/Nadia/Loki','/')

You could also create a function / SQL that uses charindex + substring to find the places of the separator and just select the 5 pieces directly without creating them into rows.

If you need to handle dynamic amount of columns, then you'll have to create the SQL dynamically and calculate the maximum number of separators in your data first.


MySQL - Problems caused by SQL Security definer or invoker I have a view used to be defined as:

> **CREATE
>     ALGORITHM = UNDEFINED
>     DEFINER = `user1`@`localhost`
>     SQL SECURITY DEFINER VIEW `schema1`.`view1` AS
>     select
>        ...
>     from
>      ...
>     where
>      ...**

The user1 has all privileges to the schema1's objects. And if I, as user2,

> select * from view1

, it works fine. But If I do:

   SELECT * FROM schema1.view1 INTO OUTFILE '/tmp/file.txt'  FIELDS TERMINATED BY '|'

I got error saying:

  Error Code: 1356. View 'schema1.view1' references invalid table(s) or
  column(s) or function(s) or definer/invoker of view lack rights to use
  them   0.000 sec

If I changed the definition of view from "SQL SECURITY DEFINER" to "SQL SECURITY INVOKER", and do the above query again (and I also have full privileges to the objects of the schema1), it works.

Can anybody explain why this happens? Thank a lot.

i got same issue
on my case, my resolution re-create the view with change this part from
CREATE ALGORITHM=UNDEFINED DEFINER=`sst_user`@`10.148.0.0/255.255.240.0` SQL SECURITY DEFINER

to this
CREATE SQL SECURITY INVOKER VIEW

and test select the view from user.


shrink after archiving, is this that bad too? I have a database which grow to 20 GB, after archiving some data, the real size of tables is just 5GB.
Backup script does save a copy of the backup to a different location.
Moving 20GB or 5Gb does make a difference, so I would like to reduce the physical size, but everywhere I read to do not shrink if I don't want to affect performance.

But it this case of (periodically/quarterly/annually) archiving, is it still recommended not to shrink?

What you are seeing on net is mostly a copied advise where people actually want to say that "please don't make shrinking data file or log file a daily routine operation". Had it been so bad Microsoft would have removed it but it is still there and even most experienced DBA's and developer use it but they are aware about the after affects so they know what to do after. Only shrinking can give you back free space

Yes of course you can shrink data and log file because you want to reclaim space and if reclaiming space is very much required. If you see Paul's article he has given a method like

The method I like to recommend is as follows:

Create a new filegroup
Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

You can use it if it suits you. Since you are aware shrinking is bad activity and would do it once in a year may be twice this is actually not bad. Shrinking do causes logical fragmentation so dont forget to rebuild indexes after shrinking.

  Backup script does save a copy of the backup to a different location. Moving 20GB or 5Gb does make a difference

Backup only includes data and few amount of transaction log(if required) to bring back database in consistent state after restore. So again backup would be around 5 G not 20G but yes when you restore it back it would take 20 G space

PS: I am not supporter of shrinking the database or database files but in extreme scenario and knowing the repercussions you can do it.


Formatting the output of SQL Server query I am new to SQL Server and SSRS...and am trying to get a matrix report formatted correctly.

My query is:

select e.name, ea.element,
    case when ea.element = 'projected_status' then 'Projected Status'
         when ea.element = 'projected_spstatus' then 'Projected Sp Status'
         when ea.element = 'day1_status' then 'Day 1 Status'
         when ea.element = 'day1_spstatus' then 'Day 1 Sp Status'
    end as FIELD,
    el.value
from employeelocation el
join employee e on e.employeeid = ea.employeeid
join employeeattribute ea on ea.attributeid = el.attributeid
where ea.attributeid in (888,899);

and I am getting this information returned:

+--------------+-----------+-----------+-----+
| Barry Green  | Projected | Status    | 484 |
| Barry Green  | Projected | Sp Status |  29 |
| Barry Green  | Day 1     | Status    | 589 |
| Barry Green  | Day 1     | Sp Status |   2 |
| George Cart  | Projected | Status    | 324 |
| George Cart  | Projected | Sp Status |  12 |
| George Cart  | Day 1     | Status    | 299 |
| George Cart  | Day 1     | Sp Status |   0 |
+--------------+-----------+-----------+-----+

What I want to create is an output like this:

I can create the basic matrix report in SSRS, but I don't know how to format the groupings.  I don't know if this is something that I need to do in my query or if there's a way to achieve this through format options in SSRS.

I just need to be pointed in the right direction.  Any assistance is greatly appreciated.

Thank you!

I have put your results into one table and then adapted your query. I've taken out the joins and used GROUP BY and PIVOT you will need to put the joins back in to make it work for you.

select name,
   [Projected Status]=max([Projected Status]),
   [Projected Sp Status]=max([Projected Sp Status]),
   [Day 1 Status]=max([Day 1 Status]),
   [Day 1 Sp Status]=max([Day 1 Sp Status]),
   [%Diff]=cast(max([Day 1 Status])as float) / cast(max([Projected Status]) as float)*100
from
(
    select name,
           element,
           value,
           row_number() over(partition by [element] order by [element]) rn
    from tableX
) t
pivot
(
    sum(value)
    for [element] in ([Projected Status], [Projected Sp Status],[Day 1 Status],[Day 1 Sp Status])
) piv

group by name

SQL Fiddle Demo


How to setup vault and Postgres in Google cloud to have the correct permissions? The Problem

I am trying to setup Hashicorp vault and Postgres in Google Cloud.

I am using liquibase to manage the schema and when that does a migration, it retrieves a username/password from vault and then runs under that user to perform the migration.

However, the default postgres user, or any other user that tries to use that table can't and receives the ERROR:  permission denied for relation.

If I understand correctly, the users are being created while logged into as the role of postgres. This all works locally but the google cloud stuff seems to be setup in a vastly different way.

Here are some outputs to see what the database is setup as:

\du
                                                List of roles
            Role name             |                         Attributes                         |      Member of
----------------------------------+------------------------------------
------------------------+---------------------
 cloudsqladmin                    | Superuser, Create role, Create DB,
Replication, Bypass RLS | {}
 cloudsqlagent                    | Create role, Create DB
| {cloudsqlsuperuser}
 cloudsqlreplica                  | Replication
| {}
 cloudsqlsuperuser                | Create role, Create DB
| {}
 postgres                         | Create role, Create DB
| {cloudsqlsuperuser}
 test                             | Create role, Create DB
| {cloudsqlsuperuser}
 v-token-power-watc-p079t4r13s85w | Password valid until
| {cloudsqlsuperuser}

\dt
                            List of relations
 Schema |         Name          | Type  |              Owner
--------+-----------------------+-------+------------------------------
 public | databasechangelog     | table | v-token-power-watc-p079t4r13s85w
 public | databasechangeloglock | table | v-token-power-watc-p079t4r13s85w
 public | test                  | table | v-token-power-watc-p079t4r13s85w

The vault creation SQL looks something like this:

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "{{name}}";
GRANT cloudsqlsuperuser to "{{name}}";"

I've tried playing with the roles in a number of ways but it seems all of the new roles that I create are always put into their own sandbox and can't play with any other roles. I'm at a lost at this point of exactly what to do next, it seems that no matter the grant options I can pass, it doens't help.

Current Questions

Is it that the owner needs to be the cloudsqlsuperuser group instead of the temp ownwer?

Is there a way to make that the default instead of having to make sure all my tables are created with the right owner?

Replicate with a local docker instance

Docker Command to start Postgres Database

docker run --rm --name database -v $(pwd)/setup.sql:/docker-entrypoint-initdb.d/setup.sql -e POSTGRES_USER=temp -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=mydb postgres:9.6

Contents of setup.sql

ALTER ROLE postgres RENAME TO cloudsqladmin;
CREATE ROLE cloudsqlsuperuser WITH CREATEDB CREATEROLE;
ALTER DATABASE mydb OWNER TO cloudsqlsuperuser;
CREATE ROLE "postgres" WITH LOGIN CREATEDB CREATEROLE IN ROLE cloudsqlsuperuser;

Creating user under postgres simulating vault user creation

docker exec -ti database psql -U postgres -d mydb -c "CREATE ROLE testuser WITH LOGIN IN ROLE cloudsqlsuperuser"

Create test table from testuser

docker exec -ti database psql -U testuser -d mydb -c "CREATE TABLE test (col1 text)"

Try and select table demonstrating error

docker exec -ti database psql -U postgres -d mydb -c "SELECT * FROM test"

Short Summary

You have to run an REASSIGN OWNED BY "$VAULT_USERNAME" TO "cloudsqlsuperuser" after the table/sequence/function creation process to reassign back to a the common role.

Longer Answer

Postgres 9.6 CREATE TABLE will by default create the table under the user's role. Because the vault user is designed to be a temporary credential, we need to also make sure that the objects created under the temporary roles are reassigned back to a common role of some kind. In the specific case I outlined in Google Cloud, we are going to reassign back to cloudsqlsuperuser.

The one command I found to work is the REASSIGN OWNED BY current_user to "cloudsqlsuperuser" command that will then take all of the objects owned by that temporary user and assign back to that common role.

Bonus: liquibase config

I am using liquibase to do my migrations and this is in my change log to fix the permissions everytime we run a migration:

<databaseChangeLog>
    <changeSet author="elindblom" id="fix-permissions" context="" logicalFilePath="fix-permissions" runAlways="true" runOrder="last">
        <sqlFile path="baselines/sql/fix-permissions.sql" splitStatements="false" relativeToChangelogFile="true" stripComments="false"/>
    </changeSet>
   ... rest of your change logs after ...
</databaseChangeLog>

The SQL looks like this:

REASSIGN OWNED BY current_user TO "cloudsqlsuperuser";

The changeSet runs after everything comes to completion and runs every-time. The very important bits are the runAlways and runOrder elements.


Can vmware (virtualisation) configuration impact the data integrity of the data file I'm wondering how much can a VMWare configuration impacts the integrity of the data file.

I mean, if the VMWare configuration is not optimal and the database writes a lot of complex operations (creations / modifications / deletions).

Can poor performances and bad configuration of the virtual machine, that host the database, lead to a potentially corrupted datafile when the the application is solicited a lot in read/write operation?

Your question doesn't contain many details so I'll treat it as a hypothetical question.

Combining data and log files, or any other type of files shouldn't lead to corruption in .vmdk's any more than it would on a normal disk.

You haven't specified which RDBMS or OS you are running, and maybe some obscure systems exist where combining data and log files on the same drive causes the files to become corrupted but I would consider that a bug in the software and it would happen just as well with physical drives.

The VMWare knowledge base contains some documented corruption issues, but none of them have anything to do with combining certain types of files or write operations on the same vmdk.

If you have actual corruption things that come to mind are issues with vmotion or old veaam versions in combination with large backup sizes, but not combining certain files.


How to simply add ' and " to numeric values for height(inch) format? I have a height field that contains two numeric values and I need to add ' and " at the end of each number to display the value as height(inch) format.
It seemed simple but couldn't find answers that I was looking for.

Height -> Height(inch)

72     ->   7'2"

65     ->   6'5"

68     ->   6'8"
.            .
.            .
.            .
.            .

Any suggestions? I am very new to sql and and would appreciate any help I can get!!


How to get row number without windows functions in SQLite? Assume there is the following table schema and data

create table tbl(
    id integer primary key,
    name text,
    pid integer
);

insert into tbl
values
    (1,'d',0),
    (2,'c',1),
    (3,'b',1),
    (4,'e',1),
    (5,'b',0),
    (6,'a',0);

It is a one-level depth hierarchy of parent and child items.

I need to aggregate children names into their parents like so

 id | name | children_names
----+------+------------
  6 | a    |
  1 | d    | c, e, b
  5 | b    |

children names need to be sorted within each row and the entire result need to be sorted by name column alphabetically, but all b names must always go last.

In PostgreSQL I would do it with row_number() over() window function like this

with
t as (select * from tbl order by name),
t2 as (select * from t where name<>'b' union all select * from t where name='b'),
t3 as (select *, row_number() over() from t2)
select a.id, a.name, string_agg(b.name, ', ' order by b.row_number)
from t3 a left join t3 b on a.id=b.pid and a.id<>b.id
where a.pid=0
group by a.id, a.name, a.row_number
order by a.row_number

but I need this in Android Room which lacks window functions yet.

So how to get the same result without window functions used?

To sort group_concat() values, the original rows must come from an ordered subquery:

SELECT id,
       name,
       group_concat(cname) AS children_names
FROM (SELECT p.id,
             p.name,
             c.name AS cname
      FROM      tbl AS p
      LEFT JOIN tbl AS c ON p.id = c.pid
      WHERE p.pid = 0
      ORDER BY c.name = 'b',
               c.name)
GROUP BY id
ORDER BY name = 'b',
         name;


How do I merge two rows only if there is the TagWidgetIds are same but the tagValues are different? I have this stored procedure which generates the following output.

The query which generates the output from the stored procedure is as follows:

SELECT tat.[agent-id],
               tat.[agent-guid],
               tat.[agent-name]  AS [AgentName],
               tw.TagWidgetID,
               tw.TagWidgetName,
               t.TagID,
               core.fnInitialCapitalization(t.DisplayName) AS TagName,
               tt.TagTypeID,
               tt.TagTypeName,
               tdt.TagDataTypeID,
               tdt.TagDataType,
               tu.TagUnitID,
               tu.TagUnit,
               --CASE
               --     WHEN itv.TagIndex IS NULL THEN 0
               --     ELSE itv.TagIndex
               --END               AS [TagIndex],
               ISNULL(itv.TagIndex, 0) AS [TagIndex],
               --CASE
               --     WHEN itv.ValueIndex IS NULL THEN 0
               --     ELSE itv.ValueIndex
               --END               AS [ValueIndex],
               ISNULL(itv.ValueIndex, 0) AS [ValueIndex],
               --CASE
               --     WHEN itv.[Value] IS NULL THEN 'N/A'
               --     ELSE itv.[Value]
               --END               AS [TagValue]
                ISNULL( itv.[Value], 'N/A') AS [TagValue]
        FROM   @tmpAgentTags tat
               LEFT OUTER JOIN tag.ItemTagValue itv
                    ON  itv.FkTagID = tat.[tag-id]
                    AND itv.FkItemID = tat.[agent-id]
               INNER JOIN tag.Tag t
                    ON  t.TagID = tat.[tag-id]
               INNER JOIN tag.TagWidget tw
                    ON  tw.TagWidgetID = t.FkTagWidgetID
               INNER JOIN tag.TagType tt
                    ON  tt.TagTypeID = t.FkTagTypeID
               INNER JOIN tag.TagDataType tdt
                    ON  tdt.TagDataTypeID = t.FkTagDataTypeID
               INNER JOIN tag.TagUnit tu
                    ON  tu.TagUnitID = t.FkTagUnitID
               INNER JOIN item.Item i
                    ON i.PkItemID = tat.[agent-id]
        WHERE i.FkItemTypeID NOT IN (5,8)

How can  I join the output rows only if the TagWidegetIds are same but the TagValues, TagIndex and ValueIndex columns are differnt? please advice

You can first insert the results from the stored procedure into a temporary table (see https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table), and then simply query that table with your conditions.  Let's say that you put the results in a temporary table called #procData.  In that case, your final query would look something like:

SELECT <columns_you_want>
FROM #procData p1
JOIN #procData p2
    ON p2.TagWidgetId = p1.TagWidgetId
    -- NOTE: if your columns are nullable, adjust this logic to account for that
    AND p2.TagValues <> p1.TagValues
    AND p2.TagIndex <> p1.TagIndex
    AND p2.ValueIndex <> p1.ValueIndex


pt-table-sync fails "Turning off AutoCommit" While trying to use pt-table-sync with the following options for a list of slaves:

pt-table-sync --print --replicate schema.checksum_table --sync-to-master slave-001

I receive the following error message on some of the slaves:

DBD::mysql::db STORE failed: Turning off AutoCommit failed at /usr/lib/perl5/DBI.pm line 723.

The MySQL version for the slave is 5.6.17 and for the master 5.6.15. Other slaves for which this does work also have MySQL 5.6.15 version.
I am able to turn off session autocommit if I log in to the slave directly using MySQL client and the same user being used for pt-table-sync.
The checksum table in use was created by previously running pt-table-checksum.

DBI.pm version:

perl -MDBI -e 'print "$DBI::VERSION\n";'
1.63

Has anyone encountered this issue before?

Please let me know if you need me to provide more information.

I think the bug is in your copy of DBD::mysql which seems to have been fixed sometime before version 4.027. Make sure your copy of DBD::mysql is up to date. 4.041 was released in Nov of 2016.

cpan DBD::mysql


Rebuild master database only in sqlserver 2012 How can I rebuild system database master only when I rebuild system database. Is it possible?

Well, you cant. So your only option is to backup msdb, model and distribution rebuild the system databases from setup and then restore the backups.

If that is not an option, i.e. the sql server does not start you can rebuild the system databases, then install all patches until your server is on the same build level as before, turn off the sql server service and copy the database files for the other system databases over the current ones, remembering to take a copy of them first.


Machine login for Microsoft SQL Server for Linux -- old question --
I'm building a multi-tenant SQL Server for Linux solution for hosting companies. Basically end-users will get access to SQL server with their own user account.
Now, I want to be able to backup their databases every day using the SQL Commandline tools. This is no issues, except for the fact that I don't know the password set for the user's account to login into SQL, AND I don't know the SA account password (as my clients choose this themselves).
Is there any way to log into SQL server as the 'machine' account (much like the local accounts that you can use on the Microsoft OS) to login so I can set up unattended backups through scripting without needing to know passwords for these users ?
I checked SQL Server Management Studio, connected to the Linux box and saw there is a user called BUILTIN\Administrators, but I have no idea how to login with this user to SQL, I tried logging in using root+password, but this doesn't work.

-- Altered question --
I'm building a plugin for a hosting panel (like plesk). This plugin allows hosting customers (end users) to use SQL Server for their hosting needs within their own environment. My real customer is the server owner (hosting company). THey need to

Install SQL server on linux

After that's been done I have the following tasks:

Whenever a new hosting account is created, I hook into this event and Create a new SQL user with a new database assigned to this user. Account creation is not a manual process, so this can run n times per day, unattended.
I will add said database to a script that performs backups automatically using cronjobs
The (hosting customer) will be able to create new "databases" from their hosting control panel on demand (self service). In practise we will validate the user account, and create a new database on their behalf (using a shell script with SQL commandline)

For all actions specified above I will need a sysadmin user to perform these tasks. I can't prompt the hosting company for the SA password every time I need to perform something (as the processes above are unattended), and it's bad practise to save/store the SA password somewhere in a text file.
How can I execute options 2-3-4 without storing the SA password? Should I create a secondary sysadmin account with a password that's generated by the machine?
I'm open-sourcing this plugin when I'm done, and even if we use generated passwords, people will be able to find them out (we need to store it, somewhere).
SQL Agent won't work (as I don't have the SA password), and some actions can't be run by SQL agent as they are 'on-demand' (point 2 and 4 above)
Any tips on my revised question?

I checked in with the WHM+cPanel - the company behind the hosting control panel and they said they store their MySql root user password on a local file on disk as well for doing all the automated maintenance, creation etc.

Excerpt from their docs:
By default, cPanel & WHM's implementation of MySQL stores the MySQL root account password in the /root/.my.cnf configuration file. Do not edit this file manually.

I'm going to end up with doing the same, and storing the file (read only) in the same /root/ folder.


Loop over all records in a table and edit a column in SQL Server How can you loop over all the rows in a SQL Server table and edit a column? You know that:

There are a lot of IDs skipped.
The column creatie must be edit whit every time one minute. So the first must be 15:00:00, the second 15:01:00, the third 15:02:00 etc.
In total are there 54 records.
I use SQL Server 2014 Express version

I have used the query below but the maximum ID is 24697... So the loop is very long.

declare @var datetime = '2015-10-19 0:0:0';
declare @counter int = 1;
declare @max int = (select max(id) from topic);

while (@counter <= @max) begin
    update Topic set Creatie = Creatie + @var where id = @counter;
    set @counter += 1;
    set @var += '0-0-0 0:1:0'
end;

Because I do every time @var += '0-0-0 0:1:0', I don't know what the result goes be after 24697 times (if it will works).

Here are the top 25 rows.

PS1: the year 2131 is a mistake my myself. don't look at it and use the year 2015.

PS2: I work with a 24h clock.

No, don't loop. Any time you think in SQL "I need to loop over this set and do something to every row" change your thinking to "I need to do something to every row in this set."

DECLARE @start SMALLDATETIME = '2015-08-06T15:00:00';

;WITH x AS
(
  SELECT id, creatie, rn = ROW_NUMBER() OVER (ORDER BY id) - 1
  FROM dbo.Topic
  --WHERE creatie = '2131-08-06T15:00:00'
)
SELECT id, creatie, DATEADD(MINUTE, rn, @start) FROM x ORDER BY id;
-- UPDATE x SET creatie = DATEADD(MINUTE, rn, @start);

When you are happy that this does what you expect, comment the SELECT and uncomment the UPDATE.


MySQL command denied for DELETE with JOIN I have a DELETE query that joins three tables. I have explicit GRANTS for the DELETE on the three tables, but not the database. I'm getting a ERROR 1142 (42000): DELETE command denied to user error when the query runs. I suspect this is happening because of the joins (even though the user has grants to delete from all three tables). I can delete from any one of the tables without issue but with the join, it fails. Does anyone know if there is a way to work around this, allowing the query to succeed?

Edit: more information

The query that I'm attempting to run is:

DELETE FROM t1, t2, t3
USING d1.table1 t1
LEFT JOIN d1.table2 t2 ON t1.id = t2.t1_id AND t2.t2_start_date = 1481554800
LEFT JOIN d1.table3 t3 ON t3.t2_id = t2.id
WHERE t1.xyz = 18

The error I'm getting:

SQLSTATE[42000]: Syntax error or access violation: 1142 DELETE command denied to user 'username'@'ip_address' for table 'table1'

The grants I have in place are:

GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table1` TO 'username'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table2` TO 'username'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table3` TO 'username'@'%'

I've finally tracked down the cause of the permissions issue I'm running into. It turns out that the problem was actually a bug in MySQL.

We're running our infrastructure on AWS Aurora which runs MySQL version 5.10. To set up for providing more information, I tried to recreate the problem locally in a docker container, using the latest version of MySQL (5.7.16). To my surprise, I couldn't reproduce this issue locally, which led me to believe the problem has to be with the difference in MySQL version.

Going through the MySQL version logs, I noticed that in MySQL version 5.6.27, a bug was fixed:

  DELETE could check privileges for the wrong database when table
  aliases were used. (Bug #20777016)

This sounded promising so I modified the query to not use aliases and sure enough, the permissions issues went away.


MySQL: carriage-return in query breaks my import I use simple echo "select * from tab" | mysql --batch --quick --skip-column-names .... > /tmp/file from mysql to get data.
Then, import to postgres using \copy tab FROM /tmp/file DELIMITER E'\t' QUOTE E'\b' NULL AS '\N' CSV.
for the most part it works well up until a carriage-return value comes in a text field.
Something like(partial string):
id      error
122859  Error invoking external process   % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current^M\n                                 Dload  Upload   Total   Spent    Left  Speed^M\n^M  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0^M  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0^M\ncurl: (35) Unknown SSL protocol error in connection to file.fm:443 ^M\n<br>The error occurred on line 26 in file.

When ^M comes it breaks my import, I was initially thinking this is import issue, but clearly it is the export (mysql) side.
I saw this solution but I'm thinking if there is a more elegant solution that doesn't requires me to rewrite all my export queries. Also, replacing \r\n to \n doesn't solve all the problems in this output example.

If CR or CRLF is the line terminator, so state.  Don't assume LF is smart enough to include CR.


SQL Server 2008 R2 Cluster Service pack 3 fail to failover node I have a SQL Server R2 cluster on Windows 2008 R2 boxes, and today i was to install SP3. I installed it without any errors and rebooted the passive node.

When i then try to failover to the passive node it fails. I get this from the Log

Log Name:      Application
Source:        MSSQLSERVER
Date:          2015-03-24 18:38:45
Event ID:      19019
Task Category: Failover
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      computername.domain.se
Description:
[sqsrvres] OnlineThread: SQLClusterResourceWorkerWaitForCompletion failed (status 0)

Event Xml:

    19019
    2
    3
    0x80000000000000

    16109747
    Application
    computername.domain.se

    [sqsrvres] OnlineThread: SQLClusterResourceWorkerWaitForCompletion failed (status 0)

    4B4A0040010000000D0000005700460041004C0049005400450043004C00300031003400000000000000

Log Name:      System
Source:        Microsoft-Windows-FailoverClustering
Date:          2015-03-24 18:38:45
Event ID:      1205
Task Category: Resource Control Manager
Level:         Error
Keywords:
User:          SYSTEM
Computer:      computername.domain.se.se
Description:
The Cluster service failed to bring clustered service or application 'Computername' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application.
Event Xml:

    1205
    0
    2
    3
    0
    0x8000000000000000

    369312

    System
    computername.domain.se.se

    computername

If i failback to the unpatched node, i get the resorces online without problem. But the patched node just keep on failing.

Does anybode have an idea on where i should start diggning?

I found the issue after a long time. It was due to 1 million+ files in the \MSSQL\log folder.

After setting up a job that cleared that folder. The failover after SP install worked fine.

The solution was confirmed on both this 2008R2 cluster, and the 2012 cluster we had the same problem on.


Why does CURRENT_LOAD_SOURCE() return "Batch No. 1" instead of the actual source file name? I'm loading some data into a table like this:

COPY table1 (
    col1,
    col2,
    file_name AS CURRENT_LOAD_SOURCE()
)
FROM LOCAL :src_file
REJECTED DATA :rejected_file
EXCEPTIONS :exceptions_file
SKIP 1;

It works, except file_name gets populated with the string Batch No. 1 instead of the actual base name of :src_file.

The doc for CURRENT_LOAD_SOURCE() doesn't explain why this happening.

Is this a bug? How do I get the actual name of the source file?

This certainly looks like a bug, whether you want to characterize it as an oversight, unfinished feature, or something else.

Vertica support tells me this is a known issue and a fix is being tracked as part of VER-36735. (Unfortunately, their issue tracker is not visible to the public.)

The problem appears to be related to the LOCAL clause, which is used to load files that are on the client machine as opposed to the server.

If you remove the LOCAL clause (and put the files you are loading on the server), then CURRENT_LOAD_SOURCE() will return the file name of the file being loaded, as expected.

COPY table1 (
    col1,
    col2,
    file_name AS CURRENT_LOAD_SOURCE()
)
FROM :src_file  -- no LOCAL!
REJECTED DATA :rejected_file
EXCEPTIONS :exceptions_file
SKIP 1;

If you're stuck with the LOCAL, then your best bet is to simply pass in a new variable (e.g. :file_name) that you explicitly set with the name of the file you are loading. You'll just need to be sure to provide the base name of the file (as opposed to the full path), to match the intended behavior of CURRENT_LOAD_SOURCE().


Dump single rows (with blobs) to single files I am creating MySQLDumps for the sake of versioning of a database. The problem currently is a table with blobs.

My first approach would be to dump single tables to single files. But next, I want on this specific table to dump each row to a single file.

How can I accomplish this using MySQLdump?

There are three(3) things you need to do

Provide a list to tables that you want to dump row by row
Use --skip-extended-insert, which forces each INSERT to be one row
Use --hex-blob

Here is such a script

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-h127.0.0.1 -P3306 --protocol=tcp -u${MYSQL_USER} -p${MYSQL_PASS}"

DB_TO_DUMP=mydb
TBLISTFILE=/tmp/tables_in_${DB_TO_DUMP}.txt

TABLES_TO_SPLIT_BY_ROW="tb1 tb2 tb3"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${TBLISTFILE}

TBLIST=""
for TB in `cat ${TBLISTFILE}` ; do TBLIST="${TBLIST} ${TB}" ; done

DT=`date +"%Y%m%d%H%M%S"`
for TB1 in `echo "${TBLIST}"`
do
    ROW_BY_ROW=0
    for TB2 in `echo "${TABLES_TO_SPLIT_BY_ROW}"`
    do
        if [ "${TB1}" == "${TB2}" ] ; then ROW_BY_ROW=1 ; fi
    done
    MYSQLDUMP_OPTIONS=""
    if [ ${ROW_BY_ROW} -eq 1 ]
    then
        MYSQLDUMP_OPTIONS="--hex-blob --skip-extended-insert"
    fi
    DUMPFILE=${TB1}_${DT}.sql
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB_TO_DUMP} ${TB1} > ${DUMPFILE}
done

Just supply the name of the database in the DB_TO_DUMP along with the tables whose rows are to be one by one in the TABLES_TO_SPLIT_BY_ROW variable.

Please note how the dumpfile name has a datetime appended to the table name so you can do some versioning based on the datetime. You can change that to be versioned any way you wish.

You did ask that each row be in its own file. I will leave that up to you to script.


If a database only ever has one insert, is it bad to index every possible column combination? I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is probably a better way to design a system like this, but let's approach this theoretically.

Theoretically speaking:

If we have a very large database (150M+ rows on several tables)
And we can assume the database will only be populated once.

Could indexing every possible column combination have a negative performance impact on a select query?

Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider.

Since you're on SQL Server 2017, loading once, and running reports, why not just use a clustered column store index instead?

That seems to be the ideal solution to your need to index every possible column combination.

Columnstore indexes - Overview


Partition Style - MBR or GPT I am setting up a new server and I am designing the server for performance over capacity.  Planning the disk partitioning style I would like to use GPT over MBR but am not sure which is best.  I am using SAN attached storage.

Which partition style, MBR or GPT, is better for a SQL Server 2008 R2 implementation?

MBR cannot handle partitions bigger that 2 TB, thus if your partition is bigger than 2 TB you have to use GPT. There is no performance gain as far as I know. It's all about the capacity!


Get the list of all Tables, Views, Stored procedures that are not added in a publication for replication How do I get the list of all Tables, Views, Stored procedures that are NOT added  in a publication for replication?

For Snapshot and Transactional:

SELECT
    name
FROM sysobjects WITH (NOLOCK)
WHERE ((xtype = 'U') OR (xtype = 'V') OR (xtype = 'P')) AND
    category <> 2 AND
    name NOT IN (SELECT DISTINCT object_name(OBJID) FROM sysarticles)

For Merge:

SELECT
    name
FROM sysobjects WITH (NOLOCK)
WHERE ((xtype = 'U') OR (xtype = 'V') OR (xtype = 'P')) AND
    category <> 2 AND
    name NOT IN (SELECT DISTINCT object_name(OBJID) FROM sysmergearticles)


SQL Server PerfMon: Compilations/sec > Batch Requests/sec I've been looking at various PerfMon metrics on one of our client's SQL Server instances trying to get a good gauge of where our database (or server) could use improvements.

One measure that has me puzzled is the ratio of two PerfMon metrics: Compilations/sec and Batch Requests/sec.

According to this post, "It's a general rule of thumb that Compilations/sec should be at 10% or less than total Batch Requests/sec".

Our application has a Windows Service that invokes scheduled calculations against the database every hour. Like clockwork, in my PerfMon CSV data, I can see the spikes in Compilations/sec and Batch Requests/sec. What I didn't expect to see was the number of Compilations/sec to exceed Batch Requests/sec.

I'm looking at 15 second samples from PerfMon centered around the time where our calculations kick off:

What does this typically indicate? Does this even make sense? Why would we be compiling more statements than executing? Am I missing something?

Sorry, not to disparage Thomas' advice, but please take "general rules" with a grain of salt, or just throw them out the window altogether.

Baseline.

What is normal for your system? Is the system currently responding ok?

If there is no performance issue, don't try to compare your system to some number someone plucked out of the air or potentially based off some very specific system and workload years ago, and drop everything to try to "fix" it.

Specifically, batch requests and compilations don't have a very nice and handy correlation in ALL scenarios. You need to understand your workload before you start panicking because your counters hit some threshold someone put in a post somewhere. If all of your batches consist of exactly one statement, then yes, having more compilations/sec than batch requests/sec might seem out of the ordinary (but still might not indicate a problem). In most cases, you are sending more than one statement in a batch. If this is the case - and particularly if you are using things like ORMs or a lot of highly variable dynamic SQL, where you will be suffering from a high number of compilations - I would really not be surprised to see one counter higher than the other.

Whether you need to do something about that, in that case, is a completely different problem.


Which full backup will be differential one based on? I wanted to perform a differential backup of a DB in SQL Server 2008.
I currently have a .bak file with a full backup and I want to add this new one in it.

Will be the differential backup based on the existing one contained in the .bak file? Or will it be based on another full backup?

Will be based off your most recent full backup, a differential backup is simply a backup of changed pages since your last full backup. Hope that helps.


Need help achieving HAProxy active/passive configuration for mysql I'm using HAProxy for mysql database backends. The database had to be master-slave. The goal is to point all mysql clients at the proxy and for all requests to go to mysql server #1 and only go to mysql server #2 if #1 is down.

There is an understanding and acceptance that when we are failed over to node 2, the database will be read only.

One thing I tried was setting the weight of node 1 to 256 and this kind of works but I don't want one out of 256 database calls going to node2; I need all calls to get directed to node1 unless node1 is actually down.

The answer is to use the check backup option to second backend node in the conf file like so:

listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysql-1 10.51.120.28:3306 check
    server mysql-2 10.51.123.25:3306 check backup


Nullable Fields to Separate Table? My website has a form page that has radio buttons yes|no corresponding to 7 questions. When the user selects yes, the user needs to specify a value (either a location, name, or medical condition, depending on the question).

However, I'm torn whether I should include the specified values in the same table as the answers.

One one hand, when I include the specified values into the same table as the answers, it is easier to query.

One the other hand, the specified values can have null values (when the user selects yes).

I went with the latter and made 7 tables for each of the specified values. Refer to this diagram:

After looking at the table, I feel it's a bit of an overkill just for a simple functionality.

I would like suggestions as to what route should I go.

Should I merge everything into one table?

--OR--

Should I stick with this diagram?

Rather than stick with that table, would creating a question table work for your application? That is, instead of having a separate table for each question, have a question table as

CREATE TABLE question (question_id numeric, question text)

and instead of the AFE table linking to a bunch of other tables, create a separate answers table as

CREATE TABLE answers(question_id numeric, afe_id numeric, answer text)

That acts as a bridge between AFE and question. This lets you add new questions easier too.


Correct way to re-use or recover databases from "PostgreSQL 9.3" to "PostgreSQL 9.5" I was using PostgreSQL 9.3, then I installed PostgreSQL 9.5, then I unistalled PostgreSQL 9.3.

Now I want to move the database from the folder of PostgreSQL/9.3/data/base of PostgresSQL 9.3 to PostgreSQL/9.5/data/base of PostgresSQL 9.5.

When I open PostgreSQL 9.5 from pg_admin I can't find the database.

I know copy past is not the correct way, so what is the correct way to move this databases from pg_9.3 folder to pg_9.5 so I can use them like any database.

When I unistall the PostgreSQL 9.3 I still find this folders bin, data, lib, data - Copie.rar.

I am using Windows as an OS.

Nice news, finally, I arrived to a Solution.

Just Install PostgreSQL 9.3 in the same folder, now I open pg_admin and all the databases are back.

Now I should to make a backup before I uninstall PostgreSQL again.

If someone have another idea, I can be happy to use it.


Do my relational algebra operations produce the desired results? I have these tables:

        Sailors

sid  sname   rating  age
---  ------  ------  ---
22   dustin  7       45
31   john    8       55
58   ben     10      35

         Boats

bid  bname      color
---  ---------  -----
101  interlake  blue
102  interlake  red
103  clipper    green
104  clipper    red

       Reserves

sid  bid  date
---  ---  ----------
22   104  8/10/2014
22   103  7/05/2014
58   103  8/11/2014
31   102  8/11/2014

I am trying to find sailors, sname and rating, who have reserved both a red and a green boat, and I need to write that in relational algebra notation.

I tried this:

Text version, for copying/referencing purposes:

R1 := Boats ⋈ Services
R2 := σcolor='Red'(R1)
R3 := σcolor='Green'(R1)
R4 := R2 ∩ R3
R5 := R4 ⋈ Sailors
R6 := πsname,rating(R5)

I was told it was wrong, but not given an explanation as to why. Can someone explain to me what the problem is?

This line was marked as wrong:

 R4 := R2 ∩ R3

This is indeed the error:

    R4 := R2 ∩ R3

If we try to get the intermediate results of your solution, we get:

R1:

bid  bname      color       sid  date
---  ---------  -----       ---  ----------
102  interlake  red         31   8/11/2014
103  clipper    green       22   7/05/2014
103  clipper    green       58   8/11/2014
104  clipper    red         22   8/10/2014

and then:

R2: (only the 'red')

bid  bname      color       sid  date
---  ---------  -----       ---  ----------
102  interlake  red         31   8/11/2014
104  clipper    red         22   8/10/2014

R3: (only the 'green')

bid  bname      color       sid  date
---  ---------  -----       ---  ----------
103  clipper    green       22   7/05/2014
103  clipper    green       58   8/11/2014

R4 will be an empty relation because nothing can be both red and green. Of course that is not what the exercise asks for.

R4:

bid  bname      color       sid  date
---  ---------  -----       ---  ----------

What you need to do is to get R2' and R3', the projections of R2 and R3 respectively, with only the attribute sid. Then take their intersection for R4:

    R1  := ...
    R2  := ...
    R3  := ...
    R2' := πsid(R2)
    R3' := πsid(R3)
    R4  := R2' ∩ R3'
    R5  := ...
    R6  := ...


Create integer id columns from existing string columns (integer coding?) I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this:

| ID_STRING_A | ID_STRING_B |
|   'AAAA'   |    'BBBB'    |
|   'BBBB'   |    'CCCC'    |
|   'AAAA'   |    'DDDD'    |

Now I want to compute an integer representation for the both-column-elements and store them into additional columns. The result should look like this:

| ID_STRING_A | ID_STRING_B | ID_INT_A | ID_INT_B |
|   'AAAA'    |   'BBBB'    |     1    |     2    |
|   'BBBB'    |   'CCCC'    |     2    |     3    |
|   'AAAA'    |   'DDDD'    |     1    |     4    |

My frist approach based on the answers is:

Unfortunately, my update part seems to be highly iniefficient although there are indices on ID_STRING_A/B. While the query itself is done in minutes, the update part seems not to end. Here's the code:

ALTER TABLE mytable ADD COLUMN ID_INT_B integer;
ALTER TABLE mytable ADD COLUMN ID_INT_A integer;

UPDATE mytable SET ID_INT_A = g.ID_INT_A , ID_INT_B = g.ID_INT_B FROM
(
    WITH T( n , s ) AS
    (
        SELECT ROW_NUMBER() OVER ( ORDER BY s ) , s
        FROM
        (
            SELECT ID_STRING_A FROM mytable
            UNION
            SELECT ID_STRING_B FROM mytable
        ) AS X( s )
    )
    SELECT m.ctid AS id_ , m.ID_STRING_A AS ID_STRING_A , m.ID_STRING_B AS ID_STRING_B , T1.n AS ID_INT_A , T2.n AS ID_INT_B FROM mytable AS m
    JOIN T AS T1 ON m.ID_STRING_A = T1.s
    JOIN T AS T2 ON m.ID_STRING_B = T2.s
) AS g
WHERE mytable.ctid = g.id_

I guess you can use the ASCII function:

SELECT ID_STRING_A,ID_STRING_B
     , ASCII(ID_INT_A) - 64 AS ID_INT_A
     , ASCII(ID_INT_B) - 64 AS ID_INT_B
FROM ...

Perhaps the intention's more clear using:

     , ASCII(ID_INT_A) - ASCII('A') + 1 AS ID_INT_A

EDIT, since the question where changed something like this is possible:

WITH T (n, s) as (
    SELECT row_number() over (order by s), s
    FROM (
        SELECT ID_STRING_A FROM mytable
        UNION
        SELECT ID_STRING_B FROM mytable
    ) as X (s)
)
SELECT m.ID_STRING_A, m.ID_STRING_B, T1.n, T2.n
FROM mytable as m
JOIN T as T1
    ON m.ID_STRING_A = T1.s
JOIN T as T2
    ON m.ID_STRING_B = T2.s

EDIT, updating table

I have a gut feeling that this can be done in a simpler way, but I cross joined the cte with itself and filtered with WHERE to update both columns at once:

ALTER TABLE mytable
    ADD ID_INT_A INT;

ALTER TABLE mytable
    ADD ID_INT_B INT;

WITH cte (n, s) as (
    SELECT row_number() over (order by s), s
    FROM (
        SELECT ID_STRING_A FROM mytable
        UNION
        SELECT ID_STRING_B FROM mytable
    ) as X (s)
), cte2 (n1,s1,n2,s2) as (
    SELECT c1.n, c1.s, c2.n, c2.s
    FROM cte c1
    CROSS JOIN cte c2
)
UPDATE mytable
    SET ID_INT_A = cte2.n1
      , ID_INT_B = cte2.n2
FROM cte2
WHERE mytable.ID_STRING_A = cte2.s1
  AND mytable.ID_STRING_B = cte2.s2
;

It should be noted that this is a 1-time operation. If you decide to add AABB later on, the enumeration will be wrong


100+ DBs in one instance or divide and conquer : MariaDb Ubuntu 64bits I have a desktop app (100+ customers and more to come) running at customer's small local server, all of them have same structure in tables (different info). I'm migrating that app to web ( almost finished ) and I need to store all those DBs into a new server : Xeon 4cores 4 ghz, 64 Gb ram, 1 Tb sss hd. What could be best approach to correctly handle the information : 1 instance for all DBs or several instances with few DBs ? each DB has 150 tables many with few 1000- records and just some with 100,000- records. Right now I have an instance with 13 DBs working pretty well, but not sure about 100+, any advice is appreciated.

The term describing this architecture is 'multi-tenancy'. There are many q&as on here on that topic already, so presumably something can be learned from those.

You'll have to consider e.g.:

All customers in the same schema or one schema for each customer?
How many queries/sec each customer generates and how much your server can handle. If each customer has its own DB user, then MariaDB (at least more recent versions) has features to limit how many queries/sec and simultaneous connections are allowed.
Problems related to volume of data:

Backup: Can you do it non-disruptively? mariabackup is pretty good. One backup for all customers or a separate backup for each?
Disaster recovery and full restore from backup, how long each takes, and whether that is acceptable to your customers.

How to do schema changes: Non-disruptively to users is the ideal. Newer MariaDB versions can do a lot of ALTER TABLE operations with ALGORITHM=NOCOPY|INPLACE|INSTANT.
Stability / high availability: If all users are on the same server, then all users will be affected if there's a problem with that server. Are you planning to do any kind of replication?
Security: Isolating customers in different databases or even different servers will help with security, but comes at higher maintenance costs.


Data normalization (from 0NF to 1NF) I am designing a relational database in mySQL - a library management system. I want to show examples of the data in 1NF, 2NF, 3NF, BCNF, etc. My example of 1NF looks something like this

"A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain."

I am unsure of if the data is in 1NF. For example is the attribute 'ISBN' atomic?

ISBN is a unique numeric commercial book identifier. An ISBN consists of 4 parts (if it is a 10 digit ISBN) or 5 parts (for a 13 digit ISBN). The different parts is prefix element, registration group, registrant, publication and check digit.
Would you divide the identification number into smaller units, to guarantee an atomic value?

Yes, it's atomic; your domain is the ISBN.  If you were storing the domains for ISBNPrefix, ISBNRegistrationGroup, ISBNRegistrant, ISBNPublication and ISBNCheckDigit, then you'd split it up.

Consider the North American phone number +1-234-567-8901 ext 234567.  Many systems choose to store such a number under two domains, such as PhoneNumber and PhoneNumberExtension, taking perhaps 2345678901 under the PhoneNumber domain and 234567 under the PhoneNumberExtension domain, ignoring the Phone Number Country Code altogether ( largely because it is always 1 ).  Since the domains are defined as such, it is not incorrect to store them in such a manner. An example of violating 1NF in this case would be to store multiple extension numbers in the same row for a singular entity existing in the PhoneNumber domain.

| PhoneNumber | PhoneNumberExtension |
  2345678901    234567, 890123

Instead, to preserve 1NF, the PhoneNumberExtension attributes are stored separately.

| PhoneNumber | PhoneNumberExtension |
  2345678901    234567
  2345678901    890123

A system with more granular domains may choose to store phone numbers by PhoneNumberCountryCode, PhoneNumberAreaCode, PhoneNumberPrefix, PhoneNumberLineNumber and PhoneNumberExtension

| CountryCode | AreaCode | Prefix | LineNumber | Extension |
  1             234        567      8901         234567

Since the domains are defined in such a way, it is again not incorrect to store the data as such.  The same logic applies to various "compound attributes," like your ISBN; geographical coordinate data and municipal mailing addresses are some common examples, but such "Domain grouping" happens even in basic data types - consider DATETIME, a raw datatype for an example - one part date, one part time.

The way you choose to define your domains will largely be a decision on how you intend to use them.  In the telephone example, there tends to be little value in breaking apart AreaCode since other more appropriate demographics are often available, such as City, though splitting the domain apart is not a "wrong" choice.  If there is little value for your purposes to split apart the ISBN, you are not violating 1NF by defining the domain on a less granular level - you just can't store two of them in the same row for the same entity.


SQL Server Profiler Exception tracing - Error procedure and line number omitted Is there a trace configuration or setting that will enable the profiler to output ERROR_PROCEDURE() and ERROR_LINE() information that is always available when writing manual error handling in CATCH blocks?

Doing a full trace of every statement being executed is very overwhelming on active systems (I got 45k entries trying it on my test system), and even then you have to guess where the error came from based on the preceding entries.

If you're able to use Extended Events, this is a lot easier. As you said:

  Doing a full trace of every statement being executed is very overwhelming on active systems

You can set up the XE session like this:

CREATE EVENT SESSION exceptions
    ON SERVER
    ADD EVENT sqlserver.error_reported
    ( ACTION ( sqlserver.sql_text, sqlserver.tsql_frame ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\exceptions' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 5 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

A simple proc to throw an error:

CREATE PROCEDURE dbo.error_out
AS
BEGIN
    SELECT CASE WHEN 1 = 1
                THEN 1 /0
                ELSE NULL
           END
END
GO

Start the session and run the procedure a couple few times:

ALTER EVENT SESSION exceptions ON SERVER STATE = START;
GO

EXEC dbo.error_out;
GO

Then check your XE session:

SELECT CONVERT(XML, x.event_data) AS x
INTO #xml
FROM   sys.fn_xe_file_target_read_file('c:\temp\exceptions*.xel', NULL, NULL, NULL) AS x;

SELECT  x.x.value('(event/@timestamp)[1]', 'DATETIME2(7)') AS event_date,
        x.x.value('(event/data[@name="message"]/value)[1]', 'VARCHAR(2044)') AS message,
        x.x.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(2044)') AS sql_text,
        ca.c.value('(@line)[1]', 'INT') AS line_number,
        ca.c.value('(@offsetStart)[1]', 'INT') AS offset_start,
        ca.c.value('(@offsetEnd)[1]', 'INT') AS offset_end
FROM #xml AS x
CROSS APPLY x.nodes('//frame') AS ca (c)

You should get results something like this:


ORA-00932 on subquery join from subquery join The only guess I have is that perhaps the size of the subquery is considered a CLOB?  I have no null values for the fields selected AFAIK.

SELECT DISTINCT
    kbq_ent.qid AS kbq_ent_qid,
    kbq_ent.category AS kbq_ent_category,
    kbq_ent.consequence AS kbq_ent_consequence,
    kbq_ent.diagnosis AS kbq_ent_diagnosis,
    kbq_ent.last_service_modify_datetime AS kbq_ent_last_servic_1,
    kbq_ent.patchable AS kbq_ent_patchable,
    kbq_ent.pci_flag AS kbq_ent_pci_flag,
    kbq_ent.published_datetime AS kbq_ent_published_d_2,
    kbq_ent.severity_level AS kbq_ent_severity_le_3,
    kbq_ent.solution AS kbq_ent_solution,
    kbq_ent.title AS kbq_ent_title,
    kbq_ent.vuln_type AS kbq_ent_vuln_type,
    kbq_ent.discovery_remote AS kbq_ent_discovery_r_4,
    kbq_ent.cvss_base AS kbq_ent_cvss_base,
    kbq_ent.cvss_temporal AS kbq_ent_cvss_tempor_5,
    kbq_ent.last_customization_user_login AS kbq_ent_last_custom_6,kbq_ent.last_customization_datetime AS kbq_ent_last_custom_7
FROM kbq_ent JOIN (SELECT vuln_eid AS vuln_eid, qid AS qid, anon_2.rel_eid AS anon_2_rel_eid
FROM (
        SELECT entity_vuln.entity_id AS vuln_eid,
            entity_vuln.qid AS qid
        FROM entity_vuln
    ) JOIN (
        SELECT entity_responsible_org.entity_id AS rel_eid
        FROM entity_responsible_org
        WHERE entity_responsible_org.org_id = :org_id_1
    ) anon_2 ON vuln_eid = anon_2.rel_eid) anon_1 ON kbq_ent.qid = anon_1.qid

kbq_ent.solution AS kbq_ent_solution is a CLOB.

LOB Rules and Restrictions

  ...

  You cannot specify LOB columns in the ORDER BY clause of a query, the
  GROUP BY clause of a query, or an aggregate function.
  You cannot specify a LOB column in a SELECT... DISTINCT or SELECT...
  UNIQUE statement or in a join. However, you can specify a LOB
  attribute of an object type column in a SELECT... DISTINCT statement,
  a query that uses the UNION, or a MINUS set operator if the object
  type of the column has a MAP or ORDER function defined on it.

  ...


SQL Server stored procedures fast in SQL but slow when called by ASP.NET I am using ASP.NET with MVC3 with a SQL server database.
From time to time, one of our stored procedures become incredibly slow when called by the web platform, but stay as fast when called directly in SSMS.

The solution to this problem is to write "SET ARITHABORT ON" at the beginning of the stored procedure, which magically works.

However, I want this to stop since I don't what is causing procedures to randomly start being incredibly slow on the web and causing timeouts.

Is there a way to resolve the problem definitely?

Thanks in advance,
Eric

Changing ARITHABORT inside the procedure causes it to recompile:

http://support.microsoft.com/kb/243586

So most likely you're dealing with parameter sniffing. If you don't figure other ways to fix this, adding "... option (recompile)" into the end of the statement you're having trouble should solve the issue. That of course increases CPU usage (slightly) because it has to do a new plan for that statement every time.


Granting permissions only on a set list of objects I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in it. All of that code has access to the tables in dbo.

The code in myschema will ultimately be called from a web service and I am struggling to get the permissions right for the user I created for the web service.

At first, I created the user with no roles except public and then gave it specific permissions on the securables in myschema. But then I could log on using that user and select from (and ever update) anything in dbo.

So I gave the user the denydatareader and denydatawriter roles, which effectively restricted the access to the objects in dbo.

The result of this is that I can execute the two stored procedures just fine.

But if I try to use the table-valued functions, I get this error:

  The SELECT permission was denied on the object 'MyFunction', database
  'MyDB', schema 'myschema'.

This is despite my use of:

grant select on myschema.MyFunction to MyUser

I'm guessing that's because of my brilliant use of denydatareader.

So what is the correct way to give a user access only to a list of specific stored procedures and table-valued functions and not to anything else?

Update:
Based on Nico's answer below, here's what I did. For the sake of brevity, just pretend you see the exists checks.

create table dbo.testtable(id int)
go
insert into dbo.testtable (id) values (1)
insert into dbo.testtable (id) values (2)
insert into dbo.testtable (id) values (3)
go
create schema myschema
go
create login mylogin with password = 'SomePassword', check_policy = off, default_database=[MyDatabase], default_language=[us_english];
create user myuser for login mylogin with default_schema = [myschema];
create role myschemarole;

execute sp_addrolemember N'myschemarole', N'myuser';

Then logged in with mylogin:

-- Error: The SELECT permission was denied...
select * from dbo.testtable

-- Error: The SELECT permission was denied...
update dbo.testtable set id = 4 where id = 2

-- Success: (3 row(s) affected)
update dbo.testtable set id = 4

-- Success: (1 row(s) affected)
insert into dbo.testtable (id) values (5)

As long as I don't have a where clause, I can insert and update to my heart's content. And on top of that, "use master" also works and the sys schema is also fair game.

Does this mean I still need denydatawriter?

I've done this a couple of weeks ago -
Created LOGIN, ROLE and USER. Added USER to ROLE and granted explicit permissions to the ROLE itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.

First I created the login:

IF NOT EXISTS
(
  SELECT
    "name"
  FROM
    "master"."dbo"."syslogins"
  WHERE
    "name" = 'your_login'
)
BEGIN
  CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF;
END

I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.

Followed by the user:

IF NOT EXISTS
(
  SELECT
    1
  FROM
    "sys"."database_principals"
  WHERE
    "name" = N'your_username'
)
BEGIN
  CREATE USER your_username FOR LOGIN your_login;
END

And the role third:

IF NOT EXISTS
(
  SELECT
    1
  FROM
    "sys"."database_principals"
  WHERE
    "name" = N'your_role_name'
)
BEGIN
  CREATE ROLE "your_role_name";
END

Finally I added the user to the created role:

EXECUTE sp_addrolemember N'your_role_name', N'your_username';

After that, permissions can be set for each type of object, eg

GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;

You don't have to add denydatareader or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT permission doesn't include the SELECT permission on the same object. ( at least in my case it didn't ;) )

If you have problems executing or selecting from your UDF, check if you have granted all permissions on the referenced objects in the UDF itself!

Update after Cobus' update in OP:

I've executed the queries you provided based on my initial answer, results following:

select * from dbo.testtable

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4 where id = 2

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
-- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

insert into dbo.testtable (id) values (5)

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?


SQL 2012 THROW in SP That needs to be run on SQL 2005 and 2012 Let me start of by saying that I know SQL 2005 doesn't support the THROW feature.

I need a SP to be run on SQL Server 2012 and 2005. I would like to use the following section of code to allow SQL server generate a different error depending on the version of SQL server. But I am getting an error when trying to create the SP on the SQL 2005 server. Is there any way to get around this error when installing the SP?

Code:

IF LEFT(@@VERSION, 25) LIKE ('%2012')
BEGIN
    ;THROW 50013, @FullErrorMessage, 1
END
ELSE IF LEFT(@@VERSION, 25) LIKE ('%2005')
BEGIN
    RAISERROR(@FullErrorMessage, 11, 1)
END

Error:

Msg 102, Level 15, State 1, Procedure SPNAME, Line xxxx
Incorrect syntax near 'THROW'.

Please use a dynamic statement:

IF LEFT(@@VERSION, 25) LIKE ('%2012')
BEGIN
    EXEC sp_executesql N';THROW 50013,  @FullMessage , 1','@FullMessage nvarchar(1000)',@FullMessage=@FullErrorMessage
END
ELSE IF LEFT(@@VERSION, 25) LIKE ('%2005')
BEGIN
    RAISERROR(@FullErrorMessage, 11, 1)
END


Is it possible to setup failover clustering between a 32 bit instance and a 64 bit instance? I am attempting to setup a couple of virtual machines in my lab for testing failover clustering on SQL Server 2008 and Windows Server 2008.

One of the hosts I have is not capable of running a 64 bit virtual machine, however the other host is.  Sure enough, I installed the 64 bit machine (host A) first without realizing host B cannot run a 64 bit VM.

Can I run failover clustering with one node on x64 and another node on x86?  Or do I have to re-install the OS on host A to be 32-bit?

Windows Server 2008 (no R2) did ship a 32-bit variant of the OS (last one MS shipped that way). So you can cluster it. You can install a 32-bit FCI on a 32-bit node. You cannot install a 32-bit FCI on a 64-bit node. So no, what you want is not possible. If you need a 32-bit FCI, install it on a 32-bit OS.


MERGE a subset of the target table I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning:

  It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.

but this is exactly what it appears I have to do to make my MERGE work.

The data I have is a standard many-to-many join table of items to categories (e.g. which items are included in which categories) like so:

CategoryId   ItemId
==========   ======
1            1
1            2
1            3
2            1
2            3
3            5
3            6
4            5

What I need to do is to effectively replace all rows in a specific category with a new list of items. My initial attempt to do this looks like this:

MERGE INTO CategoryItem AS TARGET
USING (
  SELECT ItemId FROM SomeExternalDataSource WHERE CategoryId = 2
) AS SOURCE
ON SOURCE.ItemId = TARGET.ItemId AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( CategoryId, ItemId )
    VALUES ( 2, ItemId )
WHEN NOT MATCHED BY SOURCE AND TARGET.CategoryId = 2 THEN
    DELETE ;

This appears to be working in my tests, but I am doing exactly what MSDN explicitly warns me not to do. This makes me concerned that I will run into unexpected problems later on, but I cannot see any other way to make my MERGE only affect rows with the specific field value (CategoryId = 2) and ignore rows from other categories.

Is there a "more correct" way to achieve this same result? And what are the "unexpected or incorrect results" that MSDN is warning me about?

The MERGE statement has a complex syntax and an even more complex implementation, but essentially the idea is to join two tables, filter down to rows that need to be changed (inserted, updated, or deleted), and then to perform the requested changes. Given the following sample data:

DECLARE @CategoryItem AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL,

    PRIMARY KEY (CategoryId, ItemId),
    UNIQUE (ItemId, CategoryId)
);

DECLARE @DataSource AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL

    PRIMARY KEY (CategoryId, ItemId)
);

INSERT @CategoryItem
    (CategoryId, ItemId)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 3),
    (3, 5),
    (3, 6),
    (4, 5);

INSERT @DataSource
    (CategoryId, ItemId)
VALUES
    (2, 2);

Target

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          2 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      3 ║
║          3 ║      5 ║
║          4 ║      5 ║
║          3 ║      6 ║
╚════════════╩════════╝

Source

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

The desired outcome is to replace data in the target with data from the source, but only for CategoryId = 2. Following the description of MERGE given above, we should write a query that joins the source and target on the keys only, and filter rows only in the WHEN clauses:

MERGE INTO @CategoryItem AS TARGET
USING @DataSource AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY SOURCE
    AND TARGET.CategoryId = 2
    THEN DELETE
WHEN NOT MATCHED BY TARGET
    AND SOURCE.CategoryId = 2
    THEN INSERT (CategoryId, ItemId)
        VALUES (CategoryId, ItemId)
OUTPUT
    $ACTION,
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

This gives the following results:

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
╚═════════╩════════════╩════════╝
╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      2 ║
║          3 ║      5 ║
║          3 ║      6 ║
║          4 ║      5 ║
╚════════════╩════════╝

The execution plan is:

Notice both tables are scanned fully. We might think this inefficient, because only rows where CategoryId = 2 will be affected in the target table. This is where the warnings in Books Online come in. One misguided attempt to optimize to touch only necessary rows in the target is:

MERGE INTO @CategoryItem AS TARGET
USING
(
    SELECT CategoryId, ItemId
    FROM @DataSource AS ds
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $ACTION,
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

The logic in the ON clause is applied as part of the join. In this case, the join is a full outer join (see this Books Online entry for why). Applying the check for category 2 on the target rows as part of an outer join ultimately results in rows with a different value being deleted (because they do not match the source):

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          1 ║      1 ║
║ DELETE  ║          1 ║      2 ║
║ DELETE  ║          1 ║      3 ║
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
║ DELETE  ║          3 ║      5 ║
║ DELETE  ║          3 ║      6 ║
║ DELETE  ║          4 ║      5 ║
╚═════════╩════════════╩════════╝

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

The root cause is the same reason predicates behave differently in an outer join ON clause than they do if specified in the WHERE clause. The MERGE syntax (and the join implementation depending on the clauses specified) just make it harder to see that this is so.

The guidance in Books Online (expanded in the Optimizing Performance entry) offers guidance that will ensure the correct semantic is expressed using MERGE syntax, without the user necessarily having to understand all the implementation details, or account for the ways in which the optimizer might legitimately rearrange things for execution efficiency reasons.

The documentation offers three potential ways to implement early filtering:

Specifying a filtering condition in the WHEN clause guarantees correct results, but may mean that more rows are read and processed from the source and target tables than is strictly necessary (as seen in the first example).

Updating through a view that contains the filtering condition also guarantees correct results (since changed rows must be accessible for update through the view) but this does require a dedicated view, and one that follows the odd conditions for updating views.

Using a common table expression carries similar risks to adding predicates to the ON clause, but for slightly different reasons. In many cases it will be safe, but it requires expert analysis of the execution plan to confirm this (and extensive practical testing). For example:

WITH TARGET AS
(
    SELECT *
    FROM @CategoryItem
    WHERE CategoryId = 2
)
MERGE INTO TARGET
USING
(
    SELECT CategoryId, ItemId
    FROM @DataSource
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $ACTION,
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

This produces correct results (not repeated) with a more optimal plan:

The plan only reads rows for category 2 from the target table. This might be an important performance consideration if the target table is large, but it is all too easy to get this wrong using MERGE syntax.

Sometimes, it is easier to write the MERGE as separate DML operations. This approach can even perform better than a single MERGE, a fact which often surprises people.

DELETE ci
FROM @CategoryItem AS ci
WHERE ci.CategoryId = 2
AND NOT EXISTS
(
    SELECT 1
    FROM @DataSource AS ds
    WHERE
        ds.ItemId = ci.ItemId
        AND ds.CategoryId = ci.CategoryId
);

INSERT @CategoryItem
SELECT
    ds.CategoryId,
    ds.ItemId
FROM @DataSource AS ds
WHERE
    ds.CategoryId = 2;


Fixing a corrupt Log File in small downtime window I am working with a client who has been receiving the following error from their Log backups of one of their SharePoint databases:

Backup detected log corruption in database FOOPORTAL_SP2010_Config. Context is FirstSector.
  LogFile: 2 'D:\MSSQL2K8\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FOOPORTAL_SP2010_Config_log.LDF'
  VLF SeqNo: x864 VLFBase: x172d0000 LogBlockOffset: x176b1800
  SectorStatus: 2 LogBlock.StartLsn.SeqNo: x771 LogBlock.StartLsn.Blk: x1f0c
  Size: x400 PrevSize: x200

I understand that this is indicative of problems with the disk HW and or IO subsystem, and we have been pursuing that with the Hosting Provider.  Specifically, this appears to be a "bad spot" on the/a disk, because it is consistently reported for the log file of this DB, but not other errors have been reported for the other 6 DB log backups over the last few week, nor for any of the 20+ other DB's full data backups.

My immediate issue is that the client has been running this way for several weeks, because this is a mandated 24x7 service and they haven't been able to give me a downtime service window to fix this problem until (suddenly) tonight, for 2 hours.  Unfortunately, DB corruption and this kind of Admin work is not my specialty in SQL Server, and I am not certain what the best/safest/most reliable way to proceed is.

My tentative plan right now is:

Take a full backup of the DB as soon as the downtime window starts (full backups have been working without error through out this)
Detach the old DB
Delete the data files for the space
Rename the Log file (still retaining it)
Restore a new copy of the DB from the fresh backup from step 1.

My concerns/anxiety over this are:

Should this work?  Or is htere a safer/more reliable approach?
I only have 2 hours, so is there a more time efficient approach?

Any other suggestions are welcome.

If your log file is corrupt, my concern is that a backup/restore would retain the corruption.  My approach (which would probably be faster to complete) would be:

Run a full backup.
Detach database.
Delete/rename the log file.
Attach the database and rebuild the log file.
Take a second full backup.

To attach the database and rebuild the log file, it's just additional syntax in the CREATE DATABASE statement:

CREATE DATABASE [foo]
ON (FILENAME=<<Path to data file>>)
FOR ATTACH_REBUILD_LOG;

This will force SQL Server to rebuild the log file when it attaches the database.

The risk in this is losing whatever transactions happen between the completion of the full backup and detaching the database, but it sounds like this will be a minimal risk.  If you can "freeze" Sharepoint at the beginning of the downtime window, this would help mitigate that risk.


Are SqlServer Sequences, guaranteed to return different values for concurrent transactions? In documentation it is told that:

  Duplicate validation only occurs once a record is fully populated. This can result in some cases where the same number is used for more than one record during creation, but then gets identified as a duplicate

I do NOT want to use that number in an insert query like this:

INSERT Test.Orders (OrderID, Name, Qty)
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;

I want to obtrain that number in my web application and use it to construct a ciphered text. and store the ciphered code in database. Hence, in my case, there is no duplicate checking while inserting.

The thing I need to know is that whether it is probable for two clients calling NEXT VALUE FOR function nearly at the same time, and get the same values? or not? (I need this not to happen)

Hence, in my case, there is no duplicate checking while inserting.

If you really do not intend to allow duplicate values, you should create a UNIQUE constraint on the column where the ciphered text is to be stored.  Your application will have to be coded to retry on inserts that fail with a unique constraint error.

  The thing I need to know is that whether it is probable for two clients calling NEXT VALUE FOR function nearly at the same time, and get the same values?

While it's unlikely, under normal circumstances, to get the same value on two separate calls to NEXT VALUE FOR from the same sequence, there are things that can cause it to happen:

the sequence was specified with the CYCLE option, and the maximum value has been reached (causing it to roll over)
the sequence was reset (ALTER SEQUENCE RESTART)
as documented, accessing the same sequence object multiple times in the same statement will produce the same value each time, so make sure not to do something like this expecting two different numbers:

SELECT
    NEXT VALUE FOR dbo.TestSequence,
    NEXT VALUE FOR dbo.TestSequence;

You may also be interested in reading Sequence Tables, by Paul White.


How do I optimize my transaction level running balances cartesian join? This is a continuation of an question found here:

Generate multiple running totals with GROUP BY day

This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).

All of this is being done on a PostgreSQL 11.5 DB.

I am trying to sort out ways in which I can optimize the giant cartesian join that seems necessary in the query below:

SELECT c.customer_id, d.the_day
 , sum(t.tsla_amount) OVER w AS tsla_running_amount
 , sum(t.goog_amount) OVER w AS goog_running_amount
FROM   (
   SELECT the_day::date
   FROM   generate_series(timestamp '2019-01-01'
                    , date_trunc('day', localtimestamp)
                    , interval '1 day') the_day
   ) d
CROSS  JOIN (SELECT DISTINCT customer_id FROM transactions) c  -- !
LEFT   JOIN (
SELECT customer_id
    , created_at::date AS the_day
    , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount
    , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount
FROM   transactions t
WHERE  created_at >= timestamp '2019-01-01'
GROUP  BY customer_id, created_at::date
) t USING (customer_id, the_day)
WINDOW w AS (PARTITION BY customer_id ORDER BY the_day)
ORDER  BY customer_id, the_day;

While this query works, the end goal that I am trying to achieve is that, instead of every single customer having an entry on each day, I want only the customers that have transacted up through that day to be included in the report, and then as soon as a "new" customer has a transaction, they are then included moving forward.

(Currently this query creates rows for each customer even on days in the past in which they never had a transaction yet, and defaults values to 0)

CUSTOMER DDL:

CREATE TABLE customers (
customer_id varchar(255) NOT NULL,
city varchar(255) NULL,
state varchar(255) NULL,
postal_code varchar(255) NULL,
inserted_at timestamp NOT NULL,
updated_at timestamp NOT NULL,
CONSTRAINT customers_pkey PRIMARY KEY (customer_id)
);

CREATE TRANSACTION DDL:

CREATE TABLE public.transactions (
transaction_id varchar(255) NOT NULL,
amount float8 NOT NULL,
stock_ticker varchar(255) NOT NULL,
transaction_type varchar(255) NOT NULL,
customer_id varchar NOT NULL,
inserted_at timestamp NOT NULL,
created_at timestamp NOT NULL,
CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)
);

Example:
Old customers are making stock transactions from 01-01-20 forward
New Customer makes their 1st stock transaction on 01-03-20 (purchase 2 goog)

The report generated in this case for these customers would have an entry for each day, for each customer showing a running balance of their stock transactions:

01-01-2020 - old_123_cust_id - 5 - tsla
01-01-2020 - old_234_cust_id - 10 - goog

01-02-2020 - old_123_cust_id - 5 - tsla
01-02-2020 - old_234_cust_id - 10 - goog

01-03-2020 - old_123_cust_id - 5 - tsla
01-03-2020 - old_234_cust_id - 10 - goog
01-03-2020 - NEW_567_cust_id - 2 - goog

01-04-2020 - old_123_cust_id - 5 - tsla
01-04-2020 - old_234_cust_id - 10 - goog
01-04-2020 - NEW_567_cust_id - 2 - goog

In this example, the NEW_567_cust_id, does not appear in the report, until their 1st transaction, and then moving forward, that customer continues to appear in the report, even if they do not make any additional transactions.

Additional Information:
Estimated transactions row count: 300,000
Estimated customers row count: 45,000
Earliest transaction: 01-01-2019
Range: 01-01-2019 -> Today() (When query runs)
The day of a customer's 1st transaction will never change

Happy to provide any additional information!

You can get the day of the first transaction for every customer dynamically. With proper indexing this is only half painful. There is ongoing work to include "index skip scan" in the next version of Postgres (13 as of writing) to make it less painful. See:

Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest row per user

But it would be simpler and cheaper to save that information. It would seem that the date of the first transaction per customer should not change later, like a birthday? (And you confirmed as much in an update.) Let's call it first_transaction_at to fit in with your current schema.

For customers that have yet to make their first transaction (first_transaction_at IS NULL) you can still check dynamically, or attempt to update every time before you run the query.

Or if you can't or won't change the table definition you might add a MATERIALIZED VIEW for just that purpose: to keep track of first_transaction_at per customer.

The updated table can look like:

CREATE TABLE customers (
   customer_id varchar(255) PRIMARY KEY,
   city varchar(255) NULL,
   state varchar(255) NULL,
   postal_code varchar(255) NULL,
   first_transaction_at timestamp,   -- can be NULL !
   inserted_at timestamp NOT NULL,
   updated_at timestamp NOT NULL
);

Then you can use a query like this:

SELECT c.customer_id, d.the_day
     , sum(t.tsla_amount) OVER w AS tsla_running_amount
     , sum(t.goog_amount) OVER w AS goog_running_amount
FROM   customers c  -- !
CROSS  JOIN LATERAL (
   SELECT the_day::date
   FROM   generate_series(first_transaction_at  -- !
                        , date_trunc('day', localtimestamp)
                        , interval '1 day') the_day
   ) d
LEFT   JOIN (
   SELECT customer_id
        , created_at::date AS the_day
        , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount
        , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount
   FROM   transactions t
   WHERE  created_at >= timestamp '2020-01-01'
   GROUP  BY customer_id, created_at::date
   ) t USING (customer_id, the_day)
WINDOW w AS (PARTITION BY customer_id ORDER BY the_day)
ORDER  BY customer_id, the_day;

db<>fiddle here

The point being that you don't use the complete range of days for every customer, but a customized range, generated in the LATERAL subquery.

The CROSS JOIN removes customers with first_transaction_at IS NULL from the query as no rows are produced in the subquery.

Aside 1: The data type varchar(255) typically is a misunderstanding in Postgres - and a suboptiomal idea for a PRIMARY KEY. See:

Refactor foreign key to fields

Aside 2: Consider timestamptz instead of timestamp. See:

Ignoring time zones altogether in Rails and PostgreSQL


Is there an execution difference between a JOIN condition and a WHERE condition? Is there a performance difference between these two example queries?

Query 1:

select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
where  b.tag = 'Y'

Query 2;

select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
   and b.tag = 'Y'

Notice the only difference is the placement of the supplemental condition; the first uses a WHERE clause and the second adds the condition to the ON clause.

When I run these queries on my Teradata system, the explain plans are identical and the JOIN step shows the additional condition in each case.  However, on this SO question regarding MySQL, one of the answers suggested that the second style is preferred because WHERE processing occurs after the joins are made.

Is there a general rule to follow when coding queries like this?  I'm guessing it must be platform dependent since it obviously makes no difference on my database, but perhaps that is just a feature of Teradata.  And if it is platform dependent, I'd like very much to get a few documentation references; I really don't know what to look for.

According to Chapter 9 (Parser and Optimizer), Page 172 of the Book Understanding MySQL Internals by Sasha Pachev

here is the breakdown the evaluation of a query as the following tasks:

Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
For each table, decide whether a table scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
Determine the order in which tables should be joined when more than one table is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused tables from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

On that same page, it says the following:

In MySQL optimizer terminology, every query is a set of joins. The term join is used here more broadly than in SQL commands. A query on only one table is a degenerate join. While we normally do not think of reading records from one table as a join, the same structures and algorithms used with conventional joins work perfectly to resolve the query with only one table.

EPILOGUE
Because of the keys present, the amount of data, and the expression of the query, MySQL Joins may sometimes do things for our own good (or to get back at us) and come up with results we did not expect and cannot quickly explain.
I wrote about this quirkiness before

Jan 23, 2013 : Problem with nested UPDATE queries
Feb 22, 2011 : Problem with MySQL subquery

because the MySQL Query Optimizer could make dismiss certain keys during the query's evaluation.
@Phil's comment help me see how to post this answer (+1 for @Phil's comment)
@ypercube's comment (+1 for this one too) is a compact version of my post because MySQL's Query Optimizer is primitive. Unfortunately, it has to be since it deals with outside storage engines.
CONCLUSION
As for your actual question, the MySQL Query Optimizer would determine the performance metrics of each query when it is done

counting rows
selecting keys
massaging intermittent results sets
Oh yeah, doing the actual JOIN

You would probably have to coerce the order of execution by rewriting (refactoring) the query
Here is the first Query you gave
select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
where  b.tag = 'Y';

Try rewriting it to evaluate the WHERE first
select count(*)
from   table1 a
join   (select key_col from table2 where tag='Y') b
on     b.key_col=a.key_col;

That would definitely alter the EXPLAIN plan. It could produce better or worse results.
I once answered a question in StackOverflow where I applied this technique. The EXPLAIN was horrendous but the performance was dynamite. It only worked because of having the correct indexes present and the use of LIMIT in a subquery.
As with stock prices, when it comes to Queries and trying to express them, restrictions apply, results may vary, and past performance is not indicative of future results.


Is there a way to have MySQL GROUP BY utilize trailing whitespace? I recently had an issue come up where I noticed that MySQL would ignore trailing whitespace when issuing a GROUP BY. Here's the table:

CREATE TABLE `myTable` (
 `id` int(10) unsigned NOT NULL,
 `foo` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And here's an example query I ran (not a production use, but shows what I'm trying to fix):

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY foo

Which would return a result set:

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 4 |

All well and good, except 'Bar' is length three, not four. So I went to examing the individual entries (No group by):

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 4 |
| Bar  |                 3 |

Now, there are five entries for Bar, four of which correctly report a length 3. But there's one that reports a length of four.

It turns out the one with length four has a space on the end: "Bar ". I would have expected GROUP BY to treat this as a completely different value and not lump it with the other "Bar"s.

Is there a way to tell MySQL to consider values different if they have trailing (or leading) whitespace?

(running mysql 5.1.55 64-bit on mac osx)

char values are space-padded when they are stored, so all trailing whitespace is assumed to be padding and removed when retrieving values.  varchar fields will retain trailing whitespace, but by default it is removed for comparisons.  (see The CHAR and VARCHAR types)

To retain the trailing whitespace for varchar values, use the binary operator.  Something like SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY BINARY foo should do what you want.


Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey' I dropped my UNIQUE KEY index 'myindex' on a table
and I wanted to change the UNIQUE KEY to something else,
so after dropping it I did:

alter table mytable add unique key anykey (add1, city, state);

I now get the following message on a key that does not exist
(it doesnt seem to matter what key I change 'anykey' to, I always
get the same error:

ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dumped the entire DB and reloaded it, but still cannot seem to set
a unique key on 'mytable'.

Anyone know what I should check or how to get around this issue?
I WANT a unique key on this table.

The tables important parts:

PRIMARY KEY (`id`)
ENGINE=MyISAM AUTO_INCREMENT=3433 DEFAULT CHARSET=latin1

Thank you in advance for any help!

EDIT:  here's show create table

| mytable | CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`name_aka` varchar(100) DEFAULT NULL,
`in_add1` varchar(50) NOT NULL,
`in_add2` varchar(50) NOT NULL,
`in_city` varchar(50) NOT NULL,
`in_state` varchar(2) NOT NULL,
`in_zip` varchar(5) NOT NULL,
`in_zip4` varchar(4) NOT NULL,
`ship_add1` varchar(50) NOT NULL,
`ship_add2` varchar(50) NOT NULL,
`ship_city` varchar(50) NOT NULL,
`ship_state` varchar(2) NOT NULL,
`ship_zip` varchar(5) NOT NULL,
`ship_zip4` varchar(4) NOT NULL,
`phone` varchar(14) NOT NULL,
`phoneExt` varchar(6) NOT NULL,
`phone2` varchar(14) NOT NULL,
`phone2Ext` varchar(6) NOT NULL,
`fac_fax` varchar(6) NOT NULL,
`gender` varchar(7) NOT NULL,
`newsletter_ok` enum('Y','N') DEFAULT 'Y',
`notes` varchar(300) NOT NULL,
`fac_type` varchar(14) NOT NULL,
`fac_contact_email` varchar(50) NOT NULL,
`ind_eng_bks_shipped` varchar(6) NOT NULL,
`ind_span_bks_shipped` varchar(6) NOT NULL,
`ind_eng_s_bks_shipped` varchar(6) NOT NULL,
`ind_span_s_bks_shipped` varchar(6) NOT NULL,
`indiv_other3_bks_shipped` varchar(6) DEFAULT '0',
`cases_eng_bks_shipped` varchar(6) DEFAULT '0',
`cases_span_bks_shipped` varchar(6) DEFAULT '0',
`cases_other1_bks_shipped` varchar(6) DEFAULT '0',
`cases_other2_bks_shipped` varchar(6) DEFAULT '0',
`cases_other3_bks_shipped` varchar(6) DEFAULT '0',
`last_update_by` varchar(20) NOT NULL,
`last_update_date` datetime NOT NULL,
`rev` varchar(9) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3435 DEFAULT CHARSET=latin1 |

I removed the ---- stuff obviously.

If you want to hunt down what key combinations are preventing the unique key from working, run this

SELECT add1, city, state,COUNT(1) rowcount
FROM mytable GROUP BY add1, city, state
HAVING COUNT(1) > 1;

To see the actual rows, run this

SELECT A.* FROM mytable A INNER JOIN
(SELECT add1, city, state,COUNT(1) rowcount
FROM mytable GROUP BY add1, city, state
HAVING COUNT(1) > 1) B USING (add1, city, state);

This will give you the opportunity to clean up the data

Alternatively, you may decide to make it a regular nonunique index.