Only one simultaneous connection per user? I'm currently experiencing some trouble, seemingly because my application refuses to connect to the database if there is already another connection open from the same user (but in a separate process).

Based on my observations, it doesn't seem to matter how many or few other users are connected, and @@MAX_CONNECTIONS is set to 0 (unlimited).

Is there any way such a connections-per-user limitation may have been defined on the database, and if so, how can I turn it off?

Here is my connection string:

[db]
sql=MS SQL
provider=SQLOLEDB.1
data source=foo\bar
connection=Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=foobar

As you might be able to discern from the above, we are connecting to the database using Microsoft OLE ADO. The application is an old VB6 legacy program.

By "refuses to connect" I mean that the method Open on the ADODB.Connection object fails.

Sadly, there is currently no log of the error output from this method in the production environment. I'm currently waiting for a patch to the application to be applied, to see if I can get extended information here.

Is there any way such a connections-per-user limitation may have been defined on the database, and if so, how can I turn it off?

You are using Microsoft SQL-Server. You can limit the Resources with the Resource Governor. Have a look at the Microsoft Documents. There you can see which options might be set and you should check. If there is a limitation, you may find it there.

Documentation: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor


Like with the results of a subquery (multiple values) What I'm trying to do is the following concept:

SELECT field1,field2,field3
FROM table1
where field3 like '%'+(SELECT distinct field from table2)+'%'

But this is not allowed because like does not accept multiple values. How can I rewrite this query so I can wildcard match a field with multiple result values from another table?

Rewrite it into a JOIN

SELECT t1.field1, t1.field2, t1.field3
FROM
    table1 t1
    JOIN
    (SELECT distinct field AS fieldx from table2) t2
    ON t1.field3 like CONCAT('%',fieldx,'%')


sql server max memory includes SSIS? I have installed 2 instances of sql server plus SSIS on the following server.

Note the amount of RAM is nearly 384 GB

And this is the max and min memory settings that I have applied to my 2 instances.
I assume that both instances will use equal amount of resources, if that would really be the case, 184,320 MB which is 180 GB be a good starting number to set up my memory?

How much memory would I allocate to SSIS?

(From the screenshot) I see an unusual configuration where you have  SQL Server's Min and Max memory set the same. Leave min memory as default. See my answer to SQL Server Min Server Memory.
To determine ideal memory, refer - How to determine ideal memory for instance? and SQL Server Maximum and Minimum memory configuration
We have SSIS running packages on our production servers and they hardly cause any memory issues. I just leave enough memory for OS, since DTExec.exe is an external process to sqlserver.exe and so its memory requirements wont be included as part of MAX Memory. Also, SSIS is designed to process large amounts of data row by row in memory with high speed.
If you feel that there are SSIS packages that does run for hours, then I would suggest you to monitor below SSIS Performance Counters:
Buffers in use
Flat buffers in use
Private buffers in use
Buffers spooled
Rows read
Rows written

and from Top 10 SQL Server Integration Services Best Practices - SQL CAT Team

Process / Private Bytes (DTEXEC.exe): The amount of memory currently in use by Integration Services. This memory cannot be shared with other processes.
Process / Working Set (DTEXEC.exe): The total amount of allocated memory by Integration Services.

Old but still relevant : Integration Services: Performance Tuning Techniques


Linked Server Configuration using remote user logins I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, obviously I won't be using that going forwards however I just wanted to make sure the procedure worked and SA having all privileges would cater for this.
Again for troubleshooting purposes, I initially tried SA using the 'be made using this security context' option which worked well but obviously is not secure as I want to reduce who has access to the linked server.
When I tried the 'not be made' option with a local and remote login I receive an error suggesting that no login mapping exists although the login worked when I used the 'be made using this security context' option. What am I doing wrong?

I think you need to check the Impersonate option or not use sa for testing—use another SQL Login instead for testing this functionality. I'm not 100% certain it's required but you may also need to consider ensuring the remote logins have a matching sid on both SQL Server instances as I wrote about in the User mapping to a standby database post if you do not use impersonation so something easy enough to test using sp_helplogins and CREATE Login if needed.

Why

I don't recall where I heard or read about it, or if it's even applicable in this case, but I thought the impersonate rights were inherited implicitly for logins with sysadmin and local db_owner rights.

  Impersonate

  Pass the username and password from the local login to the linked
  server. For SQL Server Authentication, a login with the exact same
  name and password must exist on the remote server. For Windows logins,
  the login must be a valid login on the linked server.

  To use impersonation, the configuration must meet the requirement for
  delegation.

  Not be made

  Specify that a connection will not be made for logins not defined in
  the list.

  Source

Impersonation

If you go with using impersonation, below are a couple syntax examples to use for domain user account logins and local SQL logins which connected to the master DB.

GRANT IMPERSONATE ON LOGIN::[domain\username1_to_be_impersonated] TO [domain\username2]
GRANT IMPERSONATE ON USER::[user1_to_be_impersonated] TO [user2]


Should master and slave have same mysql version? I have a master database running mysql 5.5.11, and I am planning to setup a new slave. I am planning to use the 5.5.30 as it is the latest version. For setting up new slave, I am copying files from master 5.5.11 version. Will there be any compatibility issues?

Your replication configuration 5.5.11 -> 5.5.30 is a supported configuration and there is no real justification for deploying a new slave using old software.

In fact, MySQL replication is supported across major releases as long as the two releases are consecutive, so 5.1.x -> 5.5.x is supported and 5.5.x -> 5.6.x is supported.

The only caveats are that the slave should be the same version as -- or newer than -- the master, and if you're doing circular replication, you should not have more than two versions of MySQL in the chain (and best practice suggests that they be identical in this case).

http://dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html

If you are copying the files from another machine of a different version, don't forget to run mysql_upgrade on the new slave.


Simplest way to use sub query.

SELECT emp_id,count(*) as count FROM ( SELECT *, count(p.id) FROM present p GROUP BY pre_date , emp_id ) d group by d.emp_id


Partitioning a large table has not improved performance, why? In SQL Server 2014 I have partitioned one of my large tables weekly and  defined a sliding window scenario to switch the oldest week's data to the archive DB and create a new partition for the next week.

This is the result:

This is for an AVL System (Vehicle Tracking). I have partitioned on PositionDate (datetime).
All our queries have PositionDate in the WHERE clause and in many cases we have VehicleId in the WHERE clause too. So I created two aligned indexes on VehicleId (int):

one on (PositionDate,VehicleId);
one on just (VehicleId).

But in every query that contains VehicleId in its WHERE clause neither of these two non-clustered indexes is used (according to the query plan).

I have a performance problem now.

I compared the query plans between partitioned and non-partitioned table for queries like below:

Select * from MyNonPart_Table Where PositionDate between '2016-05-01' AND '2016-06-01'

Select * from PartitinedTable Where PositionDate between '2016-05-01' AND '2016-06-01'

and awesomely I see that the first query costs 30% but the second, 70%.

I have one file group with two files for the partitioned table.

My questions:

Is the number of rows in each partition more than the optimal number of rows for partitioning? If I partition by day and hold last 60 days' data live, will that help me improve performance?
Are my non-clustered indexes well defined or I should remove them? We have PositionDate in the WHERE clause of all queries and VehicleId in many of them.
Am I misusing partitioning for this scenario? If I define good indexes on my non-partitioned table and move oldest data (more than 2 month old) to the archive table, will that work well for my case?

