Finding available schedules for multiple slots I was asked to come up with a database schema to keep track of the slots and their schedules and query the table to find out the available time(empty schedule) for the slots.

I created three tables 1) slots which holds all the slots 2) schedule which has all the available schedules 3) slotschedule which has all the booked schedules for the corresponding slots.

This is the database diagram

The corresponding DDL design is the following one:

create table Slots
(
  Id int  PRIMARY KEY
);
create table Schedule
(
  Id int PRIMARY KEY ,
  StartTime Time,
  EndTime Time
);

create table SlotSchedule
(
  SlotId int,
  ScheduleId int,
  CONSTRAINT FK_SlotID_Id FOREIGN KEY (SlotId)
    REFERENCES Slots(Id),
  CONSTRAINT FK_ScheduleID_Id FOREIGN KEY (ScheduleId)
    REFERENCES Schedule(Id)
);

The requirement is to query the available schedules which can be booked for the slots.

The query I wrote is

select slots.SlotId,schedule.StartTime, schedule.EndTime
from Schedule schedule
LEFT JOIN
SlotSchedule slots
on slots.ScheduleId = schedule.Id and slots.SlotId = 1
where slots.SlotId is null
order by 1, 2

which results in

But when I want to find for a list of slotId say 1, 2 the result I get is this

From which I can not infer which available schedule belongs to which slot.

This schema and query work fine and I can find a list of available schedules for a particular slot. However, if I want to query a list of slots and gets it's corresponding available schedules I am not sure if that can be done by extending this design.

How do I get all the available schedule for a list of slots i.e. for slots = 1, 2, 3 rather than just 1 as in the query above.

The fiddle is present here https://www.db-fiddle.com/f/oS7mvCgoZipqKGz74wMcFE/0

Updated answer:

select
    Slots.Id,
    Schedule.StartTime,
    Schedule.EndTime
from
    Slots
join Schedule on 1 = 1
left outer join SlotSchedule on Slots.ID = SlotSchedule.SlotId and Schedule.Id = SlotSChedule.ScheduleId
where SlotSchedule.SlotID is null;

... and here is the Fiddle.

This is perhaps not an orthodox approach. It does join the Slot and Schedule tables fully which may have serious performance issues on large tables - but I am guessing the number of rows in Schedule is tiny, and probably in Slot as well.

What this does:

(First, note, I would not use Slots as an alias for SlotSchedules when you also have a table named Slots - that's just way too confusing.)

Start with the Slots table. I then join this to the Schedule table (that's right - totally skipping the SlotSchedule table) on 1=1 - this means every row in Slots will join to every row in Schedule.

The next join is back to the SlotSchedule table and it is an outer join. This time we have conditions - we say that the Slot ID on the SlotSchedule must match our Slot record, and the Schedule ID on the SlotSchedule table must match our Schedule record - but because it is an outer join, if there is no match, we still include the SlotSchedule row, but with a value of null.

This sets us up for the WHERE clause, which is simply - we want all rows where the SlotSchedule information is null, and so we check SlotSchedule.SlotID is null.

In other words, for this combination of Slot and Schedule records, there is no SlotSchedule record, which is what we want.

Original answer:

Your where clause has:

where slots.SlotId is null

Your diagram shows:

slots.id

This is inconsistent.

Your results show slot_id = null (and your query requests this).

I don't understand how you can have slot records with null ID. It looks like you are trying to populate slotschedule with a row for every possible combination of slot and schedule, and to keep the ID null if the slot currently has no schedule.

I would recommend slotschedule only has a row when a slot is assigned to a schedule.

The first thing I would want to understand better is why slot_id is null - I think change this design to resolve the issue.


Can non-domain-connected users use Report Builder I'm dealing with a scenario where SSRS is being provided to remote clients on a different domain. We've discovered that they can launch Report Builder (a ClickOnce application) from their Report Manager browser session, but RB then uses their local credentials when trying to do anything (e.g. hit a datasource) in the RB session.

This article (http://technet.microsoft.com/en-us/library/ms365173(v=sql.105).aspx) seems to indicate that it won't be possible to hack our way around this; whereas this one about Azure (http://msdn.microsoft.com/en-us/library/windowsazure/dn189155.aspx) appears to indicate that a local RB session may be able to interact successfully with Azure - does this suggest there's a glimmer of hope?

We are currently running SQL 2008 R2.

I cant comment (not enough rep on this site) so I have to submit this as an answer.

1) I have successfully set up a SharePoint SSRS portal using forms authentication (FBA). The SSRS part was not hard - very straightforward actually, just perform the SSRS install as SharePoint integrated.  However, the SharePoint FBA part was very painful (not difficult, just lost a lot of hair and sleep over little stuff you didnt think would be necessary and couldnt find straight answers to.)

I know that you can use Report Builder 3.0 to save to a SharePoint site.  I have not tried that using the FBA SharePoint portal.  I dont work at that company anymore, so I dont have access to test that either.  I would think it is very possible though.  The portal I set up was accessed by non-domain users, which is why I had to use FBA.  I was using SharePoint 2007 and SQL Server 2008 R2.

I know the following link tells of the permissions needed for Report Builder to save to a SharePoint library: http://technet.microsoft.com/en-us/library/dd255209(v=sql.105).aspx

2) I did find a specific blog post about using Basic Authentication: http://blogs.msdn.com/b/bobmeyers/archive/2006/09/28/how-to-enable-report-builder-for-non-domain-users.aspx.
There is also an article on MS technet titled: "How to: Configure Report Builder Access"

3) Another thing that is worth trying - You can use a domain account for report viewing only, then use the ReportViewer Web service/API/webpart/etc to have a report embedded within a web application.  That way, the report connects using the username/password specified, not the user viewing the application.  That has worked for an application I helped work on that was exposed on a DMZ web server, but used an SSRS report that was on the internal network.  In that case, the SSRS instance was a Native install.  If you are using .NET - you can use Visual Studio.  It already has controls built in.

The only think I dont know about in this case is the use of Report Builder.  Just thought I would mention it because it might help spur some thoughts.


Is there a name for a query that returns results which have sub headings? I inhered a set of queries / macros from the previous employee in my position and one of the other people here showed me a query result (output from when the query worked correctly) which had subheadings as well as rows.  They described it as a report.

In reconstructing how the series of queries worked I determined that first the rows of the employers were added to a table. Next a list of employees were added to the same table.  And finally it was sorted on a hidden row which placed the employees under their subsequent employers.

This seems to me to be a very unnatural thing to do with a query...my first instinct is to place it in some other kind of report so that it loops through the employers and adds the employees below them in a sub loop; running the employee query multiple times for each employer.

Is there a name for a query like this so I can look up the technique he is using in a book?  Is it an unnatural thing to do because the actual field headings don't really apply to the rows with the employer records?

It is a standard parent-child hierarchy.  It is commonly used in recording manager/subordinate relationships among other uses.  This works well in hierarchical organizations.  It does not record historical relationships which need to be recorded using different techniques.  It also breaks down in matrix organizations, but can still be used to record the organization structure in such cases.  Project/assignment relationships need to be modeled differently.

The report structure appears to use control breaks.  There are a number of query formatting tools which handle this.

There are cases where I might model employers and employees in the same table.  Normally I would model them as separate entities with a join table recording the relationship.

EDIT:  One of the ways to model hierarchical relationships which occur within a table is to include a parent_id column which points to the parent row.  This can be an efficient method to model the relationship where there can be at most one parent, and no other data is required in the relationship.  If there can be more than one parent, or there are attributes related to the relationship, then the relationship should be modeled using the join table.

Control breaks are a reporting structure where control is transferred to the outer loop when a value changes.  There can be multiple levels of control breaks.  When coding this manually, headers for the first row need to handled manually.  These days, the complexities are usually handled by a framework or tool of some sort.  Oracle's SQLPlus query tool uses BREAK ON to specify formatting related to control breaks.


How to find database usage for a specific application action A user is opening a page in our application which takes around 35 seconds.
I want to see what is happening in the database during this time. What's the best way to do this, doing a trace?
Thanks!

Best scenario is only do this test and not let anything else run
on the server during the test.
If not isolate one application server for this purpose so you can
filter with host name or login (whatever is easier in your case).
Set up an extended event trace with the events you want to capture,
include necessary Global Fields.
Set up data storage type event_file so you can review the result later and even load
into a SQL Server database table.
Since you are capturing the trace under one minute I would not bother to
roll files.
If you are not familiar with Extended Event trace search for 'getting
started with extended events' and you will see a large amount of
useful resources.


Is it possible to achieve zero downtime when upgrading database schema using this method? I am looking for a solution in achieving zero downtime schema upgrade with the assumption that every schema upgrade is backwards-compatible with the previous application version which makes things easier. Since the database is already huge, the upgrade may take more than 2 days to complete and the business cannot afford to stop their operations that long.

I found a candidate solution in
https://stackoverflow.com/a/40365479
where its steps are comprise of:

Create a temp table
Creates triggers on the first table (for inserts, updates, deletes) so that they are replicated to the temp table
In small batches, migrate data
When done, rename table to new table, and drop the other table

But my concern is would not it cause a potential race condition problem in the last step where for instance, we have the original table 'users' and the new table 'users_new', and our goal is to replace the original table with the new table? Because to accomplish that, won't it be needed to execute two separate DDL operations such as

DROP TABLE users;
ALTER TABLE users_new RENAME users

And by doing so, what if a query was executed against the users table during the time between the execution of 1st and 2nd DDL commands?

pt-online-schema-change in the Percona Toolkit is used in production in many companies for many years. It's more than a candidate solution.

It avoids the race condition by dropping old table later.

RENAME TABLE users TO user_old, users_new TO users;
DROP TABLE users_old;

Check out the source code around "Step 5: Rename tables: orig -> old, new -> orig"

pt-osc creates three triggers - on update, on delete and on insert. So when any changes are made during copying from users to users_new, the triggers reflect the change in users_new. So by a moment right before the RENAME, users and users_new are identical.


What is a valid use case for using TIME WITH TIME ZONE? Along the lines of this related question:

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE

