Showing posts with label helloi. Show all posts
Showing posts with label helloi. Show all posts

Friday, March 23, 2012

How many synchronizations?

Hello:
I want to know how many synchronizations have been made at all. Is there any
system table or something like that where I can find this number?
Thanks a lot
Juan
No, this information is logged in the ms*_history tables in your
distribution database, but is deleted when each subscription is dropped.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Juan" <juan@.tata.net> wrote in message
news:e3RUZJoHFHA.3780@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I want to know how many synchronizations have been made at all. Is there
any
> system table or something like that where I can find this number?
> Thanks a lot
> Juan
>

Monday, March 19, 2012

How many columns is ok?

Hello!

I have a database table containing about 40 columns, mostly bit data type, some nvarchar and smallint columns...

Is it ok to have so many columns in one table and how does the number of columns affect database perfomance? Wouldn't it be better to split this table?

Thank You!
JannoPossible? Yes. OK. Not really.

My guess is that you need to work on normalizing your database. For example, all those bit datatypes might be better served off in their own table, with a vertical not horizontal, design. This would much more easily allow you to add traits (bits) in the future.

What is the nature of the table, and what is being stored in all of those BIT columns?|||Pjmcb,

Basically it's a databese for flat rental site. Those BIT columns are used to store True/False type values.

In case I'm going to store those values verticaly in a separate table... could you please explain me how does it exactly works - I'm a SQL Server newbie...

Ads Table structure:
------------
AdIDint
AdTypesmallint
UserIDnvarchar
Activebit

AdDetails Table structure:
------------
AdIDint
Areasmallint
StreetNamenvarchar
Neighbourhoodnvarchar
Citynvarchar50
PostCodenvarchar15
AvailableFromdatetime
Rentmoney8
MinimumStaysmallint
Depositmoney8
PropertyTypenvarchar
Floorsmallint2
Parkingbit1
AdditionalCostmoney
Bathroomssmallint2
Bedroomssmallint2
Gasbit1
Cablebit1
TVbit1
Phonebit1
Closetbit1
Furnishedbit1
AirConditioningbit1
Bathbit1
Fireplacebit1
Balconybit1
Elevatorbit1
Washerbit1
KitchenAccessbit1
Storagebit1
Gardenbit1
Poolbit1
Gymnbit1|||You might have a second table, and call it, say Traits.

In this table, you might include each of the bit traits, along with an ID for each trait.

Then, you could have a join table to link each particular trait with each type of apartment. You would only have records in the join table for those traits that applied to a specific apartment.

You might have a second table for manetary items, with a similar join table, and a third with rooms or similar information.

With your join tables for these last two items, you could include additional information. For example, you might have:

Rooms
--
RoomID
RoomName
RoomDesc

(which might contain: Bedroom; Bathroom; Closets; Laundry Room; Kitchen; Living Room)

AdRooms
--
AdID
RoomID
RoomCount

Obviously, I'm throwing this out off the top of my head. But when you need to run an ad featuring an apartment with a SunRoom, all you need to do is add a record to your Rooms, table, and assign that value to your new apartment. You don't have to go in and change your database design.

Your retrieval of data might be a bit more complicated (actually, I would argue that ultimately, once you get comfortable with SQL, it will be easier), but growing the application will be much easier.|||I completely agree in principle with normalizing this, but it does depend on how the data is being used. if every single ad always lists each of those categories with a yes/no, it may make sense to leave it denormalized. bits don't hurt you that much as far as performance, the table is still relatively narrow. normalizing does make maintenance much better, as you point out, when you want to add additional attributes. it also makes the query more complex, although not necessarily slower. so those are just some tradeoffs I would keep in mind.

cs

Wednesday, March 7, 2012

How is represented the Updated Table in a Trigger?

Hello

I am a newbie creating triggers and I would like to know what is the name of the updated logical table. That is, since the are identifiers like "inserted" and "deleted" that represent the inserted and deleted rows, respectivaly, I presumed that "updated" existed - but it does not.

I have a table with a column named "UpdatedTimeStamp" which I would like to update anytime the other columns are modified. I intended to create the following trigger:

create trigger myTrigger
on myTable
after update
as

update updated set UpdateTimeStamp=GetDate()

Of course, "updated" is not a valid identifier that represents the updated row.

Please, help me in creating this trigger. What is the correct way of doing a trigger like this?

Thanks a lot in advanced.Do you have books online?

Its inerted and deleted|||I know, but as I stated in my previous post, I do not know how to update a row which has just been updated. For example: I have a table with 2 columns, A and B -where B is a datetime column. I would like that when A has just been updated (not inserted or deleted), B would have the datetime value when that happened. Something like:

update [updated] set B=GetDate()

Of course, [updated] is not valid identifier for a logical table. Can you please help me in how to accomplish this scenary?|||The way you tell a row or rows have been updated is that both the inserted and deleted tables contain data. On an insert, the data only appears in the inserted table. On a delete, the data only appears in the deleted table.