DDL for my indexes:

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_Primary] PRIMARY KEY CLUSTERED
(
    [PositionDate] ASC,
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-VehicleId] ON [dbo].[MyTable]
(
    [VehicleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [NCIX_VehicleId_PositionDate] ON [dbo].[MyTable]
(
    [VehicleId] ASC,
    [PositionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

This is an example, my queries are in SPs that receive datetime type parameters.

You generally followed a delusion - that partitioning will give you a significant performance boost for queries compared to a standard index.

This is not the case. There is little difference between filtering with an index and with a partition.

Partitions are not there to make your queries faster, but to allow fast DELETE - by swapping out a partition with an empty version of the table. This allows "truncate" style performance for deleting parts of a table - and that is significant. Brutally significant, if you ever experience the long time it may take to delete dozens of gigabytes of data.

There are also insert scenarios where a partition can help.

But for queries - no, the partition will not be better than proper indices. In fact, it will be slower - as there the work is more complex (management of which partitions to access).


Org chart CTE with exact index but plan shows expensive sort node I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it.  Names are changed, but it's the same idea.  I was thinking of adding a hierarchyid column to the mix; then I could index that and hopefully make some of the queries faster.
This query is modeled on the msdocs sample on hierarchy id.  Before I ran it, I put indexes on (employeeid), (managerid, include employeeid), and (officeid, managerid, employeeid) hoping to make it as fast as possible.  But the query took over 5 hours to run.
I looked at the plan, and the (officeid, managerid, employeeid) index is used for both parts of the cte.
What I don't understand is that the ROW_NUMBER() windowing function also appears to have added a SORT node, which ss says is taking 94% of the query time.  It's already using an index that is pre-ordered in the same way, so why is there a SORT node taking up 94% of the time?

Thanks
WITH paths(path, duns, extended_gu, extended_parent)
AS (  -- This section provides the value for the root of the hierarchy
    SELECT hierarchyid::GetRoot() AS path, employeeid, officeid, managerid
    FROM OrgChart where officeid = 0

    -- This section provides values for all nodes except the root
    UNION ALL
    SELECT CAST(p.path.ToString() + CAST(ROW_NUMBER() OVER (PARTITION BY RD.officeid, RD.managerid
    ORDER BY RD.officeid, RD.managerid) AS varchar(30)) + '/' AS hierarchyid),
    RD.employeeid, RD.managerid, Rd.officeid
    FROM OrgChart rd
    JOIN paths AS p ON rd.managerid = P.employeeid
)
UPDATE rd set tree=P.Path
FROM Paths P
INNER JOIN  OrgChart rd on rd.duns = P.Duns

EDIT: Schema, added in response to comment below.  I don't see a "Paste The Plan" as a feature in the editor; where is that?  Rather than null on the roots, those ids are 0
CREATE TABLE [dbo].[OrgChart](
    [employeeid] [int] NOT NULL,
    [managerid] [int] NOT NULL,
    [officeid] [int] NOT NULL,
    ...
    [tree] [hierarchyid] NULL
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [uixOrgChart_employeeids] ON [dbo].[OrgChart]
(
    [employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [uixOrgChart_officemgremployee] ON [dbo].[OrgChart]
(
    [officeid] ASC,
    [managerid] ASC,
    [employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [uixOrgChart_mgremp] ON [dbo].[OrgChart]
(
    [managerid] ASC
)
INCLUDE([employeeid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Actually Erik's comment above was the most probative.
Creating a better index cut the time from 5 hours to a little over 3.
The other thing that made an even bigger difference was not updating columns on the main table; rather, I directed the hierarchy ids off into a normalized table instead.  That cut the time from a little over 3 to a little over 1


How to take backups of MySQL databases, explicitly based on PyMySQL I am looking for a purely Python solution to take backups of every MySQL database on localhost. I already use PyMySQL to run queries on these databases. Any help is highly appreciated.

EDIT

After some search, I ended up with the following non-python-based solution.

import os

x =\
    "../../../mysql/bin/mysqldump\
    --host=localhost\
    --port=3306\
    --databases ****\
    --user=****\
    --password=****\
    --default-character-set=utf8\
    --add-drop-database\
    --add-drop-table\
    --add-locks\
    --complete-insert\
    --extended-insert\
    --lock-all-tables\
    --create-options\
    --disable-keys\
    --quick\
    --order-by-primary\
    --set-charset\
    --tz-utc\
    > dump/test.sql;\
    cd dump;\
    tar -zcf test.sql.tar.gz test.sql;\
    rm test.sql;"

os.system(x)

So far so good but I have some questions regarding the parameters as well as the execution.

Regarding my first concern, I just want to make sure that all of them are required and do not cause any conflicts when used together. Ultimately, I would like to make a very robust and consistent dump file, with millions of records, that creates tables, databases, and inserts data. The good thing is that making the database not-available for a while is not a problem for me. My only goal is to make a robust and consistent dump file.

Regarding my second concert, I would like to know if a) the os.system(x) python function is the recommended way to execute Unix commands through Python (as opposed to subprocess.Popen() that most people use), and b) how to get informed when one of these commands goes wrong and if so throw an exception.

Any ideas?

Google searching turns up a few good posts on Python script wrappers for mysqldump

Oct 08, 2014 : StackOverflow : How to copy a database with mysqldump and mysql in Python?
May 05, 2014 : GitHub Gist : A configurable python script that wraps around mysqldump with some additional configuration.
Dec 03, 2013 : TecAdmin.net : Python Script for MySQL Database Backup
Aug 30, 2010 : StackOverflow : Python subprocess, mysqldump and pipes

The first one looks like the easiest way. The code from it says

subprocess.Popen('mysqldump -h localhost -P 3306 -u -root mydb | mysql -h localhost -P 3306 -u root mydb2', shell=True)

Although I am no expert in Python, if you trust your Python skills and instincts, then ...

Give it a Try !!!


MySQL 8: Truncate table stuck in `checking permissions` state for minutes Sometimes but not always truncate table statements get stuck in checking permissions state for several minutes (up to 7-8 minutes).

I'm running MySQL 8.0.12 (for Linux on x86_64 (MySQL Community Server - GPL))
My tables are partitioned with around 365 partitions in each.

There are only 5 users (excluding root and mysql.). The 5 users are not tied to a specific host. Sometimes the same users is used from multiple hosts at same time.

Is this a known problem? What are the possible causes or solutions?

Any help is much appreciated!

Hence my advice not to have more than about 50 partitions in a table.  Or to abandon partitioning altogether.

What may be happening:  table_open_cache (or one of the other caches) may be too small, and, hence, thrashing.

To verify that theory, I need to check several things.  Please provide SHOW GLOBAL STATUS;, SHOW VARIABLES; and how much RAM you have.  More details.

To critique the partitioning, please provide SHOW CREATE TABLE for at least one of the tables with a lot of partitions.

And you are TRUNCATEing a table with a lot of partitions?

If possible, look in the filesystem and list some of the files for that table.


Is there a way to explicitely have a not-null column in a view If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table:

create table tq84_t (
  col_1   number,
  col_2   varchar2(15) not null
);

create view tq84_v as select col_1, col_2 from tq84_t;

desc tq84_v;

will print

Name            Null?    Type
--------------- -------- -----------------------------------------
COL_1                    NUMBER
COL_2           NOT NULL VARCHAR2(15)

Now, if the view's select statement contains a union all, it seems as though that characteristic is not inherited anymore:

drop view tq84_v;

create view tq84_v as
  select col_1, col_2 from tq84_t where col_1 < 10 union all
  select col_1, col_2 from tq84_t where col_2 > 20;

desc tq84_v;

prints:

Name            Null?    Type
--------------- -------- -----------------------------------------
COL_1                    NUMBER
COL_2                    VARCHAR2(15)

This is a bit unfortunate for my purposes. So, is there a way to explicitely state that a column be not null for a view in Oracle?

Note: I obviously used a simplified view defintion to demonstrate the problem, and I could re-write the view definition without using union all. Yet, my actual view defintion is much more complicated, requiring the union all.

From the documentation:

You can specify only unique, primary key, and foreign key constraints
on views. However, you can define the view using the WITH CHECK OPTION
clause, which is equivalent to specifying a check constraint for the
view.

So it seems that there is no way to hint to the RDBMS that the underlying data (after the UNION ALL inline view is created) is NOT NULL.
This all seems a bit silly, given the UNION/UNION ALL/INTERSECT/MINUS statements all require matching datatypes!


How to find out the account(s) under which the sql server is running? I have just created a proxy and I need to grant access to the proxy to the account that runs the sql server agent. It will probably be a domain account.

How can I find it using T-SQL?

In SQL Server 2005 and SQL Server 2008 there was no documented way. So undocumented command xp_regread was used to get the result

DECLARE @sn NVARCHAR(128);

EXEC master.dbo.xp_regread
    'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
    'ObjectName',
    @sn OUTPUT;

SELECT @sn;

Since SQL Server 2008R2 SP1 we have a documented way to get to this information:  sys.dm_server_services here

You can use below query to find the same:

SELECT  DSS.servicename,
        DSS.startup_type_desc,
        DSS.status_desc,
        DSS.last_startup_time,
        DSS.service_account,
        DSS.is_clustered,
        DSS.cluster_nodename,
        DSS.filename,
        DSS.startup_type,
        DSS.status,
        DSS.process_id
FROM    sys.dm_server_services AS DSS;


How do I recreate a deleted logfile on an Oracle database? For my database assignment we were looking into "database corruption".

I was asked to delete the second redo log file, which I have done with the command: rm log02a.rdo this was in the $HOME/ORADATA/u03 directory.

Now I start up my database using startup pfile=$PFILE nomount.  Then I mount it using the command alter database mount;

When I try to open it with alter database open; it gives me the error:

ORA-03113: end-of-file on communication channel
Process ID: 22125
Session ID: 25 Serial number: 1

I am assuming this is because the second redo log file is missing. There is still log01a.rdo, but of course the file I deleted is missing.

How can I go about recovering the delete filed so that I can open my database again?

I have looked into the database CREATE scripts, and it specified the log02a.rdo file to be size 10M and part of group 2.

If I do select group#, member from v$logfile; I get:

  1
/oradata/student_db/user06/ORADATA/u03/log01a.rdo

  2
/oradata/student_db/user06/ORADATA/u03/log02a.rdo

  3
/oradata/student_db/user06/ORADATA/u03/log03a.rdo

  4
/oradata/student_db/user06/ORADATA/u03/log04a.rdo

So the missing file was part of group 2. If I try to add the log02a.rdo file again I get "already part of the database".

If I drop group 2 and then add it again with these commands:

ALTER DATABASE
  ADD LOGFILE GROUP 2 ('$HOME/ORADATA/u03/log02a.rdo')
      SIZE 10M;

Nothing. Supposedly alters the database, but it still won't start up.

Any ideas what I can do to re-create this and be able to open my database again?

See the Oracle Database Backup and Recovery Users Guide.  In particular the section titled Recovering After Losing of All Members of an Online Redo Log Group.  Your course of action will depend on other factors you can read about in the documentation.


modify T-SQL to get results I am an amateur.

I am trying the following

select * from [dbo].[AccessFeeDetail]
where LastLoginDate >= '10/15/2012 00:00:00.000', jobname not like 'Uptest_%'

I am making a mistake on where clause. Can you correct me

"LastLoginDate" and "JobName" are Fields in the same table.

Thanks!

The comma , is not a valid logical operator. Try using AND or OR, depending on what you need, rows that match either one or both criteria (use OR) or only the rows that match both criteria (use AND).

You should also avoid writing dates with ambiguous formats, like the '10/15/2012 00:00:00.000'. Use either 2012-10-15T00:00:00.000 or '20121015 00:00:00.000' format. You can read Aaron Bertrand's blog for more details about dates and range queries: Bad habits to kick : mis-handling date / range queries. In this particular query and because the date has no time part, you can also use the shorter '20121015':

select *
from [dbo].[AccessFeeDetail]
where LastLoginDate >= '20121015'
      AND
      jobname not like 'Uptest_%' ;


Restore a Database into a Current Database and merge I want to know how, or if it is possible, to backup a database then should the user wish, restore that database at a later date and merge it with the one they are currently using.  Both Databases will be using the same tables and have the same fields, the difference is in the data.  Ideally I would like to do this through SQL and not have to get a third party application.  Any ideas or suggestions, I'm kind of in a bind here.

If you have Visual Studio Ultimate or Premium, you can use "Data Compare" feature in Data menu. It can compare data between two tables and have ability to directly update your database or generate update script.


Ola Hallengren Index Optimise Script not Rebuilding Indexes I have downloaded Ola Hallengren's script and deployed to master database.
I run it using the below...

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

I get this output in SSMS but the Indexes have not been rebuilt. Fragmentation is still very high. Am I missing something?

Date and time: 2015-03-01 14:07:24
Server: TestSvr
Version: 10.50.2500.0
Edition: Standard Edition (64-bit)
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000, @SortInTempdb = 'N',
@MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL,
@LOBCompaction = 'Y', @UpdateStatistics = NULL,
@OnlyModifiedStatistics = 'N', @StatisticsSample = NULL,
@StatisticsResample = 'N', @PartitionLevel = 'Y',
@MSShippedObjects = 'N',
@Indexes = NULL, @TimeLimit = NULL, @Delay = NULL,
@WaitAtLowPriorityMaxDuration = NULL,
@WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL,
@LogToTable = 'N', @Execute = 'Y'
Source: https://ola.hallengren.com

Date and time: 2015-03-01 14:07:24
Database: [TestData]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO.

I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation when you can actually prove it is causing a tangible performance problem for a specific index. "Fragmentation is high" is not a problem on its own.


How to set up login/guest and checkout/add to cart I want to create an online store database. I want to give users the ability to 'add to cart' and 'check out' without login.

My idea is that if the user logs in, then I can save the information in tables ex(bag, order), but if the user wants to check out as guest than I can create a guest_unique_id and set up bag that way. Guest bag will be mannually deleted after 10 days.

The only issue I see is that sometimes account_id will be empty. I guess I can check to see if guest_unique_id is NULL then user has an account, if not then it is a guest.
What you guys think about this structure, is there a better way to do this?

Why not have account_id always populated? When anyone who creates a shopping bag, they get an account_id auto assigned, but there's another field on the account table with a boolean is_guest flag set true by default. You could keep things like customer details in a separate table with a 1:1 map to the account table, for customers who do create accounts.

Your challenge then is that you might have repeat customers with different account_ids, but might be worth it depending on how likely customers will create accounts.


High memory usage due to RING_BUFFER I have a server that is churning up the CPU using 60% to 80% constantly. Using a tool from Brent Ozar's First Responder Kit, it shows RING_BUFFER_SCHEDULER_MONITOR is the culprit. What is this, and how can I track down what is doing this?

60%. Ring buffer details: <Record id="3592" type="RING_BUFFER_SCHEDULER_MONITOR" time="215993000"><SchedulerMonitorEvent><SystemHealth><ProcessUtilization>57</ProcessUtilization><SystemIdle>40</SystemIdle><UserModeTime>2748425618</UserModeTime><KernelModeTime>27144174</KernelModeTime><PageFaults>986</PageFaults><WorkingSetDelta>94208</WorkingSetDelta><MemoryUtilization>100</MemoryUtilization></SystemHealth></SchedulerMonitorEvent></Record>

Well, no, not exactly. My fault for not making the wording on that a little better.

You're using sp_BlitzFirst, and here's something similar to what you're seeing:

It's reporting CPU usage and where I found it in the DMVs - the ring buffer. The ring buffer isn't CAUSING the high CPU use.

To find queries doing high CPU use, use sp_BlitzCache @SortOrder = 'cpu'.

For more details, check out this post from Microsoft's Premier Field Engineering team on using ring buffers to track CPU usage inside SQL Server.


What may cause MyISAM index corruption? I've had an error message telling about index corruption on the .myi file

Reparing the table fixed the issue

This table is accessed with select only, so what is likely to corrupt indexes ?

CREATE TABLE `my_table` (
 `col1` mediumint(1) unsigned NOT NULL,
 `col2` mediumint(1) unsigned NOT NULL,
 `fk_brand_trans` smallint(1) unsigned NOT NULL,
 `ref_trans` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `col3` smallint(1) unsigned NOT NULL,
 `attribute` smallint(1) unsigned NOT NULL,
 `ref` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `pos` tinyint(1) NOT NULL,
 KEY `veh` (`col2`,`col1`),
 KEY `trans` (`ref_trans`,`fk_brand_trans`) USING BTREE,
 KEY `brand` (`fk_brand_trans`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC

50M rows, 1.8GB data, 1.4GB index

Since the MyISAM storage engine only caches indexes, reads from .MYI files are the norm. If the .MYI file of a given MyISAM table never changes, you have to look into external factors.
Just keep in mind that if the index blocks for a MyISAM table are not in the Key Cache, it must be read from disk, then loaded into the Key Cache.
EXTERNAL FACTOR #1 : Disk
If the disk blocks that hold the .MYI file become corrupt, there may be an error reading index blocks. Doing the REPAIR TABLE must have written the rebuilt .MYI elsewhere on disk.
EXTERNAL FACTOR #2 : Memory
If the memory chips become defective and parts of the MyISAM Key Cache is in use there, the .MYI file could possibly be considered corrupt when reading index blocks. Doing the REPAIR TABLE creates a new .MYI and is freshly read into the MyISAM Key Cache.
SUGGESTION
To reduce or eliminate the amount of reads from the .MYI file of a MyISAM table, you should look into setting up a dedicated Key Cache just for this table.
Since your table has a 1.4GB .MYI file, make a 1.5GB  dedicate key cache for it:
USE mydb
SET GLOBAL my_table_keycache.key_buffer_size = 1536 * 1024 * 1024 * 1024;
CACHE INDEX mydb.my_table INTO my_table_keycache;
LOAD INDEX INTO CACHE my_table_keycache;

I have suggested this before

Sep 14, 2012 : Can I partially invalidate the MySQL table cache?
Mar 20, 2012 : optimal table design mysql with primay key and varchar value
Jan 26, 2012 : Less RAM than Index_length MyISAM

Doing this will also let you know quickly if the external factors apply in the future.


What are the first necessary actions needed to help secure a database's schema & data? This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions.

It seems like there should be a good checklist out there for all to agree on that addresses the security aspect of database design & implementation.

I'm not asking about the details of storing salted/encrypted data such as passwords... I'm asking about the general guidelines one should follow before and while setting up a database so that it naturally inherits a reasonable degree of security & privacy from unwelcome company.

Can security/privacy be implemented on a schema level?  Or is the safety of the database dependent on the code used to manipulate it?

I am looking for some answers that are somewhere between setting up db user and table rights, creating stored procs, using views, etc.... and encrypting stored data.

Can table relations, field dependencies, data types, lock modes, indexes, keys, etc... be set up to actively help protect a database against attacks and/or theft?

General guidelines:

Server physical security: behind a locked door, with a very short list of people able to access it (as a point of interest, I myself can't access our server room without being escorted).
Backup security: Make sure your backups are encrypted (if you use physical media for backups, make sure they're stored with a similar level of security to the server).
'sa' login: Paranoia dictates that you disable/remove it, practicality says "that'll break everything" (particularly when it comes to disaster-recovery time). I generally leave it enabled, but with a long, randomly-generated password (either stored with KeePass or a similar solution, or a post-it note inside the server room*). Create another account with the server admin role and use that for your maintenance requirements.
Permissions - in production, no-one except the DBAs and the application logins should have access to the server.

The application logins should not have any rights on the base tables, only execute rights on stored procedures used to access those tables.
Alternatively, if you want to get fancy, group all the stored procedures into schemas then grant execute on the schemas to the application logins. (This may not apply to non-SQL Server/Sybase RDBMSs, not sure if they have schemas with the same level of functionality.)
Ideally, DBAs should have two logins, one with read access and one SA. Monitor the use of the SA login to make sure they're only elevating to it when needed. In practice, your DBAs are probably going to back themselves not to be stupid and consequently will use their SA roles for everything.

Check your stored procedures to make sure they're not vulnerable to SQL injection.
Audit your stored procedures regularly, particularly the ones that issue DML commands (insert, update, delete etc).

(* I'm aware this would probably fail any security audit going, but to my mind if we've let an intruder into the server room that knows to look in the third drawer down for the unlabelled 'sa' password post-it, then we're screwed anyway.)


Concatenate one-to-many field in single result? Say I have the following query:

SELECT *
FROM AppDetails, AppTags
WHERE AppDetails.AppID = '1'
  AND AppDetails.AppID = AppTags.AppID

Which gives the following results:

AppID    AppName        AppType    Tag
1        Application1   Utility    Test1
1        Application1   Utility    Test2
1        Application1   Utility    Test3

How can I modify the query to return something like this:

AppID    AppName        AppType    Tags
1        Application1   Utility    Test1,Test2,Test3

Unfortunately SQL Server doesn't have an single function to perform group concatenation but there are a few different ways that you can get the result.

You can implement FOR XML PATH and STUFF():

SELECT DISTINCT d.AppId,
  d.AppName,
  d.AppType,
  Tags = STUFF((SELECT ', ' + t.TagName
                FROM AppTags t
                where d.AppID = t.AppID
                FOR XML PATH (''))
                , 1, 1, '')
FROM AppDetails d
WHERE d.AppID = '1';

See SQL Fiddle with Demo.

Or you could use FOR XML PATH with CROSS APPLY:

SELECT DISTINCT d.AppId,
  d.AppName,
  d.AppType,
  tags = left(t.tags, len(t.tags)-1)
FROM AppDetails d
CROSS APPLY
(
  SELECT t.TagName + ', '
  FROM AppTags t
  WHERE d.AppID = t.AppID
  FOR XML PATH('')
) t (Tags)
WHERE d.AppID = '1';

See SQL Fiddle with Demo.

If you want to then query against the tags values, then you can use a CTE similar to:

;with cte as
(
  SELECT DISTINCT d.AppId,
    d.AppName,
    d.AppType,
    Tags = STUFF((SELECT ', ' + t.TagName
                  FROM AppTags t
                  where d.AppID = t.AppID
                  FOR XML PATH (''))
                  , 1, 1, '')
  FROM AppDetails d
  WHERE d.AppID = '1'
)
select *
from cte
where tags like '%test1%'

See Demo

If TagName includes certain XML characters (e.g. >, &) they will become entitized (> -> &gt;, & -> &amp;). To avoid this, change FOR XML PATH('') to FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)').


Databses design, parallel many-to-many relationships. Two tables, or extra fields? Say I have want to create a database for expenses.

I will have a table for holding Person details, and a table for holding Expense details.

Then there will be two many-to-many relationships between the Person and the Expenses tables. Person Can pay an expense, and a Person can use an expense.

What is considered better? Having one relationship table to hold both "paid for" / "used by" fields. Or two have two separate tables containing each of these fields?

Two parallel tables seems more correct, but I could also see it making the SQL more complex. Is there any compelling reason to use one design over the other?

If both fields are relevant in one record (i.e. "used by" and "paid for" can both have a person_id from the persons table), a single table is a better solution. If at least one of the two fields is always NULL, splitting the table would actually make the SQL less complex.
(Also, if an expense is either "paid for" OR "used by" but not both, you could instead use an expense_type field instead of 2 separate fields).

As a sidenote, I would recommend against using space in field names.


Handling Recovery of Disk Space in MySQL and MongoDB So I have worked with MySQL all my life (not as a DBAdmin, but a programmer), and I have some limited knowledge about maintaining MySQL databases.

Recently a portion of our database has been moved to MongoDB and we have outsourced MongoDB administration to another company. They informed us that with MongoDB, when data is deleted disk space is NOT recovered and that specific clean up tasks need to be done for this which require downtime.

What I wanted to ask here is that: They also said the same is applicable to MySQL (where if we delete data, disk space is not recovered and we need to run clean up scripts for that purpose). I was never aware about this regarding MySQL.

Can anyone tell me briefly how this works for MySQL or point me to any good resources that I can understand?

InnoDB
I have mentioned how to shrink InnoDB tables and the System Tablespace (ibdata1) in the past

Mar 25, 2012 : Why does InnoDB store all databases in one file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
Apr 15, 2013 : MySQL database drop insanely slow
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full

MyISAM
You could simply run for mydb.mytable
OPTIMIZE TABLE mydb.mytable;

which will shrunk a MyISAM table and recalculate the index statistics. To shrink all MyISAM tables, see my Jul 24, 2012 answer to MySQL: reducing ibdata file size for MyISAM tables
GIVE IT A TRY !!!


Replication with two side update but one side insert I have server A and server B. I want server A can see data and changes on specific article on server B . But server B just see its own data . And also I want to update server B's data on both servers and data remain consistent. I want to prohibit the server B to see server A data As simple word I want replication with one way insert on subscriber and two way update on publisher data on both servers. Is it possible ? If it is how? Thanks

If I understand correctly, you want an insert on A to propagate to B but an insert on B not to propagate to A. If a record that exists on both gets updated on either side, you want that update to propagate. If a record that only exists on B gets updated it stays only on B.

The simplest way to get bi-directional replication going is to use merge replication. It can handle conflicts automatically in is therefore a good choice. See my stairway series for an introduction to merge replication: http://www.sqlservercentral.com/stairway/72401/

The additional requirement of hiding inserts on one node is easy if you can find a common property of those records that all other records don't have. If you think of the typical sale data distribution problem, you have a main database with the complete data set. The PA sales person is only interested in PA data, but if someone updates PA data in the main database she wants to know about it.

Replication allows you to specify a horizontal filter that would include only 'PA' data in the table article. The article about the merge publisher in my series has a brief introduction to horizontal filters.

If you do not have such an indicator yet, you could add a surrogate indicator, something like Repl_Ind BIT column and default it to 0 on the publisher, but change the default to 1 on the subscriber. (Or always specifically set it to 1 on the subscriber.) That way you could use the horizontal filter again.


Convert SID string to varbinary(85) My ssrs server was moved to a different domain because of which the Users table need to be updated with new SID

current sid:

S-1-5-21-39949945-591092532-1226208319-1007

I want to convert this sid into varbinary(85) which would look something like this:

0x0105000000000005150000005AE2326D3D9AC66DD5CCABEE56040000

I had earlier found a link but unable to find it now.

Any tips?

You can use the builtin CONVERT function with SQL Server Management Studio.

select CONVERT(VARBINARY,'S-1-5-21-39949945-591092532-1226208319-1007') FROM TableName


Does a dual key or single key lookup perform better on a table with a lot of data? We have table where rows unique between 3 columns. GroupA, GroupB, ProductID.

GroupA & GroupB are used in combinations to Group sets of ProductIDs and will always be used to lookup sets of productIDs. Sometimes a ProductID will be used in in the query also.

I wasnt sure if there would be any performance gains from combining GroupA and GroupB into the same column so that the index is clustered.

   GroupA | GroupB | ProductID
   ----------------------------
   A1     | B1     | 1
   A1     | B1     | 99
   A1     | B2     | 1
   A1     | B2     | 99
   A2     | B1     | 1
   A2     | B1     | 99
   A2     | B2     | 1
   A2     | B2     | 99

Dual group method above OR Single group method below.

   GroupAB | ProductID
   --------------------
   A1-B1   | 1
   A1-B1   | 99
   A1-B2   | 1
   A1-B2   | 99
   A2-B1   | 1
   A2-B1   | 99
   A2-B2   | 1
   A2-B2   | 99

   GroupA | GroupB | GroupAB
   ----------------------------
   A1     | B1     | A1-B1
   A1     | B2     | A1-B2
   A2     | B1     | A2-B1
   A2     | B2     | A2-B2

I don't think it would really matter.  You can cluster on both without concatenating them:

CREATE CLUSTERED INDEX ixc_MyIndexName ON MyTable (GroupA, GroupB)

Which will accomplish the same thing but not require you to modify your data.


Proper way to store a value that could be multiple different types I have an Answers table and a Questions table.

The Answers table has a value, but depending on the question, this value could be a bit, nvarchar, or number (so far). The Question has a notion of what its intended answer value type should be.

It will be important to parse these Answer values at one point or another since the numbers, at least, will need to be compared.

For a little more context, the questions and potential answers (typically a data type allowed for a textbox type input) are supplied by some users in a survey of sorts. The answers are then supplied by other specified users.

A couple options I've considered are:

A. XML or string that gets parsed differently depending on the intended type (which is kept track of in the question)

B. Three separate tables that reference (or are referenced by) the Answer table and are joined to based on the intended type. In this case, I'm not sure of the best way to set up the constraints to ensure each question has only one answer, or if that should be left to the application.

C. Three separate columns on the Answer table that can be retrieved based on the intended type.

I'd be happy just to get some input on the pros and cons of these approaches, or alternate approaches I hadn't considered.

Based on what you have said I would use the following general schema:

CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
)
CREATE TABLE [dbo].[PollOption]
(
    [PollOptionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollQuestionId] INT NOT NULL,  -- Link to the question here because options aren't shared across questions
    [OptionText] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL  -- Remove this if you don't need to hide options

    CONSTRAINT [FK_PollOption_PollQuestionId_to_PollQuestion_PollQuestionId] FOREIGN KEY ([PollQuestionId]) REFERENCES [dbo].[PollQuestion]([PollQuestionId])
)
CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

You don't really care if the answer is a number, date, word, etc. because the data is an answer to a question not something you need to operate on directly.  Furthermore the data only has meaning in context to the question. As such an nvarchar is the most versatile human readable mechanism for storing the data.

The question and the potential answers would be gathered from the first user and inserted into the PollQuestion and PollOption tables.  The second user who answers the questions would select from a list of answers (true/false = list of 2).  You can also expand the PollQuestion table to include the creator's user id if appropriate in order to track the questions they create.

On your UI the answer the user selects can be tied to the PollOptionId value.  Together with the PollQuestionId you can verify that the answer is valid for the question quickly.  Their response if valid would be entered in the PollResponse table.

There are a couple potential problems depending on the details of your use case. If the first user wants to use a math question, and you don't want to offer multiple possible answers.  Another situation is if the options the initial user provides aren't the only options the second user can choose.  You could rework this schema as follows to support these additional use cases.

CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NULL,
    [PollQuestionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [AlternateResponse] NVARCHAR(50) NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

I would also probably add a check constraint to make sure that either an option is provided or an alternate response, but not both (option and alternate response), depending on your needs.

Edit: Communicating datatype for AlternateResponse.

In a perfect world we could use the concept of generics to handle various datatypes for the AlternateReponse.  Alas we don't live in a perfect world.  The best compromise I can think of is to specify what the AlternateResponse datatype should be in the PollQuestion table, and store the AlternateReponse in the database as an nvarchar. Below is the updated question schema, and the new datatype table:

CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [QuestionDataTypeId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
    -- Insert FK here for QuestionDataTypeId
)
CREATE TABLE [dbo].[QuestionDataType]
(
    [QuestionDataTypeId] INT NOT NULL PRIMARY KEY IDENTITY,
    [Description] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
)

You can list all available data types for question creators by selecting from this QuestionDataType table.  Your UI can reference the QuestionDataTypeId to select the proper format for the alternate response field.  You aren't limited to TSQL data types, so "Phone Number" can be a data type and you will get appropriate formatting/masking on the UI.  Also if required you can cast your data to the appropriate types via a simple case statement in order to do any kind of processing (select, validation, etc.) on the alternate answers.


What does the position of the ON clause actually mean? The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, not found in tutorials and I have not found any web resource that even mentions that this is possible.

Here is a script to play around with:

SELECT *
INTO #widgets1
FROM (VALUES (1), (2), (3)) x(WidgetID)

SELECT *
INTO #widgets2
FROM (VALUES (1, 'SomeValue1'), (2, 'SomeValue2'), (3, 'SomeValue3')) x(WidgetID, SomeValue)

SELECT *
INTO #widgetProperties
FROM (VALUES
    (1, 'a'), (1, 'b'),
    (2, 'a'), (2, 'b'))
x(WidgetID, PropertyName)

--q1
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 ON w2.WidgetID = w1.WidgetID
LEFT JOIN #widgetProperties wp ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
ORDER BY w1.WidgetID

--q2
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 --no ON clause here
JOIN #widgetProperties wp
 ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
 ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID

--q3
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN (
    #widgets2 w2 --no SELECT or FROM here
    JOIN #widgetProperties wp
    ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b')
ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID

q1 looks normal. q2 and q3 have these unusual positionings of the ON clause.

This script does not necessarily make much sense. It was hard for me to contrive a meaningful scenario.

So what do these unusual syntax patterns mean? How is this defined? I noticed that not all positions and orderings for the two ON clauses are allowed. What are the rules governing this?

Also is it ever a good idea to write queries like this?

It determines the logical tables involved in the join.

With a simple example

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2
         ON w2.WidgetID = w1.WidgetID
       JOIN #widgetProperties wp
         ON w2.WidgetID = wp.WidgetID
            AND wp.PropertyName = 'b'
ORDER  BY w1.WidgetID

#widgets1 is left outer joined to #widgets2 - the result of that forms a virtual table that is inner joined to #widgetProperties. The predicate w2.WidgetID = wp.WidgetID will mean that any null extended rows from the initial outer join are filtered out, effectively making all the joins inner joins.

This differs from q2...

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2 --no ON clause here
                 JOIN #widgetProperties wp
                   ON w2.WidgetID = wp.WidgetID
                      AND wp.PropertyName = 'b'
         ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

#widgets2 is inner joined onto #widgetProperties. The virtual table resulting from that join is then the right hand table in the Left Outer Join on #widgets1

The same result can be achieved by using a derived table or Common Table Expression...

WITH VT2
     AS (SELECT w2.WidgetID,
                w2.SomeValue,
                wp.PropertyName
         FROM   #widgets2 w2
                JOIN #widgetProperties wp
                  ON w2.WidgetID = wp.WidgetID
                     AND wp.PropertyName = 'b')
SELECT w1.WidgetID,
       VT2.SomeValue,
       VT2.PropertyName
FROM   #widgets1 w1
       LEFT JOIN VT2
         ON VT2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

... Or alternatively you could re-order the virtual tables and use a RIGHT JOIN instead.

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets2 w2
       INNER JOIN #widgetProperties wp
               ON w2.WidgetID = wp.WidgetID
                  AND wp.PropertyName = 'b'
       RIGHT JOIN #widgets1 w1
               ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

This is covered by Itzik Ben Gan here

  ... the JOIN conditions must follow a chiastic relationship to the table
  order. That is, if you specify tables T1, T2, T3, and T4 in that order
  and the JOIN conditions match T1 with T2, T2 with T3, and T3 with T4,
  you must specify the JOIN conditions in the order opposite to the
  table order, like this:

FROM   T1
       <join_type> T2 T2
                  <join_type> T3 T3
                             <join_type> T4
                               ON T4.key = T3.key
                    ON T3.key = T2.key
         ON T2.key = T1.key

  To look at this join technique in a different way,
  a given JOIN condition can refer only to the table names right above
  it or table names that earlier JOIN conditions already referred to and
  resolved.

but the article has a number of inaccuracies, see the follow up letter by Lubor Kollar as well.


How to disable ChunkManager logging for MongoDB? mongoconfig.log gets huge I'm using MongoDB 3.4 for saving other systems logs and mongoconfig.log is getting too much verbosity. It's a sharded clusted, if that helps.

I checked the default mongo log verbosity and is set at default (0). But I believe this is another setting not found under /etc/mongod.conf

2017-06-13T02:54:44.868-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210965 based on: (empty)
2017-06-13T02:54:44.915-0400 I SHARDING [Balancer] ChunkManager load took 46 ms and found version 2392|1||58ade4d8189e1651990a98d2
2017-06-13T02:54:44.920-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210966 based on: (empty)
2017-06-13T02:54:44.921-0400 I SHARDING [Balancer] ChunkManager load took 0 ms and found version 3|10||58adf0e5189e1651990d9af8
2017-06-13T02:54:44.921-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210967 based on: (empty)
2017-06-13T02:54:45.026-0400 I SHARDING [Balancer] ChunkManager load took 104 ms and found version 4385|1||58adf0ed189e1651990d9c89
2017-06-13T02:54:45.035-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210968 based on: (empty)
2017-06-13T02:54:45.127-0400 I SHARDING [Balancer] ChunkManager load took 92 ms and found version 4258|1||58adf0f4189e1651990d9dd4
2017-06-13T02:54:45.136-0400 I SHARDING [Balancer] ChunkManager loading chunks for collection_user sequenceNumber: 36210969 based on: (empty)
2017-06-13T02:54:45.162-0400 I SHARDING [Balancer] ChunkManager load took 26 ms and found version 1349|1||58adf0f9189e1651990d9f26

Getting log components information returns this:

db.getLogComponents()
{
    "verbosity" : 0,
    "accessControl" : {
        "verbosity" : -1
    },
    "command" : {
        "verbosity" : -1
    },
    "control" : {
        "verbosity" : -1
    },
    "executor" : {
        "verbosity" : -1
    },
    "geo" : {
        "verbosity" : -1
    },
    "index" : {
        "verbosity" : -1
    },
    "network" : {
        "verbosity" : -1,
        "asio" : {
            "verbosity" : -1
        },
        "bridge" : {
            "verbosity" : -1
        }
    },
    "query" : {
        "verbosity" : -1
    },
    "replication" : {
        "verbosity" : -1
    },
    "sharding" : {
        "verbosity" : -1
    },
    "storage" : {
        "verbosity" : -1,
        "journal" : {
            "verbosity" : -1
        }
    },
    "write" : {
        "verbosity" : -1
    },
    "ftdc" : {
        "verbosity" : -1
    },
    "tracking" : {
        "verbosity" : -1
    }
}

I have not tested this myself.

Reference:

https://docs.mongodb.com/manual/reference/configuration-options/#systemlog-options
https://docs.mongodb.com/manual/reference/program/mongos/
https://docs.mongodb.com/manual/reference/command/

You can do this by:

  systemLog.quiet Type: boolean

  Run mongos or mongod in a quiet mode that attempts to limit the amount
  of output.

systemLog.quiet is not recommended for production systems as it may make tracking problems during particular connections much more difficult.

  --quiet

  Runs mongos in a quiet mode that attempts to limit the amount of output.

  This option suppresses:

  output from database commands
  replication activity connection accepted
  events connection
  closed events

All Sharding Commands are listed under database commands which I assume will suppress the logging you are referring to.


SSIS Lookup Component Irregularities I'm seeing odd behaviour in a dataflow with a lookup operation. If I select 'full cache' mode, some portion of the lookups are failing and I have no explanation why. 'No Cache' works correctly, as does 'Partial Cache'. At this point, I cannot trust the 'Full Cache' in production if it fails mysteriously.

The expected result looks like this-

1        808    larry   curly   moe
2    808    larry   curly   moe
3    314    foo bar baz
4    314    foo bar baz
5    314    foo bar baz
6    314    foo bar baz

Full cache produces this instead-

1   808 larry   curly   moe
2   808 larry   curly   moe
3   314 foo bar baz
4   314 foo bar baz
5   314 null    null    null
6   314 null    null    null

The '314' example is an actual result. The first two records lookup correctly, the second two fail. If they were cache misses, they should fail for all four rows.

The failures are repeatable- the same items fail in the same sequence every run. The environment is an isolated test environment with three fixed databases that aren't changing between runs. Selecting Partial Cache is noticeably slower, implying that there's a significant number of cache misses. None of the tables are particularly large, a thousand rows or so.

What's going on? Should I just abandon the hope of ever using Full Cache?

I've seen this behavior as well. Mine was due to server memory, which I'll explain in the guidelines. This was pesky because it was intermittent.

With partial cache, the cache starts off empty then queries until it finds a match. If you have multiple matches, the first one wins. With full cache, if you have multiple matches, I'm not sure which would win. Probably the first in the cache order.

Partial cache has an option for miss cache, which will remember which records don't have matches and won't query them again. This will be a problem if you are inserting into the table that you are doing a lookup on. Also with full cache, if your source contains duplicates, the second won't get a match after the first is inserted which will be a problem if you want to suppress all but the first.

Here are a few guidelines that I try to follow when using lookups:

The lookup will never swap cache to disk. If it runs out of memory, the task will fail. If this is your issue, get more ram or try partial.
Use integers whenever possible. If the field is string but can be cast to int, do it. This remove case and white space issues all together
Trim strings. White space will give you fits.
If it is a string, upper case both sides before comparing (lookups are case sensitive and will fail if the cases don't match).
Null never equals Null. If the column is nullable, replace both sides with "UNKNOWN" if string and a irrational number like -999 if it is integer.
If the column is empty, replace both sides with the text "EMPTY"

These are a pain in the butt, but it's better than getting a call at 3am because a tier 1 customers ETL failed.


MariaDB Galera Cluster wsrep_cluster_address not working I have configured MariaDB galera cluster with three nodes on CentOS-6.5 64bits:

db1 - 192.168.1.111
db2 - 192.168.1.112
db3 - 192.168.1.113

My /etc/my.cnf.d/server.cnf file is configured as follows:

[galera]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.111,192.168.1.112,192.168.1.113"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.1.112'
wsrep_node_name='db2'
wsrep_sst_method=xtrabackup
wsrep_sst_auth=username:password

All three nodes have the same config (with changes for wsrep_node_address & wsrep_node_name on each appropriately). When i start with the first node and start MariaDB with either of the following command, it works fine and it shows that the cluster is running with one node up:

#/etc/init.d/mysql bootsrap
#/etc/init.d/mysql start --wsrep-new-cluster

But when i try to start MariaDB on the second node with /etc/init.d/mysql start, it fails to start. When i comment out wsrep_cluster_address=... from the config above, then MariaDB on the second node starts but the node is not not part of the cluster. I have done this config before and worked just fine. But when i shutdown the cluster for maintenance and tried to start it again, only the first node will start. Any suggestions to why this is happening?

You don't provide much information, so I am inferring it from what you say and matching it with a common Galera/Percona XtraDB Cluster/MariaDB Cluster misconception and mistake:

  "shutdown the cluster for maintenance and tried to start it again, only the first node will start"

When you stop the cluster fully (something that shouldn't happen under normal circumstances), you need to start the cluster in reverse order. This means: the last node to be down must be the first to start. Why? Because if you are making changes on all nodes, the last one to go down is the one that will get more ahead of the other regarding the transaction UUID. When you shutdown a node normally, the other nodes survive and continue receiving updates. If you now start and create what it is essentially a new cluster from scratch, galera cannot know that the first node that you start is not the "freshest", because you are bootstraping it.

So in short, always check that you do not bootstrap the cluster on a node that is not the last to get shutdown. If you do not know it, here it is a guide on how to do it (along with other useful information on restarting a Galera cluster and other gotchas).

Hope this helps.


SQL Server 2005 delete corrupt table We are having a database in which one table name dbo.ONOA is corrupt. We need to delete this table. Please let me is there any way to force delete the table?

Have you used DBCC CHECKTABLE to see what the problem is? Check the REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD options to potentially repair the table. Database must be set in single user mode.


REBUILD - Clustered Index, TABLE, or both? I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here.

I have a very large table that we had to add a column to.  The clustered index is pretty highly fragmented, and I want to do an ALTER INDEX REBUILD to clean it up.

I also normally do an ALTER TABLE REBUILD when I change the columns since this cleans up any pointers or splits from that operation.

Do I need to do both since we are talking about a the clustered index, which is essentially the table?

My suspicion is the ALTER INDEX REBUILD on the clustered will not update everything that the ALTER TABLE will, but I also am afraid that the ALTER TABLE won't clean up the index fragmentation.

If you rebuild the clustered index there shouldn't be any need to rebuild the table.  If the table was a heap (didn't have a clustered index) then you might want to rebuild it.


Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server? I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?

There is nothing built-in for Postgres.

But two very popular backup tools support differential backups:

barman
pgbackrest


Autogrowth by MB instead of Percentage After delving into Autogrowth and the best practices surrounding it (primarily being proactive and adjust the file group sizes ahead of time), there was one looming question that I did not seem to find a thorough answer to. Most articles, including TechNet, always mention the practice of setting the Autogrowth to 10-15% as a fail safe. There seems to be minimal mention of utilizing the MB growth option as a fail safe.

Say for instance you have a current database size of 500GB with a file growth of approximately ~1GB/day, give or take a couple hundred MB. Each weekend, you adjust the necessary file groups by X amount to cover the growth - say boosting it to 540GB to cover nearly a month's worth of growth. In the event that you fail to adjust it once it reaches 540GB (simply forgot, unexpected table expansion, etc.), MSSQL kicks into action and attempts to grow the database by 15% but ends up on a continual time out because the growth amount is too large for the server to handle automatically. This causes application timeouts, and thus manual intervention is needed regardless.

I bring this up because in testing, Autogrowth by percentage seems to offer for timeouts in these test cases. However, if I set the Autogrowth to a set MB, for instance 1,000MB, it grows without any issue and does so in a timely manner.

So to summarize my question, why do articles (at least the ones that I have read) heed the practice of utilizing Autogrowth by percentage instead of Autogrowth by MB in the event proactive manual file growth has not occurred? It seems to me in testing and in theory, you'd prefer Autogrow to adjust by a set MB amount.

Am I false in this thinking, or am I just reading the wrong things? It'd be great to hear what type of administrative techniques you guys utilize here.

Thanks!

I think you're reading the wrong things. The general consensus among SQL Server professionals is to use a fixed-size autogrowth in megabytes due to the reasons you've mentioned. I would ignore TechNet in this case.

Here is a relevant article on the topic, though it's targeted at answering one of the findings in sp_Blitz.


How to check if list of values in one column match list of values in another column (aggregations) Given two results consisting of single key|value pair (you can imagine those are in CTEs), I want to join and group them by key, aggregate their values and return two different things:

a) those keys where aggregated list of values in first resultset exactly matches aggregated list of values in second resultset

b) those keys where aggregated list of values in first resultset matches second resultset independent of order

Actually I know something very close to this is string_agg, but It seems I can use this in select only and it's inefficient anyway. Is there something more efficient for this?

# resultset 1
|key  | value |
|-----|-------|
| 1   | 1     |
| 1   | 2     |
| 3   | 4     |
| 2   | 5     |
| 2   | 7     |
| 1   | 3     |

# resultset 2
|key  | value |
|-----|-------|
| 1   | 1     |
| 1   | 2     |
| 1   | 3     |
| 2   | 7     |
| 2   | 5     |
| 4   | 6     |

Desired result

a) key 1

(1,2,3 = 1,2,3)

b) key 1 and key 2

(5,7 = 7,5)

For b) you can use intersection to find out what tuples that are in both resultsets, and then aggregate on top of that:

with rs1 (key, value) as ( values (1,1),(1,2),(1,3),(2,5),(2,7),(3,4))
   , rs2 (key, value) as ( values (1,1),(1,2),(1,3),(2,5),(2,7),(4,6))
select key, array_agg(value)
from (
    select key, value from rs1
    intersect
    select key, value from rs2
) t
group by key;

1   {3,2,1}
2   {7,5}

If you are dealing with bags instead of sets, you can use intersect all to preserve duplicates:

with rs1 (key, value) as ( values (1,1),(1,2),(1,1),(4,6))
   , rs2 (key, value) as ( values (1,1),(1,2),(1,1),(5,7))
select key, array_agg(value)
from (
    select key, value from rs1
    intersect all
    select key, value from rs2
) t
group by key;

1   {2,1,1}

a) does not really make sense, since there is no order to take into concideration. We can create one by adding an ordering number n for each key

with rs1 (key, n, value) as ( values (1,1,1),(1,2,2),(1,3,3),(2,1,5),(2,2,7),(3,1,4))
   , rs2 (key, n, value) as ( values (1,1,1),(1,3,2),(1,2,3),(2,2,5),(2,1,7),(4,1,6))
select key, array_agg(value)
from (
    select key, n, value from rs1
    intersect
    select key, n, value from rs2
) t
group by key;

1   {1}

Another possible interpreation is that n is a total ordering (i.e. not within each key). The same solution can be used to deal with that.


MySQL Error Code 3019 (Undo Error Log, no space left) I'm trying to remove some data to get some extra free space, but running the "DELETE FROM table ..." query gives me this error:

Error Code: 3019 Undo Log error: No more space left over in system
tablespace for allocating UNDO log pages. Please add new data file to
the tablespace or check if filesystem is full or enable auto-extension
for the tablespace

I found this:
https://stackoverflow.com/questions/42628687/undo-log-error-no-more-space-left-over-in-system-tablespace-for-allocating-undo
The recommend to run this query:
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

But it gives me an Error Code: 1064
MySQL version: 5.7.31

Is possible to TRUNCATE TABLE ? If yes, please try that.

Is MySQL server binary log enabled ? If YEs, Purge binarylogs & then delete records.

Is MySQL Server enabled with general log or Audit Log ? If Yes, Purge general log

Check error log size & path if needed purge it.

Note: Delete statement will not release space & if your server is INNODB_FILE_PER_TABLE=OFF then TRUNCATE or DROP statement will also not reclaim the space


Count Over returns Row_Number based on Order By Quick question based on some interesting syntax I've found out.
I have a table with these columns:

ID INT,
DimBuyDetails_Id INT,
WeekOfBuy INT,
Spots INT,
Retired BIT

If I run this query I will get the ROW_NUMBER() instead of the COUNT()

SELECT *
    , COUNT(ID) OVER (
        PARTITION BY DimBuyDetails_Id, WeekOfBuy
        ORDER BY ID --<
) AS ct
FROM Core.FactBuysPerWeek

However, if I change the ORDER BY clause I'll get the COUNT()

SELECT *
    , COUNT(ID) OVER (
        PARTITION BY DimBuyDetails_Id, WeekOfBuy
        ORDER BY DimBuyDetails_Id, WeekOfBuy --<Changed Order by here
) AS ct
FROM Core.FactBuysPerWeek

Anyone know what causes this?

If I run this query I will get the ROW_NUMBER() instead of the COUNT()

OVER Clause (Transact-SQL)

  If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification.


Any recommendation on my.cnf on aws.large instance? Any best recommendation variables for my.cnf on percona server on a high 256GB RAM and 32 CPU with 4000 IOPS?

How about below ones?

> innodb_buffer_pool_size=180G
> innodb_buffer_pool_instances=16
> innodb_flush_neighbors=0

any other recommendations on Mysql percona 5.6?

With that kind of AWS instance, you must be planning heavy writes.

I would add the following

innodb_log_buffer_size  = 32M
innodb_read_io_threads  = 16
innodb_write_io_threads = 16


Best approach to get last value inserted I have several devices that report via Edge network their position (and more data) to a UDP server which directly inserts this data in a SQL 2008 r2. I want to know which is the best approach to retrieve the last position from the db, concerning performance upon insert and lookup of data.

I thought of these ways:

1) Master table & Last_position table, I insert the data in the master which holds all the data in the package, and a trigger in this table that updates the row in last_position table of the corresponding ID of device.

2) same as 1 but no trigger, and I manually make an insert and an update from the UDP server.

3) no last_position table, only master table and an index that allows me to retrieve last inserted row with something like this:

select top 1 pos_x, pos_y from master where device_id = @id  order by date_inserted desc

Currently we have implemented the solution number 1, but I'm not a big fan of triggers, specially in tables with very high insert/seconds rate. This table holds approximately 50 million rows, which migrate periodically to a master_historic table in order to hold only a week old data (in the historic we hold 2 months).

Any other solution is welcome.

Thanks!

No, please don't store this data redundantly - you already have all of the information you need in the base table, without incurring the overhead of triggers or additional writes.

Just create an index that supports your query, somewhat similar to what @Colin suggested, but I'm adding the output columns to avoid key lookups:

CREATE INDEX ix_last_position
  ON dbo.[master] -- terrible table name btw
  (device_id, date_inserted_desc)
  INCLUDE(pos_x, pos_y);

Note that this assumes the query in your question is the only (or most frequent) query pattern against this data. If you have other queries that perform the same kind of searches but have different output columns or additional filters, a different index might be better.


"login failed for user " on SQL Azure from azure hosted web application I have an Azure ASP.net MVC web application which uses a SQL Azure db. It was working fine for some months (someday for some reason Azure changed the name of the server and I had to set that again but everything works fine), but now it does not work.

First I thought, they may have changed the server name again, but I opened my SSMS and after resetting the password it connected again. So I change that password in my application connection strings and test everything worked, and yes, debug and tests work fine. So I published, but once published the application throws error

Login failed for user 'my_username'

I check on this post Azure SQL Database "Login failed for user" in application, but works fine in SSMS which seemed similar but I can not do that without getting errors.

I changed my connection string from this

Server=tcp:SERVER_NAME.database.windows.net,1433;Database=my_db;User ID=my_username@qtdhcrrxmi;Password={my_password}; Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

to this

Server=tcp:SERVER_NAME.database.windows.net,1433;Initial Catalog=my_db;Persist Security Info=False;User ID={my_username};Password={my_password};Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

as it is shown in the Azure portal, in the database connection strings. I only removed the attribute

 Data Source=SERVER_NAME.database.windows.net;

I also checked that publish procedure does not change connection strings.

But it still works on debug and on SSMS but not on production.

The problem turned out to be in the connection strings. There are some things you need to consider.

First of all there is an option in the publish wizard in Visual Studio in the Settings tab called Use this connection string at runtime (update destination web.config), that for some reason, checked or not, does not seem to work for me. But this may be an option in the first place.

Another place were a connection string is set is in Azure management portal. In your web application settings, in connection strings, you can check which connection string is effectively used by your application. In my case, regardless of the publish wizard settings I used, this connection string was wrong. I just deleted and created it again using my database username and password and everything works again.


SQL Server Replication and Recovery Model In SQL Server for Replication when recovery model is not very important then how it saves the transactions and also keeps the secondary instance in sync?

I tried to check on MSDN and found the following:

  Replication functions properly using any of the recovery models:
  simple, bulk-logged, or full. Merge replication tracks change by
  storing information in metadata tables. Transactional replication
  tracks changes by marking the transaction log, but this marking
  process is not affected by the recovery model

but I didn't get the concept for SIMPLE Recovery Model. And if the T-logs gets copied before checkpoint then what if someone manually executes checkpoint?

Even in simple recovery, configuring a database for Transactional Replication prevents log reuse until the Log Reader has consumed the log records and marked them as read.


How do I use MySQL Workbench to edit an existing, in-production database? This is basically a repost of this question, which didn't give a satisfactory answer.

I'm finishing up the design of a database using an EER diagram. I now want to forward engineer and import the database into a working database. I will then use phpMyAdmin to start adding data and coding the site.

Now, while coding, what is the best way to make necessary changes to the database structure? Is the only way to export from phpmyadmin and import in MySQL Workbench? If so, how does it handle the existing rows when I add/remove columns? And, when it's time to forward engineer it again and import it via phpmyadmin, will all of my data still be there?

How do you handle this?

This is an arguable opinion, from limited personal experience.

I believe it was the Workbench who screwed up the process of round trip engineering several times. I don't remember all the detail, but it certainly synchronized more objects than had actually changed.  It silently deleted some data a few times.  And it sometimes generated SQL that the server rejected.  It was a development DB, no big deal, especially compared to the benefits.  But in production I'd rather refrain from being at such a tool's mercy.

Some other people's experience was similar, e.g., MySQL Workbench model/source synchronize issue

So for a production DB there may be risks, including that of losing your data. I would stick with phpmyadmin and do synchronization back into the model, always checking what the Workbench is going to do. Keeping the model just for reference.


Is it okay to put dots in SQL Server database names? If I use a database name with a dot in it in SQL Server (2005 or 2008), something like "MyApp.Sales", will this cause any problems?

You can, but I wouldn't. You would always have to wrap the DB name with square brackets such as [MyApp.Sales].

So to recap: if you value your sanity, don't do it.


Multi-Column Full Text Search Going Very Slow I have a full text index on a table articles for the columns content, title and keywords

When doing the search on each of the columns, like so select count(1) from articles where match(content,title,keywords) against ('cats' in boolean mode), the results take between 12 and 15 seconds.

But doing the columns individually (select count(1) from articles where match(content) against ('cats' in boolean mode)) typically takes less than 50ms.

Why does searching the 3 columns take more than 100 times longer than all of them separately?

This isn't a question of how to make it faster, but instead is more asking "why is it so slow?"

Table/Indexes

id           int(30)       PK       auto_increment
url          varchar(1024)
title        varchar(255)  FULLTEXT
content      text          FULLTEXT
keywords     varchar(1024) FULLTEXT
comments     text
created_date int(11)
posted_date  int(11)

Explains

This first one is the multi-column query:

This second is the new much faster query that runs the 3 columns separately then unions them (query cache was cleared).

Full Text Column Order

Use/Force Index with Explain

PROBLEM
From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column.
Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on.
SOLUTION
What you really need is a single FULLTEXT index with all 3 columns
ALTER TABLE articles ADD FULLTEXT content_title_keywords_ndx (content,title,keywords);

Only then can you say
match(content,title,keywords) against ('cats' in boolean mode)

I have suggested making compound FULLTEXT indexes before

Mar 16, 2012 : Speed up search across multiple columns
Oct 13, 2012 : Can underscore be forced as a word splitter without a full-text parser plugin?
All my posts about FULLTEXT indexing and searching

GIVE IT A TRY !!!


SQL Server Database "default" tables When I add a new database to our dev server (using SSMS), it automatically creates 5 tables.

These are copies of application tables that already exist in another database.

I assume this is some recent SQL Server feature I don't know about where tables can be marked as 'common' or some such, and so get added automatically to new databases.

But how can I stop it happening?

For the avoidance of confusion, this database is not scripted, it is created directly from the SSMS GUI (right-click Databases > New Database)

(moved from StackOverflow)

This happens (based on the discussion in comments) because the tables exist in the model system database. As per the documentation (my emphasis):

  If you modify the model database, all databases created afterward will
  inherit those changes. For example, you could set permissions or
  database options, or add objects such as tables, functions, or stored
  procedures. File properties of the model database are an exception,
  and are ignored except the initial size of the data file.

As a solution, dropping the tables from model will stop this phenomenon.


Mysql: Order by boolean value mixed into the results Imagine there is a job portal where people can post job offers

Some job offers are sponsored or premium offers that people pay for. These premium offers I want to display randomly in between search results.

Lets say a user searches for
programmer

so I do

select *
from jobs
where description like '%programmer%'
order by created_at

is there a way in mysql to add in random job records that have
is_premium = 1?

lets say, each 3 results, I want to ignore the search term, and add a random record with is_premium

To do so, the actual search results and the sponsored ads are sorted respectively.
Then, room for the sponsored ads needs to be made in the sequence of the actual search results.
Finally, they are put together, and displayed in defined order.

You asked for every third job offer to be a sponsored one:

WITH
SearchTerm AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY created_at) AS search_term_order
    , jobs.*
  FROM jobs
  WHERE description LIKE '%programmer%'
),
PremiumAdd AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS premium_order
    , jobs.*
  FROM jobs
  WHERE is_premium = 1
),
FinalOrder AS (
  SELECT
    id
    , description
    , created_at
    , is_premium
    , MOD(search_term_order + 1, 2) + FLOOR((search_term_order + 1) / 2) * 3 - 2 AS position
  --  , MOD(search_term_order + 2, 3) + FLOOR((search_term_order + 2) / 3) * 4 - 3 AS position
  FROM SearchTerm
  UNION ALL
  SELECT
    id
    , description
    , created_at
    , is_premium
    , premium_order * 3
  --  , premium_order * 4
  FROM PremiumAdd
  ORDER BY position
)
SELECT
  id, description, created_at, is_premium FROM FinalOrder
;

The comments show the adjustments for having an ad in every fourth row…

N.B.:

Ads holding the search term would be listed twice. This could be handled by adjusting the WHERE clauses.
This requires MySQL 8.

Please comment, if and as this requires adjustment / further detail.


MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW How to effectively add column id, name exception for trigger:

CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW()

so that date=NOW() will be affected on updating any column except id and name?

It's hard to give you an exact answer without knowing what RDBMS you are using or what your table schema looks like. But here is an example for SQL Server.

CREATE TRIGGER dbo.TR_MYTABLE_UPDATESTAMP
ON  dbo.[MYTABLE]
AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE I SET I.[MyTableUpdateDate] = GETDATE(), I.[MyTableUpdateBy] = SYSTEM_USER
    FROM INSERTED I
    INNER JOIN [MyTable] T ON T.MyTableId = I.MyTableId
    WHERE IsNull(T.[NumericColumnA], 0) <> IsNull(I.[NumericColumnA], 0)
    OR IsNull(T.[StringColumnB], '') <> IsNull(I.[StringColumnB], '')
    OR IsNull(T.[DateColumnC], '01-01-1950 00:00:00') <> IsNull(I.[DateColumnC], '01-01-1950 00:00:00')
END
GO


SQL Server - Using spatial data I'm using SQL Server 2008 R2 for real-time location aware service. The service mostly receives location data (lat/lon) from a user and returns locations around the user.

The data is being sent from each user once in a second. For every location being reported there is a query that checks other users around that user and returns data about them.

I'm using Lat/Lon as float fields and run a scalar-function to find the closest users.

I guess that approach is not the best. I know there are spatial data constructs in SQL Server, but don't know if they are good. Should I use them? If so, what is the best approach?

What approach should I take to make this operation as fast and scalable as possible?

Maybe NoSQL is better for spatial data?

P.S - I'm talking about thousands and tens of thousands users at any given time.

Yes you should use spatial indexes, but you should also consider there are limitations that simple sargability concerns can resolve.

A spatial index is good for finding locations in a particular area. It's not good for working out which distance is shortest.

So... when trying to find the closest item, start with a small distance and find items in that area. Something like:

select id
from dbo.locations
where location.STIntersects(@point.STBuffer(@somedistance)) = 1;

...and slowly increase the value of @somedistance until you find what you're looking for. This will allow Seeks to be done, quickly eliminating locations that are not of interest.


How can I get SSMS to persist my custom keyboard shortcut? I'm on Windows 10, and SQL Server 2012 Developer, with SSMSBoost installed.

I want to assign Ctrl + K, Ctrl + F to do SSMSBoost's Format command.  I do it through the normal Tools → Options → Environment → Keyboard → Keyboard method, and the shortcut works.  But when I quit SSMS and reopen it, the shortcut setting is gone.  Doing Exports of the settings in each state and diffing the two files also reflects this difference.

How do I get it to remember the shortcut between runs?

EDIT:

No added keyboard shortcuts stick at all, for any command.  Oddly enough, deleted ones do stick.

On further investigation, this seems to be a bug in SSMSBoost, at least on the current version (3.0.6397).  Uninstalling it makes the keyboard shortcuts work normally.  :(


Only one NULL for one part of primary key I have two date columns : "start date" and "finish date".

I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one primary key (composite in my case).
Is there a way to do that?

To illustrate, there should be only one NULL for one part of the primary key.
Here are the columns:

PeriodID | PersonID | StartDate | FinishDate
       1          1      1/1/10      10/1/10
       2          1     10/1/10         NULL
       3          1    somedate   MUST NOT BE NULL

And yes, "PersonID" is a foreign key.

I'm using SQL Server 2008.

EDIT :

I got a working answer,but other ways are wellcome too.

If I understand your question properly, SQLServer's partial index can do the job :

-- only 1 FinishDate nullable
CREATE UNIQUE INDEX IDXU_TAB1_1NULL ON TableName(FinishDate) WHERE FinishDate IS NULL;
-- or only 1 FinishDate is nullable for the same PersonId
CREATE UNIQUE INDEX IDXU_TAB2_1NULL ON TableName(PersonId, FinishDate)
WHERE FinishDate IS NULL;

Just to repeat what's already said :
  1. any column which is part of PK cannot be nullable
  2. as  Chris Travers mentioned in his comment to another answer, SQLServer treats NULLs in a different ways than other RMDS when it comes to unique constraints


MySQL Regex matching newline or end of string I have a text column in mysql which contains some values separated by a newline. I want now to query by these values and thought a regex should help me with that by doing something like:

SELECT * FROM table WHERE column REGEXP 'myValue';

That works so far except I also have that string also as part of a longer value like myValueLonger, then it would match both, so I need to add the newline as a separator. So I tried

SELECT * FROM table WHERE column REGEXP 'myValue[\n]';

But that returns zero results, it looks like multiline is somehow not supported in MySQL regex?

That is where I'm already hanging right now, at the end I would also need to add a way to allow newline or the total end of string, as I don't want to end empty newlines at the end and beginning of my text field.

Does anyone know how this can be accomplished in MySQL?

Edit:
Found the problem with the multiline stuff, I had \r\n breaks, so that works:

SELECT * FROM table WHERE column REGEXP 'myValue\r\n';

But still struggling with the issue how to do that OR the end of string.

Found the solution myself:

SELECT * FROM table WHERE column REGEXP 'myValue(\r\n.*)?$'


How do I open a Progress .bup file? I have a .bup file which is supposed to be a Progress database file.
My question is, how can I open/restore it and look at the data?

You need to have Progress installed. In it you will find a command: prorest run from command line that will help you restore a previously backed up database. At least if it has been backed up using the built in back up command (probkup).

To make things easier for yourself you could start the Progress command line environment (it should be in the bin directory in your installation path). Simply run proenv to start that and then follow the steps below. If on Windows you will most likely find proenv in your Start menu.

Basic syntax:

prorest [new database] [archive]

so for example

prorest c:\temp\mynewdatabase file.bup

After that you can connect to the database using Progress built in commands. You might find more help at:
Progress KnowledgeBase
and
Progress communities.

A specific description of the prorest command can be found here: Progress KnowledgeBase Article 99033


Update first, if not then insert (mysql) I want to do something like insert on duplicate key update but reverse i.e. first it should try to update, and if it doesn't exist, then it should insert.

Why? Because I am running a large number of these queries and most of them are actually update queries and not insert queries. So if I use insert on duplicate key update it takes longer because it's actually 2 queries. So if I can somehow do the reverse it will be ideal.

Any suggestions? If it's not possible, then how do I make this more efficient. Thoughts?

This is possible with MySQL command line "Load data file". But it no SQL query.

13.2.6 LOAD DATA INFILE Syntax
https://dev.mysql.com/doc/refman/5.7/en/load-data.html


Move Primary Key to Filegroup (SQL Server 2012) How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient:

Drop non-clustered indexed (requires them to be resorted and rebuilt)
Drop clustered index (requires the whole table to be resorted)
Create new primary key constraint (huge sort operation)
Create all non-clustered indexes (sorting and writing required)

Is there a more efficient way? This is horribly inefficient and will take a long time as the table is 50GB in size on a weak server.

Isn't there a way to skip all of these and just do a rebuild on a new filegroup? That would not require any sorting of data.

CREATE UNIQUE CLUSTERED INDEX Your_PK_Name
    ON YourTable(YourColumnList)
WITH (DROP_EXISTING =  ON )
ON [YourOtherFileGroup]

This preserves the logical PK property despite it not being mentioned in the syntax.


What is the recommended method of storing statements used in timer jobs? I've have a function that pulls data from several tables, arranges it nicely and generates an email summary which is to be sent out on a weekly basis to interested parties.
I decided a function was the right way to go for this since I needed to pass in parameters (which ruled out a view) but I didn't need to update the data (which ruled out a stored procedure) but I'm uncertain on how to store the statements that are going to do a few calculations and then call this function.
I believe I have two options:

Store it directly in the command as the first (and only) step of the job:

or

Store it in a Stored Procedure and then call that from the command:

Of the two I prefer the second method, it looks neater and keeps the logic of the job out of the call to the job. However, there is no need to call this process from anywhere else so it doesn't feel right to keep it in a stored procedure.
Is there a documented or recommended way of organising such statements? Am I following it with either of the examples above? If not, what should I be doing?

I think any answer to this question will be 'mostly' opinion based.

When I think of 'best practices' and comparing options, I think:

Is one way more secure?
Is one way easier to maintain?
Is one way adhering to company policy better?
Is one way more efficient or perform better?

Sql Server Agent Job information (and encapsulated code) is stored in the MSDB system database.

It's often desirable to emulate a lot of processes in other 'non-production' environments and people frequently back up their production user database(s) and restore them to 'test' environments.  Storing your code in a user database (as a SP or Function) allows for that functionality to more easily be restored to a non-production environments during a 'refresh'.  Storing this functionality in a Sql Agent Job limits that flexibility because you either have to manually edit the non-production job or script out the production job and recreate the job in the non-production environment.  This can lead to production and non-production code not matching because someone didn't script the changes in the agent job.

Ultimately, you have to decide what works best for your company


Can't connect to MySQL database over SSH with Workbench I'm trying to connect to my database via SSH tunneling from one of our web app servers with MySQL Workbench. Here's the basic config; note that I changed some values in the screenshot for security reasons.

The problem is every time I try to connect over an SSH tunnel from one of our app servers, I get the following error:

  Failed to connect to us-east-1.amazonaws.com through SSH tunnel at computer.amazonaws.com with user social_shop_prod. Can't connect to MySQL server on 127.0.0.1.

However, if I use the same credentials over SSH via the following command line:

mysql -u social_shop_prod -h us-east-1.amazonaws.com -p

I can connect successfully and get the MySQL interactive command prompt.

Been talking with the rest of my development team here and none of us can figure out why I can't tunnel over SSH from our app servers with Workbench; but when I SSH to one of our app servers and connect to MySQL via the command line; I can connect successfully.

And why is it trying to connect over 127.0.0.1? I didn't specify that in the configuration; nor is my hosts file redirecting the domains shown below to that IP.

Any constructive input is greatly appreciated.

Since you are connecting through a SSH tunnel, this means that the MySQL port  3306 from us-east-1.amazonaws.com is being opened locally on your computer. The ip address of your computer is 127.0.0.1 or localhost.   When you connect to the mysql server on us-east-1.amazonaws.com, you're actually accessing it via 127.0.0.1, i.e. your computer.  If you had another tunnel open, or MySQL running locally on your computer, then it may be that other MySQL server that's rejecting your authentication attempts

There are some tests you can try :

1. What ports is you Windows computer listening on

From a command prompt : netstat -a (lists all the ports that are open)

In linux it would be : netstat -tlpn

2. Basic connectivity test

From a DOS command prompt or linux console : telnet 127.0.0.1 3306

If you get a time out, or some other program responds then your tunnel isn't setup correctly.

3. Change the port number that MySQL Workbench is opening locally

We're assuming that MySQL workbench is creating the tunnel on your computer.  If so, in MySQL workbench, try tunneling through another port number like 9000.

Make sure that 9000 wasn't listed as an open port from : netstat -a

  If you have ssh access to us-east-1.amazonaws.com

4. Try connecting to MySQL from us-east-1.amazonaws.com

mysql -u myuser -h 127.0.0.1 -p

And as Rolando said, you'll want to verify that you are connecting with the right credentials.  For example, if you are connecting as myuser@127.0.0.1 and you have a user myuser without a host, you probably won't be able to connect using user@127.0.0.1.