Are there any valid use cases for actually using  TIME WITH TIME ZONE or should it be considered an anti-pattern?

To be clear: I am asking about TIME…, not TIMESTAMP….

There is no valid use case. time with time zone is broken by design and only included in Postgres since it's in the SQL standard. Don't use it.

Time zones can have daylight saving time (DST) rules, which do not work with timetz at all.

In fact, the use of time with time zone is officially discouraged:

  The type time with time zone is defined by the SQL standard, but the
  definition exhibits properties which lead to questionable usefulness.
  In most cases, a combination of date, time, timestamp without time zone,
  and timestamp with time zone should provide a complete range of
  date/time functionality required by any application.


Are we required to handle Transaction in C# Code as well as in stored procedure Do we really require transaction handling in c# as well database store process both side

C#:

Using(transaction with transaction scope)
{
     Execute stored proc;
     Transaction. Complete;
}

SQL stored procedure:

Create process
As
Begin try
    Begin transaction
    Commit
End try
Begin catch
    Rollback
End catch

First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I am recommending is:

Always have the TRY / CATCH structure so that errors can be properly bubbled up
Optionally include the 3 Transaction handling pieces in the code below if you have multiple DML statements (since a single statement is a transaction in itself). HOWEVER, outside of adding some additional code where it isn't specifically needed, if one prefers to have a consistent template, then it doesn't hurt to keep in the 3 Transaction-related IF blocks. But in that case I would still recommend not keeping the 3 Transaction-related IF blocks for SELECT-only (i.e. read-only) procs.

When doing 2 or more DML statements, you need to use something along the lines of the following (which can also be done for single DML operations if one prefers to be consistent):

CREATE PROCEDURE [SchemaName].[ProcedureName]
(
    @Param  DataType
    ...
)
AS
SET NOCOUNT ON;
DECLARE @InNestedTransaction BIT;

BEGIN TRY

    IF (@@TRANCOUNT = 0)
    BEGIN
        SET @InNestedTransaction = 0;
        BEGIN TRAN; -- only start a transaction if not already in one
    END;
    ELSE
    BEGIN
        SET @InNestedTransaction = 1;
    END;

    -- { 2 or more DML statements (i.e. INSERT / UPDATE / DELETE) }

    IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
    BEGIN
        COMMIT;
    END;

END TRY
BEGIN CATCH

    IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
    BEGIN
        ROLLBACK;
    END;

    DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
            @ErrorState     INT = ERROR_STATE(),
            @ErrorSeverity  INT = ERROR_SEVERITY();

    -- optionally concatenate ERROR_NUMBER() and/or ERROR_LINE() into @ErrorMessage

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    RETURN;

END CATCH;

When doing just 1 DML statement or just a SELECT, you can get away with just the following:

CREATE PROCEDURE [SchemaName].[ProcedureName]
(
    @Param  DataType
    ...
)
AS
SET NOCOUNT ON;

BEGIN TRY

    -- { 0 or 1 DML statements (i.e. INSERT / UPDATE / DELETE) }

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
            @ErrorState     INT = ERROR_STATE(),
            @ErrorSeverity  INT = ERROR_SEVERITY();

    -- optionally concatenate ERROR_NUMBER() and/or ERROR_LINE() into @ErrorMessage

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    RETURN;

END CATCH;

Second, you should handle the transaction at the app layer only if you need to execute more than 1 query/stored procedure and they all need to be grouped into an atomic operation. Doing a single SqlCommand.Execute___ only needs to be in a try / catch, but not in a Transaction.

But, does it hurt to do a Transaction at the app layer when only making a single call? If it requires MSDTC (Microsoft Distributed Transaction Coordinator) then it is a bit heavier on the system to do this at the app layer when not expressly needed. Personally, I prefer to avoid app layer-based transactions unless absolutely necessary as it cuts down on the potential for orphaned transactions (if something went wrong with the app code prior to doing the commit or rollback). I also have found that it sometimes make debugging certain situations a little more difficult. But that being said, I don't see anything technically wrong with also handling the transaction at the app layer when making a single proc call; again, a single DML statement is its own transaction and doesn't need any explicit transaction handling at either layer.


OpenRowSet - How to configure OLE DB Provider to be used for for distributed queries I have an Excel file with data and would like to update a table in database based on the data the Excel file contains.

To do that I want to use OpenRowSet command. But I get the error below when even I want to have a SELECT from the Excel data.

SELECT  exl.*
INTO #myExcelData
FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0'
,'Excel 12.0; Database=C:\Dev\ExcelDataImport.xlsx; Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1'
,'SELECT * FROM [Sheet1$]') AS exl
GO

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

As error message says, I think I should configure the Microsoft.ACE.OLEDB.12.0 to be able to use it for distributed queries like OpenRowSet. To do that I ran the commond below:

exec sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

Output message from the command above:

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

But still I get the same error when try to run the OpenRowSet query.Could you please give me some hints how to resolve this issue and get the OpenRowSet working?

My environments:

SQL Server 2008 R2, Excel Professional Plus 10 (32 bits)

Also the providers I have in Linked sever as as following:

Thanks in advance.

after installation Restart agent and server services

Enable OLEDB Driver in SQL Server

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO


INSERT/UPDATE in same query from previous SELECT with some singularities I'm trying to perform a INSERT|UPDATE by selecting data from another DB and this is what I have so far:

INSERT INTO pdone.reps
    (veeva_rep_id,display_name,username,`first`,`last`,email)
SELECT Id, CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)),username, firstname, lastname, email
FROM veeva.`user`
WHERE Id = "00580000003UB5VAAW"

First problem, I got this error:

  [Err] 1064 - You have an error in your SQL syntax; check the manual
  that corresponds to your MariaDB server version for the right syntax
  to use near 'from veeva.user WHERE Id = "00580000003UB5VAAW"' at
  line 1

and I'm not sure what is wrong on the query, any advice?

firstname could be JOHN or John or jOhn or any and I want to normalize as John, is UCASE(LEFT(firstname, 1) fine for this? The same apply to lastname
If firstname is JOHN and lastname is DOE then username should be John Doe with a space between them, is my CONCATENATE right?
I should insert some statics fields like one URL or just VEEVA how I can do that? Values are not present on the query shown here but is just add two more columns to the insert avatar_url and rep_type
I am planning to add ON DUPLICATE KEY UPDATE but can be possible to add a restriction based on a column? Lets said UPDATE only if now > lastSyncDate?

Insert query:

INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type)
SELECT
    Id,
    CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))),
    username,
    firstname,
    lastname,
    email,
    'www.some_static_url.com',
    '1'
FROM veeva.user
WHERE Id = "00580000003UB5VAAW"

It will print Only first letter capital from both First & Last name.


Inventory Management DB Design *strong text*I am putting together a small inventory management system and have some questions concerning the database design. What I was planning on doing was to have 3 tables, items, bins and shelves. Items would be created and added to the table. When we have stock of an item it will be in a bin on a shelf. If we don't have stock the item will still be in the items table but it won't have a location or the location will be set to a value that indicates  no stock.

I started the items table but I wasn't quite sure how to create the relationship between all the tables. I believe a foreign key is what I would be looking for but I'm not sure on the proper way to implement it. I was thinking the following lay out for the tables but as I started coding it I realized this wouldn't work.

UPDATE: In response to the answer I received this is the query I came up with. It's wrapped in Python so ignore that.

db = 'inventory.sqlite'
(cur, con) = open_db(db)
cur.execute("CREATE TABLE Items (item_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            sku TEXT UNIQUE NOT NULL, \
            title TEXT UNIQUE NOT NULL
            )")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Bins (bin_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            name TEXT UNIQUE NOT NULL)")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Shelves (shelf_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            name TEXT UNIQUE NOT NULL)")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Bin_Contents (bin_id INTEGER NOT NULL, \
            item_id INTEGER NOT NULL, \
            qty INTEGER NOT NULL, \
            CONSTRAINT PK_Bin_Contents PRIMARY KEY (bin_id, item_id), \
            FOREIGN KEY (bin_id) REFERENCES Bins (bin_id), \
            FOREIGN KEY (item_id) REFERENCES Items (item_id) \
            )")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Shelf_Contents (shelf_id INTEGER NOT NULL, \
            bin_id INTEGER NOT NULL, \
            CONSTRAINT PK_Shelf_Contents PRIMARY KEY (shelf_id, bin_id), \
            FOREIGN KEY (shelf_id) REFERENCES Shelves (shelf_id), \
            FOREIGN KEY (bin_id) REFERENCES Bins (bin_id) \
            )")
close_db(con)

After writing that and looking at it I realized that wouldn't function the way I wanted it to. As I mentioned earlier I believe a foreign key is what I need but I'm not sure about the best way to implement it. I would like to be able to:

UPDATE: Will the above query create the DB/tables i need to do the following:

Use the app to search for items and display where they are located
and how many are in each location.
Use a shelf/bin number to pull up the contents of that shelf/bin. For
example the user selects shelf_a from a menu. All the bins in
shelf_a are displayed. The user can select a bin and see it's
contents.
Perform basic management tasks. Add an item to a shelf/bin, remove
some/all the quantity of an item from a shelf/bin, add new
shelves/bins etc with out anything disappearing. Shelves are still
there even they are empty same for bins, they are still there even
when they are empty.

I don't need help with the queries themselves I just need to make sure the proper relationships are in place so when I write the queries the right info comes back.

There are some things you want to think about based on the nature of your items and how you store and track them.

Are your items discrete or are they a commodity?  The way you would track television sets, each of which has its own distinct serial number is different from how you might track boxes of nails.  If your SKUs are discrete you don't have a "quantity" field, instead you track individual items with an intersection table (as suggested by Joe).  Otherwise whether you need an intersection table depends on how many different SKUs can be on a shelf (see below).

Is it important to track inventory movements?  Do you need to see stock deliveries and shipments?  If so you might want to take a kind of double entry accounting approach, treating shelves like GL numbers.  If not, a simpler quantity per shelf may be sufficient.

Can multiple SKUs share a shelf?  If so, you need an intersection table.  If not, you can get by with a foreign key from Shelf to Item.