Now with this information, try and put together a trigger for an update, and come back with your results. Also, you might want to include some schema information, so we can see the table or tables you are trying to maintain.

And remember, you want the trigger to update all rows updated, not just one of them. Think about set based operations.|||Do you have books online?

Its inerted :shocked: and deleted

How long have you been working in the bomb disposal unit at your office?

:D

hmscott|||I came up with the following solution:

suppose the existence of a table called myTable, having the columns:
- ID numeric (PK)
- Value varchar(50)
- UpdateTimeStamp datetime

Now, my trigger is

create trigger myTrigger
on myTable
after update
as

if update(Value)
begin
update myTable
set
UpdateTimeStamp=GetDate()
from myTable
inner join inserted i on i.ID=myTable.ID
inner join deleted d on d.ID=myTable.ID
end

Is this code ok?

If I open the table and modify a row, the UpdateTimeStamp column is updated as expected. However, when I update only some rows with a query, for a reason I failed to understand, all the rows get their UpdateTimeStamp field updated.

What could be wrong?|||Personally, I would not bother with the UPDATE() function. It can be misleading, especially when dealing with interfaces that my issue spurious update statements, as it merely indicates whether a column was part of an update statement, and does not indicate whether the value actually changed.

For instance, this code:
update A set Value = 5 where Value = 5
...would result in the UPDATE() function indicating that a set of records were updated, whereas in reality they have the same value as before.

If you really want to check whether the value has changed, try something like this:
begin
update myTable
set UpdateTimeStamp=GetDate()
from myTable
inner join inserted i on i.ID=myTable.ID
inner join deleted d on d.ID=myTable.ID
where inserted.Value <> deleted.Value
or inserted.Value is null and deleted.Value is not null
or inserted.Value is not null and deleted.Value is null
...or use the BINARY_CHECKSUM() function to quickly compare multiple columns.

Friday, February 24, 2012

How I join in this case?

Hello
I would like to join Stats.ClientID with Clients.ClientID using an inner
join to get hold of Clients.Client
but I cant figure out how....... (thanks to Rebecca for providing help
earlier)
SELECT
MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121 ) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
, Stats.ClientID
) vwResultsLike this perhaps:
SELECT
MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,C.ClientID
,Total AS Visitors
FROM
(...
... ) vwResults
JOIN Clients AS C
ON vwResults.client_id = C.client_id ;
David Portas
SQL Server MVP
--|||Try this:
SELECT
Client
,MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
Clients.Client,
CONVERT(DATETIME,CONVERT(CHAR(7),stat.Created,121) + '-01 23:59:59',121
) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats, Client
WHERE
Stats.ClientID = Clients.ClientID
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
, Stats.ClientID
, Clients.Client
) vwResults|||ahh thx...
doh, more coffee for me
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128499953.118747.188950@.g44g2000cwa.googlegroups.com...
> Like this perhaps:
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,C.ClientID
> ,Total AS Visitors
> FROM
> (...
> ... ) vwResults
> JOIN Clients AS C
> ON vwResults.client_id = C.client_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||If you want the end date to have the last part of the day,
Take off the time part from the convert, and take away 3ms from the EndDate.
This will give you the full scope of the month and not miss all but 3ms of
the StartDay.
SELECT
MonthAdded AS StartDate
,DATEADD(ms,-3 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121 ) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121)
, Stats.ClientID
) vwResults
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23la1mJYyFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hello
> I would like to join Stats.ClientID with Clients.ClientID using an inner
> join to get hold of Clients.Client
> but I cant figure out how....... (thanks to Rebecca for providing help
> earlier)
>
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,ClientID
> ,Total AS Visitors
> FROM
> (
> SELECT
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121 )
AS
> MonthAdded
> ,Stats.ClientID
> ,COUNT(*) AS Total
> FROM
> Stats
> GROUP BY
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
> , Stats.ClientID
> ) vwResults
>|||Hi again Rebecca
I got to work now perfectly, but now I need to add another view.... that
shows for "each w" (and works even next year) :S
Tried some but it left a mess......
is it possible to do such grouping with some modifications?
so startdate is (for this w as an example)
2005-10-03 2005-10-09
TIA
/Lasse
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4343a346$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you want the end date to have the last part of the day,
> Take off the time part from the convert, and take away 3ms from the
EndDate.
> This will give you the full scope of the month and not miss all but 3ms of
> the StartDay.
>
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(ms,-3 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,ClientID
> ,Total AS Visitors
> FROM
> (
> SELECT
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121 ) AS
> MonthAdded
> ,Stats.ClientID
> ,COUNT(*) AS Total
> FROM
> Stats
> GROUP BY
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121)
> , Stats.ClientID
> ) vwResults
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23la1mJYyFHA.3236@.TK2MSFTNGP14.phx.gbl...
23:59:59',121 )
> AS
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You have been toldthis several times in this
newsgroup.
Instead of computing things like this, why don't you build a reporting
calendar table with date ranges? Instead of storing temporal data in
strings, why don't you use temproal data types? Oh, the standard
syntax in SQL is CAST(), not CONVERT(). The prefix "vw" is eitehr a
short hand for "VolksWagen" or a violationof ISO-11179 rules for naming
data elements.|||easier with "testing-code" i guess
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-03')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-03')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-10-03 2005-10-09 A 5
2005-10-10 2005-10-16 A 4
2005-10-17 2005-10-23 B 3
or if above is too complicated/impossible:
Year (?) Month (?) Person Count
2005 40 A 5
2005 41 A 4
2005 40 B 3
*/
DROP TABLE #Test
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1128607316.078911.121310@.z14g2000cwz.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. You have been toldthis several times in this
> newsgroup.
> Instead of computing things like this, why don't you build a reporting
> calendar table with date ranges? Instead of storing temporal data in
> strings, why don't you use temproal data types? Oh, the standard
> syntax in SQL is CAST(), not CONVERT(). The prefix "vw" is eitehr a
> short hand for "VolksWagen" or a violationof ISO-11179 rules for naming
> data elements.
>|||I don't understand why your first desired result has two rows for the
same month. Going by your second example this seems to be a mistake. I
think you want to GROUP BY DATEDIFF(MONTH, '/* some fixed date */,
datecreated). For the dates join to a Calendar table.
As posted your schema and data makes no sense. Why the duplicate rows?
You need a unique constraint on (person,datecreated).
David Portas
SQL Server MVP
--|||David,
since they are different ws. nothing to do with months
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128608758.392918.172520@.g49g2000cwa.googlegroups.com...
> I don't understand why your first desired result has two rows for the
> same month. Going by your second example this seems to be a mistake. I
> think you want to GROUP BY DATEDIFF(MONTH, '/* some fixed date */,
> datecreated). For the dates join to a Calendar table.
> As posted your schema and data makes no sense. Why the duplicate rows?
> You need a unique constraint on (person,datecreated).
> --
> David Portas
> SQL Server MVP
> --
>

How i get "rank" in this one

Hello
I have this query that works good, but I cant figure out how to get the rank
and order by it desc.
SELECT * FROM Companies WHERE CONTAINS(*,'something')
how I add rank to this one?
TIA
/Lasse
you can't you need to use the contains table syntax for this, ie
SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as FT
on FT.[key]=Companies.pk
where pk is the primary key/unique key of the table you are full text
indexing.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> Hello
> I have this query that works good, but I cant figure out how to get the
rank
> and order by it desc.
> SELECT * FROM Companies WHERE CONTAINS(*,'something')
> how I add rank to this one?
> TIA
> /Lasse
>
|||Hilary,
hmm, one problem, when i search for both Firstname and Lastname using OR it
doesnt really get the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"
is there a way to solve this?
/Lasse
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> you can't you need to use the contains table syntax for this, ie
> SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as
FT
> on FT.[key]=Companies.pk
> where pk is the primary key/unique key of the table you are full text
> indexing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> rank
>
|||Hilary,
this one returns rank=128
5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund Sverige
5472
this one rank=112
3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
Rita Andersson Sverige 3714
when i search for 'rita OR andersson'
not sure i understand how that first one got higher value
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> Hilary,
> hmm, one problem, when i search for both Firstname and Lastname using OR
it
> doesnt really get the "most correct" values first, they get the highest
rank[vbcol=seagreen]
> but...... and if I use AND nothing shows since no columns contain all
> "keywords"
> is there a way to solve this?
> /Lasse
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
as[vbcol=seagreen]
> FT
the
>
|||Lasse,
Adding RANK is easy, understanding the results is a bit more difficult as
you will need to supply additional information about your environment in
order to understand the RANK values that are returned from your FTS queries.
First of all, you should review the SQL Server 2000 BOL title "Full-text
Search Recommendations" and the next to last paragraph on RANK.
Another factor is the number of rows in your table must be "statistically
significantly", i.e.. at least 10,000 rows in order for the query specific
RANK values to return the executants results. If you are testing with a very
small table, then this can explain why the rank values are not what you are
expecting.
As for searching [both Firstname and Lastname using OR it doesn't really get
the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"], this is a different problem as SQL Server 2000 (correct?) does
not search across columns, see KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
Note, while the above KB articles are specific to SQL Server 7.0, they also
apply to SQL Server 2000 as SQL Server 7.0's behavior was fixed to
correspond to the default SQL Server 2000 behavior.
Finally, one way to join multiple columns in one CONTAINSTABLE query and
influence the rank value of one column over another via the weight parameter
is the following:
use Northwind
SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, the AND condition between the Primary key (PK) values as you can also
make this an OR clause, but you may need to use a DISTINCT parameter to
remove duplicate results. Keep in mind, that you still need a "statistically
significantly" number of rows to get the best results.
Regards,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:O#mzCy9yEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hilary,
> this one returns rank=128
> 5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund
Sverige
> 5472
> this one rank=112
> 3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
> Rita Andersson Sverige 3714
> when i search for 'rita OR andersson'
> not sure i understand how that first one got higher value
>
>
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> it
> rank
> as
> the
>