Does every bin have at least one shelf?  Your application (queries) will be much simpler if every bin has at least one shelf, such that you don't need to track inventory which is in a bin but has no applicable shelf information.

EDIT:

It's important to get the terminology clear. When you say "bin" you mean a box without a lid that sits on a shelf. In a lot of inventory situations, a "bin" is a space on a rack where you could put a skid or one or more shelves.

As long as we're clear, you want a hierarchy of SHELVES each of which has many BINS each of which can have many BIN_CONTENTS each of which is an intersection between BIN and ITEM and which has a quantity. Don't store quantity on your ITEM table, store it on the intersection table, that way you can have multiple bins of the same item, or even many different kinds of items, each in their own distinct bins or even multiple different items mixed within a bin, all on the same shelf.

Consider the following ERD:


Why is the value of NUMERIC_SCALE column in INFORMATION_SCHEMA.COLUMNS table for DATA_TYPE='double' NULL? I have created a column whose data_type is DOUBLE.

alter table my_table add column my_column double;

When I queried about this column in information_schema.COLUMNS table
I found that the NUMERIC_SCALE for this column is NULL?

select DATA_TYPE,
NUMERIC_PRECISION,
NUMERIC_SCALE

from information_schema.columns

where table_schema = 'my_schema'
and table_name = 'my_table'
and column_name = 'my_column'

+-----------+-------------------+---------------+
| DATA_TYPE | NUMERIC_PRECISION | NUMERIC_SCALE |
+-----------+-------------------+---------------+
| double    |                22 |          NULL |
+-----------+-------------------+---------------+

Can someone explain this behavior?

Floating point numbers are exactly that -- there's no fixed position for the decimal separator (point), it can be anywhere, and specifying scale (i.e. the number of digits after the decimal separator) does not make sense.

Scale is used for the DECIMAL data type, a type with the fixed decimal separator, for which scale is significant and meaningful.

MySQL also allows a non-standard syntax for floating point numbers, where you can specify precision and scale, e.g. DOUBLE(10, 5). MySQL will round floating point values to that specification when storing them, and format them appropriately when returning query results.

Documentation link.

dbfiddle example.


Empty strings in VARCHAR(MAX) consuming space which cannot be recovered I have a table with a non-nullable VARCHAR(MAX) column where every row contains an empty string (third party database so I can't change it). I see a huge amount of space use, over 15GB for 1 million rows.

If I run the following query I see a huge number of used_pages for the LOB_DATA even though there is no data in that column. Excluding the VARCHAR(MAX) the row size is under 8000 bytes.

 SELECT o.name AS table_name,p.index_id, au.type, au.type_desc, au.total_pages, au.used_pages, au.data_pages
 FROM sys.allocation_units AS au
   JOIN sys.partitions AS p ON au.container_id = p.partition_id
   JOIN sys.objects AS o ON p.object_id = o.object_id
 WHERE o.name = 'table
 ORDER BY o.name, p.index_id;

type    type_desc             total_pages   used_pages  data_pages
1   IN_ROW_DATA 23258             23252           23188
2   LOB_DATA              1880733             1880455             0
3   ROW_OVERFLOW_DATA   0             0           0

Updates from comments:

The table is an archive table so the data should never have changed (only inserted)
I have an identical table in a seperate database (same server) with similar data that  doesn't exhibit this problem
Rebuilding the index didn't help
large_value_types_out_of_row is 0
DBCC CLEANTABLE didn't help

Is it possible the column was populated and it is has since been cleared out, or the data type was recently changed to varchar(max)? Have you tried rebuilding the clustered index (which will touch every row, unlike some ALTERs)?

EDIT

Since rebuilding the clustered index didn't help, I'm at a loss, and since the size of the existing data is so small, I recommend just creating a new version of the table and moving the data over. You can do this by explicitly re-creating the table and then copying the data, or you can use SELECT INTO to minimize the steps.

SELECT ... other cols ...,
    varchar_max_col = CONVERT(VARCHAR(MAX), NULL)
    INTO dbo.newtable FROM dbo.oldtable;
CREATE CLUSTERED INDEX ... ON dbo.newtable( ...cols... );
CREATE INDEX ... ON dbo.newtable( ...cols... );
...
GO
BEGIN TRANSACTION;
EXEC sp_rename 'dbo.oldtable', 'oldtable_backup', OBJECT;
EXEC sp_rename 'dbo.newtable', 'oldtable', OBJECT;
COMMIT TRANSACTION;


Subtract column value from date in days or months I want to subtract the value in column lag_days from current_date.
Usually I would do current_date - interval '1 day'
I'm getting syntax error on the following example to subtract by days:
dkarchive=> select current_date - interval lag_days day from example_tbl;
ERROR:  syntax error at or near "day"
LINE 1: select current_date - interval lag_days day from example_tb...

You don't need to use the interval syntax to begin with. You can simply subtract an integer from a date value, e.g. current_date - 1 is "yesterday".
select current_date - lag_days
from the_table;

If you do want to use an interval, the make_interval() function is quite useful for this:
select current_date - make_interval(days => lag_days)
from the_table;

There is however a difference between the two queries: the first one returns a date value, the second one a timestamp value.


How to decompose this relation into 3NF relations? Today I read about the 3NF decomposition algorithm. It said:

  Find a minimal basis of F, say G
  For each FD X → A in G, use {X, A} as the schema of one of the
  relations in the decomposition
  If none of the sets of relations from Step2 is a superkey for R, add
  another relation whose schema is a key for R

I want to decompose this relation into 3NF.

R(A,B,C)
S={A→B, A→C, B→A, B→C, C→A, C→ B, AB→ C, BC→A, AC→B, A→BC, B→AC, C→AB}

As we can see, the key of R is: {A},{B},{C}

S has several minimal basis, such as:

{A→B, B→A, B→C, C→B}; and
{A→B, B→C, C→A}

The problem is, if we use the 1st minimal basis, then we decompose R into 2 relations: (A, B), (B, C).

If we use the 2nd minimal basis, R turns into: (A, B), (B, C), (C, A).

My question is: which one is correct?

First of all, note that the original relation is already in Third Normal Form, since each attribute is prime (each attribute is a key, actually), so that the definition of 3NF is respected.

Then, note that the algorithm is incomplete. The steps are:

  Find a minimal basis of F, say G
  For each groups of FD with the same left part, X → A1, X → A2, ..., X → An in G, use {X, A1, A2, ..., An} as the schema of one of the relations in the decomposition
  Delete all the relations whose attributes are contained in another relation.
  If none of the sets of relations from Step2 is a superkey for R, add another relation whose schema is a key for R.

So in the first case, you obtain three groups of dependencies:

A → B
B → A
B → C
C → B

that produce three relations, R1(A, B), R2(A, B, C), R3(B, C), and, following the algorithm, you obtain as result only R2, since the other two have attributes contained in them.

So you have two different outputs from the algorithm, depending on the minimal base used (which in turn depends on the order in which you consider the dependencies when calculating the minimal cover).

So, the answer to your question:

  which one is correct?

is: both of them are correct, since both of them satisfies the definition of the 3NF. You have simply discovered that the synthesis algorithm for decomposing a relation in 3NF can produce different solutions.

A different question is: which is “better”, and of course the solution with a single relation is “better”, since you do not need to join tables when making queries.

Of course, if one could check at the beginning if the relation is already in 3NF, than he can avoid to apply the algorithm. But this in general cannot be done, since the check requires the exponential computation of all the keys, to find the prime attributes of the relation.


Which part of a DB bulk insert takes the most time? One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit transaction] is taking the bulk of the processing time?

From what I've read the insert statements are first saved in the cache then executed all at once when the commit transaction is executed. If I understand that correctly, wouldn't the commit transaction part take the most time?

The reason I'm asking is because we have this code where we try to limit the size of inserts within a single transaction with the aim to minimize the whole processing time to around 200ms.

The way were doing it is we're constantly checking the time if it has exceeded 200ms whenever we do an insert. Once 200ms has been reached (or all insert statements have been performed) then we execute the commit transaction. But if what I mentioned above is true, wouldn't our code still be prone to significantly exceeding 200ms execution time?

Edit: using SQLite3, btw.

The database page cache is written to disk during the commit. But even that is fast because the OS writes to its own cache. What takes the most time is, at the end of the commit, waiting for the data to be actually written to disk (FlushFileBuffers()).

If you want to minimize the overall time needed for a transaction, you have to limit the amount of data.

Alternatively, consider using asynchronous writes by enabling WAL mode.


Fix OGR2OGR "failed when converting the varchar value to data type int" issue in the MSSQL table - I am a newbie to MSSQL and am trying to convert an MSSQL table to Mapinfo Tab file using OGR2OGR but it keeps failing with the above message on a few tables.

I have tried to get a reply in the GIS SE about switches to apply in OGR2OGR but have not got any options. https://gis.stackexchange.com/questions/314669/ogr2ogr-conversion-failed-when-converting-the-varchar-value-to-data-type-int

How can I change this in the table?

What does NCPR mean -this text doesn't exist in the table -it it code for something?

The good news: You're not missing anything obvious or arcane.

The bad news: If you've got a proper DBA onsite, it's time to get them involved.

Dollars to donuts the error is in the object_definition() of dbo.vw_AUTH_CREFNO. Typically the vw_ prefix indicates that the object is a VIEW, meaning that it's just a wrapper for a larger SELECT statement which itself can have a series of poorly written JOINs, malformed expressions, and nested evaluations.

If you have VIEW DEFINITION permission in the GIS_Test database on server zzzz (and the object is not encrypted), it's time to try extracting the base definition and crossing your fingers the DDL is sensibly written and it's an obvious error. In that Object Explorer pane you've got open, right-click dbo.vw_AUTH_CREFNO and hit Script View as Create To New Query Editor Window, then Ctrl+f and hope like hell you find NCPR as a string literal in the code. What's more likely though is that you've got one ore more of the following

a string column JOINing to a numeric column somewhere
an expression somewhere between string and numeric (look for the +-/*= characters)
an explicit conversion against a value where you've got "dirty data" in the base table (whether by typecasting or coalescing into a bad target)
something else?

If you're lucky enough that NCPR is a string literal somewhere in code, but it's nested below the first level object AND it's still in the same GIS_Test database, you can spelunk pretty broadly for it using the following snippet.

select
     def = object_definition([object_id])
    ,[name]
    ,schema_name([schema_id)
from sys.objects
where object_definition([object_id]) like '%NCPR%';

If you're not so lucky, you need to look for that value in the base data, or try more advanced methods looking for which (of possibly several) problematic expression is causing your current problem.

Good luck! ¯\_(ツ)_/¯


What is the best practices for storing sales tax rates? In Canada we have a federal general services tax (GST) of 5%. Some provinces have a provincial sales tax (PST). Some provinces have a hybridized sales tax (HST) instead of a separate (GST) and (PST). Some provinces have a tax on Insurance (RST). Extended Warranty on vehicles is taxed GST but only one province taxes it for PST. One province (BC) varies the PST on passenger vehicles where the rate is 7% for < $55,000, 8% for >= $55000 AND < $56000, 9% for >= $56000 AND < $57000 and 10% for >= $57000. There is a definition of what

In the states I read that you don't have a Federal Sales Tax but you do have separate state, county and city sales taxes. Looking at Wikipedia there are many details to the taxes in the states. https://en.wikipedia.org/wiki/Sales_taxes_in_the_United_States

I will likely need to support taxes in other countries as well at some point.

What is the best practice for storing all these tax rates so that applications can use them effectively in real time to compute tax amounts?

What is the best practice for storing the tax amounts for different transactions?

Sales taxes are almost infinitely complicated, so you might want to consider using a rules engine like Drools for this.

Sales Tax is a rate or an amount that is charged on the value of an item, or by unit (per day, per litre, per each). An item can have zero, one or many sales taxes applicable to it.

Warning: Some sales taxes are recursive! Ex: there is GST on top of the Vehicle Rental Tax in BC.

The sales tax rates on an item is a function of the following:

Location of the buyer
Product category of the item being sold (gas, liquor, books)
Value of the item (ex higher rate on higher priced luxury goods)
Category of the buyer (ex charity, First Nations individual, industrial camps)
Category of the seller
Intended use of the item (for export, for manufacturing)
Duration of the service (car rentals > 8 hours and < 28 days)
Whether it's a gift or not

I think what you ultimately want is a large look-up table (generated by a materialized view) like the below. It would include default tax rates as well as exemptions (represented by null) or zero-rated (with a rate of zero)

This is only a sample of taxation rules for hotel accommodation in British Columbia:

name | area | product cat  | price range | buyer cat | service duration | rate | unit  | seller cat
-----------------------------------------------------------------------------------------------
GST    BC     Accommodation  null          null        null           0.05   Percent null
PST    BC     Accommodation  null          null        null           0.08   Percent null
MRDT   BC     Accommodation  null          null        null           0.02   Percent null
MRDT   Vanc   Accommodation  null          null        null           0.03   Percent null
PST    BC     Accommodation  [,30]         null        null           null   null    null
MRDT   BC     Accommodation  [,30]         null        null           null   null    null
PST    BC     Accommodation  null          null        [30,]          null   null    null
MRDT   BC     Accommodation  null          null        [30,]          null   null    null
PST    BC     Accommodation  null          1st Nations null           null   null    null
MRDT   BC     Accommodation  null          1st Nations null           null   null    null
PST    BC     Accommodation  null          Gov Canada  null           null   null    null
PST    BC     Accommodation  null          null        null           null   null    Industrial Camp
MRDT   BC     Accommodation  null          null        null           null   null    Industrial Camp
PST    BC     Accommodation  null          null        null           null   null    Charity
MRDT   BC     Accommodation  null          null        null           null   null    Charity

Then you'd have a crazy query that would figure out the default rates, replacing them with the most appropriate exemptions or zero-rates.

5% GST always applies
8% PST unless price is < 30 or duration > 30 days or buyer is First Nations or Government of Canada or seller is an Industrial Camp or Charity
2% Municipal and Regional District Tax unless price is < 30 or duration > 30 days or buyer is First Nations or seller is an Industrial Camp or Charity
3% Municipal and Regional District Tax if accommodation is in Vancouver unless price is < 30 or duration > 30 days or buyer is First Nations or seller is an Industrial Camp or Charity


PostgreSQL High Memory consumption We have been getting a lot of issues pertaining memory consumption in Postgres. I am thinking of deploying PgBouncer to resolve this issue, but wanted to know what could be the cause for this.
ps aux --sort=-%mem | head -30

USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  23438 41.0  6.2 78075200 8258220 ?    Ssl  Aug09 525:40 postgres: xxxxx: xxxxx(60028) idle
postgres   4225 31.7  5.9 77797888 7903220 ?    Ssl  Aug09 438:58 postgres: xxxxx: xxxxx(45012) idle
postgres 118999 39.3  4.8 76290020 6395136 ?    Ssl  Aug09 306:52 postgres: xxxxx: xxxxx(50026) idle

free -g
              total        used        free      shared  buff/cache   available

Mem:            125          36          35           3          54          84
Swap:            19           0          19

System RAM : 128 GB
CPU(s) : 32
Shared_buffer : 32 GB
DB Size : 24 GB
Max Connections : 200
Average Connections: 50
Effective_Cache_Size : 64 GB
Work_mem : 41MB
PG Version : 11
OS : Ubuntu

A lot of the idle connections are getting reused despite it being closed from the app side. I have tuned the database by changing Shared_buffer, work_mem to values as per PostgreSQL expectations, but still getting these issues.
PS : We have setup HAProxy between App and DB.

This is probably shared memory that is common to all PostgreSQL processes.
ps is not smart about distinguishing private memory and shared memory.
Don't worry.


How to change MySql Master-Master Replication to Master-Slave Replication I currently have MySql Master-Master replication set-up with Read_only on Master2.
There were lot of sync issues so I've stopped replication from Master2 to Master1 by stopping the Slave in Master1. Master1 is currently replicating to Master2 with no issues.
Is this enough or is there another best way to revert to Master-Slave replication.
Should I run RESET SLAVE on Master1 to completely stop Replication from Master2 to Master1.

On a basic level, what you have done is enough, but there is a risk it could be re-enabled by mistake as long as the Binary logs exist on master2.
So, if this is just a temporary thing then running RESET SLAVE will remove the master_log_file and read_master_log_pos values, thus preventing replication from starting again by mistake, but maintaining the other values for future use.
If this is going to be permanent, then RESET SLAVE ALL will remove all the replication information (include host, port, user, password etc).


SQL index with additional includes In an effort to be more efficient when creating a new index, if there is already an index with the same index keys, but it simply needs additional include columns, is it better to create a second index (again, with the same keys) with different include columns? Or would it be more efficient to the query engine (not to recreate the index) to recreate the index and add the include columns to the original include column set?

If simply adding the include columns to the existing index, does it matter what order the include columns are appended?

Is there a scenario where it would make more sense to create a second index, duplicating the index keys, but with a different set of include columns?

I've attempted to simplify this question in code below.

    CREATE NONCLUSTERED INDEX [IX_Index_A] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col2])

    CREATE NONCLUSTERED INDEX [IX_Index_B] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col3],[Col4])

OR

    DROP INDEX [IX_Index_A] ON [dbo].[Table]

    CREATE NONCLUSTERED INDEX [IX_Index_A] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col2],[Col3],[Col4])

The included field order doesn't matter since there's no sorting involved - those fields are available at the leaf level of the index for every row.

There's not really a disadvantage to consolidating them like in your second example, except for page density on that index (more page reads per number of rows since there's more data stored for each row).

There ARE measurable advantages, though - for updates/inserts on Col1 you only need to update one index instead of two, and you will save a lot of space by not keeping duplicate key entries for col1 in two separate indexes.

The only scenario I can think of that would make sense to have the two narrower indexes is if one of  those was designed specifically for a mission critical procedure or view.  If you have an index that includes only what you need for a certain query that gets run with high frequency it would make sense to keep it as streamlined as possible.


Does SQL server optimize or pre-parse stored procedures? Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored procedures.

So, does SQL Server optimize stored procedures? Does it even parse them beforehand, or does it just store them internally as a sql script?

I did not ask about a specific SQL Server version because I assume history is relevant in a meaningful understanding.

Note that my question is NOT, "Are ad-hoc queries as fast as stored procedures?" There are too many other factors involved in answering this.

All queries executed by SQL Server need to be compiled first. Stored Procedures are no different in this respect, they don't get any kind of additional optimisation.

They are parsed when created to check invalid syntax but they don't actually get an execution plan generated until first executed. When the plan is compiled it will use the parameter values passed in this initial execution to generate it. This can lead to parameter sniffing issues.

All execution plans generated will remain in the plan cache generally until either  evicted due to memory pressure or they becomes invalid due to schema changes or it gets an optimality based recompile. (For adhoc queries the "optimize for ad hoc workloads" option can change this behaviour).

The main benefit of stored procedures is that for subsequent similar queries the plan is more likely to be re-used. For adhoc queries this is dependant on auto parametrisation which is quite conservative so you may well end up with lots of compilations for very similar queries but with different parameter values that all end up in the same plan anyway.

I have left out a lot of details in this answer. See this plan caching white paper for full details


accessing, updating a large MYSQL table advice and guidance I'm after some guidance or advice really.  We've built an application and its gone mental with custom since coronavirus.  We are after some advice for something we have never tackled.

The problem we are having is in fact the job table.  We have 1000's of office-based users updating, creating, and editing jobs all day.  We also have then 10's of thousands of apps out in the field connecting all-day to this table to read their rotas and then update it if they clock in or out of locations.

We've put in all relevant indexes and we are still starting to see a slowdown.  To the point some times where we have to restart MYSQL.

After many hours of Googling, we have come across many solutions for big data including solutions on here but we'd like to ask the community what they think or the following idea, or is there a better way?

Idea 1

As the system grows and we get more clients this is only going to get worse.  Being that the apps can only see 1 day back and 7 days forward whereas the rota table stores all of the rota history from the beginning of time we were thinking of creating an exact replica table of the job table.  This table would store all rotas from yesterday and forward 7 days using a cronjob to delete older data and add new data each night.  The apps would then connect to this table to get the jobs list and update the original table if they clock in or out.

If users update fields for jobs yesterday or within 7 days we would use triggers to keep the two tables in sync or maybe do it through code so it was more accurate?

This way we have taken the read load away from our main rota table by pointing the apps at the new table.

Our question is, is this a good way to go, or are we digging a hole we can't climb out of?

Idea 2

Simply archive off jobs from more than a year ago to another table.  If users request to see jobs from over a year ago it simply looks in the other table.  While we like this solution we can see its a sliding scale.  As we get more customers we will have to start archiving sooner and sooner until the problem reappears in a few year's time.

if these ideas are no good does anybody have any other suggestions we can look at?  Partitioning the table is not an option at the minute.

With the data visibility being only 7+1 days and keeping all the data for much longer, it sounds like what you really need to implement is partitioning by day. This will also make it orders of magnitude easier and quicker to purge the data you no longer wish to keep.

For archiving, you can use a 2nd replicated server that has longer partition retention. For example, the primary server can keep only 8 daily partitions, bit your scheduled event can omit partition dropping from the replication log by toggling sql_log_bin around partition dropping. The result would be that your primary server only has 8 days of data on it and is kept small and fast, while your archive replica keeps all the partitions for a much longer period, up to 8192 partitions at least, which is the limit on the number of partitions on a table. You then have 20-odd years to implement additional further archiving if you really need to keep the data forever. ;-)

In addition to that - it may be worth sharing your table definitions and slow queries - there is always the possibility that there is a more efficient way to query for the data or a better way to index, or a way to pre-aggregate for queries that have to touch thousands of rows.


Have you tried this one?

Case # 1

This SQL Query only check out in February month of 2014 of all type (that is Cat,dog,horse):

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-02-01'
                  AND '2014-02-28'
GROUP BY type
ORDER BY type;

Case # 2

This SQL Query check out the only type cat for the month of February 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-02-01'
                  AND '2014-02-28'
AND      type LIKE '%cat%'
GROUP BY type
ORDER BY type;

Case 3#

This SQL Query check out all type in the year 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-01-01'
                  AND '2014-12-31'
GROUP BY type
ORDER BY type;


SQL performance issues with remote query across linked server This sproc

create proc dbo.Get_Accounts as
begin
  declare @current_date datetime
  set @current_date = dbo.fn_currdate()

  select [fields]
  into dbo.current_accounts
  from linkedserver.database.dbo.accounts
  where date = @current_date
end

continually fails after 10 minutes with the following error message:

  Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
  reported an error. Execution terminated by the provider because a
  resource limit was reached. [OLE/DB provider returned message: Timeout
  expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
  ICommandText::Execute returned 0x80040e31: Execution terminated by the
  provider because a resource limit was reached.].

However, when I run the same query from the same database (not on the remote one) in an interactive query window with the date hard-coded:

  select [fields]
  into dbo.current_accounts
  from linkedserver.database.dbo.accounts
  where date = '1/20/2012'

It returns in 30 seconds.

The local server is SQLSERVER 2008, the remote is SQLSERVER 2000.

We have done the following to no avail:

Recreated the stored proc.
sp_recompile on the stored proc
update statistics on dbo.accounts
dropped and recreated the indexes on dbo.accounts
dropped the index on dbo.accounts and try
DBCC FREEPROCCACHE & DBCC DROPCLEANBUFFERS on both local and remote servers
Rebooted the remote server (not an easy option on the local one)

Questions

Can anyone explain this bizarre behavior?
Any suggestions on other options for correcting it?

You can turn on trace flag 7300 which might give you a more detailed error message

How many rows does a representative query return? How fast/reliable is the network connection between the two servers?

It's possible that a large dataset is taking too long to transfer (on top of the actual query time).  You could raise the timeout value.

You can try to reconfigure the timeout setting as follows:

Set the remote login timeout to 300 seconds:

sp_configure 'remote login timeout', 300
go
reconfigure with override
go

Set the remote query timeout to 0 (infinite wait):

sp_configure 'remote query timeout', 0
go
reconfigure with override
go

Update: SQL Server 2012 SP1 onwards: users with SELECT permission will be able to access DBCC SHOW_STATISTICS which will improve read only performance on linked servers. Ref: https://msdn.microsoft.com/en-us/library/ms174384(v=sql.110).aspx

Update:  You are correct in saying it's not the size of data or connection speed.  It rang a bell in my foggy memory and I remembered where I had seen it: Slow in the Application, Fast in SSMS?(An Issue With Linked Servers). It's not parameter sniffing, it is the statistics themselves that are missing (due to permissions), causing a bad query plan to be used:

  You can see that the estimates are different. When I ran as sysadmin,
  the estimate was 1 row, which is a correct number, since there are no
  Orders in Northwind where the order ID exceeds 20000. But when I ran
  as a plain user, the estimate was 249 rows. We recognize this
  particular number as 30 % of 830 orders, or the estimate for an
  inequality operation when the optimizer has no information.
  Previously, this was due to an unknown variable value, but in this
  case there is no variable that can be unknown. No, it is the
  statistics themselves that are missing.

  As long as a query accesses only tables in the local server, the
  optimizer can always access the statistics for all tables in the
  query; there are no extra permission checks. But this is different
  with tables on a linked server. When SQL Server accesses a linked
  server, there is no secret protocol that is only used for inter-server
  communication. No, instead SQL Server uses the standard OLE DB
  interface for linked servers, be other SQL Server instances, Oracle,
  text files or your home-brewed data source, and connects just like any
  other user. Exactly how statistics is retrieved depends on the data
  source and the OLE DB provider in question. In this case, the provider
  is SQL Server Native Client which retrieves the statistics in two
  steps. (You can see this by running Profiler against the remote
  server). First the provider runs the procedure
  sp_table_statistics2_rowset which returns information about which
  column statistics there are, as well as their cardinality and their
  density information. In the second step, the provider runs DBCC
  SHOW_STATISTICS, a command that returns the full distribution
  statistics. (We will look closer at this command later in this
  article.) Here is the catch: to run DBCC SHOW_STATISTICS, you must be
  member of the server role sysadmin or any of the database roles
  db_owner or db_ddladmin.

  And this is why I got different results. When running as sysadmin I
  got the full distribution statistics which indicated that there are no
  rows with order ID > 20000, and the estimate was one row. (Recall that
  the optimizer never assumes zero rows from statistics.) But when
  running as the plain user, DBCC SHOW_STATISTICS failed with a
  permission error. This error was not propagated, but instead the
  optimizer accepted that there were no statistics and used default
  assumptions. Since it did get cardinality information, it learnt that
  the remote table has 830 rows, whence the estimate of 249 rows.

  Whenever you encounter a performance problem where a query that
  includes access to a linked server is slow in the application, but it
  runs fast when you test it from SSMS, you should always investigate if
  insufficient permissions on the remote database could be the cause.
  (Keep in mind that the access to the linked server may not be overt in
  the query, but could be hidden in a view.) If you determine that
  permissions on the remote database is the problem, what actions could
  you take?

  You can add the users to the role db_ddladmin, but since this gives them right to add and drop tables, this is not recommendable.
  By default, when a users connect to a remote server they connect as themselves, but you can set up a login mapping with
  sp_addlinkedsrvlogin, so that users map to a proxy account that has
  membership in db_ddladmin. Note that this proxy account must be an SQL
  login, so this is not an option if the remote server does not have SQL
  authentication enabled. This solution too is somewhat dubious from a
  security perspective, although its better the previous suggestion.
  In some cases you can rewrite the query with OPENQUERY to force evaluation on the remote server. This can be particularly useful, if
  the query includes several remote tables. (But it can also backfire,
  because the optimizer now gets even less statistics information from
  the remote server.)
  You could of course use the full battery of hints and plan guides to get the plan you want.
  Finally, you should ask yourself whether that linked-server access is needed. Maybe the databases could be on the same server? Could data
  be replicated? Some other solution?


What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)? When I check our SQL Prod server, every time I open Activity Monitor I always see SQLCLR on the first list on the resource wait section in the Activity Monitor. It always has a high number. What is SQLCLR for? Is it bad for the server having that SQLCLR with high resource wait number in the activity monitor?

SQLCLR is the ability to run .NET code within SQL Server.

When people speak of SQLCLR they are usually referring to the ability to write custom .NET code (Stored Procedures, Functions, Triggers, User-Defined Types, and User-Defined Aggregates). In this case, this ability can be turned on and off via the server option of "clr enabled" within sp_configure, and I believe it's called "CLR Integration" in the Surface Area Configuration GUI. Adding .NET functionality requires using CREATE ASSEMBLY to load the compiled .NET code into SQL Server.

However, even with the "clr enabled" option set to 0 / "off" / "disabled", CLR functionality is still used for internal functionality and some built-in functions and will always be there, unless the "use Windows fibers" / "lightweight pooling" option is enabled.

For a very detailed description of what SQLCLR is, is not, and what it can do, please see the article that I wrote on this topic on SQL Server Central: Stairway to SQLCLR Level 1: What is SQLCLR? (free registration is required to view content on that site). A stripped-down version of that article is available in the following StackOverflow answer: When do we need a CLR function in SQL Server?.

If you want to know whether or not the instance is allowing custom .NET code to be added to databases, run the following and check the "run_value" field:

EXEC sp_configure 'clr enabled';

With regards to the meaning of the SQLCLR wait type in Resource Monitor, please see the following blog post:

How It Works: What is behind the SQLCLR wait category in SQL Server 2008 Activity Monitor

and I believe the pertinent line (as it pertains to the SQLCLR category you are looking at) from that post is:

  What I found was that several of the wait_types should be ignored as they are expected waits.


MySQL query of Hotel database How to  List the room numbers that are ‘currently’ occupied at Holiday Inn Express.

I wrote the MySQL query ,but I don't get any output. Can anyone help to solve this?

SELECT r.rm_number
FROM   hotel h,
       reservation rr,
       room_booking b,
       room r
WHERE  rr.res_id = b.res_id
       AND b.rm_id = r.rm_id
       AND r.htl_id = h.htl_id
       AND h.htl_name = 'Holiday Inn Express'
       AND rr.res_checkin <= CURRENT_DATE
       AND rr.res_checkout >= CURRENT_DATE

SELECT r.rm_number
FROM   hotel h,
     reservation rr,
     room_booking b,
     room r
WHERE  rr.res_id = b.res_id
     AND b.rm_id = r.rm_id
     AND r.htl_id = h.htl_id
     AND h.htl_name = 'Holiday Inn Express'
     AND rr.res_checkin IS NOT NULL
     AND rr.res_checkout IS NULL ;


What happens to a transaction if the network connection fails? What happens to a transaction when the client connection is lost.
Example the client is a web application code that starts a transaction to a remote server instance. The client sends the sql code to the server and waits for the transaction to complete.
What happens if the network connection fails? Does it depend on the point we are in a transaction? Is it irrelevant? Is it aborted?

http://dev.mysql.com/doc/refman/5.6/en/innodb-implicit-commit.html says:

  If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

That means if your session disconnects for any reason, either by choice, or else because an error occurs like the network connection fails, etc., then a transaction in progress is rolled back.

If you think about it, this is the only appropriate thing for the RDBMS to do. It doesn't know if you had completed the work for your transaction only partially. For example, you might have debited one bank account, and you intended to credit another bank account to reflect a funds transfer. If the debit has occurred but the credit has not, and the connection is lost, MySQL has no way of completing the transaction or even of knowing what you wanted to do next. So it has to roll back all work so far.


insert inserted id to another table Here's the scenario:

create table a (
 id serial primary key,
 val text
);

create table b (
 id serial primary key,
 a_id integer references a(id)
);

create rule a_inserted as on insert to a do also insert into b (a_id) values (new.id);

I'm trying to create a record in b referencing to a on insertion to a table, but what I get is that new.id is null, as it's automatically generated from sequence. I also tried it with trigger, that is launched AFTER insert FOR EACH ROW on a table, but result was the same. Any way to work this out?

You can utilize the returning keyword which will return the value of the created serial column. If you wrap you Insert in a With expression, you can then access the returned id and insert into the second table.

WITH getval(id) as
    (INSERT INTO table_a (some_col) VALUES (some_val) RETURNING id)
INSERT into table_b (id) SELECT id from getval;

where id is the value of the serial resulting from the insert into table a. More in the docs, though it is a somewhat hidden feature.

Note, you can also use returning * to return the entire updated row, not just the serial column, should you need it.


Replication Monitor reports little to no latency, but my tracer token is taking hours to go from Distributor to Subscriber I sent a tracer token through a replication job that was reporting about 2 seconds of latency about two and a half hours ago.  I sent the token through because I noticed a row that was inserted on a published table had not been sent to the replicated table.

I'm not sure what this indicates and would appreciate any help in troubleshooting it.  Thank you.

UPDATE:
My replication publication hasabout 30 tables. Most of these tables are being replicated as expected. When I run a trace I can see their sp_MSupd_dboXXXXX procedures running, I can verify that the data is in sync.

However, for several tables, this is not happening. When I looked in the database the table was being replicated to, it did not even have its own sp_MSins, upd, and del procedures.

When I run sp_browsereplcmds and provide its publication_database_id and article_id, I can see that the command is waiting to be run.

For example, it might say {CALL sp_MSins_dboTableName (fields here)}. It will sit there for a while, then disappear.

My first assumption was that it wasn't being applied because the procedure didn't exist. So I wrote my own sp_MSins_ proc to confirm this (@c1 datatype, @c2 datatype etc...) and tested it to be sure it worked.

But even then, when I do an insert, the command sits in the repl_commands table for a while, then disappears, and no row ever hits my table.

I'm getting pretty lost here. Can anyone help? Thanks!

Some more detail:

The distribution DB lives on the subscriber. There are several publications from the same database on the publisher to the same database lon the subscriber (three to be exact, two work fine, one does not).

On two of the publications, a tracer token is processed within about 4 seconds. On the one I'm having trouble with, the tracer token hangs in limbo indefinitely.

For my money, tracer tokens are a really good way to monitor latency. I don't know what the latency metric in replication monitor is measuring, but I've personally found it to be unreliable. For my environment, I post a tracer token through a SQL Agent job and then monitor the token's progress by looking at the token tables in the distribution database.


SQL Server job stuck even after restart I have a SQL Server Agent job stuck in "In Progress". It's a FULL backup that usually takes 30 minutes, but it's taking 7 hours and counting. I've restarted the Agent and then the whole OS and it still says "In Progress".

Is there any way to stop that?

If I run select * from sys.dm_exec_requests, I get 2 rows in "running" status, one says "SELECT", the other one "DBCC TABLE CHECK". And indeed the first step of the job is to run a Database Integrity check using Ola Hallengren's scripts.

Version:

  Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)
  Jul  5 2017 23:41:17   Copyright (c) Microsoft Corporation  Standard
  Edition (64-bit) on Windows Server 2016 Datacenter 6.3  (Build
  14393: ) (Hypervisor)

Not using the GUID but using the T-SQL procedure:

sp_stop_job

Could help resolve issues like the one you are having.

Example:

exec msdb.dbo.sp_stop_job @job_name = 'enterjobnamehere'

Checking the current running queries for any queries started by the agent and killing these could also help.

An example of adapting the script to find running queries started by the agent service:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_Sessions s
on req.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE [Program_Name] like 'SQLAgent%';


How data can be transferred from Sybase SQL Anywhere to SQL Server One of our client shared .db IMS SQL Anywhere database backup file. Our mission is to transfer data from that backup file to SQL Server.

My questions is

Which software I need to download or it can be done through ODBC\OLEDB without installing Sybase Server?

Method 1:

You can create a Linked server at SQL server pointing to SYBASE and then create a SQL server agent job that does the task of refreshing the data from SYBASE to SQL with help of OPENQUERY. This is better explained in the article here.

Method 2:

If you are comfortable at creating SSIS package, then you can create one, in case method 1 cannot be used with security point in concern, to fetch the data from SYBASE to SQL server. You can follow the steps as explained in Extracting data from Sybase SQL Anywhere using SSIS through ODBC.

Method 3:

As you said, you have the access to client server, then you may give a try with option of extracting the data from sybase DB tables with help of writing few line codes using bcp utility into csv or text files on the sybase server or copy that onto SQL server. Once the data has been exported onto SQL server you may import with various option available to import the same text/csv file into SQL server.


Storing small array of many possibilities Let's say I need to store the text of a series of books, line per line in a database, and present them in a (CMS-based) website. I thought the correct setup would be:

a 'library' table: containing the ISBN-type info (per book-ID: author, title, year, maybe blurb) plus an array with the empty pages of that book.
a 'lines-of-text' table for each book.

The 'array' question here is different from the 'Storing integers for efficient select' and various similar questions (like 'match elements to dates of week') in that the set of (integer) options is not limited... it's sparse in a large and unbounded range AND because the entire use is not within SQL, but within PHP.

I now have my doubts:
[1] Should I put all the book-lines-tables together in one single table (adding a column containing the book-ID)? Or does it not matter really? There's cleanliness/readability, there's performance (once you've chosen a 'book', you're not going to switch --- so you'd be searching in a smaller table if there is many, is my idea). [Further info: I have to add (and occasionally delete) books. I also need to assign an owner to each book (that can edit the 'lines'), but that is outside the scope of Mysql I think.]

[2] How should I save the 'empty pages' array of each book?
--a Convert a php array to string (serialize/unserialize) and store as varchar (or tinytext)? Or
--b Make another table of books:empty-pages matches? Or
--c Use SET('1','2','3', ..., '999') to foresee all possible empty pages [and swear/blaspheme/explode violently the day I have to add a 1001-page-long book with an empty 1000th page]? Or
--d Calculate on-the-fly from the database?

I guess the 'SET'-approach protects against impossible arrays, from badly-intentioned 'owners' of books (which is rather improbable in my specific case); but sparse it's a lot of overhead and can easily break by extra-long entry. I guess on-the-fly is needlessly slow as it's an unchanging list (calculate once per 'book', not once every 'page'-request!).  I have the feeling solution (a) is the best given the circumstance, as I then put it in PHP which will be what's used to create the whole structure and linking (in HTML).

The MySQL SET datatype is a disaster - as are any type of array datatypes (supported unfortunately by many RDBMSs). Check out my answer to another question here. As I note, MySQLs SET is a breach of Codd's second rule - no repeating group datatypes. It is also completely non-portable should you wish to change RDBMS.

If you wish to store that data this way is to have a BOOK table with book_id, name, author, country_of_publication, subject.... Then have a CHAPTER table with book_id, chapter_id, chapter_name, chapter_sub_subject.... and then a PAGE chapter with book_id, chapter_id, page_id, pager_number, ... images... other stuff... then LINE book_id, chapter_id, page_id, line_id, line_text...

This conforms to the relational model. I'm not clear on why you'd want to do this. What's wrong with TEXT datatype? Also, you might want to look into FTS (Full Text Search) - MySQL's native FTS (available on both MyISAM and InnoDB now). There's also Lucene, Sphinx and Spider (there may well be others - Google is your friend).

[EDIT in response to OP's comment]

  I had the feeling it was wrong to make identically-structured tables
  for each book

The whole point is that you should have identically structured tables for all books - you make the structure general enough to cope with (virtually) every possibility.

  but I also though it would be slower to search through larger table
  (containing ALL sentences not just 1book's worth) to (re)construct a
  page, as well as doing an extra WHERE book_id = 'N' match...

Use indexes on book_id, chapter_id and maybe even page_id. Also, if you're going to be searching in your entire corpus for a particular word, then condsider Full Text Indexing.

  Presumably this extra overhead is near-identical to me first sending
  mysql to the single-book table. This leaves me with the query of how
  to store a small array for use in PHP --- I'm leaning towards VARCHAR
  with (un)serialize.

Be aware that the maximum size of VARHCHAR is 65535 bytes - some of James Joyce's sentences might go beyond that :-), but it's unlikely that even Joyce went beyond the 4GB of a TEXT datatype :-)

I Googled "storing books in MySQL" and came up with this which you may find of interest, and interestingly up popped this on the first page of the same search.


Finding hash match aggregate After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate.

Blocking/non-blocking aggregate operators
The blocking nature of aggregates - Rob Farley
Hash Aggregate-Craig Freedman

I have a database that has over 200 tables which has been created years back. I am trying to find all queries with group by that is currently working with hash match aggregate operator. One possibility I found is using dmv like below. But I don't know how to filter it to list only queries with hash match aggregate operators. How to achieve this? Also, on a big picture level, what are the other options to get this information other than following dmv?

SELECT cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.TEXT LIKE '%GROUP%'

It is not possible to directly connect part of the query text (e.g. GROUP BY) with a specific operation in the final execution plan.

You can write a query to find plans that:

Contain a Hash Match Aggregate; and
The query text contains a GROUP BY clause

...which is not quite the same thing, since this will find plans where the grouping logic was implemented using a Stream Aggregate, replaced with another operation, or even removed entirely - but happens to also contain a Hash Match Aggregate for some other reason.

For example:

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEQP.objectid,
    DEQP.query_plan,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    1 = DEQP.query_plan.exist(
        '//RelOp[
            @PhysicalOp = "Hash Match"
            and @LogicalOp = ("Aggregate","Partial Aggregate","Flow Distinct")]')
    AND DEST.[text] LIKE N'%GROUP BY%';

This might miss some GROUP BY queries if they have a different amount (or type) of white space between GROUP and BY than expected by the query. Maybe replacing all white space with spaces and collapsing contiguous ones into a single space could be done before the LIKE or use SQLCLR and regex instead.

You would need to inspect the results by hand to determine if the Hash Match Aggregate correlates directly to the GROUP BY clause or not. The query above could be extended, perhaps, to check the plan HashKeysBuild element against the grouping columns, but this would be hard to get right, due to optimizer operations.


ER Diagram | Group, Post, User relationship I need to design an ER diagram, but I am confused in a section, more specifically how to relate the post with the groups, because I need that post contains the information of in which group I publish and that user I publish it, I have two diagrams one where the relation of post, group and user are direct, and other where the post is related by means of the table generated of the relation many to many between group and user. My question is which of these two is more recommended.
Diagram A

Diagram B

the bridge table group_user between User and group is the right idea, when there is a many to many relationship
But users should be connected directly to posts like in the first image, because only pone user can have written the post-
Conclusion make a new mixed version


sys.dm_db_partition_stats - incorrect row count for InMemory tables I have a problem with sys.dm_db_partition_stats DMV. When having InMemory tables (with hash indexes, columnstores, classical b-tree) and trying to get row count via this query there is 0 displayed for all InMemory ones:

WITH cte AS
(
SELECT t.name as table_name,
       SUM(s.used_page_count) AS used_pages_count,
       SUM(CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
           END) AS pages,
       MAX(s.row_count) AS row_count
  FROM sys.dm_db_partition_stats s
  INNER JOIN sys.tables t
          ON s.object_id = t.object_id
  INNER JOIN sys.indexes i
          ON i.object_id = t.object_id
         AND s.index_id = i.index_id
GROUP BY t.name
)
SELECT cte.table_name,
       CAST((cte.pages * 8.)/1024 AS DECIMAL(10,3)) AS table_size_mb,
       CAST(((CASE WHEN cte.used_pages_count > cte.pages
                THEN cte.used_pages_count - cte.pages
                ELSE 0
              END) * 8./1024) AS DECIMAL(10,3)) AS indexes_mb,
       cte.row_count
  FROM cte
ORDER BY 2 DESC
;

I know that space used by table and indexes for InMemory table cannot be get by this query, but the row count should work.

When you execute just:

SELECT * FROM sys.dm_db_partition_stats

you can see that in row_count column is non-zero row count for all InMemory tables. Is there some bug in Microsoft SQL Server (I tried this on SQL2016SP1 and older versions)? Or am I doing something wrong? All InMemory tables I had created are not empty - so SELECT * displayed proper row count.

Another point - when you add filtering WHERE clause with table name or LIKE which filters InMemory tables only, non zero row count is also returned...

Details about In-Memory Tables cannot always be seen using the "normal" dynamic management views, or DMVs, such as sys.dm_db_partition_stats.

Instead, you'll need to use the In-Memory views detailed on MSDN.

For instance, this page talks about how to calculate row and table size for In-Memory tables.

MSSQLTips has a decent starting list of In-Memory related DMVs.


SQLCMD command is not able to insert accents I am trying to run sqlcmd.exe in order to setup a new database from command line. I am using SQL SERVER Express 2012 on Windows 7 64 bits.

Here's the command I use:

SQLCMD -S .\MSSQLSERVER08 -V 17 -E -i %~dp0\aqualogyDB.sql -o %~dp0\databaseCreationLog.log

And here's a pieceof the sql file creation script:

    CREATE DATABASE aqualogy
    COLLATE Modern_Spanish_CI_AS
    WITH TRUSTWORTHY ON, DB_CHAINING ON;
    GO
    use aqualogy
    GO
    CREATE TABLE [dbo].[BaseLayers] (
    [Code] nchar(100) NOT NULL ,
    [Geometry] nvarchar(MAX) NOT NULL ,
    [IsActive] bit NOT NULL DEFAULT ((1))
    )

    EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'Capas de     cartografía base de la aplicaicón. Consideramos en Galia Móvil la cartografía(...)'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'BaseLayers'

Well, please check that there are some accents on the words; which is the table's description . The database is created with no problems. 'Collate' is understood by the script, as you can see in the attached screenshot. Despite of this, accents are not properly shown when examining the table.

I'd really appreciate any help. Thank you very much.

[Edit]: Hi all. Changing the SQL file encoding using Notepad++ worked fine! Thank you very much for you help: I learned something interesting with this problem!

Another option, one which I've just learned, comes from the sqlcmd documentation. You need to set the codepage for sqlcmd to match that of the file encoding. In the case of UTF-8, the codepage is 65001 so you'd want:

SQLCMD -S .\MSSQLSERVER08 -V 17 -E -i %~dp0\aqualogyDB.sql -o %~dp0\databaseCreationLog.log
-f 65001


InnoDB & Isolation Levels - Aren't repeatable reads a bad thing? I was reading about InnoDB's isolation levels and it makes sense for the most part, but what I don't get is why are unrepeatable reads a bad thing? Shouldn't it be the other way around?

For examples:

So say we had a stock column for selling products and each time someone bought an item we would take 1 item from the column, with Repeatable read or serializable isolation levels wouldn't this corrupt the data integrity?

For example:

TX A: start transaction;
TX B: set session transaction isolation level repeatable read;
TX B: start transaction;
TX A: select stock from products;               -- val = 8
TX B: select stock from products;               -- val = 8
TX A: update products set stock = stock - 1;    -- val = 7
TX B: select stock from products;               -- val = 8
TX A: commit
TX B: select stock from products;               -- val = 8, incorrect!
TX B: commit;
TX B: select stock from products;               -- val = 7

Unless it uses the CORRECT value when running an update?

InnoDB isolation levels are not "bad" or "good", it depends on what your application requires in terms of isolation.

In your example, by using repeatable read, you make sure that once the first select has been done, no other transaction can modify the state of the database, from the point of view of that transaction. In other words, it is as if you had run "START TRANSACTION WITH CONSISTENT SNAPSHOT" on before your 5th line. This may be required in some cases, and let me put you an example:

TX A: START TRANSACTION;
TX A: SELECT * FROM products WHERE stock BETWEEN 4 AND 6; -- I see rows 4 and 6 only,
                                                          --  and I want to update those only
TX B: START TRANSACTION;
TX B: INSERT INTO products (stock) VALUES (5);

TX A: UPDATE products SET stock = 3 WHERE stock BETWEEN 4 AND 6;
TX A: COMMIT; -- race condition
TX B: COMMIT;

By setting InnoDB's REPEATABLE READ, phantom reads disappear, because TX B will be locked at the insert until A commits. However, if we are doing READ COMMITTED, we will get different outputs depending if A or B commits first, something that violates the ACID properties, and that our application may not expect.

In particular, STATEMENT-based replication requires no phantom reads, that is why READ COMMITTED is the default level, and has even a stricted behaviour than READ COMMITTED in other databases (where serializable is needed to get the InnoDB behaviour). If the first select was an update, executing those transactions independently would lead to different results (drifting the slave). Please note that transactions are written to the binlog atomically and serially.

I agree with you that that may not be needed in most applications, because you will handle those cases at application level. If that is the case, use ROW-based replication and set the default transaction isolation level to READ COMMITTED. You will also get in most cases better concurrency.


Select all rows with same ID when 1 column meets criteria I need help with a SQL query.
My problem is better explained with some examples.

Say I have my data like this pulling from two tables, ORDR and RDRO. The first table (ORDR) contains all the data about the order except for the items. The second table (RDRO) contains all the item information for an order. Both tables can be joined using [docNum].

T0.docNum | T1.itemCode
123         EDS100
123         DDS200
123         DDS500

124         LLS220
124         LDS254

125         FGD450
125         LDS552
125         DDS200
125         EDD200

I want to return all rows when 1 item number meets the criteria.

Say in this case I want all the rows of the order when one row is equal to DDS200 would give this result:

T0.docNum | T1.itemCode
123         EDS100
123         DDS200    -- match
123         DDS500

125         FGD450
125         LDS552
125         DDS200    -- match
125         EDD200

I'm sure this is trivial, I have tried the following:

SELECT T0.docNum, T1.itemCode
FROM ordr T0  INNER JOIN rdro T1 ON T0.docNum = T1.docNum
WHERE  T1.itemCode like 'DDS200';

this only returns the rows that meet the criteria, not all the rows of the order as shown above.

Also, please note I'm on SQL server.

Thanks for your help

There are a few ways that you can achieve this...

You can use a subquery to return the docNum of the rows that match the criteria from RDRO and then use an outer query to get the rest; like so:

SELECT r.docNum, r.itemCode
  FROM (SELECT DISTINCT docNum
          FROM rdro
         WHERE itemCode = 'DDS200') AS a
  JOIN rdro AS r
    ON r.docNum = a.docNum;

Or, as pointed out by Aaron, you can also use a WHERE EXISTS to achieve the same result:

SELECT r.docNum, r.itemCode
  FROM rdro as r
 WHERE EXISTS (
         SELECT *
           FROM rdro
          WHERE docNum = r.docNum
            AND itemCode = 'DDS200');

Or you can use a WHERE...IN:

SELECT r.docNum, r.itemCode
  FROM rdro as r
 WHERE r.docNum IN (
         SELECT docNum
           FROM rdro
          WHERE itemCode = 'DDS200');

Or you could even do it straight in the JOIN:

SELECT r.docNum, r.itemCode
  FROM rdro as r
  JOIN rdro AS o
    ON r.docNum = o.docNum
   AND o.itemCode = 'DDS200';

Heck, for completeness lets even throw in a CTE version:

 WITH docs
 AS
 (SELECT docNum FROM RDRO WHERE itemCode = 'DDS200')
  SELECT r.docNum, r.itemCode
    FROM RDRO AS r
    JOIN docs AS d
      ON r.docNum = d.docNum;

Try each one and review the execution plans that are generated for them before choosing which one to go for. Normally if two query plans are the same I choose the query that is easier to maintain.

Example of execution and results can be found on sqlfiddle.


how to convert integer zipcodes My question is how do you put my zip code 07601 as an integer? I've tried to google several times this question and never got an answer. The reason I'm doing this is to because its important information for something I need to fill out

It boils down to database design esp. choosing data types can affect performance as well - storing zipcodes as integers requires more thoughts ! Eg. non US countries like Canada will have letters in zip code. If you store zip codes as integers, when your company does business in Canada, you will have to change the datatype.

I would select integer datatype where any sort of arithmetic (Calculations) comes in play. Adding or subtracting zipcodes is of no value !

Also, US zipcodes have leading zeroes e.g. 08874. For integers, a leading zero is of no value.

I would suggest you store zipcode as varchar(10) and if all your zipcodes are numbers, you can use cast or convert to fill whatever you are filling out.

From msdn :

  Character fields can store text information that is not used in mathematical calculations, such as names, addresses, and numbers. For example, phone numbers or zip codes, though they include mostly numbers, are actually best stored as character values.

Also, check out @srutzky's answer 9 digit zip codes are reporting LEN of 12


Is there a way to find all Logins, and all database users this login have? I'm trying to join, every login and permissions tables. Looking over the internet but I just can't find something to list like:

LOGIN  |  USER  | DATABASE_THE_USER_HAS_ACCESS

This is because I would like to find all users with not a single permission on any database ( Login with no users ).

sys.server_principals AS log
LEFT OUTER JOIN sys.syslanguages AS l ON l.name = log.default_language_name
LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = log.principal_id and sp.type = N'COSQ'
LEFT OUTER JOIN sys.credentials AS c ON c.credential_id = log.credential_id
LEFT OUTER JOIN master.sys.certificates AS cert ON cert.sid = log.sid
LEFT OUTER JOIN master.sys.asymmetric_keys AS ak ON ak.sid = log.sid
LEFT OUTER JOIN sys.sql_logins AS sqllog ON sqllog.principal_id = log.principal_id
WHERE

I'm looking at these tables but I can't find a pattern to join them,.

First, go grab sp_foreachdb from the First Responder Kit (background here):

SQL-Server-First-Responder-Kit/sp_foreachdb.sql

Then you can collect all the database users here:

CREATE TABLE #DBUsers
(
  DBName sysname,
  DBUserName sysname,
  DBPrincipalID int,
  SID varbinary(85),
  LoginName nvarchar(128),
  LoginPrincipalID int
);

EXEC master.dbo.sp_foreachdb N'INSERT #DBUsers
SELECT ''?'',d.name, d.principal_id, d.sid, s.name, s.principal_id
FROM ?.sys.database_principals AS d
LEFT OUTER JOIN sys.server_principals AS s
ON d.sid = s.sid
WHERE d.principal_id > 4
  AND d.type <> ''R''
  AND d.name NOT LIKE N''##%##'';';

Now for example to find any server-level login that doesn't have an associated user in any database (note this will currently include those in elevated roles, like sysadmin):

SELECT * FROM sys.server_principals AS p
WHERE type NOT IN ('S','R','C')
  AND name NOT LIKE N'##%##'
  AND name NOT LIKE N'NT %\%'
  AND NOT EXISTS
  (
    SELECT 1 FROM #DBUsers
    WHERE SID = p.sid
  );

Note that you might have to dig a bit deeper, because a user might have been added to a database to explicitly deny access. But that should be a good start for the normal case.


Use result from table_schema query as  in select * from Is it possible in PostgreSQL to use the result of the query below as table name in a SELECT * FROM .

SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'seam_event_%'
ORDER BY table_name DESC
LIMIT limit 1

The current database is using table partitioning. 90% of the time we only search the last two partition tables. Therefor we would like to create a view with the records of the last 2 partition tables.

I tried the following:

SELECT * FROM (SELECT table_name
    FROM information_schema.tables
    WHERE table_name LIKE 'seam_event_%'
    ORDER BY table_name DESC
    LIMIT 1) AS a
UNION
SELECT * FROM (SELECT table_name
    FROM information_schema.tables
    WHERE table_name LIKE 'seam_event_%'
    ORDER BY table_name DESC
    LIMIT 1 OFFSET 1) AS b

Unfortunatly, this is returning:

table_name
______________
seam_event_74
seam_event_75

Bottom line, which is the best way to query the last two partition tables and how to achieve this?

Thank you in advance.

Regards,

Bas

---------- EDIT ----------

I've tried to create a functions that will return the results of the last two child tables. But i keep getting a syntax error.

Function:

DROP FUNCTION select_from_table();

CREATE OR REPLACE FUNCTION select_from_table()
  RETURNS SETOF seam_event AS
$BODY$
DECLARE
    TABLE_VAR1 varchar;
    TABLE_VAR2 varchar;
BEGIN
    select table_name from information_schema.tables where table_name like 'seam_event_%' order by table_name desc limit 1 INTO TABLE_VAR1;
    select table_name from information_schema.tables where table_name like 'seam_event_%' order by table_name desc limit 1 offset 1 INTO TABLE_VAR2;

    RETURN QUERY EXECUTE 'SELECT * FROM ' || TABLE_VAR1;
END;
$BODY$
  LANGUAGE plpgsql
  COST 100;

SELECT * FROM select_from_table();

ERROR:

ERROR:  syntax error at or near "'SELECT * FROM '"
LINE 1:  EXECUTE 'SELECT * FROM ' ||  $1
                 ^
QUERY:   EXECUTE 'SELECT * FROM ' ||  $1
CONTEXT:  SQL statement in PL/PgSQL function "select_from_table" near line 8

********** Error **********

ERROR: syntax error at or near "'SELECT * FROM '"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "select_from_table" near line 8

You can't do this with ordinary SQL, at least in PostgreSQL. You need to use PL/PgSQL to generate dynamic SQL and run it with EXECUTE. The

RETURN QUERY EXECUTE format('SELECT ... FROM %I WHERE $1, $2', tablename) USING param1, param2;

construct is useful.

See executing dynamic statements.


How to get list of databases that are mapped to a login? I was trying to list all databases that are mapped to a login in sql server using T-SQL without creating a temp or any type of table. Any help will be appreciated.

The simplest way is inside SQL Server Management Studio (SSMS). Go to the  Security tab, right click the user name, and select the option User Mapping. Wait a bit, and a list of all DBs on the server comes up, with a check mark next to the ones that the user has access to.

If you also want to know what roles the user has in each of the DBs, you can click on the database name, and the database role membership will show in the bottom window.


How to delete a procedure which name is ambigous? I am using informix...

I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a

DROP PROCEDURE myProc;

then i get a error message

ERROR: Routine (add_adr_trigger_row) ambiguous - more than one
routine resolves to given signature.
Error Code: -9700

How can i drop the procedures?

This happens when you have 2 or more procedures, with the same name, but with different numbers of input parameters.

For example, you have created 2 procedures:

CREATE PROCEDURE myProc(param1)
...
CREATE PROCEDURE myProc(param1, param2)
...

To delete the second one, you have 2 options:

The easy one:

DROP PROCEDURE myProc(param1, param2);

The hard one:

dbaccess DB -
select procname, procid, numargs from sysprocedures where procname like 'myProc';
procname  myProc
procid    1
numargs   1

procname  myProc
procid    2
**numargs   2**

UPDATE sysprocedures SET procname='myProcOLD' WHERE procid=2;
DROP PROCEDURE myProcOLD;

Even if the first method is dead simple, the first time I got called in the middle of the night for this same problem, I've chosen the second. My bad ...


Performance impact of waiting on a service broker message I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs).  These messages are picked up by yet another client app and then processed.  The way the messages are picked up is that the app issues the following SQL statement (pseudo code):

LOOP {
    WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM SB_ReceiveQ)
    ProcessMessage
}

So basically the code just blocks until a message is received.  This all works fine.

My question is about the implication of issuing a WAITFOR (RECEIVE... command which hangs on to some service broker based resource basically forever.  Are there any performance issues associated with this pattern that I should know about?

For reference, this is SQL Server 2005.

You are blocking a worker in SQL Server and workers are limited, subject to max worker threads. This means there should not be thousands of requests blocked in WAITFOR(RECEIVE...) or you'll starve the server of workers.

But the first question that comes to mind is Why no leverage Service Broker Activation? This way you wouldn't be waiting all the time but only when activated because there are messages to receive.


SQL Server Full-text crawl log location This might be a silly question, but I can't for the life of me figure out where the crawl logs for my SQL Server 2012 instance are. TechNet says to look under %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG for 2008, but the LOG folder does not exist under %ProgramFiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL.

I have upwards of 500,000 failed records after my last FTS index population and I need to know what the issue is.

You should be able to find the full text log location with the following code:

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'

I don't think it's possible to split them away from the normal SQL Error log as this connect suggests.